Windows 10 & Office 2016 Excel 2016

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

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

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

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

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

ピボットテーブルの作成・集計ができない(データベースにする準備)

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

続きを見る

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

フィルター機能を使ってデータをチェック

フィルター機能の設定

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

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

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

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

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

サンプルデータ

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

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

フィルター(Ctrl+Shift+L)

選択したセルにフィルターを適用します。

列見出しの矢印をクリックして、データを絞り込みます。

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

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

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

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

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

フィルターボタン

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

フィルターボタンが先頭行のセルにないのでオートフィルターが使えない

Excelでは、[データ]タブの[フィルター]を使ってデータの並べ替えや絞り込みができます。 いろいろな抽出条件を指定して、データの抽出がで ...

続きを見る

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

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

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

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

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

ですが、[新宿1号店]と[新宿1号店]がありますね。

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

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

販売店フィールドのフィルターボタンをクリック

データの抽出

[新宿1号店]は、[新宿1号店]に変更したいですね。

修正したい[新宿1号店]を抽出します。

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

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

すべて選択のチェックボックスをオフに

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

[OK]ボタンをクリックします。

チェックボックスをオンにしてデータを抽出

[新宿1号店]のデータが抽出されます。

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

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

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

フィルター実行後のデータ

置換作業

置換機能を使います。

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

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

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

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

[ホーム]タブの[検索と置換]

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

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

[すべて置換]ボタンをクリックします。

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

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

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

[検索と置換]ダイアログボックス

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

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

[OK]ボタンをクリックします。

置換実行後

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

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

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

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

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

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

元に戻すボタン

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

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

記号と漢数字の混在

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

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

記号の[○]と漢数字の[〇]

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

記号の[○]と漢数字の[○]が混在している場合の確認と対処

[まる]と入力して変換すると、変換候補には記号の[○]と漢数字の[〇]が表示されます。 ときどきシートにこの2つの[まる]が混在していること ...

続きを見る

リストの確認

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

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

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

フィルターボタンで確認

フィルターの解除

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

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

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

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

フィルターのクリア

空白スペースに注意

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

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

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

[担当者]のフィルターボタンをクリック

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

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

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

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

リストを選択して確認

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

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

[OK]ボタンをクリックします。

データの抽出

データが抽出されます。

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

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

空白スペースを削除

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

[川本]は1個になっていますね。

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

フィルターボタンをクリックしてリストを確認

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

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

ピボットテーブルで重複データをチェックして一意のリストを作成

Excelのリストで重複データが混在している場合、重複データをチェックして一意のリストを作成したい場合があります。 重複データをチェックする ...

続きを見る

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

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

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

目視でも分かりますね。

データを目視確認

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

[担当者]フィールドのフィルターボタンをクリック

検索でチェック

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

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

[ホーム]タブの[検索と選択]

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

[次を検索]ボタンをクリックします。

検索する文字列に半角スペースを入力

セル[E13]がヒットします。

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

検索結果

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

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

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

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

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

検索結果

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

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

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

[置換後の文字列]は空欄のままにします。

[すべて置換]ボタンをクリックします。

半角スペースを置換

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

[OK]ボタンをクリックして閉じます。

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

置換実行後

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

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

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

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

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

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

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

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

[検索と置換]ダイアログボックスのオプション

データの並べ替え

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

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

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

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

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

[データ]タブの並べ替え

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

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

参考にしてください。

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

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

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

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

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

[重複の削除]は1番目の値が残るがデータの空白スペースには注意

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

続きを見る

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

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

続きを見る

セル範囲の中から数値に見える文字列のセルを見つけるには

Excelのセルに数値が入力されていると思っても、実際は文字列だったということがあります。 見た目は数値に見えても表示形式は文字列になってい ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Windows 10 & Office 2016 Excel 2016

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

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