Hàm LARGE trong Excel để nhận giá trị cao nhất thứ n

Bài viết này sẽ hướng dẫn bạn cách sử dụng hàm LARGE trong Excel với nhiều ví dụ về công thức.

Khi phân tích một tập hợp các số, thường có ý nghĩa khi tìm những số lớn nhất. Nhận được giá trị cao nhất thật dễ dàng với Hàm MAX. Khi nói đến việc nhắm mục tiêu một giá trị lớn nhất cụ thể, hãy giả sử 2^nd hoặc 3^rd số lớn nhất trong một tập dữ liệu, hàm LARGE có ích.

Hàm LARGE trong Excel

Hàm LARGE trong Excel được sử dụng để trả về giá trị lớn nhất thứ n từ một tập dữ liệu số. Ví dụ, nó có thể tính điểm cao nhất, 2^nd đơn hàng lớn nhất, 3^rd đặt kết quả, v.v.

Cú pháp bao gồm hai đối số, cả hai đều là bắt buộc:

LARGE (mảng, k)

Ở đâu:

• Mảng – một phạm vi hoặc một mảng để tìm kiếm giá trị lớn nhất.
• K – vị trí từ giá trị cao nhất đến giá trị lớn nhất thứ k trong tập dữ liệu.

LARGE được phân loại theo Chức năng thống kê. Nó có sẵn trong tất cả các phiên bản Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và các phiên bản cũ hơn.

3 điều cần biết về hàm LARGE

Trước khi chúng ta đi đến những điều thực tế hơn và bắt đầu xây dựng công thức của riêng mình, hãy chú ý đến 3 sự kiện đơn giản giải thích những điều cần thiết:

1. Hàm LARGE chỉ xử lý các giá trị số. Ô trống, bản vănvà hợp lý giá trị bị bỏ qua.
2. Nếu mảng chứa bất kỳ sai sót, một lỗi được trả lại.
3. Trong trường hợp mảng chứa n giá trị, LARGE (mảng, 1) sẽ trả về giá trị lớn nhất và LARGE (mảng, n) sẽ trả về giá trị nhỏ nhất.

Công thức LARGE cơ bản

Ở dạng cơ bản, một công thức LARGE trong Excel rất dễ xây dựng. Đối với đối số thứ nhất, bạn cung cấp một phạm vi giá trị số. Trong 2^nd đối số, bạn xác định vị trí từ lớn nhất đến trở lại.

Trong bảng mẫu dưới đây, giả sử bạn muốn biết 2^nd điểm lớn nhất. Điều này có thể được thực hiện với công thức sau:

=LARGE(B2:B10, 2)

Cách sử dụng công thức LARGE trong Excel – ví dụ

Và bây giờ, hãy xem xét các trường hợp sử dụng cụ thể hơn và xem hàm LARGE có thể hữu ích như thế nào.

Cách lấy giá trị N hàng đầu trong Excel

Để nhận các giá trị 3, 5, 10, v.v. lớn nhất bằng một công thức, hãy thực hiện các bước sau:

1. Nhập các vị trí quan tâm trong các ô riêng biệt. Những con số này sẽ được sử dụng làm k các giá trị.
2. Lập công thức LARGE áp dụng tuyệt đối tham chiếu phạm vi cho mảng ($ B $ 2: $ B $ 10 trong trường hợp của chúng tôi) và quan hệ tham chiếu ô cho k (D3).
3. Nhập công thức vào ô trên cùng, sau đó kéo công thức đó vào các ô nhắc nhở. Làm xong!

Ví dụ, chúng ta sẽ tìm 3 điểm hàng đầu trong bảng dưới đây. Đối với điều này, chúng tôi nhập các số 1, 2 và 3 tương ứng trong D3, D4 và D5 và nhập công thức sau vào E3:

=LARGE($B$2:$B$10, D3)

Kéo nó qua E4, và bạn sẽ nhận được kết quả này:

Thay vì nhập các vị trí trên trang tính, bạn có thể sử dụng hàm ROWS với mở rộng phạm vi tham chiếu để tạo ra k giá trị tự động như được giải thích trong Công thức Excel để tìm N giá trị hàng đầu trong danh sách.

=LARGE($B$2:$B$10, ROWS(B$2:B2))

Cách tính tổng hoặc trung bình giá trị N lớn nhất

Đến Tổng n giá trị hàng đầu trong tập dữ liệu, bạn có thể sử dụng LARGE cùng với Sumproduct hoặc là Sum theo cách này:

SUMPRODUCT (LARGE (mảng, {1,…, n }))

Hoặc là

SUM (LARGE (mảng, {1,…, n}))

Đến Trung bình cộng cao nhất n giá trị, kết hợp Average và các hàm LARGE:

AVERAGE (LARGE (mảng, {1,…, n}))

Để xem nó hoạt động như thế nào trong thực tế, hãy tìm điểm trung bình của 3 điểm hàng đầu trong bảng mẫu của chúng tôi. Đối với điều này, chúng tôi đang sử dụng công thức này:

=AVERAGE(LARGE(B2:B10, {1,2,3}))

Để cộng 3 điểm cao nhất, công thức là:

=SUM(LARGE(B2:B10, {1,2,3}))

Ghi chú. Trong trường hợp bạn sử dụng một dải ô thay vì một hằng số mảng cho k, bạn cần nhấn Ctrl + Shift + Enter để biến nó thành một công thức mảng. Trong Excel 365 hỗ trợ mảng động, tất cả các công thức có thể được hoàn thành như bình thường bằng cách nhấn Đi vào Chìa khóa.

Cách hoạt động của các công thức này:

Thông thường, hàm LARGE trả về một giá trị duy nhất dựa trên k con số. Trong các công thức này, chúng tôi cung cấp một mảng hằng số như {1,2,3} cho k đối số buộc nó trả về một mảng giá trị. Mảng đó chuyển đến hàm bên ngoài để được tính tổng hoặc tính trung bình.

Nhận dữ liệu được liên kết với giá trị lớn nhất thứ n

Để truy xuất thông tin liên quan đến các giá trị lớn nhất, chỉ cần lồng hàm LARGE trong trang chuẩn INDEX MATCH công thức:

Index(return_array, Match (LARGE (lookup_array, n), lookup_array, 0))

Ở đâu:

• Return_array là một phạm vi để trích xuất các kết quả phù hợp.
• Lookup_array là một dãy số để xếp hạng từ cao nhất.
• N là vị trí của giá trị lớn nhất cần tìm kiếm.

Ví dụ: để lấy tên của một học sinh đã đạt thành tích tốt nhất trong các kỳ thi, hãy nhập công thức này vào F3:

=INDEX($A$2:$A$10, MATCH(LARGE($B$2:$B$10, $D3), $B$2:$B$10, 0))

Trong đó A2: A10 là mảng trả về (tên), B2: B10 là mảng tra cứu (điểm số) và D3 là vị trí từ lớn nhất.

Để tìm ra ai có 2^nd và 3^rd điểm cao nhất, sao chép forma sang F4 và F5:

Ghi chú:

• Giải pháp này hoạt động tốt cho các giá trị duy nhất. Nếu tập dữ liệu của bạn chứa các số trùng lặp, “ràng buộc” trong xếp hạng có thể xảy ra, điều này sẽ tạo ra kết quả không chính xác. Để ngăn điều này xảy ra, hãy sử dụng công thức để xử lý các mối quan hệ.
• Trong Excel 365, bạn có thể sử dụng các hàm mảng động để tìm ra một giải pháp đơn giản hơn nhiều có thể tự động giải quyết vấn đề ràng buộc. Để biết chi tiết đầy đủ, vui lòng xem Cách lọc N giá trị hàng đầu trong Excel.

Công thức LARGE để sắp xếp các số giảm dần

Để nhanh chóng sắp xếp danh sách các số trong Excel 365, bạn có thể sử dụng Hàm SORT. Trong Excel 2019, 2016 và các phiên bản trước đó không hỗ trợ mảng động, chúng tôi phải dựa vào hàm LARGE cũ tốt để sắp xếp giảm dần và Small để sắp xếp tăng dần.

Đối với ví dụ này, chúng tôi sẽ sắp xếp các số trong A2: A10 từ cao nhất đến thấp nhất. Để hoàn thành, chúng tôi sẽ cần hàm ROWS với tham chiếu phạm vi mở rộng để tăng k đối số bằng 1 với mọi hàng:

=LARGE($A$2:$A$10, ROWS(A$2:A2))

Công thức trên chuyển đến ô trên cùng (C2). Và sau đó, bạn kéo nó qua bao nhiêu ô có số trong danh sách ban đầu (trong trường hợp của chúng tôi là C2: C10):

Công thức LARGE cho ngày và giờ

Như bạn đã biết, ngày và giờ trong Excel là các giá trị số: ngày được lưu trữ dưới dạng số nguyên và lần dưới dạng số thập phân. Điều đó có ý nghĩa gì đối với chúng ta? Hàm LARGE tính toán các giá trị ngày và thời gian theo cách giống hệt với các số. Nói cách khác, công thức bạn đã sử dụng cho các số cũng sẽ hoạt động cho ngày và giờ!

Giả sử bạn có danh sách các ngày trong B2: B10, công thức sau sẽ trả về N ngày gần đây nhất, tùy thuộc vào số lượng ô bạn sao chép nó vào:

=LARGE($B$2:$B$10, ROWS(B$2:B2))

Công thức tương tự cũng có thể tìm thấy dài nhất 3 lần:

Nhận một ngày trong tương lai gần nhất với hôm nay hoặc ngày cụ thể

Ví dụ này cho thấy cách sử dụng cụ thể hơn của hàm LARGE trong Excel với ngày tháng.

Từ danh sách các ngày trong B2: B10, giả sử bạn muốn trả lại một ngày trong tương lai gần nhất với ngày hôm nay. Để hoàn thành nhiệm vụ, chúng tôi sẽ sử dụng COUNTIF và TODAY các hàm cùng nhau để tính giá trị cho k đối số của LARGE:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY()))

Để tìm ngày tiếp theo trừ một ngày, công thức là:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY())-1)

Để tìm một ngày đến ngay sau ngày nhất định, nhập ngày mục tiêu vào một số ô (trong ví dụ này là E3) và nối tham chiếu ô đó trong tiêu chí của COUNTIF:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1))

Trong trường hợp không tìm thấy ngày phù hợp với tiêu chí của bạn, bạn có thể sử dụng Hàm IFERROR dưới dạng “trình bao bọc” để bắt lỗi và thay thế nó bằng bất kỳ văn bản nào bạn thấy phù hợp:

=IFERROR(LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1)), "Not found")

Công thức này hoạt động như thế nào:

Về bản chất, bạn sử dụng hàm COUNTIF để đếm số ngày trong danh sách lớn hơn ngày hôm nay hoặc một ngày cụ thể. Số đếm đó là k giá trị hàm LARGE cần.

Tại thời điểm viết bài, ngày hôm nay là ngày 7 tháng 10 năm 2020. Hàm COUNTIF với “>” & TODAY () cho các tiêu chí được xác định rằng trong B2: B10, có 4 ngày lớn hơn 7-10-2020. Có nghĩa là, ngày lớn thứ 4 trong danh sách là ngày gần nhất trong tương lai mà chúng tôi đang tìm kiếm. Vì vậy, chúng tôi cắm COUNTIF vào 2^nd đối số LARGE và nhận được kết quả mong muốn:

=LARGE($B$2:$B$10, 4)

Tiếp theo nhưng một ngày là 3^rd ngày lớn nhất trong trường hợp của chúng tôi. Để có được nó, chúng tôi trừ 1 từ kết quả của COUNTIF.

Hàm LARGE trong Excel không hoạt động

Một công thức LARGE có thể tạo ra lỗi #NUM! lỗi vì những lý do sau:

• Cung cấp mảng trống hoặc không chứa một giá trị số.
• Các k giá trị là một số âm.
• Các k giá trị lớn hơn số giá trị trong mảng.

Đó là cách sử dụng hàm LARGE trong Excel để tìm các giá trị cao nhất trong tập dữ liệu. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Source link

The post Hàm LARGE trong Excel để nhận giá trị cao nhất thứ n appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.

source https://ketoanmvb.com/ham-large-trong-excel-de-nhan-gia-tri-cao-nhat-thu-n.html