विषय-सूची
सभी क्लासिक खोज और प्रकार प्रतिस्थापन कार्य VPR (VLOOKUP), जीपीआर (HLOOKUP), अधिक उजागर (मिलान) और उनके जैसे लोगों के पास एक महत्वपूर्ण विशेषता है - वे शुरुआत से अंत तक खोज करते हैं, यानी बाएं से दाएं या ऊपर से नीचे स्रोत डेटा में। जैसे ही पहला मैचिंग मैच मिलता है, सर्च बंद हो जाता है और हमें जिस एलीमेंट की जरूरत होती है, उसकी केवल पहली घटना ही मिल पाती है।
अगर हमें पहली नहीं, बल्कि आखिरी घटना को खोजने की जरूरत है तो क्या करें? उदाहरण के लिए, ग्राहक के लिए अंतिम लेनदेन, अंतिम भुगतान, नवीनतम आदेश, आदि?
विधि 1: एक सरणी सूत्र के साथ अंतिम पंक्ति ढूँढना
यदि मूल तालिका में दिनांक या पंक्ति की क्रम संख्या (आदेश, भुगतान ...) के साथ एक कॉलम नहीं है, तो हमारा कार्य, वास्तव में, अंतिम पंक्ति को खोजने के लिए है जो दी गई शर्त को पूरा करता है। यह निम्नलिखित सरणी सूत्र के साथ किया जा सकता है:
यहाँ:
- समारोह IF (अगर) एक कॉलम में सभी कोशिकाओं को एक-एक करके जांचता है ग्राहक और लाइन नंबर प्रदर्शित करता है यदि इसमें वह नाम है जिसकी हमें आवश्यकता है। शीट पर लाइन नंबर हमें फंक्शन द्वारा दिया जाता है लाइन (पंक्ति), लेकिन चूंकि हमें तालिका में पंक्ति संख्या की आवश्यकता है, इसलिए हमें अतिरिक्त रूप से 1 घटाना होगा, क्योंकि हमारे पास तालिका में एक शीर्षलेख है।
- फिर समारोह मैक्स (मैक्स) पंक्ति संख्याओं के गठित सेट से अधिकतम मान का चयन करता है, अर्थात क्लाइंट की सबसे हाल की पंक्ति की संख्या।
- समारोह सूचकांक (अनुक्रमणिका) किसी अन्य आवश्यक तालिका कॉलम से मिली अंतिम संख्या के साथ सेल की सामग्री लौटाता है (आदेश कोड).
यह सब इस प्रकार दर्ज किया जाना चाहिए सरणी सूत्र, अर्थात:
- Office 365 में नवीनतम अद्यतन स्थापित और गतिशील सरणियों के लिए समर्थन के साथ, आप बस दबा सकते हैं दर्ज.
- अन्य सभी संस्करणों में, सूत्र दर्ज करने के बाद, आपको कीबोर्ड शॉर्टकट को दबाना होगा कंट्रोल+पाली+दर्ज, जो स्वचालित रूप से सूत्र पट्टी में इसमें घुंघराले ब्रेसिज़ जोड़ देगा।
विधि 2: नए लुकअप फ़ंक्शन के साथ रिवर्स लुकअप
मैंने पहले ही एक नई सुविधा के बारे में एक वीडियो के साथ एक लंबा लेख लिखा है देखें (एक्सलुकअप), जो पुराने VLOOKUP . को बदलने के लिए Office के नवीनतम संस्करणों में दिखाई दिया (VLOOKUP). BROWSE की मदद से हमारा काम काफी हद तक हल हो जाता है, क्योंकि। इस फ़ंक्शन के लिए (VLOOKUP के विपरीत), आप स्पष्ट रूप से खोज दिशा निर्धारित कर सकते हैं: ऊपर-नीचे या नीचे-ऊपर - इसका अंतिम तर्क (-1) इसके लिए जिम्मेदार है:
विधि 3. नवीनतम तिथि के साथ एक स्ट्रिंग खोजें
यदि स्रोत डेटा में हमारे पास एक सीरियल नंबर या एक समान भूमिका निभाने वाली तारीख वाला एक कॉलम है, तो कार्य को संशोधित किया जाता है - हमें एक मैच के साथ अंतिम (निम्नतम) लाइन नहीं ढूंढनी होगी, लेकिन नवीनतम के साथ लाइन ( अधिकतम) तिथि।
मैंने पहले ही विस्तार से चर्चा की है कि क्लासिक फ़ंक्शंस का उपयोग करके इसे कैसे किया जाए, और अब आइए नए गतिशील सरणी फ़ंक्शंस की शक्ति का उपयोग करने का प्रयास करें। अधिक सुंदरता और सुविधा के लिए, हम कीबोर्ड शॉर्टकट का उपयोग करके मूल तालिका को "स्मार्ट" तालिका में भी परिवर्तित करते हैं कंट्रोल+T या आदेश होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें).
उनकी मदद से, यह "हत्यारा जोड़ा" हमारी समस्या को बहुत ही शानदार तरीके से हल करता है:
यहाँ:
- पहले समारोह फ़िल्टर (फ़िल्टर) हमारी तालिका से केवल उन पंक्तियों का चयन करता है जहां कॉलम ग्राहक - वह नाम जो हमें चाहिए।
- फिर समारोह ग्रेड (क्रम से लगाना) चयनित पंक्तियों को दिनांक के अनुसार अवरोही क्रम में क्रमबद्ध करता है, जिसमें सबसे हाल का सौदा शीर्ष पर होता है।
- समारोह सूचकांक (अनुक्रमणिका) पहली पंक्ति निकालता है, यानी हमें आवश्यक अंतिम व्यापार देता है।
- और, अंत में, बाहरी फ़िल्टर फ़ंक्शन परिणामों से अतिरिक्त पहले और तीसरे कॉलम को हटा देता है (आदेश कोड и ग्राहक) और केवल तारीख और राशि छोड़ता है। इसके लिए, स्थिरांक की एक सरणी का उपयोग किया जाता है। {0;1;0;1}, यह परिभाषित करना कि हम कौन से कॉलम चाहते हैं (1) या नहीं (0) प्रदर्शित करना चाहते हैं।
विधि 4: पावर क्वेरी में अंतिम मिलान ढूँढना
खैर, पूर्णता के लिए, आइए Power Query ऐड-इन का उपयोग करके अपनी रिवर्स सर्च समस्या का समाधान देखें। उसकी मदद से, सब कुछ बहुत जल्दी और खूबसूरती से हल हो जाता है।
1. आइए कीबोर्ड शॉर्टकट का उपयोग करके अपनी मूल तालिका को "स्मार्ट" तालिका में बदलें कंट्रोल+T या आदेश होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें).
2. इसे बटन के साथ पावर क्वेरी में लोड करें टेबल/रेंज से टैब जानकारी (डेटा - टेबल/रेंज से).
3. हम अपनी तालिका को दिनांक के अवरोही क्रम में क्रमबद्ध करते हैं (शीर्षलेख में फ़िल्टर की ड्रॉप-डाउन सूची के माध्यम से), ताकि सबसे हाल के लेनदेन शीर्ष पर हों।
4... टैब में परिवर्तन एक टीम चुनें समूह द्वारा (रूपांतरण - समूह द्वारा) और ग्राहकों द्वारा ग्रुपिंग सेट करें, और एक एग्रीगेटिंग फ़ंक्शन के रूप में, विकल्प चुनें सभी पंक्तियाँ (सभी पंक्तियाँ). आप नए कॉलम को अपनी पसंद का नाम दे सकते हैं - उदाहरण के लिए विवरण.
समूहीकरण के बाद, हमें अपने ग्राहकों के अद्वितीय नामों की सूची और कॉलम में मिलेगी विवरण - उनमें से प्रत्येक के सभी लेनदेन के साथ टेबल, जहां पहली पंक्ति नवीनतम लेनदेन होगी, जिसकी हमें आवश्यकता है:
5. बटन के साथ एक नया परिकलित कॉलम जोड़ें कस्टम कॉलम टैब कॉलम जोड़ें (कॉलम जोड़ें - कस्टम कॉलम जोड़ें)और निम्न सूत्र दर्ज करें:
यहाँ विवरण - यह वह कॉलम है जिससे हम ग्राहकों द्वारा टेबल लेते हैं, और 0 {} उस पंक्ति की संख्या है जिसे हम निकालना चाहते हैं (पावर क्वेरी में पंक्ति क्रमांकन शून्य से शुरू होता है)। हमें रिकॉर्ड के साथ एक कॉलम मिलता है (अभिलेख), जहां प्रत्येक प्रविष्टि प्रत्येक तालिका से पहली पंक्ति है:
यह कॉलम हेडर में डबल एरो वाले बटन के साथ सभी रिकॉर्ड्स की सामग्री का विस्तार करने के लिए बनी हुई है अंतिम सौदा वांछित कॉलम का चयन करना:
... और फिर उस कॉलम को हटा दें जिसकी अब आवश्यकता नहीं है विवरण इसके शीर्षक पर राइट क्लिक करके - कॉलम हटाएं (कॉलम हटाएं).
के माध्यम से परिणाम को शीट पर अपलोड करने के बाद होम — बंद करें और लोड करें — बंद करें और लोड करें (होम - बंद करें और लोड करें - बंद करें और लोड करें ...) हमें हाल के लेनदेन की सूची के साथ इतनी अच्छी तालिका मिलेगी, जैसा हम चाहते थे:
जब आप स्रोत डेटा बदलते हैं, तो आपको उन पर राइट-क्लिक करके परिणामों को अपडेट करना नहीं भूलना चाहिए - कमांड अपडेट करें और सहेजें (ताज़ा करें) या कीबोर्ड शॉर्टकट कंट्रोल+ऑल्ट+F5.
- लुकअप फ़ंक्शन VLOOKUP . का वंशज है
- नए गतिशील सरणी कार्यों का उपयोग कैसे करें SORT, FILTER, और UNIC
- LOOKUP फ़ंक्शन के साथ पंक्ति या कॉलम में अंतिम गैर-रिक्त सेल ढूँढना