見出し画像
Photo by mitsuki_haruta

クロス集計（PIVOTBY）

にゃんねこ

担当×月、商品×地域…“2軸集計”を関数で返せると、ダッシュボード運用が壊れません。

管理職向けのダッシュボード作りで、現場が詰まりがちなポイントはだいたい同じです。

  • ピボットを作る→更新する→体裁が崩れる→直す

  • 月が増える、担当が増える、列が増えるたびに事故る

  • 「集計結果の参照」がズレて、数字が信用されなくなる

そこで使うのが PIVOTBY。
PIVOTBYは、行と列の2軸でグループ化し、集計表（クロス表）を 数式の結果としてスピル出力します。マイクロソフトサポート

  • 更新に追従する

  • “出力表”なので、参照（#）で別シートへ渡せる

  • 参照をXMATCH+INDEXで作れば、ダッシュボードが崩れにくい

この章では、担当×月 / 商品×地域 の2軸集計と、
崩れない参照で“管理職向けダッシュボードの土台”を作ります。

この章のゴール（今日できること）

  • PIVOTBYで2軸集計（担当×月、商品×地域）を作れる

  • 合計（総計）・並べ替え・フィルターを式に組み込める

  • ダッシュボード参照を「ズレない形」で作れる（XMATCH+INDEX）

  • 実務演習：管理職向けダッシュボードの土台を完成させる

1) PIVOTBYの基本（ピボットテーブルとの違い）

PIVOTBYは「ピボットテーブルに似た出力」を返しますが、ピボットテーブル機能そのものではありません。マイクロソフトサポート
つまり、集計結果が“表（配列）として返る” ので、Excelの関数設計（LET/スピル/#参照）にそのまま載せられます。

2) PIVOTBYの構文（まずはここだけ）

PIVOTBYの構文は以下です。マイクロソフトサポート

=PIVOTBY(row_fields, col_fields, values, function,
 [field_headers], [row_total_depth], [row_sort_order],
 [col_total_depth], [col_sort_order], [filter_array], [relative_to])

最初の4つだけで動きます。

  • row_fields：行方向のグループ（例：担当、商品）

  • col_fields：列方向のグループ（例：月、地域）

  • values：集計したい値（例：金額）

  • function：集計方法（SUM/AVERAGE/COUNT など）マイクロソフトサポート

3) 2軸集計の基本：担当×月、商品×地域

前提データ（おすすめ）

売上明細をテーブル化（Ctrl+T）して tblSales にします。列は例として以下：

  • 日付（日時でもOK）

  • 担当

  • 商品

  • 地域

  • 金額

テーブル化すると、行が増えても参照が追従し、運用が安定します。

3-1. 担当×月（売上合計）

“月”は日付から作ります。yyyy-mm 文字列なら並びも崩れにくいです。

=LET(
  m, TEXT(tblSales[日付], "yyyy-mm"),
  PIVOTBY(tblSales[担当], m, tblSales[金額], SUM)
)

これで「担当（行）×月（列）」のクロス集計がスピル出力されます。

3-2. 商品×地域（売上合計）

=PIVOTBY(tblSales[商品], tblSales[地域], tblSales[金額], SUM)

4) 小計・並べ替え（“管理職向け”に必要な最低限）

PIVOTBYは、合計（総計）や並べ替えも引数で制御できます。マイクロソフトサポート

4-1. 総計（行・列の合計）を出す

担当×月に総計を付ける例：

=LET(
  m, TEXT(tblSales[日付], "yyyy-mm"),
  PIVOTBY(tblSales[担当], m, tblSales[金額], SUM, , 1, , 1)
)

4-2. 並べ替え（売上の大きい順にする）

row_sort_order / col_sort_order は、対応するフィールド列、その後に値列が続く順で番号指定し、負数で降順にできます。マイクロソフトサポート

担当（行）を「総計の大きい順」にしたい場合（最もよく使う）：

=LET(
  m, TEXT(tblSales[日付], "yyyy-mm"),
  PIVOTBY(tblSales[担当], m, tblSales[金額], SUM, , 1, -2, 1)
)

  • -2：行側の並べ替えで「値（売上）」を降順にする、という指定（代表パターン）

4-3. 上位N（トップ10担当など）

PIVOTBYの出力から、行をTAKEで切り出します（合計行がある場合はDROPで調整）。

=LET(
  p, LET(m, TEXT(tblSales[日付], "yyyy-mm"),
         PIVOTBY(tblSales[担当], m, tblSales[金額], SUM, , 1, -2, 1)),
  header, TAKE(p, 1),
  body, DROP(p, 1),
  VSTACK(header, TAKE(body, 10))
)

5) 帳票参照の作り方（崩れない参照）

ダッシュボードが崩れる最大要因は「参照がズレる」ことです。
対策は、“座標（行/列）を名前で探してINDEXする” です。

ここでは、PIVOTBYの結果を Calc シートに置き、Dashboard が参照する設計にします。

5-1. まず、PIVOTBYはCalcシートの“出力専用エリア”へ

Calc!A2 に、担当×月のPIVOTBYを置く（出力先は右と下を空ける）。

=LET(
  m, TEXT(tblSales[日付], "yyyy-mm"),
  PIVOTBY(tblSales[担当], m, tblSales[金額], SUM, , 1, -2, 1)
)

この出力全体は Calc!A2# で参照できます（スピル参照）。

5-2. “担当名×月”の交点を、ズレずに取りに行く（XMATCH+INDEX）

Dashboard側で

  • 担当（例：E2）に「田中」

  • 月（例：F2）に「2026-01」

が入っているとします。
その交点の売上を返す式（例：Dashboard!G2）：

=LET(
  p, Calc!A2#,
  r, DROP(INDEX(p,,1),1),        // 行ヘッダー（担当一覧）：1列目、先頭行を除外
  c, DROP(INDEX(p,1,),1),        // 列ヘッダー（月一覧）：1行目、先頭列を除外
  ri, XMATCH($E$2, r),
  ci, XMATCH($F$2, c),
  INDEX(p, ri+1, ci+1)
)

この形の強み：

  • 担当の並びが変わってもOK（名前で探す）

  • 月が増えて列が伸びてもOK（ヘッダーで探す）

  • 参照が壊れにくい（ダッシュボード運用向き）

5-3. ダッシュボードは「参照だけ」にする（編集しない）

おすすめの3シート構成：

  • Data：元データ（貼るだけ）

  • Calc：集計（PIVOTBY/GROUPBYなど）

  • Dashboard：表示（参照だけ、装飾だけ）

“計算”と“見せ方”を分離すると、#SPILL! 事故が激減します。

6) 実務演習：管理職向けダッシュボードの土台作り（完成まで）

ここからは、最小構成で「管理職が見るべき要素」を作ります。

作るもの（例）：

  1. KPI：当月売上合計

  2. 担当×月のクロス表（トレンド）

  3. 商品×地域のクロス表（どこで何が売れているか）

  4. カード：特定担当の当月売上（崩れない参照）

Step1：当月の定義（Dashboard!B2に対象月）

Dashboard!B2 に 2026-01 の形式で入力（空欄なら当月でもOK）。

当月（空欄なら今日の月）を確定するセル（例：Dashboard!B3）：

=IF($B$2="", TEXT(TODAY(),"yyyy-mm"), $B$2)

Step2：Calcに「担当×月」クロス表を作る（対象月だけに絞ってもOK）

全期間で作ると重い場合は、対象月±数か月に絞ります（filter_arrayを使う）。
PIVOTBYは filter_array を受け取れます（TRUE/FALSEの列配列）。マイクロソフトサポート

例：対象月（Dashboard!B3）だけに絞って「担当×月（実質1列）」を出すのではなく、
ここではダッシュボード向けに 直近6か月 を例にします（手堅い運用）。

Calc!A2（担当×月、直近6か月）：

=LET(
  src, tblSales,
  m, TEXT(src[日付], "yyyy-mm"),
  latest, MAX(m),
  // 直近6か月（文字列比較ではなく日付で作るのが安全）
  last6_from, EDATE(DATEVALUE(latest&"-01"), -5),
  isLast6, src[日付] >= last6_from,
  PIVOTBY(src[担当], m, src[金額], SUM, , 1, -2, 1, , isLast6)
)

※環境によって DATEVALUE(latest&"-01") の解釈が揺れる場合があります。
その場合は「対象月を日付で持つ」（例：B2に日付）運用に寄せると安定します。

Step3：Calcに「商品×地域」クロス表を作る（当月だけ）

Calc!K2（商品×地域、当月）：

=LET(
  src, tblSales,
  tgt, Dashboard!$B$3,
  m, TEXT(src[日付], "yyyy-mm"),
  isTgt, m=tgt,
  PIVOTBY(src[商品], src[地域], src[金額], SUM, , 1, -2, 1, , isTgt)
)

Step4：Dashboardに集計表を“そのまま貼る”（#参照）

Dashboardの表エリアに、Calcの出力を参照するだけ。

担当×月（例：Dashboard!A8）：

=Calc!A2#

商品×地域（例：Dashboard!A30）：

=Calc!K2#

Step5：KPI（当月売上合計）を出す

Dashboard!E4（当月売上合計）：

=LET(
  src, tblSales,
  tgt, $B$3,
  m, TEXT(src[日付], "yyyy-mm"),
  IFERROR(SUM(FILTER(src[金額], m=tgt)), 0)
)

Step6：カード（特定担当の当月売上）を崩れない参照で出す

担当名を Dashboard!E6、対象月を Dashboard!B3 として、交点を取得：

=LET(
  p, Calc!A2#,
  r, DROP(INDEX(p,,1),1),
  c, DROP(INDEX(p,1,),1),
  ri, XMATCH($E$6, r),
  ci, XMATCH($B$3, c),
  INDEX(p, ri+1, ci+1)
)

これで「担当を変える」「月を変える」だけでカード値が更新されます。

7) よくあるつまずき（現場で止まらないために）

7-1. PIVOTBYが使えない

対応バージョンは Microsoft の案内（Excel for Microsoft 365 / Excel 2024 など）を確認してください。マイクロソフトサポート

7-2. #SPILL ! になる

出力先が塞がっています。Calc/Dashboardは“出力専用エリア”を作り、右と下に余白を取りましょう。

7-3. 月の並びが崩れる

yyyy-mm 形式にしておくと、文字列ソートでも時系列になりやすいです。
（さらに厳密にするなら、月キーを日付（各月1日）で持つ運用が安全です）

まとめ：PIVOTBY＋崩れない参照で、ダッシュボード運用が安定する

  • PIVOTBYで2軸集計（担当×月、商品×地域）を関数で返すマイクロソフトサポート

  • 合計・並べ替え・フィルターまで式に組み込めるマイクロソフトサポート

  • ダッシュボード参照は「XMATCHで座標を探してINDEX」するとズレない

  • “Calcで計算、Dashboardは参照だけ”にすると壊れない

いいなと思ったら応援しよう！