Hướng dẫn cách tìm giá trị nhỏ hơn gần nhất với giá trị so sánh

Chia sẻ bài viết này:
  •  
  •  
  •  
  •  
  •  
  •  
  •   
  •   

Khi dò tìm, tham chiếu theo các giá trị số, có thể bạn sẽ gặp phải yêu cầu tìm ra các giá trị nhỏ hơn và gần nhất với giá trị so sánh. Vậy làm cách nào để thực hiện yêu cầu này? Hãy cùng tìm hiểu cách làm trong bài viết này nhé.

Tìm giá trị nhỏ hơn gần nhất trong danh sách được sắp xếp

Ví dụ chúng ta có danh sách các số tiêu chuẩn ở cột A, từ A2:A6. Trong danh sách các sốố này được sắp xếp theo thứ tự tăng dần.

Xác định số nhỏ hơn gần nhất với giá trị số ở ô C3.

Để thực hiện yêu cầu này, chúng ta có thể sử dụng 2 hàm là LOOKUP và VLOOKUP. Cách làm cụ thể như sau:

Sử dụng hàm LOOKUP

Hàm LOOKUP là 1 hàm tìm kiếm cơ bản trong Excel, trong đó khi bảng dữ liệu chứa các giá trị cần tìm được sắp xếp theo thứ tự tăng dần thì chúng ta có thể tìm được số nhỏ hơn gần nhất với số cần tìm.

Tại ô D3 chúng ta dùng công thức như sau:

=LOOKUP(C3,$A$2:$A$6)

Trong đó:

  • Lookup_value = C3 là giá trị cần tìm
  • Lookup_vector = A2:A6 là vùng chứa các giá trị tham chiếu.

Kết quả của hàm là 200, là số nhỏ hơn 230 và gần nhất với số 230.

Sử dụng hàm VLOOKUP

Hàm VLOOKUP cũng là 1 hàm rất hay sử dụng trong Excel. Trong trường hợp này chúng ta có thể sử dụng hàm VLOOKUP như sau:

E3=VLOOKUP(C3,$A$2:$A$6,1,1)

  • lookup_value = C3 là giá trị cần tìm
  • table_array = A2:A6 là vùng cần tham chiếu
  • col_index_num = 1 vì chỉ có 1 cột trong vùng tham chiếu
  • range_lookup = 1 là tìm kiếm tương đối, kết quả là giá trị nhỏ hơn gần nhất.

Khi sử dụng hàm VLOOKUP với range_lookup = 1 (hay TRUE) thì hàm sẽ tìm kiếm theo phương pháp tương đối, kết quả trả về là giá trị nhỏ hơn gần nhất với số cần tìm.

Chỉ thực hiện được điều này khi:

  • Tìm kiếm theo giá trị Số
  • Vùng cần tham chiếu (vùng kết quả cần tìm) được sắp xếp theo thứ tự tăng dần

Tìm giá trị nhỏ hơn gần nhất trong danh sách không được sắp xếp

Hai trường hợp trên chúng ta đều thấy danh sách Tiêu chuẩn cần phải được sắp xếp theo thứ tự tăng dần thì mới làm được. Vậy nếu như danh sách này không được sắp xếp thì sao?

Để giải quyết yêu cầu này, chúng ta sử dụng 2 hàm LARGE và COUNTIF kết hợp với nhau.

Xác định thứ tự của số nhỏ hơn gần nhất với hàm COUNTIF

Để tìm được số nhỏ hơn gần nhất, chúng ta sẽ nghĩ theo hướng:

  • Có bao nhiêu số lớn hơn số cần tìm?
  • Số nhỏ hơn gần nhất là vị trí thứ mấy?

Khi đó ta có thể sử dụng hàm COUNTIF như sau:

D3=COUNTIF(A2:A6,”>”&C3)+1

Ý nghĩa của hàm này là:

  • COUNTIF(A2:A6,”>”&C3) đếm xem có bao nhiêu số trong vùng A2:A6 có giá trị lớn hơn giá trị ở ô C3
  • COUNTIF(A2:A6,”>”&C3)+1 khi đó giá trị nhỏ hơn gần nhất sẽ nằm ở vị trí tiếp theo (+1).

Trong ví dụ trên, ta thấy kết quả bằng 2, tức là:

  • Có 1 số trong danh sách Tiêu chuẩn lớn hơn số 230
  • Vị trí số nhỏ hơn gần nhất với số 230 là vị trí số lớn thứ 2 trong danh sách Tiêu chuẩn

=> Để xác định cụ thể đó là số nào, chúng ta dùng thêm hàm LARGE

Xác định giá trị số nhỏ hơn gần nhất với hàm LARGE

Hàm Large là hàm giúp tìm số lớn thứ mấy ở trong 1 dãy số.

Trong hàm COUNTIF ở trên, chúng ta đã xác định được số nhỏ hơn gần nhất nằm ở vị trí số lớn thứ mấy trong danh sách. Vì vậy sẽ rất đơn giản để áp dụng với hàm LARGE.

Cách làm như sau:

E3=LARGE(A2:A6,D3)

hay E3=LARGE(A2:A6,COUNTIF(A2:A6,”>”&C3)+1)

Kết quả như sau:

Xét ví dụ số khác:

Rất thú vị phải không nào. Ngoài ra các bạn có thể tìm hiểu thêm một số bài viết có chủ đề tương tự:

Hướng dẫn cách tìm giá trị lớn hơn gần nhất với giá trị so sánh

Cách sử dụng hàm vlookup kết hợp hàm Match khi tìm theo nhiều cột chứa kết quả

Hàm tìm kiếm VLOOKUP và HLOOKUP trong Excel

Hướng dẫn 4 cách tìm giá trị lớn nhất và nhỏ nhất trong excel

Cách tra cứu ngày lớn nhất hay giá trị thỏa mãn nhiều điều kiện

Tìm giá trị gần đúng nhất bằng VBA


Chia sẻ bài viết này:
  •  
  •  
  •  
  •  
  •  
  •  
  •   
  •