Hàm SUMPRODUCT là một trong những hàm mạnh mẽ nhất trong Excel, cho phép thực hiện các phép tính phức tạp trên mảng dữ liệu. Bài viết này sẽ hướng dẫn bạn cách sử dụng hàm SUMPRODUCT, từ cơ bản đến nâng cao, kèm theo ví dụ minh họa cụ thể trong bối cảnh Việt Nam.
Sự khác biệt kỹ thuật giữa hai công thức nằm ở cách chuyển đổi giá trị TRUE và FALSE thành 1 và 0 – thông qua hai toán tử đơn phân hoặc phép nhân. Kết quả là hai mảng gồm các giá trị 1 và 0:
Giá trị của hai mảng
Phép nhân được thực hiện bởi hàm SUMPRODUCT kết hợp chúng thành một mảng mới. Do nhân với 0 luôn bằng 0, nên giá trị 1 chỉ xuất hiện khi cả hai điều kiện được đáp ứng, dẫn đến việc chỉ những hàng thỏa mãn điều kiện mới được tính:
Đếm/Tính Tổng/Tính Trung Bình Có Điều Kiện Với Nhiều Tiêu Chuẩn
Trong Excel 2003 và các phiên bản trước, không có hàm IFS. Một trong những cách sử dụng phổ biến của hàm SUMPRODUCT là tính tổng hoặc đếm có điều kiện với nhiều tiêu chuẩn. Từ Excel 2007, Microsoft đã giới thiệu các hàm SUMIFS, COUNTIFS và AVERAGEIFS dành riêng cho việc này.
Tuy nhiên, ngay cả trong các phiên bản Excel hiện đại, SUMPRODUCT vẫn là một lựa chọn thay thế hữu ích, ví dụ, khi tính tổng và đếm có điều kiện với hàm logic OR. Dưới đây là một số ví dụ minh họa.
Công Thức SUMPRODUCT Với Hàm Logic AND
Giả sử bạn có dữ liệu sau, với cột A là vùng miền, cột B là mặt hàng và cột C là doanh số:
Mục tiêu là đếm, tính tổng và tính trung bình doanh số táo ở miền Bắc.
Trong Excel 2016, 2013, 2010 và 2007, bạn có thể sử dụng SUMIFS, COUNTIFS và AVERAGEIFS. Nếu bạn muốn tìm hiểu sâu hơn, hoặc đang sử dụng Excel 2003 hoặc cũ hơn, hàm SUMPRODUCT vẫn hoàn toàn đáp ứng được.
Để đếm doanh số táo ở miền Bắc:
=SUMPRODUCT(–(A2:A12="miền Bắc"), –(B2:B12="táo"))hoặc
=SUMPRODUCT((A2:A12="miền Bắc")*(B2:B12="táo"))Để tính tổng doanh số táo ở miền Bắc:
=SUMPRODUCT(–(A2:A12="miền Bắc"), –(B2:B12="táo"), C2:C12)hoặc
=SUMPRODUCT((A2:A12="miền Bắc")*(B2:B12="táo")*C2:C12)Để tính trung bình doanh số táo ở miền Bắc:
=SUMPRODUCT(–(A2:A12="miền Bắc"), –(B2:B12="táo"), C2:C12) / SUMPRODUCT(–(A2:A12="miền Bắc"), –(B2:B12="táo"))Để linh hoạt hơn, bạn có thể nhập vùng miền và mặt hàng vào các ô riêng, rồi tham chiếu đến chúng trong công thức:
Công Thức SUMPRODUCT Tính Tổng Có Điều Kiện Hoạt Động Như Thế Nào?
Từ ví dụ trên, bạn đã biết cách SUMPRODUCT đếm số ô với nhiều điều kiện. Việc hiểu cách tính tổng cũng tương tự.
Công thức dùng để tính tổng doanh số táo ở miền Bắc là:
=SUMPRODUCT(–(A2:A12="miền Bắc"), –(B2:B12="táo"), C2:C12)Công thức này tạo ra 3 mảng:
Kết quả trung gian của công thức SUMPRODUCT
Mảng 1: 1 đại diện cho miền Bắc, 0 là các miền khác.
Mảng 2: 1 đại diện cho táo, 0 là các mặt hàng khác.
Mảng 3: Chứa doanh số từ C2 đến C12.
Vì nhân với 0 bằng 0, nhân với 1 giữ nguyên giá trị, mảng cuối cùng chứa doanh số và các số 0. Doanh số chỉ xuất hiện khi cả hai mảng đầu tiên có giá trị 1 ở cùng vị trí, tức là cả hai điều kiện được đáp ứng:
Tổng các giá trị trong mảng cuối cùng chính là doanh số táo ở miền Bắc.
Công Thức SUMPRODUCT Với Hàm Logic OR
Để đếm hoặc tính tổng có điều kiện với hàm logic OR, sử dụng dấu cộng (+) giữa các mảng. Dấu cộng (+) hoạt động như toán tử OR, trả về TRUE nếu bất kỳ điều kiện nào được đáp ứng.
Ví dụ, để đếm tổng doanh số táo và chanh, không phân biệt vùng miền:
=SUMPRODUCT((B2:B12="táo")+(B2:B12="chanh"))Để tính tổng doanh số táo và chanh, thêm dải ô doanh số:
=SUMPRODUCT((B2:B12="táo")+(B2:B12="chanh"), C2:C12)
Công Thức SUMPRODUCT Với Hàm Logic AND và OR
Trong nhiều trường hợp, bạn cần đếm hoặc tính tổng có điều kiện với cả AND và OR. Ngay cả các hàm IFs trong Excel mới nhất cũng không làm được điều này trực tiếp. Một giải pháp là kết hợp nhiều hàm SUMIFS hoặc COUNTIFS.
Cách khác là sử dụng SUMPRODUCT, với dấu * là AND và dấu + là OR.
Để đếm số lần táo và chanh được bán ở miền Bắc:
=SUMPRODUCT((A2:A12="miền Bắc")*((B2:B12="táo")+(B2:B12="chanh")))Để tính tổng doanh số:
=SUMPRODUCT((A2:A12="miền Bắc")*((B2:B12="táo")+(B2:B12="chanh"))*C2:C12)
Công Thức SUMPRODUCT Tính Trung Bình Có Trọng Số
SUMPRODUCT cũng được dùng để tính trung bình có trọng số. Công thức tổng quát là:
SUMPRODUCT(giá_trị, trọng_số) / SUM(trọng_số)
SUMPRODUCT Thay Thế Cho Công Thức Mảng
Hàm SUMPRODUCT xử lý mảng, nên có thể thay thế cho công thức mảng truyền thống, giúp việc quản lý công thức dễ dàng hơn mà không cần nhấn Ctrl + Shift + Enter.
Ví dụ, đếm số ký tự trong một dải ô:
=SUMPRODUCT(LEN(dải_ô))
MOD và SUMPRODUCT Đếm Ô Chứa Số Lẻ
Kết hợp MOD và SUMPRODUCT để đếm số ô chứa số lẻ.
Công thức:
=SUMPRODUCT(–(MOD(A1:A15,2)=1))
Các Ứng Dụng Thường Gặp Của SUMPRODUCT
- Hàm kiểm tra logic: ISNUMBER/ISERROR + SEARCH: Tìm kiếm điều kiện trong vùng dữ liệu.
- Tính tổng qua các sheet với SUMIFS: Tính toán trên nhiều sheet có cấu trúc giống nhau.
- Tách chuỗi để lấy số liệu tính toán: Tách chuỗi ký tự thành các đơn vị tính toán.
Chuyển chuỗi biểu thức cộng trừ thành kết quả: Tính toán trực tiếp từ chuỗi biểu thức.
Đếm duy nhất với SUMPRODUCT: Đếm số lượng giá trị duy nhất.
- Tính giờ đi muộn, về sớm: Tính toán thời gian chấm công.
- Ứng dụng SUMPRODUCT trong chấm công: Tính ngày công, tăng ca.
- Kết hợp SUMPRODUCT và SUBTOTAL: Tính toán bỏ qua dòng/cột ẩn.
Bài viết này đã cung cấp cho bạn cái nhìn tổng quan về hàm SUMPRODUCT trong Excel. Hy vọng những kiến thức này sẽ giúp bạn áp dụng hiệu quả hàm SUMPRODUCT vào công việc.
Discussion about this post