Kevin cần tạo một công thức tính tổng mọi ô thứ tư liên tiếp. Anh ta biết anh ta có thể sử dụng một công thức như = A6 + E6 + I6 + M6, v.v., nhưng điều này sẽ trở nên phức tạp nếu có rất nhiều cột trong trang tính.

Có một số cách bạn có thể tiếp cận vấn đề này. Một cách là thêm một số thông tin bổ sung vào trang tính để chỉ định ô nào nên được đưa vào tổng. Ví dụ, trong ví dụ bạn quan tâm đến việc tính tổng các ô trong hàng 6 của trang tính. Nếu bạn có thể thêm một số chỉ báo trong hàng 5, thì những chỉ báo này có thể được sử dụng làm “trình kích hoạt” trong công thức. Đặt số 1, chẳng hạn, phía trên mỗi ô bạn muốn bao gồm trong tổng (cột A, E, I, M, v.v.). Sau đó, bạn có thể sử dụng công thức như sau:

=SUMPRODUCT(A5:X5, A6:X6)

Về cơ bản, công thức sẽ nhân bất kỳ thứ gì ở hàng 5 với hàng 6, rồi tính tổng kết quả. Vì chỉ có 1s trong các cột bạn muốn tính tổng, đây là tất cả những gì được bao gồm trong tổng cuối cùng.

Nếu bạn không muốn thêm một hàng chỉ số vào trang tính của mình, thì bạn cần xem xét các giải pháp khác nhau. Bạn vẫn có thể sử dụng hàm SUMPRODUCT trong công thức như sau:

=SUMPRODUCT((MOD(COLUMN(6:6),4)=1)*(6:6))

Công thức này dựa vào hàm MOD để trả về phần còn lại của một phép chia. Trong trường hợp này, thứ đang được chia là số cột của ô cho giá trị 4. Điều này sẽ dẫn đến phần còn lại của 0, 1, 2 hoặc 3. Mọi ô thứ tư trong hàng sẽ có cùng phần dư. Do đó, cột A (còn được gọi là cột 1) sẽ có giá trị MOD là 1 (1 chia cho 4 là 0, còn dư 1), cũng như các cột E, I, M, v.v.

Lưu ý rằng công thức so sánh giá trị MOD có phải là 1 hay không. Nếu đúng, thì phép so sánh trả về True (1); nếu không, thì nó trả về False (0). Sau đó, giá trị này được nhân lên với ô ở hàng thứ sáu.

Cuối cùng, SUMPRODUCT tính tổng tất cả các phép nhân này và cho kết quả mong muốn.

Trong khi công thức này cung cấp tổng của mọi ô thứ tư trong hàng thứ sáu, nó có thể dễ dàng thay đổi để cung cấp tổng cho mọi ô thứ ba, ô thứ năm hoặc bất kỳ khoảng nào bạn muốn. Chỉ cần thay đổi 4 trong chức năng MOD thành khoảng thời gian mong muốn.

Nếu bạn muốn chọn một ô khác nhau trong mỗi “cụm” bốn ô để tính tổng, thì tất cả những gì bạn cần làm là thay đổi giá trị được so sánh trong hàm MOD. Trong ví dụ này, chỉ ô đầu tiên trong mỗi cụm bốn ô sẽ có MOD là 1 (A, E, I, M, v.v.). Thay vào đó, nếu bạn muốn tính tổng mọi ô thứ tư bắt đầu bằng ô C, thì bạn sẽ thay đổi giá trị so sánh từ 1 thành 3. Tại sao? Vì C là ô thứ ba trong cụm và sẽ có MOD là 3, cũng như mỗi ô thứ tư sau đó (G, K, O, v.v.).

“Gottcha” duy nhất cho quy tắc chung này là nếu bạn muốn tính tổng ô thứ tư trong mỗi cụm bốn ô. Ví dụ: bạn có thể muốn tính tổng các ô D, H, L, P, v.v. Trong trường hợp này, giá trị so sánh được sử dụng sẽ không phải là 4 vì sẽ không bao giờ có phần dư là 4 khi thực hiện thao tác MOD liên quan đến phép chia cho 4 Thay vào đó, giá trị so sánh sẽ là 0, như sau:

=SUMPRODUCT((MOD(COLUMN(6:6),4)=0)*(6:6))

Nếu bạn thích làm việc với công thức mảng, bạn có thể sử dụng một biến thể ngắn hơn một chút cho công thức trên:

=SUM(IF(MOD(COLUMN(6:6),4)=1,6:6))

Lưu ý rằng công thức phải được nhập bằng cách nhấn Ctrl + Shift + Enter.

Sau đó, nó sẽ xuất hiện trong thanh Công thức với dấu ngoặc nhọn (\ {}) xung quanh công thức. Các ghi chú sửa đổi tương tự liên quan đến ước số MOD và giá trị so sánh áp dụng ở đây như đã làm với hàm SUMPRODUCT.

Cả hai cách tiếp cận công thức này (SUMPRODUCT và công thức mảng)

tính tổng mọi ô thứ tư trong toàn bộ hàng. Thay vào đó, nếu bạn muốn giới hạn các ô mà từ đó tổng được dẫn đến một phần của hàng, thì chỉ cần thay thế 6: 6 (cả hai trường hợp) bằng phạm vi thích hợp. Do đó, nếu bạn chỉ muốn tính tổng mọi ô thứ tư trong phạm vi A6: Z6, bạn sẽ sử dụng phạm vi đó trong công thức.

Nếu bạn thực hiện nhiều phép tính tổng theo cách này và bạn áp dụng nó không chỉ cho các phạm vi trong một hàng mà còn cho các phạm vi trong một cột, bạn có thể muốn xem xét tạo một hàm do người dùng xác định để thực hiện tính tổng. Hàm đơn giản sau sẽ thực hiện thủ thuật:

Function SumEveryFourth(MyRange As Range)

Dim x As Integer     SumEveryFourth = 0     For x = 1 To MyRange.Cells.Count         If (x Mod 4) = 1 Then             SumEveryFourth = SumEveryFourth + MyRange.Cells(x).Value         End If     Next x End Function

Hàm kiểm tra phạm vi được chuyển đến nó, sau đó tính tổng mọi ô thứ tư bắt đầu bằng ô đầu tiên trong phạm vi. Nếu bạn muốn tính tổng mỗi ô thứ hai trong phạm vi, thì hãy thay đổi giá trị so sánh trong câu lệnh If, như đã thảo luận trước đó trong mẹo này. (Vì thao tác Mod được sử dụng trong hàm này và nó hoạt động giống như hàm trang tính MOD, nên các giá trị so sánh giống nhau sẽ có tác dụng xác định ô nào trong mỗi cụm sẽ được tính tổng.)

Hàm do người dùng xác định sẽ hoạt động tốt trên các ô trong một hàng hoặc các ô trong một cột. Bạn chỉ cần đảm bảo rằng bạn vượt qua nó trong phạm vi bạn muốn, như được minh họa ở đây:

=SumEveryFourth(C3:C57)

Nếu muốn, bạn có thể làm cho macro trở nên linh hoạt hơn bằng cách đặt giá trị “bước” thành một biến được chuyển cho hàm. Sau đó, macro đã thay đổi này có thể được sử dụng để tính tổng theo mỗi ô 2, 3, 4, 5, v.v.

Function SumEveryNth(MyRange As Range, N As Integer)

Dim x As Integer     SumEveryFourth = 0     For x = 1 To MyRange.Cells.Count         If (x Mod N) = 1 Then             SumEveryNth = SumEveryNth + MyRange.Cells(x).Value         End If     Next x End Function

Hàm này được gọi giống như hàm trước đó, ngoại trừ việc bạn thêm giá trị bước mong muốn. Ví dụ, điều này sẽ dẫn đến mọi ô thứ ba trong phạm vi C3: C57 được tính tổng:

=SumEveryNth(C3:C57, 3)

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3234) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: