数式でピボット形式のレポートを作成する

ピボットテーブルはデータをクロス集計します。1つのカテゴリを行に、別のカテゴリを列に配置し、各交点に合計を表示します。典型的な例では、横方向にRegion、縦方向にQuarterを配置し、各セルにSalesを表示します。

ピボットテーブルは便利ですが、手動で更新する必要があり、固定された範囲に配置されます。一方、数式で作るピボットは、データが変わるたびに自動的に再構築されます。

このレッスンでは、行見出しと列見出しを配置し、すべての交点を自動的に計算するSUMIFS数式を本体部分に設定します。

Salesという名前のシートを使います。A列がRegion、B列がQuarter、C列がAmountで、2行目から500行目までデータが入っています。

作成するレポートは次のような構成です。

• 行ラベル：E列に各Regionを重複なしで並べます。
• 列ラベル：1行目のF列からI列に、Q1、Q2、Q3、Q4を並べます。
• 本体：RegionとQuarterの組み合わせごとのAmountの合計を表示します。

本体の各セルは、「この地域はこの四半期にいくら売り上げたか」という1つの問いに答えます。

行見出しは、重複のない地域の一覧です。UNIQUEとSORTを使うと、E列にスピルさせながら順序も維持できます。

E2に入力します。

これで、地域がE2から下方向へ自動的に表示されます。集計表の場合と同じく、この一覧がグリッド全体の基準になります。

列見出しは、行に沿って横方向に並ぶ四半期です。Q1、Q2、Q3、Q4を手入力することも、UNIQUEをTRANSPOSEで囲んで横方向にスピルさせることもできます。

F1に入力すると、重複のない四半期が上部に横並びで表示されます。

TRANSPOSEは縦方向の一覧を横方向に反転します。そのため、四半期の列が見出しの行になります。これで、グリッドの両方の軸が整いました。

次に、本体部分を埋めます。各セルでは、その行の地域とその列の四半期に該当する合計を求める必要があります。SUMIFSなら、2つの条件を簡単に処理できます。

最初の本体セルであるF2に入力します。

これは、Regionが左側のラベルと一致し、Quarterが上側の見出しと一致するAmountを読み取ります。ピボット表の1つの交点に対応する計算です。

数式をコピーしてグリッド全体に適用できるのは、ドル記号のおかげです。複合参照を確認しましょう。

• $E2は列をEに固定し、行は移動できるようにします。そのため、各行がそれぞれの地域を参照します。
• F$1は行を1に固定し、列は移動できるようにします。そのため、各列がそれぞれの四半期を参照します。
• $C$2:$C$500は、データ範囲が移動しないため、完全に固定します。

F2をすべての四半期の列へコピーし、さらにすべての地域の行へコピーすると、各セルの参照が自動的に正しく調整されます。

F2を正しく設定したら、そのセルを選択し、フィルハンドルを四半期の列全体に向かって右へドラッグします。続けて、地域の行全体に向かって下へドラッグします。相対参照の部分はExcelが自動的に書き換えます。

• セルG2では、Regionが$E2、QuarterがG$1になります。
• セルF3では、Regionが$E3、QuarterがF$1になります。

これで、すべての交点に合計が入った完全なクロス集計表が完成します。ピボットウィザードは必要ありません。Salesのデータが変わった瞬間に再計算されます。

実際のピボットテーブルには総合計が表示されます。右端にTotal列を、最下部にTotal行を追加し、それぞれの行や列を通常のSUMで合計します。

最初の地域の行合計は、最後の四半期の右隣の列に入力します。

列合計を求めるには、その四半期の本体セルを行方向に合計します。端に合計を表示するとレポートが完成した印象になり、読者は一目で数値を確認できます。

使用しているツールが対応していれば、スピル参照を直接SUMIFSに渡すことで、コピー操作を省けます。スピルした見出しを条件として使います。

この1つの数式で、すべての地域と四半期の交点を合計できます。

ここでE2#は縦方向の地域一覧、F1#は横方向の四半期一覧です。Excelはこれらを組み合わせて、1回の操作で完全なグリッドを作成します。ドラッグする方法のほうが互換性は高いですが、こちらは現代的で洗練された方法です。

金額だけでなく構成比も表示すると、レポートからより多くの情報を読み取れるようになります。各地域の合計が総合計に占める割合を示す列を追加します。

地域の行合計がJ2にあり、総合計がJ10にある場合は、次のように入力します。

総合計を$J$10で固定すると、同じ分母で常に割り算をしながら、数式をすべての地域へ下方向にコピーできます。この列をパーセンテージとして表示すれば、どの地域の割合が大きいかをすぐに確認できます。

いくつかの習慣を守ると、数式で作るピボットを安定して運用できます。

• 2行目から500行目までのように、余裕を持った範囲全体を参照し、新しい行も含まれるようにします。
• データ範囲は完全な$参照で固定し、移動させるのは見出しの参照だけにします。
• スピルした見出しや合計が広がれるように、下と右に空白を残します。

適切に作成すれば、このレポートのメンテナンスは不要です。新しい売上を入力するだけで、グリッド、合計、ラベルがすべて自動的に更新されます。

数式で作るピボットを支える複合参照について、理解度を確認しましょう。

数式だけでピボットテーブルを再現しました。

• UNIQUEとSORTで、スピルする列に行見出しを作成しました。
• TRANSPOSEで、列見出しを行方向に並べました。
• 複合参照$E2とF$1を使ったSUMIFSで、ドラッグ操作またはE2#やF1#などのスピル参照により、すべての交点を埋めました。
• SUMで総合計を端に追加しました。

グリッド全体が常に最新の状態に再計算されます。次は、指標を操作するドロップダウンを使って、ダッシュボードをインタラクティブにします。