Công thức chung
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
Tóm lược
Để tính tổng mọi cột thứ n, bạn có thể sử dụng công thức dựa trên các hàm SUMPRODUCT, MOD và COLUMN.
Trong ví dụ được hiển thị, công thức trong L5 là:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
Giải trình
Về cốt lõi, sử dụng SUMPRODUCT để tính tổng các giá trị trong một hàng đã được "lọc" bằng cách sử dụng logic dựa trên MOD. Điều quan trọng là:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
Đoạn mã công thức này sử dụng hàm COLUMN để nhận một tập hợp các số cột "tương đối" cho phạm vi (được giải thích chi tiết tại đây) trông giống như sau:
(1,2,3,4,5,6,7,8,9)
Điều này đi vào MOD như sau:
MOD((1,2,3,4,5,6,7,8,9),K5)=0
trong đó K5 là giá trị của N trong mỗi hàng. Hàm MOD trả về phần còn lại cho mỗi số cột chia cho N. Vì vậy, ví dụ: khi N = 3, MOD sẽ trả về như sau:
(1,2,0,1,2,0,1,2,0)
Lưu ý rằng các số không xuất hiện cho cột 3, 6, 9, v.v. Công thức sử dụng = 0 để buộc TRUE khi phần còn lại bằng 0 và FALSE khi không, sau đó chúng tôi sử dụng dấu hai âm (-) để buộc TRUE và FALSE cho một và số không. Điều đó để lại một mảng như thế này:
(0,0,1,0,0,1,0,0,1)
Trong đó 1s bây giờ cho biết "giá trị thứ n". Điều này đi vào SUMPRODUCT dưới dạng array1, cùng với B5: J5 dưới dạng array2. Sau đó SUMPRODUCT thực hiện công việc của nó, đầu tiên là nhân, sau đó tính tổng các tích của các mảng.
Các giá trị duy nhất mà phép nhân "tồn tại" là những giá trị mà array1 chứa 1. Bằng cách này, bạn có thể nghĩ về logic của array1 "lọc" các giá trị trong array2.
Tính tổng mọi cột khác
Nếu bạn muốn tính tổng mọi cột khác, chỉ cần điều chỉnh công thức này khi cần thiết, hãy nhớ rằng công thức tự động gán 1 cho cột đầu tiên trong phạm vi. Để tính tổng EVEN các cột, hãy sử dụng:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0),A1:Z1)
Để tính tổng các cột ODD, hãy sử dụng:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1),A1:Z1)
