Googleスプレッドシート QUERY関数 超応用例 4（QUERYでevalしてみよう！）

Googleスプレッドシートの最強関数 QUERY関数について書いたnoteの第4回です。

前回は第2引数のクエリ文の全体感を理解しました。公式非掲載のネタにも少し触れています。

さすがにQUERY関数を紹介する 真面目な noteが続いたので、今回は脱線して超応用例というか QUERY曲芸 のようなものを取り上げるネタ回です。

QUERY関数を基礎から学びたい！ 正しい用法・用量でQUERY関数を使いたい！という人には、あまり役に立たないかもしれませんｗ

ここまでのQUERY関数シリーズはマガジンにまとめております。1～3を読んでない方は、是非こちらも確認ください。

今回のゴール：セルに入れた計算式の文字列を動かしたい（計算して結果を出したい）

今回やりたいことは 👆コレです。

基本編：一つの式で一つの計算式の文字列を実行させる

B列に入れた 演算子を使った計算式（四則演算）の文字列、この計算結果を C列に出力したい！というお題です。

もちろん

結果がマイナスになるケースや+や-など演算子を複数利用するケース、また足し算、引き算よりも掛け算、割り算を先に計算する点を考慮する必要があるケース、さらにカッコ内の計算が優先となるケース。

これらに対応した式となっています。

応用編：全角対応や演算子の応用、スピル対応

応用では 少し式を改良するので数式は長くなりますが

このようにB列を見やすく 数字を全角文字、間にスペースを入れて間隔をあけて、小学生でもわかる ×（かける） や ÷（わる） を使っても計算できる式に改良。

そして

A2:A7に入れた 計算式の文字列を B2セルに一つだけ式をいれて全て計算させるスピル式。

さらにもう1段階改良して

このように C2セルに入れた一つの式で、チェックをつけた 行だけ計算し結果を出力する式。

これらを作っていきます。

画像を見ていただければわかる通り、全て

=QUERY(【ここがお題の式】)

このようなQUERY関数の式で実現しています。

今回はこんなお題に挑戦していきましょう！

「セルに入力した数式を別のセルで計算する」を実現する EVALUATE

この「セルに入力した数式（文字列）」を計算させるは、通常はVBAやGASの出番となります。

イコールを付けても数式は動きださない

たとえば B2セルに入った 頭に = のついていない、 1+8 は 単なる文字列です。

これを隣のC2セルで

このように &で、頭に "=" を連結させても、それは "1+8"という文字列が、"=1+8" という文字列に変わっただけで、数式として機能はしません。

INDIRECT関数の強化版みたいな、セル範囲だけでなく演算子やシート関数の文字列が数式として扱える関数があれば良いのですが、残念ながらシート関数では この手の処理は パターン分けして処理する以外方法がなく、汎用的に使える良い方法がありません。

これはGoogleスプレッドシートだけではなく、Excelも同様です。

EVALUATE は魔法の言葉（でもVBAです）

古来より 、この「セルに入力した数式を別のセルで計算する」方法として、Excelには EVALUATEという手法がありました。

やり方は田中先生のサイトを参照いただくとして、

簡単に紹介すると 名前定義の中で EVALUATEという マクロコマンドを使う手法です。

上の画像は 名前定義で、範囲 =EVALUATE(B2) を eval と定義して、セルに =eval と入力することで、左隣のセル内の数式（の文字列）を計算した結果を出力しています。

この特徴（メリット）は 演算子の計算だけでなく、シート関数やセル範囲も数式として機能するという点です。

SUM(B5:B7) や CONCAT(E5:E7) という 文字列が SUM関数、CONCAT関数としてセル範囲を参照して機能しているのがわかりますね。

ただし、これは実態は VBAなんで、そのまま xlsxファイルとして保存が出来ません。

また、作成して保存した xlsmファイルを Web版で開いても EVALUATEは機能しません。

GoogleスプレッドシートでもGASで evalが使える

ExcelのEVALUATEと同じではありませんが、GoogleスプレッドシートでもGASでJavaScriptの eval() を使うことで

function evaluate(x) {
  return eval(x);
}

こんなシンプルなコードで、セルに入った数式の文字列を計算させるカスタム関数 evaluateを作ることが出来ます。

セルに =evaluate(B2) といれることで、B2セルに記述された計算式を計算した結果を返すことができます。

しかしこの eval()は MDMの解説を見ると

このようにセキュリティリスクの記述があるので、

これ使って大丈夫なの？？

と気にする人がいます。

これに関しては、GASで作成したカスタム関数は非常に権限が制限されているので、カスタム関数でeval()を使う分には、リスクを気にする必要はないと言ってよいでしょう。

また、eval()は残念ながら ExcelのEVALUATEのようにシート関数の文字列を実行させたり、セル範囲の文字列を参照させることは出来ません。

このように ExcelやGoogleスプレッドシートではVBAやGASが必須となる 「セルに入力した数式を別のセルで計算する」という処理、

これを実現できる 唯一のシート関数、それが QUERY関数 です。

QUERY関数でevalしよう！基礎編

それではQUERY関数でevalしてみよう（セルに入力した数式を動かそう）に挑戦していきましょう！

Q1. QUERY関数をC2に入れて B2の "1+8" という文字列の数式を計算させたい

それでは1つ目のお題いってみましょう。ヒントは前回の noteにあります。

B2に入れた 演算子を使った計算式（四則演算）の文字列、この計算結果を C2にQUERY関数を入れて出力したい！というお題です。

もちろんセルをコピペで B4，B6，B8 の式も同様に処理できる式とする必要があります。

一応データを置いておきます。

	計算
足し算	1+8
	
引き算	15-3
	
掛け算	8*6
	
割り算	16/4

考えてみましょう！

↓↓
回答はここから。
↓↓

A1. QUERY関数をC2に入れて B2の "1+8" という文字列の数式を計算させる

まずは回答です。

第1引数がない上に select句が変なことになってる・・・、なんとも奇怪なQUERY式ですよね。

簡単に言うと、これは QUERY関数の 第2引数 クエリ文の

• select句は カンマ区切りで複数列だけでなく、複数の値も指定できる

• select句で指定した 値が数値の場合、算術演算子を使用できる

• 生成された見出しは label句で削除することが出来る

• クエリ文は 文字列であり、これはセル参照を使用することも出来る

このような特性を使って実現しています。

解説していきます。

解説1-1 QUERY関数はselect句に値も指定できる

QUERY関数のselect句は、基本的には第1引数で指定したデータに対して出力したい列を選択するものです。

しかし、select句では 列を指定する 識別子（Col1,Col2..または A,B,C）だけでなく、値を指定することが可能です。

たとえば

このようにCol1,Col5に続いてカンマ区切りで 100という数値を指定すると列が1列追加され、 データ部分に 100、見出しに 100() が入ります。

select句で直接指定した値の 100は、第1引数の（見出しを除く）データの行数の分だけ繰り返されます。

これを応用すると

たとえば上の画像のように

という式で 、Col1の営業担当とCol5の金額の間に 10000という数値を入れ label句で 予算 と指定することで、全員一律であれば予算列を簡単に生成させることが出来ます。

さらに select句の中で演算子が使えるので 

このように元のデータには情報として無かった、予算、さらに達成率をQUERY関数だけで出力することが出来ます。

もし 数値でなく、select句で 文字列を直接指定したい場合は クエリ文のルールに則り 指定したい文字列を 'シングルクォートで括る必要があります。

select句で文字列を直接指定した際に生成されるラベルは 

"頑張ったで賞🌸"()

このようにダブルクォートで括られた形にります。これをlabel句で指定する際はちょっと厄介で

 ""頑張ったで賞🌸""

このようにシングルクォートではなく、ダブルクォート2発で括る必要があります。

また、このselectの句の直接指定が出来るのは単体の値のみで、配列を指定することは出来ません。

配列を指定した場合は、その配列の先頭の値（一番左上）のみが使用されます。（文字列化の時点でそうなっている為）

解説1-2 QUERY関数は 第1引数、select句の列指定を省略できて、演算子も利用できる

それでは select句で Col1,Col2…またはA,B,C… といった列の指定をせず、値の指定のみとした場合はどうなるか？

このように列指定がなくても、第1引数のデータの行数分繰り返された値だけが出力されます。

さらに、もしQUERY関数の第1引数が空白（Null）だったらどうなるか？

このように見出しは勝手に生成されてしまいますが、1行の単体の値を出力することが出来ます。

select 100 のこの部分は 算術演算子を利用することが可能で

このように記述することで 100*2+50 という文字列を計算させることが出来ます。

やりたいことに近づいてきましたね！

あとはこの時に生成される

sum(product(100()2())50())

このようなゴチャゴチャした見出し を消せれば、回答にたどり着けますね！

解説1-3 勝手に生成される見出しは label句の '' で消去できる

QUERY関数では、select句で 集計関数やスカラー関数、算術演算子を使った時、そして直接値指定した時に 勝手に見出しが生成されます。

これは生成を止める方法はありません。だから、この生成された見出しをクエリ句で削除します。

これを削除するのが label句で '' とラベルを空に指定する方法です。

label句は 半角スペースを間に挟んで

このように指定します。

column_idは 列を指定している場合は Col1,Col2…または A,B,C.. のような識別子が入るのですが、select句で値を直接指定した場合は、その値がそのまま入ります。

つまり 上の式の場合、label句を使わなかった時に ラベルとして出力される 100() ではなく、100を指定する必要があります。

また label_string は必ず文字列である必要がある、つまり ’シングルクォートで括る必要があります。

この label_string の中身を空っぽにする、つまり

label 100 ''

とすることで、単にラベルが空白となるわけではなく、ラベルが無くなり上に詰まる（ラベルが削除される）のです。

selectの指定が 値でなく 値と算術演算子の組み合わせ、先ほどの 100*2+50 だった場合も同じように

このようにselect 句で指定したものと同じ指定とすることで、ラベル無しで 100*2+50の計算結果だけを出力できます。

解説1-4 クエリ文をセル参照する際はスペース忘れに注意

あとはこの部分をセル参照とすればよいので、

こうすれば完成です。

ここでの注意点は区切りのスペースを忘れないように！です。

やりがちなのは

✖　=QUERY(,"select"&B2&"label"&B2&"''" )

このように記述してしまうケース。

これ何がダメなのか？ パッと見ではわからない場合は、一度このクエリ文だけセル出力するか、数式バーでこの部分だけを選択して確認してみましょう。

select1+8label1+8''

このようにスペースでの区切りが無い状態なのがわかりますね。

これだとクエリ文は機能しません。

&で連結する文字列側に適切なスペースを入れておくことで、生成されるクエリ文が適切に区切られたものとなり機能します。

QUERY関数で作ったeval式を確認する

それでは回答の式

の動作をコピペして確認してみましょう。

単体セルの四則演算に対してQUERY関数でeval(セルに入力した数式を計算）が出来てますね。

さすがにGASを使ったカスタム関数のeavlと違って、結果が出るが早い点も魅力です。

結果がマイナスになるケースや複数の算術演算子が組み合わされた式、カッコを付けた時の優先度の変化にもしっかり対応してますね。

ただし、あくまでも計算できるのは 数値を対象とした四則演算のみです。（一応先週紹介した通り % で余りを出すことも出来る）

SUMなどのシート関数やB3:B6といった範囲参照の文字列列を機能させることは出来ません。その点はご了承ください。

まずは基礎編クリアです！

ちなみにQUERY関数の式に集約させるために上のような式にしてますが、生成されるラベル部分を除いた2行目だけを INDEX関数で取得する

こんな式でもOKです。こっちの方がわかりやすいかもしれません。

QUERY関数でevalしよう！応用編1（全角対応）

それでは、QUERYのeval式を少し改良して 小学校でも使えるように 計算部分が全角文字だったり、かける　×、割る ÷　も使える式に改良してみましょう。

Q2. QUERY関数をC2に入れて B2の "８ － １０" などの全角や×、÷が混在した四則演算の文字列を計算させたい

	計算
全角＆スペースで間隔あけ	８ － １０ 
	
*じゃなくて×を使用	１０ × ８
	
/ じゃなくて÷を使用	２０ ÷ ５
	
全角カッコ使用	（ １０－ ２） × ４

👆これをA1セルに貼って、C2に式を入れてB2の計算結果を出力させる、コピペで C4、C6、C8 も同様に計算できるようにしたい。というお題です。

ただマイナスっぽい文字は -、ー、―、— と色々あって紛らわしいのでB列の数式文字列で使えるものは

これらに限定します。これだったら、割と簡単。

考えてみましょう！

↓↓
回答はここから。
↓↓

A2. QUERY関数をC2に入れて B2の "８ － １０" などの全角や×、÷が混在した四則演算の文字列を計算させる

回答です。

上の式はLET、LAMBDA登場前の式なんで、今だったら

QUERY関数内でLET関数を使って、2回登場する煩雑な箇所をまとめちゃうのもアリですね。

もちろん QUERY関数に集約させることにこだわらなければ、Q1の回答の最後で使ったlabel句を使わず INDEX関数で2行目だけ取得する式

これもアリです。解説していきましょう。

解説2-1 QUERY関数は演算子の間に半角スペースが入っても問題ない

セル内の普通の算術演算子を使った数式の場合、間に半角スペースが幾つ入っても無視され計算には影響しません。

前回少し触れましたが、QUERY関数の場合も同様で 単語や値の区切りにある半角スペースは幾つ入っても影響しません。

よって セル内の式を見やすくするためのスペースは、半角スペースであればいくら入っていても気にしなくてよいということです。

ただし無視できるのは半角スペースのみで、全角スペースだとエラーになってしまいます。

解説2-2 全角 → 半角 のASC関数、置換関数 SUBSTITUTE関数

ということは、まずは B2セルの全角文字を半角文字に変換すれば良さそうですね。

ここで使うのは ASC関数です。

これによって、全角数字そして全角の演算子が半角となり、

さらに全角スペースも半角スペースに置き換わります

これらは、半角になれば全て問題なくQUERY関数で 計算ができます。

残るは × と÷ ですが、これは 個々に置換するしかないです。

2つなんでSUBSTITUTEのネスト（入れ子）が手っ取り早いでしょう。

=SUBSTITUTE(SUBSTITUTE(ASC(B2),"×","*"),"÷","/")

これで半角化され、かつ ×は*に、÷は /に なり QUERY関数で演算子が計算できるようになりました。

aをbにcをdに・・・といった SUBSTITUTEの置換が何パターンもある場合は、 REDUCE関数を使って記述することが出来るんですが、2回程度ならそのまま記述した方が早いです。

解説2-3 QUERY関数内で LETを使って簡略化

とりあえずは出来たけど、select句で

SUBSTITUTE(SUBSTITUTE(ASC(B2),"×","*"),"÷","/")

この式を使うことで発生する見出し行を QUERY内で削除する為には、label句で もう一度この式を記述する必要があるんですよね。。

だから、どうしてもこのようなダルい式になってしまいます。

もちろんQUERY関数内では処理せず、外側でIDEXで2行目部分だけ取得する

でもいいんですが、今だったらのQUERY関数の中でLET関数を使って

こう記述することもできます。

これは何をやっているか？

QUERY関数の第2引数は、最終的にクエリ文として動く（文法の正しい）文字列を生成すればいいだけなので、過程はどんな方法でもいいわけです。

そこでLET関数を使って select句とlabel句で2回登場する

SUBSTITUTE(SUBSTITUTE(ASC(B2),"×","*"),"÷","/")

この部分を calc と置いて calcをクエリ文に&で入れて

LET(calc,SUBSTITUTE(SUBSTITUTE(ASC(B2),"×","*"),"÷","/"),"select "&calc&" label "&calc&"''" )

このようにクエリ文を生成しています。

さらに、わかりやすくするなら 関数のネストではなく

=QUERY(,LET(
  x,SUBSTITUTE(ASC(B2),"×","*"),
  y,SUBSTITUTE(x,"÷","/"),
  SUBSTITUTE("select _ label _ ''","_",y)
))

QUERY関数内でLETを使ってSUBSTITUTEを3回繰り返す、こんな記述でもいいかもしれません。

1段階目の SUBSTITUTEで ×を * に置き換え、その結果を xと置き、
2段階目の SUBSTITUTEで ÷ を / に置き換え、その結果を yと置き、
3段階目の SUBSTITUTEで "select _ label _ ''" という文字列の _ を y に置換する（&で連結させる代わり）

とりあえず、小学校で使えそうな セルの計算を実行させる QUERY関数を使ったeval式が出来ました。

QUERY関数でevalしよう！応用編2（スピル対応）

続いてQUERY関数のeval式を 1つのセルの計算に使うのではなく、複数セルを一括計算する スピル対応式のお題に挑戦してみましょう。

Q3. QUERY関数をB2に入れて A2:A7セルの文字列の数式を計算させたい

それではこのお題にチャレンジしてみましょう。

Q2のお題の全角対応や×、÷の対応は無視してQ1の式をベースとしてOKです。A2:A7に入った

5 + 9
15 - 28
8 * 11
15 / 3
8 * ( 5 + 2 )
8 * 5 + 2

B2セルに一つだけ式を入れて、これらを計算さたいというお題です。(範囲は縦1列で、途中に空白セルを含まないものとします）

チャレンジしてみましょう！（難しい場合は一番外側がQUERYじゃなくてもOKです）

↓↓
回答はここから。
↓↓

A3. QUERY関数をB2に入れて A2:A7セルの文字列の数式を計算させる

回答です

なんだよQUERYを2回使うのかよ！ってなっちゃいましたかね。。これはさすがに1番外側をQUERYにするには、どうしても2回必要なんです。

一応、2回目のQUERY関数も意味（役割）があります。

ちなみに一番外側をQUERY関数としなくてよければ MAPを使うのが簡単ですね。

こちらの式の方が簡単ですかね。

INDEX + QUERY のeval式を MAPの中で1つずつ処理しているだけです。

というわけで最初の回答の式を解説していきます。

解説3-1 複数セルを中身をカンマ区切りの文字列にする

この式のポイントは

JOIN(",",A2:A7)))

この部分です。

TEXTJOINを使ってもいいんですが、空白を気にする必要なく一次元配列が対象なので、より短い JOIN関数を使用しています。

これでまず、A2:A7セルの数式を カンマ区切りで連結した1つの文字列とします。

実はこれだけで、

あとはQUERY関数で select句に入れれば 計算処理は完成します。

ここまでくれば 見出しを削除して縦横変換で完成なんですが、ここは あえてlabel句を使っての見出し削除はしないで

TRANSPOSE関数で縦横変換してから、欲しい結果である2列目だけを

INDEXやCHOOSECOLSではなく QUERY祭ってことで、もう1回 QUERY関数の select句で 選択する

このような記述としました。

種を明かしてしまえば簡単ですね。。

QUERY関数でevalしよう！応用編3（スピル対応＋チェックした行だけ計算）

QUERY関数でeval式の最後のお題です。

これは実は上のケースQ3にも関係してきます。

Q3で作成した式、実は対象範囲内に1つでも空白のセルがあった場合はエラーになります。

これは select句では 空白（または空文字）を指定できない為です。

最後のお題をクリアするカギが、このselect句での空白の生成です。

Q4. QUERY関数をC2に入れて A2:A7セルの文字列の数式をB列でチェックを入れた行だけ計算させたい

このようなお題です。もちろんQUERY式でまとめるのが難しければ、他の関数でも構いません。

A列のデータはQ3と同じモノでOKです。チャレンジしてみましょう！

↓↓
回答はここから。
↓↓

A4a. QUERY関数をC2に入れて A2:A7セルの文字列の数式をB列でチェックを入れた行だけ計算させる（簡単な式）

回答です。まずは簡単な方

A3の簡単な方の回答と同じLAMBDAヘルパー関数のMAPを使う方法です。

MAPは同じサイズであれば複数の範囲を指定できるので、

• A2:A7を1つ目の範囲として 1つずつ_v1として取り出し

• B2:B7を2つ目の範囲として 1つずつ_v2として取り出し

　　▼
IF(_v2,INDEX(QUERY(,"select "&_v1),2),)

_v2（チェックボックス）がTRUEなら QUERYで計算してINDEXで2行目だけを返す、FALSEなら空白を返す

という処理をしています。

これは今までの流れでQUERY関数の使い方と、MAPを理解していれば割と簡単ですね。

とりあえず実用的なのはコッチですｗ

A4b. QUERY関数をC2に入れて A2:A7セルの文字列の数式をB列でチェックを入れた行だけ計算させる（簡単な式）

もう1つの回答。LAMBDA登場前でも使えた式です。

普段 mirはARRAYFORMULAは一番外側に付ける派なんですが、今回は合えてわかりやすいように（そしてQUERYが大外に来るように） JOIN関数の中で

ARRAYFORMULA(A2:A7&IF(B2:B7,,"/0"))

このようにしています。

逆にこれ以外の JOINしてTRANSPOSEしてQUERYでselect Col2 とする部分はQ3の回答と一緒ですね。

では

ARRAYFORMULA(A2:A7&IF(B2:B7,,"/0"))

この部分は何をしているか？

これは B2:B7（チェックボックス）にチェックが入っていたら（TRUEだったら） 空白、チェック無し（FALSE）だったら、"/0"という文字列を生成して、A2:A7の後ろに文字列として結合。

こんな配列処理をしています。

解説4-1 QUERY関数の select句で 空白列を生成する裏技

ちなみに

15 - 28/0

これを計算したらどうなるか？

これは 0で割ることは出来ないという数学ルールで エラー（解なし）になります。

シート関数なら #DIV/0! エラーを返すところですよね。

しかし、QUERY関数だと 構文上はこれは間違ってないので 全体エラーにはなりません。そして 結果が「解なし」ということで 空白になります。

つまり QUERY関数において 空白（空白列）を生成したい場合は 1/0とか2/0 を select句で指定すれば良いわけです。

今回は チェックなしの時に select 内の 該当箇所で

数値/0

でエラーを発生させることで空白を返すという技を使っているわけです。

それが

この式の ARRAYFORMULA(A2:A7&IF(B2:B7,,"/0"))) この部分の意味合いです。

もしこれを簡略化して

ARRAYFORMULA(A2:A7/B2:B7)

としてしまうと

クエリ文に入れる前に 計算されてしまいエラーとなるのでダメです。

あくまでも生成すべきは QUERY関数で計算処理された時に エラーとなる式の「文字列」なのです。

わかりましたでしょうか？

解説4-2 select句では同じ値（同じ列、同じ式）が2度使えない

じゃあ、わざわざ元の式の A2:A7 に"/0"を連結させなくても

TRUEの時は A2:A7、FALSEの時は "1/0" という文字列を返すって分岐でいいんじゃないの？

と考えるかもしれません。

しかし、これもうまくいきません。

全部チェックが入ってる時と 1つだけチェック外した時は問題ありませんが、チェックが2つ外れたとき（空白を返す箇所が2つ以上の時）は全体がエラーとなります。

これはエラーメッセージに

このようにある通り、同じ列、値、数式は select句の中で1回しか指定できない というルールがあるからです。

つまり、👇これは出来るけど

👇これは出来ないってことです

だから、

実はこのQUERYスピル式には

対象範囲にまったく同じ数式の文字列があった場合、全体エラーになるという弱点があります。

解説4-3 途中に空白がある式の場合

というわけで、今だったら

コッチ使った方が簡単だし数式の重複も気にする必要ないってことですね。

この式であれば Q4の計算式の対象範囲に 途中に空白セルがあったとしても

IFERRORを入れておくことで空白を返すことが出来ます。

これも MAPを使わない旧方式だと

A列をIFで空白判定して、空白の時はROW(A2:A7)&"/0"  で "行番号/0" という文字列を生成して重複を回避しつつ 空白を生成という感じになります。

昔このQUERY関数の空白生成方法を発見した時は感動したんですけど、今だとそこまでありがたみがないというｗ

QUERY関数でeval出来たかな！？

今回は GoogleスプレッドシートのQUERY関数を使って eval(セルに入力した数式を別のセルで計算する）を実現する方法を お題形式で学びました。

人によってはまったく利用する機会ないかもしれませんし、QUERY関数の使い方としては完全に邪道というか曲芸だとは思いますが、学校の先生とかなら使い道があったりするんじゃないでしょうか？

あと、お題の中で select句やlabel句の特性、空白列の入れ方など 少しディープな学びもあったんじゃないかと思います。

なんにしてもQUERY関数は特殊で奥が深いな～と思いますね。

QUERY関数ネタが4連続で続きましたし、まだまだこれから書くことも多いんで、一旦少し休憩ってことでQUERY関数ネタはお休みします。

次週から違うネタを2，3回はさんで、またQUERY関数シリーズといった感じで書いていきたいと思います。