Cách Sử Dụng Hàm SMALL Trong Excel để Tìm Và đánh Dấu Các Giá ...

Bạn cần tìm một vài số thấp nhất trong một trang tính, ví dụ như tìm 5 sản phẩm có doanh thu thấp nhất. Thật ra, bạn hoàn toàn có thể liệt kê các giá trị thấp nhất trong Excel với tính năng Sort. Nhưng bạn không muốn lãng phí thời gian của mình 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? Nếu vậy, hàm SMALL là một lựa chọn phù hợp. Công thức SMALL sẽ giúp bạn nhanh chóng tìm ra giá trị nhỏ nhất, nhỏ thứ hai, nhỏ thứ ba,... trong chuỗi dữ liệu của mình.

XEM NHANH BÀI VIẾT

• 1 Hàm SMALL của Excel
• 1.1 Cú pháp cơ bản của hàm SMALL
• 1.2 Những điều cần lưu ý khi sử dụng hàm SMALL
• 2 Cách sử dụng hàm SMALL trong Excel - ví dụ về công thức
• 2.1 Tìm các giá trị thấp thứ 3, 5, 10,... trong mảng
• 2.2 Tính tổng N giá trị dưới cùng
• 2.3 Truy xuất các dữ liệu liên quan đến giá trị nhỏ nhất
• 2.4 Sắp xếp các số từ thấp nhất đến cao nhất với công thức
• 3 Công thức SMALL cho ngày và giờ trong Excel
• 3.1 Tìm ngày xa nhất hoặc khoản thời gian ngắn nhất
• 3.2 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ể
• 4 Cách đánh đấu các giá trị dưới cùng trong Excel

Hàm SMALL của Excel

Cú pháp cơ bản của hàm SMALL

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(array, k)

Trong đó:

• Array - 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 SMALL có sẵn trong tất cả các phiên bản Excel dành cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và ở cả các phiên bản cũ hơn.

Để hiểu hơn về cách sử dụng cơ bản của công thức SMALL, bạn hãy quan sát ví dụ đơn giản ngay bên dưới.

Trong danh sách các số ở B2: B10, giả sử bạn muốn trích xuất giá trị nhỏ nhất thứ 3. Hãy nhập 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 - trong hình bên dưới - đã được sắp xếp theo thứ tự tăng dần:

Những điều cần lưu ý khi sử dụng hàm SMALL

Những lưu ý 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.

• Mọi ô trống, giá trị văn bản và giá trị logic TRUE và FALSE trong đối số mảng đều bị bỏ qua.
• Nếu mảng chứa một hoặc nhiều lỗi, công thức sẽ trả về error.
• Trong trường hợp có các giá trị 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à giá trị nhỏ nhất thứ 2, bạn sẽ nhận được 1 trong cả hai trường hợp.
• Giả sử n là số giá trị trong mảng, SMALL (array, 1) sẽ trả về giá trị thấp nhất và SMALL (array, 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 để biết thêm các ứng dụng thú vị khác của nó.

Tìm các giá trị thấp thứ 3, 5, 10,... trong mảng

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 hiệu quả nhất để xuất các giá trị thấp đó.

Trong bảng dưới đây, giả sử bạn muốn tìm 3 giá trị dưới cùng. Hãy nhập các số 1, 2 và 3 vào các ô riêng biệt (D3, D4 và D5 như hình bên dướ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 đối số k . Điều quan trọng là bạn phải 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à tương đối cho k .

Bạn không muốn nhập các số n thủ công? Hãy sử dụng hàm ROWS với phạm vi tham chiếu có thể mở rộng để cung cấp giá trị k .Chúng ta tạo tham chiếu tuyệt đối cho ô đầu tiên (hoặc chỉ khóa tọa độ hàng như B$2) và tham chiếu tương đối cho ô cuối cùng:

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

Do đó, phạm vi tham chiếu được mở rộng ra khi công thức được sao chép xuống cột. Trong D2, ROWS (B $ 2: B2) tạo ra 1 cho k và công thức trả về giá trị thấp nhất. Trong D3, ROWS (B $ 2: B3) trả kết quả là 2 và chúng ta nhận được giá trị thấp nhất thứ 2,... Chỉ cần sao chép công thức qua 5 ô và bạn sẽ nhận được 5 giá trị thấp nhất của mảng.

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

Bạn cầ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à công thức SUM 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 hàm SUMPRODUCT :

SUMPRODUCT(SMALL(array, {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}))

Lưu ý: Nếu bạn sử dụng tham chiếu ô thay vì hằng số mảng 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 này.

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 ta cung cấp một hằng số mảng là {1,2,3} cho đối số k, buộc nó trả về một mảng có 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.

Truy xuất các dữ liệu liên quan đến giá trị 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 kết hợp hàm INDEX MATCH cổ điển với SMALL như sau:

= INDEX ( return_array , MATCH (SMALL ( lookup_array , n ), lookup_array , 0))

Trong đó:

• 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à phạm vi có tên dự án, B2: B10 là chi phí và D3 là thứ hạng.

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 có chi phí thấp 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ó các giá trị trùng lập. Có 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 công thức phức tạp hơn một chút để phá vỡ các ràng buộc này.
• 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 giúp công thức trở nên đơ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.

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

Tôi tin rằng mọi người đều biết cách sắp xếp các số theo thứ tự với tính năng Sort trong Excel. 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 mới. Trong Excel 2019, 2016 và các phiên bản trước đó, hàm SORT này không hoạt động, nhưng bạn có thể sử dụng hàm SMALL để giải bài toán này.

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 x 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 các ô có giá trị trong tập dữ liệu gốc (trong ví dụ này là C2: C10). Bạn dùng hàm SMALL để sắp xếp các giá trị tăng dần và hàm LARGE để sắp xếp các giá trị giảm dần.

Công thức SMALL cho ngày và giờ trong Excel

Vì ngày và giờ cũng là các giá trị số (trong Excel, ngày được lưu trữ dưới dạng số tuần tự và thời gian dưới dạng phân số thập phân), nê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.

Tìm ngày xa nhất hoặc khoản thời gian ngắn nhất

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 xa nhất:

Công thức SMALL để nhận được 3 khoản thời gian 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 bên dưới, giả sử bạn muốn tìm ngày gần nhất trước một ngày cụ thể. Bạn có thể 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 hai ngày trước ngày trong ô E1, 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 này hôm nay, hãy sử dụng hàm TODAY cho tiêu chí của COUNTIF:

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

Để 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ể bọc hàm IFERROR bên ngoài công thức chính.

=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 như sau:

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

Để 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 trong E1) xuất hiện trong tập dữ liệu của chúng ta, thì đó sẽ là ngày lớn thứ 7 trong danh sách. Do đó, có sáu ngày nhỏ hơn nó. Có nghĩa là, ngày nhỏ thứ 6 là ngày trước đó gần với ngày mục tiêu nhất. 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)
• Sau đó, cắm số đếm vào đối số thứ 2 của hàm SMALL: =SMALL(B2:B10, 6)
• Để lấy ngày nhỏ thứ 5, chúng ta lấy kết quả của COUNTIF trừ đi 1,...

Cách đánh đấu 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 Top / Bottom đượ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. Nhưng bù lại, 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 định dạng tùy chỉnh này:

• Chọn phạm vi mà bạn muốn đánh dấu các giá trị dưới cùng. Trong ví dụ này là B2: B10. Nếu bạn muốn đánh dấu toàn bộ hàng, hãy chọn A2: B10.
• Trên tab Home, trong nhóm Styles, click chọn Conditional formatting > New Rule.
• Trong hộp thoại New Formatting Rule, chọn Use a formula to determine which cells to format.
• Trong hộp Format values where this formula is true, 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 cần đượ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 cần đá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), phạm vi là tham chiếu tuyệt đối ($ B $ 2: $ B $ 10).
• Nhấp vào nút Format và chọn bất kỳ định dạng nào bạn thích.
• Bấm OK hai lần để đóng cả hai cửa sổ hộp thoại.

Hàm SMALL của bạn 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! , 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 không (do lỗi đánh máy) hoặc vượt quá số giá trị trong mảng.

Trên đây là cách sử dụng công thức SMALL trong Excel để tìm và cách đánh dấu các số dưới cùng trong một tập dữ liệu. Hy vọng chúng có ích với bạn. Bên cạnh đó, hãy tham gia GITIHO ngay hôm nay nhé, chúng tôi còn rất nhiều mẹo và thủ thuật tin học văn phòng hữu ích muốn chia sẽ với bạn.