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))
trong đó chuyến đi (B5: B14) và chi phí (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à 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ơ bản, đây là công thức INDEX và MATCH: MATCH định vị vị trí của kết quả khớp gần nhất, cung cấp vị trí cho INDEX và 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ừ khỏi chi phí phạm vi được đặt tên (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ột mảng có 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 kết quả phù hợp nhất, vì vậy chúng tôi sử dụng hàm MIN để tìm chênh lệch nhỏ nhất, là 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Điều 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 (hiệu số 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 chuyến đi phạm vi được đặt tên là 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ó tỷ số hòa, công thức này sẽ trả về trận đấu đầu tiên.
Với XLOOKUP
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 phải sắp xếp dữ liệu. Đ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ột 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 kiế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 lớn nhất tiếp theo. Chúng tôi cung cấp chuyến đi phạm vi được đặt tên là mảng trả về, vì vậy kết quả là "Tây Ban Nha" như trước.
