ピボットテーブルを作る前に、AIと元データの形を整える手順

先に「問い」を決め、割り当てメモにする

フィールドを動かして形を探す方法もありますが、問いが曖昧だと、どの形が正解か判断できません。

Microsoft のフィールド リストの説明では、4つのエリアは次のように説明されています。

• フィルター:ピボットテーブルの上に表示される絞り込み
• 列:上部に列ラベルとして並ぶ項目
• 行:左側に行ラベルとして並ぶ項目
• 値:集計された数値として表示される項目

同じページには、通常は数値以外のフィールドが行に、数値フィールドが値に自動で入るとあります。自動配置は問いを知りません。「何を、何ごとに比べたいか」は人が決める部分です。

問いは次の型に当てはめると書きやすくなります。

「(絞り込み条件) のうち、(行にしたい区分) ごと、(列にしたい区分) ごとの (足す/数える対象) を見たい」

次の表を上から順に埋めてください。右の列は説明のために用意した架空の例で、実際のデータを集計した結果ではありません。

• ① 問い(一文)、書く内容：何を知りたいか、説明用の例：支店ごと・月ごとの売上金額を見て、どこが伸びているか知りたい
• ② 値、書く内容：数値の列と、合計か個数か、説明用の例：「金額」の合計
• ③ 行、書く内容：縦に並べたい主な区分、説明用の例：支店
• ④ 列、書く内容：横に並べたい第二の区分(なければ空欄)、説明用の例：月
• ⑤ フィルター、書く内容：条件で切り替えたい項目、説明用の例：商品カテゴリ
• ⑥ 必要な列、書く内容：①〜⑤のために元データに要る列、説明用の例：日付、支店、商品カテゴリ、金額
• ⑦ 足りない列、書く内容：今ない列と作り方、説明用の例：「月」列(日付から作る)
• ⑧ 照合する数字、書く内容：完成後に突き合わせる元データ側の数字、説明用の例：金額列の総合計

行・列・フィルターで迷ったときの目安です(公式の規則ではなく、編集上の提案です)。

• 区分が10種類以上ある、目安：行に置く。列にすると横に長くなる
• 比べたい軸が3つある、目安：主な2つを行と列に、残りをフィルターに
• 全体と、切り替えた数字の両方を見たい、目安：フィルターに置く
• 「金額を足す」か「何件か」か、目安：足すなら合計、数えるなら個数。問いの動詞で決める

元データの事前チェック表

Microsoft のピボットテーブル作成の説明では、元データについて、各列に空白でない固有の見出しがあること、列内のデータ型を揃えること、空の行や列を入れないこと、Excel の表として書式設定することが挙げられています。これに、事務の現場でよく起きる崩れを編集部で足したのが次の表です。「出どころ」で公式の記述と編集上の追加を分けています。

• 見出しは1行か、OKの状態：1行目だけが見出し、出どころ：公式、直し方の例：2段見出しは1行にまとめる
• 見出しに空白・重複がないか、OKの状態：すべて入力済みで重複なし、出どころ：公式、直し方の例：「金額」が2つなら「税抜金額」「税込金額」に分ける
• 列ごとに型が揃っているか、OKの状態：日付列は日付だけ、金額列は数値だけ、出どころ：公式、直し方の例：「なし」「-」などを空白か0に統一
• 空の行・列がないか、OKの状態：範囲の途中に空白なし、出どころ：公式、直し方の例：空行を削除
• Excel の表になっているか、OKの状態：表として書式設定済み、出どころ：公式、直し方の例：範囲を選んで「テーブルとして書式設定」
• 合計行・小計行が混ざっていないか、OKの状態：明細だけ、出どころ：編集上の追加、直し方の例：集計行は別シートへ。残すと二重に数えられる
• セル結合がないか、OKの状態：結合なし、出どころ：編集上の追加、直し方の例：結合を解除し、各行に値を入れ直す
• 数字や日付が文字列になっていないか、OKの状態：数値が左寄せになっていない、出どころ：編集上の追加、直し方の例：値の変換、または書式を直して入れ直す
• 表記ゆれがないか、OKの状態：同じ意味は同じ文字列、出どころ：編集上の追加、直し方の例：「東京」「東京支店」を一方に統一
• 月が横に並んでいないか(横持ち)、OKの状態：1行1件、月は「月」列に入る、出どころ：編集上の追加、直し方の例：「1月」「2月」…の列を、月と金額の2列に組み替える

最後の「横持ち」は、作れはしても、問いが変わるたびに作り直しになりがちです。これは公式の要件ではなく、編集上の判断です。

AIに頼む範囲と依頼文

AIに任せるのは、割り当て案、崩れの見落とし探し、補助列の数式案です。次の線引きで渡します。

• 渡す:列見出し、問い、5〜10行ほどの説明用に置き換えたサンプル
• 渡さない:氏名、住所、取引先名、金額の実数など。仮名や架空の数字に置き換える

学習に使われない設定があっても、外部サービスへの入力が許されるとは限りません。取引先との契約や社内ルールは別に確認してください。

依頼文はそのままコピーして使えます。

あなたは表計算の整理を手伝うアシスタントです。
ピボットテーブルを作る前の準備を手伝ってください。

【知りたいこと(問い)】
(例)支店ごと・月ごとの売上金額を見て、どこが伸びているか知りたい

【列見出し】
日付 / 支店 / 担当者 / 商品カテゴリ / 数量 / 金額 / 区分

【サンプル(架空データ)】
(ここに5〜10行を貼る)

お願いしたいこと:
1. 問いに必要な列と、不要な列を分けてください。
2. 行・列・値・フィルターに置く項目の案を、理由を一行ずつ添えて出してください。値は合計か個数かも示してください。
3. サンプルから読み取れる崩れ(表記ゆれ、文字列の数字、合計行の混入、空白など)を、行番号つきで挙げてください。
4. 足したほうがよい列があれば作り方を説明してください。数式は、前提にしているセルの位置と、Excelかスプレッドシートかを明記してください。
5. 推測で言っていることと、サンプルから確実に分かることを分けて書いてください。

回答は、割り当てメモの空欄を埋める材料として使います。次の3点は、AIが自信ありげでも人が決めてください。

• 値を合計にするか個数にするか(問いの意味で決まります)
• 「表記ゆれ」とされた項目を同じものとして扱ってよいか
• 補助列の数式が正しいか(次の節と照合の節で確かめます)

補助列を足すときの数式案

問いに「月ごと」があるのに元データが日付だけなら、「月」の列を足す方法があります。A列が日付(日付として入力済み)なら、次のような数式が提案されることがあります。

=TEXT(A2,"yyyy-mm")

これは説明用の例で、この記事の作成過程では実機で動作確認していません。表示形式の指定は環境(言語設定など)で動きが変わる場合があるため、自分の表で数行試してください。日付が文字列になっていると、期待どおりに変換されないことがあります。

確認は2段階です。

1. 日付と月が対応しているか、無作為に5行ほどを目で見る
2. 空白や「#VALUE!」などのエラーが列全体に出ていないか見る

作成後の照合と、問いに合わないときの見直し

ピボットテーブルができたら、割り当てメモの⑧と突き合わせます。

• 値の総計が元データの合計と一致するか、方法：元の「金額」列の合計とピボットの総計を比べる、ずれたときに疑うこと：文字列の数字、合計行の混入、範囲の取りこぼし
• 件数が一致するか、方法：値を個数にして元の行数と比べる、ずれたときに疑うこと：空白行、範囲の切れ目
• 同じ意味の項目が分かれていないか、方法：行ラベルの一覧を見る、ずれたときに疑うこと：表記ゆれ、末尾の空白
• 「(空白)」の項目が出ていないか、方法：行・列ラベルを見る、ずれたときに疑うこと：未入力、結合セル

Microsoft の作成の説明には、データがテキストとして解釈されると、値が合計ではなく個数で集計される旨の記述があります。合計のつもりが件数になっていたら、その列が文字列になっていないか見てください。

同じページによると、元データを Excel の表にしておくと、追加した行がピボットテーブルに含まれ、通常の範囲では手動の更新が必要です。行を足したのに数字が変わらないときは、ピボットテーブル内で右クリックして「更新」を試します。

完成した表が問いに答えていないときは、項目をむやみに動かさず、次の順に戻ります。

1. 問いの一文を書き直す(比べたい軸が変わっていないか)
2. 割り当てメモの行・列・値・フィルターを見直す
3. 必要な列が元データにあるか確認し、なければ補助列を足す
4. それでも合わなければ、問いと現在の配置、実際の見え方をAIに伝えて代案を出してもらう(貼るのは置き換えたデータだけ)

AIの代案は、メモに書き込んでからもう一度照合してください。