अपनी स्प्रेडशीट बदलें: 5 डायनामिक ऐरे फंक्शन (FILTER, UNIQUE, SORT)

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

इस ट्यूटोरियल में, हम दिखाते हैं कि डायनामिक ऐरे फंक्शन (FILTER, UNIQUE, SORT) 5 तरीकों से आपके काम करने का तरीका बदल देंगे।

Excel में डायनामिक ऐरे और स्पिलिंग

डायनामिक ऐरे: एक सेल में सूत्र डालें, और Excel स्वचालित रूप से परिणामों को पड़ोसी सेल्स में भरता या स्पिल करता है। यदि परिणाम का आकार बदलता है (अधिक पंक्तियाँ, कम पंक्तियाँ), तो स्पिल्ड रेंज स्वचालित रूप से बढ़ती या सिकुड़ती है। आप एक स्पिल्ड रेंज को पहचानेंगे क्योंकि:

• सूत्र केवल ऊपरी-बाएं सेल में रहता है।
• अन्य सेल्स एक हल्की बॉर्डर दिखाते हैं, और यदि आप उन पर क्लिक करते हैं, तो आप सूत्र को ग्रे-आउट देखेंगे।
• आप हैश सिंबल (#) का उपयोग करके संपूर्ण स्पिल्ड रेंज को A2# के रूप में संदर्भित कर सकते हैं।

डायनामिक ऐरे Microsoft 365 (Excel for Microsoft 365), Excel 2021, और बाद के संस्करणों में उपलब्ध हैं।

1. डुप्लिकेट के बिना सारांश के लिए अद्वितीय सूचियाँ उत्पन्न करें

डायनामिक ऐरे से पहले, डुप्लिकेट हटाने के लिए या तो "Remove Duplicates" या जटिल सूत्रों की आवश्यकता होती थी। UNIQUE त्वरित सारांश के लिए स्पिल करने योग्य, अद्वितीय सूचियाँ बनाता है। यह ड्रॉपडाउन, सत्यापन सूचियों, और पिवट-मुक्त डैशबोर्ड के लिए आदर्श है।
अद्वितीय क्षेत्र सूची:

• एक सेल चुनें और निम्न सूत्र डालें।

यह सूत्र अद्वितीय उत्पादों की एक सूची स्पिल करता है।

अद्वितीय संयोजन:
क्षेत्र AND सेल्सपर्सन के अद्वितीय संयोजन प्राप्त करें:

यह हर अद्वितीय संयोजन को दिखाने वाला दो-कॉलम स्पिल लौटाता है।

अद्वितीय ऑर्डर गिनें:

• सारांश के लिए UNIQUE को COUNTA के साथ संयोजित करें।

यह सूत्र अद्वितीय ऑर्डर की कुल संख्या की गणना करता है। यदि आप डेटासेट में नई पंक्तियाँ जोड़ते हैं, तो सूची स्वचालित रूप से रीफ्रेश हो जाती है।

मान जो ठीक एक बार आते हैं:
उन मानों को खोजें जो केवल एक बार होते हैं।

तीसरा तर्क (TRUE) केवल उन मानों को लौटाता है जो दोहराए नहीं गए हैं।

डेटा सत्यापन (ड्रॉपडाउन) के लिए UNIQUE का उपयोग करें:

1. उस सेल को चुनें जहां आप ड्रॉपडाउन चाहते हैं।
2. Data टैब पर जाएं >> Data Validation चुनें।
3. Allow को List पर सेट करें।
4. Source में, टाइप करें:

=SUMIF(B2:B61, I15#, G2:G61) 

यह सूत्र स्पिल्ड क्षेत्रों की पूरी सूची (I15#) को संदर्भित करता है। SUMIF प्रत्येक क्षेत्र के लिए कुल राजस्व लौटाता है। बिक्री में पंक्तियाँ जोड़ें या बदलें, और क्षेत्र और योग स्वचालित रूप से समायोजित हो जाते हैं।

अब, क्षेत्र बदलें और कुल राजस्व चयन के आधार पर स्वचालित रूप से अपडेट होगा।

2. डायनामिक रिपोर्ट के लिए डेटा स्वचालित रूप से फ़िल्टर करें

पारंपरिक फ़िल्टरिंग के लिए मैन्युअल फ़िल्टर या जटिल सूत्रों की आवश्यकता होती है। FILTER() फंक्शन डायनामिक ऐरे के लिए सबसे शक्तिशाली टूल में से एक है। यह केवल उन पंक्तियों को निकालता है जो आपकी शर्तों को पूरा करती हैं और परिणाम को एक टेबल-जैसी रेंज में स्पिल करता है। इस फ़ंक्शन का उपयोग करके, आप एक रिपोर्ट बना सकते हैं जो तुरंत अपडेट होती है।
क्षेत्र के अनुसार बिक्री फ़िल्टर करें:

• FILTER फ़ंक्शन के डायनामिक व्यवहार को दिखाने के लिए, क्षेत्र के लिए एक ड्रॉप-डाउन का उपयोग करें।

=FILTER(A2:G61, B2:B61="East") 

• इसे और अधिक डायनामिक बनाने के लिए, ड्रॉप-डाउन से मानदंड सेल को संदर्भित करें।

=FILTER(A2:G61, B2:B61=I4) 

यह उन सभी पंक्तियों को स्पिल करता है जहां क्षेत्र "East" है। यह एक मिनी रिपोर्ट है जो डेटा जोड़ने या हटाने पर विस्तार या संकुचन करती है। I4 को "North" में बदलें, और रिपोर्ट स्वचालित रूप से अपडेट हो जाती है—कोई VBA या मैन्युअल रीफ्रेश की आवश्यकता नहीं।

यह डेटा की स्थिर प्रतियों को समाप्त करता है; आपकी रिपोर्ट हमेशा स्रोत को दर्शाती है।
एकाधिक मानदंड:
East क्षेत्र AND $1,000 से अधिक राशि के लिए फ़िल्टर करें:

=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches") 

एस्टेरिस्क (*) AND के रूप में काम करता है। आप OR शर्तों के लिए प्लस (+) का उपयोग कर सकते हैं।
आप ड्रॉपडाउन से मानदंड चुनकर इंटरैक्टिव डैशबोर्ड बना सकते हैं।

3. SORT() और SORTBY() का उपयोग करके स्वचालित रूप से क्रमबद्ध सूचियाँ बनाएं

सॉर्टिंग का मतलब डेटा कॉपी करना या टेबल का उपयोग करना होता था। SORT डायनामिक, स्पिल करने योग्य क्रमबद्ध दृश्य बनाता है। जब भी आप डेटासेट में जोड़ते, हटाते, या अपडेट करते हैं, तो यह स्वचालित रूप से डेटा को क्रमबद्ध करता है।
ऑटो-सॉर्टेड बिक्री लीडरबोर्ड:

यह सूत्र संपूर्ण डेटा रेंज को कॉलम 7 (राशि) के अनुसार अवरोही क्रम में (-1) क्रमबद्ध करता है। मूल डेटा अपरिवर्तित रहता है। एक नई शीर्ष बिक्री जोड़ें, और यह स्वचालित रूप से सही स्थिति में दिखाई देती है।

एकाधिक सॉर्ट स्तर:
सेल्सपर्सन के अनुसार, फिर राशि के अनुसार क्रमबद्ध करें:

=SORT(A2:G61, {3,7}, {1,-1}) 

कर्ली ब्रैकेट सरणियाँ बनाते हैं: कॉलम 3 आरोही, फिर कॉलम 7 अवरोही के अनुसार क्रमबद्ध करें।

अलग मानदंड द्वारा क्रमबद्ध करें:
SORTBY फ़ंक्शन आपको किसी अन्य रेंज में मानों के आधार पर एक रेंज को क्रमबद्ध करने देता है।
संपूर्ण रेंज को सेल्सपर्सन नाम के अनुसार क्रमबद्ध करें जबकि सभी कॉलम रखें:

=SORTBY(A2:G61, C2:C61, 1) 

4. लक्षित अद्वितीय सारांश के लिए FILTER और UNIQUE को संयोजित करें

उन्नत सारांश के लिए, आप फ़ंक्शन को फ़िल्टर करने के लिए पहले संयोजित कर सकते हैं, फिर अद्वितीय बना सकते हैं, एक साफ, ऑटो-अपडेटिंग सूची स्पिल कर सकते हैं। स्वयं-रखरखाव वाली रिपोर्ट बनाने के लिए फ़ंक्शन संयोजित करें।

• एक सेल चुनें और निम्न सूत्र डालें।

=UNIQUE(FILTER(D2:D61, B2:B61="North")) 

यह North क्षेत्र से उत्पादों को फ़िल्टर करता है, फिर अद्वितीय वालों को स्पिल करता है।

• अगला, सारांश को क्रमबद्ध करने के लिए SORT फ़ंक्शन जोड़ें।

=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North"))) 

अब सूत्र एक क्रमबद्ध, अद्वितीय उत्पाद सूची स्पिल करता है।

यह {=INDEX(…)} जैसे बोझिल सरणी सूत्रों को अद्वितीय फ़िल्टर की गई सूचियों के लिए बदल देता है। डेटा या मानदंड बदलें, और यह क्षेत्रीय उत्पाद इन्वेंट्री जैसे रिपोर्ट के लिए निर्बाध रूप से अपडेट स्पिल करता है।

5. डायनामिक, मानदंड-चालित सारांश पृष्ठ (FILTER, UNIQUE, SORT संयोजित करें)

अब, इन फ़ंक्शंस को एक मिनी सारांश/रिपोर्टिंग पेज में संयोजित करें जो कुछ मानदंड सेल्स से अपडेट होता है।
एक क्षेत्र-स्तरीय डैशबोर्ड बनाएं: एक क्षेत्र ड्रॉपडाउन (UNIQUE द्वारा संचालित), एक फ़िल्टर की गई क्षेत्र सूची (FILTER), और एक क्षेत्र-वार शीर्ष-उत्पाद सूची (FILTER + SORT)।

चरण 1: UNIQUE का उपयोग करके क्षेत्र ड्रॉपडाउन

अद्वितीय क्षेत्रों की एक सूची बनाई गई है और ड्रॉप-डाउन बनाने के लिए उपयोग की गई है।

चरण 2: क्षेत्र-स्तरीय बिक्री विवरण

=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "No sales in this region")

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

चरण 3: चयनित क्षेत्र में शीर्ष उत्पाद

पहचानें कि चुने गए क्षेत्र में कौन से उत्पाद सबसे अधिक बिकते हैं।

• Product और Total Revenue हेडर के साथ एक छोटी टेबल बनाएं।
• L4 में, चयनित क्षेत्र में बेचे गए अद्वितीय उत्पाद प्राप्त करें:

=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4)) 

यह उस क्षेत्र के लिए उत्पादों की एक सूची स्पिल करता है।

• M4 में, उस क्षेत्र में प्रति उत्पाद कुल राजस्व की गणना करें:

=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#) 

यह L4# में प्रत्येक उत्पाद से मेल खाने वाले कुल राजस्व की एक स्पिल की गई सूची लौटाता है।

• उन्हें राजस्व के अनुसार अवरोही क्रम में दिखाने के लिए, दो स्पिल्ड कॉलम को एक साथ क्रमबद्ध करें:

=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1) 

यहां, CHOOSE({1,2}, L4#, M4#) एक दो-कॉलम सरणी (उत्पाद और कुल राजस्व) बनाता है। 2 का अर्थ है "दूसरे कॉलम (कुल राजस्व) के अनुसार क्रमबद्ध करें"; -1 का अर्थ है अवरोही क्रम।

चयनित क्षेत्र में डायनामिक शीर्ष उत्पाद रिपोर्ट:

• B4 में क्षेत्र ड्रॉपडाउन बदलें। ध्यान दें कि सभी सारांश अपडेट हो जाते हैं।
• Sales में नया डेटा जोड़ें, और यह रिपोर्ट में जुड़ जाएगा।
• कॉपी किए गए सूत्र नहीं, कोई मैन्युअल सॉर्टिंग नहीं, कोई PivotTable रीफ्रेश नहीं।

निष्कर्ष

यह ट्यूटोरियल दर्शाता है कि डायनामिक ऐरे फ़ंक्शन (FILTER, UNIQUE, SORT) 5 तरीकों से आपके काम करने का तरीका बदल देते हैं। डायनामिक ऐरे फ़ंक्शन स्प्रेडशीट बनाए रखने की व्यस्तता को समाप्त करते हैं। आप विश्लेषण पर ध्यान केंद्रित करते हैं बजाय सूत्रों की प्रतिलिपि बनाने और टूटे हुए संदर्भों को ठीक करने के। रिपोर्ट स्वयं अपडेट होती हैं। डैशबोर्ड स्वचालित रूप से अपडेट होते हैं। एक बार जब आप इन फ़ंक्शंस का उपयोग करना शुरू करते हैं, तो आप पाएंगे कि वे सारांश और डैशबोर्ड के लिए आदर्श हैं—आप ऐसी रिपोर्ट बना सकते हैं जो बिना किसी मैन्युअल रीफ्रेशिंग, जटिल सरणी सूत्रों, या हेल्पर कॉलम के तुरंत अपडेट होती हैं।

मुफ़्त उन्नत Excel अभ्यास समाधान के साथ प्राप्त करें!