एक्सेल सूत्र: लापता मूल्यों का पता लगाएं और पुनः प्राप्त करें -

विषय - सूची

सामान्य सूत्र

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))

सारांश

दो सूचियों की तुलना करने और एक सूची से दूसरी सूची में लापता मानों को खींचने के लिए, आप INDEX और MATCH के आधार पर एक सरणी सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, सूची B में अंतिम मान सेल D11 में है। D12 में सूत्र, नीचे कॉपी किया गया है:

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))

जहां "पूरा" नाम दिया गया रेंज B5: B15 है।

नोट: यह एक सरणी सूत्र है और इसे नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।

स्पष्टीकरण

अंदर से बाहर काम करना, इस सूत्र का मूल आंतरिक MATCH अभिव्यक्ति है:

ISNA(MATCH(complete,$D$5:D11,0)

यहां, MATCH फ़ंक्शन का उपयोग आंशिक सूची के विरुद्ध सभी "पूर्ण" मानों की तुलना करने के लिए किया जाता है। नामांकित श्रेणी "पूर्ण" का उपयोग लुकअप मानों के लिए किया जाता है, और आंशिक सूची का उपयोग लुकअप सरणी के रूप में किया जाता है। हालांकि, सूचना है कि आंशिक सूची को विस्तार करने वाली सीमा के रूप में दर्ज किया गया है जो सूत्र सेल के "ऊपर एक सेल" को समाप्त करता है। यह आंशिक सूची को नए मूल्यों को शामिल करने के लिए विस्तारित करने की अनुमति देता है क्योंकि वे मूल सूची के नीचे दिखाई देते हैं।

MATCH का परिणाम संख्याओं और # N / A त्रुटियों की एक सरणी है, जहां संख्याएँ पूर्ण सूची में मूल्यों का प्रतिनिधित्व करती हैं जो आंशिक सूची में मौजूद हैं; और त्रुटियां लापता मूल्यों का प्रतिनिधित्व करती हैं:

(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)

ISNA फ़ंक्शन का उपयोग इन परिणामों को TRUE और FALSE मानों की एक सरणी में बदलने के लिए किया जाता है। इस सरणी में, TRUE लापता मानों से मेल खाती है और FALSE मौजूदा मूल्यों से मेल खाती है:

(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)

ISNA फ़ंक्शन इस सरणी को लुकअप सरणी के रूप में बाहरी MATCH पर लौटाता है। MATCH फ़ंक्शन हमेशा पाया गया पहला मैच लौटाता है, इसलिए मिलान पाया गया पहला लापता मान की स्थिति (पंक्ति) को लौटा देगा। यह परिणाम INDEX को पंक्ति संख्या के रूप में लौटाया गया है, जिसका नाम श्रेणी "पूर्ण" के रूप में दिया गया है।

सेल D12 में, पंक्ति 2 पर "किवी" पाया गया पहला लापता मान है, इसलिए हमारे पास है: