LoginSignup
7

More than 5 years have passed since last update.

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

Last updated at Posted at 2016-12-09

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

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

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
7