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