यह ट्यूटोरियल दर्शाता है कि कैसे उपयोग करना है Excएली SUMIF और SUMIFS समारोहमाहौल कुछ मानदंडों को पूरा करने वाले डेटा को समेटने के लिए एक्सेल और गूगल शीट्स में।
SUMIF फ़ंक्शन अवलोकन
आप एक्सेल में SUMIF फ़ंक्शन का उपयोग उन कक्षों के योग के लिए कर सकते हैं जिनमें एक विशिष्ट मान होता है, योग कक्ष जो किसी मान से अधिक या उसके बराबर होते हैं, आदि।
(ध्यान दें कि सूत्र इनपुट कैसे दिखाई देते हैं)
SUMIF फ़ंक्शन सिंटैक्स और तर्क:
| 1 | =SUMIF (रेंज, मानदंड, [sum_range]) |
श्रेणी - सेल की वह श्रेणी जिसके लिए आप मानदंड लागू करना चाहते हैं।
मानदंड - यह निर्धारित करने के लिए उपयोग किए जाने वाले मानदंड कि कौन से कक्षों को जोड़ना है।
sum_range - [वैकल्पिक] एक साथ जोड़ने के लिए कक्ष। यदि sum_range को छोड़ दिया जाता है, तो इसके बजाय श्रेणी के कक्षों को एक साथ जोड़ दिया जाता है।
SUMIF फ़ंक्शन क्या है?
SUMIF फ़ंक्शन स्प्रेडशीट में उपयोग किए जाने वाले पुराने कार्यों में से एक है। इसका उपयोग एक विशिष्ट मानदंड के लिए जाँच करने वाले कक्षों की एक श्रेणी के माध्यम से स्कैन करने के लिए किया जाता है, और फिर उन मानों के अनुरूप एक श्रेणी में मानों को जोड़ने के लिए उपयोग किया जाता है। मूल SUMIF फ़ंक्शन केवल एक मानदंड तक सीमित था। 2007 के बाद, SUMIFS फ़ंक्शन बनाया गया था जो कई मानदंडों की अनुमति देता है। अधिकांश सामान्य उपयोग दोनों के बीच समान रहता है, लेकिन वाक्य रचना में कुछ महत्वपूर्ण अंतर हैं जिनकी चर्चा हम इस पूरे लेख में करेंगे।
यदि आपने पहले से ऐसा नहीं किया है, तो आप COUNTIFS लेख में समान संरचना और उदाहरणों की अधिक समीक्षा कर सकते हैं।
मूल उदाहरण
आइए रिकॉर्ड की गई बिक्री की इस सूची पर विचार करें, और हम कुल आय जानना चाहते हैं।
क्योंकि हमारे पास एक खर्च था, नकारात्मक मूल्य, हम सिर्फ एक मूल राशि नहीं कर सकते। इसके बजाय, हम केवल उन मानों का योग करना चाहते हैं जो 0 से अधिक हैं। "0 से अधिक" वह है जो SUMIF फ़ंक्शन में हमारा मानदंड होगा। यह बताने का हमारा सूत्र है
| 1 | =SUMIF(A2:A7, ">0") |
दो-स्तंभ उदाहरण
जबकि मूल SUMIF फ़ंक्शन को आपको उन संख्याओं की श्रेणी के लिए एक मानदंड लागू करने के लिए डिज़ाइन किया गया था, जिनका आप योग करना चाहते हैं, अधिकांश समय आपको अन्य स्तंभों पर एक या अधिक मानदंड लागू करने की आवश्यकता होगी। आइए इस तालिका पर विचार करें:
अब, यदि हम मूल SUMIF फ़ंक्शन का उपयोग यह पता लगाने के लिए करते हैं कि हमारे पास कितने केले हैं (सेल D1 में सूचीबद्ध), तो हमें वह सीमा देनी होगी जो हम चाहते हैं योग अंतिम तर्क के रूप में, और इसलिए हमारा सूत्र होगा
| 1 | =SUMIF(A2:A7, D1, B2:B7) |
हालाँकि, जब प्रोग्रामर्स को अंततः एहसास हुआ कि उपयोगकर्ता एक से अधिक मानदंड देना चाहते हैं, तो SUMIFS फ़ंक्शन बनाया गया था। एक संरचना बनाने के लिए जो कि किसी भी मानदंड के लिए काम करेगी, SUMIFS के लिए आवश्यक है कि योग सीमा पहले सूचीबद्ध हो। हमारे उदाहरण में, इसका मतलब है कि सूत्र को होना चाहिए
| 1 | =SUMIFS(B2:B7, A2:A7, D1) |
नोट: इन दो सूत्रों का एक ही परिणाम मिलता है और वे समान दिख सकते हैं, इसलिए ध्यान दें कि किस फ़ंक्शन का उपयोग यह सुनिश्चित करने के लिए किया जा रहा है कि आप सभी तर्कों को सही क्रम में सूचीबद्ध करते हैं।
तिथियों के साथ कार्य करना, अनेक मानदंड
स्प्रेडशीट में तिथियों के साथ काम करते समय, जबकि तिथि को सीधे सूत्र में इनपुट करना संभव है, सेल में तारीख रखना सबसे अच्छा अभ्यास है ताकि आप केवल सूत्र में सेल का संदर्भ दे सकें। उदाहरण के लिए, यह कंप्यूटर को यह जानने में मदद करता है कि आप दिनांक 5/27/2020 का उपयोग करना चाहते हैं, न कि संख्या 5 को 27 से विभाजित करके 2022 तक।
आइए हर दो सप्ताह में किसी साइट पर विज़िटर की संख्या रिकॉर्ड करने वाली हमारी अगली तालिका देखें।
हम उस श्रेणी के प्रारंभ और अंत बिंदु निर्दिष्ट कर सकते हैं जिसे हम D2 और E2 में देखना चाहते हैं। इस श्रेणी में आगंतुकों की संख्या का योग करने के लिए हमारा सूत्र हो सकता है:
| 1 | =SUMIFS(B2:B7, A2:A7, ">="&D2, A2:A7, "<="&E2) |
ध्यान दें कि मानदंड बनाने के लिए हम "=" की तुलना को सेल संदर्भों से कैसे जोड़ सकते हैं। साथ ही, भले ही दोनों मानदंड समान श्रेणी के कक्षों (A2:A7) पर लागू किए जा रहे थे, आपको प्रत्येक मानदंड के अनुसार एक बार दो बार श्रेणी लिखनी होगी।
एकाधिक कॉलम
कई मानदंडों का उपयोग करते समय, आप उन्हें उसी श्रेणी में लागू कर सकते हैं जैसे हमने पिछले उदाहरण के साथ किया था, या आप उन्हें विभिन्न श्रेणियों में लागू कर सकते हैं। आइए हमारे नमूना डेटा को इस तालिका में संयोजित करें:
हमने प्रयोक्ता के लिए कुछ कक्षों को सेट किया है ताकि वे वह दर्ज कर सकें जो वे कक्ष E2 से G2 में खोजना चाहते हैं। इस प्रकार हमें एक सूत्र की आवश्यकता है जो फरवरी में चुने गए सेबों की कुल संख्या को जोड़ देगा। हमारा सूत्र इस तरह दिखता है:
| 1 | =SUMIFS(C2:C7, B2:B7, ">="&F2, B2:B7, "<="&G2, A2:A7, E2) |
SUMIFS OR टाइप लॉजिक के साथ
इस बिंदु तक, हमने जिन उदाहरणों का उपयोग किया है, वे सभी AND आधारित तुलना हैं, जहाँ हम उन पंक्तियों की तलाश कर रहे हैं जो हमारे सभी मानदंडों को पूरा करती हैं। अब, हम उस मामले पर विचार करेंगे जब आप एक या किसी अन्य मानदंड को पूरा करने वाली पंक्ति की संभावना की खोज करना चाहते हैं।
आइए बिक्री की इस सूची को देखें:
हम एडम और बॉब दोनों के लिए कुल बिक्री जोड़ना चाहते हैं। ऐसा करने के लिए, आपके पास कुछ विकल्प हैं। दो SUMIFS को एक साथ जोड़ना सबसे आसान है, जैसे:
| 1 | =SUMIFS(B2:B7, A2:A7, "एडम")+SUMIFS(B2:B7, A2:A7, "बॉब") |
यहां, हमने कंप्यूटर से हमारे व्यक्तिगत स्कोर की गणना की है, और फिर हम उन्हें एक साथ जोड़ते हैं।
जब आपके पास अधिक मानदंड श्रेणियां हों, तो हमारा अगला विकल्प अच्छा है, जैसे कि आप बार-बार पूरे सूत्र को फिर से लिखना नहीं चाहते हैं। पिछले फॉर्मूले में, हमने कंप्यूटर को मैन्युअल रूप से दो अलग-अलग SUMIFS को एक साथ जोड़ने के लिए कहा था। हालाँकि, आप अपने मानदंड को एक सरणी के अंदर लिखकर भी ऐसा कर सकते हैं, जैसे:
| 1 | =SUM(SUMIFS(B2:B7, A2:A7, {"एडम", "बॉब"})) |
देखें कि कैसे घुंघराले कोष्ठक के अंदर सरणी का निर्माण किया जाता है। जब कंप्यूटर इस सूत्र का मूल्यांकन करता है, तो उसे पता चल जाएगा कि हम अपने सरणी में प्रत्येक आइटम के लिए एक SUMIFS फ़ंक्शन की गणना करना चाहते हैं, इस प्रकार संख्याओं की एक सरणी बनाते हैं। बाहरी SUM फ़ंक्शन तब संख्याओं की उस सरणी को लेगा और उसे एकल संख्या में बदल देगा। सूत्र मूल्यांकन के माध्यम से कदम बढ़ाते हुए, यह इस तरह दिखेगा:
| 123 | =SUM(SUMIFS(B2:B7, A2:A7, {"एडम", "बॉब"}))=एसयूएम(२७४०१, ४३४७०)=70871 |
हमें वही परिणाम मिलता है, लेकिन हम सूत्र को थोड़ा और संक्षेप में लिखने में सक्षम थे।
रिक्त स्थान से निपटना
कभी-कभी आपके डेटा सेट में रिक्त कक्ष होंगे जिन्हें आपको खोजने या बचने की आवश्यकता है। इनके लिए मानदंड निर्धारित करना थोड़ा मुश्किल हो सकता है, तो आइए एक और उदाहरण देखें।
ध्यान दें कि सेल A3 वास्तव में खाली है, जबकि सेल A5 में एक सूत्र है जो "" की शून्य-लंबाई वाली स्ट्रिंग लौटाता है। यदि हम . का कुल योग ज्ञात करना चाहते हैं सही मायने में रिक्त कक्ष, हम "=" की कसौटी का उपयोग करेंगे, और हमारा सूत्र इस तरह दिखेगा:
| 1 | =SUMIFS(B2:B7,A2:A7,"=") |
दूसरी ओर, यदि हम उन सभी कक्षों के लिए योग प्राप्त करना चाहते हैं जो नेत्रहीन रूप से रिक्त दिखते हैं, तो हम मानदंड को "" में बदल देंगे, और सूत्र जैसा दिखता है
| 1 | =SUMIFS(B2:B7,A2:A7,"") |
आइए इसे चारों ओर पलटें: क्या होगा यदि आप गैर-रिक्त कोशिकाओं का योग खोजना चाहते हैं? दुर्भाग्य से, वर्तमान डिज़ाइन आपको शून्य-लंबाई वाले स्ट्रिंग से बचने नहीं देगा। आप "" के मानदंड का उपयोग कर सकते हैं, लेकिन जैसा कि आप उदाहरण में देख सकते हैं, इसमें अभी भी पंक्ति 5 से मान शामिल है।
| 1 | =SUMIFS(B2:B7,A2:A7,"") |
यदि आपको शून्य लंबाई वाले स्ट्रिंग्स वाले कक्षों की गणना नहीं करने की आवश्यकता है, तो आप SUMPRODUCT के अंदर LEN फ़ंक्शन का उपयोग करने पर विचार करना चाहेंगे
Google पत्रक में SUMIF
SUMIF फ़ंक्शन Google शीट में ठीक उसी तरह काम करता है जैसे Excel में:
