21 AWESOME THINGS GOOGLE SHEETS CAN DO

The Google Spreadsheet Database Query supports the use of Named Ranges in its SELECT & WHERE Clause.

Bạn đang xem: 21 awesome things google sheets can do

Maybe this is the first time you are attempting khổng lồ use the Named Ranges in Query.

If you have already tried, then you might have sầu experienced a tough time coding your formula correctly.

With the help of this Query function tutorial, you can learn how to lớn code a Query that includes named ranges.

How khổng lồ Use Named Ranges in Query in Google Sheets – Examples

You are here because you already know the Named Ranges in Google Sheets & the use of Query.

I mean, you know khổng lồ use both of them independently.

If not, don’t hesitate to check my relevant help articles below.

Short Description About Named Ranges

You can customize the name of a cell or cells using the Named Ranges in Google Doc Sheets.

For example, you can name the cell address A1 to lớn “Hello” or a range of cells like A1: A10 lớn “Hellobaby.”

I’ve just used two funny names khổng lồ name the cell/cells.

But when you use it in real life, please bởi use meaningful names like “sales,” “purchase,” “total,” etc.

Google Docs has official documentation on how lớn use the Named Ranges in its cloud-powered Spreadsheet Application.

Here is that source liên kết.

I have my own version w.r.t. the use of this so-calledNamed Ranges in Google Sheets.See that also here –Make the Formulas Simple by Applying Names lớn Range of Cells.

Short Description About Query

The Query syntax is similar khổng lồ the Squốc lộ.

This function helps users to lớn persize various data manipulation techniques in Google Doc Sheets.

Here is my tutorial lớn learn using the Query function in Google Spreadsheets – Learn Query Function with Examples in Google Sheets.

Now coming baông xã khổng lồ our tutorial, i.e.,how to use named ranges in Query in Google Sheets, I am beginning with the Select clause.

The Use of Named Ranges in Select Clause of Query

The Select clause is the option lớn select columns or reorder the columns in Query.

For example, in two-column data in the range A1:B, you can use the Query khổng lồ return only the second column.

=QUERY(A1:B,"Select B")You can replace column B here with a named range.

Read on to lớn underst& how to vì that & why that is necessary.

Why Should One Use Named Ranges in Query Select Clause

To underst& the benefit, insert a new column between your existing columns in the range & see that the column reference is the same in Query.

The “Select B” won’t become “Select C.” If you use named ranges, you can sort it out.

How to lớn Use Named Ranges in Query Select Clause

To use named ranges to select acolumn in Query, you should use a helper cell. Here is one example.

Xem thêm: Các Bài Tập Hóa 8 - Giải Bài Tập Hóa Học 8, Hóa 8

Sample Data:


*

Now let’s see how to name a range for use in Query.

Steps:

1. In cell F1, type this formula.

=left(address(row(B1),column(B1),4))I am naming column B.

If you want to name column A, change B1 lớn A1.

Formula Explanation

The ADDRESS formula, which includes the ROW & COLUMN functions, returns the cell address of B1.

The LEFTfunction extracts the first letter that is the column heading from it.

Update:- You can use the below REGEXEXTRACT instead of the above Left và Address combination as it (the latter combo) won’t return both the characters when you refer khổng lồ cells such as AB1.

=REGEXEXTRACT(address(row(B1),column(B1),4),"+")2. Cliông xã on cell F1 và then go khổng lồ the thực đơn Data > Named ranges. I am naming the range (here cell F1) as “Age.”


*

3. Here is the Query formula that includes a named range in the Select clause.

=QUERY(A:B,"Select "&Age)I have sầu this formula in cell D1.

I am inserting a new column between A andBvà see what happens to the formula result.


*

Example to Named Ranges in Where Clause in Query

Below you can learn the use of Named Ranges in the Query Where clause.

Here I am using a new phối of data.


*

In this data, column A contains the first and last names in combined size.

To filter the data by the names in column A, I have entered the first name & last name (criteria) separately in E2 and F2.

So we will name the cells individually and then combine them in the formula.

Select E2 & name it “FirstName.” Similarly, name F2 as “LastName.”

=QUERY(A1:B,"Select * where A matches ""&join(" ",FirstName,LastName)&""")This Query formula will filter the rows that match the name “Sean Carter” in column A.

Similar:Matches Regular Expression Match in Google Sheets Query.

If there is only a single criterion, then the JOIN function is not required. The formula would be like this.

Change the value in cell E2 to “Amy Gonzalez” and use the below formula.

=QUERY(A1:B,"Select * where A matches ""&FirstName&""")The single và double quotes around the named range in this Query formula may change according lớn the criterion type (text, number, or date).

You can follow the above sầu same approach if the criterion is a text string. If it’s number, there is a difference in the use.

You can check my Function Guide & piông chồng Query và see how to lớn use different criteria in it.

By following the above sầu examples, you can quickly learn how lớn use Named Ranges in Query in Google Sheets.

link tải 567 live app | W88Vuive | F8bet|xo so ket qua| tải app qqlive apk | jun88