Tin NóngTrong bảng tính excel nếu muốn tìm giá trị lớn nhất trong dải số thì chúng ta sẽ dùng hàm Max. Vậy khi muốn tìm số lớn thứ 2 trong excel thì phải làm sao? Giải pháp đơn giản và dễ dàng nhất trong trường hợp này là bạn hãy dùng hàm LARGE trong excel. Trong nội dung bài viết hôm nay, Unica sẽ chia sẻ với bạn hàm LARGE excel, cách dùng hàm LARGE kèm ví dụ minh họa dễ hiểu.
1. Hàm LARGE là gì? Ý nghĩa của hàm LARGE trong excel
Hàm LARGE trong excel là hàm tính giúp chúng ta tìm giá trị lớn thứ hai, thứ ba hoặc thứ k trong bảng tính.
Nhờ có hàm LARGE chúng ta có thể xác định được những giá trị thứ k của bảng dữ liệu được sắp xếp theo thứ tự giảm dần một cách nhanh chóng. Điều này giúp chúng ta phân tích, quản lý dữ liệu tốt hơn.
Bạn có thể sử dụng hàm LARGE để thay thế hàm Max tìm giá trị lớn nhất trong bảng dữ liệu. Chỉ cần bạn chọn vị trí thứ hạng cần tìm là 1, tức là vị trí thứ nhất.
Hàm LARGE trong excel là hàm tìm giá trị thứ hai, ba hoặc thứ k trong excel
2. Công thức hàm LARGE
Hàm LARGE trong excel có cú pháp như sau: =LARGE(array,k)
Trong đó:
– array: là phạm vi dữ liệu cần xác định giá trị lớn thứ k
– k: Là thứ hạng của giá trị được sắp xếp theo thứ tự từ lớn đến nhỏ.
3. Cách dùng hàm LARGE trong excel
Dưới đây là ví dụ minh họa giúp bạn biết cách sử dụng hàm LARGE để tìm giá trị lớn thứ 2 trong excel.
Chúng ta có bảng dữ liệu gồm tên nhân viên và doanh thu sau đây:
Cho một bảng dữ liệu
Yêu cầu: Hãy dùng hàm LARGE để tìm giá trị lớn thứ 2 trong cột doanh thu.
Cách thực hiện:
– Bước 1: Khi dùng hàm tìm giá trị lớn thứ 2 trong excel, bạn hãy xác định các thành phần theo công thức gốc. Ở đây chúng ta có:
+ array là cột doanh thu. Cụ thể là vùng B2:B8
+ k là giá trị lớn thứ 2. Có nghĩa là k=2.
– Bước 2: Tiếp theo chúng ta hãy lập thành công thức cụ thể
=LARGE(B2:B8,2)
Để hiểu rõ hơn về công thức áp dụng bạn hãy tham khảo hình phân tích công thức ở bên dưới đây
Nhập hàm LARGE trong excel
– Bước 3: Nhập công thức vào bảng tính, nhấn Enter để nhận kết quả.
Nhấn Enter để nhận kết quả
Chúng tôi sẽ thêm một ví dụ về hàm LARGE trong excel để bạn nắm vững hơn cách dùng hàm LARGE
Chúng ta có bảng dữ liệu
Ví dụ cho một bảng dữ liệu
Yêu cầu: Hãy tìm giá trị lớn nhất, lớn thứ 2 và giá trị nhỏ nhất trong bảng dữ liệu.
Cách thực hiện:
– Bước 1: Căn cứ theo bảng dữ liệu gốc để xác định các thành phần trong công thức gốc.
Ở đây chúng ta có array là A2:B2. và k lần lượt là 1,2,10
– Bước 2: Lập công thức LARGE cụ thể:
+ Tìm giá trị lớn nhất =LARGE(A2:B6,1)
+ Tìm giá trị lớn thứ hai =LARGE(A2:B6,2)
+ Tìm giá trị nhỏ nhất =LARGE(A2:B6,10)
– Bước 3: Áp dụng công thức vào bảng tính để nhận kết quả
Kết quả nhập hàm vào bảng tính
4. Hàm LARGE kết hợp với một số hàm khác
Ở phần trên, chúng tôi đã hướng dẫn bạn cách dùng hàm LARGE cơ bản trong excel. Ở phần dưới đây chúng tôi sẽ hướng dẫn bạn cách dùng hàm LARGE nâng cao bằng cách kết hợp với các hàm khác trong excel.
4.1. Hàm LARGE kết hợp hàm INDEX và hàm MATCH
Khi bạn kết hợp hàm LARGE với hàm Index và hàm Match, bạn sẽ truy xuất được dữ liệu liên quan đến giá trị cao thứ k mà bạn muốn tìm.
Dưới đây là ví dụ minh hoa cụ thể:
Chúng ta có bảng tính:
Ví dụ bảng tính báo giá sản phẩm iphone
Yêu cầu: Hãy tìm tên của sản phẩm có giá bán cao nhất.
Cách thực hiện:
– Bước 1: Sử dụng hàm LARGE để xác định giá trị lớn nhất trong bảng, với công thức cụ thể là: =LARGE(D3:D7,1)
Nhập công thức hàm vào bảng tính
– Bước 2: Dùng hàm Index và Match để tìm tên sản phẩm có giá trị lớn nhất với công thức cụ thể sau: =INDEX(C3:C7,MATCH(D9,D3:D7,0)
Trong đó:
– C3:C7: dải dữ liệu chứa tên sản phẩm có giá trị cao nhất cần tìm.
– D9: Giá trị lớn nhất được tìm bằng hàm LARGE.
– D3:D7: dải tham chiếu giá tiền sản phẩm có giá trị cao thứ nhất cần tìm.
– 0 (Exact Match): hàm Match sẽ tìm kiếm giá trị lớn nhất một cách chính xác với lookup_value
Kết quả tìm giá trị lớn thứ nhất
– Bước 3: Áp dụng công thức vừa phân tích vào bảng tính, nhấn Enter để nhận kết quả
Kết quả cuối cùng nhận được
4.2. Hàm LARGE kết hợp chức năng định dạng theo điều kiện
Chúng ta có bảng tính sau đây:
Ví dụ cho một bảng dữ liệu
Yêu cầu: Tự động tô màu phân biệt những nhân viên theo doanh thu lớn thứ 1, thứ 2, thứ 3.
Cách thực hiện:
– Bước 1: Phân tích bảng dữ liệu và yêu cầu.
Trong trường hợp này chúng ta có 2 yêu cầu:
– Tìm giá trị doanh thu lớn thứ 1, thứ 2, thứ 3 trong bảng dữ liệu.
– Tự động tô màu phân biệt, đây chính là chức năng định dạng theo điều kiện.
– Bước 2: Lập công thức để tính giá trị doanh thu lớn nhất =LARGE(B2:B8,1)
Trong đó:
– Vùng cần xét là: B2:B8
– k=1
Khi áp dụng vào bảng tính chúng ta sẽ có kết quả như sau:
Kết quả khi áp dụng vào bảng tính
– Bước 3: Sử dụng chức năng định dạng theo điều kiện (conditional formatting) để tô màu tự động cho nhân viên có doanh thu lớn nhất.
Trình tự thực hiện như sau:
+ Nhấp chuột trái chọn vùng dữ liệu. Vùng áp dụng cụ thể là A2:B8
+ Bạn vào tab Home trên thanh công cụ, chọn mục Conditional Formatting.
Vào tab Home chọn Conditional Formatting
– Khi bảng tùy chọn của cửa sổ Conditional Formatting hiển thị, bạn hãy chọn New Rule…
Chọn New Rule…
– Khi hộp thoại hiển thị, bạn hãy chọn “Use a formula to determine which cells to format”
– Khi hộp thoại mới hiển thị, ở mục “Format values where this formula is true”, bạn hãy nhập công thức sau đây
=$B2=LARGE($B$2:$B$8,1)
Nhập công thức vào “Format values where this formula is true”
– Bạn hãy quan sát ở mục Preview đang hiển thị “No format Set”. Lúc này để tự động tô màu cho nhân viên có doanh thu cao nhất bạn hãy tiếp tục chọn “Format” ở bên cạnh “No Format Set”. Khi hộp thoại hiển thị, bạn hãy chọn tab Fill, tiếp tục chọn màu bạn muốn sử dụng. Sau đó nhấn Ok.
Tự động tô màu cho ô nhân viên
Lúc này ở hộp thoại Preview trong mục New Formatting Rule đã đổi thành màu bạn đã chọn.
Mục New Formatting Rule trong hộp thoại Preview đổi thành màu đỏ
Bạn hãy nhấn chọn Ok để tự động tô màu cho nhân viên có doanh thu lớn nhất.
Và đây là kết quả hiển thị.
Kết quả hiển thị cuối cùng
Để tự động tô màu cho nhân viên có doanh thu lớn thứ 2 và nhân viên có doanh thu lớn thứ 3, bạn hãy áp dụng trình tự theo các bước hướng dẫn ở trên. Bạn chỉ cần thay công thức ở mục “Format values where this formula is true” như sau:
Đối với nhân viên có doanh thu lớn thứ 2:
=$B2=LARGE($B$2:$B$8,2)
Đối với nhân viên có doanh thu lớn thứ 3:
=$B2=LARGE($B$2:$B$8,3)
Sau khi hoàn thành chúng ta sẽ có bảng dữ liệu như sau:
Bảng tô màu cho nhân viên doanh thu thứ 2 và thứ 3
Lưu ý: Để chúng ta có thể dễ dàng phân biệt nhân viên có doanh thu lớn, bạn hãy lập bảng quy định màu phân biệt thứ hạng doanh thu như hình minh họa ở trên.
5. Một số lưu ý khi dùng hàm LARGE
Khi bạn sử dụng hàm LARGE trong excel, bạn cần lưu ý một số điểm sau:
– Đối với array, bạn nên cố định vùng để khi copy công thức vẫn đảm bảo kết quả trả về luôn chính xác.
– Nếu k = 1 thì kết quả trả về sẽ là giá trị lớn nhất trong bảng.
– Nếu k >1 và là số nguyên dương (tạm gọi là x) thì kết quả trả về là giá trị đứng thứ x, theo thứ tự từ lớn đến nhỏ.
– Nếu k=n thì kết quả trả về sẽ là giá trị nhỏ nhất có trong bảng dữ liệu.
– Nếu k n: Với n là số dữ liệu trong bảng tham chiếu thì kết quả trả về giá trị lỗi #NUM!
– Nếu giá trị tham chiếu không phải là định dạng số thì kết quả trả về sẽ là #Value! error.
6. Tổng kết
Cách sử dụng hàm LARGE trong excel khá đơn giản, dễ nhớ, dễ thực hiện, ít sai sót. Tuy nhiên, trên thực tế hàm LARGE sẽ được kết hợp với các hàm khác để hỗ trợ giải quyết công việc. Nếu bạn muốn học thêm về hàm LARGE nâng cao và các hàm nâng cao khác trong excel hãy đăng ký ngay khóa học excel nâng cao.