Xu Hướng 2/2024 # Công Thức Hàm Large If Trong Excel Để Tìm Giá Trị Lớn Nhất # Top 8 Xem Nhiều

Bạn đang xem bài viết Công Thức Hàm Large If Trong Excel Để Tìm Giá Trị Lớn Nhất được cập nhật mới nhất tháng 2 năm 2024 trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới 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:

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.

=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”:

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

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:

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

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ử .

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

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:

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

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.

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

Ở đâu:

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

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ị )

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:

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.

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

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):

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

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:

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;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;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:

{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;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í):

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

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í:

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)

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

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

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:

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:

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

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

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

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:

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

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

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 .

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

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:

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à:

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.

Đố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).

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:

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

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}

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

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

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.

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

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:

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:

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

Để đá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:

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:

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

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.

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:

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:

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.

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($C$2:$C$15, $B$2:$B$15="Literature"), 3)

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($C$2:$C$15, $B$2:$B$15=$F$1), $F$2)

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à:

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ả:

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

Hướng Dẫn Cách Dùng Hàm Large Trong Excel Để Tìm Giá Trị Lớn Thứ Mấy

Trong trường hợp muốn tìm theo giá trị lớn nhất, chúng ta có thể sử dụng hàm MAX. Nhưng với những giá trị lớn thứ hai, thứ ba hoặc thứ n thì sao? Lúc này chúng ta cần tới hàm LARGE. Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách dùng hàm LARGE trong Excel để tìm giá trị lớn thứ mấy.

Cấu trúc hàm LARGE

Hàm LARGE giúp chúng ta tìm ra giá trị lớn thứ mấy trong 1 dãy số. Ví dụ: tìm giá trị doanh thu lớn thứ 2 trong cột Doanh thu:

Chúng ta sử dụng hàm LARGE như sau:

Để tìm giá trị lớn thứ k trong 1 vùng array, chúng ta có công thức tổng quát như sau:

=LARGE(array, k)

Ứng dụng hàm LARGE kết hợp với một số hàm trong Excel

Để hiểu rõ hơn ứng dụng của hàm LARGE, chúng ta cùng tìm hiểu qua một số ví dụ sau:

Hàm LARGE kết hợp hàm INDEX + MATCH

Việc tìm ra giá trị lớn thứ mấy có thể khiến bạn nghĩ: “Vậy thì có tác dụng gì? Nếu muốn tìm tên người đạt được giá trị đó thì có được không?”. Việc này hoàn toàn có thể làm được như sau:

Để tìm nhân viên có doanh thu lớn thứ 2, chúng ta phải qua 2 bước:

Bước 1: Tìm giá trị doanh thu lớn thứ 2 với hàm LARGE

Bước 2: Sử dụng hàm INDEX + MATCH để tìm tên nhân viên tương ứng theo giá trị mức doanh thu lớn thứ 2

Hàm LARGE kết hợp chức năng định dạng theo điều kiện (Conditional formatting)

Vẫn trong bảng doanh thu ở trên, chúng ta sẽ thực hiện yêu cầu sau đây:

Tìm những giá trị doanh thu lớn thứ 1, thứ 2, thứ 3 trong cột Doanh thu

Kết hợp trong chức năng định dạng theo điều kiện để tự động tô màu

Trường hợp nhân viên có doanh thu lớn nhất:

Sử dụng hàm LARGE tìm giá trị doanh thu lớn nhất

=LARGE(B2:B8,1)

Thiết lập định dạng theo điều kiện có sử dụng hàm LARGE

Trong chức năng định dạng theo điều kiện (conditional formatting), để sử dụng công thức làm điều kiện định dạng thì chúng ta bắt buộc phải viết dưới dạng 1 biểu thức so sánh Đúng-Sai. Khi đó:

So sánh từng giá trị doanh thu với doanh thu lớn nhất

Nếu đúng: tô màu theo doanh thu lớn nhất

Nếu sai: không tô màu

Vùng áp dụng: A2:B8

Các bước thực hiện như sau:

Chọn vùng A2:B8

Chọn chức năng Conditional Formatting

Trong cửa sổ Conditional Formatting, chọn New Rule…

Trong cửa sổ New Formatting Rule, chọn Use a formula to determine which cells to format

Viết công thức như sau:

=$B2=LARGE($B$2:$B$8,1)

Lưu ý:

Cố định cột trong ô B2. Việc này giúp chỉ xét theo giá trị cột Doanh thu mà không tính sang cột khác

Cố định vùng B2:B8 cả cột và dòng. Việc này giúp vùng tìm giá trị lớn nhất luôn đầy đủ từ B2:B8

Thực hiện Format cho các giá trị thỏa mãn theo màu như yêu cầu.

=$B2=LARGE($B$2:$B$8,2)

=$B2=LARGE($B$2:$B$8,3)

Kết quả thu được là:

Hướng dẫn 4 cách tìm giá trị lớn nhất và nhỏ nhất trong excel Tìm nội dung xuất hiện nhiều nhất trong danh sách Hướng dẫn cách định dạng theo điều kiện cho biểu đồ trên Excel

Công Thức Excel Max If Để Tìm Giá Trị Lớn Nhất Với Các Điều Kiện

Bài viết cho thấy một vài cách khác nhau để có được giá trị tối đa trong Excel dựa trên một hoặc một số điều kiện mà bạn chỉ định.

Trong hướng dẫn trước của chúng tôi, chúng tôi đã xem xét các sử dụng phổ biến của Hàm MAX được thiết kế để trả về số lớn nhất trong bộ dữ liệu. Tuy nhiên, trong một số trường hợp, bạn có thể cần đi sâu vào dữ liệu của mình hơn nữa để tìm giá trị tối đa dựa trên các tiêu chí nhất định. Điều này có thể được thực hiện bằng cách sử dụng một vài công thức khác nhau và bài viết này giải thích tất cả các cách có thể.

Công thức Excel MAX IF

Cho đến gần đây, Microsoft Excel không có chức năng MAX IF tích hợp để có được giá trị tối đa dựa trên các điều kiện. Trước đây, họ đã giới thiệu MAXIFS và bây giờ, người dùng Excel 2024 và Excel 2024 có kèm theo đăng ký Office 365 có thể thực hiện tối đa điều kiện một cách dễ dàng.

Trong Excel 2013 và các phiên bản trước đó, bạn vẫn phải tạo riêng cho mình công thức mảng bằng cách kết hợp hàm MAX với câu lệnh IF:

{= MAX (NẾU ( tiêu chí= = tiêu chí, Phạm vi tối đa))}

Để xem công thức MAX IF chung này hoạt động như thế nào trên dữ liệu thực, vui lòng xem xét ví dụ sau. Giả sử, bạn có một bảng với kết quả nhảy xa của nhiều sinh viên. Bảng bao gồm dữ liệu cho ba vòng và bạn đang tìm kiếm kết quả tốt nhất của một vận động viên cụ thể, Jacob nói. Với tên sinh viên trong A2: A10 và khoảng cách trong C2: C10, công thức có hình dạng này:

=MAX(IF(A2:A10="Jacob", C2:C10))

Hãy nhớ rằng một công thức mảng phải luôn luôn được nhập bằng cách nhấn Ctrl + Shift + Enter Phím đồng thời. Kết quả là, nó được tự động bao quanh với dấu ngoặc nhọn như hiển thị trong ảnh chụp màn hình bên dưới (nhập niềng răng bằng tay sẽ không hoạt động!).

Tôi là bảng tính thực tế, sẽ thuận tiện hơn khi nhập tiêu chí vào một số ô, để bạn có thể dễ dàng thay đổi điều kiện mà không cần thay đổi công thức. Vì vậy, chúng tôi nhập tên mong muốn trong F1 và nhận được kết quả sau:

=MAX(IF(A2:A10=F1, C2:C10))

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

Trong bài kiểm tra logic của hàm IF, chúng tôi so sánh danh sách các tên (A2: A10) với tên đích (F1). Kết quả của hoạt động này là một mảng TRUE và FALSE, trong đó các giá trị TRUE biểu thị các tên khớp với tên đích (Jacob):

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

Cho giá trị_ if_true đối số, chúng tôi cung cấp kết quả nhảy xa (C2: C10), vì vậy nếu kiểm tra logic đánh giá thành TRUE, số tương ứng từ cột C được trả về. Các giá trị_ if_false đối số bị bỏ qua, nghĩa là sẽ chỉ có giá trị FALSE trong đó điều kiện không được đáp ứng:

{FALSE;FALSE;FALSE;5.48;5.42;5.57;FALSE;FALSE;FALSE}

Mảng này được đưa đến hàm MAX, trả về số lượng tối đa bỏ qua các giá trị FALSE.

Công thức MAX IF với nhiều tiêu chí

Trong trường hợp khi bạn cần tìm giá trị tối đa dựa trên nhiều điều kiện, bạn có thể: Sử dụng báo cáo IF lồng nhau để bao gồm các tiêu chí bổ sung:

Hoặc xử lý nhiều tiêu chí bằng cách sử dụng thao tác nhân:

Giả sử bạn có kết quả nam và nữ trong một bảng duy nhất và bạn muốn tìm ra bước nhảy dài nhất trong số các cô gái ở vòng 3. Để hoàn thành, chúng tôi nhập tiêu chí đầu tiên (nữ) vào G1, tiêu chí thứ hai (3) trong G2 và sử dụng các công thức sau để tính giá trị tối đa:

=MAX(IF(B2:B16=G1, IF(C2:C16=G2, D2:D16))) =MAX(IF((B2:B16=G1)*(C2:C16=G2), D2:D16))

Vì cả hai đều là công thức mảng, hãy nhớ nhấn Ctrl + Shift + Enter để hoàn thành chúng một cách chính xác.

Như được hiển thị trong ảnh chụp màn hình bên dưới, các công thức tạo ra kết quả tương tự, do đó, việc sử dụng cái nào là vấn đề sở thích cá nhân của bạn. Đối với tôi, công thức với logic Boolean dễ đọc và xây dựng hơn – nó cho phép thêm bao nhiêu điều kiện bạn muốn mà không cần lồng các hàm IF bổ sung.

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

Công thức đầu tiên sử dụng hai hàm IF lồng nhau để đánh giá hai tiêu chí. Trong thử nghiệm logic của câu lệnh IF đầu tiên, chúng tôi so sánh các giá trị trong cột Giới tính (B2: B16) với tiêu chí trong G1 (“Nữ”). Kết quả là một mảng các giá trị TRUE và FALSE trong đó TRUE đại diện cho dữ liệu phù hợp với tiêu chí:

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

Theo cách tương tự, hàm IF thứ hai kiểm tra các giá trị trong cột Round (C2: C16) theo tiêu chí trong G2.

Cho value_if_true đối số trong câu lệnh IF thứ hai, chúng tôi cung cấp kết quả nhảy xa (D2: D16) và bằng cách này, chúng tôi nhận được các mục có TRUE trong hai mảng đầu tiên ở các vị trí tương ứng (ví dụ: các mục có giới tính là “nữ” và làm tròn là 3):

{FALSE; FALSE; FALSE; FALSE; FALSE; 4.63; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 4.52}

Mảng cuối cùng này đi đến hàm MAX và nó trả về số lớn nhất.

Công thức thứ hai đánh giá các điều kiện tương tự trong một thử nghiệm logic đơn và hoạt động nhân giống như toán tử AND:

Khi các giá trị TRUE và FALSE được sử dụng trong bất kỳ hoạt động số học nào, chúng sẽ được chuyển đổi thành 1 và 0, tương ứng. Và bởi vì nhân với 0 luôn cho số không, mảng kết quả chỉ có 1 khi tất cả các điều kiện là TRUE. Mảng này được đánh giá trong bài kiểm tra logic của hàm IF, trả về khoảng cách tương ứng với các phần tử 1 (TRUE).

MAX IF không có mảng

Nhiều người dùng Excel, bao gồm cả tôi, có thành kiến ​​với các công thức mảng và cố gắng loại bỏ chúng bất cứ khi nào có thể. May mắn thay, Microsoft Excel có một vài chức năng xử lý mảng nguyên bản và chúng ta có thể sử dụng một trong các chức năng đó, cụ thể là TÓM TẮT, như một loại “trình bao bọc” xung quanh MAX.

Công thức MAX IF chung không có mảng như sau:

Đương nhiên, bạn có thể thêm nhiều cặp phạm vi / tiêu chí nếu cần.

Để xem công thức hoạt động, chúng tôi sẽ sử dụng dữ liệu từ ví dụ trước. Mục đích là để có được cú nhảy tối đa của một vận động viên nữ ở vòng 3:

=SUMPRODUCT(MAX(((B2:B16=G1) * (C2:C16=G2) * (D2:D16))))

Công thức này được cạnh tranh với một bình thường Đi vào tổ hợp phím và trả về kết quả giống như công thức MAX IF của mảng:

Nhìn kỹ hơn vào ảnh chụp màn hình ở trên, bạn có thể nhận thấy rằng các bước nhảy không hợp lệ được đánh dấu bằng “x” trong các ví dụ trước có 0 giá trị trong các hàng 3, 11 và 15 và phần tiếp theo giải thích lý do.

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

Như với công thức MAX IF, chúng tôi đánh giá hai tiêu chí bằng cách so sánh từng giá trị trong các cột Giới tính (B2: B16) và Vòng (C2: C16) với các tiêu chí trong các ô G1 và G2. Kết quả là hai mảng giá trị TRUE và FALSE. Nhân các phần tử của mảng trong cùng một vị trí sẽ chuyển TRUE và FALSE thành 1 và 0 tương ứng, trong đó 1 đại diện cho các mục đáp ứng cả hai tiêu chí. Mảng nhân thứ ba chứa kết quả nhảy xa (D2: D16). Và bởi vì nhân với 0 sẽ cho 0, chỉ các mục có 1 (TRUE) trong các vị trí tương ứng tồn tại:

{0; 0; 0; 0; 0; 4.63; 0; 0; 0; 0; 0; 0; 0; 0; 4.52}

Trong trường hợp Phạm vi tối đa chứa bất kỳ giá trị văn bản nào, phép toán nhân trả về lỗi #VALUE do toàn bộ công thức sẽ không hoạt động.

Hàm MAX lấy nó từ đây và trả về số lớn nhất đáp ứng các điều kiện đã chỉ định. Mảng kết quả bao gồm một phần tử duy nhất {4,63} đi đến hàm SUMPRODVEL và nó xuất ra số lượng tối đa trong một ô.

Ghi chú. Do logic cụ thể của nó, công thức hoạt động với các cảnh báo sau:

Phạm vi nơi bạn tìm kiếm giá trị cao nhất phải chỉ chứa các số. Nếu có bất kỳ giá trị văn bản nào, hãy #VALUE! lỗi được trả lại.

Công thức không thể đánh giá điều kiện “không bằng 0” trong tập dữ liệu âm. Đến tìm giá trị tối đa bỏ qua số không, sử dụng công thức MAX IF hoặc hàm MAXIFS.

Công thức Excel MAX IF với logic OR

Để tìm giá trị tối đa khi bất kì trong số các điều kiện đã chỉ định được đáp ứng, hãy sử dụng công thức MAX IF đã quen thuộc với logic Boolean, nhưng thêm các điều kiện thay vì nhân chúng.

Ngoài ra, bạn có thể sử dụng công thức không phải là mảng sau:

Ví dụ: chúng ta hãy tìm ra kết quả tốt nhất trong vòng 2 và 3. Xin lưu ý rằng trong ngôn ngữ Excel, tác vụ được tạo theo cách khác: trả về giá trị tối đa nếu vòng là 2 hoặc 3.

Với các vòng được liệt kê trong B2: B10, kết quả trong C2: C10 và tiêu chí ở F1 và H1, công thức diễn ra như sau:

=MAX(IF((B2:B10=F1) + (B2:B10=H1), C2:C10))

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

Giá trị tối đa với cùng điều kiện cũng có thể được tìm thấy bằng cách sử dụng công thức không phải là mảng này:

=SUMPRODUCT(MAX(((B2:B10=F1) + (B2:B10=H1)) * C2:C10))

Tuy nhiên, chúng ta cần thay thế tất cả các giá trị “x” trong cột C bằng các số 0 trong trường hợp này vì SUMPRODVEL MAX chỉ hoạt động với dữ liệu số:

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

Công thức mảng hoạt động chính xác giống như MAX IF với logic AND ngoại trừ việc bạn tham gia các tiêu chí bằng cách sử dụng thao tác bổ sung thay vì nhân. Trong các công thức mảng, phép cộng hoạt động như toán tử OR:

Việc thêm hai mảng TRUE và FALSE (kết quả của việc kiểm tra các giá trị trong B2: B10 theo các tiêu chí trong F1 và H1) tạo ra một mảng 1 và 0 trong đó 1 đại diện cho các mục mà điều kiện là TRUE và 0 đại diện cho các mục trong đó cả hai điều kiện là FALSE. Kết quả là, hàm IF “giữ” tất cả các mục trong C2: C10 ( value_if_true) với bất kỳ điều kiện nào là TRUE (1); các mục còn lại được thay thế bằng FALSE vì value_if_false đối số không được chỉ định.

Công thức không mảng hoạt động theo cách tương tự. Sự khác biệt là thay vì kiểm tra logic của IF, bạn nhân các phần tử của mảng 1 và 0 với các phần tử của mảng kết quả nhảy xa (C2: C10) ở các vị trí tương ứng. Điều này vô hiệu hóa các mục không đáp ứng bất kỳ điều kiện nào (có 0 trong mảng đầu tiên) và giữ các mục đáp ứng một trong các điều kiện (có 1 trong mảng đầu tiên).

MAXIFS – cách dễ dàng để tìm giá trị cao nhất với các điều kiện

Người dùng Excel 2024 và Excel cho Office 365 không gặp rắc rối trong việc thuần hóa mảng để xây dựng công thức MAX IF của riêng họ. Các phiên bản Excel này cung cấp hàm MAXIFS được chờ đợi từ lâu, giúp tìm giá trị lớn nhất với điều kiện chơi của trẻ.

Trong đối số đầu tiên của MAXIFS, bạn nhập phạm vi cần tìm giá trị tối đa (D2: D16 trong trường hợp của chúng tôi) và trong các đối số tiếp theo, bạn có thể nhập tối đa 126 cặp phạm vi / tiêu chí. Ví dụ:

=MAXIFS(D2:D16, B2:B16, G1, C2:C16, G2)

Như được hiển thị trong ảnh chụp màn hình bên dưới, công thức đơn giản này không có vấn đề gì với việc xử lý phạm vi chứa cả giá trị số và văn bản:

Để biết thông tin chi tiết về chức năng này, vui lòng xem Hàm MAXIFS với các ví dụ về công thức.

Bài tập thực hành để tải về

Maxif: Công Thức Mảng Max+If Tìm Giá Trị Lớn Nhất Có Điều Kiện

MAXIF: Công thức mảng MAX+IF TÌM GIÁ TRỊ LỚN NHẤT có điều kiện. Công thức, diễn giải dễ hiểu, ngắn gọn.

Ví dụ rõ ràng, thực hành và áp dụng luôn

CÔNG THỨC MAXIF CHUNG

{= MAX (IF (iteria_range = iteria, value_range))}

DIỄN GIẢI CÔNG THỨC

Để tìm giá trị lớn nhất trong một phạm vi với các tiêu chí cụ thể, bạn có thể sử dụng công thức mảng cơ bản dựa trên hàm IF và hàm MAX.

Trong ví dụ hiển thị, công thức trong ô H5 là:

Giải thích:

{=MAX(NẾU(QUÉT CỘT D3:D8=F5,THÌ TÌM MAX CÁC GIÁ TRỊ TƯƠNG ỨNG VỚI VÙNG C3:C8))}

trong đó trả về nhiệt độ tối đa vào ngày trong H5.

Đây là một công thức mảng và phải được kết thúc công thức bằng thao tác nhấn đồng thời tổ hợp phím: Ctrl + Shift + Enter

Vùng so sánh và vùng quét cần có độ dài bằng nhau

Kết quả trả về #VALUE là do 2 vùng đang không bằng nhau

CÔNG THỨC MẢNG NÀY HOẠT ĐỘNG NHƯ THẾ NÀO

Trong Ví dụ bên trên, chúng ta chỉ lấy có 6 dòng dữ liệu. Do đó viết công thức xong và nhấn Ctrl + shift + Enter sẽ trả kết quả luôn.

Với dữ liệu hiển thị chứa gần 10.000 hàng. Dữ liệu biểu thị số đọc nhiệt độ được thực hiện cứ sau 2 phút trong một khoảng thời gian. Đối với bất kỳ ngày nào (được cung cấp trong ô H5), chúng tôi muốn có được nhiệt độ tối đa vào ngày đó.

Nếu có 10.000 dòng dữ liệu bắt đầu từ 1 đến 10.000:

Bên trong hàm IF, kiểm tra logic được nhập dưới dạng D1: D10.000 = F5.

Diễn giải trong ví dụ bên trên: logic quét D3: D8 = F5

Vì chúng ta so sánh giá trị trong F5 với một phạm vi ô (một mảng), kết quả sẽ là một mảng kết quả, trong đó mỗi mục trong mảng là TRUE hoặc FALSE. Các giá trị TRUE biểu thị giá trị khớp với F5.

Hàm IF hoạt động như một bộ lọc. Vì chúng ta cung cấp cho IF một mảng cho kiểm tra logic, IF trả về một mảng kết quả.

Kết quả trả về từ hàm IF sẽ được hàm MAX làm việc tiếp và trả về giá trị đại diện cho kết quả lớn nhất, theo điều kiện

MAXIFS THÌ NHƯ NÀO

Trong Excel O365 và Excel 2024, hàm MAXIFS mới có thể tìm thấy giá trị lớn nhất với một hoặc nhiều tiêu chí mà không cần công thức mảng. Với MAXIFS, công thức tương đương cho ví dụ này là:

Small If Công Thức Để Tìm Giá Trị Thấp Nhất Thứ N

Bạn đang cố gắng tìm cách nhận giá trị dưới cùng dựa trên một hoặc nhiều điều kiện? Chỉ cần sử dụng hàm SMALL cùng với IF. Hướng dẫn này chỉ ra cách xây dựng một giải pháp hoạt động và giải thích logic bên trong của nó, để bạn có thể dễ dàng giải mã công thức và điều chỉnh theo nhu cầu của mình.

Microsoft Excel có một số hàm để thực hiện các phép tính “có điều kiện” như MAXIFS, MINIFS, AVERAGEIF, và những thứ tương tự. Rất tiếc, hàm IF SMALL không tồn tại. Tuy nhiên, không có gì ngăn cản bạn xây dựng công thức của riêng mình để tìm giá trị nhỏ nhất thứ n với tiêu chí. Nếu bạn không quen thuộc với Excel Small chức năng, sau đó bạn có thể muốn bắt đầu với những điều cơ bản và đọc hướng dẫn được liên kết ở trên trước.

Công thức IF Small trong Excel

Để nhận giá trị thấp nhất thứ n phù hợp với tiêu chí bạn chỉ định, bạn có thể sử dụng công thức chung sau:

Ở đâu n là 1 st, 2 nd, 3 rd, v.v. giá trị thấp nhất để trả lại.

Để công thức hoạt động chính xác, bạn nên nhập nó dưới dạng công thức mảng bằng cách nhấn Ctrl + Shift + Enter các phím đồng thời. Khi bạn làm điều này, Excel sẽ bao quanh công thức trong dấu ngoặc nhọn như được hiển thị trong ảnh chụp màn hình bên dưới. Trong Excel 365, nó cũng hoạt động như một công thức thông thường do hỗ trợ mảng động.

=SMALL(IF($B$2:$B$15="art", $C$2:$C$15), $E3)

Công thức này trả về giá trị nhỏ nhất Nghệ thuật điểm F3. Sao chép nó xuống thông qua F5, và bạn sẽ nhận được 2 nd và 3 rd kết quả thấp nhất.

Để thuận tiện, bạn cũng có thể nhập tên của các đối tượng mục tiêu trong các ô xác định trước (F2 – Nghệ thuật và G2 – Khoa học), và tham chiếu các ô đó làm tiêu chí:

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

Nếu thiết kế trang tính của bạn không cung cấp n , bạn có thể tạo chúng trực tiếp trong công thức bằng cách sử dụng hàm ROWS với tham chiếu phạm vi mở rộng như sau:

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

Do sử dụng khéo léo tham chiếu tuyệt đối và tương đối, tham chiếu phạm vi sẽ tự động mở rộng khi công thức được sao chép vào các ô bên dưới. Trong E3, ROWS (A $ 2: A2) tạo ra n bằng 1 và công thức trả về điểm nhỏ nhất cho Nghệ thuật. Trong E4, tham chiếu thay đổi thành A $ 2: A3 khiến ROWS trả về 2, vì vậy chúng tôi nhận được 2 nd điểm nhỏ nhất, v.v.

Trong trường hợp không tìm thấy giá trị nhỏ nhất thứ n với các tiêu chí được chỉ định, công thức SMALL IF sẽ trả về lỗi #NUM. Để bẫy lỗi này và thay thế nó bằng bất kỳ giá trị nào bạn thấy thích hợp (“-” trong trường hợp của chúng tôi), bạn có thể sử dụng Hàm IFERROR:

=IFERROR(SMALL(IF($B$2:$B$15=$F$2, $C$2:$C$15), $E3), "-")

Từ một mảng cung cấp cho 1 st đối số, hàm SMALL trả về giá trị nhỏ nhất thứ n được chỉ định trong 2 nd tranh luận. Vấn đề là chúng ta không muốn mọi giá trị trong mảng được xử lý mà chỉ xử lý điểm của một môn học nhất định. Để giới hạn mảng ở Nghệ thuật giá trị, chúng tôi nói với Hàm IF để so sánh danh sách (B2: B15) với đối tượng mục tiêu:

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

Bởi vì kiểm tra logic được thực hiện trên một mảng số, kết quả cũng là một mảng, trong đó các số đại diện cho Nghệ thuật điểm và FALSE đánh giá bất kỳ điểm nào khác:

IF($B$2:$B$15="Art", $C$2:$C$15)

Vì hàm SMALL bỏ qua lỗi (và bất kỳ thứ gì khác không phải là số), kết quả là giá trị nhỏ nhất thứ n từ Nghệ thuật điểm trong mảng trên.

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

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

Để tạo công thức IF NHỎ để đánh giá một số điều kiện, hãy chọn một trong các cách tiếp cận sau.

Cung cấp nhiều tiêu chí bằng cách sử dụng câu lệnh IF lồng nhau:

Nhân các biểu thức logic:

Xin lưu ý rằng cả hai đều là công thức mảng, vì vậy đừng quên nhấn Ctrl + Shift + Enter để hoàn thành chúng đúng cách. Trong Excel 365, chúng cũng sẽ hoạt động như các công thức thông thường.

Để kiểm tra công thức “trong trường”, chúng tôi sẽ mở rộng bảng mẫu của mình với Trường học và nhập 2 tiêu chí vào các ô riêng biệt như hình bên dưới.

Bây giờ, chúng ta có tất cả các tham số cần thiết cho công thức NẾU NHỎ:

Criteria_range1 – danh sách các môn học (B2: B15)

Criteria_range2 – các loại trường (C2: C15)

Tiêu chí2 – trường mục tiêu (G2)

Giá trị – điểm (D2: D15)

N – số 1 đến 3 trong F6, F7 và F8

Đặt các đối số lại với nhau, chúng ta nhận được các công thức sau:

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

Nhập một trong hai vào G6, kéo nó qua G8 và bạn sẽ nhận được kết quả sau.

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

Về bản chất, cả hai công thức đều sử dụng hàm IF để kiểm tra nhiều điều kiện, do đó chỉ những giá trị mà tất cả các điều kiện là TRUE mới nhận được vào mảng đối số của hàm SMALL.

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

IF lồng nhau:

Trong bài kiểm tra logic của hàm IF đầu tiên, chúng tôi so sánh danh sách các đối tượng với mục tiêu ( Nghệ thuật): $ B $ 2: $ B $ 15 = $ G $ 1. Kết quả của thao tác này là một mảng các giá trị TRUE và FALSE, trong đó TRUE tương ứng với Nghệ thuật giá trị trong cột B:

Câu lệnh IF thứ hai kiểm tra ô nào trong phạm vi C2: C15 khớp với tiêu chí trong G2 ( Trẻ em), và cũng trả về một mảng TRUE và FALSE:

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

Cho value_if_true đối số của IF thứ hai, chúng tôi cung cấp điểm (D2: D15). Điều này đảm bảo rằng chỉ những mục có TRUE trong hai mảng trên mới “sống sót”; tất cả các điểm khác được thay thế bằng các giá trị FALSE:

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

Mảng cuối cùng này chuyển đến hàm SMALL, từ đó nó trả về giá trị thấp nhất thứ n.

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

Nhân tiêu chí:

Công thức thứ hai kiểm tra cả hai điều kiện trong một phép thử logic duy nhất, trong đó phép toán nhân hoạt động như toán tử AND.

Khi các phần tử của hai mảng TRUE / FALSE được nhân lên, các giá trị logic được chuyển đổi thành 1’s (TRUE) và 0’s (FALSE). Khi nhân với 0 cho không, mảng kết quả có 1 cho dữ liệu đáp ứng cả hai tiêu chí:

Hàm IF đánh giá mảng 1 và 0 này trong bài kiểm tra logic và chuyển điểm tương ứng với 1 thành NHỎ.

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

Công thức IF SMALL với nhiều tiêu chí Or

Ví dụ trước cho thấy cách tìm các giá trị dưới cùng dựa trên nhiều tiêu chí bằng cách sử dụng logic AND, tức là khi tất cả các điều kiện được thỏa mãn. Nhận giá trị nhỏ nhất thỏa mãn bất kì trong số các điều kiện, bạn cần xây dựng công thức IF SMALL với logic Or. Đối với điều này, hãy cộng các tiêu chí thay vì nhân chúng.

Ví dụ: hãy chọn điểm thấp nhất trong hai môn học khác nhau, giả sử Lịch sử và Văn chương. Về mặt nếu Excel, công thức sẽ trả về điểm nhỏ nhất thứ n nếu Môn học là một trong hai Lịch sử HOẶC LÀ Văn chương.

Với các môn ở B2: B15 và điểm C2: C15, đây là công thức để trả về điểm thấp nhất:

Tất nhiên, bạn có thể nhập các tiêu chí và n số trong các ô riêng biệt, hoàn thành công thức bằng cách nhấn Ctrl + Shift + Enter và nhận được kết quả này:

Kiểm tra danh sách đối tượng theo 2 tiêu chí khác nhau sẽ cho ra 2 mảng giá trị TRUE và FALSE. Việc thêm các phần tử của các mảng đó sẽ tạo ra mảng 1 và 0, trong đó số 1 tương ứng với các mục đáp ứng ít nhất một điều kiện (bất kể điều kiện nào) và các số không tương ứng với các mục không đáp ứng bất kỳ điều kiện nào. Mảng cuối cùng này đi đến kiểm tra logic của IF:

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

Hàm IF đánh giá từng phần tử của mảng trên và chuyển điểm tương ứng với 1 cho hàm SMALL, từ đó nó chọn giá trị thứ n được chỉ định:

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

Cách tìm giá trị nhỏ nhất lớn hơn 0

Mặc dù tập dữ liệu của bạn đôi khi có thể chứa một số giá trị 0, không phải lúc nào cũng có ý nghĩa khi đưa những số không đó vào kết quả công thức. Để loại bỏ tất cả các giá trị 0, chúng tôi sẽ sử dụng công thức NHỎ NHỎ đã quen thuộc với tiêu chí “không bằng 0”.

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

Công thức 1. SMALL IF không bằng 0

Để nhận giá trị nhỏ nhất thứ n bỏ qua số 0, hãy sử dụng công thức mảng chung sau:

Giả sử một vài học sinh trượt một số môn thi và bị 0 điểm cho các môn đó. Để có 3 điểm thấp nhất lớn hơn 0, công thức là:

Các bạn nhớ bấm CTRL + Shift + Enter để hoàn thành nó một cách chính xác.

Công thức này đi đến ô trên cùng (E2), trong đó nó sẽ trích xuất điểm thấp nhất. Và sau đó, bạn kéo công thức xuống qua hai ô khác để trích xuất 2 nd và 3 rd điểm thấp nhất. Hàm ROWS (A $ 2: A2) tự động tạo n số, vì vậy bạn không cần nhập chúng vào bất kỳ đâu trong trang tính.

Để tìm giá trị nhỏ nhất thứ n lớn hơn 0 dựa trên tiêu chí, hãy đặt tiêu chí bổ sung theo cách sau:

Kéo công thức sang bên phải và bạn sẽ nhận được công thức nhỏ nhất Khoa học điểm quá:

Lọc các giá trị nhỏ nhất dựa trên tiêu chí

Logic của công thức giống như trong các ví dụ trước. Sự khác biệt là bạn sử dụng Chức năng FILTER thay vì IF để áp dụng các tiêu chí.

Giải pháp này chỉ hoạt động trong các bản dựng gần đây của Excel 365, nơi các hàm mảng động có sẵn.

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

Nếu chỉ có một điều kiện được đáp ứng, bạn có thể nhận được giá trị nhỏ nhất thứ n với công thức này:

Đối với tập dữ liệu mẫu của chúng tôi, công thức như sau:

=SMALL(FILTER($C$2:$C$15, $B$2:$B$15=F$2), $E3)

Trong đó B2: B15 là phạm vi tiêu chí (danh sách các môn học), C2: C15 là các giá trị (điểm số), F2 là tiêu chí (chủ đề quan tâm) và E3 là điểm số nhỏ nhất thứ n để trả về.

Công thức 2. Nhận giá trị nhỏ nhất thứ n với nhiều tiêu chí

Để kiểm tra nhiều điều kiện, đây là công thức để sử dụng:

Giả sử bạn đang tìm điểm thấp nhất thứ n trong một môn học cụ thể (G1) trong số các học sinh của một trường nhất định (G2), công thức là:

=SMALL(FILTER($D$2:$D$15, ($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2)), $F6)

Trong đó B2: B15 là danh sách các môn học, C2: C15 là các loại trường, D2: D15 là điểm số và F6 là giá trị nhỏ nhất thứ n để trả về.

Tìm Giá Trị Lớn Nhất

Khi học nhập môn lập trình, gặp bài toán tìm số lớn nhất – nhỏ nhất trong một mảng số bao gồm nhiều số, hàng, cột hoặc cả hàng và cột là mình phải viết nhiều hàm như hàm if..then, so sánh mới tìm ra được.

Nhưng may quá, Excel đã có sẵn hàm MAX để tìm giá trị lớn nhất và tìm giá trị nhỏ nhất với hàm MIN. Ngoài ra, Excel còn 2 hàm MINA và MAXA cũng có chức năng tương tự nhưng có thể tìm được giá trị lớn nhất – nhỏ nhất trong mảng có chứa các giá trị LOGIC (TRUE/ FALSE).

Tìm giá trị lớn nhất bằng hàm MAX

Mô tả:

Là hàm số học, có kết quả trả về là giá trị lớn nhất của các đối số, các Cells hoặc các Range (vùng dữ liệu).

Cú pháp: Trong đó:

Number1, number2,... là các số, Cells, Range. Tối đa bạn có thể sử dụng đến number255.

Chú thích:

Nếu đối số là các Cells hay Range tham chiếu, chỉ các số trong các Cells hay Range tham chiếu đó mới được dùng. Các ô trống, giá trị LOGIC hoặc văn bản trong các Cells hay Range tham chiếu bị bỏ qua.

Nếu các đối số không chứa số, hàm MAX trả về 0 (không).

Các đối số là văn bản hay giá trị lỗi không thể chuyển đổi thành số sẽ khiến xảy ra lỗi.

=MAX(29,12,45, 32,25,5,19,39,)

Hàm tìm giá trị lớn nhất giữa các đối số là số xác định.

=MAX(C10,B3, E6,D3,C6,A6,B10,D10)

Hàm tìm giá trị lớn nhất giữa các Cells chứa giá trị số – Excel tự bỏ qua Cells chứa giá trị không phải là số.

=MAX(A2:A11)

Hàm tìm giá trị lớn nhất của một Range trong 1 cột.

=MAX(C7,A2:A11)

Hàm tìm giá trị lớn nhất giữa 1 Cells và một Range.

=MAX(A2:A11, C2:C11,E2:E11)

Hàm tìm giá trị lớn nhất giữa nhiều Range với nhau.

=MAX(A:A, C:C,D:D)

Hàm tìm giá trị lớn nhất giữa các toàn bộ các cột.

=MAX(B2,A3:E3)

Hàm tìm giá trị lớn nhất giữa 1 Cells và 1 Range trong 1 hàng.

=MAX(A2:E2,A5:E5, A7:E7,A9:E9,A11:E11)

Hàm tìm giá trị lớn nhất giữa các Range trong các hàng.

=MAX(A2:E11)

Hàm tìm giá trị lớn nhất của 1 Range gồm hàng và cột.

=MAX(A:E)

Hàm tìm giá trị lớn nhất của 1 Range là toàn bộ các cột.

Tìm giá trị nhỏ nhất bằng hàm MIN

Mô tả:

Về cơ bản thì hàm MIN giống với hàm MAX nhưng thay vì hàm MAX tìm giá trị lớn nhất thì hàm MIN sẽ tìm giá trị nhỏ nhất của các đối số, các Cells hoặc Range.

Cú pháp:

=MIN(number 1,number 2,...)

Ví dụ:

★ Thay hàm MAX trong ví dụ trên thành hàm MIN thì kết quả sẽ trả về giá trị nhỏ nhất của các đối số, các Cells hoặc Range.

Mô tả:

Nếu trong các Cells hoặc Range cần tìm giá trị lớn nhất – nhỏ nhất có chứa các giá trị LOGIC như TRUE/ FALSE thì hàm MIN, MAX sẽ bỏ qua các giá trị này trong kết quả, vậy làm sao để tìm được giá trị lớn nhất – nhỏ nhất có chứa giá trị LOGIC?

➤ Câu trả lời: Khi đó bạn có thể dùng hàm MINA, MAXA thay cho hàm MIN, MAX.

Về chức năng và tính chất của hàm MINA, MAXA cũng như hàm MIN, MAX nhưng khi sử dụng hàm MINA, MAXA thì Excel định trị giá trị FALSE là 0 và TRUE là 1.

Cập nhật thông tin chi tiết về Công Thức Hàm Large If Trong Excel Để Tìm Giá Trị Lớn Nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!