Công nghệ số & AI – Buổi 8: Excel nâng cao, Subtotal và PivotTable

Hàm INT, MOD, ngày giờ, INDEX và MATCH, SUMIF với ký tự đại diện, AutoFilter, Advanced Filter, Subtotal và PivotTable, kèm video hướng dẫn.

Công nghệ số & AI – Buổi 8: Excel nâng cao, Subtotal và PivotTable
Nội dung này có sự hỗ trợ của công cụ trí tuệ nhân tạo (AI).

Mục tiêu buổi 8: dùng các hàm xử lý số và ngày giờ (INT, MOD, YEAR, TODAY, HOUR, MINUTE), tra cứu linh hoạt bằng INDEX + MATCH, thống kê bằng SUMIF, COUNTIF có ký tự đại diện; lọc dữ liệu (AutoFilter, Advanced Filter, FILTER), tổng hợp bằng Subtotal, lập báo cáo bằng PivotTable và biểu đồ.

Video hướng dẫn

Bài thực hành 8, bài tự luyện 1: HLOOKUP và VLOOKUP
▶ Video: Bài thực hành 8, bài tự luyện 1: HLOOKUP và VLOOKUP
Xử lý ngày tháng (Date) trong Excel
▶ Video: Xử lý ngày tháng (Date) trong Excel
Hàm và lệnh Subtotal
▶ Video: Hàm và lệnh Subtotal

1. Hàm số học và ngày giờ

HàmCông dụngVí dụ
INTLấy phần nguyênSố tuần: =INT((E3-D3)/7)
MODLấy phần dưSố ngày lẻ: =MOD(E3-D3,7)
YEAR, MONTH, DAYTách năm, tháng, ngày=YEAR(E3)
TODAYNgày hiện tạiTuổi: =YEAR(TODAY())-YEAR(E3)
HOUR, MINUTETách giờ, phút=HOUR(D3-C3), =MINUTE(D3-C3)
  • Excel lưu ngày là số (số ngày tính từ 1/1/1900), nên trừ hai ngày ra số ngày. Số ngày gửi tính cả ngày đầu: =E3-D3+1.
  • Ngày nhập sai định dạng (bị căn trái, không tính được) thường do Region của máy là MM/dd/yyyy. Kiểm tra lại cài đặt hoặc nhập bằng hàm =DATE(2025,2,16).

2. INDEX và MATCH

  • =MATCH(giá trị, vùng 1 cột/dòng, 0) trả về vị trí. Ví dụ =MATCH("P003",$C$17:$C$21,0) → 3.
  • =INDEX(vùng, số dòng, [số cột]) trả về giá trị tại vị trí đó.
  • Kết hợp: =INDEX($D$17:$D$21,MATCH(C3,$C$17:$C$21,0)).
  • Tra cứu 2 chiều (dòng theo tên, cột theo nhà cung cấp):
    =INDEX($C$11:$D$12,MATCH(D3,$B$11:$B$12,0),MATCH(E3,$C$10:$D$10,0))

Ưu điểm so với VLOOKUP: cột tìm không bắt buộc ở bên trái, chèn thêm cột vào bảng tra không làm sai công thức.

3. Thống kê có điều kiện

  • Ký tự đại diện: * thay cho nhiều ký tự, ? thay 1 ký tự.
  • Doanh thu máy số 01: =SUMIF($A$4:$A$11,"01*",$F$4:$F$11), hoặc ghép ô: =SUMIF($A$4:$A$11,F17&"*",$F$4:$F$11).
  • Số lần thuê: =COUNTIF($A$4:$A$11,F17&"*").
  • Nhiều điều kiện: SUMIFS, COUNTIFS, ví dụ =COUNTIFS(F3:F11,"Kinh",G3:G11,"Nam").

4. Lọc dữ liệu

CáchDùng khiThao tác
AutoFilterLọc nhanh, xem tạmData → Filter → bấm mũi tên ở tiêu đề cột
Advanced FilterLọc ra vị trí mới theo vùng điều kiệnTạo vùng điều kiện (tiêu đề + giá trị) → Data → Advanced → Copy to another location
FILTER (Excel 2021/365)Lọc bằng công thức, tự cập nhật=FILTER(A3:L11,(F3:F11="Kinh")*(YEAR(E3:E11)<2004),"Không có")

Vùng điều kiện Advanced Filter: điều kiện cùng dòng là "và", khác dòng là "hoặc".

5. Subtotal: tổng hợp theo nhóm

  1. Sắp xếp bảng theo cột cần nhóm trước (ví dụ Loại phòng, Hình thức thuê). Bỏ bước này Subtotal sẽ tách thành nhiều nhóm lặp.
  2. Chọn bảng → Data → Subtotal: At each change in (cột nhóm), Use function (Sum, Count, Average...), Add subtotal to (cột cần tính).
  3. Dùng nút 1, 2, 3 bên trái để thu gọn/mở rộng. Bỏ: Data → Subtotal → Remove All.

Hàm =SUBTOTAL(9,F3:F20) tính tổng và bỏ qua các dòng đang bị lọc ẩn, khác với SUM.

6. PivotTable: báo cáo trong vài cú kéo thả

  1. Chọn một ô trong bảng → Insert → PivotTable → New Worksheet.
  2. Kéo trường vào 4 vùng: Rows (nhóm theo, ví dụ Ngành), Values (giá trị tính, ví dụ Tổng điểm), Columns, Filters.
  3. Đổi cách tính: bấm trường trong Values → Value Field Settings → Average, Max, Count...
  4. Dữ liệu gốc thay đổi: chuột phải PivotTable → Refresh.
  5. Biểu đồ: PivotTable Analyze → PivotChart.

Ví dụ báo cáo: điểm trung bình mỗi ngành, số thí sinh mỗi dân tộc, điểm cao nhất mỗi ngành, số Nam và Nữ.

Bài thực hành

  • Xem tại bài thực hành đã in và file PDF giảng viên cung cấp.

Buổi tiếp theo: làm việc nhóm và khảo sát trực tuyến với Google Drive, Google Forms.