Hàm nâng cao trong Excel

1. Cấu trúc: =SUMIF(vùng_điều_kiện; điều_kiện; [vùng_tính_tổng]). Công dụng: cộng các giá trị ứng với những dòng thoả 1 điều kiện.
2. Nhấn vào ô kết quả, ví dụ J4, rồi gõ =SUMIF( .
3. Quét vùng điều kiện E4:E11 (cột Khu vực) và nhấn F4 để cố định thành $E$4:$E$11.
4. Gõ ; rồi nhập điều kiện "Miền Bắc" (hoặc nhấn vào ô chứa chữ Miền Bắc để lấy tham chiếu).
5. Gõ ; rồi quét vùng tính tổng G4:G11 (cột Số lượng), nhấn F4 để cố định.
6. Đóng ngoặc ) và nhấn Enter — Excel trả về tổng số lượng của khu vực đó.
7. Điều kiện so sánh phải để trong nháy kép: =SUMIF($F$4:$F$11;">2000000";$G$4:$G$11).
8. Dùng ký tự đại diện để cộng theo phần đầu tên: =SUMIF($C$4:$C$11;"Máy*";$G$4:$G$11).
9. Nếu bỏ trống đối số thứ ba, Excel sẽ cộng chính vùng điều kiện.

💡 Nhớ thứ tự: SUMIF viết VÙNG ĐIỀU KIỆN trước, vùng cần cộng sau cùng.

1. Cấu trúc: =SUMIFS(vùng_tính_tổng; vùng_đk1; đk1; [vùng_đk2; đk2]…). Có thể tới 127 cặp điều kiện.
2. Nhấn ô kết quả và gõ =SUMIFS( .
3. Quét NGAY vùng cần cộng G4:G11 và nhấn F4 — khác với SUMIF, vùng tính tổng đứng ĐẦU TIÊN.
4. Gõ ; quét vùng điều kiện 1 là D4:D11, nhấn F4, gõ ; rồi nhập "Nội thất".
5. Gõ ; quét vùng điều kiện 2 là E4:E11, nhấn F4, gõ ; rồi nhập "Miền Bắc".
6. Đóng ngoặc ) và nhấn Enter — kết quả là tổng của các dòng thoả CẢ HAI điều kiện.
7. Kết hợp điều kiện số: thêm cặp $F$4:$F$11;">=1000000".
8. So sánh với một ô nhập liệu phải nối chuỗi: =SUMIFS($G$4:$G$11;$F$4:$F$11;">="&$K$4).
9. Điều kiện theo khoảng ngày: dùng hai cặp ">="&ngày_đầu và "<="&ngày_cuối trên cùng một cột ngày.

💡 SUMIFS luôn là quan hệ VÀ (tất cả điều kiện cùng đúng). Muốn quan hệ HOẶC, cộng nhiều SUMIFS lại với nhau.

1. Cấu trúc: =COUNTIF(vùng_điều_kiện; điều_kiện). Công dụng: đếm số ô thoả 1 điều kiện.
2. Nhấn ô kết quả và gõ =COUNTIF( .
3. Quét vùng cần đếm D4:D11 (cột Nhóm hàng) và nhấn F4 để cố định.
4. Gõ ; rồi nhập "Nội thất", đóng ngoặc ) và nhấn Enter.
5. Đếm theo số: =COUNTIF($G$4:$G$11;">=10") — đếm các đơn có số lượng từ 10 trở lên.
6. Đếm gần đúng theo ký tự đại diện: =COUNTIF($C$4:$C$11;"*bàn*").
7. Tìm dữ liệu trùng: =COUNTIF($B$4:$B$11;B4)>1 trả về TRUE nếu mã đơn bị trùng.
8. Đếm ô trống dùng COUNTBLANK, đếm ô không trống dùng COUNTA.

💡 COUNTIF không phân biệt chữ HOA và chữ thường: "nội thất" và "Nội Thất" được đếm như nhau.

1. Cấu trúc: =COUNTIFS(vùng_đk1; đk1; [vùng_đk2; đk2]…). Không có vùng đếm riêng vì chính các vùng điều kiện được đếm.
2. Nhấn ô kết quả và gõ =COUNTIFS( .
3. Quét vùng điều kiện 1 là E4:E11, nhấn F4, gõ ; rồi nhập "Miền Bắc".
4. Gõ ; quét vùng điều kiện 2 là G4:G11, nhấn F4, gõ ; rồi nhập ">=10".
5. Đóng ngoặc ) và nhấn Enter — kết quả là số đơn thoả cả hai điều kiện.
6. Các vùng điều kiện bắt buộc phải CÙNG SỐ DÒNG, nếu lệch nhau Excel báo lỗi #VALUE!.
7. Đếm trong khoảng: =COUNTIFS($F$4:$F$11;">=1000000";$F$4:$F$11;"<=3000000").
8. Lập bảng thống kê chéo: đặt Nhóm hàng theo dòng, Khu vực theo cột rồi kéo COUNTIFS với địa chỉ hỗn hợp $D4 và E$3.

💡 Muốn thống kê nhanh nhiều chiều mà không viết nhiều công thức, hãy cân nhắc dùng PivotTable.

1. Cấu trúc: =AVERAGEIF(vùng_điều_kiện; điều_kiện; [vùng_tính_trung_bình]).
2. Nhấn ô kết quả và gõ =AVERAGEIF( .
3. Quét vùng điều kiện D4:D11 (cột Nhóm hàng) và nhấn F4.
4. Gõ ; rồi nhập "Thiết bị".
5. Gõ ; quét vùng tính trung bình F4:F11 (cột Đơn giá), nhấn F4, đóng ngoặc ) và nhấn Enter.
6. Làm tròn kết quả cho gọn: =ROUND(AVERAGEIF($D$4:$D$11;"Thiết bị";$F$4:$F$11);0).
7. Trung bình có điều kiện số: =AVERAGEIF($G$4:$G$11;">5";$F$4:$F$11).
8. Nếu không có dòng nào thoả điều kiện, Excel báo lỗi #DIV/0! — hãy bọc IFERROR để hiện 0 hoặc dòng chữ thân thiện.

💡 AVERAGEIF bỏ qua ô trống và ô chứa chữ trong vùng tính trung bình, nhưng KHÔNG bỏ qua số 0.

1. Cấu trúc: =AVERAGEIFS(vùng_tính_TB; vùng_đk1; đk1; [vùng_đk2; đk2]…).
2. Nhấn ô kết quả và gõ =AVERAGEIFS( .
3. Quét NGAY vùng tính trung bình F4:F11 và nhấn F4 — giống SUMIFS, vùng tính đứng đầu.
4. Gõ ; quét vùng điều kiện 1 là D4:D11, nhấn F4, gõ ; rồi nhập "Nội thất".
5. Gõ ; quét vùng điều kiện 2 là E4:E11, nhấn F4, gõ ; rồi nhập "Miền Nam".
6. Đóng ngoặc ) và nhấn Enter.
7. Thêm điều kiện số nếu cần: cặp $G$4:$G$11;">=5".
8. Bọc IFERROR để tránh lỗi #DIV/0!: =IFERROR(AVERAGEIFS(…);"Chưa có dữ liệu").

💡 Ba hàm SUMIFS, COUNTIFS, AVERAGEIFS dùng cùng một kiểu đối số — học thuộc một hàm là dùng được cả ba.

1. Cấu trúc: =IFS(điều_kiện1; kết_quả1; điều_kiện2; kết_quả2; …; TRUE; kết_quả_mặc_định).
2. Excel xét các điều kiện lần lượt từ TRÁI sang PHẢI và dừng ngay khi gặp điều kiện đúng đầu tiên.
3. Nhấn ô kết quả và gõ =IFS( .
4. Nhập cặp đầu tiên: F4>=5000000; "Lớn".
5. Gõ ; rồi nhập cặp tiếp theo: F4>=2000000; "Vừa".
6. Gõ ; rồi nhập cặp cuối làm mặc định: TRUE; "Nhỏ". Đóng ngoặc ) và nhấn Enter.
7. Kéo Fill Handle xuống hết cột để xếp hạng cho toàn bộ danh sách.
8. Thứ tự điều kiện rất quan trọng: phải xếp từ mức CAO xuống mức THẤP, nếu ngược lại mọi dòng sẽ rơi vào mức đầu tiên.
9. Nếu không dòng nào thoả và không có TRUE ở cuối, Excel trả về lỗi #N/A.

💡 IFS có từ Excel 2019 / Microsoft 365. Máy Excel 2016 hãy viết IF lồng: =IF(F4>=5000000;"Lớn";IF(F4>=2000000;"Vừa";"Nhỏ")).

1. Cấu trúc: =SUBTOTAL(mã_hàm; vùng_dữ_liệu). Mã hàm quyết định phép tính: 9 hoặc 109 là SUM, 1 hoặc 101 là AVERAGE, 3 hoặc 103 là COUNTA…
2. Vào thẻ Data, nhấn Filter để bật bộ lọc cho bảng dữ liệu.
3. Nhấn ô tổng cuối bảng và gõ =SUBTOTAL(109; rồi quét vùng G4:G11, đóng ngoặc ) và nhấn Enter.
4. Lọc thử theo Khu vực = Miền Bắc — kết quả SUBTOTAL tự đổi theo phần đang hiển thị, trong khi SUM vẫn giữ nguyên tổng cũ.
5. Phân biệt: mã 1–11 vẫn tính các dòng bị ẩn thủ công, mã 101–111 bỏ qua các dòng bị ẩn.
6. SUBTOTAL luôn bỏ qua các ô SUBTOTAL khác trong vùng nên không bị cộng trùng khi có nhiều dòng tổng nhỏ.
7. Đánh số thứ tự tự động theo bộ lọc: =SUBTOTAL(103;$C$4:C4) đặt ở cột STT.
8. Cách khác: chọn bảng rồi vào Data > Subtotal để Excel tự chèn dòng tổng nhỏ theo từng nhóm.

💡 Khi làm báo cáo có bộ lọc, hãy dùng SUBTOTAL thay cho SUM để dòng tổng luôn khớp với phần dữ liệu đang xem.