【Googleスプレッドシート】SUMPRODUCT関数と正規表現を使って特定のURLをカウントする方法
1. はじめに
「データの中から特定のパターンを含むものだけを集計したいけれど、どうすればいいのか？」
「SUMPRODUCTを使えばいいらしいけれど、正規表現を条件に含める方法が分からない...」
こんな悩みを抱えている方に向けて、Googleスプレッドシートの SUMPRODUCT 関数と REGEXMATCH 関数を組み合わせて、特定のパターンを含むデータをカウント・合計する方法を解説します。
関数の使い方だけでなく、なぜ --（二重マイナス演算子）が必要なのか、全列参照の問題点など、スプレッドシートのパフォーマンスを最適化するポイントも説明します。
この記事では、データ分析を行うマーケターやWeb担当者向けに、具体的な関数の書き方や応用テクニックを紹介します。
2. 対象読者
- Googleスプレッドシートを使ってデータ集計を行いたい方
-
SUMPRODUCT関数やREGEXMATCH関数の使い方を学びたい方 - スプレッドシートで条件付き集計を行いたい方
- 特定のパターンを含むデータを抽出する方法を知りたい方
3. 記事を読むメリット
この記事を読むことで、次のようなことが分かります。
-
SUMPRODUCTを使って条件付きでデータを合計・カウントする方法 -
REGEXMATCHを使って、特定のパターンを抽出する方法 -
--（二重マイナス）を使う理由とブール値の変換方法 -
SUMPRODUCTで全列参照を避けるべき理由と、最適な範囲指定の方法
4. 結論
-
SUMPRODUCT関数とREGEXMATCH関数を組み合わせることで、スプレッドシート上で特定のパターンを含むデータを集計できる。 -
--を使うことで、TRUE/FALSEを1/0に変換し、条件付きの計算が可能になる。 - 全列参照 (
A:A) は処理速度が遅くなるため、範囲を限定 (A2:A1000) するのがベストプラクティス。
5. 本文
5.1 SUMPRODUCT 関数とは？
SUMPRODUCT 関数は、指定した範囲の要素同士を掛け合わせ、その合計を求める関数です。
基本構文
=SUMPRODUCT(配列1, 配列2, ...)
例：商品と価格の合計
| A列 (商品) | B列 (数量) | C列 (単価) |
|---|---|---|
| りんご | 3 | 100 |
| みかん | 5 | 80 |
| りんご | 2 | 100 |
| みかん | 4 | 80 |
=SUMPRODUCT(B2:B5, C2:C5)
これは、数量 × 単価 を計算し、すべての合計を求めます。
5.2 REGEXMATCH を使って特定のパターンを検索
REGEXMATCH は、指定したテキストが 正規表現に一致するかどうか を判定する関数です。
構文
=REGEXMATCH(検索対象セル, "正規表現")
例：特定のパターンを含むかチェック
=REGEXMATCH(A2, "example|sample|test")
TRUE または FALSE が返されます。
5.3 SUMPRODUCT と REGEXMATCH を組み合わせる
特定の条件を満たすデータの合計を求める式
=SUMPRODUCT(--(データ範囲!D2:D1000="click"), --REGEXMATCH(データ範囲!F2:F1000, "example|sample|test"), データ範囲!F2:F1000)
解説
-
データ範囲!D2:D1000="click"→D列がclickならTRUE（1）、それ以外はFALSE（0）。 -
REGEXMATCH(データ範囲!F2:F1000, "example|sample|test")-
F列に 特定のパターン が含まれる場合TRUE（1）、それ以外FALSE（0）。
-
-
SUMPRODUCTにより、条件を満たすF列の値を合計。
5.4 --（二重マイナス）を使う理由
REGEXMATCH や 条件式 は TRUE/FALSE を返しますが、SUMPRODUCT では 1/0 にする必要があります。
変換方法
--(条件式)
例
=SUMPRODUCT(--(A2:A10="りんご"))
この -- によって TRUE → 1、FALSE → 0 に変換されます。
5.5 全列参照を避けるべき理由
SUMPRODUCT で A:A のように 全列指定すると、104万行すべてが対象 になり、処理が遅くなります。
❌ 避けるべき書き方
=SUMPRODUCT(--(A:A="りんご"))
✅ 最適な書き方
=SUMPRODUCT(--(A2:A1000="りんご"))
6. まとめ
✅ SUMPRODUCT と REGEXMATCH で特定のパターンを含むデータを集計できる。
✅ -- を使って TRUE/FALSE を 1/0 に変換する。
✅ SUMPRODUCT では 全列参照を避け、範囲を限定する のが重要。
Googleスプレッドシートでデータを効率よく分析するために、ぜひ活用
Discussion