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.

Trả lời nhanh: Pivot Table trong Google Sheets tổng hợp dữ liệu nhiều chiều bằng kéo thả (Insert > Pivot table) và cập nhật theo dữ liệu nguồn; SUMPRODUCT tính tổng theo nhiều điều kiện ngay trong công thức, hợp để dựng bảng báo cáo cố định. Dùng Pivot để khám phá dữ liệu nhanh, dùng SUMPRODUCT hoặc SUMIFS cho báo cáo cần định dạng riêng.

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

Tính Năng	Google Sheets	Excel
Tạo Pivot	Insert > Pivot table	Insert > PivotTable
Refresh dữ liệu	Tự động realtime	Phải click Refresh
Calculated Field	Có (trong Values)	Có (phức tạp hơn)
Slicers	Không có sẵn (dùng Filter)	Có Slicers đẹp
Power Pivot	Không	Có (Pro)
Online/Offline	Online	Cả hai
Giá	Miễn phí	Có phí

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

Chủ Đề	Kiến Thức Cốt Lõi	Ứng Dụng
Pivot Table	Rows/Columns/Values/Filters, Calculated Fields, Refresh	Báo cáo tổng quan đa chiều
SUMPRODUCT cơ bản	Array multiplication + summation	Thay thế SUMIFS phức tạp
SUMPRODUCT nâng cao	Nhiều điều kiện, wildcard, date conditions	Phân tích KPI phức tạp
Market Basket	COUNTIFS với OrderID	Gợi ý sản phẩm mua kèm
Performance	Giới hạn range, SUMIFS vs SUMPRODUCT	Tăng tốc file lớn

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)