Khi bạn nghe tới cái tên hàm SUMPRODUCT lần đầu tiên, nó có vẻ như chỉ là một công thức vô ích – công thức cung cấp cách tính tổng trong Excel thông thường của các tích số. Nhưng định nghĩa này thực tế không cho thấy thậm chí là chỉ một phần nhỏ tính năng hữu ích của hàm SUMPRODUCT. Trong bài hướng dẫn dưới đây, 123job.vn sẽ giải thích cách sử dụng cơ bản và nâng cao của hàm SUMPRODUCT thế nào trong Excel. Bạn sẽ thấy một vài ví dụ cụ thể về công thức dùng để so sánh mảng, tính tổng có điều kiện, cùng với tính năng đếm số ô có nhiều điều kiện, và hơn thế nữa.
I. Sử dụng hàm SUMPRODUCT tính tổng nhiều điều kiện hiệu quả trong Excel Link
1. Sơ lược về hàm SUMPRODUCT trong Excel
Hàm SUMPRODUCT cung cấp cách tính tổng trong Excel là hàm tính tích lần lượt các phạm vi hoặc mảng với nhau và trả về tổng số sản phẩm. Điều này về cơ bản nghe có vẻ khá nhàm chán, nhưng hàm tính tổng Excel này có những chức năng cực kỳ linh hoạt và tất nhiên có thể được sử dụng để đếm và tính tổng linh hoạt hơn. Trong thực tế, hàm SUMPRODUCT cung cấp cách tính tổng trong Excel được dùng khá nhiều và cực kỳ hữu ích.
2. Công thức tính của hàm SUMPRODUCT
Về mặt kỹ thuật, cách tính tổng trong Excel bằng hàm này sẽ là quá trình thực hiện nhân các số trong các mảng đã chỉ định và trả về tổng của các sản phẩm đó.
Cú pháp của hàm SUMPRODUCT tính tổng trong Excel rất đơn giản và dễ hiểu:
= SUMPRODUCT ( array1, [ array2 ], [ array3 ], … )Trong đó :
- array1: là mảng đầu tiên bắt buộc phải có trong cách tính tổng trong Excel nếu muốn nhân các thành phần của nó rồi cộng tổng lại.
- [array2], [array3] (Mảng 1, mảng 2, v.v.) là các phạm vi ô tính tổng Excel hoặc mảng liên tục tiếp theo có các phần tử mà bạn muốn nhân, sau đó cộng tổng vào.
- Số lượng mảng tối thiểu với cách tính tổng trong Excel là 1. Trong trường hợp này, công thức hàm SUMPRODUCT chỉ đơn giản là cộng tất cả các phần tử tương ứng trong mảng và trả về tổng.
- Số lượng mảng tính tổng Excel tối đa là 255 trong Excel 2016, Excel 2013, Excel 2010 và Excel 2007 và chỉ có 30 trong các phiên bản Excel trước đó.
- Mặc dù cách tính tổng trong Excel với hàm SUMPRODUCT hoạt động với các mảng, nhưng thực tế nó lại không yêu cầu sử dụng phím tắt mảng (Ctrl + Shift + Enter). Bạn chỉ cần sử dụng một công thức SUMPRODUCT theo cách thông thường bằng cách nhấn vào phím Enter.
Lưu ý :
- Tất cả các mảng tính tổng Excel trong công thức hàm SUMPRODUCT phải có cùng số lượng hàng và cột, nếu không, bạn sẽ lập tức nhận được #VALUE! lỗi.
- Nếu bất kỳ đối số mảng nào tương ứng chứa các giá trị không phải là số, chúng sẽ được coi là số không đối với cách tính tổng trong Excel.
- Nếu một mảng tính tổng Excel là một bài kiểm tra logic, nó sẽ dẫn đến các giá trị TRUE và FALSE. Trong hầu hết các trường hợp cách tính tổng trong Excel, bạn cần chuyển đổi giá trị của chúng thành 1 và 0 bằng cách sử dụng ký tự toán tử đơn nguyên kép (-).
- Tổng hợp theo cách tính tổng trong Excel không hỗ trợ các ký tự đại diện.
3. Ví dụ cách sử dụng hàm SUMPRODUCT
Giả sử bạn có số lượng được cho trong các ô A2: A4, giá trong các ô B2: B4 và bạn muốn tìm hiểu tổng số bằng cách tính tổng trong Excel. Nếu bạn đang làm bài kiểm tra toán ở trường, bạn sẽ thực hiện tính tích số lượng với giá cho mỗi mục, sau đó trực tiếp cộng các tổng phụ. Tuy nhiên thì trong Microsoft Excel, bạn có thể nhận kết quả chính xác chỉ bằng một công thức hàm SUMPRODUCT duy nhất:
= SUMPRODUCT ( A2 : A4, B2 : B4 )
Ví dụ cách sử dụng hàm SUMPRODUCT
4. Cách tính tổng trong Excel sử dụng hàm SUMPRODUCT
Trường hợp 1: Sử dụng hàm trong Excel với một tiêu chí duy nhất
Ví dụ: Tập dữ liệu được cho sẵn dưới đây chứa 6 tên trong các hàng và 12 tháng trong các cột. Hãy tưởng tượng rằng sếp của bạn đang yêu cầu bạn cộng tất cả các số cho tháng Sáu. Bắt đầu công thức cách tính tổn trong Excel của bạn bằng cách chọn phạm vi dữ liệu (không có tên và tháng) và thực hiện nhân nó với mảng chứa các dữ liệu tháng bằng 6. Các bước thực hiện cách tính tổng trong Excel:
Bạn nhập vào công thức := SUMPRODUCT ( array1, [ array2 ], [ array3 ], … )
Trường hợp 1
Trong đó :
- Mảng 1 của công thức cách tính tổn trong Excel cộng tất cả các số trong lựa chọn C9: N14.
- Array2 là trường kiểm tra từng số tháng xem nó có bằng 6. Kết quả của hàm này sẽ trả về là TRUE (1) hoặc FALSE (0). Bạn có thể thấy kết quả tương ứng của hàng này trong hàng 16.
- Chỉ với cột H trong trang tính cho kết quả là TRUE, vì vậy kết quả là 1.
- Để nhân các trường Array1 với Array2 có nghĩa là nhân các số thực với 0 hoặc 1.
- Trong ví dụ này, công thức Excel của chúng ta đã nhân tất cả các số trong cột C bằng 0 và tiếp đến tất cả các số từ cột H bằng 1.
- Tất cả các giá trị theo đó được nhân với 0 cộng với không.
- Các số duy nhất còn lại theo cách tính tổng trong Excel được nhân với 1, trong trường hợp này là tháng 6.
Trường hợp 2: Sử dụng cách tính tổng trong Excel bằng hàm SUMPRODUCT làm tiêu chí cho các cột
Hãy liên tục ví dụ trên bằng cách thực thi thêm các tiêu chuẩn khác. Sếp của bạn giờ đây nhu yếu phải biết doanh thu bán hàng trong quý 2 là bao nhiêu, vì thế tất cả chúng ta cần dùng các tài liệu từ tháng thứ 4 trở lên và gồm có cả tháng 6 .
Trường hợp 2
Mô tả :
Công thức tính tổng Excel chúng ta sử dụng là:
= SUMPRODUCT ( ( C9 : N14 ) * ( C8 : N * > = 4 ) * ( C8 : N8Có 3 tháng tài liệu thuộc về Q2. Lưu ý rằng bạn không cần ba phần đối số để chỉ định mỗi tháng. Thay vào đó, bạn hoàn toàn có thể sử dụng duy nhất một công thức để bộc lộ trong đó các tháng lớn hơn hoặc bằng 4 ( > = 4 ) và nhỏ hơn hoặc bằng 6 (Thường thì bạn sẽ muốn làm cho công thức này là linh động và bao quát nhất hoàn toàn có thể. Không phải ai cũng cảm thấy tự do khi biến hóa công thức phức tạp. Tuy nhiên, hầu hết tất cả chúng ta đều trọn vẹn ổn với việc kiểm soát và điều chỉnh một số ít duy nhất trong đối số. Thay vì mã hóa cố định và thắt chặt các tháng để thêm, bạn hoàn toàn có thể tìm hiểu thêm các ô chỉ định tương ứng của các giá trị này. Các ô này thường được gọi chung với cái tên là tham số .
Công thức tính tổng Excel
Để cách tính tổng trong Excel đạt được điều đó, hãy thêm tháng số lượng giới hạn là giá trị thấp hơn trong ô J4 và số lượng giới hạn cao hơn trong ô J5. Bây giờ, bạn hoàn toàn có thể đổi khác công thức để phản ánh điều này. Thay vì tìm hiểu thêm số 4, hãy viết vào công thức là ô J4. Thay số 6 tương ứng bằng ô J5 .
Bạn sẽ được công thức cách tính tổng trong Excel cuối cùng sẽ là:
= SUMPRODUCT ( ( C9 : N14 ) * ( C8 : N8 > = J14 ) * ( C8 : N8
II. Hướng dẫn cách dùng hàm SUMPRODUCT để đếm theo nhiều điều kiện Link
1. Nguyên tắc đếm của hàm SUMPRODUCT
Cách tính tổng trong Excel bằng hàm SUMPRODUCT là một hàm xử lý dữ liệu dạng mảng. Cấu trúc của hàm tính tổng Excel SUMPRODUCT đơn giản chỉ là các mảng:
= SUMPRODUCT ( array1, [ array2 ], [ array3 ], … )
Nếu chúng ta chỉ nhìn vào cấu trúc trên thì bạn có hình dung được thực sự hàm SUMPRODUCT có sức mạnh thế nào không? Sức mạnh của hàm này nằm ở chữ “”Mảng-Array”, bởi vì:
- Phạm vi của mảng Excel này rất linh hoạt, có thể bao gồm nhiều vùng dữ liệu tương ứng được kết nối lại hoặc một phần cụ thể trong mỗi phần tử của 1 vùng dữ liệu. Việc tính toán bằng cách tính tổng trong Excel theo mảng gần như không gặp giới hạn gì
- Bản chất của việc đếm này là mỗi phần tử trong 1 vùng nếu được thỏa mãn điều kiện thì được tính là 1. Kết quả cần đếm chính là tổng của các giá trị thỏa mãn điều kiện được cho đó. Như vậy, trong việc đếm theo điều kiện cho trước có thể hiểu gần giống tính tổng các giá trị thỏa mãn điều kiện trong Excel, chỉ khác là mỗi giá trị được cho là thỏa mãn này không phụ thuộc 1 giá trị nào mà chỉ có giá trị là 1.
2. Ứng dụng hàm SUMPRODUCT thực hiện đếm theo nhiều điều kiện
Đếm đúng chuẩn số lần bán của ca 1 trong tháng 6 với mẫu sản phẩm TáoTrong nhu yếu này, tất cả chúng ta thấy bao hàm tới 3 điều kiện kèm theo : Ca1, Tháng 6 và Mặt hàng “ Táo ”
Khi tăng thêm điều kiện cần đáp ứng cho đề bài thì chúng ta chỉ cần tăng thêm các thành phần trong hàm SUMPRODUCT như sau:
= SUMPRODUCT ( ( $ B USD 2 : USD B USD 18 = 1 ) * ( MONTH ( $ A $ 2 : USD A $ 18 ) = 6 ) * ( USD C USD 2 : USD C USD 18 = ” Táo ” ) * 1 )Kết quả :
Kết quả
Như vậy việc thêm điều kiện đối với cách tính tổn trong Excel thực ra chỉ làm hàm SUMPRODUCT dài hơn, còn ý nghĩa và độ khó thực sự không hề tăng lên.
III. Hướng dẫn sử dụng hàm SUMPRODUCT nâng cao trong Excel
1. Lồng ghép hàm khác trong hàm SUMPRODUCT để thay đổi giá trị của mảng cần tính
Lồng ghép hàm khác trong hàm SUMPRODUCT để thay đổi giá trị của mảng cần tính
Trong ví dụ trên, để hoàn toàn có thể tính tổng Excel dữ liệu tiền của những mẫu sản phẩm bán được trong tháng 3, nhưng tài liệu tương ứng chỉ có cột Ngày bán, không có sẵn cột Tháng. Để hoàn toàn có thể tính được, tất cả chúng ta phải tách được phần tài liệu Tháng trong cột Ngày .
Ở cách tính tổng trong Excel thông thường, chúng ta phải làm như sau:
- Dùng hàm IF trong Excel để xét xem tháng của từng ngày bán trong đây có phải là 3 không. Nếu đúng thì sẽ lấy số lượng của hàng hóa nhân đơn giá, còn không thì hàm sẽ trả kết quả là số 0
- Sau đó dùng hàm SUM trong Excel để cộng tổng lại từng các kết quả thu được từ hàm IF trên từng dòng
Cách làm như vậy sẽ khiến bạn tốn nhiều công thức và thời gian phải không? Nếu dùng cách tính tổng trong Excel với hàm SUMPRODUCT trong Excel thì chúng ta chỉ cần 1 hàm là ra ngay kết quả, trong đó xét 3 mảng:
- Mảng Ngày bán: B2:B5 sẽ kết hợp hàm MONTH trong Excel cho mảng này để có thể đưa về giá trị số tháng, từ đó hàm sẽ so sánh với số 3
- Mảng dữ liệu Số lượng: C2:C5
- Mảng dữ liệu Đơn giá: D2:D5
- Nhân 3 mảng này lại với nhau, ta sẽ lập tức thu được kết quả
2. Hàm SUMPRODUCT tính toán cho bảng dữ liệu 2 chiều
Hàm SUMPRODUCT tính toán cho bảng dữ liệu 2 chiều
Trong bảng tính cho trước trên, ta có nhu yếu triển khai xác lập đơn giá của 1 loại sản phẩm trong đó gồm 2 điều kiện kèm theo : Mã hàng và Mã công ty .Bảng chứa thông tin mà tất cả chúng ta cần tìm ( Bảng đơn giá ) là phần bảng 2 chiều :
- Chiều dọc là dữ liệu thông tin mã Công ty
- Chiều ngang là dữ liệu thông tin mã hàng
- Giao điểm của các dòng, các cột trong bảng tính Excel này chính là đơn giá cần tìm của sản phẩm.
Khi đó chúng ta sử dụng cách tính tổng trong Excel cho hàm SUMPRODUCT với công thức như sau:
G3 = SUMPRODUCT ( ( USD B USD 14 : USD B USD 17 = C3 ) * ( USD C USD 13 : USD F USD 13 = B3 ) * USD C USD 14 : USD F $ 17 )
- Xét mảng Mã công ty (B14:B17) có chứa mã công ty tương ứng tại ô C3 không
- Xét mảng Mã hàng (C13:F13) có chứa mã hàng cần làm đối chiếu tính tổng Excel tại ô B3 không
- Nếu chứa kết quả đúng ở cả 2 mảng trên, thì hàm Excel trên sẽ lấy tương ứng theo đơn giá trong vùng C14:F17 theo dòng và cột cụ thể chứa các mã đó.
IV. So sánh hàm COUNTIFS với SUMPRODUCT trong việc đếm theo điều kiện
1. Dùng hàm COUNTIFS
Ưu điểm :
- Các thành phần trong hàm Excel này được phân biệt bởi dấu phẩy nên rất rõ ràng, dễ sửa, thậm chí là dễ kiểm tra công thức.
- Khi thực hiện tính tổng trong Excel, hoặc các tính toán khác trên dữ liệu lớn và nhiều công thức thì hàm này sẽ tính nhanh hơn so với hàm Sumproduct.
Nhược điểm : Khi phải tính với 1 thành phần đơn cử được cho trong mảng ( ví dụ như tính giá trị Tháng ở cột Ngày ) thì tài liệu sẽ không tính trực tiếp được mà phải trải qua giá trị tương ứng của ngày đầu tháng và ngày cuối tháng đó ( hoặc hoàn toàn có thể phải tạo cột phụ để tách giá trị tháng ) .
2. Dùng hàm SUMPRODUCT
Ưu điểm :
- Viết dữ liệu nhanh và gọn hơn hơn so với hàm COUNTIFS
- Thực hiện cách tính tổng trong Excel được dễ dàng cho đối tượng mảng, xét 1 phần dữ liệu tương ứng trong mảng
Nhược điểm:
- Phải hiểu rõ được bản chất của dữ liệu tạo ra bởi từng thành phần trong mảng thì cách tính tổng trong Excel mới ra kết quả đúng
- Tính toán cách tính tổng trong Excel trên dữ liệu lớn và nhiều công thức thì hàm này sẽ chậm hơn so với COUNTIFS
- Nếu trong vùng dữ liệu tính tổng Excel có lỗi bất kỳ tại vị trí nào thì kết quả sẽ ra lỗi, trong khi hàm COUNTIFS khi đó sẽ bỏ qua lỗi và tính vẫn đúng
Dùng hàm SUMPRODUCT
Như vậy tất cả chúng ta hoàn toàn có thể rút ra Kết luận như sau :
- Trong trường hợp không phải làm việc với đối tượng mảng thì chúng ta nên dùng hàm COUNTIFS
- Không nên dùng hàm SUMPRODUCT để tính toán quá nhiều, bởi chỉ 1 lỗi nhỏ trong bảng dữ liệu cho trước cũng có thể khiến công thức báo lỗi. Chỉ nên dùng hàm này trong trường hợp nhỏ, lẻ hoặc khi các yêu cầu cho không thể dùng được hàm COUNTIFS.
V. Kết luận
Khi đã hiểu rõ phương pháp và cách vận dụng hàm SUMPRODUCT vào cách tính tổng trong Excel theo nhiều điều kiện thì chúng ta sẽ thấy tính ứng dụng của việc này rất cao trong thực tế. Đồng thời, chúng ta đã biết được cách kết hợp hợp lý các hàm khác bên trong hàm SUMPRODUCT, cùng với cách sử dụng hàm SUMPRODUCT trong Excel để tính toán trên những bảng tính 2 chiều. Hy vọng những kiến thức đầy đủ này sẽ giúp ích cho các bạn trong công việc.
Source: https://thiennhuong.com
Category: Thủ Thuật
