Excelで見た目は同じなのに一致しない。空白・重複データをまとめて整えるアプリを作りました

Excelを使っていて、こんなことはないでしょうか。

同じ文字に見える。

なのに、検索すると一致しない。

VLOOKUPやXLOOKUPでもヒットしない。

見比べても、

「いや、どう見ても同じじゃん……」

となる。

例えば、画面上ではどちらも

田中

に見えていても、実際のデータが

田中

と

田中

だったら、Excel上では別の文字列として扱われます。

後ろにたった1個、見えないスペースが入っているだけです。

こういう小さな違いは、データが数件ならすぐ直せます。

でも100行、1000行と増えてくると、

「どこがおかしいのか探す作業」

そのものが面倒になってきます。

今回は、この「ExcelやCSVを集計する前の地味な掃除」をまとめてできるWindowsアプリを作りました。

その前に、そもそもなぜこういうことが起こるのかを整理してみます。

見た目が同じでも、データとしては違うことがある

例えば、この2つです。

田中

田中

見た目ではほとんど分かりません。

でも2つ目には、末尾にスペースがあります。

ほかにも、

佐藤

佐藤

のように先頭にスペースが入っていたり、

半角スペースではなく全角スペースが混ざっていたりすることがあります。

人間が見ると同じように見えても、PCにとっては別の文字列です。

だから、

「一覧には絶対あるのにXLOOKUPで見つからない」

「COUNTIFの件数が思ったより少ない」

といったことが起こります。

データが汚れる原因は、入力ミスだけではない

こういうデータを見ると、

「入力した人が適当だったのでは？」

と思いたくなるのですが、必ずしもそうとは限りません。

Excelに手入力したデータだけでなく、

Webシステムから出力したCSV。

アンケート結果。

別のExcelからコピーしたデータ。

複数人で編集した表。

外部ソフトから書き出した一覧。

こうしたファイルを組み合わせていると、少しずつ表記が混ざることがあります。

さらに厄介なのが、

見た目では気付きにくいこと。

Excelのセルを眺めても、

田中

と

田中

の違いはほぼ分かりません。

重複データも地味に厄介

スペースだけではありません。

例えば、

A001　田中　営業部

A001　田中　営業部

という完全に同じ行が2つ入っていたら、単純な集計では2件としてカウントされます。

本当は1人なのに2人。

売上データなら1件なのに2件。

もちろん、「同じ内容だから重複」とは限らないデータもあります。

だからこそ、

勝手に削除するのではなく、まず重複が何件あるのか確認する

ことが大切です。

空白行も、数行なら気にならない

表の途中にこんな空白が入っていることもあります。

A001　田中　営業部

A002　佐藤　開発部

A003　鈴木　研究部

1行なら手作業で削除できます。

でも大きなCSVでは、

「空白行が何個あるのか」

を探すところから始まります。

こういう作業って、一つひとつは簡単なんですよね。

難しい処理ではありません。

でも、

簡単な作業が大量にあると急に面倒になる。

TomoLabで最近ツールを作っていて、何度もこの問題に行き着きます。

Excelだけでも直せる

もちろん、専用アプリがないと解決できない問題ではありません。

例えば前後の余分なスペースなら、ExcelのTRIM関数を使えます。

=TRIM(A2)

重複データなら、

データ → 重複の削除

でも処理できます。

空白行もフィルターなどを使えば削除できます。

なので、数十行程度のデータをたまに直すだけなら、Excelだけで十分だと思います。

問題は、

毎回やる場合です。

ファイルを開いて、

余分なスペースがないか調べて、

重複を確認して、

空白行を探して、

関数を作って、

値に戻して……

本来やりたいのは、その後の集計や分析なのに、

分析を始める前の準備に時間を取られる。

ここをどうにかしたいと思いました。

そこで「データを直す前に診断する」アプリを作った

今回作ったのが、

▶ TomoLab Data Cleaner v1.1｜BOOTH

です。

ExcelやCSVを読み込んで、

まず、

「このデータに何があるのか」

を確認します。

いきなり自動修正はしません。

ここはかなり意識しました。

ファイルを選んで「診断する」を押すと、行数や列数に加えて、空白行・完全重複行・前後にスペースがあるセルなどを確認できます。

例えば、

というように表示されます。

なぜ「自動で全部きれいにする」にしなかったのか

最初は、

ワンクリックで全部きれいにする

でもいいかなと思いました。

でも、実際のデータを考えると少し怖いです。

例えば、

A  B

という2つのスペースが、

入力ミスなのか。

意図的なのか。

アプリ側には判断できません。

重複行も同じです。

同じ内容の行が2つあるからといって、必ず削除していいとは限りません。

だからこのアプリでは、

診断する

↓

直す内容を自分で選ぶ

↓

処理後を確認する

↓

保存する

という順番にしました。

必要なものだけ選んで整える

現在のバージョンでは、主に次の処理を選べます。

• セル前後の余分なスペースを削除

• 全角スペースを通常のスペースに統一

• 連続した半角スペースを1つにする

• 完全に空の行を削除

• 完全に同じ重複行を削除

例えば、

「 佐藤 」

なら、

「佐藤」

へ。

でも、自分が使いたくない処理はOFFにできます。

全部を強制的に実行しない

のがポイントです。

保存する前にBefore / Afterを見る

データを扱うツールで一番怖いのは、

「実行したけど、何が変わったのか分からない」

ことだと思います。

なので、保存前に

処理前

と

処理後

を切り替えて確認できるようにしました。

さらに、

「何セル変更したか」

「空白行を何行削除したか」

「重複行を何行削除したか」

も表示します。

「何を変えたか」も表示する

今回さらに入れたのが、

変更履歴

です。

例えば、

3行目・1列目：「 佐藤」→「佐藤」

のように表示します。

空白行を削除した場合も、

7行目：完全に空の行のため削除

のように分かります。

自動化ツールだからこそ、

処理をブラックボックスにしない

ようにしました。

元のExcelは上書きしない

これも重要です。

例えば、

sales_data.xlsx

を読み込んだ場合。

処理後は、

sales_data_cleaned.xlsx

のように、別のファイルとして保存できます。

元データを直接変更する仕様にはしていません。

「やっぱり元の方がよかった」

となっても、元ファイルは残っています。

ファイルはPCの外へ送らない

ExcelやCSVには、

仕事のデータ。

顧客情報。

研究データ。

売上データ。

などが含まれることがあります。

そのためTomoLab Data Cleanerでは、

Excel / CSVをPC内だけで処理します。

ファイルを外部サーバーへアップロードする機能はありません。

オンラインツールを使いたくないデータでも扱えるようにしました。

どんな人向け？

個人的には、このアプリが一番向いているのは、

「CSVを出力して、Excelで集計する人」

だと思っています。

例えば、

業務システムからCSVを出す。

↓

Excelで開く。

↓

別の一覧とXLOOKUPする。

↓

ピボットテーブルで集計する。

このような作業をしていると、

「なぜか一致しない」

「件数がおかしい」

という問題が起こることがあります。

そんなときに、

集計を始める前のチェック

として使うイメージです。

研究でExcelやCSVを扱う場合も同じで、解析へ進む前に表データを一度確認したいときに使えます。

逆に、向いていないExcelもあります

注意点もあります。

▶ TomoLab Data Cleaner v1.1｜BOOTHは、

集計・分析用のデータ表

を対象にしています。

例えば、

セルを大量に結合して作った帳票。

複雑なレイアウトの見積書。

特殊な数式や外部参照が大量に入ったExcel。

こういったファイルを「きれいに整形する」ためのアプリではありません。

特に空白行や重複行を削除すると、行自体が詰まります。

なので、

1行＝1件

のような表形式のデータに向いています。

▶ TomoLab Data Cleaner v1.1｜BOOTH

今回作ったアプリはこちらです。

▶ TomoLab Data Cleaner v1.1｜BOOTH

Windows向け・買い切り 680円

Excel / CSVに対応しています。

主な特徴は、

診断 → 処理選択 → Before / After → 変更履歴 → 別ファイル保存

という流れです。

「全部自動できれいにする」よりも、

何が問題なのか分かった上で、安全に直せること

を優先しました。

▶ TomoLab Data Cleaner v1.1｜BOOTH

「見た目は同じ」を疑ってみる

Excelで検索や照合がうまくいかないと、

関数が間違っているのかな。

参照範囲がおかしいのかな。

と思って、数式ばかり確認してしまうことがあります。

でも原因が、

末尾に入った1個のスペース

だったりします。

Excelで、

となったら、

一度データそのものを疑ってみると解決するかもしれません。

そして、その確認を毎回やるのが面倒なら、今回のツールを使ってみてください。

TomoLabについて

TomoLabでは、

「1回なら簡単。でも何十回もやると面倒。」

そんなPC作業を少し楽にするWindowsツールを作っています。

今回も、難しいことを自動化したというより、

本来やりたい作業へ進む前の面倒を減らす

ために作りました。

ExcelやCSVについて、

「これも毎回直すのが面倒」

という作業があれば、コメントで教えてもらえるとうれしいです。

今後のアップデートの参考にします。