Googleスプレッドシートには、Excelにはない特別な関数がいくつかあります。それらはスクリプト無しでまた、特別な機能を使わずにデータの塊の中から、色々な条件でデータを塊で取り出すことが出来ます。Excelもいよいよ2019よりこの配列関数をスピルという機能としてサポートするようで、Filter関数、Unique関数、Sort関数などが導入されることになりました。

前回の記事で、Query関数の最もよく使う事例についてまとめました。今回はそこでは扱いきれなかった部分についてまとめてみたいと思います。利用頻度は高くないと思いますが、スプレッドシート上でデータベース的な扱いのできる機能ですので、ケースによっては非常に便利じゃないかなと思います。

※正直これだけ多彩なことができる関数は多分他にはないと思います。

今回使用するスプレッドシート

※内部でUrlfetchAppを使っている関数があるため、一度スクリプトエディタで実行させて承認をしてください。

データの結合をさせる

Accessなどのデータベースがとても便利といえるのは、テーブル同士を結合してクエリを作り、新たなテーブルとして利用することが出来る点にあります。最近はExcelでもPowerQueryなどのツール(2016から標準搭載)があり、また範囲をテーブル化出来るようになったこともあって、特にクエリ機能については習得すべき大きな機能になっています。

2つのシートを単純に結合する

単純に同じ形式のデータの塊を1つのデータの塊に結合する方法です。クエリの最も基本的なものですね。しかし、数式は少し特殊です。

  1. なお、範囲の指定は{}を使ってくくり、範囲同士は;で繋げるというルールがあります。
  2. また範囲の結合時にはカラムの指定は、1個目のカラムならば、Col1、2個目ならばCol2といった表記を使います。
  3. そこで、条件で例えば空は除く場合には、Where Col1 is not nullと指定します。
  4. ただし、where条件で指定のColが文字列の場合には、Where Col1 != “”でも抜き出せます。
  5. この単純な結合は、Query関数を使わなくても可能です。
  6. select文を省略していますが、使用することも可能です

図:2つの範囲を結合してみた

他のスプレッドシートの範囲を結合する

同じファイル内であれば、前述の数式で結合が可能です。しかし、他のシートとなると単純に結合はできません。しかし、Googleスプレッドシートにはimportrangeという優れた関数で呼び出す事が可能です。importrange関数自体は、=importrange(“シートのID”,”範囲指定”)で可能です。

このimportrangeをQuery関数内で使用して結合をします。以下のような構文になります。

今回は、Where条件としてCol4が500以上、つまり金額が500以上のレコードを抽出して結合しています。

また、importrangeではなく、Query文同士で結合も可能になっています。手法的にはQuery文の中でQuery文を使う構文になります。

図:複雑だけれど、すごく便利です

疑似外部結合を実現する

Query関数には残念ながら、INNER JOINやLEFT JOINのような結合に関するオプション項目がありません。Visualization APIにはJOINメソッドがあり、キー同士で結合の出来るメソッドがあるのですが・・・必ずしも、同じものではないようです。しかし、データベースではこの結合方法はよく使うテクニックで、これが出来ると利用幅が広がるのに。Google SpreadsheetにもPower Queryのようなツールが欲しいところですね。

さて、これを実現させる方法として、VLOOKUPARRAYFORMULA関数を組み合わせて実現する方法を使ってみました。

Accessではよく使うテクニックですが、こうする事で2つのテーブルの「キー」を元にリンクして1つのテーブルを作る事が可能です。解説すると・・・

  1. メインのマスタからは外部キー以外の部分を選択しておく(D3:E12の部分)
  2. iferrorでエラーがでたら何も表示しないようにしておく(外部キーに存在しないキーがある場合の対処)
  3. arrayformulaにてvlookup関数の結果を配列で返すようにする
  4. vlookup関数では1つの値ではなく、範囲で値を指定(外部キー)。ターゲットの範囲はその外部キーを持つ別のマスタを参照。
  5. 絶対値で参照させている

複雑な構文なので多用できないのと、Arrayformula関数自体が難しい概念の関数なので、利用する時は注意書きを添えておくと良いでしょう。

図:2つの異なるテーブルをキーで結合して1つの表に

特殊な抽出オプション

NULL値を基準にデータを抽出

データが空のもの、そうでないものを抽出する機会が以外と多いです。Accessのクエリではこれを用いて不一致や重複などのクエリを作ったりもしますね。データが空とはつまり「NULL」なので、IS NULLで空のデータ、IS NOT NULLで空ではないデータを抽出することが可能です。Where条件に加えて使用します。

今回は3列目が空のデータを操作してみました。

図:空白がつまりNULLです。

正規表現を用いてデータを抽出

Googleの関数系やメソッドは「正規表現」が使えるものが多い印象。その正規表現を用いて合致するデータを抽出するオプションが、Query関数にもあります。その際にWhere条件に使用するのがMATCHESです。正規表現が使えると、ほとんどのデータをこれ一つで処理ができますが、正規表現自体がとっても難しいものなので、無理して使わなくても良いと思います。

よくあるのは、メールアドレスの形式ルールに従ってるかどうかチェックなどなど。。。今回は「ベニ」を含むキノコ名を抽出してみました。

図:便利だけれど難しくてどうにも苦手・・・

指定文字列を含むデータを抽出

指定文字列を含んでいるものだけを抽出はよく利用します。どの位置であっても良いので、とにかくその文字を含んでるものは全て抽出したいというわりと大雑把な抽出だからこそ、利用シーンは多いですね。

この時に使うのがWhere条件にCONTAINSです。Drive APIのsearchメソッドなどでも見かけましたね。また、逆に「含まない」というケースもありますね。その場合は、NOTで条件をくくればOKです。

図:ベニを含むキノコだけを抽出してみた

ワイルドカードを使って抽出その1

前方一致、後方一致、部分一致といった類の抽出方法で、Where条件にLIKEを使い、通常はアスタリスクを使うのですが、Query関数では「%」を使って表現します。キノコの名前のうち「タケ」で終わるものだけを抽出するようにしてみました。

例えば、「ベニ%」とすれば、ベニで始まるキノコとなり、「エ%ギ」とすると、エから始まりギで終わるキノコといった具合に柔軟に抽出が可能となります。

図:後方一致で見事に抽出できた。

ワイルドカードを使って抽出その2

もうひとつのワイルドカードとしてアンダーバー(_)を使った抽出法があります。これは、アンダーバーの数 = 文字数に合致するものを抽出するというもので、4つ使えば4文字のデータということになります。こちらもその1同様に「エ__ギ」といった指定をすれば、エからはじまり、ギで終わる4文字のキノコという事で抽出が可能です。

同じく、WhereにてLIKEを使います。

図:4文字のキノコを抽出してみた

頭文字が一致するレコードを抽出

ワイルドカードが使えれば、使う機会はないのですが、手っ取り早く、前方一致(頭の文字で一致するもの)を抽出するためのオプションがstarts with句です。whereに続けて列を指定し、starts with ‘頭文字’といった具合に入れると、頭の文字が一致するレコードを抽出してくれます。

図:ドクから始まるきのこを抽出

後ろ文字が一致するレコードを抽出

こちらもあまり使う機会は無いと思いますが、後方一致(後ろの文字が指定の文字で終わるデータ)を抽出するためのオプションがends with句です。where条件に列を指定し、ends withに続けて文字列を入れると、抽出が可能です。ワイルドカードでももちろん代用可能。

図:タケで終わるきのこを抽出してます。

グループ化する

Accessではおなじみのグループ化。Group By句を使って同じ名前のものはまとめて、さらにSUMを使うことで総合計を集計できる。リストのデータの対象の列の項目ごとにグループ化を行うので、複数のグループ化も行うことが可能。Pivotでも非常によく利用する。グループ化する場合は、必ずSUMやCOUNTなどでの集計列が必要になるので、使用時は注意。

また、集計関数を使うので、LABEL句を併用する必要もあります。

図:こんな集計表が関数一発でできる魅力

表示形式を編集する

元のオリジナルの表は、例えば数値に対して「個」であったり、「円」といった単位のついた文字列がない場合、表示形式から装飾が可能ですが、オリジナルデータに対して手を加えたくない場合もあります。そんな時、Query関数で特定の列だけ、一時的に装飾をすることが可能です。Format句を使って表現します。

ちなみに、この結果作成された表の値をSUM関数などで集計を掛けてやると、集計されるだけでなくきちんと付けた装飾を継承してくれるので、個とか円の表示がなされます。とても便利。他にも、”format C ‘yyyy年mm月dd日’”といった表記をすれば、元の数値から日付形式を生成も可能です。

図:元の表を弄らなくて済むので利点は大きい

トップ10表示をする

Accessでもよくやるトップ10集計クエリ。全部のデータは必要ないけれど、上位10件だけ欲しいといった表記の集計表が必要なケースは多々あります。この表記もQuery関数で一発で可能。この際に利用するのは、limit句となります。ほぼ必ず、ORDER BY句も併用してソートするのが定石です。ORDER BY はDESCで降順、ASCで昇順になります。limitの後の数値を変えればトップ○○は変更可能

図:TOP10も簡単に作れてしまう。恐ろしい

下位ランキング

前述のlimit句とは異なり、逆に下位○○のランキングも必要なケースはあるかもしれません。ブービーは誰なのか?といったゲームなどでは使う機会がありそうですね。この表記もQuery関数なら一発です。offset句を利用します。limit句同様にORDER BY句を併用するのが定石。但し、limitと異なり、これは上位○○分のレコードを省くという意味なので、指定する数値は、今回の事例だと8を指定すると、10レコード出すことができました(全部で18レコードあるので、8を省くと下位10レコードになるため)。ここが厄介なポイントですね。

図:ブービーは新宿支店のモウセンゴケ800円

ちょっと変わった高等テクニック

総合計を付け加える

Query関数はこのように大変便利なのですが、その集計結果に手動で毎回SUMなどの数式を入れてる人も多いと思います。しかし、この場合、Query関数の計算結果、配列が広がった場合、このSUMの数式の場所が邪魔して、エラーになってしまいます(上書きされないため)。

そこで、配列数式を利用して、Queryの計算結果に対していわゆる総合計を加える手法があります。主に、QueryでGroup Byでグループ化合計を取ったものに対して、総合計をつけるような事例で利用します。このケースの場合、数式が広がっても、自動的に総合計がついて来てくれます。

数式に{}がついてるのが特徴。だからといって、arrayformulaでやってみるとエラーになります。また、この時使用する2つ目の式のSUMでQueryの小計を参照して合計を取ろうとすると、循環参照となりエラーになります。なので、参照すべきは元の表です。試しに1つ新しい支店の情報を加えてみると、手動でSUMで合計取った時との大きな違いがわかります。

※WHERE条件などを加えてフィルタしてしまってる場合にはSUMではなくSUMIFSなどで同じようなフィルタ合計を取る数式を加えると良いです。

図:自動総合計を付け加えられる

Pivotの結果の行列入れ替え

あまり使う機会はないと思いますが、pivotを使ったクロス集計結果の行と列を入れ替えたい場合、数式を変えるのではなくTranspose関数を使うことで行列転換が可能です。

図:縦横転換はQuery関数の結果に対しても有効だった

Pivotの縦の結果に総合計を加える

上記の2項目をさらに組み合わせて、pivotの結果に対して総合計を加えるというテクニックを使うことも可能です。かなり複雑なので理解するのに時間が掛かると思いますが、総合計を自動で加えることが可能になるので、非常に便利です。Transpose関数ももちろん利用しますし、Group Byも利用します。。

理屈としては、Pivotで取ったQuery関数の配列結果と、別に作った特定項目のGroup Byで集計したQuery結果をTransposeで反転、これを配列数式で結合させたものになります。なので、総合計の行は別の数式で作ってるわけです。ですので、この結果を更にTransposeで反転させれば、行の合計に早変わりになるわけです。

図:クロス集計の各列の総合計を取ることも可能

図:反転させれば行の合計となる

Pivotの縦横の結果に総合計を加える

列の合計までは前項までに出しました。しかし、さらに行の合計も同時に欲しい場合があります。というか集計表を名乗るなら、縦横にそれぞれ総合計を設けるのが定石でしょう。それを行うには、かなり複雑な数式になるので、何段階かに分けてqueryの結果を容易し、Transposeで反転させては、またそれを結合させたりするという作業を行います。

ただしこの作業を行う場合には、一番最初のQueryでのPivotは自分が欲しい行列の組み合わせとは逆のものを用意しておく事に注意してください。1回必ずTransposeで反転が必要ですから。また、それぞれの総合計列も1回transposeするので、LABELの付け方に注意が必要です。

手順を簡単に説明すると

  1. まずは目的のクロス集計とは縦横逆のものをQuery関数で作っておく
  2. 次にA列を基準にGroup Byで集計したQuery関数の結果をTransposeで反転させたものを作る(これが3.にて、行の総合計になる)
  3. 1.と2.を配列数式で結合させたものを作り、Transposeで反転させる(ここで目的のクロス集計の形になる)。
  4. 次にC列を基準にGroup Byで集計したQuery関数の結果に、総合計を加えて、配列数式で結合させたものを作り、Transposeで反転させてやる(これが列の総合計になる)
  5. 3.と4.の数式を配列数式で結合させる。

こんな感じ。Query関数はトータル3回使います。Transpose関数も同じく3回使います。これで、要素が追加されても、自動的に縦横それぞれに総合計が加わるので、以降は結果に手動でsumで数式を作る必要はありません。

図:作るのは大変だけれど、1度作ればメンテフリー

番外編

マトリックス図をリストに変換

Microsoft PowerQueryには「ピボット解除」という機能が装備されています。これは、よくあるクロス集計表のような形で集計されているものを、集計前のリスト表に変換してくれる非常にありがたい機能なのですが、GoogleスプレッドシートおよびQuery関数にはそのような関数は装備されていません。これは、Transpose関数とは意味が異なり、実際にデータの二次利用をする上では、リストでないと困るシーンがたくさんあります。

これを実現するためにはユーザ定義関数として、Google Apps Scriptで作成する必要があります。Query関数に装備してほしい機能ですね。

図:リストに分解すると二次利用がしやすくなる

GASでVisualization APIを使う

本来、Visualization APIとは、JavaScriptなどから使う為のライブラリであるので、Google Apps Scriptから使うのは正攻法ではないのですが、UrlfetchAppを利用することで、アクセスする事が可能です。これをスプレッドシートの関数として使ってみました。結論としては、使えました。ただ、参照させたシートが「他のシートを結合」であったので、ちょっと遅かったです。単体のシートならば実用できるかも。

この手法であればQuery関数にはない、Visualization APIの機能を使ってアクセスさせる事も可能です。ただし、UrlfetchAppを使っている関係で、一度スクリプトエディタ内で実行させて承認をさせないと利用ができません。また、1日に利用できる回数も限られているので、大量に使う事はできないので、使い所を見極める必要があります。

関連リンク

共有してみる: