Ringkasan
Untuk mencari gelombang terendah pada hari Isnin, memandangkan satu set data dengan gelombang pasang tinggi dan rendah, anda boleh menggunakan formula larik berdasarkan fungsi IF dan MIN. Dalam contoh yang ditunjukkan, formula dalam I6 adalah:
(=MIN(IF(day=I5,IF(tide="L",pred))))
yang mengembalikan gelombang Isnin terendah dalam data, -0.64
Untuk mendapatkan tarikh gelombang Isnin terendah, formula di I7 adalah:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Di mana lembaran kerja mengandungi rentang bernama berikut: tarikh (B5: B124), hari (C5: C124), waktu (D5: D124), pred (E5: E124), pasang (F5: F124).
Kedua-duanya adalah formula array dan mesti dimasukkan dengan kawalan + shift + enter.
Data dari tidesandcurrents.noaa.gov untuk Santa Cruz, California.
Penjelasan
Pada tahap tinggi, contoh ini adalah mengenai mencari nilai minimum berdasarkan pelbagai 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, JIKA pertama memeriksa apakah hari itu "Isnin", berdasarkan nilai dalam I5:
IF(day=I5 // is day "Mon"
Sekiranya hasilnya BENAR, kami menjalankan JIKA lain:
IF(tide="L",pred) // if tide is "L" return prediction
Dengan kata lain, jika hari itu "Isnin", kita periksa sama ada air pasang adalah "L". Sekiranya demikian, kami mengembalikan tahap pasang yang diramalkan, menggunakan pred pred range yang dinamakan .
Perhatikan bahawa kami tidak memberikan "nilai jika salah" untuk JIKA. Ini bermaksud jika salah satu ujian logik adalah PALSU, JIKA luar akan mengembalikan SALAH. Untuk maklumat lebih lanjut mengenai IF bersarang, lihat artikel ini.
Penting untuk memahami bahawa set data merangkumi 120 baris, jadi setiap julat yang dinamakan dalam formula mengandungi 120 nilai. Inilah yang menjadikannya formula larik - kami memproses banyak nilai sekaligus. Setelah kedua-dua IF dinilai, IF luar akan mengembalikan array yang mengandungi 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)
Perkara penting yang perlu diperhatikan di sini hanyalah nilai yang berkaitan dengan hari Isnin dan air surut yang bertahan dalam perjalanan melalui IF bersarang. Nilai-nilai lain telah diganti dengan SALAH. Dengan kata lain, kita menggunakan struktur IF berganda untuk "membuang" nilai yang tidak kita minati.
Susunan di atas dikembalikan terus ke fungsi MIN. Fungsi MIN secara automatik mengabaikan nilai-nilai PALSU, dan mengembalikan nilai minimum yang tetap, -0.64.
Ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Minimum dengan MINIFS
Sekiranya anda mempunyai Office 365 atau Excel 2019, anda boleh menggunakan fungsi MINIFS untuk mendapatkan gelombang Isnin terendah seperti ini:
=MINIFS(pred,day,"Mon",tide,"L")
Hasilnya sama, dan formula ini tidak memerlukan kawalan + shift + enter.
Dapatkan tarikhnya
Sebaik sahaja anda menemui tahap air pasang Isnin minimum, anda pasti ingin mengetahui tarikh dan waktu. Ini boleh dilakukan dengan formula INDEX dan MATCH. Formula dalam I7 adalah:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Bekerja dari dalam ke luar, kita perlu terlebih dahulu mencari kedudukan gelombang Isnin terendah dengan fungsi MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Di sini, kami menjalani ujian bersyarat yang sama seperti yang kami gunakan di atas untuk mengehadkan pemprosesan kepada air surut sahaja pada hari Isnin. Namun, kami menerapkan satu lagi ujian untuk membatasi hasil ke nilai minimum sekarang di I6, dan kami menggunakan sintaks yang lebih sederhana berdasarkan logik boolean untuk menerapkan kriteria. Kami mempunyai tiga ungkapan yang berasingan, masing-masing menguji satu syarat:
(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 kelenturan XLOOKUP. Kita boleh menggunakan logik yang sama dari formula INDEX dan MATCH di atas, dalam formula sederhana dan elegan.
