SUMPRODUCT - Tính tổng tích các mảng dữ liệu
“Tổng – Tích” đó chính là sự kết hợp của hai phép tính cộng – nhân thành phần tương ứng của các mảng với nhau thì ta sẽ được giá trị cần tính.
Nội dung bài viết [Hiển thị]
1. Để làm gì?
- Đếm dữ liệu thỏa mãn một hoặc nhiều điều kiện.
- Tính tổng thỏa mãn một hoặc nhiều điều kiện.
2. Dùng như thế nào?
2.1. Ví dụ minh họa
Về hàm Sumproduct chúng ta sẽ đi thực hành hai vấn đề được ứng dụng nhiều nhất trong công việc hằng ngày của người kỹ sư. Đó chính là đếm với nhiều điều kiện và tính tổng mảng với một hoặc nhiều điều kiện. Sử dụng hàm này các bạn sẽ thấy tiết kiện được rất nhiều thời gian trình bày và với những người chưa quen cách tổ chức dữ liệu thì Sumproduct ứng dụng tính toán khá tiện lợi.
Tính tổng tích các mảng
Đề bài:
- Dữ liệu: Bảng tổ hợp cọc theo thực tế thi công.
- Yêu cầu: Tính chiều dài Cọc thường và Cọc Bitum sau khi đã tổ hợp xong (Tính cho từng cọc).
Hướng dẫn:
Phân tích yêu cầu:
- Cọc thường và Cọc Bitum có nhiều đoạn với chiều dài khác nhau.
- Số lượng các đoạn của từng cọc là khác nhau.
- Việc tính chiều dài hai loại cọc trên là việc nhân số lượng đoạn với chiều dài từng đoạn rồi cộng chúng lại để được chiều dài từng cọc.
Giải quyết yêu cầu:
Thấy rằng ở bảng dữ liệu trên phân ra làm hai vùng chính đó là: số đoạn và chiều dài mỗi đoạn. Vậy, khi ta lấy Chiều dài/đoạn x Số lượng từng đoạn là sẽ ra kết quả cần tính.
Cách thông thường ta tính Chiều dài = 5x1+8x1+12x1
Dùng hàm Sumproduct: tại ô O8 gõ công thức tính tổng tích của hai vùng dữ liệu (Chiều dài cọc/đoạn và Số lượng từng đoạn) =SUMPRODUCT(D7:J7;D8:J8)
Với cấu trúc hàm trên ta được kết quả với quy luật tính toán như sau: (5x1) + (8x1) + (12x1) = 25, những ô nào không có dữ liệu hoặc dữ liệu không phải dạng số thì hàm sẽ trả về Kết quả = 0.
Làm tương tư như trên với một vài thao tác khóa địa chỉ ô, khóa hàng và khóa cột thì sẽ được kết quả cuối cùng sau:
Kết luận: Việc sử dụng hàm Sumproduct giúp bảng tính của bạn:
- Tránh trường hợp sử dụng công thức dài và phức tạp để ra được kết quả.
- Giúp bạn dễ dàng làm chủ bảng tính trong quá trình chỉnh sửa dữ liệu.
- Bạn có thể sử dụng tối đa tính năng đồng bộ hóa công thức để tính cho những trường hợp tương tự.
2.2. Cấu trúc
Lưu ý:
- Dữ liệu dạng số học – Tính toán bình thường.
- Dữ liệu dạng văn bản – Hàm sẽ nhận giá trị tại ô chứa văn bản bằng 0 (Trường hợp tính toán bình thường).
- Dữ liệu dạng ngày tháng, thời gian – Hàm sẽ nhận là số học (bản chất) chứ không phải dạng hiển thị.
- Hàm với vai trò kiểm tra điều kiện – Hàm sẽ kiểm tra để trả về Kết quả 1/0 (Đúng/Sai) với những mảng dữ liệu được kiểm tra để tính toán như dạng số học.
- Dữ liệu giữa các mảng phải bố trí cùng dạng hàng hoặc dạng cột và có độ lớn tương đương nhau.
2.3. Khái niệm
Ở ví dụ trên bạn đã thấy được để tính chiều dài hai Cọc thường và Cọc bitum với cách thông thường bạn sẽ phải dùng hai phép tính cộng và nhân. Nhưng với hàm Sumproduct bạn có thể rút ngắn được những phép tính không cần thiết để tính toán ngay được kết quả với tổng – tích các mảng.
Vậy Sumproduct là một hàm Excel cho phép nhân các số trong các mảng cụ thể rồi cộng các kết quả của các tích này lại với nhau. Nó được áp dụng trong việc tính toán mảng dữ liệu với số học thông thường và với mảng dữ liệu có điều kiện.
3. Cần lưu ý gì không?
3.1. Về mảng
- Bạn có thể dùng 255 mảng đối với Excel 2007 trở đi và 30 mảng đối với Excel 2003.
- Độ lớn các mảng (array1; array2 …) bạn sử dụng phải giống nhau. Ngược lại Sumproduct sẽ trả về kết quả lỗi #VALUE.
3.2. Về điều kiện và giá trị trả về
- Việc kiểm tra điều kiện bằng hàm Sumproduct thì bạn phải sử dụng hai dấu trừ (--) trước từng mảng dữ liệu muốn kiểm tra.
- Điều kiện trong từng mảng với dữ liệu được thêm vào để so sánh thì phải được để trong dấu ngoặc kép (“”).
- Kết quả của việc kiểm tra điều kiện bằng hàm Sumproduct được trả về dạng số học là 1 hoặc 0 tức là điều kiện Đúng (True) thì trả về giá trị tại ô đó bằng 1, ngược lại nếu điều kiện Sai (False) thì trả về kết quả bằng 0.
4. Cần lưu ý gì không?
4.1. Ví dụ 1: Hàm Sumproduct với tổng tích thông thường.
Đề bài:
Dữ liệu: Bảng tổng hợp vật tư nhập về công trường.
Yêu cầu: Tính tổng giá trị vật tư nhập về theo kế hoạch dự kiến và thực tế.
Hướng dẫn:
Phân tích yêu cầu:
- Khối lượng và Đơn giá được liệt kê từng mảng trong bảng Dữ liệu.
- Giá trị bằng Khối lượng x Đơn giá.
Giải quyết yêu cầu:
Theo yêu cầu của đề bài thì ta có hai việc để hoàn thiện là tính tổng giá trị vật tư dự kiến nhập (Theo kế hoạch) và tổng giá trị vật tư đã nhập về (Theo thực tế thi công). Thấy rằng bản chất của việc này là lấy tính Ʃ (Khối lượng x Đơn giá) của từng loại vật tư theo từng ngày là ra kết quả.
Theo cách chúng ta đang sử dụng là sử dụng hàm các phép tính thông thường nhưng nếu bạn sử dụng hàm Sumproduct thì câu chuyện sẽ đơn giản và thú vị hơn. Cụ thể tại ô J7 gõ công thức =SUMPRODUCT(F6:F10;H6:H10)
Cấu trúc hàm và ý nghĩa của thành phần cấu trúc bạn đã biết, thế bạn có hình dung ra hàm nó sẽ hoạt động thế nào để ra kết quả chưa? Hình minh họa dưới sẽ chỉ ra cho bạn thấy, rất đơn giản:
Tương tự vậy tính tổng giá trị Khối lượng vật tư về công trường theo thực tế thi công, tại ô K7 gõ công thức =SUMPRODUCT(G6:G10;H6:H10) với thành phần cấu trúc tương tự tính tổng giá trị khối lượng vật tư theo kế hoạch dự kiến.
Kết quả cuối cùng:
4.2. Ví dụ 2: Hàm Sumproduct với tổng tích nhiều điều kiện.
Đề bài:
Dữ liệu: Bảng tổng hợp Vật tư nhập về công trường.
Yêu cầu: Tính tổng khối lượng vật tư Cát nhập về công trường trước ngày 05/09/2017.
Hướng dẫn:
Phân tích yêu cầu
- Tính tổng khối lượng Vật tư.
- Vật tư là Cát.
- Vật tư nhập về trước ngày 05/09/2017 – ô K7.
Giải quyết yêu cầu
Theo những lưu ý được trình bày ở trên thì với khi dùng hàm Sumproduct để kiểm tra điều kiện thì những mảng điều kiện đó phải được để sau toán tử -- và điều kiện so sánh để trong dấu ngoặc kép. Cụ thể tại ô J7 nhập công thức =SUMPRODUCT(--(C6:C10
Dành một vài phút suy nghĩ và tìm hiểu về thành phần trong cấu trúc hàm nhé, sẽ khá thú vị nếu bạn để ý một chút kiến thức tôi trình bày dưới đây:
*) Thứ nhất: Dữ liệu trong mảng là Text thì kết quả sẽ trả về 0
Như ta đã biết trong thành phần cấu trúc hàm, ô nào chứa giá trị không phải là số học thì sẽ trả về Kết quả = 0.
Ở đây mảng Array2 (Vùng C6:C10) chứa dữ liệu Text ở tất cả các ô (Kết quả mảng này sẽ trả về Kết quả = 0).
Array1 và Array2 chứa dữ liệu là số (Ngày tháng trong Excel bản chất là số học).
Vì vậy: Theo cấu trúc đơn thuần (không có điều kiện) thì Tổng tích này sẽ trả về Kết quả = 0.
*) Thứ hai: Nếu thành phần trong cấu trúc hàm có điều kiện thì hàm đó hoạt động như thế nào?
Ta để ý đến một toán tử lạ xuất hiện trong cấu trúc đó là hai dấu trừ “- -” và so sánh (“<” và “=”) kiểm tra điều kiện trong mảng.
Giờ ta sẽ phân tích xem kết quả điều kiện trong thành phần cấu trúc này sẽ trả về kết quả như thế nào.
Kiểm tra thành phần C6:C10
*) Thứ ba: Biến kết quả True/ False thành 1/0 để đúng nghĩa khi sử dụng Sumproduct.
*) Kết luận: Thay vì bạn hiểu Array1 sẽ là kiểm tra điều kiện thỏa mãn trước ngày 05/09/2017 thì bạn sẽ thấy rằng, điều kiện này sẽ tương đương với mảng dữ liệu chứa giá trị 0 hoặc 1.
Tương tự như vậy bạn có thể kiểm tra cấu trúc Array2.
Phân tích cấu trúc hàm tổng quan để bạn dễ hình dung, chúng ta đi vào chi tiết để bạn hiểu sâu hơn về điều kiện trong hàm Sumproduct hoạt động như thế nào:
Kết quả trả về có dạng:
4.3. Ví dụ 3: Hàm Sumproduct với đếm số lượng nhiều điều kiện.
Đề bài:
Dữ liệu: Bảng tổng hợp khối lượng Vật tư nhập về công trường.
Yêu cầu: Đếm số lần Khối lượng Cát dự kiến nhỏ hơn Khối lượng Cát thực tế.
Hướng dẫn:
Phân tích yêu cầu
- Đếm số lần.
- Vật tư Cát.
- Khối lượng nhập về dự kiến nhỏ hơn Khối lượng nhập về thực tế.
Giải quyết yêu cầu
Việc đếm số lần thỏa mãn điều kiện trên trong thực tế tôi nghĩ bạn sẽ làm theo cách thông thường nhất là thống kê và tổng hợp.
Nhưng với tính năng kiểm tra điều kiện và tính tổng của hàm Sumproduct bạn có thể xử lý vấn đề này theo cách đặc biệt hơn.
Tại ô J6 gõ công thức =SUMPRODUCT(--(D6:D10="Cát");--(F6:F10<G6:G10))
Kết quả trả về có dạng:
