Công thức Excel: Tìm kết quả phù hợp nhất
Công thức Excel: Tìm kết quả phù hợp nhất

Công thức chung
{=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))}
Tóm lược

Để tìm kết quả khớp gần nhất trong dữ liệu số, bạn có thể sử dụng INDEX và MATCH, với sự trợ giúp từ các hàm ABS và MIN. Trong ví dụ được hiển thị, công thức trong F5, được sao chép xuống, là:

=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))

Ở đâu chuyến đi (B5: B14) và Giá cả (C5: C14) là phạm vi được đặt tên.

Trong F5, F6 và F7, công thức trả về chi phí cho chuyến đi gần nhất với 500, 1000 và 1500, tương ứng.

Lưu ý: đây là một công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.

Giải trình

Về cốt lõi, đây là một INDEX và MATCH công thức: TRẬN ĐẤU xác định vị trí của trận đấu gần nhất, cung cấp vị trí cho MỤC LỤCvà INDEX trả về giá trị tại vị trí đó trong cột Chuyến đi. Công việc khó khăn được thực hiện với hàm MATCH, được cấu hình cẩn thận để phù hợp với “sự khác biệt tối thiểu” như sau:

MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)

Thực hiện từng bước, giá trị tra cứu được tính bằng MIN và ABS như sau:

MIN(ABS(cost-E5)

Đầu tiên, giá trị trong E5 được trừ đi phạm vi được đặt tên Giá cả (C5: C14). Đây là một hoạt động mảng và vì có 10 giá trị trong phạm vi, kết quả là mảng với 10 giá trị như sau:

{899;199;250;-201;495;1000;450;-101;500;795}

Những con số này đại diện cho sự khác biệt giữa mỗi chi phí trong C5: C15 và chi phí trong ô E5, 700. Một số giá trị âm vì một chi phí thấp hơn số trong E5. Để chuyển đổi giá trị âm thành giá trị dương, chúng tôi sử dụng Chức năng ABS: 

ABS({899;199;250;-201;495;1000;450;-101;500;795})

trả về:

{899;199;250;201;495;1000;450;101;500;795}

Chúng tôi đang tìm kiếm phù hợp nhất, vì vậy chúng tôi sử dụng Hàm MIN để tìm sự khác biệt nhỏ nhất, là 101:

MIN({899;199;250;201;495;1000;450;101;500;795}) // returns 101

Giá trị này trở thành giá trị tra cứu bên trong MATCH. Mảng tra cứu được tạo như trước:

ABS(cost-E5) // generate lookup array

trả về cùng một mảng mà chúng ta đã thấy trước đó:

{899;199;250;201;495;1000;450;101;500;795}

Bây giờ chúng ta có những gì chúng ta cần để tìm vị trí của kết quả khớp gần nhất (chênh lệch nhỏ nhất) và chúng ta có thể viết lại phần MATCH của công thức như sau:

MATCH(101,{899;199;250;201;495;1000;450;101;500;795},0) // returns 8

Với 101 là giá trị tra cứu, hàm MATCH trả về 8, vì 101 nằm ở vị trí thứ 8 trong mảng. Cuối cùng, vị trí này được đưa vào INDEX dưới dạng đối số hàng, với phạm vi được đặt tên chuyến đi dưới dạng mảng:

=INDEX(trip,8)

và INDEX trả về chuyến đi thứ 8 trong phạm vi, “Tây Ban Nha”. Khi công thức được sao chép xuống các ô F6 và F7, nó sẽ tìm thấy kết quả phù hợp nhất với 1000 và 1500, “Pháp” và “Thái Lan” như được hiển thị.

Lưu ý: nếu có sự ràng buộc, công thức này sẽ trả về Đầu tiên trận đấu.

Với XLOOKUP

Các Hàm XLOOKUP cung cấp một cách thú vị để giải quyết vấn đề này, vì loại đối sánh 1 (đối sánh chính xác hoặc đối sánh lớn nhất tiếp theo) hoặc -1 (đối sánh chính xác hoặc đối sánh nhỏ nhất tiếp theo) không yêu cầu dữ liệu phải được sắp xếp. Điều này có nghĩa là chúng ta có thể viết một công thức như sau:

=XLOOKUP(0,ABS(cost-E5),trip,,1)

Như trên, chúng tôi sử dụng giá trị tuyệt đối của (cost-E5) để tạo mảng tra cứu:

{899;199;250;201;495;1000;450;101;500;795}

Sau đó, chúng tôi định cấu hình XLOOKUP để tìm số 0, với loại đối sánh được đặt thành 1, đối với đối sánh chính xác hoặc đối sánh lớn nhất tiếp theo. Chúng tôi cung cấp phạm vi được đặt tên chuyến đi là mảng trả về, vì vậy kết quả là “Tây Ban Nha” như trước.

https://exceljet.net/formula/find-closest-match