QUERY takes an input range of data, and can return an output in a table based on criteria specified using SQL-like code. Use cases are huge, the most common is the ability to return a filtered dataset e.g. columns D and B where E = London. The FILTER function can do similar things but you cannot specify which columns you want and the headers don't come across.

If you are already using FILTER/SORT and other dynamic arrays in Google Sheets, you may find the built in options too limiting and want to stretch it further and that is where QUERY's flexibility comes in. If you aren't a coder don't worry it’s not too hard to pick up, the instructions here should enable you to do enough without taking a long time to practice.

We will cover all nine of QUERY's clauses, which must be written in this order when you use a combination: 1. SELECT, 2. WHERE, 3. GROUP BY, 4. PIVOT, 5. ORDER BY, 6. LIMIT, 7. OFFSET, 8. LABEL, 9. FORMAT

Group by /Pivot works with SUM, COUNT, AVG, MIN, MAX and filtering is explored with advanced operators such as contains, dates, AND/OR/NOT (SQL's like is also possible for fuzzy matching but contains is easier to use for many).

If you prefer article form, I go through these features on this article I wrote: [ Ссылка ]

Another video on several of Sheets' Dynamic array functions is also useful here: [ Ссылка ]

Table of Contents:

00:00 - Introduction
00:40 - Intro/Select
02:21 - Filter/Where clause
03:12 - Filter CONTAINS
03:49 - Using dates
04:37 - AND/OR
05:48 - Refer to cell/dropdown
09:01 - Rename columns with label
09:39 - LIMIT
10:02 - OFFSET
10:45 - Combine sheets dynamically QUERY
13:01 - Headers
13:27 - Order by
14:03 - Group by
15:25 - Pivot
16:18 - FORMAT number style
17:19 - CLAUSE/function order

свернуть