動的配列で集計表を作成する

集計表は、大量の生データの行を、読みやすい小さなまとまりに整理します。カテゴリごとに1行を割り当て、その横に合計を表示します。たとえば、数百行ある売上記録を、地域ごとの総売上を示す整った表にまとめるイメージです。

以前は、手動でピボットテーブルを作成し、更新する必要がありました。現在は、データが変わった瞬間に自動更新される動的配列数式を使えます。ボタン操作も、更新操作も不要です。

このレッスンでは、3つの強力な機能を組み合わせます。UNIQUEでカテゴリを一覧にし、SUMIFSでカテゴリごとに合計し、FILTERで一致する行を取り出します。これらを組み合わせることで、常に最新の集計表を作成できます。

Salesという名前のシートに3つの列があるとします。A列はRegion、B列はProduct、C列はAmountで、2行目から200行目までデータが入っています。

ここでは、地域ごとの総売上を示す集計表を作成します。最初の課題は、地域名を手入力せずに、重複のない一覧を取得することです。後から新しい地域が追加される可能性があるためです。

• A2:A200には、East、West、East、Northのように、同じ地域名が何度も入っています。
• 必要なのは、East、West、Northをそれぞれ1回ずつ表示した一覧です。

この重複のない一覧が、集計表全体の基盤になります。

UNIQUE関数は範囲を受け取り、それぞれの値を1回だけ返します。結果はスピルします。つまり、重複のない値の数に応じて、1つの数式が必要なセル数を自動的に埋めます。

セルE2に入力すると、その下に地域の一覧が自動的に表示されます。

後からデータに新しい地域を追加すると、スピルした一覧も自動的に広がります。数式を編集する必要はありません。

次に、E列にある各地域のAmountの合計を求めます。SUMIFSは、別の範囲が条件に一致する場合にだけ、指定した範囲の値を合計します。

構文はSUMIFS(sum_range, criteria_range, criteria)です。最初の地域の横にあるF2に入力します。

E2#という参照がポイントです。#記号は、E2からスピルした範囲全体を表します。そのため、この1つの数式で、UNIQUEが作成したすべての地域の合計を求められます。

スピル参照のE2#は、数式が生成したブロック全体を常に指します。ブロックがどれだけ大きくなっても対応できるため、集計表を動的に保てます。

UNIQUEが3つの地域を見つけた場合、E2#は高さ3セルの範囲になり、SUMIFSは3つの合計を返します。データが5地域に増えると、どちらの範囲も編集なしで一緒に広がります。

• E2 = 先頭の1セルだけ
• E2# = E2から始まるスピル配列全体

#記号に慣れておきましょう。これはダッシュボード用数式の中心となる記号です。

合計が順序よく並んでいると、集計表はより読みやすくなります。地域の一覧をSORTで囲んでカテゴリをアルファベット順にするか、表全体を合計順に並べ替えます。

E2に入力して、地域をアルファベット順に一覧表示します。

F列の合計は引き続きE2#を参照しているため、地域を並べ替えると合計も自動的に対応する位置に並び替わります。2つの列は常に連動します。

合計だけでなく、あるカテゴリの元データの行を確認したい場合もあります。FILTERは条件を満たすすべての行を返し、それらをスピルさせます。

RegionがセルH1の値と一致する売上行をすべて表示します。

H1にEastが入っていれば、Eastの行がすべて表示されます。H1をWestに変更すると、表示ブロックが瞬時に書き換わります。これは、ダッシュボードでドリルダウン表示を作るための基礎になります。

一致するデータがないと、FILTERは#CALC!エラーを返します。表示を整えるには、オプションの第3引数に代替メッセージを指定します。

第3引数は、一致するデータが0件の場合に表示されます。

これで、売上のない地域にはエラーの代わりにわかりやすいメッセージが表示されます。予期しない選択によってレイアウトが崩れないよう、ダッシュボードでは必ずこの代替メッセージを追加しましょう。

集計表では、金額だけでなく、地域ごとの注文数を表示することもよくあります。COUNTIFSは、SUMIFSと同じように条件に一致する行を数えますが、合計範囲は必要ありません。

合計の横にあるG列に入力します。

これで、3列の集計表はRegion、Total Sales、Order Countを表示するようになります。すべてがE2#にある1つのスピルした地域一覧を基準に動作し、まとめて更新されます。

横に並べた完全な構成は次のとおりです。

• E2: =SORT(UNIQUE(Sales!A2:A200))で地域を一覧表示します。
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#)で地域ごとに合計します。
• G2: =COUNTIFS(Sales!A2:A200, E2#)で地域ごとの件数を数えます。

行方向に入力するのはE2の数式だけです。F列とG列は#参照によってスピルします。Salesのどこかに新しい売上を追加すると、クリック操作なしですべての列が更新されます。

数値を手入力したり、ピボットテーブルを更新したりする方法と比べて、数式で作る集計表には明確な利点があります。

• 常に最新: データが変わった瞬間に再計算されます。
• 自動でサイズ調整: UNIQUEと#参照によって、新しいカテゴリが自動的に表示されます。
• 透明性: セル内のロジックを誰でも確認できます。

一方で、スピル範囲が広がるには空きスペースが必要です。スピルが妨げられる場合については、後のレッスンで説明します。今は、数式の下に十分な空きを確保してください。

自動更新される集計表の作り方について、学んだ内容を確認しましょう。

自動的に管理される集計表を作成しました。

• UNIQUEで各カテゴリを1回ずつ一覧にし、結果をスピルさせます。
• SORTでその一覧を読みやすい順序に並べます。
• SUMIFSとCOUNTIFSで、E2#のスピル参照を使ってカテゴリごとの合計と件数を求めます。
• FILTERでドリルダウン用の一致する行を取り出し、一致しない場合は代替メッセージを表示します。

すべての数式がスピルした一覧を基準にしているため、新しいデータを追加すると、手動操作なしで集計表全体が更新されます。次は、数式だけで完全なピボット形式のレポートを作成します。