Hướng dẫn google sheets query where in range - truy vấn trang tính của google ở ​​đâu trong phạm vi

Chạy Truy vấn ngôn ngữ API trực quan hóa Google trên dữ liệu.

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.

  • Mỗi cột của 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.

  • 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.

• 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.

  • Giá trị cho 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.

  • Xem https://developers.google.com/chart/interactive/docs/queryl Language để biết thêm chi tiết về ngôn ngữ truy vấn.

• =QUERY('Form Responses 1'!$1:$1000,"select * where H=1")
  

  1 - [Tùy chọn] - Số lượng hàng tiêu đề ở đầu data. Nếu bị bỏ qua hoặc đặt thành

  =QUERY('Form Responses 1'!$1:$1000,"select * where H=1")
  

  3, giá trị được đoán dựa trên nội dung của data.[ OPTIONAL ] - The number of header rows at the top of data. If omitted or set to

  =QUERY('Form Responses 1'!$1:$1000,"select * where H=1")
  

  3, the value is guessed based on the content of 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")

=QUERY('Form Responses 1'!$1:$1000,"select * where H=1")

Nhóm theo

Tổng hợp các giá trị

=QUERY('Form Responses 1'!$1:$1000,"select * where H=1")

=QUERY('Form Responses 1'!$1:$1000,"select * where H=1")

=QUERY('Form Responses 1'!$1:$1000,"select * where H=1")

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") 

=QUERY('Form Responses 1'!$1:$1000,"select * where H=1")

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") 

Phải mất 3 đối số:

1. Phạm vi dữ liệu bạn muốn phân tích
2. Truy vấn bạn muốn chạy, được đặt trong các trích dẫn
3. 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") 

Phạ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") 

Ở 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") 

Ghi 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") 

Trong 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") 

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") 

Lầ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") 

Bả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 10

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 11

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 12

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 13

Đ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 14

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 15

Form Responses 16

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 17

Đầ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 18

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 19

Đ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.