Podsumowanie
Aby znaleźć najniższy przypływ w poniedziałek, biorąc pod uwagę zestaw danych z wieloma dniami przypływów i odpływów, możesz użyć formuły tablicowej opartej na funkcjach JEŻELI i MIN. W pokazanym przykładzie formuła w I6 to:
(=MIN(IF(day=I5,IF(tide="L",pred))))
co zwraca najniższy poniedziałkowy przypływ w danych, -0,64
Aby pobrać datę najniższego poniedziałkowego przypływu, wzór w I7 jest następujący:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Gdzie arkusz zawiera następujące nazwane zakresy: data (B5: B124), dzień (C5: C124), godzina (D5: D124), pred (E5: E124), przypływ (F5: F124).
Oba są formułami tablicowymi i należy je wprowadzić za pomocą Ctrl + Shift + Enter.
Dane z tidesandcurrents.noaa.gov dla Santa Cruz w Kalifornii.
Wyjaśnienie
Na wysokim poziomie ten przykład dotyczy znalezienia wartości minimalnej na podstawie wielu kryteriów. Aby to zrobić, używamy funkcji MIN wraz z dwiema zagnieżdżonymi funkcjami JEŻELI:
(=MIN(IF(day=I5,IF(tide="L",pred))))
pracując od środka na zewnątrz, pierwszy IF sprawdza, czy dzień to „Pon” na podstawie wartości w I5:
IF(day=I5 // is day "Mon"
Jeśli wynik jest PRAWDA, uruchamiamy kolejny JEŻELI:
IF(tide="L",pred) // if tide is "L" return prediction
Innymi słowy, jeśli dzień to „Pon”, sprawdzamy, czy przypływ to „L”. Jeśli tak, zwracamy przewidywany poziom pływów, używając nazwanego zakresu pred .
Zwróć uwagę, że nie podajemy „wartości, jeśli fałsz” dla żadnego z nich. Oznacza to, że jeśli którykolwiek z testów logicznych ma wartość FALSE, zewnętrzny IF zwróci FALSE. Aby uzyskać więcej informacji na temat zagnieżdżonych IF, zobacz ten artykuł.
Należy pamiętać, że zestaw danych zawiera 120 wierszy, więc każdy z nazwanych zakresów w formule zawiera 120 wartości. To właśnie sprawia, że jest to formuła tablicowa - przetwarzamy wiele wartości naraz. Po oszacowaniu obu JEŻELI zewnętrzny JEŻELI zwróci tablicę zawierającą 120 wartości, takich jak ta:
(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)
Kluczową rzeczą, na którą należy zwrócić uwagę, są tylko wartości związane z poniedziałkiem i odpływem, które przetrwają podróż przez zagnieżdżone IF. Pozostałe wartości zostały zastąpione FALSE. Innymi słowy, używamy podwójnej struktury JEŻELI, aby „wyrzucić” wartości, którymi nie jesteśmy zainteresowani.
Powyższa tablica jest zwracana bezpośrednio do funkcji MIN. Funkcja MIN automatycznie ignoruje wartości FAŁSZ i zwraca minimalną wartość pozostałych, czyli -0,64.
To jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Minimum z MINIFS
Jeśli masz Office 365 lub Excel 2019, możesz użyć funkcji MINIFS, aby uzyskać najniższy poniedziałkowy przypływ w następujący sposób:
=MINIFS(pred,day,"Mon",tide,"L")
Wynik jest taki sam, a ta formuła nie wymaga Ctrl + Shift + Enter.
Uzyskaj datę
Gdy już znajdziesz minimalny poniedziałkowy poziom przypływu, z pewnością zechcesz poznać datę i godzinę. Można to zrobić za pomocą formuły INDEX i MATCH. Wzór w I7 to:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Pracując od wewnątrz, musimy najpierw zlokalizować pozycję najniższego poniedziałkowego przypływu za pomocą funkcji MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Tutaj przeprowadzamy te same testy warunkowe, które zastosowaliśmy powyżej, aby ograniczyć przetwarzanie tylko do poniedziałkowych odpływów. Jednak stosujemy jeszcze jeden test, aby ograniczyć wyniki do wartości minimalnej teraz w I6 i używamy nieco prostszej składni opartej na logice boolowskiej, aby zastosować kryteria. Mamy trzy oddzielne wyrażenia, z których każde testuje jeden warunek:
(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
To jest przykład, który ładnie prezentuje elastyczność XLOOKUP. Możemy użyć dokładnie tej samej logiki z powyższych formuł INDEKS i PODAJ.POZYCJĘ, w prostej i eleganckiej formule.
