You can extract data from a range with the QUERY function
The QUERY function “uses the query language of the Google Visualization API to query the entire data”.
QUERY(データ, クエリ, [見出し])I’ve introduced it before, but I would like to try it out by actually using it using a query.
This time , I would like to extract data from the Tokyo Metropolitan Open Data Catalog site using data from the Tokyo Metropolitan Wholesale Market Daily Report (planned sales volume, flowers).
WHERE (conditional specification)
You can specify conditions by using where. In the following case, only data with “spray chrysanthemum” in column F will be extracted.
=query(estimate_quantity_flower!A2:K,"select * where F = 'スプレー菊'")Only the data with “spray chrysanthemum” in column F was extracted!
WHERE AND (multiple conditions specified)
If you use WHERE AND, you can specify multiple conditions (○○ and ○○).
=query(estimate_quantity_flower!A2:K,"select * where B='板橋' and F='スプレー菊'")
Only data with “Itabashi” in column B and “Spray chrysanthemum” in column F was extracted!
WHERE OR (multiple conditions specified)
With WHERE OR, you can specify multiple conditions (○○ or ○○).
=query(estimate_quantity_flower!A2:K,"select * where B='板橋' or B='スプレー菊'")Data with “Itabashi” in column B or “Spray chrysanthemum” in column F has been extracted!
WHERE AND OR (multiple condition specification/compound)
Let’s combine this AND and OR.
=query(estimate_quantity_flower!A2:K,"select * where (B='板橋'or B='大田') and F='スプレー菊'")The or condition is enclosed in parentheses after where, and the and condition is connected after that.
Now, the data with “Itabashi” or “Ota” in column B and “Spray chrysanthemum” in column F has been extracted!
It seems that you can do various things with compound conditions of or and and.
summary
By using the “query function” like this, you can extract data under various conditions!
This time, I extracted it with a query using a WHERE clause. Next time, I would like to try extracting with a different pattern query!
Please refer to it