Trong Excel nếu bạn có một danh sách các giá trị và bạn không thể tìm thấy giá trị khớp chính xác, thì bạn cần tìm giá trị Gần nhất Lớn hơn hoặc Gần nhất Nhỏ hơn. Ví dụ: nếu bạn đang thực hiện so sánh các số và bạn không tìm thấy kết quả khớp chính xác trong phạm vi, bạn có thể sử dụng số lớn hơn hoặc nhỏ hơn tiếp theo và tiếp tục.

Chúng ta có thể sử dụng kết hợp công thức Countif, Large và Small để có kết quả đầu ra.

Countif được sử dụng để đếm số lần dữ liệu cụ thể được lặp lại trong một phạm vi ô.

Cú pháp = COUNTIF (phạm vi, tiêu chí)

Lớn: Trả về giá trị lớn nhất thứ k trong một tập dữ liệu.

Cú pháp = LARGE (mảng, k)

Nhỏ: Trả về giá trị nhỏ nhất thứ k trong một tập dữ liệu.

Cú pháp = SMALL (mảng, k)

Hãy cho chúng tôi hiểu thế nào là Số lớn hơn và nhỏ hơn gần nhất

Chúng ta có một danh sách với các số ngẫu nhiên 1,2,3,5,8,10 và chúng ta cần một công thức để đánh giá như sau:

  • Nếu k có trong danh sách, trả về k – Nếu k không có trong danh sách, trả về giá trị cao hơn tiếp theo. Ví dụ: nếu k = 7, trả về 8 (trong trường hợp số lớn hơn gần nhất)

  • Nếu k không có trong danh sách, trả về giá trị nhỏ hơn tiếp theo. Ví dụ: nếu k = 7, trả về 5 (trong trường hợp số nhỏ hơn gần nhất)

Hãy lấy một ví dụ, chúng tôi có một số số ngẫu nhiên trong cột A và chúng tôi cần tìm giá trị trong phạm vi này, đó là Ô gần nhất Nhỏ hơn hoặc Gần nhất Lớn hơn cho ô B2 chứa 46. Số này không có sẵn trong phạm vi của chúng tôi. Xem ảnh chụp màn hình dưới đây

img1

Trước tiên hãy để chúng tôi xem cách chúng tôi tính toán Số lớn hơn gần nhất:

Chúng ta có thể kiểm tra thủ công giá trị lớn nhất tiếp theo của 46 là 54.

Bây giờ chúng ta hãy cố gắng đạt được cùng một kết quả bằng cách sử dụng công thức – Chúng tôi sẽ sử dụng kết hợp các hàm LARGE & COUNTIF * để tạo ra số lớn hơn gần nhất.

Tiêu chí lớn hơn dấu ‘>’ với Giá trị Tra cứu, tức là ô B2 Dấu lớn hơn kết hợp với Giá trị Tra cứu sẽ trả về 5 giá trị, tức là 54, 57, 61, 74, 83 lớn hơn 46.

  • Khi chúng ta hợp nhất Hàm lớn & Hàm đếm, chúng ta sẽ nhận được 54. Tham khảo công thức trong ô G2 và G3 trong ảnh chụp nhanh bên dưới.

img2

Tái bút: B2 trong Countifformula là Giá trị tra cứu, tức là 46 Trong ô C2, công thức được sử dụng để tìm số Closest Larger * là

= LARGE ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, “>” & B2))

img3

Bây giờ chúng ta hãy xem cách chúng ta tính toán Số gần nhất nhỏ hơn – Chúng ta có thể kiểm tra thủ công giá trị nhỏ hơn tiếp theo của 46 là 28.

Cho phép có được cùng một đầu ra bằng cách sử dụng công thức – Chúng tôi sẽ sử dụng kết hợp các hàm SMALL & COUNTIF * để tạo ra số nhỏ hơn gần nhất.

Tiêu chí là nhỏ hơn ‘<’ với Giá trị Tra cứu, tức là ô B2 Dấu nhỏ hơn kết hợp với Giá trị Tra cứu sẽ trả về 2 giá trị, tức là 16 & 28 mà nhỏ hơn 46.

  • Khi chúng ta hợp nhất Các hàm nhỏ & Các hàm đếm, chúng ta nhận được 28. Tham khảo công thức trong các ô G5 và G6 trong ảnh chụp nhanh bên dưới.

Xem ảnh chụp màn hình bên dưới với công thức:

img4

Lưu ý: ô B2 trong CountifFormula là Giá trị Tra cứu, tức là 46 Trong ô D2, Công thức được sử dụng để tìm số Gần nhất Nhỏ hơn * là

= NHỎ ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, “<” & B2))

img5

Đây là cách chúng ta có thể trả về số lớn hơn gần nhất và số nhỏ hơn của tủ quần áo.