分析チームマネージャーが選ぶ使えるGoogleスプレッドシートの関数8選

お疲れ様です。ジョンです。

今回は分析チームマネージャー目線からの、これを知らなきゃ高機能のGoogleスプレッドシートを使いこなせてないでしょ！といった関数のまとめをお伝えします。

時代の変遷(Microsoft→Googleスプレッドシートへ)

2016年くらいまでは、MicrosoftのExcelを使うことが多かったですが、最近ではExcelファイルをSlackに送ってくるやつに対して、①ダウンロードするボタンを押す　②ダウンロード確認画面　③ダブルクリック　③開くまで待機　の「耐え難い4Step」を強要してんじゃねー！とキレてもいいくらいGoogleスプレッドシートが民主権を得てきました。

共同編集、Slackで送られてきてもワンクリックで表示、クラウド上にあるので自宅作業も簡単といった理由で僕は非常に大好きなのですが、今日は

分析やっている人に向けたスプレッドシートのテクニックまとめ

をご紹介！

PS.MicrosoftのExcelへ

僕はExcelのピポットが秒でできるショートカットキー操作(Alt+D→P→F)や、行列入れ替えつつ、値だけ貼り付けできるショートカットキー(Alt+E+S+V)とかはクッソ早くて今でも大好きです。

①データ集計に必須：sumifs関数

SQLでデータ抽出した後にそれを整形するプロセスで、この関数を知らない人はもはやアナリストとは名乗れないレベルでよく使う関数です。

簡潔に言うと、

複数の条件に一致した数字のみを抽出して足し上げることができます。

使い方のイメージは以下です。

左側のデータセット例のように、SQLで日付ごと、ユーザーカテゴリごと、行ったアクションごとのUUとコンバージョンUUを抽出したとします。

これらをユーザーカテゴリとアクションごとにコンバージョンが高くなる傾向があるかどうか？をまとめる必要があるケースに便利です。

関数の入れ方はこうなっています。

=SUMIFS($E:$E,$D:$D,$I9,$C:$C,$H9)　　

**使い方解説**

$E:$E　　　　→　集計したい範囲(今回はE列のUU)
$D:$D,$I9　 → D列のアクションがI9(つまりA)に一致するものに限定
$C:$C,$H9　 → C列のユーザーカテゴリがH9(つまり新規)に一致するものに限定

ちょっとめんどくさいですが、絶対参照も駆使して1回関数を組んでしまうとコピペで下に張り付けるだけで複数条件でのデータまとめが可能です。

この関数が僕的にオススメなのは、条件指定をセル参照にしておけば、セル上で条件を直接変更すると、それにあわせて値も自動で変わるところです。

たとえば日付も条件指定に入れておいて、日付の条件はセル参照にし、そのセルの日付を昨日に変えれば、一瞬で昨日のデータに代わります。

<日付のセル参照でデータを管理した例>

この場合日付のセルの2019/10/1を直接2019/10/2に変更するとセル上の数字がすべて2019/10/2のものに置き換わります。

常に昨日のデータを参照したければ、TODAY()関数使って管理すると勝手にセルの日付が変わるので、そういう使い方もできます。

②データを引っ張ってくる：importrange関数

この関数はGoogleスプレッドシートの強みが最大限に活かせることができる関数といっても過言ではないでしょう。

何ができるかというと

他のスプレッドシートからデータを引っ張ってくることができます。

例えば、誰かが、ユーザーIDと対応する都道府県を毎日更新してくれているとします。

これらのデータを使って分析したいけど、同じファイルに勝手にシートを追加して分析するのは、はばかれる場合に便利です。

〇引っ張りたいデータ元から引っ張る元

ここで重要なのが、URLの『d/』いかにあるハイライトしている部分で、このスプレッドシートのキーになるので、こちらを使います。

〇実際に別のシートに引っ張ってみた

これで、別シートから値を引っ張ってこれました。

文法は以下

=importrange("15oc0SxHC96p0PmHcwnqZcDhCcGhX3WBwRfHrzdN42Uw","シート3!B2:C100")

**使い方解説**

"15o~~~~42Uw"の部分　：　引っ張りたいデータのスプレッドシートのキー
"シート3!B2:C100"　　 ： 引っ張りたいスプシのシート名とセルの場所

この関数を入れると初め数字が出てこないですが、慌てずに、関数を入力したセルにカーソルを合わせ、『アクセスを許可』ボタンを押してください。

また、今回はB2:C100まで引っ張っていますが、B2:C100のところに空白だからといって何か値をいれてしまうとエラーが出るので気を付けましょう。

このあとの関数

③データ重複削除：UNIQUE関数

UNIQUE関数は

選択したデータ範囲に対して重複を削除してくれるものとなっております。

今回の例のように複数行も対応しているので、2行以上についても知りたい場合もデータ選択範囲を増やすことで対応可能です。

=UNIQUE(C7:D17)

**使い方解説**

データ選択範囲を指定すると縦に重複をカットしてくれる

④行列の入れ替え：TRANSPOSE関数

この関数は

データの行と列を入れ替えることが可能です

実際UNIQUE関数などの縦の情報に対して効果を発揮する関数なども多く、縦持ちに入れ替えたいケースも少なくありません。　

こちらはそういった場合に有効な関数。

＝TRANSPOSE(B5:E11)

**使い方解説**

データ選択範囲を指定するだけで行列が入れ替えされる

⑤条件一致のものだけ抽出：FILTER関数

この関数は

条件一致しているものだけ抽出するのに使う関数です

=FILTER(C5:E11,C5:C11 = "食品")

**使い方解説**

C5:E11　　　　　　：データ抽出範囲
C5:C11 = "食品"　:条件指定箇所
                条件指定は『=』だけではなく『>』などの不等号も利用可能

⑥関数処理の軽量化：ARRAYFORMULA関数

この関数は

行の始めに選択範囲と関数を設定するとすべての関数対応セルに関数をいれなくても関数の処理を行うことができ、軽量化につながります。

ビッグデータの生ログを抽出してごりごり処理する場合とかにオススメです。(そんな場面がある時点で結構データの整い方に問題はあるのは置いておいて)

使用例はコチラ

こちらは一番上だけ関数を組むと選択範囲(C5:C18)のすべてに処理が適用されます。

=ARRAYFORMULA(IF(C5:C18>=80,"合格","不合格"))

**使い方解説**
ARRAYFORMULAの中にIF関数を入れているだけなのですが、
IF関数のデータ選択範囲を一つだけではなく、C5:C18と今回処理を行いたい箇所に広げるだけでおｋ

⑦セル内にグラフ表示：SPARKLINE関数

この関数は

セル内にグラフを表示できる関数です

ただ、簡易的なものなので、一次元のものしか扱えません。

=SPARKLINE(C10:C23,{"charttype","column";"max",500})

**使い方解説**

C10:C23　　　　：データ選択範囲
"charttype","column"　：　チャートのタイプを棒グラフにする
"max",500　　　　　　　　：　グラフの最大値を500と設定する
※基本的に{ ~~~ } の中を設定しなくてもデータ選択範囲だけ指定すれば線グラフが表示されます

⑧日付から曜日をだす：TEXT関数

この関数は

数値をテキストに変更できる関数

なのですが、僕は基本的には日付に対して利用し、曜日を出すのによく利用します。

=TEXT(F4,"ddd")

**使い方解説**

"ddd" : 数値を曜日形式に変更(曜日の簡略系)
"dddd": 数値を曜日形式に変更(曜日の正式名)

まとめ

今回ご紹介した関数は実際のビジネスの場合でも使用頻度が高く、知っておくとより効率的に仕事を進めることができるかもしれません。

全部覚えておくのが不安な方はぜひブックマークしてでも覚えてみてください。

今回は以上になります。