सामग्रीहरू
सबै क्लासिक खोज र प्रकार प्रतिस्थापन कार्यहरू VPR (VLOOKUP), GPR (HLOOKUP), थप खुलासा (MATCH) र तिनीहरू जस्तै तिनीहरूसँग एउटा महत्त्वपूर्ण विशेषता छ - तिनीहरू सुरुदेखि अन्त्यसम्म खोज्छन्, अर्थात् स्रोत डेटामा बाँया-देखि-दायाँ वा माथि-देखि-तल। पहिलो मिल्दो मिल्दोजुल्दो फेला पार्ने बित्तिकै, खोज रोकिन्छ र हामीलाई चाहिने तत्वको पहिलो घटना मात्र फेला पर्दछ।
यदि हामीले पहिलो होइन, तर अन्तिम घटना फेला पार्न आवश्यक छ भने के गर्ने? उदाहरणका लागि, ग्राहकको लागि अन्तिम लेनदेन, अन्तिम भुक्तानी, सबैभन्दा भर्खरको अर्डर, आदि?
विधि १: एरे सूत्रको साथ अन्तिम पङ्क्ति फेला पार्दै
यदि मूल तालिकामा मिति वा पङ्क्तिको क्रम संख्या भएको स्तम्भ छैन (अर्डर, भुक्तानी ...), तब हाम्रो कार्य वास्तवमा, दिइएको अवस्थालाई सन्तुष्ट पार्ने अन्तिम पङ्क्ति फेला पार्नु हो। यो निम्न एरे सूत्र संग गर्न सकिन्छ:
यहाँ:
- समारोह IF (यदि) एक एक गरेर स्तम्भमा सबै कक्षहरू जाँच गर्दछ ग्राहक र यदि हामीलाई चाहिने नाम समावेश छ भने रेखा नम्बर देखाउँछ। पानामा लाइन नम्बर हामीलाई प्रकार्य द्वारा दिइएको छ लाइन (ROW), तर हामीलाई तालिकामा पङ्क्ति नम्बर चाहिने भएकोले, हामीले 1 पनि घटाउनुपर्छ, किनभने हामीसँग तालिकामा हेडर छ।
- त्यसपछि समारोह MAX (अधिकतम) पङ्क्ति संख्याहरूको गठन गरिएको सेटबाट अधिकतम मान चयन गर्दछ, अर्थात् क्लाइन्टको सबैभन्दा हालको लाइनको संख्या।
- समारोह INDEX (INDEX) कुनै पनि अन्य आवश्यक तालिका स्तम्भबाट फेला परेको अन्तिम नम्बरको साथ कक्षको सामग्रीहरू फर्काउँछ (अर्डर कोड).
यो सबै रूपमा प्रविष्ट गर्नुपर्छ सरणी सूत्र, अर्थात्:
- Office 365 मा नवीनतम अद्यावधिकहरू स्थापित र गतिशील arrays को लागि समर्थन संग, तपाईं सजिलै थिच्न सक्नुहुन्छ प्रविष्ट गर्नुहोस्.
- अन्य सबै संस्करणहरूमा, सूत्र प्रविष्ट गरेपछि, तपाईंले किबोर्ड सर्टकट थिच्नु पर्छ Ctrl+सिफ्ट+प्रविष्ट गर्नुहोस्, जसले स्वचालित रूपमा सूत्र पट्टीमा घुमाउरो कोष्ठकहरू थप्नेछ।
विधि २: नयाँ लुकअप प्रकार्यको साथ उल्टो लुकअप गर्नुहोस्
मैले पहिले नै नयाँ सुविधाको बारेमा भिडियोको साथ लामो लेख लेखेको छु हेर्नुहोस् (XLOOKUP), जुन पुरानो VLOOKUP लाई प्रतिस्थापन गर्न Office को नवीनतम संस्करणहरूमा देखा पर्यो (VLOOKUP)। ब्राउजको मद्दतले, हाम्रो कार्य एकदम प्राथमिक रूपमा हल गरिएको छ, किनभने। यस प्रकार्यको लागि (VLOOKUP को विपरीत), तपाइँ स्पष्ट रूपमा खोजी दिशा सेट गर्न सक्नुहुन्छ: माथि-डाउन वा तल-माथि - यसको अन्तिम तर्क (-1) यसको लागि जिम्मेवार छ:
विधि ३. पछिल्लो मिति भएको स्ट्रिङ खोज्नुहोस्
यदि स्रोत डेटामा हामीसँग क्रम संख्या वा समान भूमिका खेल्ने मिति भएको स्तम्भ छ भने, कार्य परिमार्जन गरिएको छ - हामीले मिलानको साथ अन्तिम (न्यूनतम) रेखा फेला पार्न आवश्यक छैन, तर पछिल्लो (। अधिकतम) मिति।
मैले क्लासिक प्रकार्यहरू प्रयोग गरेर यो कसरी गर्ने भनेर विस्तृत रूपमा छलफल गरिसकेको छु, र अब नयाँ गतिशील एरे प्रकार्यहरूको शक्ति प्रयोग गर्ने प्रयास गरौं। थप सुन्दरता र सुविधाको लागि, हामी किबोर्ड सर्टकट प्रयोग गरेर मूल तालिकालाई "स्मार्ट" तालिकामा रूपान्तरण पनि गर्छौं। Ctrl+T वा आदेशहरू गृह - तालिकाको रूपमा ढाँचा (घर - तालिकाको रूपमा ढाँचा).
तिनीहरूको मद्दतले, यो "हत्यारा जोडी" ले हाम्रो समस्यालाई धेरै राम्रो तरिकाले समाधान गर्छ:
यहाँ:
- पहिले कार्य FILTER (फिल्टर) हाम्रो तालिकाबाट स्तम्भमा भएका ती पङ्क्तिहरू मात्र चयन गर्दछ ग्राहक - हामीलाई चाहिने नाम।
- त्यसपछि समारोह ग्रेड (SORT) चयन गरिएका पङ्क्तिहरूलाई मिति अनुसार घट्दो क्रममा क्रमबद्ध गर्दछ, सबैभन्दा पछिल्लो सम्झौता शीर्षमा रहेको।
- समारोह INDEX (INDEX) पहिलो पङ्क्ति निकाल्छ, अर्थात् हामीलाई चाहिएको अन्तिम ट्रेड फर्काउँछ।
- र, अन्तमा, बाह्य FILTER प्रकार्यले परिणामहरूबाट अतिरिक्त 1st र 3rd स्तम्भहरू हटाउँछ (अर्डर कोड и ग्राहक) र मिति र रकम मात्र छोड्छ। यसको लागि, स्थिरांकहरूको एरे प्रयोग गरिन्छ। {0;1;0;1}, हामी कुन स्तम्भहरू (1) चाहन्छौं वा (0) प्रदर्शन गर्न चाहँदैनौं भनेर परिभाषित गर्दै।
विधि ४: पावर क्वेरीमा अन्तिम मिलान खोज्दै
ठीक छ, पूर्णताको लागि, पावर क्वेरी एड-इन प्रयोग गरेर हाम्रो उल्टो खोज समस्याको समाधान हेरौं। उनको सहयोग संग, सबै कुरा धेरै छिटो र सुन्दर तरिकाले हल गरिएको छ।
1। किबोर्ड सर्टकट प्रयोग गरेर हाम्रो मूल तालिकालाई "स्मार्ट" मा रूपान्तरण गरौं Ctrl+T वा आदेशहरू गृह - तालिकाको रूपमा ढाँचा (घर - तालिकाको रूपमा ढाँचा).
2। यसलाई बटनको साथ पावर क्वेरीमा लोड गर्नुहोस् तालिका/दायराबाट ट्याब तथ्याङ्क (डेटा - तालिका/दायराबाट).
3। हामी हाम्रो तालिका (हेडरमा रहेको फिल्टरको ड्रप-डाउन सूची मार्फत) मितिको घट्दो क्रममा क्रमबद्ध गर्छौं, ताकि हालैका कारोबारहरू शीर्षमा छन्।
4… ट्याबमा परिवर्तन टोली छान्नुहोस् द्वारा समूह (रूपान्तरण - समूह द्वारा) र ग्राहकहरु द्वारा समूह सेट, र एक समग्र प्रकार्य को रूप मा, विकल्प चयन गर्नुहोस् सबै रेखाहरू (सबै पङ्क्तिहरू)। तपाईंले आफ्नो मनपर्ने नयाँ स्तम्भलाई नाम दिन सक्नुहुन्छ - उदाहरणका लागि विवरण.
समूहीकरण पछि, हामीले हाम्रा ग्राहकहरूको अद्वितीय नामहरूको सूची र स्तम्भमा प्राप्त गर्नेछौं विवरण - तिनीहरू मध्ये प्रत्येकको सबै लेनदेनको साथ तालिकाहरू, जहाँ पहिलो रेखा नवीनतम लेनदेन हुनेछ, जुन हामीलाई चाहिन्छ:
5। बटनसँग नयाँ गणना गरिएको स्तम्भ थप्नुहोस् अनुकूलन स्तम्भ ट्याब स्तम्भ थप्नुहोस् (स्तम्भ थप्नुहोस् - अनुकूलन स्तम्भ थप्नुहोस्)र निम्न सूत्र प्रविष्ट गर्नुहोस्:
यहाँ विवरण - यो स्तम्भ हो जसबाट हामी ग्राहकहरु द्वारा टेबल लिन्छौं, र ० {} हामीले निकाल्न चाहेको पङ्क्तिको संख्या हो (Power Query मा पङ्क्ति नम्बरिङ शून्यबाट सुरु हुन्छ)। हामीले रेकर्डको साथ स्तम्भ पाउँछौं (रेकर्ड), जहाँ प्रत्येक प्रविष्टि प्रत्येक तालिकाको पहिलो पङ्क्ति हो:
यो स्तम्भ हेडरमा डबल तीरहरूको साथ बटनको साथ सबै रेकर्डहरूको सामग्री विस्तार गर्न बाँकी छ। अन्तिम सम्झौता इच्छित स्तम्भहरू चयन गर्दै:
... र त्यसपछि स्तम्भ मेटाउनुहोस् जुन अब आवश्यक छैन विवरण यसको शीर्षकमा दायाँ क्लिक गरेर - स्तम्भहरू हटाउनुहोस् (स्तम्भहरू हटाउनुहोस्).
मार्फत पानामा नतिजा अपलोड गरेपछि घर - बन्द गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र भित्र लोड गर्नुहोस् (घर - बन्द र लोड - बन्द र लोड गर्न ...) हामीले भर्खरैको लेनदेनको सूचीको साथ यस्तो राम्रो तालिका पाउनेछौं, हामीले चाहेको जस्तो:
जब तपाइँ स्रोत डेटा परिवर्तन गर्नुहुन्छ, तपाइँ तिनीहरूलाई दायाँ-क्लिक गरेर परिणामहरू अद्यावधिक गर्न नबिर्सनु हुँदैन - आदेश अद्यावधिक र बचत गर्नुहोस् (रिफ्रेस) वा किबोर्ड सर्टकट Ctrl+alt+F5.
- LOOKUP प्रकार्य VLOOKUP को वंशज हो
- नयाँ डायनामिक एरे प्रकार्यहरू SORT, FILTER, र UNIC कसरी प्रयोग गर्ने
- LOOKUP प्रकार्यको साथ पङ्क्ति वा स्तम्भमा अन्तिम गैर-खाली कक्ष फेला पार्दै