Trong bài viết này, chúng ta sẽ học cách trả về giá SUM theo các ngày trong tuần trong Excel.

Nói cách đơn giản, trong khi làm việc với dữ liệu đầy đủ về giá. Đôi khi, chúng ta cần tìm tổng giá trị có một điều kiện. Điều kiện là lấy tổng các giá trị giá trong các cột tách biệt.

Đối với bài viết này, chúng ta sẽ cần sử dụng các chức năng sau:

Bây giờ chúng ta sẽ tạo công thức cho các hàm này. Ở đây chúng ta sẽ được cung cấp một danh sách các giá trị và chúng ta cần lấy tổng các giá trị nằm trong mỗi bội số của cột thứ n đã cho.

Sử dụng công thức:

=SUMPRODUCT ( -- ( MOD ( COLUMN ( list ) - COLUMN ( first ) + 1 , n ) = 0 ) , list )

list: danh sách các giá trị dọc theo cột trước tiên: ô đầu tiên của danh sách trả về số cột n: giá trị cột thứ n để cộng lại.

Ví dụ:

Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới.

Ở đây chúng tôi đã đưa ra dữ liệu chứa các số. Ở đây chúng ta cần tìm TỔNG của các số trong mỗi bội số của cột thứ 3. Vì vậy, chúng tôi đã gán danh sách trong một cột cho công thức.

image

Bây giờ chúng ta sẽ sử dụng công thức dưới đây để lấy SUM của các số ở vị trí thứ 3, 6, 9, 12, v.v. cho đến khi danh sách kết thúc Công thức:

=SUMPRODUCT ( -- ( MOD ( COLUMN ( list ) - COLUMN ( C5 ) + 1 , P5 ) = 0 ) ,list )

list: danh sách các giá trị dọc theo cột được cung cấp khi sử dụng các phạm vi được đặt tên.

C5: ô đầu tiên của danh sách trả về số cột được cho làm tham chiếu ô P5: giá trị của n được cho làm tham chiếu ô Giải thích:

trả về số cột tương đối cho mỗi khoảng thời gian n. Giống như \ {1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1} Mảng trên sẽ được so khớp với giá trị 0 và trả về một mảng True & False. Hàm SUMPRODUCT \ {FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE} xem xét giá trị 1 cho mọi giá trị TRUE nhận được và 0 cho giá trị FALSE như hình dưới đây.

SUMPRODUCT (- (\ {0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0}), \ {40, 36, 51, 33, 42, 30, 92, 67, 34, 54, 69, 56, 50})

* – chuyển đổi TRUE thành 1 và FALSE thành 0.

  • Các giá trị tương ứng với 1s sẽ chỉ được cộng như được hiển thị trong công thức giải thích ở trên.

  • Bây giờ hàm SUMPRODUCT cộng tất cả các giá trị như hình dưới đây.

SUMPRODUCT (\ {0, 0, 51, 0, 0, 30, 0, 0, 34, 0, 0, 56, 0})

image

SUM của các giá trị \ {51 + 30 + 34 + 56} phải trả về 171.

Ở đây, mảng cho hàm được cung cấp dưới dạng phạm vi được đặt tên và ô là cell_reference. Nhấn Enter để nhận kết quả.

image

Như bạn có thể thấy trong ảnh chụp nhanh ở trên, tổng các giá trị được trích xuất tại mỗi khoảng thời gian thứ 3 trả về 171. Các giá trị được đánh dấu dưới các hộp màu đỏ như được hiển thị trong ảnh chụp nhanh ở trên.

Hoặc bạn có thể sử dụng mảng danh sách theo công thức dưới đây:

=SUMPRODUCT ( -- ( MOD ( COLUMN ( C5:O5 ) - COLUMN ( C5 ) + 1 , P5 ) = 0 ), C5:O5 )

Công thức trên sẽ trả về cùng một kết quả. Chỉ có một danh sách thay thế có tên là dải ô được thay thế bằng dải mảng (C5: O5).

image

Như bạn có thể thấy công thức hoạt động tốt. Bây giờ chúng ta cần sao chép công thức cho các danh sách khác. Vì vậy, hãy sử dụng CTRL + D hoặc tùy chọn kéo xuống của excel.

image

Như bạn có thể thấy từ công thức trên, bạn có thể nhận các giá trị có điều kiện. Ghi chú:

  1. Hàm SUMPRODUCT coi các giá trị không phải là số là 0.

  2. Hàm SUMPRODUCT coi giá trị logic là TRUE là 1 và False là 0.

  3. Mảng đối số phải có cùng độ dài, nếu không công thức trả về lỗi.

Hy vọng bài viết này về cách trả về SUM chỉ mỗi cột thứ n với các công thức trong Excel là giải thích. Tìm thêm các bài viết về hàm SUMPRODUCT tại đây. Vui lòng chia sẻ câu hỏi của bạn bên dưới trong hộp bình luận. Chúng tôi sẽ hỗ trợ bạn.

Các bài viết liên quan

Bài viết phổ biến

liên kết: / logic-công thức-nếu-hàm-với-ký tự đại diện [Nếu có ký tự đại diện]