Google Sheets Nâng Cao: 15 Công Thức và Tính Năng Mạnh Nhất 2026
Tuân Hoang
Google Sheets không chỉ là bảng tính đơn giản — đây là công cụ phân tích dữ liệu mạnh mẽ nếu bạn biết dùng đúng cách.
Bài viết này trình bày 15 công thức và tính năng Google Sheets nâng cao giúp bạn tự động hóa báo cáo, phân tích dữ liệu như chuyên gia và tiết kiệm hàng chục giờ mỗi tuần.
Mục lục:
- 1. ARRAYFORMULA — Công thức một lần cho toàn bộ cột
- 2. Hàm QUERY — SQL ngay trong Google Sheets
- 3. IMPORTRANGE — Kết nối dữ liệu giữa nhiều file
- 4. FILTER & SORT động — Thay thế VLOOKUP
- 5. Conditional Formatting nâng cao với công thức tùy chỉnh
- 6. Pivot Table nâng cao — Phân tích đa chiều
- 7. Google Apps Script — Tự động hóa không giới hạn
- 8. Data Validation thông minh
- 9. Named Ranges & Named Functions
- 10. Tích hợp với Google Workspace & API ngoài
- 11. FAQ
1. ARRAYFORMULA — Công Thức Một Lần Cho Toàn Bộ Cột
Đây là một trong những hàm nâng cao được dùng nhiều nhất. ARRAYFORMULA cho phép một công thức tính toán đồng thời cho hàng nghìn ô thay vì phải copy-paste từng dòng.
// Không dùng ARRAYFORMULA — phải copy xuống từng hàng:
=A2*B2
// Dùng ARRAYFORMULA — một công thức cho toàn bộ cột:
=ARRAYFORMULA(A2:A1000*B2:B1000)
// Kết hợp với IF để xử lý ô trống:
=ARRAYFORMULA(IF(A2:A="";"";A2:A*B2:B))
Ứng dụng thực tế: Tính tổng doanh thu = số lượng × đơn giá cho toàn bộ bảng đơn hàng chỉ với một công thức đặt ở ô C2. Khi thêm dữ liệu mới vào cột A và B, cột C tự động cập nhật không cần thao tác gì thêm.
Mẹo quan trọng: ARRAYFORMULA hoạt động tốt nhất với các hàm số học và logic cơ bản. Với hàm TEXT như VLOOKUP hay IF lồng nhau phức tạp, hãy kết hợp với hàm MAP hoặc LAMBDA (Google Sheets đã hỗ trợ từ 2022).
Muốn áp dụng ngay vào công việc? Xem hướng dẫn quản lý bán hàng bằng Google Sheets với các template thực tế sử dụng ARRAYFORMULA.
2. Hàm QUERY — SQL Ngay Trong Google Sheets
Hàm QUERY là vũ khí bí mật của người dùng Google Sheets chuyên nghiệp. Nó sử dụng ngôn ngữ truy vấn giống SQL để lọc, sắp xếp, nhóm và tổng hợp dữ liệu mà không cần viết công thức phức tạp.
// Cú pháp cơ bản:
=QUERY(data_range; "SELECT ... WHERE ... GROUP BY ... ORDER BY ...")
// Báo cáo doanh thu theo nhân viên tháng 5:
=QUERY(A:E;"SELECT B, SUM(D) WHERE MONTH(A)+1=5 GROUP BY B ORDER BY SUM(D) DESC LABEL SUM(D) 'Doanh Thu'")
// Lọc và kết hợp điều kiện:
=QUERY(Sheet1!A:F;"SELECT A,C,D WHERE B='Hà Nội' AND D>1000000")
SELECT
Chọn cột cần lấy. Dùng * để lấy tất cả. Hỗ trợ SUM, COUNT, AVG, MAX, MIN trực tiếp.
WHERE
Lọc dữ liệu. Hỗ trợ AND, OR, NOT, LIKE, MATCHES (regex), IS NULL, BETWEEN.
GROUP BY + PIVOT
Tổng hợp theo nhóm. PIVOT tạo bảng chéo động — mạnh hơn pivot table thủ công.
3. IMPORTRANGE — Kết Nối Dữ Liệu Giữa Nhiều File
IMPORTRANGE giải quyết bài toán phổ biến: nhiều phòng ban có file riêng, muốn tổng hợp dữ liệu về một file báo cáo trung tâm mà không cần copy-paste thủ công.
// Cú pháp:
=IMPORTRANGE("spreadsheet_url_hoac_id"; "ten_sheet!A2:D100")
// Kết hợp với QUERY để lọc dữ liệu từ file ngoài:
=QUERY(IMPORTRANGE("1BxiM..."; "Doanh Thu!A:E"); "SELECT Col1,Col3,SUM(Col5) GROUP BY Col1,Col3")
Lưu ý khi dùng IMPORTRANGE:
- Lần đầu dùng phải click "Allow Access" để cấp quyền kết nối giữa hai file
- Tối đa 50 hàm IMPORTRANGE trên một spreadsheet
- Dữ liệu cập nhật tự động nhưng có thể trễ 30-60 phút
- File nguồn phải được chia sẻ quyền ít nhất là "Viewer" với người dùng hàm
4. FILTER & SORT Động — Thay Thế VLOOKUP Thông Minh Hơn
VLOOKUP chỉ tìm được một kết quả và không xử lý được nhiều điều kiện phức tạp. Bộ đôi FILTER + SORT mạnh hơn nhiều:
// FILTER với nhiều điều kiện:
=FILTER(A2:D100; B2:B100="Hà Nội"; C2:C100>5000000)
// Sắp xếp kết quả lọc theo cột D giảm dần:
=SORT(FILTER(A2:D100; B2:B100="Hà Nội"); 4; FALSE)
// XLOOKUP — thay thế VLOOKUP hiện đại (2023+):
=XLOOKUP(F2; A2:A100; C2:C100; "Không tìm thấy"; 0; 1)
Kết hợp FILTER với UNIQUE để tạo danh sách không trùng lặp động: =UNIQUE(FILTER(B2:B100; A2:A100=G1)) — liệt kê tất cả sản phẩm của một chi nhánh cụ thể, tự cập nhật khi dữ liệu thay đổi.
5. Conditional Formatting Nâng Cao Với Công Thức Tùy Chỉnh
Hầu hết người dùng chỉ dùng Conditional Formatting với điều kiện đơn giản như "lớn hơn 100" hay "chứa text X". Nhưng tính năng thực sự mạnh là Custom Formula — cho phép bạn dùng bất kỳ công thức nào làm điều kiện tô màu.
Highlight hàng trùng lặp:
=COUNTIF($A$2:$A2;$A2)>1
Áp dụng cho cả hàng, tô màu mọi dòng có giá trị cột A xuất hiện lần 2 trở lên.
Highlight cả hàng theo điều kiện:
=$D2<0
Chọn range A2:Z100, áp công thức này — tô đỏ toàn bộ hàng khi cột D âm.
Heatmap theo giá trị tương đối:
=$B2>AVERAGE($B$2:$B$100)
Tô xanh các hàng vượt trung bình, tô đỏ các hàng dưới trung bình.
Deadline sắp đến hạn:
=AND($C2>=TODAY();$C2<=TODAY()+7)
Tô vàng các task có deadline trong 7 ngày tới.
6. Pivot Table Nâng Cao — Phân Tích Đa Chiều
Pivot Table trong Google Sheets không chỉ là bảng tổng hợp cơ bản. Với các tính năng nâng cao, bạn có thể xây dựng dashboard phân tích đa chiều:
- Calculated Fields: Thêm cột tính toán ngay trong pivot — ví dụ tỷ lệ chuyển đổi = đơn thành công / tổng đơn
- Show Values As: Hiển thị % của tổng, % thay đổi so với hàng trước, running total
- Multiple Value Fields: Kéo nhiều trường vào Values cùng lúc — so sánh doanh thu và lợi nhuận song song
- Slicer: Bộ lọc trực quan dạng nút bấm — click chọn chi nhánh, tháng, sản phẩm để lọc pivot và chart đồng thời
- Kết hợp với Chart: Chart gắn với pivot tự cập nhật khi thay đổi bộ lọc — tạo dashboard tương tác mà không cần code
Xem thêm cách xây dựng báo cáo tự động với Google Sheets và Pivot Table cho doanh nghiệp.
7. Google Apps Script — Tự Động Hóa Không Giới Hạn
Apps Script là ngôn ngữ JavaScript chạy trên cloud của Google, cho phép tự động hóa mọi tác vụ lặp đi lặp lại trong Google Sheets và toàn bộ Google Workspace.
7.1 Trigger Theo Lịch (Time-Driven Trigger)
// Tự động gửi báo cáo email mỗi thứ Hai 8h sáng:
function guiBaoCaoTuan() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getRange('A1:D50').getValues();
const body = data.map(row => row.join(' | ')).join('\n');
GmailApp.sendEmail('boss@company.com', 'Báo cáo tuần', body);
}
7.2 Custom Menu và Sidebar
Thêm menu tùy chỉnh vào thanh công cụ Google Sheets, cho phép nhân viên không biết code cũng có thể chạy các quy trình phức tạp chỉ với một click:
function onOpen() {
SpreadsheetApp.getUi().createMenu('📊 Báo Cáo')
.addItem('Xuất báo cáo tháng', 'xuatBaoCaoThang')
.addItem('Gửi email cho team', 'guiEmailTeam')
.addSeparator()
.addItem('Xóa dữ liệu cũ', 'xoaDuLieuCu')
.addToUi();
}
7.3 Kết Nối API Bên Ngoài
Apps Script có thể gọi bất kỳ REST API nào, mở ra khả năng tích hợp không giới hạn:
- Lấy tỷ giá ngoại tệ từ API ngân hàng, cập nhật tự động mỗi ngày
- Đồng bộ đơn hàng từ Shopify/WooCommerce vào Google Sheets
- Gửi thông báo Telegram/Zalo khi doanh thu vượt target
- Tạo Google Docs hợp đồng/báo giá tự động từ dữ liệu Sheets
- Đọc/ghi dữ liệu vào Google Calendar, Google Forms
Muốn Ứng Dụng Google Sheets Cho Doanh Nghiệp?
SheetStore giúp bạn xây hệ thống quản lý kinh doanh hoàn chỉnh trên nền Google Sheets — không cần kỹ thuật viên
Tư Vấn Miễn Phí8. Data Validation Thông Minh
Data Validation không chỉ là dropdown đơn giản. Kết hợp với công thức nâng cao, bạn có thể tạo form nhập liệu cực kỳ thông minh:
Dropdown động theo điều kiện:
Dùng INDIRECT để tạo dropdown cấp 2 phụ thuộc vào lựa chọn cấp 1. Chọn "Hà Nội" → dropdown kho tự hiện danh sách kho Hà Nội.
Custom formula validation:
Validate email: =ISNUMBER(MATCH("*@*.*";A1;0)). Chặn ngày trong quá khứ: =A1>=TODAY().
9. Named Ranges & Named Functions
Named Ranges cho phép đặt tên có nghĩa cho một vùng dữ liệu — thay vì viết VLOOKUP(A2;Sheet3!$B$2:$D$500;3;0), bạn viết VLOOKUP(A2;DANH_MUC_SAN_PHAM;3;0) — dễ đọc và bảo trì hơn nhiều.
Named Functions (tính năng mới từ 2022) cho phép tạo hàm tùy chỉnh không cần Apps Script. Định nghĩa một lần, dùng lại mọi nơi trong file:
// Tạo Named Function "TINH_HOA_HONG" với tham số doanh_thu:
// Formula definition: =IF(doanh_thu>50000000; doanh_thu*0.05; doanh_thu*0.03)
// Dùng trong sheet như hàm thông thường:
=TINH_HOA_HONG(C2)
10. Tích Hợp Google Workspace & API Ngoài
Google Sheets là trung tâm kết nối cực mạnh trong hệ sinh thái Google Workspace:
- Google Forms → Sheets: Câu trả lời form tự động đổ vào sheet, kết hợp ARRAYFORMULA để xử lý và báo cáo ngay lập tức
- Looker Studio (Data Studio): Kết nối Sheets với Looker Studio để tạo dashboard trực quan chia sẻ cho toàn công ty
- BigQuery: Khi dữ liệu vượt hàng triệu dòng, Connected Sheets cho phép query BigQuery trực tiếp từ giao diện Sheets
- Zapier/Make: Tích hợp Google Sheets với hàng nghìn app khác — CRM, Slack, email marketing, không cần code
- Google Sheets API: Đọc/ghi dữ liệu từ ứng dụng web, mobile app, hệ thống ERP thông qua REST API chính thức
Tham khảo thêm hướng dẫn kết nối Google Sheets với API và tự động hóa workflow cho doanh nghiệp vừa và nhỏ.
Tổng Kết: Lộ Trình Học Google Sheets Nâng Cao
ARRAYFORMULA, FILTER, SORT, UNIQUE — thay thế copy-paste thủ công
QUERY function, IMPORTRANGE, Pivot Table nâng cao — phân tích dữ liệu chuyên nghiệp
Apps Script cơ bản, Named Functions, tích hợp API — tự động hóa hoàn toàn
Nếu bạn đang xây dựng hệ thống quản lý kinh doanh trên Google Sheets, xem thêm hướng dẫn xây dựng hệ thống quản lý kinh doanh hoàn chỉnh với Google Sheets và template quản lý kho hàng Google Sheets miễn phí.
11. Câu Hỏi Thường Gặp
ARRAYFORMULA trong Google Sheets dùng để làm gì?
ARRAYFORMULA cho phép một công thức áp dụng cho toàn bộ cột hoặc dải ô thay vì phải copy-paste từng ô. Ví dụ =ARRAYFORMULA(A2:A100*B2:B100) tính tích của hai cột chỉ với một công thức duy nhất, giúp tăng tốc độ và giảm lỗi nhập liệu.
Hàm QUERY trong Google Sheets có thể làm gì?
Hàm QUERY sử dụng cú pháp giống SQL để lọc, sắp xếp, nhóm và tổng hợp dữ liệu trực tiếp trong Google Sheets. Bạn có thể viết =QUERY(A:D,"SELECT A,B,SUM(D) WHERE C='Hà Nội' GROUP BY A,B") để lấy báo cáo tổng hợp mà không cần pivot table.
IMPORTRANGE hoạt động như thế nào và có giới hạn gì?
IMPORTRANGE kéo dữ liệu từ một Google Sheet khác vào sheet hiện tại theo thời gian thực. Giới hạn: tối đa 50 hàm IMPORTRANGE trên một file, dữ liệu cập nhật mỗi 30 phút hoặc khi file nguồn thay đổi. File nguồn phải được chia sẻ quyền xem với tài khoản dùng IMPORTRANGE.
Google Apps Script khác gì với macro thông thường?
Macro chỉ ghi lại thao tác thủ công và chạy lại. Apps Script là ngôn ngữ JavaScript đầy đủ, cho phép viết logic phức tạp, gọi API bên ngoài, tự động hóa theo lịch (time-driven trigger), gửi email, tạo Google Docs/Slides từ dữ liệu Sheets, và xây dựng sidebar/dialog tùy chỉnh.
Conditional Formatting nâng cao có thể dựa trên công thức không?
Có — đây là tính năng mạnh nhất của Conditional Formatting trong Google Sheets. Bạn chọn "Custom formula is" và nhập công thức như =$C2>AVERAGE($C$2:$C$100) để tô màu các hàng có giá trị vượt trung bình, hoặc =COUNTIF($A$2:$A2,$A2)>1 để highlight dữ liệu trùng lặp.
Google Sheets có thể xử lý bao nhiêu dòng dữ liệu?
Google Sheets hỗ trợ tối đa 10 triệu ô trên một file. Với dữ liệu lớn, nên dùng hàm QUERY hoặc FILTER thay vì VLOOKUP để tối ưu hiệu năng. Khi vượt 500.000 dòng, nên cân nhắc BigQuery hoặc Sheets API.
Ứng Dụng Google Sheets Cho Kinh Doanh Của Bạn
SheetStore giúp bạn triển khai các kỹ thuật nâng cao này vào hệ thống quản lý thực tế — không cần tự học từ đầu
Chia sẻ bài viết:
Tuân Hoang
Đội ngũ SheetStore
Google Workspace Certified, 5+ years experience
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.