Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách tính tổng dữ liệu tương ứng với các số tuần cụ thể trong Excel và Google Trang tính.
Tổng nếu theo số tuần
Để "tính tổng nếu" theo số tuần, chúng ta sẽ sử dụng Hàm SUMIFS. Nhưng trước tiên chúng ta cần thêm một cột trợ giúp có chứa Hàm WEEKNUM.
Các Số tuần cột trợ giúp được tính bằng Hàm WEEKNUM:
| 1 | = WEEKNUM (B3,1) |
Tiếp theo, chúng ta sẽ sử dụng Hàm SUMIFS để tổng hợp tất cả Bán hàng diễn ra trong một Số tuần.
| 1 | = SUMIFS (D3: D9, C3: C9, F3) |
Tổng nếu theo số tuần - Không có cột người trợ giúp
Phương pháp cột trợ giúp rất dễ làm theo, nhưng bạn cũng có thể sao chép phép tính trong một công thức duy nhất bằng cách sử dụng Hàm SUMPRODUCT kết hợp với Hàm WEEKNUM để tính tổng Tổng số lần bán hàng qua Số tuần.
| 1 | = SUMPRODUCT (- (TUẦN (B3: B9 + 0,1) = E3), C3: C9) |
Trong ví dụ này, chúng ta có thể sử dụng Hàm SUMPRODUCT để thực hiện các phép tính phức tạp "tính tổng nếu". Hãy xem qua ví dụ trên.
Đây là công thức cuối cùng của chúng tôi:
| 1 | = SUMPRODUCT (- (TUẦN (B3: B9 + 0,1) = E3), C3: C9) |
Đầu tiên, Hàm SUMPRODUCT liệt kê mảng giá trị từ các phạm vi ô:
| 1 | =(--(({"1/3/2020"; "1/6/2020"; "1/9/2020"; "1/12/2020"; "1/15/2020"; "1/18/2020"; "1/21/2020"}+0,1)=1), {4; 9; 1; 7; 6; 2; 5}) |
Sau đó, Hàm WEEKNUM tính toán Số tuần của mỗi Ngày bán hàng.
Hàm WEEKNUM không được thiết kế để hoạt động với các giá trị mảng, vì vậy chúng ta phải thêm số 0 (“+0”) cho hàm WEEKNUM để xử lý các giá trị một cách chính xác.
| 1 | = SUMPRODUCT (- ({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Số tuần giá trị bằng 1 được thay đổi thành giá trị TRUE.
| 1 | = SUMPRODUCT (- ({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
Tiếp theo, dấu gạch ngang kép (-) chuyển đổi các giá trị TRUE và FALSE thành 1s và 0s:
| 1 | = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
Sau đó, Hàm SUMPRODUCT nhân từng cặp mục nhập trong mảng để tạo ra một mảng Số lượng bán hàng có một Số tuần của 1:
| 1 | = SUMPRODUCT ({4; 0; 0; 0; 0; 0; 0}) |
Cuối cùng, các số trong mảng được cộng lại với nhau:
| 1 | =4 |
Công thức này sau đó được lặp lại cho các giá trị có thể có khác của Số tuần.
Bạn có thể tìm thêm chi tiết về cách sử dụng câu lệnh Boolean và lệnh “-” trong Hàm SUMPRODUCT tại đây.
Khóa tham chiếu ô
Để làm cho các công thức của chúng tôi dễ đọc hơn, chúng tôi đã hiển thị các công thức mà không có tham chiếu ô bị khóa:
| 1 | = SUMPRODUCT (- (TUẦN (B3: B9 + 0,1) = E3), C3: C9) |
Nhưng những công thức này sẽ không hoạt động bình thường khi sao chép và dán ở nơi khác trong tệp của bạn. Thay vào đó, bạn nên sử dụng các tham chiếu ô bị khóa như sau:
| 1 | = SUMPRODUCT (- (TUẦN ($ B $ 3: $ B $ 9 + 0,1) = E3), $ C $ 3: $ C $ 9) |
Đọc bài viết của chúng tôi về Khóa tham chiếu ô để tìm hiểu thêm.
Tổng nếu theo số tuần trong Google Trang tính
Các công thức này hoạt động giống hệt nhau trong Google Trang tính cũng như trong Excel.
Tuy nhiên, Hàm WEEKNUM trong Google Trang tính linh hoạt hơn trong Excel và chấp nhận các đầu vào và đầu ra của mảng. Do đó, thao tác {Array} +0 trong công thức WEEKNUM (B3: B9 + 0,1) là không bắt buộc.
Công thức SUMPRODUCT đầy đủ có thể được viết trong Google Trang tính như sau:
| 1 | =GIỚI THIỆU(--(TUẦN($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
