सारांश
कुछ मामलों में, आप संख्यात्मक मान प्राप्त करने के लिए लुकअप फॉर्मूला जैसे SUMIFS का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, G6 में सूत्र है:
=SUMIFS(sales,region,G4,quarter,G5)
जहाँ क्षेत्र (B5: B20), तिमाही (C5: C20), और बिक्री (D5: D20) को नाम दिया गया है।
परिणाम मध्य क्षेत्र, 127,250 के लिए Q3 बिक्री है।
स्पष्टीकरण
यदि आप SUMIFS फ़ंक्शन के लिए नए हैं, तो आप यहां कई उदाहरणों के साथ एक मूल अवलोकन पा सकते हैं।
SUMIFS फ़ंक्शन को एक या अधिक मानदंडों के आधार पर संख्यात्मक मानों को योग करने के लिए डिज़ाइन किया गया है। हालांकि, विशिष्ट मामलों में, आप आवश्यक मानदंड को पूरा करने वाले संख्यात्मक मान को "देखने" के लिए SUMIFS का उपयोग करने में सक्षम हो सकते हैं। ऐसा करने के मुख्य कारण सादगी और गति हैं।
दिखाए गए उदाहरण में, हमारे पास चार क्षेत्रों के लिए त्रैमासिक बिक्री डेटा है। हम SUMIFS को एक सम श्रेणी और पहली शर्त देकर शुरू करते हैं, जो G4, "Central" में मान के लिए परीक्षण करता है।
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- बिक्री सीमा (D5: D20) है
- मानदंड रेंज 1 क्षेत्र है (B5: B20)
- मानदंड 1 G4 ("केंद्रीय") है
फिर हम दूसरी श्रेणी / मापदंड जोड़ी जोड़ते हैं, जो तिमाही की जाँच करता है:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- मानदंड रेंज 2 तिमाही (C5: C20) है
- मानदंड 2 G5 ("Q3") है
इन मानदंडों के साथ, SUMIFS 127,250, सेंट्रल Q3 बिक्री नंबर देता है।
SUMIFS का व्यवहार सभी मिलान मूल्यों को जोड़ना है। हालाँकि, क्योंकि सिर्फ एक मिलान मूल्य है, परिणाम स्वयं मूल्य के समान है।
नीचे, हम कई लुकअप फॉर्मूला विकल्पों को देखते हैं।
लुकअप सूत्र विकल्प
यह खंड संक्षेप में उसी परिणाम देने वाले अन्य सूत्र विकल्पों की समीक्षा करता है। SUMPRODUCT के अपवाद के साथ (नीचे), ये अधिक पारंपरिक लुकअप सूत्र हैं जो लक्ष्य मान की स्थिति का पता लगाते हैं, और उस स्थान पर मान लौटाते हैं।
VLOOKUP के साथ
दुर्भाग्य से, VLOOKUP इस समस्या का एक अच्छा समाधान नहीं है। एक सहायक कॉलम के साथ, कई मानदंडों (उदाहरण के लिए) के साथ मिलान करने के लिए एक VLOOKUP फॉर्मूला बनाना संभव है, लेकिन यह एक अजीब प्रक्रिया है जो आपको स्रोत डेटा के साथ टिंकर करने की आवश्यकता है।
INDEX और MATCH के साथ
INDEX और MATCH एक बहुत ही लचीला लुकअप संयोजन है जिसका उपयोग सभी प्रकार की लुकअप समस्याओं के लिए किया जा सकता है, और यह उदाहरण कोई अपवाद नहीं है। INDEX और MATCH के साथ, हम क्षेत्र और तिमाही द्वारा बिक्री को इस तरह से एक सरणी सूत्र के साथ देख सकते हैं:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
नोट: यह एक सरणी सूत्र है, और इसे नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।
इस दृष्टिकोण के साथ चाल 1s और 0s के लुकअप सरणी के रूप में बनाने के लिए MATCH फ़ंक्शन के अंदर सरणी संचालन के साथ बूलियन तर्क का उपयोग करना है। फिर हम MATCH फ़ंक्शन को संख्या 1 खोजने के लिए कह सकते हैं। एक बार लुकअप सरणी बनाए जाने के बाद, सूत्र इस पर हल होता है:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
लुकअप ऐरे में केवल 1 शेष रहने पर, MATCH, INDEX फ़ंक्शन में 11 की स्थिति देता है, और INDEX उस स्थान पर बिक्री संख्या 127,250 देता है।
अधिक जानकारी के लिए, देखें: INDEX और MATCH कई मानदंडों के साथ
XLOOKUP के साथ
XLOOKUP एक्सेल में एक लचीला नया कार्य है जो ऐरे को मूल रूप से संभाल सकता है। XLOOKUP के साथ, हम INDEX और MATCH के समान ही दृष्टिकोण का उपयोग कर सकते हैं, लुकअप सरणी बनाने के लिए बूलियन लॉजिक और एरे ऑपरेशन का उपयोग कर रहे हैं:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
एक बार सरणी ऑपरेशन चलने के बाद, फार्मूला हल होता है:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
और XLOOKUP ऊपर, 127,250 के समान परिणाम देता है।
अधिक: कई मानदंडों के साथ XLOOKUP
LOOKUP के साथ
LOOKUP फ़ंक्शन एक्सेल में एक पुराना फ़ंक्शन है जिसके बारे में बहुत से लोगों को पता भी नहीं है। LOOKUP की एक प्रमुख खासियत यह है कि यह सरणियों को मूल रूप से संभाल सकता है। हालाँकि, LOOKUP में कुछ अलग कमजोरियाँ हैं:
- "सटीक मिलान मोड" में बंद नहीं किया जा सकता
- हमेशा लुकअप डेटा सॉर्ट किया जाता है, AZ
- हमेशा एक अनुमानित मैच देता है (यदि सटीक मिलान नहीं मिल सकता है)
बहरहाल, LOOKUP का उपयोग इस तरह इस समस्या को हल करने के लिए किया जा सकता है:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
जो सरल है:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
यदि स्थिति दोनों आवश्यकताओं को पूरा नहीं करती है, तो SUMIFS एक अच्छा विकल्प नहीं है।
