Tóm lược
Để tìm thủy triều thấp nhất vào thứ Hai, với một bộ dữ liệu có nhiều ngày thủy triều cao và thấp, bạn có thể sử dụng công thức mảng dựa trên các hàm IF và MIN. Trong ví dụ được hiển thị, công thức trong I6 là:
(=MIN(IF(day=I5,IF(tide="L",pred))))
trả về mức thủy triều thấp nhất vào thứ Hai trong dữ liệu, -0,64
Để truy xuất ngày thủy triều thứ hai thấp nhất, công thức trong I7 là:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Trong trường hợp bảng tính chứa các phạm vi sau đây có tên là: ngày (B5: B124), ngày (C5: C124), thời gian (D5: D124), pred (E5: E124), thủy triều (F5: F124).
Cả hai đều là công thức mảng và phải được nhập bằng control + shift + enter.
Dữ liệu từ tidesandcurrents.noaa.gov cho Santa Cruz, California.
Giải trình
Ở cấp độ cao, ví dụ này là về việc tìm giá trị tối thiểu dựa trên nhiều tiêu chí. Để làm điều đó, chúng tôi đang sử dụng hàm MIN cùng với hai hàm IF lồng nhau:
(=MIN(IF(day=I5,IF(tide="L",pred))))
làm việc từ trong ra ngoài, IF đầu tiên kiểm tra xem ngày có phải là "Thứ Hai" hay không, dựa trên giá trị trong I5:
IF(day=I5 // is day "Mon"
Nếu kết quả là TRUE, chúng tôi chạy IF khác:
IF(tide="L",pred) // if tide is "L" return prediction
Nói cách khác, nếu ngày là "Mon", chúng tôi kiểm tra xem thủy triều có phải là "L" hay không. Nếu vậy, chúng tôi quay trở lại mức thủy triều dự đoán, sử dụng phạm vi tên pred .
Lưu ý rằng chúng tôi không cung cấp "giá trị nếu sai" cho IF. Điều đó có nghĩa là nếu một trong hai kiểm tra logic là FALSE, IF bên ngoài sẽ trả về FALSE. Để biết thêm thông tin về IF lồng nhau, hãy xem bài viết này.
Điều quan trọng là phải hiểu rằng tập dữ liệu bao gồm 120 hàng, vì vậy mỗi phạm vi được đặt tên trong công thức chứa 120 giá trị. Đây là những gì làm cho đây là một công thức mảng - chúng tôi đang xử lý nhiều giá trị cùng một lúc. Sau khi cả hai IF được đánh giá, IF bên ngoài sẽ trả về một mảng chứa 120 giá trị như sau:
(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)
Điều quan trọng cần lưu ý ở đây là chỉ các giá trị được liên kết với Thứ Hai và thủy triều thấp mới tồn tại trong chuyến đi thông qua các IF lồng nhau. Các giá trị khác đã được thay thế bằng FALSE. Nói cách khác, chúng ta đang sử dụng cấu trúc IF kép để "vứt bỏ" các giá trị mà chúng ta không quan tâm.
Mảng trên được trả về trực tiếp cho hàm MIN. Hàm MIN tự động bỏ qua các giá trị FALSE và trả về giá trị nhỏ nhất của những giá trị còn lại, -0,64.
Đây là công thức mảng và phải được nhập bằng control + shift + enter.
Tối thiểu với MINIFS
Nếu bạn có Office 365 hoặc Excel 2019, bạn có thể sử dụng hàm MINIFS để nhận mức thấp nhất vào Thứ Hai như sau:
=MINIFS(pred,day,"Mon",tide,"L")
Kết quả giống nhau và công thức này không yêu cầu control + shift + enter.
Nhận ngày
Khi bạn tìm thấy mực nước triều tối thiểu vào thứ Hai, chắc chắn bạn sẽ muốn biết ngày và giờ. Điều này có thể được thực hiện với công thức INDEX và MATCH. Công thức trong I7 là:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Làm việc từ trong ra ngoài, trước tiên chúng ta cần xác định vị trí của thủy triều thấp nhất vào Thứ Hai bằng chức năng MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Tại đây, chúng tôi sẽ thực hiện các thử nghiệm có điều kiện tương tự mà chúng tôi đã áp dụng ở trên để chỉ giới hạn xử lý đối với thủy triều thấp vào Thứ Hai. Tuy nhiên, chúng tôi áp dụng một thử nghiệm nữa để hạn chế kết quả ở giá trị nhỏ nhất hiện tại trong I6 và chúng tôi sử dụng cú pháp đơn giản hơn một chút dựa trên logic boolean để áp dụng tiêu chí. Chúng tôi có ba biểu thức riêng biệt, mỗi biểu thức kiểm tra một điều kiện:
(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
Đây là một ví dụ thể hiện tính linh hoạt của XLOOKUP. Chúng ta có thể sử dụng chính xác cùng một logic từ các công thức INDEX và MATCH ở trên, trong một công thức đơn giản và trang nhã.
