Googleスプレッドシートで簡単データ分析！Excelに無い関数やツールの使い方もご紹介

関数を用いたデータ分析やデータクレンジング（データ整備）

GoogleSpreadsheetにはたくさんの関数が存在します。関数を使えば、基礎的な分析をしたり、データ分析前にデータクレンジング（データを整えること）をすることができます。今回は前回の記事でご説明できなかった関数や、Excelには無いGoogleSpreadsheet独自の関数をいくつかご紹介します。

FILTER関数

条件が1つの場合

=FILTER（表示させたいデータがある範囲,条件）

条件が2つ以上の場合：「AまたはB」

=FILTER（表示させたいデータがある範囲,(条件A)+(条件B)）

条件が2つ以上の場合：「AかつB」

=FILTER（表示させたいデータがある範囲,(条件A)*(条件B)）

FILTER関数とは、条件に合致するデータを抽出する関数です。複数の条件を指定することも可能です。まずは、画像のような年齢・性別・体重・身長のデータを使って、FILTER関数を試してみます。

1つの条件：性別や身長で対象データを抽出する

性別が女性の方のデータを抽出してみます。この場合、ポイントは以下の3つになります。

表示させたいデータがある範囲はA2からE11
条件を判別するデータの範囲はC2からC11
条件は対象のセルが"女"になっていること

そのため、記述する関数式は「=FILTER（A2:E11,C2:C11="女"）」となります。すると性別が女性である5人のデータが表示されました。ちなみにC2には"女"が記入されているので、「=FILTER（A2:E11,C2:C11=C2）」という関数式に置き換えることも可能です。

次に、身長が170より大きい方のデータのみを抽出してみます。この場合、ポイントは以下の3つになります。

表示させたいデータがある範囲はA2からE11
条件を判別するデータの範囲はE2からE11
条件は対象のセルの値>170であること

そのため、記述する関数式は「=FILTER（A2:E11,E2:E11>170）」となります。結果、身長が170より大きい4人のデータが表示されました。

複数の条件：年齢と体重で対象データを抽出する

年齢が20歳以上『または』体重が50未満の方のデータ抽出を行う場合、ポイントは以下のようになります。

表示させたいデータがある範囲はA2からE11
条件を判別するデータの範囲はE2からE11
条件は対象のセルの値>170であること

そのため、記述する関数式は「=FILTER（A2:E11,(B2:B11>=20)+(D2:D11<50)）」となります。すると年齢が20歳以上、または体重が50未満の方7人のデータが表示されました。
ここで対象を「年齢が20歳以上『かつ』体重が50未満」と変えてみると、関数式は「=FILTER（A2:E11,(B2:B11>=20)*(D2:D11<50)）」となります。結果、2つの条件の両方に当てはまる1人の方のデータが表示されました。

QUERY関数

QUERY関数とは、選択した範囲のデータを別の場所に表示する関数です。またその際に条件と合致するデータを選出したり、昇順・降順に並べ替えられます。

基本の関数式

=QUERY（表示させたいデータがある範囲）

別のシート間でデータを表示させることもでき、その場合はデータを表示させたいシートのセルに以下の命令文を記述します。

シート間移動する場合

=QUERY（'表示させたいデータがあるシート名'! 表示させたいデータがある範囲）

詳細を設定する文法
select	"select A"	A列のデータのみを表示する
where	"where 条件"	条件を満たした行のデータを表示する
oeder by	"order by A asc" / "order by A desc"	データをA列の昇順 / 降順に沿って並び替えて表示する

select：指定の列のみ表示させる

FILTER関数の時と同じデータを使って、B列の年齢以外を抜き出して順番を変えて表示させてみます。この時ポイントは以下のようになります。

表示させたいデータがある範囲はA2からE11
データを表示する順番はA・E・D・C列

関数式は「=QUERY（A2:E11, "select A,E,D,C"）」となるので、これを記入すると、画像のようにデータが左から名前、身長、体重、性別の順に並んで表示されました。

where：性別で対象データを抜き出す

次に、whereを用いて男性のデータのみを抜き出します。

表示させたいデータがある範囲はA2からE11
条件を判別するデータの範囲はC列
条件は対象のセルが"女"になっていること

上記のポイントから式を立てると、「=QUERY（A2:E11,"where C='男' "）」となり、結果男性である5人のデータのみが表示されました。

また先ほどのselectと組み合わせることもでき、その場合はselect文とwhere文の間にスペースを入れます。注意点として、それぞれをダブルクオーテーションで囲うのではなく、両方を合わせて一塊に囲います。試しに「=QUERY（A2:E11,"select A,E,D,C where C='男' "）」とすると、A・E・D・C列の順で男性のデータのみが表示されました。

order by：身長で昇順に並び替える

order byを使って、身長で並び替えてデータを表示させてみます。ポイントは以下3点です。

表示させたいデータがある範囲はA2からE11
並び替えの基準にするデータの範囲はE列
昇順で並び替え

これより関数式は「=QUERY（A2:E11,"order by E asc"）」となり、画像のように身長の昇順に合わせて並び替えられたデータが表示されました。

またorder byも前述のselect文、where文と組み合わせることができます。例えば「=QUERY（A2:E11,"where C= '男' order by E asc"）」とすると男性のデータのみが身長の低い順に並べられて表示されました。
また、「=QUERY（A2:E11,"select A,E,D,C where C= '男' order by E asc"）」とすると男性のデータのみが身長の低い順に並べられて、さらに左から名前、身長、体重、性別の順に並んで表示されました。

SPRKLINE関数

SPARKLINE関数とは、セル内に小さなグラフを表示する関数です。グラフの見た目を加工することもできます。

基本の型

=SPARKLINE（グラフにしたいデータの範囲）,{"charttype","グラフの種類"}

charttypeでグラフの種類を指定できます。グラフの種類の部分には以下の単語を挿入します。

line：折れ線グラフ
bar：積み重ね棒グラフ
column：縦棒グラフ
winloss：正負2つの結果を表す縦棒グラフ

line：折れ線グラフ

折れ線グラフは、データの推移を表現する際に用いられます。

見た目を加工する文法
color	"color","カラーコード"	グラフの色を指定する
linewidth	"linewidth",数値	グラフの線の太さを指定する
ymax / ymin	"ymax / ymin",数値	縦軸の最大値 / 最小値を指定する
xmax / xmin	"xmax / xmin",数値	横軸の最大値 / 最小値を指定する

試しに、お店のデータを用いて客数の推移をグラフにしてみます。SPARKLINE関数はデフォルトで折れ線グラフが選択されているため、画像上部のように「=SPARKLINE(B2:G2)」と範囲を指定しただけの関数式でも自動で折れ線グラフが作成されます。
しかしそのままでは見た目が粗雑なので、上記の見た目を加工できる文法を使って行きます。

グラフの色は黄緑（#AAC863）
グラフの線の太さは3
グラフの縦軸の範囲は2000〜4500

以上の3つを満たす式は「=SPARKLINE（B2:G2,{"color","#AAC863" ; "linewidth",3 ; "ymax",4500 ; "ymin",2000}）」で、これを入力すると画像下部のようにみやすくなったグラフが表示されました。

bar：積み重ね棒グラフ

積み重ね棒グラフは、データの全体量と各要素の割合を比較する際に用いられます。

見た目を加工する文法
color1 / color2	"color1","カラーコードまたは色の名称"	グラフで使用する 1つ目 / 2つ目の色を指定する
empty	"empty","zero / ignore"	空のセルを 0として表示 / 非表示させる
nan	"nan","convert / ignore"	セルが数字ではない場合に 0として表示 / 非表示させる
rtl	"rtl", true / false	グラフを右 / 左から表示させる
max	"max",数値	横軸の最大値を指定する（最小値は常に0）

2010年、15年、20年の電力生産データを用いてグラフを作成してみます。基本の関数式では画面上部のようなグラフが出来上がりました。

基本の関数式

=SPARKLINE（データの範囲,{"charttype","bar"}）

色はピンク（#EF858C）と水色（skyblue）
グラフの横軸の最大値は12000

ここで上記2つの調整を加えていくと式は「=SPARKLINE（データの範囲,{"charttype","bar";"color1","#EF858C";"color2","skyblue";"max",12000}）」となり、画像下部のようなグラフが表示されました。

原子力・エネルギー図表集　第1章1 世界および日本のエネルギー情勢 - エネ百科（JAERO 日本原子力文化財団）

column：縦棒グラフ

縦棒グラフは、同じ指標で複数のデータを比べるためのグラフです。

見た目を加工する文法
color	"color","カラーコードまたは色の名称"	グラフ内の縦棒の色を指定する
highcolor / lowcolor	"highcolor / lowcolor","カラーコードまたは色の名称"	グラフ内の最大値 / 最小値の色を指定する
firstcolor / lastcolor	"firstcolor / lastcolor","カラーコードまたは色の名称"	グラフ内の最初 / 最後の縦棒の色を指定する
negcolor	"negcolor","カラーコードまたは色の名称"	グラフ内の全ての負の縦棒の色を指定する
empty	"empty","zero / ignore"	空のセルを 0として表示 / 非表示させる
nan	"nan","convert / ignore"	セルが数字ではない場合に 0として表示 / 非表示させる
axis	"axis", true / false	軸を表示 / 非表示させる
axiscolor	"axiscolor","カラーコードまたは色の名称"	軸の色を指定する
rtl	"rtl", true / false	グラフを右 / 左から表示させる
ymax / ymin	"ymax / ymin",数値	縦軸の最大値 / 最小値を指定する

とあるお店の収支データを使ってグラフを作成します。基本の関数式では、画像上部のようなグラフが表示されました。

基本の関数式

=SPARKLINE（データの範囲,{"charttype","column"}）

ここに以下3つの調整を加えます。

全体の色は黄緑（#9ACD32）、最大値の色は赤（red）、最小値の色は青（blue）
セルが数字ではない場合は0として表示
軸を表示

すると関数式は「=SPARKLINE（データの範囲,{"charttype","column";"color","#9ACD32";"highcolor","red";"lowcolor","blue";"nan","convert";"axis",true}）」になり、画像下部のカラフルなグラフが作成されました。

winloss：正負2つの結果を表す縦棒グラフ

正負2つの結果を表す縦棒グラフは、正負（プラス・マイナス）や勝敗、コインの表裏を視覚的に表すときに用いられるグラフです。

見た目を加工する文法
color	"color","カラーコードまたは色の名称"	グラフ内の縦棒の色を指定する
highcolor / lowcolor	"highcolor / lowcolor","カラーコードまたは色の名称"	グラフ内の最大値 / 最小値の色を指定する
firstcolor / lastcolor	"firstcolor / lastcolor","カラーコードまたは色の名称"	グラフ内の最初 / 最後の縦棒の色を指定する
negcolor	"negcolor","カラーコードまたは色の名称"	グラフ内の全ての負の縦棒の色を指定する
empty	"empty","zero / ignore"	空のセルを 0として表示 / 非表示させる
nan	"nan","convert / ignore"	セルが数字ではない場合に 0として表示 / 非表示させる
axis	"axis", true / false	軸を表示 / 非表示させる
axiscolor	"axiscolor","カラーコードまたは色の名称"	軸の色を指定する
rtl	"rtl", true / false	グラフを右 / 左から表示させる
ymax / ymin	"ymax / ymin",数値	縦軸の最大値 / 最小値を指定する

例として、AとBという2つのスポーツチームの今期の成績（勝敗）をグラフにしてみます。勝ちの場合は1、負けの場合は-1とし、基本の関数式を用いると、画像上部のように黒い四角が複数並んだグラフが表示されました。

基本の関数式

=SPARKLINE（データの範囲,{"charttype","winloss"}）

ここにさらに以下の調整を加えてみます。

全体の色は赤（red）、負の縦棒の色は青（blue）
空のセルを0として表示

すると式は「=SPARKLINE（データの範囲,{"charttype","winloss";"color","red";"negcolor","blue";"empty","zero"}）」となり、入力するとグラフは画像下部のようなわかりやすいものに変身しました。

条件付き書式を用いたデータ分析やデータクレンジング（データ整備）

ここまで関数の使い方を見てきたけど、関数以外にも便利な機能があるって聞いたんだけど...？

そう、条件付き書式という機能を使えばデータをデータのままで見やすくすることができます。具体的にどんな風に使うのか見ていきましょう！

基本の使い方

条件付き書式とは、セルの値によって、条件ごとの書式を反映させる機能です。
文字や数字にかかわらず、以下のような条件に沿って太字か否か、斜体か否か、文字の色、背景の色などを設定できます。

空白である / ではない
〇〇を含む / 含まないテキスト
〇〇で始まる / 終わるテキスト
〇〇と完全一致するテキスト
日付
〇〇より前 / 後の日付
〇〇より大きい / 小さい（〇〇未満）
〇〇以上 / 以下
〇〇と等しい / 等しくない
〇〇〜△△の間にある / ない

とある会社の社員の出勤データを例に条件付き書式を設定してみます。

まず、データのセル範囲を選択しながら、メニューの「表示形式」をクリックします。するとさらにメニューが表示されるので、下の方に出てくる「条件付き書式」をクリックします。

画面右端に「条件付き書式設定ルール」という項目が表示されるので、「セルの書式設定の条件」という部分から「完全一致するテキスト」を選びます。
今回は下のマスに終日出勤していることを指す「all」を入力します。

書式設定のスタイルでは背景色を緑に設定して、完了ボタンを押します。

また「＋条件を追加」というボタンをクリックし、同じように「am」と「pm」の背景色をそれぞれオレンジと黄色にしてみました。すると画像のように、真っ白だった出勤簿が3色に色分けされ、誰がいつどの程度出勤しているのか、どのようなワークスタイルをおくっているのかが把握できるようになりました。

カラースケール

数値がずらっと並んだデータの集まりは、パッと見では特徴の把握ができません。そこで、条件付き書式のカラースケール機能を使ってそれらの数値に大小で異なる色をつけると、データの特色が一目瞭然になります。

とあるテーマパークの今年上半期の入場者数データでカラースケールを試してみます。
まず、基本の使い方同様に、データのセル範囲を選択しながら、メニューの「表示形式」をクリックします。するとさらにメニューが表示されるので、下の方に出てくる「条件付き書式」をクリックします。

画面右端に「条件付き書式設定ルール」という項目が表示されるので、右上の「単一色」となっている部分で「カラースケール」をクリックします。

書式ルールから色の組み合わせを細かく設定することもできます。

今回は、値が低いほど背景色が赤くなり、値が高いほど緑になり、中間であれば白くなるように設定しました。

すると、画像のように綺麗に色分けされました。
結果からは、年始の時期と、3月中盤から4月上旬までの春休み期間、5月頭のゴールデンウィーク期間に客数が増えていることがわかります。

また、天気データと組み合わせて考察したところ、特に赤くなっている、つまり客足が落ちている日には雨が強かったり、風が強かったことがわかりました。

おわりに

今回の記事では、Google Spreadsheet（グーグルスプレッドシート）を活用して簡単にデータ分析やデータクレンジングをする方法を、具体例を交えて解説しました。「データ分析」は初心者でも無料で簡単に始められるとおわかりいただけたでしょうか。この記事を読んでデータ分析に興味を持っていただけたら幸いです。

インターネット・アカデミーではデータ分析やデータ管理の講座をご用意しております。データについて詳しく学んでみたいという方は、ぜひ無料カウンセリングでお気軽にご相談ください。