Appearance
📘 Buổi 4: Excel Dashboard & Phân tích bán hàng
Biến Pivot Table thành Dashboard để sếp nhìn 1 cái là hiểu.
🎯 Mục tiêu buổi học
Sau buổi này, học viên sẽ:
- Thiết kế Excel Dashboard chuyên nghiệp theo best practices (IBCS, Tufte's Principles)
- Sử dụng thành thạo VLOOKUP/XLOOKUP, INDEX/MATCH, IF lồng nhau để tra cứu và xử lý logic
- Tạo dynamic chart với Slicer và Timeline — dashboard tương tác không cần VBA
- Thực hiện phân tích bán hàng end-to-end: revenue, growth rate, top products, seasonality
📋 Tổng quan
Ở Buổi 3, bạn đã học cách làm sạch dữ liệu bẩn và tạo Pivot Table để tổng hợp dữ liệu đa chiều. Dữ liệu đã sạch, Pivot Table đã chạy — nhưng bạn không thể gửi cho sếp 1 bảng Pivot Table rồi bảo "anh/chị tự đọc". Sếp cần insight, cần nhìn 1 cái là hiểu doanh thu tăng hay giảm, sản phẩm nào bán chạy, khu vực nào cần đẩy mạnh. Đó chính là lý do bạn cần Dashboard.
Dashboard không phải chỉ là "biểu đồ đẹp". Dashboard là công cụ ra quyết định — nơi những con số được chuyển thành câu chuyện trực quan, giúp stakeholder hành động nhanh và đúng. Theo khảo sát của Aberdeen Group, các doanh nghiệp sử dụng dashboard đưa ra quyết định nhanh hơn 5 lần so với doanh nghiệp chỉ dùng báo cáo truyền thống. McKinsey ước tính doanh nghiệp data-driven có xác suất lợi nhuận cao hơn 23 lần so với đối thủ.
Nhưng trước khi xây Dashboard, bạn cần hàm tra cứu và logic — chính là bộ công cụ giúp bạn liên kết dữ liệu giữa các bảng, tính toán KPI, và xử lý điều kiện phức tạp. VLOOKUP, INDEX/MATCH, IF/IFS, SUMIFS — đây là "xương sống" của mọi mô hình phân tích trong Excel. Kết hợp thêm kiến thức Sales Analysis Framework — revenue breakdown, growth rate MoM/QoQ/YoY, Pareto 80/20 — bạn sẽ có đầy đủ vũ khí để thực hiện phân tích bán hàng chuyên nghiệp.
Buổi học hôm nay đưa bạn từ bước Analyze sang Share trong Google Data Analytics Framework: từ data sạch đến insight, từ insight đến dashboard, từ dashboard đến quyết định kinh doanh.
📌 Phần 1: Hàm tra cứu & logic (Lookup & Logic Functions)
Khái niệm
Hàm tra cứu (Lookup Functions) cho phép bạn tìm và lấy dữ liệu từ bảng khác dựa trên giá trị khóa — giống như tra từ điển: bạn có 1 từ (key), bạn tìm nghĩa (value) từ bảng từ điển (lookup table). Hàm logic (Logic Functions) cho phép bạn đặt điều kiện — nếu A thì B, nếu không thì C — để phân loại, tính toán linh hoạt.
Bảng tổng hợp hàm tra cứu & logic quan trọng:
| Nhóm | Hàm | Mục đích | Khi nào dùng |
|---|---|---|---|
| Tra cứu cơ bản | VLOOKUP | Tìm giá trị theo cột dọc | Tra cứu giá sản phẩm, tên khách hàng, mã vùng |
| Tra cứu nâng cao | XLOOKUP | Thay thế VLOOKUP — linh hoạt hơn, hỗ trợ tìm ngược | Excel 365+ — tra cứu 2 chiều, xử lý lỗi tích hợp |
| Tra cứu vị trí | INDEX + MATCH | Tìm giá trị theo hàng + cột, không giới hạn hướng | Khi cần tra cứu từ phải sang trái, hoặc 2 điều kiện |
| Logic đơn | IF | Kiểm tra 1 điều kiện, trả về True/False | Phân loại: doanh thu > 1M → "VIP", ngược lại → "Standard" |
| Logic đa điều kiện | IFS | Kiểm tra nhiều điều kiện tuần tự | Phân hạng: A/B/C/D dựa trên nhiều ngưỡng |
| Logic chọn giá trị | SWITCH | So khớp giá trị → trả kết quả tương ứng | Mapping mã vùng → tên vùng, mã SP → tên nhóm SP |
| Tổng hợp có điều kiện | SUMIFS | Tính tổng theo nhiều điều kiện | Tổng doanh thu theo vùng + tháng + sản phẩm |
| Đếm có điều kiện | COUNTIFS | Đếm theo nhiều điều kiện | Đếm số đơn hàng từ khu vực A trong tháng 1 |
| Trung bình có điều kiện | AVERAGEIFS | Trung bình theo nhiều điều kiện | Giá trị đơn hàng trung bình của nhóm VIP |
Tại sao quan trọng cho Data Analyst?
Trong thực tế, dữ liệu bán hàng không bao giờ nằm trong 1 bảng duy nhất. Bạn có bảng đơn hàng (Order Table), bảng sản phẩm (Product Master), bảng khách hàng (Customer Table), bảng khu vực (Region Table). Hàm tra cứu là "cầu nối" giúp bạn liên kết dữ liệu từ nhiều bảng — tương tự khái niệm JOIN trong SQL mà bạn sẽ học ở các buổi sau.
Hàm logic giúp bạn phân loại, gắn nhãn, tính KPI có điều kiện — những việc xuất hiện trong 100% dự án phân tích thực tế. Ví dụ: phân khách hàng theo tier (VIP/Standard/Basic), gắn nhãn đơn hàng (trên/dưới target), tính bonus cho nhân viên theo bậc.
Theo khảo sát của ExcelJet, VLOOKUP và IF là 2 hàm được tìm kiếm nhiều nhất trên Google liên quan đến Excel — chiếm hơn 35% tổng lượng tìm kiếm hàm Excel toàn cầu.
Áp dụng thực tế
So sánh chi tiết VLOOKUP vs XLOOKUP vs INDEX/MATCH:
| Tiêu chí | VLOOKUP | XLOOKUP | INDEX + MATCH |
|---|---|---|---|
| Cú pháp | =VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) | =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) |
| Hướng tra cứu | Chỉ trái → phải | Cả 2 chiều | Cả 2 chiều |
| Thêm/xóa cột | Bị lỗi (col_index thay đổi) | Không ảnh hưởng | Không ảnh hưởng |
| Xử lý lỗi | Cần thêm IFERROR | Tích hợp param if_not_found | Cần thêm IFERROR |
| Tốc độ | Chậm trên data lớn | Nhanh | Nhanh |
| Phiên bản Excel | Mọi phiên bản | Excel 365 / 2021+ | Mọi phiên bản |
| Khuyến nghị | Legacy — vẫn phổ biến | Best choice nếu dùng Excel 365 | Best choice cho mọi phiên bản |
Hàm logic và tổng hợp có điều kiện — Chi tiết:
| Hàm | Cú pháp | Ví dụ | Output |
|---|---|---|---|
IF | =IF(condition, true_value, false_value) | =IF(E2>=1000000, "VIP", "Standard") | "VIP" nếu doanh thu ≥ 1M |
IFS | =IFS(cond1, val1, cond2, val2, ...) | =IFS(E2>=5000000,"Platinum", E2>=1000000,"Gold", E2>=500000,"Silver", TRUE,"Bronze") | Phân 4 hạng theo doanh thu |
SWITCH | =SWITCH(expression, val1, result1, ...) | =SWITCH(C2, "HN","Miền Bắc", "HCM","Miền Nam", "DN","Miền Trung") | Mapping thành phố → vùng miền |
SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, ...) | =SUMIFS(E:E, C:C,"Hà Nội", D:D,">=2026-01-01") | Tổng doanh thu Hà Nội từ 01/2026 |
COUNTIFS | =COUNTIFS(range1, criteria1, ...) | =COUNTIFS(C:C,"Hà Nội", F:F,"VIP") | Đếm khách VIP ở Hà Nội |
AVERAGEIFS | =AVERAGEIFS(avg_range, range1, criteria1, ...) | =AVERAGEIFS(E:E, C:C,"HCM", B:B,">=2026-01-01") | TB doanh thu HCM từ 01/2026 |
📌 Phần 2: Excel Dashboard Design
Khái niệm
Excel Dashboard là trang tổng hợp trực quan hiển thị các KPI, biểu đồ và bảng dữ liệu quan trọng nhất trên 1 màn hình — giúp người dùng nắm bắt tình hình kinh doanh trong vài giây mà không cần đọc báo cáo dài. Dashboard tốt tuân theo nguyên tắc "5-second rule": người xem hiểu được thông điệp chính trong 5 giây đầu tiên.
Cấu trúc layout chuẩn cho Dashboard:
| Vùng | Vị trí | Nội dung | Ví dụ |
|---|---|---|---|
| Header | Trên cùng | Tiêu đề dashboard, kỳ báo cáo, filters | "Sales Dashboard — Q1/2026" |
| KPI Cards | Ngay dưới header | 3–5 chỉ số quan trọng nhất, so sánh vs target/kỳ trước | Tổng doanh thu, Số đơn hàng, AOV, Growth % |
| Charts | Giữa trang | 2–4 biểu đồ chính: trend, breakdown, comparison | Line chart doanh thu theo tháng, Bar chart theo vùng |
| Detail Tables | Dưới cùng | Bảng chi tiết cho người muốn drill-down | Top 10 sản phẩm, Top 10 khách hàng |
| Slicer/Filter | Bên phải hoặc trên | Bộ lọc tương tác: thời gian, khu vực, sản phẩm | Slicer "Khu vực", Timeline "Tháng" |
Công cụ tạo Dashboard trong Excel:
| Công cụ | Mục đích | Cách dùng |
|---|---|---|
| Slicer | Bộ lọc trực quan, click để filter Pivot Table/Chart | Insert → Slicer → chọn field |
| Timeline | Bộ lọc thời gian trực quan (by day/month/quarter/year) | Insert → Timeline → chọn date field |
| Conditional Formatting | Tô màu tự động theo giá trị — highlight KPI | Data Bars, Color Scales, Icon Sets |
| Sparklines | Mini chart nhỏ gọn trong 1 ô — hiển thị trend | Insert → Sparklines (Line/Column/Win-Loss) |
| Named Ranges | Đặt tên cho vùng dữ liệu — công thức dễ đọc hơn | Formulas → Define Name |
| Camera Tool | "Chụp" vùng dữ liệu từ sheet khác → dán vào Dashboard | Quick Access Toolbar → Camera |
Tại sao quan trọng cho Data Analyst?
Dashboard là output cuối cùng mà stakeholder thấy — và đánh giá năng lực của bạn qua đó. Một phân tích sâu sắc nhưng trình bày bằng bảng số Excel thô thì giá trị giảm 50% trở lên vì stakeholder không đọc. Ngược lại, một Dashboard đẹp, rõ ràng, tương tác được — giúp sếp tự khám phá insight — là cách nhanh nhất để prove your value trong doanh nghiệp.
Theo nghiên cứu của Wharton School of Business, thuyết trình có visual thuyết phục 43% hơn so với chỉ dùng text/số. Dashboard chính là visual cho dữ liệu.
Dashboard Design Patterns phổ biến:
| Pattern | Mô tả | Khi nào dùng |
|---|---|---|
| Strategic Dashboard | KPI tổng quan, trend dài hạn, so sánh target | Cho C-level, ban lãnh đạo — xem hàng tuần/tháng |
| Operational Dashboard | Dữ liệu real-time, chi tiết từng giao dịch | Cho team vận hành — xem hàng ngày |
| Analytical Dashboard | Drill-down sâu, nhiều filter, cross-tab | Cho DA team — phân tích deep-dive |
Áp dụng thực tế
Conditional Formatting — 3 kỹ thuật cho Dashboard:
| Kỹ thuật | Mô tả | Ví dụ áp dụng |
|---|---|---|
| Data Bars | Thanh ngang trong ô thể hiện độ lớn giá trị | Cột doanh thu theo sản phẩm — dễ so sánh visual |
| Color Scales | Gradient màu theo giá trị (xanh → đỏ) | Heatmap doanh thu theo vùng × tháng |
| Icon Sets | Mũi tên ↑↓→, đèn xanh/vàng/đỏ | KPI vs target: ↑ vượt target, → đạt, ↓ dưới target |
Slicer — Biến dashboard thành interactive:
Slicer kết nối với Pivot Table (từ Buổi 3) → khi click Slicer, tất cả Pivot Chart liên kết sẽ tự động cập nhật. Đây chính là cách tạo "pseudo-BI tool" ngay trong Excel mà không cần Power BI.
Một Slicer có thể điều khiển nhiều Pivot Table cùng lúc: Right-click Slicer → Report Connections → tick tất cả Pivot Table cần kết nối. Kết hợp Slicer (lọc theo category) + Timeline (lọc theo thời gian) → Dashboard tương tác hoàn chỉnh.
📌 Phần 3: Sales Analysis Framework
Khái niệm
Sales Analysis (phân tích bán hàng) là quá trình đánh giá hiệu quả kinh doanh thông qua dữ liệu bán hàng — từ đó phát hiện xu hướng, cơ hội và vấn đề. Đây là bài toán phổ biến nhất mà DA gặp trong thực tế, bất kể bạn làm ở ngành nào — retail, FMCG, tech, F&B, hay e-commerce.
4 trụ cột của Sales Analysis:
| # | Trụ cột | Câu hỏi trả lời | Công cụ / Hàm |
|---|---|---|---|
| 1 | Revenue Analysis | Doanh thu bao nhiêu? Từ đâu? Khi nào? | SUMIFS, Pivot Table, Bar/Line Chart |
| 2 | Growth Rate | Tăng trưởng bao nhiêu %? So với kỳ nào? | Công thức MoM/QoQ/YoY, Line Chart |
| 3 | Top/Bottom Analysis (Pareto) | 20% sản phẩm nào tạo 80% doanh thu? | Pivot Table sort, Pareto Chart |
| 4 | Seasonality Detection | Có pattern theo mùa/tháng/tuần không? | Line Chart theo thời gian, heatmap |
Revenue breakdown dimensions — Phân tích doanh thu theo nhiều chiều:
| Chiều phân tích | Câu hỏi | Ví dụ insight |
|---|---|---|
| By Product | Sản phẩm nào bán chạy nhất / kém nhất? | "iPhone chiếm 45% doanh thu, nhưng phụ kiện có margin cao nhất" |
| By Region | Khu vực nào mạnh nhất? | "Miền Nam chiếm 55% doanh thu, miền Trung tăng trưởng nhanh nhất" |
| By Channel | Kênh nào hiệu quả? Online hay offline? | "Online tăng 40% YoY, offline giảm 5%" |
| By Time | Xu hướng theo thời gian? Peak season? | "Q4 luôn cao nhất do Black Friday + Tết, Q2 thấp nhất" |
Tại sao quan trọng cho Data Analyst?
Sales Analysis là bài toán interview phổ biến nhất cho vị trí DA — gần như 100% buổi phỏng vấn sẽ hỏi: "Cho bạn bảng dữ liệu bán hàng, bạn phân tích gì?". Nếu bạn trả lời được theo framework 4 trụ cột ở trên, bạn đã vượt qua 80% ứng viên.
Ngoài phỏng vấn, trong thực tế, Sales Report/Dashboard là deliverable được yêu cầu nhiều nhất từ stakeholder. Theo khảo sát của Databox, 67% doanh nghiệp yêu cầu DA tạo Sales Dashboard là task đầu tiên khi onboard.
Áp dụng thực tế
Growth Rate — Công thức tính tăng trưởng:
| Loại | Công thức | Ví dụ | Ý nghĩa |
|---|---|---|---|
| MoM (Month-over-Month) | T1: 500M → T2: 550M → MoM = +10% | Tăng trưởng ngắn hạn, phát hiện trend mới | |
| QoQ (Quarter-over-Quarter) | Q1: 1.5B → Q2: 1.35B → QoQ = −10% | Xu hướng trung hạn, loại nhiễu tháng | |
| YoY (Year-over-Year) | T1/2026: 550M vs T1/2025: 480M → YoY = +14.6% | Loại bỏ seasonality, so sánh công bằng nhất |
Tại sao YoY tốt hơn MoM? Vì MoM bị ảnh hưởng bởi seasonality — doanh thu T2 luôn giảm so với T1 (vì tháng 2 ngắn + sau Tết). Nếu chỉ nhìn MoM, bạn sẽ hoảng — nhưng YoY so sánh T2/2026 vs T2/2025 mới cho thấy tình hình thực sự.
Pareto 80/20 Rule trong Sales:
Nguyên tắc Pareto: 20% sản phẩm tạo ra 80% doanh thu. Đây là quy luật xuất hiện liên tục trong kinh doanh và giúp DA tập trung phân tích vào nhóm sản phẩm/khách hàng tạo ra giá trị lớn nhất.
| Bước | Hành động | Công cụ |
|---|---|---|
| 1 | Sort doanh thu giảm dần theo sản phẩm | Pivot Table → Sort Largest to Smallest |
| 2 | Tính cumulative % (% tích lũy) | =SUM($E$2:E2)/SUM($E$2:$E$100) |
| 3 | Xác định điểm cắt 80% | Sản phẩm nào có cumulative % ≤ 80% → nhóm "Top" |
| 4 | Tạo Pareto Chart | Combo chart: Bar (doanh thu) + Line (cumulative %) |
📌 Phần 4: Charting Best Practices
Khái niệm
Biểu đồ là ngôn ngữ trực quan của Dashboard — chọn đúng loại biểu đồ giúp insight hiện ra ngay lập tức, chọn sai thì data bị "nói dối" hoặc gây hiểu nhầm. Edward Tufte — cha đẻ của data visualization hiện đại — đã đưa ra nguyên tắc Data-ink ratio: tối đa hóa "mực" dùng cho data, tối thiểu hóa "mực" dùng cho trang trí không cần thiết.
Mục tiêu: Data-ink ratio → 1.0. Loại bỏ mọi thứ không giúp người xem hiểu data tốt hơn.
Chọn đúng loại biểu đồ:
| Mục đích | Loại biểu đồ | Khi nào dùng | Ví dụ |
|---|---|---|---|
| So sánh | Bar Chart (ngang/dọc) | So sánh giá trị giữa các nhóm | Doanh thu theo khu vực, theo sản phẩm |
| Xu hướng | Line Chart | Thể hiện thay đổi theo thời gian | Doanh thu MoM, xu hướng 12 tháng |
| Tỷ lệ | Pie / Donut Chart | Thể hiện phần trong tổng thể (max 5–6 phần) | Tỷ trọng doanh thu theo channel |
| Tương quan | Scatter Plot | Mối quan hệ giữa 2 biến số | Giá bán vs số lượng bán, quảng cáo vs doanh thu |
| Phân phối | Histogram | Phân bố giá trị trong dataset | Phân bố giá trị đơn hàng |
| Pareto | Combo (Bar + Line) | Phân tích 80/20 | Top products + cumulative % |
| So sánh thời gian | Stacked Bar | So sánh composition qua thời gian | Doanh thu theo kênh qua từng quý |
Tại sao quan trọng cho Data Analyst?
Chọn sai biểu đồ là lỗi phổ biến nhất của DA mới vào nghề. Ví dụ: dùng Pie Chart cho 15 sản phẩm (quá nhiều phần, không đọc được), dùng 3D Bar Chart (bóp méo giá trị), hoặc dùng dual-axis chart sai scale (gây hiểu nhầm). Những lỗi này không chỉ làm mất uy tín mà còn có thể dẫn đến quyết định kinh doanh sai.
Tiêu chuẩn IBCS (International Business Communication Standards) — được nhiều tập đoàn lớn áp dụng (SAP, Siemens, McKinsey) — quy định rõ: biểu đồ kinh doanh cần đơn giản, nhất quán, và tập trung vào message.
Áp dụng thực tế
10 lỗi phổ biến khi tạo biểu đồ — và cách sửa:
| # | Lỗi | Vấn đề | Cách sửa |
|---|---|---|---|
| 1 | 3D Chart | Bóp méo giá trị, khó so sánh chính xác | Luôn dùng 2D |
| 2 | Pie chart > 6 phần | Không phân biệt được các lát nhỏ | Gộp nhóm nhỏ thành "Khác", hoặc dùng Bar Chart |
| 3 | Trục Y không bắt đầu từ 0 | Phóng đại sự khác biệt → gây hiểu nhầm | Luôn để trục Y từ 0 (trừ khi có lý do rõ ràng + ghi chú) |
| 4 | Quá nhiều màu sắc | Rối mắt, mất focus | Dùng 1–2 màu chính, highlight data quan trọng |
| 5 | Thiếu title, label | Người xem không biết đang nhìn gì | Luôn có: title mô tả, axis label, data label khi cần |
| 6 | Legend quá xa data | Phải dò qua lại giữa legend và chart | Đặt label trực tiếp trên data series khi có thể |
| 7 | Gridlines quá đậm | Chiếm attention, giảm data-ink ratio | Dùng gridlines nhạt hoặc bỏ hẳn |
| 8 | Sai thứ tự sắp xếp | Bar chart random order → khó so sánh | Sort giảm dần cho Bar Chart (trừ khi có thứ tự tự nhiên) |
| 9 | Dual axis sai scale | 2 trục Y khác đơn vị/scale → gây hiểu nhầm tương quan | Tách thành 2 chart riêng, hoặc normalize về cùng scale |
| 10 | Decoration thừa | Hình nền, icon trang trí, border đậm | Loại bỏ — áp dụng Tufte's Data-ink ratio |
Title convention cho biểu đồ theo IBCS:
| Yếu tố | Convention | Ví dụ tốt | Ví dụ chưa tốt |
|---|---|---|---|
| Title | Mô tả insight, không chỉ mô tả data | "Doanh thu Q1/2026 tăng 15% so với cùng kỳ" | "Biểu đồ doanh thu" |
| Subtitle | Đơn vị, kỳ báo cáo, nguồn data | "Đơn vị: triệu VNĐ · Nguồn: ERP · Kỳ: 01-03/2026" | (để trống) |
| Axis label | Rõ ràng, có đơn vị | "Doanh thu (triệu VNĐ)" | "Revenue" |
| Data label | Hiển thị trên bar/line khi ít data point | Giá trị trực tiếp trên bar | (không có) |
📊 Framework tổng hợp
Dashboard Design Process — 5 bước
┌──────────────────────────────────────────────────────────────────┐
│ EXCEL DASHBOARD DESIGN PROCESS │
├──────────────────────────────────────────────────────────────────┤
│ │
│ ① DEFINE ② PREPARE ③ BUILD │
│ ┌────────────┐ ┌────────────┐ ┌─────────────────┐ │
│ │ Xác định │ │ Data sạch │ │ Tạo Pivot Table │ │
│ │ audience & │───→│ (Buổi 3) │───→│ + Pivot Chart │ │
│ │ KPIs │ │ + Lookup │ │ + Slicer/Timeline│ │
│ └────────────┘ │ formulas │ └────────┬────────┘ │
│ └────────────┘ │ │
│ ▼ │
│ ⑤ ITERATE ④ LAYOUT │
│ ┌────────────┐ ┌─────────────────┐ │
│ │ Thu feedback│ │ KPI Cards (top) │ │
│ │ từ stake- │←───│ Charts (middle) │ │
│ │ holder, │ │ Tables (bottom) │ │
│ │ cải thiện │ │ Filters (right) │ │
│ └────────────┘ └─────────────────┘ │
│ │
│ Nguyên tắc: 5-second rule · Data-ink ratio · IBCS standards │
└──────────────────────────────────────────────────────────────────┘Sales Analysis Framework — 4 trụ cột
┌──────────────────────────────────────────────────────────────┐
│ SALES ANALYSIS FRAMEWORK │
├──────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────┐ ┌──────────────┐ │
│ │ REVENUE │ │ GROWTH │ │
│ │ ANALYSIS │ │ RATE │ │
│ │ │ │ │ │
│ │ By Product │ │ MoM (tháng) │ │
│ │ By Region │ │ QoQ (quý) │ │
│ │ By Channel │ │ YoY (năm) │ │
│ │ By Time │ │ │ │
│ └──────┬───────┘ └──────┬───────┘ │
│ │ │ │
│ └────────┬──────────┘ │
│ │ │
│ ┌────────▼────────┐ │
│ │ DASHBOARD │ ← Output cuối cùng │
│ └────────┬────────┘ │
│ │ │
│ ┌────────┴──────────┐ │
│ │ │ │
│ ┌──────▼───────┐ ┌──────▼───────┐ │
│ │ PARETO │ │ SEASONALITY │ │
│ │ 80/20 │ │ DETECTION │ │
│ │ │ │ │ │
│ │ Top products │ │ Peak months │ │
│ │ Top customers│ │ Weekly trend │ │
│ │ Top regions │ │ Holiday │ │
│ │ │ │ effects │ │
│ └──────────────┘ └──────────────┘ │
│ │
│ Tools: Excel (SUMIFS, Pivot, Chart, Slicer, Cond. Format) │
│ Standards: IBCS, Tufte, Dashboard Design Patterns │
└──────────────────────────────────────────────────────────────┘🛠️ Demo / Code mẫu
Tình huống: Phân tích bán hàng và tạo Dashboard cho chuỗi cửa hàng
Bạn nhận được bộ dữ liệu bán hàng đã được làm sạch từ Buổi 3, gồm 3 bảng: Order Table (Bảng đơn hàng), Product Master (Bảng sản phẩm), Region Table (Bảng khu vực). Nhiệm vụ: liên kết dữ liệu, tính KPI, và xây Dashboard.
Bước 1: VLOOKUP — Tra cứu thông tin sản phẩm từ Product Master
// Tra cứu Tên sản phẩm từ bảng Product Master dựa trên Mã SP
=VLOOKUP(C2, ProductMaster!$A:$D, 2, FALSE)
// C2 = "SP-001" → tìm trong cột A bảng ProductMaster
// col_index = 2 → trả về Tên SP (cột B)
// FALSE = exact match (luôn dùng FALSE cho data analysis!)
// Tra cứu Nhóm sản phẩm (cột 3)
=VLOOKUP(C2, ProductMaster!$A:$D, 3, FALSE)
// Tra cứu Đơn giá (cột 4) — kết hợp IFERROR
=IFERROR(VLOOKUP(C2, ProductMaster!$A:$D, 4, FALSE), 0)
// Nếu không tìm thấy Mã SP → trả về 0 thay vì #N/ABước 2: XLOOKUP — Phiên bản nâng cấp (Excel 365)
// Cú pháp gọn hơn, không cần nhớ col_index
=XLOOKUP(C2, ProductMaster!$A:$A, ProductMaster!$B:$B, "Không tìm thấy")
// lookup_value = C2 (Mã SP)
// lookup_array = cột Mã SP trong ProductMaster
// return_array = cột Tên SP trong ProductMaster
// if_not_found = "Không tìm thấy" — tích hợp xử lý lỗi
// XLOOKUP trả về nhiều cột cùng lúc (spill)
=XLOOKUP(C2, ProductMaster!$A:$A, ProductMaster!$B:$D)
// Trả về Tên SP + Nhóm SP + Đơn giá — 1 công thức, 3 cột outputBước 3: INDEX + MATCH — Tra cứu linh hoạt nhất
// Tra cứu từ phải sang trái (VLOOKUP không làm được)
// Bài toán: biết Tên SP, tìm Mã SP
=INDEX(ProductMaster!$A:$A, MATCH(D2, ProductMaster!$B:$B, 0))
// INDEX trả về giá trị từ cột A (Mã SP)
// MATCH tìm vị trí dòng có Tên SP = D2 trong cột B
// INDEX + MATCH 2 điều kiện: Tìm doanh thu theo Sản phẩm + Khu vực
=INDEX(Revenue!$D:$D, MATCH(1, (Revenue!$A:$A=B2)*(Revenue!$B:$B=C2), 0))
// Nhấn Ctrl+Shift+Enter (array formula) hoặc dùng Excel 365+ (tự động spill)Bước 4: IF lồng nhau & IFS — Phân loại khách hàng
// Phân loại khách hàng theo tổng doanh thu
// IF lồng nhau (nested IF) — tối đa 7 cấp
=IF(F2>=10000000, "Platinum",
IF(F2>=5000000, "Gold",
IF(F2>=1000000, "Silver", "Bronze")))
// IFS — gọn hơn, dễ đọc hơn (Excel 2019+/365)
=IFS(
F2>=10000000, "Platinum",
F2>=5000000, "Gold",
F2>=1000000, "Silver",
TRUE, "Bronze"
)
// TRUE cuối cùng = "else" — tương đương default case
// SWITCH — mapping mã khu vực sang tên vùng
=SWITCH(E2,
"HN", "Miền Bắc",
"HCM", "Miền Nam",
"DN", "Miền Trung",
"CT", "Miền Tây",
"Khác"
)Bước 5: SUMIFS / COUNTIFS / AVERAGEIFS — Tính KPI
// Tổng doanh thu khu vực Hà Nội, tháng 01/2026
=SUMIFS(Revenue!$F:$F,
Revenue!$C:$C, "Hà Nội",
Revenue!$B:$B, ">=2026-01-01",
Revenue!$B:$B, "<=2026-01-31")
// Đếm số đơn hàng VIP trong Q1/2026
=COUNTIFS(Orders!$G:$G, "Platinum",
Orders!$B:$B, ">=2026-01-01",
Orders!$B:$B, "<=2026-03-31")
// Giá trị đơn hàng trung bình theo channel "Online"
=AVERAGEIFS(Orders!$F:$F, Orders!$E:$E, "Online")
// Growth Rate MoM
// Giả sử B2 = doanh thu tháng trước, B3 = doanh thu tháng này
=(B3-B2)/B2
// Format: Percentage, 1 decimal → "12.5%"
// Nếu B2 = 0 → lỗi chia cho 0, cần wrap:
=IFERROR((B3-B2)/B2, 0)
// Growth Rate YoY — cùng kỳ năm trước
// Doanh thu T1/2026 vs T1/2025
=IFERROR((SUMIFS(Revenue!$F:$F, Revenue!$B:$B, ">=2026-01-01", Revenue!$B:$B, "<=2026-01-31")
- SUMIFS(Revenue!$F:$F, Revenue!$B:$B, ">=2025-01-01", Revenue!$B:$B, "<=2025-01-31"))
/ SUMIFS(Revenue!$F:$F, Revenue!$B:$B, ">=2025-01-01", Revenue!$B:$B, "<=2025-01-31"), 0)Bước 6: Tạo Slicer & Timeline cho Dashboard
Bước 6a — Tạo Pivot Table từ dữ liệu đã liên kết:
1. Select vùng dữ liệu → Insert → Pivot Table → New Worksheet
2. Rows: Khu vực, Nhóm SP
3. Columns: Tháng (Group by Month từ cột Ngày)
4. Values: SUM of Doanh thu
Bước 6b — Tạo Pivot Chart:
1. Click vào Pivot Table → Insert → Pivot Chart → Clustered Column
2. Di chuyển chart sang sheet "Dashboard"
Bước 6c — Thêm Slicer:
1. Click Pivot Table → Analyze → Insert Slicer
2. Chọn: "Khu vực", "Nhóm SP", "Hạng KH"
3. Resize Slicer, đặt bên phải Dashboard
4. Right-click Slicer → Report Connections → tick tất cả Pivot Table
Bước 6d — Thêm Timeline:
1. Click Pivot Table → Analyze → Insert Timeline
2. Chọn cột "Ngày mua"
3. Set hiển thị: Months (có thể chuyển Days/Quarters/Years)
4. Đặt Timeline ở trên cùng Dashboard, ngay dưới headerBước 7: Conditional Formatting cho KPI Cards
Bước 7a — Data Bars cho cột doanh thu:
1. Select cột Doanh thu → Home → Conditional Formatting → Data Bars
2. Chọn Gradient Fill (Blue) → OK
Bước 7b — Icon Sets cho Growth Rate:
1. Select cột Growth % → Conditional Formatting → Icon Sets
2. Chọn "3 Arrows" (↑ ↓ →)
3. Manage Rules → Edit Rule:
- ↑ (Green): when value >= 10%
- → (Yellow): when value >= 0% and < 10%
- ↓ (Red): when value < 0%
Bước 7c — Color Scales cho heatmap:
1. Select vùng Pivot Table (Revenue by Region × Month)
2. Conditional Formatting → Color Scales
3. Chọn: Green (high) → Yellow (mid) → Red (low)
// Hiệu ứng: nhìn 1 cái biết khu vực/tháng nào doanh thu cao (xanh) hay thấp (đỏ)
Bước 7d — Sparklines cho trend mini:
1. Chọn 1 ô trống cuối dòng Top Product
2. Insert → Sparklines → Line
3. Data Range: doanh thu 12 tháng của SP đó
4. Repeat cho mỗi SP → mini trend chart trong 1 ô🏪 Ví dụ thực tế
🇻🇳 Việt Nam: Thế Giới Di Động — Sales Dashboard cho 5.000+ cửa hàng
Thế Giới Di Động (MWG) — chuỗi bán lẻ điện tử lớn nhất Việt Nam — vận hành hơn 5.000 cửa hàng trên toàn quốc. Đội ngũ Data Analyst tại MWG xây dựng Sales Dashboard để theo dõi hiệu quả kinh doanh hàng ngày.
Dashboard KPIs của MWG:
| KPI | Mô tả | Công thức Excel tương đương |
|---|---|---|
| Tổng doanh thu | Revenue toàn hệ thống | =SUMIFS(Revenue, Date, ">="&StartDate, Date, "<="&EndDate) |
| Same-Store Sales Growth (SSSG) | Tăng trưởng cùng cửa hàng YoY — loại cửa hàng mới mở | =IFERROR((ThisYear-LastYear)/LastYear, 0) chỉ tính store mở > 12 tháng |
| Average Order Value (AOV) | Giá trị trung bình mỗi đơn | =AVERAGEIFS(OrderValue, Region, "HCM") |
| Revenue per sqm | Doanh thu trên mỗi m² mặt bằng | =SUMIFS(Revenue, StoreID, A2) / VLOOKUP(A2, StoreInfo, 5, FALSE) |
| Conversion Rate | Tỷ lệ khách vào → mua hàng | =COUNTIFS(Status, "Completed") / COUNTIFS(Status, "<>") |
Sales Analysis thực tế tại MWG:
| Phân tích | Insight phát hiện | Hành động |
|---|---|---|
| Revenue by Category | Điện thoại chiếm 52% doanh thu nhưng margin chỉ 8%; Phụ kiện chiếm 12% doanh thu nhưng margin 45% | Đẩy mạnh upsell phụ kiện kèm điện thoại — tăng margin tổng |
| Growth YoY by Region | Miền Trung tăng trưởng 22% YoY, nhanh nhất cả nước | Mở thêm cửa hàng tại Đà Nẵng, Huế, Quy Nhơn |
| Pareto Analysis | Top 15 model (8% SKU) tạo ra 72% doanh thu | Tập trung marketing, đảm bảo tồn kho cho 15 model này |
| Seasonality | Peak: tháng 1 (Tết), tháng 9–11 (Back to School + iPhone mới) | Chuẩn bị tồn kho + nhân sự trước 2 tháng |
Bài học cho DA:
- Dashboard cần refresh data tự động — MWG dùng Power Query + Macro để tự cập nhật mỗi ngày
- KPI không chỉ là số tuyệt đối — cần so sánh (vs target, vs cùng kỳ, vs khu vực khác)
- Pareto giúp focus — không cần phân tích hết 10.000 SKU, chỉ cần focus top 15
🌍 Quốc tế: Unilever — Regional Sales Dashboard cho 190+ quốc gia
Unilever — tập đoàn FMCG hàng đầu thế giới (Omo, Lifebuoy, Knorr, Dove) — quản lý doanh thu hơn €60 tỷ/năm trên 190+ quốc gia. Mỗi khu vực (region) có Sales Dashboard riêng, tổng hợp lên Global Dashboard.
Dashboard Framework của Unilever (đơn giản hóa cho Excel):
| Layer | Nội dung | Audience |
|---|---|---|
| Global Dashboard | Revenue by region, top/bottom markets, overall growth | CEO, CFO |
| Regional Dashboard | Revenue by country, category, channel, growth vs target | Regional VP |
| Country Dashboard | Revenue by product, city, store, promotion effectiveness | Country Manager, DA team |
Phân tích Sales tại Unilever:
| Phân tích | Phương pháp | Tool trong Excel |
|---|---|---|
| Revenue by Category | Breakdown doanh thu theo Home Care, Personal Care, Food & Beverage | Pivot Table + Stacked Bar Chart |
| Channel Mix | Online vs Modern Trade vs Traditional Trade — xu hướng dịch chuyển sang online | SUMIFS by Channel + 100% Stacked Bar |
| Growth Rate | QoQ, YoY comparison — underlying growth (loại tác động tỷ giá) | Công thức Growth Rate + Line Chart |
| Market Share vs Competition | So sánh với P&G, Nestlé tại từng category | External data + Bar Chart comparison |
| Promotion ROI | Doanh thu promotion / chi phí promotion | SUMIFS (promo vs non-promo period) / cost |
Unilever áp dụng IBCS cho biểu đồ:
- Actual: cột đen đặc (solid black)
- Previous Year: cột viền (outline only)
- Plan/Budget: cột gạch chéo (hatched)
- Forecast: cột chấm chấm (dotted)
- Variance: cột xanh (positive) / đỏ (negative)
Bài học cho DA:
- Dashboard cần phân tầng (layered): từ tổng quan → chi tiết, từ global → local
- Áp dụng IBCS giúp biểu đồ nhất quán toàn tổ chức — ai cũng đọc được
- Growth Rate quan trọng hơn absolute revenue — vì nó cho thấy hướng đ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 viết được
VLOOKUPvới exact match (FALSE) để tra cứu dữ liệu từ bảng khác - [ ] Tôi hiểu ưu điểm
XLOOKUPso vớiVLOOKUPvà biết khi nào dùng - [ ] Tôi dùng được
INDEX + MATCHđể tra cứu từ phải sang trái và tra cứu 2 điều kiện - [ ] Tôi viết được
IFlồng nhau vàIFSđể phân loại dữ liệu theo nhiều cấp - [ ] Tôi sử dụng thành thạo
SUMIFS,COUNTIFS,AVERAGEIFSđể tính KPI có điều kiện - [ ] Tôi thiết kế được layout Dashboard theo chuẩn: KPI cards → Charts → Detail tables
- [ ] Tôi tạo được Slicer và Timeline kết nối với nhiều Pivot Table cùng lúc
- [ ] Tôi áp dụng Conditional Formatting: Data Bars, Color Scales, Icon Sets
- [ ] Tôi thêm được Sparklines để hiển thị trend mini trong 1 ô
- [ ] Tôi tính được Growth Rate: MoM, QoQ, YoY bằng công thức Excel
- [ ] Tôi thực hiện được Pareto Analysis: sort → cumulative % → xác định top 20%
- [ ] Tôi chọn đúng loại biểu đồ cho từng mục đích: so sánh, xu hướng, tỷ lệ, tương quan
- [ ] Tôi áp dụng được Data-ink ratio của Tufte: loại bỏ 3D, gridlines đậm, decoration thừa
- [ ] Tôi tuân thủ title convention: title = insight, có đơn vị + kỳ báo cáo
- [ ] Tôi xây dựng được Sales Dashboard end-to-end từ dữ liệu sạch (Buổi 3)
🔑 Từ khóa quan trọng
| Tiếng Việt | English | Giải thích |
|---|---|---|
| Bảng điều khiển | Dashboard | Trang tổng hợp trực quan hiển thị KPI, biểu đồ và bảng dữ liệu trên 1 màn hình |
| Hàm tra cứu | Lookup Function | Hàm tìm và lấy dữ liệu từ bảng khác (VLOOKUP, XLOOKUP, INDEX/MATCH) |
| Tốc độ tăng trưởng | Growth Rate | Phần trăm thay đổi giữa 2 kỳ: MoM (tháng), QoQ (quý), YoY (năm) |
| Quy tắc Pareto 80/20 | Pareto Principle / 80-20 Rule | 20% nguyên nhân tạo ra 80% kết quả — áp dụng xác định top sản phẩm/khách hàng |
| Bộ lọc trực quan | Slicer | Nút bấm lọc kết nối Pivot Table/Chart — tạo dashboard tương tác |
| Bộ lọc thời gian | Timeline | Thanh lọc thời gian trực quan cho Pivot Table — lọc theo ngày/tháng/quý/năm |
| Định dạng có điều kiện | Conditional Formatting | Tô màu, icon, data bar tự động theo giá trị — highlight KPI |
| Biểu đồ thu nhỏ | Sparkline | Mini chart trong 1 ô Excel — hiển thị trend nhanh |
| Tỷ lệ mực dữ liệu | Data-ink Ratio (Tufte) | Nguyên tắc: tối đa mực cho data, tối thiểu mực cho trang trí |
| Phân tích bán hàng | Sales Analysis | Quy trình đánh giá hiệu quả kinh doanh qua dữ liệu bán hàng |
| Giá trị đơn hàng trung bình | Average Order Value (AOV) | Tổng doanh thu / Số đơn hàng — KPI cơ bản trong retail |
| Phát hiện mùa vụ | Seasonality Detection | Phân tích pattern lặp lại theo mùa/tháng/tuần trong dữ liệu |
| Tiêu chuẩn trình bày kinh doanh | IBCS (International Business Communication Standards) | Bộ tiêu chuẩn quốc tế cho biểu đồ và báo cáo kinh doanh |
| Phần trăm tích lũy | Cumulative Percentage | % cộng dồn — dùng trong Pareto Chart để xác định ngưỡng 80% |
🔗 Xem thêm Buổi 4
→ 📝 Blog → 🧠 Case Study → 🏆 Tiêu chuẩn → 🛠 Workshop → 🎮 Mini Game