Có thể bạn quan tâm: Tính Tổng Các Giá Trị Trùng Lặp Trong Excel: Hướng Dẫn Chi Tiết Từng Bước
Giới thiệu
Trong công việc quản lý dữ liệu, việc tính tổng số lượng theo các tiêu chí cụ thể là một nhu cầu phổ biến. Excel cung cấp một loạt các hàm mạnh mẽ để thực hiện điều này, từ SUMIF, SUMIFS cho tới SUMPRODUCT, SUM kết hợp với các mảng. Bài viết này sẽ đưa bạn đi qua từng bước, từ những công thức cơ bản đến những kỹ thuật nâng cao, giúp bạn nhanh chóng và chính xác tính tổng số lượng đáp ứng điều kiện trong Excel.
Có thể bạn quan tâm: Tính Tổng Các Dữ Liệu Giống Nhau Trong Excel – Hướng Dẫn Chi Tiết Và Thực Tiễn
1. Tổng quan về các hàm tổng có điều kiện
| Hàm | Mô tả | Đối tượng sử dụng |
|---|---|---|
SUMIF | Tính tổng các ô thỏa mãn một điều kiện duy nhất | Khi chỉ có một điều kiện |
SUMIFS | Tính tổng các ô thỏa mãn nhiều điều kiện | Khi có nhiều điều kiện |
SUMPRODUCT | Tính tổng tích của hai mảng, linh hoạt với điều kiện phức tạp | Khi cần tính tổng theo điều kiện phức tạp |
SUM + IF array | Tính tổng theo điều kiện trong mảng | khi cần tính tổng phức tạp nhưng không sử dụng SUMIFS |
SUBTOTAL | Tính tổng trong danh sách dữ liệu được lọc | Khi cần tổng trong dữ liệu đã lọc |
Có thể bạn quan tâm: Tính Tương Đối Của Địa Chỉ Ô Trong Excel
2. Cài đặt dữ liệu mẫu
Để thực hành, chúng ta sẽ mở một bảng dữ liệu mẫu gồm các cột:
| A | B | C | D | E |
|---|---|---|---|---|
| Mã Sản Phẩm | Tên Sản Phẩm | Loại | Số Lượng | Giá |
| 001 | Giấy A4 | Văn phòng | 10 | 20,000 |
| 002 | Bút Lông | Văn phòng | 15 | 30,000 |
| 003 | Laptop HP | Máy tính | 5 | 10,000,000 |
| 004 | Máy tính bảng | Máy tính | 8 | 8,000,000 |
| 005 | Sổ tay | Văn phòng | 20 | 15,000 |
| … | … | … | … | … |
Chúng ta sẽ sử dụng bảng này để thực hiện các ví dụ tính tổng.
3. Tính tổng với SUMIF
3.1 Cú pháp
SUMIF(đoạn_tham_chieu, điều_kiện, đoạn_tổng)
- đoạn_tham_chieu: vùng dữ liệu để kiểm tra điều kiện.
- điều_kiện: điều kiện cần thỏa mãn.
- đoạn_tổng: vùng dữ liệu cần tính tổng (có thể là cùng vùng hoặc vùng khác).
3.2 Ví dụ 1: Tổng số lượng sản phẩm theo loại
Giả sử chúng ta muốn tính tổng số lượng của các sản phẩm thuộc loại “Văn phòng”.
=SUMIF(C2:C100, "Văn phòng", D2:D100)
Kết quả: tổng số lượng các sản phẩm trong danh mục “Văn phòng”.
3.3 Ví dụ 2: Tổng giá trị đơn hàng lớn hơn 5,000,000
=SUMIF(E2:E100, ">5000000", D2:D100)
Kết quả: tổng số lượng các sản phẩm có giá trên 5 Botox. (Chú ý: Khi tính tổng giá trị, ta có thể nhân số lượng với giá).
=SUMIF(E2:E100, ">5000000", D2:D100E2:E100)
Lưu ý: Trong Excel 365, bạn có thể sử dụng công thức mảng:
excel
=SUM(IF(E2:E100>5000000, D2:D100E2:E100, 0))
Khi nhập, nhấnCtrlUsuarios+Shift+Enterđể làm cho công thức trở thành công thức mảng.
4. Tính tổng với SUMIFS
4.1 Cú pháp
SUMIFS(đoạn_tổng, đoạn_điều_kiện1သာ, điều_kiện1, đoạn_điều_kiện2, điều_kiện2, ...)
4.2 Ví dụ 1: Tổng số lượng sản phẩm “Văn phòng” với giá dưới 25,000
=SUMIFS(D2:D100, C2:C100, "Văn phòng", E2:E100, "<25000")
4.3 Ví dụ 2: Tổng số lượng sản phẩm thuộc loại “Máy tính” và mã bắt đầu bằng “00”
=SUMIFS(D2:D100, C2:C100, "Máy tính", A2:A100, "00")
Chú ý: Khi sử dụng ký tự đại diện (
), điều kiện phải đặt trong dấu ngoặc kép.
5. Tính tổng phức tạp với SUMPRODUCT
Hàm SUMPRODUCT rất mạnh mẽ khi bạn muốn kết hợp nhiều điều kiện, hoặc tính tổng theo điều kiện phức tạp không thể thực hiện bằng SUMIFS.
5.1 Cú pháp cơ bản
SUMPRODUCT(mảng1, mảng2, mảng3, ...)
Mỗi mảng sẽ được nhân với nhau, sau đó tổng các giá trị sẽ được tính.
5.2 Ví dụ 1: Tổng số lượng sản phẩm “Văn phòng” có giá > 20,000
=SUMPRODUCT((C2:C100="Văn phòng")(E2:E100>20000), D2:D100)
(C2:C100="Văn phòng")trả về mảng 1/0.(E2:E100>20000)trả về mảng 1/0.- Khi nhân hai mảng, chỉ những hàng thỏa mãn cả hai điều kiện mới trả về 1, còn lại là 0.
- Cuối cùng nhân với
D2:D100để lấy số lượng.
5.3 Ví dụ 2: Tổng giá trị đơn hàng trong khoảng 2,000,000 – 5,000,000
=SUMPRODUCT((E2:E100>=2000000)(E2:E100<=5000000), D2:D100E2:E100)
Lưu ý:
SUMPRODUCTkhông yêu cầu nhấnCtrl+Shift+Enter; nó tự động xử lý mảng.
6. Sử dụng công thức mảng với SUM + IF
Đối với các phiên bản Excel trước 365, bạn có thể dùng công thức mảng để tính tổng theo điều kiện.
=SUM(IF(C2:C100="Văn phòng", D2:D100, 0))
Nhấn Ctrl+Shift+Enter để biến thành công thức mảng. Khi thành công, Excel sẽ đặt dấu ngoặc kép {} xung quanh công thức.
6.1 Ví dụ nâng cao: Tổng số lượng theo nhiều điều kiện
Có thể bạn quan tâm: Tính Tổng Số Học Sinh Xếp Loại Trong Excel: Hướng Dẫn Chi Tiết Và Thực Tiễn
=SUM(IF((C2:C100="Văn phòng")(E2:E100>20000), D2:D100, 0))
Cảnh báo: Công thức mảng có thể làm chậm Excel nếu vùng dữ liệu lớn.
SUMIFSthường nhanh hơn কলেজ.
7. Tính tổng trong dữ liệu đã lọc với SUBTOTAL
Khi bạn lọc dữ liệu bằng Filter hoặc Advanced Filter, bạn có thể tính tổng chỉ các dòng hiển thị bằng SUBTOTAL.
=SUBTOTAL(9, D2:D100)
9создание hàmSUMtrongSUBTOTAL.- Tùy vào yêu cầu, bạn có thể dùng
1choAVERAGE,2choCOUNT,3choCOUNTA, vv.
Lưu ý:
SUBTOTALtự động bỏ qua các hàng ẩn khi lọc.
8. Kết hợp SUMPRODUCT với dữ liệu động (Dynamic Arrays)
Trong Excel 365, bạn Lai có thể sử dụng FILTER, SORT, UNIQUE kết hợp với SUMPRODUCT để tính tổng theo các điều kiện phức tạp.
8.1 Ví dụ: Tổng số lượng theo danh sách loại duy nhất
=SUMPRODUCT(FILTER(D2:D100, C2:C100=UNIQUE(C2:C100)))
UNIQUE(C2:C100)lấy danh sách loại duy nhất.FILTER(D2:D100, C2:C100=...)lọc số lượng tương ứng.SUMPRODUCTtính tổng.
9. Tính tổng trong PivotTable
PivotTable là công cụ mạnh mẽ để tóm tắt dữ liệu, bao gồm tính tổng fod.
- Chọn vùng dữ liệu.
所属 2. Insert > PivotTable. - Đưa Loại vào Rows, Số Lượng vào Values (sử dụng “Sum of Số Lượng”).
- Nếu cần điều kiện, bạn có thể thêm Thẻ vào Filters.
PivotTable cho phép bạn thay đổi tiêu chí lọc một cách nhanh chóng mà không phải chỉnh công thức.
10. Tối ưu hóa hiệu suất tính tổng
10.1 Tránh công thức mảng lớn
Nếu dữ liệu lớn (thousands rows), công thức mảng có thể làm chậm. Thay vào đó, hãy dùng SUMIFS hoặc SUMPRODUCT.
10.2 Sử dụng bảng (Table)
Chuyển vùng dữ liệu thành bảng (Ctrl+T). Khi dùng bảng, các công thức tự động Iterate, tránh làm đó.
=SUMIFS(Số_LượngValue, LoạiValue, "Văn phòng")
10.3 Giới hạn vùng dữ liệu
Thay vì dùng C2:C1000 để tính tổng, hãy giới hạn vùng thực tế (C2:C200) để tránh tính toán không cần thiết.
11. Thêm tính năng nâng cao: VBA macro
Nếu bạn cần tính tổng theo điều kiện phức tạp nhiều lần, bạn có thể viết VBA.
Sub TinhTong() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") Dim tong As Double tong = Application.WorksheetFunction.SumIf(ws.Range("C:C"), "Văn phòng", ws.Range("D:D")) MsgBox "Tổng số lượng 'Văn phòng': " & tong
End Sub
Lưu ý: Khi sử dụng VBA, bạn cần đảm bảo macro được bật và macro security được thiết lập.
12. Xử lý dữ liệu có khoảng trống hoặc lỗi
12.1 Sử dụng IFERROR
Khi có lỗi trong dữ liệu, bạn có thể bảo vệ công thức bằng IFERROR.
=IFERROR(SUMIFS(D2:D100, C2:C100, "Văn phòng", E2:E100, "<>"""), 0)
12.2 Sử dụng TRIM và UPPER để chuẩn hóa dữ liệu
Trước khi lọc, hãy chuẩn hóa dữ liệu:
=SUMIFS(D2:D100, C2:C100, TRIM(UPPER("Văn phòng")), E2:E100, "<>""")
13. Kiểm tra và xác nhận kết quả
- So sánh: Kiểm tra kết quả với một phương pháp thủ công (đếm thủ công, tính tổng bằng tay) để chắc chắn không có lỗi.
- Bảng kiểm tra: Tạo một bảng dữ liệu mẫu nhỏ và tính tổng bằng cả
SUMIF,SUMIFS,SUMPRODUCTđể so sánh. - Debug: Nếu kết quả không đúng, kiểm tra từng phần: điều kiện, vùng dữ liệu, kiểu dữ liệu số (số thực vs zust).
14. Tình huống thực tế: Tổng số lượng bán trong tháng
Giả sử bạn có bảng bán hàng:
| A | B | C | D | E |
|---|---|---|---|---|
| Ngày | Mã Sản Phẩm | Tên | Số Lượng | Giá |
| 01/01/2026 | 001 | Giấy A4 | 50 | 20,000 |
| 05/01/2026 | 002 | Bút Lông | 30 | 30,000 |
| 12/01/2026 | 001 | Giấy A4 | 70 | 20,000 |
| 20/01/2026 | 003 | Laptop HP | 2 | 10,000,000 |
| 25/01/2026 | 004 | Máy tính bảng | 5 | 8,000,000 |
14.1 Tổng số lượng bán trong tháng 1
=SUMIFS(D2:D100, A2:A100, ">=01/01/2026", A2:A100, "<=31/01/2026")
14.2 Tổng doanh thu trong tháng 1
=SUMPRODUCT((A2:A100>=DATE(2026,1,1))(A2:A100<=DATE(2026,1,31)), D2:D100E2:E100)
15. Kết luận
Tính tổng số lượng có điều kiện trong Excel là một kỹ năng cốt lõi giúp bạn quản lý dữ liệu hiệu quả. Bằng cách nắm vững các hàm SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL và hiểu cách tối ưu hiệu suất, bạn có thể xử lý mọi tình huống từ đơn giản đến phức tạp. Hãy thử áp dụng các ví dụ thực tế, điều chỉnh cho dữ liệu riêng của bạn và khám phá thêm các tính năng nâng cao như dynamic arrays và VBA.
Khuyến nghị: Khi làm việc với dữ liệu lớn, hãy lưu ý tới hiệu suất. Sử dụng bảng (
Table) và giới hạn vùng dữ liệu thực tế sẽ giúp công việc mượt mà hơn. Nếu phát hiện lỗi, hãy kiểm tra từng bước, dùng công cụEvaluate Formulatrong Excel để debug.
16. Thư viện công thức nhanh
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=SUMIF(range, criteria, sum_range) | Tính tổng theo một điều kiện | =SUMIF(C:C, "Văn phòng", D:D) |
=SUMIFS(sum_range, criteria_range1, criteria1, ...) | Tính tổng theo nhiều điều kiện | =SUMIFS(D:D, C:C, "Văn phòng", E:E, ">20000") |
=SUMPRODUCT((condition1)(condition2), sum_range) | Tính tổng theo điều kiện phức tạp | =SUMPRODUCT((C:C="Văn phòng")(E:E>20000), D:D) |
=Ide | Tính tổng trong dữ liệu đã lọc | =SUBTOTAL(9, D:D) |
=SUM(IF(...)) (mảng) | Tính tổng theo điều kiện trong mảng | =SUM(IF((C:C="Văn phòng")(E:E>20000), D:D, 0)) |
17. Tài liệu tham khảo
- Trang chủ Microsoft Excel – Hàm số: https://support.microsoft.com/vi-vn/excel
- Exceljet – Sumif & Sumifs cheat sheet: https://exceljet.net/formulas/sumif-sumifs
- Chuyên mục VBA – Sumif in VBA: https://www.mrexcel.com/learn/excel-vba-programোৰে
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ẻ.