🛠 Workshop Buổi 3: Data Cleaning Challenge

Nhận dataset bán hàng "bẩn" 500 dòng, audit lỗi, clean bằng hàm Excel, tạo Pivot Table phân tích đa chiều — tất cả trong một buổi!

🎯 Mục tiêu

Sau khi hoàn thành workshop này, bạn sẽ:

1. Audit được 15+ loại lỗi trong một dataset thực tế — liệt kê, phân loại và đánh giá mức độ nghiêm trọng của từng loại dirty data
2. Sử dụng thành thạo các hàm cleaning — TRIM, CLEAN, UPPER/LOWER/PROPER, SUBSTITUTE, IFERROR để làm sạch dữ liệu một cách hệ thống
3. Tạo Pivot Table phân tích sales theo 3 chiều (region, product category, month) với Calculated Fields và Pivot Chart
4. Hoàn thành deliverable chuyên nghiệp — file Excel gồm sheet raw, sheet cleaned, Pivot Table + báo cáo lỗi, sẵn sàng đưa vào portfolio Capstone

🧰 Công cụ & Setup

Công cụ	Phiên bản	Ghi chú
Microsoft Excel	2016+ / 365	Khuyến nghị Excel 365 cho công thức mảng động

Setup trước buổi học

1. Mở Microsoft Excel → tạo file mới
2. Tải dataset mẫu buoi03_sales_dirty.xlsx từ LMS hoặc link được phát trong lớp
3. Mở file → kiểm tra sheet "Raw_Data" có đủ 500 dòng dữ liệu
4. Tạo sẵn 3 sheet trống: "Audit", "Cleaned", "Pivot"
5. Bật Developer tab (tuỳ chọn): File → Options → Customize Ribbon → tick Developer

💡 Google Sheets cũng dùng được, nhưng một số tính năng Pivot Table và Find & Replace wildcard hoạt động tốt hơn trên Excel desktop.

📦 Dataset: Dữ liệu bán hàng "bẩn"

Dataset mô phỏng dữ liệu bán hàng của một chuỗi cửa hàng bán lẻ tại Việt Nam trong 12 tháng. Dataset được cố tình chèn 15+ loại lỗi phổ biến mà DA thường gặp khi nhận data từ hệ thống ERP, CRM, hoặc nhập liệu thủ công.

• Name: Vietnam Retail Sales — Dirty Dataset
• Rows: 500 đơn hàng (bao gồm ~30 dòng duplicate)
• Columns: 12 cột
• Thời gian: 01/2025 – 12/2025
• Ngữ cảnh: Chuỗi cửa hàng bán lẻ đa kênh tại Việt Nam, bán qua cửa hàng và online

Cột	Kiểu dữ liệu	Mô tả	Ví dụ (sạch)
Order_ID	string	Mã đơn hàng duy nhất	ORD-0001
Order_Date	date	Ngày đặt hàng (DD/MM/YYYY)	15/03/2025
Customer_Name	string	Tên khách hàng	Nguyễn Văn An
Phone	string	Số điện thoại	0901234567
Product_Name	string	Tên sản phẩm	Áo thun nam basic
Category	string	Danh mục (5 loại)	Thời trang, Điện tử, Gia dụng, Thực phẩm, Mỹ phẩm
Quantity	int	Số lượng mua	2
Unit_Price	float	Đơn giá (VNĐ)	350000
Revenue	float	Doanh thu = Quantity × Unit Price	700000
Region	string	Khu vực (4 vùng)	Bắc, Trung, Nam, Tây Nguyên
Payment_Method	string	Thanh toán	COD, Momo, Bank Transfer, Credit Card
Salesperson	string	Nhân viên bán hàng	Trần Thị Bình

15+ loại lỗi được cài sẵn trong dataset

#	Loại lỗi	Mô tả	Số dòng ảnh hưởng	Ví dụ trong dataset
1	Khoảng trắng thừa (leading/trailing)	Khoảng trắng đầu/cuối chuỗi	~40 dòng	" Nguyễn Văn An "
2	Khoảng trắng thừa giữa từ	Nhiều dấu cách giữa các từ	~25 dòng	"Nguyễn Văn An"
3	Viết hoa/thường không nhất quán	Customer_Name, Region	~50 dòng	"nguyễn văn an", "NGUYỄN VĂN AN", "bắc", "BẮC"
4	Duplicate rows (trùng hoàn toàn)	Dòng giống hệt nhau	~30 dòng	Cùng Order_ID, cùng mọi giá trị
5	Missing values (ô trống)	Ô trống ở các cột quan trọng	~35 dòng	Revenue trống, Phone trống, Region trống
6	Ngày tháng sai format	Nhiều format ngày trong cùng cột	~20 dòng	"2025-03-15", "Mar 15, 2025", "15-03-2025"
7	Ký tự ẩn (non-printable)	Ký tự line break, tab trong text	~15 dòng	Tên sản phẩm chứa CHAR(10), CHAR(9)
8	Tên thành phố/region không nhất quán	Cùng nơi, viết khác nhau	~25 dòng	"HCM", "TP.HCM", "Hồ Chí Minh" → đều là "Nam"
9	Revenue âm	Giá trị doanh thu âm bất thường	~5 dòng	-500000
10	Revenue ≠ Quantity × Unit_Price	Công thức tính sai	~15 dòng	Quantity=2, Unit_Price=100000, Revenue=150000
11	Số điện thoại sai format	Nhiều format SĐT	~20 dòng	"090-123-4567", "+84901234567", "0901 234 567"
12	Category typo	Lỗi chính tả tên danh mục	~10 dòng	"Thoi trang", "Dien tu", "Điện Tử"
13	Quantity = 0 hoặc âm	Số lượng không hợp lệ	~5 dòng	0, -1
14	Giá trị #N/A, #VALUE!	Lỗi công thức trong cột Revenue	~8 dòng	#N/A, #VALUE!
15	Dữ liệu text trong cột số	Text lẫn vào cột Quantity/Revenue	~5 dòng	"hai", "N/A", "không rõ"
16	Salesperson tên viết tắt	Không nhất quán cách viết	~10 dòng	"T.T.Bình" vs "Trần Thị Bình"

📥 Tải dataset: File buoi03_sales_dirty.xlsx được phát trong lớp hoặc tải từ LMS. Nếu tự tạo, hãy dùng bảng lỗi ở trên để cài lỗi vào dataset sạch.

⏱️ Thời lượng

Phần	Thời gian	Nội dung
Hướng dẫn & Setup	10 phút	Giới thiệu dataset, tạo sheet cấu trúc
Bài tập 1: Audit dữ liệu	20 phút	Liệt kê, phân loại, đếm lỗi
Bài tập 2: Data Cleaning	30 phút	Dùng hàm Excel làm sạch từng loại lỗi
Bài tập 3: Pivot Table	20 phút	Tạo Pivot Table + Calculated Field + Chart
Review & Feedback	10 phút	Trình bày, nhận xét chéo
Tổng	90 phút

---

Bài tập 1: Audit dữ liệu — Liệt kê & phân loại lỗi

Hướng dẫn

Mục tiêu: Khảo sát dataset, phát hiện và liệt kê tất cả các loại lỗi trước khi bắt tay vào sửa. Audit trước — Clean sau. Đây là quy trình chuẩn trong thực tế: không bao giờ clean mà không biết rõ dataset có bao nhiêu loại lỗi.

Bước 1 — Quan sát nhanh (3 phút)

1. Mở sheet "Raw_Data" — cuộn từ trên xuống dưới
2. Quan sát cột nào "nhìn là thấy lỗi" — viết hoa/thường lẫn lộn, ô trống, giá trị lạ
3. Chú ý cột Revenue — có giá trị âm, #N/A, text lẫn vào không?
4. Nhìn cột Order_Date — format ngày có đồng nhất không?

Bước 2 — Kiểm tra khoảng trắng thừa (3 phút)

1. Chọn ô bất kỳ trong cột Customer_Name → nhìn thanh Formula Bar
2. Nếu thanh Formula Bar hiện " Nguyễn Văn An " (có dấu cách đầu/cuối) → đó là lỗi khoảng trắng
3. Dùng công thức kiểm tra: so sánh LEN(A2) với LEN(TRIM(A2))

Bước 3 — Kiểm tra duplicate (3 phút)

1. Chọn toàn bộ dữ liệu → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
2. Chọn cột Order_ID để highlight → đếm số dòng bị highlight
3. Hoặc dùng hàm COUNTIF để đếm

Bước 4 — Kiểm tra missing values (3 phút)

1. Đếm ô trống từng cột bằng COUNTBLANK
2. Ghi lại cột nào có bao nhiêu ô trống
3. Đánh giá: thiếu dữ liệu ở cột nào là nghiêm trọng nhất?

Bước 5 — Kiểm tra outlier & invalid values (3 phút)

1. Revenue âm: =COUNTIF(I2:I501, "<0")
2. Quantity ≤ 0: =COUNTIF(G2:G501, "<=0")
3. Lỗi công thức: =SUMPRODUCT(--ISERROR(I2:I501))
4. Revenue ≠ Quantity × Unit_Price: =SUMPRODUCT((G2:G501*H2:H501<>I2:I501)*1)

Bước 6 — Tổng hợp báo cáo Audit (5 phút)

1. Chuyển sang sheet "Audit"
2. Tạo bảng liệt kê tất cả loại lỗi tìm được
3. Ghi rõ: Loại lỗi → Cột bị ảnh hưởng → Số dòng → Mức độ → Cách xử lý dự kiến

Code mẫu / Công thức kiểm tra

📌 Đếm tổng số dòng:
=COUNTA(A2:A501)

📌 Kiểm tra khoảng trắng thừa (TRUE = có lỗi):
=LEN(C2) <> LEN(TRIM(C2))

📌 Đếm dòng có khoảng trắng thừa ở cột Customer_Name:
=SUMPRODUCT((LEN(C2:C501)<>LEN(TRIM(C2:C501)))*1)

📌 Đếm duplicate Order_ID:
=ROWS(A2:A501) - SUMPRODUCT(1/COUNTIF(A2:A501, A2:A501))
→ Kết quả = số dòng trùng (tổng dòng - số giá trị duy nhất)

📌 Đếm ô trống từng cột:
Revenue:    =COUNTBLANK(I2:I501)
Phone:      =COUNTBLANK(D2:D501)
Region:     =COUNTBLANK(J2:J501)

📌 Đếm Revenue âm:
=COUNTIF(I2:I501, "<0")

📌 Đếm ô có lỗi công thức (#N/A, #VALUE!):
=SUMPRODUCT(--ISERROR(I2:I501))

📌 Đếm giá trị text lẫn trong cột số (Quantity):
=SUMPRODUCT(--ISTEXT(G2:G501))

📌 Đếm Quantity ≤ 0:
=COUNTIFS(G2:G501, "<=0", G2:G501, "<>")

📌 Đếm các giá trị Region duy nhất (để phát hiện inconsistency):
=COUNTA(UNIQUE(J2:J501))
→ Nếu > 4 (Bắc, Trung, Nam, Tây Nguyên) → có inconsistency

📌 Đếm các Category duy nhất:
=COUNTA(UNIQUE(F2:F501))
→ Nếu > 5 → có typo hoặc inconsistency

📌 Kiểm tra Revenue ≠ Quantity × Unit_Price (số dòng không khớp):
=SUMPRODUCT((ISNUMBER(G2:G501))*(ISNUMBER(H2:H501))*(ISNUMBER(I2:I501))*(G2:G501*H2:H501<>I2:I501)*1)

Kết quả mong đợi

Bảng Audit Report trên sheet "Audit":

#	Loại lỗi	Cột ảnh hưởng	Số dòng	Mức độ	Cách xử lý
1	Khoảng trắng thừa	Customer_Name, Product_Name	~65	Trung bình	TRIM
2	Ký tự ẩn	Product_Name	~15	Trung bình	CLEAN
3	Viết hoa/thường lẫn	Customer_Name, Region	~50	Cao	PROPER / UPPER
4	Duplicate rows	Tất cả cột	~30	Cao	Remove Duplicates
5	Missing values	Revenue, Phone, Region	~35	Cao	IF/ISBLANK, điền hoặc đánh dấu
6	Ngày sai format	Order_Date	~20	Cao	DATEVALUE, Text to Columns
7	Region inconsistency	Region	~25	Cao	SUBSTITUTE / Find & Replace
8	Revenue âm	Revenue	~5	Trung bình	ABS hoặc flag để xem xét
9	Revenue ≠ Qty × Price	Revenue	~15	Cao	Tính lại = Quantity × Unit_Price
10	SĐT sai format	Phone	~20	Thấp	SUBSTITUTE, Text to Columns
11	Category typo	Category	~10	Cao	SUBSTITUTE / Find & Replace
12	Quantity ≤ 0	Quantity	~5	Trung bình	Flag / xoá
13	Lỗi #N/A, #VALUE!	Revenue	~8	Cao	IFERROR
14	Text trong cột số	Quantity, Revenue	~5	Cao	VALUE, IFERROR
15	Salesperson viết tắt	Salesperson	~10	Thấp	SUBSTITUTE / bảng mapping

Tổng hợp:

Metric	Giá trị
Tổng dòng raw	500
Tổng loại lỗi phát hiện	15+
Tổng dòng bị ảnh hưởng (ước tính)	~250–300 (nhiều dòng có >1 lỗi)
Dòng cần xoá (duplicate)	~30
Dòng sạch sau khi clean (dự kiến)	~470

✅ Checkpoint: Bạn đã có bức tranh toàn diện về "sức khỏe" dataset. Bước tiếp theo là clean từng loại lỗi.

---

Bài tập 2: Data Cleaning — Làm sạch từng loại lỗi

Hướng dẫn

Mục tiêu: Tạo sheet "Cleaned" với dữ liệu đã được làm sạch hoàn toàn, sẵn sàng cho phân tích Pivot Table. Mỗi bước cleaning dùng công thức Excel cụ thể.

⚠️ Nguyên tắc vàng: KHÔNG BAO GIỜ sửa trực tiếp trên dữ liệu gốc. Luôn giữ sheet "Raw_Data" nguyên vẹn, tạo sheet mới để clean.

Bước 1 — Copy header sang sheet Cleaned (1 phút)

1. Copy dòng header từ Raw_Data sang sheet "Cleaned"
2. Thêm cột phụ nếu cần (ví dụ: Revenue_Recalculated)

Bước 2 — Xử lý khoảng trắng & ký tự ẩn (3 phút)

Áp dụng cho cột Customer_Name, Product_Name, Region, Category, Salesperson:

📌 Xoá khoảng trắng thừa + ký tự ẩn + chuẩn hoá viết hoa:
=TRIM(CLEAN(C2))

BEFORE → AFTER:

Before (Raw)	After (Cleaned)	Hàm dùng
" Nguyễn Văn An "	"Nguyễn Văn An"	TRIM
"Áo thun↵nam basic" (có line break)	"Áo thun nam basic"	CLEAN
" Tai nghe Bluetooth "	"Tai nghe Bluetooth"	TRIM(CLEAN(...))

Bước 3 — Chuẩn hóa viết hoa/thường (3 phút)

📌 Customer_Name — Viết hoa chữ cái đầu mỗi từ:
=PROPER(TRIM(CLEAN(C2)))

📌 Before → After:
"nguyễn văn an"    → "Nguyễn Văn An"
"NGUYỄN VĂN AN"   → "Nguyễn Văn An"
"nguyễn  văn   an" → "Nguyễn Văn An"

💡 Với tên tiếng Việt, PROPER hoạt động tốt. Nhưng nếu có tên kiểu "McDonald", PROPER sẽ cho "Mcdonald" — cần xử lý riêng bằng SUBSTITUTE.

Bước 4 — Chuẩn hóa Region (5 phút)

Dùng SUBSTITUTE lồng nhau hoặc Find & Replace để đồng nhất tên vùng:

📌 Chuẩn hóa Region bằng nested SUBSTITUTE:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  TRIM(UPPER(J2)),
  "HCM", "NAM"),
  "TP.HCM", "NAM"),
  "HỒ CHÍ MINH", "NAM"),
  "SÀI GÒN", "NAM")

📌 Cách tốt hơn — Dùng bảng mapping + VLOOKUP:
Tạo bảng mapping ở ô riêng:
  M2: "Bắc"        N2: "Bắc"
  M3: "BẮC"        N3: "Bắc"
  M4: "bắc"        N4: "Bắc"
  M5: "Trung"      N5: "Trung"
  M6: "Nam"        N6: "Nam"
  M7: "HCM"        N7: "Nam"
  M8: "TP.HCM"     N8: "Nam"
  M9: "Tây Nguyên" N9: "Tây Nguyên"
  ...

Công thức:
=IFERROR(VLOOKUP(TRIM(UPPER(J2)), $M:$N, 2, FALSE), TRIM(PROPER(J2)))

BEFORE → AFTER:

Before (Raw)	After (Cleaned)
"bắc"	"Bắc"
"BẮC"	"Bắc"
"HCM"	"Nam"
"TP.HCM"	"Nam"
"Hồ Chí Minh"	"Nam"
"Tay Nguyen"	"Tây Nguyên"

Bước 5 — Chuẩn hóa Category (3 phút)

📌 Fix typo Category bằng nested SUBSTITUTE:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  TRIM(PROPER(F2)),
  "Thoi Trang", "Thời Trang"),
  "Dien Tu", "Điện Tử"),
  "Gia Dung", "Gia Dụng")

📌 Hoặc dùng Find & Replace:
Ctrl + H → Find: "Thoi trang" → Replace: "Thời Trang" → Replace All
Ctrl + H → Find: "Dien tu"    → Replace: "Điện Tử"    → Replace All

Bước 6 — Xử lý lỗi công thức & giá trị không hợp lệ (5 phút)

📌 Xử lý #N/A, #VALUE! trong cột Revenue:
=IFERROR(I2, G2*H2)
→ Nếu Revenue bị lỗi → tính lại = Quantity × Unit_Price

📌 Xử lý text lẫn trong cột Quantity:
=IFERROR(VALUE(TRIM(G2)), 0)
→ Nếu Quantity là text → trả về 0 (flag để xem xét sau)

📌 Tính lại Revenue cho TẤT CẢ dòng (đảm bảo chính xác):
=IFERROR(IF(AND(ISNUMBER(G2), ISNUMBER(H2), G2>0), G2*H2, 0), 0)

BEFORE → AFTER:

Quantity (Raw)	Unit_Price (Raw)	Revenue (Raw)	Revenue (Cleaned)
2	100000	150000	200000 (tính lại)
3	250000	#N/A	750000 (IFERROR)
"hai"	100000	#VALUE!	0 (flag)
-1	350000	-350000	0 (invalid qty)

Bước 7 — Xử lý missing values (3 phút)

📌 Đánh dấu missing values:
=IF(ISBLANK(J2), "MISSING", TRIM(PROPER(J2)))

📌 Hoặc điền giá trị mặc định cho Region:
=IF(ISBLANK(J2), "Không xác định", TRIM(PROPER(J2)))

📌 Revenue trống → tính lại nếu có Quantity và Unit_Price:
=IF(ISBLANK(I2), IF(AND(ISNUMBER(G2), ISNUMBER(H2)), G2*H2, "MISSING"), I2)

Bước 8 — Chuẩn hóa số điện thoại (2 phút)

📌 Loại bỏ dấu gạch, khoảng trắng, +84 → format chuẩn 10 số:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  TRIM(D2), "-", ""), " ", ""), "+84", "0"), ".", "")

BEFORE → AFTER:

Before (Raw)	After (Cleaned)
"090-123-4567"	"0901234567"
"+84901234567"	"0901234567"
"0901 234 567"	"0901234567"

Bước 9 — Xoá Duplicate (2 phút)

⚠️ Thực hiện bước này SAU KHI đã clean tất cả các cột khác (vì sau khi chuẩn hóa, có thể phát hiện thêm duplicate mới).

1. Chọn toàn bộ dữ liệu trên sheet "Cleaned"
2. Vào Data → Remove Duplicates
3. Tick tất cả cột → OK
4. Excel sẽ thông báo: "X duplicate values found and removed. Y unique values remain."

Hoặc dùng hàm để đánh dấu trước khi xoá:

📌 Đánh dấu duplicate (dòng xuất hiện lần 2 trở đi):
=IF(COUNTIF($A$2:A2, A2) > 1, "DUPLICATE", "UNIQUE")
→ Kéo xuống toàn bộ → Filter chỉ giữ "UNIQUE"

Bước 10 — Kiểm tra sau khi clean (3 phút)

📌 Đếm dòng còn lại sau khi xoá duplicate:
=COUNTA(A2:A471)   → ~470 dòng

📌 Kiểm tra Region chỉ còn 4 giá trị:
=COUNTA(UNIQUE(J2:J471))   → phải = 4

📌 Kiểm tra Category chỉ còn 5 giá trị:
=COUNTA(UNIQUE(F2:F471))   → phải = 5

📌 Kiểm tra không còn ô trống ở Revenue:
=COUNTBLANK(I2:I471)   → phải = 0

📌 Kiểm tra không còn Revenue âm:
=COUNTIF(I2:I471, "<0")   → phải = 0

📌 Kiểm tra không còn lỗi công thức:
=SUMPRODUCT(--ISERROR(I2:I471))   → phải = 0

Kết quả mong đợi

So sánh BEFORE vs AFTER tổng thể:

Metric	Before (Raw)	After (Cleaned)
Tổng dòng	500	~470
Duplicate	~30	0
Missing Revenue	~35	0 (đã tính lại hoặc flag)
Lỗi #N/A, #VALUE!	~8	0
Số Region duy nhất	8+ (inconsistent)	4 (Bắc, Trung, Nam, Tây Nguyên)
Số Category duy nhất	8+ (có typo)	5
Revenue âm	~5	0
Khoảng trắng thừa	~65 dòng	0

✅ Checkpoint: Sheet "Cleaned" đã sẵn sàng cho Pivot Table. Mỗi cột đã đồng nhất format, không còn lỗi.

---

Bài tập 3: Pivot Table — Phân tích sales đa chiều

Hướng dẫn

Mục tiêu: Tạo Pivot Table phân tích doanh thu theo 3 chiều: Region, Category, Month. Thêm Calculated Field và tạo Pivot Chart trực quan.

Bước 1 — Tạo Pivot Table (3 phút)

1. Chọn toàn bộ dữ liệu trên sheet "Cleaned" (bao gồm header)
2. Vào Insert → PivotTable
3. Chọn "New Worksheet" → đặt tên sheet là "Pivot"
4. PivotTable Fields panel sẽ xuất hiện bên phải

Bước 2 — Pivot 1: Doanh thu theo Region (3 phút)

1. Kéo Region vào Rows
2. Kéo Revenue vào Values → chọn Sum of Revenue
3. Kéo Revenue lần nữa vào Values → chọn Count of Revenue (= số đơn)
4. Sắp xếp giảm dần theo Sum of Revenue

Kết quả mong đợi — Pivot 1:

Region	Sum of Revenue	Count of Revenue
Nam	~450,000,000	~155
Bắc	~380,000,000	~130
Trung	~250,000,000	~105
Tây Nguyên	~170,000,000	~80
Grand Total	~1,250,000,000	~470

Bước 3 — Pivot 2: Doanh thu theo Region × Category (5 phút)

1. Giữ Region ở Rows
2. Kéo Category vào Columns
3. Values giữ Sum of Revenue
4. Bạn sẽ thấy bảng chéo Region × Category

Kết quả mong đợi — Pivot 2:

Region	Điện tử	Gia dụng	Mỹ phẩm	Thời trang	Thực phẩm	Grand Total
Nam	~150M	~90M	~60M	~100M	~50M	~450M
Bắc	~120M	~80M	~55M	~85M	~40M	~380M
Trung	~80M	~55M	~35M	~50M	~30M	~250M
Tây Nguyên	~55M	~35M	~25M	~35M	~20M	~170M

Bước 4 — Pivot 3: Doanh thu theo Month (5 phút)

1. Tạo Pivot Table mới (hoặc copy Pivot Table hiện tại)
2. Kéo Order_Date vào Rows → Excel tự động group theo Months
3. Nếu không tự group: chuột phải lên ngày → Group → chọn Months
4. Kéo Revenue vào Values → Sum

💡 Nếu Order_Date không tự group, có thể cột ngày chưa đúng format Date. Quay lại sheet Cleaned và đảm bảo cột Order_Date là kiểu Date (Format Cells → Date).

Kết quả mong đợi — Pivot 3:

Month	Sum of Revenue
Jan	~95,000,000
Feb	~85,000,000
Mar	~100,000,000
Apr	~90,000,000
May	~95,000,000
Jun	~105,000,000
Jul	~110,000,000
Aug	~100,000,000
Sep	~95,000,000
Oct	~115,000,000
Nov	~125,000,000
Dec	~135,000,000

📌 Xu hướng: Doanh thu tăng vào Q4 (mùa mua sắm cuối năm) — pattern phổ biến trong retail Việt Nam.

Bước 5 — Calculated Field: Doanh thu trung bình / đơn (3 phút)

1. Click vào Pivot Table → PivotTable Analyze → Fields, Items & Sets → Calculated Field
2. Name: Avg_Revenue_Per_Order
3. Formula: = Revenue / Quantity
4. Click Add → OK
5. Cột mới xuất hiện trong Pivot Table

📌 Calculated Field formula:
Name:    Avg_Revenue_Per_Order
Formula: = Revenue / Quantity

📌 Hoặc đơn giản hơn:
Kéo Revenue vào Values → click chuột phải → Value Field Settings
→ chọn "Average" thay vì "Sum"

Bước 6 — Pivot Chart (3 phút)

1. Click vào Pivot Table (Pivot 1: Region)
2. Vào PivotTable Analyze → PivotChart (hoặc Insert → Chart)
3. Chọn Column Chart (cột đứng) cho doanh thu theo Region
4. Thêm tiêu đề: "Doanh thu theo khu vực — 2025"
5. Thêm Data Labels để hiện số trên cột

Tạo thêm chart cho xu hướng theo tháng:

1. Click vào Pivot Table Month
2. Insert → Line Chart (biểu đồ đường)
3. Tiêu đề: "Xu hướng doanh thu theo tháng — 2025"
4. Thêm Trendline: chuột phải lên đường → Add Trendline → Linear

Kết quả mong đợi

Sau bài tập 3, bạn sẽ có:

#	Output	Mô tả
1	Pivot Table 1	Doanh thu + số đơn theo Region
2	Pivot Table 2	Bảng chéo Region × Category
3	Pivot Table 3	Doanh thu theo Month (xu hướng thời gian)
4	Calculated Field	Doanh thu trung bình / đơn hàng
5	Pivot Chart — Column	Biểu đồ cột doanh thu theo Region
6	Pivot Chart — Line	Biểu đồ đường xu hướng doanh thu theo tháng

3 Insights rút ra từ Pivot Table:

Insight 1: Khu vực Nam đóng góp ~36% tổng doanh thu (~450M/1.25B), dẫn đầu toàn quốc, gấp 2.6× khu vực Tây Nguyên.

Insight 2: Category "Điện tử" chiếm tỷ trọng cao nhất (~32% tổng doanh thu) ở tất cả khu vực, nhưng tại Tây Nguyên, "Gia dụng" gần bắt kịp "Điện tử" — cơ hội mở rộng.

Insight 3: Doanh thu Q4 (Oct–Dec: ~375M) cao hơn Q1 (Jan–Mar: ~280M) khoảng 34%, phản ánh mùa mua sắm cuối năm. Đề xuất tăng stock và chạy khuyến mãi sớm từ tháng 9.

✅ Checkpoint: Bạn đã biết tạo Pivot Table đa chiều, Calculated Field và Pivot Chart — công cụ phân tích mạnh nhất trong Excel.

---

🏆 Bài tập Bonus: Nâng cao

Dành cho bạn hoàn thành sớm hoặc muốn thử thách thêm.

Bonus 1: Slicer & Timeline

1. Click vào Pivot Table → PivotTable Analyze → Insert Slicer
2. Chọn Region và Category → OK
3. Hai slicer xuất hiện — click để lọc Pivot Table tương tác
4. Thêm Timeline: PivotTable Analyze → Insert Timeline → chọn Order_Date
5. Kéo timeline để lọc theo tháng/quý

Bonus 2: Top 10 sản phẩm bán chạy nhất

1. Tạo Pivot Table mới: Rows = Product_Name, Values = Sum of Revenue
2. Sắp xếp giảm dần
3. Chuột phải lên dòng đầu → Filter → Top 10
4. Tạo Bar Chart (biểu đồ thanh ngang) cho top 10

Bonus 3: Find & Replace với Wildcards

Thực hành Find & Replace nâng cao với ký tự đại diện:

📌 Tìm tất cả Order_ID bắt đầu bằng "ORD-0":
Find: ORD-0*          (* = bất kỳ ký tự nào)

📌 Tìm tên khách có đúng 3 từ:
Find: ? * ? * ?       (? = 1 ký tự bất kỳ)

📌 Tìm và thay tất cả variant của "Hồ Chí Minh":
Find: *Chi Minh*      → Replace: "Nam"

📌 Tìm ký tự ~ thật sự (vì ~ là escape character):
Find: ~~              (dùng ~~ để tìm dấu ~)

---

📦 Deliverable

#	Deliverable	Format	Mô tả
1	File Excel hoàn chỉnh	.xlsx	Sheet 1 — Raw_Data: Dữ liệu gốc (giữ nguyên, không sửa)
			Sheet 2 — Audit: Báo cáo liệt kê 15+ loại lỗi + số lượng + mức độ + cách xử lý
			Sheet 3 — Cleaned: Dữ liệu đã clean hoàn toàn (~470 dòng)
			Sheet 4 — Pivot: 3 Pivot Tables + Calculated Field + 2 Pivot Charts
2	Báo cáo lỗi	1 trang (trong sheet Audit hoặc file riêng)	Bảng audit + tổng hợp before/after + 3 insights từ Pivot

🎯 Output này đóng góp vào Capstone Project: Kỹ năng Data Cleaning và Pivot Table là nền tảng cho giai đoạn Process → Analyze của Capstone. File cleaned sẽ là input cho các buổi phân tích nâng cao sau.

Cấu trúc file nộp

HọTên_Buoi03_Workshop.xlsx
├── Sheet 1: Raw_Data
│   └── 500 dòng dữ liệu gốc (không sửa đổi)
├── Sheet 2: Audit
│   ├── Bảng liệt kê 15+ loại lỗi
│   ├── Số lượng dòng bị ảnh hưởng mỗi loại
│   ├── Mức độ nghiêm trọng (Cao / Trung bình / Thấp)
│   └── Cách xử lý dự kiến / đã xử lý
├── Sheet 3: Cleaned
│   ├── ~470 dòng dữ liệu đã clean
│   ├── Tất cả cột đã chuẩn hóa format
│   └── Không còn duplicate, missing, error
├── Sheet 4: Pivot
│   ├── Pivot Table 1: Revenue theo Region
│   ├── Pivot Table 2: Region × Category
│   ├── Pivot Table 3: Revenue theo Month
│   ├── Calculated Field: Avg Revenue / Order
│   ├── Pivot Chart: Column (Region)
│   └── Pivot Chart: Line (Monthly trend)
└── (Bonus) Sheet 5: Dashboard
    └── Slicer + Timeline + Top 10 Products

Cách nộp:

1. Đặt tên file: HoTen_Buoi03_DataCleaning.xlsx
2. Upload lên LMS hoặc Google Drive (share link với quyền view)
3. Đảm bảo tất cả sheet đều hiển thị khi mở file

📊 Tiêu chí chấm điểm

Tiêu chí	Trọng số	Mô tả chi tiết
Audit đầy đủ	20%	Phát hiện ≥12/15 loại lỗi; mỗi loại ghi rõ cột, số dòng, mức độ. Dùng công thức COUNTBLANK, COUNTIF, ISERROR.
Data Cleaning chính xác	35%	Áp dụng đúng hàm cho từng loại lỗi (TRIM, CLEAN, PROPER, SUBSTITUTE, IFERROR). Dữ liệu clean không còn duplicate, missing, error. Region = 4, Category = 5.
Pivot Table & Chart	30%	Tạo đủ 3 Pivot Tables (Region, Region×Category, Month). Có Calculated Field. Có ≥1 Pivot Chart với label đầy đủ.
Trình bày & Insights	15%	File có cấu trúc rõ ràng (4 sheets đúng tên). Có ≥3 insights từ Pivot Table với số liệu. Format số liệu nhất quán.

Thang điểm chi tiết

Mức	Điểm	Mô tả
Xuất sắc	9–10	Đầy đủ 15+ lỗi, clean hoàn hảo, 3 Pivot Tables + Charts, insights sâu + Bonus
Tốt	7–8	≥12 lỗi, clean tốt (1–2 lỗi nhỏ), 3 Pivot Tables, insights hợp lý
Đạt	5–6	≥8 lỗi, clean cơ bản, ≥1 Pivot Table, insights chung chung
Chưa đạt	< 5	< 8 lỗi, clean thiếu nhiều, không có Pivot Table

💡 Tips & Common Mistakes

✅ Tips

1. Luôn giữ nguyên dữ liệu gốc (Raw_Data). Đây là nguyên tắc #1 trong Data Engineering. Nếu clean sai, bạn có thể quay lại raw và làm lại. Không bao giờ sửa trực tiếp file gốc.

2. Clean theo thứ tự: Text → Format → Logic → Duplicate. Xử lý khoảng trắng, ký tự ẩn trước (TRIM, CLEAN) → chuẩn hóa viết hoa/thường (PROPER) → sửa giá trị logic (IFERROR, tính lại Revenue) → xoá duplicate cuối cùng (vì sau khi chuẩn hóa text, có thể phát hiện thêm duplicate).

3. Dùng Paste Values sau khi clean. Công thức cleaning tham chiếu đến Raw_Data. Sau khi hoàn tất, hãy Copy → Paste Special → Values để giữ giá trị, bỏ công thức. Điều này giúp file nhẹ hơn và không bị lỗi khi xoá sheet Raw.

4. Pivot Table — Right-click là bạn. Chuột phải trong Pivot Table cho phép: Sort, Filter, Group, Show Values As (% of Total, Running Total, Difference), Refresh Data. Hãy thử tất cả!

5. Find & Replace với wildcard rất mạnh. * = bất kỳ chuỗi nào, ? = 1 ký tự bất kỳ, ~ = escape. Dùng Ctrl + H nhanh hơn viết SUBSTITUTE cho nhiều trường hợp.

6. Freeze Panes khi clean. Vào View → Freeze Panes → Freeze Top Row để giữ header khi cuộn — đặc biệt quan trọng với dataset 500 dòng.

7. Dùng IFERROR bọc ngoài mọi công thức phức tạp. Thà nhận 0 hoặc "ERROR" rõ ràng, còn hơn để #VALUE! lan ra cả sheet.

❌ Common Mistakes

Lỗi	Giải thích	Cách sửa
Sửa trực tiếp trên Raw_Data	Mất dữ liệu gốc, không thể kiểm tra lại	Luôn tạo sheet mới "Cleaned"
Xoá duplicate TRƯỚC khi chuẩn hóa text	Có thể bỏ sót duplicate do khác viết hoa/thường	TRIM + PROPER trước → Remove Duplicates sau
TRIM không xử lý được tab/line break	TRIM chỉ xử lý khoảng trắng (space), không xử lý CHAR(9) tab, CHAR(10) line break	Dùng TRIM(CLEAN(...)) — CLEAN xoá non-printable chars
Pivot Table không group ngày	Cột Order_Date chưa đúng kiểu Date (có thể là Text)	Kiểm tra Format Cells → đảm bảo là Date. Dùng DATEVALUE nếu cần convert
Quên Refresh Pivot Table sau khi sửa data	Pivot Table không tự cập nhật khi data thay đổi	Chuột phải → Refresh, hoặc PivotTable Analyze → Refresh
Revenue = 0 mà không flag	Dòng Quantity=0 hoặc lỗi khác bị "ẩn" sau IFERROR	Dùng cột phụ flag: =IF(Revenue_Clean=0, "CHECK", "OK")
Nhầm SUBSTITUTE với REPLACE	SUBSTITUTE thay theo nội dung text, REPLACE thay theo vị trí	Dùng SUBSTITUTE khi biết text cần thay; REPLACE khi biết vị trí ký tự

📚 Tài liệu tham khảo

• Excel TRIM function — Microsoft Support
• Excel CLEAN function — Microsoft Support
• Excel SUBSTITUTE function — Microsoft Support
• Excel PivotTable guide — Microsoft Support
• Tidy Data — Hadley Wickham (2014) — Journal of Statistical Software
• Google Data Analytics Certificate — Process Data — Coursera
• ExcelJet — Data Cleaning Functions — exceljet.net
• Wildcards in Find & Replace — Microsoft Support

🔗 Xem thêm Buổi 3

→ 📘 Nội dung chính → 📝 Blog → 🧠 Case Study → 🏆 Tiêu chuẩn → 🎮 Mini Game