Công thức Excel: XLOOKUP mới nhất theo ngày
Công thức Excel: XLOOKUP mới nhất theo ngày

Công thức chung
=XLOOKUP(max,dates,results,,-1) // latest match by date
Tóm lược

Để nhận được kết quả phù hợ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ế độ so khớp 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)

Ở đâu ngày (C5: C15), mục (B5: B15) và giá bán (D5: D15) là 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ẽ là 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ề trận đấu thứ nhất trong một tập dữ liệu. Để có được trận đấu 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ố tùy chọn match_mode thành -1 để buộc so khớp gần đúng “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 thông qua các đối số từng cái một, lookup_value là ngày lớn nhất (mới nhất) trong dữ liệu:

MAX(date) // get max date value

Các lookup_array được bắt nguồn bằng một logic boolean biểu hiện:

(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ảng 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 được đặt tên ngày, 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 các số không và ngày tháng cho các vành đai:

={43484;0;0;0;0;0;43561;0;43671;0;0}

Lưu ý: số sê-ri hợp lệ Ngày tháng trong Excel.

Mảng này được gửi trực tiếp đến XLOOKUP dưới dạng lookup_array tranh luận.

Các return_arrayphạm vi được đặt tên giá bán (D5: D15)

Đối số tùy chọn không tìm thấy 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 mới 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.

https://exceljet.net/formula/xlookup-latest-by-date