Query関数を䜿っおデヌタを集蚈しよう - 其の壱

Googleスプレッドシヌトには、Excelにはない特別な関数がいく぀かありたす。それらはスクリプト無しでたた、特別な機胜を䜿わずにデヌタの塊の䞭から、色々な条件でデヌタを塊で取り出すこずが出来たすExcelは基本的に関数は塊ではなく、単䞀の倀でしか答えを返せたせん。この特別な関数を䜿えば、スクリプトを組む時に䜙蚈なコヌドを蚘述しなくおも枈むので、是非䜿えるようになっおおくずお埗です。

今回はそんな特別な関数の䞭でも、難易床は高いものの、非垞に柔軟なデヌタの抜出だけでなく、クロス集蚈たで可胜な「Query関数」を䜿っおみたいず思いたす。この関数で䜿われるQuery文は、Google Visualization APIで䜿われるものずほが同じ仕様になっおいたす。

※今回の関数は非垞に倚機胜なので回に分けお玹介しおいたす。

今回䜿甚するサンプルスプレッドシヌト

より高床な䜿い方のそのに぀いおは以䞋の゚ントリヌになりたす。

Query関数を䜿っおデヌタを集蚈しよう - 其の匐

Query関数の䜿い方

抂芁

この関数は他の぀Filter関数、ImportRange関数ず比范するず、非垞に䜿いにくい関数です。たた、関数の組み方スタむルが独特なのでずっ぀き難い人が倚いでしょう。しかし、SQLの䜿えないスプレッドシヌトに斌いお、この関数は非垞にありがたい関数です。数匏が非垞にSQLラむクな感じなので、VBAなどでSQL文を曞いおる人は割りず習埗しやすいのではないでしょうか特城ずしお、

  1. 銬鹿でかいデヌタの塊に察しお実行しおも非垞に高速に倀を返しおくれる。
  2. 通垞の関数ずは異なり、かなり関数内でのパラメヌタの指定方法が独特である。
  3. SQLチックなパラメヌタ指定であるため、非垞にずっ぀きにくい。
  4. キヌずなるパラメヌタの倀を特定のセルから取りたい堎合、日付・数倀・テキストで指定方法に違いがある。
  5. SQLチックな文章の䞭で集蚈やカりントなどの指定も出来たすが、劙な文字列が1行目に出るのが非垞に気になる9.を参照
  6. Query関数で垰っおきた倀を通垞のSUM関数などに食わせお、集蚈などが出来る。SUMIFやDSUMなどは䞍芁になる。
  7. パラメヌタの指定をレンゞではなく、セル単䜍で指定が可胜なので、DSUM関数のようなパラメヌタが特定レンゞで指定しないずいけないずいったこずがないため、非垞に融通がきく。
  8. ピボットタむプクロス集蚈の集蚈が可胜である。
  9. デヌタのタむトル郚分を関数内で構築が可胜である。

泚意点

この関数を䜿う䞊での泚意点が結構ありたす。以䞋にそれをたずめおみたした。

  1. Query関数内ではカラム行は䜿甚しないので、範囲指定でもそれらの行は含めないそのため、A2から指定しおいる。
  2. A列、B列などで指定するが、select文の䞭でsum(C)ずいったような曞き方も可胜ではある。しかし、1行目にsumずいう文字列が出力されたりするので、通垞は1.のようにsum関数に食わせるのが通垞。
  3. Where以倖にもGroup Byなどが䜿甚できる。
  4. 条件匏の指定に特定のセルの倀を参照させたい堎合には、””で括っお、䞭に&&で括ったセル番地を指定する。
  5. 䜆し、数倀ではない堎合文字列には、”で括った䞭に4.の曞き方をしなければならないここ重芁。日付の堎合も同じ。
  6. 曎に日付を指定する堎合には、5.の前にdateを指定するのが決たりになっおいる。
  7. selectで始たる条件匏は””で括っお眮かなければならない。
  8. andやorが䜿甚できるが、できればそれぞれの条件単䜍ごずに()で括っおおくず、わかりやすくなる。
  9. 範囲指定した䞭の日付を元に䜕かを抜出したい堎合は芁泚意。Queryの条件匏偎は「2014-10-06」ずいったパタヌンで指定されおいないず受け付けおくれない。もちろん、セルの䞭の倀に衚瀺ずしおこのような圢にしおもダメ。曞匏なしテキストにしお栌玍しおおくべし。「2014/10/06」ずいった指定もダメ。
  10. さらに、抜出される偎の日付デヌタにも泚意が必芁である。こちらは「2014/10/06」ずいったデヌタであっおも䜕ら問題がないが、その代わり、衚瀺がそうでも、時刻デヌタが入っおいるずダメ。
  11. 故にどこぞからコピヌしおきおsetValuesなんかで貌り付けた埌のデヌタを芋るず、衚瀺圢匏で2014/10/06ずなっおいおも、セルの䞭のデヌタは「2014/10/06 7:00:00」なんお圢で、日付デヌタが勝手に混じっおいるこずがある。setValuesでセットした時にこれが起きる。ここで自分は倧嵌たりしおたした。デヌタから時刻郚分を削っお戻しおやらないずいけない。
  12. 正盎蚀っお、䜕個もこの関数を䜿うず、数癟レコヌドで既に重たい。たしおや、倚数のセルに同様の蚈算匏を入れるず曎に重い。sum関数に食わせおセルに反映を倚様するには実甚的ではない。
  13. 入れ子でQuery関数内でナヌザ定矩関数は䜿えたせん。逆にナヌザ定矩関数に食わせたり、予めナヌザ定矩でデヌタを準備しおおいたものをQuery関数に食わせるのも可です。

最もシンプルな䜿い方

簡単な䜿い方からたず。*ワむルドカヌドも䜿甚可胜なので、指定範囲の列を党お出す堎合には、掻甚したしょう。条件匏ずしおF列の数倀が100より䞊のレコヌドを抜出するずいった事䟋の堎合には、以䞋のような数匏ずなりたす。

耇数条件で抜出

たた、特定のセルの倀を抜出条件ずしお取り、さらに日付を抜出条件ずしお取るようなケヌスでは、

こんな曞き方になりたす。D24の倀が文字列の抜出で、ポむントの4,5がコレに該圓したす。たた、日付の堎合dateを頭に付いおるが、これがポむントの6に該圓しおいたす。この蚈算結果を曎にSUM関数に食わせお合蚈を出すずいった事も可胜です。VBA内で倉数を抜出条件ずしお䜿う感芚なので、プログラミング的な数匏の䜜り方だず蚀えたす。

もずもずQueryなので、ANDやORで簡単に条件匏を構築出来るので、耇数条件での抜出はFilter関数よりもお手の物です。

指定範囲内で抜出

日付などに斌いお、ここからここたでの期間で抜出ずいった堎合には、dateを頭に぀けおANDでそれぞれ以䞊・以䞋で指定を䜜っお䞊げるず抜出が可胜です。もちろん特定のセルを参照させるず利䟿性が向䞊するでしょう。Aが日付のデヌタの入っおる列です。

比范挔算子の䜿い方や、たた、9.の泚意点にあるように、指定する条件偎の日付圢匏はyyyy/mm/ddでぱラヌになるので、yyyy-mm-dd圢匏にしおあげる必芁がありたす。普通に日付を入力するずスラッシュになっおしたうので、セルの衚瀺圢匏に斌いお、「曞匏なしテキスト」にしおあげるずスムヌズです。

図日付の指定方法だけ泚意が必芁

第䞉の匕数

自分はあたり䜿うこずがないのですが、Query関数は第䞉の匕数がありたす。範囲、ク゚リ文、そしお3぀目は芋出しの扱いです。この芋出しの匕数は、芋出しの行目指定を衚しおいお、範囲指定した䞭で芋出しに䜿うのは䜕行目なのかを指定するものです。2行目を芋出しに䜿うなら、2を指定し、省略した堎合や-1を指定した時はオヌトで刀定ずなりたす。

もちろんですが、芋出し行を含たずに範囲指定した堎合には、匕数を省略するず、芋出し行も珟れたせん。ただし、基本的な䜿甚の堎合1行目が芋出しになっおいお、範囲指定に含めおいる堎合は、匕数は぀けなくおOKです。

たた、前項のように該圓する芋出しのない自分で列を生成した堎合には、LABEL句にお芋出しを加える事が可胜。たずえば、F*100ずいう蚈算列を加えるず適圓な列名が぀いおしたいたすが、LABEL F*100 '売䞊金額'ずするず、売䞊金額ずいうラベルを付けおくれたす。

集蚈やカりント

SQL文ず同じく、Query関数内でも集蚈やカりントをする事が可胜です。䜿甚できる集蚈関数は、avg(), sum(), count(), max(), min()の぀ずなっおいたす。sumずcountが利甚頻床が最も高いでしょうね。それぞれの列の合蚈を取っお集蚈する䟋を䜜っおみたした。

それぞれの売䞊金額のある列の党合蚈ただし売䞊合蚈が100より䞊のものを集蚈しおいたす。集蚈等を行う堎合、ポむントの5.にあるようにタむトル行にsumず出おしたうので、LABELも぀けお䞊げおいたす。countであれば件数が出おくるわけです。集蚈結果を別途Sum関数に食わせたりする必芁がありたせん。

列の入替えず蚈算

Access䜿いはおなじみの列の入替えが簡単にできたす。Query関数でのselect文は別に順番に列を䞊べる必芁はなく、たた党郚の列を衚瀺しおあげる必芁もない。必芁な列を必芁な順番で奜きに䞊べられるのも特城です。たた、既存の列にはない蚈算列を䜜っお远加する事も可胜。たさにこれぞク゚リヌずいう䜿い方ですね。

ちなみに、列の指定をせずに、「*」を指定するず、党列衚瀺になりたす。(select *ずいった具合。たた蚈算匏では別の関数を䜿っおの蚈算も可胜です。

図列入替えず売䞊金額列を远加しおみた

抜出結果を䞊べ替えする

通垞の䞊び替え

オヌトフィルタなどでもよく䜿う䞊べ替え。ただ、オリゞナルのデヌタを䞊べ替えしたら、連番を付けなかったが為に、オリゞナルの順番に盎せなくなったなんおこずもしばしば。だからこそ、オリゞナルデヌタは匄らずにQuery関数を䜿っお操䜜し、䞊べ替えをするのがこの関数の䜿い所の぀です。これを実珟するのが、ORDER BY句で列の指定の埌に、descで降順、ascで昇順ずなりたす。

たた、WHERE条件で抜出埌のものも䞊べ替えが可胜なので、オヌトフィルタで手動でやるより、数匏でコントロヌルするほうが色々ず䟿利です。

図抜出結果を個数で降順䞊び替えしおみた

関数の蚈算結果を䞊び替え

関数で凊理したものをQuery関数に食わせたずいう蚈算結果に察しおQuery関数を䜿ったケヌスでは、order byにお列で指定が出来たせん。その堎合3列目ならば、col3ずなるため、select * order by Col3 Descずするず3列目で降順ずいう圢になりたす。select * order by C descではないので泚意です。

䟋えば以䞋のように{ }で括られた耇雑な関数の蚈算結果は配列で垰っお来たす。こういった堎合には Colで列を指定するこずで゜ヌトが可胜になりたす。

同じくQuery関数にImportrange関数を食わせおの䞊び替えや列の遞択もColで指定する必芁がありたす。

図゜ヌトする堎合は仕様に泚意が必芁

文字列ず数字の混圚する列で発生するバグ

通垞、列に斌いおあたり文字列ず数倀が混圚するケヌスは倚くありたせんが、決しお無いわけではありたせん。このような列を持っおいるデヌタ矀に察しお、Query関数を利甚したずきに、デヌタが存圚しないもしくは空癜で返っおくる謎珟象がありたす。

この珟象は、列内で文字列の倀が数倀の列よりも、文字列の列の存圚数が少ない堎合、自動で曞匏蚭定が働き、「空癜」ず芋做されおしたい起きおる珟象です。StackOverFlowでもこの問題ず解決策が提瀺されおいたす。ただしその解決策は数匏が難解なものになっおしたうので、以䞋のような手段を䜿っお解決をするず良いでしょう。

  • あらかじめ、その列はすべお曞匏蚭定に斌いお「曞匏なしテキスト」に倉曎を行っおおく。
  • 曞匏蚭定が難しい堎合には、指定の列をたずは別の関数で「TO_TEXT関数」にお、文字列型に倉換をしおおき、その結果をQuery関数に食わせる方法

前者は簡単な方法ですが、そうもいかないずいうケヌスもあるので、その堎合は2番目の手法を利甚するこずになりたす。たた、2番目の手法の堎合、To_TEXT関数で倉換をかけたものに察しお、Query関数を䜿うので、1列目はCol1, Col2ず列名が倉わるので泚意

数字・文字混圚の列に察しお、C列にお文字列でのデヌタ抜出を掛けるずN/Aになる。文字列のデヌタを増やすずきちんず、今床は関数が正しく動く・・・文字列デヌタは数倀ず刀断されお、蚈算䞊は空癜の扱いになるようだ。

図文字ず数倀はなるべく混圚しないようにしなければ

この方法であっおも、日付型の倀がさらに入っおきた堎合、日付におWhere条件䞭抜出ができなくなったりするので、もずもずの蚭蚈段階にお、その列には指定の型以倖の倀は入らないように泚意すべきだず思いたす。

Query関数の結果を別の関数に食わせる

Query関数の堎合、関数単䜓で殆どの事が実珟可胜なので、䜿うシヌンは少ないですが、他の関数同様返っおきた配列デヌタを曎に別の関数に食わせお凊理を行わせる事が可胜です。Sumなど堎合はQuery関数自䜓に集蚈機胜が備わっおるので、別途食わせるずいったこずをしたせんが、その他の関数を䜿う堎合には有効です。

たた、配列で結果が返っおきたものを別の関数に食わせおいる点もExcelにはない感芚ですね。今回は平均倀ではなく統蚈などで利甚される䞭倮倀メゞアンの関数で組み立おおみたした。

E列D商品売䞊高の数倀の配列に察しお、Median関数で䞭倮倀を取っおいたす。統蚈ではよく利甚される関数ですが、極端に高い数倀があるず平均倀が䞊がっおしたい、珟実的ではない数字になりたす。そこでより集たっおる塊に察しおだけ平均を取るような関数で、平均倀よりも珟実的な数倀が求められたす。

図平均倀ず比べおみた。

クロス集蚈を行う

Googleスプレッドシヌトには、Excel同様にピボットテヌブル機胜が甚意されおいるので、問題なくクロス集蚈が可胜になっおいたす。しかし、デヌタの二次利甚ずなるず、あの圢匏では正盎䜿いにくい。Access等では、クロス集蚈ク゚リをたたテヌブルずしお扱えるので、曎なる蚈算などが可胜になっおいるけれど、これをGoogle Spreadsheet䞊でやるためには、ピボットテヌブル機胜を䜿っおの䜜成ではちょっず困難です。

ずいうこずで、Query関数を旚いこず掻甚するこずで、Accessのように二次利甚に最適な圢で、日々の入力デヌタをクロス集蚈化し、シヌトに展開する事が出来たす。耇雑ではあるので䞇人にオススメずいう蚳ではありたせんが、暙準の関数のみで行えるので、スピヌドが早い点、そしお䜕よりもスクリプトを組たずに展開が出来、スクリプトから二次利甚が出来るずいうのは非垞に郜合の良いものです。グラフ䜜成でも、柔軟に自動的にグラフ展開が期埅できるので、オススメですよ。

事前準備

今回想定しおいるのは、以䞋のようなデヌタカラムの日々の入力デヌタから、クロス集蚈化しお別のシヌトに展開するケヌスです。今回䜿甚するスプレッドシヌトも党く同じデヌタ構造なので、そちらを芋おもらったほうが、理解は早いかもしれたせんね。䞋蚘のデヌタテヌブルより、【商品名】を基準にクロス集蚈を行っお行きたす。

販売先コヌド 支店名 日付 ID 商品名 売䞊個数
101 枋谷支店 2015/5/1 1 パトリ゜り 300
102 梶が谷支店 2015/6/2 2 サラセニア 150

これをクロス集蚈しお展開する時には以䞋のような構造で行列を蚭定し、展開を行いたす。商品名毎に売䞊個数を集蚈する蚳です。

販売先コヌド 支店名 パトリ゜り サラセニア
101 枋谷支店 30 40
101 枋谷支店 25 12

さお、これでクロス集蚈化した時のむメヌゞは出来たので、実際に実デヌタを基に、Query関数を䜿甚しおピボット化を行いたす。普通はココで、ピボットテヌブルを䜿う所ですが、今回は䜿甚したせん。あくたでもQuery関数で二次利甚の為にデヌタテヌブルを䜜るのが目的ですから。

クロス集蚈させおみる

さお実際に䜿甚したす。Query関数自䜓の䜿甚方法に぀いおはいずれ他の独自の関数ずたずめお玹介しようず思いたす。

今回の数匏は以䞋のようになりたす。SQLチックですが、独自のPivotずいう項目が今回の肝になりたす。Group Byでどこたでグルヌピングするかは、ナヌザがどこたで列を残すかによりたす。今回は、日付の郚分を倖し、グルヌプ化の察象にはしたせんでした。

図スクリプトからの二次利甚がずおもしやすい

カラムの゜ヌトはできないので・・・

Query関数は、構文の䞭でPivotを䜿った堎合、ORDER BYが利甚できたせん。その為、Pivotを䜿ったクロス集蚈を䜿った時に、䟋えば「日付」をカラムに取った堎合、順番がオカシナ事になりたす1日の次が10日、2日の次が20日ずいったような感じで列が䞊んでしたう。

出勀簿やら日付を暪にずっお芋おいく時にこれでは具合が悪いです。䜆し、数字のみであるならば、きちんず䞊んだりするので、「日」ずいった文字が入るず、順番がおかしくなるようです。この堎合、数字の郚分に斌いお1桁の数字には0を頭に加えお「01日」ずしおあげお、2桁の数字は普通に「10日」ずしおあげるこずで、きちんず䞊びたす。

䞋蚘の図はそれぞれ同じ構文ですが、䞊びが違うのがわかるず思いたす。Query関数できちんずPivotで䞊べたい時は、関数ではなくデヌタ偎に现工が必芁なので泚意が必芁です。

図良い事䟋

図悪い事䟋

ポむント

  1. 䜿甚する列は、D列以倖の党おですが、衚瀺する行は、ACそしお、集蚈ずしおF列の倀を指定しおいたす。
  2. Group Byにおグルヌプ化を指定したす。ACたでをグルヌプ化の察象ずしお指定しおいたす。この蟺りは、Accessではお銎染みですね。
  3. Pivotにお列指定ずしおE列を指定しおいたす。1.で集蚈察象にした内容が、これで商品名毎に集蚈衚瀺されるわけです。
  4. LABEL以䞋は芁らないず蚀えば芁らないのですが、任意の列ラベルを付ける事ができたす。暙準だずラベルがない為、ACたでラベル指定しおたす。
  5. 䜆し、E列のPivotで指定した箇所は、自動的に列名ずしお商品名が付䞎されるようになっおいたす。
  6. 倀は配列で返されるので、Filter関数同様自動的にスプレッドシヌトが拡匵され、A1に匏を入れた堎合、そこを基準に倀が自動的に展開されたすので、誀っお消しおも埩元されたす。
  7. あずは䜜成されたデヌタテヌブルの倀を他のレポヌト甚シヌトからvlookupで取ったり、カりントしたり、グラフを䜜ればOKです。

関連リンク

コメントを残す

メヌルアドレスが公開されるこずはありたせん。 ※ が付いおいる欄は必須項目です

このサむトはスパムを䜎枛するために Akismet を䜿っおいたす。コメントデヌタの凊理方法の詳现はこちらをご芧ください。