Truy vấn Google Trang tính phù hợp

Chức năng Truy vấn của Google Trang tính thay thế rất nhiều chức năng bảng tính khác, điều này thậm chí còn không vui chút nào – LỌC, TRUNG BÌNH và TỔNG tất cả đều biến mất khỏi cửa sổ khi nó đi vào hình ảnh

Tuy nhiên, có một vài sắc thái đối với các truy vấn, vì vậy hãy thực hiện từng bước. Bài đăng này chứa tám video, với các ghi chú của vách đá được nhúng bên dưới mỗi video

Hãy đi sâu vào

Nhân tiện…nếu bạn đang muốn chuyển từ Trang tính sang BigQuery, hãy xem Công thức khởi động nhanh BigQuery của chúng tôi tại truy vấn. công thức nấu ăn

1. Khái niệm cơ bản về Truy vấn Google Trang tính

QUERY kết hợp tất cả khả năng của các hàm số học (SUM, COUNT, AVERAGE) với khả năng lọc của một hàm như FILTER

Trong ví dụ này, chúng ta sẽ xem qua một số ví dụ về QUERY để phân tích mẫu dữ liệu Twitter

Cấu trúc cơ bản của một QUERY là nhập

• Phạm vi dữ liệu - 'tab dữ liệu'. A. C'
• Và một chuỗi truy vấn – “chọn A trong đó C = ‘twitter’”

Chúng tôi kết hợp chúng như vậy

=query( ‘tab dữ liệu’. A. C, “chọn A trong đó C = ‘twitter’”)

Ví dụ hàm QUERY trả lời câu hỏi

Q. Số lượng tin nhắn lại trung bình từ mẫu là bao nhiêu?

A. =truy vấn(D1. G, “chọn trung bình(G)”)

Q. Số lượng tin nhắn lại trung bình trên Twitter có chứa @mention nhưng không phải là tin nhắn lại là bao nhiêu?

A. =truy vấn(D1. G, “chọn avg(G) trong đó E chứa ‘@’ chứ không phải E chứa ‘RT’”)

Q. Mỗi người dùng trong danh sách đã nhận được bao nhiêu tin nhắn lại?

A. =truy vấn(D1. G, “chọn F, tổng (G) trong đó E. = ‘’ nhóm theo tổng nhãn F(G) ‘tổng số lượt retweet’”)

Tại sao các truy vấn trong Trang tính lại phức tạp đến vậy – chẳng phải điều này sẽ dễ dàng hơn sao?

Khi tôi lần đầu tiên bắt đầu viết so sánh ngày trong các truy vấn của Google (lấy dữ liệu trước/sau một ngày nhất định), thật khó để tìm ra cú pháp chính xác

Thật không may, nó không thực sự được đề cập ở bất cứ đâu trong tài liệu Google Trang tính, vì vậy tôi phải tự mình tìm hiểu

Bạn phải làm đúng hai việc

1. Viết chuỗi 'ngày' trước ngày thực tế để tuyên bố ý định của bạn
2. Nhập ngày của bạn ở định dạng 'yyyy-mm-dd'

Nếu bạn muốn so sánh động một ngày từ một ô khác, thì đó là một câu chuyện khác. Tôi muốn giữ các phạm vi ngày (7 ngày qua, 30 ngày qua, từ đầu năm đến nay, v.v.), sau đó tham chiếu chúng trong một truy vấn như thế này

Chú ý cú pháp

1. Để kết hợp chuỗi với một giá trị bên ngoài, hãy đóng dấu ngoặc kép của truy vấn và sử dụng '&'
2. Sử dụng hàm TEXT để chuyển đổi ngày thành định dạng 'yyyy-mm-dd' chính xác

Một khi bạn hiểu rõ về nó, bạn sẽ không bao giờ nghĩ về nó nữa. Tuy nhiên, nếu bạn quên cú pháp, thì CIFL cheat sheet sẽ hỗ trợ bạn

3. Gỡ lỗi các lỗi Truy vấn phổ biến

Về gỡ lỗi các lỗi Truy vấn phổ biến

Lỗi không bao giờ thú vị, nhưng thông báo lỗi của Trang tính sẽ luôn chỉ cho bạn đi đúng hướng

#REF

1) Kết quả mảng không được mở rộng vì nó sẽ ghi đè lên dữ liệu trong D6

Có dữ liệu trong ô D6 chặn mở rộng truy vấn của bạn – hãy xóa nó

2) Tên trang tính chưa được giải quyết…

Trang tính mà bạn đang tham chiếu trong truy vấn của mình không tồn tại – có thể do lỗi đánh máy hoặc đã xóa nó. (

#GIÁ TRỊ

3) PARSE_ERROR

Gây ra bởi một chuỗi truy vấn sai thông tin – nó sẽ luôn cho bạn biết chính xác nơi cần tìm

Có nghĩa là có một lỗi cú pháp ở đâu đó xung quanh mệnh đề 'where' của tôi - một dấu phẩy treo hoặc một lỗi đánh máy

=truy vấn(tweet. A1. D,‘select C, sum(D), where D >= 0 group by C limit 2’,0)

4) LABEL_COL_NOT_IN_SELECT

Nguyên nhân là do sự không khớp giữa các cột trong mệnh đề 'chọn' và mệnh đề 'nhãn' của bạn

=truy vấn(tweet. A1. D,‘select C, sum(D) where D >= 0 group by C limit 2 label sum© “,0)

Ở đây sum(D) bị dán nhãn sai thành sum©

5) KHÔNG_CỘT

Khi bạn đang chọn một cột nằm ngoài phạm vi của mình

=truy vấn(tweet. A1. C,‘select C, sum(D) where D >= 0 group by C limit 2’,0)

Đã khắc phục bằng cách mở rộng phạm vi dữ liệu của bạn để bao gồm cột (tweet. A1. D trong trường hợp này)

6) THÊM_COL_TO_GROUP_BY_OR_AGG

Bật lên khi bạn đang chọn một cột (B trong trường hợp này) không có trong mệnh đề 'nhóm theo' của bạn

=truy vấn(tweet. A1. D,‘select C, B, sum(D) where D >= 0 group by C limit 2’,0)

Nếu bạn không tính tổng hoặc tổng hợp cột B, thì bạn phải nhóm theo cột đó

#N/A

7) Truy vấn hoàn thành với đầu ra trống

Đơn giản có nghĩa là không có kết quả nào từ truy vấn của bạn như hiện được viết – có khả năng là do điều kiện quá hạn chế

=truy vấn(tweet. A2. D,‘chọn C, D trong đó D = 100 giới hạn 2’,0)

Hãy thử đơn giản hóa truy vấn của bạn để ít hạn chế hơn (xóa D = 100 trong trường hợp này)

Khi truy vấn nhiều Google Sheets, với importrange + {}

Trong mỗi cuộc đời, sẽ có lúc bạn muốn kết hợp hai phạm vi dữ liệu trong một truy vấn Google Trang tính

Hãy nói về cách kết hợp các phạm vi dữ liệu từ trong cùng một bảng tính (hoặc từ hai trang tính khác nhau), để chạy một truy vấn – làm việc thông qua các ví dụ sử dụng dữ liệu Twitter mẫu

Để truy vấn một phạm vi kết hợp từ trong cùng một trang tính

1. Đính kèm cả hai phạm vi trong { } để kết hợp chúng và phân tách bằng dấu ';' để xếp chồng lên nhau
2. Thay vì tham chiếu các cột bằng chữ cái của chúng (A, B), hãy gọi chúng là Col1, Col2 tùy thuộc vào thứ tự của chúng

Để truy vấn một phạm vi kết hợp từ các trang tính khác nhau

• Trước tiên hãy chạy chức năng nhập phạm vi trên Trang tính bên ngoài mà bạn muốn lấy từ đó. Phần tử đầu tiên là ID bảng tính, được chứa trong URL của trang tính, giữa /d/ và /edit. (tài liệu. Google. com/spreadsheets/d/
  1ekTIZ40VVT9p9kE80wNaiy6Y2xslo4K9KVmmg-Dx654 /chỉnh sửa).

• Bạn sẽ được yêu cầu cho phép truy cập vào trang tính

• Nhúng phạm vi từ hàm nhập phạm vi của bạn trong chính truy vấn đó

Thành thật mà nói, mục tiêu của tôi là không bao giờ phải kết hợp các phạm vi từ bên trong truy vấn (tôi thích thực hiện kiểu tổng hợp đó trước khi viết truy vấn), nhưng đôi khi điều đó là không thể tránh khỏi

5. Truy vấn theo tên cột không phải chữ cái

Điều này sẽ giúp bạn đỡ đau đầu hơn nếu nhiều đồng nghiệp làm việc trong Trang tính của bạn

Truy vấn là đối tượng nhạy cảm – chúng yêu cầu chúng tôi chỉ định chính xác các chữ cái cột ( A, tổng(B) ) hoặc số ( Col1, tổng(Col2) ) mà chúng tôi muốn trả về

Nhưng với cách chúng ta sử dụng Trang tính, các cột đó có thể luôn thay đổi. Mọi người thêm hàng mới, xóa hàng và thường thay đổi mọi thứ xung quanh

Bằng cách lồng hai truy vấn vào với nhau, chúng tôi có thể chứng minh các truy vấn của bạn luôn khớp với cùng tiêu đề cột trong tương lai

1. Truy vấn đầu tiên (bên trong) sử dụng hàm Match để tra cứu số cột của bạn – match('header name', tab. A1. Z1), giả sử tiêu đề của bạn ở hàng đầu tiên. Lưu ý bên dưới rằng chúng tôi sẽ muốn bọc phạm vi của mình { tweets. A2. D } trong dấu ngoặc nhọn, để giải phóng chúng tôi chọn số cột (Col1) thay vì chữ cái (A)
2. Truy vấn thứ hai (bên ngoài) là nơi bạn xây dựng logic lựa chọn thực tế của mình (chọn nhóm Col1, tổng(Col2) theo Col1…), bởi vì trong Truy vấn bên trong, bạn đã lấy ra các cột cụ thể mà mình cần. Bạn có thể mã hóa cứng trong Col1, Col2, v.v. vì bạn luôn biết thứ tự xuất hiện của chúng, bất kể các cột có di chuyển xung quanh trong Trang tính thực của bạn hay không

Đặt tất cả lại với nhau, và nó trông như thế này

Dòng đầu tiên (bên trong truy vấn) tìm tên cột của bạn – truy vấn thực tế mà nó đang chạy chỉ là 'chọn Col3, Col4. ’

Dòng thứ hai (truy vấn bên ngoài) chạy toán học thực tế của bạn và gắn nhãn các cột của bạn. Hãy nhớ rằng các nhãn cột truy vấn sẽ luôn xuất hiện ở cuối câu lệnh và nhìn chung trông giống như câu lệnh CHỌN (chọn Col1, tổng(Col2) ánh xạ ra nhãn Col1 'xử lý', tổng (Col2) 'tổng số tin nhắn lại')

Bạn có thể mất một chút thời gian thử để làm quen với điều này – vui lòng sao chép Bảng tính minh họa được sử dụng trong video để sử dụng lại công thức của chúng tôi

6. Xây dựng Pivot Table với truy vấn

Tuyệt vời để xây dựng báo cáo chuỗi thời gian trong Trang tính

Nếu bạn đang làm việc với dữ liệu chuỗi thời gian trong Trang tính (giống như hầu hết chúng ta làm), thì xoay vòng trong các truy vấn sẽ là động thái yêu thích mới của bạn

Nó cho phép bạn tạo báo cáo đầy đủ trong một Truy vấn, bằng cách nhóm các giá trị ở cột bên trái và chuyển đổi phạm vi ngày (hoặc bất kỳ cột thứ hai nào) sang bên phải

Bạn xoay vòng với một cú pháp rất giống với nhóm – truy vấn này tạo ra kết quả ở trên

=truy vấn(tweet. A1. D,‘select C, sum(D) where D >= 0 group by C pivot A’,0)

Như bạn có thể thấy, nó đơn giản như việc thêm 'trục A' sau mệnh đề 'nhóm theo' trong một truy vấn

Bạn có thể sử dụng danh sách thả xuống để thêm các biến đầu vào trong truy vấn của mình

Khi bạn đang tạo báo cáo trong Google Trang tính, mục tiêu chắc chắn là không khiến những người *đang sử dụng* báo cáo nghĩ

Menu thả xuống rất phù hợp cho việc này vì chúng cho phép bạn trình bày một số tùy chọn hạn chế để báo cáo người dùng

Xác thực dữ liệu trong Trang tính tự động tạo menu thả xuống khi bạn tạo chúng – khá hấp dẫn phải không?

Và trộn giá trị đã chọn từ menu thả xuống của bạn với Truy vấn rất đơn giản

= truy vấn (dữ liệu. A. B, 'chọn A trong đó B = "&' vị trí tab thả xuống'. C2 & “‘, 0)

Chỉ cần một vài lần nhấn phím

1. Gói giá trị thả xuống của bạn trong dấu ngoặc đơn (‘), nếu đó là một chuỗi (không có gì nếu đó là một số)
2. Đóng (và sau đó mở lại) truy vấn của bạn bằng dấu ngoặc kép (“)
3. Sử dụng dấu và (&) để thêm giá trị thả xuống vào chuỗi truy vấn
4. Tham chiếu ô thực tế của giá trị thả xuống ('dữ liệu'. A2) để thêm nó vào chuỗi truy vấn

Đó là nó. Người dùng báo cáo của bạn sẽ cảm ơn bạn

8. Sự khác biệt giữa SQL và Truy vấn

Sự khác biệt giữa Truy vấn Trang tính và SQL là gì?

Nếu bạn đã quen với việc viết các truy vấn SQL, thì ban đầu có thể gặp khó khăn khi làm việc với các truy vấn Google Trang tính (và ngược lại)

Nếu bạn chưa biết bất kỳ câu SQL nào, hãy tìm hiểu Truy vấn trước. Nó sẽ giúp làm việc với cơ sở dữ liệu *dễ dàng hơn nhiều* khi bạn đến đó

Hãy xem qua một số cách mà chúng giống và khác nhau, và cách bạn có thể áp dụng từng cách trong công việc của mình

truy vấn cơ bản

SQL. CHỌN * TỪ bảng WHERE column_name = ‘xyz’ ĐẶT HÀNG BẰNG tên_cột desc GIỚI HẠN 10

trang tính. = truy vấn( ‘tab’. A. D, ‘CHỌN * WHERE A = ‘xyz’ ĐẶT HÀNG THEO A desc GIỚI HẠN 10’)

Cú pháp truy vấn cơ bản gần giống nhau. Truy vấn Google Trang tính sử dụng cùng một câu lệnh SELECT để chọn cột, WHERE / AND / OR để đặt logic, ORDER BY để sắp xếp kết quả và LIMIT để chỉ lấy một số kết quả nhất định (xem danh sách đầy đủ các mệnh đề ngôn ngữ trong Google )

Điểm khác biệt chính là không có câu lệnh 'TỪ' trong truy vấn Trang tính – thay vì tham chiếu một bảng, bạn tham chiếu một dải ô ('tab'. A. D trong ví dụ này)

Ngoài ra, bạn sẽ nhận thấy rằng, thay vì tham chiếu tên cột (như trong SQL), trong truy vấn Trang tính, bạn tham chiếu cột theo chữ cái (A, B, C, v.v.) hoặc theo thứ tự của chúng (Col1, Col2, Col3)

Làm toán trên cột

SQL. CHỌN tên_cột, tổng (tên_cột_khác) TỪ bảng NHÓM THEO tên_cột

trang tính. = truy vấn( ‘tab’. A. D, 'CHỌN A, tổng (B) NHÓM THEO A')

Các hàm tổng hợp avg(), count(), sum(), max(), min() mà bạn thường sử dụng trong SQL có sẵn trong các truy vấn Trang tính, cũng như các hàm cơ bản +, -, *, / số học

Cột ghi nhãn

SQL. CHỌN cột_name NHƯ 'nhãn' TỪ bảng

trang tính. = truy vấn( ‘tab’. A. D, 'CHỌN A, B NHÃN A 'nhãn1', B 'nhãn2' ‘)

Trong SQL, để gắn nhãn cho một cột, bạn chỉ cần thêm 'AS' vào câu lệnh 'SELECT' của mình. column_name AS “nhãn. ”

Trong Trang tính, điều này được thực hiện ở cuối truy vấn, với câu lệnh 'nhãn'

Việc gắn nhãn cột sum(A) là trống sẽ loại bỏ tiêu đề sum() tự động khỏi hiển thị, trả về một số liệu đơn rõ ràng và đẹp mắt

SQL. CHỌN column_name TỪ bảng WHERE date_column > ‘8/22/2016’

trang tính. = truy vấn( ‘tab’. A. D, ‘CHỌN MỘT NƠI C > ngày ‘22-08-2016’ ‘)

Ngày tháng trong các truy vấn của Google rất khó để tìm ra lần đầu tiên, nếu bạn đã quen làm việc với SQL

Không giống như SQL, có thể xử lý nhiều định dạng ngày ('20160826', '8/26/2016', '2016-08-26'), Trang tính chỉ có thể lấy ngày ở định dạng 'yyyy-mm-dd' – vì vậy đôi khi bạn có

Trong Trang tính, bạn cũng phải xác định rằng bạn đang so sánh một ngày bằng cách thêm cụm từ 'ngày' trước chuỗi ngày của bạn

Kết hợp cụm từ mờ

SQL. CHỌN column_name TỪ bảng WHERE column_name THÍCH ‘%phrase%’

trang tính. = truy vấn( ‘tab’. A. D, ‘CHỌN MỘT NƠI CHỨA ‘giá trị’ ‘)

Để kéo văn bản có chứa một giá trị, bạn chỉ cần sử dụng 'chứa', thay vì câu lệnh 'thích' trong SQL. Các truy vấn trang tính có câu lệnh 'thích', nhưng tôi thấy mình sử dụng 'chứa' thường xuyên hơn vì tính đơn giản của nó

Tuy nhiên, một nếp nhăn đã được thêm vào, nếu bạn đang muốn thêm tham số 'không chứa', bạn phải đặt 'không' trước ký tự cột

Tham gia bàn

SQL. Chọn một. tên_cột, b. column_name TỪ table1 a, table2 b WHERE a. tên_cột = b. tên cột dọc

trang tính. Không được hỗ trợ ????

Mối quan tâm lớn nhất của tôi với các truy vấn Trang tính là chúng không hỗ trợ nối hai bảng với nhau bằng một khóa duy nhất. Tôi đã thử mọi cách giải quyết mà tôi có thể nghĩ ra nhưng cho đến nay vẫn chưa tìm ra cách

Nhóm Google Trang tính, nếu bạn đang đọc phần này – hãy trợ giúp

Khớp từ một danh sách các tùy chọn

SQL. CHỌN column_name TỪ bảng WHERE column_name IN (‘match 1’, ‘match 2’, ‘match 3’)

trang tính. = truy vấn( ‘tab’. A. D, ‘CHỌN A Ở ĐÂU A = ‘match1’ HOẶC A = ‘match2’ HOẶC A = ‘match3’ ‘)

Thật không may, trong Google Trang tính, không có cụm từ 'IN' trong SQL, vì vậy bạn phải liệt kê ra các giá trị mà bạn đang tìm kiếm để khớp từng cái một

9. Các truy vấn lồng nhau như SQL

Với một chút cách tiếp cận khó hiểu, bạn có thể tính gần đúng chức năng lồng nhau của SQL trong Trang tính

Như tôi đã đề cập ở trên, việc không thể tham gia truy vấn trong Sheets là một nhược điểm lớn

Giả sử bạn có dữ liệu trong hai tab khác nhau và bạn muốn lấy tất cả các giá trị từ một bảng tồn tại trong bảng kia

tôi đã nghĩ là không thể. tôi đã sai

Đó là một cách khó khăn để bắt chước cách mà các truy vấn SQL có thể lồng vào nhau, nhưng nó hoạt động

Bạn kết hợp công thức mảng và nối để tạo ra một chuỗi dài trong truy vấn của bạn ( OR A = ‘value1’ OR A = ‘value2’, v.v.)

Công thức cuối cùng trông như thế này

= truy vấn (tab1. A. Z, ‘chọn A, tổng (B) trong đó A. = ' và (B = 'blah' ' & nối (công thức mảng (' OR B = “ & tab2. C2. C & “‘)) & ‘) nhóm theo B’,0)

Điều này lấy tất cả các giá trị có trong Tab 2, cột C và tìm kiếm chúng trong cột B của Tab 1

Đó là truy vấn lồng nhau của một người nghèo, nhưng nó hoạt động. )

Cần giúp đỡ?

Đó là rất nhiều truy vấn về Google Trang tính, nhưng tôi hy vọng nó sẽ bắt đầu thu hút bạn

Đảm bảo lấy một bản sao miễn phí của bảng gian lận công thức Trang tính tại đây – nhưng nếu bạn đã làm được đến mức này, thì có lẽ bạn đã sẵn sàng để thành thạo Trang tính bằng cách đầu tư một vài giờ vào khóa học Truy vấn CIFL nâng cao (bao gồm hướng dẫn chi tiết và

Hoặc liên hệ trực tiếp với chúng tôi – đội ngũ chuyên gia tư vấn của chúng tôi luôn sẵn sàng giúp bạn giải quyết các vấn đề HỎI phức tạp nhất của bạn

Có chức năng khớp trong Google Trang tính không?

Tôi có thể sử dụng Vlookup trong truy vấn trong Google Trang tính không?

Chúng tôi có thể sử dụng truy vấn SQL trong Google Trang tính không?