एक्सेल फॉर्मूला: रैंक मान महीने के हिसाब से -

सारांश

एक संख्यात्मक मान द्वारा रैंक किए गए नामों की सूची प्रदर्शित करने के लिए, आप पाठ फ़ंक्शन की मदद से LARGE, INDEX, MATCH के आधार पर सूत्रों का एक सेट का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, G5 में सूत्र है:

=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)

और जी 10 में सूत्र है:

=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))

जहाँ ग्राहक (B5: B17) दिनांक (C5: C17) और राशि (C5: C17) को श्रेणी कहा जाता है।

नोट: ये सरणी सूत्र हैं और इन्हें एक्सेल 365 को छोड़कर कंट्रोल + शिफ्ट + एंटर के साथ दर्ज किया जाना चाहिए।

स्पष्टीकरण

यह उदाहरण स्पष्टता के लिए दो भागों में स्थापित किया गया है: (1) प्रत्येक माह के लिए शीर्ष 3 राशियों को निर्धारित करने का सूत्र और (2) शीर्ष 3 मासिक राशियों में से प्रत्येक के लिए ग्राहक नाम को पुनः प्राप्त करने का सूत्र।

ध्यान दें कि स्रोत डेटा में कोई वास्तविक रैंक नहीं है। इसके बजाय, हम मात्राओं के साथ सीधे काम करने के लिए LARGE फ़ंक्शन का उपयोग कर रहे हैं। RANK फ़ंक्शन के साथ स्रोत डेटा में रैंक जोड़ने के लिए एक और दृष्टिकोण होगा, और क्लाइंट नामों को पुनः प्राप्त करने के लिए रैंक मान का उपयोग करना होगा।

भाग 1: प्रत्येक माह शीर्ष 3 राशियों को पुनः प्राप्त करें

प्रत्येक सप्ताह के लिए शीर्ष 3 राशियों को पुनः प्राप्त करने के लिए, G5 में सूत्र है:

=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)

नोट: यह एक सरणी सूत्र है और इसे Excel 365 को छोड़कर नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।

अंदर से बाहर काम करते हुए, हम पहली बार नामित तिथि सीमा में प्रत्येक तिथि के लिए महीने के नाम प्राप्त करने के लिए TEXT फ़ंक्शन का उपयोग करते हैं :

TEXT(date,"mmmm") // get month names

कस्टम नंबर फॉर्मेट "mmmm" नाम की श्रेणी की तारीख में प्रत्येक नाम के लिए "अप्रैल", "मे", "जून" जैसे स्ट्रिंग लौटाएगा । परिणाम इस तरह महीने के नाम की एक सरणी है:

("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")

TEXT फ़ंक्शन इस सरणी को IF फ़ंक्शन में वितरित करता है, जिसे किसी दिए गए महीने में दिनांक को G4 में मान के विरुद्ध परीक्षण करके दिनांक को फ़िल्टर करने के लिए कॉन्फ़िगर किया गया है (एक मिश्रित संदर्भ, इसलिए सूत्र नीचे और आर-पार कॉपी किया जा सकता है):

IF(TEXT(date,"mmmm")=G$4,amount) // filter on month

अप्रैल में केवल राशियाँ जीवित रहती हैं और इसे IF के माध्यम से बनाती हैं; अन्य सभी मूल्य FALSE हैं:

(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)

अंत में, LARGE फ़ंक्शन F5 (भी एक मिश्रित संदर्भ) में मूल्य का उपयोग करता है जो "एनटीटी" सबसे बड़ा मूल्य है जो वापस रहता है। सेल G5 में, LARGE 18,500, "1" सबसे बड़ा मूल्य देता है। जैसा कि सूत्र नीचे और तालिका में कॉपी किया गया है, LARGE फ़ंक्शन तीन महीनों में से प्रत्येक में शीर्ष 3 मात्रा देता है।

अब जब हम प्रत्येक माह में शीर्ष 3 मूल्यों को जानते हैं, तो हम प्रत्येक के लिए क्लाइंट नाम को पुनः प्राप्त करने के लिए "कुंजी" जैसी इस जानकारी का उपयोग कर सकते हैं।

भाग 2: ग्राहक के नाम पुनः प्राप्त करें

नोट: यह कई मानदंडों के साथ INDEX और MATCH का उपयोग करने का एक उदाहरण है। यदि यह अवधारणा आपके लिए नई है, तो यहां एक मूल उदाहरण है।

G5: I7 में शीर्ष तीन मूल्यों से जुड़े नाम को पुनः प्राप्त करने के लिए, हम INDEX और MATCH का उपयोग करते हैं:

=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))

नोट: यह एक सरणी सूत्र है और इसे Excel 365 को छोड़कर नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।

अंदर से बाहर काम करना, इस तरह से बूलियन तर्क का उपयोग करने के लिए MATCH फ़ंक्शन कॉन्फ़िगर किया गया है:

MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)

लुकअप मान 1 है, और लुकअप सरणी इस अभिव्यक्ति के साथ निर्मित है:

(amount=G5)*(TEXT(date,"mmmm")=G$9)

लुकअप ऐरे बनाने वाली अभिव्यक्ति बूलियन लॉजिक का उपयोग "फ़िल्टर आउट" करने के लिए करती है जो कि (1) अप्रैल में नहीं है, और (2) G5 (18,500) में मान नहीं है। परिणाम इस तरह 1s और 0s की एक सरणी है:

(0;0;1;0;0;0;0;0;0;0;0;0;0)

मैच प्रकार के लिए 1 और शून्य के लुकअप मान के साथ (सटीक मैच के लिए) MATCH 3 को सीधे INDEX फ़ंक्शन पर लौटाता है:

=INDEX(client,3) // returns "Janus"

INDEX नामित ग्राहक, "जानूस" में तीसरा मान लौटाता है।

जैसा कि सूत्र नीचे और तालिका में कॉपी किया गया है, यह प्रत्येक तीन महीनों में शीर्ष 3 क्लाइंट लौटाता है।