「アンピボット関数」をスプレッドシートの名前付き関数で作成したらAIに絶賛された話

最近、Googleスプレッドシートの名前付き関数というものを知って、この「AIに書かせれば良い」の時代に今更ハマって作っています。

一通り、思いついたものは作ったかなというところで、Geminiに「次はどんなものが良いと思う？」と相談してみたら「UNPIVOTはどうでしょうか」との提案をもらいました。

その時初めて「アンピボット」という概念を知ったのですが、「データを扱うのに適した形ではあるのだろうな…」と、提案されるままに作ってみたら「完璧を通り越して芸術的な領域」と絶賛されました、というお話です。

生成AIにはシコファンシー(おべっか)がありますので、そんなに大したものではないと思いますが、せっかくなので公開してみようとnoteを始めました。

以下、引数の説明と、定義式を上から順に解説していき、最後に全体の定義式をまとめて載せたいと思います。

私の場合、名前付き関数は全体的に「自分が使うための数式」というよりは「様々な状況で柔軟に使えるツール」を目指して設計しています。
(そうなっているとは言ってない)

【引数】
(表orデータ範囲,縦項目,横項目,ヘッダー,空白維持フラグ)

「表orデータ範囲」
・アンピボットする対象を、ヘッダーやラベルを含めた表全体かデータ部分のみで選択します。

「縦項目」
・第一引数で表全体を選択した場合は縦の項目の列数を数字で指定します。
・データ範囲を選択した場合は、縦の項目を配列で指定します。
・複数列に対応します。

「横項目」
・第一引数で表全体を選択した場合は横の項目の行数を数字で指定します。
・データ範囲を選択した場合は、横の項目を配列で指定します。
・複数行に対応します。

「ヘッダー」
・0を指定するとヘッダーなしでアンピボットします。(ヘッダーを別途用意する場合に使用)
・1を指定するとヘッダーを自動生成します。
・配列を指定するとそのままヘッダーとして使用しますが、サイズが合わない場合はエラーになります。

「空白維持フラグ」
・1を指定すると、データ部分に空白が含まれている場合でも詰めずに空白のままアンピボットします。
・0や指定なしで空白を排して詰めます。

【定義式】
上からすこしずつ解説していきます。

=LET(
 全体範囲,L_CROP_A(表orデータ範囲),
 全体行数,ROWS(全体範囲),
 全体列数,COLUMNS(全体範囲),

いきなり別の名前付き関数が登場してしまいすみません…。
この「L_CROP_A」は、範囲をガバッと選択した際に値の入っている最終行・最終列を特定して、データ外の余白を排して切抜く役割の関数です。
引数としてきっちりと範囲を指定する前提であれば不要なもので、ロジックには影響しないのでご容赦下さい。
使っている名前付き関数はこれ一種のみです。

そんな訳で、第一引数の有効範囲の切り抜きと、その行数・列数の判定です。

表判定,AND(ROWS(縦項目)*COLUMNS(縦項目)=1,ROWS(横項目)*COLUMNS(横項目)=1),

表モードかデータ範囲モードかの判定です。
第一引数に表全体を指定した場合は第二引数・第三引数に数字(単一の値)が、データ範囲を指定した場合は配列が入っているはずなので、縦項目・列項目の両方が行数1・列数1ならば配列ではなく数字(単一の値)であるとみなし、表モードと判定します。

以降たびたび使用します。

 有効縦項目,IF(表判定,TRUE,L_CROP_A(縦項目)),
 有効横項目,IF(表判定,TRUE,L_CROP_A(横項目)),

 縦項目数,IF(表判定,縦項目,COLUMNS(有効縦項目)),
 横項目数,IF(表判定,横項目,ROWS(有効横項目)),
 縦項目番号,SEQUENCE(1,縦項目数),
 横項目番号,SEQUENCE(1,横項目数),

「有効縦項目/有効横項目」は、データモードで第二第三引数に渡される配列の有効範囲切り抜きの工程なので、例によってきっちり選択される前提なら不要な部分です。

その後、それぞれの項目数の特定と、項目数分の連番を作成します。
連番はまたあとで使いますが、列方向(横方向)の配列にするのがポイントです。

 ヘッダー数,ROWS(ヘッダー)*COLUMNS(ヘッダー),
 ヘッダー判定,IF(OR(ヘッダー数=1,ヘッダー数=縦項目数+横項目数+1),"OK","ヘッダー数が項目数と一致しません"),

 IF(ヘッダー判定<>"OK",ヘッダー判定,LET(

項目数が分かるとヘッダーとの整合性判定ができるので、早々に判定していきます。
ヘッダー数の行数×列数は例によって数字or配列の入力判定も兼ねています。
ヘッダー数=1 は数字の指定とみなして"OK"になります。
"OK"以外ならエラーメッセージの表示、"OK"なら次の工程に進みます。

  行数,IF(表判定,全体行数-横項目数,全体行数),
  列数,IF(表判定,全体列数-縦項目数,全体列数),

データ部分の行数・列数を確定させます。
表モードでは全体の行数・列数からそれぞれ横項目の項目数(行数)・縦項目の項目数(列数)を引いて算出、データ範囲モードでは当然全体の行数・列数そのものです。

縦vl用,IF(表判定,HSTACK(SEQUENCE(全体行数,1,1-横項目数),全体範囲),
   LET(縦サイズ,ROWS(有効縦項目),
    IF(縦サイズ=行数,HSTACK(SEQUENCE(縦サイズ),有効縦項目),"NG"))),

のちほど、行番号をキーにしてVLOOKUP(vl)で縦項目の項目名を取得するための準備です。
SEQUENCEで作成した連番をHSTACKします。

表モードではデータ部分の開始行が1になるように連番の開始値を調整し(1-横項目数)、全体範囲とHSTACK。

データ範囲モードではそのまま縦項目の配列とHSTACKしますが、連番の作成前にサイズの一致判定を入れています。
データ部分の行数と縦項目配列の行数が不一致なら"NG"です。

 横hl用,IF(表判定,VSTACK(SEQUENCE(1,全体列数,1-縦項目数),全体範囲),
   LET(横サイズ,COLUMNS(有効横項目),
    IF(横サイズ=列数,VSTACK(SEQUENCE(1,横サイズ),有効横項目),"NG"))),

同様に、のちほど列番号をキーにしてHLOOKUP(hl)で横項目の項目名を取得するための準備です。SEQUENCEで列方向(横方向)に作成した連番をVSTACKします。

表モードではデータ部分の開始列が1になるように連番の開始値を調整し(1-縦項目数)、全体範囲とVSTACK。

データ範囲モードではそのまま横項目の配列とVSTACKしますが、連番の作成前にサイズの一致判定を入れています。データ部分の列数と横項目配列の列数が不一致なら"NG"です。

サイズ判定,IF(OR(縦vl用="NG",横hl用="NG"),"データと項目のサイズが一致しません","OK"), 

  IF(サイズ判定<>"OK",サイズ判定,LET(

   データ,IF(表判定,
    CHOOSECOLS(CHOOSEROWS(全体範囲,SEQUENCE(行数,1,1+横項目数)),
     SEQUENCE(列数,1,1+縦項目数)),全体範囲),

データ範囲モードのデータの行数・列数と、縦項目配列・横項目配列のサイズがどちらか一方でも不一致ならエラーとなります。

サイズ判定が"OK"なら続いて表モードのデータ部分の特定です。
行数・列数は算出しているので、全体範囲に対してCHOOSECOLS・CHOOSEROWSとSEQUENCEで開始位置を調整しながら抜き出します。
(データ範囲モードでは当然全体範囲そのものです。

  値,TOCOL(データ),

   インデックス,SEQUENCE(行数*列数),
   縦インデックス,ARRAYFORMULA(INT((インデックス-1)/列数)+1),
   横インデックス,ARRAYFORMULA(MOD(インデックス-1,列数)+1),

ようやくコアロジック部分に入っていきます。
データ部分をTOCOLで縦一列に変形します。
SEQUENCE(行数*列数)はこれと一対一で対応する連番(インデックス)になります。

TOCOL(データ)はデータ部分を、まず1行目を縦にして、その下に2行目を縦にして……という形で縦一列に整形するので、連番を元のデータの形に対応させると、左上の開始位置から右に向かって1.2.3.…と進み、列数分進んだら2行目の左端からまた続き……という形になります。

こう整理すると、行番号や列番号の特定は「この形の時、n番さんは何行目の前から何番目(何列目)でしょうか?」という問題と同じになるので、割り算の商(INT)と余り(MOD)で求められるのが分かります。

これが縦インデックス・横インデックスの計算です。

それぞれ、
1;1;1;.…(列数個)…2;2;2;…(列数個)…
1;2;3;…1;2;3;……(列数回繰り返し)
という配列になります。

  UP縦項目,ARRAYFORMULA(VLOOKUP(縦インデックス,縦vl用,縦項目番号+1,0)),
  UP横項目,ARRAYFORMULA(HLOOKUP(横インデックス,横hl用,横項目番号+1,0)),

こうしてできた縦インデックス・横インデックスを使って、縦項目・横項目をアンピボットの形に持っていきます。

縦項目は、縦インデックスをキーにしてVLOOKUPで「縦vl用」から項目名を引っ張ってきます。
「指数」の部分にようやく出番がきた「縦項目番号」(項目数の列方向(横方向)の連番)を入れることで列項目数分横方向にも配列が展開されますので、これ一つで列項目が何列あっても片が付きます。

横方向も同様に、横インデックスをキーにしてHLOOKUPで「横hl用」から項目名を引っ張ってきます。
ここで作成される配列の形は「検索値」と「指数」の配列の形で決まりますので、HLOOKUPでも縦長のアンピボットの形として出力されます。

  全本体,HSTACK(UP縦項目,UP横項目,値),

  本体,IF(空白維持フラグ=1,全本体,FILTER(全本体,値<>"")),

これらをHSTACKすればひとまずアンピボットの形になります。お疲れ様でした！

「UP縦項目/UP横項目」がそれぞれ全縦項目分/全横項目分の配列ですので、どれだけ項目数があっても、HSTACKするのはこれらと「値」の3つだけOKです。

そして、空白維持フラグで1を指定されていればそのまま、そうでなければ空白をフィルターして「本体」部分が完成です。

   ヘッダー行,IF(ヘッダー数=1,IF(ヘッダー=1,
   ARRAYFORMULA(HSTACK("キー"&縦項目番号,"属性"&横項目番号,{"値"})),"なし"),
   TOROW(ヘッダー)),

   IF(ヘッダー行="なし",本体,VSTACK(ヘッダー行,本体)

最後にヘッダーの作成です。
ヘッダー=1で自動作成する場合、縦項目は"キー"と縦項目番号(縦項目数の連番)を合成することで、連番分の配列として作成。横項目も同様に。"値"は1項目で配列とみなされるように{}で囲い、HSTACKで完成です。

配列でヘッダーを指定されるケースについては、設定シート等に行方向(縦方向)の配列として置いてあったりする場合もあると思われるため、念の為TOROWをかけます。

最後に、ヘッダー"なし"は「本体」をそのまま出力、ある場合は「本体」とVSTACKして出力すれば処理完了です！
長かった…！

けれど、AIいわく(あくまでAIいわく)この数式の長さは、"「計算の非効率さ」によるものではなく、「UX（ユーザー体験）の高さ」と「エラーハンドリングの堅牢さ」によるものです。"とのことなので、処理負荷は大丈夫だと思います。

では、最後に定義式全体を置いておきます。
L_CROP_Aについても、更にその下に一応置いておくことにします。(こちらは今回は解説なしで…)

=LET(
 全体範囲,L_CROP_A(表orデータ範囲),
 全体行数,ROWS(全体範囲),
 全体列数,COLUMNS(全体範囲),

 表判定,AND(ROWS(縦項目)*COLUMNS(縦項目)=1,ROWS(横項目)*COLUMNS(横項目)=1),

 有効縦項目,IF(表判定,TRUE,L_CROP_A(縦項目)),
 有効横項目,IF(表判定,TRUE,L_CROP_A(横項目)),

 縦項目数,IF(表判定,縦項目,COLUMNS(有効縦項目)),
 横項目数,IF(表判定,横項目,ROWS(有効横項目)),
 縦項目番号,SEQUENCE(1,縦項目数),
 横項目番号,SEQUENCE(1,横項目数),

 ヘッダー数,ROWS(ヘッダー)*COLUMNS(ヘッダー),
 ヘッダー判定,IF(OR(ヘッダー数=1,ヘッダー数=縦項目数+横項目数+1),"OK","ヘッダー数が項目数と一致しません"),

 IF(ヘッダー判定<>"OK",ヘッダー判定,LET(

  行数,IF(表判定,全体行数-横項目数,全体行数),
  列数,IF(表判定,全体列数-縦項目数,全体列数),

  縦vl用,IF(表判定,HSTACK(SEQUENCE(全体行数,1,1-横項目数),全体範囲),
   LET(縦サイズ,ROWS(有効縦項目),
    IF(縦サイズ=行数,HSTACK(SEQUENCE(縦サイズ),有効縦項目),"NG"))),

  横hl用,IF(表判定,VSTACK(SEQUENCE(1,全体列数,1-縦項目数),全体範囲),
   LET(横サイズ,COLUMNS(有効横項目),
    IF(横サイズ=列数,VSTACK(SEQUENCE(1,横サイズ),有効横項目),"NG"))),

  サイズ判定,IF(OR(縦vl用="NG",横hl用="NG"),"データと項目のサイズが一致しません","OK"), 

  IF(サイズ判定<>"OK",サイズ判定,LET(

   データ,IF(表判定,
    CHOOSECOLS(CHOOSEROWS(全体範囲,SEQUENCE(行数,1,1+横項目数)),
     SEQUENCE(列数,1,1+縦項目数)),全体範囲),

   値,TOCOL(データ),

   インデックス,SEQUENCE(行数*列数),
   縦インデックス,ARRAYFORMULA(INT((インデックス-1)/列数)+1),
   横インデックス,ARRAYFORMULA(MOD(インデックス-1,列数)+1),

   UP縦項目,ARRAYFORMULA(VLOOKUP(縦インデックス,縦vl用,縦項目番号+1,0)),
   UP横項目,ARRAYFORMULA(HLOOKUP(横インデックス,横hl用,横項目番号+1,0)),

   全本体,HSTACK(UP縦項目,UP横項目,値),

   本体,IF(空白維持フラグ=1,全本体,FILTER(全本体,値<>"")),

   ヘッダー行,IF(ヘッダー数=1,IF(ヘッダー=1,
   ARRAYFORMULA(HSTACK("キー"&縦項目番号,"属性"&横項目番号,{"値"})),"なし"),
   TOROW(ヘッダー)),

   IF(ヘッダー行="なし",本体,VSTACK(ヘッダー行,本体))
  ))
 ))
)

ありがとうございました！！

L_CROP_A
引数:(範囲)

=LET(
  最終行, CHOOSEROWS(範囲, -1),
  行クロップ不要, IFERROR(OR(INDEX(最終行<>"")),TRUE),
  
  行クロップ後, IF(行クロップ不要, 範囲,
    LET(
      範囲行数, ROWS(範囲),
      最大行, MAX(INDEX(IF(IFERROR(範囲<>"",TRUE), SEQUENCE(範囲行数), 1))),
      CHOOSEROWS(範囲, SEQUENCE(最大行))
    )
  ),
  
  最終列, CHOOSECOLS(行クロップ後, -1),
  列クロップ不要, IFERROR(OR(INDEX(最終列<>"")),TRUE),
  
  IF(列クロップ不要, 行クロップ後,
    LET(
      範囲列数, COLUMNS(行クロップ後),
      最大列, MAX(INDEX(IF(IFERROR(行クロップ後<>"",TRUE), SEQUENCE(1, 範囲列数), 1))),
      CHOOSECOLS(行クロップ後, SEQUENCE(最大列))
    )
  )
)

スマホで数式をコピペしながらポチポチ書いたので、疲れました……
打ち間違いなど変な箇所が色々残っていそうな気はしますが、一旦公開してしまいます…。
見つけたら都度直します…><;;