フィルターと置換機能を使って不揃いのデータを整える

Excelのフィルター機能は、データのチェックをして整えるときにも便利です。

ピボットテーブルの元表には、統一されたデータがなければ、分析もままなりません。

また、AccessからExcelのデータをインポートするときも、しっかりクレンジングされたデータが必要になります。

フィルターと置換機能を使って、データのばらつきを整える方法を紹介します。

フィルター機能の設定

下のような表があります。1000件のデータです。

表内のセルであれば、どこでもいいので1つのセルを選択しておきます。

ここでは、セル［A1］をアクティブにしています。

参考Excelが1つの表と認識するには、空白の行と列で囲まれている必要があります。

ただし、A列や1行目から入力している場合は、右端の列と、下端の行が空白になっていればOKです。

［データ］タブの［並べ替えとフィルター］グループにある［フィルター］をクリックします。

ポップヒントには、以下のように記されています。

参考ポップヒントからショートカットキーは、［Ctrl］+［Shift］+［L］というのが分かりますね。

［フィルター］ボタンをクリックすると、下のように1行目の項目名の右横に矢印ボタンが表示されます。

このボタンのことを［フィルターボタン］と呼びます。

フィルターの本来の機能は、目的のデータのみを絞り込んで表示するためのものですが、入力ミスなどによるデータのばらつきも発見することができます。

参考フィルターモードをオンにしても列見出しにフィルターボタンが表示されない場合は、以下の記事を参照してください。

フィルターボタンで不揃いのデータを探す

［販売店］フィールドのフィルターボタンをクリックしてみます。

下のように表示されます。

［検索］ボックスの下に並んでいるのが、販売店のリストになります。

同じ名称はまとめられるので、販売店の名称は1個ずつ表示されます。

ですが、［新宿１号店］と［新宿1号店］がありますね。

全角の数字［１］と半角の数字［1］が異なる項目として認識されています。

本当は、同じものでも、少しでも違うと別物と認識されるわけです。

データの抽出

［新宿１号店］は、［新宿1号店］に変更したいですね。

修正したい［新宿１号店］を抽出します。

［(すべて選択)］のチェックボックスをクリックしてオフにします。

すべてのチェックボックスがオフになります。

そして、［新宿１号店］のチェックボックスをクリックしてオンにします。

［OK］ボタンをクリックします。

［新宿１号店］のデータが抽出されます。

フィルターボタンは下のように変わります。

そして、ウィンドウ下のステータスバーには［1000レコード中 58個が見つかりました］と表示されています。

数個のデータ修正であれば、手入力でもいいのですが、数が多い場合は面倒ですね。

置換作業

置換機能を使います。

ここでは、フィルタリング（絞り込み）した状態で置換作業をしますが、フィルタリングは解除していてもOKです。

［販売店］のフィールド、D列を選択しておきます。

そして、［ホーム］タブの右端にある［検索と選択］をクリックして［置換］をクリックします。

ショートカットキーは、［Ctrl］+［H］ですね。

［検索と置換］ダイアログボックスが表示されます。

［検索する文字列］に修正したい文字列［新宿１号店］と入力して、［置換後の文字列］には統一したい［新宿1号店］と入力します。

［すべて置換］ボタンをクリックします。

参考［検索と置換］ダイアログボックスは、フィールドを選択しなければシート全体が対象になっています。範囲選択すると、その範囲内での置換になります。

ここでは、念のためフィールドを選択して操作しています。

もし、シート全体でも問題ない、もしくは、その方が効率的であれば、範囲選択する必要はありません。

下のようにメッセージウィンドウが表示されます。

ステータスバーに表示された件数と合っています。

［OK］ボタンをクリックします。

もし、置換件数が異なるようであれば、検索文字列の入力ミスや範囲選択にミスがあるかもしれませんので、見直してみてください。

長い文字列や入力しにくい文字列の場合は、セルの文字列をコピーして貼り付けしたほうがミスは少なくなると思います。

［検索と置換］ダイアログボックスも閉じます。

参考また、間違えて置換してしまったと気がついたら、クイックアクセスツールバーの［元に戻す］ボタンをクリックしてください。

置換前の状態に戻すことができます。

ショートカットキーは、［Ctrl］+［Z］ですね。

記号の［○］と漢数字の［〇］もよく混在していることがあります。

見た目では判別ができませんが、フィルターでは異なる値として認識されます。

記号の［○］と漢数字の［〇］が混在しているかもと思ったら、範囲選択して［太字］の書式設定をしてみてください。

フォントにもよりますが、［游ゴシック］や［メイリオ］では漢数字の［〇］は太字になります。

漢数字の［〇］を記号の［○］に修正するようにオートコレクトに登録することもできます。

リストの確認

フィルターのリストでデータが統一されたかどうかを確認します。

抽出されたままの状態で構いませんので、［販売店］のフィルターボタンをクリックします。

表示されたリストには、［新宿1号店］はありますが、［新宿１号店］の項目は消えていますね。これでOKです。

フィルターの解除

この一覧は、右下にマウスポインタを合わせて、大きさを調整できますので、表示しきれていない場合は、大きく表示するとリストが見やすくなります。

［"販売店"からフィルターをクリア］をクリックして、フィルターを解除して、すべてのデータを表示しておきます。

範囲選択も解除しておきます。

参考［(すべて選択)］のチェックボックスをオンにして、［OK］ボタンを押してもフィルタリングを解除することができます。

空白スペースに注意

［販売店］フィールドのフィルターボタンでリストを表示して、データをチェックした方法で他のフィールドもチェックしていきます。

［担当者］フィールドのフィルターボタンをクリックしてみます。

すると、リストに［川本］という同じ名前が2個表示されています。

リストをクリックして確認

こういう場合は、名前の末尾に全角の空白スペースがあります。

それを確認するには、リストに表示された2つの名前の箇所をクリックしてみてください。

2番目の［川本］の後ろには、［全角スペース］が入っていることが分かると思います。

末尾に全角スペースが入ったデータを抽出します。

［(すべて選択)］のチェックボックスをクリックしてオフにして、［川本　］のチェックボックスをクリックしてオンにします。

［OK］ボタンをクリックします。

データが抽出されます。

ここでは、一件、抽出されました。

末尾の空白スペースを削除します。

そして、修正できたかどうかを、フィルターボタンをクリックして確認します。

［川本］は1個になっていますね。

確認できたら、［"担当者"からフィルターをクリア］をクリックして、フィルターを解除しておきます。

注意ただ、文字列末尾の全角スペースはフィルターボタンでチェックできても、半角スペースはチェックできません。

ピボットテーブルでは、半角と全角スペースが区別されます。

セルの不要なスペースは削除

フィルター機能を使って、データをチェックしても、文字列の先頭の空白スペースや、文字列末尾の半角スペースはリストを見てもチェックすることができません。

たとえば、［担当者］フィールドの［木山］は先頭に［半角スペース］が入っています。

目視でも分かりますね。

ですが、フィルターボタンをクリックしても、別物とは認識されていません。

検索でチェック

［担当者］フィールドを選択して、［ホーム］タブの右端にある［検索と選択］をクリックして［検索］をクリックします。

参考ショートカットキーは、［Ctrl］+［F］ですね。

［検索と置換］ダイアログボックスの［検索］タブの［検索する文字列］のテキストボックスに空白スペースを入力します。

［次を検索］ボタンをクリックします。

セル［E13］がヒットします。

文字列の先頭に空白スペースが入っているセルですね。

再度、［次を検索］をクリックすると、［川本］と入力されたセル［E34］がヒットします。

先ほど、データをチェックしたはずなのに、と思うかもしれませんが、フィールドコントロールでチェックしたのは、［全角スペース］なんですね。

このセルには、末尾に［半角スペース］が入力されていました。

続けて、［次を検索］ボタンをクリックすると、セル［E35］のセルにも［半角スペース］が入力されていました。

全部で3つのセルに［半角スペース］が入力されていました。

置換で空白スペースを削除

空白スペースを置換して、削除します。

［検索と置換］ダイアログボックスの［置換］タブをクリックして、［検索する文字列］のテキストボックスに半角スペースを入力します。

［置換後の文字列］は空欄のままにします。

［すべて置換］ボタンをクリックします。

メッセージウィンドウが表示されます。

［OK］ボタンをクリックして閉じます。

［検索と置換］ダイアログボックスも閉じます。

参考全角スペースは、フィルター機能を使ってチェックして修正しましたが、実は、全角スペースも半角スペースも置換作業で削除することができます。

また、フィールドを選択して検索して、置換しなくても、シート全体で行ってもかまいません。

ただ、削除していい空白スペースかどうかは、先に確認したほうがいいと思います。

半角と全角を区別して置換

全角括弧と半角の括弧の混在もよくあります。

また、半角アルファベットと全角アルファベット、半角カタカナと全角カタカナもルールに従って置換して統一します。

半角と全角を区別して検索や置換したい場合は、［オプション］ボタンをクリックして、［半角と全角を区別する］のチェックボックスをオンにして、実行します。

既定では、オフになっています。

データの並べ替え

データを昇順、または降順に並べ替えることもデータのチェックには役立ちます。

不揃いに表現されたデータを見つけやすくなります。

［データ］タブの［並べ替えとフィルター］グループにある［昇順］、［降順］ボタンを使います。

参考項目行もデータとして認識されてしまうような場合は、データが入力されている行と異なる書式設定をしてみてください。

データと区別されて、並べ替えの対象にはならないと思います。（例えば、文字サイズ変更、太字設定、セルの色を付けるなど）

データのクレンジングについて

関数を使う方法もあります。データクレンジングに役立つ関数は、以下のページに記しています。

参考にしてください。

データクレンジングに必要な操作や機能

ピボットテーブルやAccessからデータをインポートする時など、元データがしっかりクレンジングされていないと正確なデータ分析はできません。

手間がかかるかもしれませんが、できるだけ正確に効率よくクレンジングできるよう工夫してみてください。

以下の本は、実務で出会う困ったなというケースがほぼ網羅されていると思います。おすすめです。

以下の記事も役立つと思います。