Để tìm giá trị gần nhất trong một mảng các giá trị số trong Google Sheets, chúng ta có thể sử dụng combo các hàm Filter, Min và Abs.
Có một phương pháp phổ biến khác được sử dụng bởi người dùng Excel, mà cũng hoạt động trong Google Sheets.
Trong phương pháp đó, chúng ta thay thế hàm Filter bằng Index-Match. Tuy nhiên, nó có một vấn đề nhỏ!
Vậy vấn đề đó là gì?
Nếu có hai giá trị gần nhất, một giá trị cao hơn và một giá trị thấp hơn, công thức chỉ trả về giá trị thấp hơn.
Nhưng combo của Filter, Min và Abs có thể trả về cả hai giá trị. Dưới đây là ví dụ minh họa:
Cách Tìm Giá Trị Gần Nhất Trong Google Sheets
Dữ liệu mẫu: B1:B (trong đó, B1 chứa tên cột)
Giá trị cần đánh giá trong dữ liệu mẫu: D2
Công thức để tìm giá trị gần nhất:
Chèn một trong hai công thức dưới đây vào ô E2.
Công thức #1 (Phương pháp Filter [Google Sheets]):
=filter(B2:B,ABS(D2-B2:B)=min(ABS(D2-B2:B)))
Công thức #2 (Phương pháp Index-Match [Excel]):
=index(B2:B10,match(min(ABS(D2-B2:B10)),ABS(D2-B2:B10),0))
Sử dụng các công thức trên, chúng ta có thể tìm ra giá trị gần nhất của ô D2 trong mảng B2:B.
Thay đổi giá trị trong ô D2 thành 500 và tìm giá trị gần nhất bằng cả hai công thức.
Công thức đầu tiên sẽ trả về hai giá trị. Chúng là 400 và 600.
Công thức thứ hai chỉ trả về một giá trị, tức là 400.
Giải thích Công thức
Công thức 1 – Phương pháp Filter
Nó thực chất là một công thức FILTER.
Cú pháp: FILTER(dải, điều kiện1, [điều kiện2, …])
Trong đó, chúng ta biết rằng dải cần lọc là B2:B. Còn đối số điều kiện1 thì sao?
Nó là ABS(D2-B2:B)=min(ABS(D2-B2:B))
Chúng ta có thể chia điều kiện1 này thành hai phần.
Phần_1: ABS(D2-B2:B)
Phần_2: min(ABS(D2-B2:B)) hoặc có thể nói là min(phần_1).
Hai công thức part_1 và part_2 trên trả về gì?
Chúng ta có thể kiểm tra chúng trong một Sheet (sử dụng ArrayFormula với chúng là bắt buộc ngoài Filter).
Kiểm tra part_1 trong ô F2:
=ArrayFormula(ABS(D2-B2:B))
Phần_1 trả về sự chênh lệch giữa các số trong B2:B và D2, tức là D2-B2:B.
Lưu ý: Tôi đã sử dụng ABS, tức là ABS(D2-B2:B), để trả về các số tuyệt đối (số không có dấu trừ).
Bằng cách bao phủ công thức trên bằng MIN, chúng ta có thể nhận được giá trị chênh lệch nhỏ nhất, tức là phần_2.
Đó là chìa khóa để tìm giá trị gần nhất trong Google Sheets.
Kiểm tra phần_2 trong ô G2:
=ArrayFormula(MIN(ABS(D2-B2:B)))
Kết quả: 10
Đó là số thứ ba trong dải ô F2:F (phần_1).
Nếu chúng ta lọc B2:B (dải) bằng điều kiện F2:F=G2 (phần_1=phần_2), chúng ta sẽ có được 50.
Theo cách này, chúng ta có thể tìm giá trị gần nhất trong Google Sheets.
Công thức 2 – Phương pháp Index-Match
Nếu bạn đã hiểu cách sử dụng combo Filter, Min và Abs để tìm giá trị gần nhất trong Google Sheets, cách Index-Match để làm điều tương tự là dễ dàng để học.
Ở đây, chúng ta cũng sẽ sử dụng các phần_1 và phần_2 như trên để giải thích.
Trong phương pháp này, tôi đã sử dụng hàm MATCH để tìm vị trí (tương đối) của đầu ra của phần_2 trong đầu ra của phần_1.
Sau đó, sử dụng INDEX, tôi đã thay đổi số hàng bằng số hàng đó nhiều lần trong B2:B để nhận được giá trị gần nhất.
Tìm Giá Trị Gần Nhất Trong Google Sheets và Ứng Dụng Thực Tiễn
Ví dụ, bạn là một trong những nhà cung cấp vật liệu trang trí cảnh quan, và bạn có một kho hàng đá trắng 20-40 mm.
Giả sử, giá đơn vị của mặt hàng này thay đổi dựa trên số lượng đặt hàng.
Tức là, cho đơn đặt hàng 10 kg, bạn bán sản phẩm với giá 1,43 đô la mỗi kg.
Nếu số lượng đặt hàng là 25 kg, thì bạn bán cùng một sản phẩm với giá 1,28 đô la mỗi kg.
Tỷ giá đơn vị là 1,14 cho 50 kg và 1 cho 100 kg.
Bạn muốn tìm giá trị đơn vị gần nhất khi có ai đó đặt hàng 45 kg.
Trong trường hợp này, bạn có thể sử dụng một trong các công thức trên để tìm giá trị gần nhất trong Google Sheets. Đây là cách thực hiện.
=filter(C2:C,ABS(D2-B2:B)=min(ABS(D2-B2:B)))
Công thức này tìm giá trị gần nhất của số lượng và trả về giá đơn vị của nó.
Tài nguyên
- Tìm Ngày Gần Nhất Đến Hoặc Cách Ngày Hôm Nay Trong Google Sheets.
- Truy vấn để Lọc Ngày Gần Nhất Lớn Hơn Ngày Hôm Nay Trong Google Sheets.
- Tìm Giá Trị Gần Nhất Lớn Hơn Hoặc Bằng Khóa Tìm Kiếm Trong Vlookup Trong Google Sheets.
- Các Phương pháp Thay thế cho Hàm XMATCH Trong Google Sheets.
- Các Phương pháp Thay thế cho Hàm XLOOKUP Trong Google Sheets – Chế độ Tìm kiếm và So khớp.
- Cách Tra Cứu Ngày Mới Nhất Trong Google Sheets [Công thức Mảng].
- Trích xuất Bản ghi Sớm nhất hoặc Mới nhất Trong Mỗi Thể loại Dựa trên Mốc thời gian.
- Tra Cứu Giá Trị Mới Nhất – Sự Khác Biệt Trong Excel và Google Sheets.
- Công thức để Kết hợp các Hàng và Nhận Giá Trị Mới Nhất Trong Google Sheets.
