एक्सेलमध्ये डायनॅमिक अॅरे

डायनॅमिक अॅरे काय आहेत

सप्टेंबर 2018 मध्ये, मायक्रोसॉफ्टने एक अपडेट जारी केले जे मायक्रोसॉफ्ट एक्सेलमध्ये पूर्णपणे नवीन साधन जोडते: डायनॅमिक अॅरे आणि त्यांच्यासोबत काम करण्यासाठी 7 नवीन कार्ये. या गोष्टी, अतिशयोक्तीशिवाय, सूत्रे आणि फंक्शन्ससह कार्य करण्याच्या सर्व सामान्य तंत्रात आमूलाग्र बदल करतात आणि अक्षरशः प्रत्येक वापरकर्त्याची चिंता करतात.

सार स्पष्ट करण्यासाठी एक साधे उदाहरण विचारात घ्या.

समजा आपल्याकडे शहर-महिन्यांवरील डेटासह एक साधा तक्ता आहे. जर आपण शीटच्या उजवीकडे कोणताही रिकामा सेल निवडला आणि त्यामध्ये एका सेलशी नाही तर ताबडतोब श्रेणीशी जोडणारा सूत्र प्रविष्ट केला तर काय होईल?

Excel च्या मागील सर्व आवृत्त्यांमध्ये, वर क्लिक केल्यानंतर प्रविष्ट करा आम्हाला फक्त एका पहिल्या सेल B2 ची सामग्री मिळेल. दुसरे कसे?

बरं, किंवा ही श्रेणी काही प्रकारच्या एकत्रित फंक्शनमध्ये गुंडाळणे शक्य होईल जसे की =SUM(B2:C4) आणि त्यासाठी एकूण मिळू शकेल.

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

आता सर्व काही वेगळे आहे.

आता असा फॉर्म्युला एंटर केल्यावर आपण त्यावर क्लिक करू शकतो प्रविष्ट करा - आणि परिणामी आम्ही संदर्भित केलेली सर्व मूल्ये लगेच मिळवा:

ही जादू नाही तर मायक्रोसॉफ्ट एक्सेलमध्ये आता नवीन डायनॅमिक अॅरे आहेत. नवीन जगात आपले स्वागत आहे 🙂

डायनॅमिक अॅरेसह काम करण्याची वैशिष्ट्ये

तांत्रिकदृष्ट्या, आमचा संपूर्ण डायनॅमिक अॅरे पहिल्या सेल G4 मध्ये संग्रहित केला जातो, त्याच्या डेटासह उजवीकडे आणि खाली सेलची आवश्यक संख्या भरून. तुम्ही अ‍ॅरेमधील इतर कोणताही सेल निवडल्यास, फॉर्म्युला बारमधील लिंक निष्क्रिय असेल, हे दर्शविते की आम्ही “बाल” सेलपैकी एकामध्ये आहोत:

एक किंवा अधिक "मूल" सेल हटवण्याचा प्रयत्न केल्याने काहीही होणार नाही - एक्सेल त्वरित त्यांची पुनर्गणना करेल आणि भरेल.

त्याच वेळी, आम्ही इतर सूत्रांमध्ये या "मूल" पेशींचा सुरक्षितपणे संदर्भ घेऊ शकतो:

जर तुम्ही अॅरेचा पहिला सेल कॉपी केला (उदाहरणार्थ, G4 ते F8), तर संपूर्ण अॅरे (त्याचे संदर्भ) नियमित सूत्रांप्रमाणेच त्याच दिशेने फिरतील:

जर आपल्याला अॅरे हलवण्याची गरज असेल, तर ते हलविण्यासाठी पुरेसे असेल (माऊस किंवा च्या संयोजनासह Ctrl+X, Ctrl+V), पुन्हा, फक्त पहिला मुख्य सेल G4 – त्यानंतर, तो एका नवीन ठिकाणी हस्तांतरित केला जाईल आणि आमचा संपूर्ण अॅरे पुन्हा विस्तारित केला जाईल.

तयार केलेल्या डायनॅमिक अ‍ॅरेसाठी तुम्हाला शीटवर इतरत्र संदर्भ देण्याची आवश्यकता असल्यास, तुम्ही त्याच्या अग्रगण्य सेलच्या पत्त्यानंतर विशेष वर्ण # (“पाउंड”) वापरू शकता:

उदाहरणार्थ, आता तुम्ही तयार केलेल्या डायनॅमिक अ‍ॅरेचा संदर्भ देणार्‍या सेलमध्ये सहजपणे ड्रॉपडाउन सूची बनवू शकता:

डायनॅमिक अॅरे एरर

परंतु अॅरेचा विस्तार करण्यासाठी पुरेशी जागा नसल्यास किंवा त्याच्या मार्गावर इतर डेटाने आधीच व्यापलेले सेल असल्यास काय होईल? एक्सेलमध्ये मूलभूतपणे नवीन प्रकारच्या त्रुटी पूर्ण करा - #हस्तांतरण करा! (#स्पिल!):

नेहमीप्रमाणे, जर आपण पिवळा हिरा आणि उद्गारवाचक चिन्ह असलेल्या चिन्हावर क्लिक केले तर आपल्याला समस्येच्या स्त्रोताचे अधिक तपशीलवार स्पष्टीकरण मिळेल आणि आपल्याला हस्तक्षेप करणाऱ्या पेशी त्वरीत सापडतील:

अ‍ॅरे शीटच्या बाहेर गेल्यास किंवा विलीन केलेल्या सेलला मारल्यास तत्सम त्रुटी उद्भवतील. आपण अडथळे दूर केल्यास, नंतर सर्वकाही लगेचच माशीवर दुरुस्त होईल.

डायनॅमिक अॅरे आणि स्मार्ट टेबल्स

डायनॅमिक अॅरे कीबोर्ड शॉर्टकटद्वारे तयार केलेल्या "स्मार्ट" टेबलकडे निर्देश करत असल्यास Ctrl+T किंवा द्वारे मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित), नंतर त्यास त्याची मुख्य गुणवत्ता देखील वारसा मिळेल - स्वयं-आकार.

तळाशी किंवा उजवीकडे नवीन डेटा जोडताना, स्मार्ट टेबल आणि डायनॅमिक श्रेणी देखील आपोआप ताणली जाईल:

तथापि, एक मर्यादा आहे: आम्ही स्मार्ट टेबलमधील फॉर्म्युलामध्ये डायनॅमिक श्रेणी संदर्भ वापरू शकत नाही:

डायनॅमिक अॅरे आणि इतर एक्सेल वैशिष्ट्ये

ठीक आहे, तुम्ही म्हणाल. हे सर्व मनोरंजक आणि मजेदार आहे. मूळ श्रेणीच्या पहिल्या सेलच्या संदर्भात, खाली आणि उजवीकडे आणि त्या सर्व गोष्टींच्या संदर्भासह सूत्र मॅन्युअली स्ट्रेच करण्याची, पूर्वीप्रमाणेच गरज नाही. आणि एवढेच?

जोरदार नाही.

डायनॅमिक अॅरे हे एक्सेलमधील दुसरे साधन नाही. आता ते मायक्रोसॉफ्ट एक्सेलच्या अगदी हृदयात (किंवा मेंदूमध्ये) एम्बेड केलेले आहेत - त्याचे गणना इंजिन. याचा अर्थ असा की आम्हाला परिचित असलेले इतर एक्सेल सूत्र आणि कार्ये आता डायनॅमिक अॅरेसह कार्य करण्यास समर्थन देतात. घडलेल्या बदलांच्या खोलीची कल्पना देण्यासाठी काही उदाहरणे पाहू या.

ट्रान्सपोज

श्रेणी बदलण्यासाठी (स्वॅप पंक्ती आणि स्तंभ) मायक्रोसॉफ्ट एक्सेलमध्ये नेहमीच अंगभूत कार्य असते ट्रान्सप (हस्तांतरण). तथापि, ते वापरण्यासाठी, आपण प्रथम परिणामांसाठी श्रेणी योग्यरित्या निवडणे आवश्यक आहे (उदाहरणार्थ, जर इनपुट 5 × 3 ची श्रेणी असेल, तर तुम्ही 3 × 5 निवडले असेल), नंतर फंक्शन प्रविष्ट करा आणि दाबा. संयोजन Ctrl+शिफ्ट+प्रविष्ट करा, कारण ते केवळ अॅरे फॉर्म्युला मोडमध्ये कार्य करू शकते.

आता तुम्ही फक्त एक सेल निवडू शकता, त्यात समान सूत्र प्रविष्ट करा आणि सामान्य वर क्लिक करा प्रविष्ट करा - डायनॅमिक अॅरे सर्वकाही स्वतःच करेल:

गुणाकार सारणी

जेव्हा मला एक्सेलमधील अॅरे फॉर्म्युलाचे फायदे व्हिज्युअलायझ करण्यास सांगितले गेले तेव्हा मी दिलेले हे उदाहरण आहे. आता, संपूर्ण पायथागोरियन टेबलची गणना करण्यासाठी, पहिल्या सेल B2 मध्ये उभे राहणे पुरेसे आहे, तेथे दोन अॅरे (संख्या 1..10 च्या अनुलंब आणि क्षैतिज संच) गुणाकार करणारे सूत्र प्रविष्ट करा आणि त्यावर क्लिक करा. प्रविष्ट करा:

Gluing आणि केस रूपांतरण

अॅरे केवळ गुणाकार केले जाऊ शकत नाहीत, परंतु मानक ऑपरेटर आणि (अँपरसँड) सह एकत्र चिकटवले जाऊ शकतात. समजा आपल्याला दोन स्तंभांमधून नाव आणि आडनाव काढायचे आहे आणि मूळ डेटामध्ये जंपिंग केस दुरुस्त करणे आवश्यक आहे. आम्ही हे एका लहान सूत्राने करतो जे संपूर्ण अॅरे बनवते आणि नंतर आम्ही त्यावर फंक्शन लागू करतो PROPNACH (योग्य)रजिस्टर व्यवस्थित करण्यासाठी:

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

समजा आमच्याकडे संख्यांचा एक समूह आहे ज्यातून आम्हाला शीर्ष तीन निकाल मिळवायचे आहेत, त्यांना उतरत्या क्रमाने व्यवस्थित करा. आता हे एका सूत्राने केले जाते आणि पुन्हा, कोणत्याही न करता Ctrl+शिफ्ट+प्रविष्ट करा पूर्वीप्रमाणे:

जर तुम्हाला परिणाम स्तंभात न ठेवता एका ओळीत ठेवायचे असतील, तर या सूत्रातील कोलन (रेषा विभाजक) अर्धविरामाने (एका ओळीत घटक विभाजक) पुनर्स्थित करणे पुरेसे आहे. Excel च्या इंग्रजी आवृत्तीमध्ये, हे विभाजक अनुक्रमे अर्धविराम आणि स्वल्पविराम आहेत.

VLOOKUP एकाच वेळी अनेक स्तंभ काढत आहे

कार्य व्हीपीआर (VLOOKUP) आता तुम्ही व्हॅल्यू एकमधून नाही तर एकाच वेळी अनेक कॉलममधून काढू शकता - फंक्शनच्या तिसर्‍या वितर्कमध्ये अॅरे म्हणून त्यांची संख्या (कोणत्याही इच्छित क्रमाने) निर्दिष्ट करा:

OFFSET फंक्शन डायनॅमिक अॅरे मिळवते

डेटा विश्लेषणासाठी सर्वात मनोरंजक आणि उपयुक्त (VLOOKUP नंतर) फंक्शन म्हणजे फंक्शन विल्हेवाट लावणे (ऑफसेट), ज्यासाठी मी माझ्या पुस्तकातील एक संपूर्ण प्रकरण आणि एक लेख येथे समर्पित केला आहे. हे फंक्शन समजून घेण्यात आणि त्यावर प्रभुत्व मिळवण्यात नेहमीच अडचण आली आहे की यामुळे डेटाचा अॅरे (श्रेणी) परत आला, परंतु आम्ही ते पाहू शकलो नाही, कारण Excel ला बॉक्सच्या बाहेर अॅरेसह कसे कार्य करावे हे अद्याप माहित नव्हते.

आता ही समस्या भूतकाळात आहे. आता पहा, एकच सूत्र आणि OFFSET द्वारे परत आलेला डायनॅमिक अॅरे वापरून, तुम्ही दिलेल्या उत्पादनासाठी कोणत्याही क्रमवारी केलेल्या सारणीतून सर्व पंक्ती काढू शकता:

चला तिच्या युक्तिवादांवर एक नजर टाकूया:

• A1 - प्रारंभिक सेल (संदर्भ बिंदू)
• ПОИСКПОЗ(F2;A2:A30;0) - सुरुवातीच्या सेलपासून खाली - पहिल्या सापडलेल्या कोबीकडे शिफ्टची गणना.
• 0 - सुरुवातीच्या सेलच्या सापेक्ष "विंडो" उजवीकडे शिफ्ट करा
  
• СЧЁТЕСЛИ(A2:A30;F2) - परत केलेल्या "विंडो" च्या उंचीची गणना - कोबी असलेल्या ओळींची संख्या.
• 4 — “विंडो” चा आकार क्षैतिजरित्या, म्हणजे आउटपुट 4 स्तंभ

डायनॅमिक अॅरेसाठी नवीन कार्ये

जुन्या फंक्शन्समध्ये डायनॅमिक अॅरे मेकॅनिझमला सपोर्ट करण्याव्यतिरिक्त, मायक्रोसॉफ्ट एक्सेलमध्ये अनेक पूर्णपणे नवीन फंक्शन्स जोडली गेली आहेत, विशेषत: डायनॅमिक अॅरेसह काम करण्यासाठी तीक्ष्ण केली गेली आहेत. विशेषतः, हे आहेत:

• गट (क्रमवारी) - इनपुट श्रेणीची क्रमवारी लावते आणि आउटपुटवर डायनॅमिक अॅरे तयार करते
  
• SORTPO (SORTBY) - एक श्रेणी दुसर्‍यामधील मूल्यांनुसार क्रमवारी लावू शकते
  
• FILTER (फिल्टर) - निर्दिष्ट अटी पूर्ण करणार्‍या स्त्रोत श्रेणीमधून पंक्ती पुनर्प्राप्त करते
• UNIK (अद्वितीय) - श्रेणीतून अद्वितीय मूल्ये काढते किंवा डुप्लिकेट काढून टाकते
  
• SLMASSIVE (रांडरे) - दिलेल्या आकाराच्या यादृच्छिक संख्यांचे अॅरे व्युत्पन्न करते
• जन्मानंतर (क्रम) — दिलेल्या पायरीसह संख्यांच्या क्रमातून एक अॅरे तयार करतो
  

त्यांच्याबद्दल अधिक - थोड्या वेळाने. वैचारिक अभ्यासासाठी त्यांचा स्वतंत्र लेख (आणि एक नाही) वाचतो आहे 🙂

निष्कर्ष

जर तुम्ही वर लिहिलेले सर्व काही वाचले असेल, तर मला वाटते की तुम्हाला झालेल्या बदलांचे प्रमाण आधीच लक्षात आले आहे. त्यामुळे आता एक्सेलमध्ये अनेक गोष्टी सोप्या, सोप्या आणि तार्किक पद्धतीने करता येतील. आता इथे, या साइटवर आणि माझ्या पुस्तकांमध्ये किती लेख दुरुस्त करावे लागतील याचा मला थोडासा धक्का बसला आहे हे मान्य केले पाहिजे, परंतु मी हे करण्यास हलक्या मनाने तयार आहे.

निकालांचा सारांश, प्लसस डायनॅमिक अॅरे, तुम्ही खालील लिहू शकता:

• आपण संयोजन बद्दल विसरू शकता Ctrl+शिफ्ट+प्रविष्ट करा. एक्सेल आता "नियमित सूत्र" आणि "अॅरे फॉर्म्युला" मध्ये फरक पाहत नाही आणि त्यांना त्याच प्रकारे वागवते.
• फंक्शन बद्दल संक्षेप (SUMPRODUCT), जे पूर्वी शिवाय अॅरे सूत्र प्रविष्ट करण्यासाठी वापरले जात होते Ctrl+शिफ्ट+प्रविष्ट करा तुम्ही देखील विसरू शकता - आता ते पुरेसे सोपे आहे सारांश и प्रविष्ट करा.
• स्मार्ट टेबल्स आणि परिचित फंक्शन्स (SUM, IF, VLOOKUP, SUMIFS, इ.) आता डायनॅमिक अॅरेना पूर्ण किंवा अंशतः समर्थन देतात.
• बॅकवर्ड कंपॅटिबिलिटी आहे: जर तुम्ही एक्सेलच्या जुन्या आवृत्तीमध्ये डायनॅमिक अॅरे असलेले वर्कबुक उघडले, तर ते अॅरे फॉर्म्युलामध्ये (कुरळे ब्रेसेसमध्ये) बदलतील आणि "जुन्या शैली" मध्ये कार्य करणे सुरू ठेवतील.

काही नंबर सापडला उणे:

• तुम्ही डायनॅमिक अ‍ॅरेमधून वैयक्तिक पंक्ती, स्तंभ किंवा सेल हटवू शकत नाही, म्हणजे ती एकच अस्तित्व म्हणून राहते.
• आपण नेहमीच्या मार्गाने डायनॅमिक अॅरे क्रमवारी लावू शकत नाही डेटा - वर्गीकरण (डेटा - क्रमवारी). यासाठी आता एक विशेष कार्य आहे. गट (क्रमवारी).
• डायनॅमिक रेंजचे स्मार्ट टेबलमध्ये रूपांतर करता येत नाही (परंतु तुम्ही स्मार्ट टेबलवर आधारित डायनॅमिक रेंज बनवू शकता).
  

अर्थात, हा शेवट नाही आणि मला खात्री आहे की मायक्रोसॉफ्ट भविष्यात ही यंत्रणा सुधारत राहील.

मी कुठे डाउनलोड करू शकतो?

आणि शेवटी, मुख्य प्रश्न 🙂

मायक्रोसॉफ्टने प्रथम सप्टेंबर 2018 मध्ये एका परिषदेत एक्सेलमधील डायनॅमिक अॅरेचे पूर्वावलोकन जाहीर केले आणि दाखवले. आग्रही. पुढील काही महिन्यांत, नवीन वैशिष्ट्यांची सखोल चाचणी आणि रनिंग-इन होते, प्रथम मांजरी स्वतः मायक्रोसॉफ्टचे कर्मचारी आणि नंतर ऑफिस इनसाइडर्सच्या वर्तुळातील स्वयंसेवक परीक्षकांवर. या वर्षी, डायनॅमिक अॅरे जोडणारे अपडेट हळूहळू ऑफिस 365 च्या नियमित सदस्यांसाठी आणले जाऊ लागले. उदाहरणार्थ, मला ते फक्त ऑगस्टमध्ये माझ्या Office 365 Pro Plus (मासिक लक्ष्यित) सबस्क्रिप्शनसह मिळाले.

तुमच्या एक्सेलमध्ये अद्याप डायनॅमिक अॅरे नसल्यास, परंतु तुम्हाला त्यांच्यासोबत काम करायचे असेल, तर खालील पर्याय आहेत:

• तुमच्याकडे Office 365 सबस्क्रिप्शन असल्यास, हे अपडेट तुमच्यापर्यंत पोहोचेपर्यंत तुम्ही फक्त प्रतीक्षा करू शकता. हे किती लवकर घडते ते तुमच्या ऑफिसला किती वेळा अपडेट्स वितरीत केले जातात यावर अवलंबून असते (वर्षातून एकदा, सहा महिन्यांत एकदा, महिन्यातून एकदा). तुमच्याकडे कॉर्पोरेट पीसी असल्यास, तुम्ही तुमच्या अ‍ॅडमिनिस्ट्रेटरला अपडेट्स अधिक वेळा डाउनलोड करण्यासाठी सेट करण्यास सांगू शकता.
  
• तुम्ही त्या ऑफिस इनसाइडर्स चाचणी स्वयंसेवकांच्या रँकमध्ये सामील होऊ शकता - त्यानंतर तुम्ही सर्व नवीन वैशिष्ट्ये आणि कार्ये प्राप्त करणारे पहिले असाल (परंतु एक्सेलमध्ये नक्कीच बग्गी वाढण्याची शक्यता आहे).
• तुमच्याकडे सबस्क्रिप्शन नसेल, परंतु Excel ची एक बॉक्स्ड स्टँडअलोन आवृत्ती असेल, तर तुम्हाला किमान 2022 मध्ये Office आणि Excel च्या पुढील आवृत्तीचे प्रकाशन होईपर्यंत प्रतीक्षा करावी लागेल. अशा आवृत्त्यांचे वापरकर्ते फक्त सुरक्षा अद्यतने आणि दोष निराकरणे प्राप्त करतात आणि सर्व नवीन “गुडीज” आता फक्त ऑफिस 365 सदस्यांकडे जातात. दुःखद पण खरे 🙂
  

कोणत्याही परिस्थितीत, जेव्हा तुमच्या एक्सेलमध्ये डायनॅमिक अॅरे दिसतात - या लेखानंतर, तुम्ही त्यासाठी तयार असाल 🙂

• अॅरे फॉर्म्युले काय आहेत आणि ते Excel मध्ये कसे वापरायचे
• OFFSET फंक्शन वापरून विंडो (श्रेणी) समीकरण
• एक्सेलमध्ये टेबल ट्रान्सपोज करण्याचे 3 मार्ग