QUERY Function: Perform SQL-like Data Operations in Google Sheets

スポンサーリンク
スポンサーリンク

About the QUERY Function

Overview of QUERY

Filter and Manipulate Data Using SQL-like QueriesGoogle Sheets Function

=QUERY( data_range, query, [header_rows] )

Summary The QUERY function is a powerful tool for filtering, aggregating, and sorting data within a spreadsheet using SQL-like syntax.

  • Enables flexible data manipulation with simple query syntax.
  • Performs database-like operations directly in Google Sheets.
  • Simplifies complex filtering and aggregation tasks.

When to Use QUERY

  • To extract data based on specific conditions.
  • When sorting or aggregating data is required.
  • For efficient data manipulation using SQL-like syntax.

How to Use QUERY

The following table demonstrates the basic usage of the QUERY function.

  A B C D
1 Name Age Score
2 John 20 85
3 Jack 22 90
4 Smith 19 78

The examples below show how to use QUERY:

Description Formula Result
Extract rows with scores of 80 or higher =QUERY(A1:C4, “SELECT A, C WHERE C >= 80”, 1) Displays data for John and Jack
Sort by age in descending order =QUERY(A1:C4, “SELECT A, B ORDER BY B DESC”, 1) Order: Jack, John, Smith
Calculate the average score =QUERY(A1:C4, “SELECT AVG(C)”, 1) Average score: 89

Results

  • Query syntax allows easy manipulation of data.
  • Data can be filtered and sorted based on specific conditions.

List of Queries Used in the QUERY Function

QUERY Function: Query Syntax and Examples in Google Sheets
The QUERY function in Google Sheets is a powerful tool for filtering, sorting, and aggregating data flexibly within a spreadsheet. This article provides a list ...

Advanced Examples of QUERY

The QUERY function enables complex data processing with ease. Below are advanced use cases:

  A B C D
1 Name Age Score Region
2 John 20 85 New York
3 Jack 22 90 Los Angels
4 Smith 19 78 Chicago
Description Formula Result
Extract top scorers from a specific region =QUERY(A1:D4, “SELECT A, C WHERE D = ‘New York’ AND C >= 80”, 1) Displays John only
Calculate average scores by region =QUERY(A1:D4, “SELECT D, AVG(C) GROUP BY D”, 1) Displays the average scores for each region

Points to Note

  • The data range must include a header row.
  • Query strings should be enclosed in single quotes.
  • Errors may occur if data types are inconsistent.

Conclusion

  • The QUERY function is a powerful tool for efficient filtering, aggregation, and sorting of data.
  • SQL-like syntax provides flexibility in data manipulation.
  • By paying attention to header rows and data formatting, complex data operations can be simplified.