Ringkasan
Untuk menemukan pasang terendah pada hari Senin, berdasarkan kumpulan data dengan banyak hari pasang naik dan surut, Anda dapat menggunakan rumus array berdasarkan fungsi IF dan MIN. Pada contoh yang ditampilkan, rumus di I6 adalah:
(=MIN(IF(day=I5,IF(tide="L",pred))))
yang mengembalikan pasang terendah Senin dalam data, -0,64
Untuk mendapatkan kembali tanggal pasang surut terendah hari Senin, rumus pada I7 adalah:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Dimana lembar kerja berisi rentang bernama berikut: tanggal (B5: B124), hari (C5: C124), waktu (D5: D124), pred (E5: E124), pasang (F5: F124).
Keduanya adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Data dari tidesandcurrents.noaa.gov untuk Santa Cruz, California.
Penjelasan
Pada tingkat tinggi, contoh ini adalah tentang menemukan nilai minimum berdasarkan beberapa kriteria. Untuk melakukan itu, kami menggunakan fungsi MIN bersama dengan dua fungsi IF bersarang:
(=MIN(IF(day=I5,IF(tide="L",pred))))
bekerja dari dalam ke luar, IF pertama memeriksa apakah hari itu "Sen", berdasarkan nilai di I5:
IF(day=I5 // is day "Mon"
Jika hasilnya BENAR, kami menjalankan IF lain:
IF(tide="L",pred) // if tide is "L" return prediction
Dengan kata lain, jika hari itu "Sen", kami memeriksa apakah pasang surut "L". Jika demikian, kita mengembalikan tingkat pasang surut yang diprediksi, menggunakan rentang bernama pred .
Perhatikan bahwa kami tidak memberikan "nilai jika salah" untuk IF mana pun. Itu berarti jika salah satu tes logika adalah FALSE, IF luar akan mengembalikan FALSE. Untuk informasi selengkapnya tentang IF bertumpuk, lihat artikel ini.
Penting untuk dipahami bahwa kumpulan data menyertakan 120 baris, jadi setiap rentang bernama dalam rumus berisi 120 nilai. Inilah yang menjadikan ini rumus array - kami memproses banyak nilai sekaligus. Setelah kedua IF dievaluasi, IF luar akan mengembalikan array yang berisi 120 nilai seperti ini:
(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)
Hal utama yang perlu diperhatikan di sini hanya nilai yang terkait dengan hari Senin dan air surut yang bertahan dalam perjalanan melalui IF bertingkat. Nilai-nilai lain telah diganti dengan FALSE. Dengan kata lain, kami menggunakan struktur IF ganda untuk "membuang" nilai yang tidak kami minati.
Larik di atas dikembalikan langsung ke fungsi MIN. Fungsi MIN secara otomatis mengabaikan nilai FALSE, dan mengembalikan nilai minimum yang tersisa, -0.64.
Ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Minimal dengan MINIFS
Jika Anda memiliki Office 365 atau Excel 2019, Anda dapat menggunakan fungsi MINIFS untuk mendapatkan gelombang terendah Senin seperti ini:
=MINIFS(pred,day,"Mon",tide,"L")
Hasilnya sama, dan rumus ini tidak memerlukan control + shift + enter.
Dapatkan tanggalnya
Setelah Anda menemukan tingkat pasang surut minimum pada hari Senin, Anda pasti ingin mengetahui tanggal dan waktunya. Ini dapat dilakukan dengan rumus INDEX dan MATCH. Rumus di I7 adalah:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Bekerja dari dalam ke luar, pertama-tama kita perlu menemukan posisi pasang surut terendah Senin dengan fungsi MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Di sini, kami menjalankan pengujian bersyarat yang sama yang kami terapkan di atas untuk membatasi pemrosesan hanya pada saat air surut hari Senin. Namun, kami menerapkan satu pengujian lagi untuk membatasi hasil ke nilai minimum sekarang di I6, dan kami menggunakan sintaks yang sedikit lebih sederhana berdasarkan logika boolean untuk menerapkan kriteria. Kami memiliki tiga ekspresi terpisah, masing-masing menguji satu kondisi:
(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
Ini adalah contoh yang menunjukkan fleksibilitas XLOOKUP dengan baik. Kita dapat menggunakan logika yang persis sama dari rumus INDEX dan MATCH di atas, dalam rumus yang sederhana dan elegan.
