Tải xuống sổ làm việc mẫu
Hướng dẫn này trình bày cách sử dụng Hàm SUMPRODUCT trong Excel trong Excel.
Tổng quan về hàm SUMPRODUCT
Hàm SUMPRODUCT Nhân các mảng số và tính tổng mảng kết quả.
Để sử dụng Hàm SUMPRODUCT Excel Worksheet, hãy chọn một ô và nhập:
(Chú ý cách các đầu vào công thức xuất hiện)
Hàm SUMPRODUCT Cú pháp và đầu vào:
| 1 | = SUMPRODUCT (array1, array2, array3) |
array1 - Mảng số.
Hàm SUMPRODUCT là gì?
Hàm SUMPRODUCT là một trong những hàm mạnh mẽ hơn trong Excel. Tên của nó, có thể khiến bạn tin rằng nó chỉ dành cho các phép tính toán học cơ bản, nhưng nó có thể được sử dụng cho nhiều việc khác.
Mảng
SUMPRODUCT yêu cầu đầu vào của mảng.
Vì vậy, trước tiên, chúng ta muốn hiểu "mảng" là gì? Mảng đơn giản là một nhóm các mục (ví dụ: số) được sắp xếp theo một thứ tự cụ thể, giống như một dải ô. Vì vậy, nếu bạn có các số 1, 2, 3 trong các ô A1: A3, Excel sẽ đọc đây là mảng {1,2,3}. Trên thực tế, bạn có thể nhập trực tiếp {1,2,3} vào công thức Excel và nó sẽ nhận dạng mảng.
Chúng ta sẽ nói nhiều hơn về mảng bên dưới, nhưng trước tiên hãy xem một ví dụ đơn giản.
Toán học cơ bản
Hãy xem một ví dụ cơ bản về SUMPRODUCT, sử dụng nó để tính tổng doanh số bán hàng.
Chúng tôi có bảng sản phẩm và chúng tôi muốn tính tổng doanh số bán hàng. Bạn muốn chỉ thêm một cột mới, lấy số lượng đã bán * giá và sau đó tổng hợp cột mới. Tuy nhiên, thay vào đó, bạn có thể chỉ cần sử dụng Hàm SUMPRODUCT. Hãy xem qua công thức:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Hàm sẽ tải các phạm vi số thành mảng, nhiều chúng với nhau và sau đó tổng hợp các kết quả:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
Hàm SUMPRODUCT có thể nhân tất cả các số cho chúng ta VÀ thực hiện phép tính tổng.
Bình quân gia quyền
Một trường hợp khác hữu ích khi sử dụng SUMPRODUCT là khi bạn cần tính giá trị trung bình có trọng số. Điều này thường xảy ra nhất khi giải quyết bài tập ở trường, vì vậy hãy xem xét bảng sau.
Chúng ta có thể thấy giá trị của các câu đố, bài kiểm tra và bài tập về nhà đối với điểm tổng thể, cũng như mức trung bình hiện tại cho từng mục cụ thể. Sau đó, chúng ta có thể tính điểm tổng thể bằng cách viết
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Hàm của chúng ta lại nhân từng mục trong các mảng trước khi tính tổng. Điều này diễn ra như vậy
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Nhiều cột
Một nơi khác mà chúng ta có thể sử dụng SUMPRODUCT là với nhiều cột hơn cần được nhân với nhau. Hãy xem một ví dụ mà chúng ta cần tính khối lượng của các mảnh gỗ.
Thay vì tạo cột trợ giúp để tính tổng doanh thu cho mỗi hàng, chúng ta có thể thực hiện việc này bằng một công thức duy nhất. Công thức của chúng tôi sẽ là
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Các mục đầu tiên của mỗi mảng sẽ nhân với nhau (ví dụ: 4 * 2 * 1 = 8). Sau đó, thứ 2 (4 * 2 * 2 = 16) và 3rd, v.v. Nhìn chung, điều này sẽ tạo ra một loạt các sản phẩm giống như {8, 16, 16, 32). Khi đó tổng khối lượng sẽ là tổng của mảng đó, 72.
Một tiêu chí
Được rồi, hãy thêm một lớp phức tạp khác. Chúng tôi đã thấy rằng SUMPRODUCT có thể xử lý các mảng số, nhưng nếu chúng tôi muốn kiểm tra các tiêu chí thì sao? Bạn cũng có thể tạo mảng cho các giá trị Boolean (Giá trị Boolean là các giá trị TRUE hoặc FALSE).
Ví dụ: lấy một mảng cơ bản {1, 2, 3}. Hãy tạo một mảng tương ứng cho biết nếu mỗi số lớn hơn 1. Mảng này sẽ giống như {FALSE, TRUE, TRUE}.
Điều này cực kỳ hữu ích trong các công thức, vì chúng ta có thể dễ dàng chuyển đổi TRUE / FALSE thành 1 / 0. Hãy xem một ví dụ.
Sử dụng bảng dưới đây, chúng tôi muốn tính toán "Có bao nhiêu đơn vị đã bán được là màu Đỏ?"
Chúng ta có thể làm như vậy, với công thức này:
| 1 | = SUMPRODUCT (A2: A4, - (B2: B4 = "Đỏ")) |
"Giữ lấy! Có gì với biểu tượng dấu trừ kép ở đó? ” bạn nói. Hãy nhớ cách tôi đã nói chúng ta có thể chuyển đổi từ True / False thành 1/0? Chúng tôi thực hiện điều này bằng cách buộc máy tính thực hiện một phép toán. Trong trường hợp này, chúng tôi đang nói "lấy giá trị âm rồi lấy lại giá trị phủ định". Viết ra điều đó, mảng của chúng ta sẽ thay đổi như thế này:
| 123 | {Đúng, Đúng, Sai}{-1, -1, 0}{1, 1, 0} |
Vì vậy, quay lại công thức SUMPRODUCT đầy đủ, nó sẽ tải trong các mảng của chúng ta và sau đó nhân lên, như thế này
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Lưu ý cách 3rd item trở thành 0, bởi vì bất cứ thứ gì nhân với 0 đều trở thành 0.
Nhiều tiêu chí
Chúng tôi có thể tải tới 255 mảng vào hàm của mình, vì vậy chúng tôi chắc chắn có thể tải trong nhiều tiêu chí hơn. Hãy nhìn vào bảng lớn hơn này, nơi chúng tôi đã thêm Tháng đã bán.
Nếu chúng ta muốn biết có bao nhiêu mặt hàng được bán có màu đỏ và vào tháng 2, chúng tôi có thể viết công thức của mình như
| 1 | = SUMPRODUCT (A2: A4, - (B2: B4 = "Red"), - (C2: C4 = "Feb")) |
Sau đó, máy tính sẽ đánh giá các mảng của chúng ta và nhân lên. Chúng tôi đã trình bày cách thay đổi mảng True / False thành 1/0, vì vậy tôi sẽ bỏ qua bước đó ngay bây giờ.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Chúng tôi chỉ có một hàng trong ví dụ của chúng tôi phù hợp với tất cả các tiêu chí, nhưng với dữ liệu thực, bạn có thể có nhiều hàng mà bạn cần thêm cùng nhau.
Tiêu chí phức tạp
Được rồi, cho đến thời điểm này, bạn có thể không ấn tượng vì tất cả các ví dụ của chúng tôi có thể đã được thực hiện bằng cách sử dụng các hàm khác như SUMIF hoặc COUNTIF. Bây giờ chúng ta sẽ làm một cái gì đó những chức năng khác không thể làm. Trước đây, cột Tháng của chúng tôi có tên thực tế của các tháng. Nếu thay vào đó nó có ngày tháng thì sao?
Chúng tôi không thể thực hiện SUMIF ngay bây giờ, vì SUMIF không thể xử lý các tiêu chí chúng tôi cần. Mặc dù SUMPRODUCT có thể xử lý chúng ta thao tác với mảng và thực hiện một bài kiểm tra sâu hơn. Chúng tôi đã thao túng các mảng khi chúng tôi dịch True / False thành 1/0. Chúng ta sẽ thao tác mảng này với hàm MONTH. Đây là công thức đầy đủ mà chúng tôi sẽ sử dụng
| 1 | = SUMPRODUCT (A2: A4, - (B2: B4 = "Đỏ"), - (THÁNG (C2: C4) = 2)) |
Chúng ta hãy nhìn vào 3rd mảng chặt chẽ hơn. Đầu tiên, công thức của chúng tôi sẽ trích xuất số tháng từ mỗi ngày trong C2: C4. Điều này sẽ cung cấp cho chúng tôi {1, 2, 2}. Tiếp theo, chúng ta kiểm tra xem giá trị đó có bằng 2. Bây giờ mảng của chúng ta trông giống như {False, True, True}. Chúng tôi thực hiện lại phép trừ nhân đôi và chúng tôi có {0, 1, 1}. Giờ đây, chúng ta đã quay trở lại vị trí tương tự mà chúng ta đã có trong Ví dụ 3 và công thức của chúng ta sẽ có thể cho chúng ta biết rằng đã có 50 chiếc được bán vào tháng Hai có màu đỏ.
Nhân đôi trừ với nhân
Nếu bạn đã thấy hàm SUMPRODUCT được sử dụng trước đây, bạn có thể đã thấy một ký hiệu hơi khác. Thay vì sử dụng dấu trừ kép, bạn có thể viết
| 1 | = SUMPRODUCT (A2: A4 * (B2: B4 = "Đỏ") * (THÁNG (C2: C4) = 2)) |
Công thức vẫn sẽ hoạt động theo cách tương tự, chúng tôi chỉ nói với máy tính theo cách thủ công rằng chúng tôi muốn nhân các mảng. SUMPRODUCT vẫn sẽ làm điều này, vì vậy không có thay đổi nào ở đó về cách hoạt động của toán học. Thực hiện phép toán chuyển True / False của chúng ta thành 1/0 giống nhau. Vì vậy, tại sao sự khác biệt?
Hầu hết thời gian, điều đó không quá quan trọng và nó phụ thuộc vào sở thích của người dùng. Có ít nhất một trường hợp cần nhân.
Khi bạn sử dụng SUMPRODUCT, máy tính sẽ yêu cầu tất cả các đối số (array1, array2, v.v.) có cùng kích thước. Điều này có nghĩa là chúng có cùng số hàng hoặc số cột. Tuy nhiên, bạn có thể thực hiện những gì đã biết dưới dạng tính toán mảng hai chiều với SUMPRODUCT mà chúng ta sẽ thấy trong ví dụ tiếp theo. Khi bạn làm điều đó, các mảng có kích thước khác nhau, vì vậy chúng ta cần bỏ qua kiểm tra "tất cả cùng kích thước" đó.
Hai kích thước
Tất cả các ví dụ trước đều có các mảng của chúng ta đi theo cùng một hướng. SUMPRODUCT có thể xử lý mọi thứ theo hai hướng, như chúng ta sẽ thấy trong bảng tiếp theo.
Dưới đây là bảng của chúng tôi về các đơn vị đã bán, nhưng dữ liệu được sắp xếp lại khi các danh mục ở trên cùng. Nếu chúng ta muốn biết có bao nhiêu mặt hàng có màu Đỏ và thuộc loại A, chúng ta có thể viết
| 1 | = SUMPRODUCT ((A2: A4 = "Đỏ") * (B1: C1 = "A") * B2: C4) |
Chuyện gì đang xảy ra ở đây?? Hóa ra là chúng ta sẽ nhân lên theo hai hướng khác nhau. Việc hình dung điều này khó thực hiện hơn chỉ với một câu văn, vì vậy chúng tôi có một vài hình ảnh để giúp chúng tôi. Đầu tiên, tiêu chí hàng của chúng ta (có phải là Màu đỏ không?) Sẽ nhân lên trên mỗi hàng trong mảng.
| 1 | = SUMPRODUCT ((A2: A4 = "RED") * B2: C4) |
Tiếp theo, tiêu chí cột (có phải là loại A không?) Sẽ nhân lên từng cột
| 1 | = SUMPRODUCT ((A2: A4 = "Đỏ") * (B1: C1 = "A") * B2: C4) |
Sau khi cả hai tiêu chí đó đã hoàn thành công việc của chúng, chỉ còn lại các số không phải là 5 và 10. SUMPRODUCT sẽ cung cấp cho chúng ta tổng số 15 như câu trả lời của chúng ta.
Hãy nhớ cách chúng ta đã nói về việc các mảng cần có cùng kích thước trừ khi bạn đang thực hiện hai thứ nguyên? Điều đó đã đúng một phần. Nhìn lại các mảng chúng ta đã sử dụng trong công thức của mình. Các Chiều cao của hai trong số các mảng của chúng tôi giống nhau và chiều rộng của hai trong số các mảng của chúng tôi giống nhau. Vì vậy, bạn vẫn cần đảm bảo rằng mọi thứ sẽ được sắp xếp một cách chính xác, nhưng bạn có thể làm điều đó theo các chiều khác nhau.
Hai chiều và phức tạp
Nhiều khi chúng tôi nhận được dữ liệu không có trong bố cục tốt nhất phù hợp với các công thức của chúng tôi. Chúng tôi có thể cố gắng sắp xếp lại nó theo cách thủ công hoặc chúng tôi có thể thông minh hơn với các công thức của mình. Hãy xem xét bảng sau.
Tại đây, chúng tôi có dữ liệu cho các mặt hàng và doanh số bán hàng của chúng tôi được kết hợp với nhau cho mỗi tháng. Làm thế nào để chúng ta tìm hiểu xem Bob đã bán được bao nhiêu mặt hàng trong cả năm?
Để thực hiện việc này, chúng tôi sẽ sử dụng hai chức năng bổ sung: SEARCH và ISNUMBER. Hàm TÌM KIẾM sẽ cho phép chúng ta tìm kiếm từ khóa “các mặt hàng” trong các ô tiêu đề. Đầu ra từ hàm này sẽ là một số hoặc một lỗi (nếu từ khóa không được tìm thấy). Sau đó, chúng tôi sẽ sử dụng ISNUMBER để chuyển đổi điều đó xuất thành các giá trị Boolean của chúng tôi. Công thức của chúng tôi sẽ giống như dưới đây.
Bây giờ bạn đã khá quen thuộc với mảng đầu tiên. Nó sẽ tạo ra một đầu ra như {0, 1, 0, 1}. Mảng tiêu chí tiếp theo mà chúng ta vừa nói đến. Nó sẽ tạo một số cho tất cả các ô có “Mục” trong đó và lỗi cho các ô khác {5, # N / A !, 5, # N / A!}. Sau đó ISNUMBER chuyển đổi giá trị này thành Boolean {True, False, True, False}. Sau đó, khi chúng ta nhân lên, nó sẽ chỉ giữ các giá trị từ cột đầu tiên và cột thứ ba. Sau khi tất cả các mảng nhân với nhau, các số khác 0 duy nhất mà chúng tôi sẽ có là những số được đánh dấu ở đây:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob") * (ISNUMBER (TÌM KIẾM ("Mục", B1: E1)) * B2: E5)) |
SUMPRODUCT sau đó sẽ cộng tất cả những thứ đó lại và chúng ta nhận được kết quả cuối cùng là 29.
SUMPRODUCT hoặc
Nhiều tình huống phát sinh trong đó chúng tôi muốn có thể tính tổng các giá trị nếu cột tiêu chí của chúng tôi có một giá trị HOẶC một giá trị khác. Bạn có thể thực hiện điều này trong SUMPRODUCT bằng cách thêm hai mảng tiêu chí chống lại nhau.
Trong ví dụ này, chúng tôi muốn cộng các đơn vị được bán cho cả Đỏ và Xanh lam.
Công thức của chúng tôi sẽ như thế này
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Đỏ") + (B2: B7 = "Xanh lam")) |
Hãy xem xét mảng tiêu chí Màu đỏ. Nó sẽ tạo ra một mảng giống như sau: {1, 1, 0, 0, 0, 0}. Mảng tiêu chí Xanh lam sẽ giống như {0, 0, 1, 0, 1, 0}. Khi bạn thêm chúng lại với nhau, mảng mới sẽ trông giống như {1, 1, 1, 0, 1, 0}. Chúng ta có thể thấy cách hai mảng đã kết hợp với nhau thành một mảng tiêu chí duy nhất. Sau đó, hàm sẽ nhân nó với mảng đầu tiên của chúng ta và chúng ta sẽ nhận được {100, 50, 10, 0, 75, 0}. Lưu ý rằng các giá trị cho Màu xanh lá cây đã bị xóa. Bước cuối cùng của SUMPRODUCT là cộng tất cả các số lại với nhau để đạt được giải pháp 235 của chúng tôi.
Một lời cảnh báo ở đây. Hãy cẩn thận về thời điểm các mảng tiêu chí không loại trừ lẫn nhau. Trong ví dụ của chúng tôi, các giá trị trong cột B có thể là Đỏ hoặc Xanh, nhưng chúng tôi biết rằng nó không bao giờ có thể là cả hai. Hãy xem xét nếu chúng tôi đã viết công thức này:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50) + (B2: B7 = "Blue")) |
Mục đích của chúng tôi là tìm các mặt hàng Xanh lam đã được bán hoặc có số lượng hơn 50. Tuy nhiên, các điều kiện này không phải là loại trừ, vì một hàng có thể có cả trên 50 trong cột A và có màu xanh lam. Điều này sẽ dẫn đến mảng tiêu chí đầu tiên trông giống như {1, 1, 0, 1, 1, 0}, mảng tiêu chí thứ hai là {0, 0, 1, 0, 1, 0}. Thêm chúng với nhau sẽ tạo ra {1, 1, 1, 1, 2, 0}. Bạn có thấy làm thế nào chúng tôi có một 2 trong đó bây giờ? Nếu để một mình, SUMPRODUCT sẽ tăng gấp đôi giá trị trong hàng đó, thay đổi 75 thành 150 và chúng tôi sẽ nhận được kết quả sai. Để khắc phục điều này, chúng tôi đặt một kiểm tra tiêu chí bên ngoài trên mảng của chúng tôi, như sau:
| 1 | = SUMPRODUCT (A2: A7, - ((A2: A7> = 50) + (B2: B7 = "Blue")> 0)) |
Bây giờ, sau khi hai mảng tiêu chí bên trong đã được thêm vào với nhau, chúng ta sẽ kiểm tra xem kết quả có lớn hơn 0. Điều này sẽ loại bỏ 2 mảng mà chúng ta đã có trước đó và thay vào đó chúng ta sẽ có một mảng như {1, 1, 1 , 1, 1, 0} sẽ tạo ra kết quả chính xác.
SUMPRODUCT Chính xác
Hầu hết các hàm trong Excel không phân biệt chữ hoa chữ thường, nhưng đôi khi chúng ta cần có khả năng tra cứu với phân biệt chữ hoa chữ thường. Khi kết quả mong muốn là số, chúng ta có thể thực hiện điều này bằng cách sử dụng EXACT bên trong hàm SUMPRODUCT. Hãy xem xét bảng sau:
Chúng tôi muốn tìm điểm cho Mục “ABC123”. Thông thường, hàm EXACT sẽ so sánh hai mục và trả về kết quả đầu ra Boolean cho biết liệu hai mục có chính xác như nhau. Tuy nhiên, vì chúng ta đang ở trong SUMPRODUCT, máy tính của chúng ta sẽ biết rằng chúng ta đang xử lý các mảng và sẽ có thể so sánh một mục với mỗi mục trong một mảng. Công thức của chúng ta sẽ như thế này
| 1 | = SUMPRODUCT (- EXACT ("ABC123", A2: A5), B2: B5) |
Sau đó, hàm EXACT sẽ kiểm tra từng mục trong A2: A5 để xem nó có khớp với giá trị và trường hợp hay không. Điều này sẽ tạo ra một mảng trông giống như {0, 1, 0, 0}. Khi được nhân với B2: B5, mảng sẽ trở thành {0, 2, 0, 0}. Sau khi tổng kết cuối cùng, chúng tôi nhận được giải pháp của chúng tôi là 2.
SUMPRODUCT trong Google Trang tính
Hàm SUMPRODUCT hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
Ví dụ về SUMPRODUCT trong VBA
Bạn cũng có thể sử dụng hàm SUMPRODUCT trong VBA. Loại: application.worksheet functions.sumproduct (array1, array2, array3)
Thực thi các câu lệnh VBA sau
| 1 | Phạm vi ("B10") = Application.WorksheetFunction.SumProduct (Phạm vi ("A2: A7"), Phạm vi ("B2: B7")) |
sẽ tạo ra kết quả sau
Đối với các đối số của hàm (array1, v.v.), bạn có thể nhập chúng trực tiếp vào hàm hoặc xác định các biến để sử dụng thay thế.
