Tìm giá trị lớn hơn gần nhất trong Excel

Bài toán dưới đây sử dụng cách dò tìm gần đúng trong Excel khi bạn cần thực hiện dò giá trị nhưng giá trị ở bảng dò và giá trị dò không hoàn toàn giống nhau. Ở đây sử dụng 2 cách: 1 là sử dụng công thức mảng, 2 là sử dụng hàm Lookup kết hợp với hàm Len và SUBSTITUTE

• Sheet 1 chứa danh sách mã hàng và tên hàng

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:

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.

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

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.

Để 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

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

zik.vn – Nền tảng học online duy nhất tại Việt Nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm

Với sứ mệnh: “Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển zik.vn đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Zik.vn tự hào khi được đồng hành cùng:

• 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…

• 100.000+ học viên trên khắp Việt Nam

Tìm hiểu ngay các khóa học của Zik.vn TẠI ĐÂY