ऑरलैंडो में इग्नाइट 2018 सम्मेलन में गतिशील सरणी सूत्रों की घोषणा के आठ दिन हो चुके हैं। यहाँ मैंने जो सीखा है:
- 24 सितंबर, 2018 को इग्नाइट में आधुनिक एरर्स की घोषणा की गई और आधिकारिक तौर पर डायनामिक एरेस कहा गया।
- मैंने एक 60-पृष्ठ ई-पुस्तक लिखी है जिसमें उन्हें उपयोग करने के 30 उदाहरण हैं, और मैं इसे 2018 के अंत तक मुफ्त दे रहा हूं।
- रोल-आउट किसी के चाहने वाले की तुलना में बहुत धीमा होने वाला है, जो निराशाजनक है। इतने आहिस्ता क्यो? Excel टीम ने Calc Engine कोड में परिवर्तन किया है जो 30 वर्षों से स्थिर है। विशेष चिंता का विषय: ऐड-इन के साथ जो एक्सेल में सूत्रों को इंजेक्ट करते हैं जो अनजाने में अंतर्निहित चौराहे का उपयोग करते हैं। यदि Excel अब एक Spill सीमा देता है, तो ऐड-इन्स टूट जाएगा।
- किसी सरणी द्वारा दी गई श्रेणी को संदर्भित करने का एक नया तरीका है:
=E3#
लेकिन इसका अभी तक कोई नाम नहीं है। # कहा जाता है गिरा फॉर्मूला ऑपरेटर । आप स्पिल रेफ (एक्सेल एमवीपी जॉन अकम्पोरा द्वारा सुझाए गए) या द स्पिलर (एमवीपी इंग्बोर्ग ह्वीगोर्स्ट द्वारा सुझाए गए ) जैसे नाम के बारे में क्या सोचते हैं ?
पिवट टेबल डेटा क्रंचिंग के सह-लेखक के रूप में, मुझे एक अच्छी पिवट टेबल से प्यार है। लेकिन क्या होगा अगर आपको अपडेट करने के लिए अपने पिवट टेबल की आवश्यकता है और आप अपने प्रबंधक के प्रबंधक को रिफ्रेश पर क्लिक करने के लिए भरोसा नहीं कर सकते हैं? आज वर्णित तकनीक एक धुरी तालिका को बदलने के लिए तीन सूत्रों की एक श्रृंखला प्रदान करती है।
अद्वितीय ग्राहकों की क्रमबद्ध सूची प्राप्त करने के लिए, =SORT(UNIQUE(E2:E564))
I2 में उपयोग करें ।
![](https://cdn.wiki-base.com/6133197/replace_a_pivot_table_with_3_dynamic_array_formulas_-_excel_tips_2.jpg.webp)
उत्पाद को शीर्ष पर रखने के लिए, =TRANSPOSE(SORT(UNIQUE(B2:B564)))
J1 में उपयोग करें ।
![](https://cdn.wiki-base.com/6133197/replace_a_pivot_table_with_3_dynamic_array_formulas_-_excel_tips_3.jpg.webp)
यहाँ एक समस्या है: आप नहीं जानते कि ग्राहकों की सूची कितनी लंबी होगी। आप नहीं जानते कि उत्पाद सूची कितनी चौड़ी होगी। यदि आप I2 # का संदर्भ देते हैं, तो स्पिलर स्वचालित रूप से दिए गए सरणी के वर्तमान आकार को संदर्भित करेगा।
धुरी तालिका के मान क्षेत्र को वापस करने का सूत्र J2 में एकल सरणी सूत्र है =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#)
।
अंग्रेजी में, यह कहता है कि आप G2: G564 से राजस्व जोड़ना चाहते हैं जहां E में ग्राहक I2 सरणी सूत्र से वर्तमान पंक्ति के ग्राहक से मेल खाते हैं और B में उत्पाद J1 में सरणी सूत्र के वर्तमान स्तंभ से मेल खाते हैं।
![](https://cdn.wiki-base.com/6133197/replace_a_pivot_table_with_3_dynamic_array_formulas_-_excel_tips_4.jpg.webp)
यदि अंतर्निहित डेटा बदल जाए तो क्या होगा? मैंने स्रोत में इन दो कोशिकाओं को बदलकर एक नया ग्राहक और एक नया उत्पाद जोड़ा।
![](https://cdn.wiki-base.com/6133197/replace_a_pivot_table_with_3_dynamic_array_formulas_-_excel_tips_5.jpg.webp)
रिपोर्ट नई पंक्तियों और नए कॉलम के साथ अपडेट होती है। I2 # और J1 # का ऐरे-रेंज संदर्भ अतिरिक्त पंक्ति और स्तंभ को संभालता है।
![](https://cdn.wiki-base.com/6133197/replace_a_pivot_table_with_3_dynamic_array_formulas_-_excel_tips_6.jpg.webp)
SUMIFS क्यों काम करता है? यह एक्सेल में एक अवधारणा है जिसे ब्रॉडकास्टिंग कहा जाता है। यदि आपके पास एक सूत्र है जो दो सरणियों को संदर्भित करता है:
- सरणी एक है (27 पंक्तियाँ) x (1 स्तंभ)
- सरणी दो है (1 पंक्ति) x (3 कॉलम)
- एक्सेल परिणामी सरणी लौटाएगा जो संदर्भित सरणियों के सबसे लंबे और चौड़े हिस्से जितना लंबा और चौड़ा है:
- परिणाम (27 पंक्तियों) x (3 कॉलम) होगा।
- इसे ब्रॉडकास्टिंग एरेज़ कहते हैं।
वीडियो देखेंा
एक्सेल फ़ाइल डाउनलोड करें
एक्सेल फ़ाइल को डाउनलोड करने के लिए: 3-डायनामिक-ऐरे-फॉर्मूलों के साथ एक पिवट-टेबल-रिप्लेसमेंट। xls
एक्सेल थॉट्स ऑफ द डे
मैंने अपने एक्सेल के बारे में सलाह के लिए अपने एक्सेल मास्टर दोस्तों से पूछा है। विचार करने के लिए आज का विचार:
"अपने डेटा को और अपने स्प्रैडशीट को पास रखें"
जॉर्डन गोल्डमेयर