Excel スピルでクロス集計（データ更新が自動反映）を行う数式
この記事ではスピルでクロス集計を行う数式を紹介します。
この数式はクロス集計の元データが増えると、
クロス集計側のシートを編集しなくても、増えたデータが自動的に反映されます。
内容的にはピボットテーブルで出来ることと同様で、
そちらで十分であれば必ずしもスピルで実施する必要はありません。
この方法がピボットテーブルに比べて勝っている点は
そのため同じデータからクロス集計を複数行う場合や
データの更新し忘れが致命的な場合にはスピルの方が有利です。
注意点として最新の機能を使うためExcelのバージョンが古いと利用でない場合があります。
クロス集計のサンプル
手順（数式の説明）
クロス集計の縦方向をURLにしています。そのヘッダー行をA列に作成します。
LET関数なしの場合、A2セルに以下の数式を設定します。
=SORT(UNIQUE( FILTER(データ!B:B,(データ!B:B<>"")*(データ!B:B<>"URL")) ))
LET関数ありの場合、A2セルに以下の数式を設定します。
同じセル参照が多いため可能であればこちらが良いでしょう。
=LET( URL範囲,データ!B:B, SORT(UNIQUE( FILTER(URL範囲,(URL範囲<>"")*(URL範囲<>"URL")) )) )
FILTER関数はセル範囲から特定の条件を満たす行を抽出する関数です。
1つめの引数にセル範囲を指定し、2つめの引数に抽出条件を指定します。
条件の前半は空白セルの除外で、後半はヘッダー（項目）行の除外です
*は「かつ(AND)」の条件で両方を満たす必要があります。
(URL範囲<>"")*(URL範囲<>"URL")
これにより空白でもヘッダーでもない行を取得します。
UNIQUE関数はセル範囲の重複を除外する関数です。
SORT関数は指定範囲の並び替えを行う関数です。（並び替える必要がなければ不要です）
これにより縦方向（URL）のヘッダー列が完成します。
次に横方向の年月です。そのヘッダー行を1行めに作成します。
LET関数なしの場合、B1セルに以下の数式を設定します。
=TRANSPOSE(SORT(UNIQUE( FILTER(データ!A:A,(データ!A:A<>"")*(データ!A:A<>"月（年間）")) )))
LET関数ありの場合、B1セルに以下の数式を設定します。
=LET( 年月範囲,データ!A:A, TRANSPOSE(SORT(UNIQUE( FILTER(年月範囲,(年月範囲<>"")*(年月範囲<>"月（年間）")) ))) )
参照範囲と条件以外はURLの数式とほぼ同じですが、
最後にTRANSPOSE関数で縦方向から横方向に入れ替えています。
これにより横方向（年月）のヘッダー行が完成します。
最後に集計データの数式を作成します。B2セルに以下の数式を設定します。
=SUMIFS( データ!C:C, データ!A:A,B1#, データ!B:B,A2# )
B1#とA1#はセル単体ではなくスピル全体の範囲指定です。
これで完成です。A2、B1、B2の3セルのみの数式でクロス集計表が完成します。
もしデータシートに新しい年月やURLが追加されても
自動的にクロス集計表が拡充されます。（ただし手動集計にしていると自動ではなくなります）
SUMIFS関数で合計していますが、平均（AVERAGEIFS関数）、
最大(MAXIFS関数)、最小(MINIFS関数)、件数(COUNTIFS関数)でも同様に利用可能です。
GROUPBY関数やPIVOTBY関数で対応
2024年9月のアップデートで新たに追加されたGROUPBY関数やPIVOTBY関数でピボットテーブルを使わずに、更新の問題を解消できる場合があります。
集計の軸が1つの場合はGROUPBY関数、2つの場合はやPIVOTBY関数を使用します。
GROUPBY関数やPIVOTBY関数が使用できる場合は、こちらの方が効率的な場合があります。
Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。