岡安裕一 こんにちは。
久松さんのご紹介のおかげで、想定以上の方にブログをご覧いただき、少々プレッシャーを感じている岡安です。
昨日は「Excelを使った業務改善の小技～ショップデータの取り込み①～」ということで、CSVファイルの取り込み方法の一つの考え方としてデータ変換用のシートを一枚かませる方法をお伝えしました。
すでにサンプルのExcelファイルをダウンロードされている方もいらっしゃるかと思いますが、今日の記事では、データ変換用のシートで使っている関数などの細かい説明をしていきます。
すでにそんな関数知っとるがな、という方には必要ない内容となりますが、同じ関数でも人によって使い方は違うものですし、同じことを実現するのにも、違った関数を使うこともあります。
関数の使い方の例として参考にしてもらえれば幸いです。
今回のサンプルで使っているのは、以下の関数です。
- 単純な他のシートのデータ参照
- DATE関数
- LEFT,MID,RIGHT関数
- CONCATENATE関数
- IF関数
データをきれいにするという考え方
各関数の説明をする前に、一つお話を。
今回のサンプルで使用しているデータ変換シートは、単純に順番を入れ替える以外の部分では、データをきれいにする（データクレンジング）ことを念頭においた作りになっています。
日々の業務で発生するデータは、システムを入力する人などによって、同じデータでも入力される内容に違い（ハイフンのあるなし、表記の揺れなど）があり、これをそのまま放置するとデータ分析はもちろんのこと、正しい業務を実施できないことも起こり得ます。
クラウド化・IT化を進めると、入力時点である程度表記の揺れなどを最小にすることもできますが、ガチガチに決めてしまうと今度は入力の手間が増える（例えば郵便番号を入力するときに、3桁を入れて、次に下4桁を別の項目として入力するなど）ことになり、これはECショップで顧客にデータを入れてもらう場合などは、面倒になって離脱されてしまうリスクもありますので、できれば避けたいところです。
また、社内の担当者が入力するような場合にも正しいデータを正しいタイミングで入力してもらうためには、できるだけ手間をかけさせないことが鉄則でもありますので、後処理でどうにかできる部分は入力には負担を掛けないような形にしてしまう方がよいといえるでしょう。
細かい部分にこだわり過ぎる必要はありませんが、データをきれいにすると後で業務が楽になる、クラウド化・IT化の際にも有効であるという意識は持っておくとよいと思います。
それでは、個別の関数の説明をしていきます。
他のシートのデータを単純に参照する方法
これはほぼ説明する必要はないかと思いますが念のため記載しておきます。
サンプルのデータ変換シートの「受注日」「商品名1」などで使用している式です。
参照したいセルに「＝」を記入して、参照したいセルをクリックすれば参照完了です。
具体的には、
=ネットショップダウンロードデータ!D2
といった形となります。
1点だけ、参照を使った際の注意点があります。それは、式を入力したシートのセルや行を削除するような場合です。
行を丸ごと削除したりすると、参照先のデータが1行飛ばして表示されることがありますので、注意してください。
データの削除などをした場合には、データにずれなどが発生していないか、確認するとよいでしょう。
日付形式でないデータを日付形式に変換するDATE関数
これも比較的メジャーな関数ですが、Excel内部だけでデータを扱っていると、それほど使う頻度は高くはりません。
このDATE関数は、ダウンロードしたデータが日付形式ではない場合、年・月・日が別のセルに入っているようなデータをまとめて、日付データにするといった際に使用される関数です。
サンプルでは、データ変換シートの「発送日」で使用しています。
一般的な式
=DATE(年,月,日)
サンプル上の式
=DATE(LEFT(ネットショップダウンロードデータ!E2,4),MID(ネットショップダウンロードデータ!E2,5,2),RIGHT(ネットショップダウンロードデータ!E2,2))-1
サンプルの式では、ネットショップダウンロードデータの「受取希望日」を参照しています。
今回のサンプルでは、LEFT、MID、RIGHT関数を使っていて、少々わかりづらいのですが、基本的にはDATAの関数の中に、年：4桁の数字、月：2ケタの数字、日：2ケタの数字を入れることで、日付データに変換されます。
（LEFT、MID、RIGHT関数は、次の項目で詳しく説明します）
サンプルを見ていただくとわかりますが、「受取希望日」は日付データではなく、単純なテキストデータとして、「20151224」といったデータになっています。
このテキストデータをLEFT、MID、RIGHT関数を使用して分解して使っているのが、サンプルファイルの式です。
通常はExcel内のデータとして使うだけであれば、「20151224」というテキスト形式のままでもそれほど問題はおきませんが、このサンプルでは受け取り希望日の一日前に発送を行うという前提の元に販売管理データとして、「受取希望日」ではなく「発送日」を管理しています。
そのため、受取希望日から1日分の日付を引くという要件が必要となるため、テキストから一旦日付型に変換して、赤字の「-1」（1日減らす）を式に追加して、前日のデータとして計算して、表示させています。（テキストのままだと日付の増減の計算は面倒ですが、日付型に変えてしまえば簡単です）
なお、DATE関数は、Excelで作成したデータをクラウドサービスなどにアップするデータを用意するようなときにも使用されることがあります。
テキストの文字の一部を抽出するLEFT,MID,RIGHT関数
LEFT、MID、RIGHT関数は、テキスト（文字列）データから一部の文字を抽出する関数です。
サンプルでは、データ変換シートの「発送日」、「郵便番号」で使用しています。
一般的な式
=LEFT(文字列,文字数)
=MID(文字列,開始位置,文字数)
=RIGHT(文字列,文字数)
「20151224」という文字列があるセル（E2）に記入されていて、
- 文字列の先頭（左端）から4文字分抽出したい
- 文字列の5文字目から2文字分抽出したい
- 文字列の末尾（右端）から2文字分抽出したい
場合には、以下の式で必要な文字列を抽出できます。
=LEFT(E2,4)
=MID(E2,5,2)
=RIGHT(E2,2)
Excelでは、関数の中でさらに関数を使うことができるため、DATE関数やCONCATENATE関数と組み合わせて使うこともできます。
この文字列を抽出する関数は、他の関数のIF関数での条件分岐や一旦ばらして他の文字と組み合わせるためにCONCATENATE関数とあわせて使われることも多いです。
サンプル内では、
=DATE(LEFT(ネットショップダウンロードデータ!E2,4),MID(ネットショップダウンロードデータ!E2,5,2),RIGHT(ネットショップダウンロードデータ!E2,2))-1
といった形でテキストを年・月・日を一旦ばらしてDATE関数の中で日付データに変換するために使用されています。
文字と文字をくっつけるCONCATENATE関数
さて、次は比較的シンプルな、CONCATENATE関数です。
これは、テキスト（文字列）とテキスト（文字列）を結合するための関数です。
サンプルでは、データ変換シートの「氏名」「TEL」「郵便番号」「住所」で使用しています。
一般的な式
=CONCATENATE(文字列1,文字列2,文字列3)
サンプル上の式「氏名」
=CONCATENATE(ネットショップダウンロードデータ!H2,” “,ネットショップダウンロードデータ!I2)
CONCATENATEで囲った複数の文字列を単純に結合しますが、「” “」（半角スペース）のように””で囲った文字列を直接記載することも可能です。
姓と名が別のセルに分かれているようなデータを結合する際には、サンプルのように半角スペースを入れたり、ハイフンなしの郵便番号に「”-“」でハイフンを結合させることもできます。
条件によって処理を変える場合に使用するIF関数
IF関数は、特定の条件によって処理する内容を分岐させるための関数です。
サンプルでは、データ変換シートの「TEL」、「郵便番号」で使用しています。
一般的な式
=IF(条件式,条件に合致した場合,条件に合致しなかった場合)
サンプル内では、
=IF(LEFT(ネットショップダウンロードデータ!J2,1)=”0″,ネットショップダウンロードデータ!J2,CONCATENATE(“0”,ネットショップダウンロードデータ!J2))
といった式で、電話番号の最初に「0」が入っていない場合に、「0」を追加する変換を行っています。
CSVでデータをダウンロードしてExcelで開くと、「090-0000-0000」という「-」（ハイフン）が入った値は文字列としてそのまま表示されますが、「09000000000」といった「-」なしのデータは、数字データと判断され、最初の「0」が欠落して、「9000000000」として表示されてしまいます。
「LEFT(ネットショップダウンロードデータ!J2,1)=”0″」の部分が条件式です。
文字列の最初の1文字目が「0」かどうかを判別しています。
最初の文字が「0」の場合は、変換は特に必要ありませんので、「ネットショップダウンロードデータ!J2」で記載されているデータをそのまま表示します。
それに対し、最初の文字が「0」以外の場合には、1文字目に「0」を追加する必要がありますので、「CONCATENATE(“0”,ネットショップダウンロードデータ!J2)」で最初の文字として「0」を追記しています。（この場合、データ形式は明示的には変更していませんが、文字列を扱うCONCATENATE関数を使用することで、数字形ではなく文字列形式に変換されることになります）
IF関数は、非常に便利なもので、EXCEL内で色々な処理を行うのによく使われます。
さらにIF関数にIF関数を重ね（入れ子）たり、OR関数などを組み合わせて複雑な条件分岐を行うこともできますが、あまり複雑にし過ぎるとあとあとに変更したり、他の方に引き継ぐのが難しくなるため、できるだけシンプルな形で使用するのがよいでしょう。
まとめ
今回は細かい関数の使用例について詳しく説明してみました。
色々紹介はしてみましたが、潔癖症のようにすべてのデータをきれいにする必要はありません。
購入履歴を確認するために「顧客名の表記を統一（姓＋” “＋名）しておきたい」とか、県別の売上を集計するために「都道府県名だけを抽出したデータ」を用意しておきたい、現在手で入力しなおしている部分あるので、「入力を省力化したい」といったニーズがある場合には、今回ご紹介した関数などを使ってみてはいかがでしょうか。
今回ご紹介していない関数の使い方を知りたいとか、「こんなことに困っているんだけどいい方法がないか」といったご質問があれば、Facebookやメールでお気軽にお問い合わせください。
ではでは。