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

• 重複の削除
• 置換(Ctrl+H)
• 　改行マークの置換
• 区切り位置
• フラッシュフィル

重複の削除

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

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

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

置換(Ctrl+H)

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

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

＜置換後＞

改行マークの置換

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

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

＜置換後＞

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

区切り位置

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

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

最初に、分割するセルの範囲を指定します。（１度に操作できるのは１列だけです。）

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

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

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

完了をクリックすると表示先に設定した箇所に分割されたデータが入力されます。

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

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

「スペースによって右または左に揃えられた固定長フィールドのデータ」をチェックし、「次へ」をクリックします。

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

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

フラッシュフィル

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

列の分割の場合

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

＜C列フラッシュフィル後＞

列の結合の場合

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

＜フラッシュフィル後＞

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

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

---

---

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