एक्सेल सूत्र: लुकअप सबसे कम सोमवार ज्वार -

सारांश

सोमवार को सबसे कम ज्वार का पता लगाने के लिए, कई दिनों के उच्च और निम्न ज्वार के साथ डेटा का एक सेट दिया गया, आप IF और MIN फ़ंक्शन के आधार पर एक सरणी सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, I6 में सूत्र है:

(=MIN(IF(day=I5,IF(tide="L",pred))))

जो डेटा में सबसे कम सोमवार ज्वार को लौटाता है, -0.64

सबसे कम सोमवार ज्वार की तारीख को प्राप्त करने के लिए, I7 में सूत्र है:

(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))

: जहां कार्यपत्रक निम्नलिखित नामित श्रेणियों में शामिल तिथि (B5: B124), दिन (सी 5: C124), समय (D5: D124), pred (E5: E124), ज्वार (एफ 5: F124)।

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

सांता क्रूज़, कैलिफोर्निया के लिए tidesandcurrents.noaa.gov से डेटा।

स्पष्टीकरण

उच्च स्तर पर, यह उदाहरण कई मानदंडों के आधार पर न्यूनतम मूल्य खोजने के बारे में है। ऐसा करने के लिए, हम दो नेस्टेड IF फ़ंक्शन के साथ MIN फ़ंक्शन का उपयोग कर रहे हैं:

(=MIN(IF(day=I5,IF(tide="L",pred))))

अंदर से बाहर काम करना, पहला IF जाँचता है कि क्या दिन "सोम" है, I5 के मान के आधार पर:

IF(day=I5 // is day "Mon"

यदि परिणाम TRUE है, तो हम एक और IF चलाते हैं:

IF(tide="L",pred) // if tide is "L" return prediction

दूसरे शब्दों में, यदि दिन "सोम" है, तो हम जांचते हैं कि क्या ज्वार "एल" है। यदि हां, तो हम नामांकित श्रेणी का उपयोग कर भविष्यवाणी की ज्वार स्तर लौटने के लिए, pred ।

सूचना हम IF के लिए "गलत होने पर" मान प्रदान नहीं करते हैं। इसका मतलब है कि या तो तार्किक परीक्षण FALSE है, बाहरी IF FALSE लौटाएगा। नेस्टेड IFs के बारे में अधिक जानकारी के लिए, इस लेख को देखें।

यह समझना महत्वपूर्ण है कि डेटा सेट में 120 पंक्तियाँ शामिल हैं, इसलिए सूत्र में नामित प्रत्येक श्रेणी में 120 मान शामिल हैं। यह वही है जो इसे एक सरणी सूत्र बनाता है - हम एक साथ कई मानों को संसाधित कर रहे हैं। दोनों IF का मूल्यांकन होने के बाद, बाहरी IF एक सरणी लौटाएगा जिसमें 120 मान इस तरह होंगे:

(FALSE;FALSE;FALSE;FALSE;FALSE;3.27;FALSE;0.3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;2.02;FALSE;0.17;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;3.04;FALSE;-0.55;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1.96;FALSE;-0.64;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;3;FALSE;-0.02;FALSE;FALSE;FALSE;FALSE)

यहां ध्यान देने योग्य बात यह है कि केवल सोमवार और कम ज्वार से जुड़े मूल्य ही नेस्टेड आईएफएस के माध्यम से यात्रा से बचते हैं। अन्य मूल्यों को FALSE के साथ बदल दिया गया है। दूसरे शब्दों में, हम उन मूल्यों को दोहरा रहे हैं, जिन्हें हम दिलचस्पी नहीं लेते हैं।

ऊपर दिए गए सरणी को MIN फ़ंक्शन पर सीधे लौटा दिया गया है। MIN फ़ंक्शन स्वचालित रूप से FALSE मानों को अनदेखा करता है, और उन लोगों का न्यूनतम मूल्य लौटाता है जो रहते हैं, -0.64।

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

न्यूनतम के साथ न्यूनतम

यदि आपके पास Office 365 या Excel 2019 है, तो आप इस तरह से न्यूनतम सोमवार का ज्वार प्राप्त करने के लिए MINIFS फ़ंक्शन का उपयोग कर सकते हैं:

=MINIFS(pred,day,"Mon",tide,"L")

परिणाम समान है, और इस सूत्र को नियंत्रण + शिफ्ट + दर्ज करने की आवश्यकता नहीं है।

दिनांक प्राप्त करें

एक बार जब आप न्यूनतम सोमवार ज्वार का स्तर पाते हैं, तो आप निस्संदेह तारीख और समय जानना चाहेंगे। यह एक INDEX और MATCH फॉर्मूला के साथ किया जा सकता है। I7 में सूत्र है:

(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))

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

MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))

यहां, हम उन्हीं सशर्त परीक्षणों के माध्यम से चलते हैं जिन्हें हमने ऊपर लागू किया है ताकि प्रसंस्करण को केवल सोमवार कम ज्वार तक सीमित रखा जा सके। हालाँकि, हम I6 में परिणाम को न्यूनतम मान तक सीमित करने के लिए एक और परीक्षण लागू करते हैं, और हम मापदंड लागू करने के लिए बूलियन तर्क के आधार पर थोड़ा सरल सिंटैक्स का उपयोग करते हैं। हमारे पास तीन अलग-अलग अभिव्यक्तियाँ हैं, प्रत्येक परीक्षण एक शर्त:

(day=I5)* // day is "Mon" (tide="L")* // tide is "L" (pred=I6) // prediction is min value

Each of these expressions runs on 120 values and returns an array of 120 TRUE FALSE results. When these arrays are multiplied by one another, the TRUE FALSE values are coerced to 1s and 0s. The result is a single array like this:

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

Because there is only one value in the entire data set that meets all three conditions, there is only a single 1 in the array.

Now you can see why we have configured the MATCH function to look for the number 1 in exact match mode. MATCH locates the 1, and returns a position of 88 directly to the INDEX function. We can now rewrite the formula like this:

=INDEX(date,88) // returns 23-Dec-19

The INDEX function then returns the 88th value in the named range date, which is 23-Dec-19. This is the date that corresponds to the lowest Monday tide level.

This is an array formulas and must be entered with control + shift + enter.

Get the time

The formula to retrieve the time of the lowest Monday tide is almost the same as the formula to get the date. The only difference is that the named range time is provided to INDEX instead of date. The formula in I8 is:

(=INDEX(time,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))

In other respects the behavior of the formula is the same, so we end up with a similar result:

=INDEX(time,88) // returns 2:44 PM

As before, INDEX returns the 88th item in the array, which is 2:44 PM.

This is an array formulas and must be entered with control + shift + enter.

Note: in the event of a tie (two Monday low tides with the same value), the INDEX and MATCH formulas above will return the first match.

Date and time with XLOOKUP

With the XLOOKUP function, you can simplify the formulas used to get the date and time associated with the lowest tide:

=XLOOKUP(1,(day=I5)*(tide="L")*(pred=I6),date) // get date =XLOOKUP(1,(day=I5)*(tide="L")*(pred=I6),time) // get time

यह एक उदाहरण है जो अच्छी तरह से XLOOKUP के लचीलेपन को दिखाता है। हम INDEX और MATCH फ़ार्मुलों से बिल्कुल एक ही तर्क का उपयोग सरल और सुरुचिपूर्ण सूत्र में कर सकते हैं।