Khi bạn nghe đến cái tên SUM PRODUCT lần đầu tiên, bạn có thể nghĩ rằng đó là một công thức vô dụng – công thức tính tổng thông thường. Trên thực tế, định nghĩa này không chỉ ra một phần nhỏ chức năng của hàm SUMPRODUCT.
Bản chất của chức năng hút nước là gì? Trên thực tế, hàm SUM PRODUCT là một hàm thực sự sở hữu nhiều tính năng vượt trội so với các hàm trong Excel. Bởi vì khả năng độc đáo của nó là có thể xử lý các mảng một cách tinh vi và cực kỳ thông minh, hàm SUM PRODUCT ngược lại rất hữu ích, nếu không cần thiết, khi so sánh dữ liệu trong hai hoặc nhiều phạm vi và tính toán dữ liệu trong các bài toán có nhiều điều kiện. Các ví dụ trong bài viết sau đây sẽ cho bạn thấy tất cả các khả năng rất quan trọng của hàm SUMPRODUCT và tính hữu ích của nó sẽ sớm trở nên cực kỳ rõ ràng …
I. Hàm sumproduct là gì?
1. Hàm sumproduct là gì?
Hàm SUMPRODUCT trong Excel là hàm dùng để tính tổng tích các phần tử số trong một mảng hoặc các mảng khác nhau, không giống như hàm PRODUCT, hàm này chỉ được sử dụng để tính tích các giá trị. Bên cạnh đó, hàm SUMPRODUCT để tính tổng nhiều điều kiện cũng được nhiều người ưa chuộng
Dãy hàm trong Excel về cơ bản sẽ là một dãy số có giá trị liên tiếp nhau mà người dùng đã khoanh vùng trên bảng dữ liệu. Hàm SUMPRODUCT có sẵn trên tất cả các phiên bản của các hàm Excel cơ bản, với cách sử dụng khá giống nhau. Cách sử dụng hàm SUMPRODUCT trong Excel chi tiết sẽ được 123job.vn trình bày trong bài viết dưới đây.
2. Cú pháp
Hàm SUMPRODUCT có cú pháp cụ thể là = SUMPRODUCT (array1, [array2], [array3]…).
Trong đó:
Array1: đối số mảng đầu tiên mà người dùng đang cố gắng sử dụng để nhân các số rồi tính tổng. Đối số này là một trường bắt buộc.
Array2, Array3: là một đối số mảng tùy chọn có hoặc không, cụ thể trong phạm vi từ 2 đến 255.
Các đối số tồn tại trong mỗi mảng phải có cùng kích thước, cùng số phần tử trong đó. Trong trường hợp các phần tử trong các mảng tương ứng không được hiển thị dưới dạng số, hàm SUMPRODUCT cơ bản của Excel sẽ mặc định bằng 0.
3. Ví dụ
Qua ví dụ sau, chúng ta sẽ hiểu rõ hơn về hàm SUMPRODUCT là gì. Chúng ta sẽ tính doanh thu bán sách của 1 cửa hàng trong bảng ví dụ cụ thể bên dưới. Trong đó, bảng đã đưa ra số lượng cho từng loại sách và giá thành của từng loại sách đó tính theo đơn vị là hàng nghìn đồng.
Bảng số lượng nhất định
Chúng ta sẽ nhập công thức tính cụ thể của bài tập là
= SUMPRODUCT (C2: C4, D2: D4) và sau đó nhấn Enter để khởi chạy công thức.
Khởi chạy công thức để tính toán các hàm trong excel
Ngay sau đó người dùng sẽ có kết quả hiển thị doanh thu của cửa hàng khi bán hết tổng số sách đã cho ở trên.
Kết quả cho thấy doanh thu
II. Cách sử dụng hàm sumproduct trong excel cơ bản
1. Cách sử dụng cơ bản của hàm sumproduct là gì
Để biết tổng quan về cách hoạt động của hàm SUMPRODUCT cơ bản trong Excel, hãy xem qua ví dụ sau.
Giả sử bạn có số đếm trong các ô A2: A4 và thông qua đó bạn muốn tính tổng. Nếu bạn đang giải toán ở trường, bạn sẽ nhân số lượng với giá thành của mỗi sản phẩm, rồi cộng chúng lại. Trong Microsoft Excel, bạn có thể nhận được kết quả nhanh chóng chỉ với một công thức SUMPRODUCT:
= SUMPRODUCT (A2: A4, B2: B4)
Kết quả nhanh chóng chỉ với một SUMPRODUCT. công thức
Dưới đây là bảng phân tích về những gì đang diễn ra trong Excel về mặt toán học:
Công thức hàm sumproduct tính tổng nhiều điều kiện chọn số đầu tiên trong mảng đầu tiên, sau đó nhân nó với số đầu tiên trong mảng thứ hai, rồi tiếp tục lấy số thứ hai trong mảng đầu tiên bằng cách nhân số thứ hai trong mảng thứ hai. và sau đó tiếp tục chu kỳ như vậy.
Khi tất cả các phần tử của mảng đã được nhân trong hàm sumproduct tính tổng nhiều điều kiện, công thức sẽ tính tổng các sản phẩm và trả về kết quả là tổng.
Nói cách khác, công thức hàm SUMPRODUCT tổng hợp nhiều điều kiện đại diện cho công thức toán học sau:
= A2 * B2 + A3 * B3 + A4 * B4
2. Cách sử dụng hàm sumproduct với nhiều điều kiện
Thông thường trong Microsoft Excel cơ bản, luôn có nhiều cách để hoàn thành công việc. Nhưng khi so sánh hai hoặc nhiều mảng, đặc biệt với nhiều điều kiện, nếu không phải là duy nhất, thì hàm SUMPRODUCT tổng hợp nhiều điều kiện là hiệu quả nhất.
Giả sử bạn có một danh sách các mặt hàng được hiển thị trong cột A, doanh số dự kiến trong cột B và doanh số bán hàng thực tế dưới dạng dữ liệu trong cột C. Mục tiêu của bạn là tìm hiểu xem có bao nhiêu đã bán được bao nhiêu đơn hàng đã được thực hiện ít hơn dự kiến. Đối với trường hợp này, hãy sử dụng lần lượt một trong các biến của công thức SUMPRODUCT:
= SUMPRODUCT (- (C2: C10
đẹp
= SUMPRODUCT ((C2: C10
Trong đó C2: C10 đại diện cho doanh số bán hàng thực tế và B2: B10 đại diện cho doanh số bán hàng dự kiến.
Hàm sumproduct là gì?
Nhưng nếu bạn trong trường hợp này có nhiều hơn một điều kiện thì sao? Giả sử bạn muốn đếm số lượng Táo bán ra ít hơn dự kiến. Giải pháp là thêm một điều kiện cụ thể nữa vào công thức SUMPRODUCT tổng hợp nhiều điều kiện:
= SUMPRODUCT (- (C2: C10
Hoặc, bạn có thể sử dụng một cú pháp khác như sau:
= SUMPRODUCT ((C2: C10
Sử dụng cú pháp khác
3. Tính tổng, các ô trung bình có điều kiện với nhiều tiêu chí
Trong Excel 2003 và các phiên bản trước đó, hàm IFS không tồn tại, một trong những cách sử dụng phổ biến nhất của hàm SUMPRODUCT là tính tổng hoặc đếm các ô có nhiều tiêu chí có điều kiện. Bắt đầu từ phiên bản 2007 của Excel, Microsoft đã chính thức giới thiệu một loạt các hàm được thiết kế đặc biệt cho các tác vụ này – hàm SUMIFS, cùng với hàm COUNTIFS và hàm AVERAGEIFS.
Nhưng ngay cả trong các phiên bản hiện đại hơn của các hàm Excel cơ bản, công thức SUMPRODUCT có thể là một công thức thay thế, ví dụ, trong tính tổng và đếm có điều kiện của các ô. chứa hàm OR logic. Dưới đây bạn sẽ thấy một vài ví dụ về công thức chứng minh khả năng đặc biệt này.
4. Cách sử dụng hàm sumproduct nâng cao trong excel
Lồng một hàng khác trong hàm SUMPRODUCT để thay đổi giá trị của mảng được tính
Tích hợp các hàng hóa khác
Ở ví dụ trên, để có thể thực hiện tổng các mặt hàng đã bán trong tháng 3 nhưng dữ liệu hiển thị chỉ có cột Ngày bán, cột Tháng không có. Để tính toán thông tin này, chúng ta phải tách Tháng khỏi cột Ngày.
Theo cách tính toán và áp dụng các hàm thông thường trong Excel, chúng ta phải thực hiện các thao tác sau:
Sử dụng hàm IF để xem tháng của mỗi lần bán hàng có phải là số 3. Nếu đúng, nó sẽ lấy số lượng đã cho để nhân với đơn giá, ngược lại, kết quả cuối cùng sẽ là 0.
Sau đó, sử dụng hàm SUM trong Excel để tổng hợp kết quả thu được từ hàm IF trong mỗi hàng
Làm như vậy thực sự sẽ tốn rất nhiều công thức và thời gian đúng không? Nếu lúc đó chúng ta sử dụng hàm SUMPRODUCT thì chúng ta chỉ cần áp dụng 1 hàm là có ngay kết quả, trong đó xét đến 3 mảng:
Mảng Ngày bán: B2: B5 là dãy ô sẽ kết hợp hàm MONTH cho mảng này để có thể trả về giá trị là số tháng, từ đó so sánh giá trị này với số 3.
Số lượng mảng: C2: C5
Đơn giá mảng: D2: D5
Nhân 3 mảng này với nhau, ta được kết quả
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 đưa ra ở trên, chúng ta có yêu cầu xác định đơn giá cụ thể của một mặt hàng bao gồm 2 điều kiện: Mã mặt hàng và Mã công ty.
Bảng chứa thông tin cụ thể cần tìm (Bảng đơn giá) là bảng được xác định theo 2 chiều:
Chiều dọc của bảng là thông tin mã Công ty
Chiều rộng của bảng là thông tin mã hàng
Giao điểm của các hàng và cột trong bảng này là đơn giá cụ thể được tìm thấy cho sản phẩm.
Sau đó, chúng tôi sử dụng hàm SUMPRODUCT trong Excel như sau:
G3 = SUMPRODUCT (($ B $ 14: $ B $ 17 = C3) * ($ C $ 13: $ F $ 13 = B3) * $ C $ 14: $ F $ 17)
Kiểm tra xem mảng Mã công ty (B14: B17) có chứa mã công ty cụ thể trong ô C3 hay không
Xem xét liệu mảng Mã mặt hàng (C13: F13) có hiển thị thông tin có chứa mã mặt hàng trong ô B3 hay không
Nếu nó chứa kết quả đúng ở cả hai mảng trên thì đơn giá trong khoảng C14: F17 sẽ được lấy theo hàng và cột chứa các mã đó.
III. Tổng kết
Như vậy qua một số ví dụ trên chúng ta đã có thể biết được hàm sumproduct là gì, cách sử dụng hàm sumproduct trong excel và hàm sumproduct tính tổng nhiều điều kiện… Hi vọng những kiến thức này sẽ giúp ích nhiều cho các bạn. cho các bạn trong công việc của nhân viên văn phòng!
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.
Comments