A query function allows you to use data controls to model your data in Google sheets, making it an important function.
The query function is very useful because it can replace the work of many other commands and replicate the functionality of the 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 ofSheetsGoogle. However, this is not the case and you can immediately start using the function of query to filter and quickly look for data in your favorite format.
What is a Google sheet query?
The word Query derives from Structured Query Language or SQL, a specific language of the domain used in programming to simplify the management of large or small data sets. An Query in Google sheet allows you to manipulate and analyze data with a single query formula. With the query function it is possible to specify complex conditions to filter, order and group data. You can also use various integrated functions to calculate and transform your data. The output of the query is a new table that includes only the lines and columns that meet the specified conditions.
Which syntax do I have to understand to use the query function?
Here is a basic syntax of a function of query in Google sheets:
= Query (date, Query, [Headers])
- "Data" refers to the cell interval (called the interval) that contain the data you want to work with.
- "Query" is the actual query you want to perform on the data.
- "[Headers]" is an optional parameter that specifies if the first line of the data interval contains column header. If set to 1, the first line is treated as heads. If set to 0 or omitted, the first line is treated as data. I advise you to create the headers of the first line and to set them on 1 for a simpler experience.
The query are particularly useful when it is necessary to extract a subset of data from a set of large data and when you want to perform complex calculations or transformations on the data.
How to use the Google sheets in three simple steps
Create a list of numbered steps on how people can do it. I include screenshot.
1. Available for your data.
First you want to create your first interval with name, so you don't have to copy the cells every time you want to use them. Using command for Mac or Ctrl for Windows, it highlights each cell with data. Click "Data" and then click "intervals called" (in the photo below).
You will therefore give a name to the entire data set, therefore since this spreadsheet focuses on the names, sex and extracurricular activities of the students, I called it "class". Make sure not to use spaces during the name.
Then you will insert an empty cell to test the interval called. Type "= Query (enter the name of your data)" and see if it highlights all the cells for you (like the image below).
If you close the parenthesis and "send", an identical copy of your data should be generated to the right.
2
Now you can specify which headquarters to be addressed to your query. Use the following formula to specify which columns desires are displayed. I chose columns A and D on my graph so that they can see the names of the students and their states of origin.
= Query (Class, "Select A, D", 1)
After filling out this formula, go ahead and press sending. If you find problems, make sure you have written it exactly and not to lose any comma or parenthesis. You can add any column you want to the equation by adding a comma and then the letter of the column. So, if I wanted the name, sex and state of origin of the student, I would have written:= Query (class, "Select A, B, D", 1).
3. Get more detailed data.
Suppose that another column includes the student's GPA (see below) and we want to know which students have a GPA greater than 3.0.
You can use the following formula to order the GPAs and generate a ratio that includes the names, the sex and the GPA of students greater than 3.0.
= Query (Class, "Select A, B, G Where G> 3", 1)
Suppose you want to know how many students choose art as an extracurricular activity. You could use this formula to generate such data:
= Query (Class, "Select A, and Where E = 'Art'", 1)
A report would therefore be generated (like the one below), so that there are seven students in art.
If you want to know which 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 Query in Google sheets
1. Save time
Pretend to have a gigantic Google sheet with the name, sex, role and age of all those who work in a company. Your boss asks you to give him the name of every person who identifies himself as a woman in your company so that they can be invited to a special conference led by women.
You could spend the whole afternoon to copy the names and roles of these women, or you could save a lot of time using a query that generates a report in less than a second, which would be similar to this:
= Query (staff, "Select A, B where B = 'Female'", 1)
Here, the column "A" is their name and the column "B" is their sex.
2. Get more accurate results
Human error is inevitable, especially when you look at the same screen for hours trying to filter the data. Anyone who has to do with large data sets can testify to the difficulty of making copy and paste errors. The use of query in Google sheets is an exact formula; If you enter the precise data you want, you will genre a careful relationship.
3. Reuse the same formulas
Once you have a formula that you know you use often, it is not necessary to write it over and over again. You can simply click in the report to change the formula or you can save a model of that formula in a separate document.
For example, if you work in sales and each quarter you are asked to present the quarterly earnings from the highest to the lowest performance, you can ask them to add their entrances to a Google sheet.
So, when the moment of the presentation comes, you just have to generate a report using a formula like this:
= Query (sellers, "Select A, CO ORDER FOR C ASC", 1)
So "A" would be the name of the sellers, "C" would only represent the revenue for a specific and "ascending" quarter would go from the highest to the lowest, allowing you to identify the best results.
The query allow you to analyze and view your data in various ways, facilitating the processing of insights and the making of informed decisions. Have fun experimenting with different query functions to see what you can do with your data!
Published inGoogle
Comment first