この記事は16551 回閲覧されました。

Google SpreadsheetにはExcelにはない概念の特殊な関数があります。Excelの場合、関数が返せる値は1つのみですが、Google Spreadsheetは自作関数であっても、この特殊な関数であっても、配列で返す事が可能です。よって、ExcelではMSQUERYやデータソース機能を使わないと出来ない複数のレコードデータで返す事のできる関数は非常に利用価値の高いものです。

これら関数を使いこなせると、Excelでは常識であった複数の関数の組み合わせやテクニックは不要になります。しかも、レコードを追加された場合に動的に返せるので、ちょっとしたAccess的なデータベース構築が可能になります。今回は、そんな特殊な関数を紹介します。

目次

使用する関数等

※今回は、使用するスプレッドシート以外にも、ImportRange用のデータのみが入ったシートを2つ用意しています。(data1data2のシート)

ImportRange関数の使い方

概要

この関数は、他のスプレッドシートファイルに入っているデータの塊を取ってくる関数です。Excelのシートのリンクの機能に似ているようでちょっと違うもので、使用する為には関数に対してシートへのリンクの許可を与える必要性があります。非常に便利で、これまでの関数同様、配列で値を返してくれるので、これをSum関数に食わせて合計値を出すといったようなこともできるので、組み合わせればDSUM的な事をやらすこともできる便利な関数です。特徴としては・・・

  1. 馬鹿でかいデータの塊に対して実行しても非常に高速に値を返してくれる。
  2. ちょっと関数の引数の指定方法にクセがある。
  3. QUERY関数同様に、結果をSUM関数等に食わせることが可能である。
  4. 取りたいスプレッドシートのIDとシート名、範囲の指定が必要。

といったところです。クセといっても、取得したいデータが入っているスプレッドシートのIDが必要ということだけで、ほかはそれほど特殊ではありません。

使用例

今回のケースでは、他のシート2つから、今回使用するスプレッドシートへデータをリンクさせます。それぞれのスプレッドシートには、dataシートがあり、A2:Fのレンジデータを取得します。関数の書き方は以下の通りです。Fには終わりのセルを指定していないので、レコードが存在してる分だけ全て取ってくるので、レコードデータの追加があっても、関数の修正は必要ありません。

注意点として、

  1. 引数1つ目は、スプレッドシートのIDを記入。ダブルコーテーションで括ることを忘れずに。
  2. 引数2つ目は、その範囲を指定。シート名!範囲の形で入力する。ダブルコーテーションで括ることを忘れずに。
  3. リンクさせているので、参照元のシートの値が変わると、こちらのシートも変動します。

関数入力しただけでは値が取得できないので、もう一度セルを触って下記のようなアクセス許可ボタンを押します。

importrange

図:ImportRange関数にアクセス許可を与える必要があります。

Filter関数の使い方

概要

この関数は、配列関係の関数では割りと使いやすく使用するシーンも非常に多い、実に効果的な関数です。そして非常に高速にデータの抽出が出来ます。この関数に他の関数を組み合わせれば、正直、DSUM的な事が可能で、やSUMIFSは必要ないんじゃないか?と思うほどです。5000レコード/27カラムのデータでも、なんとか許容できるスピードで表示してくれます。もちろん、単発で抽出ならば数秒も掛からず抽出が可能なので、この関数はGoogle Spreadsheetを使う上では絶対の覚えておきたい関数と言えます(他の機能があまりにも貧弱なので、余計そのように感じると思います)。特徴としては・・・

  1. 馬鹿でかいデータの塊に対して実行しても非常に高速に値を返してくれる。
  2. 通常の関数同様の指定方法に若干拡張された感じなので、QUERY関数よりかは理解しやすい。
  3. 条件指定が複数可能であり、また計算式での条件指定も可能。
  4. 計算結果をSUM関数等に食わせることが可能である。
  5. QUERY関数よりも高速であり、複数のセルにSUMで食わせるような数式を書いても、断然早い。
  6. 日付の条件式は普通にDATE関数やDATEVALUE関数を使用出来る。特に日付の形式は限定されない。

といったところです。とは言え、若干クセがある関数でもあります。

使用例

今回は、dataシートにあるシートのA2:Dの範囲に於いて、D列の値が100以上の値のレコードを返せという関数式を書いてみます。

注意点として、

  1. 複数の条件式を加える時は、カンマに繋げて条件式を加えて行けばOKです(AND条件となります)
  2. 比較演算子に於いて、一致しないものを抽出する場合には、<>を使用します。
  3. ImportRangeとは違い、範囲指定でダブルコーテーションで括る必要性はありません。但し、比較の文字列はダブルコーテーションで括ります。

応用例

SUM関数に食わせる事例

レコードで返してくれる便利な関数ですが、他にも帰ってきた配列データを更にSum関数に食わせる事で、その合計値を出す事が可能です。以下のような数式を作ります。

合計したい範囲を指定し、それに条件式を加えた数式をSUM関数で括るだけです。集計表等でsumifs関数の代用として使うことが可能です。

OR条件を実現する

通常、Filter関数は条件式を続けていくだけなので、AND条件になりますが、OR条件を使いたい場合があります。しかし、この場合の数式はちょっとだけトリッキーですので、注意が必要です。以下のような数式を作ります。

上記の式は、A2:Aの抽出がキーワード1かキーワード2にヒットするものが抽出されます。ポイントとして、

  1. 条件式を()で括る
  2. それぞれの条件式を+演算子でつなげる。カンマで区切らない。

Query関数の使い方

概要

この関数は他の2つと比較すると、非常に使いにくい関数です。また、関数の組み方スタイルが独特なのでとっつき難い人が多いでしょう。しかし、SQLの使えないスプレッドシートに於いて、この関数は非常にありがたい関数です。数式が非常にSQLライクな感じなので、VBAなどでSQL文を書いてる人は割りと習得しやすいのではないでしょうか?特徴として、

  1. 馬鹿でかいデータの塊に対して実行しても非常に高速に値を返してくれる。
  2. 通常の関数とは異なり、かなり関数内でのパラメータの指定方法が独特である。
  3. SQLチックなパラメータ指定であるため、非常にとっつきにくい。
  4. キーとなるパラメータの値を特定のセルから取りたい場合、日付・数値・テキストで指定方法に違いがある。
  5. SQLチックな文章の中で集計やカウントなどの指定も出来ますが、妙な文字列が1行目に出るのが非常に気になる。
  6. Query関数で帰ってきた値を通常のSUM関数などに食わせて、集計などが出来る。SUMIFやDSUMなどは不要になる。
  7. パラメータの指定をレンジではなく、セル単位で指定が可能なので、DSUM関数のようなパラメータが特定レンジで指定しないといけないといったことがないため、非常に融通がきく。
  8. ピボットタイプ(クロス集計)の集計が可能である。
  9. データのタイトル部分を関数内で構築が可能である。

ここでは、ピボットスタイルの集計抽出に関しては記述しません。Google Spreadsheetで入力データをクロス集計的にする方法を参照してください。抽出時に1行目のタイトル文字列を構築する方法についても、記述してあります。

注意点

この関数を使う上での注意点が結構あります。以下にそれをまとめてみました。

  1. Query関数内ではカラム行は使用しないので、範囲指定でもそれらの行は含めない(そのため、A2から指定している)。
  2. A列、B列などで指定するが、select文の中でsum(C)といったような書き方も可能ではある。しかし、1行目にsumという文字列が出力されたりするので、通常は1.のようにsum関数に食わせるのが通常。
  3. Where以外にもGroup Byなどが使用できる。
  4. 条件式の指定に特定のセルの値を参照させたい場合には、””で括って、中に&&で括ったセル番地を指定する。
  5. 但し、数値ではない場合(文字列)には、”で括った中に5.の書き方をしなければならない(ここ重要)。日付の場合も同じ。
  6. 更に日付を指定する場合には、6.の前に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関数に食わせてセルに反映を多様するには実用的ではない。

使い方

簡単な使い方からまず。*も使用可能なので、指定範囲の列を全て出す場合には、活用しましょう。条件式としてF列の数値が100より上のレコードを抽出するといった事例の場合には、以下のような数式となります。

また、特定のセルの値を抽出条件として取り、さらに日付を抽出条件として取るようなケースでは、

こんな書き方になります。D24の値が文字列の抽出で、ポイントの4,5がコレに該当します。また、日付の場合dateを頭に付いてるが、これがポイントの6に該当しています。この計算結果を更にSUM関数に食わせて合計を出すといった事も可能です。VBA内で変数を抽出条件として使う感覚なので、プログラミング的な数式の作り方だと言えます。

Image関数の使い方

概要

この関数は非常にユニークな関数で、セルの中に画像を表示させる事が出来るという関数です。Google Spreadsheetは画像を挿入出来るのですが、その配置は他のオブジェクトと同じで、セルとは関係なく貼り付けられてしまいます。対してこの関数の場合、URLを関数に渡すと、セル内に画像を配置できるので、画像はセルの位置に属します。使いようによっては、非常に有用な関数です。

imagefunction

図:画像一覧や商品一覧なんかも作れますね。

使い方

使い方自体は非常に簡単です。以下のように、image()の中に画像への直リンクURLを貼ればOK。直リンクでなければ表示されずエラーになりますので、注意。スプレッドシートを元に書類や申請書をジェネレートしてる場合、画像の挿入よりも、上部に於けるレイアウトの自由度やきっちり収める上ではこの関数は非常に役立ちます(ロゴ入れたりとかね)。

重要なのは、使い方で単純に直接関数内にURLを入れる場合には、上記のような感じで良いのですが、折角のスプレッドシートなので、セルの値を用いて

としても良いでしょう。また、現在すでにdeprecated扱いになってはいますが使えるGoogle Chart APIにてURLに文字列を投げて、QRコードを生成しても良いでしょう。WebAPIにて、RESTで投げると画像で返してくれるものであれば、なんでも利用出来ます。最近はあまりそういうサービスを見かけなくなりましたが。

更には、Google DriveやPhotosの画像の直リンクを取得して、Google Apps Scriptなどを併用して貼り付けるのも面白いと思います。但し、Google DriveのケースにおいてGoogle Apps for Workだと書類やファイルの外部からの参照や共有を通常は出来ないように管理者が設定してると思われるので、この方法を試しても画像が表示されません。image関数で使用出来る画像のURLは、ウェブに一般公開され誰でも見られるリンクでなければなりません。当然、社内のイントラ内の画像も使えません。

arrayformula関数

概要

この関数は他の関数と違ってそれそのものは特に計算をするわけでもなく、値を引っ張ってくるというわけでもないという特殊な関数です。では何をする為の関数かといったら、以下のような使い方をする関数です。excelの配列数式とは別物です。

  1. 特定の関数や数式を1つづつ入れることなく、まとめてそれらの計算を行えるようにしてくれる
  2. 本来配列指定や配列で返せない関数や計算式を配列で返せるようになる
  3. 一つづつ計算式を入れるよりも、高速に計算が可能である。

これだけだとピンと来ないのですが、使い方を見ていただければ、その有用性がわかると思います。但しsum(A2:C3)としたものをArrayformulaで囲っても計算は出来ません。値が配列で返せていないからです。

使い方

今回のサンプルのスプレッドシートでは2パターンの使い方を記述してあります。ちょっととっつきにくい関数ですが、利用方法がわかれば、すぐに身につけることが出来ます。今回の事例では、vlookupをまとめて行う場合と、その結果を使ってまとめて掛け算をする数式をつくってみます。

sumifに対して使う場合

通常、sumifは別の表から、特定の条件と合致した値の合計を算出する関数です。以下のような使い方になります。

上記の式では、L2の値を持ってるレコードをA2:A20より探し出し、合致してる場合にはC列の値を合計した数字を出せという事になります。そしてこれを通常は一個つくったら、ドラッグして複製するわけなのですが、Arrayformulaを併用すると以下のようになります

通常は1個である条件式をL2:L6という範囲の形にする事で、配列で返ってくるようになります。これで、条件の範囲分計算がされるので、ドラッグで数式コピーは必要ありません。計算も高速です。

図:sumifsを使った場合の事例

vlookupに対して使う場合

通常、vlookupは別の表から、特定の値を持つレコードを探し出し、そのレコードの何列目の値を持ってくるというのが使い方です。ですので、例えば以下のような数式になるわけです。

上記の式ではA2の値をdataシートのA2:D20から見つけ出し、その4列目を絶対値で拾ってくるという式になります。そして、この数式を通常は必要な数だけ複製するなりセルに入力する必要があります。また列が変更になった場合、当然数式を設定した数だけ修正が必要になるわけです。また、値はあくまでも1個しか返しません。しかし、Arrayformulaを併用すると、以下のようになります。

vlookupの値の指定がちょっと変わりました。これはA2:A20の値に対してそれぞれvlookupでdataシートのA2:D20から探しだし、4列目の値を絶対値で拾ってこいという数式になります。この数式1個で、この数式を入れた列の必要な分だけ、自動で値が入るようになります。また、数式は1個しか使っていないので、計算が早く、また数式は1個しか使っていないので、修正は1個で済むというメリットがあります。

図:C2に数式が入っていますが、C3以降は数式ではなく返ってきた値が入っています

単価*数量をまとめて計算する場合

基本的にはvlookupの事例の応用です。これも通常は数式を必要な数だけ用意しなければなりません。ですので、通常は

といった数式を入れるはずです。これで価格*数量=合計金額が算出されるわけです。これも、Arrayformulaをかませると、同じことが1個の数式で実現が可能です。

指定範囲*指定範囲で計算をそれぞれ行ってくれます。また、Query関数やFilter関数の事例と同じく、Sum関数に食わせると配列で返ってきた値を合算するというテクニックが使えます。例えば

で、合計金額をそれぞれ計算した結果(配列)をさらにSum関数で合算するという事が可能です。計算式がシンプルになるので、ぜひ、繰り返し数式をいれるようなシーンがあるのであれば、積極的にarrauformulaを使ってみると良いでしょう。

関連リンク

Pocket
このエントリーをはてなブックマークに追加
Bookmark this on Yahoo Bookmark
Pocket