फॉर्मूला पहेली - साल के हिसाब से भुगतान - पहेली

एक पाठक ने मुझे इस सप्ताह एक दिलचस्प सूत्र समस्या भेजी, तो सोचा कि मैं इसे एक सूत्रीय चुनौती के रूप में साझा करूंगा। समस्या यह है:

आपके पास एक निश्चित मासिक भुगतान, एक आरंभ तिथि और कुछ महीनों की संख्या है। निम्न कार्यपत्रक के आधार पर आप वर्ष तक कुल भुगतान का उपयोग करने के लिए किस सूत्र का उपयोग कर सकते हैं:

दूसरे शब्दों में, E5 में कौन सा फॉर्मूला काम करता है, जिसे I5 में कॉपी किया गया है, प्रत्येक वर्ष के लिए राशि प्राप्त करने के लिए दिखाया गया है?

मैं खुद एक फॉर्मूला लेकर आया था, लेकिन मुझे आपके विचारों को देखना अच्छा लगेगा। यदि आप रुचि रखते हैं, तो उस फ़ॉर्मूले के साथ एक टिप्पणी छोड़ दें जिसे आप प्रस्तावित करते हैं।

आप अपने सूत्र में निम्नलिखित नामित श्रेणियों का उपयोग कर सकते हैं यदि आपको पसंद है: mos (C5), राशि (C6), प्रारंभ (C7), अंत (C8)।

आप नीचे वर्कशीट डाउनलोड कर सकते हैं।

उत्तर (विस्तार के लिए क्लिक करें)

इतने महान सूत्र! जवाब प्रस्तुत करने के लिए समय निकालने वाले सभी का धन्यवाद। समस्या के बारे में नीचे दिए गए कुछ उपाय और नीचे दिए गए मेरे विचार हैं।

नोट: मैंने कभी स्पष्ट नहीं किया कि महीने की सीमाओं को कैसे संभाला जाना चाहिए। मैं उदाहरण के रूप में सिर्फ एक और वर्कशीट का अनुसरण कर रहा था। उन्हें मुख्य जानकारी 30 भुगतान है, 1 मार्च से शुरू होकर 2017 में 10 भुगतान, 2018 में 12 भुगतान और 2019 में 8 भुगतान (शेष)।

इसलिए, यदि आप यह समझने के लिए संघर्ष कर रहे हैं कि आप इस तरह की समस्या को हल करने का प्रयास कैसे कर सकते हैं, तो पहले भुगतान पर ध्यान दें। एक बार जब आप जानते हैं कि एक वर्ष में कितने भुगतान होते हैं, तो आप उस संख्या को राशि से गुणा कर सकते हैं और आप कर सकते हैं।

तो, आप किसी दिए गए वर्ष में भुगतानों की संख्या कैसे पा सकते हैं? नीचे दी गई टिप्पणियों में आपको बहुत सारे अच्छे विचार मिलेंगे। मेरे द्वारा देखे गए कई पैटर्न हैं, और मैंने कुछ नीचे सूचीबद्ध किया है। यह काम प्रगति पर है…

डिजाइन पैटर्न्स

IF + AND/OR + YEAR + MONTH

IF इतने फॉर्मूलों में एक भरोसेमंद स्टैंडबाय है, और इसका उपयोग कई सुझाए गए फॉर्मूलों में किया जाता है ताकि यह पता लगाया जा सके कि ब्याज का वर्ष शुरू और समाप्ति तारीखों के "सीमा में" है। कई मामलों में, सूत्र सूत्रों को संक्षिप्त रखने के लिए OR या AND के साथ संयुक्त है।

IFERROR + DATEDIF + MAX + MIN

DatedIF महीनों में दो तिथियों के बीच के अंतर को वापस कर सकता है, इसलिए प्रत्येक वर्ष के लिए प्रारंभ तिथि और समाप्ति तिथि की गणना करने के लिए यहां MAX और MIN (संक्षिप्त के लिए, IF के बजाय) का उपयोग करने का विचार है, और DatedIF को महीनों के बीच पाने दें। DatedIF एक #NUM त्रुटि फेंकता है जब प्रारंभ दिनांक अंतिम तिथि से कम नहीं होती है, इसलिए IFERROR का उपयोग त्रुटि को पकड़ने और शून्य पर लौटने के लिए किया जाता है। नीचे form 闫, अरुण और डेविड द्वारा सूत्र देखें।

MAX + MIN + YEAR + MONTH

रॉबर्ट और पीटर के सूत्र लगभग सभी काम मैक्स और एमआईएन के साथ करते हैं, बिना आईएफ के दृष्टि से। गजब का। यदि IF को प्रतिस्थापित करने के लिए MAX और MIN का उपयोग करने का विचार आपके लिए नया है, तो यह आलेख अवधारणा की व्याख्या करता है।

DAYS360

Excel DAYS360 फ़ंक्शन 360-दिन के वर्ष के आधार पर दो तिथियों के बीच दिनों की संख्या लौटाता है। यह इस विचार के आधार पर महीनों की गणना करने का एक तरीका है कि हर महीने में 30 दिन हैं।

SUM + DATE

यह DATE फ़ंक्शन और प्रत्येक महीने के लिए एक संख्या के साथ एक सरणी का उपयोग करके मेरा अक्षम (लेकिन सुरुचिपूर्ण!) दृष्टिकोण है। DATE फ़ंक्शन एक सरणी स्थिरांक का उपयोग करते हुए एक वर्ष के प्रत्येक महीने के लिए एक तारीख तक घूमता है, और बूलियन तर्क का उपयोग ओवरलैप की जांच करने के लिए किया जाता है।