Hàm SUMIFS với toán tử so sánh trong Excel
Trong bài này, chúng ta sẽ tìm hiểu Hàm SUMIFS với Toán tử so sánh trong Excel.
Toán tử so sánh AND và OR
Logic AND được sử dụng khi tất cả các điều kiện đã nêu cần phải thỏa mãn.
Logic HOẶC được sử dụng khi bất kỳ điều kiện nào đã nêu thỏa mãn.
Nói một cách dễ hiểu, Excel cho phép bạn thực hiện cả hai logic này trong hàm SUMIFS.
Công thức Cú pháp
Logic OR với SUMIFS được sử dụng khi chúng ta cần tìm tổng nếu điều kiện value1 hoặc value2 thỏa mãn Cú pháp của SUMIFS với logic OR
|
=SUMIFS ( sum_range, criteria_range, \{ “value1”, “value2” }) |
Ở đây chúng ta cần tìm tổng của phạm vi Bán hàng Nếu “Mike” hoặc “Joe” xảy ra trong phạm vi Emp Sử dụng
|
=SUMIFS(C2:C10,A2:A10,\{“Mike”,”Joe”}) |
Như bạn có thể thấy, chúng tôi có tổng doanh số bán hàng của Joe và Mike.
Logic AND với SUMIFS được sử dụng khi chúng ta cần tìm tổng nếu cả giá trị1 và value2 đều thỏa mãn điều kiện Cú pháp của SUMIFS với logic AND
|
=SUMIFS ( sum_range, criteria_range1, value1, [criteria_range2, value2],.. ) |
Ở đây chúng ta cần tìm tổng của phạm vi Bán hàng Nếu “Joe” xảy ra trong phạm vi Emp và “North” xảy ra trong phạm vi Khu vực Sử dụng
|
=SUMIFS ( C2:C10, A2:A10, G1, B2:B10, G2) |
Như bạn có thể thấy, chúng tôi chỉ lấy tổng của vùng Joe ’North.
Bạn cũng có thể tìm ra tổng bằng cách sử dụng hàm SUMPRODUCT. Hãy hiểu ở đây, chúng ta sẽ học cách Tính tổng 5 giá trị trên cùng hoặc 5 giá trị dưới cùng từ mảng đã cho trong Excel.
Nói một cách dễ hiểu, trong khi làm việc với các số dữ liệu, đôi khi được đưa ra một điều kiện tức là khi chúng ta cần lấy tổng các số của 5 số trên cùng của mảng hoặc dưới cùng của mảng. Bạn có thể thực hiện giải pháp cho vấn đề này một cách dễ dàng bằng cách sử dụng các hàm excel như được giải thích bên dưới.
Tổng các giá trị TOP N
Làm thế nào để giải quyết vấn đề?
Công thức chung:
|
= ( ( range, \{ 1 , 2 , …. ,n } } ) |
range: dải giá trị Giá trị: các số được phân tách bằng dấu phẩy như nếu bạn muốn tìm 3 giá trị hàng đầu, hãy sử dụng \ {1, 2, 3}.
Một sơ đồ khác Ở đây chúng tôi có một loạt các giá trị từ A1: A23.
Ở đây phạm vi được cung cấp bằng cách sử dụng công cụ excel phạm vi được đặt tên.
Đầu tiên, chúng ta cần tìm Năm giá trị hàng đầu bằng cách sử dụng hàm LARGE và sau đó thực hiện phép tính tổng trên 5 giá trị đó. Bây giờ chúng ta sẽ sử dụng công thức sau để lấy tổng
Sử dụng công thức:
|
= ( ( range, \{ 1 , 2 , 3 , 4 , 5 } ) ) |
Giải thích:
-
liên kết: / logic-công thức-excel-lớn-hàm [LARGE]
hàm trả về 5 giá trị số hàng đầu và trả về mảng cho hàm SUMPRODUCT. = SUMPRODUCT (\ {149, 123, 100, 87, 85}))
-
Hàm SUMPRODUCT nhận mảng 5 giá trị hàng đầu, mảng có 5 số hàng đầu trả về giá trị SUM của các số đó.
Ở đây phạm vi được cho là phạm vi được đặt tên. Nhấn Enter để lấy SUM của 5 số hàng đầu.
Như bạn thấy trong ảnh chụp nhanh ở trên, tổng là 544. Tổng các giá trị 149 + 123 + 100 + 87 + 85 = 544.
Quá trình trên được sử dụng để tính tổng của một vài số từ trên cùng. Nhưng để tính toán cho n (lớn) số giá trị trong một phạm vi dài.
Sử dụng công thức:
|
= ( ( range, ROW ( INDIRECT ( “1:10” ) ) |
Ở đây, chúng tôi tạo ra tổng của 10 giá trị hàng đầu bằng cách nhận một mảng từ 1 đến 10 \ {1; 2; 3; 4 ; 5; 6; Số 7; số 8 ; Số 9; 10} bằng cách sử dụng các hàm ROW & INDIRECT trong Excel.
Ở đây chúng tôi có tổng của 10 số hàng đầu \ {149; 123; 100; 87; 85; 82; 61; 58; 54; 51}) kết quả là 850.
Tổng giá trị N dưới cùng
Làm thế nào để giải quyết vấn đề?
Công thức chung:
|
= ( ( range, \{ 1 , 2 , …. ,n } } ) |
range: dải giá trị Giá trị: các số được phân tách bằng dấu phẩy như nếu bạn muốn tìm 3 giá trị dưới cùng, hãy sử dụng \ {1, 2, 3}.
Ví dụ:
Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới.
Ở đây chúng tôi có một loạt các giá trị từ A1: A23.
Ở đây phạm vi được cung cấp bằng cách sử dụng công cụ excel phạm vi được đặt tên.
Đầu tiên, chúng ta cần tìm Năm giá trị dưới cùng bằng cách sử dụng hàm SMALL và sau đó thực hiện phép tính tổng trên 5 giá trị đó. Bây giờ chúng ta sẽ sử dụng công thức sau để lấy tổng
Sử dụng công thức:
|
= ( ( range, \{ 1 , 2 , 3 , 4 , 5 } ) ) |
Giải thích:
hàm trả về 5 giá trị số dưới cùng và trả về mảng cho hàm SUMPRODUCT.
SUMPRODUCT (\ {23, 27, 28, 28, 30}))
-
Hàm SUMPRODUCT nhận mảng 5 giá trị dưới cùng, mảng có 5 số dưới cùng trả về giá trị SUM của các số đó.
Ở đây phạm vi được cho là phạm vi được đặt tên. Nhấn Enter để lấy SUM của 5 số dưới cùng.
Như bạn thấy trong ảnh chụp ở trên, tổng là 136. Tổng các giá trị 23 + 27 + 28 + 28 + 30 = 136.
Quá trình trên được sử dụng để tính tổng của một vài số từ dưới cùng. Nhưng để tính toán cho n (lớn) số giá trị trong một phạm vi dài.
Sử dụng công thức:
|
= ( ( range, ROW ( INDIRECT ( “1:10” ) ) |
Ở đây chúng tôi tạo tổng của 10 giá trị dưới cùng bằng cách nhận một mảng từ 1 đến 10 \ {1; 2; 3; 4 ; 5; 6; Số 7; số 8 ; Số 9; 10} bằng cách sử dụng các hàm ROW & INDIRECT trong Excel.
Ở đây chúng tôi có tổng của 10 số dưới cùng cho kết quả là 307.
Dưới đây là một số lưu ý quan sát được hiển thị bên dưới.
Ghi chú:
-
Công thức chỉ hoạt động với số.
-
Công thức chỉ hoạt động khi không có bản sao trong bảng tra cứu. Hàm SUMPRODUCT coi các giá trị không phải số (như văn bản abc) và các giá trị lỗi (như #NUM !, #NULL!) Là giá trị rỗng.
-
Hàm SUMPRODUCT coi giá trị logic là TRUE là 1 và False là 0.
-
Mảng đối số phải có cùng độ dài với hàm khác.
Hy vọng bài viết này về Hàm SUMIFS với Toán tử so sánh trong Excel. Tìm thêm các bài viết về hàm SUMPRODUCT tại đây. Vui lòng chia sẻ câu hỏi của bạn bên dưới trong hộp bình luận. Chúng tôi sẽ hỗ trợ bạn.