非エンジニアがおくる 関数ゴリ押しデータ抽出&集計

# はじめまして、こんにちは。

[Livesenseその2 Advent Calendar 2016](http://qiita.com/advent-calendar/2016/livesense_2)、
二桁目突入の10日目を担当させていただきます、7404と申します。

今年は三枚で大変という話を受けて、[あなたの頼みなら！](http://qiita.com/masahixixi)と二つ返事でオッケーしてしまったんですが、
タイトルどおりエンジニアではないのです。
事務員がエントリーする。世の中不思議がいっぱいです。

土曜日ですし、ゆるりと見ていただけますと幸いです。

## 今回のテーマ「関数を覚えるために関数で遊び倒す」

普段の業務でデータ集計が結構多いこともあり、せっかくですし、
アドベントカレンダー参加を機に新しい関数を覚えることにしました。

以下のSpreadsheet関数で行ったデータ集計法についてまとめたいと思います。

- [ARRAYFORMULA](https://support.google.com/docs/answer/3093275?hl=ja)

 > 配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。 

- [QUERY](https://support.google.com/docs/answer/3093343?hl=ja)

> Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。


他、INDEX,MATCH,VLOOKUP,MID,SEARCH,IF,ISBLANK,COUNTA,COUNTBLANK…などおなじみのメンバー。

ちらっと手順記載前に、完成したものはこちら↓

![3.png](https://qiita-image-store.s3.amazonaws.com/0/146336/16572314-7206-1899-27dd-32934841cadb.png)

QUERY関数を使って条件に当てはまった結果を抽出しています。

**なにを思ったか自宅でSpreadsheetを開いて「あ～～集計するデータどうしよ～！？」と なったので、
元データは私が好きなものとさせていただきました。**


### 抽出元となるデータと作成したシート

データが載っているシート(シート名：hello)
※ 元データは公式&wikipediaさまのお力を借りました。

![1.png](https://qiita-image-store.s3.amazonaws.com/0/146336/ae518d07-355f-da11-1cb6-65b13bc2fcf5.png)

それとは別に絞込/検索用のシート(シート名：Search)

![2.png](https://qiita-image-store.s3.amazonaws.com/0/146336/4beaee7b-737f-5595-3617-9717a42bf363.png)

入力した文字に対しあれこれする集計シート(シート名：Sheet)を作りました。

![qt4.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/61175148-21c1-da87-bcc2-6651a2a5f6a0.png)


もうこの時点ですでに関数ゴリ押しの荒業感がひどいですが、気にせず書きます。結果は出ます。

まず、入力した文字に対してどう加工するか、です。


## ★ ARRAYFORMULA + MID/SEARCH

![qt5.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/37dd808e-9687-38b7-6f3a-83112c10abc3.png)


おもに名前検索とよみ検索で使用しました。
ARRAYFORMULAを使用すると、
1セルに関数を入れ込むだけで展開してくれるのでとても楽です。

![qt6.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/d9f854d2-bdd9-7fb7-88a2-71e7dfb9f1d3.png)


L5以下には関数が入ってません！

L4に入力した関数は

`=ArrayFormula(IF($B$4:$B$41="","",IF(MID(Hello!$B$2:$B$39,1,LEN(Search!B5))=Search!B5,2,IF(MID(Hello!$B$2:$B$39,FIND(" ",Hello!$B$2:$B$39&" ")+1,LEN(Search!B5))=Search!B5,2,IF($B4:$B41=Search!B5,2,"-")))))`

…。

かなりの荒業で結果を返してしまってるので、細かいところはお好みでおねがいします。
ざっくりですが、この関数がなにをしてるかと言うと、

![qt5.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/85de5308-98d2-8710-639e-4349fe43879a.png)

1. 検索シート名前に入力した文字数分、元データのB列「名前」を指定した位置から調べて抽出
2. 1で抽出した名字/名前が、検索シートに入力した文字と一致してるか？
3. 一致してれば「2」、していなければ「-」を返す

 ※ 元データは、名前と読みの間には半角スペースを入れて苗字と名前を区別して調べられるようにしています。やたら長いIF分岐はそれです。


■ 検索用のシート/名前に「小」を入れてみます。

![9.png](https://qiita-image-store.s3.amazonaws.com/0/146336/e30a967f-299a-b72a-cacf-11bbf9844d76.png)


小=1文字なので名字or名前の一文字目を返し、「小」と一致してるか見ています。

今回「小」に該当するメンバーは、

- **小**川麻琴
- 久住**小**春
- **小**田さくら

の3人です。ちゃんと結果が出ていますね。

集計シートには他にもこんなやつが。

![qtt1.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/c0912f93-2d2d-d419-f09c-1ccf1b2ae661.png)


黄色い線を引いた列は完成後は非表示にしてたのですが、
名前/読み検索については
ほぼ「文字入ってなければ空白(IF($B$4:$B$41="","")」の役割で使用してます。
特別用意する必要はなかったのですが、文字数の確認と、
別の抽出/集計で楽をしたかったので入れました。

さて、その別の集計。
検索シートの他の入力欄に対応するようにビシビシ関数を入れていきます！
せっかくなのでレギュラーのみなさまのちからを借ります。
（別の関数使って集計すんなよとかArrayFormulaを使った意味は聞かないでください…。


### 既に長いので ダッシュでお送りします。

![qt20.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/29235666-5bb9-bef0-ab34-29e779ed1a76.png)

※ テーブル上段が左側の表(B～J列)に入っている関数、
下段が右側の表(L～T列)に入っている関数です。
関数が入ってるセルと入ってないセルを調べる関係で、ISBLANKとIFを使い分けます。

■ 生まれた年

| 意味 | 関数 |
|:-:|:-:|
| 元データの生年月日を左から4文字引っ張る  | `=IF(OR(Search!$B$7="",LEN(Search!$B$7)<>4),"",LEFT(VLOOKUP(A4,Hello!$A:$D,4,0),4))`  |
|  検索シートと入力した文字と一致してる？ |`=IF(ISBLANK(Search!$B$7),"",IF(D4=Search!$B$7,8,"-"))`   |

検索シートには4文字以外の文字が入った場合警告が出るようになってます。

![qt7.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/14bf3a56-65ed-4442-b0fe-8f56103f5e21.png)


■ 誕生月

| 意味 | 関数 |
|:-:|:-:|
| 元データの生年月日を月の数字引っ張る  | `=IF(OR(Search!$B$8="",LEN(Search!$B$8)>2),"",MID(VLOOKUP(A4,Hello!$A:$D,4,0),6,2))` |
|  検索シートと入力した文字と一致してる？ |`=IF(E4="","",IF(TEXT(Search!$B$8,"00")=E4,16,"-"))`|

このとき、入力した数字が一桁だった場合、0を補完するようにしてます。
1～9月生まれを調べるなら、03でも3でも調べられるようになってます。
また、入力された数字が3桁だったら調べません。

■ 現時点の年齢

| 意味 | 関数 |
|:-:|:-:|
| 元データの年齢を引っ張る |`=IF(OR(Search!$B$9="",LEN(Search!$B$9)>2),"",Vlookup(A4,Hello!$A:$E,5,0))`|
|  検索シートと入力した文字と一致してる？ |`=IF(F4="","",IF(Search!$B$9=F4,32,"-"))`|

これも誕生月とやってることは一緒です。

■ 色（現メンバーは現在の、卒業メンバーは最後）

| 意味 | 関数 |
|:-:|:-:|
| 元データの色を引っ張る | `=IF(Search!$B$10="","",Vlookup(A4,Hello!$A:$F,6,0))`  |
|  検索シートと入力した文字と一致してる？ |`=IF(ISBLANK(Search!$B$10),"",IF(ISERROR(FIND(Search!$B$10,G4)),"-",64))`  |

結構各自の担当カラーが「青」と「ブルー」って言い方違ったりするので、
色々考えた結果、今回は泣く泣くどちらでも引っかかるようにしました。
ただし、飯窪さんの「ハニー色」は「黄・イエロー」では出ません。これは譲歩できません。

■ 期（グループ加入したときの）

| 意味 | 関数 |
|:-:|:-:|
| 元データから何期か引っ張ってくる | `=IF(Search!$B$11="","",VLOOKUP(A4,Hello!$A:$G,7,0))`  |
|  検索シートと入力した文字と一致してる？ |`=IF(H4="","",IF(OR(H4=Search!$B$11,H4=Search!$C$11),128,"-"))` |

これだけOR検索してます。

■ 出身地

| 意味 | 関数 |
|:-:|:-:|
| 入力した文字数分元データから出身地を引っ張ってくる | `=IF(Search!$B$12="","",LEFT(VLOOKUP(A4,Hello!$A:$K,11,0),LEN(Search!$B$12)))`  |
|  検索シートと入力した文字と一致してる？ |`=ArrayFormula(IF($I4:$I41="","",IF(MID(Hello!$K$2:$K$39,1,LEN(Search!$B$12))=Search!$B$12,256,"-")))` |

例えば、東京都出身のメンバーだったら、東でも東京でも出るようにしてます。


■ 在籍（現役メンバーか卒業メンバーか）

| 意味 | 関数 |
|:-:|:-:|
| ❍、×どっち？ | `=IF(Search!$B$13="","",IF(Vlookup(A4,Hello!$A:$I,9,0)="-","○","✕"))`  |
|  検索シートと入力した文字と一致してる？ |`=IF(J4="","",IF(Search!$B$13=J4,512,2,"-"))` |

検索シートには、在籍メンバーなら"❍",卒業したメンバーなら"×"と入力します。
在籍メンバーの元データの卒業日には"-"を入れてます。

これで集計準備はばっちりです。

## データ呼び出し

![qt21.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/d4fe9801-a47e-b183-8645-b1bde7b816b5.png)



入力した文字に対し、集計しているのがこのシートです。
2列めに数字を振ってるのですが、該当する結果があればその数字がセルに入っていきます。
U列でL～T列の合計値を出してるのですが、該当するデータが多いほど合計値の数字も大きくなります。
また、L~T列の中で1つでも"-"があれば、""を返すようにしています。
これは複数条件を入力した場合、内1つでも当てはまればそれがMAX値となってしまうのを防ぐためです。

U列の関数
`=IF(COUNTIF(L4:T4,"-")>=1,"",SUM(L4:T4))`


後ほど、U列の中で一番値が大きい合計値をQuery関数で使います。

もうちょっと検索シートに文字を入れてみます。

![qt13.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/a0313ca1-4eaa-5c78-55de-d58433bdc1d0.png)

すると

![qt23.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/23e410c3-5d9f-4f89-6db2-54e05badaa6d.png)


ちゃんと増えてますね。
一番左のIDが、元データと紐付いている数字、メンバーを区別しているものです。
ID34の合計値が一番高いようですね。

元データのシートではこうなってます。

![qt24.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/a7632983-2d75-079a-045c-f4fe1d575a08.png)


IDをキーにメンバーの合計値を引っ張ってきます。
※ もちろんVlookupでも大丈夫です。

I列の関数
`=INDEX(Sheet!$A$4:$U$41,MATCH(A2,Sheet!$A$4:$A$41,0),21)` 

長々とお付き合いいただきすみません。
そしてようやくQuery関数の出番です。

### ★QUERY関数


![qt16.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/4fe0496f-aa8b-6bbd-5542-b69a9c0cec09.png)


![qt14.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/2beb00f0-b263-264e-db98-d1a05fa6b579.png)

■ 入力した関数
`=IF(Sheet!L1="","",QUERY(Hello!$A$2:$L$39,"SELECT B,C,D,E,F,G,H,I,J,K where L = "&Sheet!L1&""))` 

ちょっと不格好ですが、
「MAXの数字に該当するデータを持ってきてね」です。
元データの中で、抽出したい列をSELECTの後に入れ込み、
その中で当てはまる条件のみを抽出したい場合はWHEREで指定します。

今回の条件でしているのは WHERE = U列の中で一番数が大きい合計値　です。
さっき集計シートで 計算してたのはこれに使うためでした。

さて、今回検索シートに入力した条件すべてに該当した場合のMAX値(WHERE句の条件)ですが

- **名字/名前のはじまりが「小(一致してれば集計シートで+2)」**
- **生まれた年が「1999(+8)」**
- **出身地が「神奈川(+256)」**

266です。
これに当てはまるメンバー…一人いましたね。

![qt18.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/25aa4695-4da9-d682-f386-fce49e9a081b.png)


**神奈川県出身の1999年生まれの小田さくらちゃん**を抽出することができました～✌(・_・)✌
※ 当てはまるメンバーがいかなったときは「イナイヨー」って出るようにしてます。

![qt19.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/384c76c5-870a-ee53-19af-c4e5ce38a2fe.png)


すごく不格好でも関数ゴリ押しでなんとかなるもんですね。
今回これを作ったことで、GASを早急に覚える必要性を感じてきました。

今後はもっとスマートに頑張りたいと思います。

これで10日めのアドベントカレンダーはおしまいです。
ここまでお読み下さりありがとうございました！

明日は共にクレイジーな土日にしようと約束した（してない）あの方が再登場です。


##### おまけ

![qt17.PNG](https://qiita-image-store.s3.amazonaws.com/0/146336/691b5b43-2951-9c6d-4104-c35c0713aaa9.png)


- [FILTER](https://support.google.com/docs/answer/3093197?hl=ja)

> ソース範囲をフィルタ処理して、指定した条件を満たす行または列のみを返します。

で、遊ぶのも楽しそうですね。