सामग्री
सर्व क्लासिक शोध आणि प्रकार प्रतिस्थापन कार्ये व्हीपीआर (VLOOKUP), जीपीआर (HLOOKUP), अधिक उघड (सामना) आणि त्यांच्या सारख्यांचे एक महत्त्वाचे वैशिष्ट्य आहे - ते सुरुवातीपासून शेवटपर्यंत शोधतात, म्हणजे स्त्रोत डेटामध्ये डावीकडून उजवीकडे किंवा वरपासून खालपर्यंत. प्रथम जुळणारी जुळणी सापडताच, शोध थांबतो आणि आपल्याला आवश्यक असलेल्या घटकाची फक्त पहिली घटना आढळते.
आपल्याला पहिली नाही तर शेवटची घटना शोधण्याची आवश्यकता असल्यास काय करावे? उदाहरणार्थ, क्लायंटसाठी शेवटचा व्यवहार, शेवटचे पेमेंट, सर्वात अलीकडील ऑर्डर इ.
पद्धत 1: अॅरे फॉर्म्युलासह शेवटची पंक्ती शोधणे
जर मूळ सारणीमध्ये तारखेचा स्तंभ किंवा पंक्तीचा अनुक्रमांक नसेल (ऑर्डर, पेमेंट ...), तर आमचे कार्य खरेतर, दिलेल्या अटी पूर्ण करणारी शेवटची पंक्ती शोधणे आहे. हे खालील अॅरे सूत्राने केले जाऊ शकते:
येथे:
- कार्य IF (तर) एका स्तंभातील सर्व सेल एक एक करून तपासते ग्राहक आणि जर आपल्याला आवश्यक असलेले नाव असेल तर ओळ क्रमांक प्रदर्शित करते. शीटवरील ओळ क्रमांक फंक्शनद्वारे आम्हाला दिला जातो लाइन (ROW), परंतु आपल्याला सारणीतील पंक्ती क्रमांकाची आवश्यकता असल्याने, आपल्याला 1 देखील वजा करावा लागेल, कारण आपल्याकडे टेबलमध्ये शीर्षलेख आहे.
- मग फंक्शन कमाल (अधिकतम) पंक्ती क्रमांकांच्या तयार केलेल्या संचामधून कमाल मूल्य निवडते, म्हणजे क्लायंटच्या सर्वात अलीकडील ओळीची संख्या.
- कार्य INDEX (INDEX) कोणत्याही इतर आवश्यक टेबल कॉलममधून सापडलेल्या शेवटच्या संख्येसह सेलची सामग्री परत करते (ऑर्डर कोड).
हे सर्व म्हणून प्रविष्ट करणे आवश्यक आहे अॅरे सूत्र, म्हणजे:
- Office 365 मध्ये नवीनतम अद्यतने स्थापित आणि डायनॅमिक अॅरेसाठी समर्थनासह, आपण फक्त दाबू शकता प्रविष्ट करा.
- इतर सर्व आवृत्त्यांमध्ये, सूत्र प्रविष्ट केल्यानंतर, आपल्याला कीबोर्ड शॉर्टकट दाबावा लागेल Ctrl+शिफ्ट+प्रविष्ट करा, जे फॉर्म्युला बारमध्ये आपोआप कुरळे ब्रेसेस जोडेल.
पद्धत 2: नवीन लुकअप फंक्शनसह रिव्हर्स लुकअप
मी आधीच एका नवीन वैशिष्ट्याबद्दल व्हिडिओसह एक लांब लेख लिहिला आहे पहा (XLOOKUP), जे जुने VLOOKUP पुनर्स्थित करण्यासाठी Office च्या नवीनतम आवृत्त्यांमध्ये दिसले (VLOOKUP). BROWSE च्या मदतीने, आमचे कार्य अगदी प्राथमिकरित्या सोडवले जाते, कारण. या फंक्शनसाठी (VLOOKUP विपरीत), तुम्ही शोध दिशा स्पष्टपणे सेट करू शकता: टॉप-डाउन किंवा बॉटम-अप – त्याचा शेवटचा युक्तिवाद (-1) यासाठी जबाबदार आहे:
पद्धत 3. नवीनतम तारखेसह स्ट्रिंग शोधा
जर स्त्रोत डेटामध्ये आमच्याकडे अनुक्रमांक किंवा समान भूमिका बजावणारी तारीख असलेला स्तंभ असेल, तर कार्य सुधारित केले जाईल - आम्हाला जुळणीसह शेवटची (सर्वात कमी) ओळ शोधण्याची आवश्यकता नाही, परंतु नवीनतम ( कमाल) तारीख.
क्लासिक फंक्शन्स वापरून हे कसे करायचे याबद्दल मी आधीच तपशीलवार चर्चा केली आहे आणि आता नवीन डायनॅमिक अॅरे फंक्शन्सची शक्ती वापरण्याचा प्रयत्न करूया. अधिक सौंदर्य आणि सोयीसाठी, आम्ही कीबोर्ड शॉर्टकट वापरून मूळ टेबलचे रूपांतर “स्मार्ट” टेबलमध्ये करतो. Ctrl+T किंवा आज्ञा मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित).
त्यांच्या मदतीने, हे "किलर जोडपे" आमच्या समस्येचे अतिशय कृपापूर्वक निराकरण करते:
येथे:
- प्रथम कार्य FILTER (फिल्टर) कॉलममध्ये असलेल्या आमच्या टेबलमधून फक्त त्या पंक्ती निवडतो ग्राहक - आम्हाला आवश्यक असलेले नाव.
- मग फंक्शन गट (क्रमवारी) सर्वात अलीकडील डीलसह, उतरत्या क्रमाने निवडलेल्या पंक्तींची तारखेनुसार क्रमवारी लावते.
- कार्य INDEX (INDEX) पहिली पंक्ती काढतो, म्हणजे आपल्याला आवश्यक असलेला शेवटचा ट्रेड परत करतो.
- आणि, शेवटी, बाह्य FILTER फंक्शन परिणामांमधून अतिरिक्त 1ले आणि 3रे स्तंभ काढून टाकते (ऑर्डर कोड и ग्राहक) आणि फक्त तारीख आणि रक्कम सोडते. यासाठी, स्थिरांकांचा अॅरे वापरला जातो. {0;1;0;1}, आम्हाला कोणते स्तंभ हवे आहेत (1) किंवा (0) प्रदर्शित करायचे नाहीत हे परिभाषित करणे.
पद्धत 4: पॉवर क्वेरीमध्ये शेवटची जुळणी शोधणे
बरं, पूर्णतेसाठी, पॉवर क्वेरी अॅड-इन वापरून आमच्या रिव्हर्स शोध समस्येवर उपाय पाहू. तिच्या मदतीने, सर्वकाही अतिशय जलद आणि सुंदरपणे सोडवले जाते.
1. कीबोर्ड शॉर्टकट वापरून आपल्या मूळ सारणीला “स्मार्ट” मध्ये रूपांतरित करू Ctrl+T किंवा आज्ञा मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित).
2. बटणासह पॉवर क्वेरीमध्ये लोड करा टेबल/श्रेणीतून टॅब डेटा (डेटा — सारणी/श्रेणीवरून).
3. आम्ही आमचे टेबल (शीर्षलेखातील फिल्टरच्या ड्रॉप-डाउन सूचीद्वारे) तारखेच्या उतरत्या क्रमाने क्रमवारी लावतो, जेणेकरून सर्वात अलीकडील व्यवहार शीर्षस्थानी असतील.
4… टॅबमध्ये परिवर्तन एक संघ निवडा गट (परिवर्तन — गटानुसार) आणि ग्राहकांनुसार गटबद्धता सेट करा आणि एकत्रित कार्य म्हणून, पर्याय निवडा सर्व ओळी (सर्व पंक्ती). तुम्ही तुमच्या आवडीच्या नवीन स्तंभाला नाव देऊ शकता – उदाहरणार्थ माहिती.
गटबद्ध केल्यानंतर, आम्हाला आमच्या ग्राहकांच्या अनन्य नावांची यादी आणि कॉलममध्ये मिळेल माहिती - त्या प्रत्येकाच्या सर्व व्यवहारांसह सारण्या, जिथे पहिली ओळ नवीनतम व्यवहार असेल, ज्याची आम्हाला आवश्यकता आहे:
5. बटणासह एक नवीन गणना केलेला स्तंभ जोडा सानुकूल स्तंभ टॅब स्तंभ जोडा (स्तंभ जोडा — सानुकूल स्तंभ जोडा)आणि खालील सूत्र प्रविष्ट करा:
येथे माहिती - हा तो स्तंभ आहे ज्यामधून आम्ही ग्राहकांद्वारे टेबल घेतो, आणि २,३,४ {} आपण काढू इच्छित असलेल्या पंक्तीची संख्या आहे (पॉवर क्वेरीमधील पंक्ती क्रमांक शून्य पासून सुरू होते). आम्हाला रेकॉर्डसह एक स्तंभ मिळतो (विक्रम), जिथे प्रत्येक एंट्री प्रत्येक सारणीतील पहिली पंक्ती आहे:
स्तंभ शीर्षलेखातील दुहेरी बाणांसह बटणासह सर्व रेकॉर्डची सामग्री विस्तृत करणे बाकी आहे शेवटचा सौदा इच्छित स्तंभ निवडणे:
… आणि नंतर आवश्यक नसलेला स्तंभ हटवा माहिती त्याच्या शीर्षकावर उजवे-क्लिक करून - स्तंभ काढा (स्तंभ काढा).
द्वारे पत्रकावर निकाल अपलोड केल्यानंतर होम — बंद करा आणि लोड करा — बंद करा आणि लोड करा (घर — बंद करा आणि लोड करा — बंद करा आणि लोड करा...) आम्हाला हवे होते तसे अलीकडील व्यवहारांच्या सूचीसह एक छान टेबल मिळेल:
जेव्हा तुम्ही स्रोत डेटा बदलता, तेव्हा तुम्ही त्यावर उजवे-क्लिक करून परिणाम अद्यतनित करण्यास विसरू नये - आदेश अपडेट आणि सेव्ह करा (रिफ्रेश) किंवा कीबोर्ड शॉर्टकट Ctrl+alt+F5.
- LOOKUP फंक्शन VLOOKUP चे वंशज आहे
- SORT, FILTER आणि UNIC ही नवीन डायनॅमिक अॅरे फंक्शन्स कशी वापरायची
- LOOKUP फंक्शनसह एका ओळीत किंवा स्तंभातील शेवटचा रिक्त नसलेला सेल शोधणे