उदाहरण कार्यपुस्तिका डाउनलोड करें
यह ट्यूटोरियल दर्शाता है कि कैसे उपयोग करना है एक्सेल SUMPRODUCT फंक्शन एक्सेल में।
SUMPRODUCT फ़ंक्शन अवलोकन
SUMPRODUCT फ़ंक्शन संख्याओं की सरणियों को गुणा करता है और परिणामी सरणी का योग करता है।
SUMPRODUCT एक्सेल वर्कशीट फ़ंक्शन का उपयोग करने के लिए, एक सेल चुनें और टाइप करें:
(ध्यान दें कि सूत्र इनपुट कैसे दिखाई देते हैं)
SUMPRODUCT फ़ंक्शन सिंटैक्स और इनपुट:
| 1 | =SUMPRODUCT(सरणी1,सरणी2,सरणी3) |
सरणी1 - संख्याओं की सरणी।
SUMPRODUCT फ़ंक्शन क्या है?
SUMPRODUCT फ़ंक्शन एक्सेल के भीतर अधिक शक्तिशाली कार्यों में से एक है। यह नाम, आपको विश्वास दिला सकता है कि यह केवल बुनियादी गणित गणनाओं के लिए है, लेकिन इसका उपयोग बहुत अधिक के लिए किया जा सकता है।
सरणियों
SUMPRODUCT को सरणियों के इनपुट की आवश्यकता होती है।
तो सबसे पहले, "सरणी" से हमारा क्या मतलब है? एक सरणी सरल है वस्तुओं का एक समूह (उदा। संख्याएं) एक विशिष्ट क्रम में व्यवस्थित, ठीक कोशिकाओं की एक श्रृंखला की तरह। इसलिए, यदि आपके पास कक्ष A1:A3 में संख्या 1, 2, 3 है, तो Excel इसे सरणी {1,2,3} के रूप में पढ़ेगा। वास्तव में, आप सीधे एक्सेल फ़ार्मुलों में {1,2,3} दर्ज कर सकते हैं और यह सरणी को पहचान लेगा।
हम नीचे सरणियों के बारे में अधिक बात करेंगे, लेकिन पहले एक सरल उदाहरण देखें।
मूल गणित
आइए कुल बिक्री की गणना करने के लिए इसका उपयोग करते हुए SUMPRODUCT का एक मूल उदाहरण देखें।
हमारे पास उत्पादों की हमारी तालिका है, और हम कुल बिक्री की गणना करना चाहते हैं। आप बस एक नया कॉलम जोड़ने के लिए लुभाते हैं, बेची गई मात्रा * मूल्य लेते हैं और फिर नए कॉलम का योग करते हैं। इसके बजाय, हालांकि, आप केवल SUMPRODUCT फ़ंक्शन का उपयोग कर सकते हैं। आइए सूत्र के माध्यम से चलते हैं:
| 1 | =SUMPRODUCT(A2:A4,B2:B4) |
फ़ंक्शन संख्याओं की श्रेणियों को सरणियों में लोड करेगा, उन्हें एक दूसरे के विरुद्ध एकाधिक करेगा, और फिर परिणामों को जोड़ देगा:
| 1234 | =SUMPRODUCT({100, 50, 10}, {6, 7, 5})=SUMPRODUCT({100 * 6, 50 * 7, 10 * 5})=SUMPRODUCT({600, 350, 50}=1000 |
SUMPRODUCT Funciton हमारे लिए सभी संख्याओं को गुणा करने और योग करने में सक्षम था।
भारित औसत
एक और मामला जहां SUMPRODUCT का उपयोग करना सहायक होता है, जब आपको भारित औसत की गणना करने की आवश्यकता होती है। यह अक्सर स्कूलवर्क के साथ व्यवहार करते समय होता है, तो आइए निम्नलिखित तालिका पर विचार करें।
हम देख सकते हैं कि क्विज़, परीक्षण और गृहकार्य का समग्र ग्रेड के लिए कितना मूल्य है, साथ ही साथ प्रत्येक विशेष आइटम के लिए वर्तमान औसत क्या है। हम समग्र ग्रेड की गणना तब लिखकर कर सकते हैं
| 1 | =SUMPRODUCT(B2:B4, C2:C4) |
कुल योग करने से पहले हमारा फ़ंक्शन फिर से प्रत्येक आइटम को सरणियों में गुणा करता है। यह इस तरह काम करता है
| 123 | =SUMPRODUCT({30%, 50%, 20%}, {73%, 90%, 95%})=SUMPRODUCT({22%, 45%, 19%})= ८६% |
एकाधिक कॉलम
एक अन्य स्थान जिसका हम SUMPRODUCT का उपयोग कर सकते हैं वह और भी अधिक स्तंभों के साथ है जिन्हें सभी को एक दूसरे के विरुद्ध गुणा करने की आवश्यकता है। आइए एक उदाहरण देखें जहां हमें लकड़ी के टुकड़ों में मात्रा की गणना करने की आवश्यकता है।
प्रत्येक पंक्ति के लिए कुल बिक्री की गणना करने के लिए एक सहायक कॉलम बनाने के बजाय, हम इसे एक सूत्र के साथ कर सकते हैं। हमारा सूत्र होगा
| 1 | =SUMPRODUCT(B2:B5, C2:C5, D2:D5) |
प्रत्येक सरणी के पहले आइटम एक दूसरे के विरुद्ध गुणा करेंगे (उदा., 4 * 2 * 1 = 8)। फिर, दूसरा (4 * 2 * 2 = 16), और 3तृतीय, आदि। कुल मिलाकर, यह {8, 16, 16, 32) जैसे दिखने वाले उत्पादों की श्रेणी का उत्पादन करेगा। तब कुल आयतन उस सरणी का योग होगा, 72।
एक मानदंड
ठीक है, चलो जटिलता की एक और परत जोड़ते हैं। हमने देखा है कि SUMPRODUCT संख्याओं की सरणियों को संभाल सकता है, लेकिन अगर हम मापदंड की जाँच करना चाहते हैं तो क्या होगा? ठीक है, आप बूलियन मानों के लिए सरणियाँ भी बना सकते हैं (बूलियन मान वे मान हैं जो TRUE या FALSE हैं)।
उदाहरण के लिए, एक मूल सरणी {1, 2, 3} लें। आइए एक संगत सरणी बनाते हैं जो इंगित करती है कि क्या प्रत्येक संख्या 1 से बड़ी है। यह सरणी {FALSE, TRUE, TRUE} की तरह दिखेगी।
यह सूत्रों में अत्यंत सहायक है, क्योंकि हम आसानी से TRUE / FALSE को 1 / 0 में बदल सकते हैं। आइए एक उदाहरण देखें।
नीचे दी गई तालिका का उपयोग करके, हम गणना करना चाहते हैं कि "कितनी इकाइयाँ बेची गईं, लाल थीं?"
हम इस सूत्र के साथ ऐसा कर सकते हैं:
| 1 | =SUMPRODUCT(A2:A4, --(B2:B4="लाल")) |
"रुको! वहां डबल माइनस सिंबल के साथ क्या है?" तुम कहो। याद रखें कि मैंने कैसे कहा था कि हम सही/गलत से 1/0 में परिवर्तित हो सकते हैं? ऐसा हम कंप्यूटर को गणितीय संक्रिया करने के लिए बाध्य करके करते हैं। इस मामले में, हम कह रहे हैं "नकारात्मक मान लें, और फिर नकारात्मक फिर से लें"। इसे लिखते हुए, हमारी सरणी इस तरह बदलने जा रही है:
| 123 | {सत्य, सत्य, असत्य}{-1, -1, 0}{1, 1, 0} |
तो, पूर्ण SUMPRODUCT सूत्र पर वापस, यह हमारे सरणियों में लोड होने वाला है और फिर इस तरह से गुणा करेगा
| 123 | =SUMPRODUCT({100, 50, 10}, {1, 1, 0})=SUMPRODUCT({100, 50, 0})=150 |
ध्यान दें कि कैसे 3तृतीय आइटम 0 बन गया, क्योंकि 0 से गुणा किया गया कुछ भी शून्य हो जाता है।
एकाधिक मानदंड
हम अपने फ़ंक्शन में 255 सरणियों को लोड कर सकते हैं, इसलिए हम निश्चित रूप से अधिक मानदंडों में लोड कर सकते हैं। आइए इस बड़ी तालिका को देखें जहां हमने बेचा गया महीना जोड़ा है।
अगर हम जानना चाहते हैं कि कितने आइटम बेचे गए तो लाल थे तथा फरवरी के महीने में थे, हम अपना फॉर्मूला इस तरह लिख सकते थे
| 1 | =SUMPRODUCT(A2:A4, --(B2:B4="Red"), --(C2:C4="Feb")) |
कंप्यूटर तब हमारे सरणियों का मूल्यांकन करेगा और गुणा करेगा। हमने पहले ही कवर कर लिया है कि कैसे सही/गलत सरणियाँ 1/0 में बदल जाती हैं, इसलिए मैं अभी उस चरण को छोड़ने जा रहा हूँ।
| 123 | =SUMPRODUCT({100, 50, 10}, {1, 1, 0}, {0, 1, 1})=SUMPRODUCT({0, 50, 0})=50 |
हमारे उदाहरण में हमारे पास केवल एक पंक्ति थी जो सभी मानदंडों से मेल खाती थी, लेकिन वास्तविक डेटा के साथ, आपके पास कई पंक्तियाँ हो सकती थीं जिन्हें आपको एक साथ जोड़ने की आवश्यकता थी।
जटिल मानदंड
ठीक है, इस बिंदु तक, आप शायद प्रभावित न हों क्योंकि हमारे सभी उदाहरण SUMIF या COUNTIF जैसे अन्य कार्यों का उपयोग करके किए जा सकते थे। अब हम उन अन्य कार्यों में कुछ करने जा रहे हैं नहीं कर सकता करना। पहले, हमारे महीने के कॉलम में महीनों के वास्तविक नाम थे। क्या होगा अगर इसके बजाय इसकी तारीखें हों?
हम अभी SUMIF नहीं कर सकते, क्योंकि SUMIF हमारे लिए आवश्यक मानदंड नहीं संभाल सकता। SUMPRODUCT हालांकि हमें सरणी में हेरफेर करने और एक गहन परीक्षण करने में संभाल सकता है। जब हमने सही/गलत का 1/0 में अनुवाद किया है, तो हम पहले से ही सरणियों में हेरफेर कर रहे हैं। हम इस सरणी को MONTH फ़ंक्शन के साथ हेरफेर करने जा रहे हैं। यहां पूरा फॉर्मूला है जिसका हम उपयोग करने जा रहे हैं
| 1 | =SUMPRODUCT(A2:A4, --(B2:B4="Red"), --(MONTH(C2:C4)=2)) |
आइए देखें 3तृतीय अधिक बारीकी से सरणी। सबसे पहले, हमारा सूत्र C2:C4 में प्रत्येक तिथि से माह संख्या निकालने जा रहा है। यह हमें {1, 2, 2} देगा। इसके बाद, हम जांचते हैं कि क्या वह मान 2 के बराबर है। अब हमारा ऐरे {False, True, True} जैसा दिखता है। हम डबल माइनस फिर से करते हैं, और हमारे पास {0, 1, 1} है। अब हम उसी स्थान पर वापस आ गए हैं जो हमारे पास उदाहरण 3 में था, और हमारा सूत्र हमें यह बताने में सक्षम होगा कि फरवरी में 50 इकाइयाँ बेची गईं जो लाल थीं।
डबल माइनस बनाम गुणा
यदि आपने SUMPRODUCT फ़ंक्शन को पहले उपयोग में देखा है, तो हो सकता है कि आपने थोड़ा अलग नोटेशन देखा हो। डबल माइनस का उपयोग करने के बजाय, आप लिख सकते हैं
| 1 | =SUMPRODUCT(A2:A4*(B2:B4="Red")*(MONTH(C2:C4)=2)) |
सूत्र अभी भी उसी तरह काम करने वाला है, हम कंप्यूटर को केवल मैन्युअल रूप से बता रहे हैं कि हम सरणियों को गुणा करना चाहते हैं। SUMPRODUCT वैसे भी ऐसा करने जा रहा था, इसलिए गणित के काम करने के तरीके में कोई बदलाव नहीं आया है। गणित ऑपरेशन करने से हमारा सही/गलत 1/0 में बदल जाता है। तो, अंतर क्यों?
ज्यादातर समय, यह बहुत ज्यादा मायने नहीं रखता है, और यह उपयोगकर्ता की वरीयता के लिए आता है। हालांकि कम से कम एक ऐसा मामला है जहां गुणा करने की जरूरत है।
जब आप SUMPRODUCT का उपयोग करते हैं, तो कंप्यूटर सभी तर्कों (सरणी1, सरणी2, आदि) के समान आकार के होने की अपेक्षा करता है। इसका मतलब है कि उनके पास पंक्तियों या स्तंभों की संख्या समान है। हालाँकि, आप SUMPRODUCT के साथ दो आयामी सरणी गणना के रूप में जो जानते हैं वह कर सकते हैं जिसे हम अगले उदाहरण में देखेंगे। जब आप ऐसा करते हैं, तो सरणियाँ अलग-अलग आकार की होती हैं, इसलिए हमें उस "सभी समान आकार" की जाँच को बायपास करना होगा।
दो आयाम
पिछले सभी उदाहरणों में हमारे सरणियाँ एक ही दिशा में जा रही थीं। SUMPRODUCT दो दिशाओं में जाने वाली चीजों को संभाल सकता है, जैसा कि हम अगली तालिका में देखेंगे।
यहां बेची गई इकाइयों की हमारी तालिका है, लेकिन डेटा को फिर से व्यवस्थित किया जाता है जहां श्रेणियां शीर्ष पर जा रही हैं। यदि हम यह पता लगाना चाहते हैं कि लाल और श्रेणी A में कितने आइटम थे, तो हम लिख सकते हैं
| 1 | =SUMPRODUCT((A2:A4="Red")*(B1:C1="A")*B2:C4) |
यहां क्या हो रहा है?? यह पता चला है कि हम दो अलग-अलग दिशाओं में गुणा करने जा रहे हैं। इसकी कल्पना करना केवल एक लिखित वाक्य के साथ करना कठिन है, इसलिए हमारी मदद करने के लिए हमारे पास कुछ चित्र हैं। सबसे पहले, हमारी पंक्ति मानदंड (क्या यह लाल है?) सरणी में प्रत्येक पंक्ति में गुणा करने जा रहा है।
| 1 | =SUMPRODUCT((A2:A4="RED")*B2:C4) |
इसके बाद, कॉलम मानदंड (क्या यह श्रेणी ए है?) प्रत्येक कॉलम को गुणा करने जा रहा है
| 1 | =SUMPRODUCT((A2:A4="Red")*(B1:C1="A")*B2:C4) |
उन दोनों मानदंडों के अपना काम करने के बाद, केवल गैर-शून्य शेष 5 और 10 हैं। SUMPRODUCT तब हमें हमारे उत्तर के रूप में कुल 15 का कुल योग देगा।
याद रखें कि जब तक आप दो आयाम नहीं कर रहे हैं, तब तक हमने समान आकार की आवश्यकता वाले सरणियों के बारे में कैसे बात की? वह आंशिक रूप से सही था। हम अपने सूत्र में उपयोग किए गए सरणियों को फिर से देखते हैं। NS ऊंचाई हमारे दो सरणियों में से एक समान है, और चौड़ाई हमारे दो सरणियों के समान हैं। इसलिए, आपको अभी भी यह सुनिश्चित करने की आवश्यकता है कि चीजें सही ढंग से पंक्तिबद्ध होने जा रही हैं, लेकिन आप इसे विभिन्न आयामों में कर सकते हैं।
दो आयाम और जटिल
कई बार हमें ऐसे डेटा के साथ प्रस्तुत किया जाता है जो हमारे फ़ार्मुलों के लिए उपयुक्त सर्वोत्तम लेआउट में नहीं होता है। हम इसे मैन्युअल रूप से पुनर्व्यवस्थित करने का प्रयास कर सकते हैं, या हम अपने सूत्रों के साथ होशियार हो सकते हैं। आइए निम्नलिखित तालिका पर विचार करें।
यहां हमारे पास प्रत्येक महीने के लिए हमारे आइटम और बिक्री का डेटा एक साथ मिला हुआ है। हम यह कैसे पता लगाएंगे कि बॉब ने पूरे वर्ष में कितनी वस्तुएं बेची हैं?
ऐसा करने के लिए, हम दो अतिरिक्त कार्यों का उपयोग करेंगे: खोज और ISNUMBER। खोज फ़ंक्शन हमें हेडर सेल के भीतर हमारे कीवर्ड "आइटम" की तलाश करने जा रहा है। इस फ़ंक्शन से आउटपुट या तो किसी संख्या या त्रुटि से जा रहा है (यदि कीवर्ड नहीं मिला है)। फिर, हम परिवर्तित करने के लिए ISNUMBER का उपयोग करेंगे वह हमारे बूलियन मूल्यों में आउटपुट। हमारा फॉर्मूला नीचे जैसा दिखने वाला है।
अब तक आप पहले एरे से काफी परिचित हो चुके होंगे। यह {0, 1, 0, 1} जैसा आउटपुट बनाने वाला है। अगले मानदंड सरणी के बारे में हमने अभी बात की। यह "आइटम" वाले सभी कक्षों के लिए एक संख्या बनाने जा रहा है, और अन्य के लिए एक त्रुटि {5, #N/A!, 5, #N/A!}। फिर ISNUMBER इसे बूलियन {सच, गलत, सही, गलत} में बदल देता है। फिर जब हम गुणा करते हैं, तो यह केवल पहले और तीसरे कॉलम से मान रखने वाला है। सभी सरणियों के एक-दूसरे के विरुद्ध गुणा करने के बाद, हमारे पास केवल गैर-शून्य संख्याएँ होंगी जिन्हें यहाँ हाइलाइट किया गया है:
| 1 | =SUMPRODUCT((A2:A5="Bob")*(ISNUMBER(SEARCH("Items",B1:E1))*B2:E5)) |
SUMPRODUCT तब उन सभी को जोड़ देगा, और हमें अपना अंतिम परिणाम 29 प्राप्त होगा।
SUMPRODUCT या
यदि हमारे मानदंड कॉलम में एक मान या कोई अन्य मान है, तो कई स्थितियां उत्पन्न होती हैं, जहां हम मूल्यों का योग करने में सक्षम होना चाहते हैं। आप इसे SUMPRODUCT में एक दूसरे के विरुद्ध दो मानदंड सरणियाँ जोड़कर पूरा कर सकते हैं।
इस उदाहरण में, हम रेड और ब्लू दोनों के लिए बेची गई इकाइयों को जोड़ना चाहते हैं।
हमारा फॉर्मूला इस तरह दिखेगा
| 1 | =SUMPRODUCT(A2:A7, (B2:B7="लाल")+(B2:B7="नीला")) |
आइए लाल मानदंड सरणी को देखें। यह इस तरह दिखने वाली एक सरणी का उत्पादन करेगा: {1, 1, 0, 0, 0, 0}। ब्लू क्राइटेरिया ऐरे {0, 0, 1, 0, 1, 0} जैसा दिखेगा। जब आप उन्हें एक साथ जोड़ते हैं, तो नया सरणी {1, 1, 1, 0, 1, 0} जैसा दिखेगा। हम देख सकते हैं कि कैसे दो सरणियों ने एक साथ एक मानदंड सरणी में मिश्रित किया है। फ़ंक्शन तब हमारे पहले सरणी से गुणा करेगा, और हमें {100, 50, 10, 0, 75, 0} मिलेगा। ध्यान दें कि ग्रीन के लिए मान शून्य कर दिए गए हैं। SUMPRODUCT का अंतिम चरण हमारे 235 के समाधान तक पहुँचने के लिए सभी संख्याओं को एक साथ जोड़ना है।
यहाँ सावधानी का एक शब्द। इस बारे में सावधान रहें जब मानदंड सरणियाँ परस्पर अनन्य न हों। हमारे उदाहरण में, कॉलम बी में मान या तो लाल या नीला हो सकता है, लेकिन हम जानते थे कि यह दोनों कभी नहीं हो सकते। विचार करें कि क्या हमने यह सूत्र लिखा है:
| 1 | =SUMPRODUCT(A2:A7, (A2:A7>=50)+(B2:B7="नीला")) |
हमारा इरादा उन नीली वस्तुओं को खोजना है जो बेची गई थीं या 50 से अधिक मात्रा में थीं। हालांकि, ये शर्तें अनन्य नहीं हैं, क्योंकि कॉलम ए में एक पंक्ति 50 से अधिक हो सकती है। तथा नीला हो। इसके परिणामस्वरूप पहला मानदंड सरणी {1, 1, 0, 1, 1, 0} जैसा दिखेगा, दूसरा मानदंड सरणी {0, 0, 1, 0, 1, 0} होगा। उन्हें एक साथ जोड़ने पर {1, 1, 1, 1, 2, 0} का उत्पादन हुआ। क्या आप देखते हैं कि अब हमारे पास 2 कैसे हैं? अगर अकेला छोड़ दिया जाता है, तो SUMPRODUCT उस पंक्ति में मान को दोगुना कर देगा, 75 को 150 में बदल देगा, और हमें गलत परिणाम मिलेगा। इसे ठीक करने के लिए, हम अपने एरे पर एक बाहरी मानदंड की जाँच करते हैं, जैसे:
| 1 | =SUMPRODUCT(A2:A7, --((A2:A7>=50)+(B2:B7="Blue")>0)) |
अब, दो आंतरिक मानदंड सरणियों को एक साथ जोड़ने के बाद, हम जाँच करेंगे कि क्या परिणाम 0 से अधिक है। इससे हमारे पास पहले वाले 2 से छुटकारा मिल जाता है, और इसके बजाय हमारे पास {1, 1, 1 जैसी एक सरणी होगी। , 1, 1, 0} जो सही परिणाम देगा।
SUMPRODUCT सटीक
एक्सेल में अधिकांश फ़ंक्शन केस-संवेदी नहीं होते हैं, लेकिन कभी-कभी हमें केस संवेदनशीलता को ध्यान में रखते हुए लुकअप करने में सक्षम होने की आवश्यकता होती है। जब वांछित परिणाम संख्यात्मक होता है, तो हम इसे SUMPRODUCT फ़ंक्शन के अंदर EXACT का उपयोग करके पूरा कर सकते हैं। निम्नलिखित तालिका पर विचार करें:
हम आइटम "एबीसी123" के लिए स्कोर खोजना चाहते हैं। आम तौर पर, EXACT फ़ंक्शन दो वस्तुओं की तुलना करेगा और यह बताते हुए एक बूलियन आउटपुट लौटाएगा कि क्या दो आइटम हैं बिल्कुल सही वही। हालांकि, चूंकि हम एक SUMPRODUCT के अंदर हैं, इसलिए हमारे कंप्यूटर को पता चल जाएगा कि हम सरणियों के साथ काम कर रहे हैं और एक सरणी में प्रत्येक आइटम के साथ एक आइटम की तुलना करने में सक्षम होंगे। हमारा फॉर्मूला इस तरह दिखेगा
| 1 | =SUMPRODUCT(--EXACT("ABC123", A2:A5), B2:B5) |
EXACT फ़ंक्शन तब A2:A5 में प्रत्येक आइटम की जांच करेगा कि यह मान और केस से मेल खाता है या नहीं। यह एक सरणी का उत्पादन करेगा जो {0, 1, 0, 0} जैसा दिखता है। जब B2:B5 से गुणा किया जाता है, तो सरणी {0, 2, 0, 0} बन जाती है। अंतिम योग के बाद, हमें 2 का हल मिलता है।
Google पत्रक में SUMPRODUCT
SUMPRODUCT फ़ंक्शन Google पत्रक में ठीक उसी तरह काम करता है जैसे Excel में होता है:
VBA . में SUMPRODUCT उदाहरण
आप VBA में SUMPRODUCT फ़ंक्शन का भी उपयोग कर सकते हैं। प्रकार: application.worksheetfunction.sumproduct(array1,array2,array3)
निम्नलिखित VBA कथनों को क्रियान्वित करना
| 1 | रेंज ("बी 10") = एप्लिकेशन। वर्कशीट फ़ंक्शन। सम उत्पाद (रेंज ("ए 2: ए 7"), रेंज ("बी 2: बी 7")) |
निम्नलिखित परिणाम देगा
फ़ंक्शन तर्कों (सरणी 1, आदि) के लिए, आप या तो उन्हें सीधे फ़ंक्शन में दर्ज कर सकते हैं, या इसके बजाय उपयोग करने के लिए चर परिभाषित कर सकते हैं।
