Cú pháp hàm Query trong Google Sheets
QUERY(dữ liệu; truy vấn; [tiêu đề])
– Dữ liệu: Có thể là các cột (ví dụ: A:C), dãy hàng (A1:C100), hoặc là dữ liệu được IMPORTRANGE, INDEX, FILTER từ nơi khác.
– Truy vấn: Cú pháp ngôn ngữ truy vấn => Xem trang tiếp
– [tiêu đề] là số lượng dòng tiêu đề
Lưu ý: Để đảm bảo hàm Query lấy đủ dữ liệu, các cột dữ liệu phải thống nhất là text hoặc number, date,… Nếu các cột không thống nhất dữ liệu, thì có thể mất dữ liệu khi dùng Query, khi đó bạn nên chuyển sang hàm Filter hoặc xem cách khắc phụ nếu vẫn muốn dùng Query tại đây
Query Select lấy cột thông thường
- Dù chọn Ngôn ngữ là Việt Nam hay Hoa Kỳ thì khi lấy các cột, bạn vẫn dùng dấy phẩy (,), ví dụ “Select A, B, C”
- Lấy cột A, B, C,.., AA, AB,…, AAA,…,ZZZ chỉ dùng khi lấy từ vùng dữa liệu sẵn có cùng file (các chữ cái VIẾT HOA).
- Nếu DATA dữ liệu dùng hàm VSTACK, HSTACK, IMPORTRANGE, INDEX, FILTER,… từ nơi khác thì bắt buộc dùng Col1, Col2, Col3,… Và nhớ Col1, Col2,… phải viết liền chữ Col với số cột, và chữ C viết hoa. Lưu ý rằng, Col1, Col2 là tính từ cột đầu tiên của vùng bạn chọn, chứ không được hiểu là tính từ cột A. Ngoài ra, bạn cần lưu ý rằng, khi bạn chèn cột, xóa cột thì kết quả của hàm Query chưa chắc đã đúng vì các tên cột vẫn là A, B, C mà không thay đổi khi bạn xóa, chèn cột như sử dụng hàm Filter (xem thêm hàm Filter tại đây)
- Chọn các cột cần trả về và thứ tự trả về (bạn viết cột nào trước thì cột đấy sẽ xuất hiện trước_. Nếu bị bỏ qua (hoặc dùng Select *), tất cả các cột của bảng sẽ được trả về theo thứ tự mặc định.
- Các cột lấy phải nằm trong vùng dữ liệu. Ví dụ, vùng dữ liệu là DATA!A:H mà bạn lấy cột K sẽ báo lỗi, vì K không nằm trong A:H
- Khi đánh hàm Query, ngoại trừ tên hàm, dấu ; (chấm phẩy) và (…) đóng mở ngoặc là màu đèn thì các thành phần còn lại của công thức phải có màu xanh, vàng,….
- Hàng tiêu đề chính là hàng “Ngày bán – Nhân viên – Mặt hàng -…”, Giả sử bạn chọn DATA!A:H thì số hàng tiêu đề là 1, nếu bạn chọn DATA!A2:H thì số hàng tiêu đề là 0.
- Nhớ kết thúc hàm Query luôn là “;số hàng tiêu đề)trong đó “ (nháy kép) cuối là đóng của nháy kép đầu của câu lệch “Select ,nếu bạn bỏ qua thông số này, có thể xảy ra tình trạng kết quả hàm Query nhiều dòng đầu tiên bị gộp lại, vui lòng xem hướng dẫn xử lý tại đây
=QUERY(DATA!A:H;“Select *”;1)
📝 Lấy tất cả các cột ở sheet DATA!A:H, trong đó dòng đầu tiên là dòng tiêu đề. Bạn có thể bỏ qua không đánh “Select *” nếu như lấy toàn bộ.
=QUERY(DATA!A:H;“Select C, D, F, G”;1)
📝 Lấy các cột C, D, F, G ở sheet DATA!A:H. Dòng đầu tiên là tiêu đề. Kết quả trả về cột Mặt hàng, ĐVT, Đơn giá, Thành tiền
=QUERY(DATA!A2:H;“Select Col3, Col7”;0)
📝Lấy các cột Col3, Col7 ở sheet DATA!A2:H. Không có dòng tiêu đề. Kết quả trả về cột Mặt hàng, Thành tiền
Query Select các cột đặc biệt như: BY, OR, AND, ASC, NOT
Google sheets giới hạn 18.278 cột cho 1 sheet, tương ứng bạn có thể có cột A, cột B,… cột AA, cột AB,… cột AAA,… và cột cuối cùng là ZZZ.
Hàm QUERY coi một số từ khóa như AND, OR, ASC, BY, NOT là các từ dành riêng và không thể được sử dụng trực tiếp làm tiêu đề cột. Để tránh vấn đề này, bạn cần sử dụng dấu gạch ngược (góc phía trên bên trái bàn phím – không phải dấu nháy đơn) quanh các tên cột này để xử lý chúng như văn bản thuần túy hoặc bạn phải chuyển sang dùng Col khi lấy cột.
Query Select thêm cột trống (cột ảo)
- Khi thêm cột ảo là “cột trống” thì mỗi lần tăng cột ảo thì thêm 1 phím cách (cột ảo đầu tiên là 1 phím cách, cột ảo thứ 2 là 2 phím cách,…)
- Bạn cũng có thểm thêm 1 cột ảo là số, hoặc ký tự đặc biệt hoặc text, ví dụ: =QUERY(DATA!A:H;“Select C, D, 0, 100, ‘$'”;1)
- Sau khi tạo cột ảo, muốn đổi tên tiêu đề cột ảo thì dùng Label, còn nếu không muốn hiện tiêu đề cột ảo, thì dùng ” (2 nháy đơn – (nhớ là 2 nháy đơn, chứ không phải nháy kép). Ví dụ: Label ‘Chưa thanh toán’ ”
Query Select lấy cột ngày tháng năm
Query bắt đầu với tháng đầu tiên là 0 ( tháng 1 = 0). Vì vậy hàm Month trong Query cần cộng ( + ) thêm 1
=QUERY(DATA!A:H;“Select day(A), month(A)+1, year(A), Quarter(A), C, D, E, G”;1)
Kết quả trả về Ngày cột A > Tháng cột A > Năm cột A > Quý cột A, và cột Mặt hàng, ĐVT, Số lượng, Thành tiền
Query Select tính toán sum, count, max, min, avg
Khi tính toán các cột theo 1 hoặc nhiều cột đã nhóm lại (group by) để cộng (sum), đếm (count), giá trị lớn nhất (max), giá trị nhỏ nhất (min), tính trung bình (avg) thì những cột nào không sum, count, max, min, avg thì đều phải đặt sau group by
Xem thêm bài viết về group by tại đây
Query Select tính toán cộng, trừ, nhân, chia hoặc với công thức tính toán cụ thể
- Khi tính toán với số, dù chọn Ngôn ngữ là Việt Nam hay Hoa Kỳ thì định dạng số luôn là 1,123.45 khi tính toán với cột. Và khi bạn muốn tính toán với % thì đổi sang dạng số, ví dụ thay vì nhân với 75% thì bạn viết nhân với 0.75
- Khi tính toán cột với công thức thì công thức được đặt trong “& công thức &”
Trong trường hợp này label G*0.1 ‘Thuế GTGT 10%’ dùng để đổi tên cột G*0.1
và G/”&SUM(DATA!G:G)&“ ‘Tỷ trọng’ dùng để đổi tê cột G/”&SUM(DATA!G:G)&”
trong trường hợp bạn muốn tiêu đề là “ô trống” thì dùng 2 nháy đơn ” (nhớ là 2 nháy đơn, chứ không phải nháy kép), ví dụ label G*0.1 ”
Xem thêm bài viết về label tại đây
Thực hành 1
Đang làm
Query Select tính toán nâng cao
Bạn muốn ghép các cột với nhau, hoặc cộng cột A với cột B, hoặc có những phép tính phức tạp hơn với các cột?
Thực hành 2
Đang làm
Query Select lấy nhiều cột mà không cần liệt kê
Ví dụ bạn bảng tính gồm 50 cột, và bạn muốn lấy từ cột 10 đến cột 30, và bạn sẽ liệt kê Col10, Col11, Col12,…, Col29, Col30?
Thực hành 3
Đang làm
Query Select chọn các cột linh hoạt theo tên tiêu đề
Thông thường, khi bạn dùng Select A, B, C hoặc Select Col1, Col2, Col3 thì bạn khó có thể tùy biến được các cột, tuy nhiên cũng có giải pháp giúp bạn lựa chọn tiêu đề các cột, Query sẽ trích lọc dữ liệu cho bạn theo các tiêu đề này
Thực hành 4
Đang làm
