Обобщение
За да намерите най-ниския прилив в понеделник, при набор от данни с много дни приливи и отливи, можете да използвате формула за масив, базирана на функциите 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), ден (C5: C124), час (D5: D124), пред (E5: E124), прилив (F5: F124).
И двете са формули на масив и трябва да се въвеждат с control + shift + enter.
Данни от tidesandcurrents.noaa.gov за Санта Круз, Калифорния.
Обяснение
На високо ниво този пример е за намиране на минимална стойност въз основа на множество критерии. За целта използваме функцията MIN заедно с две вложени IF функции:
(=MIN(IF(day=I5,IF(tide="L",pred))))
работещ отвътре навън, първият IF проверява дали денят е "Понеделник", въз основа на стойността в I5:
IF(day=I5 // is day "Mon"
Ако резултатът е ИСТИНСКИ, изпълняваме друг IF:
IF(tide="L",pred) // if tide is "L" return prediction
С други думи, ако денят е "понеделник", ние проверяваме дали приливът е "L". Ако е така, връщаме предсказаното ниво на прилива, като използваме посочения диапазон pred .
Забележете, че не предоставяме "стойност, ако е невярно" нито за IF. Това означава, че ако някой от логическите тестове е FALSE, външният IF ще върне FALSE. За повече информация относно вложени IF, вижте тази статия.
Важно е да се разбере, че наборът от данни включва 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)
Ключовото нещо, което трябва да забележите тук, са само стойностите, свързани с понеделник и отлива, оцеляват при пътуването през вложените IF. Останалите стойности са заменени с FALSE. С други думи, ние използваме структурата с двоен IF, за да „изхвърлим“ стойностите, които не ни интересуват.
Масивът по-горе се връща директно към функцията MIN. Функцията MIN автоматично игнорира FALSE стойностите и връща минималната стойност на тези, които остават, -0,64.
Това е формула на масив и трябва да се въведе с control + shift + enter.
Минимум с MINIFS
Ако имате Office 365 или Excel 2019, можете да използвате функцията MINIFS, за да получите най-ниския прилив в понеделник по следния начин:
=MINIFS(pred,day,"Mon",tide,"L")
Резултатът е същият и тази формула не изисква управление + shift + enter.
Вземете датата
След като намерите минималното ниво на прилив в понеделник, несъмнено ще искате да знаете датата и часа. Това може да стане с формула 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
Всеки от тези изрази работи на 120 стойности и връща масив от 120 TRUE FALSE резултата. Когато тези масиви се умножават един по друг, стойностите TRUE FALSE се принуждават към 1s и 0s. Резултатът е единичен масив като този:
(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)
Тъй като в целия набор от данни има само една стойност, която отговаря на трите условия, в масива има само една единица.
Сега можете да видите защо сме конфигурирали функцията MATCH да търси числото 1 в режим на точно съвпадение. MATCH локализира 1 и връща позиция 88 директно към функцията INDEX. Вече можем да пренапишем формулата по следния начин:
=INDEX(date,88) // returns 23-Dec-19
След това функцията INDEX връща 88-мата стойност в посочената дата на диапазона , която е 23 декември-19. Това е датата, която съответства на най-ниското ниво на прилив в понеделник.
Това е формула на масив и трябва да се въведе с 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 по-горе, в проста и елегантна формула.
