Tính tổng giá trị N nhỏ nhất hoặc dưới cùng dựa trên tiêu chí trong Excel
Trong hướng dẫn trước, chúng ta đã thảo luận về cách tính tổng n giá trị nhỏ nhất trong một dải dữ liệu. Trong bài viết này, chúng tôi sẽ thực hiện một thao tác nâng cao hơn - để tính tổng n giá trị thấp nhất dựa trên một hoặc nhiều tiêu chí trong Excel.
Tính tổng giá trị N nhỏ nhất hoặc dưới cùng dựa trên tiêu chí trong Excel
Giả sử, tôi có một loạt dữ liệu như ảnh chụp màn hình bên dưới, bây giờ, tôi muốn tính tổng 3 đơn hàng thấp nhất của sản phẩm Apple.
Trong Excel, để tính tổng n giá trị dưới cùng trong một phạm vi có tiêu chí, bạn có thể tạo công thức mảng bằng cách sử dụng các hàm SUM, SMALL và IF. Cú pháp chung là:
Array formula, should press Ctrl + Shift + Enter keys together.
- range=criteria: Phạm vi ô phù hợp với tiêu chí cụ thể;
- values: Danh sách chứa n giá trị dưới cùng mà bạn muốn tính tổng;
- N: Giá trị dưới cùng thứ N.
Để giải quyết vấn đề trên, vui lòng áp dụng công thức mảng dưới đây vào một ô trống:
Và sau đó nhấn Ctrl + Shift + Enter các phím với nhau để có được kết quả chính xác như hình ảnh chụp màn hình bên dưới:
Giải thích công thức:
=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))
- IF(($A$2:$A$14=D2), $B$2:$B$14): Nếu sản phẩm trong phạm vi A2: A14 bằng “Apple”, nó sẽ trả về số tương đối từ danh sách đơn hàng (B2: B14); Nếu sản phẩm không phải là “Apple”, FALSE sẽ được hiển thị. Bạn sẽ nhận được kết quả như sau: {800; FALSE; FALSE; FALSE; 1000; 230; FALSE; FALSE; 1600; FALSE; 900; FALSE; 500}.
- SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): Hàm SMALL này sẽ bỏ qua các giá trị FALSE và trả về 3 giá trị dưới cùng trong mảng, do đó, kết quả là: {230,500,800}.
- SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): Cuối cùng, hàm SUM tính tổng các số trong mảng để được kết quả: 1530.
Lời khuyên: Đối phó với hai hoặc nhiều điều kiện:
Nếu bạn cần tính tổng n giá trị dưới cùng dựa trên hai hoặc nhiều tiêu chí, bạn chỉ cần thêm phạm vi và tiêu chí khác bằng ký tự * trong hàm IF như sau:
Array formula, should press Ctrl + Shift + Enter keys together.
- Range1=criteria1: Phạm vi ô đầu tiên phù hợp với tiêu chí đầu tiên;
- Range2=criteria2: Phạm vi ô thứ hai để khớp với tiêu chí thứ hai;
- Range3=criteria3: Phạm vi ô thứ ba để phù hợp với tiêu chí thứ ba;
- values: Danh sách chứa n giá trị dưới cùng mà bạn muốn tính tổng;
- N: Giá trị dưới cùng thứ N.
Ví dụ: tôi muốn tính tổng 3 đơn đặt hàng cuối cùng của sản phẩm Apple được bán bởi Kerry, vui lòng áp dụng công thức dưới đây:
Và sau đó nhấn Ctrl + Shift + Enter các phím với nhau để nhận được kết quả như bạn cần:
Hàm tương đối được sử dụng:
- TỔNG:
- Hàm SUM thêm giá trị. Bạn có thể thêm các giá trị riêng lẻ, tham chiếu ô hoặc phạm vi hoặc kết hợp cả ba.
- NHỎ:
- Hàm SMALL trong Excel trả về một giá trị số dựa trên vị trí của nó trong danh sách khi được sắp xếp theo giá trị theo thứ tự tăng dần.
- IF:
- Hàm IF kiểm tra một điều kiện cụ thể và trả về giá trị tương ứng mà bạn cung cấp cho TRUE hoặc FALSE.
Các bài viết khác:
- Tổng các giá trị N nhỏ nhất hoặc dưới cùng
- Trong Excel, chúng ta dễ dàng tính tổng một dải ô bằng cách sử dụng hàm SUM. Đôi khi, bạn có thể cần tính tổng 3, 5 hoặc n số nhỏ nhất hoặc dưới cùng trong một dải dữ liệu như hình minh họa bên dưới. Trong trường hợp này, hàm SUMPRODUCT cùng với hàm SMALL có thể giúp bạn giải quyết vấn đề này trong Excel.
- Tổng số tiền trong hóa đơn theo độ tuổi trong Excel
- Để tính tổng số tiền hóa đơn dựa trên độ tuổi như hình minh họa bên dưới có thể là một nhiệm vụ phổ biến trong Excel, hướng dẫn này sẽ chỉ ra cách tính tổng số tiền hóa đơn theo độ tuổi bằng hàm SUMIF thông thường.
- Tính tổng tất cả các ô số bỏ qua lỗi
- Khi tính tổng một dải số có chứa một số giá trị lỗi, hàm SUM thông thường sẽ không hoạt động chính xác. Để chỉ tính tổng các số và bỏ qua các giá trị lỗi, hàm AGGREGATE hoặc SUM cùng với các hàm IFERROR có thể giúp bạn.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
