シナジーマーケティング株式会社

Menu

知らないと損をする! データ整理・名寄せに使える6つのExcel(エクセル)小技集

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

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

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

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

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

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

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

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

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

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

2.【整理】不要なデータの削除(スペース/改行)

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

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

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

133_matuzawa_2-1-1

 133_matuzawa_2-1-2

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

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

133_matuzawa_2-2-1

 133_matuzawa_2-2-2

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

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

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

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

133_matuzawa_3-1-1

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

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

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

133_matuzawa_3-1-2

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

3-1-3-1

 133_matuzawa_3-1-3-2

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

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

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

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

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

 =DATEDIF(開始日,終了日,単位)

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

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

133_matuzawa_3-2-1

133_matuzawa_3-2-2

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

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

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

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

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

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

Excel 関数 ◯◯したい(やりたいことを記入)

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

まずは手元のExcelリスト宛に一斉メールを送ることから始めませんか?10,000社以上が利用しているメール配信システム『エクメルン』はこちら

「顧客管理」をはじめたいあなたに。必要なのはどちら?
SFAとCRMシステムの違い

「顧客管理をしたい」
あなたがやりたいことは、具体的になんでしょうか。その情報をもとに、どういう運用をしたいですか?
顧客管理とひとくちに言っても、求める方向性によって、適するツールが変わります。それぞれの言葉の定義、得意なこと、代表的なシステムなど基本情報を網羅。やりたいことの優先度で、最適なシステムを選択しましょう。

SFAとCRMシステムの違い

無料ダウンロード

PageTop