कहें कि आप एक सूची से अद्वितीय वस्तुओं को गिनने में सक्षम होना चाहते हैं, लेकिन एक मोड़ के साथ। और कहें कि आप इस वर्कशीट के साथ काम कर रहे हैं:
कॉलम D, कॉलम B से प्रत्येक सेक्शन में पंक्तियों की संख्या को गिनता है, और कॉलम C उस सेक्शन के लिए कॉलम A के पहले पांच अक्षरों के आधार पर अद्वितीय सेक्शन की संख्या को गिनाता है। कक्ष B2: B11 में ARG होता है, और आप A2: A11 के पहले पाँच वर्णों में आठ अद्वितीय वस्तुओं की गणना कर सकते हैं क्योंकि A7: A9 में प्रत्येक में 11158 होते हैं, इसलिए दो डुप्लिकेट की गणना नहीं की जाती है। इसी प्रकार, D12 में 5 बताता है कि BRD के लिए पाँच पंक्तियाँ हैं, लेकिन 12:16 की पंक्तियों के भीतर, पहले पाँच वर्णों की तीन अनूठी वस्तुएँ हैं, क्योंकि 11145 को दोहराया जाता है और 11173 को दोहराया जाता है।
लेकिन आप एक्सेल को ऐसा करने के लिए कैसे कहते हैं? और आप C2 में कौन सा फॉर्मूला इस्तेमाल कर सकते हैं जिसे C12 और C17 में कॉपी किया जा सकता है?
D2 में सरल गणना सूत्र, =COUNTIF(B:B,B2)कॉलम बी में मौजूद बी 2 (एआरजी) की संख्या को गिनता है।
आप स्तंभ A के पहले पांच वर्णों को अलग करने के लिए एक सहायक स्तंभ का उपयोग करते हैं, जैसा कि इस आकृति में है:
अगला, आपको किसी तरह यह इंगित करने की आवश्यकता है कि एआरजी के लिए, आप केवल कक्षों में रुचि रखते हैं F2: F11 अद्वितीय वस्तुओं की संख्या का पता लगाने के लिए। सामान्य तौर पर, आपको इस आकृति में दिखाए गए सरणी सूत्र का उपयोग करके यह मान मिलेगा:
आप सेल C3 का उपयोग अस्थायी रूप से केवल सूत्र दिखाने के लिए करते हैं; आप देख सकते हैं कि यह पिछले आंकड़ों में C3 में मौजूद नहीं है। (आप शीघ्र ही सीख जाएंगे कि यह सूत्र कैसे काम करता है।)
तो C2, C12 और C17 में सूत्र क्या है? इस आंकड़े में आश्चर्यजनक (और शांत) उत्तर दिखाया गया है:
वाह! यह कैसे काम करता है?
इस आकृति में परिभाषित नामों के उत्तर पर एक नज़र डालें:
यह पहले वाले आंकड़े से एक ही सूत्र है, लेकिन F2: F11 की रेंज का उपयोग करने के बजाय, यह Rg नामक श्रेणी का उपयोग करता है। साथ ही, सूत्र एक सरणी सूत्र था, लेकिन नामित सूत्रों को ऐसे माना जाता है जैसे वे सरणी सूत्र हैं! यानी, =AnswerCtrl + Shift + Enter के साथ दर्ज नहीं किया गया है, लेकिन हमेशा की तरह दर्ज किया गया है।
तो Rg को कैसे परिभाषित किया जाता है? यदि सेल C1 का चयन किया गया है (जो इस ट्रिक को समझने के लिए एक महत्वपूर्ण कदम है), तो इसे इस आंकड़े में परिभाषित किया गया है:
वह है =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1)।
Loan_Details शीट का नाम है, लेकिन आप इस फॉर्मूले को लंबी शीट के नाम के बिना देख सकते हैं। ऐसा करने का एक आसान तरीका अस्थायी रूप से शीट को कुछ सरल नाम देना है, जैसे x, और फिर परिभाषित नाम पर फिर से देखें:
यह सूत्र पढ़ना आसान है!
आप देख सकते हैं कि यह सूत्र कॉलम B के सभी के खिलाफ $ B1 (वर्तमान पंक्ति के सापेक्ष संदर्भ को नोट करता है) से मेल खाता है और 1. घटाता है। आप 1 को घटाते हैं क्योंकि आप F1 से OFFSET का उपयोग कर रहे हैं। अब जब आप C के सूत्र के बारे में जानते हैं, तो C2 के लिए एक पर एक नज़र डालें:
MATCH($B2,$B:$B,0)सूत्र का हिस्सा है, 2 है, तो सूत्र (पत्रक नाम के संदर्भ के बिना) है:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
या:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
या:
=OFFSET($F$1,1,0,10,1)
क्योंकि COUNTIF($B:$B,$B2)10 है, 10 ARG हैं। यह रेंज F2: F11 है। तथ्य की बात के रूप में, यदि सेल C2 का चयन किया जाता है और आप R5 पर जाने के लिए F5 दबाते हैं, तो आप यह देखते हैं:
यदि प्रारंभिक सेल C12 था, R5 पर जाने के लिए F5 दबाने से यह उत्पन्न होता है:
तो अब, उत्तर के रूप में परिभाषित किया गया है =SUM(1/COUNTIF(rg,rg)), आप सभी कर रहे हैं!
आइए अधिक सरल उदाहरण का उपयोग करके इस सूत्र पर अधिक बारीकी से काम करते हैं। आम तौर पर, COUNTIF के लिए वाक्य रचना है =COUNTIF(range,criteria)जैसे, =COUNTIF(C1:C10, "b")यह आंकड़ा में:
यह सीमा में b की संख्या के रूप में 2 देगा। लेकिन सीमा को मानदंड के रूप में स्वयं पारित करना मानदंड के रूप में सीमा में प्रत्येक आइटम का उपयोग करता है। यदि आप सूत्र के इस हिस्से को उजागर करते हैं:
और F9 दबाएं, आप देखें:
रेंज में प्रत्येक आइटम का मूल्यांकन किया जाता है, और संख्याओं की इस श्रृंखला का अर्थ है कि एक है और दो बी, तीन सी, और चार ए हैं। इन संख्याओं को 1 में विभाजित किया गया है, 1, are, 1, 1, 1, 1, 1, 1, 1, ¼, जैसा कि आप यहां देख सकते हैं:
तो आप 2 हिस्सों, 3 तिहाई, 4 चौथाई, और 1 पूरे, और उन्हें पैदावार जोड़ रहे हैं 4. यदि एक आइटम 7 बार दोहराया गया था, तो आप 7 सातवें और इतने पर होगा। बहुत अच्छा! (इस सूत्र की खोज / आविष्कार के लिए डेविड हैगर को सलाम।)
लेकिन एक मिनट पकड़ो। जैसा कि यह खड़ा है, आपको केवल C2, C12 और C17 में इस सूत्र को दर्ज करना होगा। क्या यह बेहतर नहीं होगा यदि आप इसे C2 में दर्ज कर सकते हैं और भर सकते हैं और केवल इसे सही कोशिकाओं में दिखा सकते हैं? वास्तव में, आप ऐसा कर सकते हैं। आप C2 में सूत्र को संशोधित कर सकते हैं =IF(B1B2,Answer,""), और जब आप इसे भरते हैं, तो यह कार्य करता है:
लेकिन यहाँ क्यों रुकना? एक सूत्र का नाम सूत्र में क्यों नहीं बनाया गया, जैसा कि यहाँ दिखाया गया है:
इस काम के लिए, सेल C2 को सक्रिय सेल होना चाहिए (या सूत्र को अलग होने की आवश्यकता होगी)। अब आप कॉलम C के फॉर्मूले को बदल सकते हैं =Answer2:
आप देख सकते हैं कि C3 =Answer2में स्तंभ C की सभी कोशिकाएँ हैं, इसलिए स्तंभ D में इसे जारी क्यों नहीं रखा जाए? D2 में सूत्र B1 और B2 की तुलना को लागू करने के बाद भी यहाँ दिखाया गया है:
इसलिए यदि आप सेल D2 को चयनित रखते हैं और किसी अन्य सूत्र को परिभाषित करते हैं, तो उत्तर 3 कहें:
तब आप =Answer3सेल D2 में प्रवेश कर सकते हैं और नीचे भर सकते हैं:
वर्कशीट का शीर्ष भाग यहां है, जिसमें फ़ार्मुले दिखाए जा रहे हैं, उसके बाद उसी स्क्रीनशॉट के साथ जिसमें मान दिख रहे हैं:
जब अन्य लोग यह पता लगाने की कोशिश करते हैं, तो वे पहले अपना सिर खरोंच कर सकते हैं!
यह अतिथि लेख Excel MVP Bob Umlas का है। यह किताब से है, अधिक एक्सेल बॉक्स के बाहर। पुस्तक के अन्य विषयों को देखने के लिए, यहाँ क्लिक करें।
