集計しやすい表の作り方。AIと見直す結合セル・横持ち・見出しの問題

なぜ「見やすい表」は集計に向かないのか

Microsoftの公式ガイドピボットテーブルを作成してワークシート データを分析するでは、作成前のデータ準備として次の点が挙げられています。

• すべての列にヘッダーを付け、各列のラベルは重複させず、空白にしない
• 2行のヘッダーや結合セルは避ける
• 同じ列に日付とテキストなど、異なる種類のデータを混ぜない
• 空の行や列を作らない
• データをExcelの表として書式設定する

見やすさのための工夫(セルの結合、二段見出し、区切りの空白行)は、そのまま集計機能の入力条件に反します。報告書として整えた表をそのまま集計元にすると、範囲選択がずれる、項目が拾えないといった問題が起きることがあります。

Before/After:説明用の例

Before(見やすさ重視の表)

• 佐藤(2行を結合)、商品：商品A、4月：10、5月：12、6月：8
• ****、商品：商品B、4月：5、5月：7、6月：6
• 鈴木(2行を結合)、商品：商品A、4月：9、5月：11、6月：14
• ****、商品：商品B、4月：3、5月：4、6月：5

問題は3つあります。

1. 結合セル:「佐藤」が入っているのは上のセルだけで、2行目の担当欄は空白です。担当別に絞り込むと、商品Bの行が漏れます。
2. 横持ち:月が列になっているため、7月分は列を足す形になります。式や集計範囲も毎回直すことになります。
3. 意味が列名に埋まっている:「4月」が列見出しにあるので、「月で絞り込む」「月ごとの推移を出す」が素直にできません。

After(集計用の一覧)

• 2026-04、担当：佐藤、商品：商品A、数量：10
• 2026-05、担当：佐藤、商品：商品A、数量：12
• 2026-06、担当：佐藤、商品：商品A、数量：8
• 2026-04、担当：佐藤、商品：商品B、数量：5
• …(以下同様)、担当：、商品：、数量：

担当名は全行に入れ、月は「年月」という列の値にします。新しい月が増えても行を足すだけで済み、列の追加や式の修正は要りません。この形を一般に「縦持ち」と呼びます。

「2026-04」のような年月は、Excelが日付として解釈することがあります。入力後に表示を確認し、列の中身を日付かテキストのどちらかに揃えてください。

判断基準:直すべきか、そのままでよいか

• 印刷・PDFで見せるだけ、判断：そのままでよい
• 毎月行や列を足し、合計を出している、判断：縦持ちの一覧に直す
• フィルター、並べ替え、ピボットテーブルを使う、判断：結合解除と1行見出しは必須
• 他の人が入力を続ける、判断：入力用は縦持ち、見せる用は別シートに分ける

おすすめは「入力・集計用の一覧」と「見せる用の表」を分ける運用です。見せる用の表は、一覧を元にピボットテーブルなどで作ります。見やすさと集計のしやすさを両立でき、元の「見やすい表」の代わりにもなります。

作り直す前の設計チェックリスト

作業に入る前に、次の項目を確認します。AIに表を見せるときも、この答えを添えると提案の精度が上がりやすくなります。

1. この表で何を知りたいか:「担当別の月次合計」のように、集計の軸を書き出したか
2. 1行が何を表すか:「1回の販売」「1日の勤務」など、1行の単位を一文で言えるか
3. 見出しは1行か:二段見出しや結合された見出しを、1つの名前にまとめられるか
4. 列の中身は同じ種類か:同じ列に日付と文字、数値と「なし」などが混在していないか
5. 月や商品が列名になっていないか:なっているなら、「月」「商品」という列を作る
6. 結合セルや「上と同じ」の空白がないか:全行に値を入れる
7. 合計行・小計行・空白行が混ざっていないか:合計は一覧の外、または集計機能で出す
8. 元の表を残したか:変換前のファイルを別名で保存し、数字の突き合わせに使う
9. 入力する人が増えるか:増えるなら、入力できる値を決める(例:担当名の表記ゆれを防ぐ)

AIに頼む:渡す情報・依頼文・人の確認

実データを貼る必要はありません。列見出しと数行のダミー値があれば構造は伝わります。顧客名や金額などの機密は、「顧客A」「1000」のような架空の値に置き換えてください。

以下はExcelで作った集計表の構造です(値は説明用のダミー)。
集計やピボットテーブルに使える一覧形式に作り直したいです。

【現在の表】
(ここに表を貼る。結合セルは「佐藤(2行結合)」のように注記する)

【やりたい集計】
担当別・商品別・月別の数量合計

【お願い】
1. 集計の妨げになっている点を、結合セル/横持ち/見出し/空白行に分けて列挙
2. 縦持ちにした場合の列名と、各列に入れる値の例を表で提案
3. 元の表から新しい一覧へ移す手順を、Excelの操作で説明
4. 変換後に元の数字と一致しているか確認する方法(合計の突き合わせなど)

AIの回答は、次の点を人が確かめてから使います。

• 提案された列名が、自分の業務の言葉と合っているか
• 空白と「0」の区別が意味を持っていないか(未入力なのか実績ゼロなのか)
• 示された手順や式が、少量のダミーデータで正しく動くか

縦持ちへの変換と、変換後の検証

件数が少なければ、AIの提案を見ながら手で組み替えても構いません。月が多い、毎月更新するといった場合は、ExcelのPower Queryの「ピボット解除」が候補になります。Microsoftの列のピボット解除(Power Query)では、マトリックス状の表を、類似する値が1つの列に入る形に変換する機能として説明されています。対象としてExcel for Microsoft 365、Excel 2024、Excel 2021が挙げられており、方法は「列のピボット解除」「他の列のピボット解除」「選択した列のみのピボット解除」の3つです。

この例なら、動かさない列(担当・商品)を選び、残りの月の列を解除する方式が考えやすいでしょう。ただし、確認したのは機能の概要とメニュー名までです。月の列が増えたときの更新の挙動や、実際の画面での操作は、上のページと手元のExcelで確かめてください。

結合セルは、変換の前に解除して空白を埋めておきます。AIに「この列の空白を直前の値で埋める手順を教えて」と頼むと、手順を整理してもらえます。

変換が終わったら、次の3点を見ます。

1. 行数:元の表のデータセル数(担当×商品×月)と、一覧の行数が一致する
2. 合計:数量の総合計が、元の表の総合計と同じ
3. 抜け:担当や商品の列に空白が残っていない(フィルターで「空白」を確認)

この3点が合えば、ピボットテーブルを作る準備は整っています。