Excelでデータクレンジングを自動化する10の簡単な方法

Excelでデータクレンジング(データクリーニング)を自動化する方法をお探しですか?この記事はまさにそのためのものです。手元にあるデータは、必ずしもすぐに分析できる状態とは限りません。そのような場合には、いくつかの手順を踏んでデータを整理・整備する必要があります。この記事では、Excelでデータクレンジングを自動化するための10個の方法を、ステップごとにわかりやすく解説します。

Excelでデータクレンジングを自動化する10の方法

ここでは、氏名、生年月日、職業、給与が含まれた整理されていないサンプルデータセットを使用します。このデータセットを使って、Excelでデータクレンジングを自動化するさまざまな方法をご紹介します。

1. Power Query機能を使ってデータをクリーンアップする

最初の方法では、Power Query機能を使ってデータクレンジングを自動化します。以下の手順に従って、自分のデータセットでも試してみてください。

手順:

• まず、セル範囲B4:D10を選択します。
• 次に、データタブを開き、「テーブルまたは範囲から」をクリックします。

• すると、「テーブルの作成」ダイアログボックスが開き、データセットがすでに選択された状態になっています。
• そのままOKを押します。

• 次に、Power Queryエディターが表示されます。
• 「先頭行を見出しとして使用する」をクリックして、見出しを設定します。

• 続いて、テキストの大文字・小文字を揃えるために、「氏名」列を選択します。
• 変換タブを開き、「テキスト列」→「形式」→「各単語の頭文字を大文字にする」の順にクリックします。

• 次に、下のボタンをクリックして、空のセルを含む行を削除します。
• その後、「空白の削除」をクリックします。

• 次に、「閉じて読み込む」→「閉じて次に読み込む...」を選択します。

• 「データのインポート」ダイアログボックスが開きます。
• 「新しいワークシート」オプションを選択します。
• 最後にOKをクリックします。

• これで、Power Queryエディターを使ってデータを自動的にクリーンアップできます。

2. 「区切り位置」機能を活用する

このサンプルデータセットでは、氏名の姓と名が同じ「氏名」列に入力されています。区切り位置機能を使えば、これらのデータを2つの異なる列に分割でき、データクレンジングを自動化できます。

以下がその手順です。

手順:

• まず、セル範囲B5:B10を選択します。
• 次に、データタブを開き、「データツール」グループの「区切り位置」を選択します。

• その後、「次へ」をクリックします。

• 次に、区切り文字として「セミコロン」「コンマ」「スペース」、および「その他」に「@」を選択します。
• そして、「次へ」をクリックします。

• 貼り付け先としてセルC5を指定します。
• その後、「完了」をクリックします。

• 最終的に、データが「名」と「姓」の2つの列に分割されているのが確認できます。

3. フラッシュフィル機能でデータクレンジングを自動化する

次に、フラッシュフィル機能を使ったデータクレンジングの自動化方法をご紹介します。このデータセットには不要な記号が混在しています。以下の手順でそれらを取り除くことができます。

手順:

• まず、「名」列にJackと入力します。
• 次に、データタブを開き、「データツール」グループの「フラッシュフィル」をクリックします。

• すると、他のすべての不要な記号が自動的に削除されます。

4. SUBSTITUTE関数でデータをクリーンアップする

次に、SUBSTITUTE関数を使って、不要な記号を削除しデータクレンジングを自動化する方法をご紹介します。

以下の手順に従って、自分でも試してみてください。

手順:

• まず、セルD5を選択します。
• 次に、以下の数式を入力します。

=SUBSTITUTE(C5,"@",)

このSUBSTITUTE関数では、セルC5を対象テキスト(text)として指定し、“@”を検索文字列(old_text)、空白(" ")を置換文字列(new_text)として指定しています。

• その後、Enterキーを押し、フィルハンドルを下方向にドラッグして、残りのセルにも数式をオートフィルします。

• 最終的に、SUBSTITUTE関数によってD列から不要な記号が削除されました。

5. 「検索と置換」機能を活用する

5番目の方法では、Excelの検索と置換機能を使ってデータをクリーンアップします。ここでは、「#」を含む値が2つ、「@」や「;」を含む値が2つあります。これらの記号を検索と置換機能で空白に置き換えます。

手順:

• まず、セル範囲B5:D10を選択します。
• 次に、ホームタブを開き、「編集」グループの「検索と選択」→「置換」を選択します。

• すると、「検索と置換」ダイアログボックスが開きます。
• 「検索する文字列」ボックスに“#”を入力し、「置換後の文字列」ボックスは空白のままにします。
• その後、「置換」をクリックします。

• すると、“#”が削除されているのが確認できます。

• 同様に、検索と置換機能を使って、データセットから“@”や“;”も削除できます。

6. 表示形式(数値形式)を変更してデータを整える

次に、数値形式(表示形式)を変更してデータクレンジングを自動化する方法を学びます。ここでは、「生年月日」の一部の値が日付形式になっていません。以下の手順に従って、自分でデータをクリーンアップしてみましょう。

手順:

• まず、セル範囲C5:C10を選択します。
• 次に、ホームタブを開き、「数値」グループのドロップダウンボタンをクリックします。

• その後、「短い日付形式」を選択します。

• これで、データセットの数値形式を変更し、Excelでデータを自動的にクリーンアップできます。

7. 「重複の削除」オプションで重複データを除去する

次に、重複の削除機能を使って、重複している値を取り除き、データクレンジングを自動化します。

手順:

• まず、セル範囲B4:D11を選択します。
• 次に、データタブを開き、「データツール」グループの「重複の削除」を選択します。

• すると、「重複の削除」ダイアログボックスが開きます。
• その後、OKを押します。

• 次に、重複に関する情報を含む別のダイアログボックスが表示されます。
• そこでOKを押します。

• 最終的に、重複の削除機能によって、データセットから重複した値が取り除かれます。

8. 「ジャンプ」(セルの選択)機能で空白セルを検出する

次に、「ジャンプ」(セルの選択)機能を使って空白セルを検出し、データクレンジングを自動化する方法をご紹介します。

以下がその手順です。

手順:

• まず、セル範囲B4:D11を選択します。
• 次に、ホームタブを開き、「編集」グループの「検索と選択」→「ジャンプ」を選択します。

• すると、「セルの選択」ダイアログボックスが表示されます。
• その後、「空白セル」を選択します。
• 次に、OKをクリックします。

• すると、空白セルがすべて選択された状態になります。

• ここで、選択したセルの書式をお好みに合わせて変更できます。
• 今回は、ホームタブを開き、「塗りつぶしの色」をクリックして、赤を選択します。

• これで、空白セルを検出し、Excelでデータを自動的にクリーンアップできます。

9. リストとの照合(COUNTIF関数)でデータをクリーンアップする

手持ちのデータを、別の既存リストと照合したい場合もあるでしょう。以下のスクリーンショットはその例です。ここでは、左側のデータから退職者を特定します。右側には退職者の番号リストがあります。

上のスクリーンショットはシンプルな例です。データは範囲B5:D22にあり、目的はF列の「退職メンバー」リストに該当する行をデータ領域内で特定することです。後でこれらの不要な行を削除できます。

手順:

• まず、セルD5を選択します。
• 次に、以下の数式を入力します。

=IF(COUNTIF($F$5:$F$11,C5),"Resigned","")

この数式のCOUNTIF関数(太字部分)は、リスト内の値(セル範囲F5:F11)とデータの値(セルC5)が一致すれば1を返します。この結果が1以上であれば、IF関数は「Resigned(退職済み)」を返し、そうでなければ何も返しません。

• その後、Enterキーを押し、フィルハンドルを下方向にドラッグして、残りのセルにも数式をオートフィルします。

この数式全体は、C列の「会員番号」が「退職メンバー」リストに見つかった場合に「Resigned」と表示します。会員番号が見つからない場合は、空の文字列を返します。

• つまり、C列の「会員番号」が「退職メンバー」リストに存在すれば「Resigned」と表示され、存在しなければ空欄のままになります。

D列でリストを並べ替えれば、退職メンバーの行がまとめて表示されるため、素早く削除できます。

• D列で並べ替えるには、セルD5からD22までを選択します。
• 次に、「ホーム」⇒「編集」⇒「並べ替えとフィルター」⇒「降順」を選択します。

• 次に、「並べ替えの警告」ダイアログボックスが開きます。
• その後、「選択範囲を拡張する」を選択します。
• そして、「並べ替え」をクリックします。

• すると、Excelのサンプルファイルは以下のようになります。

この手法は、他の種類のリスト照合タスクにも応用できます。

10. スペルチェックでデータをクリーンアップする

最後に、スペルチェックを使ってデータクレンジングを自動化する方法をご紹介します。以下の手順に従って、自分でも試してみてください。

手順:

• まず、セル範囲C5:C10を選択します。
• 次に、校閲タブを開き、「スペルチェック」をクリックします。

• すると、「スペルチェック」ダイアログボックスが開きます。
• その後、「Manager」を選択します。
• そして、「変更」をクリックします。

• 次に、「Receptionist」を選択します。
• 続いて、「変更」をクリックします。

• 今度は、「Clerk」を選択します。
• その後、「変更」を選択します。

• 次に、Microsoft Excelの警告ボックスが表示されます。
• そこでOKをクリックします。

• これで、スペルチェックを使ってExcelのデータをクリーンアップできます。

練習用セクション

この記事では、以下の画像のようなExcelワークブックを用意していますので、ご自身で自由に練習することができます。

まとめ

この記事では、Excelでデータクレンジングを自動化するためのさまざまな方法をご紹介しました。この記事が皆さんのお役に立てば幸いです。内容でわかりにくい点がありましたら、ぜひコメントでお知らせください。また、ここで紹介しきれていない代替手段があれば、ぜひ教えてください。今後もこのような記事を多数公開していきますので、ぜひチェックしてください。ご覧いただきありがとうございました!