Note: The other languages of the website are Google-translated. Back to English

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

{=SUM(SMALL(IF(range=criteria,values),{1,2,N}))}
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:

=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))

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:

{=SUM(SMALL(IF((range1=criteria1)*(range2=criteria2) *(range3=criteria3)…,values),{1,2,N}))}
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:

=SUM(SMALL(IF(($A$2:$A$14=E2)*($B$2:$B$14=F2), $C$2:$C$14),{1,2,3}))

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

Bạn có muốn hoàn thành công việc hàng ngày của mình một cách nhanh chóng và hoàn hảo? Kutools cho Excel mang đến 300 tính năng nâng cao mạnh mẽ (Kết hợp sổ làm việc, tính tổng theo màu, chia nhỏ nội dung ô, chuyển đổi ngày tháng, v.v.) và tiết kiệm 80% thời gian cho bạn.

  • Được thiết kế cho 1500 tình huống công việc, giúp bạn giải quyết 80% các vấn đề về Excel.
  • Giảm hàng nghìn lần nhấp chuột và bàn phím mỗi ngày, giảm mỏi mắt và mỏi tay.
  • Trở thành chuyên gia Excel trong 3 phút. Không còn cần phải nhớ bất kỳ công thức và mã VBA đau đớn nào.
  • 30 ngày dùng thử miễn phí không giới hạn. Đảm bảo hoàn tiền trong 60 ngày. Nâng cấp miễn phí và hỗ trợ trong 2 năm.
Ribbon của Excel (với Kutools cho Excel được cài đặ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 lại các tab hiệu quả cho Office (bao gồm Excel), Giống như Chrome, Firefox và Internet Explorer mới.
Ảnh chụp màn hình của Excel (với Office Tab được cài đặt)
Nhận xét (0)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Không có bình luận nào được đăng ở đây
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất