Excel 汚いデータをキレイにするにあたって便利な関数

データをクリーニングするにあたって便利な関数は、大体においてこんなものでしょう。

• RIGHT関数/LEFT関数/MID関数…右から、左から、何個目から何個まで文字列を取得
• TEXT関数…数値を書式変換した文字列にする
• LEN関数…文字数
• TRIM関数…左右の空白除去
• VALUE関数…文字型のものを数字型に変換
• JIS関数/ASC関数…全角・半角変換
• UPPER関数/LOWER関数…英数の大文字・小文字変換

Replace関数(特定文字を置換する)も便利な関数ですが、Excelの場合だと、置換機能があるので、ちょっとした半手作業でデータを処理する場合、私はあまり使いません。

オーソドックスな利用シチュエーションは以下の通り。とりあえず、ここでは機能と使い方のヒントを列記していきます。関数の詳細については、Excelのヘルプを見てください。

LEFT関数を使って0つき数字の生成

RIGHT関数はそもそも右から何文字目までをゲットしてくる、という典型的な文字列操作関数。「0001」「0002」など0をつけて数字の桁を揃えたいとき、非常によく使います。
ex)A1に「123」という値が入っていて、B1のセルに「00123」という5桁のゼロつき数字にして表示したいとき
　B1のセルに「=RIGHT("00000" & A1,5)」と書く
　※()カッコの中の文字列を引数と云います。「引数」というコトバは、頻出単語なので、関数の中に入れる文字列のコトなんだ、ととりあえず覚えておきましょう。この例の場合だと「"00000" & A1」と「5」のことを引数と云います。
　たとえばこのままドラッグすれば、A列の数字が全部5桁の0つき数字に揃います。

TEXT関数を使って数値の書式を揃える

TEXT関数を使って、数値の書式を変換することも可能です。上記の例で0つき数字5桁に揃えるには、
「=TEXT(A1, "00000")」
とかですね。TEXT関数でカンマつきの数値変換、及び日付変換などもできます。VBで云うところのFormat関数のようなものです。

LEFT関数で固定長の文字列の生成

RIGHT関数とは逆に左から何文字目までをゲットしてくる、というタイプの関数。固定長の文字列を作成したいときなどによく使いますね。固定長というのは、全角の6文字なら6文字で、文字と全角スペースで六文字を埋めてしまうこと。PCだと可変のCSVでのやりとりなどが多いのでこのワザの出番はあまりありませんが、ホスト相手にやりとりするデータだと、固定長にすることが多いので、基本はコレです。
ex)A1に「まつもとひろゆき」とかいてあって、B1のセルに「まつもとひろゆき□□□□□□□□□□□□」と表示したいとき。
　B1に「=LEFT(A1 &REPT("□",20),20)」と書けば、
「まつもとひろゆき□□□□□□□□□□□□」
という結果に。REPT関数は今回の場合、□を20コ繰り返すという意味。□を全角スペースと見立てると、固定長になります。

MID関数

左から何個目から、何個までをゲットしてくる関数。ちなみに左から「何個目から…」というところを1に設定すると、LEFT関数と同一の意味で使えます(一番左は1とカウントします)。上記の「まつもとひろゆき」の例の場合、「=MID(A1 & REPT("□",20),1,20」となります。
ちょっとした固定長のデータの検証するときなどにも多用していますねー。このテの仕様書は大抵Excelでいただいているので、フィールド名と文字数などが表になっているわけです。開始文字列の数をExcel上でちゃちゃと計算して(区切り文字があればそれも考慮)、仕様書の文字列数をそのままセットすれば、MID関数で固定長のデータを拾ってこれるわけですよね。私の場合、開始文字列と、拾ってくる文字列数を、仕様書のフォーマットにあわせて縦に向かって計算して、コピー&ペースト術で、それらのタテ型の列をヨコに並べる。で、このフィールドごとの開始文字列のポジションの計算結果と文字数を利用して、MID関数を利用することが多いです。仕様書をそのまま利用できるのでなかなかこのテの組合せは、ちょっとしたときにベンリで重宝しています。

LEN関数

文字数をカウントして返してくるカウント。Replace関数やMID関数、FIND関数などと組み合わせて使うシーンが多いですねー。

TRIM関数

引数の文字列の左右の空白を除去してくれます。「□あいうえお□□」という文字列がセルA1に入っていたとしたら、「=TRIM(A1)」で「あいうえお」を返してきます。空白が入っていれば、文字列の文字コードの組み合わせは変わってきてしまい、「見た目」は一緒なのに、データは異なるデータとして認識されますので、なにかと不都合がおこります。ですからなにかとデータにはTRIM関数を一旦かませてから、比較したり、加工したりすることなどが多いです。

VALUE関数

セルの書式設定が「文字列」のまま、見た目「数字」のデータを計算しようとしても、データ型は文字列として扱われ、四則の計算などの処理は一切出来ません。ですから、数字としてデータを扱いたい場合、一旦「VALUE」関数で、数値型になおしてから、計算などを行います(詳しくは「Excel文字列を数値変換する」を参照)。

JIS関数/ASC関数

JIS関数…半角文字を全角文字に変換する関数
ASC関数…全角文字を半角文字に変換する関数
というわけで、引数に指定された文字列を変換してくれますヨ。いわゆる「全角/半角」を知らないも入力するのがExcelワールドなので、集計作業等をするにあたってデータをクリーニングしたいとき、このテの関数はかなり重宝します。

UPPER関数/LOWER関数

UPPER関数…英文字をすべて大文字に変換する関数
LOWER関数…英文字をすべて小文字に変換する
このあたりも上記同様、押さえておきたい関数ですね。

以上、大抵の言語には、このテの役割を果す関数は、名前は違えど揃っているのではないでしょうか。プログラマーの方はこのテのコワザの思考方法は一通り抑えているのではないかと思います。

Excel的には、これらの関数と「Excelコピー&ペースト術」を組み合わせで、一発限りのちょっとしたデータをクリーニングのときなどは、大方半手作業で処理できてしまうわけです。

理想は勿論このテの加工をせずにすむよう、汚いデータを作成しないことです。その為には､事前にきちんとなんの為にデータを収集しているのだからどんなモノが必要なのか計画を練り入力時のがっつりしたエラーチェックなどは必須事項です。後から修正する方が当然大変ですから…。

しかし現実にはキタナいデータを突然送り付けられたり、特に単発のものであれば、多少の整形処理をしなくてはならないシーンは多いわけです。そんなとき、これらの関数を知っていれば、グンと早く、より確実な仕上がりのキレイなデータにすることができます。手作業部分が増えれば、それだけヒューマンエラーの出る可能性も大きくなるので、なるべく手作業をやらずにすむ部分はやらないというポリシーで、バッチ的に処理する工夫をして、ラクをしましょう。