Hàm Query trong Google Sheet là một trong những công cụ mạnh nhất mà bất kỳ dân Pro văn phòng hay dân data analyst nào cũng thực hành và nắm vững nó để công việc nó nhanh hơn nhiều lần – viết được cả câu lệnh kiểu SQL để lọc, sắp xếp, tính toán và tổng hợp dữ liệu chỉ trong một công thức duy nhất. Dưới đây là 7 kỹ thuật cốt lõi, sắp xếp theo thứ tự bảng chữ cái, đủ để bạn dùng QUERY thành thạo trong công việc hàng ngày.
Mục lục.
Câu lệnh SELECT cơ bản
Bước đầu tiên của mọi câu QUERY – chọn xem muốn lấy những cột nào
CÚ PHÁP
=QUERY(range; “select *”)
CÔNG DỤNG
SELECT là phần bắt buộc của mọi câu lệnh QUERY, quyết định những cột nào sẽ xuất hiện trong kết quả trả về. Gõ “select *” để lấy nguyên vẹn toàn bộ các cột trong vùng dữ liệu tham chiếu – tiện khi chỉ muốn sao chép hoặc xem lại dữ liệu gốc ở một vị trí khác. Nếu chỉ cần một phần thông tin, liệt kê tên các cột theo đúng ký hiệu chữ cái (A, B, C…) của vùng dữ liệu, cách nhau bằng dấu phẩy – thứ tự các cột trong kết quả sẽ đúng theo thứ tự bạn liệt kê, không nhất thiết phải giống thứ tự gốc. Một điểm hay bị nhầm: ký hiệu cột trong QUERY luôn tính theo cột đầu tiên của vùng tham chiếu là cột A, dù vùng đó không thực sự bắt đầu từ cột A của sheet gốc.
VÍ DỤ
=QUERY(DonHang!A1:F200; “select A, C, F”)
KẾT QUẢ
Trả về một bảng chỉ gồm 3 cột A, C, F lấy từ vùng dữ liệu DonHang, sắp xếp theo đúng thứ tự đã liệt kê trong câu lệnh – toàn bộ các cột B, D, E hoàn toàn không xuất hiện trong kết quả. Cách làm này gọn hơn hẳn so với việc ẩn thủ công từng cột không cần thiết, và tự động cập nhật nếu dữ liệu gốc ở DonHang thay đổi.
Điều kiện WHERE
Lọc dữ liệu ngay trong câu lệnh, không cần bật Filter thủ công
CÚ PHÁP
=QUERY(range; “select * where cot = ‘gia_tri'”)
CÔNG DỤNG
WHERE thêm một điều kiện lọc vào câu SELECT, chỉ giữ lại những dòng dữ liệu thoả điều kiện đó – y hệt chức năng Filter nhưng chạy dưới dạng công thức nên luôn cập nhật tự động. Với dữ liệu dạng chữ, giá trị so sánh cần đặt trong dấu nháy đơn; với số thì không cần. Có thể dùng các toán tử so sánh quen thuộc như =, !=, >, <, >=, <= tuỳ vào loại dữ liệu của cột đang lọc. Với cột kiểu ngày tháng, giá trị so sánh cần viết theo khuôn date ‘yyyy-mm-dd’ thì QUERY mới hiểu đúng, viết theo định dạng ngày Việt Nam thông thường sẽ báo lỗi hoặc lọc sai.
VÍ DỤ
=QUERY(DonHang!A1:F200; “select * where D = ‘Mien Nam'”)
KẾT QUẢ
Chỉ trả về các dòng có giá trị cột D đúng bằng “Miền Nam”, toàn bộ dữ liệu của các khu vực khác (Miền Trung, Miền Bắc…) bị loại khỏi kết quả hoàn toàn. Nếu dữ liệu nguồn được thêm dòng mới thuộc Miền Bắc, kết quả QUERY sẽ tự cập nhật thêm dòng đó mà không cần chỉnh sửa lại công thức.
Kết hợp AND, OR, CONTAINS trong WHERE
Nâng cấp bộ lọc lên nhiều điều kiện cùng lúc, linh hoạt như dân viết SQL thực thụ
CÚ PHÁP
WHERE dk1 and dk2 / WHERE dk1 or dk2 / WHERE cot contains ‘chuoi’
CÔNG DỤNG
AND yêu cầu TẤT CẢ các điều kiện phải cùng đúng thì dòng dữ liệu mới được giữ lại; OR chỉ cần MỘT trong các điều kiện đúng là đủ. CONTAINS lại khác hẳn hai từ khoá trên – nó không so khớp chính xác mà kiểm tra xem một cột có CHỨA một đoạn ký tự nào đó hay không, rất hữu ích khi lọc theo từ khoá trong tên sản phẩm, mô tả, hay ghi chú dài. Cả ba từ khoá này có thể phối hợp với nhau trong cùng một câu WHERE để tạo ra điều kiện lọc phức tạp tuỳ ý.
VÍ DỤ
=QUERY(DonHang!A1:F200; “select * where D = ‘Mien Nam’ and E contains ‘Ao'”)
KẾT QUẢ
Chỉ trả về các dòng vừa thuộc khu vực “Miền Nam”, vừa có tên sản phẩm ở cột E chứa chữ “Áo” (ví dụ Áo sơ mi, Áo khoác) – hai lớp lọc chạy đồng thời trong đúng một công thức, thay vì phải lọc riêng từng bước bằng công cụ Filter truyền thống. Muốn kết hợp nhiều hơn hai điều kiện, cứ tiếp tục nối thêm and/or vào sau, nhưng nên dùng dấu ngoặc đơn để nhóm rõ ràng khi trộn cả and lẫn or trong cùng một câu, tránh Sheets hiểu sai thứ tự ưu tiên.
Sắp xếp kết quả với ORDER BY
Kết quả trả về đã được xếp hàng ngay ngắn, khỏi cần bấm nút Sort riêng
CÚ PHÁP
=QUERY(range; “select … order by cot desc”)
CÔNG DỤNG
ORDER BY sắp xếp lại thứ tự các dòng trong kết quả trả về theo giá trị của một cột chỉ định. Thêm từ khoá “desc” để sắp xếp giảm dần (giá trị lớn nhất lên trước), hoặc “asc” (hay đơn giản là bỏ trống, vì đây là mặc định) để sắp xếp tăng dần. ORDER BY có thể đứng cùng WHERE trong một câu lệnh, và luôn được đặt ở cuối, sau các điều kiện lọc. Cũng có thể sắp xếp theo nhiều cột cùng lúc bằng cách liệt kê các cột cách nhau dấu phẩy – cột đứng trước sẽ được ưu tiên sắp xếp trước, cột sau chỉ dùng để phân định khi các dòng có giá trị bằng nhau ở cột đầu.
VÍ DỤ
=QUERY(DonHang!A1:F200; “select C, F where F > 0 order by F desc”)
KẾT QUẢ
Trả về danh sách gồm tên người bán (cột C) và doanh thu (cột F), chỉ tính những dòng có doanh thu dương, và toàn bộ danh sách được sắp xếp từ doanh thu cao nhất xuống thấp nhất – một bảng xếp hạng doanh số hoàn chỉnh chỉ trong một dòng công thức.
Tinh chỉnh kết quả với OFFSET, FORMAT, OPTIONS
Bộ ba tinh chỉnh, giúp kết quả QUERY trông đúng ý mình muốn hơn
Ba từ khoá bổ trợ này thường bị bỏ qua nhưng lại rất hữu dụng khi cần một kết quả chỉn chu hơn. OFFSET bỏ qua một số dòng đầu tiên của kết quả đã lọc/sắp xếp – hữu ích khi cần lấy tiếp phần dữ liệu phía sau, kiểu phân trang thủ công. FORMAT định dạng lại cách hiển thị của một cột cụ thể trong kết quả (ví dụ ép ngày tháng hiển thị theo đúng khuôn mẫu mong muốn) mà không ảnh hưởng tới dữ liệu gốc. OPTIONS cho phép khai báo thêm các thiết lập khác của câu truy vấn, phổ biến nhất là no_format để giữ nguyên định dạng số/ngày tháng như trong dữ liệu gốc, tránh QUERY tự ý định dạng lại theo kiểu riêng của nó. Cả ba từ khoá này đều có thể phối hợp cùng lúc với WHERE, ORDER BY và GROUP BY trong cùng một câu lệnh, miễn giữ đúng thứ tự chuẩn của cú pháp QUERY.
VÍ DỤ
=QUERY(DonHang!A1:F200; “select * where F > 1000000 offset 5”)
KẾT QUẢ
Trả về các dòng có doanh thu trên 1.000.000, nhưng bỏ qua đúng 5 dòng đầu tiên khớp điều kiện trong kết quả – dùng khi đã xem qua 5 dòng đầu và giờ muốn xem tiếp phần dữ liệu còn lại mà không lặp lại những dòng đã thấy.
Tính toán và đặt nhãn cột với LABEL
Vừa tính toán ngay trong câu lệnh, vừa tự đặt tên đẹp cho cột kết quả
Cho phép chèn thẳng một biểu thức tính toán (nhân, chia, cộng, trừ, hoặc kết hợp) vào ngay trong câu SELECT, thay vì phải tạo một cột phụ tính toán riêng rồi mới đưa cột đó vào QUERY. Vì kết quả của một biểu thức tính toán mặc định sẽ có tiêu đề cột rất khó đọc (thường lặp lại nguyên công thức), LABEL cho phép đặt lại một cái tên dễ hiểu cho cột đó – cú pháp là lặp lại đúng biểu thức, theo sau bởi LABEL và tên mới đặt trong dấu nháy đơn.
Kết quả trả về ba cột: người bán, doanh thu, và một cột mới tên “Thue VAT” tự động tính bằng 8% giá trị cột doanh thu cho từng dòng – không cần tạo cột phụ nháp nào trong bảng tính gốc, mọi phép tính đều nằm gọn trong một công thức QUERY duy nhất. Đổi số 0.08 thành bất kỳ tỷ lệ nào khác (thuế, hoa hồng, chiết khấu…) là công thức lập tức tính lại theo tỷ lệ mới, không cần sửa gì thêm ở phần LABEL.
Tổng hợp dữ liệu với GROUP BY
Gộp dữ liệu theo nhóm và tính toán tổng hợp, như một PivotTable thu nhỏ trong một dòng công thức
CÚ PHÁP
=QUERY(range; “select cot_nhom, sum(cot_so) group by cot_nhom”)
CÔNG DỤNG
GROUP BY gộp các dòng có cùng giá trị ở một (hoặc nhiều) cột thành một nhóm duy nhất, rồi áp dụng các hàm tổng hợp như SUM, COUNT, AVG, MIN, MAX lên các cột số còn lại – tương tự cách PivotTable tổng hợp dữ liệu, nhưng chạy trực tiếp dưới dạng công thức. Có một quy tắc bắt buộc cần nhớ: mọi cột xuất hiện trong SELECT mà KHÔNG được áp dụng hàm tổng hợp thì bắt buộc phải xuất hiện trong danh sách sau GROUP BY, nếu không câu lệnh sẽ báo lỗi. Có thể nhóm theo nhiều cột cùng lúc (ví dụ vừa theo khu vực vừa theo mặt hàng) để có báo cáo chi tiết hơn một cấp.
VÍ DỤ
=QUERY(DonHang!A1:F200; “select D, count(C), sum(F) group by D”)
KẾT QUẢ
Trả về một bảng tổng hợp gọn gàng: mỗi khu vực (cột D) chiếm đúng một dòng, kèm theo số lượng đơn hàng và tổng doanh thu tương ứng của khu vực đó – toàn bộ 200 dòng dữ liệu thô ban đầu được gói gọn thành vài dòng báo cáo, sẵn sàng đưa vào slide trình bày mà không cần dựng PivotTable riêng.
Lời kết cho Hàm Query trong Google Sheet-Sức mạnh của SQL
QUERY không thay thế hoàn toàn PivotTable hay Filter, nhưng khi cần một kết quả linh hoạt, tự động cập nhật và gói gọn trong một ô – không gì nhanh gọn bằng. Nó mượn sức mạnh của SQL để dùng cho bảng tính làm cho mọi việc nó nhanh và trơn tru hơn hẳn khi cần tra cứu dữ liệu.
