सामान्य सूत्र
SUMPRODUCT(--(A:A=A1))
सारांश
प्रस्तावना
यह एक कष्टप्रद लंबा परिचय है, लेकिन संदर्भ महत्वपूर्ण है, क्षमा करें!
यदि आप COUNTIF के साथ किसी श्रेणी में बहुत लंबी संख्या (16+ अंक) की गणना करने का प्रयास करते हैं, तो आप गलत परिणाम देख सकते हैं, कि कुछ संख्याओं को पाठ के रूप में संग्रहीत किए जाने पर भी कुछ फ़ंक्शन कितनी संख्या में काम करते हैं। नीचे स्क्रीन पर विचार करें। कॉलम D में सभी संख्याएँ गलत हैं, हालांकि कॉलम B में प्रत्येक संख्या अद्वितीय है, COUNTIF द्वारा दी गई गणना बताती है कि ये संख्या डुप्लिकेट हैं।
=COUNTIF(data,B5)
यह समस्या यह है कि एक्सेल संख्याओं को कैसे संभालता है। एक्सेल केवल 15 महत्वपूर्ण अंकों को संभाल सकता है, और यदि आप एक्सेल में 15 से अधिक अंकों के साथ एक संख्या दर्ज करते हैं, तो आप देखेंगे कि अनुगामी अंक चुपचाप शून्य में परिवर्तित हो जाएंगे। ऊपर बताई गई गिनती की समस्या इस सीमा से उत्पन्न होती है।
आम तौर पर, आप टेक्स्ट के रूप में लंबी संख्या दर्ज करके इस सीमा से बच सकते हैं, या तो एकल उद्धरण ('999999999999999999) के साथ नंबर शुरू करके या सेल (ओं) को पाठ के रूप में दर्ज करने से पहले प्रारूपित करके कर सकते हैं। जब तक आपको किसी संख्या पर गणित संचालन करने की आवश्यकता नहीं होती है, यह एक अच्छा समाधान है, और यह आपको क्रेडिट कार्ड नंबर और सीरियल नंबर जैसी चीजों के लिए अतिरिक्त संख्याओं को दर्ज करने देता है, बिना किसी संख्या को खोए।
हालाँकि, यदि आप 15 से अधिक अंकों (यहां तक कि जब पाठ के रूप में संग्रहीत) के साथ एक संख्या की गणना करने के लिए COUNTIF का उपयोग करने का प्रयास करते हैं, तो आप अविश्वसनीय परिणाम देख सकते हैं। ऐसा इसलिए होता है क्योंकि COUNTIF आंतरिक रूप से प्रसंस्करण के दौरान किसी बिंदु पर किसी संख्या में लंबे मान को वापस परिवर्तित करता है, जो ऊपर वर्णित 15 अंकों की सीमा को ट्रिगर करता है। वर्तमान सभी अंकों के बिना, कुछ संख्या को डुप्लिकेट की तरह गिना जा सकता है जब COUNTIF के साथ गिना जाता है।
उपाय
एक समाधान COUNTIF सूत्र को एक सूत्र से बदलना है जो SUM या SUMPRODUCT का उपयोग करता है। दिखाए गए उदाहरण में, E5 में सूत्र इस तरह दिखता है:
=SUMPRODUCT(--(data=B5))
सूत्र नामित श्रेणी "डेटा" (B5: B9) का उपयोग करता है और SUMPRODUCT के साथ प्रत्येक संख्या के लिए सही गणना करता है।
स्पष्टीकरण
सबसे पहले, SUMPRODUCT के अंदर की अभिव्यक्ति मौजूदा पंक्ति में कॉलम B से मान के साथ नामित श्रेणी "डेटा" में सभी मानों की तुलना करती है। इसके परिणामस्वरूप TRUE / FALSE परिणामों की एक सरणी होती है।
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
इसके बाद, डबल नेगेटिव TRUE / FALSE मानों को 1/0 मानों तक ले जाता है।
=SUMPRODUCT((1;0;0;0;0))
अंत में, SUMPRODUCT बस सरणी में आइटमों को रकम देता है और परिणाम देता है।
एरे सूत्र
आप SUMPRODUCT के बजाय SUM फ़ंक्शन का भी उपयोग कर सकते हैं, लेकिन यह एक सरणी सूत्र है और इसे नियंत्रण रेखा + के साथ दर्ज किया जाना चाहिए:
(=SUM(--(B:B=B5)))
इस समस्या के साथ अन्य कार्य
मैंने इसे स्वयं सत्यापित नहीं किया है, लेकिन ऐसा लगता है कि कई कार्यों में एक ही समस्या है, जिसमें SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF और AVERAGEIFS शामिल हैं।
