Como usar a função query no Google Sheets

Uma função de consulta permite que você use comandos de dados para modelar seus dados no Google Sheets, tornando-a uma função importante.

The query function is very useful because it can replace the work of many other commands and replicate the functionality of pivot tables (a table that allows you to group, compare, and summarize larger data sets). A query may seem complicated or overwhelming if you are not familiar with the functions ofGoogle Sheets. However, this is not the case, and you can start using the query function right away to quickly filter and search data in your preferred format.

What is a query in Google Sheets?

The word query comes from Structured Query Language, or SQL, which is a domain-specific language used in programming to simplify the management of large or small data sets. A query in Google Sheets allows you to manipulate and analyze data with a single query formula. With the query function, you can specify complex conditions for filtering, sorting, and grouping data. You can also use various built-in functions to calculate and transform your data. The output of the query is a new table that includes only the rows and columns that meet the specified conditions.

What syntax do I need to understand to use the query function?

Here is a basic syntax of a query function in Google Sheets:

=QUERY(data, query, [headers])

• “data” refers to the range of cells (called a range) that contain the data you want to work with.
• “query” is the actual query you want to run on the data.
• “[headers]” is an optional parameter that specifies whether the first row of the data range contains column headers. If set to 1, the first row is treated as headers. If set to 0 or omitted, the first row is treated as data. I recommend creating headers in the first row and setting it to 1 for an easier experience.

Queries are particularly useful when you need to extract a subset of data from a large data set and when you want to perform complex calculations or transformations on the data.

How to use queries in Google Sheets in three easy steps

Create a list of numbered steps on how people can do this. Include screenshots.

1. Name your data.

First, you’ll want to create your first named range, so you don’t have to copy cells every time you want to use them. Using Command for Mac or CTRL for Windows, highlight every cell with data. Click “Data” and then click “Named ranges” (in the photo below).

You will then name the entire data set, so since this spreadsheet focuses on student names, gender, and extracurricular activities, I have called it “class.” Be sure not to use spaces when naming.

Then you will enter an empty cell to test the named range. Type “=query(ENTER YOUR DATA NAME)” and see if it highlights all the cells for you (like the image below).

If you close the parentheses and press “enter,” an identical copy of your data should be generated to the right.

2. Specify what data you want.

Now you can specify which headers you want your query to address. Use the following formula to specify which columns you want displayed. I chose columns A and D on my chart so I could see the student names and their home states.

=QUERY(class,  "select A,D", 1)

After filling out this formula, go ahead and press enter. If you run into issues, ensure that you have written it exactly and that you did not miss any commas or parentheses. You can add any columns you want to the equation by adding a comma and then the letter of the column. So, if you wanted the student’s name, gender, and home state, you would write:
=query(class, “select A, D”, 1).

3. Get more detailed data.

Let’s say another column includes the student’s GPAs (see below), and we want to know which students have a GPA greater than 3.0.

You can use the following formula to sort by GPAs and generate a report that includes the names, gender, and GPAs of students above 3.0.

=QUERY(class,  "select A, B, G where G > 3", 1)

Let’s say you want to know how many students choose art as an extracurricular activity. You could use this formula to generate that data:

=QUERY(class,  "select A, E  where E = 'Art' ", 1)

A report would then be generated (like the one below), so you can know that there are seven students in art.

If you want to know which female students have the highest GPA, you can use the following formula:

=QUERY(class, “select A, B, G where B = ‘Female’ order by G asc”, 1)

And the following report will be generated.

Examples of queries in Google Sheets

1. Save time

Pretend you have a gigantic Google Sheet with the name, gender, role, and age of everyone who works at a company. Your boss asks you to give them the name of every person who identifies as a woman at your company so they can be invited to a special women-led conference.

You could spend all afternoon copying the names and roles of these women, or you could save yourself a lot of time by using a query that generates a report in less than a second, which would look similar to this:

=query(staff, “seleziona A, B dove B = 'Femmina'”, 1)

Here, column “A” is their name and column “B” is their gender.

2. Get more accurate results

Human error is inevitable, especially when you stare at the same screen for hours trying to filter data. Anyone who deals with large data sets can attest to the difficulty of making copy-and-paste mistakes. Using queries in Google Sheets is an exact formula; if you input the precise data you want, you will generate an accurate report.

3. Reuse the same formulas

Uma vez que você tenha uma fórmula que você sabe que usará com frequência, não há necessidade de digitá-la repetidamente. Você pode simplesmente clicar no relatório para editar a fórmula ou pode salvar um modelo dessa fórmula em um documento separado.

Por exemplo, se você trabalha em vendas e a cada trimestre lhe é pedido para apresentar os ganhos trimestrais do mais alto para o mais baixo, você pode pedir para eles adicionarem suas receitas a uma planilha do Google.

Então, quando for hora da apresentação, você só precisará gerar um relatório usando uma fórmula como esta:

=query(venditori, “seleziona A, C ordine per C asc”, 1)

Então “A” seria o nome dos vendedores, “C” representaria apenas a receita de um trimestre específico e “ascendente” iria do mais alto para o mais baixo, permitindo que você identifique os melhores desempenhos.

As consultas permitem analisar e visualizar seus dados de várias maneiras, facilitando a obtenção de insights e a tomada de decisões informadas. Divirta-se experimentando diferentes funções de consulta para ver o que você pode fazer com seus dados!

Pubblicato in Google