【Excel講師の仕事術】自己流のエクセル、卒業しませんか? この連載では、エクセルを操作するときの「ルール」を決めることで、ミスを減らして業務を効率化していく仕事術を解説します。

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

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

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

 

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

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

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

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

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

文字列の削除前の表

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

文字列を空白で置換

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

空白で置換後の表

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

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

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

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

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

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

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

  

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

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

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

「?」で置換する入力例

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

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

「*」で置換する入力例

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

データクレンジングはエクセルの置換機能で。使いやすい機能でシンプルに解決【Excel講師の仕事術】

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

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

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

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

まとめ
  • 空白の文字列に置換=検索した文字列の削除
  • 任意の1文字を指定するワイルドカードは「?」
  • 文字数を指定しないワイルドカードは「*」

関連記事