エクセル(Microsoft® Excel®)でデータプレパレーションを行う際、エクセルの機能や関数を使うことで、より効率的に行うことができます。
今回は、データプレパレーション作業を行う際に便利なエクセル機能について説明します。
重複の削除
名寄せやマスター用テーブルを作成する際には、重複データを削除します。
その際、「重複の削除」機能を使用します。
重複の削除を行う範囲内にあるセルを選択後、データメニューのデータツールブロックにあるの「重複の削除」ボタンをクリックすると、「重複の削除」ウィンドウが表示されます。
1つのセルを選択している状態で、「重複の削除」ボタンをクリックすると、自動判別した範囲が設定されています。思うどおりの範囲が設定されていない場合は、自分で範囲を選択してから「重複の削除」ボタンをクリックします。
重複が含まれる列=すべてが完全一致している場合に削除する対象の列をチェックし、「OK」ボタンをクリックすると、重複した列が削除されます。
「重複の削除」では、一番上の行が残る仕様になっています。他の列のデータで削除する行を決めたい場合には、あらかじめ重複する行の内残す行を一番上に移動したあと「重複の削除」機能を使用するか、countif関数を使った方法があります。countif関数を使った方法は、別途ご紹介します。
置換(Ctrl+H)
氏名の間のスペースをなくす、性別が0と1で表現されているものを男女に変更する際など、特に一時的にデータを変更する際には、置換機能を使用すると一括変更(置換)できます。
最初に、置換するセルの範囲を指定します。その後、ホームメニューの編集ブロックにある検索と選択のプルダウンメニューの「置換」をクリックすると、「検索と置換」ウィンドウが、置換タブが選択された状態で表示されます。ショートカットキーは、Ctrl+Hです。
検索する文字列には置換対象の値、置換後の文字列に、置換後の値を入力し、「すべて置換」をクリックすると、選択範囲を一括で置換します。「置換」ボタンは、一度検索してから1つずつ置換するか市内を判断する場合に使用しますが、データプレパレーションでは、使用するケースは少ないです。
<置換後>
改行マークの置換
エクセルのセル内にある改行マークは、以下の方法で置換できます。
置換するセルの範囲を指定後、「検索と置換」ウィンドウを表示します。
検索する文字列の入力欄にカーソルをあわせた後、「Ctrl+J」をクリックします。表示状の変化はありませんが、改行マークが設定されます。置換後の文字列には、後の使用方法に応じ、空白や“。”を入力し、
「すべて置換」をクリックすると、選択範囲を一括で置換します。
<置換後>
<注意>
改行マークの置換の場合、見た目だけでは、改行マークが入っているかわかりません。特に、改行マークの含んだ置換を実行した場合には、入力欄をdelキーで削除することをオススメします。
区切り位置
氏名や郵便番号、電話番号などのデータを複数の列に分割したい場合、区切る位置が一定の文字で表記されている場合や、スペースにより固定長で整形されたデータは、「区切り位置機能」を使用することで簡単に、列を分割することが可能です。
1.区切る位置が一定の文字で表記されている場合の方法
最初に、分割するセルの範囲を指定します。(1度に操作できるのは1列だけです。)
その後、データメニューのデータツールブロックにある「データの区切り位置」をクリックすると、「区切り位置指定ウィザード」ウィンドウが表示されます。
「カンマやタブの区切り文字によってフィールドごとに区切られたデータ」をチェックし、「次へ」をクリックします。
郵便番号、電話番号などの区切りでよく使う「-」や年月日の「/」、時間の「:」などは、その他をチェックした上で、該当する文字を右の欄に入力します。
区切り文字を選択するとデータプレビュー欄に分割後のプレビューが表示されます。
「次へ」ボタンをクリックすると、区切った後のデータ形式を設定するウィンドウが表示されます。
表示先は、選択したセルの一番上のセルが設定されていますので、そのまま完了すると、元データが上書きされます。あらかじめ、分割後の列を挿入しておいたほうが後の作業が楽になります。
また、列のデータ形式は、標準で設定すると、郵便番号、電話番号は、数値に自動選択されますので、0から始まるセルは、最初の0がなくなります。(例:03は3となります)これを、回避するためには、列のデータ形式を、文字列にします。
完了をクリックすると表示先に設定した箇所に分割されたデータが入力されます。
2.スペースにより固定長で整形されている場合
最初に、分割するセルの範囲を指定します。(1度に操作できるのは1列だけです。)その後、データメニューのデータツールブロックにある「データの区切り位置」をクリックすると、「区切り位置指定ウィザード」ウィンドウが表示されます。
「スペースによって右または左に揃えられた固定長フィールドのデータ」をチェックし、「次へ」をクリックします。
データに応じてデータのプレビュー欄に区切り位置が設定されますので、必要に応じて、位置変更、作成を行った後、「次へ」をクリックします。
列のデータ形式を調整し、表示先を設定してから、完了ボタンをクリックすると、設定した範囲の一番上のセルが設定されます。変更しない場合は、元のデータを上書きして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