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

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 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 đ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 - và G2 - ), 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 , 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 . 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 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 ở 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:

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 đ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ừ đ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 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Ỏ:

• - danh sách các môn học (B2: B15)
• - các loại trường (C2: C15)
• - trường mục tiêu (G2)
• - điểm (D2: D15)
• - 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 đối số của hàm SMALL.

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 ( ): $ 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 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 ( ), 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 đố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 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ử và . Về mặt nếu Excel, công thức sẽ trả về điểm nhỏ nhất thứ n nếu là một trong hai HOẶC LÀ .

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

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 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 đ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í.

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