Chỉ trả về SUM từ các công thức
Trong bài viết này, chúng ta sẽ học cách chỉ trả về SUM từ các công thức trong Excel.
Nói cách đơn giản, trong khi làm việc với dữ liệu được điền một phần. Đôi khi, chúng ta cần tìm tổng giá trị có một điều kiện. Điều kiện là nhận được tổng trong đó các giá trị chỉ được trích xuất bằng công thức. Đối với bài viết này, chúng ta sẽ cần sử dụng các chức năng sau:
Tổng = (A1 B1 C1 .. + A2 B2 C2 .. +…)
Cú pháp:
|
= ( array1 , [array2] , …) |
mảng: Là phạm vi hoặc danh sách các giá trị.
Cú pháp:
|
= ( reference ) |
tham chiếu: tham chiếu đến ô, nơi kiểm tra công thức Bây giờ chúng ta sẽ tạo một công thức từ các hàm này. Ở đây, chúng tôi sẽ cung cấp dữ liệu và chúng tôi cần kết quả tổng khi áp dụng công thức.
Sử dụng công thức:
|
= ( array * ( array) ) |
Giải thích:
-
Hàm ISFORMULA trả về TRUE & FALSE trên cơ sở điều kiện đang được kiểm tra trên các ô.
-
Hàm SUMPRODUCT xem xét giá trị 1 cho mọi giá trị TRUE và 0 cho giá trị FALSE.
-
Sản phẩm được lấy giữa các giá trị tương ứng sẽ bỏ qua các giá trị FALSE vì các giá trị được nhân với 0. Chỉ các giá trị TRUE nhận được SUM khi các giá trị được nhân với 1s.
Ví dụ:
Hãy kiểm tra công thức này bằng cách chạy nó trên một ví dụ Ở đây chúng ta có dữ liệu có Giá ngũ cốc được chiết xuất theo tích Số lượng thành Đơn giá của nó và Một số giá trị Giá được nhập theo cách thủ công.
Vì vậy, nếu tôi cần tìm Tổng giá cho giá trị mà công thức trích ra Tổng giá.
Bây giờ chúng ta sẽ sử dụng công thức dưới đây để lấy Công thức SUM:
|
= SUMPRODUCT ( E2 : E15 * ISFORMULA ( E2 : E15 ) ) |
Giải thích:
Hàm ISFORMULA trả về TRUE & FALSE trên cơ sở điều kiện đang được kiểm tra trên các ô mảng từ E2 đến E15. Hàm SUMPRODUCT coi giá trị 1 cho mọi giá trị TRUE nhận được và 0 cho giá trị FALSE như hình dưới đây.
SUMPRODUCT (E2: E15 * \ {TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE})
-
Sản phẩm được lấy giữa các giá trị tương ứng sẽ bỏ qua các giá trị FALSE vì các giá trị được nhân với 0. Chỉ các giá trị TRUE nhận được SUM vì các giá trị được nhân với 1 như hình bên dưới.
SUMPRODUCT (\ {58.41; 0; 0; 0; 82.84; 95.58; 0; 90.27; 0; 37.8; 78.48; 57.97; 97.72; 77.88}
Ở đây mảng của hàm được cung cấp dưới dạng cell_reference. Nhấn Enter để nhận kết quả.
Như bạn có thể thấy trong ảnh chụp nhanh ở trên, tổng các giá trị được trích xuất khi công thức tồn tại.
Nếu bạn cần tìm tổng các giá trị không có công thức, hãy sử dụng hàm NOT với hàm ISFORMULA.
Sử dụng công thức:
|
= SUMPRODUCT ( E2:E15 * NOT ( ISFORMULA ( E2:E15 ) ) ) |
Sử dụng công thức và nhận giá trị như thể hiện trong ảnh chụp nhanh dưới đây.
Như bạn thấy từ công thức trên bạn có thể nhận các giá trị có điều kiện. Lưu ý:
-
Hàm SUMPRODUCT coi các giá trị không phải số là 0.
-
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 nếu không hàm sẽ trả về lỗi.
Hy vọng bài viết này về cách chỉ trả về SUM từ các công thức trong Excel là giải thích. 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.
Các bài viết liên quan
Bài viết phổ biến
liên kết: / logic-công thức-nếu-hàm-với-ký tự đại diện [Nếu có ký tự đại diện]