【QuickTips 10】QuizKnockのスプレッドシート クイズをQuery関数で突破する

シンプルでちょっと便利な Quick Tips。マガジンにまとめていきます。

今回も約3000文字の長めバージョンです。

QuizKnockがGoogleスプレッドシート（スプシ）！？

X経由で知ったのですが、 QuizKnock（クイズノック）のブログで 東言さんが 「Googleスプレッドシート論」を語っていました。

https://quizknock-schole.com/articles/blog/arcEXrZfTmBoJ4xZrzecAqYd

通常は会員限定のようですが、こちらは無料で読めます。

最後にスプシクイズ があって、

こんなお題になっています。

一般的なクイズとしてはマニアックですが「挑戦者求む」とあったのと、Googleスプレッドシート職人としては、これはアレを使いたくなる内容だなってことで、回答・解説をnoteにまとめてみました。

通常思いつく式 SPLIT関数で配列化+REDUCE関数でループ + 分岐

この問題ネックとなるのが

1. 文字列の四則演算をどう実行するか？

2. 四則演算のルール（+や- より *や/を先に実行）を無視して左から順に計算するにはどうするか？

この2点。

実際ブログでも

と書かれています。

そうすると、一つの解決方法としてExcelでも使える REDUCE関数を使う方法が思いつきます。

=LET(
  a,A1,
  x,SPLIT(REGEXREPLACE(a,"([\+\-\*\/])","_$1"),"_"),
  REDUCE(,x,LAMBDA(acc,cv,
    LET(
      op,LEFT(cv,1),num,SPLIT(cv,"+-*/"),
      SWITCH(op,"+",acc+num,"-",acc-num,"*",acc*num,"/",acc/num,acc+num)
    )
  ))
)

四則演算の演算子である +-*/ の手前に REGEXREPLACE関数で 区切り文字とする _（アンダースコア）を挟み、SPLIT関数で分割して配列化、

これをREDUCE関数で初期値 空白（0でも可）として、配列の要素の先頭についている演算子、そして数字を取り出し、SWITCH関数で演算子ごとに分岐、計算処理を一つ前の処理の結果に対して繰り返すという式です。

実際、ブログのコメント欄にはこの手順の式が読者から投稿されていました。

文字列の四則演算を動かす eval的使い方ができる QUERY関数

しかし QUERY関数の超応用テクニックを知っている人なら、

「QUERY関数のselect句に 四則演算の数式の文字列を放り込むと、evalのように計算が実行される」

これが使えるのでは！？って思いますよね。

見出しが生成されてしまいますが、これは後でINDEX関数で2行目だけを取り出せばOK。

しかし問題があって、通常の四則演算として計算されるので、

+-より先に 2*3 や 4/5 が計算されてしまい、計算結果が 6.2 と 左から順に計算した場合と違ってしまう点です。

REGEXREPLACEでひたすらカッコをつけて突破する

これを突破する為に計算の優先度を制御できる () で式を括ることで、左から順に計算させるように 元の数式の文字列を改変します。

まず 正規表現が使える REGEXREPLACE関数を使って、数値の塊の後ろに ) を付けましょう。

この先少し式が長くなるので LET関数で 対象のセルA1を aと置いて式を作りましょう。

数値の塊 \d+ をカッコで括って (\d+) とキャプチャグループ にします。

キャプチャグループは 置換後に $1として呼び出せるので、 $1) とすることで、全ての数値の塊の後ろに ) を 付けます。

GoogleスプレッドシートのREGEX系関数はExcelと違ってセルが数値の時はエラーとなるんですが、

お題の条件が「文字列を参照し」なので、A1が数値のケースは無視してよいでしょう。

遂になる "(" を REPT関数で生成する

閉じカッコは用意できましたが、対となる開始のカッコを用意する必要があります。

ちなみに開始のカッコは

全て先頭に付ければ問題ないので、) と同じ数 ( を繰り返したテキストを生成すればOK。ってことは、REPT関数の出番ですね。

) の数は、もともとの A1のテキストの文字数 LEN(a) を

数式で数値の塊の後ろに ) を付けた結果のテキスト（bと置く）の文字数 LEN(b) から引いた数なので

こんな式で生成できます。これを & でbと連結すれば

=LET(a,A1,b,REGEXREPLACE(a,"(\d+)","$1)"),
REPT("(",LEN(b)-LEN(a))&b)

左から順に計算されるカッコがついた 数式のテキストに変換できました。

QUERY関数で突破する回答

ここまでくればゴールが見えますね。回答です。

=LET(
  a,A1,
  b,REGEXREPLACE(a,"(\d+)","$1)"),
  INDEX(QUERY(,"select "&REPT("(",LEN(b)-LEN(a))&b),2)
)

インデント無しで 95文字の式です。かなり簡潔ですね。

下にフィルコピーすれば、A2、A3 セルに対しても同様に計算した結果が出力されます。

【オマケ】複数セルを一気に処理するスピル式

お題はA1セル1つを対象とした式なんですが、A1:A3のセル範囲に同じような数式のテキストがあった場合、これらを一つの式で処理するスピル式もQUERY関数を少しアレンジして作ることが出来ます。

=MAP(A1:A3,LAMBDA(a,
  LET(
    b,REGEXREPLACE(a,"(\d+)","$1)"),
    INDEX(QUERY(,"select "&REPT("(",LEN(b)-LEN(a))&b),2)
  )
))

MAP関数を使うのが一般的ですが、これだと芸がないので、

=ARRAYFORMULA(LET(
  a,A1:A3,
  b,REGEXREPLACE(a,"(\d+)","$1)"),
  c,QUERY(,"select "&JOIN(",",REPT("(",LEN(b)-LEN(a))&b)),
  QUERY(TRANSPOSE(c),"select Col2")
))

こんな式でも出来るって回答も入れておきましょう。

上の式はLETを省略して1行で書くこともできるので

=INDEX(TRANSPOSE(QUERY(,"select "&JOIN(",",REPT("(",LEN(REGEXREPLACE(A1:A3,"(\d+)","$1)"))-LEN(A1:A3))&REGEXREPLACE(A1:A3,"(\d+)","$1)")))),,2)

このお題をさらに発展させた複数セルをまとめて処理するスピル式にした場合でも、LETやLAMBDA関数が登場する前のシート関数だけを使って解けるってことです。

以上、mirのQuizKnock Googleスプレッドシートクイズチャレンジでした。

今回のQucik Tipsの関連 note

メインで使用した QUERY関数をevalのように使う超応用テク

QUERY関数の全てはマガジンにまとめています。

REDUCE関数について書いたnote

SPLIT関数について書いたnote

LET関数について

MAP関数について

ARRAYFORMULA関数について

REGEXREPLACE関数や正規表現については、まだ詳しくまとめていません。そのうち書きたいと思います。