本記事は、データの重複チェックやデータクレンジングなどの「データの名寄せ作業」を簡単に効率化できるExcel(エクセル)の機能や関数をご紹介した前回記事の続編です。今回は、改めて「データの名寄せ作業」の工程を整理し、各工程でのExcel作業効率化のテクニックをご紹介します。

• 複数のリスト・データを名寄せ（マージ）する必要があるが、方法がわからない
• データクレンジング作業に時間がかかる、業務効率が悪い
• 顧客データのメンテナンスが必要なのに、Excelが苦手

などの課題を抱えておられるマーケティング・制作担当者様にオススメです。

1.データの名寄せ作業：3つのステップ

3つのステップとは、データの【整理】【絞り込み（フィルタリング）】【統合（マージ・名寄せ）】です。この順で作業を行わなければ、データに誤りが生じる可能性が大きくなりますので、気をつけましょう。

まずは、データの【整理】です。複数のリストやデータを【統合】する場合、まずはそれぞれもとになる各リスト（Excelのブック・シート）内のデータの【整理】が必要です。この作業は、「データクレンジング」とも呼ばれます。各リスト内のクレンジングが完了したら、マージする各データ間でも「データの標準化」を行いましょう。

※「データクレンジング」や「データの標準化」が必要な理由については、「初めての『顧客管理』 Vol.3～顧客管理の第一歩、インポート前のデータ整形～」をご参照ください。

次に、データの【絞り込み（フィルタリング）】です。必要な場合は、データの【絞り込み】を実施し、【統合】に必要なデータを精査・抽出します。

最後に、データの【統合（マージ・名寄せ）】です。それぞれのデータ内にある必要な部分を寄せ集め、データの【統合】を行い、必要なデータを完成させます。

これが、データの名寄せ作業の一連の工程です。今回は、この【整理】の工程で使える6つの小技をご紹介します。（【絞り込み】【統合】については次回以降にご紹介予定です）

2.【整理】不要なデータの削除（スペース／改行）

 複数のデータを名寄せする場合、名寄せするリスト内や、複数の各リストのあいだで、データ形式や管理方法が統一されていない、または不十分なデータである場合がよくあります。なかでも、データに不要な情報が含まれているケースが非常に多く、その代表的なもののひとつが、不要なスペースと改行の存在です。 

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

不要なスペースについては、TRIM関数で解決できます。TRIM関数とは、指定したセル内の文字列の間にあるスペースを1つだけを残して、他のスペースは削除する関数です。名前などのデータを扱う場合、特に便利です。

 

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

 不要な改行の削除については、CLEAN関数で解決できます。CLEAN関数とは、指定したセル内の文字列の中にある改行を削除する関数です。アンケートの自由記入欄などフリーアンサー形式で設計されている項目のデータを扱う場合などが、特に便利です。

 

3.【整理】日付・年齢など時間に関する関数

2.の「改行」や「スペース」と同様に、日付や年齢などの「時間・期間」に関するデータも、名寄せするリスト内や複数の各リストのあいだで、入力形式がバラバラなケースの代表例です。

①日付データを扱う際は「シリアル値」かどうかがポイント

例えば、以下の「アンケート回答日」のデータを一括で西暦・和暦変換するケースで考えてみると…

大体の場合、【表示形式】の【日付】の編集で解決できます。
1）変換したいセルを選択し、右クリックし、【セルの書式設定】をクリック
2）【表示形式】タブで【分類】→【日付】を選択し、それぞれの【種類】を編集

しかし、この方法で解決できない場合は、入力されているデータ自体が「シリアル値」でない可能性が高いと思われます。
「シリアル値であるかどうか」とは、簡単にいうと「Excelがそのセルのデータを日付と認識しているかどうか」ということです。

シリアル値であるかどうかは、以下の方法で確認できます。

この場合、文字列のデータを「シリアル値」に変換してから【表示形式】の編集を行う必要がありますが、これはDATEVALUE関数で解決できます。

 

その他、日付データを文字列にしたり、8ケタの文字列を日付にする場合は、以下のリンク先をご参照ください。

■日付データを文字列に・8ケタの文字列を日付に（TEXT関数）

②年齢や年数などの「期間」に関する問題はDATEDIF関数

複数のデータを1つに名寄せする際、「いま現在の年齢や、会員期間など」の新しいデータを付与することもあるかと思います。その際、「生年月日しかなく、今何歳かがわからない」「会員登録日の情報しかない」ということもよくあります。こういった「期間」に関する多くの問題はDATEDIF関数で解決できます。

DATEDIF関数は次のように入力します。

 =DATEDIF（開始日,終了日,単位）

 この引数となる「単位」の部分には、求めたい期間によって、次のように値を指定します。
“Y”：期間内の満年数
“M”：期間内の満月数
“D”：期間内の日数

「年齢」を求める場合は”Y”、「会員期間」などは細かく見る場合は”D”を利用すると良いでしょう。

4.【整理】その他のデータ整理につかえる関数

2.でご紹介した、無駄な改行やスペースの削除以外にも、データの名寄せ作業で利用する代表的な関数は他にもあります。こちらについては、前回の記事をご参照ください。

■知らないと損をする！リストやデータ作成・整理に使える６つのExcel(エクセル)小技集

1. 重複データのチェック・一括削除
2. 複数データの一括変換（置換）
3. 全角文字を半角に（ASC関数）・半角文字を全角に（JIS関数）
4. データの一部を抜き出す（RIGHT関数、LEFT関数、MID関数）
5. 複数のセルを一つのセルに（CONCATENATE関数／&演算子）

5.とりあえず【Excel　関数　◯◯したい】で検索！

 今回ご紹介した関数以外にも、Excelには驚くほどさまざまな関数が用意されています。このシリーズでご紹介できる関数も、データの名寄せ作業で使う代表的な関数のみですので、もしご紹介した関数以外でやりたいことがあれば、どんどん検索してみましょう！

Excel　関数　◯◯したい（やりたいことを記入）

などのキーワードで検索すれば、おそらく必要な関数や計算式がヒットします。使えば使うほど関数を身につけることができ、今後のExcelでの作業効率もあがります。

 なお、効率的な検索方法については、以下のリンク先でご紹介しています。こちらもぜひご参照ください。

■あなたの仕事を必ず助ける６つの検索技

6.【偉大】もはや関数は不要？！『フラッシュフィル』と『名寄せ専用ツール』

 最後に、Excel 2013のみの機能ですが『フラッシュフィル』と、Excel以外の『名寄せ専用ツール』についてご紹介します。

 『フラッシュフィル』は、Excel 2013にのみ搭載されている機能ですが、名寄せに関するExcelの作業効率を必ず向上させるすごい機能です！あまり関数を知らない初心者にとっては、強力なサポート機能になります。また、Excel上級者にとっても、関数を書くより作業効率を向上できる可能性大です。

 『フラッシュフィル』とは・・・

名簿などで、氏名が姓と名に分けて入力されているデータを、後から 1 つのセルにまとめる場合に、フラッシュ フィルを利用すると、データの先頭と 2 行目のセルに入力するだけで自動的に残りのデータを入力することができます。フラッシュ フィルは、入力したデータの規則性を認識し、必要とするデータを瞬時に入力する機能です。

（大量のデータを瞬時に入力しよう（フラッシュフィル）／Office 総合ポータルより）

上記の引用の場合は、前回ご紹介した「5. 複数のセルを一つのセルに（CONCATENATE関数／&演算子）」の関数と同等の動作になりますが、これ以外のケースでも『フラッシュフィル』は動作します。『フラッシュフィル』は、データに何らかの一貫性がありそのパターンを認識すると自動的に作動するので、Excel2013をご利用の場合は、まず関数を入力する前に作りたいデータを直接入力してみるといいかもしれません。それでも反応がなければ、【フラッシュフィル】を動かしてみてください。

 また、Excelでは処理できないデータ量である場合や、名寄せ作業自体にコストをかけられる場合は、専用のツールやサービスを利用するというのもひとつの方法です。
たとえば、弊社のデータクレンジングサービスの場合、前回や今回ご紹介した重複チェックやデータクレンジングなどの＜【整理】の工程の課題＞は、ツールを利用するだけでほぼ解決できます。

 次回は、＜【絞り込み】【統合】の工程＞で活用できる関数や小技をご紹介する予定です。お楽しみに！

Excel(エクセル)小技集シリーズ

• 【初級編】リストやデータ作成・整理に使える６つのExcel小技集
• 【中級編】データ整理・名寄せに使える6つのExcel小技集（本記事です）
• 【上級編】知らないと損をする！データ・リストの名寄せに使えるExcel(エクセル)小技集～上級編①
• 【上級編】データ・リストの名寄せに使えるExcel小技集②

※記載されている内容は掲載当時のものであり、一部現状とは内容が異なる場合があります。ご了承ください。