Резюме
Чтобы найти самый низкий прилив в понедельник, учитывая набор данных с большим количеством дней приливов и отливов, вы можете использовать формулу массива, основанную на функциях ЕСЛИ и МИН. В показанном примере формула 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).
Оба являются формулами массива и должны вводиться с помощью Ctrl + Shift + Enter.
Данные с сайта tidesandcurrents.noaa.gov для Санта-Крус, Калифорния.
Объяснение
На высоком уровне этот пример посвящен поиску минимального значения на основе нескольких критериев. Для этого мы используем функцию MIN вместе с двумя вложенными функциями IF:
(=MIN(IF(day=I5,IF(tide="L",pred))))
работая изнутри, первая IF проверяет, является ли день «Mon», на основе значения в 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.
Это формулы массива, которые необходимо вводить с помощью Ctrl + Shift + Enter.
Минимум с MINIFS
Если у вас есть Office 365 или Excel 2019, вы можете использовать функцию MINIFS, чтобы получить самый низкий прилив в понедельник следующим образом:
=MINIFS(pred,day,"Mon",tide,"L")
Результат тот же, и эта формула не требует Ctrl + Shift + Enter.
Получить дату
Как только вы найдете минимальный уровень прилива в понедельник, вы, несомненно, захотите узнать дату и время. Это можно сделать с помощью формулы ИНДЕКС и ПОИСКПОЗ. Формула в I7:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Работая изнутри, нам нужно сначала определить положение самого низкого прилива понедельника с помощью функции ПОИСКПОЗ:
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. Мы можем использовать ту же логику из формул ИНДЕКС и ПОИСКПОЗ, приведенных выше, в простой и элегантной формуле.
