Appearance
📘 Buổi 3: Excel — Làm sạch dữ liệu & Pivot Table
80% thời gian DA là làm sạch dữ liệu. Excel là công cụ đầu tiên phải thành thạo.
🎯 Mục tiêu buổi học
Sau buổi này, học viên sẽ:
- Nhận diện và xử lý dữ liệu bẩn: duplicate, missing, inconsistent format
- Sử dụng thành thạo các hàm cleaning: TRIM, CLEAN, TEXT, SUBSTITUTE, IFERROR
- Tạo Pivot Table để tổng hợp và phân tích dữ liệu đa chiều
- Áp dụng nguyên tắc Tidy Data vào thực tế
📋 Tổng quan
Ở Buổi 1, bạn đã nắm được bức tranh tổng quan nghề Data Analyst và quy trình phân tích 6 bước (Ask → Prepare → Process → Analyze → Share → Act). Buổi 2 trang bị cho bạn tư duy phân tích và thống kê mô tả — biết cách nhìn dữ liệu qua mean, median, standard deviation, phát hiện outlier. Giờ là lúc bắt tay vào thực hành với công cụ đầu tiên: Excel. Và bước thực hành đầu tiên — cũng là bước chiếm nhiều thời gian nhất — chính là làm sạch dữ liệu (Data Cleaning).
Tại sao Data Cleaning quan trọng đến vậy? Vì trong thực tế, dữ liệu bạn nhận được gần như không bao giờ sạch. File export từ hệ thống ERP có khoảng trắng thừa, tên khách hàng viết hoa viết thường lẫn lộn, ngày tháng nhập sai format, dòng dữ liệu bị trùng, ô trống không rõ lý do. Nếu bạn phân tích trên dữ liệu bẩn (dirty data), kết quả sẽ sai — dù biểu đồ có đẹp đến đâu. Câu nói kinh điển trong ngành: "Garbage in, garbage out" (rác vào, rác ra). Theo ước tính của IBM, dirty data gây thiệt hại khoảng $3.1 trillion/năm chỉ riêng tại Mỹ.
Sau khi dữ liệu đã sạch, bạn cần tổng hợp và phân tích nó — và Pivot Table là công cụ mạnh nhất trong Excel để làm điều này. Pivot Table cho phép bạn nhóm, tổng hợp, lọc dữ liệu theo nhiều chiều chỉ trong vài click chuột, thay vì viết hàng chục hàm SUMIF/COUNTIF. Đây chính là bước Process → Analyze trong Google Data Analytics Framework mà bạn đã học ở Buổi 1. Buổi học hôm nay sẽ biến bạn từ người "sợ Excel" thành người tự tin xử lý bất kỳ bộ dữ liệu nào được giao.
📌 Phần 1: Nhận diện dữ liệu bẩn (Dirty Data)
Khái niệm
Dirty data (dữ liệu bẩn) là dữ liệu có lỗi, chưa sẵn sàng để phân tích. Bất kỳ bộ dữ liệu nào khi thu thập từ nhiều nguồn (manual input, hệ thống, survey, web scraping…) đều có nguy cơ chứa lỗi. Một Data Analyst giỏi phải có khả năng nhận diện nhanh các loại lỗi phổ biến trước khi bắt đầu phân tích.
6 loại dữ liệu bẩn phổ biến nhất:
| # | Loại lỗi | Mô tả | Ví dụ |
|---|---|---|---|
| 1 | Duplicate (Trùng lặp) | Cùng một bản ghi xuất hiện nhiều lần | Khách hàng "Nguyễn Văn A" xuất hiện 3 dòng giống hệt nhau |
| 2 | Missing (Thiếu dữ liệu) | Ô trống, NULL, hoặc giá trị không xác định | Cột "Số điện thoại" bị trống 15% dòng |
| 3 | Formatting errors (Lỗi định dạng) | Ngày tháng, số, text không đúng format | Ngày "15/01/2026" vs "01-15-2026" vs "Jan 15, 2026" trong cùng cột |
| 4 | Typos (Lỗi chính tả) | Nhập sai do con người | "Hà Nội" vs "Ha Noi" vs "HÀ NỘI" vs "Hà nội" |
| 5 | Outlier (Giá trị ngoại lai) | Giá trị bất thường, có thể là lỗi hoặc thật | Đơn hàng 999,999,999 VNĐ (nhập nhầm?) |
| 6 | Inconsistency (Không nhất quán) | Cùng thông tin nhưng ghi khác nhau | Giới tính: "Nam" vs "M" vs "Male" vs "nam" trong cùng cột |
Tại sao quan trọng cho Data Analyst?
Dirty data ảnh hưởng trực tiếp đến mọi bước phân tích phía sau. Nếu bạn không phát hiện dòng trùng lặp, hàm SUM sẽ tính gấp đôi doanh thu. Nếu bạn không chuẩn hóa tên thành phố, Pivot Table sẽ tách "Hà Nội" và "Ha Noi" thành 2 nhóm riêng. Nếu bạn bỏ qua missing values, hàm AVERAGE sẽ cho kết quả lệch.
Nhớ lại Buổi 1 — bước Process trong Google Data Analytics Framework chính là Data Cleaning. Google ước tính DA dành 60–80% thời gian dự án cho bước này. Đây không phải công việc "thấp cấp" — đây là kỹ năng sống còn giúp bạn đảm bảo output phân tích đáng tin cậy.
Chi phí của dirty data:
| Nguồn | Thiệt hại | Ghi chú |
|---|---|---|
| IBM (2023) | $3.1 trillion/năm tại US | Do quyết định sai dựa trên dữ liệu bẩn |
| Gartner | Trung bình doanh nghiệp mất $12.9 triệu USD/năm | Do poor data quality |
| Harvard Business Review | Chỉ 3% dữ liệu doanh nghiệp đạt chuẩn "acceptable quality" | Tức 97% dữ liệu có vấn đề ở mức nào đó |
Áp dụng thực tế
Data Quality Dimensions — 5 chiều đánh giá chất lượng dữ liệu:
| Chiều | Mô tả | Câu hỏi kiểm tra | Ví dụ lỗi |
|---|---|---|---|
| Accuracy (Chính xác) | Dữ liệu có phản ánh đúng thực tế? | Tuổi khách hàng = 250 có đúng không? | Tuổi = 250 → nhập sai |
| Completeness (Đầy đủ) | Có thiếu dữ liệu nào quan trọng? | Bao nhiêu % ô trống trong cột bắt buộc? | 30% dòng thiếu email |
| Consistency (Nhất quán) | Cùng thông tin, cùng format? | "TP.HCM" = "Ho Chi Minh" = "Sài Gòn"? | 3 cách viết cho cùng 1 thành phố |
| Timeliness (Kịp thời) | Dữ liệu có cập nhật, đúng thời điểm? | Dữ liệu khách hàng từ 2020 có còn phù hợp cho phân tích 2026? | Dùng data cũ 5 năm để phân tích xu hướng hiện tại |
| Validity (Hợp lệ) | Dữ liệu có đúng format, range cho phép? | Email có dấu @? Ngày sinh < ngày hiện tại? | Email: "abc.com" (thiếu @) |
Ví dụ Before/After — Nhận diện dirty data trong 1 bảng:
BEFORE (Dữ liệu bẩn):
| ID | Tên khách hàng | Thành phố | Ngày mua | Doanh thu |
|---|---|---|---|---|
| 1 | Nguyễn Văn An | Hà Nội | 15/01/2026 | 500000 |
| 2 | nguyễn văn an | Ha Noi | 2026-01-15 | 500000 |
| 3 | Trần Thị Bình | TP.HCM | 16/01/2026 | |
| 4 | LÊ VĂN CƯỜNG | Sài Gòn | Jan 17, 2026 | 750000 |
| 5 | Trần Thị Bình | Ho Chi Minh | 16-01-2026 | 300000 |
| 6 | Phạm Đức Dũng | Đà Nẵng | 18/01/2026 | -200000 |
Lỗi phát hiện:
- Dòng 1 & 2: Duplicate (cùng khách hàng, cùng ngày, cùng doanh thu) + Inconsistency (viết hoa/thường khác nhau)
- Dòng 3: Missing (doanh thu trống)
- Dòng 3, 4, 5: Inconsistency thành phố ("TP.HCM" vs "Sài Gòn" vs "Ho Chi Minh" — cùng 1 thành phố)
- Cột ngày mua: Formatting error (4 format khác nhau cho ngày tháng)
- Dòng 6: Outlier/Validity (doanh thu âm?)
📌 Phần 2: Data Cleaning với Excel
Khái niệm
Excel cung cấp một bộ công cụ mạnh mẽ để xử lý dữ liệu bẩn, từ các hàm text xử lý chuỗi, đến các tính năng built-in như Remove Duplicates, Text to Columns, Find & Replace. Đây là những "vũ khí" mà mọi DA phải thành thạo trước khi chuyển sang Python hay SQL.
Bộ công cụ Data Cleaning trong Excel:
| Nhóm | Hàm / Tính năng | Mục đích |
|---|---|---|
| Loại bỏ khoảng trắng, ký tự ẩn | TRIM, CLEAN | Xóa khoảng trắng thừa, ký tự không in được |
| Chuẩn hóa text | UPPER, LOWER, PROPER | Đồng nhất viết hoa/thường |
| Sửa/thay thế dữ liệu | SUBSTITUTE, REPLACE | Thay thế chuỗi ký tự cụ thể |
| Xử lý lỗi & missing | IFERROR, ISBLANK, IF | Xử lý giá trị lỗi (#N/A, #VALUE!) và ô trống |
| Loại bỏ trùng lặp | Remove Duplicates | Xóa dòng trùng trong 1 click |
| Tách/gộp cột | Text to Columns, CONCAT/TEXTJOIN | Tách 1 cột thành nhiều cột hoặc ngược lại |
| Tìm & thay nâng cao | Find & Replace (wildcard) | Tìm kiếm pattern với *, ?, ~ |
Tại sao quan trọng cho Data Analyst?
Mỗi hàm cleaning giải quyết một loại lỗi cụ thể. DA cần biết dùng hàm nào cho tình huống nào — giống như bác sĩ cần biết kê đúng thuốc cho đúng bệnh. Việc thành thạo bộ hàm này giúp bạn xử lý bộ dữ liệu 10.000–100.000 dòng trong vài phút, thay vì sửa tay từng ô.
Đặc biệt, Excel là công cụ mọi doanh nghiệp đều có — từ startup đến tập đoàn. Dù sau này bạn dùng Python (pandas) hay SQL để clean data ở quy mô lớn hơn, tư duy cleaning trong Excel vẫn là nền tảng. Theo khảo sát của ECDL/ICDL Foundation, 82% nhân viên văn phòng sử dụng Excel hàng ngày, nhưng chỉ 30% biết dùng đúng các hàm cleaning.
Áp dụng thực tế
Bảng chi tiết: Mỗi hàm — Cú pháp — Ví dụ — Khi nào dùng:
| Hàm | Cú pháp | Input | Output | Khi nào dùng |
|---|---|---|---|---|
TRIM | =TRIM(text) | " Hà Nội " | "Hà Nội" | Loại khoảng trắng đầu/cuối và khoảng trắng thừa giữa các từ |
CLEAN | =CLEAN(text) | Text có ký tự ẩn (line break, tab) | Text sạch | Khi data copy từ web, PDF, hệ thống cũ |
UPPER | =UPPER(text) | "hà nội" | "HÀ NỘI" | Chuẩn hóa toàn bộ viết hoa |
LOWER | =LOWER(text) | "HÀ NỘI" | "hà nội" | Chuẩn hóa toàn bộ viết thường |
PROPER | =PROPER(text) | "nguyễn văn an" | "Nguyễn Văn An" | Viết hoa chữ cái đầu mỗi từ |
SUBSTITUTE | =SUBSTITUTE(text, old, new) | "TP.HCM", "TP.HCM", "Hồ Chí Minh" | "Hồ Chí Minh" | Thay thế chuỗi ký tự cụ thể |
REPLACE | =REPLACE(text, start, num, new) | "0901234567", 1, 1, "+84" | "+84901234567" | Thay thế theo vị trí ký tự |
IFERROR | =IFERROR(value, value_if_error) | =VLOOKUP(...) trả về #N/A | 0 hoặc "Không tìm thấy" | Bắt lỗi cho hàm VLOOKUP, INDEX/MATCH, phép chia cho 0 |
ISBLANK | =ISBLANK(cell) | Ô trống | TRUE | Kiểm tra ô có trống không → kết hợp IF |
Kết hợp nhiều hàm — Nested formula:
Trong thực tế, bạn thường cần kết hợp nhiều hàm để xử lý triệt để. Ví dụ:
=TRIM(CLEAN(PROPER(A2)))→ Loại ký tự ẩn (CLEAN) → Loại khoảng trắng thừa (TRIM) → Viết hoa chữ đầu (PROPER). Một công thức xử lý 3 loại lỗi cùng lúc.
📌 Phần 3: Tidy Data Principles
Khái niệm
Tidy Data là khái niệm do Hadley Wickham (Chief Scientist tại Posit, tác giả nhiều package R nổi tiếng) đề xuất trong bài báo khoa học năm 2014. Tidy Data định nghĩa cấu trúc dữ liệu "gọn gàng" theo 3 nguyên tắc cốt lõi:
| # | Nguyên tắc | Mô tả | Ví dụ |
|---|---|---|---|
| 1 | Mỗi biến (variable) là 1 cột | Mỗi cột chứa đúng 1 loại thông tin | Cột "Doanh_thu", cột "Số_lượng" — không gộp chung |
| 2 | Mỗi quan sát (observation) là 1 dòng | Mỗi dòng là 1 đơn vị quan sát hoàn chỉnh | Mỗi dòng = 1 giao dịch, 1 khách hàng, 1 sản phẩm |
| 3 | Mỗi bảng chứa 1 loại đơn vị quan sát | Không trộn nhiều loại entity vào 1 bảng | Bảng "Đơn hàng" tách riêng bảng "Khách hàng" |
Reshaping Data: Wide vs Long format
Dữ liệu thực tế thường ở dạng wide format (dễ đọc cho con người nhưng khó phân tích):
Wide format (KHÔNG tidy):
| Cửa hàng | Doanh thu Q1 | Doanh thu Q2 | Doanh thu Q3 | Doanh thu Q4 |
|---|---|---|---|---|
| HN-01 | 500 | 620 | 580 | 710 |
| HCM-01 | 800 | 750 | 920 | 880 |
Long format (Tidy):
| Cửa hàng | Quý | Doanh thu |
|---|---|---|
| HN-01 | Q1 | 500 |
| HN-01 | Q2 | 620 |
| HN-01 | Q3 | 580 |
| HN-01 | Q4 | 710 |
| HCM-01 | Q1 | 800 |
| HCM-01 | Q2 | 750 |
| HCM-01 | Q3 | 920 |
| HCM-01 | Q4 | 880 |
Long format tuân thủ Tidy Data: mỗi biến ("Cửa hàng", "Quý", "Doanh thu") là 1 cột, mỗi dòng là 1 quan sát (doanh thu 1 cửa hàng trong 1 quý cụ thể).
Tại sao quan trọng cho Data Analyst?
Tidy Data là tiền đề để mọi công cụ phân tích hoạt động chính xác:
- Pivot Table yêu cầu dữ liệu ở dạng long/tidy để nhóm và tổng hợp
- SQL hoạt động dựa trên cấu trúc bảng quan hệ — bản chất là tidy data
- Python pandas và R đều có các thư viện (melt, pivot, tidyr) xây dựng trên triết lý tidy data
- Power BI / Tableau hoạt động tốt nhất khi dữ liệu ở dạng tidy
Nếu dữ liệu không ở dạng tidy, bạn sẽ phải reshape (chuyển đổi) trước khi phân tích — và đây là bước nhiều DA mới hay bỏ qua, dẫn đến biểu đồ sai hoặc Pivot Table không hoạt động.
Áp dụng thực tế
Data Validation trong Excel:
Data Validation là tính năng Excel giúp ngăn dữ liệu bẩn ngay từ đầu — thay vì phải clean sau. Đây là cách áp dụng nguyên tắc "prevention > cure" (phòng bệnh hơn chữa bệnh).
| Loại Validation | Mô tả | Ví dụ áp dụng |
|---|---|---|
| List | Chỉ cho phép chọn từ danh sách có sẵn | Cột "Thành phố" chỉ được chọn: Hà Nội, TP.HCM, Đà Nẵng… |
| Whole Number | Chỉ chấp nhận số nguyên trong khoảng | Cột "Tuổi" phải từ 18 đến 100 |
| Date | Chỉ chấp nhận ngày trong khoảng | Cột "Ngày mua" phải từ 01/01/2024 đến hôm nay |
| Text Length | Giới hạn số ký tự | Cột "Mã sản phẩm" phải đúng 8 ký tự |
| Custom | Điều kiện tùy chỉnh bằng formula | Cột "Email" phải chứa ký tự "@" → =ISNUMBER(FIND("@",A2)) |
Cách thiết lập Data Validation:
- Chọn cột hoặc range cần validate
- Vào Data → Data Validation
- Chọn loại (List, Whole Number, Date…)
- Thiết lập Input Message (hướng dẫn khi click vào ô)
- Thiết lập Error Alert (cảnh báo khi nhập sai)
📌 Phần 4: Pivot Table
Khái niệm
Pivot Table (Bảng tổng hợp) là công cụ mạnh nhất trong Excel để tổng hợp, nhóm, và phân tích dữ liệu đa chiều. Thay vì viết hàng chục hàm SUMIF, COUNTIF, AVERAGEIF cho từng nhóm, Pivot Table cho phép bạn kéo-thả (drag & drop) các trường dữ liệu để tạo báo cáo tổng hợp trong vài giây.
4 vùng của Pivot Table:
| Vùng | Mô tả | Ví dụ |
|---|---|---|
| Rows (Dòng) | Nhóm dữ liệu theo chiều dọc | Khu vực, Sản phẩm, Nhân viên |
| Columns (Cột) | Nhóm dữ liệu theo chiều ngang | Tháng, Quý, Năm |
| Values (Giá trị) | Cột cần tổng hợp/tính toán | SUM(Doanh thu), COUNT(Đơn hàng), AVERAGE(Giá trị đơn) |
| Filters (Bộ lọc) | Lọc toàn bộ Pivot Table theo điều kiện | Lọc theo năm 2026, theo khu vực miền Bắc |
Các hàm aggregation phổ biến trong Pivot Table:
| Aggregation | Ý nghĩa | Khi nào dùng |
|---|---|---|
| SUM | Tổng giá trị | Tổng doanh thu, tổng chi phí |
| COUNT | Đếm số bản ghi | Số đơn hàng, số khách hàng |
| AVERAGE | Trung bình | Doanh thu trung bình/đơn, rating trung bình |
| MAX | Giá trị lớn nhất | Đơn hàng lớn nhất, doanh thu cao nhất |
| MIN | Giá trị nhỏ nhất | Chi phí thấp nhất, thời gian giao hàng nhanh nhất |
| COUNT NUMBERS | Đếm ô có số | Đếm dòng có giá trị doanh thu (phát hiện missing) |
Tại sao quan trọng cho Data Analyst?
Pivot Table là công cụ thực hiện bước Analyze trong Google Data Analytics Framework. Khi stakeholder hỏi "Doanh thu theo khu vực và sản phẩm trong Q1/2026 là bao nhiêu?" — bạn cần trả lời trong vài phút, không phải vài giờ. Pivot Table cho phép bạn làm điều đó chỉ bằng kéo-thả.
Ngoài ra, Pivot Table kết hợp với Pivot Chart tạo thành bộ đôi mạnh mẽ cho descriptive analytics — loại phân tích phổ biến nhất mà DA thực hiện hàng ngày (nhớ lại Buổi 1 — Phần 4: Các loại phân tích dữ liệu). Pivot Table cũng hỗ trợ Calculated Fields — cho phép bạn tạo metric mới từ các cột có sẵn (ví dụ: Profit = Revenue - Cost) mà không cần thêm cột vào bảng dữ liệu gốc.
Áp dụng thực tế
Ví dụ: Phân tích doanh thu chuỗi cửa hàng theo nhiều chiều
Giả sử bạn có bảng dữ liệu bán hàng với 50.000 dòng, các cột: Ngày, Khu vực, Cửa hàng, Nhóm SP, Sản phẩm, Số lượng, Đơn giá, Doanh thu.
| Câu hỏi kinh doanh | Cấu hình Pivot Table | Kết quả |
|---|---|---|
| Tổng doanh thu theo khu vực? | Rows: Khu vực; Values: SUM(Doanh thu) | Biết khu vực nào doanh thu cao nhất |
| Doanh thu theo khu vực × sản phẩm? | Rows: Khu vực; Columns: Nhóm SP; Values: SUM(Doanh thu) | Ma trận doanh thu 2 chiều |
| Doanh thu theo tháng? | Rows: Ngày (Group by Month); Values: SUM(Doanh thu) | Trend doanh thu theo thời gian |
| Số đơn trung bình mỗi cửa hàng/tháng? | Rows: Cửa hàng; Columns: Ngày (Group by Month); Values: COUNT(Doanh thu) | So sánh hiệu suất cửa hàng |
| Top 5 sản phẩm doanh thu cao nhất? | Rows: Sản phẩm; Values: SUM(Doanh thu); Sort Descending; Top 10 Filter → chọn 5 | Xác định sản phẩm chủ lực |
Group by Date trong Pivot Table:
Khi kéo cột Ngày vào Rows, Excel sẽ hiển thị từng ngày — quá chi tiết. Bạn cần nhóm theo thời gian:
- Right-click vào cột Ngày trong Pivot Table → Group
- Chọn: Months, Quarters, Years (có thể chọn nhiều cùng lúc)
- Pivot Table tự động nhóm: Q1-2026, Q2-2026, … hoặc Jan-2026, Feb-2026, …
Đây là kỹ thuật cực kỳ hữu ích để phân tích seasonality (mùa vụ) và trend (xu hướng) — hai concept quan trọng trong time series analysis mà bạn sẽ học sâu hơn ở các buổi sau.
📊 Framework / Mô hình chính
Tidy Data Principles — Hadley Wickham
┌────────────────────────────────────────────────────────────┐
│ TIDY DATA PRINCIPLES │
│ (Hadley Wickham, 2014) │
├────────────────────────────────────────────────────────────┤
│ │
│ Nguyên tắc 1: Mỗi BIẾN là 1 CỘT │
│ ┌──────────┬──────────┬──────────┐ │
│ │ Tên KH │ Tuổi │ Doanh thu│ ← Mỗi cột = 1 biến │
│ ├──────────┼──────────┼──────────┤ │
│ │ An │ 28 │ 500K │ │
│ │ Bình │ 35 │ 320K │ ← Mỗi dòng = 1 │
│ │ Cường │ 42 │ 750K │ quan sát │
│ └──────────┴──────────┴──────────┘ │
│ │
│ Nguyên tắc 2: Mỗi QUAN SÁT là 1 DÒNG │
│ → Không gộp nhiều quan sát vào 1 dòng │
│ │
│ Nguyên tắc 3: Mỗi BẢNG chứa 1 LOẠI đơn vị quan sát │
│ → Bảng "Đơn hàng" tách riêng bảng "Khách hàng" │
│ │
│ ┌──────────────────┐ ┌──────────────────┐ │
│ │ Bảng Khách hàng │ │ Bảng Đơn hàng │ │
│ │ ─────────────── │ │ ─────────────── │ │
│ │ KH_ID | Tên │───→│ DH_ID | KH_ID │ │
│ │ Tuổi | TP │ │ Ngày | Tổng │ │
│ └──────────────────┘ └──────────────────┘ │
│ Quan hệ qua KH_ID (Foreign Key) │
└────────────────────────────────────────────────────────────┘Data Quality Dimensions — Framework đánh giá chất lượng dữ liệu
┌──────────────────────────────────────────────────────────┐
│ DATA QUALITY DIMENSIONS │
├──────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────┐ │
│ │ ACCURACY │ ← Dữ liệu đúng thực tế? │
│ └──────┬───────┘ │
│ ┌─────────────┼─────────────┐ │
│ │ │ │ │
│ ┌──┴──────┐ ┌──┴──────────┐ ┌┴────────────┐ │
│ │COMPLETE-│ │ CONSISTENCY │ │ VALIDITY │ │
│ │ NESS │ │ │ │ │ │
│ │Đầy đủ? │ │ Nhất quán? │ │ Đúng format?│ │
│ └─────────┘ └─────────────┘ └─────────────┘ │
│ │ │
│ ┌─────┴─────┐ │
│ │ TIMELINESS │ ← Dữ liệu cập nhật? │
│ └───────────┘ │
│ │
│ → Dữ liệu cần đạt cả 5 chiều để "sạch" và sẵn sàng │
│ cho phân tích chính xác. │
│ │
│ Tiêu chuẩn tham khảo: ISO 8000 (Data Quality) │
└──────────────────────────────────────────────────────────┘Quy trình Data Cleaning trong Excel — 7 bước
| Bước | Hành động | Công cụ Excel | Kiểm tra |
|---|---|---|---|
| 1 | Backup dữ liệu gốc | Save As hoặc copy sheet | Luôn giữ bản gốc để đối chiếu |
| 2 | Scan tổng quan: bao nhiêu dòng, cột, missing? | COUNTA, COUNTBLANK, Ctrl+End | Biết "quy mô" dirty data |
| 3 | Remove Duplicates | Data → Remove Duplicates | Kiểm tra trước/sau — bao nhiêu dòng bị xóa? |
| 4 | Chuẩn hóa text | TRIM, CLEAN, PROPER, UPPER | Spot-check 20 dòng ngẫu nhiên |
| 5 | Fix inconsistency | SUBSTITUTE, Find & Replace | Kiểm tra unique values (Remove Duplicates preview) |
| 6 | Xử lý missing & error | IFERROR, IF(ISBLANK(...)) | COUNTBLANK = 0 cho cột bắt buộc? |
| 7 | Validate | Data Validation, filters, Pivot Table kiểm tra | Tạo Pivot Table nhanh để verify tổng khớp |
🛠️ Demo / Code mẫu
Tình huống: Làm sạch bộ dữ liệu bán hàng
Bạn nhận được file Excel chứa dữ liệu bán hàng từ 3 chi nhánh, do 3 nhân viên khác nhau nhập → dữ liệu bẩn đủ loại. Bảng có các cột: Mã ĐH, Tên KH, Thành phố, Ngày mua, Sản phẩm, Số lượng, Đơn giá, Doanh thu.
Bước 1: TRIM & CLEAN — Loại khoảng trắng và ký tự ẩn
// Loại khoảng trắng thừa ở đầu, cuối, và giữa các từ
=TRIM(B2)
// Input: " Nguyễn Văn An "
// Output: "Nguyễn Văn An"
// Loại ký tự ẩn (không in được) — thường gặp khi copy từ web/PDF
=CLEAN(B2)
// Loại bỏ ký tự có mã ASCII 0-31 (line break, tab, v.v.)
// KẾT HỢP: Loại cả khoảng trắng lẫn ký tự ẩn
=TRIM(CLEAN(B2))
// Công thức "must-have" khi bắt đầu clean bất kỳ cột text nàoBước 2: PROPER / UPPER / LOWER — Chuẩn hóa viết hoa/thường
// Viết hoa chữ đầu mỗi từ — cho tên người, tên thành phố
=PROPER(TRIM(CLEAN(B2)))
// Input: "nguyễn VĂN an"
// Output: "Nguyễn Văn An"
// Viết hoa toàn bộ — cho mã sản phẩm, mã đơn hàng
=UPPER(A2)
// Input: "dh-001"
// Output: "DH-001"
// Viết thường toàn bộ — cho email
=LOWER(G2)
// Input: "AnNguyen@Gmail.COM"
// Output: "annguyen@gmail.com"Bước 3: SUBSTITUTE — Chuẩn hóa dữ liệu không nhất quán
// Chuẩn hóa tên thành phố: "TP.HCM" → "Hồ Chí Minh"
=SUBSTITUTE(C2, "TP.HCM", "Hồ Chí Minh")
// Chuẩn hóa nhiều giá trị — lồng SUBSTITUTE
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2, "TP.HCM", "Hồ Chí Minh"), "Sài Gòn", "Hồ Chí Minh"), "Ho Chi Minh", "Hồ Chí Minh")
// Mọi biến thể → "Hồ Chí Minh"
// Xóa ký tự đặc biệt không mong muốn
=SUBSTITUTE(D2, "-", "/")
// Input: "15-01-2026"
// Output: "15/01/2026"
// Thay thế dấu phẩy bằng dấu chấm (lỗi phổ biến khi export CSV locale VN)
=SUBSTITUTE(E2, ",", ".")
// Input: "1.500.000" → cần kiểm tra contextBước 4: IFERROR & ISBLANK — Xử lý lỗi và missing values
// IFERROR: Bắt mọi lỗi (#N/A, #VALUE!, #DIV/0!, #REF!)
=IFERROR(VLOOKUP(A2, BangGia, 2, FALSE), "Không tìm thấy")
// Nếu VLOOKUP lỗi → trả về "Không tìm thấy" thay vì hiển thị #N/A
// IFERROR kết hợp phép tính: tránh lỗi chia cho 0
=IFERROR(F2/G2, 0)
// Nếu G2 = 0 → trả về 0 thay vì #DIV/0!
// IF + ISBLANK: Xử lý ô trống
=IF(ISBLANK(F2), "THIẾU DỮ LIỆU", F2)
// Nếu ô F2 trống → ghi "THIẾU DỮ LIỆU", ngược lại giữ nguyên giá trị
// Đếm số ô trống trong cột để đánh giá mức độ missing
=COUNTBLANK(F2:F10000)
// Kết quả: 523 → 523/10000 = 5.23% dữ liệu thiếu
// Tỷ lệ missing (%)
=COUNTBLANK(F2:F10000)/COUNTA(A2:A10000)*100Bước 5: Remove Duplicates
Cách thực hiện:
1. Chọn toàn bộ bảng dữ liệu (Ctrl + A nếu chỉ có 1 bảng)
2. Vào tab Data → Remove Duplicates
3. Chọn cột cần kiểm tra trùng:
- Tất cả cột: xóa dòng giống hệt 100%
- Chỉ cột "Mã ĐH": xóa dòng có cùng mã đơn hàng
- Cột "Tên KH" + "Ngày mua" + "Doanh thu": xóa dòng có
cùng khách, cùng ngày, cùng số tiền
4. Excel báo: "X duplicate values found and removed. Y unique values remain."💡 Mẹo quan trọng
Luôn chuẩn hóa text TRƯỚC khi Remove Duplicates! Vì "Nguyễn Văn An" và "nguyễn văn an" được Excel coi là 2 dòng khác nhau. Hãy chạy TRIM(CLEAN(PROPER(...))) trước, paste values, rồi mới Remove Duplicates.
Bước 6: Find & Replace nâng cao (Wildcard)
Trong Excel, Find & Replace (Ctrl + H) hỗ trợ wildcard:
- * (dấu sao): đại diện cho bất kỳ chuỗi ký tự nào
Find: "Nguyễn*An" → Tìm: "Nguyễn Văn An", "Nguyễn Thị An", "Nguyễn An"
- ? (dấu hỏi): đại diện cho đúng 1 ký tự
Find: "DH-00?" → Tìm: "DH-001", "DH-002", ... "DH-009"
- ~ (dấu ngã): escape ký tự đặc biệt
Find: "~*" → Tìm dấu * thực sự (không phải wildcard)
Ví dụ thực tế:
- Find: "*@gmail*" → Replace: lọc tất cả email Gmail
- Find: "Q?/2026" → Tìm: "Q1/2026", "Q2/2026", "Q3/2026", "Q4/2026"Bước 7: Tạo Pivot Table — Step by Step
1. CHUẨN BỊ DỮ LIỆU:
- Đảm bảo dữ liệu đã clean (bước 1–6 ở trên)
- Header rõ ràng, không trùng tên cột
- Không có dòng trống xen giữa data
- Format dạng Table (Ctrl + T) → Excel tự mở rộng khi thêm dữ liệu
2. TẠO PIVOT TABLE:
- Chọn bất kỳ ô nào trong bảng dữ liệu
- Vào Insert → PivotTable
- Chọn "New Worksheet" hoặc "Existing Worksheet"
- Click OK
3. CẤU HÌNH PIVOT TABLE:
Ví dụ: "Tổng doanh thu theo Khu vực và Nhóm sản phẩm, theo quý"
- Kéo "Khu vực" → Rows
- Kéo "Nhóm SP" → Columns
- Kéo "Doanh thu" → Values (mặc định SUM)
- Kéo "Ngày mua" → Rows → Right-click → Group → chọn Quarters
4. TÙY CHỈNH:
- Click vào ô Values → Value Field Settings → chuyển SUM → AVERAGE, COUNT…
- Sort: Right-click → Sort → Largest to Smallest
- Filter: kéo trường vào Filters area, hoặc click dropdown trên Row/Column labels
5. CALCULATED FIELD:
- Vào PivotTable Analyze → Fields, Items & Sets → Calculated Field
- Ví dụ: Profit = Revenue - Cost
- Name: "Lợi nhuận"
- Formula: = Doanh_thu - Chi_phi
- Click Add → OK
6. PIVOT CHART:
- Click vào Pivot Table → Insert → PivotChart
- Chọn loại biểu đồ: Column, Bar, Line, Pie
- Chart tự động liên kết với Pivot Table — thay đổi filter/sort → chart cập nhậtVí dụ tổng hợp: Before → After Data Cleaning
BEFORE (10.000 dòng, trích mẫu 6 dòng):
| Mã ĐH | Tên KH | Thành phố | Ngày mua | SP | SL | Đơn giá | Doanh thu |
|---|---|---|---|---|---|---|---|
| dh-001 | Nguyễn Văn An | Hà Nội | 15/01/2026 | Áo thun | 2 | 250000 | 500000 |
| DH-001 | nguyễn văn an | Ha Noi | 2026-01-15 | Áo thun | 2 | 250000 | 500000 |
| DH-002 | Trần Thị Bình | TP.HCM | 16/01/2026 | Quần jean | 1 | 450000 | |
| DH-003 | LÊ VĂN CƯỜNG | Sài Gòn | Jan 17 2026 | áo khoác | 3 | 350000 | 1050000 |
| DH-004 | Phạm Đức Dũng | Da Nang | 18/01/2026 | giày thể thao | 1 | 800000 | -800000 |
| DH-005 | Trần Thị Bình | Ho Chi Minh | 19/01/2026 | Áo thun | 2 | 250000 | 500000 |
Cleaning formulas áp dụng (cột mới):
// Chuẩn hóa Mã ĐH
=UPPER(TRIM(A2)) → "DH-001"
// Chuẩn hóa Tên KH
=PROPER(TRIM(CLEAN(B2))) → "Nguyễn Văn An"
// Chuẩn hóa Thành phố
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(C2),
"TP.HCM","Hồ Chí Minh"),
"Sài Gòn","Hồ Chí Minh"),
"Ho Chi Minh","Hồ Chí Minh") → "Hồ Chí Minh"
// Chuẩn hóa Sản phẩm
=PROPER(TRIM(E2)) → "Áo Thun"
// Xử lý Doanh thu thiếu & âm
=IF(ISBLANK(H2), F2*G2, IF(H2<0, ABS(H2), H2)) → Tính lại hoặc lấy trị tuyệt đốiAFTER (sau khi clean + remove duplicates):
| Mã ĐH | Tên KH | Thành phố | Ngày mua | SP | SL | Đơn giá | Doanh thu |
|---|---|---|---|---|---|---|---|
| DH-001 | Nguyễn Văn An | Hà Nội | 15/01/2026 | Áo Thun | 2 | 250000 | 500000 |
| DH-002 | Trần Thị Bình | Hồ Chí Minh | 16/01/2026 | Quần Jean | 1 | 450000 | 450000 |
| DH-003 | Lê Văn Cường | Hồ Chí Minh | 17/01/2026 | Áo Khoác | 3 | 350000 | 1050000 |
| DH-004 | Phạm Đức Dũng | Đà Nẵng | 18/01/2026 | Giày Thể Thao | 1 | 800000 | 800000 |
| DH-005 | Trần Thị Bình | Hồ Chí Minh | 19/01/2026 | Áo Thun | 2 | 250000 | 500000 |
Kết quả: 6 dòng → 5 dòng (1 duplicate removed), 0 missing values, format nhất quán, dữ liệu sẵn sàng cho Pivot Table.
🏪 Ví dụ thực tế
🌍 Quốc tế: Airbnb — Data Cleaning cho 7 triệu Listings toàn cầu
Airbnb quản lý dữ liệu hơn 7 triệu listings tại 220+ quốc gia (tính đến đầu 2026). Dữ liệu đến từ hàng triệu host trên toàn thế giới — mỗi người nhập theo cách riêng → dirty data ở quy mô khổng lồ.
Các loại dirty data Airbnb phải xử lý:
| Loại lỗi | Ví dụ cụ thể | Giải pháp |
|---|---|---|
| Inconsistent format | Giá phòng: " | Chuẩn hóa về 1 currency (USD), loại ký tự đặc biệt bằng SUBSTITUTE + VALUE |
| Missing data | 15% listings thiếu ảnh, 8% thiếu mô tả, 3% thiếu giá | Phân loại: missing critical (giá) → yêu cầu bổ sung; missing optional (ảnh) → flag "incomplete" |
| Duplicate listings | Host đăng cùng 1 phòng nhiều lần để tăng visibility | Phát hiện duplicate qua: cùng địa chỉ + tương tự tên + cùng host → merge/remove |
| Outlier pricing | Phòng "1 USD/đêm" (lỗi nhập) hoặc "$99,999/đêm" (giá ảo) | IQR method theo location & room type → flag outlier → review thủ công |
| Typos/Inconsistency | Thành phố: "Ho Chi Minh City", "HCMC", "Saigon", "Hồ Chí Minh" | Mapping table chuẩn hóa → VLOOKUP hoặc SUBSTITUTE lồng nhau |
| Invalid data | Check-in time: "25:00", Số khách tối đa: "-2" | Data Validation rules + automated flagging |
Bài học cho DA:
- Ở quy mô nhỏ (< 100K dòng), Excel hoàn toàn xử lý được Data Cleaning
- Ở quy mô lớn hơn, cần Python (pandas) hoặc SQL — nhưng tư duy cleaning giống hệt nhau
- Luôn xây dựng data dictionary (bảng giải thích ý nghĩa, format, allowed values cho mỗi cột) — đây chính là Tidy Data nguyên tắc #3
🇻🇳 Việt Nam: Thế Giới Di Động — Chuẩn hóa dữ liệu 5.000+ cửa hàng
Thế Giới Di Động (MWG) vận hành hơn 5.000 cửa hàng trên toàn quốc (Thế Giới Di Động, Điện Máy Xanh, Bách Hóa Xanh). Với hàng triệu giao dịch mỗi ngày, Data Cleaning là công việc liên tục và hệ thống.
Bài toán thực tế:
| Vấn đề | Chi tiết | Giải pháp |
|---|---|---|
| Tên sản phẩm không nhất quán | "iPhone 15 Pro Max 256GB" vs "Iphone 15 pro max 256 GB" vs "IP 15 ProMax 256" | Xây dựng Master Product Table — mỗi sản phẩm có 1 Product_ID duy nhất, mapping bằng VLOOKUP/INDEX MATCH |
| Địa chỉ cửa hàng bẩn | "123 Nguyễn Huệ, Q.1, TP.HCM" vs "123 Nguyễn Huệ, Quận 1, Hồ Chí Minh" | Chuẩn hóa bằng SUBSTITUTE + PROPER + mapping table chuẩn quận/huyện |
| Doanh thu bị duplicate | 1 đơn hàng ghi 2 lần do sync lỗi giữa POS và hệ thống trung tâm | Remove Duplicates theo Mã ĐH + Thời gian + Mã SP; validate bằng Pivot Table đối chiếu tổng |
| Missing data giao hàng | 12% đơn hàng online thiếu thông tin trạng thái giao hàng | IF(ISBLANK(...)) → flag "Cần kiểm tra"; Pivot Table đếm missing theo ngày/khu vực → phát hiện lỗi hệ thống |
Kết quả:
- Giảm 35% sai sót trong báo cáo doanh thu hàng tháng
- Tiết kiệm 20 giờ/tuần nhờ tự động hóa Data Cleaning bằng Excel macro + template chuẩn hóa
- Dashboard doanh thu chính xác hơn → quyết định mở/đóng cửa hàng dựa trên dữ liệu sạch
Bài học cho DA: Trong doanh nghiệp lớn, Data Cleaning không phải việc làm 1 lần — nó là quy trình liên tục (continuous process). Hãy xây dựng template cleaning có thể tái sử dụng, không phải làm lại từ đầu mỗi khi nhận dữ liệu mới.
✅ Checklist buổi học
Sau buổi học này, bạn nên tự tin trả lời "Có" cho tất cả các câu hỏi dưới đây:
- [ ] Tôi nhận diện được 6 loại dữ liệu bẩn: duplicate, missing, formatting, typos, outlier, inconsistency
- [ ] Tôi hiểu Data Quality Dimensions: accuracy, completeness, consistency, timeliness, validity
- [ ] Tôi sử dụng thành thạo
TRIMvàCLEANđể loại khoảng trắng, ký tự ẩn - [ ] Tôi biết dùng
UPPER,LOWER,PROPERđể chuẩn hóa text - [ ] Tôi viết được
SUBSTITUTElồng nhau để chuẩn hóa dữ liệu không nhất quán - [ ] Tôi dùng
IFERRORđể bắt lỗi vàISBLANKđể phát hiện missing values - [ ] Tôi biết thứ tự đúng: chuẩn hóa text → Remove Duplicates (không làm ngược)
- [ ] Tôi hiểu 3 nguyên tắc Tidy Data của Hadley Wickham
- [ ] Tôi phân biệt được wide format vs long format và biết khi nào cần reshape
- [ ] Tôi thiết lập được Data Validation để ngăn dữ liệu bẩn từ đầu
- [ ] Tôi tạo được Pivot Table: kéo thả Rows, Columns, Values, Filters
- [ ] Tôi biết cách Group by Date trong Pivot Table (Month, Quarter, Year)
- [ ] Tôi tạo được Calculated Field trong Pivot Table
- [ ] Tôi tạo được Pivot Chart liên kết với Pivot Table
- [ ] Tôi thực hiện được quy trình 7 bước Data Cleaning hoàn chỉnh trên 1 bộ dữ liệu thực
🔑 Từ khóa quan trọng
| Tiếng Việt | English | Giải thích |
|---|---|---|
| Làm sạch dữ liệu | Data Cleaning / Data Cleansing | Quá trình loại bỏ lỗi, thiếu, trùng, không nhất quán trong data để dữ liệu sẵn sàng phân tích |
| Dữ liệu bẩn | Dirty Data | Data có lỗi, chưa qua xử lý, chưa sẵn sàng cho phân tích |
| Bảng tổng hợp | Pivot Table | Công cụ trong Excel để tổng hợp, nhóm, phân tích data đa chiều bằng kéo-thả |
| Dữ liệu gọn | Tidy Data | Cấu trúc dữ liệu chuẩn: mỗi biến 1 cột, mỗi quan sát 1 dòng, mỗi bảng 1 loại |
| Xác thực dữ liệu | Data Validation | Quy tắc kiểm tra dữ liệu đầu vào để ngăn lỗi ngay từ khi nhập |
| Dữ liệu trùng lặp | Duplicate Data | Bản ghi giống nhau xuất hiện nhiều lần trong dataset |
| Dữ liệu thiếu | Missing Data / Null Values | Ô trống hoặc giá trị không xác định trong dataset |
| Định dạng không nhất quán | Inconsistent Format | Cùng thông tin nhưng ghi khác format (ngày tháng, tên, mã) |
| Rác vào rác ra | Garbage In, Garbage Out (GIGO) | Nguyên tắc: dữ liệu bẩn → phân tích sai, bất kể tool hay model nào |
| Chất lượng dữ liệu | Data Quality | Mức độ chính xác, đầy đủ, nhất quán, kịp thời, hợp lệ của dữ liệu |
| Trường tính toán | Calculated Field | Cột mới trong Pivot Table được tạo từ công thức dựa trên các cột có sẵn |
| Biểu đồ tổng hợp | Pivot Chart | Biểu đồ liên kết trực tiếp với Pivot Table, tự cập nhật khi thay đổi filter |
| Chuẩn hóa dữ liệu | Data Standardization | Quá trình đưa dữ liệu về cùng format, cùng quy ước thống nhất |
| Từ điển dữ liệu | Data Dictionary | Tài liệu mô tả ý nghĩa, kiểu dữ liệu, format cho mỗi cột trong dataset |
🔗 Xem thêm Buổi 3
→ 📝 Blog → 🧠 Case Study → 🏆 Tiêu chuẩn → 🛠 Workshop → 🎮 Mini Game