ऑटो-साइज़िंग के साथ डायनामिक रेंज

क्या आपके पास एक्सेल में डेटा के साथ टेबल हैं जिनका आकार बदला जा सकता है, यानी काम के दौरान पंक्तियों (कॉलम) की संख्या बढ़ या घट सकती है? यदि तालिका का आकार "फ्लोट" है, तो आपको इस क्षण की लगातार निगरानी करनी होगी और इसे ठीक करना होगा:

• रिपोर्ट फ़ार्मुलों में लिंक जो हमारी तालिका को संदर्भित करते हैं
• हमारी तालिका के अनुसार निर्मित पिवट तालिकाओं की प्रारंभिक श्रेणियां
• हमारी तालिका के अनुसार निर्मित चार्ट की प्रारंभिक श्रेणियां
• ड्रॉपडाउन के लिए श्रेणियां जो डेटा स्रोत के रूप में हमारी तालिका का उपयोग करती हैं

कुल मिलाकर ये सब आपको बोर नहीं होने देंगे

गतिशील "रबर" रेंज बनाने के लिए यह अधिक सुविधाजनक और सही होगा, जो स्वचालित रूप से डेटा की पंक्तियों और स्तंभों की वास्तविक संख्या में आकार में समायोजित हो जाएगा। इसे लागू करने के लिए, कई तरीके हैं।

विधि 1. स्मार्ट टेबल

सेल की अपनी श्रेणी को हाइलाइट करें और टैब से चुनें होम - तालिका के रूप में प्रारूप (होम - तालिका के रूप में प्रारूप):

यदि आपको साइड इफेक्ट के रूप में तालिका में जोड़े गए धारीदार डिज़ाइन की आवश्यकता नहीं है, तो आप इसे दिखाई देने वाले टैब पर बंद कर सकते हैं कंस्ट्रक्टर (डिजाइन). इस तरह से बनाई गई प्रत्येक तालिका को एक नाम प्राप्त होता है जिसे टैब पर उसी स्थान पर अधिक सुविधाजनक से बदला जा सकता है कंस्ट्रक्टर (डिजाइन) मैदान में तालिका नाम (तालिका नाम).

अब हम अपने "स्मार्ट टेबल" के लिए डायनेमिक लिंक का उपयोग कर सकते हैं:

• टेबल 1 - शीर्षलेख पंक्ति को छोड़कर संपूर्ण तालिका से लिंक करें (A2:D5)
• तालिका1[#सभी] - संपूर्ण तालिका से लिंक करें (A1:D5)
• तालिका 1 [पीटर] - पहले सेल-हेडर के बिना श्रेणी-स्तंभ का संदर्भ (C2:C5)
• तालिका 1 [# शीर्षलेख] - कॉलम के नाम के साथ "हेडर" से लिंक करें (A1:D1)

इस तरह के संदर्भ सूत्रों में बहुत अच्छा काम करते हैं, उदाहरण के लिए:

= SUM (तालिका 1 [मास्को]) - कॉलम "मॉस्को" के लिए योग की गणना

or

= वीपीआर (F5;टेबल 1;3;0) - सेल F5 से महीने के लिए तालिका में खोजें और इसके लिए सेंट पीटर्सबर्ग राशि जारी करें (VLOOKUP क्या है?)

टैब पर चयन करके पिवट टेबल बनाते समय ऐसे लिंक का सफलतापूर्वक उपयोग किया जा सकता है सम्मिलित करें - पिवट तालिका (सम्मिलित करें - पिवट तालिका) और डेटा स्रोत के रूप में स्मार्ट तालिका का नाम दर्ज करना:

यदि आप ऐसी तालिका का एक टुकड़ा चुनते हैं (उदाहरण के लिए, पहले दो कॉलम) और किसी भी प्रकार का आरेख बनाते हैं, तो नई लाइनें जोड़ते समय, वे स्वचालित रूप से आरेख में जुड़ जाएंगे।

ड्रॉप-डाउन सूचियां बनाते समय, स्मार्ट टेबल तत्वों के सीधे लिंक का उपयोग नहीं किया जा सकता है, लेकिन आप एक सामरिक चाल का उपयोग करके इस सीमा को आसानी से प्राप्त कर सकते हैं - फ़ंक्शन का उपयोग करें अप्रत्यक्ष (अप्रत्यक्ष), जो टेक्स्ट को एक लिंक में बदल देता है:

वे। टेक्स्ट स्ट्रिंग के रूप में एक स्मार्ट टेबल का लिंक (उद्धरण चिह्नों में!) एक पूर्ण लिंक में बदल जाता है, और ड्रॉप-डाउन सूची सामान्य रूप से इसे मानती है।

विधि 2: गतिशील नामित श्रेणी

यदि किसी कारण से आपके डेटा को स्मार्ट टेबल में बदलना अवांछनीय है, तो आप थोड़ी अधिक जटिल, लेकिन अधिक सूक्ष्म और बहुमुखी विधि का उपयोग कर सकते हैं - एक्सेल में एक डायनामिक नामित रेंज बनाएं जो हमारी तालिका को संदर्भित करता है। फिर, जैसा कि एक स्मार्ट तालिका के मामले में होता है, आप किसी भी सूत्र, रिपोर्ट, चार्ट आदि में बनाई गई श्रेणी के नाम का स्वतंत्र रूप से उपयोग कर सकते हैं। आइए एक सरल उदाहरण से शुरू करें:

कार्य: एक गतिशील नामित श्रेणी बनाएं जो शहरों की सूची को संदर्भित करे और नए शहरों को जोड़ने या उन्हें हटाते समय स्वचालित रूप से आकार में खिंचाव और सिकुड़ जाए।

हमें किसी भी संस्करण में उपलब्ध दो बिल्ट-इन एक्सेल फ़ंक्शन की आवश्यकता होगी - POICPOZ (मिलान) श्रेणी के अंतिम सेल को निर्धारित करने के लिए, और सूचकांक (अनुक्रमणिका) एक गतिशील लिंक बनाने के लिए।

MATCH का उपयोग करके अंतिम सेल ढूँढना

MATCH(lookup_value, रेंज, match_type) - एक फ़ंक्शन जो किसी श्रेणी (पंक्ति या स्तंभ) में दिए गए मान की खोज करता है और उस सेल की क्रमिक संख्या देता है जहां यह पाया गया था। उदाहरण के लिए, सूत्र MATCH("मार्च";A1:A5;0) परिणाम के रूप में नंबर 4 लौटाएगा, क्योंकि "मार्च" शब्द कॉलम A1:A5 में चौथे सेल में स्थित है। अंतिम फ़ंक्शन तर्क Match_Type = 0 का अर्थ है कि हम एक सटीक मिलान की तलाश कर रहे हैं। यदि यह तर्क निर्दिष्ट नहीं है, तो फ़ंक्शन निकटतम सबसे छोटे मान के लिए खोज मोड पर स्विच हो जाएगा - यह वही है जो हमारे सरणी में अंतिम कब्जे वाले सेल को खोजने के लिए सफलतापूर्वक उपयोग किया जा सकता है।

चाल का सार सरल है। MATCH ऊपर से नीचे की सीमा में कोशिकाओं की खोज करता है और, सिद्धांत रूप में, जब यह दिए गए के लिए निकटतम सबसे छोटा मान पाता है, तो रुक जाना चाहिए। यदि आप एक ऐसा मान निर्दिष्ट करते हैं जो स्पष्ट रूप से वांछित मान के रूप में तालिका में उपलब्ध किसी से भी अधिक है, तो MATCH तालिका के बहुत अंत तक पहुंच जाएगा, कुछ भी नहीं ढूंढेगा और अंतिम भरे हुए सेल की अनुक्रम संख्या देगा। और हमें इसकी आवश्यकता है!

यदि हमारे सरणी में केवल संख्याएँ हैं, तो हम एक संख्या को वांछित मान के रूप में निर्दिष्ट कर सकते हैं, जो स्पष्ट रूप से तालिका में से किसी से भी अधिक है:

गारंटी के लिए, आप संख्या 9ई + 307 (9 की घात का 10 गुना 307, यानी 9 शून्य के साथ 307) का उपयोग कर सकते हैं - वह अधिकतम संख्या जिसके साथ एक्सेल सिद्धांत रूप में काम कर सकता है।

यदि हमारे कॉलम में टेक्स्ट वैल्यू हैं, तो सबसे बड़ी संभावित संख्या के बराबर के रूप में, आप रिपीट ("i", 255) निर्माण सम्मिलित कर सकते हैं - एक टेक्स्ट स्ट्रिंग जिसमें 255 अक्षर "i" शामिल हैं - का अंतिम अक्षर वर्णमाला। चूंकि एक्सेल वास्तव में खोज करते समय वर्ण कोड की तुलना करता है, इसलिए हमारी तालिका में कोई भी पाठ तकनीकी रूप से इतनी लंबी "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" पंक्ति से "छोटा" होगा:

INDEX . का उपयोग करके एक लिंक जेनरेट करें

अब जब हम तालिका में अंतिम गैर-रिक्त तत्व की स्थिति जानते हैं, तो यह हमारी पूरी श्रृंखला के लिए एक लिंक बना रहता है। इसके लिए हम फ़ंक्शन का उपयोग करते हैं:

INDEX(श्रेणी; row_num; column_num)

यह पंक्ति और स्तंभ संख्या के आधार पर सेल की सामग्री देता है, उदाहरण के लिए, फ़ंक्शन =INDEX(A1:D5;3;4) हमारी तालिका में शहरों और महीनों के साथ पिछली विधि से 1240 देगा - सामग्री तीसरी पंक्ति और चौथे कॉलम से, यानी सेल D3। यदि केवल एक कॉलम है, तो उसकी संख्या को छोड़ा जा सकता है, अर्थात सूत्र INDEX(A4:A3;2) अंतिम स्क्रीनशॉट में "समारा" देगा।

और एक पूरी तरह से स्पष्ट बारीकियां नहीं है: यदि INDEX हमेशा की तरह = चिह्न के बाद सेल में दर्ज नहीं किया जाता है, लेकिन कोलन के बाद सीमा के संदर्भ के अंतिम भाग के रूप में उपयोग किया जाता है, तो यह अब बाहर नहीं निकलता है सेल की सामग्री, लेकिन उसका पता! इस प्रकार, $A$2:INDEX($A$2:$A$100;3) जैसा सूत्र आउटपुट पर श्रेणी A2:A4 का संदर्भ देगा।

और यह वह जगह है जहां MATCH फ़ंक्शन आता है, जिसे हम सूची के अंत को गतिशील रूप से निर्धारित करने के लिए INDEX के अंदर सम्मिलित करते हैं:

=$ए$2:इंडेक्स($ए$2:$ए$100; मैच (आरईपी ("मैं"; 255)ए2:ए100))

एक नामित श्रेणी बनाएं

यह सब एक पूरे में पैक करने के लिए बनी हुई है। एक टैब खोलें सूत्र (सूत्र) और क्लिक करें नाम प्रबंधक (नाम प्रबंधक). खुलने वाली विंडो में, बटन पर क्लिक करें बनाएं (नया), क्षेत्र में हमारी श्रेणी का नाम और सूत्र दर्ज करें रेंज (संदर्भ):

क्लिक करना बाकी है OK और तैयार श्रेणी का उपयोग किसी भी सूत्र, ड्रॉप-डाउन सूची या चार्ट में किया जा सकता है।

• तालिकाओं और लुकअप मानों को जोड़ने के लिए VLOOKUP फ़ंक्शन का उपयोग करना
• ऑटो-पॉप्युलेटिंग ड्रॉपडाउन सूची कैसे बनाएं
• बड़ी मात्रा में डेटा का विश्लेषण करने के लिए पिवट टेबल कैसे बनाएं