स्वयं-आकारासह डायनॅमिक श्रेणी

तुमच्याकडे Excel मध्ये डेटा असलेली टेबल्स आहेत ज्याचा आकार बदलता येऊ शकतो, म्हणजे कामाच्या ओघात पंक्तींची संख्या (स्तंभ) वाढू शकते किंवा कमी होऊ शकते? जर टेबलचा आकार “फ्लोट” असेल तर आपल्याला या क्षणाचे सतत निरीक्षण करावे लागेल आणि ते दुरुस्त करावे लागेल:

• अहवाल सूत्रांमधील दुवे जे आमच्या सारणीचा संदर्भ घेतात
• आमच्या सारणीनुसार तयार केलेल्या मुख्य सारण्यांच्या प्रारंभिक श्रेणी
• आमच्या सारणीनुसार तयार केलेल्या चार्टच्या प्रारंभिक श्रेणी
• आमच्या सारणीचा डेटा स्रोत म्हणून वापर करणार्‍या ड्रॉपडाउनसाठी श्रेणी

एकूण हे सर्व तुम्हाला कंटाळा येऊ देणार नाही 😉

डायनॅमिक "रबर" श्रेणी तयार करणे अधिक सोयीस्कर आणि योग्य असेल, जे डेटाच्या पंक्ती आणि स्तंभांच्या वास्तविक संख्येमध्ये स्वयंचलितपणे आकार समायोजित करेल. याची अंमलबजावणी करण्यासाठी, अनेक मार्ग आहेत.

पद्धत 1. स्मार्ट टेबल

तुमच्या सेलची श्रेणी हायलाइट करा आणि टॅबमधून निवडा मुख्यपृष्ठ - सारणी म्हणून स्वरूपित (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित):

आपल्याला साइड इफेक्ट म्हणून टेबलमध्ये जोडलेल्या स्ट्रीप डिझाइनची आवश्यकता नसल्यास, आपण दिसत असलेल्या टॅबवर ते बंद करू शकता. कन्स्ट्रक्टर (डिझाइन). अशा प्रकारे तयार केलेल्या प्रत्येक सारणीला एक नाव प्राप्त होते जे टॅबवर त्याच ठिकाणी अधिक सोयीस्कर नावाने बदलले जाऊ शकते. कन्स्ट्रक्टर (डिझाइन) शेतात टेबल नाव (सारणीचे नाव).

आता आम्ही आमच्या "स्मार्ट टेबल" साठी डायनॅमिक लिंक वापरू शकतो:

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

असे संदर्भ सूत्रांमध्ये उत्तम काम करतात, उदाहरणार्थ:

= एसयूएम (तक्ता1[मॉस्को]) - "मॉस्को" स्तंभासाठी बेरीजची गणना

or

=VPR(F5;टेबल 1;3;0) – सेल F5 मधून महिन्यासाठी टेबलमध्ये शोधा आणि त्यासाठी सेंट पीटर्सबर्ग बेरीज जारी करा (VLOOKUP म्हणजे काय?)

टॅबवर निवडून मुख्य सारणी तयार करताना अशा लिंक्सचा यशस्वीपणे वापर केला जाऊ शकतो घाला - पिव्होट टेबल (इन्सर्ट - पिव्होट टेबल) आणि डेटा स्रोत म्हणून स्मार्ट टेबलचे नाव प्रविष्ट करणे:

जर तुम्ही अशा सारणीचा तुकडा निवडला (उदाहरणार्थ, पहिले दोन स्तंभ) आणि कोणत्याही प्रकारचा आकृती तयार केला, तर नवीन ओळी जोडताना त्या आपोआप आकृतीत जोडल्या जातील.

ड्रॉप-डाउन सूची तयार करताना, स्मार्ट टेबल घटकांच्या थेट लिंक्स वापरल्या जाऊ शकत नाहीत, परंतु तुम्ही युक्ती वापरून ही मर्यादा सहजपणे पार करू शकता - फंक्शन वापरा अप्रत्यक्ष (अप्रसिद्ध), जे मजकूर दुव्यात बदलते:

त्या. मजकूर स्ट्रिंगच्या स्वरूपात स्मार्ट टेबलची लिंक (अवतरण चिन्हांमध्ये!) पूर्ण वाढलेल्या दुव्यामध्ये बदलते आणि ड्रॉप-डाउन सूची सामान्यतः ते समजते.

पद्धत 2: डायनॅमिक नावाची श्रेणी

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

कार्य: एक डायनॅमिक नावाची श्रेणी बनवा जी शहरांच्या सूचीचा संदर्भ देईल आणि नवीन शहरे जोडताना किंवा हटवताना आपोआप आकार वाढेल आणि लहान होईल.

आम्हाला कोणत्याही आवृत्तीमध्ये उपलब्ध असलेल्या दोन अंगभूत एक्सेल फंक्शन्सची आवश्यकता असेल - POICPOZ (सामना) श्रेणीचा शेवटचा सेल निश्चित करण्यासाठी, आणि INDEX (INDEX) डायनॅमिक लिंक तयार करण्यासाठी.

MATCH वापरून शेवटचा सेल शोधत आहे

MATCH(lookup_value, range, match_type) - एक फंक्शन जे एका श्रेणीमध्ये (पंक्ती किंवा स्तंभ) दिलेल्या मूल्याचा शोध घेते आणि जिथे ते सापडले त्या सेलची क्रमिक संख्या परत करते. उदाहरणार्थ, सूत्र MATCH(“मार्च”;A1:A5;0) परिणामी क्रमांक 4 देईल, कारण “मार्च” हा शब्द A1:A5 स्तंभातील चौथ्या सेलमध्ये आहे. शेवटचे फंक्शन आर्ग्युमेंट Match_Type = 0 म्हणजे आम्ही अचूक जुळणी शोधत आहोत. जर हा युक्तिवाद निर्दिष्ट केला नसेल, तर फंक्शन सर्वात जवळच्या सर्वात लहान मूल्यासाठी शोध मोडवर स्विच करेल - आमच्या अॅरेमधील शेवटचा व्यापलेला सेल शोधण्यासाठी हेच यशस्वीरित्या वापरले जाऊ शकते.

युक्तीचे सार सोपे आहे. MATCH वरपासून खालपर्यंत श्रेणीतील सेलचा शोध घेते आणि सिद्धांतानुसार, दिलेल्या सेलसाठी सर्वात जवळचे सर्वात लहान मूल्य आढळल्यास ते थांबले पाहिजे. आपण इच्छित मूल्य म्हणून टेबलमध्ये उपलब्ध असलेल्या कोणत्याही मूल्यापेक्षा स्पष्टपणे मोठे असलेले मूल्य निर्दिष्ट केल्यास, MATCH टेबलच्या अगदी शेवटी पोहोचेल, काहीही सापडणार नाही आणि शेवटच्या भरलेल्या सेलचा क्रम क्रमांक देईल. आणि आम्हाला त्याची गरज आहे!

आमच्या अॅरेमध्ये फक्त संख्या असल्यास, आम्ही इच्छित मूल्य म्हणून एक संख्या निर्दिष्ट करू शकतो, जी स्पष्टपणे टेबलमधील कोणत्याहीपेक्षा मोठी आहे:

हमी साठी, तुम्ही 9E + 307 (9 गुणिले 10 ते 307 च्या पॉवर, म्हणजे 9 शून्यांसह 307) ही संख्या वापरू शकता - तत्वतः Excel ज्या कमाल संख्येसह कार्य करू शकते.

आमच्या स्तंभात मजकूर मूल्ये असल्यास, सर्वात मोठ्या संभाव्य संख्येच्या समतुल्य म्हणून, तुम्ही बांधकाम REPEAT(“i”, 255) – 255 अक्षरे असलेली मजकूर स्ट्रिंग समाविष्ट करू शकता “i” – चे शेवटचे अक्षर वर्णमाला. Since Excel actually compares character codes when searching, any text in our table will technically be “smaller” than such a long “yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy” line:

INDEX वापरून लिंक तयार करा

आता आपल्याला सारणीतील शेवटच्या नॉन-रिक्त घटकाची स्थिती माहित आहे, ती आपल्या संपूर्ण श्रेणीसाठी एक दुवा तयार करणे बाकी आहे. यासाठी आम्ही फंक्शन वापरतो:

INDEX(श्रेणी; पंक्ती_संख्या; स्तंभ_संख्या)

हे पंक्ती आणि स्तंभ क्रमांकानुसार श्रेणीतील सेलची सामग्री देते, उदाहरणार्थ, आमच्या टेबलमधील शहरे आणि महिन्यांसह मागील पद्धतीतील फंक्शन =INDEX(A1:D5;3;4) 1240 देईल – सामग्री 3री पंक्ती आणि 4थ्या स्तंभातून, म्हणजे सेल D3. जर फक्त एक स्तंभ असेल, तर त्याची संख्या वगळली जाऊ शकते, म्हणजे सूत्र INDEX(A2:A6;3) शेवटच्या स्क्रीनशॉटमध्ये “समारा” देईल.

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

आणि इथेच MATCH फंक्शन येते, जे आम्ही सूचीचा शेवट डायनॅमिकपणे निर्धारित करण्यासाठी INDEX मध्ये समाविष्ट करतो:

=$A$2:INDEX($A$2:$A$100; सामना(प्रतिनिधी("I";255);A2:A100))

नावाची श्रेणी तयार करा

हे सर्व एकाच संपूर्ण मध्ये पॅक करणे बाकी आहे. एक टॅब उघडा सुत्र (सूत्र) आणि क्लिक करा नाव व्यवस्थापक (नाव व्यवस्थापक). उघडलेल्या विंडोमध्ये, बटणावर क्लिक करा तयार करा (नवीन), फील्डमध्ये आमच्या श्रेणीचे नाव आणि सूत्र प्रविष्ट करा श्रेणी (संदर्भ):

त्यावर क्लिक करणे बाकी आहे OK आणि तयार श्रेणी कोणत्याही सूत्र, ड्रॉप-डाउन सूची किंवा चार्टमध्ये वापरली जाऊ शकते.

• टेबल्स आणि लुकअप व्हॅल्यू लिंक करण्यासाठी VLOOKUP फंक्शन वापरणे
• ऑटो-पॉप्युलेटिंग ड्रॉपडाउन सूची कशी तयार करावी
• मोठ्या प्रमाणात डेटाचे विश्लेषण करण्यासाठी मुख्य सारणी कशी तयार करावी