リンクユニット

Windows10 & Office2016 Excel2016

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

投稿日:2018年8月7日 更新日:

Excelのフィルター機能は、データのチェックをして整えるときにも便利です。ピボットテーブルの元表には、統一されたデータがなければ、分析もままなりません。また、Accessからインポートするときも、しっかりクレンジングされたデータが必要になります。

テーブルやピボットテーブルに変換する時の注意点

Excelのテーブルやピボットテーブルは、データベースとしての管理が簡単にできるようになる、とても便利な機能です。でも、既存の表をテーブルや ...

続きを見る

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

フィルター機能の設定

下のような表があります。1000件のデータです。表内のセルであれば、どこでもいいので1つのセルを選択しておきます。ここでは、セル[A1]をアクティブにしています。

参考Excelが一つの表と認識するには、空白の行と列で囲まれている必要があります。ただし、A列や1行目から入力している場合は、右端の列と、下端の行が空白になっていればOKです。
サンプルデータ

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

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

選択したセルにフィルターを適用します。列見出しの矢印をクリックして、データを絞り込みます。

参考ショートカットキーは、[Ctrl]+[Shift]+[L]ですね。

[データ]タブの[フィルター]

[フィルター]ボタンをクリックすると、下のように1行目の項目名の右横に矢印ボタンが表示されます。このボタンのことを[フィルターコントロール]と呼びます。

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

フィルターコントロールで不揃いのデータを探す

[販売店]フィールドのフィルターコントロールをクリックしてみます。下のように表示されます。[検索]ボックスの下に並んでいるのが、販売店のリストになります。同じ名称はまとめられるので、販売店の名称は1個ずつ表示されます。

ですが、[新宿1号店]と[新宿1号店]がありますね。全角の数字[1]と半角の数字[1]が異なる項目として認識されています。本当は、同じものでも、少しでも違うと別物と認識されるわけです。
販売店フィールドのフィルターコントロールをクリック

データの抽出

[新宿1号店]は、[新宿1号店]に変更したいですね。修正したい[新宿1号店]を抽出します。

[(すべて選択)]のチェックボックスをクリックしてオフにします。すべてのチェックボックスがオフになります。
すべて選択のチェックボックスをオフに

そして、[新宿1号店]のチェックボックスをクリックしてオンにします。[OK]ボタンをクリックします。
チェックボックスをオンにしてデータを抽出

[新宿1号店]のデータが抽出されます。フィルターコントロールのボタンは下のように変わります。

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

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

置換作業

置換機能を使います。参考ここでは、フィルタリング(絞り込み)した状態で置換作業をしますが、フィルタリングは解除していてもOKです。

[販売店]のフィールド、D列を選択しておきます。そして、[ホーム]タブの右端にある[検索と選択]をクリックして[置換]をクリックします。

参考ショートカットキーは、[Ctrl]+[H]ですね。
[ホーム]タブの[検索と置換]

[検索と置換]ダイアログボックスが表示されます。[検索する文字列]に修正したい文字列[新宿1号店]と入力して、[置換後の文字列]には統一したい[新宿1号店]と入力します。[すべて置換]ボタンをクリックします。

参考[検索と置換]ダイアログボックスは、フィールドを選択しなければシート全体が対象になっています。範囲選択すると、その範囲内での置換になります。ここでは、念のためフィールドを選択して操作しています。もし、シート全体でも問題ない、もしくは、その方が効率的であれば、範囲選択する必要はありません。
[検索と置換]ダイアログボックス

下のようにメッセージウィンドウが表示されます。ステータスバーに表示された件数と合っています。[OK]ボタンをクリックします。
置換実行後

もし、置換件数が異なるようであれば、検索文字列の入力ミスや範囲選択にミスがあるかもしれませんので、見直してみてください。長い文字列や入力しにくい文字列の場合は、セルの文字列をコピーして貼り付けしたほうがミスは少なくなると思います。

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

参考また、間違えて置換してしまったと気がついたら、クイックアクセスツールバーの[元に戻す]ボタンをクリックしてください。置換前の状態に戻すことができます。ショートカットキーは、[Ctrl]+[Z]ですね。
元に戻すボタン

リストの確認

フィルターのリストでデータが統一されたかどうかを確認します。抽出されたままの状態で構いませんので、[販売店]のフィルターコントロールボタンをクリックします。表示されたリストには、[新宿1号店]はありますが、[新宿1号店]の項目は消えていますね。これでOKです。
フィルターコントロールで確認

フィルターの解除

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

["販売店"からフィルターをクリア]をクリックして、フィルターを解除して、すべてのデータを表示しておきます。範囲選択も解除しておきます。

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

空白スペースに注意

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

[担当者]フィールドのフィルターコントロールをクリックしてみます。すると、リストに[川本]という同じ名前が2個表示されています。
[担当者]のフィルターコントロールをクリック

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

こういう場合は、名前の末尾に全角の空白スペースがあります。それを確認するには、リストに表示された2つの名前の箇所をクリックしてみてください。

2番目の[川本]の後ろには、[全角スペース]が入っていることが分かると思います。
リストを選択して確認

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

[(すべて選択)]のチェックボックスをクリックしてオフにして、[川本 ]のチェックボックスをクリックしてオンにします。[OK]ボタンをクリックします。
データの抽出

データが抽出されます。ここでは、一件、抽出されました。末尾の空白スペースを削除します。
空白スペースを削除

そして、修正できたかどうかを、フィルターコントロールをクリックして確認します。[川本]は1個になっていますね。

確認できたら、["担当者"からフィルターをクリア]をクリックして、フィルターを解除しておきます。
フィルターコントロールをクリックしてリストを確認

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

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

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

たとえば、[担当者]フィールドの[木山]は先頭に[半角スペース]が入っています。目視でも分かりますね。
データを目視確認

ですが、フィルターコントロールをクリックしても、別物とは認識されていません。
[担当者]フィールドのフィルターコントロールをクリック

検索でチェック

[担当者]フィールドを選択して、[ホーム]タブの右端にある[検索と選択]をクリックして[検索]をクリックします。参考ショートカットキーは、[Ctrl]+[F]ですね。
[ホーム]タブの[検索と選択]

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

セル[E13]がヒットします。文字列の先頭に空白スペースが入っているセルですね。
検索結果

再度、[次を検索]をクリックすると、[川本]と入力されたセル[E34]がヒットします。先ほど、データをチェックしたはずなのに、と思うかもしれませんが、フィールドコントロールでチェックしたのは、[全角スペース]なんですね。

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

続けて、[次を検索]ボタンをクリックすると、セル[E35]のセルにも[半角スペース]が入力されていました。全部で3つのセルに[半角スペース]が入力されていました。
検索結果

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

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

[検索と置換]ダイアログボックスの[置換]タブをクリックして、[検索する文字列]のテキストボックスに半角スペースを入力します。[置換後の文字列]は空欄のままにします。[すべて置換]ボタンをクリックします。
半角スペースを置換

メッセージウィンドウが表示されます。[OK]ボタンをクリックして閉じます。[検索と置換]ダイアログボックスも閉じます。
置換実行後

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

また、フィールドを選択して検索して、置換しなくても、シート全体で行ってもかまいません。ただ、削除していい空白スペースかどうかは、先に確認したほうがいいと思います。

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

全角括弧と半角の括弧の混在もよくあります。また、半角アルファベットと全角アルファベット、半角カタカナと全角カタカナもルールに従って置換して統一します。

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

既定では、オフになっています。
[検索と置換]ダイアログボックスのオプション

並べ替え

データを昇順、または降順に並べ替えることもデータのチェックには役立ちます。不揃いに表現されたデータを見つけやすくなります。

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

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

データクレンジングに必要な操作や機能 : 初心者のためのOffice講座-SupportingBlog1

ピボットテーブルやAccessからデータをインポートする時など、元データがしっかりクレンジングされていないと正確なデータ分析はできません。手間がかかるかもしれませんが、出来るだけ正確に効率よくクレンジングできるよう工夫してみてください。

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

【重複の削除】は1番目の値が残る、データ末尾の空白には注意

Excel2007以降には、[データ]タブに[重複の削除]というコマンドがあります。 ポップヒントには、以下のように記されています。重複する ...

続きを見る

2つのセルの文字列を比較して同じ文字列かどうかを確認する方法

Excelで2つのセルに入力された文字列が合っているかどうかを確認する方法です。 下のようにB列とC列に文字列が入力されています。 B1=C ...

続きを見る

おすすめ記事と広告

記事を更新しました

絵文字の入力方法

Windows10のIMEでは、面白い絵文字をたくさん表示することができます。特にWord2016では、下のように色付き絵文字を入力すること ...

テーブルのサイズ変更と解除

Excelでテーブルを作成した後、テーブルのサイズを変更する方法です。テーブルの機能については、テーブルの便利機能(テーブルに変換して効率ア ...

グラフの代替テキストの設定

Excelで作成したグラフに代替テキストを設定する方法です。 代替テキストとは何?と言う方は、代替テキストからお読みください。 グラフエリア ...

Windows10 Creators Update後に画面中央に【あ】【A】が表示される

Windows10 Creators Updateを実行すると、既定では、IMEの入力モードを切り替える度に画面中央に[あ]または[A]が大 ...

ピボットテーブルとピボットグラフを同時に作成

Excel2013以降には、ピボットテーブルとピボットグラフを同時に作成できるコマンドがあります。ピボットテーブルの作成に慣れた方なら、グラ ...

-Windows10 & Office2016 Excel2016

Copyright © 2001-2018 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2018 AllRights Reserved Powered by AFFINGER4.