एक्सेल सूत्र: कॉलम में महीने के योग -

सारांश

कॉलम में महीने के हिसाब से योग करने के लिए आप EOMONTH फ़ंक्शन के साथ SUMIFS फ़ंक्शन का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, G5 में सूत्र है:

=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))

यह सूत्र तीन नामित श्रेणियों का उपयोग करता है: "राशि" (D5: D15), "ग्राहक" (B5: B15), और "दिनांक" (C5: C15)।

स्पष्टीकरण

SUMIFS फ़ंक्शन को एक या अधिक मानदंडों के आधार पर एक सीमा में मानों को योग करने के लिए डिज़ाइन किया गया है। इस मामले में, हमें तीन मापदंड चाहिए:

1. क्लाइंट का नाम = कॉलम F में क्लाइंट
2. दिनांक> = महीने का पहला (पंक्ति 4 में तारीख से)
3. तारीख <= महीने का अंत (पंक्ति 4 में तारीख से)

मुख्य बिंदु: G4 में महीने के नाम: I4 वास्तव में मान्य दिनांक हैं, जो कस्टम संख्या प्रारूप "एमएमएम" के साथ प्रारूपित हैं। यह हमें G4: I4 में दिनांक मानों को सीधे मानदंड # 2 और # 3 के लिए उपयोग करने की अनुमति देता है।

SUMIFS में वापस, पहली श्रेणी / मानदंड जोड़ी का उपयोग क्लाइंट नाम की जांच करने के लिए किया जाता है:

client,$F5

सूचना F5 एक मिश्रित संदर्भ है, जिसमें स्तंभ लॉक है। यह तालिका के माध्यम से सूत्र को कॉपी करने के रूप में पंक्ति को बदलने की अनुमति देता है, लेकिन क्लाइंट का नाम हमेशा कॉलम एफ से आता है।

दूसरी श्रेणी / मापदंड जोड़ी का उपयोग महीने के पहले के खिलाफ तारीखों की जांच करने के लिए किया जाता है:

date,">="&G$4

जैसा कि ऊपर उल्लेख किया गया है, जी 4: आई 4 में मूल्य वास्तव में दिनांक हैं: जनवरी 1, 2019, फरवरी 1, 2019 और मार्च 1, 2019। इसलिए, यह मानदंड पंक्ति में पहले महीने की तारीखों की तुलना में अधिक तारीखों की जांच करता है। सूचना है कि यह एक और मिश्रित संदर्भ है, इस बार पंक्ति बंद होने के साथ। यह कॉलम को तालिका में कॉपी किए जाने के रूप में बदलने की अनुमति देता है, लेकिन पंक्ति संख्या को नियत रखता है। जब एक तार्किक ऑपरेटर और किसी अन्य सेल से मान का उपयोग करने वाले मानदंड का निर्माण करते हैं तो एक एम्परसेंड (और) के साथ तालमेल आवश्यक होता है।

तीसरी सीमा / मानदंड जोड़ी का उपयोग महीने के अंतिम दिन के खिलाफ तारीखों की जांच करने के लिए किया जाता है:

date,"<="&EOMONTH(G$4,0)

प्रत्येक महीने के अंतिम दिन को प्राप्त करने के लिए, हम पंक्ति 4 में स्तंभ शीर्षलेख से दिनांक पर EOMONTH फ़ंक्शन का उपयोग करते हैं। महीने के तर्क के लिए प्रदान किए गए शून्य के साथ, EOMONTH उसी महीने के अंतिम दिन को लौटाता है। फिर से, पंक्ति बदलने के लिए G4 के संदर्भ को मिलाया जाता है।

धुरी तालिका समाधान

इस समस्या के लिए एक पिवट टेबल एक उत्कृष्ट समाधान होगा, क्योंकि यह बिना किसी फॉर्मूले के अपने आप ही महीने तक समूह बना सकता है। सूत्र बनाम पिवट टेबल की साइड-बाय-साइड तुलना के लिए, यह वीडियो देखें: पिवट टेबल क्यों।