ビスリポブログbizzrepo blog

みなさんの会社では、どのようにデータクレンジングしていますか？

データクレンジングと聞くと、「難しそう」「時間がかかりそう」「専門知識が必要そう」と感じてしまうかもしれません。

でも実は、みなさんお馴染みのエクセルでも、一通りのクレンジング処理をこなすことができるんです。

本記事では、そんなエクセルを使ったデータクレンジングのテクニックを、具体例と図解を交えて詳しく解説していきます。

そもそもデータクレンジングとは？

データクレンジングとは、データを扱いやすいよう、処理しやすいよう、分析しやすいように、整形し、加工し、重複や欠損を取り除く処理のことです。

具体的には、以下のような作業を行います。

• データの整形：データを処理しやすい形に整える
• データの正規化（表記ゆれの排除）：さまざまな表記が混在している場合に、正規の表記方法を定義しその表記に揃える
• 重複データの統合：同一の存在を表す情報が重複して存在している場合に一つにまとめる
• データ形式の変換：「数値を日付に変換する」のように、データを適切な形式に変換する
• 欠損値の補完：欠けているデータを補完したり削除したりする

これらを行うことで、さまざまな業務にデータを活用できるようになります。その結果、

• 業務効率が上がる
• 精度の高い分析結果に基づいた意思決定が可能になる
• 顧客満足度が高まる
• 営業効率が上がる
• マーケティングの費用対効果が改善する

などのメリットを得られ、事業成長を後押ししてくれる、と考えられています。

【関連記事】データクレンジングとは？ツール・やり方・成功事例まで徹底解説

Excelを使ったデータクレンジングのやり方15選【図解付き】

ここからは、エクセルを使ったデータクレンジングの方法とテクニックを、 以下3つのステップに分けて、実践的な具体例とともに紹介していきます。

1. データ整形の方法
2. データの正規化（表記ゆれの排除）方法
3. 重複データ統合の方法

データクレンジングに使えるエクセル機能・関数の一覧表も作成してみました。参考にしてみてください。

Excelデータクレンジング① データの整形

まずは、用意したデータを処理しやすい形に整えていきましょう。エクセルでは、次の関数が役に立ちます。

• TRIM関数
• CLEAN関数
• LEFT関数・RIGHT関数・MID関数

順に解説していきます。

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

外部ソースからデータを読み込んだ場合、セル内の文字列に不要なスペースが入っていることがあります。このようなケースでは、TRIM関数で不要なスペースを一括削除してしまいましょう。

なお、TRIM関数では、文字列の先頭および末尾に挿入されているスペースはすべて削除される一方、文字と文字の間に挿入されたスペースは1つだけ残る仕様となっています。

CLEAN関数：不要な改行・非表示文字を削除

外部ソースからインポートしたデータには不要な改行や非表示文字が含まれていることがあります。改行や非表示文字は処理の妨げになることがあるので、CLEAN関数で削除してしまいましょう。

LEFT関数・RIGHT関数・MID関数：文字列の一部を抽出

セルに入力された文字列から必要な文字列だけ抜き出したいときに使います。

• A1セルの左から5文字だけ抽出したいとき：　=LEFT(A1, 5)
• A1セルの右から8文字だけ抽出したいとき：　=RIGHT(A1, 8)
• A1セルの（左から）6文字目から8文字分を抽出したいとき：　=MID(A1, 6, 8)

Excelデータクレンジング② データの正規化（表記ゆれの排除）

データ整形が完了したら、次はデータの正規化（表記ゆれの排除）を行っていきます。エクセルでは、以下の機能や関数が役に立ちます。

• 置換機能
• SUBSTITUTE関数
• UPPER関数・LOWER関数・PROPER関数
• PHONETIC関数
• ASC関数・JIS関数

順に解説していきます。

置換機能：検索した文字列を一括変換

表記ゆれのバリエーションがさほど多くない場合、置換機能で特定の文字や文字列を一括変換してしまうのが手っ取り早いです。

【操作手順】

1. 置換したいセルを選択
2. 「Ctrl」+「H」キーを押す（またはホームタブの「検索と選択」から「置換」をクリック）
3. 「検索する文字列」に「㈱」を入力
4. 「置換後の文字列」に「株式会社」を入力（※削除したい場合は空白のままでOK）
5. 必要に応じて「オプション」をクリックし、詳細な設定を行う
6. 「すべて置換」をクリック

SUBSTITUTE関数：特定の文字列を指定した文字列に置き換える

SUBSTITUTE関数を使えば、関数を使って置換機能とほぼ同じことができます。

UPPER関数・LOWER関数・PROPER関数：大文字小文字を揃える

英字に大文字・小文字が混在しており、すべて大文字、すべて小文字、先頭のみ大文字＋小文字などに揃えたいときに使います。

• A1の文字列を大文字に揃えたいとき：　=UPPER(A1)
• A1の文字列を小文字に揃えたいとき：　=LOWER(A1)
• A1の文字列を先頭のみ大文字、他は小文字に揃えたいとき：　=PROPER(A1)

PHONETIC関数：ひらがなをカタカナに揃える

ひらがなとカタカナが混在しているデータをカタカナに統一したい場合に役に立ちます。

ASC関数・JIS関数：半角を全角に・全角を半角に揃える

日本語文字列ではよく使う処理です。以下のように使用します。

• A1セルを半角に揃えたい：　=ASC(A1)
• A1セルを全角に揃えたい：　=JIS(A1)

なお、変換されるのは、数字、英字、スペース、カタカナ、記号のみです。ひらがな、漢字、全角または半角のいずれかにしかない記号は変換されません。

Excelデータクレンジング③ 重複データの統合

データの正規化が完了したら、次は重複データの統合または削除を行いましょう。エクセルでは、以下の機能や関数が役に立ちます。

• 「重複の削除」機能
• UNIQUE関数

順に解説していきます。

「重複の削除」機能

重複データを削除するだけで良い場合は、「重複の削除」機能を使うのが手っ取り早いです。

【操作手順】

1. 重複を削除したいセル範囲を選択します。
2. データタブの「データツール」から「重複の削除」をクリックします。
3. 重複チェックを行う列を選択します。
4. 「OK」をクリックします。

UNIQUE関数：ユニークな値を抽出

指定範囲内のユニークな値を抽出することができる関数です。

Excelでのデータクレンジングで注意すべきポイントは？

ここで紹介したテクニックを組み合わせて使うことで、エクセルでも非常に高度なデータクレンジングが可能になりますが、一つ注意すべきポイントがあります。

それは、データのサイズ。次の表を見てみてください。

エクセルでは、この行数と列数の上限を超えてデータを読み込むことはできません。

上限に達していなくても、数十万行を超えたあたりから、メモリ不足によるフリーズやクラッシュが発生しやすくなります。

大規模データをクレンジングしたい場合は、別のツールを検討する必要があるでしょう。

データクレンジングに使えるツールの選び方や製品比較については、近日公開の下記記事でより詳しく解説します。気になる方は参考にしてみてください。

【関連記事】データクレンジングツールの種類&選び方【おすすめ20選】

【VBA・Power Query】Excelデータクレンジングを自動化できる？

エクセルでデータクレンジングする場合、VBAやPower Queryにより機能拡張できる点もメリットとなります。ここで紹介した機能や関数をマスターしてしまったという人は、VBAやPower Queryに挑戦してみるのもおすすめです。

Excel VBA（Visual Basic for Applications）

VBAとは、エクセル上で動作するプログラミング言語のことです。VBAを扱えるようになると、独自のマクロを作成することができるため、データクレンジングをある程度自動化することができます。一度作成したマクロは再利用できるので、同様のデータ処理を繰り返し行う際に非常に便利です。

Power Query

Power Queryは、Microsoftが提供しているETLツールで、エクセルとのデータ連携のしやすさに特徴があります。Power Queryを使えば、「外部ソースからデータを取得」「Power Queryでクレンジング処理を実行」「エクセルワークシートに読み込む」までの3ステップを自動化することができます。

Excelデータクレンジングをマスターしてデータ活用を成功させよう！

データクレンジングをエクセルで行うことのメリットは、他のツールと比べて圧倒的に学習負荷が低く、追加のコストもほとんどかけずに済むという点。

この記事で紹介した方法や注意点を参考に、ぜひ実践してみてください。

データクレンジングのお悩み、解決します。

私たちネットレックスでは、強力なデータクレンジング機能を備えたマスタ管理ツール「ビズリポ」を開発・提供しています。

ビズリポでは、多種多様なデータを自動でクレンジングし、システム上のマスターデータベースに保持。同時に、多種多様な形式・接続方法で外部に配信することができます。

クレンジングルールは、VBAやPower Queryよりもずっと簡単に設定可能。

どのようなデータ／システムとも連携させることができます。

月々6万円から利用できるので、よくあるエンタープライズ仕様のETLツールより、ずっと手軽にお試しいただけます。

下記からお問い合わせいただければ、お客様のユースケースに合った使い方ができるかどうか、実際に操作画面をお見せしながらご説明させていただきます！

少しでも気になった方、いつでもお気軽にお問い合わせください。

⇒ 超強力な自動変換で一元管理！ マスタ管理ツール『ビズリポ』