Trong môi trường làm việc hiện đại, Microsoft Excel đã trở thành công cụ không thể thiếu để phân tích dữ liệu, lập kế hoạch tài chính và đưa ra quyết định kinh doanh. Một trong những tính năng mạnh mẽ nhất用 trong Excel là khả năng tính toán “thành tiền” có điều kiện – tức là tính tổng, trung bình, đếm các giá trị đáp ứng một hoặc nhiều tiêu chí nhất định manche. Ở bài viết này, chúng ta sẽ đi sâu vào cách thực hiện những tính toán này bằng cách sử dụng các hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, cũng như một số phương pháp nâng cao khác như PivotTable, dynamic arrays và thậm chí là một số đoạn mã VBA đơn giản.
Có thể bạn quan tâm: Cách Đánh Dấu Tích Vào Ô Vuông Trong Excel 2026
1. Tại sao cần tính thành tiền có điều kiện?
Trong các báo cáo tài chính, bảng kê doanh thu, bảng chi phí hay bảng phân tích dữ liệu bán hàng, người dùng thường njeng muốn:
– Tính tổng doanh thu của một vùng miền cụ thể.
– Đếm số đơn hàng đạt mức doanh thu trên 10 triệu.
– Tính trung bình giá bán của một loại sản phẩm trong một thời gian nhất định.
Những câu trả lời này không thể được trả về bằng cách tính tổng toàn bộ dữ liệu mà không phân loại. Đó chính là lý do tại sao Excel cung cấp các hàm tính có điều kiện.
Có thể bạn quan tâm: Tìm Tên Trùng Nhau Trong Danh Sách Excel
2. Giới thiệu các hàm SUMIF và SUMIFS
2.1 Hàm SUMIF – tổng một vùng dữ liệu theo một điều kiện đơn giản
Cú pháp:
SUMIF(range, criteria, sum_range)
range– vùng dữ liệu cần kiểm tra điều kiện.criteria– điều kiện (có thể là số, chuỗi, biểu thức, biểu tượng hoặc ?).sum_range– vùng dữ liệu thực sự sẽ được cộng (nếu bỏ qua, mặc định làrange).
Ví dụ thực tế
Giả sử chúng ta có bảng dữ liệu bán hàng (A1:C10) với cột A là “Khách hàng”, B là “Sản phẩm” và C là “Doanh thu”. Nếu muốn tính tổng doanh thu của khách hàng “Nguyễn Văn A”:
=SUMIF(A2:A10,"Nguyễn Văn A",C2:C10)
2.2 Hàm SUMIFS – tổng dữ liệu dựa trên nhiều điều kiện
Cú pháp:
SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
sum_range– vùng dữ liệu thực sự cộng.criteria_rangeX– vùng dữ liệu kiểm tra điều kiện.criteriaX– điều kiện tương).
Ví dụ thực tế
Tính tổng doanh thu của khách hàng “Nguyễn Văn A” bán sản phẩm “Sản phẩm X”:
=SUMIFS(C2:C10, A2:A10, "Nguyễn Văn A", B2:B10, "Sản phẩm X")
Có thể bạn quan tâm: Tìm Số Còn Thiếu Trong Dãy Số Excel: Hướng Dẫn Chi Tiết, Công Thức Và Thủ Thuật
3. Các hàm COUNTIF và COUNTIFS – đếm số lượng có điều kiện
3.1 Hàm COUNTIF
Cú pháp:
COUNTIF(range, criteria)
Ví dụ
Đếm số đơn hàng có doanh thu lớn hơn 5 triệu:
=COUNTIF(C2:C10, ">5000000")
3.2 Hàm COUNTIFS
Cú pháp:
COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
Ví dụ
Đếm số đơn hàng của khách hàng “Nguyễn Văn A” có doanh thu자가 5 triệu:
=COUNTIFS(A2:A10, "Nguyễn Văn A", C2:C10, ">5000000")
4. Hàm AVERAGEIF và AVERAGEIFS – tính trung bình có điều kiện
4.1 AVERAGEIF
Cú pháp:
AVERAGEIF(range, criteria, average_range)
Ví dụ
Tính doanh thu trung bình của khách hàng “Nguyễn Văn A”:
=AVERAGEIF(A2:A10, "Nguyễn Văn A", C2:C10)
4.2 AVERAGEIFS
Cú pháp:
AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Ví dụ
Tính trung bình doanh thu của khách hàng “Nguyễn Văn A” bán “Sản phẩm X”:
=AVERAGEIFS(C2:C10, A2:A10, "Nguyễn Văn A", B2:B10, "Sản phẩm X")
5. Thực hành với dữ liệu thực tế
Để minh họa, ta sẽ xây dựng một bảng dữ liệu mẫu gồm 4 cột:
– Ngày (A)
– Khách hàng (B)
– Sản phẩm (C)
– Doanh thu (D)
| Ngày | Khách hàng | Sản phẩm | Doanh thu |
|---|---|---|---|
| 01/01/2026 | Nguyễn Văn A | Sản phẩm X | 5,000,000 |
| 02/01/2026 | Nguyễn Văn B | Sản phẩm Y | 3,000,000 |
| 03/01/2026 | Nguyễn Văn A | Sản phẩm X | 7,000,000 |
| 04/01/2026 | Nguyễn Văn C | Sản phẩm Z | 4,500,000 |
| 05/01/2026 | Nguyễn Văn A | Sản phẩm Y | 2,000,000 |
| 06/01/2026 | Nguyễn Văn B | Sản phẩm X | 6,000,000 |
| 07/01/2026 | Nguyễn Văn rile | Sản phẩm Z | 8,000,000 |
| 08/01/2026 | Nguyễn Văn A | Sản phẩm(cap) | 9,500,000 |
| chạy |
5.1 Tính tổng doanh thu theo tháng
Giả sử ta muốn tính tổng doanh thu của tháng 01/2026. Ta có thể dùng hàm SUMIFS với điều kiện ngày trong tháng:
=SUMIFS(D2:D9, A2:A9, ">=01/01/2026", A2:A9, "<=31/01/2026")
5.2 Đếm số đơn hàng đạt mức doanh thu nhất định
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
Đếm số đơn hàng có doanh thu >= 5 triệu:
=COUNTIF(D2:D9, ">=5000000")
5.3 Tính doanh thu trung bình của một sản phẩm
=AVERAGEIF(C2:C9, "Sản phẩm X", D2:D9)
6. Sử dụng hàm FILTER và SUM để tính tổng có điều kiện (Dynamic Arrays)
Khi Excel cung cấp tính năng dynamic arrays (Excel 365), ta có thể tận dụng hàm FILTER để lọc dữ liệu và sau đó tính tổng. Đây là một cách linh hoạt và trực quan hơn so với SUMIFS.
Ví dụ
Tính tổng doanh thu của khách hàng “Nguyễn Văn A”:
=SUM(FILTER(D2:D9, B2:B9="Nguyễn Văn A"))
FILTER(D2:D9, B2:B9="Nguyễn Văn A")sẽ trả về một mảng các giá trị doanh thu của khách hàng này.SUM(...)sẽ tính tổng của mảng đó.
6.1 Lọc nhiều điều kiện
Để lọc nhiều điều kiện, ta có thể kết hợp các điều kiện bằng toán tử (AND) hoặc + (OR). Ví dụ, tính tổng doanh thu của khách hàng “Nguyễn Văn A” bán “Sản phẩm X”:
=SUM(FILTER(D2:D9, (B2:B9="Nguyễn Văn A") (C2:C9="Sản phẩm X")))
6.2 Lọc theo ngày
Tính tổng doanh thu trong một khoảng thời gian:
=SUM(FILTER(D2:D9, (A2:A9>=DATE(2026,1,1)) (A2:A9<=DATE(2026,1,31))))
7. Tạo báo cáo bằng PivotTable
PivotTable là một công cụ mạnh mẽ để phân tích dữ liệu có điều kiện mà không cần viết công thức. Hãy làm theo các bước sau:
- Chọn dữ liệu: Bảng dữ liệu từ A1:D9.
- Insert > PivotTable.
- Chọn bảng nguồn và đặt PivotTable ở một trang tính mới.
- Kéo các trường vào khu vực Rows, Columns, Values, Filters:
- Kéo “Khách hàng” vào Rows.
- Kéo “Sản phẩm” vào Columns.
- Kéo “Doanh thu” vào Values (đặt làm SUM).
- Nếu muốn lọc theo ngày, kéo “Ngày” vào Filters và chọn khoảng ngày mong muốn.
PivotTable sẽ tự động tính tổng, đếm, trung bình theo các tiêu chí đã đặt. Bạn có thể dễ dàng thay đổi cấu trúc, thêm các điều kiện lọc, hoặc sử dụng tính năng “Value Field Settings” để tính trung bình, tối đa, tối thiểu, v.v.
8. Sử dụng VBA để tính tổng có điều kiện
Đôi khi, bạn muốn tính toán trên một tập dữ liệu lớn hoặc thực hiện nhiều bước tự động. VBA (Visual Basic for Applications) là một công cụ mạnh mẽ. Dưới đây là một ví dụ đơn giản:
Function TinhTongCoDieuKien(rng As Range, condCol As Range, condVal As String, sumCol As Range) As Double Dim i As Long Dim result As Double result = 0 For i = 1 To rng.Rows.Count If condCol.Cells(i, 1).Value = condVal Then result = result + sumCol.Cells(i, 1).Value End If Next i TinhTongCoDieuKien = result
End Function
Bạn có thể gọi hàmarchie trong Excel như:
=TinhTongCoDieuKien(A2:D9, B2:B9, "Nguyễn Văn A", D2:D9)
9. Thực hành nâng cao: Kết hợp hàm IF + SUMPRODUCT
Khi không muốn sử dụng SUMIFS, bạn có thể dùng SUMPRODUCT để thực hiện các điều kiện phức tạp:
=SUMPRODUCT((A2:A9="Nguyễn Văn A")(B2:B9="Sản phẩm X")D2:D9)
(A2:A9="Nguyễn Văn A")trả về mảng 1/0.làm AND.- Cuối cùng nhân với giá trị doanh thu và tính tổng.
9.1 Ví dụ: Đếm số đơn hàng có doanh thu > 5 triệu và giảm giá < 10%
=SUMPRODUCT((D2:D bane >= 5000000)(E2:E9<0.1))
10. Lưu ý khi làm việc với dữ liệu lớn
- Chỉ số vùng dữ liệu: Luôn chỉ định một vùng dữ liệu cố định (ví dụ
D2:D1000) thay vì toàn bộ cột (D:D) để tránh mất hiệu suất. - Sử dụng bảng (Table): Khi dữ liệu được định dạng thành “Table” (Insert > Table), các hàm như SUMIFS, COUNTIFS sẽ tự động mở rộng khi thêm dòng mới.
- Kiểm tra lỗi dữ liệu: Đảm bảo dữ liệu nhập vào không chứa lỗi (#N/A, #DIV/0!). Có thể sử dụng hàm IFERROR để tránh lỗi trong tính toán.
11. Kết luận
Tính toán thành tiền có điều kiện trong Excel không chỉ là một kỹ năng hữu ích mà còn là một công cụ quyết định trong công việc phân tích dữ liệu, lập báo cáo vàಕರಣ. Bằng cách nắm vững các hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, cùng với công cụ PivotTable, dynamic arrays và thậm chí là VBA, bạn sẽ có thể xử lý bất kỳ yêu cầu tính toán nào một cách nhanh chóng, chính xác và hiệu quả.
Hãy thử áp dụng những công thức và kỹ thuật đã trình bày vào dữ liệu thực tế của bạn, và bạn sẽ cảm nhận được sự khác biệt rõ rệt trong năng suất làm việc và chất lượng phân tích. Chúc bạn thành công!
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ẻ.