Chuyển tới nội dung chính

Bài 6: Nhóm Hàm Thống Kê Theo Điều Kiện

Giai đoạn 2 – Trung cấp · Mục tiêu: Thành thạo COUNTIF(S), SUMIF(S), AVERAGEIF(S) để lập báo cáo tổng hợp nhanh từ dữ liệu thô.

1. Tổng quan​

Nhóm hàm thống kê có điều kiện cho phép bạn đếm, tính tổng, tính trung bình chỉ với những bản ghi thỏa mãn điều kiện nhất định – cực kỳ hữu ích khi lập báo cáo từ dữ liệu lớn.

HàmChức năngSố điều kiện
COUNTIFĐếm ô thỏa điều kiện1
COUNTIFSĐếm ô thỏa nhiều điều kiệnNhiều
SUMIFTính tổng thỏa điều kiện1
SUMIFSTính tổng thỏa nhiều điều kiệnNhiều
AVERAGEIFTrung bình thỏa điều kiện1
AVERAGEIFSTrung bình thỏa nhiều điều kiệnNhiều

2. Hàm COUNTIF & COUNTIFS​

COUNTIF – Đếm theo 1 điều kiện​

=COUNTIF(vùng_điều_kiện, điều_kiện)

Ví dụ:

=COUNTIF(C2:C100, "Hà Nội")           → Đếm nhân viên ở Hà Nội
=COUNTIF(D2:D100, ">5000000")         → Đếm lương > 5 triệu
=COUNTIF(B2:B100, "Nam")              → Đếm nhân viên nam

COUNTIFS – Đếm theo nhiều điều kiện​

=COUNTIFS(vùng_ĐK1, ĐK1, vùng_ĐK2, ĐK2, ...)

Ví dụ:

=COUNTIFS(C2:C100, "Hà Nội", B2:B100, "Nam")
→ Đếm nhân viên nam ở Hà Nội

3. Hàm SUMIF & SUMIFS​

SUMIF – Tính tổng theo 1 điều kiện​

=SUMIF(vùng_điều_kiện, điều_kiện, vùng_tính_tổng)

Ví dụ:

=SUMIF(A2:A100, "Bút bi", D2:D100)
→ Tổng doanh thu của sản phẩm "Bút bi"

:::info Lưu ý thứ tự tham số

  • SUMIF: vùng ĐK → ĐK → vùng tổng
  • SUMIFS: vùng tổng → vùng ĐK1 → ĐK1 → vùng ĐK2 → ĐK2 (thứ tự khác nhau!) :::

SUMIFS – Tính tổng theo nhiều điều kiện​

=SUMIFS(vùng_tính_tổng, vùng_ĐK1, ĐK1, vùng_ĐK2, ĐK2, ...)

Ví dụ:

=SUMIFS(D2:D100, A2:A100, "Bút bi", C2:C100, "Tháng 6")
→ Tổng doanh thu "Bút bi" trong "Tháng 6"

4. Hàm AVERAGEIF & AVERAGEIFS​

AVERAGEIF​

=AVERAGEIF(vùng_điều_kiện, điều_kiện, vùng_tính_TB)

AVERAGEIFS​

=AVERAGEIFS(vùng_tính_TB, vùng_ĐK1, ĐK1, vùng_ĐK2, ĐK2, ...)

Ví dụ:

=AVERAGEIF(B2:B50, "Phòng Kinh Doanh", E2:E50)
→ Lương trung bình của Phòng Kinh Doanh

=AVERAGEIFS(E2:E50, B2:B50, "Phòng KD", C2:C50, ">=2020")
→ Lương TB phòng KD, nhân viên vào từ 2020 trở đi

5. Ký tự đại diện (Wildcard) trong điều kiện​

Ký tựÝ nghĩaVí dụ
*Bất kỳ chuỗi ký tự nào"*phone" → iPhone, Smartphone
?Đúng 1 ký tự bất kỳ"B?t" → Bút, Bật, Bít
~Tìm chính ký tự * hoặc ?"~*" → tìm dấu *
=COUNTIF(A2:A100, "*Nguyễn*")    → Đếm tên có chứa "Nguyễn"
=SUMIF(A2:A100, "SP-???", D2:D100) → Tổng sản phẩm có mã 2 ký tự sau "SP-"

Bài tập thực hành​

Cho bảng Nhật ký bán hàng gồm: Mã SP, Tên SP, Nhân viên, Khu vực, Số lượng, Đơn giá, Thành tiền.

  1. Đếm số đơn hàng của nhân viên "Trần Văn B".
  2. Tổng doanh thu khu vực "Miền Nam".
  3. Doanh thu trung bình của sản phẩm "Laptop" tại "Miền Bắc".
  4. Đếm số đơn hàng có thành tiền > 10 triệu của khu vực "Miền Trung".

Bài trước: Bài 5 – Hàm Logic · Bài tiếp: Bài 7 – Hàm Tìm Kiếm & Tham Chiếu