-1.jpeg?width=48&height=48&name=DSC_2387%20(1)-1.jpeg){kind=small}
データクレンジングとは、ローデータ（生データ）の表記ゆれや重複を修正し、検索・分析などのデータ活用がしやすい状態に整えることです。データクレンジングには専用のツールもありますが、「使い慣れたエクセルでデータクレンジングができないか」と考える方も多いでしょう。
データクレンジング入門ガイド
〜顧客データを正し、活かすための基本・手法・ツール活用〜 データクレンジング入門ガイド
- データクレンジング概要
- データクレンジングの手法
- データクレンジング実行時のポイント
- HubSpotのAIアシスタント
今すぐダウンロードする
全てのフィールドが必須です。
本記事では、エクセルを使ったデータクレンジングの方法を画像付きで紹介します。それぞれのステップにおけるポイントも紹介しますので、ぜひ実践してみてください。
エクセルでもデータクレンジングは可能
エクセルには、データの品質を向上させるための基本的な機能が備わっているため、データクレンジングのツールとして活用できます。特別なツールを導入しなくても、手軽にデータ整理を始められる点がエクセルを活用する大きなメリットといえるでしょう。
例えば、入力ミスによる表記ゆれの統一、不要な空白の削除などは、エクセルの標準機能や関数を活用することで効率的に進められます。
データ量が多い場合は、専用のツールを活用することで作業効率を高めることができます。詳しくは次の記事をご覧ください。
データクレンジングで活用できるエクセルの機能・関数
ここでは、データクレンジングに活用できるエクセルの機能や関数を目的別に紹介します。
- 表記を統一する
- 不要な文字や空白・改行を削除する
- 重複を削除する
- その他
表記を統一する
全角と半角の統一や漢字の表記ゆれといった修正には、次のような機能や関数が便利です。
- 置換機能
- SUBSTITUTE関数
- JIS関数・ASC関数
置換機能
置換は、指定した文字列を別の任意の文字列に置き換える機能です。
次のいずれかの方法で置換機能を表示させてから、次のステップに進みましょう。
- エクセルのリボンから表示する：［ホーム］＞［検索と選択］＞［置換］
- ショートカットキーを使って表示する：「Ctrlキー」＋「f」
【「ダイコン」を「大根」に置換する場合】
置き換えたいセルを選択します。
［検索する文字列］：置換前の「ダイコン」を入力
［置換後の文字列］：置換したい文字列「大根」を入力
［すべて置換］をクリックします。
選択範囲内の「ダイコン」が「大根」に置換されました。
SUBSTITUTE関数
SUBSTITUTE関数は、セル内の文字を指定した別の文字に置き換える関数です。
B2セルの「文具」と「ハナマル店」の間の空白（半角）を削除してみましょう。
=SUBSTITUTE（文字列, 検索文字列, 置換文字列, ［置換対象］）
文字列には「B2」セルを指定、検索文字列は「削除したい空白（半角）」を「" "」と指定しましょう。置換後の文字列は、何も入れないため「""」とします。
空白を削除した文字列を出力する「F列」を用意し、セル「F2」に「=SUBSTITUTE(B2," ","")」を入力しましょう。
セル「F2」をコピーして、セル「F3～F9」に貼り付けると、ほかの行にも適用されます。
JIS関数・ASC関数
JIS関数とASC関数は、半角文字と全角文字の表記ゆれを修正する際に役立つ関数です。
- JIS関数：半角文字を全角文字に変換する関数
- ASC関数：全角文字を半角文字に変換する関数
【店名の半角文字を全角にしたい場合】
JIS関数を用いて、半角を全角にする例を紹介します。
B列の店名をクレンジングしてF列に出力してみましょう。
クレンジング後の内容を出力するF列を用意し、セル「F2」に「=JIS(B2)」と入力します。
「ﾊﾅ」が「ハナ」となり、全角で出力されました。
セル「F2」をコピーして、セル「F3～F9」に貼り付けると、ほかの行にも適用されます。
【「社名」列をASC関数を使用して半角にする場合】
D列に出力用の列を用意し、セル「D2」に「=ASC（A2）」と入力します。
セル「D2」をコピーし、セル「D3～D9」に貼り付けます。
社名列の全角文字が半角文字に統一されました。
不要な文字や空白・改行を削除する
データの中に空白や改行、誤入力による不要な文字などが入っている場合は、次のような機能や関数を使って一括で削除できます。
- 置換機能
- SUBSTITUTE関数
- TRIM関数
- CLEAN関数
置換機能
置換機能を応用して、置換後の文字列を何も指定しないことで指定の文字列を削除できます。
【空白を削除したい場合】
空白を削除したいセルを選択します。
［検索と置換画面］を開きます。
検索する文字列：空白を入れる
置換後の文字列：何も入れない
このまま置換を実行すると、全角スペースと半角スペースがどちらも削除されます。全角と半角を区別したい場合は、［検索と置換］画面のオプションをクリックし、「半角と全角を区別する」にチェックを入れましょう。
［すべて置換］をクリックします。
半角・全角の空白がすべて削除されました。
【改行を削除したい場合】
空白を削除するときと同じ要領で、改行を削除することも可能です。［検索する文字列］に「Ctrl」+「J」を入力して置換しましょう。
【コード列の「_」以下を削除したい場合】
不要な文字列を削除したいセルを選択します。
「A○○」に続く「_○○」の部分は文字列が同一ではないため、「ワイルドカード」と呼ばれる記号「*」を使って、指定します。
「*」は0文字以上の任意の文字列を表すため、「_*」は「_］で始まる文字列すべてが該当します。
検索する文字列：_*
置換後の文字列：空白にする
［すべて置換］をクリックします。
「_」以下が削除されました。
「*」を使用すると、文字数に関係なく該当箇所が削除されますが、「?」のワイルドカードを使用すると、任意の数だけ文字数を削除することが可能です。
「?」1つが1文字を表すため、「A001_01」という文字列に対して「_?」を空白に置き換えた場合、「_」と、それ以降の文字を1つ分だけ削除することになります。置換を実行すると、「_0」が消えて「A0011」となります。
SUBSTITUTE関数
SUBSTITUTE関数も、置換機能と同様に置換後の文字列を何も指定しない（「" "」と指定する）ことで不要な文字の削除が可能です。
【空白を削除したい場合】
B2セルの「文具」と「ハナマル店」の間の空白（半角）を削除してみましょう。
=SUBSTITUTE（文字列, 検索文字列, 置換文字列, ［置換対象］）
文字列には「B2」セルを指定、検索文字列は［削除したい空白（半角）」を「" "」と指定します。置換後の文字列は、何も入れないため「""」とします。
まず空白を削除した文字列を出力する「F列」を用意し、セル「F2」に「=SUBSTITUTE(B2," ","")」を入力します。
セル「F2」をコピーしてセル「F3～F9」に貼り付けると、ほかの行にも適用されます。
【改行を削除したい場合】
改行を削除したい場合は、SUBSTITUTE関数の検索文字列に「CHAR(10)」と入力します。
CHAR関数は、「＝CHAR(数値)」が基本式となり、特定の数値（コード）を入力することで対応した文字コードを返します。今回の（）内の数字「10」は、改行を意味するコードです。空白を削除する場合は「32」を入力するなど、数値を変えることでアレンジが可能です。
TRIM関数
TRIM関数とは、文字列の間にあるスペース1つを残して、他の余分なスペースを削除する関数です。
B列のデータから、余分なスペースを削除したデータをF列に出力してみましょう。
空白を削除した文字列を出力する「F列」を用意し、セルF2に「=TRIM(B2)」と入力します。
セル「F2」をコピーし、セル「F3～F9」に貼り付けると、ほかの行にも適用されます。
CLEAN関数
CLEAN関数は、改行やタブなどの印刷できない文字をすべて削除する関数です。
ほかのツールのデータをコピー＆ペーストで入力すると、余分な改行などが含まれることがあります。改行が入ることで、見た目は同じデータであっても別データとして扱われてしまうため、CLEAN関数で余分な文字を削除します。
B列の店名の改行を削除してみましょう。
改行を削除した文字列を出力する「C列」を用意し、セルC2に「=CLEAN（B2）」と入力します。
改行が削除されました。
重複を削除する
エクセルには、重複データを削除する機能があります。
【「購入者の重複」を削除する場合】
重複を削除したい表を選択します。
［データ］＞［重複の削除］をクリックします。
今回は購入者の重複を削除したいので、その他のチェックは外しましょう。
［OK］をクリックします。
重複していた行が削除されました。
いずれも最初に記載されていたデータのみが残り、ほかのデータは行ごと削除されます。
重複の削除は、不要な文字列の削除や表記ゆれの修正などをすべて終えたあとにも行いましょう。表記ゆれによって別のデータとして判定されていた重複が見つかる可能性があるためです。
データの重複を統合する際に便利な機能・関数は、次の記事で詳しく解説しています。
その他
その他、データを整理する際に役立つ機能・関数を紹介します。
- 区切り位置指定機能
- CONCATENATE関数
- PHONETIC関数
区切り位置指定機能
「区切り位置指定ウィザード」を使用すると、1つのセルに入っている内容の分割が可能です。
【コードの「_」の前後を分割する場合】
分割したいセルを選択します。
［データ］＞［区切り位置］と進み、「区切り位置指定ウィザード」を開きます。
「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択し、［次へ］をクリックします。
「区切り位置指定ウィザード-2／3」が開きます。
今回は「_」で区切りたいため、「その他」にチェックを入れ、区切りたい文字「_」を入力後、「次へ」をクリックします。
区切り文字をスペースやコンマにしたい場合は、該当箇所にチェックを入れましょう。
「区切り位置指定ウィザード-3／3」が開いたら、データを区切ったあとの列のデータ形式を選択します。
データのプレビューに区切られたデータが表示されます。データプレビューの各列を選択すると、該当する列が黒く表示されるのでわかりやすいでしょう。
各列の「列のデータ形式」を選択します。今回は、右側の列を「文字列」にしましょう。数字と認識されると、「01」の「0」が非表示になってしまうためです。
次に「表示先］を選択し、左上の「A00」を表示したいセルを入力します。区切られたデータが表示先のセル（今回ならE2）を起点に表示されます。
［完了］をクリックします。
コードの列（C列）に入っていた「_」前後の内容が、E2セルを起点に分割されました。
CONCATENATE関数
CONCATENATE関数を用いると、別の列のセルに入力されている内容を結合できます。
【店コードと品コードを結合する場合】
セル「G2」にセル「D2」「E2」に入っている項目を結合した文字列を出力してみましょう。
結合したコードを出力する列（G列）を用意します。
セル「G2」に「=CONCATENATE(D2,E2)」と入力します。関数ではなく「＆」を使用した「=D2＆E2」の入力でも結合可能です。
店コードと品コードを結合した「コード：1111」が出力されます。
セル「G2」をコピーしてセル「G3～G9」に貼り付けると、すべての行に適用できます。
PHONETIC関数
PHONETIC関数は、フリガナの抽出ができる関数です。
【品名のフリガナを出力したい場合】
C列の品物名のフリガナを出力してみましょう。
フリガナを出力するためのF列を用意し、セル「F2」に「=PHONETIC(C2)」と入力します。
セル「F2」をコピーしてセル「F3～F9」に貼り付けると、ほかの行にも適用されます。
エクセルでデータクレンジングを行う際の注意点
エクセルでデータクレンジングを行う際は、次の2点に気を付けましょう。
- 関数が入力されたままセルを移動させない
- 置換機能を使う際は意図しない文字の置換に注意する
関数が入力されたままセルを移動させない
関数でデータを修正した場合は、関数が入力されたままセルを移動させないように注意しましょう。移動したことで関数の参照元がずれて、エラーが出る可能性があるためです。
データを移動させたい場合は、次の手順で、関数や参照元が含まれる数式を「値」に変換しましょう。
セル「G2～G9」のデータを選択して、コピーします。
G2～G9を選択して右クリック、「形式を選択して貼り付け」を選択します。
「貼り付け」欄の「値」にチェックを入れて、［OK］をクリックします。
置換機能を使う際は意図しない文字の置換に注意する
置換の際に、本来置き換えるつもりではなかった文字まで誤って一緒に置換してしまうリスクもあります。例えば、「No」の表記を一括削除した際に、「note」の文字列の「no」が消えてしまい「te」になるなどが考えられます。一度置換して保存してしまうと元に戻すことが難しいため、事前に確認したうえで置換機能を使いましょう。
エクセルを活用したデータクレンジングで作業を効率化しよう
エクセルの機能や関数を活用すると、データクレンジングを簡単に行うことができます。ただし、エクセルにはデータ容量の制限（最大1,048,576 行、16,384 列）があり、複雑な関数を使うと処理速度も遅くなります。
データ量が増えてくると、手作業によるミスや修正漏れも発生しやすくなるため、エクセルを使ったデータクレンジングが難しいと感じたら、専用のツールの導入を検討すると良いでしょう。
HubSpotのOperations Hubは、顧客データのクレンジングが可能なツールです。アプリの連携やビジネスプロセスの自動化もHubSpotのCRM（顧客関係管理）ツール上で完結できるため、大幅な業務効率化につながります。まずは無料プランでOperations Hubを試してみてください。
データ品質管理（クレンジング）ツール | HubSpot（ハブスポット）詳細はこちら→
