In the car on the way to the next day’s quest. Saito spoke while driving.
“There should be FILTER’s today. Inoue, teach Takahashi the syntax before we arrive.”
“Got it.”
Inoue, who was sitting in the back seat, took out a whiteboard from behind.
“First, the basic FILTER. The results might look a little similar to QUERY.”
=FILTER(Range, Condition 1, [Condition 2, …])
“As its name suggests, it’s simple and intuitive to use.”
A:Name B:Age C:Gender
—————- ———– —————
Takahashi Male 27
Saito Male 43
Inoue Female 17
“For example, lets say we wanted to extract people aged under 30 from here.”
=FILTER(A2:C4, C2:C4<=30)
“The results would be like this.”
(Author’s Note: Header lines are normally not included in the results, but are added here for convenience of explanation.)
Name Age Gender
————— ———— ————
Takahashi Male 27
Inoue Female 17
“As for the condition part, you write an expression that either returns TRUE or FALSE. If you want to narrow it down to just women, you can do this.”
=FILTER(A2:C4, C2:C4<=30, B2:B4=”Female”)
Name Age Gender
————— ———— ————
Inoue Female 17
“I see. Adding conditions and writing them one after another is similar to SUMIFS and COUNTIFS.”
“That’s right, but isn’t this more intuitive?”
That was certainly true. In the case of SUMIFS and COUNTIFS, the condition is assumed to be true. For example, if you wanted to count the number of men in the table using COUNTIFS, you would use this.
=COUNTIFS(B2:B4,”Male”)
This wasn’t a problem, but it was a little difficult to understand when trying to specify other conditions, such as under the age of 30.
=COUNTIFS(C2:C4,”<=30″)
It also felt a bit strange to enclose mathematical formulas in quotation marks. Furthermore, if I wanted to create a table showing how many people were under that age, I would need to reference the number 30 from a different cell and write it like this.
=COUNTIFS(C2:C4,”<=”&D3)
It made sense but was hard to read. In that respect, the way FILTER’s conditions worked seemed easier to read.
Inoue continued her explanation.
“On that note, the range of conditions does not need to be included in the range to be filtered and extracted. It is enough that the rows line up. So, if you only want to return the names of people under the age of 30, you can write this.”
=FILTER(A2:A4,C2:C4<=30)
Name
———-
Takahashi
Inoue
“As a side note, this can also be used as a replacement for COUNTIFS.”
=COUNTA(FILTER(A2:A4,C2:C4<=30))
“SUMIFS and AVERAGEIFS perform conditional aggregation compared to SUM and AVERAGE, but with this method, you can perform conditional aggregation even if there is no corresponding IFS function.”
=MEDIAN(FILTER)
=COUNTUNIQUE(FILTER)
“MEDIAN is use to find the median value while COUNTUNIQUE counts all non-duplicate values. You’ll see them often as it’s otherwise annoying to do.”
“I see, that’s convenient.”
“Yes. FILTER is a function that Excel doesn’t have（※）so it’s quite useful. That’s the explanation.”
“Thank you very much.”
Saito turned around from the driver’s seat and spoke up.
“Hey. Don’t forget SORT and UNIQUE, keep going.”
Inoue erased the words on the whiteboard with an ‘oops’ expression. It seemed like she had a tendency to forget things.
※Actually, it seems that Excel is also following suit and adding the FILTER function. However, it is only a beta feature.
> Note: September 24, 2018: The FILTER function is a beta feature and is currently only available to some Office Insiders. We’ll be optimising these features over the coming months. When it’s ready, we’ll release it to Office Insiders and Office 365 subscription holders.
https://support.office.com/ja-jp/article/filter-%E9%96%A2%E6%95%B0-f4f7cb66-82eb-4767-8f7c-4877ad80c759
Author’s Notes
I previously blogged about techniques for conditionally aggregating MEDIAN and COUNTUNIQUE. If you are interested, please click here.
MEDIAN https://www.minemura-coffee.com/entry/2017/04/21/143507
COUNTUNIQUE https://www.minemura-coffee.com/entry/2017/05/30/210338