データ分析で使えるエクセルの機能と関数のまとめ

エクセルの関数は星の数ほどありますが、便利な関数だけ覚えておけば大丈夫です。
では、データ分析でよく使えるエクセルの機能や関数はどういったものがあるでしょうか？

■フィルタ

「フィルタ」を使うと簡単便利にデータを絞り込むことができます。

メニューからデータ→フィルタをクリックすればできますが、

ショートカットキーを覚えて置くと便利です。

ショートカットはAltボタンを押しながら、D＋F＋F（Fボタン２回）です。

■マージ

以前の記事でも紹介しましたが、Vlookup関数はエクセルで分析をする上で必要不可欠な関数です。必ず覚えたい関数ですね。

■トランスフォーム（数値変換）

日付を操作する関数

• year(日付)　   →　年だけ
• month(日付)　→　月だけ
• day(日付)　　 →　日だけ

 日付から、年だけ、月だけ、日だけを取り出す関数です。特にmonth関数は月次の数値を計算するときのキーを作るときによく使う関数です。

さらに

• weekday(日付)　→　曜日に変換
• weeknum(日付) →　その年の何番目の週か？（週数）

曜日別や週次の推移を計算する際にはこうした関数も覚えて置くと便利です。

文字列操作する関数

• left(文字列 , 文字数）　→　指定した文字列の左から指定した文字数だけ抽出
• right(文字列 , 文字数)   →　指定した文字列の右から指定した文字数だけ抽出
• ASC(文字列）　→　全角カタカナを半角カタカナに変換

アンケートデータに入っているフリーアンサー情報などを加工する際によくつかう関数です。

異常値を操作する関数

• IF関数
• IFERROR関数

少し高度なデータ操作をする場合には、IF関数も理解する必要があります。IF関数は覚えなくてもデータの加工はできるので、「効率的にエクセルの関数を組めるようになりたい！」と感じるようになってから勉強すればいいと思います。

【参考になる本】

分析設計の仕方

過去の記事で、準備作業の全体を紹介しましたが、
今回は、分析設計の方法について深掘りしたいと思います。

過去の記事：

データ分析前の準備作業　（概要）
https://daaataaa.blogspot.jp/2015/10/blog-post.html

分析設計書の項目

分析設計書は書かなくても、分析できますが会社や研究などで作業を始めるときは作っておいた方が迷うことがなくなるのでスムーズです。

最低限書くべき項目は、

1. 概要
2. 目的
3. 仮説
4. データ

あったほうがいいものは、

1. 作成する説明変数
2. 分析ステップ

※これらの項目はこれが正解というものでもないので、適時カスタマイズしてください。

①概要

概要には、分析が必要となっている背景や状況を書きます。

あとで振り返るときなどに、なんでこの分析したんだっけ？？

という状況を打破できます。打破打破。

②目的

目的には、何を明らかにするのかを書きます。

ビジネスオーナーと、アウトプットを明確にするための共通意識を形成するためのものです。

分析者とビジネスオーナーが一致する場合は不要ですが、あまりないと思います。

ビジネスオーナーとは分析をお願いする人です。会社では上司だったり、お客さんだったり、大学では教授とかになるんですかね？

※目的の設定は意外と難しい作業で慣れが必要です。

③仮説

目的とセットで作ります。何が要因となっているのか？どこを掘れば、目的を達成するためのインサイトを抽出できるのか？をビジネスオーナーと一緒に議論するたたき台にもなります。

あまり分析に関与するメンバーが少ない場面でも、これがあると「あそこも見なきゃ！あれもたさなきゃ！」みたいな雑念が消えるので目的を達成するための最短経路をつくれるなどの、メリットがたくさんできるのでおすすめです。

※雑念が出た場合はメモしておいて、次ステップに回したり、考察のしたりするときなどに使ったりできます。

④データ

分析に使うデータを定義します。

割りと企業なんかだと、例えば売上金額にしても、POSデータ、CRMの売上データなど同じデータでも複数のデータ・ソースがあるため、これもビジネスオーナーと認識をあわせるのに作っておいて、確認するという意味でも最初に明記しておくのがおすすめです。

補足：目的と仮説について

目的と仮説の書き方ですが、例えば目的を「CRMにおける離反モデル構築する」とした場合、仮説は、「離反は月次購入金額が影響するのか？、直近半年における日曜日の来店回数も影響するのか？」など書きます。箇条書きで整理すると分かりやすいと思います。

あったほうがいいもの①作成する説明変数

これも目的と仮説に対応させて作成します。

例えば、

 月次購入金額 = 月数をキーにして売上金を合計する

など具体的な変数の作り方を書きます。

あったほうがいいもの①分析ステップ

分析する作業になれてくれば作らなくてもいいですが、先に作っておくと、

スムーズに分析作業をすすめることができます。

1. 前処理
2. ビジュアライズ
3. モデリング
4. レポーティング用のビジュアライズ、モデル詳細の出力

など予め必要となる作業ステップや、出力物をメモしておけるとスムーズです。

まとめ

分析設計は、

• 分析を達成するゴール設定を明確にする
• ゴールを達成するための手段を具体化する
• 目的と手段を分析に関わるメンバーに共有し、同じ認識を持つ

ために作りましょ！

SQL分析をスタートするために最低限覚えておくべきこと

大きなデータを使うには、SQLは避けて通れません。
とはいえ、SQLで分析には、大変な労力となります。

大変な労力となる理由：いっぱいSQLがある

SQLといっても、MySQLやPostgreSQLから、BigQueryなどのクラウドサービスまで多様な種類のSQLが存在するので、細かく覚えないといけないためです。

基本はどれも一緒ですが、分析までしようとすると、データベース独自の関数を覚える必要があります。

さらに、一つの会社で、複数のデータベースを使っている場合があるので、完璧先生を目指すとなると長い年月が必要となります。

じゃあどうしよう→基本だけ覚える

基本はselect句、from句、where句だけ覚えてしまえば、技術者以外の単に仕事で使いたい人が作業する分には十分です。仕事で使う場面というは「いつ、誰が何をいくら買ったか？」「どのページを何回みたか？」などを指しています。

初心者の方に説明しますと、DB（データベース）は大きなエクセルファイルです。

その中にtable(テーブル）と呼ばれるシートがいくつか入っています。

あまりにもデータが大きいので、エクセルのように一度に開くと大変なことになりますが、必要な列だけとか、必要な行だけ抽出するための操作がSQLとなります。

SQLのはじめに

エクセルでsheet1全体を開く操作をSQLで書くと、

----

 SELECT * FROM sheet1

----

となります。

selectの後ろの*印は全部の列を指定する。

fromの後ろのfromはどのテーブルかを指定する。といった具合です。

sheet1に一億行のデータが入っているとPCが大変なことになる（基本的にはならずエラーを返してくれるはず）ので、そういう場合は、limitをつけてちょっとだけ見せてとおことわりを入れます。

----

 SELECT * FROM sheet1 limit 100

----

とすれば、最初の100行だけ持ってくるので、安心です。

これはデータの中身を確認したいときによく使うフレーズです。

①SELECT句の役割について

SQLは非常にシンプルな作りなので、上記でだいたいの構造は理解できると思いますが、

select句は、カラム（列）を選択する句です。

変数名を記入し、複数ある場合はカンマでつなぎます。

* はワイルドカードですべての行を取得します。　

②FROM句の役割について

FROM句はどのテーブルからデータを取得するのかを指定します。

例えば FROM salesなどと書くとsalesテーブルからデータを取得します。

③WHERE句の役割について

WHERE句では、抽出するデータの条件を設定します。

例えば、「WHERE  age > 70 」と書くと、70歳超の人のデータだけが出力されます。

次のステップはGROUP BY句やJOINを覚えることだが

まずは、①〜③とlimitを使い、取得したいデータを自在に操作できるようになることが重要です。データ範囲の指定は、基本的な操作です。しかし正確に集計をする上では、実は非常に難しい操作であったりもします。

抽出したいデータを取り出し、エクセル等のツールで集計＆分析を正確にできるようになってから、GROUP BY句を覚えるので十分だと思います。

まとめ

まずは、select 〜〜〜 from 〜〜〜 where 〜〜〜　と正確に抽出したいデータ範囲を指定することから始めよう！

集計は無理にSQLで行おうとせず最初は、エクセルやTableauを使っちゃおう！

エクセルでデータ分析をするために最低限覚えるべき３ポイント

SQLをはじめ、RやPythonを使えることに越したことはないですが、エクセルでも十分頑張れば、分析できるものだと思います。

エクセルでデータを処理することに不自由を感じたら、SQLやR、Pythonのスキルを習得できればいいと思います。

では、エクセルで何を覚えれば自在にデータを扱えるようになるのでしょうか？

ポイントを絞って３つだけ。

ポイント

1. シフトとコントロールキーを組みあわせたショートカットキー使い方
2. vlookup関数
3. ピボットテーブル

【ポイント１】シフトとコントロールキーを組みあわせたショートカットキー使い方

シフトキーを押しながら→を何回か押すと複数のセルを選択できることが分かります。

そして、コントロールキーを押しながら→を１回押すと右端のセルまでジャンプできます。また、セルに値が入っている場所を選択しているときにコントロールキーを押しながら→ボタンを押すと値が入っている所までジャンプしてくれます。

※難しいので、実際にエクセルを触りながらいじってみるとシフトとコントロールキーの役割がわかるはず！シフトはキープしてくる。コントロールキーはジャンプしてくれる。という感覚を身に着けられればOK！

シフトキーとコントロールキーを組み合わせることで、自在にセルの選択できれば、効率も上がってきます。

【ポイント２】vlookup関数

vlookup関数はデータとデータをつなげる関数です。

たとえば、商品IDに商品名を紐付けするときなどに使います。

vlookup関数に使い慣れることで、マージ（merge)と呼ぼれるデータ操作の初歩を学ぶことができます。

操作方法はこちら：

https://support.office.com/ja-jp/article/VLOOKUP-%E9%96%A2%E6%95%B0-0bbc8083-26fe-4963-8ab8-93a18ad188a1

【ポイント３】ピボットテーブル

ピボットテーブルは、集計をするための機能です。

たとえば、商品ごとの月次の合計売上金額を出したいときなどに使います。

ピボットテーブルを使い慣れることで、集約(group by)と呼ばれるデータ操作を学ぶことができます。

操作方法はこちら：

https://www.microsoft.com/ja-jp/office/2013/tips/excel/excel16.aspx

まとめ

エクセル等の表計算ソフトは、直感的にデータを触ることができる（データフレームの各要素をマウスでいじれる）ことが最大の利点ですが、逆にそれ故手間がかかってしまうことが弱点です。エクセルでのデータ操作に慣れ、より効率的にデータ処理をしたいと思ったらSQLのスキルを身につけられるといいと思います。

データ分析前の準備作業

データ分析をしたことがないのに、
いきなりRを触るのはハードルが高いですよね。。
データを分析する前の作業はどういったものがあるのかをご紹介します。

分析設計

ビジネスの現場において分析作業を始める前には、分析設計を行います。

　①目的の作成

　　⇒分析によって解決したい課題は具体的に何か？

　②分析ステップの作成

　　⇒目的を達成するための作業はどういったものはあるか？

　③分析ストーリーの作成

　　⇒分析によって、どういった数値をだして、その数値に何を語らせるか？

分析に関わるメンバーが少なければ、これらをまとめる設計書はメモ程度のもので大丈夫。客先に提出したり、責任者の合意を得て進める分析プロジェクトであれば、エクセルやパワーポイントで資料化します。

データの収集

分析設計が終わったら、分析につかうデータを集めます。

大きく、２つの集め方があるかと思います。

　①既存のデータを使う（人からもらう）

　　⇒たとえばデータベースを管理している人

　②自らデータを作る（自分で作る）

　　⇒たとえば、アンケートを実施して、集める

特に前者は、どれだけデータベースに負荷をかけるのか？ネットワークの権限設定をする必要があるのか？など、システムやセキュリティに影響を及ぼす範囲を確認する必要があります。

データの確認

データを収集したあとに、データ確認をします。

データがきちんとしたものでないとわかった時が、

遅ければ遅いほど、手戻りの量が増えてしまうので、

この作業は、極力早い段階で実施することが需要です。

　・レコードの数（何行のデータか？）

　・データ期間（いつからいつまでのデータか？）

　・欠損値の数（データに抜けはないか？）

これらの作業が終了したら、いざ集計作業となります。

データ分析前にすべき準備方法について参考になる本はこちら。