एक तालिका की कल्पना करें जिसमें विभागों के नाम (या खाते, या कुछ और) एक पंक्ति में पंक्तियों में सूचीबद्ध हैं। 
कसौटी द्वारा कोशिकाओं को योग
प्रत्येक विभाग के लिए कुल राशि की गणना करना आवश्यक है। कई इसे एक फ़िल्टर के साथ करते हैं और कोशिकाओं में कलम के साथ लिखते हैं।
हालांकि यह आसानी से और बस एक फ़ंक्शन के साथ किया जा सकता है - SUMMESLI ।
SUMMESLES (SUMIF) - कोशिकाएं जो किसी दिए गए शर्त को पूरा करती हैं (केवल एक स्थिति निर्दिष्ट की जा सकती है)। इस फ़ंक्शन का उपयोग तब भी किया जा सकता है यदि तालिका को प्रत्येक माह (मासिक, प्रत्येक माह में तीन कॉलम - आय, व्यय | अंतर) द्वारा स्तंभों में विभाजित किया जाता है और आपको आय, व्यय और अंतर द्वारा केवल सभी अवधि के लिए कुल राशि की गणना करने की आवश्यकता होती है।
SUMMESLI के लिए कुल तीन तर्क हैं: श्रेणी , मानदंड , Range_Summing ।
= सारांश (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)
- रेंज (A1: A20000) - मापदंड के साथ सीमा को इंगित करता है। यानी वह कॉलम जिसमें मानदंड तर्क द्वारा इंगित मूल्य की खोज करनी है।
- मानदंड (A1) मान (पाठ या संख्यात्मक, साथ ही तिथि) है जो रेंज में पाया जाना चाहिए। वाइल्डकार्ड वर्ण "*" और "?" हो सकते हैं। यानी उन मानों को संक्षेप करने के लिए मानदंड "* मास *" निर्दिष्ट करना जिसमें "द्रव्यमान" शब्द होता है। उसी समय, शब्द "द्रव्यमान" या तो पाठ में कहीं भी हो सकता है, या एक सेल में केवल एक ही शब्द हो सकता है। और "द्रव्यमान" को निर्दिष्ट करते हुए, "द्रव्यमान" से शुरू होने वाले सभी मूल्यों को अभिव्यक्त किया जाएगा। "?" - केवल एक वर्ण को प्रतिस्थापित करता है, अर्थात "मैस" निर्दिष्ट करना। एक "आप मान" मास "और वैल्यू" मास्क ", आदि के साथ लाइनों को जोड़ सकते हैं।
यदि मानदंड एक सेल में लिखा गया है और आपको अभी भी वाइल्डकार्ड वर्णों का उपयोग करने की आवश्यकता है, तो आप आवश्यक सेल जोड़कर इस सेल का लिंक बना सकते हैं। मान लें कि आपको उन मानों को योग करने की आवश्यकता है जिनमें "कुल" शब्द है। शब्द "कुल" सेल A1 में लिखा गया है, जबकि कॉलम A में "वर्तनी" शब्द के विभिन्न वर्तनी मूल्य हो सकते हैं: "जून के लिए योग", "जुलाई के लिए योग", "मार्च के लिए योग"। सूत्र तो इस तरह दिखना चाहिए:
= सारांश (A1: A20000; "*" और A1 & "*"; बी 1: B20000)
"*" & A1 & "*" - & संकेत (एम्परसेंड) कई मूल्यों को एक में जोड़ता है। यानी परिणाम "* परिणाम *" होगा।
सूत्र कैसे काम करते हैं, इसके सिद्धांत को समझने के लिए, गणना सूत्र का उपयोग करना बेहतर है: सूत्रों की गणना करने के लिए चरणों को कैसे देखें
तार्किक और गणितीय संकेतों के साथ सभी पाठकीय मानदंड और मानदंड दोहरे उद्धरण चिह्नों में संलग्न होने चाहिए (= SUMMESLI (A1, A20000; "कुल"; बी 1: B20000))। यदि मानदंड एक संख्या है, तो उद्धरण की आवश्यकता नहीं है। यदि आप सीधे प्रश्न चिह्न या तारांकन चिह्न ढूंढना चाहते हैं, तो आपको इसके सामने एक टिल्ड (~) लगाने की आवश्यकता है।
टिल्ड और इसकी विशेषताओं के बारे में इस लेख में पाया जा सकता है: तारांकन को कैसे बदलें / निकालें / खोजें? - Sum_Range (B1: B20000) (वैकल्पिक तर्क) - योग या संख्यात्मक मानों की सीमा को निर्दिष्ट करता है।
यह कैसे काम करता है: फ़ंक्शन मानदंड तर्क द्वारा निर्दिष्ट मान के लिए रेंज की खोज करता है, और जब एक मैच पाया जाता है, तो Range_Amount तर्क द्वारा संकेतित डेटा को sums। यानी यदि हमारे पास कॉलम A में एक विभाग का नाम और कॉलम B में एक राशि है, तो विकास विभाग को निर्दिष्ट करने के लिए मानदंड के रूप में कॉलम B के सभी मूल्यों का योग होगा, जिसके विपरीत विकास विभाग कॉलम A में पाया जाता है। वास्तव में, SumArrangement रेंज तर्क के समान आकार नहीं हो सकता है और यह फ़ंक्शन की त्रुटि का कारण नहीं होगा। हालांकि, जब समन के लिए कोशिकाओं को परिभाषित करते हैं, तो Range_Amount तर्क के शीर्ष बाएं सेल का उपयोग सारांश के लिए प्रारंभिक सेल के रूप में किया जाएगा, और फिर रेंज तर्क के आकार और आकार में संगत कोशिकाओं को समन किया जाएगा।
कुछ विशेषताएं
फ़ंक्शन का अंतिम तर्क (Sum_And_Band: B1: B20000) वैकल्पिक है। इसका मतलब है कि यह निर्दिष्ट नहीं किया जा सकता है। यदि आप इसे निर्दिष्ट नहीं करते हैं, तो फ़ंक्शन रेंज तर्क द्वारा निर्दिष्ट मूल्यों को जोड़ देगा। इसके लिए क्या है उदाहरण के लिए, आपको केवल उन संख्याओं का योग प्राप्त करने की आवश्यकता है जो शून्य से अधिक हैं। राशि के कॉलम ए में। तब फ़ंक्शन इस तरह दिखेगा:
= सारांश (A1: A20000; "> 0")
क्या माना जाना चाहिए: रेंज_सुमिंग और रेंज लाइनों की संख्या के बराबर होनी चाहिए। अन्यथा, आप गलत परिणाम प्राप्त कर सकते हैं। वैकल्पिक रूप से, यदि यह मेरे द्वारा दिए गए सूत्रों की तरह दिखाई देगा: समासों की सीमा और सीमा एक पंक्ति से शुरू होती है और इनमें समान पंक्तियाँ होती हैं: A1: A20000; बी 1: बी 20000
दो या अधिक मानदंडों द्वारा योग
लेकिन क्या करें जब योग 2 और अधिक के लिए मानदंड? मान लीजिए कि आपको केवल उन राशियों को योग करने की आवश्यकता है जो एक विभाग से संबंधित हैं और केवल एक निश्चित तिथि के लिए हैं। 2007 और उसके बाद के संस्करण के खुश मालिक SUMMESLIMN फ़ंक्शन का उपयोग कर सकते हैं:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing। पहला तर्क उन कोशिकाओं की श्रेणी को निर्दिष्ट करता है जिनमें एक में एकत्रित की जाने वाली मात्रा होती है।
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria। उन कक्षों की श्रेणी निर्दिष्ट करता है जिनमें आप किसी मैच के लिए मापदंड से खोजना चाहते हैं।
$ I $ 3, $ H8 - कसौटी। यहाँ, SUMMESLI में, वाइल्डकार्ड वर्ण * और ? अनुमति है । और वे उसी तरह काम करते हैं।
तर्कों को निर्दिष्ट करने की विशिष्टता: सबसे पहले, मानदंड सीमा निर्दिष्ट है (वे क्रमांकित हैं), फिर मूल्य (कसौटी) को सीधे सेमीकोलन में इंगित किया जाता है, जिसे इस सीमा में पाया जाना चाहिए - $ A $ 2: $ A $ 50; $ I $ 3। और कुछ नहीं। आपको पहले सभी सीमाओं को निर्दिष्ट करने की कोशिश नहीं करनी चाहिए, और फिर उनके लिए मानदंड - फ़ंक्शन या तो एक त्रुटि देगा, या यह योग नहीं करेगा जो आवश्यक है।
सभी शर्तों की तुलना सिद्धांत I के अनुसार की जाती है। इसका मतलब है कि यदि सभी सूचीबद्ध शर्तों को पूरा किया जाता है। यदि कम से कम एक शर्त पूरी नहीं होती है, तो फ़ंक्शन लाइन को छोड़ देता है और कुछ भी नहीं जोड़ता है।
SUMMERS के लिए, पंक्तियों की संख्या में योग और मानदंड समान होना चाहिए।
क्योंकि SUMMESLIMN केवल Excel के संस्करणों में दिखाई दिया, जो 2007 से शुरू हुआ, फिर ऐसे संस्करणों के दुखी उपयोगकर्ता ऐसे मामलों में कैसे हो सकते हैं? बहुत सरल: एक और फ़ंक्शन का उपयोग करें - SUMPRODUCT। मैं तर्कों को रंग नहीं दूंगा, क्योंकि उनमें से कई हैं और वे सभी मूल्यों के सरणियों हैं। यह फ़ंक्शन तर्कों द्वारा इंगित सरणियों को गुणा करता है। मैं इस फ़ंक्शन का उपयोग करने के सामान्य सिद्धांत का वर्णन करने का प्रयास करूंगा ताकि कई शर्तों पर डेटा को संक्षेप में प्रस्तुत किया जा सके।
समन समस्या को कई मानदंडों द्वारा हल करने के लिए, फ़ंक्शन इस तरह दिखेगा:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - तिथि सीमा। $ I $ 3 मानदंड की तारीख है जिसके लिए डेटा को योग करना आवश्यक है।
$ B $ 2: $ B $ 50 - विभागों के नाम। H5 - विभाग का नाम, जिस पर डेटा सम्मिलित होना चाहिए।
$ C $ 2: $ C $ 50 - राशियों के साथ सीमा।
हम तर्क का विश्लेषण करते हैं, क्योंकि कई लोगों के लिए, यह पूरी तरह से अस्पष्ट है इस समारोह को देखकर। यदि केवल इस मदद के कारण इस एप्लिकेशन का वर्णन नहीं किया गया है। अधिक पठनीयता के लिए, श्रेणियों का आकार कम करें:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
तो, अभिव्यक्ति ($ A $ 2: $ A $ 5 = $ I $ 3) और ($ B $ 2: $ B $ 5 = H5) तार्किक हैं और तार्किक FALSE और TRUE के रिटर्न सरणियाँ हैं। सही है अगर रेंज $ A $ 2: $ A $ 5 का सेल I $ 3 सेल के मान के बराबर है और रेंज $ B $ 2: $ B $ 5 का सेल H5 के मान के बराबर है। यानी हमारे पास निम्नलिखित हैं:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
जैसा कि आप देख सकते हैं, पहले सरणी में शर्त के लिए दो मैच हैं, और दूसरे में। इसके अलावा, इन दो सरणियों को गुणा किया जाता है (इसके लिए गुणा चिह्न (*) जिम्मेदार है)। जब गुणन होता है, तो सरणियों स्थिरांक 0 और 1, क्रमशः ({0; 1; 1; 0} * {0; 0; 1; 0}) में सारणी FALSE और TRUE का निहितार्थ रूपांतरण होता है। जैसा कि आप जानते हैं, जब शून्य से गुणा किया जाता है, तो हम शून्य हो जाते हैं। और परिणाम एक एकल सरणी है:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
फिर सरणी {0; 0; 1; 0} को श्रेणी $ C $ 2: $ C $ 50 में संख्याओं के एक सरणी से गुणा किया जाता है:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
और नतीजतन, हमें 30 मिलते हैं। हमें जो चाहिए था - हमें केवल वह राशि मिलती है जो कसौटी पर खरी उतरती है। यदि कसौटी पर खरा उतरने वाले एक से अधिक योग हैं, तो उन्हें सारांशित किया जाएगा।
SUMMYROIZV का लाभ
यदि तर्कों में गुणन चिह्न के बजाय प्लस चिह्न है:
($ ए $ २: $ ए $ ५ = $ आई $ ३) + ($ बी $ २: $ बी $ ५ = एच ५)
तब शर्तों की तुलना OR सिद्धांत के अनुसार की जाएगी: i.e. कुल रकम को कम से कम एक शर्त पूरी होने पर सम्मिलित किया जाएगा: या तो $ A $ 2: $ A $ 5 सेल वैल्यू $ I $ 3 या रेंज $ B $ 2 के सेल के बराबर है: $ B $ 5 सेल वैल्यू H5 के बराबर है।
यह SUMMESLIMN पर SUMMPRODUCT का लाभ है। SUMMESLIMN, OR सिद्धांत के अनुसार मानों को योग नहीं कर सकता, केवल AND सिद्धांत के अनुसार (सभी शर्तें पूरी होनी चाहिए)।
कमियों
SUMPRODUCT वाइल्डकार्ड * और? का उपयोग नहीं कर सकते अधिक सटीक रूप से उपयोग करना संभव है, लेकिन उन्हें विशेष पात्रों के रूप में नहीं, बल्कि एक तारांकन चिह्न और एक प्रश्न चिह्न के रूप में माना जाएगा। मुझे लगता है कि यह एक महत्वपूर्ण नुकसान है। और हालांकि यह बाईपास हो सकता है, मैं SUMPRODUCT के अंदर अन्य कार्यों का उपयोग करता हूं - यह अभी भी बहुत अच्छा होगा यदि फ़ंक्शन किसी तरह वाइल्डकार्ड का उपयोग कर सकता है।
उदाहरण में आपको ऊपर लिखे गए कार्यों की बेहतर समझ के लिए कार्यों के उदाहरणों के एक जोड़े मिलेंगे।
एक उदाहरण डाउनलोड करें
कई मानदंडों द्वारा राशि (41.5 KiB, 10,477 डाउनलोड)
यह भी देखें:
रंग भरकर कोशिकाओं को गर्म करना
फ़ॉन्ट रंग द्वारा कोशिकाओं का योग
सेल प्रारूप द्वारा कोशिकाओं को गर्म करना
रंग भरकर कोशिकाओं की मात्रा की गणना करें
फ़ॉन्ट रंग द्वारा कोशिकाओं की मात्रा की गणना करें
स्थिति सहित कई शीटों से डेटा कैसे जमा करें
वीडियो ट्यूटोरियल {"बॉटम बार": {"टैक्स्टस्टाइल": "स्टैटिक", "टैक्स्पोसिस्टैटिक": "बॉटम", "टेक्सटाउथोहाइड": ट्रू, "टेक्सटपोसेंमरगैनिस्टैटिक": 0, "टेक्स्टपेसिडेनमेटिक:" निचला "," टेक्सप्सिटामर्जिनफेट ": 24,"। " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easututCubic "," texteffectduration ": 600," texteffectlirectirection ":" निरूपण " : 30, "टेक्सटेक्टेक्टेलेडे": 500, "टेक्सटेक्टेक्टेपरेट": असत्य, "टेक्सटेक्टेक्ट 1": "स्लाइड", "टेक्सटेफ्लेक्लेरिडायरेसिओन 1": "राइट", "टेक्सटेक्फेसेलेरेडिस्टेंस 1": 120, "टेक्सटेक्फेसेलिंग 1": "ईज़ीऑक्टेक्टस" ; textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: निरपेक्ष; शीर्ष: 0px; बायां: 0px; चौड़ाई: 100%; height: 100% ; पृष्ठभूमि रंग: # 333333; अपारदर्शिता: 0.6; फिल्टर: एक lpha (अपारदर्शिता = 60); "," शीर्षक ":" प्रदर्शन: ब्लॉक; स्थिति: रिश्तेदार; फ़ॉन्ट: बोल्ड 14px \ "ल्यूसिडा सैंस यूनिकोड \", \ "ल्यूसिडा ग्रांडे \", संस-सेरिफ़, एरियल; रंग: #fff; "," विवरण ":" प्रदर्शन: ब्लॉक; स्थिति: रिश्तेदार; फ़ॉन्ट: 12px \ "ल्यूसिडा सैंस यूनिकोड \", \ "ल्यूसिडा ग्रैंड \", संस-सेरिफ़, एरियल; रंग: #fff; मार्जिन-टॉप: 8px; "," बटन ":" प्रदर्शन: ब्लॉक; स्थिति: रिश्तेदार; मार्जिन-टॉप: 8px; "," टेक्सटेफ़ेक्ट्रेस्पोंसिव ": ट्रू," टेक्सटेक्क्ट्रेस्पोन्सिवेसाइज़ "": 640, "टाइटलस्क्रेस्पोन्सिव": "फॉन्ट-साइज़: 12 पीएक्स;", "डिस्क्रैस्प्रेसिव्सिव": "डिस्प्ले: कोई नहीं: महत्वपूर्ण;", "बटनस्क्रीमस्पॉन्सिव": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}