Tôi đề nghị gắn cờ các hàng quan tâm trong Form Responses 1
, nói với trong H1:
=ArrayFormula[countif[Sheet2!A:A,C1:C]]
và sau đó lựa chọn dựa trên các cờ, giả sử với:
=QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
Hoặc nếu bạn không muốn những lá cờ xuất hiện:
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
Chạy Truy vấn ngôn ngữ API trực quan hóa Google trên dữ liệu. Mỗi cột của Trong trường hợp các loại dữ liệu hỗn hợp trong một cột duy nhất, kiểu dữ liệu đa số xác định loại dữ liệu của cột cho mục đích truy vấn. Các loại dữ liệu thiểu số được coi là giá trị null. Giá trị cho Xem //developers.google.com/chart/interactive/docs/queryl Language để biết thêm chi tiết về ngôn ngữ truy vấn.Sử dụng mẫu
QUERY[A2:E6,"select avg[A] pivot B"]
QUERY[A2:E6,F2,FALSE]
Cú pháp
QUERY[data, query, [headers]]
data
- Phạm vi của các ô để thực hiện truy vấn trên.data
chỉ có thể giữ boolean, số [bao gồm các loại ngày/thời gian] hoặc giá trị chuỗi.query
- Truy vấn để thực hiện, được viết bằng ngôn ngữ truy vấn API trực quan hóa Google.query
phải được đặt trong các dấu ngoặc kép hoặc là một tham chiếu đến một ô chứa văn bản thích hợp.
1 - [Tùy chọn] - Số lượng hàng tiêu đề ở đầu =QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
data
. Nếu bị bỏ qua hoặc đặt thành
3, giá trị được đoán dựa trên nội dung của =QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
data
.[ OPTIONAL ] - The number of header rows at the top of data
. If omitted or set to
3, the value is guessed based on the content of =QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
data
.
Ví dụ
Tạo một bản sao
Lưu ý: Mỗi ví dụ nằm trong tab của riêng mình.: Each example is in its own tab.
Dữ liệu mẫu
Chọn & ở đâu
Trả về các hàng phù hợp với điều kiện được chỉ định bằng cách sử dụng các mệnh đề
=QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
5 và =QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
6.Nhóm theo
Tổng hợp các giá trị
=QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
7 trên các hàng sử dụng các mệnh đề =QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
5 và =QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
9.Trục
Biến đổi các giá trị riêng biệt trong các cột thành các cột mới.
Đặt bởi
Tổng hợp các giá trị
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
0 trên các hàng và sắp xếp theo giá trị tối đa là =QUERY['Form Responses 1'!$1:$1000,"select * where H=1"]
7.Tiêu đề
Chỉ định số lượng hàng tiêu đề trong phạm vi đầu vào, cho phép chuyển đổi đầu vào phạm vi hàng đa tiêu đề được chuyển đổi thành một đầu vào tiêu đề hàng duy nhất.
Là hữu ích không?
Làm thế nào chúng ta có thể cải thiện nó?
Chức năng truy vấn Google Sheets là chức năng mạnh mẽ và linh hoạt nhất trong Google Sheets.most powerful and versatile function in Google Sheets.
Nó cho phép bạn sử dụng các lệnh dữ liệu để thao tác dữ liệu của bạn trong Google Sheets và nó cực kỳ linh hoạt và mạnh mẽ.it’s incredibly versatile and powerful.
Chức năng duy nhất này thực hiện công việc của nhiều chức năng khác và có thể sao chép hầu hết các chức năng của các bảng trục.
Video này là bài học 14 trên 30 từ khóa học Google Sheets miễn phí của tôi: Thử thách công thức nâng cao 30 ngàyAdvanced Formulas 30 Day Challenge
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
2Phải mất 3 đối số:
- Phạm vi dữ liệu bạn muốn phân tích
- Truy vấn bạn muốn chạy, được đặt trong các trích dẫn
- một số tùy chọn để nói có bao nhiêu hàng tiêu đề trong dữ liệu của bạn
Ở đây, một chức năng truy vấn ví dụ:
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
3Phạm vi dữ liệu trong ví dụ này là A1: D234
Câu lệnh truy vấn là chuỗi bên trong các trích dẫn, màu xanh lá cây. Trong trường hợp này, nó cho biết chức năng chọn các cột B và D từ dữ liệu.
Đối số thứ ba là số 1, cho biết hàm rằng dữ liệu gốc có một hàng tiêu đề duy nhất. Đối số này là tùy chọn và, nếu bị bỏ qua, sẽ được xác định tự động bằng các tờ.
Nó là một trong những chức năng chỉ dành cho Google không có sẵn trong các công cụ bảng tính khác.
Ghi chú chức năng truy vấn
Các từ khóa không nhạy cảm với trường hợp, vì vậy bạn có thể viết ra chọn chọn hoặc chọn chọn và cả hai công việc.
Tuy nhiên, các chữ cái cột phải là chữ hoa: A, B, C, v.v. Nếu không, bạn sẽ gặp lỗi.
Các từ khóa phải xuất hiện theo thứ tự này [tất nhiên, bạn không phải sử dụng tất cả]:
- lựa chọn
- ở đâu
- nhóm theo
- đặt bởi
- giới hạn
- nhãn mác
Bạn sẽ thấy các ví dụ về tất cả các từ khóa dưới đây.
Có một vài từ khóa khác nhưng chúng ít phổ biến hơn nhiều. Xem danh sách đầy đủ ở đây.
Mẫu chức năng truy vấn Google Sheets
Nhấn vào đây để mở một bản sao chỉ xem >>
Vui lòng tạo một bản sao: Tệp> Tạo một bản sao
Nếu bạn có thể truy cập vào mẫu, đó có thể là do tổ chức của bạn cài đặt không gian làm việc của Google. Nếu bạn nhấp chuột phải vào liên kết và mở nó trong cửa sổ ẩn danh, bạn sẽ có thể nhìn thấy nó.
Các ví dụ về chức năng truy vấn của Google Sheets
Nếu bạn muốn theo dõi cùng với các giải pháp, vui lòng tạo một bản sao của mẫu Google Sheet ở trên.
Đây là những gì dữ liệu bắt đầu của chúng tôi trông như thế nào:
Trong hướng dẫn này, tôi đã sử dụng một phạm vi được đặt tên để xác định dữ liệu, giúp sử dụng dễ dàng hơn và sạch hơn nhiều trong chức năng truy vấn. Hãy thoải mái sử dụng phạm vi được đặt tên là các quốc gia trên mạng cũng vậy, đã tồn tại trong mẫu.named range to identify the data, which makes it much easier and cleaner to use in the QUERY function. Feel free to use the named range “countries” too, which already exists in the template.
Nếu bạn mới sử dụng phạm vi được đặt tên, thì đây là cách bạn tạo ra chúng:
Chọn phạm vi dữ liệu của bạn và chuyển đến menu:
Dữ liệu> Phạm vi được đặt tên
Một khung mới sẽ hiển thị ở phía bên phải của bảng tính của bạn. Trong hộp đầu vào đầu tiên, hãy nhập tên cho bảng dữ liệu của bạn để bạn có thể tham khảo dễ dàng.
Chọn tất cả
Tuyên bố
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
4 lấy tất cả các cột từ bảng dữ liệu của chúng tôi.Ở phía bên phải của bảng [Tôi đã sử dụng ô G1] nhập chức năng truy vấn Google Sheets sau đây bằng cách sử dụng ký hiệu phạm vi được đặt tên:
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
5Ghi chú: Nếu bạn không muốn sử dụng phạm vi có tên thì đó không phải là vấn đề. Công thức truy vấn của bạn sẽ trông như thế này: if you don’t want to use named ranges then that’s no problem. Your QUERY formula will look like this:
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
6Trong phần còn lại của bài viết này, tôi đã sử dụng phạm vi được đặt tên là các quốc gia, nhưng cảm thấy thoải mái khi tiếp tục sử dụng tham chiếu phạm vi thông thường A1: D234 ở vị trí của nó.
Đầu ra từ truy vấn này là bảng đầy đủ của chúng tôi một lần nữa, bởi vì
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
4 lấy tất cả các cột từ bảng quốc gia:Wow, bạn đi! Bạn đã viết truy vấn đầu tiên của bạn! Vỗ nhẹ vào lưng.
Chọn các cột cụ thể
Điều gì sẽ xảy ra nếu chúng ta không muốn chọn mọi cột, nhưng chỉ có một số nhất định?
Sửa đổi chức năng truy vấn Google Sheets của bạn để đọc:
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
8Lần này, chúng tôi chỉ chọn các cột B và D từ bộ dữ liệu gốc, vì vậy đầu ra của chúng tôi sẽ trông như thế này:
Lưu ý quan trọng
Hãy nhớ rằng, chức năng truy vấn của chúng tôi là trong ô G1, vì vậy đầu ra sẽ nằm trong các cột G, H, I, v.v.
B và D bên trong câu lệnh Chọn Truy vấn tham khảo các tài liệu tham khảo cột trong dữ liệu gốc.
Từ khóa ở đâu
Từ khóa chỉ định một điều kiện phải được thỏa mãn. Nó lọc dữ liệu của chúng tôi. Nó đến sau từ khóa chọn.
Sửa đổi chức năng truy vấn Google Sheets của bạn để chỉ chọn các quốc gia có dân số lớn hơn 100 triệu:
=QUERY['Form Responses 1'!$1:$1000,"select A,B,C,D,E,F,G where H=1"]
9Bảng đầu ra của chúng tôi là:
Hãy cùng xem một ví dụ khác nơi từ khóa, lần này chỉ chọn các nước châu Âu. Sửa đổi công thức của bạn thành:
Form Responses 1
0
Lưu ý làm thế nào có những trích dẫn duy nhất xung quanh từ ‘Châu Âu. Tương phản điều này với ví dụ số trước không yêu cầu trích dẫn đơn lẻ xung quanh số.
Bây giờ bảng đầu ra là:
Đặt hàng theo từ khóa
Thứ tự theo từ khóa sắp xếp dữ liệu của chúng tôi. Chúng ta có thể chỉ định [các] cột và hướng [tăng dần hoặc giảm dần]. Nó đến sau khi chọn và nơi từ khóa.
Hãy để sắp xếp dữ liệu của chúng tôi theo dân số từ nhỏ nhất đến lớn nhất. Sửa đổi công thức của bạn để thêm thứ tự sau theo từ khóa, chỉ định hướng tăng dần với ASC:
Form Responses 1
1
Bảng đầu ra:
Sửa đổi công thức truy vấn của bạn để sắp xếp dữ liệu theo quốc gia theo thứ tự giảm dần, z - a:
Form Responses 1
2
Bảng đầu ra:
Giới hạn từ khóa
Từ khóa giới hạn hạn chế số lượng kết quả được trả về. Nó đến sau khi chọn, ở đâu và đặt hàng theo từ khóa.
Hãy để thêm một từ khóa giới hạn vào công thức của chúng tôi và chỉ trả về 10 kết quả:
Form Responses 1
3
Điều này bây giờ chỉ trả về 10 kết quả từ dữ liệu của chúng tôi:
Các chức năng số học
Chúng ta có thể thực hiện các hoạt động toán học tiêu chuẩn trên các cột số.
Vì vậy, hãy để tìm ra bao nhiêu phần trăm của tổng dân số thế giới [7,16 tỷ] mỗi quốc gia chiếm.
Chúng tôi sẽ phân chia cột dân số cho tổng số [7.162.119.434] và nhân với 100 để tính toán tỷ lệ phần trăm. Vì vậy, hãy sửa đổi công thức của chúng tôi để đọc:
Form Responses 1
4
Tôi đã chia các giá trị trong cột D cho tổng dân số [bên trong dấu ngoặc đơn], sau đó nhân với 100 để có được tỷ lệ phần trăm.
Bảng đầu ra lần này là:
Lưu ý - Tôi đã áp dụng định dạng cho cột đầu ra trong Google Sheets chỉ hiển thị 2 vị trí thập phân.
Nhãn từ khóa
Việc tiêu đề cho cột số học khá xấu xí phải không? Chà, chúng ta có thể đổi tên nó bằng cách sử dụng từ khóa nhãn, xuất hiện ở cuối câu lệnh truy vấn. Thử thứ này đi:
Form Responses 1
5
Form Responses 1
6
Hàm tổng hợp
Chúng tôi có thể sử dụng các chức năng khác trong các tính toán của chúng tôi, ví dụ, tối đa, tối đa và trung bình.
Để tính toán các quần thể tối đa, tối đa và trung bình trong bộ dữ liệu quốc gia của bạn, hãy sử dụng các hàm tổng hợp trong truy vấn của bạn như sau:
Form Responses 1
7
Đầu ra trả về ba giá trị - các quần thể tối đa, tối thiểu và trung bình của bộ dữ liệu, như sau:
Nhóm theo từ khóa
Ok, hãy hít thở sâu. Đây là khái niệm thách thức nhất để hiểu. Tuy nhiên, nếu bạn đã từng sử dụng các bảng xoay trong Google Sheets [hoặc Excel] thì bạn sẽ ổn với điều này.
Nhóm theo từ khóa được sử dụng với các hàm tổng hợp để tóm tắt dữ liệu thành các nhóm như một bảng trục.
Hãy để tóm tắt bởi lục địa và đếm xem có bao nhiêu quốc gia mỗi lục địa. Thay đổi công thức truy vấn của bạn để bao gồm một nhóm theo từ khóa và sử dụng chức năng tổng hợp đếm để đếm số lượng quốc gia, như sau:
Form Responses 1
8
Lưu ý, mọi cột trong câu lệnh CHỌN [nghĩa là trước khi nhóm] phải được tổng hợp [ví dụ: tính, tối thiểu, tối đa] hoặc xuất hiện sau nhóm theo từ khóa [ví dụ: cột C trong trường hợp này].
Đầu ra cho truy vấn này là:
Hãy cùng xem một ví dụ phức tạp hơn, kết hợp nhiều loại từ khóa khác nhau. Sửa đổi công thức để đọc:
Form Responses 1
9
Điều này có thể dễ đọc hơn được chia ra nhiều dòng:
QUERY[A2:E6,"select avg[A] pivot B"]
0
Điều này tóm tắt dữ liệu của chúng tôi cho mỗi lục địa, loại cao nhất đến dân số trung bình thấp nhất và cuối cùng giới hạn kết quả chỉ trong top 3.
Đầu ra của truy vấn này là:
Nâng cao các kỹ thuật chức năng truy vấn Google Sheets
Cách thêm tổng số hàng vào các công thức truy vấn của bạn
Cách sử dụng ngày làm bộ lọc trong các công thức truy vấn của bạn
Có thêm 4 từ khóa mà thiên đường đã được đề cập trong bài viết này: trục, bù, định dạng và tùy chọn.
Ngoài ra, có nhiều chức năng thao tác dữ liệu có sẵn hơn so với chúng tôi đã thảo luận ở trên. Ví dụ, có một loạt các chức năng vô hướng để làm việc với ngày.
Giả sử bạn có một cột ngày trong cột A của bộ dữ liệu của bạn và bạn muốn tóm tắt dữ liệu của mình theo năm. Bạn có thể cuộn nó lên bằng cách sử dụng chức năng vô hướng năm:
QUERY[A2:E6,"select avg[A] pivot B"]
1
Đối với các kỹ thuật nâng cao hơn với chức năng truy vấn, hãy xem bài học này từ khóa học 30 nâng cao:
Video này là bài học 15 trên 30 từ khóa học Google Sheets miễn phí của tôi: Thử thách công thức nâng cao 30 ngày.Advanced Formulas 30 Day Challenge.
Các nguồn lực khác
Hàm truy vấn được đề cập trong các ngày 14 và 15 của khóa học công thức nâng cao miễn phí của tôi cho Google Sheets: Tìm hiểu 30 công thức nâng cao trong 30 ngày
Tài liệu chính thức của Google cho hàm truy vấn [].
Tài liệu chính thức cho ngôn ngữ truy vấn API trực quan hóa Google Google.
Tìm kiếm một chuyên gia Google Sheets để giúp dự án tiếp theo của bạn? Lên lịch tư vấn ngay hôm nay với một chuyên gia Google Sheets được Ben chấp thuận.