Google Spreadsheet で条件に当てはまるレコード数をカウントしたい時に COUNTIF や COUNTIFS を使うが、複数条件ある時 COUNTIFS だとデフォルトが AND になるので OR でカウントしたいときの Tips
サンプルデータ
| |
A |
B |
C |
| 1 |
販売商品コード |
商品名 |
単価 |
| 2 |
A1012 |
ChatGTP入門 |
1670 |
| 3 |
A1018 |
React 入門コース |
5800 |
| 4 |
A1020 |
プログラミング入門コース |
2800 |
| 5 |
A1012 |
ChatGTP入門 |
1670 |
| 6 |
A1016 |
STUDIO デザインコース |
2400 |
| 7 |
A1011 |
Spotifyコース |
5800 |
| 8 |
A1018 |
React 入門コース |
5800 |
| 9 |
A1019 |
AI 入門コース |
2400 |
| 10 |
A1019 |
AI 入門コース |
2400 |
| 11 |
A1012 |
ChatGTP入門 |
1670 |
| 12 |
A1020 |
プログラミング入門コース |
2800 |
商品名が ChatGTP入門 or AI 入門コース のレコード数を求める
1. 別々に COUNTIF で数を求め合算する
重複が発生しない条件なら、シンプルに条件ごとに COUNTIF で集計したものを足せば良い
= COUNTIF($B$2:$B$12, "ChatGTP入門") + COUNTIF($B$2:$B$12, "AI 入門コース")
COUNTIF を足していく方法はシンプルで可読性も高いが、条件が増えると記述が面倒…
2. OR 条件を 配列 {} で定義する方法
🙅 動作しない
= SUM(COUNTIFS($B$2:$B$12, {"ChatGTP入門", "AI 入門コース"}))
Excel では上記のように COUNTIFS の条件を 配列 {} 内に複数書けば OR 条件として動作するような記述があるが Google Spreadsheet では、最初の条件のレコードのみが返され、意図したとおりに動作しない
= ARRAYFORMULA(SUM(COUNTIF($B$2:$B12, {F2, F3})))
3. MAP + LAMBDA を使う方法
MAP関数 MAP(配列1, [配列2, ...], LAMBDA)
この関数は、LAMBDA 関数を各値に適用して、指定した配列内の各値を新しい値にマッピングします。
cf. MAP function - Google Docs Editors Help
LAMBDA 関数 LAMBDA(名前, 数式)
一連の名前とそれらを使用する数式を含むカスタム関数を作成して返すことができます。
cf. LAMBDA function - Google Docs Editors Help
=COUNTIF(MAP($B$2:$B12, $B$2:$B12,
LAMBDA(a, b, OR(a="ChatGTP入門", b="AI 入門コース")))
, TRUE)
条件の分だけ範囲を作って渡す必要があるので、条件が多くなると少し見通しが悪くなりそうだと感じた
複数の OR 条件を AND したい場合
(A OR B OR C) AND (D OR E) のようなケース
商品名が React 入門コース or Spotifyコース or プログラミング入門コース or STUDIO デザインコース かつ 単価が 5000 以上 or 2500以下 のレコード数を求める
(該当レコードは: 3 React 入門コース 5800, 6 STUDIO デザインコース, 2400, 7 Spotifyコース 5800,8 React 入門コース 5800 なので 4 になればよい
🙅 CONTIF を足すを方法では定義できない
全てが OR 条件になってしまうので AND 条件を表現できない
=(COUNTIF($B$2:B$12, "React 入門コース") + COUNTIF($B$2:B$12, "Spotifyコース") + COUNTIF($B$2:B$12, "プログラミング入門コース") + COUNTIF($B$2:B$12, "STUDIO デザインコース"))
+ (COUNTIF($C$2:C$12, ">=5000") + COUNTIF($C$2:C$12, "<=2500"))
🙅 配列 {} 定義・MAP + LAMBDA はエラーになる
配列 {} 定義
=ARRAYFORMULA(
SUM(
COUNTIFS(
$B$2:$B$12, {"React 入門コース", "Spotifyコース", "プログラミング入門コース", "STUDIO デザインコース"},
$C$2:$C$12, {">=5000", "<=2500"}
)
)
)
-> COUNTIFS の配列引数のサイズが異なります。
MAP + LAMBDA
=COUNTIFS(
MAP($B$2:$B$12, $B$2:$B$12, $B$2:$B$12, $B$2:$B$12,
LAMBDA(a, b, c, d OR(a="React 入門コース", b="Spotifyコース" c="プログラミング入門コース", d="STUDIO デザインコース"))), TRUE,
MAP($C$2:$C$12, $C$2:$C$12,
LAMBDA(a, b OR(a>=5000, b<=2500))), TRUE
)
-> 数式の解析エラーです。
🙆 素直に query を使う
query を使い結果を count で出せば良い
=QUERY(A2:C12,
"SELECT count(A)
WHERE
(B = 'React 入門コース' OR B = 'Spotifyコース' OR B = 'プログラミング入門コース' OR B = 'STUDIO デザインコース')
AND (C >= 5000 OR C <= 2500)
LABEL count(A) ''"
)
素直に query で書くほうが SQL が読めるなら見通しも良くて良い
まとめ
- OR 条件が1つだけなら
ARRAYFORMULA(SUM(COUNTIF(範囲, {条件1, 条件2, 条件3, ...}))) か Query を使うのが良さそう
- 複数の OR 条件を AND するような複雑な条件の場合は、素直に query を使い結果を
count(A) のような形で出すのが良い
[参考]
#N/A クエリが空の出力で完了しました。
