Công thức hàm Large IF trong Excel để tìm giá trị lớn nhất

Hướng dẫn cho biết cách sử dụng công thức hàm LARGE IF trong Excel với một hoặc một số tiêu chí.

Trong khi làm việc với dữ liệu số trong Excel, bạn có thể thường muốn tìm các số lớn nhất. Trong một tập dữ liệu lớn, có thể cần phải thu hẹp kết quả bằng cách sử dụng một hoặc nhiều tiêu chí. Tin xấu là hàm LARGEIF không tồn tại trong Excel. Tin tốt là bạn có thể dễ dàng xây dựng công thức LARGE IF của riêng mình 🙂

Công thức LARGE IF cơ bản trong Excel

Để nhận giá trị lớn nhất thứ n trong tập dữ liệu với điều kiện, bạn có thể sử dụng hàm LARGE và IF cùng nhau:

{=LARGE(IF(criteria_range=criteria, values), n)}

Trong trường hợp n là 1 ^st , 2 ^nd , 3 ^thứ vv giá trị cao nhất để trở lại.

Xin lưu ý rằng đó là một công thức mảng phải được nhập bằng cách nhấn Ctrl + Shift + Enter các phím đồng thời. Trong Excel 365, do hỗ trợ mảng động , nó có thể được nhập bình thường bằng cách nhấn Đi vào Chìa khóa.

Để xem công thức đang hoạt động, vui lòng xem xét ví dụ sau.

Trong bảng dưới đây, giả sử bạn muốn tìm 3 điểm lớn nhất trong các môn học khác nhau. Để hoàn tất, chúng tôi nhập tên chủ đề vào các ô riêng biệt (F2, G2 và H2), sau đó sử dụng công thức này:

=LARGE(IF($B$2:$B$15=F$2, $C$2:$C$15), $E3)

Trong đó B2: B15 là danh sách các môn học ( tiêu chí_ dải ô), C2: C15 là điểm số ( giá trị ) và E3 là n .

Tất nhiên, bạn có thể mã hóa tên môn học trong công thức (ví dụ: $ B $ 2: $ B $ 15 = “Khoa học”), nhưng việc sử dụng tham chiếu ô làm cho giải pháp linh hoạt hơn.

Công thức trên chuyển đến F3, sau đó bạn kéo nó xuống 2 hàng và 2 cột sang bên phải. Nhờ sử dụng khéo léo các tham chiếu ô tuyệt đối và hỗn hợp , công thức sẽ tự động điều chỉnh khi được sao chép sang các ô khác, cho phép bạn nhận được tất cả kết quả chỉ trong một lần!

Nếu cần, bạn thậm chí có thể làm mà không cần gõ n số trên trang tính. Thay vào đó, hãy tạo chúng trực tiếp trong công thức bằng cách nhúng hàm ROWS với tham chiếu phạm vi mở rộng như sau:

=LARGE(IF($B$2:$B$15=E$2,$C$2:$C$15), ROWS(A$2:A2))

Cơ chế mở rộng phạm vi được giải thích chi tiết trong ví dụ này (công thức Excel để tìm các giá trị hàng đầu 3, 5, 10, v.v.) và ở đây tôi sẽ chỉ hiển thị kết quả:

Trong trường hợp không tìm thấy giá trị lớn nhất cụ thể với điều kiện nhất định, công thức sẽ trả về lỗi #NUM. Để ngăn điều này xảy ra, hãy bọc công thức IF LARGE bên trong hàm IFERROR và thay thế lỗi bằng bất kỳ giá trị nào bạn thấy phù hợp, ví dụ: bằng dấu “-” hoặc “Không tìm thấy”:

=IFERROR(LARGE(IF($B$2:$B$15=$F$2, $C$2:$C$15), $E3), "Not found")

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

Như bạn có thể biết, hàm LARGE trong Excel trả về giá trị cao nhất thứ n được chỉ định trong đối số ^{thứ 2} từ một mảng được xác định trong đối số ^{thứ nhất} . Trong trường hợp của chúng ta, mảng cần được giới hạn ở chỉ số điểm của một chủ đề nhất định. Để hoàn thành việc này, chúng tôi nhúng một câu lệnh IF vào đối số mảng , so sánh từng chủ đề trong B2: B15 với mục tiêu trong F2 ( Lịch sử ):

IF($B$2:$B$15=$F$2, $C$2:$C$15)

Vì IF thực hiện kiểm tra logic trên một mảng số, đầu ra cũng là một mảng, trong đó điểm Lịch sử được biểu thị bằng số và tất cả các điểm khác bằng giá trị FALSE:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;247;FALSE;FALSE;235;FALSE;210;FALSE;FALSE}

Hàm LARGE bỏ qua lỗi trong mảng và tìm giá trị lớn nhất thứ n cụ thể trong số các điểm Lịch sử .

Excel LARGE IF với nhiều tiêu chí

Để kiểm tra một số điều kiện trong một công thức, hãy sử dụng một trong các phương pháp sau:

Lồng nhiều câu lệnh IF vào nhau:

{=LARGE(IF(criteria_range1=criteria1, IF(criteria_range2=criteria2, values)), n)}

Nhân một số biểu thức phạm vi = tiêu chí:

{=LARGE(IF((criteria_range1=criteria1) * (criteria_range2=criteria2), values), n)}

Trong các phiên bản Excel trước động, cả hai phải được nhập dưới dạng công thức mảng bằng cách nhấn Ctrl + Shift + Enter. Trong Excel 365, chúng cũng có thể hoạt động như các công thức thông thường.

Và bây giờ, chúng ta hãy thử các kỹ thuật này trên bảng mẫu của chúng tôi. Đối với điều này, chúng tôi sẽ thêm một cột mới, có tên Nhóm và sử dụng một trong các công thức dưới đây để trả về 3 điểm cao nhất trong một chủ đề nhất định trong một nhóm nhất định:

=LARGE(IF($B$2:$B$15=$G$1, IF($C$2:$C$15=$G$2, $D$2:$D$15)), F6)

=LARGE(IF(($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2), $D$2:$D$15), F6)

Ở đâu:

• B2: B15 là các nhóm (c riteria_range1 )
• G1 là nhóm mục tiêu (c riteria1 )
• C2: C15 là danh sách các môn học (c riteria_range2 )
• G1 là đối tượng đích (c nghi thức1 )
• D2: D15 là điểm số ( giá trị )
• F6 là n

Nhập công thức vào một trong hai công thức trong G6 và sao chép nó qua G8. Kết quả, bạn sẽ đạt 3 điểm Khoa học lớn nhất trong bảng A.

Khi thích hợp, bạn có thể đặt tiêu chí trực tiếp vào công thức như sau:

=LARGE(IF(($B$2:$B$15="A") * ($C$2:$C$15="Science"), $D$2:$D$15), F6)

Nhưng hãy nhớ điều này sẽ yêu cầu cập nhật công thức mỗi khi tiêu chí thay đổi.

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

Trong các công thức trên, chúng tôi sử dụng câu lệnh IF để kiểm tra hai tiêu chí khác nhau, vì vậy chỉ những giá trị mà cả hai điều kiện đều ĐÚNG mới được đưa vào mảng của hàm LARGE.

Công thức 1 (IF lồng nhau):

Kiểm tra logic của câu lệnh IF đầu tiên so sánh danh sách các nhóm với nhóm đích: $ B $ 2: $ B $ 15 = $ G $ 1. Kết quả của bài kiểm tra là một mảng các giá trị TRUE và FALSE, trong đó TRUE đại diện cho nhóm A và FALSE bất kỳ nhóm nào khác:

{TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}

Theo cách tương tự, IF thứ hai so sánh danh sách các môn học với mục tiêu ( Khoa học ) và trả về một mảng TRUE và FALSE khác:

{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

Đối với các mục có TRUE trong cả hai mảng, công thức IF lồng nhau trả về điểm số ( value_if_true ). Các mục không đáp ứng tiêu chí được biểu thị bằng các giá trị FALSE:

{287;FALSE;275;FALSE;FALSE;FALSE;FALSE;FALSE;237;FALSE;FALSE;FALSE;190;FALSE}

Mảng cuối cùng này được chuyển cho hàm LARGE, từ đó nó chọn giá trị lớn nhất thứ n.

Công thức 2 (nhân tiêu chí):

Trong công thức này, chúng tôi đánh giá hai tiêu chí khác nhau trong một phép thử logic duy nhất. Phép nhân chuyển đổi các giá trị logic thành 1 (TRUE) và 0 (FALSE). Và bởi vì nhân với 0 luôn cho không, nên mảng kết quả có 1 cho các mục đáp ứng cả hai tiêu chí:

{1;0;1;0;0;0;0;0;1;0;0;0;1;0}

Hàm IF đánh giá mảng 1 và 0 và chuyển điểm tương ứng với 1 thành LARGE.

Công thức IF LARGE với nhiều tiêu chí (HOẶC logic)

Để công thức IF LARGE hoạt động với logic HOẶC, tức là khi điều kiện này hoặc điều kiện đó là ĐÚNG, hãy tính tổng các biểu thức phạm vi = tiêu chí thay vì nhân chúng:

{=LARGE(IF((criteria_range1=criteria1) + (criteria_range2=criteria2), values), n)}

Giả sử bạn muốn tìm 3 điểm lớn nhất trong hai môn học khác nhau, giả sử như Lịch sử và Văn học . Đối với Excel, bạn hình thành nhiệm vụ theo cách khác: trả về điểm số lớn nhất thứ n nếu Chủ đề là Lịch sử HOẶC Văn học .

Với tiêu chí 1 ( Văn học ) ở F1 và tiêu chí 2 ( Lịch sử ) ở F2, công thức như sau:

=LARGE(IF(($B$2:$B$15=$F$1) + ($B$2:$B$15=$F$2), $C$2:$C$15), E6)

Trong đó B2: B15 là các môn học, C2: C15 là điểm số và E6 là giá trị lớn nhất thứ n để trả về.

Hoàn thành công thức bằng cách nhấn Ctrl + Shift + Enter các phím với nhau và bạn sẽ nhận được kết quả này:

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

Trong công thức mảng, phép toán cộng hoạt động giống như toán tử OR :

Biểu thức range = tiêu chí trả về hai mảng giá trị TRUE và FALSE giống như những giá trị được thảo luận trong ví dụ trước. Sau khi thêm các phần tử của các mảng này, chúng ta nhận được một mảng gồm 1 và 0, trong đó 1 tương ứng với các mục đáp ứng một trong hai điều kiện và 0 tương ứng với các mục không đáp ứng bất kỳ điều kiện nào:

{0;1;0;1;0;0;1;1;0;1;0;1;0;1}

Hàm IF kiểm tra tất cả các phần tử của mảng trên và chuyển điểm số tương ứng với 1 cho hàm LARGE:

{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;210;FALSE;125}

FILTER n giá trị hàng đầu dựa trên tiêu chí

Các giải pháp này sử dụng một số hàm mảng động chỉ có sẵn trong Excel 356. Trong Excel 2019, Excel 2016 và các phiên bản trước đó, các công thức này sẽ không hoạt động.

Trong Excel 365, có một cách nữa để lấy N giá trị hàng đầu với các điều kiện. Cái hay của cách tiếp cận này là không giống như các công thức mảng CSE truyền thống, các công thức động được hoàn thành theo cách thông thường bằng cách nhấnĐi vàoChìa khóa. Hơn nữa, công thức chỉ cần được nhập vào một ô và tự động điền trên toàn bộ phạm vi tràn .

Công thức 1. Lọc n giá trị hàng đầu với một điều kiện

Để tìm các giá trị lớn nhất dựa trên điều kiện, hãy sử dụng công thức chung sau:

SORT(FILTER(values, (values >=LARGE(IF(criteria_range=criteria, values), n)) * (criteria_range=criteria)), 1, -1)

Trong đó n là số mục nhập hàng đầu cần trích xuất.

Ví dụ: để đạt được 3 điểm cao nhất trong một môn học nhất định, công thức trong E4 là:

=SORT(FILTER(C2:C15, (C2:C15>=LARGE(IF(B2:B15=F1, C2:C15), 3)) * (B2:B15=F1)), 1, -1)

Trong đó C2: C15 là điểm, B2: B15 là đối tượng và F1 là đối tượng quan tâm. Bởi vì công thức chỉ được nhập trong một ô, bạn không cần bận tâm về việc khóa các phạm vi và ô có tham chiếu tuyệt đối.

Nếu bạn muốn truy xuất không chỉ điểm số mà còn cả dữ liệu liên quan, hãy thực hiện các điều chỉnh sau đối với công thức:

• Đối với đối số mảng của FILTER, cung cấp toàn bộ bảng (A2: C15).
• Trong đối số sort_index của SORT, chỉ ra số cột để sắp xếp theo ( cột ^thứ 3 trong trường hợp của chúng tôi).

=SORT(FILTER(A2:C15, (C2:C15>=LARGE(IF(B2:B15=F1, C2:C15), 3)) * (B2:B15=F1)), 3, -1)

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

Hàm FILTER trong Excel lọc một mảng dữ liệu dựa trên tiêu chí trong đối số bao gồm . Trong trường hợp của chúng tôi, các tiêu chí như sau:

(C2:C15>=LARGE(IF(B2:B15=F1, C2:C15), 3)) * (B2:B15=F1))

Phần bên trái của biểu thức chọn các điểm lớn hơn hoặc bằng điểm cao thứ 3 trong nhóm đối tượng:

(C2:C15>=LARGE(IF(B2:B15=F1, C2:C15), 3))

Tại đây, hàm IF sẽ kiểm tra xem Chủ đề có bằng mục tiêu trong F1 ( Khoa học ) hay không và chuyển điểm tương ứng thành LARGE. Tất cả các điểm khác được thay thế bằng FALSE:

{FALSE;190;FALSE;230;FALSE;237;FALSE;FALSE;252;268;FALSE;275;FALSE;287}

Trong mảng trên, hàm LARGE tìm điểm lớn thứ 3, là 268.

Vì vậy, tiêu chí của chúng tôi đơn giản hóa thành:

(C2:C15>=268) * (B2:B15=F1)

Và hàm FILTER giải quyết thành:

FILTER(C2:C15, {0;0;0;0;0;0;0;0;0;1;0;1;0;1})

Trong đó 1 tương ứng với điểm lớn hơn hoặc bằng 268 trong Khoa học .

Cuối cùng, FILTER trả về một mảng gồm 3 điểm hàng đầu:

{268;275;287}

Hàm SORT với đối số s ort_order được đặt thành -1 sắp xếp các điểm số này theo thứ tự giảm dần và đổ kết quả vào E4: E6.

Công thức 2. Lọc n giá trị hàng đầu với nhiều tiêu chí AND

Để lọc các giá trị cao nhất có hai hoặc nhiều điều kiện, bạn cần đánh giá một số biểu thức logic thay vì một:

SORT(FILTER(values, (values >=LARGE(IF((criteria_range1=criteria1) * (criteria_range2=criteria2), values), n)) * (criteria_range1=criteria1) * (criteria_range2=criteria2)), 1, -1)

Ví dụ: để có được 3 điểm cao nhất trong số các sinh viên của một nhóm cụ thể (G1) trong một môn học cụ thể (G2), bạn có thể sử dụng công thức này:

=SORT(FILTER(D2:D15, (D2:D15>=LARGE(IF((B2:B15=G1)*(C2:C15=G2), D2:D15), 3)) * (B2:B15=G1)*(C2:C15=G2)), 1, -1)

Trong đó B2: B15 là các nhóm, C2: C15 là các đối tượng và D2: D15 là điểm.

Để nhận điểm số cũng như dữ liệu liên quan, công thức trong F5 là:

=SORT(FILTER(A2:D15, (D2:D15>=LARGE(IF((B2:B15=G1)*(C2:C15=G2), D2:D15), 3)) * (B2:B15=G1)*(C2:C15=G2)), 4, -1)

Công thức 3. Lọc n giá trị hàng đầu với nhiều tiêu chí hoặc

Để đánh giá một số tiêu chí bằng cách sử dụng logic hoặc, chúng tôi đã thêm các biểu thức logic trong công thức LARGE IF . Cách tiếp cận này cũng hoạt động cho công thức FILTER:

SORT(FILTER(values, (values >=LARGE(IF((criteria_range1=criteria1) + (criteria_range2=criteria2), values), n)) * ((criteria_range1=criteria1) + (criteria_range2=criteria2))), 1, -1)

Trong bảng mẫu của chúng tôi, để lấy 3 điểm cao nhất trong Văn học (F1) hoặc Lịch sử (F2), hãy xây dựng các tiêu chí theo cách này:

=SORT(FILTER(C2:C15, (C2:C15>=LARGE(IF((B2:B15=F1)+(B2:B15=F2), C2:C15), 3)) * ((B2:B15=F1)+(B2:B15=F2))), 1, -1)

Trong đó B2: B15 là các môn học và C2: C15 là điểm số.

Để trích xuất không chỉ điểm mà tất cả các thông tin liên quan, công thức trong E5 là:

=SORT(FILTER(A2:C15, (C2:C15>=LARGE(IF((B2:B15=F1)+(B2:B15=F2), C2:C15), 3)) * ((B2:B15=F1)+(B2:B15=F2))), 3, -1)

FILTER giá trị lớn nhất thứ n với các điều kiện

Giải pháp này chỉ hoạt động trong Excel 365. Trong các phiên bản khác, chức năng FILTER không khả dụng.

Các ví dụ trước cho thấy cách lọc N giá trị hàng đầu với một hoặc nhiều điều kiện. Nếu bạn cần tìm một giá trị cụ thể, chẳng hạn như số cao nhất ^{thứ 2} hoặc ^thứ 3 trong tập dữ liệu, thì bạn có thể sử dụng công thức LARGE FILTER đơn giản hơn nhiều.

Về cơ bản, logic giống như trong các công thức IF LARGE được thảo luận trong phần đầu tiên của hướng dẫn này. Sự khác biệt là bạn sử dụng hàm FILTER thay vì IF để áp dụng các tiêu chí.

Công thức 1. Tìm giá trị cao nhất thứ n với điều kiện

Đối với một điều kiện, công thức cơ bản này sẽ hoạt động:

LARGE(FILTER(values, criteria_range=criteria), n)

Ví dụ, để đạt điểm cao ^thứ 3 trong môn Văn , hãy sử dụng một trong các công thức sau:

=LARGE(FILTER($C$2:$C$15, $B$2:$B$15="Literature"), 3)

=LARGE(FILTER($C$2:$C$15, $B$2:$B$15=$F$1), $F$2)

Trong đó B2: B15 là các đối tượng, C2: C15 là điểm, F1 là đối tượng quan tâm và F2 là điểm lớn nhất thứ n để trả về.

Công thức 2. Lọc giá trị lớn nhất thứ n sử dụng nhiều tiêu chí AND

Để đánh giá hai hoặc nhiều tiêu chí bằng cách sử dụng logic AND, đây là công thức để sử dụng:

LARGE(FILTER(values, (criteria_range1=criteria1) * (criteria_range2=criteria2)), n)

Giả sử bạn đang tìm kiếm điểm cao nhất ^thứ 3 (G3) trong môn Văn (G2) trong nhóm B (G1). Cung cấp các tham chiếu tương ứng cho công thức:

=LARGE(FILTER($D$2:$D$15, ($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2)), $G$3)

Và bạn sẽ nhận được kết quả này:

Công thức 3. Nhận giá trị lớn nhất thứ n bằng cách sử dụng nhiều tiêu chí OR

Để kiểm tra nhiều tiêu chí bằng logic OR, công thức là:

LARGE(FILTER(values, (criteria_range1=criteria1) + (criteria_range2=criteria2)), n)

Giả sử bạn muốn tìm điểm cao nhất ^thứ 5 (F3) trong các môn nhân văn – Văn (F1) hoặc Lịch sử (F2). Công thức có dạng như sau:

=LARGE(FILTER($C$2:$C$15, ($B$2:$B$15=$F$1)+($B$2:$B$15=$F$2)), $F$3)

Nó sẽ kiểm tra xem điều kiện 1 hoặc điều kiện 2 là ĐÚNG và xuất ra kết quả:

Đó là cách sử dụng hàm LARGE trong Excel với các tiêu chí. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của Dịch vụ kế toán tại Hà Nội – Ketoanmvb vào tuần tới!

Source link

The post Công thức hàm Large IF trong Excel để tìm giá trị lớn nhất 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/cong-thuc-ham-large-if-trong-excel-de-tim-gia-tri-lon-nhat.html