Cách sử dụng chức năng SUMPRODUCT trong các mô hình dữ liệu - núm vú

Hàm SUMPRODUCT thực sự được liệt kê dưới danh mục toán và lượng giác của các hàm Excel. Vì mục đích chính của SUMPRODUCT là tính tổng sản phẩm, hầu hết mọi người không biết bạn thực sự có thể sử dụng nó để tra cứu các giá trị. Trong thực tế, bạn có thể sử dụng chức năng linh hoạt này khá hiệu quả trong hầu hết các mô hình dữ liệu.

SUMPRODUCT cơ bản

Chức năng SUMPRODUCT được thiết kế để nhân giá trị từ hai hoặc nhiều dải dữ liệu và sau đó thêm các kết quả cùng nhau để trả lại tổng của sản phẩm. Hãy xem con số sau để xem một kịch bản điển hình trong đó SUMPRODUCT hữu ích.

SUMPRODUCT, nhận được tổng doanh thu liên quan đến việc nhân pric "width =" 458 ">

Nếu không có SUMPRODUCT, nhận được tổng doanh thu liên quan đến nhân giá của đơn vị và sau đó tổng hợp kết quả

Bạn sẽ thấy một phân tích chung trong đó bạn cần doanh số bán hàng trong những năm 2011 và 2012. Như bạn thấy, để có được tổng doanh thu cho mỗi năm, trước tiên bạn phải nhân Giá theo số đơn vị để có được tổng số cho mỗi Region

Với chức năng SUMPRODUCT, bạn có thể thực hiện phân tích hai bước chỉ với một công thức. Bạn có thể thực hiện phân tích tương tự với chỉ 3!

Chức năng SUMPRODUCT cho phép bạn thực hiện phân tích tương tự với chỉ "width =" 535 "> > Cú pháp của hàm SUMPRODUCT khá đơn giản:

SUMPRODUCT (

Array1 , Array2 , …) Array

: Array thể hiện một loạt các dữ liệu. Bạn có thể sử dụng bất cứ nơi nào từ 2 đến 255 mảng trong công thức SUMPRODUCT. Các mảng được nhân với nhau rồi thêm vào. Quy tắc cứng và nhanh duy nhất bạn phải nhớ là tất cả các mảng phải có cùng một số giá trị. Nghĩa là bạn không thể sử dụng SUMPRODUCT nếu phạm vi X có 10 giá trị và phạm vi Y có 11 giá trị. Nếu không, bạn sẽ có được #VALUE! lỗi.

Sự xoắn của hàm SUMPRODUCT

Điều thú vị về chức năng SUMPRODUCT là nó có thể được sử dụng để lọc các giá trị. Hãy xem con số sau:

Chức năng SUMPRODUCT có thể được sử dụng để lọc dữ liệu dựa trên tiêu chí. "Width =" 535 ">

Chức năng SUMPRODUCT có thể được sử dụng để lọc dữ liệu dựa trên tiêu chí.

Công thức trong tế bào E12 đang kéo tổng số đơn vị cho vùng Bắc. Trong khi đó, ô E13 đang kéo các đơn vị đăng nhập cho vùng Bắc vào năm 2011.

Để hiểu cách thức hoạt động này, hãy xem công thức trong ô E12. Công thức đó đọc SUMPRODUCT ((C3: C10 = "Bắc") * (E3: E10)).

Trong Excel, TRUE đánh giá là 1 và FALSE đánh giá là 0. Mỗi giá trị trong cột C bằng North đánh giá là TRUE hoặc 1. Trường hợp giá trị không phải là Bắc, nó sẽ đánh giá FALSE hoặc 0. Phần của công thức đọc (C3: C10 = "Bắc") liệt kê thông qua mỗi giá trị trong dải C3: C10, gán 1 hoặc 0 cho mỗi giá trị. Sau đó, nội bộ, công thức SUMPRODUCT chuyển thành

Câu trả lời là 1628 vì

(1 * 751) + (0 * 483) + (0 * 789) + (0 * 932) + (1 * 877) + (0 * 162) + (0 * 258) + (0 * 517)

bằng 1628.

Sử dụng các công thức SUMPRODUCT trong một mô hình dữ liệu

Như mọi khi trong Excel, bạn không cần mã số cứng các tiêu chí trong công thức của mình. Thay vì sử dụng "Bắc" rõ ràng trong công thức SUMPRODUCT, bạn có thể tham khảo một ô có chứa giá trị bộ lọc. Bạn có thể tưởng tượng rằng ô A3 chứa từ Bắc, trong trường hợp đó bạn có thể sử dụng (C3: C10 = A3) thay vì (C3: C10 = "Bắc"). Bằng cách này, bạn có thể tự động thay đổi tiêu chí lọc của bạn, và công thức của bạn tiếp tục.

Hình dưới đây thể hiện cách bạn có thể sử dụng khái niệm này để kéo dữ liệu vào một bảng dàn xếp dựa trên nhiều tiêu chí. Lưu ý rằng mỗi công thức SUMPRODUCT được hiển thị ở đây đề cập đến các ô B3 và C3 để lọc trên Tài khoản và Dòng sản phẩm. Một lần nữa, bạn có thể thêm danh sách thả xuống xác nhận dữ liệu vào các ô B3 và C3, cho phép bạn dễ dàng thay đổi các tiêu chí.

Chức năng SUMPRODUCT có thể được sử dụng để kéo các số tóm tắt từ d "width =" 535 ">

Chức năng SUMPRODUCT có thể được sử dụng để kéo số tóm tắt từ lớp dữ liệu vào các bảng dàn.