CHƯƠNG 7 MICROSOFT EXCEL NÂNG CAO — HÀM TRA CỨU, TỔNG HỢP DỮ LIỆU, BIỂU ĐỒ VÀ PIVOTTABLE
Mục tiêu chương
Sau khi học xong chương này, người học có thể:
• Tổng hợp dữ liệu theo nhiều điều kiện bằng SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, IFS và các hàm logic lồng nhau.
• Tra cứu dữ liệu giữa các bảng bằng VLOOKUP, INDEX kết hợp MATCH và XLOOKUP; xử lý lỗi tra cứu và chọn đúng hàm cho từng tình huống.
• Làm sạch và chuẩn hóa dữ liệu nhập từ nơi khác (chữ, ngày, số, bản ghi trùng) bằng hàm và công cụ có sẵn.
• Chọn đúng loại biểu đồ, dựng và định dạng biểu đồ đạt yêu cầu trình bày của Trường, đưa sang Word đúng Quy định.
• Tạo PivotTable, Pivot Chart và Slicer để tổng hợp và phân tích nhanh; dùng định dạng có điều kiện, kiểm soát nhập liệu, bảo vệ bảng tính; dựng bảng điều khiển một trang.
| Vị trí của chươngChương 7 nối tiếp Chương 6 (Excel cơ bản): giả định người học đã quen với ô, công thức, địa chỉ tuyệt đối, hàm IF, SUMIF, sắp xếp, lọc và Table. Chương này dùng file Chuong7_DuLieu_Mau.xlsx với dữ liệu giả: bảng chi phí làm phim 120 dòng, bảng điểm 60 người học, bảng thiết bị mượn trả. Mọi hình và bảng rộng không quá 75% bề rộng phần chữ. Công thức trong tài liệu viết với dấu phẩy phân cách đối số; máy cài vùng Việt Nam có thể cần dấu chấm phẩy (xem mục 6.2.2). |
|---|
7.1. Chuẩn bị dữ liệu cho phân tích
7.1.1. Dữ liệu sạch là điều kiện của mọi hàm tổng hợp
Hàm tổng hợp, tra cứu và PivotTable đều giả định dữ liệu có dạng bảng phẳng. Chương 6 đã nêu nguyên tắc; trước khi dùng các công cụ nâng cao, cần kiểm tra lại năm điều:
• Mỗi cột một loại thông tin (ngày, tên hạng mục, số lượng); không trộn hai thông tin trong một cột, như "Máy quay - 3 ngày".
• Mỗi dòng một bản ghi; không dòng trống chen giữa, không dòng tổng cộng nằm lẫn trong dữ liệu.
• Không gộp ô trong vùng dữ liệu; hàng tiêu đề chỉ một dòng, tên cột không trùng nhau.
• Đúng kiểu dữ liệu: ngày là ngày thật (căn phải), số là số thật, mã có số 0 đầu là Text.
• Cùng cách viết cho cùng một giá trị: "Điện ảnh" và "Dien anh" là hai giá trị khác nhau đối với Excel.
7.1.2. Table và tham chiếu có cấu trúc
Chuyển vùng dữ liệu thành Table (Ctrl + T, Chương 6) rồi đặt tên ở tab Table Design > Table Name. Công thức có thể viết theo tên bảng và tên cột thay cho địa chỉ ô. Ví dụ, với bảng tbChiPhi:
Bảng 7.1 — So sánh địa chỉ ô và tham chiếu có cấu trúc
| Cách viết | Ví dụ | Ưu và nhược |
|---|---|---|
| Địa chỉ ô | =SUM(G2:G121) | Ngắn; phải sửa khi thêm dòng, dễ chọn sai vùng |
| Tham chiếu có cấu trúc | =SUM(tbChiPhi[ThanhTien]) | Tự mở rộng khi thêm dòng; đọc hiểu ngay; dài hơn |
| Cột của dòng hiện tại | =[@SoLuong]*[@DonGia] | Công thức giống nhau cho mọi dòng, không cần địa chỉ |
Khi gõ công thức, nhấp chuột chọn cả cột trong Table thì Excel tự viết tên bảng và tên cột. Từ đây trở đi, tài liệu dùng tên bảng khi viết ví dụ để người đọc thấy ngay công thức làm gì.
7.1.3. Bộ dữ liệu mẫu của chương
File Chuong7_DuLieu_Mau.xlsx gồm các trang tính sau (dữ liệu giả; không phải người thật hay số liệu thật). Bảng chi phí có 120 dòng, tổng thành tiền sau khi tính xong là 321.450.000 đồng; con số này dùng để kiểm tra kết quả.
Bảng 7.2 — Các trang tính của file dữ liệu mẫu
| Trang tính | Nội dung | Dùng ở mục |
|---|---|---|
| ChiPhi | 120 dòng chi phí: Ngay, MaCT, HangMuc, SoLuong; ba cột Nhom, DonGia, ThanhTien để trống cho người học | 7.2, 7.3, 7.6, 7.8 |
| DanhMuc | 12 hạng mục: Nhom, DonGia, DVT (bảng để tra cứu) | 7.3 |
| BangDiem | 60 người học ba lớp DA26A, MT26A, SK26A: ChuyenCan, GiuaKy, CuoiKy | 7.2, 7.3, 7.6 |
| Thang, BangGia | Bảng ngưỡng xếp loại; bảng giá thuê thiết bị theo gói 1, 3, 7 ngày | 7.3 |
| NhapLieu | Dữ liệu "bẩn" giả lập nhập từ CSV: họ tên viết lộn xộn, ngày dạng chữ, bản ghi trùng | 7.4 |
| ThietBi, NganSach | Danh sách thiết bị mượn trả; ngân sách theo nhóm chi phí | 7.7, 7.8 |
7.2. Hàm điều kiện nhiều tiêu chí
7.2.1. SUMIFS, COUNTIFS, AVERAGEIFS
Ở Chương 6, SUMIF, COUNTIF, AVERAGEIF chỉ xét một điều kiện. Khi cần nhiều điều kiện cùng đúng, dùng ba hàm có chữ S ở cuối. Điểm khác quan trọng: với SUMIFS và AVERAGEIFS, vùng cần tính đứng đầu, sau đó mới đến các cặp (vùng điều kiện, điều kiện).
Bảng 7.3 — Các hàm điều kiện nhiều tiêu chí
| Hàm | Cú pháp | Công dụng |
|---|---|---|
| SUMIFS | =SUMIFS(vùng_cộng, vùng_ĐK1, ĐK1, vùng_ĐK2, ĐK2, …) | Tổng các ô thỏa mọi điều kiện |
| COUNTIFS | =COUNTIFS(vùng_ĐK1, ĐK1, vùng_ĐK2, ĐK2, …) | Đếm số dòng thỏa mọi điều kiện |
| AVERAGEIFS | =AVERAGEIFS(vùng_TB, vùng_ĐK1, ĐK1, …) | Trung bình các ô thỏa mọi điều kiện |
| MAXIFS, MINIFS | =MAXIFS(vùng_lấy, vùng_ĐK1, ĐK1, …) | Lớn nhất, nhỏ nhất trong các ô thỏa điều kiện (Excel 2019 trở lên) |
Các vùng phải có cùng số dòng. Điều kiện có thể là chữ ("Thiết bị"), số, phép so sánh trong nháy kép (">=40") hoặc ký tự đại diện ("Ăn*" khớp mọi chữ bắt đầu bằng Ăn). Điều kiện ngày ghép phép so sánh với hàm DATE bằng dấu &: ">="&DATE(2026,11,1).
Ví dụ trên bảng chi phí (kết quả kiểm tra bằng số liệu mẫu):
Bảng 7.4 — Ví dụ hàm điều kiện nhiều tiêu chí trên bảng chi phí
| Yêu cầu | Công thức | Kết quả |
|---|---|---|
| Tổng chi nhóm Thiết bị | =SUMIFS(tbChiPhi[ThanhTien],tbChiPhi[Nhom],"Thiết bị") | 73.100.000 |
| Chi nhóm Thiết bị trong tháng 11/2026 | =SUMIFS(tbChiPhi[ThanhTien],tbChiPhi[Nhom],"Thiết bị",tbChiPhi[Ngay],">="&DATE(2026,11,1),tbChiPhi[Ngay],"<="&DATE(2026,11,30)) | 6.700.000 |
| Số dòng chi ăn uống từ 40 suất trở lên | =COUNTIFS(tbChiPhi[HangMuc],"Ăn*",tbChiPhi[SoLuong],">=40") | 13 |
| Trung bình thành tiền một dòng nhóm Hậu kỳ | =AVERAGEIFS(tbChiPhi[ThanhTien],tbChiPhi[Nhom],"Hậu kỳ") | 4.000.000 |
| Dòng chi lớn nhất nhóm Hậu kỳ | =MAXIFS(tbChiPhi[ThanhTien],tbChiPhi[Nhom],"Hậu kỳ") | 6.000.000 |
| Ba lỗi hay gặp với hàm có chữ SCác vùng không cùng số dòng (ví dụ G2:G121 với B2:B120): hàm trả #VALUE!. Dùng tên cột của Table để tránh lệch vùng.Ngày viết trực tiếp trong nháy kép như ">=1/11/2026": kết quả phụ thuộc cách máy hiểu chuỗi ngày. Hãy dùng ">="&DATE(2026,11,1) hoặc tham chiếu đến một ô chứa ngày.Điều kiện chữ có khoảng trắng thừa hoặc khác dấu ("Thiet bi"): hàm trả 0 mà không báo lỗi. Kiểm tra bằng COUNTIFS trước khi tin kết quả SUMIFS. |
|---|
7.2.2. Hàm IF lồng nhau, IFS và các hàm logic
Chương 6 đã dùng IF lồng nhau để xếp loại. Khi có nhiều mức, công thức dài và dễ sai ngoặc. Excel 2019 trở lên có hàm IFS gọn hơn: liệt kê từng cặp (điều kiện, kết quả), Excel dừng ở điều kiện đúng đầu tiên. Điều kiện cuối cùng đặt là TRUE để làm trường hợp còn lại.
Liệt kê 7.1 — So sánh IF lồng nhau và IFS khi xếp loại năm mức
=IF(H2>=9,"Xuất sắc",IF(H2>=8,"Giỏi",IF(H2>=6.5,"Khá",IF(H2>=5,"Trung bình","Yếu"))))
=IFS(H2>=9,"Xuất sắc",H2>=8,"Giỏi",H2>=6.5,"Khá",H2>=5,"Trung bình",TRUE,"Yếu")
Các hàm AND, OR, NOT ghép nhiều điều kiện thành một: AND đúng khi mọi điều kiện đúng, OR đúng khi có ít nhất một điều kiện đúng, NOT đảo kết quả. Ví dụ đánh dấu người học cần hỗ trợ: =IF(OR(H2<5,F2<5),"Cần hỗ trợ","") — tổng kết dưới 5 hoặc giữa kỳ dưới 5.
Kiểm tra trên bảng điểm mẫu: có 8 người Xuất sắc, 8 Giỏi, 25 Khá, 16 Trung bình và 3 Yếu (tổng 60). Nếu bảng của người học ra con số khác, kiểm tra lại thứ tự điều kiện: phải xếp từ cao xuống thấp.
Khi có hơn khoảng bốn mức hoặc ngưỡng hay thay đổi, nên chuyển ngưỡng ra một bảng tra và dùng hàm tra cứu (mục 7.3.2) thay cho IF lồng nhau: sửa bảng, không phải sửa công thức.
7.2.3. IFERROR: bắt lỗi có kiểm soát
IFERROR(biểu thức, giá trị_khi_lỗi) trả về giá trị thay thế khi biểu thức lỗi. Ví dụ =IFERROR(E2/F2,"") cho ô trống thay vì #DIV/0!. Dùng có chừng mực: bọc IFERROR ngoài mọi công thức sẽ giấu cả lỗi thật (sai tên hàm, sai vùng). Quy tắc: viết công thức không bọc, kiểm tra đúng rồi mới thêm IFERROR cho đúng trường hợp lỗi được dự đoán trước, và dùng giá trị thay thế dễ nhận ra như "Không có" thay cho số 0.
7.2.4. SUMPRODUCT: tổng có điều kiện theo kiểu nhân mảng
SUMPRODUCT nhân các mảng cùng kích thước theo từng cặp rồi cộng lại. Có thể thay SUMIFS khi điều kiện phức tạp mà SUMIFS không viết được. Ví dụ tính tổng chi của các dòng chi nhóm Hậu kỳ có số lượng lớn hơn 1: =SUMPRODUCT((tbChiPhi[Nhom]="Hậu kỳ")*(tbChiPhi[SoLuong]>1)*tbChiPhi[ThanhTien]). Mỗi biểu thức trong ngoặc trả về dãy đúng/sai (1/0); phép nhân giữ lại các dòng thỏa mọi điều kiện. Ở mức nhập môn, chỉ cần nhận ra dạng công thức này khi gặp trong bảng tính của người khác.
7.3. Hàm tra cứu
7.3.1. Ý tưởng tra cứu
Tra cứu là việc lấy thông tin từ một bảng khác dựa trên một khóa. Trong bảng chi phí, mỗi dòng chỉ ghi tên hạng mục và số lượng; nhóm chi phí và đơn giá nằm trong bảng DanhMuc. Hàm tra cứu dùng tên hạng mục (khóa tra cứu) để tìm dòng tương ứng trong bảng tra rồi trả về giá trị ở cột kết quả. Cách làm này tránh nhập lại đơn giá ở nhiều nơi: khi giá đổi, chỉ sửa một chỗ trong bảng tra.
7.3.2. VLOOKUP
VLOOKUP(khóa, bảng_tra, số_cột, kiểu_khớp) tìm khóa ở cột đầu tiên của bảng tra rồi trả về giá trị ở cột thứ "số_cột" cùng dòng. Bốn đối số:
• khóa: giá trị cần tìm, thường là một ô (C2).
• bảng_tra: vùng chứa bảng tra, cột khóa nằm ngoài cùng bên trái; cố định bằng địa chỉ tuyệt đối (DanhMuc!$A$2:$D$13).
• số_cột: đếm từ cột đầu tiên của bảng tra (cột khóa là 1).
• kiểu_khớp: FALSE (hoặc 0) khớp chính xác; TRUE (hoặc 1) khớp gần đúng, dùng cho bảng ngưỡng sắp xếp tăng dần.
Bảng 7.5 — Hai cách dùng VLOOKUP
| Mục đích | Công thức | Điều kiện |
|---|---|---|
| Lấy nhóm chi phí theo tên hạng mục | =VLOOKUP(C2,DanhMuc!$A$2:$D$13,2,FALSE) | Khớp chính xác; tên hạng mục phải đúng từng chữ |
| Lấy đơn giá theo tên hạng mục | =VLOOKUP(C2,DanhMuc!$A$2:$D$13,3,FALSE) | Như trên |
| Xếp loại theo ngưỡng điểm | =VLOOKUP(H2,Thang!$A$2:$B$6,2,TRUE) | Khớp gần đúng; cột đầu bảng ngưỡng sắp xếp tăng dần |
Với khớp gần đúng, Excel lấy ngưỡng lớn nhất không vượt quá giá trị tra: bảng Thang có các ngưỡng 0, 5, 6,5, 8, 9 thì điểm 7,4 rơi vào ngưỡng 6,5 và trả "Khá". Đây là cách xếp loại gọn nhất khi có nhiều mức, và thay đổi ngưỡng chỉ cần sửa bảng Thang.
| Hạn chế của VLOOKUPVLOOKUP chỉ tra sang phải (cột khóa phải là cột đầu); "số_cột" bị lệch khi chèn thêm cột vào bảng tra; quên đối số thứ tư thì Excel mặc định khớp gần đúng, cho kết quả sai mà không báo lỗi. Vì vậy luôn viết đủ bốn đối số và ưu tiên hai cách dưới đây khi bảng tra phức tạp. |
|---|
7.3.3. INDEX và MATCH
MATCH(khóa, vùng, 0) trả về vị trí của khóa trong vùng (ví dụ "Thuê bộ đèn" nằm ở vị trí 2). INDEX(vùng, vị_trí) trả về giá trị ở vị trí đó. Ghép lại thành công thức tra cứu: =INDEX(DanhMuc!$C$2:$C$13,MATCH(C2,DanhMuc!$A$2:$A$13,0)). Hai hàm này không buộc khóa phải nằm ở cột đầu, nên tra sang trái được, và không bị lệch khi chèn cột.
Tra cứu hai chiều: trang BangGia có hàng là thiết bị (Máy quay, Đèn, Micro), cột là gói thuê (1 ngày, 3 ngày, 7 ngày). Muốn giá thuê Đèn gói 3 ngày dùng hai MATCH, một theo dòng, một theo cột: =INDEX(BangGia!$B$2:$D$4,MATCH("Đèn",BangGia!$A$2:$A$4,0),MATCH("3 ngày",BangGia!$B$1:$D$1,0)). Kết quả là 3.200.000 đồng.
7.3.4. XLOOKUP (Excel 365)
XLOOKUP(khóa, vùng_tìm, vùng_kết_quả, nếu_không_thấy, kiểu_khớp) là hàm tra cứu hiện đại thay cho VLOOKUP: tra được cả hai phía, không cần số cột, mặc định khớp chính xác, và có sẵn đối số xử lý khi không tìm thấy.
Liệt kê 7.2 — Lấy nhóm chi phí và đơn giá bằng XLOOKUP
=XLOOKUP(C2,DanhMuc!$A$2:$A$13,DanhMuc!$B$2:$B$13,"Không có")
=XLOOKUP(C2,DanhMuc!$A$2:$A$13,DanhMuc!$C$2:$C$13,0)
Hình 7.1 — Hộp thoại Function Arguments của hàm XLOOKUP: các đối số Lookup_value, Lookup_array, Return_array, If_not_found và kết quả xem trước.
Bảng 7.6 — So sánh VLOOKUP, INDEX kết hợp MATCH và XLOOKUP
| Tiêu chí | VLOOKUP | INDEX + MATCH | XLOOKUP |
|---|---|---|---|
| Tra sang trái | Không | Có | Có |
| Khớp mặc định | Gần đúng (dễ sai) | Theo kiểu_khớp trong MATCH | Chính xác |
| Lệch khi chèn cột | Có (số_cột) | Không | Không |
| Xử lý không thấy | Cần IFERROR | Cần IFERROR | Có sẵn đối số thứ tư |
| Phiên bản Excel | Mọi phiên bản | Mọi phiên bản | Excel 2021 và 365 |
Lựa chọn thực tế: dùng XLOOKUP khi mọi máy đều là Excel 365 hoặc 2021; dùng INDEX + MATCH khi cần tương thích máy cũ hoặc tra hai chiều; hiểu VLOOKUP vì có mặt trong rất nhiều bảng tính có sẵn. Đối với Google Sheets, VLOOKUP và INDEX + MATCH dùng được nguyên vẹn, XLOOKUP cũng đã có.
7.3.5. Xử lý lỗi tra cứu
Bảng 7.7 — Nguyên nhân #N/A và cách sửa
| Nguyên nhân | Dấu hiệu | Cách sửa |
|---|---|---|
| Khóa không có trong bảng tra | Tên hạng mục mới hoặc viết sai | Thêm vào bảng tra hoặc sửa tên; dùng đối số "nếu_không_thấy" để hiện thông báo rõ |
| Khoảng trắng thừa | Nhìn giống nhau nhưng không khớp | Dùng TRIM làm sạch cả hai bên (mục 7.4) |
| Kiểu dữ liệu khác nhau | Khóa là số ở bảng này, chữ ở bảng kia | Đổi cùng một kiểu; số lưu dạng chữ có dấu tam giác xanh ở góc ô |
| Bảng tra không cố định | Kéo công thức xuống thì vùng trượt theo | Dùng địa chỉ tuyệt đối $A$2:$D$13 hoặc tên bảng |
| Khớp gần đúng với bảng chưa sắp xếp | Kết quả sai, không báo lỗi | Sắp xếp tăng dần cột khóa của bảng ngưỡng |
7.3.6. Ghép bảng: chi phí tra đơn giá, bảng điểm tra xếp loại
Áp dụng cho file mẫu: trong trang ChiPhi, cột Nhom dùng XLOOKUP hoặc VLOOKUP tra từ DanhMuc; cột DonGia tra tương tự; cột ThanhTien là =[@SoLuong]*[@DonGia]. Sau khi điền xong, tổng cột ThanhTien phải bằng 321.450.000 đồng. Trong trang BangDiem, thêm cột TongKet (=ROUND([@ChuyenCan]*10%+[@GiuaKy]*30%+[@CuoiKy]*60%,1)) và cột XepLoai tra từ bảng Thang bằng khớp gần đúng.
Kiểm tra: điểm tổng kết trung bình của cả 60 người là 7,15; người có tổng kết cao nhất là 9,6 (có 1 người).
7.4. Làm sạch và chuẩn hóa dữ liệu
Dữ liệu đến từ Google Form, tệp CSV, phần mềm khác hay do nhiều người nhập thường lộn xộn. Làm sạch là bước tốn thời gian nhất nhưng quyết định kết quả của mọi hàm và PivotTable phía sau. Trang NhapLieu của file mẫu giả lập một danh sách như vậy.
7.4.1. Hàm văn bản nâng cao
Bảng 7.8 — Các hàm văn bản dùng khi làm sạch dữ liệu
| Hàm | Công dụng | Ví dụ và kết quả |
|---|---|---|
| TRIM | Bỏ khoảng trắng đầu, cuối và rút các khoảng trắng liền nhau còn một | =TRIM(" Lê Văn C ") cho "Lê Văn C" |
| CLEAN | Bỏ ký tự không in được (thường có khi sao chép từ web) | =CLEAN(B2) |
| PROPER, UPPER, LOWER | Viết hoa đầu mỗi từ; chữ hoa; chữ thường | =PROPER(TRIM(B2)) cho "Nguyễn Văn An" |
| SUBSTITUTE | Thay một đoạn chữ bằng đoạn khác | =SUBSTITUTE(D2,".","") bỏ dấu chấm trong số điện thoại |
| LEFT, MID, RIGHT | Lấy n ký tự từ trái, từ giữa, từ phải | =RIGHT(A2,3) lấy 3 ký tự cuối của mã |
| FIND | Vị trí của một đoạn chữ trong chuỗi | =FIND(" ",B2) vị trí khoảng trắng đầu tiên |
| TEXTJOIN | Nối nhiều ô bằng dấu phân cách, bỏ qua ô trống | =TEXTJOIN(", ",TRUE,A2:A4) |
| TEXT | Đổi số hoặc ngày thành chuỗi theo mẫu định dạng | =TEXT(B2,"dd/mm/yyyy") |
Hàm làm sạch thường lồng nhau: =PROPER(TRIM(CLEAN(B2))) bỏ ký tự lạ, rút khoảng trắng rồi viết hoa đầu từ. Kết quả nằm ở cột mới; sau khi kiểm tra, sao chép cột kết quả rồi Paste Special > Values đè lên cột cũ hoặc giữ cả hai để còn đối chiếu.
7.4.2. Hàm ngày giờ
Ngày trong Excel là số (Chương 6), nên cộng trừ được. Các hàm sau giúp tính tiến độ và hạn trả:
Bảng 7.9 — Các hàm ngày giờ thường dùng
| Hàm | Công dụng | Ví dụ và kết quả |
|---|---|---|
| DATE | Tạo ngày từ năm, tháng, ngày | =DATE(2026,11,9) cho ngày 09/11/2026 |
| DATEDIF | Khoảng cách giữa hai ngày theo "Y" (năm), "M" (tháng), "D" (ngày) | =DATEDIF(DATE(2006,3,12),DATE(2026,11,20),"Y") cho 20 |
| EDATE, EOMONTH | Ngày sau n tháng; ngày cuối tháng | =EOMONTH(DATE(2026,11,9),0) cho 30/11/2026 |
| NETWORKDAYS | Số ngày làm việc giữa hai ngày (bỏ thứ Bảy, Chủ nhật, và ngày nghỉ nếu liệt kê) | =NETWORKDAYS(DATE(2026,11,9),DATE(2026,11,16)) cho 6 |
| WORKDAY | Ngày làm việc sau n ngày làm việc | =WORKDAY(DATE(2026,11,9),10) cho 23/11/2026 |
Hàm DATEDIF có sẵn nhưng không hiện gợi ý khi gõ; vẫn gõ đủ tên hàm bình thường. Để tính cả ngày lễ, liệt kê các ngày nghỉ trong một vùng rồi đưa vào đối số cuối của NETWORKDAYS hoặc WORKDAY.
7.4.3. Công cụ có sẵn
• Flash Fill (Ctrl + E): gõ vài ô mẫu cạnh cột dữ liệu, Excel đoán quy luật và điền phần còn lại, ví dụ tách tên từ họ và tên. Kiểm tra kỹ kết quả vì Excel có thể đoán sai khi dữ liệu không đồng nhất.
• Text to Columns (Data > Text to Columns): tách một cột thành nhiều cột theo ký tự phân cách (dấu phẩy, khoảng trắng) hoặc độ rộng cố định; bước ba còn cho chọn kiểu dữ liệu từng cột, dùng để đổi ngày dạng chữ thành ngày thật.
• Remove Duplicates (Data > Remove Duplicates): xóa các dòng trùng theo các cột đã chọn. Luôn làm trên bản sao vì không hoàn tác được sau khi lưu.
• Find and Replace (Ctrl + H): thay hàng loạt; chọn Options để tìm cả định dạng và phân biệt chữ hoa thường.
• Go To Special (F5 > Special): chọn nhanh ô trống, ô có công thức, ô có lỗi để xử lý một lượt.
7.4.4. Quy trình làm sạch một bảng nhập từ CSV
Trang NhapLieu có 8 dòng với bốn lỗi điển hình: họ tên viết hoa thường lộn xộn và thừa khoảng trắng; ngày sinh là chữ dạng "12/03/2006"; số điện thoại mất số 0 đầu ở một số dòng; có 2 dòng trùng mã (còn 6 người khác nhau). Quy trình gợi ý:
1. Sao lưu trang gốc (nhấp phải thẻ trang tính > Move or Copy > Create a copy); mọi thao tác làm trên bản sao.
2. Họ tên: cột mới =PROPER(TRIM(B2)); kiểm tra bằng mắt vài dòng rồi dán giá trị.
3. Ngày sinh: cột mới =DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)), định dạng dd/mm/yyyy. Cách này không phụ thuộc cài đặt ngày của máy.
4. Điện thoại: =IF(LEFT(D2,1)="0",D2,"0"&D2) thêm số 0 bị mất; cột giữ kiểu Text.
5. Trùng lặp: Remove Duplicates theo cột MaSV; so sánh số dòng trước và sau để ghi lại số dòng đã xóa.
6. Ghi lại thay đổi ở một trang GhiChu: đã sửa gì, bao nhiêu dòng, ai sửa, ngày nào.
Bảng 7.10 — Lỗi dữ liệu thường gặp và cách xử lý
| Lỗi | Nhận biết | Cách xử lý |
|---|---|---|
| Khoảng trắng thừa | Tra cứu không khớp dù nhìn giống nhau | TRIM |
| Hoa thường lộn xộn | "TRẦN thị bình" | PROPER hoặc UPPER, LOWER |
| Ngày lưu dạng chữ | Căn trái; sắp xếp theo ngày sai | DATE kết hợp RIGHT, MID, LEFT; hoặc Text to Columns |
| Số lưu dạng chữ | Tam giác xanh ở góc ô; SUM bỏ qua | Convert to Number; hoặc nhân với 1 |
| Mất số 0 đầu | Số điện thoại 9 chữ số | Nối "0" vào; đặt cột dạng Text từ đầu |
| Bản ghi trùng | Hai dòng giống nhau hoàn toàn | Remove Duplicates trên bản sao |
7.5. Biểu đồ
7.5.1. Chọn loại biểu đồ theo câu hỏi cần trả lời
Biểu đồ không phải để trang trí mà để trả lời một câu hỏi. Chọn loại biểu đồ theo câu hỏi đó, không theo kiểu nào trông đẹp.
Bảng 7.11 — Loại biểu đồ và khi nào dùng
| Câu hỏi | Loại biểu đồ | Ví dụ |
|---|---|---|
| So sánh giá trị giữa các nhóm | Cột (Column), thanh ngang (Bar) | Chi phí theo nhóm; thanh ngang khi tên nhóm dài |
| Thay đổi theo thời gian | Đường (Line) | Chi phí theo tháng |
| Tỷ lệ các phần trong một tổng | Tròn (Pie), vành khuyên (Doughnut) | Cơ cấu chi phí; chỉ khi ít hơn khoảng 5 phần |
| Thực tế so với kế hoạch | Cột kết hợp đường (Combo) | Chi thực tế (cột) và ngân sách (đường) |
| Phân bố của một dãy số | Histogram | Phân bố điểm tổng kết |
| Quan hệ giữa hai đại lượng | Phân tán (Scatter) | Điểm giữa kỳ và điểm cuối kỳ |
| Xu hướng nhỏ trong một ô | Sparklines (Insert > Sparklines) | Mini biểu đồ đường cạnh mỗi hàng |
7.5.2. Dựng biểu đồ
1. Chuẩn bị bảng nhỏ gồm hàng tiêu đề và các giá trị cần vẽ (có thể là kết quả SUMIFS hoặc PivotTable). Bảng không có dòng tổng cộng.
2. Chọn cả bảng, vào Insert > Recommended Charts (hoặc chọn nhóm Column, Line, Pie…); Excel gợi ý vài kiểu phù hợp dữ liệu.
3. Biểu đồ xuất hiện cạnh bảng; hai tab Chart Design và Format hiện khi nhấp vào biểu đồ. Chart Design đổi kiểu, đổi màu, thêm thành phần (Add Chart Element); Format chỉnh màu viền, nền, chữ.
4. Nút Switch Row/Column đổi trục khi Excel vẽ ngược ý muốn; nhấp phải biểu đồ > Select Data để thêm bớt chuỗi dữ liệu.
Với dữ liệu mẫu, biểu đồ chi phí theo nhóm cho thấy nhóm lớn nhất là Nhân sự với 76.000.000 đồng, chiếm 23,6% tổng chi 321.450.000 đồng.
Hình 7.2 — Biểu đồ cột kết hợp đường: chi thực tế theo sáu nhóm (cột) và ngân sách (đường), tính bằng triệu đồng; bảng nguồn nằm phía trên biểu đồ.
7.5.3. Các thành phần và định dạng
• Chart Title: tiêu đề nêu thông điệp hoặc nội dung rõ ràng; trong tài liệu Word có thể bỏ, vì đã có chú thích Hình ở dưới (Quy định).
• Axis (trục): trục dọc (giá trị) và trục ngang (nhóm); có thể đặt đơn vị hiển thị là nghìn hoặc triệu đồng để số ngắn gọn.
• Data Labels: ghi giá trị lên cột; chỉ dùng khi ít cột, nếu không sẽ rối.
• Legend (chú giải): đặt phía trên hoặc dưới; bỏ nếu biểu đồ chỉ có một chuỗi.
• Gridlines: đường lưới mờ vừa đủ; bỏ nếu đã có nhãn dữ liệu.
• Sparklines: biểu đồ rất nhỏ nằm trong một ô, cho thấy xu hướng của cả hàng; Insert > Line/Column.
Hình 7.3 — Ngăn Format Chart Area mở bằng Ctrl + 1 khi chọn biểu đồ, với các mục Fill, Border; ba nút bên phải biểu đồ là Chart Elements, Chart Styles và Chart Filters.
7.5.4. Nguyên tắc trình bày biểu đồ
Bảng 7.12 — Các lỗi trình bày biểu đồ gây hiểu lầm
| Lỗi | Hậu quả | Cách đúng |
|---|---|---|
| Trục dọc không bắt đầu từ 0 với biểu đồ cột | Chênh lệch nhỏ trông rất lớn | Đặt Minimum bằng 0 (Format Axis) |
| Dùng biểu đồ 3D, bóng đổ | Cột phía sau bị che, khó đọc giá trị | Dùng 2D, màu phẳng |
| Biểu đồ tròn nhiều phần | Không so sánh được phần nhỏ | Dùng cột ngang xếp giảm dần |
| Quá nhiều màu | Người xem không biết nhìn vào đâu | Một màu chủ đạo; tô nổi duy nhất phần cần nhấn mạnh |
| Thiếu đơn vị, nguồn | Không biết số đo bằng gì, lấy ở đâu | Ghi đơn vị trên trục hoặc tiêu đề; nguồn ở chú thích |
| Chữ quá nhỏ khi in | Đọc không được khi thu nhỏ vào Word | Cỡ chữ trong biểu đồ tối thiểu 10 pt sau khi thu nhỏ |
Quy tắc gọn: một biểu đồ, một thông điệp; người xem phải hiểu thông điệp đó trong vài giây. Màu có chủ đích (một màu cho thực tế, một màu cho kế hoạch), và kiểm tra bằng cách nhìn thử bản in đen trắng.
7.5.5. Đưa biểu đồ sang Word
• Dán dạng ảnh (Paste Special > Picture): kích thước và nét cố định, không thay đổi khi file Excel đổi; phù hợp tài liệu nộp.
• Dán liên kết (Paste > Keep Source Formatting & Link Data): biểu đồ trong Word cập nhật khi dữ liệu Excel đổi; cần giữ cả hai file cùng nhau.
• Kích thước: theo Quy định của Trường, hình rộng không quá 75% bề rộng chữ (khoảng 11,25 cm) và căn giữa.
• Chú thích: đặt dưới biểu đồ bằng References > Insert Caption (nhãn Hình, đánh số theo chương) để danh mục hình tự cập nhật; nguồn số liệu ghi ở cuối chú thích. Xem Chương 4 và 5.
7.6. PivotTable, Pivot Chart và Slicer
7.6.1. PivotTable giải quyết bài toán gì
PivotTable (bảng tổng hợp xoay) tạo bảng tổng kết từ một bảng dữ liệu dài bằng thao tác kéo thả, không cần viết công thức. Cùng một dữ liệu, chỉ cần kéo cột khác vào, bảng tổng hợp đổi theo: tổng chi theo nhóm, theo tháng, theo nhóm trong từng tháng, số dòng, trung bình… Phù hợp khi cần khám phá dữ liệu và thay đổi cách nhìn liên tục.
7.6.2. Tạo PivotTable
1. Chuyển dữ liệu thành Table (có tên tbChiPhi) và đảm bảo cột ThanhTien đã tính xong.
2. Nhấp vào một ô trong bảng, chọn Insert > PivotTable; hộp thoại hỏi nguồn (Table/Range) và nơi đặt (New Worksheet).
3. Ngăn PivotTable Fields hiện ra bên phải. Kéo tên cột vào bốn vùng bên dưới (xem bảng).
4. Nhấp mũi tên cạnh trường ở vùng Values > Value Field Settings để đổi cách tính (Sum, Count, Average…) và định dạng số.
Bảng 7.13 — Bốn vùng của PivotTable
| Vùng | Vai trò | Ví dụ trong bảng chi phí |
|---|---|---|
| Rows | Mỗi giá trị là một dòng của bảng tổng hợp | Nhom |
| Columns | Mỗi giá trị là một cột | Tháng (sau khi nhóm theo ngày) |
| Values | Số được tính toán | Tổng ThanhTien (Sum of ThanhTien) |
| Filters | Lọc cả bảng theo một trường | HangMuc hoặc Nhom |
Kéo Nhom vào Rows và ThanhTien vào Values được bảng tổng hợp sáu nhóm, tổng cộng 321.450.000 đồng (Grand Total), trùng với tổng cột ThanhTien. Kết quả từng nhóm chính là các số SUMIFS ở mục 7.2: nhóm Thiết bị 73.100.000, Nhân sự 76.000.000, Ăn uống 57.450.000, Di chuyển 11.600.000, Hậu kỳ 68.000.000, Khác 35.300.000.
Hình 7.4 — PivotTable chi theo nhóm (Rows) và theo tháng (Columns, nhóm từ trường Ngay); ngăn PivotTable Fields bên phải có bốn vùng Filters, Columns, Rows, Values.
7.6.3. Nhóm dữ liệu và hiển thị theo tỷ lệ
• Nhóm theo ngày: kéo Ngay vào Columns, nhấp phải một ngày > Group > chọn Months (và Years nếu dữ liệu nhiều năm). Với dữ liệu mẫu, tổng chi tháng 10, 11 và 12 lần lượt là 105.400.000, 88.550.000 và 127.500.000 đồng.
• Nhóm theo khoảng số: nhấp phải một giá trị số > Group, nhập Starting at, Ending at, By (ví dụ điểm theo bậc 1 điểm).
• Hiển thị theo tỷ lệ: Value Field Settings > Show Values As > % of Grand Total để thấy mỗi nhóm chiếm bao nhiêu phần trăm tổng chi.
7.6.4. Pivot Chart và Slicer
Pivot Chart (PivotTable Analyze > PivotChart) là biểu đồ gắn với PivotTable: đổi bảng thì biểu đồ đổi theo. Slicer (PivotTable Analyze > Insert Slicer) là bộ nút lọc trực quan: chọn trường (ví dụ Nhom), nhấp các nút để lọc cả bảng và biểu đồ; giữ Ctrl để chọn nhiều nút. Timeline làm việc tương tự nhưng lọc theo ngày. Một Slicer có thể điều khiển nhiều PivotTable nếu bấm Report Connections.
Hình 7.5 — PivotTable kèm Slicer Nhom: ba nút Hậu kỳ, Nhân sự, Thiết bị được chọn nên bảng chỉ còn ba dòng nhóm tương ứng.
7.6.5. Làm mới dữ liệu và các lỗi thường gặp
• PivotTable không tự cập nhật khi dữ liệu nguồn đổi: nhấp phải bảng > Refresh (hoặc Alt + F5; Ctrl + Alt + F5 làm mới tất cả).
• Dữ liệu mới nằm ngoài vùng nguồn: nếu nguồn là vùng thường, thêm dòng ở cuối chưa chắc được tính; dùng Table để nguồn tự mở rộng.
• Giá trị bị đếm (Count) thay vì cộng (Sum): cột có ô trống hoặc số lưu dạng chữ; làm sạch dữ liệu (mục 7.4) rồi đổi lại Sum.
• Ngày không nhóm được: cột có ô trống hoặc có giá trị là chữ.
• Không sửa trực tiếp ô trong PivotTable; muốn đổi số phải sửa dữ liệu nguồn rồi Refresh.
7.6.6. Khi nào dùng PivotTable, khi nào dùng SUMIFS
Bảng 7.14 — PivotTable hay SUMIFS
| Tiêu chí | PivotTable | SUMIFS |
|---|---|---|
| Mục đích | Khám phá, thay đổi cách nhìn nhanh | Báo cáo cố định, bố cục tự thiết kế |
| Cập nhật | Phải Refresh | Tự cập nhật khi dữ liệu đổi |
| Bố cục | Theo khuôn của PivotTable | Tùy ý, nằm trong bảng của người học |
| Độ khó | Thao tác kéo thả, không viết công thức | Phải viết và kiểm tra công thức |
| Dùng cho | Phân tích tạm thời, nhiều chiều | Bảng điều khiển, biểu mẫu nộp cho người khác |
7.7. Định dạng có điều kiện, kiểm soát và bảo vệ
7.7.1. Conditional Formatting
Conditional Formatting (Home > Conditional Formatting) tự đổi màu ô theo giá trị, giúp nhìn ra ngoại lệ trong bảng dài mà không phải đọc từng số. Các nhóm quy tắc:
Bảng 7.15 — Các kiểu định dạng có điều kiện
| Kiểu | Công dụng | Ví dụ |
|---|---|---|
| Highlight Cells Rules | Tô ô lớn hơn, nhỏ hơn, bằng, chứa chữ, trùng lặp | Tô ô TongKet nhỏ hơn 5 |
| Top/Bottom Rules | Tô n giá trị lớn nhất, nhỏ nhất hoặc trên trung bình | Tô 10 dòng chi lớn nhất |
| Data Bars | Thanh ngang trong ô, dài theo giá trị | So sánh nhanh thành tiền |
| Color Scales | Dải màu liên tục từ thấp đến cao | Bản đồ nhiệt của bảng điểm |
| Icon Sets | Biểu tượng (mũi tên, đèn giao thông) theo ngưỡng | Đèn xanh, vàng, đỏ cho tiến độ |
| New Rule > Use a formula | Tô theo công thức bất kỳ, kể cả tô cả dòng | =$H2<5 tô cả dòng người học có tổng kết dưới 5 |
Tô cả dòng bằng công thức: chọn vùng dữ liệu (A2:I61), Conditional Formatting > New Rule > Use a formula to determine which cells to format, nhập =$H2<5 (cột khóa cố định bằng $, dòng không cố định), chọn màu nền. Trên bảng điểm mẫu có 3 người xếp loại Yếu và 6 người thuộc nhóm cần hỗ trợ (tổng kết hoặc giữa kỳ dưới 5). Hộp Manage Rules liệt kê, sửa, đổi thứ tự và xóa quy tắc; đây là nơi phải kiểm tra khi bảng tự nhiên tô màu lạ.
Hình 7.6 — Hộp thoại Conditional Formatting Rules Manager: quy tắc =$H2<5 áp dụng cho vùng $A$2:$I$61, tô cả dòng khi tổng kết dưới 5.
7.7.2. Data Validation nâng cao
• Danh sách từ vùng: Allow > List, ô Source chọn vùng tên hạng mục trong DanhMuc; thêm hạng mục mới ở bảng tra thì danh sách cập nhật nếu nguồn là Table.
• Thông báo nhập và báo lỗi: tab Input Message hiện gợi ý khi chọn ô; tab Error Alert chọn Stop (chặn), Warning hoặc Information.
• Điều kiện bằng công thức: Allow > Custom, ví dụ =COUNTIF($B$2:$B$200,B2)=1 không cho nhập mã trùng.
• Danh sách phụ thuộc: chọn Nhom ở ô này thì ô bên cạnh chỉ hiện các hạng mục của nhóm đó; làm bằng tên vùng và INDIRECT, hoặc hàm mảng động FILTER trong Excel 365.
• Circle Invalid Data (Data > Data Validation > Circle Invalid Data): khoanh đỏ những ô đã có từ trước nhưng vi phạm quy tắc.
7.7.3. Bảo vệ trang tính và ô
Bảo vệ giúp người khác không vô tình sửa nhầm công thức. Mặc định mọi ô đều ở trạng thái Locked, nhưng khóa chỉ có tác dụng khi trang tính được bảo vệ. Quy trình:
1. Chọn các ô cho phép nhập (ví dụ cột SoLuong), Ctrl + 1 > tab Protection > bỏ dấu Locked.
2. Review > Protect Sheet; nhập mật khẩu (có thể để trống) và chọn thao tác vẫn cho phép (chọn ô, định dạng cột, sắp xếp, lọc).
3. Review > Allow Edit Ranges nếu muốn cho từng người sửa một vùng riêng bằng mật khẩu riêng.
4. Protect Workbook (Review > Protect Workbook): chặn thêm, xóa, đổi tên trang tính.
Hình 7.7 — Hộp thoại Protect Sheet: ô mật khẩu (có thể để trống) và danh sách thao tác vẫn cho phép sau khi bảo vệ.
Bảng 7.16 — Các mức bảo vệ và giới hạn của chúng
| Biện pháp | Làm được | Không làm được |
|---|---|---|
| Protect Sheet | Ngăn sửa nhầm công thức, ô khóa | Không phải bảo mật; mật khẩu trang tính có thể bị phá bằng công cụ |
| Protect Workbook (cấu trúc) | Ngăn thêm, xóa, đổi tên trang tính | Không ngăn xem nội dung |
| Encrypt with Password (File > Info) | Mã hóa cả file; không mở được nếu không có mật khẩu | Mất mật khẩu thì mất file; không khôi phục |
| Ẩn dòng, cột, trang tính | Gọn bảng khi in hoặc gửi | Không giấu được; ai cũng bỏ ẩn được |
7.7.4. Goal Seek: tìm giá trị đầu vào để đạt kết quả
Goal Seek (Data > What-If Analysis > Goal Seek) tìm giá trị của một ô đầu vào để một ô công thức đạt kết quả mong muốn. Ba ô điền: Set cell (ô công thức), To value (giá trị cần đạt), By changing cell (ô đầu vào được thay đổi). Ví dụ: một người học có ChuyenCan 8 và GiuaKy 5. Cần điểm CuoiKy bao nhiêu để TongKet đạt đúng 5,0? Đặt Set cell là ô TongKet, To value 5, By changing cell là ô CuoiKy; Excel trả kết quả 4,5 (kiểm tra: 8×10% + 5×30% + 4,5×60% = 0,8 + 1,5 + 2,7 = 5,0). Goal Seek chỉ đổi một ô và ô đầu vào phải là số, không phải công thức.
7.7.5. An toàn khi chia sẻ bảng tính
• Inspect Document (File > Info > Check for Issues): tìm tên tác giả, nhận xét, dòng cột ẩn, trang tính ẩn trước khi gửi ra ngoài.
• Dữ liệu cá nhân: xóa hoặc thay bằng mã trước khi chia sẻ danh sách người học (Chương 2 và 3).
• Liên kết ngoài và macro: File .xlsm hoặc liên kết đến file khác có thể kích hoạt cảnh báo bảo mật; chỉ bật khi biết rõ nguồn.
• Gửi bản PDF hoặc bản giá trị (Paste Special > Values) khi người nhận chỉ cần xem kết quả.
7.8. Ứng dụng trong ngành: bảng điều khiển một trang
7.8.1. Bảng điều khiển kinh phí phim ngắn
Bảng điều khiển (dashboard) gom các kết quả quan trọng vào một trang để người xem nắm tình hình trong vài giây. Với kinh phí phim ngắn, một trang gồm: tổng chi và tổng ngân sách; bảng chi theo nhóm so với ngân sách; biểu đồ cột kết hợp đường; ô cảnh báo nhóm vượt ngân sách bằng định dạng có điều kiện. Số kiểm tra với dữ liệu mẫu: tổng chi 321.450.000 đồng, tổng ngân sách 329.000.000 đồng, nhóm vượt ngân sách: Nhân sự, Hậu kỳ.
Bảng 7.17 — Chi theo nhóm so với ngân sách (dữ liệu mẫu)
| Nhóm | Chi thực tế | Ngân sách | Chênh lệch |
|---|---|---|---|
| Thiết bị | 73.100.000 | 77.000.000 | 3.900.000 |
| Nhân sự | 76.000.000 | 70.000.000 | -6.000.000 |
| Ăn uống | 57.450.000 | 65.000.000 | 7.550.000 |
| Di chuyển | 11.600.000 | 12.000.000 | 400.000 |
| Hậu kỳ | 68.000.000 | 62.000.000 | -6.000.000 |
| Khác | 35.300.000 | 43.000.000 | 7.700.000 |
| Tổng | 321.450.000 | 329.000.000 | 7.550.000 |
1. Tạo PivotTable chi theo nhóm; đặt cạnh cột Ngân sách bằng XLOOKUP từ trang NganSach.
2. Cột Chênh lệch = Ngân sách − Chi thực tế; âm nghĩa là vượt. Tô đỏ bằng Conditional Formatting (Highlight Cells < 0).
3. Chèn biểu đồ cột kết hợp đường: chi thực tế là cột, ngân sách là đường (Change Series Chart Type > Combo).
4. Thêm Slicer lọc theo tháng nếu cần.
5. Bố cục một trang: tiêu đề, các con số tổng ở trên cùng, bảng bên trái, biểu đồ bên phải; ẩn đường lưới (View > Gridlines).
Hình 7.8 — Bảng điều khiển kinh phí một trang: ba số tổng, bảng chi so với ngân sách có ô chênh lệch âm tô đỏ và biểu đồ cột kết hợp đường.
7.8.2. Bảng điểm lớp: xếp hạng, phân bố và người cần hỗ trợ
Trên bảng điểm mẫu có thể lập bảng thống kê theo lớp bằng COUNTIFS, AVERAGEIFS hoặc PivotTable: điểm tổng kết trung bình của lớp DA26A là 7,20, MT26A là 7,43, SK26A là 6,83; số người Giỏi trở lên của từng lớp là 4, 8, 4. Xếp hạng toàn khóa dùng RANK.EQ (Chương 6); lọc danh sách cần hỗ trợ bằng tô màu có điều kiện hoặc bộ lọc. Chỉ chia sẻ bảng điểm với người có quyền; khi cần công khai, thay họ tên bằng mã.
7.8.3. Lịch quay và quản lý thiết bị mượn trả
Trang ThietBi ghi 8 thiết bị mượn. Với ô H1 là ngày kiểm tra (20/11/2026), cột "Trạng thái" dùng =IF(E2<>"","Đã trả",IF(D2<$H$1,"Quá hạn","Đang mượn")) cho ra 3 thiết bị quá hạn (Bộ đèn LED 1, Máy quay B, Đầu ghi âm). Cột "Số ngày mượn" dùng NETWORKDAYS giữa ngày mượn và ngày trả dự kiến; tô đỏ cả dòng quá hạn bằng quy tắc công thức. Cách làm này thay cho sổ ghi tay và nhắc việc không bỏ sót.
7.8.4. Danh sách kiểm tra một bảng điều khiển tốt
• Có tiêu đề, ngày cập nhật, nguồn dữ liệu.
• Con số quan trọng nhất ở vị trí dễ thấy nhất; không quá một trang A4.
• Mỗi biểu đồ một thông điệp, có đơn vị; không dùng 3D.
• Cảnh báo bằng màu có chú giải, không chỉ dựa vào màu (có chữ hoặc biểu tượng đi kèm).
• Công thức đã kiểm tra bằng số tính tay; không có lỗi #N/A, #REF!.
• Trang tính nguồn được bảo vệ hoặc ẩn khỏi bản gửi; dữ liệu cá nhân đã thay bằng mã.
• Đã xuất PDF một trang (Page Layout > Fit to 1 page, Chương 6) và sao lưu theo 3-2-1.
Tóm tắt chương
• Dữ liệu phải là bảng phẳng, sạch; dùng Table và tham chiếu có cấu trúc (tbChiPhi[ThanhTien]) để công thức tự mở rộng và dễ đọc.
• SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS xét nhiều điều kiện cùng đúng; vùng cần tính đứng đầu ở SUMIFS và AVERAGEIFS; các vùng cùng số dòng; điều kiện ngày ghép bằng ">="&DATE(…).
• IFS gọn hơn IF lồng nhau; điều kiện cuối là TRUE; xếp từ cao xuống thấp. IFERROR chỉ dùng có chủ đích, không giấu lỗi thật.
• Tra cứu lấy dữ liệu từ bảng khác theo khóa: VLOOKUP (khớp chính xác FALSE hoặc gần đúng TRUE cho ngưỡng), INDEX + MATCH (mọi hướng, hai chiều), XLOOKUP (hiện đại, Excel 365).
• #N/A thường do khóa không có, khoảng trắng thừa, khác kiểu dữ liệu hoặc vùng không cố định; khắc phục bằng TRIM, địa chỉ tuyệt đối, đối số nếu_không_thấy.
• Làm sạch dữ liệu: TRIM, PROPER, SUBSTITUTE, TEXT, DATE kết hợp RIGHT, MID, LEFT; Flash Fill, Text to Columns, Remove Duplicates (làm trên bản sao); ghi lại thay đổi.
• DATEDIF, EOMONTH, NETWORKDAYS, WORKDAY tính tuổi, tiến độ, hạn trả theo ngày làm việc.
• Chọn biểu đồ theo câu hỏi cần trả lời; một biểu đồ một thông điệp; trục cột bắt đầu từ 0; tránh 3D; chèn sang Word đúng Quy định (≤ 75%, chú thích dưới hình).
• PivotTable tổng hợp bằng kéo thả vào Rows, Columns, Values, Filters; nhóm theo ngày; Pivot Chart và Slicer; phải Refresh khi nguồn đổi; Table làm nguồn tự mở rộng.
• Conditional Formatting (kể cả bằng công thức), Data Validation, Protect Sheet (ngăn sửa nhầm, không phải bảo mật), Goal Seek, Inspect Document; bảng điều khiển một trang gom số tổng, bảng, biểu đồ và cảnh báo.
Thuật ngữ cần nhớ
Bảng 7.18 — Thuật ngữ cần nhớ
| Thuật ngữ | Giải nghĩa |
|---|---|
| Structured reference | Tham chiếu có cấu trúc: viết công thức theo tên bảng và tên cột |
| SUMIFS, COUNTIFS, AVERAGEIFS | Hàm tính tổng, đếm, trung bình theo nhiều điều kiện cùng đúng |
| MAXIFS, MINIFS | Giá trị lớn nhất, nhỏ nhất theo điều kiện |
| IFS | Hàm chọn kết quả theo danh sách điều kiện; thay IF lồng nhau |
| SUMPRODUCT | Nhân các mảng theo từng cặp rồi cộng; tổng có điều kiện kiểu mảng |
| Lookup value, Lookup table | Khóa tra cứu; bảng tra |
| VLOOKUP | Tra cứu theo cột đầu của bảng tra, trả về cột thứ n bên phải |
| INDEX, MATCH | Lấy giá trị theo vị trí; tìm vị trí của khóa; ghép lại để tra cứu |
| XLOOKUP | Hàm tra cứu hiện đại của Excel 365, thay VLOOKUP |
| Exact match, Approximate match | Khớp chính xác; khớp gần đúng (bảng ngưỡng sắp xếp tăng dần) |
| #N/A | Lỗi không tìm thấy giá trị cần tra |
| TRIM, PROPER, SUBSTITUTE | Hàm bỏ khoảng trắng thừa; viết hoa đầu từ; thay chữ |
| Flash Fill | Điền theo mẫu Excel tự đoán (Ctrl + E) |
| Text to Columns | Tách một cột thành nhiều cột theo ký tự phân cách |
| NETWORKDAYS, WORKDAY | Tính số ngày làm việc; ngày làm việc sau n ngày |
| Chart type, Combo chart | Loại biểu đồ; biểu đồ kết hợp cột và đường |
| Sparklines | Biểu đồ rất nhỏ nằm trong một ô |
| PivotTable, Pivot Chart | Bảng tổng hợp xoay; biểu đồ gắn với PivotTable |
| Slicer, Timeline | Bộ nút lọc trực quan; bộ lọc theo ngày cho PivotTable |
| Refresh | Làm mới PivotTable sau khi dữ liệu nguồn thay đổi |
| Conditional Formatting | Định dạng có điều kiện: tự đổi màu ô theo giá trị hoặc công thức |
| Protect Sheet | Bảo vệ trang tính: khóa ô, chặn sửa nhầm (không phải bảo mật) |
| Goal Seek | Tìm giá trị đầu vào để công thức đạt kết quả mong muốn |
| Dashboard | Bảng điều khiển: trang gom số tổng, bảng, biểu đồ và cảnh báo |
Câu hỏi ôn tập
1. Nêu năm đặc điểm của dữ liệu "sạch" cần có trước khi dùng hàm tổng hợp hoặc PivotTable. Cho một ví dụ vi phạm từng đặc điểm.
2. So sánh địa chỉ ô (G2:G121) và tham chiếu có cấu trúc (tbChiPhi[ThanhTien]). Khi thêm một dòng ở cuối bảng thì điều gì xảy ra với mỗi cách?
3. Viết công thức tính tổng chi nhóm Hậu kỳ trong tháng 12/2026. Giải thích vì sao ngày nên viết bằng DATE thay cho viết thẳng chuỗi.
4. SUMIFS trả về 0 trong khi người học chắc chắn có dữ liệu. Nêu ba nguyên nhân có thể và cách kiểm tra từng nguyên nhân.
5. Viết công thức xếp loại năm mức bằng IFS và bằng VLOOKUP khớp gần đúng. Ưu điểm của cách dùng bảng tra khi nhà trường đổi ngưỡng là gì?
6. Vì sao không nên bọc IFERROR ngoài mọi công thức? Nêu một ví dụ IFERROR dùng đúng.
7. Giải thích bốn đối số của VLOOKUP. Hậu quả của việc quên đối số thứ tư là gì?
8. Viết công thức INDEX + MATCH lấy đơn giá của một hạng mục và công thức tra giá thuê theo thiết bị và gói ngày (tra hai chiều).
9. So sánh VLOOKUP, INDEX + MATCH, XLOOKUP ở ba điểm: tra sang trái, lệch khi chèn cột, phiên bản Excel. Trong trường hợp nào người học chọn từng hàm?
10. Cho ô B2 chứa " trần THỊ b " và ô C2 chứa ngày dạng chữ "05/07/2006". Viết công thức cho ra họ tên chuẩn và ngày thật; nêu thêm hai việc cần làm với cả cột.
11. Chọn loại biểu đồ cho từng yêu cầu: so sánh chi phí giữa sáu nhóm; xu hướng chi theo tháng; thực tế so với ngân sách; cơ cấu chi gồm 12 hạng mục. Giải thích.
12. Nêu ba lỗi trình bày làm biểu đồ gây hiểu lầm và cách sửa. Quy định của Trường yêu cầu gì về kích thước và chú thích khi đưa biểu đồ vào Word?
13. Bốn vùng của PivotTable là gì? Tạo PivotTable thế nào để thấy chi theo nhóm và theo tháng? Khi nào cần Refresh?
14. Khi nào nên dùng PivotTable, khi nào dùng SUMIFS để làm báo cáo nộp cho người khác?
15. Protect Sheet có phải là biện pháp bảo mật không? Nêu giới hạn và đề xuất biện pháp mạnh hơn khi cần giữ bí mật bảng điểm.
Bài tập tìm hiểu (làm ở nhà, nộp trước buổi sau)
Chọn 2 trong 6 bài, trình bày 1–2 trang Word có liên kết nguồn, định dạng đúng Quy định của Trường (Chương 5): style, chú thích hình và bảng, trích dẫn. Được dùng AI hỗ trợ nhưng phải ghi rõ.
1. Power Query. Tìm hiểu Get & Transform (Data > Get Data): nhập và làm sạch dữ liệu bằng các bước có thể chạy lại. Thử làm một ví dụ với tệp CSV và so sánh với làm sạch bằng hàm ở mục 7.4.
2. Hàm mảng động. Tìm hiểu FILTER, UNIQUE, SORT trong Excel 365: cú pháp, ví dụ lọc danh sách người học theo lớp, và vì sao kết quả "tràn" sang nhiều ô.
3. Biểu đồ gây hiểu lầm. Tìm hai biểu đồ thực tế (báo chí, quảng cáo) trình bày gây hiểu lầm; chỉ ra lỗi và vẽ lại bằng Excel cho đúng.
4. Excel và Google Sheets. So sánh PivotTable, XLOOKUP, Slicer ở hai phần mềm; nêu trường hợp một nhóm làm phim chọn từng loại.
5. Lỗi bảng tính trong thực tế. Tìm một vụ việc sai sót do tra cứu hoặc tổng hợp sai trong bảng tính (ví dụ VLOOKUP khớp gần đúng, vùng tham chiếu sai); nguyên nhân, hậu quả, cách phòng tránh.
6. Bảng điều khiển trong ngành. Tìm một mẫu dashboard quản lý sản xuất phim hoặc sự kiện; mô tả các chỉ số, loại biểu đồ, và đề xuất cải tiến theo danh sách kiểm tra ở mục 7.8.4.
Bài tập thực hành
BT1–BT3 làm trên lớp (3 tiết thực hành); BT4–BT6 làm ở nhà. Dùng file Chuong7_DuLieu_Mau.xlsx (dữ liệu giả). Nộp cả .xlsx và .pdf, nén TinHoc_Buoi07_HoTen.zip.
1. Tra cứu và ghép bảng (trên lớp, 35 phút). Trang ChiPhi: điền cột Nhom, DonGia bằng XLOOKUP (hoặc VLOOKUP khớp chính xác), cột ThanhTien =[@SoLuong]*[@DonGia]. Trang BangDiem: thêm TongKet và XepLoai tra từ bảng Thang bằng khớp gần đúng; dùng COUNTIF đếm từng loại. Kiểm tra: tổng ThanhTien 321.450.000 đồng; Xuất sắc 8, Giỏi 8, Khá 25, Trung bình 16, Yếu 3. Dùng IFERROR cho ô hạng mục không có và ghi chú trường hợp đó.
2. Hàm nhiều điều kiện (trên lớp, 30 phút). Trong trang ChiPhi (Table tbChiPhi, cột ThanhTien đã tính ở BT1), lập bảng kết quả gồm: tổng chi nhóm Thiết bị; chi nhóm Thiết bị tháng 11/2026; số dòng chi ăn uống từ 40 suất; trung bình và lớn nhất của nhóm Hậu kỳ. Kiểm tra: 73.100.000; 6.700.000; 13; 4.000.000; 6.000.000.
3. Làm sạch dữ liệu (trên lớp, 30 phút). Sao lưu trang NhapLieu rồi làm sạch: họ tên chuẩn (PROPER, TRIM), ngày sinh thành ngày thật, số điện thoại đủ số 0, xóa dòng trùng. Kiểm tra: còn 6 người. Chụp ảnh trước và sau, ghi lại thay đổi ở trang GhiChu.
4. Biểu đồ (ở nhà). Dựng biểu đồ cột kết hợp đường: chi thực tế theo sáu nhóm (cột) và ngân sách (đường) từ trang NganSach; chỉnh trục bắt đầu từ 0, đơn vị triệu đồng, bỏ 3D, không bắt buộc tiêu đề. Dán sang Word dưới dạng ảnh rộng không quá 75% bề rộng chữ, chú thích "Hình" đặt dưới, đánh số theo chương.
5. PivotTable (ở nhà). Tạo PivotTable chi theo nhóm và tháng; đổi sang hiển thị % of Grand Total; thêm Slicer Nhom và một Pivot Chart. Kiểm tra: tổng chi tháng 10, 11, 12 là 105.400.000, 88.550.000, 127.500.000; nhóm lớn nhất là Nhân sự, chiếm 23,6%.
6. Bảng điều khiển một trang (ở nhà). Dựng bảng điều khiển kinh phí: tổng chi, tổng ngân sách, bảng chi so với ngân sách có cột Chênh lệch tô đỏ khi âm, biểu đồ và Slicer. Thêm trang ThietBi với cột trạng thái (đã trả, đang mượn, quá hạn) và tô đỏ dòng quá hạn. Kiểm tra: nhóm vượt ngân sách Nhân sự, Hậu kỳ; 3 thiết bị quá hạn. Xuất PDF một trang A4 ngang và bảo vệ trang tính (cho nhập ở cột SoLuong).
| Tiêu chí đánh giá bài tậpĐúng và đủ yêu cầu kỹ thuật, kết quả khớp số kiểm tra (40%) · Chọn đúng hàm và đúng loại địa chỉ, không gõ cứng số vào công thức, xử lý lỗi hợp lý (30%) · Trình bày rõ ràng, biểu đồ đúng nguyên tắc, ảnh chụp đầy đủ, có nhận xét cá nhân (20%) · Nộp đúng hạn, đúng định dạng file (10%). BT1 và BT5 là bài trọng tâm. Bài tập tìm hiểu đánh giá riêng theo yêu cầu của từng bài. |
|---|
— Hết Chương 7 —