Tuần 2 · Ngày 10 · Tổng kết tuần · 4 giờ

Mục tiêu

  • Củng cố Unpivot, Group By trên một bộ dữ liệu mới
  • Hoàn thành mini project "Pipeline dữ liệu Q1/2025"
  • Làm bài kiểm tra tuần 2

Dữ liệu cần tải

Ngày 5: Review 4 + Tổng kết tuần 2

Kế hoạch 4 giờ

Thời gianNội dung
1 giờPhần 1: Review ngày 4 với dữ liệu lịch viếng thăm
1 giờ 30Phần 2: Mini project
1 giờPhần 3: Kiểm tra tuần 2
30 phútPhần 4: Tự đánh giá

Bối cảnh dữ liệu mới: lịch viếng thăm cửa hàng

Ở công ty FMCG, nhân viên bán hàng (salesman) đi tuyến hằng ngày để viếng thăm cửa hàng và lấy đơn. Phòng nhân sự bán hàng theo dõi bằng file LichViengTham_Q1_2025.xlsx:

  • 3 sheet: T1-2025, T2-2025, T3-2025.
  • Mỗi dòng là một nhân viên. Mỗi ngày trong tháng là một cột (1, 2, …, 31). Đây là dạng rộng.
  • Mã trong ô:
MãÝ nghĩa
XViếng thăm có đơn hàng
VViếng thăm không có đơn
PNghỉ phép
KVắng không phép
LNghỉ lễ / Tết
(trống)Chủ nhật

Phần 1: Review ngày 4

Lịch viếng thăm tháng 1 (45 phút)

Query mới từ sheet T1-2025, đặt tên ViengTham_T1. Tạo bảng dạng dài với các cột:

CộtKiểu
Mã NVText
Họ và tênText
NgàyDate (ví dụ 02/01/2025)
MãText (X / V / P / K / L)

Trả lời:

  1. Bảng có bao nhiêu dòng? Có bao nhiêu nhân viên?
  2. Đếm số dòng theo từng Mã. Dùng Group By hoặc visual sau khi nạp.
  3. Tỷ lệ viếng thăm có đơn = X / (X + V) của tháng 1 là bao nhiêu?

Các bước:

  1. Get data > Excel > sheet T1-2025 > Transform Data. Dòng đầu đã là tên cột (Mã NV, Họ và tên, 1, 2, …).
  2. Chọn Mã NV, Họ và tên > Unpivot Other Columns. Đổi tên Attribute thành Ngày, Value thành Mã.
  3. Cột Ngày đang là văn bản “1”, “2”, … Đổi kiểu sang Whole Number, rồi tạo Custom Column: 
    #date(2025, 1, [Ngày])
    Xóa cột Ngày cũ, đổi tên cột mới thành Ngày, đổi kiểu Date.

Đáp số:

  1. 1.512 dòng, 56 nhân viên. Các ô Chủ nhật trống đã tự bị loại khi Unpivot.
  2. Theo mã:
MãSố dòng
X658
V458
L280
P79
K37

  1. Tỷ lệ có đơn tháng 1 = 658 / (658 + 458), khoảng 59,0%.

    L = 280 là các ngày nghỉ Tết 28 - 31/01/2025: 4 ngày x 56 nhân viên = 224, cộng ngày 1/1 (Tết Dương lịch) 56 dòng = 280.

Lưu ý: file có 56 nhân viên, nhưng công ty chỉ có 54 nhân viên chính thức. Có 2 dòng lạ (NV901, NV902). Tuần 3 bạn sẽ dùng Merge kiểu Left Anti để phát hiện tự động.

Group By + Pivot: bảng công của nhân viên (15 phút)

Từ ViengTham_T1, tạo bảng tổng hợp mỗi nhân viên một dòng, mỗi mã một cột (X, V, P, K, L), giá trị là số ngày.

Nhân viên nào có nhiều ngày K (vắng không phép) nhất trong tháng 1?

Cách 1: Reference query > Group By (Mã NV, Họ và tên, Mã; Count Rows) > chọn cột Mã > Pivot Column, Values = cột đếm, Don’t Aggregate.

Cách 2 (ngắn hơn): Reference query > chọn cột Mã > Pivot Column, Values Column = Ngày, Aggregate = Count (All).

Pivot sẽ tạo các ô null khi nhân viên không có mã đó. Hãy Replace Values null → 0 cho các cột số.

Sắp xếp giảm dần cột K: đồng hạng nhất là NV008 (Phạm Thanh Châu) và NV016 (Võ Gia Hoa), mỗi người 3 ngày K.

Kiểm tra lại bằng cách lọc bảng dài ViengTham_T1 theo Mã NV = NV008 và Mã = K, xem có đúng 3 dòng không. Tự đối chiếu bằng 2 cách khác nhau là thói quen quan trọng nhất của người làm dữ liệu.

Phần 2: Mini project “Pipeline dữ liệu Q1/2025”

Đề bài. Giám đốc kinh doanh muốn biết trong Q1/2025: (1) doanh thu thực tế so với chỉ tiêu theo từng tháng, (2) hiệu quả viếng thăm của đội bán hàng trong tháng 3.

Tạo file SaoMai_Q1_Pipeline.pbix với 3 query sạch:

  1. DonHang: từ file đơn hàng bẩn, làm sạch đầy đủ như Review 3, có cột Thành tiền và Tháng.
  2. ChiTieu: từ ChiTieu_2025.xlsx, dạng dài, có cột Tháng (số) và Chỉ tiêu (đồng).
  3. ViengTham_T3: sheet T3-2025, dạng dài như bài 1.

Một trang báo cáo Q1/2025 gồm:

  • Matrix 1 (DonHang): Rows = Tháng, Values = Thành tiền.
  • Matrix 2 (ChiTieu): Rows = Tháng, Values = Chỉ tiêu, lọc Tháng 1 - 3 bằng visual filter.
  • Card: tỷ lệ viếng thăm có đơn tháng 3. Tính bằng tay từ 2 card đếm X và V, hoặc dùng Group By trong Power Query.
  • Bar chart: số ngày theo Mã (X/V/P/K/L) tháng 3.

Mọi query phải chạy lại không lỗi khi Refresh. Không sửa tay file gốc.

Đối chiếu số liệu:

ThángDoanh thu thực tếChỉ tiêu% đạt (tính tay)
15.264.785.7905.400.000.000khoảng 97,5%
23.240.399.0803.324.000.000khoảng 97,5%
33.585.838.8203.661.000.000khoảng 97,9%
Q112.091.023.69012.385.000.000khoảng 97,6%

Viếng thăm tháng 3: X = 596, V = 701, P = 118, K = 41. Không có ngày lễ trong tháng 3. Tỷ lệ có đơn = 596 / 1.297, khoảng 46,0%, thấp hơn nhiều so với tháng 1 (59%) vì sau Tết cửa hàng còn tồn hàng.

Điều còn bất tiện: cột % đạt phải tính tay, vì 2 bảng chưa có quan hệ. Tuần 5 - 6 sẽ giải quyết bằng mô hình dữ liệu và measure DAX.

Phần 3: Kiểm tra tuần 2

Thời gian: 60 phút. 10 câu trắc nghiệm (10 điểm) + 1 bài thực hành (10 điểm). Đạt: từ 14/20.

Thực hành (30 phút, 10 điểm). Sheet T2-2025 của LichViengTham_Q1_2025.xlsx:

  1. (4 điểm) Đưa về dạng dài: Mã NV, Họ và tên, Ngày (kiểu Date), Mã.
  2. (3 điểm) Đếm số dòng theo từng Mã.
  3. (3 điểm) Nhân viên nào có nhiều ngày X nhất trong tháng 2? Bao nhiêu ngày?

  1. Bảng dài có 1.344 dòng. Ngày tạo bằng #date(2025, 2, [Ngày]).

  2. Theo mã:

MãSố dòng
V623
X514
P102
L56
K49

L = 56 là ngày 01/02/2025 (mùng 4 Tết, công ty vẫn nghỉ). Ngày 02/02 là Chủ nhật nên trống.

  1. Đồng hạng nhất: NV001 và NV032, mỗi người 18 ngày X. Nếu bạn chỉ trả lời một người thì vẫn được 2/3 điểm. Hãy tập thói quen kiểm tra có đồng hạng không khi trả lời câu hỏi “ai cao nhất”.

Chấm điểm: mục 1 được 4 điểm nếu đúng số dòng và kiểu Date; được 2 điểm nếu đúng dạng dài nhưng cột Ngày vẫn là số.

Phần 4: Tự đánh giá tuần 2

  • Kiểm tra tuần 2 đạt từ 14/20
  • Mini project chạy Refresh không lỗi, số liệu khớp bảng đối chiếu
  • Tôi làm sạch file DonHang bẩn trong 40 phút mà không xem lại bài
  • Tôi nhận ra và xử lý được: dòng rác, dòng trùng, Locale, null, hoa/thường, dạng rộng
  • Tôi đọc hiểu được code M của các bước đơn giản (Table.SelectRows, Table.AddColumn)