エクセル(Microsoft® Excel®)でデータプレパレーションを行う際、エクセルの機能や関数を使うことで、より効率的に行うことができます。
今回は、データプレパレーション作業を行う際に便利なエクセル機能について説明します。

データプレパレーションに便利なエクセル機能

重複の削除

重複データ名寄せやマスター用テーブルを作成する際には、重複データを削除します。
その際、「重複の削除」機能を使用します。
重複の削除ボタン重複の削除を行う範囲内にあるセルを選択後、データメニューのデータツールブロックにあるの「重複の削除」ボタンをクリックすると、「重複の削除」ウィンドウが表示されます。
1つのセルを選択している状態で、「重複の削除」ボタンをクリックすると、自動判別した範囲が設定されています。思うどおりの範囲が設定されていない場合は、自分で範囲を選択してから「重複の削除」ボタンをクリックします。

重複の削除ウィンドウ
重複が含まれる列=すべてが完全一致している場合に削除する対象の列をチェックし、「OK」ボタンをクリックすると、重複した列が削除されます。

重複の削除結果ウィンドウ

「重複の削除」では、一番上の行が残る仕様になっています。他の列のデータで削除する行を決めたい場合には、あらかじめ重複する行の内残す行を一番上に移動したあと「重複の削除」機能を使用するか、countif関数を使った方法があります。countif関数を使った方法は、別途ご紹介します。

置換(Ctrl+H)

置換対象氏名の間のスペースをなくす、性別が0と1で表現されているものを男女に変更する際など、特に一時的にデータを変更する際には、置換機能を使用すると一括変更(置換)できます。
検索と置換ボタン最初に、置換するセルの範囲を指定します。その後、ホームメニューの編集ブロックにある検索と選択のプルダウンメニューの「置換」をクリックすると、「検索と置換」ウィンドウが、置換タブが選択された状態で表示されます。ショートカットキーは、Ctrl+Hです。

検索と置換ウィンドウ検索する文字列には置換対象の値、置換後の文字列に、置換後の値を入力し、「すべて置換」をクリックすると、選択範囲を一括で置換します。「置換」ボタンは、一度検索してから1つずつ置換するか市内を判断する場合に使用しますが、データプレパレーションでは、使用するケースは少ないです。

<置換後>
置換結果

改行マークの置換

改行マークの置換対象エクセルのセル内にある改行マークは、以下の方法で置換できます。

検索と置換ウィンドウ置換するセルの範囲を指定後、「検索と置換」ウィンドウを表示します。
検索する文字列の入力欄にカーソルをあわせた後、「Ctrl+J」をクリックします。表示状の変化はありませんが、改行マークが設定されます。置換後の文字列には、後の使用方法に応じ、空白や“。”を入力し、
「すべて置換」をクリックすると、選択範囲を一括で置換します。

<置換後>
改行マークの置換結果
<注意>
改行マークの置換の場合、見た目だけでは、改行マークが入っているかわかりません。特に、改行マークの含んだ置換を実行した場合には、入力欄をdelキーで削除することをオススメします。

区切り位置

区切り位置対象氏名や郵便番号、電話番号などのデータを複数の列に分割したい場合、区切る位置が一定の文字で表記されている場合や、スペースにより固定長で整形されたデータは、「区切り位置機能」を使用することで簡単に、列を分割することが可能です。

1.区切る位置が一定の文字で表記されている場合の方法

区切り位置ボタン最初に、分割するセルの範囲を指定します。(1度に操作できるのは1列だけです。)

区切り位置指定ウィザート 1/3 カンマ、タブ、スペース区切り
その後、データメニューのデータツールブロックにある「データの区切り位置」をクリックすると、「区切り位置指定ウィザード」ウィンドウが表示されます。
「カンマやタブの区切り文字によってフィールドごとに区切られたデータ」をチェックし、「次へ」をクリックします。

区切り位置指定ウィザート 2/3 カンマ、タブ、スペース区切り
郵便番号、電話番号などの区切りでよく使う「-」や年月日の「/」、時間の「:」などは、その他をチェックした上で、該当する文字を右の欄に入力します。
区切り文字を選択するとデータプレビュー欄に分割後のプレビューが表示されます。

区切り位置指定ウィザート 3/3 カンマ、タブ、スペース区切り
「次へ」ボタンをクリックすると、区切った後のデータ形式を設定するウィンドウが表示されます。
表示先は、選択したセルの一番上のセルが設定されていますので、そのまま完了すると、元データが上書きされます。あらかじめ、分割後の列を挿入しておいたほうが後の作業が楽になります。
また、列のデータ形式は、標準で設定すると、郵便番号、電話番号は、数値に自動選択されますので、0から始まるセルは、最初の0がなくなります。(例:03は3となります)これを、回避するためには、列のデータ形式を、文字列にします。

区切り位置 カンマ、タブ、スペース区切り 実行結果
完了をクリックすると表示先に設定した箇所に分割されたデータが入力されます。

2.スペースにより固定長で整形されている場合

区切り位置指定ウィザート 1/3 固定長最初に、分割するセルの範囲を指定します。(1度に操作できるのは1列だけです。)その後、データメニューのデータツールブロックにある「データの区切り位置」をクリックすると、「区切り位置指定ウィザード」ウィンドウが表示されます。

区切り位置指定ウィザート 2/3 固定長「スペースによって右または左に揃えられた固定長フィールドのデータ」をチェックし、「次へ」をクリックします。

区切り位置指定ウィザート 3/3 固定長データに応じてデータのプレビュー欄に区切り位置が設定されますので、必要に応じて、位置変更、作成を行った後、「次へ」をクリックします。

区切り位置 固定長 実行結果列のデータ形式を調整し、表示先を設定してから、完了ボタンをクリックすると、設定した範囲の一番上のセルが設定されます。変更しない場合は、元のデータを上書きして1列目が入りますので、ご注意ください。

フラッシュフィル

一定のルールに基づきデータが入力されている場合の分割や列の結合行う際は、「フラッシュフィル」機能を使うことで一括操作が可能です。(エクセル2013以降で利用可能)
よく使用するパターン
・名前の分割、結合
・メールアドレスの@前後の分割、結合
・郵便番号、電話番号の分割、結合
・一定の文字を追加、削除(1,2,3←→A001,A002,A003)

列の分割の場合

フラッシュフィル手順分割する列を追加後、1行目に、分割するデータを入力します。入力した下のセルを選択し、データメニューのデータツールブロックにある「フラッシュフィル」をクリックすると、自動判定したルールで一番下まで同じルールで入力されます。
<B列フラッシュフィル後>
フラッシュフィル実行結果 列分割
<C列フラッシュフィル後>
フラッシュフィル実行結果 列分割

列の結合の場合

結合する列を追加後、1つの行に結合するデータを入力します。入力した下のセルを選択し、データメニューのデータツールブロックにある「フラッシュフィル」をクリックすると、自動判定したルールで一番下まで同じルールで入力されます。

<フラッシュフィル後>
フラッシュフィル実行結果 列結合

フラッシュフィル使用時の注意事項

  • 対象の列入力する列の間に空白列があると作動しません。
  • 空白行があるとその行で入力は終了します。
  • ふりがながある
  • 一定のルール以外は思い通りに作動しない場合があります。
    例:氏名を分割する場合で、元データに区切り文字がない場合は、文字数での分割ルールが適用されますので、思い通りに作動しません。


データプレパレーション作業を行う際に便利なエクセル機能についての説明は、いかがでしたでしょうか。

「datapreparation.jp」では、データプレパレーションを中心に様々な、データ操作、加工、活用などの記事を作成しています。
よければ、他の記事もご覧ください。

DataPreparation.jp(データプレパレーション.jp)
https://datapreparation.jp


記事作成日:2018年5月1日 作成環境:Excel 2013、Windows8.1

Copyright © 2018 dbE All rights reserved