If you need to manipulate data in Google Sheets, the QUERY function can help! It brings powerful, database-style searching to your spreadsheet, so you can look up and filter your data in any format you like. We’ll walk you through how to use it.

如果您需要处理Google表格中的数据，QUERY函数可以为您提供帮助！ 它为电子表格带来了强大的数据库样式搜索，因此您可以查找和过滤所需格式的数据。 我们将指导您如何使用它。

使用QUERY功能 (Using the QUERY Function)

The QUERY function isn’t too difficult to master if you’ve ever interacted with a database using SQL. The format of a typical QUERY function is similar to SQL and brings the power of database searches to Google Sheets.

如果您曾经使用SQL与数据库进行交互，则QUERY函数并不是很难掌握。 典型的QUERY函数的格式类似于SQL，并将数据库搜索的功能带到Google表格中。

The format of a formula that uses the QUERY function is =QUERY(data, query, headers). You replace “data” with your cell range (for example, “A2:D12” or “A:D”), and “query” with your search query.

使用QUERY函数的公式的格式为=QUERY(data, query, headers) 。 您用单元格范围(例如，“ A2：D12”或“ A：D”)替换“数据”，并用搜索查询替换“查询”。

The optional “headers” argument sets the number of header rows to include at the top of your data range. If you have a header that spreads over two cells, like “First” in A1 and “Name” in A2, this would specify that QUERY use the contents of the first two rows as the combined header.

可选的“标头”参数设置要包含在数据范围顶部的标头行数。 如果您的标头分布在两个单元格中，例如A1中的“第一”和A2中的“名称”，则将指定QUERY使用前两行的内容作为组合标头。

In the example below, a sheet (called “Staff List”) of a Google Sheets spreadsheet includes a list of employees. It includes their names, employee ID numbers, birth dates, and whether they’ve attended their mandatory employee training session.

在下面的示例中，Google表格电子表格的工作表(称为“工作人员列表”)包括员工列表。 它包括他们的姓名，员工ID号，出生日期，以及他们是否参加过必修的员工培训课程。

On a second sheet, you can use a QUERY formula to pull a list of all of employees who haven’t attended the mandatory training session. This list will include employee ID numbers, first names, last names, and whether they attended the training session.

在第二张纸上，您可以使用QUERY公式提取未参加强制培训的所有员工的列表。 此列表将包括员工ID号，名字，姓氏以及他们是否参加了培训课程。

To do this with the data shown above, you could type =QUERY('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'"). This queries the data from range A2 to E12 on the “Staff List” sheet.

要使用上面显示的数据执行此操作，可以键入=QUERY('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'") 。 这将查询“工作人员列表”表上从A2到E12范围的数据。

Like a typical SQL query, the QUERY function selects the columns to display (SELECT) and identifies the parameters for the search (WHERE). It returns columns A, B, C, and E, providing a list of all matching rows in which the value in column E (“Attended Training”) is a text string containing “No.”

像典型SQL查询一样，QUERY函数选择要显示的列(SELECT)并标识搜索的参数(WHERE)。 它返回A，B，C和E列，提供所有匹配行的列表，其中E列中的值(“参加培训”)是包含“ No”的文本字符串。

As shown above, four employees from the initial list haven’t attended a training session. The QUERY function provided this info, as well as matching columns to show their names and employee ID numbers in a separate list.

如上所示，最初列表中的四名员工尚未参加培训。 QUERY函数提供了此信息以及匹配列，以在单独的列表中显示其名称和员工ID号。

This example uses a very specific range of data. You could change this to query all the data in columns A to E. This would allow you to continue to add new employees to the list. The QUERY formula you used will also update automatically whenever you add new employees or when someone attends the training session.

本示例使用非常具体的数据范围。 您可以更改它以查询A到E列中的所有数据。这将允许您继续向列表中添加新员工。 每当您添加新员工或某人参加培训课程时，您使用的QUERY公式也会自动更新。

The correct formula for this is =QUERY('Staff List'!A2:E, "Select A, B, C, E WHERE E = 'No'"). This formula ignores the initial “Employees” title in cell A1.

正确的公式是=QUERY('Staff List'!A2:E, "Select A, B, C, E WHERE E = 'No'") 。 此公式将忽略单元格A1中的初始“雇员”标题。

If you add an 11th employee who hasn’t attended the training to the initial list, as shown below (Christine Smith), the QUERY formula updates, as well, and displays the new employee.

如果将未参加培训的第11名员工添加到初始列表中，如下所示(克里斯汀·史密斯)(克里斯汀·史密斯)，则QUERY公式也会更新，并显示新员工。

进阶的QUERY公式 (Advanced QUERY Formulas)

The QUERY function is versatile. It allows you to use other logical operations (like AND and OR) or Google functions (like COUNT) as part of your search. You can also use comparison operators (greater than, less than, and so on) to find values between two figures.

QUERY功能是通用的。 它允许您在搜索过程中使用其他逻辑运算(例如AND和OR)或Google函数(例如COUNT)。 您还可以使用比较运算符(大于，小于等)来查找两个图形之间的值。

将比较运算符与QUERY结合使用 (Using Comparison Operators with QUERY)

You can use QUERY with comparison operators (like less than, greater than, or equal to) to narrow down and filter data. To do this, we’ll add an additional column (F) to our “Staff List” sheet with the number of awards each employee has won.

您可以将QUERY与比较运算符(例如小于，大于或等于)一起使用来缩小和过滤数据。 为此，我们将在“工作人员名单”表中增加一列(F)，其中包含每位员工获得的奖励数量。

Using QUERY, we can search for all employees who have won at least one award. The format for this formula is =QUERY('Staff List'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0").

使用QUERY，我们可以搜索所有获得至少一项奖励的员工。 此公式的格式为=QUERY('Staff List'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0") 。

This uses a greater than comparison operator (>) to search for values above zero in column F.

这使用大于比较运算符(>)在F列中搜索大于零的值。

The example above shows the QUERY function returned a list of eight employees who have won one or more awards. Out of 11 total employees, three have never won an award.

上面的示例显示QUERY函数返回了一个或多个奖项的八名员工的列表。 在11名员工中，有3名从未获奖。

将AND和OR与QUERY结合使用 (Using AND and OR with QUERY)

Nested  work well within a larger QUERY formula to add multiple search criteria to your formula.

嵌套的在较大的QUERY公式中可以很好地工作，可以向您的公式添加多个搜索条件。

A good way to test AND is to search for data between two dates. If we use our employee list example, we could list all employees born from 1980 to 1989.

测试AND的一种好方法是在两个日期之间搜索数据。 如果使用雇员列表示例，则可以列出1980年至1989年之间出生的所有雇员。

This also takes advantage of comparison operators, like greater than or equal to (>=) and less than or equal to (<=).

这还利用了比较运算符，例如大于或等于(> =)且小于或等于(<=)。

The format for this formula is =QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' and D <= DATE '1989-12-31'"). This also uses an additional nested DATE function to parse date timestamps correctly, and looks for all birthdays between and equal to January 1, 1980, and December 31, 1989.

此公式的格式为=QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' and D <= DATE '1989-12-31'") 。 它还使用附加的嵌套DATE函数正确解析日期时间戳记，并查找1980年1月1日至1989年12月31日之间的所有生日。

As shown above, three employees who were born in 1980, 1986, and 1983 meet these requirements.

如上所示，1980年，1986年和1983年出生的三名员工符合这些要求。

You can also use OR to produce similar results. If we use the same data, but switch the dates and use OR, we can exclude all employees who were born in the 1980s.

您也可以使用OR产生类似的结果。 如果我们使用相同的数据，但更改日期并使用OR，则可以排除所有1980年代出生的员工。

The format for this formula would be =QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' or D <= DATE '1980-1-1'").

此公式的格式为=QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' or D <= DATE '1980-1-1'") 。

Of the original 10 employees, three were born in the 1980s. The example above shows the remaining seven, who were all born before or after the dates we excluded.

在最初的10名员工中，有3名出生于1980年代。 上面的示例显示了剩下的七个人，他们都在我们排除的日期之前或之后出生。

结合使用COUNT和QUERY (Using COUNT with QUERY)

Rather than simply searching for and returning data, you can also mix QUERY with other functions, like COUNT, to manipulate data. Let’s say we want to clear a number of all the employees on our list who have and haven’t attended the mandatory training session.

您不仅可以简单地搜索和返回数据，还可以将QUERY与其他功能(例如COUNT)混合使用来处理数据。 假设我们要清除清单上所有已经参加过和未参加过强制培训的员工。

To do this, you can combine QUERY with COUNT like this  =QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E").

为此，您可以将QUERY与COUNT结合起来，例如=QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E") 。

Focusing on column E (“Attended Training”), the QUERY function used COUNT to count the number of times each type of value (a “Yes” or a “No” text string) was found. From our list, six employees have completed the training, and four haven’t.

以E列(“参加培训”)为重点，QUERY函数使用COUNT计数找到每种类型的值(“是”或“否”文本字符串)的次数。 在我们的清单中，有六名员工已完成培训，而四名员工尚未完成。

You can easily change this formula and use it with other types of Google functions, like SUM.

您可以轻松更改此公式，并将其与其他类型的Google函数(例如SUM)一起使用。

翻译自: