Công thức chung
=XLOOKUP(max,dates,results,,-1) // latest match by date
Tóm lược
Để có kết quả khớp mới nhất trong một tập hợp dữ liệu theo ngày, bạn có thể sử dụng XLOOKUP ở chế độ đối sánh gần đúng bằng cách đặt match_mode thành -1. Trong ví dụ được hiển thị, công thức trong G5, được sao chép xuống, là:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
trong đó ngày (C5: C15), mục (B5: B15) và giá (D5: D15) là các phạm vi được đặt tên.
Giải trình
XLOOKUP cung cấp một số tính năng đặc biệt tốt cho các tra cứu phức tạp hơn. Trong ví dụ này, chúng tôi muốn giá mới nhất cho một mặt hàng theo ngày. Nếu dữ liệu được sắp xếp theo ngày theo thứ tự tăng dần, điều này sẽ rất đơn giản. Tuy nhiên, trong trường hợp này, dữ liệu không được sắp xếp.
Theo mặc định, XLOOKUP sẽ trả về kết quả khớp đầu tiên trong tập dữ liệu. Để có được kết quả khớp cuối cùng, chúng ta có thể đặt đối số tùy chọn search_mode, thành -1 để khiến XLOOKUP tìm kiếm "từ cuối đến đầu tiên". Tuy nhiên, chúng tôi không thể sử dụng phương pháp này ở đây vì không có gì đảm bảo rằng giá mới nhất cho một mặt hàng sẽ xuất hiện sau cùng.
Thay vào đó, chúng ta có thể đặt đối số match_mode tùy chọn thành -1 để bắt buộc đối sánh gần đúng là "chính xác hoặc nhỏ nhất tiếp theo", đồng thời điều chỉnh giá trị tra cứu và mảng tra cứu như được giải thích bên dưới. Công thức trong G5, được sao chép xuống, là:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Làm việc lần lượt qua các đối số, lookup_value là ngày lớn nhất (mới nhất) trong dữ liệu:
MAX(date) // get max date value
Lookup_array được dẫn xuất bằng biểu thức logic boolean:
(item=F5)*date
Bằng cách so sánh từng mục với giá trị trong F5, "Belt", chúng tôi nhận được một mảng các giá trị TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
trong đó giá trị TRUE đại diện cho các mục nhập cho "Vành đai". Mảng này hoạt động giống như một bộ lọc. Khi nó được nhân với các giá trị trong phạm vi ngày được đặt tên , các giá trị TRUE / FALSE được đánh giá thành 1 và 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Kết quả là một mảng chỉ chứa số không và ngày tháng cho vành đai:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Lưu ý: số sê-ri là ngày Excel hợp lệ.
Mảng này được chuyển trực tiếp đến XLOOKUP dưới dạng đối số lookup_array.
Return_array là phạm vi giá được đặt tên (D5: D15)
Đối số tùy chọn not_found không được cung cấp.
Match_mode được đặt thành -1, cho đối sánh chính xác hoặc mục nhỏ nhất tiếp theo.
XLOOKUP xem qua mảng tra cứu cho giá trị ngày lớn nhất. Vì mảng đã được lọc để loại trừ các ngày không được liên kết với "Belt", XLOOKUP chỉ cần tìm ngày phù hợp nhất (ngày chính xác hoặc ngày nhỏ nhất tiếp theo) tương ứng với ngày gần nhất.
Kết quả cuối cùng là giá liên quan đến ngày mới nhất. Công thức sẽ tiếp tục hoạt động khi dữ liệu được sắp xếp theo bất kỳ thứ tự nào.
