More than 5 years have passed since last update.

Livesenseその2Advent Calendar 2016

@7404

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

Last updated at 2016-12-10Posted at 2016-12-09

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

Livesenseその2 Advent Calendar 2016、
二桁目突入の10日目を担当させていただきます、7404と申します。

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

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

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

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

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

ARRAYFORMULA

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

QUERY

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

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

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

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

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

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

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

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

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

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

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

★ ARRAYFORMULA + MID/SEARCH

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

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,"-")))))

…。

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

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

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

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

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

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

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

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

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

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

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

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

※ テーブル上段が左側の表(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文字以外の文字が入った場合警告が出るようになってます。

■ 誕生月

意味	関数
元データの生年月日を月の数字引っ張る	`=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,"-"))`

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

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

データ呼び出し

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

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

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

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