एक्सेल में गतिशील सरणियाँ

गतिशील सरणियाँ क्या हैं

सितंबर 2018 में, माइक्रोसॉफ्ट ने एक अपडेट जारी किया जो माइक्रोसॉफ्ट एक्सेल में एक पूरी तरह से नया टूल जोड़ता है: डायनामिक एरेज़ और उनके साथ काम करने के लिए 7 नए फ़ंक्शन। ये चीजें, अतिशयोक्ति के बिना, सूत्रों और कार्यों और चिंता के साथ काम करने की सभी सामान्य तकनीक को मौलिक रूप से बदल देती हैं, शाब्दिक रूप से, प्रत्येक उपयोगकर्ता।

सार को समझाने के लिए एक सरल उदाहरण पर विचार करें।

मान लीजिए कि हमारे पास शहर-महीनों के डेटा के साथ एक साधारण तालिका है। क्या होगा यदि हम शीट के दायीं ओर किसी खाली सेल का चयन करते हैं और उसमें एक सूत्र दर्ज करते हैं जो एक सेल से नहीं, बल्कि तुरंत एक श्रेणी से जुड़ता है?

एक्सेल के सभी पिछले संस्करणों में, पर क्लिक करने के बाद दर्ज हमें केवल एक प्रथम सेल B2 की सामग्री प्राप्त होगी। और कैसे?

ठीक है, या इस श्रेणी को किसी प्रकार के एकत्रीकरण फ़ंक्शन जैसे =SUM(B2:C4) में लपेटना संभव होगा और इसके लिए एक भव्य कुल प्राप्त करना संभव होगा।

यदि हमें एक आदिम योग की तुलना में अधिक जटिल संचालन की आवश्यकता होती है, जैसे कि अद्वितीय मान या शीर्ष 3 निकालना, तो हमें कीबोर्ड शॉर्टकट का उपयोग करके एक सरणी सूत्र के रूप में अपना सूत्र दर्ज करना होगा। कंट्रोल+पाली+दर्ज.

अब सब कुछ अलग है।

अब इस तरह का फॉर्मूला डालने के बाद, हम बस पर क्लिक कर सकते हैं दर्ज - और परिणामस्वरूप सभी मान तुरंत प्राप्त करें uXNUMXbuXNUMXbजिसका हमने उल्लेख किया है:

यह कोई जादू नहीं है, बल्कि Microsoft Excel के पास अब नई गतिशील सरणियाँ हैं। नई दुनिया में आपका स्वागत है

गतिशील सरणियों के साथ काम करने की विशेषताएं

तकनीकी रूप से, हमारा संपूर्ण डायनेमिक सरणी पहले सेल G4 में संग्रहीत है, इसके डेटा के साथ आवश्यक संख्या में कोशिकाओं को दाईं और नीचे भरता है। यदि आप सरणी में किसी अन्य सेल का चयन करते हैं, तो फॉर्मूला बार में लिंक निष्क्रिय होगा, यह दर्शाता है कि हम "चाइल्ड" सेल में से एक में हैं:

एक या अधिक "चाइल्ड" कोशिकाओं को हटाने के प्रयास से कुछ भी नहीं होगा - एक्सेल तुरंत पुनर्गणना करेगा और उन्हें भर देगा।

उसी समय, हम इन "चाइल्ड" कोशिकाओं को अन्य सूत्रों में सुरक्षित रूप से संदर्भित कर सकते हैं:

यदि आप किसी सरणी के पहले कक्ष की प्रतिलिपि बनाते हैं (उदाहरण के लिए, G4 से F8 तक), तो संपूर्ण सरणी (उसके संदर्भ) नियमित सूत्रों की तरह उसी दिशा में आगे बढ़ेगी:

यदि हमें सरणी को स्थानांतरित करने की आवश्यकता है, तो यह स्थानांतरित करने के लिए पर्याप्त होगा (माउस या के संयोजन के साथ कंट्रोल+X, कंट्रोल+V), फिर से, केवल पहला मुख्य सेल G4 - इसके बाद, इसे एक नए स्थान पर स्थानांतरित कर दिया जाएगा और हमारे पूरे सरणी को फिर से विस्तारित किया जाएगा।

यदि आपको बनाई गई गतिशील सरणी के लिए शीट पर कहीं और संदर्भित करने की आवश्यकता है, तो आप इसके प्रमुख सेल के पते के बाद विशेष वर्ण # ("पाउंड") का उपयोग कर सकते हैं:

उदाहरण के लिए, अब आप आसानी से एक सेल में ड्रॉपडाउन सूची बना सकते हैं जो बनाई गई गतिशील सरणी को संदर्भित करता है:

गतिशील सरणी त्रुटियां

लेकिन क्या होता है यदि सरणी का विस्तार करने के लिए पर्याप्त जगह नहीं है, या यदि इसके पथ में पहले से ही अन्य डेटा द्वारा कब्जा कर लिया गया कक्ष हैं? एक्सेल में मौलिक रूप से नए प्रकार की त्रुटियों को पूरा करें – #स्थानांतरण करना! (#स्पिल!):

हमेशा की तरह, यदि हम पीले हीरे और विस्मयादिबोधक चिह्न के साथ आइकन पर क्लिक करते हैं, तो हमें समस्या के स्रोत का अधिक विस्तृत विवरण मिलेगा और हम जल्दी से हस्तक्षेप करने वाली कोशिकाओं को ढूंढ सकते हैं:

यदि ऐरे शीट से हट जाता है या मर्ज किए गए सेल से टकराता है तो इसी तरह की त्रुटियाँ होंगी। यदि आप बाधा को दूर करते हैं, तो मक्खी पर सब कुछ तुरंत ठीक हो जाएगा।

गतिशील सरणियाँ और स्मार्ट टेबल

यदि डायनेमिक ऐरे किसी कीबोर्ड शॉर्टकट द्वारा बनाई गई "स्मार्ट" तालिका की ओर इशारा करता है कंट्रोल+T द्वारा या होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें), तो यह अपने मुख्य गुण - ऑटो-साइज़िंग को भी इनहेरिट कर लेगा।

नीचे या दाईं ओर नया डेटा जोड़ते समय, स्मार्ट टेबल और डायनेमिक रेंज भी अपने आप खिंच जाएगी:

हालांकि, एक सीमा है: हम एक स्मार्ट टेबल के अंदर फ़ोरमुला में एक गतिशील रेंज संदर्भ का उपयोग नहीं कर सकते हैं:

गतिशील सरणियाँ और अन्य एक्सेल सुविधाएँ

ठीक है, तुम कहते हो। यह सब दिलचस्प और मजेदार है। पहले की तरह, मूल श्रेणी के पहले सेल के संदर्भ में नीचे और दाईं ओर और सभी के संदर्भ में सूत्र को मैन्युअल रूप से फैलाने की कोई आवश्यकता नहीं है। और यह सबकुछ है?

काफी नहीं है.

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

खिसकाना

किसी श्रेणी को स्थानांतरित करने के लिए (पंक्तियों और स्तंभों को स्वैप करें) Microsoft Excel में हमेशा एक अंतर्निहित कार्य होता है ट्रांसपी (ट्रांसपोज़). हालांकि, इसका उपयोग करने के लिए, आपको पहले परिणामों के लिए सही ढंग से श्रेणी का चयन करना होगा (उदाहरण के लिए, यदि इनपुट 5×3 की सीमा थी, तो आपने 3×5 का चयन किया होगा), फिर फ़ंक्शन दर्ज करें और दबाएं संयोजन कंट्रोल+पाली+दर्ज, क्योंकि यह केवल सरणी सूत्र मोड में काम कर सकता है।

अब आप सिर्फ एक सेल को सेलेक्ट कर सकते हैं, उसमें वही फॉर्मूला एंटर करें और नॉर्मल पर क्लिक करें दर्ज - डायनेमिक ऐरे सब कुछ अपने आप कर लेगा:

पहाड़ा

यह वह उदाहरण है जो मैं देता था जब मुझे एक्सेल में सरणी सूत्रों के लाभों की कल्पना करने के लिए कहा गया था। अब, संपूर्ण पाइथागोरस तालिका की गणना करने के लिए, पहले सेल बी 2 में खड़े होने के लिए पर्याप्त है, वहां एक सूत्र दर्ज करें जो दो सरणी (संख्याओं के लंबवत और क्षैतिज सेट 1..10) को गुणा करता है और बस क्लिक करें दर्ज:

ग्लूइंग और केस रूपांतरण

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

निष्कर्ष शीर्ष 3

मान लीजिए कि हमारे पास संख्याओं का एक समूह है जिससे हम शीर्ष तीन परिणाम प्राप्त करना चाहते हैं, उन्हें अवरोही क्रम में व्यवस्थित करना। अब यह एक सूत्र द्वारा किया जाता है और, फिर से, बिना किसी के कंट्रोल+पाली+दर्ज पहले की तरह:

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

VLOOKUP एक ​​साथ कई कॉलम निकाल रहा है

कार्य VPR (VLOOKUP) अब आप एक से नहीं, बल्कि एक साथ कई कॉलम से मान खींच सकते हैं - फ़ंक्शन के तीसरे तर्क में एक सरणी के रूप में बस उनकी संख्या (किसी भी वांछित क्रम में) निर्दिष्ट करें:

ऑफ़सेट फ़ंक्शन एक गतिशील सरणी लौटा रहा है

डेटा विश्लेषण के लिए सबसे दिलचस्प और उपयोगी (VLOOKUP के बाद) फ़ंक्शन में से एक फ़ंक्शन है निपटान (ऑफसेट), जिसके लिए मैंने एक समय में अपनी पुस्तक का एक पूरा अध्याय और यहाँ एक लेख समर्पित किया था। इस फ़ंक्शन को समझने और महारत हासिल करने में कठिनाई हमेशा यह रही है कि इसके परिणामस्वरूप डेटा की एक सरणी (रेंज) लौटा दी गई, लेकिन हम इसे नहीं देख सके, क्योंकि एक्सेल अभी भी नहीं जानता था कि बॉक्स के बाहर सरणी के साथ कैसे काम किया जाए।

अब यह समस्या पुरानी हो गई है। देखें कि अब, OFFSET द्वारा लौटाए गए एकल सूत्र और एक गतिशील सरणी का उपयोग करके, आप किसी दिए गए उत्पाद के लिए किसी भी क्रमबद्ध तालिका से सभी पंक्तियों को कैसे निकाल सकते हैं:

आइए एक नजर डालते हैं उनके तर्कों पर:

• A1 - प्रारंभिक सेल (संदर्भ बिंदु)
• (F2;A2:A30;0) - शुरुआती सेल से नीचे की ओर शिफ्ट की गणना - पहली मिली गोभी में।
• 0 - शुरुआती सेल के सापेक्ष "विंडो" को दाईं ओर शिफ्ट करना
  
• (A2:A30;F2) - लौटाई गई "खिड़की" की ऊंचाई की गणना - उन पंक्तियों की संख्या जहां गोभी है।
• 4 - क्षैतिज रूप से "विंडो" का आकार, यानी आउटपुट 4 कॉलम

गतिशील सरणियों के लिए नए कार्य

पुराने कार्यों में गतिशील सरणी तंत्र का समर्थन करने के अलावा, माइक्रोसॉफ्ट एक्सेल में कई पूरी तरह से नए कार्यों को जोड़ा गया है, विशेष रूप से गतिशील सरणी के साथ काम करने के लिए तेज किया गया है। विशेष रूप से, ये हैं:

• ग्रेड (क्रम से लगाना) - इनपुट रेंज को सॉर्ट करता है और आउटपुट पर एक डायनेमिक ऐरे बनाता है
  
• सॉर्टपो (इसके अनुसार क्रमबद्ध करें) - एक श्रेणी को दूसरे से मूल्यों के आधार पर क्रमबद्ध कर सकते हैं
  
• फ़िल्टर (फ़िल्टर) - निर्दिष्ट शर्तों को पूरा करने वाली स्रोत श्रेणी से पंक्तियों को पुनः प्राप्त करता है
• अद्वितीय (अद्वितीय) - किसी श्रेणी से अद्वितीय मान निकालता है या डुप्लिकेट हटाता है
  
• निंदनीय (रंडारे) - किसी दिए गए आकार की यादृच्छिक संख्याओं की एक सरणी उत्पन्न करता है
• खेड़ी (क्रम) - किसी दिए गए चरण के साथ संख्याओं के अनुक्रम से एक सरणी बनाता है
  

उनके बारे में - थोड़ी देर बाद। वे विचारशील अध्ययन के लिए एक अलग लेख (और एक नहीं) के लायक हैं

निष्कर्ष

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

परिणामों को सारांशित करना, पेशेवरों गतिशील सरणियाँ, आप निम्नलिखित लिख सकते हैं:

• आप संयोजन के बारे में भूल सकते हैं कंट्रोल+पाली+दर्ज. एक्सेल अब "नियमित सूत्रों" और "सरणी सूत्रों" के बीच कोई अंतर नहीं देखता है और उन्हें उसी तरह व्यवहार करता है।
• समारोह के बारे में SUMPRODUCT (समउत्पाद), जिसका उपयोग पहले बिना के सरणी सूत्रों को दर्ज करने के लिए किया जाता था कंट्रोल+पाली+दर्ज आप भी भूल सकते हैं - अब यह काफी आसान है SUM и दर्ज.
• स्मार्ट टेबल और परिचित कार्य (SUM, IF, VLOOKUP, SUMIFS, आदि) अब भी पूरी तरह या आंशिक रूप से गतिशील सरणियों का समर्थन करते हैं।
• पश्चगामी संगतता है: यदि आप एक्सेल के पुराने संस्करण में गतिशील सरणियों के साथ एक कार्यपुस्तिका खोलते हैं, तो वे सरणी सूत्रों (घुंघराले ब्रेसिज़ में) में बदल जाएंगे और "पुरानी शैली" में काम करना जारी रखेंगे।

कुछ नंबर मिला माइनस:

• आप एक गतिशील सरणी से अलग-अलग पंक्तियों, स्तंभों या कक्षों को हटा नहीं सकते हैं, अर्थात यह एक इकाई के रूप में रहता है।
• आप सामान्य तरीके से एक गतिशील सरणी को सॉर्ट नहीं कर सकते डेटा - छँटाई (डेटा - क्रमबद्ध करें). इसके लिए अब एक विशेष समारोह है। ग्रेड (क्रम से लगाना).
• डायनेमिक रेंज को स्मार्ट टेबल में नहीं बदला जा सकता (लेकिन आप स्मार्ट टेबल के आधार पर डायनेमिक रेंज बना सकते हैं)।
  

बेशक, यह अंत नहीं है, और मुझे यकीन है कि Microsoft भविष्य में इस तंत्र में सुधार करना जारी रखेगा।

मैं कहां से डाउनलोड कर सकता हूं?

और अंत में, मुख्य प्रश्न 🙂

माइक्रोसॉफ्ट ने पहली बार सितंबर 2018 में एक सम्मेलन में एक्सेल में गतिशील सरणियों का पूर्वावलोकन घोषित किया और दिखाया आग लगना. अगले कुछ महीनों में, पहली बार में, नई सुविधाओं का गहन परीक्षण और चलन शुरू हुआ बिल्ली की स्वयं Microsoft के कर्मचारी, और फिर Office इनसाइडर्स के मंडली से स्वयंसेवी परीक्षकों पर। इस वर्ष, डायनेमिक सरणियों को जोड़ने वाला अद्यतन नियमित रूप से Office 365 ग्राहकों के लिए धीरे-धीरे रोल आउट किया जाने लगा। उदाहरण के लिए, मैंने इसे केवल अगस्त में अपनी Office 365 Pro Plus (मासिक लक्षित) सदस्यता के साथ प्राप्त किया था।

यदि आपके एक्सेल में अभी तक गतिशील सरणियाँ नहीं हैं, लेकिन आप वास्तव में उनके साथ काम करना चाहते हैं, तो निम्नलिखित विकल्प हैं:

• यदि आपके पास Office 365 सदस्यता है, तो आप बस तब तक प्रतीक्षा कर सकते हैं जब तक यह अद्यतन आप तक नहीं पहुँच जाता। यह कितनी जल्दी होता है यह इस बात पर निर्भर करता है कि आपके कार्यालय में कितनी बार अपडेट डिलीवर किए जाते हैं (साल में एक बार, हर छह महीने में एक बार, महीने में एक बार)। यदि आपके पास एक कॉर्पोरेट पीसी है, तो आप अपने व्यवस्थापक से अपडेट को अधिक बार डाउनलोड करने के लिए सेट करने के लिए कह सकते हैं।
  
• आप उन ऑफिस इनसाइडर टेस्ट वालंटियर्स के रैंक में शामिल हो सकते हैं - फिर आप सभी नई सुविधाओं और कार्यों को प्राप्त करने वाले पहले व्यक्ति होंगे (लेकिन निश्चित रूप से एक्सेल में बग्गी बढ़ने की संभावना है)।
• यदि आपके पास सदस्यता नहीं है, लेकिन एक्सेल का एक बॉक्सिंग स्टैंडअलोन संस्करण है, तो आपको कम से कम 2022 में ऑफिस और एक्सेल के अगले संस्करण के रिलीज होने तक इंतजार करना होगा। ऐसे संस्करणों के उपयोगकर्ता केवल सुरक्षा अद्यतन और बग समाधान प्राप्त करते हैं, और सभी नए "उपहार" अब केवल Office 365 ग्राहकों के पास जाते हैं। दुखद लेकिन सच
  

किसी भी स्थिति में, जब आपके एक्सेल में गतिशील सरणियाँ दिखाई देंगी - इस लेख के बाद, आप इसके लिए तैयार होंगे

• सरणी सूत्र क्या हैं और एक्सेल में उनका उपयोग कैसे करें
• OFFSET फ़ंक्शन का उपयोग करके विंडो (रेंज) योग
• एक्सेल में टेबल ट्रांसफर करने के 3 तरीके