Bạn có thể đã nghe đến SQL, một ngôn ngữ truy vấn cấu trúc cho việc xử lý thông tin trong cơ sở dữ liệu quan hệ, trong đó các dữ liệu được lưu trữ theo dạng bảng. Trong bảng tính, thông tin cũng được lưu trữ dưới dạng bảng, vì vậy có lý thuyết là bạn có thể truy vấn dữ liệu theo cách tương tự như SQL.
Google Sheets cung cấp tùy chọn này với sự giúp đỡ của hàm QUERY. Nó rất giống với SQL và kết hợp các tính năng của nhiều hàm khác nhau như FILTER, SUM, VLOOKUP và nhiều hơn nữa. Vì vậy, nếu bạn đã có kiến thức về SQL, việc nắm vững hàm QUERY trong Google Sheets sẽ dễ dàng hơn nhiều. Nếu bạn chưa biết gì về SQL, đừng lo lắng, chúng tôi đã cống hiến rất nhiều công sức vào bài viết này để trở nên dễ hiểu và phù hợp cho người mới bắt đầu, nó sẽ giải đáp hầu hết câu hỏi liên quan đến QUERY trong Google Sheets mà bạn có thể gặp phải.
Hàm QUERY trong Google Sheets là gì?
Hàm QUERY trong Google Sheets cho phép bạn truy vấn dữ liệu dựa trên các tiêu chí và, nếu cần, thay đổi định dạng, thực hiện tính toán bổ sung, thay đổi thứ tự các cột, v.v. Điều này đảm bảo nguồn dữ liệu của bạn không thay đổi, và bảng tính của bạn chứa các cột và dòng mà bạn cần để hoàn thành công việc.
Làm thế nào để sử dụng hàm QUERY trong Google Sheets?
Hàm QUERY trong Google Sheets cho phép người dùng thực hiện các thao tác dữ liệu khác nhau. Ví dụ, nó rất hữu ích khi bạn cần chuẩn bị dữ liệu trong một định dạng đặc biệt để có thể sử dụng chúng để tạo ra một số loại biểu đồ. Nguồn dữ liệu của bạn có thể bao gồm quá nhiều thông tin hoặc không phù hợp với định dạng đặc biệt của biểu đồ hoặc thứ tự của các cột.
Google Sheets Query sử dụng ngôn ngữ truy vấn của API Google Sheets để truy xuất và thao tác dữ liệu từ API Google Sheets. Ví dụ, giả sử bạn muốn lấy tất cả các dòng từ một bảng điều khiển nơi giá trị trong cột A lớn hơn 10. Trong trường hợp này, cách hoạt động của hàm Query trong Google Sheets như sau:
- Bạn tạo một truy vấn theo kiểu SQL bằng cách sử dụng ngôn ngữ truy vấn của API Google Sheets.
- Hàm gửi yêu cầu đến API Google Sheets sử dụng truy vấn và nhận lại dữ liệu đã lọc.
Cú pháp của hàm QUERY trong Google Sheets
Một lời tiên tri nhỏ ở đầu bài viết – tôi sẽ giải thích từng chuỗi truy vấn riêng lẻ và chỉ vào bảng tính Google Sheets này để cho thấy cách nó hoạt động trong thực tế.
Bây giờ, bắt đầu hành trình của chúng ta bằng cách xem xét cú pháp của hàm QUERY trong Google Sheets.
=QUERY(data, query, [headers])
data– một tập hợp các ô mà bạn muốn Google Sheets truy vấn.query– một chuỗi chứa truy vấn được tạo bằng ngôn ngữ truy vấn của API Google. Hãy nhớ bao quanh truy vấn của bạn bằng dấu nháy kép như thế này:=query('data from Airtable'!A:L,"select *")headers– một phần tùy chọn của công thức Query để xác định số dòng tiêu đề trong tập dữ liệu của bạn.
Các literal trong hàm QUERY của Google Sheets là gì?
Literal là các loại giá trị khác nhau mà bạn nhập vào một bảng tính. Có các loại literal sau trong ngôn ngữ truy vấn của API Google:
- Chuỗi ký tự – giá trị văn bản được đặt trong dấu nháy đơn hoặc nháy kép. Lưu ý rằng chúng phân biệt chữ hoa chữ thường. Ví dụ: “ngày đầu tiên” ‘một người’ “Burger”
- Số – các số được sử dụng trong notation thập phân. Ví dụ: 1 2.5 7.15 -20.0 .8
- Ngày/Giờ – loại literal này bao gồm: 1) từ “DATE” theo sau là giá trị với định dạng yyyy-MM-dd; 2) từ “TIMEOFDAY” theo sau là giá trị với định dạng HH:mm:ss[.SSS]; 3) từ “TIMESTAMP” hoặc “DATETIME” theo sau là giá trị với định dạng yyyy-MM-dd HH:mm:ss[.SSS]
Lưu ý: Mỗi cột chỉ có thể có một loại literal duy nhất: chuỗi ký tự hoặc giá trị số (bao gồm số và ngày/giờ). Nếu một cột cụ thể chứa nhiều loại literal, Google Sheets sẽ sử dụng loại dữ liệu được sử dụng phổ biến nhất cho cột đó để thực thi hàm Query.
Cách nhập và truy vấn dữ liệu mà không cần sử dụng công thức
Nếu bạn mới bắt đầu với truy vấn dữ liệu hoặc gặp khó khăn trong việc làm theo cú pháp các công thức Google Sheets khác nhau, Coupler.io cung cấp một giải pháp đơn giản và trực quan hơn.
Bạn có thể truy vấn dữ liệu từ các bảng tính và ứng dụng khác nhau theo thời gian thực bằng cách sử dụng Coupler.io mà không cần bất kỳ công thức nào. Điều này giúp bạn tiết kiệm thời gian, tránh các lỗi tiềm ẩn và có dữ liệu cập nhật.
Hãy thử ngay – chọn nguồn dữ liệu đích trong biểu mẫu dưới đây và nhấp vào “Tiếp theo”. Bạn có thể đăng nhập bằng tài khoản Google của mình mà không cần cung cấp chi tiết thanh toán.
Sau đó, bạn sẽ cần kết nối nguồn dữ liệu của mình và xác định dữ liệu nào sẽ được nhập. Sau đó, bạn có thể truy vấn và chuyển đổi dữ liệu bằng cách sử dụng các tùy chọn sau:
- Quản lý cột – ẩn và hiển thị các cột theo nhu cầu của bạn.
- Bộ lọc – thay vì sử dụng các tổ hợp khác nhau của các mệnh đề WHERE, bạn có thể lọc dựa trên nhiều tiêu chí.
- Sắp xếp – Sắp xếp dữ liệu theo thứ tự tăng dần, giảm dần hoặc theo ngày mà không cần sử dụng mệnh đề ORDER BY.
- Công thức – dễ dàng tạo công thức tùy chỉnh bằng cách sử dụng các cột khác nhau.
Đây là cách bạn có thể sử dụng Coupler.io để truy vấn dữ liệu từ Airtable sang Google Sheets.
Bây giờ, hãy lưu trữ dữ liệu đã được chuyển đổi của bạn trong Google Sheets. Đặt lịch một lịch trình tự động làm mới dữ liệu để chuyển dữ liệu từ Airtable sang Google Sheets mỗi 15 phút.
Điều này giúp bạn luôn có dữ liệu từ Airtable được truy vấn theo thời gian thực trong Google Sheets.
Các mệnh đề truy vấn trong Google Sheets để thiết kế các truy vấn dữ liệu
Trong SQL, một mệnh đề là một thành phần trong một lệnh xác định một hành động cụ thể. Trong ngôn ngữ truy vấn của API Google Sheets, có 9 mệnh đề, mỗi mệnh đề có mục tiêu riêng. Chúng là tùy chọn, có nghĩa là bạn không cần phải bao gồm tất cả chúng trong một truy vấn duy nhất.
Một chuỗi truy vấn có thể chứa nhiều mệnh đề phân tách bằng dấu cách và phải được viết theo thứ tự này: 1) SELECT, 2) WHERE, 3) GROUP BY, 4) PIVOT, 5) ORDER BY, 6) LIMIT, 7) OFFSET, 8) LABEL, 9) FORMAT.
Hãy tiếp tục đọc để biết thêm về các mệnh đề này và xem các ví dụ đi kèm. Nhưng trước tiên, hãy khám phá một số ví dụ dữ liệu để giúp bạn nắm vững các kỹ năng công thức QUERY của mình.
#1: Google Sheets Query: SELECT
Mệnh đề SELECT cho phép bạn xác định các cột bạn muốn truy vấn và thứ tự bạn muốn sắp xếp chúng trong bảng tính mới của mình. Nếu không chỉ định thứ tự, dữ liệu sẽ được trả về “như thế” trong một bảng tính nguồn.
Bạn có thể sử dụng các cột định danh (những chữ cái nằm trên đầu mỗi cột trong một bảng tính), ví dụ: SELECT A, B, hoặc tham chiếu đến các cột như Col1, Col2 và so on theo thứ tự số. Bạn cũng có thể tham chiếu đến kết quả của các toán tử số học, các hàm đơn vị hoặc các hàm tổng hợp như các thành phần sắp xếp trong mệnh đề này.
Lưu ý: Nếu bạn dự định nhúng QUERY trong các công thức phức tạp hơn, chúng tôi khuyên bạn nên sử dụng cột Col1, Col2, v.v. trong thứ tự số. Nếu bạn chọn tùy chọn này, sau đó đối số dữ liệu của cú pháp tổng quát Query phải được đặt trong dấu ngoặc nhọn.
Ví dụ về chọn tất cả các cột
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select *")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select *"– chọn tất cả thông tin trong phạm vi dữ liệu đã được đề cập ở trên
Lưu ý Đối số tiêu đề cho phép bạn xác định số dòng tiêu đề để trả về. Nếu bạn bỏ qua phần tiêu đề (headers), dữ liệu trả về sẽ bao gồm dòng tiêu đề. Để xóa tiêu đề, hãy nhập “0” vào công thức Query của bạn như sau:
query('data from Airtable'!A:I,"select *", 0)
Cùng một công việc có thể được thực hiện thông qua Coupler.io, nó lấy tất cả các dữ liệu từ một bảng hoặc bảng tính khác vào tài liệu hiện tại của bạn. Hãy xem bài viết How to Reference another Spreadsheet để biết cách cấu hình kết nối này.
Ví dụ về chọn một hoặc nhiều cột
Nếu bạn muốn lấy một số cột cụ thể hoặc nhiều cột cụ thể, bạn phải xác định chúng bằng cột định danh như sau:
query('data from Airtable'!A:L,"select C, E, I")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select C, E, I"– lấy tất cả dữ liệu từ các cột C, E và I
Ví dụ về chọn nhiều bảng
Nếu bạn muốn truy vấn các bảng khác nhau trong Google Sheets, nghĩa là bạn muốn lấy dữ liệu từ nhiều tab của một bảng tính, hãy sử dụng ví dụ dưới đây:
query({'data from Airtable'!A1:L; Sheet1!A1:L; Sheet2!A1:L}, "select * where Col1 is not null")
{'data from Airtable'!A1:L; Sheet1!A1:L; Sheet2!A1:L}– một mảng công thức trong dấu ngoặc nhọn chứa danh sách các tab mà tôi muốn trích xuất dữ liệu từ, được phân tách bằng dấu chấm phẩy."select * where Col1 is not null"– lấy tất cả dữ liệu trong đó nội dung của cột 1 (cột A, ID đơn hàng) không trống. Tiếp theo, nó hiển thị các cột C (tên khách hàng), E (sản phẩm) và I (tổng số tiền) cho các dòng đã được lọc.
Lưu ý: Nếu bạn muốn truy vấn dữ liệu từ một bảng tính khác, tôi khuyên bạn nên sử dụng một kết hợp của QUERY và IMPORTRANGE.
#2: Google Sheets Query: WHERE
Người dùng sử dụng WHERE khi họ cần trích xuất các dòng cụ thể từ các cột họ đã xác định sẵn trong mệnh đề SELECT, đáp ứng một hoặc nhiều điều kiện.
Để so sánh các giá trị giữa các dòng, bạn cần biết những toán tử logic cơ bản đi kèm với mệnh đề WHERE này.
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select C, E, I WHERE I>=40")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select C, E, I WHERE I>=40"– lấy dữ liệu từ cột C, E, I, trong đó giá trị của cột I (giá tổng) lớn hơn hoặc bằng 40.
Lưu ý: Nếu bạn muốn nói rằng ô trống hoặc nội dung của nó bằng 0 – hãy sử dụng toán tử is null, và nếu bạn muốn chọn các dòng không trống, nhập is not null.
Ví dụ về nhiều điều kiện kết hợp WHERE
Bạn có thể kết hợp nhiều điều kiện bằng cách sử dụng and, or và not trong mệnh đề WHERE của truy vấn như sau:
query('data from Airtable'!A:L,"select C, E, I WHERE I>=40 and not E='Denver sandwich'")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select C, E, I WHERE I>=40 and not E='Denver sandwich'"– lấy dữ liệu từ các cột C, E và I, trong đó giá trị của cột I (giá tổng) lớn hơn hoặc bằng 40 và chuỗi cột E (sản phẩm) không chứa “Denver sandwich”.
Ví dụ về các toán tử nâng cao trong WHERE
Sử dụng các toán tử so sánh nâng cao này để thực hiện các truy vấn phức tạp hơn:
Đối với trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select C, E, I WHERE E starts with 'C' and C like 'K%'")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select C, E, I WHERE E starts with 'C' and C like 'K%'"– lấy dữ liệu từ các cột C, E và I, trong đó giá trị của cột E (sản phẩm) bắt đầu bằng chữ cái “C” và giá trị cột C (tên khách hàng) bắt đầu bằng chữ cái “K”.
Ví dụ về khớp chuỗi trong hàm QUERY
Toán tử matches cho phép bạn khớp các mẫu phức tạp bằng cách sử dụng biểu thức chính quy và sử dụng các tiêu chí tìm kiếm phức tạp trong các truy vấn của mình.
query('data from Airtable'!A:L,"select C, E, I WHERE E matches 'Steak sandwich'")
'data from Airtable'!A:I– phạm vi dữ liệu truy vấn"select C, E, I WHERE E matches 'Steak sandwich'"– lọc bảng dữ liệu chỉ trả về các hàng mà cột E (sản phẩm) khớp chính xác với biểu thức “Steak sandwich”. Nó sau đó hiển thị các cột C (tên khách hàng), E (sản phẩm) và I (giá tổng) cho các hàng đã lọc.
Hoạt động với các khớp mục tiêu
Nếu bạn muốn lọc dữ liệu dựa trên việc khớp mục tiêu, hãy sử dụng các biểu thức chính quy (RegEx) trong công thức.
query('data from Airtable'!A:L,"select C, E, I WHERE E matches '.*sandwich.*'", 1)
'data from Airtable'!A:I– phạm vi dữ liệu truy vấn"select C, E, I WHERE E matches '.*sandwich.*'"– lọc bảng dữ liệu chỉ trả về các hàng mà cột E (sản phẩm) khớp một phần với biểu thức “sandwich”. Sử dụng biểu thức chính quy như.*trước và sau “sandwich” để khớp với bất kỳ ký tự nào xuất hiện trước hoặc sau “sandwich”.
#3: Google Sheets Query: GROUP BY
Mệnh đề GROUP BY được sử dụng để nhóm các giá trị trong phạm vi dữ liệu đã chọn dựa trên một điều kiện nào đó.
Lưu ý: Các cột mà bạn đề cập trong mệnh đề SELECT phải có mặt trong mệnh đề GROUP BY hoặc là một phần của hàm tổng hợp (ví dụ: trung bình, đếm, tối đa, tối thiểu, tổng).
Ví dụ về GROUP BY với một cột: Google Sheets Query SUM
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select C, sum(I) Group by C")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select C, sum(I) Group by C"– công thức tính tổng các mua hàng (cột I) và nhóm chúng theo tên khách hàng (cột C).
Ví dụ về GROUP BY với nhiều cột trong Google Sheets Query
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select C, H, sum(I) Group by C,H")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select C, H, sum(I) Group by C,H"– công thức lấy dữ liệu từ cột C và H, tính tổng các mua hàng (cột I) và nhóm dữ liệu theo tên khách hàng (cột C) và thuộc tính (cột H).
#4: Google Sheets Query: PIVOT
Bằng cách sử dụng mệnh đề PIVOT, bạn có thể chuyển đổi các hàng thành cột và ngược lại, cũng như tóm tắt, biến đổi và nhóm dữ liệu theo trường.
Lưu ý: Các cột mà bạn đề cập trong mệnh đề SELECT phải có mặt trong mệnh đề GROUP BY hoặc là một phần của hàm tổng hợp (ví dụ: trung bình, đếm, tối đa, tối thiểu, tổng).
Ví dụ về PIVOT mà không có GROUP BY
Nếu các hàng của cột chuyển đổi chứa các giá trị giống nhau, mệnh đề PIVOT sẽ tổng hợp chúng. Vì vậy, nếu bạn không sử dụng GROUP BY làm phần của mệnh đề PIVOT, bạn sẽ có một bảng có một hàng duy nhất.
query('data from Airtable'!A:L,"select sum(G) Pivot E")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select sum(G) Pivot E"– công thức tính tổng giá của tất cả các loại hamburger đã bán (cột G) và nhóm chúng theo sản phẩm (cột E).
Ví dụ về PIVOT với GROUP BY
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select C, sum(G) Group BY C Pivot E",1)
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select C, sum(G) Group BY C Pivot E"– công thức trả về một bảng PIVOT có tên hamburger (cột E) trong tiêu đề hàng và danh sách khách hàng (cột C) như cột chính, cho thấy khách hàng đã mua hamburger nào và trả bao nhiêu tiền (cột G).
#5: Google Sheets Query: ORDER BY (thứ tự tăng dần hoặc giảm dần)
Trong Google Sheets, bạn có thể sắp xếp dữ liệu bằng cách sử dụng các hàm khác nhau, bao gồm SORT, SORTN hoặc QUERY. Trong hàm QUERY của Google Sheets, bạn có thể sắp xếp dữ liệu trong các cột theo thứ tự tăng dần (ASC) hoặc giảm dần (DESC) bằng cách sử dụng mệnh đề ORDER BY.
Các thành phần cần sắp xếp trong mệnh đề ORDER BY có thể là các định danh cột hoặc kết quả của các phép toán số học, các hàm đơn vị hoặc các hàm tổng hợp.
Ví dụ về sắp xếp theo thứ tự tăng dần với ORDER BY
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select * where A is not null order by A")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select * where A is not null order by A"– công thức trả về tất cả dữ liệu và sắp xếp theo ID đơn hàng (cột A) theo thứ tự tăng dần.
Lưu ý: Rất quan trọng để thêm is not null vào mệnh đề của bạn để đảm bảo đầu ra không xem xét ô trống và hiển thị tất cả các ô trong bảng của bạn.
Ví dụ về sắp xếp theo thứ tự giảm dần với ORDER BY
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:I,"select * order by A DESC")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select * order by A DESC"– công thức trả về tất cả dữ liệu và sắp xếp theo ID đơn hàng (cột A) theo thứ tự giảm dần.
Ví dụ về sắp xếp theo ngày với ORDER BY
Để sắp xếp dữ liệu theo ngày, bạn có thể sử dụng mệnh đề ORDER BY kết hợp với ngày.
QUERY('data from Airtable'!A1:L21,"ORDER BY B",1)
'data from Airtable'!A:I– phạm vi dữ liệu để truy vấn"ORDER BY B"– công thức để sắp xếp toàn bộ dữ liệu dựa trên giá trị của cột B (ngày đặt hàng) theo thứ tự tăng dần.
#6: Google Sheets Query: LIMIT (+ ví dụ công thức)
Mệnh đề LIMIT giảm số dòng được trích xuất từ bảng khác.
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select * Limit 5")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select * Limit 5"– công thức trả về tất cả dữ liệu và giới hạn kết quả trả về thành 5 dòng đầu tiên + tiêu đề.
Ví dụ về sử dụng mệnh đề OFFSET trong công thức QUERY
Bằng cách sử dụng mệnh đề này, bạn có thể yêu cầu Google Sheets bỏ qua một số hàng được xác định trước trong bảng tính nguồn dữ liệu của bạn.
Ví dụ chỉ về việc sử dụng mệnh đề OFFSET
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select * Offset 10")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select * Offset 10"– công thức trả về tất cả dữ liệu và bỏ qua 10 dòng đầu tiên, không bao gồm tiêu đề.
Ví dụ về mệnh đề OFFSET kết hợp với mệnh đề LIMIT
Nếu chỉ định OFFSET kết hợp với mệnh đề LIMIT, mặc dù nó đứng sau LIMIT trong cú pháp, OFFSET sẽ được áp dụng trước.
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select * Limit 5 Offset 10")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select * Limit 5 Offset 10"– công thức trả về tất cả dữ liệu, bỏ qua 10 dòng đầu tiên và giới hạn kết quả thành 5 dòng, không bao gồm tiêu đề.
#8: Google Sheets Query: LABEL (+ ví dụ công thức)
Mệnh đề LABEL cho phép bạn gán tên cho một trường tiêu đề một hoặc nhiều cột. Tuy nhiên, bạn sẽ không thể sử dụng nó thay cho định danh cột trong một truy vấn.
Bạn có thể sử dụng các định danh cột hoặc kết quả của các phép toán số học, các hàm đơn vị hoặc các hàm tổng hợp như các thành phần trong mệnh đề này.
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:L,"select * label C 'customer', E 'Burger', I 'Total paid'")
'data from Airtable'!A:L– phạm vi dữ liệu để truy vấn"select * label C 'customer', E 'Burger', I 'Total paid'"– công thức trả về tất cả dữ liệu và gán tên mới cho cột C, E và I.
#9: Google Sheets Query: FORMAT
Người dùng sử dụng mệnh đề FORMAT để định dạng giá trị NUMBER, DATE, TIME, TIMEOFDATE và DATETIME cho một hoặc nhiều cột.
Trong trường hợp của chúng tôi, công thức sẽ là như sau:
query('data from Airtable'!A:K,"select B, G, I, J label J 'Hour' format B 'dd-mmm-yyyy', G '##.00', I '##.000', J 'HH'")
'data from Airtable'!A:K– phạm vi dữ liệu để truy vấn"select B, G, I, J label J 'Hour' format B 'dd-mmm-yyyy', G '##.00', I '##.000', J 'HH'"– công thức trả về các cột B, G, I và J, định dạng ngày trong cột B, số trong các cột G và I, và giờ trong cột J, đồng thời cũng thay đổi nhãn của nó thành “Hour”.
