Hàm SMALL của Excel để tìm và đánh dấu các giá trị thấp nhất

Trong hướng dẫn ngắn này, chúng ta sẽ nói về hàm SMALL của Excel, cách hoạt động và cách sử dụng nó để tìm số, ngày hoặc giờ nhỏ nhất thứ N.

Bạn cần tìm một vài số thấp nhất trong một trang tính? Điều này khá dễ thực hiện với tính năng Sắp xếp trong Excel. Bạn không muốn lãng phí thời gian vào việc sắp xếp lại dữ liệu của mình với mỗi lần thay đổi? Hàm SMALL sẽ giúp bạn nhanh chóng tìm ra giá trị nhỏ nhất, nhỏ nhất thứ hai, nhỏ nhất thứ ba, v.v.

Hàm SMALL của Excel

SMALL là một hàm thống kê trả về giá trị nhỏ nhất thứ n trong một tập dữ liệu.

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

Small (mảng, k)

Ở đâu:

• Mảng – một mảng hoặc một dải ô để trích xuất giá trị nhỏ nhất.
• K – một số nguyên cho biết vị trí từ giá trị thấp nhất trở về, tức là nhỏ nhất thứ k.

Hàm 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.

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

Một công thức Small ở dạng cơ bản rất dễ xây dựng – bạn chỉ cần chỉ định phạm vi và vị trí từ mục nhỏ nhất để trả về.

Trong danh sách các số trong B2: B10, giả sử bạn muốn trích xuất 3^rd giá trị nhỏ nhất. Công thức đơn giản như sau:

=SMALL(B2:B10, 3)

Để giúp bạn kiểm tra kết quả dễ dàng hơn, cột B được sắp xếp theo thứ tự tăng dần:

4 điều bạn nên biết về hàm Small

Những lưu ý sử dụng sau đây sẽ giúp bạn hiểu rõ hơn về hoạt động của hàm SMALL và tránh nhầm lẫn khi xây dựng công thức cho riêng mình.

1. Bất kì ô trống, bản văn giá trị và hợp lý giá trị TRUE và FALSE trong mảng đối số bị bỏ qua.
2. Nếu mảng chứa một hoặc nhiều sai sót, một lỗi được trả lại.
3. Trong trường hợp có trùng lặp trong mảng, công thức của bạn có thể dẫn đến “ràng buộc”. Ví dụ: nếu hai ô chứa số 1 và hàm SMALL được cấu hình để trả về giá trị nhỏ nhất và số 2^nd giá trị nhỏ nhất, bạn sẽ nhận được 1 trong cả hai trường hợp.
4. Giả sử n là số giá trị trong mảng, SMALL (mảng, 1) sẽ trả về giá trị thấp nhất và SMALL (mảng, n) sẽ chọn giá trị cao nhất.

Cách sử dụng hàm SMALL trong Excel – ví dụ về công thức

Và bây giờ, chúng ta hãy xem xét thêm một số ví dụ về hàm SMALL của Excel vượt ra ngoài cách sử dụng cơ bản của nó.

Tìm các giá trị 3, 5, 10, v.v.

Như bạn đã biết, hàm SMALL được thiết kế để tính giá trị thấp nhất thứ n. Ví dụ này cho thấy cách làm điều này hiệu quả nhất.

Trong bảng dưới đây, giả sử bạn muốn tìm 3 giá trị dưới cùng. Đối với điều này, hãy nhập các số 1, 2 và 3 vào các ô riêng biệt (D3, D4 và D5 trong trường hợp của chúng tôi). Sau đó, nhập công thức sau vào E3 và kéo nó xuống qua E5:

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

Trong E3, công thức trích ra giá trị nhỏ nhất bằng cách sử dụng số trong D3 cho k tranh luận. Điều quan trọng là cung cấp các tham chiếu ô thích hợp để công thức sao chép chính xác trong các ô khác: tuyệt đối cho mảng và quan hệ cho k.

Bạn không muốn làm phiền gõ các cấp bậc theo cách thủ công? Sử dụng chức năng ROWS với mở rộng phạm vi tham chiếu để cung cấp k giá trị. Đối với điều này, chúng tôi thực hiện một tham chiếu tuyệt đối cho ô đầu tiên (hoặc chỉ khóa tọa độ hàng như B $ 2) và tham khảo tương đối cho ô cuối cùng:

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

Do đó, tham chiếu phạm vi mở rộng khi công thức được sao chép xuống cột. Trong D2, ROWS (B $ 2: B2) tạo ra 1 cho kvà công thức trả về chi phí thấp nhất. Trong D3, ROWS (B $ 2: B3) mang lại 2 và chúng tôi nhận được 2^nd chi phí thấp nhất, v.v.

Chỉ cần sao chép công thức qua 5 ô và bạn sẽ nhận được 5 giá trị dưới cùng:

Tính tổng N giá trị dưới cùng

Muốn tìm tổng các giá trị n nhỏ nhất trong một tập dữ liệu? Nếu bạn đã trích xuất các giá trị như được hiển thị trong ví dụ trước, giải pháp đơn giản nhất sẽ là TỔNG công thức như:

=SUM(E3:E5)

Hoặc bạn có thể tạo một công thức độc lập bằng cách sử dụng hàm SMALL cùng với GIỚI THIỆU:

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

Để nhận tổng của 3 giá trị dưới cùng trong tập dữ liệu của chúng tôi, công thức có dạng sau:

=SUMPRODUCT(SMALL(B2:B10, {1,2,3}))

Hàm SUM sẽ cho kết quả tương tự:

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

Ghi chú. Nếu bạn dùng tham chiếu ô thay vì mảng hằng số 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, SUM SMALL hoạt động như một công thức thông thường trong cả hai trường hợp.

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

Trong một công thức thông thường, SMALL trả về một giá trị nhỏ nhất thứ k trong một phạm vi. Trong trường hợp này, chúng tôi cung cấp một mảng hằng số như {1,2,3} cho đối số k, buộc nó trả về một mảng gồm 3 giá trị nhỏ nhất:

{29240, 43610, 58860}

Hàm SUMPRODUCT hoặc SUM cộng các số trong mảng và xuất ra tổng. Đó là nó!

Công thức INDEX MATCH SMALL để nhận các kết quả phù hợp nhỏ nhất

Trong trường hợp bạn muốn truy xuất một số dữ liệu được liên kết với giá trị nhỏ nhất, hãy sử dụng INDEX MATCH kết hợp với SMALL cho giá trị tra cứu:

INDEX(return_array, MATCH (Small (lookup_array, n), lookup_array, 0))

Ở đâu:

• Return_array là một phạm vi để trích xuất dữ liệu liên quan.
• Lookup_array là một phạm vi để tìm kiếm giá trị thứ n thấp nhất.
• N là vị trí của giá trị nhỏ nhất cần quan tâm.

Ví dụ, để lấy tên của dự án có chi phí thấp nhất, công thức trong E3 là:

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

Trong đó A2: A10 là tên dự án, B2: B10 là chi phí và D3 là thứ hạng từ nhỏ nhất.

Sao chép công thức vào các ô bên dưới (E4 và E5), và bạn sẽ nhận được tên của 3 dự án rẻ nhất:

Ghi chú:

• Giải pháp này hoạt động tốt cho một tập dữ liệu không có bản sao. Tuy nhiên, hai hoặc nhiều giá trị trùng lặp trong một cột số có thể tạo ra “ràng buộc” trong xếp hạng, dẫn đến kết quả sai. Trong trường hợp này, hãy sử dụng tinh vi hơn một chút công thức để phá vỡ quan hệ.
• Trong Excel 365, tác vụ này có thể được thực hiện với sự trợ giúp của các hàm mảng động mới. Ngoài việc đơn giản hơn nhiều, cách tiếp cận này 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ấy giá trị N dưới cùng trong Excel.

Sắp xếp các số từ thấp nhất đến cao nhất với một công thức

Tôi tin rằng mọi người đều biết cách đặt các con số theo thứ tự Sắp xếp Excel đặc tính. Nhưng bạn có biết cách thực hiện sắp xếp theo công thức không? Người dùng Excel 365 có thể làm điều đó một cách dễ dàng với Hàm SORT. Trong Excel 2019, 2016 và các phiên bản trước đó, SORT không hoạt động, than ôi. Nhưng có một chút niềm tin, và Small sẽ đến giải cứu 🙂

Giống như trong ví dụ đầu tiên, chúng tôi sử dụng hàm ROWS với tham chiếu phạm vi mở rộng để tăng k bởi 1 trong mỗi hàng nơi công thức được sao chép:

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

Nhập công thức vào ô đầu tiên, sau đó kéo công thức xuống bao nhiêu ô có giá trị trong tập dữ liệu gốc (trong ví dụ này là C2: C10):

Tiền boa. Xắp xếp giảm dần, sử dụng hàm LARGE thay vì SMALL.

Công thức Small của Excel cho ngày và giờ

Vì ngày và giờ cũng là giá trị số (trong hệ thống Excel nội bộ, ngày được lưu trữ dưới dạng số tuần tự và lần dưới dạng phân số thập phân), hàm SMALL cũng có thể xử lý chúng mà không cần thêm bất kỳ nỗ lực nào từ phía bạn.

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, một công thức cơ bản mà chúng tôi đã sử dụng cho các con số cũng hoạt động tốt cho ngày và giờ:

=SMALL($B$2:$B$10, D2)

Công thức Small để tìm 3 ngày sớm nhất:

Công thức Small để nhận được 3 lần ngắn nhất:

Ví dụ tiếp theo cho thấy cách hàm SMALL có thể giúp bạn hoàn thành một nhiệm vụ cụ thể hơn liên quan đến ngày tháng.

Tìm một ngày trước đó gần nhất với ngày hôm nay hoặc ngày cụ thể

Trong danh sách ngày tháng, giả sử bạn muốn tìm ngày gần nhất trước một ngày cụ thể. Điều này có thể được thực hiện bằng cách sử dụng hàm SMALL kết hợp với COUNTIF.

Với danh sách các ngày trong B2: B10 và ngày mục tiêu trong E1, công thức sau sẽ trả về một ngày trước đó gần với ngày mục tiêu nhất:

=SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1))

Để trích xuất một ngày hai ngày trước ngày trong E1, tức là một ngày trước đó nhưng một ngày, công thức là:

=SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1)

Để tìm một ngày trong quá khứ gần nhất với ngày hôm nay, sử dụng Hàm TODAY cho tiêu chí của COUNTIF:

=SMALL(B2:B10, COUNTIF(B2:B10, "<"&TODAY()))

Tiền boa. Để tránh lỗi 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ể kết thúc Hàm IFERROR xung quanh công thức của bạn, như sau:

=IFERROR(SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1), "Not Found")

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

Ý tưởng chung là đếm số ngày nhỏ hơn ngày mục tiêu bằng COUNTIF. Và số lượng này chính xác là những gì hàm SMALL cần cho k tranh luận.

Để hiểu rõ hơn về khái niệm này, chúng ta hãy xem xét nó từ một góc độ khác:

Nếu ngày 1 tháng 8 năm 2020 (ngày mục tiêu ở E1) xuất hiện trong tập dữ liệu của chúng tôi, thì đó sẽ là ngày 7^{thứ tự} ngày lớn nhất trong danh sách. Do đó, có sáu ngày nhỏ hơn nó. Có nghĩa là 6^{thứ tự} ngày nhỏ nhất là ngày trước đó gần nhất với ngày mục tiêu.

Vì vậy, trước tiên, chúng tôi tính toán xem có bao nhiêu ngày nhỏ hơn ngày trong E1 (kết quả là 6):

COUNTIF(B2:B10, "<"&E1)

Và sau đó, cắm số đếm vào 2^nd đối số của Small:

=SMALL(B2:B10, 6)

Để có được ngày trước nhưng một ngày (là 5^{thứ tự} ngày nhỏ nhất trong trường hợp của chúng tôi), chúng tôi trừ 1 từ kết quả của COUNTIF.

Cách tô sáng các giá trị dưới cùng trong Excel

Để đánh dấu n giá trị nhỏ nhất trong bảng của bạn với Định dạng có điều kiện trong Excel, bạn có thể sử dụng tùy chọn Trên cùng / Dưới cùng được tích hợp sẵn hoặc thiết lập quy tắc của riêng bạn dựa trên công thức Small. Phương pháp đầu tiên nhanh hơn và dễ áp ​​dụng hơn, trong khi phương pháp thứ hai cung cấp khả năng kiểm soát và linh hoạt hơn. Các bước dưới đây sẽ hướng dẫn bạn cách tạo quy tắc tùy chỉnh:

1. Chọn phạm vi mà bạn muốn đánh dấu các giá trị dưới cùng. Trong trường hợp của chúng tôi, các số ở B2: B10, vì vậy chúng tôi chọn nó. Nếu bạn muốn đánh dấu toàn bộ hàng, hãy chọn A2: B10.
2. Trên Trang Chủ tab, trong Phong cách nhóm, bấm Định dạng có điều kiện > Quy tắc mới.
3. bên trong Quy tắc định dạng mới hộp thoại, chọn Sử dụng công thức để xác định ô cần định dạng.
4. bên trong Định dạng các giá trị trong đó công thức này đúng , hãy nhập một công thức như sau:=B2<=SMALL($B$2:$B$10, 3)Trong đó B2 là ô ngoài cùng bên trái của phạm vi số sẽ được kiểm tra, $ B $ 2: $ B $ 10 là toàn bộ phạm vi và 3 là n giá trị dưới cùng để đánh dấu.Trong công thức của bạn, hãy lưu ý đến các loại tham chiếu: ô ngoài cùng bên trái là tham chiếu tương đối (B2) trong khi phạm vi là tham chiếu tuyệt đối ($ B $ 2: $ B $ 10).
5. Nhấn vào định dạng và chọn bất kỳ định dạng nào bạn thích.
6. Bấm OK hai lần để đóng cả hai cửa sổ hộp thoại.

Làm xong! 3 giá trị dưới cùng trong cột B được đánh dấu:

Để biết thêm thông tin, vui lòng xem Định dạng có điều kiện trong Excel dựa trên công thức.

Hàm SMALL của Excel không hoạt động

Như bạn vừa thấy từ các ví dụ của chúng tôi, việc sử dụng hàm SMALL trong Excel khá dễ dàng và bạn không gặp khó khăn gì với nó. Nếu công thức của bạn không hoạt động, rất có thể đó sẽ là lỗi #NUM! , có thể xảy ra vì những lý do sau:

• Mảng trống hoặc không chứa một giá trị số.
• Các k giá trị nhỏ hơn 0 (một lỗi đánh máy ngớ ngẩn có thể khiến bạn mất hàng giờ khắc phục sự cố!) hoặc vượt quá số giá trị trong mảng.

Đó là cách sử dụng công thức Small trong Excel để tìm và đánh dấu các số dưới cùng trong một tập dữ liệu. Nếu bạn biết bất kỳ trường hợp nào khác mà chức năng có ích, bạn rất mong được chia sẻ trong các nhận xét. 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!

The post Hàm SMALL của Excel để tìm và đánh dấu các giá trị thấp 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/ham-small-cua-excel-de-tim-va-danh-dau-cac-gia-tri-thap-nhat.html