संदेश बोर्ड में सामान्य प्रश्नों में से एक यह है कि दो अलग-अलग स्थितियों के साथ SumIf फ़ंक्शन का उपयोग कैसे किया जाए। दुर्भाग्य से, इसका उत्तर यह है कि SumIf दो अलग-अलग स्थितियों को संभाल नहीं सकता है।
दो शर्तों को करने के लिए, आपको एक जटिल सरणी सूत्र का उपयोग करना होगा। सशर्त योग विज़ार्ड ऐड-इन आपको इन जटिल फ़ार्मुलों को आसानी से दर्ज करने की अनुमति देता है।
यहां उत्पाद, बिक्री प्रतिनिधि और बिक्री के लिए कॉलम के साथ एक एक्सेल वर्कशीट है। डेटा A2: C29 कोशिकाओं में है।
यदि आप कुल बिक्री करना चाहते हैं, तो एक सरल SUM () फ़ंक्शन काम करेगा। =SUM(C2:C29)।
कई महानुभाव सुमी फंक्शन की खोज करते हैं। इस फ़ंक्शन का उपयोग करना, उत्पाद एबीसी के लिए कुल बिक्री का पता लगाना काफी आसान है।=SUMIF(A2:A29,E2,C2:C29)
बिक्री प्रतिनिधि द्वारा किए गए बिक्री की कुल संख्या का पता लगाना भी आसान है =SUMIF(B2:B29,E2,C2:C29)।
फिर आप मानेंगे कि जो एबीसी द्वारा बनाए गए उत्पाद एबीसी की कुल बिक्री का पता लगाना संभव है। हालाँकि, SumIf फ़ंक्शन के साथ ऐसा करने का कोई तरीका नहीं है। यह पता चला है कि आपको एक काफी जटिल सरणी या CSE सूत्र का उपयोग करने की आवश्यकता है।
आइए इसका सामना करते हैं - योग सूत्र Excel 101 है। SumIf सूत्र जटिलता में बहुत पीछे नहीं है। हालांकि, जोए द्वारा किए गए कुल एबीसी बिक्री की गणना करने के लिए सीएसई सूत्र मेरे सिर को भी स्पिन करने के लिए पर्याप्त है।
अच्छी खबर - Microsoft सशर्त सम विज़ार्ड प्रदान करता है जो 1, 2 या अधिक स्थितियों के आधार पर जटिल नौसिखिया सूत्रों में प्रवेश करने की अनुमति देता है। सशर्त योग विज़ार्ड एक ऐड-इन है। इस कार्यक्षमता को एक्सेल में जोड़ने के लिए, टूल मेनू पर जाएं और ऐड-इन का चयन करें। ऐड-इन संवाद बॉक्स में, सशर्त सम विज़ार्ड के आगे चेकबॉक्स चुनें और ठीक चुनें। यह संभव है कि इस बिंदु पर आपको अपनी स्थापना सीडी की आवश्यकता हो, क्योंकि Microsoft डिफ़ॉल्ट इंस्टॉल में विज़ार्ड को शामिल नहीं करता है।
एक बार ऐड-इन सफलतापूर्वक चालू हो जाने के बाद, टूल मेनू के निचले भाग के पास एक Conditonal Sum … विकल्प होगा।
अपने डेटासेट में एकल कक्ष चुनें और टूल चुनें - सशर्त योग। यह मानते हुए कि आपका डेटा शीर्ष पंक्ति की एक पंक्ति के साथ अच्छी तरह से स्वरूपित है, एक्सेल आपके डेटा की सीमा का ठीक से अनुमान लगाएगा। अगला चुनें।
चरण 2 में, योग करने के लिए कॉलम चुनें। इस मामले में, विज़ार्ड ने पहले ही अनुमान लगा लिया है कि आप पहले (और केवल) संख्यात्मक कॉलम - बिक्री का योग करना चाहते हैं। संवाद बॉक्स के बीच में तीन ड्रॉपडाउन नियंत्रण हैं। ये पहली स्थिति के लिए सही होते हैं - उत्पाद एबीसी के बराबर होता है, इसलिए जोड़ें स्थिति बटन चुनें।
तब आप अपनी दूसरी शर्त जोड़ सकते हैं। इस स्थिति में, आप यह निर्दिष्ट करना चाहते हैं कि बिक्री प्रतिनिधि जो है। पहली ड्रॉपडाउन के लिए तीर चुनें। एक्सेल उपलब्ध कॉलम नामों की एक वर्णमाला सूची प्रदान करता है। बिक्री प्रतिनिधि चुनें।
केंद्र ड्रॉपडाउन सही है, लेकिन यहां पूर्णता के लिए, आप देख सकते हैं कि आप बराबर, कम से कम, अधिक से अधिक या बराबर, इससे अधिक या बराबर, या नहीं के बराबर, चुन सकते हैं।
तीसरी ड्रॉपडाउन से, जो चुनें।
शर्त जोड़ें बटन चुनें।
अब आप चरण 3 पर जाने के लिए तैयार हैं। अगला बटन दबाएं।
चरण 3 में, आपके पास दो विकल्प हैं। पहली पसंद में, विज़ार्ड "एबीसी" और "जो" मानों के साथ एकल सूत्र में सूत्र में हार्डकोड करेगा। यह आपको उत्तर देगा, लेकिन सूत्र को आसानी से बदलने का कोई अवसर नहीं होगा। दूसरी पसंद के साथ, एक्सेल "एबीसी" मूल्य के साथ एक नया सेल स्थापित करेगा और मूल्य "जो" के साथ एक नया सेल। एक तीसरी कोशिका में वह सूत्र होगा जो उन दो मूल्यों के आधार पर एक सशर्त योग करता है। इस विकल्प के साथ, आप कोशिकाओं में नए मान टाइप कर सकते हैं ताकि आदम द्वारा कुल XYZ को बेचा जा सके।
विज़ार्ड तब पूछेगा कि आप एबीसी के लिए मान कहाँ चाहते हैं। एक सेल का चयन करें और अगला चुनें। दोहराएं क्योंकि विज़ार्ड आपको जो और सूत्र के लिए एक सेल का चयन करने के लिए कहता है।
जब आप अंतिम चरण में समाप्त चुनते हैं, तो एक्सेल CSE सूत्र का थोड़ा अलग (लेकिन मान्य) संस्करण बनाएगा।
यह सूत्र इस बात की गणना करता है कि जो एबीसी की $ 33,338 बेची गई है।
यदि आप उत्पाद इनपुट सेल को ABC से DEF में बदलते हैं, तो सूत्र यह दर्शाने के लिए फिर से गणना करेगा कि जो DEF के $ 24,478 में बेचा गया है।
सशर्त योग विज़ार्ड सभी Excel स्वामियों की पहुंच के भीतर जटिल फ़ार्मुलों को अच्छी तरह से रखता है।
अतिरिक्त जानकारी:यदि आप एक तालिका का निर्माण करना चाहते हैं जो प्रत्येक बिक्री प्रतिनिधि द्वारा प्रत्येक उत्पाद की बिक्री दिखाएगी, तो कुछ विशेष "देखभाल और खिलाने" हैं जो आपको इन सूत्रों के बारे में जानने की आवश्यकता होगी। श्रेणी के शीर्ष पर प्रत्येक बिक्री प्रतिनिधि टाइप करें। प्रत्येक उत्पाद को श्रेणी के बाएं कॉलम के नीचे लिखें। विज़ार्ड द्वारा प्रदान किए गए सूत्र को संपादित करें। नीचे दी गई छवि में, सूत्र सेल E6 में उत्पाद को इंगित कर रहा है। यह संदर्भ वास्तव में $ E6 होना चाहिए। यदि आप संदर्भ को E6 के रूप में छोड़ते हैं और सूत्र को स्तंभ G पर कॉपी करते हैं, तो सूत्र E6 के बजाय F6 को देखेगा और यह गलत होगा। E6 में E से पहले एक डॉलर का चिह्न जोड़ना यह सुनिश्चित करेगा कि सूत्र हमेशा स्तंभ E में उत्पाद को देखता है। सूत्र भी F5 में बिक्री प्रतिनिधि को इंगित कर रहा है। इस संदर्भ को वास्तव में F $ 5 होना चाहिए। यदि आप संदर्भ को F5 के रूप में छोड़ देते हैं और पंक्ति 7 में कॉपी करते हैं,F5 संदर्भ F6 में बदल जाएगा और यह सही नहीं है। पंक्ति संख्या को लॉक करने से पहले एक डॉलर का चिह्न जोड़ना पंक्ति संख्या को संदर्भित करेगा और संदर्भ हमेशा पंक्ति 5 को इंगित करेगा।
संपादन मोड में (सेल का चयन करें और संपादित करने के लिए F2 दबाएं), E से पहले एक $ लिखें। F5 में 5 से पहले एक डॉलर का चिह्न लिखें। अभी तक दर्ज नहीं मारा!
यह सूत्र एक विशेष प्रकार का सूत्र है। यदि आप Enter दबाते हैं, तो आपको एक 0 मिलेगा, जो सही नहीं है।
Enter टाइप करने के बजाय, Enter दबाते समय Ctrl और Shift कुंजी दबाए रखें। C trl + S hift + E nter का यह जादुई संयोजन है, इसलिए मैं इन CSE फॉर्मूलों को कॉल करता हूं।
बाकी तालिका के फार्मूले की नकल करने से पहले एक अंतिम विचार है। आपका झुकाव F6 की प्रतिलिपि बनाने और F6: G8 पर चिपकाने का हो सकता है। यदि आप यह कोशिश करते हैं, तो एक्सेल आपको "आप कैन नॉट चेंज पार्ट ऑफ अ अर्रे" संदेश दे सकते हैं। एक्सेल शिकायत कर रहा है कि आप एक सीएसई सूत्र को उस सीमा में नहीं चिपका सकते हैं जिसमें मूल सीएसई सूत्र हो।
इसके आसपास काम करना आसान है। F6 कॉपी करें। F7 को चिपकाएँ: F8।
कॉपी F6: F8 G6 को चिपकाएँ: G8 आपके पास दो शर्तों के आधार पर योग दिखाने वाले CSE फ़ार्मुलों की एक तालिका होगी।
