Trong bài viết về Hướng dẫn cách tìm giá trị nhỏ hơn gần nhất với giá trị so sánh, chúng ta đã biết các cách khác nhau để tìm giá trị nhỏ hơn gần nhất với 1 số. Vậy nếu muốn tìm giá trị lớn hơn gần nhất với 1 số thì dùng cách nào? Bài viết này sẽ hướng dẫn chi tiết cho các bạn.
Nội dung chính Show
- Tìm giá trị lớn hơn gần nhất trong danh sách được sắp xếp
- Tìm vị trí số lớn hơn gần nhất với hàm MATCH
- Tìm giá trị số lớn hơn gần nhất với hàm INDEX kết hợp hàm MATCH
- Tìm giá trị lớn hơn gần nhất trong danh sách không được sắp xếp
- Sử dụng hàm COUNTIF kết hợp hàm LARGE
- Video liên quan
Tìm giá trị lớn hơn gần nhất trong danh sách được sắp xếp
Để có thể xác định giá trị lớn hơn gần nhất, chúng ta không thể dùng cách thông thường với hàm LOOKUP hay hàm VLOOKUP. Ở đây chúng ta sẽ tìm hiểu về 2 hàm INDEX và MATCH.
Tìm vị trí số lớn hơn gần nhất với hàm MATCH
Trong các dạng tìm kiếm, việc tìm kiếm bằng hàm MATCH cho phép chúng ta tìm trong danh sách được sắp xếp theo thứ tự giảm dần hoặc tăng dần. Tham số của hàm MATCH gồm:
- Số 1: tìm kiếm theo giá trị nhỏ hơn gần nhất (tương đối)
- Số 0: tìm kiếm theo giá trị bằng (chính xác)
- Số -1: tìm kiếm theo giá trị lớn hơn gần nhất (tương đối)
Như vậy trong trường hợp này, chúng ta tìm theo tham số là -1
Kết quả thu được là:
Có 3 số có giá trị lớn hơn 168, và số 180 là số thứ 3 trong danh sách trên.
Tìm giá trị số lớn hơn gần nhất với hàm INDEX kết hợp hàm MATCH
Hàm INDEX là hàm giúp chúng ta xác định giá trị trong 1 ô, dựa vào vị trí dòng và cột. Trong hướng dẫn về hàm MATCH ở trên, chúng ta đã xác định được vị trí số lớn hơn gần nhất là vị trí thứ mấy, ứng với số dòng. Cột cần tìm là cột số Tiêu chuẩn. Như vậy ta có công thức tại ô E3 là:
E3=INDEX(A2:A6,D3)
hay E3=INDEX(A2:A6,MATCH(C3,$A$2:$A$6,-1))
Kết quả thu được là:
Như vậy chính xác kết quả là số 180 là số lớn hơn gần nhất so với số 168.
Lưu ý: Chỉ áp dụng được trường hợp này khi dãy số được sắp xếp theo thứ tự tăng dần hoặc giảm dần.
Tìm giá trị lớn hơn gần nhất trong danh sách không được sắp xếp
Sử dụng hàm COUNTIF kết hợp hàm LARGE
Trong bài Hướng dẫn cách tìm giá trị nhỏ hơn gần nhất với giá trị so sánh, chúng ta đã biết cách sử dụng hàm COUNTIF với hàm LARGE. Khi tìm số lớn hơn gần nhất, chúng ta sẽ xét ngược lại như sau:
Hàm COUNTIF: Trong hàm COUNTIF, chúng ta đổi dấu so sánh là dấu nhỏ hơn:
D3=COUNTIF(A2:A6,”<“&C3)+1
Trong đó:
COUNTIF(A2:A6,”<“&C3) là đếm số giá trị trong vùng A2:A6 mà nhỏ hơn giá trị ở C3
COUNTIF(A2:A6,”<“&C3)+1 đem kết quả số giá trị nhỏ hơn giá trị C3 cộng thêm 1 để ra vị trí số gần nhất mà lớn hơn số cần tìm.
Hàm SMALL: tham chiếu tới giá tị lớn hơn gần nhất, từ vị trí đã xác định bởi hàm COUNTIF
E3=SMALL(A2:A6,D3)
hay E3=SMALL(A2:A6,COUNTIF(A2:A6,”<“&C3)+1)
Khi xét theo hàm SMALL, chúng ta sẽ tìm số nhỏ thứ mấy trong danh sách. Logic như sau:
- Số lớn hơn gần nhất sẽ là số đứng tiếp theo các số nhỏ hơn số cần tìm (đã xác định bởi hàm COUNTIF ở trên)
- Khi xét theo hàm SMALL, số 1 ứng với số nhỏ nhất, số nhỏ thứ 4 trong danh sách chính là số lớn hơn gần nhất với số cần tìm
Ví dụ với số 195, ta có:
- Số nhỏ hơn gồm các số: 140, 100, 180 ứng với các vị trí A2, A3, A4
- Số tiếp theo sẽ là số 200, chính là số lớn hơn gần nhất với số 195, được xác định bởi hàm SMALL. Kết quả số nhỏ hơn thứ 4 đồng nghĩa với số lớn hơn gần nhất với số 195.
Như vậy qua bài viết này, chúng ta đã biết thêm 1 phương pháp tìm giá trị lớn hơn gần nhất với 1 số bằng cách sử dụng các hàm trong Excel.
Việc sử dụng các hàm này thật linh hoạt và đa dạng phải không nào. Khi chúng ta biết cách sử dụng các hàm trong excel, biết phát triển tư duy logic thì hầu hết mọi vấn đề đều có thể giải quyết được.
Ngoài ra các bạn có thể tham khảo một số bài viết để mở rộng thêm kiến thức:
Hướng dẫn 4 cách tìm giá trị lớn nhất và nhỏ nhất trong excel
Tìm giá trị gần đúng nhất bằng VBA
Hướng dẫn cách lồng ghép hàm, viết hàm lồng nhau trong Excel
Giả sử bạn có một bảng dữ liệu và muốn tìm giá trị lớn nhất ( hoặc nhỏ nhất ) dựa trên nhiều điều kiện .
Tìm với một điều kiện
Ví dụ như hình dưới , trong cột A có tên nhiều sản phẩm trùng nhau và bạn muốn tìm giá trị lớn nhất của KTO chẳng hạn , thì bạn dùng công thức mảng sau
=MAX(IF(A$2:A$13=D2;B$2:B$15))
Rồi bấm tổ hợp phím CTRL_Shift_Enter để thành công thức mảng , hoặc dùng công thức khác
=MAX((A2:A13=D2)*B2:B13)
Rồi bấm tổ hợp phím CTRL_Shift_Enter để thành công thức mảng.
Nếu muốn tìm giá trị nhỏ nhất bạn vẫn dùng công thức mảng nhưng thay Max thành Min
Tìm với nhiều điều kiện
Nếu như bạn có bảng dữ liệu như hình dưới
Muốn tìm dữ liệu lớn nhất thỏa mãn hai điều kiện E2 và E3 thì bạn dùng công thức
=MAX(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13)))
Rồi bấm tổ hợp phím CTRL_Shift_Enter để thành công thức mảng.
Excel cho Microsoft 365 Excel cho Microsoft 365 dành cho máy Mac Excel cho web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Xem thêm...Ít hơn
Hàm MINIFS trả về giá trị tối thiểu trong số các ô được xác định bởi một tập hợp các điều kiện hoặc tiêu chí cho trước.
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
| Dải ô thực tế để xác định giá trị nhỏ nhất. |
| Là tập hợp các ô cần đánh giá theo tiêu chí. |
criteria1 | Là tiêu chí ở dạng số, biểu thức hoặc văn bản xác định ô nào sẽ được đánh giá là nhỏ nhất. Tập hợp tiêu chí này cũng áp dụng cho các hàm MAXIFS, SUMIFS và AVERAGEIFS. |
criteria_range2, | Các phạm vi bổ sung và các tiêu chí được liên kết của chúng. Bạn có thể nhập tối đa 126 cặp phạm vi/tiêu chí. |
Kích cỡ và hình dạng của các tham đối min_range và criteria_rangeN phải giống nhau, nếu không những hàm này sẽ trả về lỗi #VALUE!. .
Sao chép dữ liệu ví dụ trong mỗi bảng sau đây và dán vào ô A1 của một bảng tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem toàn bộ dữ liệu.
Ví dụ 1
89 | 1 |
93 | 2 |
96 | 2 |
85 | 3 |
91 | 1 |
88 | 1 |
Công thức | Kết quả |
=MINIFS(A2:A7,B2:B7,1) | 88 Trong criteria_range1, ô B2, B6 và B7 khớp với tiêu chí 1. Trong các ô tương ứng ở min_range, A7 có giá trị tối thiểu. Do đó, kết quả là 88. |
Ví dụ 2
10 | b |
11 | a |
100 | a |
111 | b |
1 | a |
1 | a |
Công thức | Kết quả |
=MINIFS(A2:A5,B3:B6,"a") | 10 Lưu ý: Criteria_range và min_range không được căn chỉnh nhưng chúng có hình dạng và kích cỡ như nhau. Trong criteria_range1, các ô thứ 1, 2 và 4 khớp với tiêu chí "a". Trong các ô tương ứng ở min_range, A2 có giá trị tối thiểu. Do đó, kết quả là 10. |
Ví dụ 3
10 | b | Doanh nghiệp | 100 |
11 | a | Kỹ thuật | 100 |
12 | a | Doanh nghiệp | 200 |
13 | b | Kỹ thuật | 300 |
14 | b | Kỹ thuật | 300 |
15 | b | Doanh nghiệp | 400 |
Công thức | Kết quả | ||
=MINIFS(A2:A7,B2:B7,"b",D2:D7,">100") | 13 Trong criteria_range1, B2, B5, B6 và B7 khớp với tiêu chí của "b". Trong các ô tương ứng ở criteria_range2, D5, D6 và D7 khớp với tiêu chí > 100. Cuối cùng, trong số các ô tương ứng ở min_range, D5 có giá trị tối thiểu. Do đó, kết quả là 13. |
Ví dụ 4
10 | b | Doanh nghiệp | 8 |
1 | a | Kỹ thuật | 8 |
100 | a | Doanh nghiệp | 8 |
11 | b | Kỹ thuật | 0 |
1 | a | Kỹ thuật | 8 |
1 | b | Doanh nghiệp | 0 |
Công thức | Kết quả | ||
=MINIFS(A2:A7,B2:B7,"b",D2:D7,A8) | 1 Tham đối của criteria2 là A8. Tuy nhiên, vì A8 bị bỏ trống nên được coi là bằng 0 (không). Các ô trong criteria_range2 khớp với 0 là D5 và D7. Cuối cùng, trong số các ô tương ứng ở max_range, A7 có giá trị tối thiểu. Do đó, kết quả là 1. |
Ví dụ 5
10 | b |
1 | a |
100 | a |
1 | b |
1 | a |
1 | a |
Công thức | Kết quả |
=MINIFS(A2:A5,B2:C6,"a") | #VALUE! Vì kích cỡ và hình dạng của min_range và criteria_range không giống nhau nên MINIFS trả về lỗi #VALUE!. . |
Ví dụ 6
10 | b | Doanh nghiệp | 100 |
1 | a | Kỹ thuật | 100 |
100 | a | Doanh nghiệp | 200 |
1 | b | Kỹ thuật | 300 |
1 | a | Kỹ thuật | 100 |
1 | a | Doanh nghiệp | 400 |
Công thức | Kết quả | ||
=MINIFS(A2:A6,B2:B6,"a",D2:D6,">200") | 0 Không có ô nào khớp với tiêu chí. |
Bạn luôn có thể hỏi một chuyên gia trong Cộng đồng Kỹ thuật Excel hoặc nhận sự hỗ trợ trongCộng đồng trả lời.
Hàm MAXIFS
Hàm SUMIFS
Hàm AVERAGEIFS
Hàm COUNTIFS