Công thức chung
=INDEX(location,XMATCH(0,distance,1))
Tóm lược
Để xác định vị trí gần nhất theo khoảng cách, bạn có thể sử dụng công thức dựa trên hàm XMATCH với hàm INDEX. Trong ví dụ được hiển thị, công thức trong ô E5 là:
=INDEX(location,XMATCH(0,distance,1))
trong đó vị trí (B5: B12) và khoảng cách (C5: C12) là các phạm vi được đặt tên.
Giải trình
Về cơ bản, công thức này là một công thức INDEX và MATCH cơ bản. Tuy nhiên, thay vì sử dụng hàm MATCH cũ hơn, chúng tôi đang sử dụng hàm XMATCH, cung cấp cài đặt chế độ đối sánh mạnh mẽ hơn:
=INDEX(location,XMATCH(0,distance,1))
Làm việc từ trong ra ngoài, chúng tôi đang sử dụng hàm XMATCH để tìm vị trí của vị trí gần nhất:
XMATCH(0,distance,1) // find row nearest zero
Chúng tôi thực hiện điều đó bằng cách đặt giá trị tra cứu thành không (0), mảng tra cứu thành khoảng cách (C5: C12) và so khớp chế độ thành 1.
Giá trị chế độ so khớp là 1 cho XMATCH biết để tìm một kết quả phù hợp chính xác hoặc giá trị lớn nhất tiếp theo. Vì giá trị tra cứu được cung cấp dưới dạng không (0), XMATCH sẽ tìm khoảng cách đầu tiên lớn hơn 0. Một lợi ích tuyệt vời của XMATCH - điều khiến nó khác biệt với MATCH - là nó không phải là mảng tra cứu được sắp xếp. Bất kể thứ tự nào, MATCH sẽ trả về giá trị khớp chính xác đầu tiên hoặc giá trị lớn nhất tiếp theo.
Trong ví dụ, XMATCH trả về 5, vì khoảng cách nhỏ nhất là 7 (vị trí G), xuất hiện thứ năm trong danh sách. Công thức giải quyết thành:
=INDEX(location,5) // returns "G"
và INDEX trả về mục thứ năm từ vị trí phạm vi được đặt tên (B5: B12), là "G".
Lưu ý: khi hòa, XMATCH sẽ trả về trận đầu tiên cho các giá trị hòa.
Nhận khoảng cách
Công thức để trả về khoảng cách thực của vị trí gần nhất gần như giống nhau. Thay vì đặt cho INDEX tên vị trí, chúng tôi cung cấp cho INDEX khoảng cách. Công thức trong F5 là:
=INDEX(distance,XMATCH(0,distance,1)) // returns distance
XMATCH trả về kết quả tương tự như trên (5) và INDEX trả về 7.
