Bạn có biết cách tính tổng các số trong một cột nhất định khi một giá trị trong cột khác đáp ứng bất kỳ điều kiện nào được chỉ định không? Trong bài viết này, bạn sẽ học 3 cách khác nhau để thực hiện SUMIF bằng cách sử dụng nhiều tiêu chí và logic HOẶC.
Microsoft Excel có một chức năng đặc biệt để tính tổng các ô có nhiều điều kiện – Hàm SUMIFS. Hàm này được thiết kế để hoạt động với logic AND – một ô chỉ được thêm vào khi tất cả các tiêu chí được chỉ định là TRUE cho ô đó. Tuy nhiên, trong một số trường hợp, bạn có thể cần tính tổng với nhiều tiêu chí HOẶC, nghĩa là thêm một ô khi bất kỳ điều kiện nào là TRUE. Và đây là lúc hàm SUMIF có ích.
SUMIF + SUMIF để tính tổng các ô bằng cái này hoặc cái kia
Khi bạn đang tìm cách tính tổng các số trong một cột khi cột khác bằng A hoặc B, giải pháp rõ ràng nhất là xử lý từng điều kiện riêng lẻ, sau đó cộng các kết quả lại với nhau:
SUMIF (phạm vi, tiêu chí1, sum_range) + SUMIF (phạm vi, tiêu chí2, sum_range)
Trong bảng bên dưới, giả sử bạn muốn cộng doanh số cho hai sản phẩm khác nhau, giả sử Apples và Lemons. Đối với điều này, bạn có thể cung cấp các mặt hàng quan tâm trực tiếp trong tiêu chí đối số của 2 hàm SUMIF khác nhau:
=SUMIF(A2:A10, "apples", B2:B10) + SUMIF(A2:A10, "lemons", B2:B10)
Hoặc bạn có thể nhập tiêu chí vào các ô riêng biệt và tham khảo các ô đó:
=SUMIF(A2:A10, E1, B2:B10) + SUMIF(A2:A10, E2, B2:B10)
Trong đó A2: A10 là danh sách các mục (phạm vi), B2: B10 là các số tính tổng (sum_rage), E1 và E2 là các mục đích (tiêu chí):
Công thức này hoạt động như thế nào:
Hàm SUMIF đầu tiên thêm vào Apples doanh số bán hàng, SUMIF thứ hai tính tổng Lemons bán hàng. Phép toán cộng sẽ cộng các tổng phụ với nhau và xuất ra tổng.
SUMIF với hằng số mảng – công thức thu gọn với nhiều tiêu chí
Phương pháp SUMIF + SUMIF hoạt động tốt cho 2 điều kiện. Nếu bạn cần tính tổng với 3 tiêu chí trở lên, công thức sẽ trở nên quá lớn và khó đọc. Để đạt được kết quả tương tự với công thức nhỏ gọn hơn, hãy cung cấp tiêu chí của bạn trong mảng hằng số:
SUM (SUMIF (phạm vi, {crireria1, crireria2, crireria3,…}, Sum_range))
Hãy nhớ rằng công thức này hoạt động dựa trên logic HOẶC – một ô được tính tổng khi bất kỳ điều kiện đơn lẻ nào được đáp ứng.
Trong trường hợp của chúng tôi, để tính tổng doanh số cho 3 mặt hàng khác nhau, công thức là:
=SUM(SUMIF(A2:A10, {"Apples","Lemons","Oranges"}, B2:B10))
Trong ảnh chụp màn hình ở trên, các điều kiện được mã hóa cứng trong một mảng, có nghĩa là bạn sẽ phải cập nhật công thức với mọi thay đổi trong tiêu chí. Để tránh điều này, bạn có thể nhập tiêu chí vào các ô được xác định trước và cung cấp cho công thức dưới dạng tham chiếu phạm vi (E1: E3 trong ví dụ này).
=SUM(SUMIF(A2:A10, E1:E3, B2:B10))
Trong Excel 365 hỗ trợ mảng động, nó hoạt động như một công thức thông thường được hoàn thành với Đi vào Chìa khóa. Trong các phiên bản trước động của Excel 2019, Excel 2016, Excel 2013 trở về trước, nó phải được nhập dưới dạng công thức mảng với Ctrl + Shift + Enter đường tắt:
Công thức này hoạt động như thế nào:
Một hằng số mảng được cắm vào các tiêu chí của SUMIF buộc nó phải trả về nhiều kết quả dưới dạng một mảng. Trong trường hợp của chúng tôi, đó là 3 số tiền khác nhau: cho Apples, Lemons và Oranges:
{425;425;565}
Để có được tổng số, chúng tôi sử dụng Hàm SUM và quấn nó xung quanh công thức SUMIF.
SUMPRODUCT và SUMIF để tính tổng các ô có nhiều điều kiện OR
Bạn không thích mảng và đang tìm kiếm một công thức bình thường cho phép bạn tính tổng với nhiều tiêu chí trong các ô khác nhau? Không vấn đề gì. Thay vì SUM, hãy sử dụng GIỚI THIỆU hàm xử lý mảng nguyên bản:
SUMPRODUCT (SUMIF (phạm vi, crireria_range, sum_range))
Giả sử các điều kiện nằm trong các ô E1, E2 và E3, công thức có dạng như sau:
=SUMPRODUCT(SUMIF(A2:A10, E1:E3, B2:B10))
Công thức này hoạt động như thế nào:
Giống như trong ví dụ trước, hàm SUMIF trả về một mảng số, đại diện cho tổng của mỗi điều kiện riêng lẻ. SUMPRODUCT cộng các số này lại với nhau và xuất ra tổng cuối cùng. Không giống như hàm SUM, hàm SUMPRODUCT được thiết kế để xử lý mảng, vì vậy nó hoạt động như một công thức thông thường mà bạn không cần phải nhấn Ctrl + Shift + Enter.
SUMIF sử dụng nhiều tiêu chí với các ký tự đại diện
Kể từ khi Hàm SUMIF trong Excel hỗ trợ các ký tự đại diện, bạn có thể đưa chúng vào nhiều tiêu chí nếu cần.
Ví dụ, để tổng hợp doanh số bán hàng cho tất cả các loại apples và bananas, công thức là:
=SUM(SUMIF(A2:A10, {"*Apples","*Bananas"}, B2:B10))
Nếu các điều kiện của bạn được cho là được nhập vào các ô riêng lẻ, bạn có thể nhập ký tự đại diện trực tiếp vào các ô đó và cung cấp tham chiếu phạm vi làm tiêu chí cho công thức SUMPRODUCT SUMIF:
Trong ví dụ này, chúng tôi đặt một ký tự đại diện trước tên mục để khớp với bất kỳ chuỗi ký tự đứng trước nào, chẳng hạn như Green apples và Goldfinger bananas
. Để nhận tổng số cho các mục có chứa văn bản cụ thể ở bất kỳ vị trí nào trong ô, hãy đặt dấu hoa thị ở cả hai bên, ví dụ: “* apple *”.
Đó là cách sử dụng SUMIF trong Excel với nhiều đ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!
The post Cách sử dụng SUMIF trong Excel với nhiều điều kiện (HOẶC logic) 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/cach-su-dung-sumif-trong-excel-voi-nhieu-dieu-kien-hoac-logic.html
Không có nhận xét nào:
Đăng nhận xét