Резюме
Щоб знайти найменший приплив у понеділок, враховуючи набір даних із багатоденними припливами та відливами, ви можете використовувати формулу масиву на основі функцій 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))))
працюючи зсередини назовні, перший ПФ перевіряє, чи день "Пн", виходячи із значення в I5:
IF(day=I5 // is day "Mon"
Якщо результат TRUE, ми запускаємо інший IF:
IF(tide="L",pred) // if tide is "L" return prediction
Іншими словами, якщо день "Пн", ми перевіряємо, чи приплив "Л". Якщо так, ми повертаємо прогнозований приплив, використовуючи названий діапазон pred .
Зверніть увагу, що ми не надаємо "значення, якщо хибне" для жодного IF. Це означає, що якщо будь-який логічний тест FALSE, зовнішній IF поверне FALSE. Для отримання додаткової інформації про вкладені ПЧ див. Цю статтю.
Важливо розуміти, що набір даних включає 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. Іншими словами, ми використовуємо подвійну структуру 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
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 у простій та елегантній формулі.
