From a table in A1:D, return rows where region is West and amount is over 500, sorted by amount

公式

=QUERY(A1:D, "select A, B, D where C = 'West' and D > 500 order by D desc", 1)

说明

QUERY uses a SQL-like string. Column letters refer to the input range, not the sheet. The final 1 tells Sheets row 1 is a header.

步骤

  1. A1:D is the table including headers.
  2. select lists the columns to return.
  3. where filters region and amount.
  4. order by D desc sorts the result.

变体

Group and sum

Pivot-style totals by region without a pivot table.

=QUERY(A1:D, "select C, sum(D) where D is not null group by C label sum(D) 'Total'", 1)

Contains text

Case-sensitive contains. Use lower() on a helper column for case-insensitive search.

=QUERY(A1:D, "select * where B contains 'Acme'", 1)

Dates in QUERY

The date literal must be yyyy-mm-dd inside the query string.

=QUERY(A1:D, "select A, D where A >= date '2024-01-01'", 1)