ルール 14
データクレンジングも置換機能でシンプルに
関数は使わずに、不揃いなデータを一瞬で整える

Excelの置換機能をフル活用する

Excelの置換機能でできるのは、データを置き換えることだけではありません。例えば、特定の文字列の一括削除や、不要なスペースの削除、部分一致の「あいまい」な条件の指定なども可能です。

　

特に、あいまいな条件の指定は重宝します。「ワイルドカード」と呼ばれる特殊な記号を使うことで、難しい関数を使わなくてもデータクレンジングの作業が可能です。データクレンジングとは、誤記や余計な文字列がある（汚れた）データを、置換・削除によって整える（洗浄する）ことを指します。

ただ、データクレンジングを手作業で行っていると、高確率でミスが発生します。置換機能で行うことをルールとするとともに、機能をフル活用できるよう、しっかりマスターしてください。

ワイルドカードを使った置換の例

文字列の削除は空白の文字列で置換する

以下は、ある商品の出荷を管理するための表です。別の基幹システムで利用するために、［商品識別ID］列に含まれる不要な数字「190901_」を一括削除してみましょう。

文字列の削除前の表

文字列を削除するには、［検索と置換］ダイアログボックスの［置換後の文字列］に空白を指定します。空白を指定するとは、何も入力しないという意味です。ある文字列を、何もない文字列で置換するので、削除となります。無関係な箇所を置換対象としないために、セル範囲をあらかじめ選択しておきます。

文字列を空白で置換

［すべて置換］ボタンをクリックすると、［検索する文字列］で指定した文字列が削除されます。

空白で置換後の表

半角・全角スペースを区別して削除できる

続けて、文字列に含まれる不要なスペースを一括削除しましょう。ここでは半角のスペースが含まれているデータを想定します。

不要な半角スペースが含まれている表

先ほどと同じように、［置換後の文字列］には何も入力しません。オプションで［半角と全角を区別する］にチェックを付けることで、全角スペースと半角スペースを区別して削除できます。

このオプションを指定しないで置換すると、半角・全角に関わらず、すべてのスペースが削除されるので注意してください。

半角スペースを空白で置換して削除

任意の1文字の指定は「?」を使う

　　

今度は、あいまいな条件を指定して、不揃いな文字列の一部分を一括で置換してみます。以下の例では［商品識別ID］列に含まれる「19」から始まる6桁の数字が不揃いの状態です。

不揃いの文字列が含まれている表

「19XXXX」をすべて「191101」に置換するには、どうしたらいいでしょうか？ こんなとき、本記事の冒頭で触れたワイルドカードが役立ちます。任意の1文字を表すワイルドカードは半角の「?」です。この例の場合、数字の前後にある「_」まで含めて「_19????_」を検索する文字列にすると、確実に置換できます。

「?」で置換する入力例

「?」を使った一括置換後の表

文字数を指定しないときは「*」を使う

文字数は関係なく「○○から始まる」「○○を含む」「○○で終わる」といった条件を指定したいときは、「*」を使います。ここでは、［商品識別ID］列の「Beer」以降の文字列をすべて取り除く例を紹介します。以下のように指定します。

「*」で置換する入力例

「*」を使った一括置換後の表

Excelでのデータクレンジングというと、詳しい人は「文字列操作関数」が思い浮かぶと思います。文字列操作関数については以降の記事（ルール38）で触れますが、本記事の例のように型にはめやすい修正であれば、関数を使わずとも、置換機能だけで簡単にデータクレンジングが行えます。

もし、こういった局面に遭遇したら、まずは置換機能で解決できるかどうかを考えてみてください。Excelに限りませんが、わざわざ複雑な機能を使う必要はないのです。

筆者が現場を回っていると、適切なデータクレンジングの方法が分からないがために、上から順番に手動で修正している人を時折見かけます。10件、20件ならば手作業でも問題はありませんが、200件、300件というデータを1人で直す行為は、精神衛生上よくありません。ミスが心配で、気を病む人もいるでしょう。

このようなデータクレンジングは絶対にしてはいけません。本記事のルールをマスターすれば、シンプルに解決できるはずです。

関連記事

• 連載「Excel講師の仕事術」の注目・新着記事一覧