Google Sheets Nâng Cao Bài 8: Pivot Table và SUMPRODUCT — Phân Tích Dữ Liệu Đa Chiều

Đây là bài 8 trong series Google Sheets Nâng Cao. Bài trước (Bài 7) đã hướng dẫn Charts và Dashboard. Bài này tập trung vào hai công cụ phân tích dữ liệu mạnh nhất: Pivot Table và hàm SUMPRODUCT.

Nếu bạn đang phân tích dữ liệu doanh số với hàng nghìn dòng, câu hỏi "Doanh thu theo sản phẩm X tại tỉnh Y trong tháng Z là bao nhiêu?" có thể khiến bạn mất hàng giờ nếu làm thủ công. Với Pivot Table và SUMPRODUCT, câu trả lời đến trong vài giây.

Pivot Table trong Google Sheets — Khác Gì Excel?

Điểm Mạnh của Pivot Table GS

• Tự động cập nhật: Click "Refresh" hoặc Data > Pivot table sẽ cập nhật ngay khi dữ liệu nguồn thay đổi
• Calculated fields: Tạo cột tính toán tùy chỉnh ngay trong Pivot
• Filter theo nhiều điều kiện: Drag & drop như Excel nhưng mượt mà hơn
• Share và collaborate: Nhiều người cùng xem/tương tác realtime

Điểm Khác Biệt So Với Excel

Cách Tạo Pivot Table Trong Google Sheets

Bước 1: Chuẩn Bị Dữ Liệu

Dữ liệu cần đáp ứng yêu cầu:

• Hàng đầu tiên là header (tên cột)
• Không có hàng/cột trống xen giữa
• Mỗi cột chứa một loại dữ liệu nhất quán
• Không có merged cells

Ví dụ dữ liệu doanh số:
Ngày        | Sản Phẩm   | Danh Mục  | Khu Vực   | Nhân Viên  | Số Lượng | Đơn Giá  | Doanh Thu
2027-01-01  | iPhone 15  | Điện thoại| Hà Nội    | Nguyễn A   | 2        | 25,000,000| 50,000,000
2027-01-01  | Samsung S25| Điện thoại| TP.HCM    | Trần B     | 3        | 22,000,000| 66,000,000
2027-01-02  | MacBook Pro| Laptop    | Hà Nội    | Nguyễn A   | 1        | 45,000,000| 45,000,000
...

Bước 2: Tạo Pivot Table

1. Chọn toàn bộ vùng dữ liệu (có thể chọn cả cột A:H)
2. Vào menu: Insert > Pivot table
3. Chọn nơi đặt: "New sheet" (khuyến nghị) hoặc "Existing sheet"
4. Click "Create"

Bước 3: Cấu Hình Rows, Columns, Values, Filters

Panel "Pivot table editor" (bên phải)

ROWS — Nhóm dữ liệu theo hàng ngang
  Ví dụ: Kéo "Danh Mục" vào Rows
  → Mỗi danh mục sẽ thành một hàng

COLUMNS — Nhóm dữ liệu theo cột dọc
  Ví dụ: Kéo "Khu Vực" vào Columns
  → Mỗi khu vực thành một cột

VALUES — Giá trị tính toán (SUM, COUNT, AVERAGE...)
  Ví dụ: Kéo "Doanh Thu" vào Values → SUM
  → Hiển thị tổng doanh thu

FILTERS — Lọc dữ liệu
  Ví dụ: Kéo "Ngày" vào Filters
  → Có thể lọc theo tháng/quý/năm

Kết quả Pivot Table cơ bản:

              | Hà Nội      | TP.HCM      | Tổng Cộng
Điện thoại    | 250,000,000 | 380,000,000 | 630,000,000
Laptop        | 180,000,000 | 220,000,000 | 400,000,000
Phụ kiện      | 45,000,000  | 67,000,000  | 112,000,000
Tổng Cộng     | 475,000,000 | 667,000,000 | 1,142,000,000

Bước 4: Calculated Fields

Thêm cột tính toán tùy chỉnh trong Pivot Table:

Trong "Values" panel, click "+ Add" > "Calculated field"

Ví dụ 1: Tính Margin (%)
  Tên: Margin %
  Formula: =('Doanh Thu' - 'Chi Phí') / 'Doanh Thu'

Ví dụ 2: Doanh thu trung bình/đơn
  Tên: Avg Revenue per Order
  Formula: ='Doanh Thu' / 'Số Lượng'

Ví dụ 3: Commission bán hàng
  Tên: Hoa Hồng (8%)
  Formula: ='Doanh Thu' * 0.08

Refresh Pivot Table

Cách 1: Thay đổi dữ liệu nguồn → Pivot tự cập nhật ngay

Cách 2: Nếu không tự cập nhật:
  - Click vào ô bất kỳ trong Pivot Table
  - Menu Data > Refresh data

Cách 3: Apps Script tự động refresh mỗi giờ
function refreshAllPivots() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(sheet => {
    try {
      const pivotTables = sheet.getPivotTables();
      pivotTables.forEach(pivot => pivot.refreshData());
    } catch(e) {}
  });
}

SUMPRODUCT — Hàm Đa Năng Nhất Google Sheets

Cú Pháp SUMPRODUCT

=SUMPRODUCT(array1, [array2], [array3], ...)

Cách hoạt động:
1. Nhân từng phần tử tương ứng của các mảng
2. Cộng tất cả kết quả lại

Ví dụ cơ bản:
=SUMPRODUCT({1,2,3}, {4,5,6})
= (1x4) + (2x5) + (3x6)
= 4 + 10 + 18
= 32

So Sánh SUMPRODUCT vs SUMIFS

Cùng một kết quả, khác nhau về tính linh hoạt:

SUMIFS (đơn giản hơn, nhanh hơn):
=SUMIFS(H:H, B:B, "iPhone 15", D:D, "Hà Nội")
→ Tổng Doanh Thu của iPhone 15 tại Hà Nội

SUMPRODUCT (linh hoạt hơn, mạnh hơn):
=SUMPRODUCT((B2:B1000="iPhone 15")*(D2:D1000="Hà Nội")*H2:H1000)
→ Kết quả giống nhau

Khi nào dùng SUMIFS: điều kiện đơn giản, cố định
Khi nào dùng SUMPRODUCT: điều kiện phức tạp, tính toán trong điều kiện,
                           wildcard trong logic, kết hợp TEXT functions

SUMPRODUCT Nhiều Điều Kiện

// 2 điều kiện
=SUMPRODUCT((B2:B1000="iPhone 15")*(D2:D1000="Hà Nội")*H2:H1000)

// 3 điều kiện
=SUMPRODUCT(
  (B2:B1000="iPhone 15")*
  (D2:D1000="Hà Nội")*
  (MONTH(A2:A1000)=1)*
  H2:H1000
)

// Điều kiện với SEARCH (chứa text)
=SUMPRODUCT(
  (ISNUMBER(SEARCH("iPhone",B2:B1000)))*
  H2:H1000
)
// Tổng doanh thu tất cả sản phẩm chứa từ "iPhone"

// Điều kiện với so sánh số
=SUMPRODUCT(
  (H2:H1000 > 10000000)*  // Đơn hàng > 10 triệu
  (F2:F1000 >= 2)*         // Số lượng >= 2
  H2:H1000
)

// Điều kiện với ngày tháng
=SUMPRODUCT(
  (A2:A1000 >= DATE(2027,1,1))*
  (A2:A1000 <= DATE(2027,1,31))*
  H2:H1000
)
// Doanh thu tháng 1/2027

SUMPRODUCT với Arrays — Ứng Dụng Nâng Cao

// Weighted average (trung bình có trọng số)
// Điểm trung bình học sinh có trọng số theo số tín chỉ
=SUMPRODUCT(DiemSo, SoTinChi) / SUM(SoTinChi)

Ví dụ:
=SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10)
// B = Điểm, C = Số tín chỉ

// Đếm unique values (không dùng UNIQUE function)
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))
// Đếm số sản phẩm unique trong cột A

// Rank theo điều kiện
=SUMPRODUCT((H2:H1000 > H2) * (D2:D1000 = D2)) + 1
// Xếp hạng doanh thu của nhân viên trong cùng khu vực

// SUMPRODUCT với IF (thay thế SUMIFS phức tạp)
=SUMPRODUCT(IF(D2:D1000="Hà Nội", H2:H1000, 0))
// Giống SUMIFS nhưng có thể dùng trong calculated field

Ứng Dụng Thực Tế: Phân Tích Doanh Thu Multi-Dimensional

Case Study: Phân Tích Doanh Số Chuỗi Cửa Hàng

Dữ liệu: 10,000 dòng giao dịch, 12 tháng, 5 cửa hàng, 50 sản phẩm

Câu hỏi kinh doanh cần trả lời:
1. Sản phẩm bán chạy nhất theo từng cửa hàng?
2. Doanh thu Q4 tăng trưởng bao nhiêu % so với Q3?
3. Nhân viên nào có tỷ lệ đơn hàng giá trị cao nhất?
4. Danh mục nào có margin cao nhất?

// Câu hỏi 1: Doanh thu top 5 sản phẩm tại Hà Nội
=QUERY(DATA!A:H,
  "SELECT B, SUM(H)
   WHERE D = 'Hà Nội'
   GROUP BY B
   ORDER BY SUM(H) DESC
   LIMIT 5
   LABEL B 'Sản Phẩm', SUM(H) 'Doanh Thu'"
)

// Câu hỏi 2: Tăng trưởng Q4 vs Q3
=LET(
  q4, SUMPRODUCT((MONTH(A2:A10000)>=10)*H2:H10000),
  q3, SUMPRODUCT((MONTH(A2:A10000)>=7)*(MONTH(A2:A10000)<=9)*H2:H10000),
  (q4-q3)/q3
)

// Câu hỏi 3: % đơn hàng > 10 triệu theo nhân viên
=SUMPRODUCT((E2:E10000=E2)*(H2:H10000>10000000)) /
COUNTIF(E:E, E2)

// Câu hỏi 4: Margin trung bình theo danh mục
=SUMPRODUCT((C2:C10000="Điện thoại")*((H2:H10000-G2:G10000*F2:F10000)/H2:H10000)) /
COUNTIF(C2:C10000,"Điện thoại")

Market Basket Analysis Đơn Giản

Tìm sản phẩm thường mua kèm nhau (association rules cơ bản):

// Giả sử mỗi đơn hàng có OrderID
// Tìm: Khi mua iPhone thì bao nhiêu % mua thêm AirPods?

// Bước 1: Đếm đơn có iPhone
=COUNTIF(DON_HANG!SanPham:SanPham, "iPhone*")

// Bước 2: Đếm đơn có cả iPhone VÀ AirPods (cùng OrderID)
=SUMPRODUCT(
  (COUNTIFS(DON_HANG!OrderID:OrderID, DON_HANG!OrderID, 
            DON_HANG!SanPham:SanPham, "AirPods*") > 0) *
  (DON_HANG!SanPham:SanPham = "iPhone 15")
)

// Bước 3: Tỷ lệ mua kèm
= Bước2 / Bước1 * 100 &"% khách mua iPhone cũng mua AirPods"

Kết Hợp Pivot Table + SUMPRODUCT

Pattern: Pivot cho Tổng Quan, SUMPRODUCT cho Chi Tiết

Workflow phân tích 3 tầng:

Tầng 1 — Pivot Table (tổng quan nhanh):
→ Doanh thu theo Danh Mục x Khu Vực
→ Nhận ra: "Laptop ở TP.HCM tháng 3 drop mạnh"

Tầng 2 — SUMPRODUCT (drill down):
=SUMPRODUCT(
  (C2:C10000="Laptop")*
  (D2:D10000="TP.HCM")*
  (MONTH(A2:A10000)=3)*
  H2:H10000
)
→ Xác nhận con số: 45,000,000đ vs trung bình 180,000,000đ

Tầng 3 — FILTER (xem chi tiết giao dịch):
=FILTER(DATA!A:H,
  (DATA!C:C="Laptop")*
  (DATA!D:D="TP.HCM")*
  (MONTH(DATA!A:A)=3))
→ Xem từng đơn hàng cụ thể trong tháng 3

Dynamic Dashboard Kết Hợp

// Cell B1: Dropdown chọn Khu Vực (Data Validation)
// Cell B2: Dropdown chọn Danh Mục
// Cell B3: Dropdown chọn Tháng

// KPI tự động theo selection
=SUMPRODUCT(
  (D2:D10000=B1)*   // Khu vực được chọn
  (C2:C10000=B2)*   // Danh mục được chọn
  (MONTH(A2:A10000)=B3)*  // Tháng được chọn
  H2:H10000
)

// So sánh với cùng kỳ tháng trước
=SUMPRODUCT(
  (D2:D10000=B1)*
  (C2:C10000=B2)*
  (MONTH(A2:A10000)=B3-1)*
  H2:H10000
)

// % thay đổi
=(CURRENT - PREVIOUS) / PREVIOUS

Performance Tips — Tối Ưu Tốc Độ

SUMPRODUCT Chậm? Làm Thế Này

VẤNA ĐỀ: SUMPRODUCT với 100,000+ dòng rất chậm

GIẢI PHÁP 1: Giới hạn range chính xác
Thay vì: =SUMPRODUCT(A:A, B:B)  // Toàn cột = 10 triệu ô
Dùng:    =SUMPRODUCT(A2:A10000, B2:B10000)  // Giới hạn thực tế

GIẢI PHÁP 2: Dùng SUMIFS thay SUMPRODUCT khi có thể
SUMPRODUCT((A:A="X")*B:B) → SUMIFS(B:B, A:A, "X")  // Nhanh hơn 5-10x

GIẢI PHÁP 3: Tính toán trung gian trên sheet riêng
Thay vì SUMPRODUCT 3 điều kiện phức tạp,
dùng cột helper: =IF(A2="X",IF(B2="Y",C2,0),0)
rồi SUM cột helper

GIẢI PHÁP 4: LET function để tái sử dụng
=LET(
  filteredData, FILTER(H2:H10000, (D2:D10000="Hà Nội")*(C2:C10000="Laptop")),
  SUM(filteredData)
)
// Tính FILTER 1 lần thay vì nhiều lần trong SUMPRODUCT

Pivot Table Performance

// Tối ưu Pivot Table:
1. Đặt Pivot Table trên sheet riêng (không cùng sheet dữ liệu)
2. Dùng Named Range cho data source (dễ quản lý)
3. Không dùng toàn cột (A:H) — dùng A1:H10000
4. Tắt "Show totals" nếu không cần
5. Nhóm ngày theo Tháng/Quý thay vì giữ nguyên từng ngày

Bài Tập Thực Hành

Bài Tập 1: Pivot Table Cơ Bản

Dataset: Download file mẫu doanh số 2027 (1,000 dòng)
Yêu cầu:
1. Tạo Pivot Table: Doanh thu theo Khu Vực (Rows) x Quý (Columns)
2. Thêm Calculated Field: "Tỷ lệ hoàn thành KPI" = Doanh Thu / 500000000
3. Filter chỉ hiển thị các khu vực có doanh thu > 100 triệu

Kết quả mong đợi: Bảng 5 khu vực x 4 quý với % KPI

Bài Tập 2: SUMPRODUCT Nâng Cao

Yêu cầu:
1. Tính doanh thu trung bình có trọng số (weighted avg) theo số lượng
2. Đếm số sản phẩm unique có doanh thu > 50 triệu trong Q1
3. Tính rank của từng nhân viên trong khu vực của họ

Gợi ý:
1. =SUMPRODUCT(DonGia, SoLuong) / SUM(SoLuong)
2. =SUMPRODUCT((COUNTIFS(SanPham,SanPham,Quy,"Q1")>0)*(DoanhThu>50000000)*
              (1/COUNTIFS(SanPham,SanPham,Quy,"Q1")))
3. =SUMPRODUCT((KhuVuc=KhuVucCuaBanNay)*(DoanhThu>DoanhThuCuaBanNay))+1

Bài Tập 3: Dashboard Kết Hợp

Yêu cầu: Xây dựng dashboard có:
- 1 Pivot Table tổng quan
- 5 SUMPRODUCT KPI cards
- 3 dropdown filter (Khu Vực, Tháng, Danh Mục) ảnh hưởng đồng thời đến cả KPI và Pivot

Thách thức: Làm sao để Pivot Table cũng phản ứng với dropdown filter?
Giải pháp: Tạo QUERY function thay thế Pivot (QUERY linh hoạt hơn với dynamic filter)

Kiến Thức Cần Có Cho Bài Tiếp Theo

Bài 9 sẽ tập trung vào Bảo Mật, Phân Quyền và Chia Sẻ Chuyên Nghiệp. Bạn nên nắm vững:

• Pivot Table cơ bản (bài này)
• SUMPRODUCT với nhiều điều kiện
• Cách tạo dynamic dashboard

Tóm Tắt Bài 8

Xem bài tiếp theo: Bài 9: Bảo Mật, Phân Quyền và Chia Sẻ Chuyên Nghiệp

Xem bài trước: Bài 7: Charts và Dashboard (link sẽ cập nhật)