田村憲孝 他部署から届いたExcelの売上表を開いたら、月ごとにセルが結合され、途中に小計行が挟まり、担当者名は「山田」と「山田 」(後ろに空白)が混在している。集計を始める前に、まず表の直しで30分。データをもらうたびに、この作り直しから始めている方は多いのではないでしょうか。
この記事では、特にAIには読み込ませにくい「崩れた表」を、Excelに組み込まれているCopilotで集計に使える形へ直す手順を紹介します。前回の記事売上データの集計・分析をExcelのCopilotに任せる手順では、集計の前提となるデータの形(1行1件・見出し行・テーブル化)を確認しました。今回はその正しい形に修正する作業自体をCopilotにやってもらおうということです。Copilotの画面の開き方やライセンスはシリーズ第1回の全体ガイドにまとめています。
準備:元ファイルのコピーで作業する
最初にやることはCopilotの操作ではありません。元ファイルをコピーして、コピーの側で作業してください。今回の手順は表の行を消したり値を書き換えたりする作業なので、失敗してもいつでも元に戻れる状態を先に作っておきます。修正が終わって検算まで済んだら、コピーを正式版に差し替えます。
※あわせて、Copilotに入力してよいデータかどうかの確認も前回の記事「売上データの集計・分析をExcelのCopilotに任せる手順」と同様です。顧客名や取引先の実名を含む表を扱ってよいかは、あなたの会社のルールが最優先です。
手順1:「どこが崩れているか」をCopilotに出力させる
修正する箇所を自分で探す前に、「チャットのみ」モード(ブックを変更せず、回答だけを受け取るモード)で、表の問題点を出力させます。
この表をピボットテーブルで集計できる形にしたいです。
修正するべき点を、優先順に一覧で教えてください。
「小計行が混ざっている」「B列に結合セルがある」「担当者名の表記が揃っていない」のように、修正点の一覧が出力されます。自分の目では見落としやすい「数字に見えて文字列扱いのセル」のような問題も、ここで挙がってくることがあります。この一覧が、このあとの作業で活かせるチェックリストになります。
手順2:セル結合だけは手作業で解除する
セル結合の解除だけはCopilotに任せず、手作業をおすすめします。結合セルがあると、Copilotが「どこからどこまでが表か」「どの行がどの月のデータか」を正しく認識できず、あなたの意図とは異なる修正となるケースがあるためです。修正してもらうAIの視界を、先に確保しておくイメージです。
操作は1分で終わります。表全体を選択して、「ホーム」タブの「セルを結合して中央揃え」をクリックして解除します。
解除すると、結合されていた範囲の先頭以外のセルが空白になります。この空白の穴埋め以降は、Copilotの出番です。モードを「編集を許可する」に切り替えて、次のように指示します。
B列の空白セルを、それぞれすぐ上の値で埋めてください。
手順3:小計行を削除して「1行1件」にする
続けて「編集を許可する」モードのまま、表の中に挟まっている小計行・合計行を削除します。
「小計」「合計」を含む行をすべて削除して、1行に1つの取引だけが並ぶ形にしてください。
小計行は、人が紙で見るときには便利ですが、集計の元データとしては、1件ずつの明細の間に「すでに足し合わせた結果の行」が混ざっている状態です。そのまま集計すると同じ金額が二重に数えられる原因になるため、元データからは外します。小計はこのあとCopilotに集計させれば数秒で再現できるので、消してしまって問題ありません。
手順4:表記ゆれと文字列の数値は「データのクリーニング」で直す
ここからは、Copilotの専用機能「データのクリーニング」を使います。場所はリボンの「データ」タブの中です。下の画面のように、ウィンドウの幅によっては「データツール」ボタンの中にまとまっているので、見当たらないときはそちらを開いてみてください。
ボタンを押すと、Copilotが表を調べて、次の3種類の問題を検出します。
- 表記のゆれ:同じ値のはずのセルで大文字・小文字や記号の使い方が揃っていない(例:「ABC Corp」と「abc corp.」)
- 数値の形式の混在:同じ列に数値のセルと文字列扱いのセルが混ざっている(SUMで合計すると文字列のセルだけ無視される、集計トラブルの定番です)
- 余分な空白:値の前後や間に入り込んだ不要なスペース(「山田」と「山田 」が別人扱いされる原因)
検出された候補は画面の右側に表示され、1件ずつ内容を確認して「適用」または「無視」を選べます。勝手に全部書き換えられるのではなく、人が確認してから修正される設計です。
なお、この機能は英語のデータで最も精度が出るように作られており、日本語の表記ゆれ(全角・半角の混在など)は検出されない場合があります。検出されなかったものは、次の手順のチャット指示で修正します。
※お使いの環境に「データのクリーニング」がまだ表示されない場合も、この手順は飛ばして構いません。次の手順5のチャット指示で修正できます。
手順5:日本語の表記ゆれはチャットで指示して直す
「データのクリーニング」で拾いきれなかった日本語のゆれは、「編集を許可する」モードで個別に指示します。
担当者列の表記を統一してください。
・「山田」「山田さん」「ヤマダ」→「山田」
・全角スペース・半角スペースはすべて削除
どう統一するかの基準(この例では「姓のみ・スペースなし」)を人が決めて、置き換えの作業をCopilotに任せます。基準を伝えずに「表記を統一して」とだけ指示すると、意図と違う基準で編集されることがあるため、人間の意図が伝わる対応表の形で書いて伝えるのが確実です。
仕上げ:テーブル化して検算する(ここは人の仕事です)
最後に、修正した表をテーブルに変換します(表の中のセルを選んで Ctrl+T)。これで前回の記事の「準備」の3条件(1行1件・見出し行・テーブル化)がすべて揃い、集計に使える形になりました。
締めくくりに、修正で元のデータが欠けたり変わったりしていないかを確認します。
- 件数:修正後の行数が「元の行数-削除した小計行の数」と一致するか
- 金額:金額列の合計(範囲選択してステータスバーで見える合計)が、修正前のファイルの明細行の合計と一致するか
行の削除や値の置き換えを伴う作業なので、ここでの検算は集計のとき以上に大切です。表の形を修正する作業はCopilotに任せられますが、「修正した後も中身が正しい状態で維持されているか」を確かめるのは人間のタスクです。
ここまで整えば、あとは前回の記事の手順そのままに、集計表とグラフの作成までCopilotに依頼できます。
よくある質問
Q1. 「データのクリーニング」ボタンが見つかりません。
Copilotのライセンスが有効なMicrosoft 365で、リボンの「データ」タブに表示されます。ウィンドウの幅が狭いと「データツール」ボタンの中にまとまっているので、まずそちらを開いてみてください(手順4の画面を参照)。Web版のExcelから先行して提供された機能のため、お使いの環境によってはまだ表示されない場合もあります。その場合はブラウザーでExcelを開いて確認するか、手順5のチャット指示で同様の修正作業をしてください。Copilotが使える環境かどうかの確認方法は第1回の記事にまとめています。
Q2. 表記ゆれがあるはずなのに検出されません。
「データのクリーニング」は英語のデータで最も精度が出る設計のため、日本語のゆれ(全角・半角、送りがななど)は検出されないことがあります。その場合は手順5のように、統一の基準を対応表で示してチャットから指示してください。
Q3. Copilotの修正で表が崩れてしまいました。
まずは Ctrl+Z(元に戻す)で修正前に戻せます。戻せない状態になっても、この記事の最初の手順どおり元ファイルのコピーで作業していれば、元ファイルは無傷です。修正のやり直しは、一度に全部を指示するのではなく、この記事の手順のように「構造→表記」と1段階ずつ行うと安定します。
崩れた表を修正する作業は、これまで「Excelに詳しい人が時間をかけてマニュアルで行う」ものでした。いまは、問題点の洗い出しから小計行の削除、表記ゆれの統一までCopilotに任せられます。人の仕事は、最初にバックアップ用のコピーを取ること、統一の修正基準を決めること、最後の検算の3つです。まずは手元にある「このExcelいったい誰がつくったんよ。。。」な感じのファイルをコピーして、手順1の診断から試してみてください。
株式会社ウェブタイガーでは、CopilotやChatGPTなどの生成AIを業務で使えるようになる企業研修を行っています。Excelでのデータ整形・集計を含む実践型の内容は研修・セミナーのご案内をご覧ください。