Excelパワークエリでは、横に月が並ぶクロス集計表を、年月と金額が縦に並ぶデータベース形式へ変換できます。逆に、縦持ちの明細から年月を列へ展開することもできます。中心となる操作は［列のピボット解除］と［列のピボット］です。単に表の向きを変えるのではなく、「一行が何を表すか」「同じ組み合わせが複数行あるとき何をするか」を決める必要があります。変換前後の件数と合計を照合し、更新で新しい月列が追加されても動く手順にします。
クロス集計表と縦持ち表の違い
クロス集計表は、行に店舗や商品、列に4月・5月・6月、交点に売上金額を置いた形式です。人が月ごとの比較を読むには便利ですが、月が増えるたび列が増え、フィルター、結合、グラフ、ピボットテーブルの元データとして扱いにくくなります。
縦持ち表は、店舗、商品、年月、売上金額のように、年月を一列の値として持ちます。一行が「ある店舗・商品・年月の売上」を表します。列構成が固定されるため、月が増えても行が増えるだけで、集計、結合、更新に向きます。本記事ではこの形式を便宜上データベース形式と呼びますが、Excel表をデータベース製品そのものへ変えるわけではありません。
変換前の表を整える
- 一行目に一意な列見出しを置き、二段見出しや結合セルをなくす。
- 途中の空白行、説明行、小計行、総計行を元データから分ける。
- 店舗コード、商品コードなど識別列を明確にする。
- 月見出しの形式を統一し、年を省略して曖昧にしない。
- 数値セルへ「円」「未確定」などの文字を混在させない。
- 空白が0、未入力、対象外のどれかを業務ルールで決める。
元表を直接修正する前に別名保存します。帳票の見た目を残したい場合は、帳票シートを原本として保ち、作業用テーブルまたはPower Queryの変換で不要行を除きます。合計行を含めてピボット解除すると、明細と総計が混ざり二重計上になります。
クロス集計表をPower Queryへ取り込む
- クロス集計範囲の一セルを選び、Ctrl+TでExcelテーブルにします。
- ［先頭行をテーブルの見出しとして使用する］を確認します。
- ［データ］、［テーブルまたは範囲から］を選びます。
- Power Queryエディターで識別列、月列、値のプレビューを確認します。
- 自動追加された［変更された型］が見出しや値を誤解釈していないか確認します。
見出しが二段になっている場合、先に不要な上段を削除し、正しい行を［1行目をヘッダーとして使用］で昇格します。ただし「2026年」という上段と「4月」という下段を単純に片方だけ残すと年が失われます。作業用コピーで「2026-04」のような一意の見出しを作るか、変換手順として結合します。
その他の列のピボット解除を使う
- 店舗コード、店舗名、商品コードなど、縦持ち後も固定で残す識別列を選択します。
- 選択した見出しを右クリックし、［その他の列のピボット解除］を選びます。
- 月ごとの列見出しが［属性］列へ、交点の値が［値］列へ変わったことを確認します。
- ［属性］を［年月］、［値］を［売上金額］など意味の分かる名前へ変更します。
- 年月を日付、売上金額を数値など、用途に合うデータ型へ変換します。
Microsoft Learnは、ピボット解除で列見出しが［属性］、その下の値が［値］という二列の属性・値ペアになると説明しています。識別列を選んで［その他の列のピボット解除］を使う方法は、次月の列が追加されたときも、識別列以外を自動的に縦持ちへ取り込めるため定期更新に向きます。
三つのピボット解除を使い分ける
- ［列のピボット解除］：現在選んだ列を縦持ちにする。対象列を固定したい場合。
- ［その他の列のピボット解除］：現在選んだ識別列以外を縦持ちにする。新しい月列も取り込みたい場合。
- ［選択した列のみピボット解除］：選択列だけを対象として固定し、後で追加された未知の列を変換しない場合。
名称が似ていますが、更新時の新規列の扱いが違います。月が増える帳票では識別列を固定して［その他の列のピボット解除］が一般的です。一方、将来追加される「備考」列まで金額として取り込む危険があるため、入力表の列契約と見出し規則も管理します。
年月列を正しい型へ変換する
ピボット解除直後の［年月］は、元の列見出しなのでテキストになることがあります。「4月」だけでは年が分からず、並べ替えも文字順になる場合があります。元見出しを「2026-04-01」のように変換し、日付型へ設定します。見出しが日本語、英語月名、会計年度表記の場合は、ロケールと変換規則を明示します。
Microsoftのデータ型に関する公式説明は、ロケールが日付や数値の解釈へ影響するとしています。自動型判定で一部だけエラーになった場合、エラーを削除せず元の文字列を調べます。代表的な月、年度境界、うるう日を確認します。
空白・0・エラーを扱う
売上金額の空白が「取引なし」なのか「未入力」なのかで扱いが変わります。ピボット解除ではnullのセルが結果行として現れない場合があるため、期待行数を「識別行数×月数」と比較し、欠けた組み合わせを確認します。0は値として残り、nullとは異なります。空白を一律0へ置換すると未入力を売上なしと誤認するため、業務ルールに従います。
文字列やExcelエラーが金額列へ混じる場合は、元セル、年月、識別コードを残したエラー一覧を作ります。エラー行を削除して合計を合わせるのではなく、修正、除外、保留の件数と金額を記録します。
変換結果を検算する
- 元表の月別合計と、縦持ち表を年月で集計した合計が一致する。
- 元表の総合計と、縦持ち表の売上金額合計が一致する。
- 識別行数、月数、空白数から期待される行数を説明できる。
- 店舗コード、商品コード、年月の組み合わせに想定外の重複がない。
- 先頭月、最終月、0、負数、空白、エラーの代表例が正しく変換された。
縦持ち表をクロス集計へ戻す
- 縦持ち表で、一行を識別する店舗・商品などの列を確認します。
- 横方向の見出しにしたい［年月］列を選択します。
- ［変換］、［列のピボット］を選びます。
- ［値列］として［売上金額］を選びます。
- ［詳細設定］で、合計など必要な集計方法、または条件を満たす場合だけ［集計しない］を選びます。
- ［OK］を選び、年月が列見出しへ変わったことを確認します。
Power Queryの公式説明では、列のピボットは列内の一意な値を新しい列見出しにし、値列を交点へ配置します。既定では合計が選ばれる場合があり、合計、平均、最小、最大、行数などを選べます。表示用のクロス表で何を集計するかを明示します。
「集計しない」でエラーになる理由
店舗A・商品X・2026年4月という同じ行キーと列キーの組み合わせに売上金額が二行あると、一つの交点へ二つの値を置けません。［集計しない］は各組み合わせに一つだけ値があることを期待するため、公式例では要素が多すぎるというエラーになります。これはPower Queryの故障ではなく、データの粒度が出力表より細かいことを示します。
- 二行を合計するのが正しいなら、ピボット時に［合計］を選ぶ。
- 注文別に残すなら、注文番号を行キーへ追加する。
- 最新版だけを選ぶなら、更新日時と採用規則を決めてから一行へ絞る。
- 本当の取り込み重複なら、一意キーと証拠を確認して除外する。
エラーを消すためだけに重複行を削除したり、最大値を選んだりしません。どの値を残すかは業務上の意味で決め、集計方法をクエリ名や手順書へ記録します。
往復しても完全に元へ戻らない場合
ピボット解除とピボットは便利ですが、必ず可逆ではありません。ピボット時に合計すれば複数明細は一値へ統合され、元の行へ分解できません。nullの組み合わせ、元の列順、セル書式、コメント、数式、結合、色もデータ変換では保持されません。編集原本、変換後の分析表、配布用の見た目を別に管理します。
読み込みと更新
検算後、［ホーム］、［閉じて読み込む］または［閉じて次に読み込む］で新しいワークシートやデータモデルへ出力します。出力表を手修正しても更新で上書きされるため、変換はPower Queryの適用したステップへ追加します。翌月列を元表へ追加し、［すべて更新］で縦持ち表へ新しい年月が入るか、合計と行数が一致するかをテストします。
Microsoftのベストプラクティスに沿い、不要な行や列は早めに絞りますが、小計・エラー・期間外データを除く根拠を残します。データ量が大きい場合は適切なコネクターを使い、元データベースで実行できる処理かも確認します。
完了条件
変換の完了は、［属性］と［値］の二列ができた時点ではありません。一行の意味と識別キーが明確で、二段見出しや総計行が除かれ、新しい月列をどう扱うか決まり、年月と金額の型が正しく、null・0・エラーの方針があり、月別合計と総合計が一致し、逆変換時の重複を集計またはキー追加で説明でき、翌月追加後も更新できる状態です。
公式情報・参考資料
コメント