QUERY + ARRAYFORMULA + REGEX: Bộ 3 Công Thức Nâng Cao Làm Chủ Google Sheets [2026]

Giới Thiệu: Ba Hàm Nâng Cao Thay Đổi Cách Bạn Dùng Google Sheets

Nếu bạn chỉ dùng Google Sheets để nhập số liệu và tính tổng, bạn đang bỏ lỡ 80% sức mạnh của công cụ này. Có ba hàm nâng cao mà khi nắm vững, bạn có thể xử lý dữ liệu như một chuyên gia phân tích: QUERY, ARRAYFORMULA và REGEX.

QUERY giúp bạn lọc, sắp xếp và tổng hợp dữ liệu bằng ngôn ngữ giống SQL — không cần biết lập trình. ARRAYFORMULA áp dụng một công thức cho toàn bộ cột chỉ với một ô duy nhất. Còn REGEX (bao gồm REGEXMATCH, REGEXEXTRACT, REGEXREPLACE) giúp bạn tìm kiếm, trích xuất và làm sạch dữ liệu văn bản theo pattern cực kỳ linh hoạt.

Bài viết này sẽ hướng dẫn chi tiết từng hàm với 5 ví dụ thực tế mỗi loại, sau đó chỉ cách kết hợp cả ba để giải quyết các bài toán phức tạp trong công việc.

Bảng Tóm Tắt: Khi Nào Dùng Hàm Nào?

QUERY — SQL Ngay Trong Google Sheets

Cú Pháp Cơ Bản

=QUERY(data, query_string, [headers])

• data: Vùng dữ liệu cần truy vấn, ví dụ A:E hoặc A1:F500
• query_string: Chuỗi truy vấn dạng SQL đặt trong dấu ngoặc kép
• [headers]: Số hàng tiêu đề (thường là 1). Bỏ qua hoặc để -1 nếu dữ liệu không có tiêu đề

Các từ khóa SQL được hỗ trợ: SELECT, WHERE, ORDER BY, GROUP BY, LIMIT, OFFSET, LABEL, FORMAT, cùng các hàm tổng hợp SUM, AVG, COUNT, MAX, MIN.

Ví Dụ 1: Lọc Theo Nhiều Điều Kiện

Giả sử bạn có bảng dữ liệu khách hàng với cột A (Tên), B (Chức vụ), C (Email), D (Thành phố), E (Doanh thu). Bạn muốn lấy ra những khách ở Hà Nội có doanh thu trên 1 triệu, sắp xếp theo doanh thu giảm dần:

=QUERY(A:E,"SELECT A,B,C WHERE D='Hà Nội' AND E>1000000 ORDER BY E DESC",1)

Giải thích: SELECT A,B,C chọn 3 cột cần hiển thị, WHERE D='Hà Nội' lọc theo thành phố, AND E>1000000 thêm điều kiện doanh thu, ORDER BY E DESC sắp xếp giảm dần.

Kết quả mẫu: Chỉ hiển thị các hàng thỏa mãn cả hai điều kiện, đã sắp xếp theo doanh thu cao nhất ở trên.

Ví Dụ 2: Group By Và Tính Tổng

Bạn có bảng giao dịch với cột A (Nhân viên), B (Tháng), C (Doanh số). Muốn tính tổng doanh số từng nhân viên:

=QUERY(A:C,"SELECT A, SUM(C) GROUP BY A LABEL SUM(C) 'Tổng Doanh Số'",1)

Giải thích: GROUP BY A nhóm theo tên nhân viên, SUM(C) cộng doanh số của từng người, LABEL đặt tên cho cột kết quả.

Kết quả mẫu: Bảng tóm tắt với mỗi nhân viên 1 hàng và tổng doanh số tương ứng.

Ví Dụ 3: Top 5 Kết Quả Tốt Nhất

Lấy 5 sản phẩm có doanh thu cao nhất (bỏ qua những hàng doanh thu trống):

=QUERY(A:D,"SELECT A,B,D WHERE D IS NOT NULL ORDER BY D DESC LIMIT 5",1)

Giải thích: WHERE D IS NOT NULL loại bỏ hàng trống, ORDER BY D DESC sắp xếp từ cao đến thấp, LIMIT 5 chỉ lấy 5 hàng đầu.

Ứng dụng thực tế: Tạo bảng Top 5 bán chạy, Top 5 khách hàng VIP, Top 5 nhân viên xuất sắc.

Ví Dụ 4: Lọc Chuỗi Chứa Từ Khóa

Tìm tất cả nhân viên có chức vụ liên quan đến "Manager" và lương trên 5 triệu đang làm việc:

=QUERY(A:F,"SELECT A,B,C WHERE E>5000000 AND F='Active' AND B CONTAINS 'Manager'",1)

Giải thích: CONTAINS là toán tử tìm chuỗi con — khác với = yêu cầu khớp chính xác. Rất hữu ích khi cột B có thể có giá trị như "Senior Manager", "Project Manager", "Team Manager".

Ví Dụ 5: Nhúng Biến Vào Query

Thay vì hardcode giá trị trong query, bạn có thể tham chiếu từ ô khác để tạo bộ lọc động:

=QUERY(A:C,"SELECT * WHERE B='"&F1&"'",1)

Giải thích: Dùng & để ghép giá trị ô F1 vào chuỗi query. Khi bạn thay đổi F1 (ví dụ từ dropdown), kết quả QUERY tự động cập nhật — tạo ra bộ lọc động không cần script.

Lưu ý: Chuỗi text trong WHERE phải được bao bởi dấu nháy đơn ('). Với số, không cần dấu nháy: "SELECT * WHERE C>"&G1.

Mẹo Dùng QUERY Hiệu Quả

• Lỗi "Unable to parse query string": Kiểm tra dấu nháy đơn xung quanh giá trị text trong WHERE. Lỗi phổ biến nhất.
• Tên cột trong SELECT: Dùng Col1, Col2... nếu dữ liệu không có header (và đặt [headers]=0).
• Kết hợp WITH OFFSET: LIMIT 10 OFFSET 5 lấy 10 hàng bắt đầu từ hàng thứ 6 — hữu ích cho phân trang.
• WHERE với ngày tháng: Dùng format date '2026-01-01', ví dụ: WHERE A >= date '2026-01-01'.

ARRAYFORMULA — Một Công Thức Cho Toàn Bộ Cột

Cú Pháp Cơ Bản

=ARRAYFORMULA(formula)

ARRAYFORMULA cho phép một công thức xử lý toàn bộ một range thay vì chỉ một ô. Kết hợp với IF để xử lý ô trống, bạn có thể tạo ra các cột tính toán tự động mở rộng khi thêm dữ liệu mới.

Cách dùng nhanh: Thay vì nhập công thức vào B2 rồi kéo xuống B100, bạn chỉ cần nhập một công thức ARRAYFORMULA vào B1 (hoặc B2), và nó tự xử lý toàn bộ cột.

Ví Dụ 1: Tính Thành Tiền Tự Động

Bạn có cột B (Số lượng) và C (Đơn giá). Muốn cột D tự tính thành tiền cho tất cả các hàng có dữ liệu:

=ARRAYFORMULA(IF(A2:A<>"",B2:B*C2:C,""))

Giải thích: IF(A2:A<>"",...,"") kiểm tra nếu cột A (tên sản phẩm) không trống thì tính, ngược lại để trống. Tránh lỗi hiển thị số 0 ở các hàng chưa nhập liệu.

Kết quả: Mỗi khi bạn thêm một hàng mới vào cột A, B, C — cột D tự động tính thành tiền mà không cần kéo công thức.

Ví Dụ 2: Tạo Mã Đơn Hàng Tự Động

Tự động sinh mã đơn hàng theo định dạng DH001, DH002, DH003... cho mỗi đơn có dữ liệu trong cột B:

=ARRAYFORMULA(IF(B2:B<>"","DH"&TEXT(ROW(B2:B)-1,"000"),""))

Giải thích: ROW(B2:B)-1 trả về số thứ tự từ 1, 2, 3... TEXT(...,"000") định dạng thành 001, 002, 003. Ghép với "DH" tạo ra mã đơn hàng chuyên nghiệp.

Kết quả mẫu: DH001, DH002, DH003... tự động điền khi có đơn hàng mới.

Ví Dụ 3: Phân Loại Khách Hàng Tự Động

Phân loại khách hàng thành VIP/Standard/Basic dựa trên doanh thu ở cột D:

=ARRAYFORMULA(IF(D2:D>10000000,"VIP",IF(D2:D>5000000,"Standard","Basic")))

Giải thích: ARRAYFORMULA với IF lồng nhau hoạt động theo logic: trên 10 triệu = VIP, 5-10 triệu = Standard, còn lại = Basic. Áp dụng cho toàn bộ cột D cùng một lúc.

Ứng dụng: Phân loại sản phẩm theo giá, phân loại nhân viên theo điểm, phân loại lead theo điểm chất lượng.

Ví Dụ 4: VLOOKUP Hàng Loạt

Tra cứu tên sản phẩm từ Sheet2 cho toàn bộ danh sách mã sản phẩm trong cột A:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,Sheet2!A:B,2,0),"Không tìm thấy"))

Giải thích: IFERROR(...,"Không tìm thấy") xử lý trường hợp mã không tồn tại trong danh sách. Không cần kéo VLOOKUP xuống từng hàng — ARRAYFORMULA làm hết.

Lưu ý hiệu năng: Với dataset lớn (>1000 hàng), cân nhắc dùng INDEX/MATCH thay VLOOKUP để tốc độ tốt hơn.

Ví Dụ 5: Tạo Nhãn Hiển Thị

Ghép họ tên, bộ phận và chức vụ thành một chuỗi để hiển thị trên báo cáo:

=ARRAYFORMULA(IF(A2:A<>"",A2:A&" - "&B2:B&" ("&C2:C&")",""))

Kết quả mẫu: "Nguyễn Văn An - Kinh doanh (Trưởng phòng)", "Lê Thị Bình - Marketing (Nhân viên)"

Ứng dụng: Tạo cột dropdown label, tạo tên file xuất, tạo dòng tiêu đề báo cáo động.

Mẹo Dùng ARRAYFORMULA Hiệu Quả

• Tránh lỗi tràn kết quả: ARRAYFORMULA cần các ô bên dưới trống. Nếu bị lỗi #REF!, có thể có dữ liệu đang cản trở ở phía dưới.
• Không kết hợp được với mọi hàm: Một số hàm như CONCATENATE không hoạt động với ARRAYFORMULA — dùng toán tử & thay thế.
• Phím tắt nhập nhanh: Nhấn Ctrl+Shift+Enter khi nhập công thức, Google Sheets sẽ tự thêm ARRAYFORMULA().
• Kết hợp với LEN để đếm ký tự: =ARRAYFORMULA(LEN(A2:A)) đếm độ dài toàn bộ cột A.

REGEX — Tìm Kiếm Và Xử Lý Text Nâng Cao

Ba Hàm REGEX Trong Google Sheets

Google Sheets cung cấp ba hàm REGEX với chức năng khác nhau:

• REGEXMATCH(text, pattern): Trả về TRUE/FALSE — có khớp pattern không?
• REGEXEXTRACT(text, pattern): Trả về phần chuỗi khớp với pattern đầu tiên
• REGEXREPLACE(text, pattern, replacement): Thay thế phần khớp bằng chuỗi mới

Các Pattern Regex Hay Dùng

Ví Dụ 1: Kiểm Tra Email Hợp Lệ (REGEXMATCH)

Validate toàn bộ cột email để phát hiện email sai định dạng trước khi gửi chiến dịch:

=REGEXMATCH(A2,"[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}")

Kết quả: TRUE với "nguyenvana@gmail.com", FALSE với "nguyenvana@" hoặc "test.com"

Kết hợp với ARRAYFORMULA: =ARRAYFORMULA(REGEXMATCH(A2:A,"[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}")) để kiểm tra cả cột cùng lúc. Sau đó dùng QUERY WHERE cột này = TRUE để lấy danh sách email hợp lệ.

Ví Dụ 2: Trích Xuất Số Từ Chuỗi (REGEXEXTRACT)

Cột A chứa các chuỗi hỗn hợp như "Đơn hàng #12345 - Giao thành công". Muốn lấy số đơn hàng:

=REGEXEXTRACT(A2,"[0-9]+")

Kết quả: "12345" (chuỗi số đầu tiên tìm thấy)

Lưu ý: REGEXEXTRACT trả về chuỗi text, không phải số. Nếu cần tính toán, bọc thêm VALUE(): =VALUE(REGEXEXTRACT(A2,"[0-9]+"))

Ví Dụ 3: Lấy Tên Miền Từ Email (REGEXEXTRACT)

Phân tích nguồn email khách hàng — gmail.com, yahoo.com hay email công ty:

=REGEXEXTRACT(A2,"@(.+)")

Kết quả: "gmail.com" từ "khachhang@gmail.com", "vinamilk.com.vn" từ "sales@vinamilk.com.vn"

Ứng dụng: Sau khi có cột tên miền, dùng QUERY GROUP BY để đếm xem khách hàng đến từ tổ chức nào nhiều nhất.

Ví Dụ 4: Chuẩn Hóa Số Điện Thoại (REGEXREPLACE)

Dữ liệu điện thoại nhập không nhất quán: "(090) 123-4567", "090.123.4567", "090 123 4567". Xóa tất cả ký tự không phải số:

=REGEXREPLACE(A2,"[^0-9]","")

Kết quả: "0901234567" — chuẩn hóa về định dạng thuần số dù đầu vào có dấu ngoặc, gạch ngang hay khoảng trắng.

Bước tiếp theo: Kết hợp với REGEXMATCH để xác nhận số đã chuẩn hóa có đúng định dạng SĐT Việt Nam không: =REGEXMATCH(REGEXREPLACE(A2,"[^0-9]",""),"^(0)[0-9]{9}$")

Ví Dụ 5: Kiểm Tra Mã Sản Phẩm Đúng Format (REGEXMATCH)

Công ty quy định mã sản phẩm phải gồm 2 chữ cái in hoa + 4 chữ số (ví dụ SP0001, AB1234). Validate toàn bộ danh sách:

=REGEXMATCH(A2,"^[A-Z]{2}[0-9]{4}$")

Giải thích pattern: ^ = bắt đầu chuỗi, [A-Z]{2} = đúng 2 chữ hoa, [0-9]{4} = đúng 4 chữ số, $ = kết thúc chuỗi. Ký tự ^ và $ quan trọng — không có chúng, "XSP0001X" cũng sẽ trả về TRUE.

Kết quả: TRUE với "SP0001", FALSE với "sp0001" (chữ thường), "SP001" (thiếu số), "SP00010" (thừa số).

Mẹo Làm Việc Với REGEX

• Test pattern trước khi dùng: Vào regex101.com, chọn flavor "ECMAScript" (gần nhất với Google Sheets) và test pattern với dữ liệu thực.
• Escape ký tự đặc biệt: Các ký tự . * + ? ( ) [ ] { } ^ $ | có nghĩa đặc biệt trong regex. Nếu muốn tìm ký tự đó theo nghĩa đen, thêm dấu phía trước: . để tìm dấu chấm thật.
• Case-sensitive mặc định: REGEX trong Google Sheets phân biệt chữ hoa/thường. Dùng (?i) ở đầu pattern để bỏ qua: (?i)[a-z] sẽ khớp cả chữ hoa.
• Lỗi khi không tìm thấy: REGEXEXTRACT trả về lỗi #N/A nếu không khớp — bọc thêm IFERROR: =IFERROR(REGEXEXTRACT(A2,"[0-9]+"),"")

Kết Hợp Cả Ba Hàm: Ví Dụ Thực Chiến

Kết Hợp ARRAYFORMULA + REGEXMATCH: Validate Cả Cột

Kiểm tra toàn bộ cột A xem email nào là gmail:

=ARRAYFORMULA(REGEXMATCH(A2:A,"@gmail.com"))

Lưu ý: Dấu . để thoát dấu chấm — nếu viết @gmail.com thì dấu . có nghĩa là "bất kỳ ký tự nào" và sẽ match cả "@gmailXcom".

Kết Hợp QUERY + Cột Phụ REGEX: Lọc Theo Pattern

Bài toán: Bạn muốn QUERY chỉ lấy những hàng có số điện thoại hợp lệ. QUERY không hỗ trợ regex trực tiếp, nhưng bạn có thể:

1. Tạo cột phụ (ví dụ cột G) với =ARRAYFORMULA(REGEXMATCH(E2:E,"^(0)[0-9]{9}$"))
2. Chạy QUERY lọc theo cột phụ: =QUERY(A:G,"SELECT A,B,C,D,E WHERE G=TRUE",1)

Kết quả: Chỉ hiển thị những hàng có SĐT đúng định dạng 10 chữ số bắt đầu bằng 0.

Kết Hợp QUERY + ARRAYFORMULA: Truy Vấn Dữ Liệu Được Tính Toán

QUERY có thể truy vấn kết quả của ARRAYFORMULA — cho phép lọc trên dữ liệu đã được xử lý:

=QUERY(ARRAYFORMULA(A2:A&" "&B2:B),"SELECT * WHERE Col1 CONTAINS 'Hà Nội'",0)

Giải thích: ARRAYFORMULA ghép cột A và B thành một chuỗi, sau đó QUERY tìm kiếm trong chuỗi đã ghép. Hữu ích khi điều kiện lọc cần kết hợp nhiều cột.

Pipeline Làm Sạch Và Lọc Dữ Liệu Hoàn Chỉnh

Kịch bản thực tế: Bạn nhận file Excel thô với cột SĐT lộn xộn, cần làm sạch và lọc khách hàng hợp lệ ở TP.HCM:

1. Cột G — Chuẩn hóa SĐT: =ARRAYFORMULA(REGEXREPLACE(E2:E,"[^0-9]",""))
2. Cột H — Validate SĐT: =ARRAYFORMULA(REGEXMATCH(G2:G,"^0[0-9]{9}$"))
3. Cột I — Lấy tên miền email: =ARRAYFORMULA(IFERROR(REGEXEXTRACT(D2:D,"@(.+)"),""))
4. Sheet kết quả — QUERY lọc cuối:

=QUERY(A:H,"SELECT A,B,C,D,G WHERE F='TP.HCM' AND H=TRUE ORDER BY B",1)

Pipeline này tự động xử lý từ dữ liệu thô đến danh sách khách hàng sạch, hợp lệ, đã chuẩn hóa.

Lỗi Thường Gặp Và Cách Sửa

Kết Luận

QUERY, ARRAYFORMULA và REGEX là ba hàm nâng cao nhất trong Google Sheets, và chúng bổ trợ cho nhau hoàn hảo. QUERY giúp bạn phân tích và trích xuất dữ liệu như một chuyên gia SQL. ARRAYFORMULA giúp tự động hóa tính toán trên toàn bộ bảng dữ liệu. REGEX giúp xử lý và validate dữ liệu text với độ chính xác cao.

Để nắm vững ba hàm này, hãy bắt đầu với bài toán thực tế của bạn:

• Có bảng dữ liệu lớn cần lọc theo nhiều điều kiện? → Bắt đầu với QUERY
• Cần tính toán tự động cho cả cột mà không muốn kéo công thức? → ARRAYFORMULA
• Cần làm sạch hoặc validate dữ liệu text, email, số điện thoại? → REGEX

Khi bạn đã quen từng hàm, hãy thử kết hợp chúng như pipeline trong phần cuối bài — đó là lúc năng suất xử lý dữ liệu của bạn tăng vọt. Một bảng tính được xây dựng đúng với ba hàm này có thể thay thế hoàn toàn nhiều bước xử lý thủ công tốn hàng giờ mỗi tuần.

Gợi ý tiếp theo: Học cách kết hợp QUERY với IMPORTRANGE để lọc dữ liệu từ nhiều sheet khác nhau, hoặc khám phá cách dùng ARRAYFORMULA với các hàm thống kê như COUNTIF, SUMIF để tạo báo cáo tự động.