Có thể bạn quan tâm: Cách Tính Thành Tiền Có Điều Kiện Trong Excel
Giới thiệu
Trong công việc quản lý dữ liệu, báo cáo tài chính, hoặc đơn giản là khi ta muốn tính tổng các giá trị có mẫu lặp lặp lại trong dunkel Excel, taಮಾನ thường gặp tình huống tính tổng các ô xen kẽ. Ví dụ, ta có một cột dữ liệu A1:A100, và chỉ muốn cộng các giá trị ở các ô A1, A3, A5, … A99. Hoặc trong bảng dữ liệu rộng hơn, ta cần tính tổng các ô ở hàng lẻ, cột lẻ, hoặc thậm chí là các ô có chỉ số chia hết cho một số nhất định.
Để thực hiện điều này, Excel cung cấp nhiều công cụ và hàm mạnh mẽ. Bài viết này sẽ hướng dẫn chi tiết cách tính tổng các ô xen kẽ, từ các hàm cơ bản như SUMPRODUCT, OFFSET, SUMIF, đến các hàm nâng cao hơn như FILTER, LET, SEQUENCE, và thậm chí là VBA cho những người muốn tối ưu hiệu suất. Bạn sẽ học được cách thức hoạt động của từng công thức, cách áp dụng vào các trường hợp thực tế, và những mẹo nhỏ giúp tránh lỗi khi làm việc với dữ liệu lớn.
Có thể bạn quan tâm: Tính Số Tuần Và Số Ngày Lẻ Trong Excel – Hướng Dẫn Chi Tiết
1. Cách tiếp cận cơ bản: Sử dụng hàm SUMPRODUCT
1.1. Cú pháp cơ bản
Hàm SUMPRODUCT là một trong những công cụ linh hoạt nhất để tính tổng các ô có điều kiện mà không cần tạo cột phụ. Cú pháp:
SUMPRODUCT(array1, array2, array3, ...)
Trong đó, array1, array2, … là các dãy số. SUMPRODUCT nhân các phần tử tương ứng của các dãy rồi cộng lại.
1.2. Tính tổng các ô lẻ trong một cột
Giả sử dữ liệu nằm trong cột A từ A1 đến A100. Ta muốn tính tổng các ô có chỉ số hàng lẻ: A1, A3, A5, …
Công thức:
=SUMPRODUCT((MOD(ROW(A1:A100)-ROW(A1)+1,2)=1)A1:A100)
Giải thích:
ROW(A1:A100)-ROW(A1)+1tạo một dãy số 1,2,3,…,100.MOD(...,2)=1trả về mảng logic TRUE (giá trị 1) cho các hàng lẻ, FALSE (giá trị 0) cho hàng chẵn.- Nhân với
A1:A100sẽ giữ lại giá trị ở các ô lẻ, đặt 0 ở các ô chẵn. - Cuối cùng,
SUMPRODUCTcộng các giá trị còn lại.
1.3. Tính tổng các ô chẵn
Tương tự, thay =1 thành =0:
=SUMPRODUCT((MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)A1:A100)
1.4. Tính tổng các ô lẻ trong hàng và cột lẻ
Nếu dữ liệu nằm trong vùng B2:E10 và bạn muốn tính tổng các ô lẻ cả hàng lẻ và cột lẻ, bạn có thể dùng SUMPRODUCT với cả hai điều kiện:
=SUMPRODUCT( (MOD(ROW(B2:E10)-ROW(B2)+1,2)=1) (MOD(COLUMN(B2:E10)-COLUMN(B2)+1,2)=1) B2:E10
)
Điều này đảm bảo chỉ tính các ô ở chỗ hàng lẻ và cột lẻ.
Có thể bạn quan tâm: Cách Đánh Dấu Tích Vào Ô Vuông Trong Excel 2026
2. Sử dụng hàm OFFSET (cùng với SUM)
2.1. Cú pháp OFFSET
OFFSET(reference, rows, cols, height, width)
Hàm trả về một vùng ô tham chiếu từ reference, dịch đi rows dòng và cols cột, với chiều cao height và chiều rộng width. Khi kết hợp với SUM, ta có thể tính tổng các ô xen kẽ.
2.2. Tính tổng các ô lẻ trong cột
Giả sử muốn tính tổng các ô lẻ trong cột A:
=SUM(OFFSET(A1,0,0,COUNT(A:A),1,1)) // Mặt khác, dùng SUMPRODUCT
Breaking down:
=SUMPRODUCT((MOD(ROW(A1:A100)-ROW(A1)+1,2)=1)A1:A100)
Tuy nhiên, OFFSET thường không tối ưu cho dữ liệu lớn vì nó tạo ra mảng động.
3. Hàm SUMIF với tiêu chí
3.1. Định nghĩa SUMIF
SUMIF(range, criteria, sum_range)
range: vùng kiểm tra điều kiện.criteria: điều kiện kiểm tra.sum_range: vùng thực hiện tổng (nếu khácrange).
3.2. Tính tổng các ô lẻ
Trong Excel 2026 trở đi, ta có thể dùng SUMIF với công thức mảng:
=SUMIF(ROW(A1:A100),MOD(ROW(A1:A100),2)=1,A1:A100)
Nhưng Excel 2026 và trước đó không hỗ trợ mảng trong SUMIF. Ta có thể dùng SUMPRODUCT như ở phần 1.
3.3. Tính tổng theo điều kiện phức tạp
Nếu muốn tính tổng các ô მერე có giá trị > 0 và nằm ở hàng lẻ:
=SUMPRODUCT((MOD(ROW(A1:A100)-ROW(A1)+1,2)=1)(A1:A100>0)A1:A100)
4. Sử dụng hàm INDEX và ROW/COLUMN
4.1. Cú pháp INDEX
INDEX(array, row_num, column_num)
Trả về giá trị tại vị trí row_num, column_num trong array.
4.2. Tính tổng các ô lẻ mà không dùng mảng
Bạn có thể dùng INDEX với SUM và ROW:
=SUM(IF(MOD(ROW(A1:A100)-ROW(A1)+1,2)=1, INDEX(A1:A100,ROW(A1:A100)-ROW(A1)+1))
Trong Excel 365, bạn không cần nhập công thức với Ctrl+Shift+Enter vì tính năng mảng động.
5. Sử dụng hàm LET và LAMBDA (Excel 365)
Có thể bạn quan tâm: Tính Trung Bình Có Điều Kiện Trong Excel: Cách Thực Hiện Và Ứng Dụng Thực Tế
5.1. Hàm LET
LET cho phép định nghĩa biến trong công thức, làm cho công thức ngắn gọn và nhanh hơn.
Ví dụ:
=LET( arr, A1:A100, idx, ROW(arr)-ROW(A1)+1, sum, SUMPRODUCT((MOD(idx,2)=1)arr), sum
)
5.2. Hàm LAMBDA
Bạn có thể tạo hàm tùy chỉnh:
=LET( arr, A1:A100, idx, ROW(arr)-ROW(A1)+1, SUMPRODUCT((MOD(idx,2)=1)arr)
)
Nếu bạn muốn tái sử dụng, tạo một LAMBDA:
=LET( OddSum, LAMBDA(rng, SUMPRODUCT((MOD(ROW(rng)-ROW(INDEX(rng,1,1))+1,2)=1)rng)), OddSum(A1:A100)
)
6. Sử dụng hàm FILTER (Excel 365)
6.1. Cú pháp FILTER
FILTER(array, include, if_empty)
Trả về một mảng gồm các phần tử thỏa mãn điều kiện include.
6.2. Tính tổng các ô lẻ
=SUM(FILTER(A1:A100, MOD(ROW(A1:A100)-ROW(A1)+1,2)=1))
Điều này rất dễ đọc và trực quan.
6.3. Tính tổng các ô lẻ có giá trị > 10
=SUM(FILTER(A1:A100, (MOD(ROW(A1:A100)-ROW(A1)+1,2)=1)(A1:A100>10)))
7. Tính tổng các ô lẻ trong vùng hai chiều
7.1. Kết hợp với ROW và COLUMN
Giả sử vùng dữ liệu là B2:E10, và bạn muốn tính tổng các ô lẻ cả hàng lẻ và cột lẻ:
=SUMPRODUCT( (MOD(ROW(B2:E10)-ROW(B2)+1,2)=1) (MOD(COLUMN(B2:E10)-COLUMN(B2)+1,2)=1) B2:E10
)
7.2. Sử dụng hàm INDEX + ROW + COLUMN + MOD
=SUMPRODUCT( INDEX(B2:E10,ROW(B2:E10)-ROW(B2)+1,COLUMN(B2:E10)-COLUMN(B2)+1) (MOD(ROW(B2:E10)-ROW(B2)+1,2)=1)(MOD(COLUMN(B2:E10)-COLUMN(B2)+1,2)=1)
)
8. Tính tổng các ô lẻ trong cột lẻ trong một bảng dữ liệu lớn
8.1. Khi dữ liệu lớn hơn 1 triệu dòng
Trong trường hợp dữ liệu lớn, SUMPRODUCT và ARRAYFORMULA có thể chậm. Bạn có thể dùng AGGREGATE với hàm 9 (SUM) và OFFSET:
=AGGREGATE(9,6,OFFSET($A$1,SEQUENCE(ROWS($A$1:$A$1000000)),0,1,1),1)
Tuy nhiên, cách này vẫn Monitoring.
8.2. Sử dụng Power Query
Power Query rất mạnh để xử lý dữ liệu lớn. Bạn có thể import dữ liệu, tạo cột mới dùng Index để đánh số hàng, rồi lọc theo điều kiện lẻ và cộng dồn.
9. Sử dụng VBA để tính tổng các ô lẻ
Đối với những người quen thuộc với lập trình, VBA cung cấp một cách nhanh chóng và linh hoạt.
9.1. Macro mẫu
Function SumOddCells(rng As Range) As Double Dim cell As Range Dim sum As Double sum = 0 For Each cell In rng If (cell.Row + cell.Column) Mod 2 = 0 Then 'Cả hàng lẻ và cột lẻ sum = sum + cell.Value End If Next cell SumOddCells = sum
End Function
Sau khi thêm macro này vào Module, bạn có thể dùng trong ô:
=SumOddCells(A1:A100)
9.2. Tối ưu hiệu suất
- Dùng
Application.ScreenUpdating = Falseđể tránh làm chậm. - Đọc giá trị vào mảng thay vì lặp từng ô:
arr = rng.Value. - Dùng
Longcho chỉ số.
10. Mẹo và lưu ý khi tính tổng các ô xen kẽ
| Mẹo | Mô tả |
|---|---|
| Kiểm tra dữ liệu | Trước khi dùng công thức, hãy chắc chắn rằng vùng(Matrix) không chứa lỗi (#DIV/0!, #N/A). |
| Sử dụng INDEX thay vì OFFSET | OFFSET thường chậm hơn vì tính toán động, INDEX nhanh hơn. |
| Tránh mảng lớn trong SUMPRODUCT | Nếu vùng quá lớn, hãy chia nhỏ vùng hoặc dùng VBA. |
| Sử dụng hàm FILTER | Đối với Excel 365, FILTER rất trực quan và dễ đọc. |
| Tối ưu bằng LET | Đặt biến trung gian tránh tính lặp lại. |
| Đảm bảo kiểu dữ liệu đúng | Nếu vùng chứa văn bản, hàm SUMPRODUCT sẽ trả về lỗi. |
| Xác định đúng phạm vi | Đôi khi vùng dữ liệu bị lặp lại hoặc trống, cần kiểm tra. |
| Sử dụng ổn định OFFSET | Nếu vùng thay đổi, OFFSET có thể lỗi; dùng INDEX + ROW/ COLUMN tốt hơn. |
| Kiểm tra hiệu suất | Dùng công cụ Evaluate Formula để xem công thức thực thi như thế nào. |
| Đặt công thức trong ô trống | Tránh ghi đè dữ liệu quan trọng. |
11. Ví dụ thực tế: Tổng doanh thu lẻ trong bảng báo cáo
Giả sử bạn có bảng doanh thu theo ngày:
| Ngày | Doanh thu |
|---|---|
| 1/1 | 200 |
| 2/1 | 150 |
| 3/1 | 300 |
| 4/1 | 120 |
| … | … |
Bạn muốn tính tổng doanh thu của các ngày lẻ (1, 3, 5, …). Dùng công thức:
=SUMPRODUCT((MOD(ROW(B2:B100)-ROW(B2)+1,2)=1)B2:B100)
Nếu cần tính doanh thu của các ngày lẻ và có doanh thu > 250, dùng:
=SUMPRODUCT((MOD(ROW(B2:B100)-ROW(B2)+1,2)=1)(B2:B100>250)B2:B100)
12. Kết luận
Tính tổng các ô xen kẽ trong Excel là một kỹ năng quan trọng giúp bạn xử lý dữ liệu một cách linh hoạt, đặc biệt khi làm việc với bảng có mẫu lặp lặp. Excel cung cấp rất nhiều công cụ để thực hiện điều này: từ các hàm đơn giản như SUMPRODUCT, OFFSET, SUMIF cho đến các hàm nâng cao hơn như FILTER, LET, LAMBDA, và thậm chí là VBA. Việc lựa chọn công thức phù hợp phụ thuộc vào nhu cầu cụ thể, kích thước dữ liệu và phiên bản Excel bạn đang sử dụng.
Hãy thử áp dụng những công thức trên vào dữ liệu thực tế của bạn, kiểm tra hiệu suất và điều chỉnh nếu cần. Khi bạn thành thạo, việc tính tổng các ô xen kẽ sẽ trở thành một công cụ tự nhiên trong bộ công cụ phân tích dữ liệu của mình, giúp bạn tiết kiệm thời gian và tránh sai sót trong quá trình xử lý.
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.