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:

{= SMALL (IF (tiêu_phí=tiêu chí, giá trị), n)}

Ở đâ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.

Từ bảng mẫu của chúng tôi, hãy chọn 3 điểm dưới cùng trong một chủ đề cụ thể, giả sử Nghệ thuật. Với danh sách các môn học (tiêu_phí) trong B2: B15, điểm C2: C15 (giá trị) và n trong E3, công thức có dạng sau:

=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), "-")

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

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:

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

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:

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

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.

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:

{= SMALL (IF (tiêu_phí1=tiêu chí1, IF(tiêu_phí2=tiêu chí2, giá trị)), n)}

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

{= SMALL (IF ((tiêu_phí1=tiêu chí1) * (tiêu_phí2=tiêu chí2), giá trị), n)}

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)
• Tiêu chí1 – chủ đề mục tiêu (G1)
• 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)

=SMALL(IF(($B$2:$B$15=$G$1) * ($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.

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

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.

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:

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

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;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;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;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;FALSE;FALSE;125}

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.

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

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

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

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.

{= SMALL (IF ((tiêu_phí1=tiêu chí1) + (tiêu_phí2=tiêu chí2), giá trị), n)}

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:

=SMALL(IF(($B$2:$B$15="History") + ($B$2:$B$15="Literature"), $C$2:$C$15), 1)

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:

=SMALL(IF(($B$2:$B$15=$E$2) + ($B$2:$B$15=$F$2), $C$2:$C$15), E6)

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

Logic của công thức rất giống với những gì được thảo luận trong SMALL IF với nhiều tiêu chí AND thí dụ. Sự khác biệt là phương trình range = tiêu chí được kết hợp với phép toán cộng hoạt động như Toán tử Or trong công thức mảng:

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:

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

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:

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

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

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:

{= SMALL (IF (giá trị<> 0, giá trị), n)}

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

=SMALL(IF($C$2:$C$15<>0, $C$2:$C$15), ROWS(A$2:A2))

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.

Công thức 2. SMALL IF lớn hơn 0 với điều kiện

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

{= SMALL (IF ((giá trị<> 0) * (tiêu_phí=tiêu chí), giá trị), n)}

Ví dụ, chúng ta hãy lấy 3 điểm dưới cùng cho từng môn học. Đối với điều này, chúng tôi nhập tên chủ đề trong E2 (Nghệ thuật) và F2 (Khoa học) và sử dụng công thức này để trích xuất giá trị nhỏ nhất Nghệ thuật điểm số:

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

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í

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.

Một cách khác để lấy giá trị dưới cùng thứ n trong Excel dựa trên các điều kiện là sử dụng hàm SMALL cùng với FILTER. Điều này hoạt động đẹp như một công thức bình thường được hoàn thành với Đi vào Chìa khóa.

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:

SMALL (FILTER (giá trị, tiêu_phí=tiêu chí), n)

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

SMALL (FILTER (giá trị, (tiêu_phí1=tiêu chí1) * (tiêu_phí2=tiêu chí2)), n)

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

Đó là cách sử dụng SMALL IF trong Excel để tìm các giá trị nhỏ nhất dựa trên điều kiện. 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 SMALL IF công thức để tìm giá trị thấp 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/small-if-cong-thuc-de-tim-gia-tri-thap-nhat-thu-n.html