Özet
Günlerce yükselen ve alçalan gelgitler içeren bir veri kümesi verildiğinde, Pazartesi günündeki en düşük gelgiti bulmak için, EĞER ve MİN işlevlerine dayalı bir dizi formülü kullanabilirsiniz. Gösterilen örnekte, I6'daki formül şöyledir:
(=MIN(IF(day=I5,IF(tide="L",pred))))
verilerdeki en düşük Pazartesi gelgitini veren -0.64
En düşük Pazartesi gelgitinin tarihini elde etmek için, I7'deki formül:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Çalışma sayfası aşağıdaki adlandırılmış aralıkları içerdiğinde: tarih (B5: B124), gün (C5: C124), saat (D5: D124), pred (E5: E124), gelgit (F5: F124).
Her ikisi de dizi formülleridir ve control + shift + enter ile girilmelidir.
Santa Cruz, California için tidesandcurrents.noaa.gov'dan veriler.
Açıklama
Yüksek düzeyde, bu örnek, birden çok kritere göre minimum bir değer bulmakla ilgilidir. Bunu yapmak için, MİN işlevini iki iç içe EĞER işlevi ile birlikte kullanıyoruz:
(=MIN(IF(day=I5,IF(tide="L",pred))))
İçten dışa doğru çalışıldığında, ilk EĞER I5'teki değere göre günün "Pzt" olup olmadığını kontrol eder:
IF(day=I5 // is day "Mon"
Sonuç DOĞRU ise, başka bir EĞER çalıştırırız:
IF(tide="L",pred) // if tide is "L" return prediction
Yani gün "Pzt" ise gelgitin "L" olup olmadığını kontrol ederiz. Eğer öyleyse, biz adlandırılmış aralık kullanılarak, tahmin edilen gelgit seviyesini dönmek pred .
Her iki EĞER için de "yanlışsa değer" sağlamadığımıza dikkat edin. Bu, mantıksal testlerden biri YANLIŞ ise, dış EĞER YANLIŞ döndürecektir. İç içe geçmiş IF'ler hakkında daha fazla bilgi için bu makaleye bakın.
Veri kümesinin 120 satır içerdiğini anlamak önemlidir, bu nedenle formüldeki adlandırılmış aralıkların her biri 120 değer içerir. Bunu bir dizi formülü yapan şey budur - aynı anda birçok değeri işliyoruz. Her iki IF değerlendirildikten sonra, dış EĞER aşağıdaki gibi 120 değer içeren bir dizi döndürür:
(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)
Burada dikkat edilmesi gereken en önemli şey, yalnızca Pazartesi ile ilişkili değerlerdir ve düşük gelgitler, iç içe geçmiş IF'ler aracılığıyla yolculuktan sağ çıkabilir. Diğer değerler FALSE ile değiştirildi. Başka bir deyişle, ilgilenmediğimiz değerleri "atmak" için çift IF yapısını kullanıyoruz.
Yukarıdaki dizi doğrudan MIN işlevine döndürülür. MIN işlevi, FALSE değerlerini otomatik olarak yok sayar ve kalanlar için minimum değer olan -0,64'ü döndürür.
Bu bir dizi formülüdür ve kontrol + shift + enter ile girilmelidir.
MINIFS ile minimum
Office 365 veya Excel 2019'unuz varsa, aşağıdaki gibi en düşük Pazartesi gelgitini elde etmek için MINIFS işlevini kullanabilirsiniz:
=MINIFS(pred,day,"Mon",tide,"L")
Sonuç aynıdır ve bu formül kontrol + üst karakter + enter gerektirmez.
Tarihi al
Minimum Pazartesi gelgit seviyesini bulduğunuzda, şüphesiz tarih ve saati bilmek isteyeceksiniz. Bu, INDEX ve MATCH formülü ile yapılabilir. I7'deki formül:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
İçten dışa doğru çalışarak, önce MATCH fonksiyonu ile en düşük Pazartesi gelgitinin konumunu bulmamız gerekir:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Burada, işlemeyi yalnızca Pazartesi günkü düşük gelgitler ile sınırlandırmak için yukarıda uyguladığımız koşullu testleri gerçekleştiriyoruz. Bununla birlikte, sonuçları artık I6'da minimum değerle sınırlamak için bir test daha uyguluyoruz ve kriterleri uygulamak için boole mantığına dayalı biraz daha basit bir sözdizimi kullanıyoruz. Her biri bir koşulu test eden üç ayrı ifademiz var:
(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
Bu, XLOOKUP'un esnekliğini güzel bir şekilde gösteren bir örnektir. Yukarıdaki INDEX ve MATCH formüllerinden tamamen aynı mantığı basit ve zarif bir formülde kullanabiliriz.
