【Googleスプレッドシート】クロス集計で2軸の分布を可視化!ヒートマップ的な表現

クロス集計は、2つのカテゴリ変数の関係を表にして、それぞれの組み合わせの頻度や合計を表示する分析手法です。例えば、販売データで「商品カテゴリ」と「購入月」のクロス集計を行えば、どの商品がどの時期に売れているかが一目でわかります。そして、その表に色を付けてヒートマップのように表現すると、数値の大小が視覚的に把握でき、より直感的な分析が可能になります。この記事では、GoogleスプレッドシートでCOUNTIFS関数やQUERY関数を使ったクロス集計の作り方と、条件付き書式でヒートマップ的な表現にする手順を詳しく解説します。

クロス集計とヒートマップ表現の概要

クロス集計とは、行と列にそれぞれ異なるカテゴリを配置し、その交点に集計値(件数、合計、平均など)を表示する表のことです。2軸の分布を可視化する代表的な方法で、例えば「性別×商品分類」や「地域×月」などの関係を分析するのに使います。ヒートマップ的な表現とは、セルに値の大小に応じた色を付けることで、数値の分布を色の濃淡で直感的に把握できるようにする方法です。Googleスプレッドシートでは、COUNTIFS関数やQUERY関数でクロス集計表を作り、条件付き書式のカラースケールを適用することで、簡単にヒートマップが作成できます。

COUNTIFS関数を使ったクロス集計の手順

COUNTIFS関数は、複数の条件に一致するセルをカウントします。ここでは、2つのカテゴリの組み合わせごとに件数を集計します。データは縦持ち(1行1レコード)で、カテゴリ列がA列とB列にあると想定します。

1. クロス集計表の行ラベルと列ラベルを準備する
   まず、表示したい行のカテゴリ一覧と列のカテゴリ一覧を、表の左端と上部にそれぞれ並べます。例えば、行に「商品カテゴリ」の一覧、列に「月」の一覧を、重複のないユニークな値として用意します。UNIQUE関数を使ってデータから抽出しても構いません。
2. COUNTIFS関数を最初のセルに入力する
   クロス集計表の左上のセル(例:B2)に、次の数式を入力します。
   =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, B$1)
   ここで、$A$2:$A$100が最初のカテゴリ(行条件)の範囲、$A2がその行ラベル、$B$2:$B$100が2番目のカテゴリ(列条件)の範囲、B$1がその列ラベルです。行と列を固定するために、$の位置に注意します。
3. 数式を表全体にコピーする
   上記の数式を、クロス集計表の全セル(行ラベルと列ラベルを除く範囲)にコピーします。コピー先のセルで相対参照が自動調整され、各行の行ラベルと各列の列ラベルに基づいた集計が行われます。
4. 合計行や合計列を追加する(オプション)
   必要に応じて、行の合計や列の合計をSUM関数で追加します。これにより、全体の分布を把握しやすくなります。

QUERY関数を使ったクロス集計の手順

QUERY関数は、SQLライクな構文でデータを操作できます。特に、PIVOT句を使うと、クロス集計表を直接生成できます。データは1行1レコードの縦持ち形式で、カテゴリ列と数値列(件数を数える場合はダミーの1列)が必要です。

1. QUERY関数の構文を理解する
   基本形は次の通りです。
   =QUERY(データ範囲, "SELECT 列1, COUNT(列3) WHERE 列3 IS NOT NULL GROUP BY 列1 PIVOT 列2", 1)
   列1が行ラベル、列2が列ラベル、列3が集計対象です。COUNTの代わりにSUMやAVGも使えます。第3引数の1はヘッダー行数を示します。
2. 実際のデータに合わせて数式を入力する
   例えば、データがA列に商品カテゴリ、B列に月、C列に売上金額がある場合、次のように入力します。
   =QUERY(A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B", 1)
   これで、行に商品カテゴリ、列に月、交点に売上合計が表示されたクロス集計表が生成されます。
3. 必要に応じてWHERE句でフィルタリングする
   特定の条件に限定したい場合は、WHERE句を追加します。例えば、売上が0より大きいデータのみを対象にする場合、”SELECT A, SUM(C) WHERE C>0 GROUP BY A PIVOT B”とします。
4. 結果表の並び替えを調整する
   QUERY関数の結果は、行ラベルがアルファベット順や数値順に並びます。必要に応じてORDER BY句で並び替えを指定します。ただし、PIVOT句を使う場合、列の順序はデータの出現順になります。

条件付き書式でヒートマップを表現する手順

クロス集計表が完成したら、セルに色を付けてヒートマップにします。Googleスプレッドシートの条件付き書式の「カラースケール」機能を使うと、値に応じて自動的にグラデーションを適用できます。

1. クロス集計表の数値部分を選択する
   行ラベルと列ラベルを除いた、集計値が入っているセル範囲を選択します。合計行や合計列がある場合は、それも含めるかどうかは目的によります。
2. 条件付き書式のカラースケールを設定する
   メニューから「表示形式」→「条件付き書式」をクリックし、右側のパネルで「カラースケール」タブを選びます。最小値と最大値の色を選択します。一般的には、最小値を白(または薄い色)、最大値を濃い青色や赤色に設定します。中間値も設定可能です。
3. スケールの基準を確認する
   カラースケールは、デフォルトで選択範囲の最小値と最大値に基づいて色を割り当てます。絶対値で色を固定したい場合は、「最小値」や「最大値」を数値で指定することもできます。必要に応じてカスタマイズしましょう。
4. オプションで色の閾値を調整する
   より細かい制御が必要な場合は、パーセントやパーセンタイルを基準に設定することも可能です。例えば、上位10%を濃い色にする、などができます。

注意点とよくある失敗

COUNTIFS関数で行と列の固定を間違える

COUNTIFS関数をコピーする際、$の位置を正しく設定しないと、参照がずれて正しい集計ができません。行条件では行ラベルの列を絶対参照($A2)に、列条件では列ラベルの行を絶対参照(B$1)にすることがポイントです。範囲は絶対参照($A$2:$A$100)にします。

QUERY関数のPIVOT句で列順が意図通りにならない

PIVOT句はデータの出現順に列を並べます。特定の順序(例:月の順)にしたい場合、データソース側で並び替えておくか、QUERY内でORDER BYを使って工夫する必要があります。ただし、ORDER BYとPIVOTの併用は制限があるため、代替としてピボットテーブル機能を使うことも検討しましょう。

条件付き書式のカラースケールが範囲外のセルに影響する

カラースケールを設定する範囲は、集計値のセルだけに限定しましょう。行ラベルや列ラベルを含めると、文字列が無視されるか、不適切な色が付くことがあります。また、空白セルはスケールの対象外になりますが、数式で空白を返す場合は注意が必要です。

まとめ

この記事では、GoogleスプレッドシートでCOUNTIFS関数とQUERY関数を使ってクロス集計表を作成し、条件付き書式のカラースケールでヒートマップ表現にする方法を解説しました。COUNTIFSはシンプルで自由度が高く、QUERYは動的な集計に適しています。ヒートマップを組み合わせることで、数値の大小が一目でわかるため、データ分析の質が向上します。ぜひ実際のデータで試してみてください。さらに応用として、スパークラインやグラフを併用すると、よりリッチな可視化が可能です。