Google Sheets Nâng Cao: Công Thức Và Tính Năng Chuyên Sâu Giúp Bạn Làm Chủ Dữ Liệu 2026
Tuân Hoang
Google Sheets không chỉ là bảng tính đơn giản — đây là nền tảng phân tích dữ liệu mạnh mẽ nếu bạn nắm được các kỹ thuật nâng cao.
Bài viết này hướng dẫn chi tiết QUERY function, ARRAYFORMULA, Apps Script automation, Conditional Formatting nâng cao và Pivot Table — giúp bạn làm chủ Google Sheets ở cấp độ chuyên sâu.
Mục lục:
1. Hàm QUERY — SQL Ngay Trong Google Sheets
Hàm QUERY là một trong những tính năng nâng cao mạnh nhất của Google Sheets, cho phép bạn truy vấn dữ liệu bằng cú pháp gần giống SQL mà không cần biết lập trình database.
Cú pháp cơ bản
=QUERY(data, query, [headers])
-- Ví dụ: Lọc doanh thu Hà Nội, sắp xếp giảm dần
=QUERY(A1:D500,"SELECT A,B,D WHERE C='Hà Nội' ORDER BY D DESC",1)
Các mệnh đề QUERY quan trọng
SELECT + WHERE
=QUERY(data,"SELECT A,C WHERE B>1000000")
Chọn cột A, C với điều kiện cột B lớn hơn 1 triệu.
GROUP BY + SUM
=QUERY(data,"SELECT A,SUM(D) GROUP BY A")
Tổng hợp doanh thu theo từng nhóm/danh mục.
LIMIT + OFFSET
=QUERY(data,"SELECT * ORDER BY D DESC LIMIT 10")
Top 10 bản ghi có giá trị lớn nhất.
CONTAINS + LIKE
=QUERY(data,"SELECT * WHERE A CONTAINS 'Nguyen'")
Lọc dữ liệu theo chuỗi ký tự chứa trong ô.
Mẹo thực tế:
Khi cần lọc theo giá trị từ ô khác, dùng chuỗi nối: "SELECT * WHERE B='"&E1&"'". Cách này giúp tạo dashboard tự động cập nhật khi thay đổi giá trị lọc tại ô E1.
2. ARRAYFORMULA — Xử Lý Mảng Dữ Liệu Siêu Tốc
ARRAYFORMULA là công cụ nâng cao giúp áp dụng một công thức cho toàn bộ cột dữ liệu chỉ bằng một ô duy nhất — không cần kéo công thức, tiết kiệm thời gian và giảm lỗi khi thêm dữ liệu mới.
Ứng dụng phổ biến nhất
| Mục đích | Công thức ARRAYFORMULA |
|---|---|
| Tính thành tiền tự động | =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)) |
| Đánh số thứ tự tự động | =ARRAYFORMULA(IF(B2:B="","",ROW(B2:B)-1)) |
| Nối họ tên đầy đủ | =ARRAYFORMULA(IF(A2:A="","",A2:A&" "&B2:B)) |
| Xếp loại tự động | =ARRAYFORMULA(IFS(D2:D>=9,"Xuat sac",D2:D>=7,"Kha",D2:D>=5,"TB",D2:D<5,"Yeu")) |
| Chuyển đổi text sang số | =ARRAYFORMULA(VALUE(TRIM(A2:A))) |
Kết hợp ARRAYFORMULA với VLOOKUP nâng cao hiệu suất tra cứu dữ liệu lớn: =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,Sheet2!$A:$C,3,0),"")) — tra cứu toàn cột chỉ bằng một công thức.
Để hiểu rõ hơn về các công thức cơ bản, tham khảo bài viết 30 công thức Google Sheets quan trọng nhất cần biết trước khi đi sâu vào nâng cao.
3. Conditional Formatting Nâng Cao — Hơn Cả Tô Màu Đơn Giản
Nhiều người chỉ dùng Conditional Formatting để tô màu ô — nhưng với custom formula, bạn có thể làm được nhiều hơn thế rất nhiều.
Kỹ thuật nâng cao
Tô màu toàn hàng theo điều kiện
Áp dụng cho vùng A2:Z1000, dùng công thức:
=$C2="Hoàn thành"
Dấu $ trước C cố định cột, giúp toàn bộ hàng được tô màu khi cột C bằng "Hoàn thành".
Highlight deadline sắp đến
=AND($D2<>"", $D2-TODAY()<=3, $D2>=TODAY())
Tô đỏ các hàng có deadline còn 3 ngày hoặc ít hơn — cực kỳ hữu ích cho quản lý project.
Heatmap doanh thu tự động
Dùng Color Scale thay vì single color để tạo heatmap từ thấp (đỏ) đến cao (xanh) — cực kỳ hiệu quả cho bảng dữ liệu tổng hợp theo tháng/quý.
Format → Conditional formatting → Color scale → chọn min/max color.
Đánh dấu giá trị trùng lặp
=COUNTIF($A$2:$A$1000,A2)>1
Tự động tô màu các ô có giá trị xuất hiện nhiều hơn một lần trong cột.
4. Pivot Table — Phân Tích Dữ Liệu Đa Chiều
Pivot Table trong Google Sheets (Insert → Pivot table) cho phép tổng hợp và phân tích dữ liệu lớn theo nhiều chiều chỉ trong vài click.
Quy trình tạo Pivot Table chuẩn
- Chuẩn bị dữ liệu: Mỗi cột có tiêu đề rõ ràng, không có hàng trống, dữ liệu nhất quán (không trộn số và chữ trong cùng cột).
- Insert → Pivot table: Chọn vùng dữ liệu, tạo ở sheet mới để dễ quản lý.
- Cấu hình Rows/Columns/Values: Kéo thả trường vào đúng vị trí — Rows là chiều phân tích, Values là chỉ số cần tổng hợp.
- Thêm Calculated Field: Tạo chỉ số tính toán trực tiếp trong Pivot — ví dụ: Biên lợi nhuận = Lợi nhuận / Doanh thu × 100.
- Thêm bộ lọc: Add field vào Filters để tạo bộ lọc tương tác mà không thay đổi cấu trúc Pivot.
Pivot Table vs QUERY — khi nào dùng gì?
Dùng Pivot Table khi:
- Cần khám phá dữ liệu nhanh
- Không biết trước cần phân tích chiều nào
- Muốn tương tác drag-and-drop trực quan
Dùng QUERY khi:
- Cần kết quả nhúng vào dashboard
- Điều kiện lọc phức tạp đa tầng
- Kết hợp dữ liệu từ nhiều sheet
5. Google Apps Script — Tự Động Hóa Không Giới Hạn
Google Apps Script (GAS) là JavaScript chạy trên nền Google Cloud, tích hợp trực tiếp vào Google Sheets. Với GAS, bạn có thể tự động hóa mọi tác vụ lặp đi lặp lại.
Các use case thực tế phổ biến nhất
Email tự động
Gửi email báo cáo hàng ngày, thông báo khi dữ liệu vượt ngưỡng, reminder khi deadline đến. Dùng MailApp.sendEmail().
Sync Google Forms
Tự động xử lý và phân loại dữ liệu từ Form submission — gán vào đúng sheet, gửi email xác nhận cho người điền.
API Integration
Kéo dữ liệu từ API bên ngoài (thời tiết, tỷ giá, CRM) vào Sheets. Dùng UrlFetchApp.fetch().
Script mẫu: Tự động gửi báo cáo doanh thu
// Tools → Apps Script → Paste script này
function guiBaoCaoDoanhThu() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
const doanhThu = sheet.getRange('B2').getValue();
const ngay = Utilities.formatDate(new Date(), 'Asia/Ho_Chi_Minh', 'dd/MM/yyyy');
MailApp.sendEmail({
to: 'boss@company.com',
subject: 'Bao cao doanh thu ' + ngay,
body: 'Doanh thu hom nay: ' + doanhThu.toLocaleString('vi-VN') + ' VND'
});
}
Để cài trigger tự động: Apps Script → Triggers (biểu tượng đồng hồ) → Add Trigger → chọn function, loại trigger (time-driven), và thời gian chạy.
Xem thêm cách ứng dụng Apps Script trong thực tế tại bài viết xây dựng Dashboard doanh nghiệp với Google Sheets.
6. Kết Hợp Công Thức Nâng Cao
Sức mạnh thực sự của Google Sheets nâng cao nằm ở việc kết hợp nhiều hàm để giải quyết bài toán phức tạp.
Công thức kết hợp thực chiến
XLOOKUP — Thế hệ mới thay thế VLOOKUP
=XLOOKUP(F2, A2:A1000, C2:C1000, "Không tìm thấy", 0)
Tra cứu linh hoạt hơn VLOOKUP — có thể tra từ phải sang trái, trả về giá trị mặc định thay vì lỗi #N/A, hỗ trợ wildcard.
IMPORTRANGE kết hợp QUERY
=QUERY(IMPORTRANGE("URL_sheet_khac","Sheet1!A:D"),"SELECT * WHERE Col3>0")
Kéo dữ liệu từ file Sheets khác và lọc trực tiếp — cực kỳ hữu ích cho dashboard tổng hợp từ nhiều nguồn.
FILTER + SORT kết hợp
=SORT(FILTER(A2:D100,(C2:C100="HCM")*(D2:D100>5000000)),4,FALSE)
Lọc và sắp xếp dữ liệu trong một công thức — thay thế hoàn toàn cho bộ lọc thủ công, kết quả tự động cập nhật.
REGEXEXTRACT cho xử lý văn bản
=REGEXEXTRACT(A2,"[0-9]+")
Trích xuất số hoặc pattern từ chuỗi văn bản phức tạp — hữu ích khi làm sạch dữ liệu import từ hệ thống khác.
7. Ứng Dụng Thực Tế Cho Doanh Nghiệp
Các kỹ thuật Google Sheets nâng cao có thể xây dựng những hệ thống quản lý chuyên nghiệp mà không cần phần mềm đắt tiền:
Hệ thống CRM đơn giản
- QUERY tổng hợp khách hàng theo giai đoạn
- ARRAYFORMULA tính ngày follow-up tự động
- Apps Script gửi reminder email
- Conditional Formatting highlight hot leads
Dashboard KPI thời gian thực
- IMPORTRANGE kéo dữ liệu từ nhiều sheet
- Pivot Table tổng hợp theo tháng/quý
- SPARKLINE tạo mini chart trong ô
- Color Scale heatmap trực quan
Quản lý kho hàng tự động
- SUMIF tổng hợp nhập/xuất kho
- Conditional Formatting cảnh báo hàng sắp hết
- Apps Script alert khi tồn kho dưới ngưỡng
- QUERY báo cáo hàng chậm luân chuyển
Báo cáo tài chính tự động
- QUERY tổng hợp doanh thu theo danh mục
- ARRAYFORMULA tính margin tự động
- Apps Script export PDF gửi email hàng tháng
- Pivot Table phân tích chi phí đa chiều
Xây Dựng Hệ Thống Quản Lý Với Google Sheets
SheetStore cung cấp template và hỗ trợ setup dashboard Google Sheets chuyên nghiệp cho doanh nghiệp của bạn
8. Câu Hỏi Thường Gặp
Hàm QUERY trong Google Sheets dùng để làm gì?
Hàm QUERY cho phép truy vấn dữ liệu bằng cú pháp giống SQL — lọc, sắp xếp, nhóm và tính toán từ một vùng dữ liệu. Ví dụ: =QUERY(A1:D100,"SELECT A, SUM(D) WHERE B='Hà Nội' GROUP BY A") tổng hợp doanh thu theo thành phố.
ARRAYFORMULA trong Google Sheets hoạt động như thế nào?
ARRAYFORMULA áp dụng một công thức cho toàn bộ cột chỉ từ một ô, không cần kéo xuống. Ví dụ: =ARRAYFORMULA(A2:A*B2:B) tính tích hai cột cho tất cả hàng có dữ liệu. Khi thêm hàng mới, kết quả tự động cập nhật.
Google Apps Script có khó học không?
Apps Script dựa trên JavaScript nên ai biết JS học rất nhanh. Người chưa biết lập trình có thể tự viết macro đơn giản sau 2-4 tuần. Tài liệu tại developers.google.com/apps-script rất đầy đủ với nhiều ví dụ thực tế.
Conditional Formatting nâng cao có thể làm gì?
Với custom formula, bạn có thể tô toàn hàng theo điều kiện, tạo heatmap, đánh dấu trùng lặp, highlight deadline sắp đến. Ví dụ: =$C2="Hoàn thành" áp dụng cho toàn bảng sẽ tô màu cả hàng khi cột C bằng "Hoàn thành".
Pivot Table trong Google Sheets khác Excel như thế nào?
Pivot Table Google Sheets cập nhật real-time và hỗ trợ cộng tác đồng thời. Tuy nhiên thiếu một số tính năng Excel nâng cao như Calculated Items. Trong nhiều trường hợp, hàm QUERY linh hoạt hơn Pivot Table truyền thống.
Bài viết liên quan:
📚 Bài Viết Liên Quan
Chia sẻ bài viết:
Tuân Hoang
Đội ngũ SheetStore
Bạn thấy bài viết hữu ích?
Đăng ký nhận thông báo khi có bài viết mới.