データクレンジングをエクセルで行うやり方｜便利な関数やポイントも紹介

最終更新日:2024/02/15

データクレンジングをエクセルで行うやり方

データクレンジングは、データの不備や重複などを修正してデータ品質を高める手法を指し、エクセルで簡単に行えます。「データクレンジングのやり方を知りたい」「エクセルで仕分ける方法があるか知りたい」という方も多いのではないでしょうか。本記事では、データクレンジングの概要やメリット、エクセルを用いたデータクレンジングの方法についてわかりやすく解説します。

データクレンジングとは

データクレンジングとは、データの誤りや欠如、重複などを修正し、データの品質を高める手法のことです。企業がデータの分析や活用を行う際、部署や担当者によってデータの形式・粒度がバラバラである場合も多く、そのままでは正しいデータ分析や活用はできません。そこでデータクレンジングをすれば、正確な情報をもとにデータの有効活用ができます。

データクレンジングの目的

データクレンジングを行う目的は、データの分析精度を向上させるためです。正確なデータ分析を行ううえでは、元となるデータが正しく、十分に揃っていることが前提となります。そしてデータの品質や形式を整えるためには、データクレンジングが不可欠な作業です。

データクレンジングが必要な場面としては、マーケティング活動や営業活動でのデータ活用などが挙げられます。収集・蓄積したデータを使ってマーケティングや営業を効果的に行うためには、データクレンジングを行ってデータの正確性を担保しておくことが必要です。

AIやビッグデータといった技術が進歩し、デジタル化が進む現代においては、ビジネスにおいてデータを分析・活用する重要性はこれまで以上に高まっています。データ分析の精度が企業の意思決定の成否に影響するため、データクレンジングの必要性は高いといえます。

データクレンジングの手順

データクレンジングは、以下の手順に従って行っていくことが一般的です。

【データクレンジングの手順】

1. 重要なデータフィールドの特定：顧客データや販売データなど、自社にとって重要となるデータフィールドを特定する
2. データの収集：特定したデータフィールドに関するデータを、形式を問わずに収集する
3. データの取り込み：収集したデータを一つのデータベースに取り込む
4. データの整形と不要なデータの削除：一定のルールに基づきデータを整形し、古い商品名や住所などは削除する
5. データの整理：重複しているデータを一つにまとめる名寄せを行い、データの分析をしやすくする

データクレンジングはエクセルの置換機能で代用できる

前述のとおり、小規模なデータであればエクセルの置換機能を使って簡単にデータクレンジングを行うことが可能です。たとえば、エクセルでは以下のようなことができます。

• データの置き換え
• 文字列の一括消去
• 不要なスペースの消除
• あいまいな条件の指定

データの置き換え

エクセルの置換機能を使えば、簡単にデータの置き換えができます。たとえば、以下のように「Japan」という文字列が複数あり、これらをすべて「日本」に変更するとします。

この場合、置換機能で検索する文字列を「Japan」、置換後の文字列を「日本」にして置換を実行すれば、一括で置換することが可能です。

実行の結果、以下のようにすべて「日本」にすることができました。

文字列の一括削除

エクセルの置換機能では、文字列の一括削除も可能です。たとえば、以下のように「アジアの日本」という文字列が複数あり、これらから「アジアの」を一括削除するとします。

この場合、置換機能で検索する文字列を「アジアの」、置換後の文字列を「空白」にして置換を実行すれば、一括削除が可能です。

実行の結果、以下のようにすべての文字列から「アジアの」を一括削除することができました。

不要なスペースの削除

エクセルの置換機能を使って不要なスペースの削除もできます。たとえば、以下のように都道府県名と「Japan」の間にスペースがある文字列に対して、一括でスペースを削除するとします。

この場合、置換機能で検索する文字列にスペースを入力し、置換後の文字列を「空白」にして置換を実行すれば、スペースの一括削除が可能です。なお、オプション項目で「半角と全角を区別する」にチェックを入れれば、半角スペースと全角スペースを区別することもできます。

実行の結果、以下のようにすべての文字列からスペースを削除できました。

あいないな条件の指定

エクセルの置換機能では、「○○を含む」や部分一致といったあいまいな条件の指定もできます。あいまいな条件の指定を行う際は、ワイルドカード（不特定の文字列を指定する際に使用する記号）を使うと便利です。

たとえば、以下のように「鶏肉」・「豚肉」・「牛肉」の3種類の文字列に対して、ワイルドカードを使ってすべて「肉類」に変換するとします。

この場合、置換機能で検索する文字列に「?肉」を入力し、置換後の文字列を「肉類」にして置換を実行すれば置き換えが可能です。ここで使う半角の「?」がワイルドカードであり、「?」の一文字だけで「鶏」「豚」「牛」のすべてを含むことができます。

実行の結果、以下のように「鶏肉」・「豚肉」・「牛肉」の3種類の文字列をすべて「肉類」に置き換えられました。

上記の例に加えて、文字数を指定しない場合の使い方も紹介します。たとえば、以下のように文字数の異なるユーザーコードに対して、ワイルドカードを使ってすべて「JPN_01」に変換するとします。

この場合、置換機能で検索する文字列に「JPN_01*」を入力し、置換後の文字列を「JPN_01」にして置換を実行すれば置き換えが可能です。ここで使う半角の「*」がワイルドカードであり、「*」の一文字だけで「JPN_01」以降のすべての文字数を含むことができます。

実行の結果、以下のようにすべてのユーザーコードを「JPN_01」に置き換えられました。

エクセルでデータクレンジングに便利な関数

ここでは、エクセルでデータクレンジングを行うときに知っておきたい関数として、以下の便利な関数を紹介します。

• TRIM関数：不要なスペースを削除する
• CLEAN関数：不要な改行を削除する
• DATEDIF関数：年齢や年数などの期間に関するデータの整理
• ASC関数（JIS関数）：全角を半角にする（半角を全角にする）
• RIGHT関数・LEFT関数・MID関数：データの一部を抜き出す

TRIM関数：不要なスペースを削除する

TRIM関数は、不要なスペースを削除したい際に使える関数です。「＝TRIM（文字列）」で、指定したセルの文字列の不要なスペースを削除できます。たとえば、下図のようにTRIM関数を使うことで、（株）と会社名の間にある余分なスペースを削除することが可能です。

＜関数入力＞

＜関数実行後＞

以降の行も関数をコピーするだけで、下図のように簡単に不要なスペースを削除できます。

CLEAN関数：不要な改行を削除する

CLEAN関数は、不要な改行を削除したいときに利用できる関数です。「＝CLEAN（文字列）」で、指定したセルの文字列の不要な改行を削除できます。たとえば、下図のようにCLEAN関数を使うことで、文章内の余分な改行を削除することが可能です。

＜関数入力＞

＜関数実行後＞

以降の行も関数をコピーするだけで、下図のように簡単に不要な改行を削除できます。

DATEDIF関数：年齢や年数などの期間に関するデータの整理

DATEDIF関数は、年齢や年数などの期間に関するデータを整理したい際に便利な関数です。「＝DATEDIF（開始日、終了日、単位）」で、開始日から終了日までの期間に基づく計算ができます。単位には以下のいずれかの条件を指定します。

• “Y”： 開始日から終了日までの満年数
• “M”： 開始日から終了日までの満月数
• “D”： 開始日から終了日までの日数

たとえば、下図のようにDATEDIF関数を使うことで、ユーザーの年齢を自動的に算出することが可能です。

＜関数入力＞

＜関数実行後＞

以降の行も関数をコピーするだけで、下図のように簡単に年齢を算出できます。

ASC関数（JIS関数）：全角を半角にする（半角を全角にする）

ASC関数は、指定した文字列の全角を半角に変換したい場合に使える関数です。「＝ASC（文字列）」で、指定したセルの文字列の全角を半角に変えられます。たとえば、下図のようにASC関数を使うことで、全角の電話番号を半角に変換することが可能です。なお、半角を全角にしたい場合には、JIS関数を用います。

＜関数入力＞

＜関数実行後＞

以降の行も関数をコピーするだけで、下図のように簡単に年齢を算出できます。

RIGHT関数・LEFT関数・MID関数：データの一部を抜き出す

RIGHT関数・LEFT関数・MID関数は、データの一部を抜き出したい場合に便利な関数です。RIGHT関数・LEFT関数・MID関数の式はそれぞれの以下のとおりです。

たとえばLEFT関数の使用例として、下図のようにユーザーコードの左から3文字だけを抜き出すことが可能です。

＜関数入力＞

＜関数実行後＞

以降の行も関数をコピーするだけで、下図のように簡単に年齢を算出できます。

エクセルでデータクレンジングを行う際のポイント

エクセルでデータクレンジングを行う際は、以下のポイントを押さえておくことが重要です。

【エクセルでデータクレンジングを行う際のポイント】

• データ活用の目的を考慮して進める
• データの検証を行う
• プロセスの再現性を高める
• 定期的にデータ状況を確認する

データ活用の目的を考慮して進める

エクセルでデータクレンジングを行う際は、データ活用の目的を考慮して進めることがポイントの一つです。データ活用の目的があいまいな状態では、データの対象やデータクレンジングの方法などが定まらず、効果的なデータ分析を行うことはできません。データ活用の目的によって収集データの種類や範囲、作業方法などが異なる可能性があるため、はじめにデータ活用の目的を明確にしていきましょう。

たとえば、データを営業活動に利用したい場合は、顧客データや過去の販売履歴などのデータを中心に収集することになります。また、エクセルでのデータクレンジングはあくまで簡易的なものであるため、収集対象となるデータの規模によってはエクセル以外のツールを使ったほうが効率的な場合もあります。

データの検証を行う

エクセルでデータクレンジングを行う際は、データの検証を行うこともポイントです。収集したデータのなかには誤記や古い情報が含まれている場合もあるため、データ内容に問題がないかを確認するようにしましょう。

エクセルは、表記ゆれやデータの重複を機械的に検出・修正することはできますが、データ自体の情報の正しさまでは検証できません。そのため、エクセルの機能だけに頼らず、担当者の目でもデータ内容を確認することが大切です。たとえば、同じ会社の本拠地の情報が2つ存在していた場合、どちらが最新情報であるかを判断するためには目視確認が求められるでしょう。

プロセスの再現性を高める

エクセルでデータクレンジングを行う際は、プロセスの再現性を高めておくことも大事なポイントです。エクセルでのデータクレンジング方法を特定の担当者のみが理解している状況では、担当者の異動や退職が生じた際にデータクレンジングを継続できなくなってしまいます。

継続的にデータクレンジングを行っていくためには、プロセスの再現性を高め、特定の担当者に依存しないよう作業を標準化することが重要です。作業マニュアルなどを作成・共有し、メンバー全員が同じプロセスでデータクレンジングを行える環境を整備していきましょう。

定期的にデータ状況を確認する

エクセルでのデータクレンジングは一回きりで終わりにせず、定期的にデータ状況を確認することもポイントです。データは常に追加・更新されていくものであり、データクレンジングを行った後にも新たなデータが追加されていくことになります。その結果、データクレンジングを行った時点では表記ゆれやデータの不備を修正できていたとしても、その後にまた表記ゆれやデータの不備が発生していくことになるのです。

そのため、データの品質を担保するためには、定期的なデータクレンジングを行うことが重要です。たとえば、毎月1日、毎週月曜日などのようにタイミングを決め、データクレンジングを定例作業として業務に組み込んでいくことをおすすめします。。

まとめ

データクレンジングは、データの誤りや欠如、重複などを修正し、データの品質を高める手法です。小規模なデータであれば、エクセルの置換機能を使って簡単にデータクレンジングを行えます。また、TRIM関数やCLEAN関数などの関数を使ってデータクレンジングを行うことも可能です。

データクレンジングを行うことで、データの品質向上や業務効率の改善、データの運用コストの削減などを実現できるでしょう。

AIsmiley編集部

株式会社アイスマイリーが運営するAIポータルメディア「AIsmiley」は、AIの専門家によるコンテンツ配信とプロダクト紹介を行うWebメディアです。AI資格を保有した編集部がDX推進の事例や人工知能ソリューションの活用方法、ニュース、トレンド情報を発信しています。

・Facebookでも発信しています @AIsmiley.inc
・Xもフォローください @AIsmiley_inc
・Youtubeのチャンネル登録もお願いいたします@aismiley
メルマガに登録する