spreadsheet
関数
モーニング娘。

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

More than 1 year has passed since last update.


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

Livesenseその2 Advent Calendar 2016

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

今年は三枚で大変という話を受けて、あなたの頼みなら!と二つ返事でオッケーしてしまったんですが、

タイトルどおりエンジニアではないのです。

事務員がエントリーする。世の中不思議がいっぱいです。

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


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

普段の業務でデータ集計が結構多いこともあり、せっかくですし、

アドベントカレンダー参加を機に新しい関数を覚えることにしました。

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


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



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


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

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

3.png

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

なにを思ったか自宅でSpreadsheetを開いて「あ~~集計するデータどうしよ~!?」と なったので、

元データは私が好きなものとさせていただきました。


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

データが載っているシート(シート名:hello)

※ 元データは公式&wikipediaさまのお力を借りました。

1.png

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

2.png

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

qt4.PNG

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

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


★ ARRAYFORMULA + MID/SEARCH

qt5.PNG

おもに名前検索とよみ検索で使用しました。

ARRAYFORMULAを使用すると、

1セルに関数を入れ込むだけで展開してくれるのでとても楽です。

qt6.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


  1. 検索シート名前に入力した文字数分、元データのB列「名前」を指定した位置から調べて抽出

  2. 1で抽出した名字/名前が、検索シートに入力した文字と一致してるか?

  3. 一致してれば「2」、していなければ「-」を返す

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

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

9.png

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

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



  • 川麻琴

  • 久住


  • 田さくら

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

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

qtt1.PNG

黄色い線を引いた列は完成後は非表示にしてたのですが、

名前/読み検索については

ほぼ「文字入ってなければ空白(IF($B$4:$B$41="","")」の役割で使用してます。

特別用意する必要はなかったのですが、文字数の確認と、

別の抽出/集計で楽をしたかったので入れました。

さて、その別の集計。

検索シートの他の入力欄に対応するようにビシビシ関数を入れていきます!

せっかくなのでレギュラーのみなさまのちからを借ります。

(別の関数使って集計すんなよとかArrayFormulaを使った意味は聞かないでください…。


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

qt20.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

■ 誕生月

意味
関数

元データの生年月日を月の数字引っ張る
=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

入力した文字に対し、集計しているのがこのシートです。

2列めに数字を振ってるのですが、該当する結果があればその数字がセルに入っていきます。

U列でL~T列の合計値を出してるのですが、該当するデータが多いほど合計値の数字も大きくなります。

また、L~T列の中で1つでも"-"があれば、""を返すようにしています。

これは複数条件を入力した場合、内1つでも当てはまればそれがMAX値となってしまうのを防ぐためです。

U列の関数

=IF(COUNTIF(L4:T4,"-")>=1,"",SUM(L4:T4))

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

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

qt13.PNG

すると

qt23.PNG

ちゃんと増えてますね。

一番左のIDが、元データと紐付いている数字、メンバーを区別しているものです。

ID34の合計値が一番高いようですね。

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

qt24.PNG

IDをキーにメンバーの合計値を引っ張ってきます。

※ もちろんVlookupでも大丈夫です。

I列の関数

=INDEX(Sheet!$A$4:$U$41,MATCH(A2,Sheet!$A$4:$A$41,0),21)

長々とお付き合いいただきすみません。

そしてようやくQuery関数の出番です。


★QUERY関数

qt16.PNG

qt14.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

神奈川県出身の1999年生まれの小田さくらちゃんを抽出することができました~✌(・_・)✌

※ 当てはまるメンバーがいかなったときは「イナイヨー」って出るようにしてます。

qt19.PNG

すごく不格好でも関数ゴリ押しでなんとかなるもんですね。

今回これを作ったことで、GASを早急に覚える必要性を感じてきました。

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

これで10日めのアドベントカレンダーはおしまいです。

ここまでお読み下さりありがとうございました!

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


おまけ

qt17.PNG


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


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