इस आंकड़े की जांच करें:
मान लीजिए कि आप इस से एक रिपोर्ट तैयार करना चाहते हैं जैसे कि आपने इस क्षेत्र को फ़िल्टर किया है। यदि आप उत्तर पर फ़िल्टर करते हैं, तो आप देखेंगे:
लेकिन क्या होगा यदि आप एक ही चीज़ का सूत्र-आधारित संस्करण चाहते थे?
यहां वह परिणाम है जो आप कॉलम I: K में देख रहे हैं:
स्पष्ट रूप से, यह एक ही रिपोर्ट है, लेकिन यहां कोई फ़िल्टर्ड आइटम नहीं हैं। यदि आप पूर्व में एक नई रिपोर्ट चाहते हैं, तो बस G1 से पूर्व में मान को बदलना अच्छा होगा:
यहाँ यह कैसे किया जाता है। सबसे पहले, यह VLOOKUP का उपयोग करके नहीं किया गया है। इसलिए मैंने इस तकनीक के शीर्षक के बारे में झूठ बोला!
कॉलम एफ पहले नहीं दिखाया गया था, और इसे छिपाया जा सकता है (या कहीं और ले जाया गया ताकि यह रिपोर्ट में हस्तक्षेप न करे)।
स्तंभ F में जो दिखाया गया है वह पंक्ति संख्याएँ हैं जहाँ G1 स्तंभ A में पाया जाता है; वह है, किन पंक्तियों में "उत्तर" का मान होता है? इस तकनीक में ऊपर सेल का उपयोग करना शामिल है, इसलिए इसे कम से कम पंक्ति 2 में शुरू करना होगा। यह कॉलम ए के खिलाफ "उत्तर" मूल्य से मेल खाता है, लेकिन पूरे कॉलम के बजाय, ओएफएसईटीईटी फ़ंक्शन का उपयोग करें OFFSET($A$1,F1,0,1000,1):।
चूंकि F1 0 है, यह वह है OFFSET(A1,0,0,1000,1)जो A1: A1000 है। (1000 मनमाना है, लेकिन काम करने के लिए काफी बड़ा है - आप इसे किसी भी अन्य संख्या में बना सकते हैं)।
F2 में मूल्य 2 वह जगह है जहां पहला "उत्तर" है। आप अंत में F1 का मान भी जोड़ना चाहते हैं, लेकिन यह अभी तक शून्य है।
सेल F3 में "मैजिक" जीवन में आता है। आप पहले से ही जानते हैं कि पहला उत्तर पंक्ति 2 में पाया जाता है। इसलिए, आप A1 के नीचे दो पंक्तियों की खोज शुरू करना चाहते हैं। आप ऐसा कर सकते हैं 2 OFFSET फ़ंक्शन के दूसरे तर्क के रूप में निर्दिष्ट करके।
F3 में सूत्र स्वचालित रूप से उस 2 को इंगित करेगा जो सेल F2 में गणना की गई थी: जब आप सूत्र को कॉपी करते हैं, तो आप देखेंगे =OFFSET($A$1,F2,0,1000,1)कि OFFSET($A$1,2,0,1000,1)कौन सा A3: A1000 है। तो आप उत्तर को इस नई श्रेणी के विरुद्ध मिलान कर रहे हैं और यह उत्तर को इस नई श्रेणी के तीसरे सेल में पाता है, इसलिए MATCH 3 देता है।
उपरोक्त कक्ष से मान को जोड़कर, F2, आपको 3 प्लस 2, या 5 दिखाई देंगे, जो कि दूसरी उत्तर वाली पंक्ति है।
यह सूत्र सभी मूल्यों को प्राप्त करने के लिए काफी दूर तक भरा हुआ है।
आपको वह पंक्ति संख्याएँ मिलेंगी जहाँ सभी उत्तर अभिलेख पाए जाते हैं।
K के माध्यम से कॉलम में परिणाम के लिए आप उन पंक्ति संख्याओं का अनुवाद कैसे करते हैं? यह सब एक सूत्र के साथ किया जाता है। इस सूत्र को I2 में दर्ज करें =IFERROR(INDEX(A:A,$F2),””):। राइट कॉपी करें और फिर कॉपी करें।
IFERROR का उपयोग क्यों करें? त्रुटि कहां है? सूचना सेल F6 - इसमें # N / A शामिल है (यही वजह है कि आप कॉलम F को छिपाना चाहेंगे) क्योंकि उत्तर की पंक्ति 15 के बाद कोई और उत्तर नहीं हैं। इसलिए यदि स्तंभ F में कोई त्रुटि है, तो एक रिक्त लौटें। अन्यथा स्तंभ A से मान उठाएँ (और दाएं, B और C भरे जाने पर)।
$ F2 स्तंभ एफ का एक पूर्ण संदर्भ है इसलिए भरने का अधिकार अभी भी कॉलम एफ को संदर्भित करता है।
यह अतिथि लेख Excel MVP Bob Umlas का है। यह उनकी पुस्तक, एक्सेल आउटसाइड द बॉक्स से उनकी पसंदीदा तकनीकों में से एक है।
बॉक्स के बाहर एक्सेल »
