2024年6月29日

項目名が階層構造のネ申Excel表

Excel にはデータの抽出を支援するいくつかのFILTER・MATCH系・LOOKUP系の関数があったり、「テーブル」機能があるものの、現場で実際に作られる表には項目名が階層構造（入れ子）になっているこんなのは余りにも日常的にある。

この表の項目名は階層構造になっている。でもこれでは、たとえば「TS2」の「Q符号」の「区分」の列を（行番号でない方法で）一意に指定することはできない。

行番号でない方法で列を一意に指定できるようにするためには、この表の3階層ある項目名を何かの区切り文字で連結してファイルシステムのディレクトリーパスのようにすればよい。

階層構造の列項目名を平坦にするワークシート関数: `_toColKeys`

元の項目名の行は「セルの結合」をしているものの、セル単位でみると、実際にはこんな構造になっている。この配列を _headerArray とする。

この図の下段の縦の方向の各セルがディレクトリー・パスの各階層に対応している。

前処理（正規化）

問題なのは上位階層が空になっている空セルがあることだ。空セルといっても実際にはスペース文字が入っていたり、エラー値が入っている場合などの「揺れ」あり得るので、その揺れを取り除いて正規化しておいて、特別な値 Null を入れておくことにする（今回は Null としては実際には FALSE を用いた）。そして有効な項目名が入っている箇所でも、改行文字やスペース文字など、データベースの「キー」的にみると好ましくない文字があるので、それらを別の文字（_Char_alt）で置換して正規化しておく。（この処理には SUBSTITUTE・TEXTSPLIT・TEXTJOIN を使ったが、近い将来公開される予定の REGEXREPLACE 関数を用いるのが効果的だろう。）

      LET(
        _Null, FALSE,
        _Char_HT, CHAR(9),
        _Char_LF, CHAR(10),
        _Char_CR, CHAR(13),
        _Char_SP, " ",
        _Char_alt, "_",

        _headerArray_act,
          MAP(_headerArray,
            LAMBDA(_v,
              IFS(
                ISNUMBER(_v), _v,
                ISTEXT(_v),
                  LET(
                    _v_trim,
                      TEXTJOIN(_Char_alt, TRUE,
                        TEXTSPLIT(
                          SUBSTITUTE(
                          SUBSTITUTE(
                          SUBSTITUTE(
                          SUBSTITUTE(
                            _v,
                            _keySeparator_act, _Char_SP),
                            _Char_HT, _Char_SP),
                            _Char_LF, _Char_SP),
                            _Char_CR, _Char_SP),
                          _Char_SP)),
                    IF(EXACT("", _v_trim), _Null, _v_trim)),
                TRUE, _Null))),

空セルの補完

正規化処理の後の配列 _headerArray_act はこんな感じだ。

例えば3列目では第1階層が空白になっている。これを補完しなければならない。上位階層のセルが空白の場合は、一つ左の列の同じ階層から項目名を継承すればよい。いわばこれは、一つ左の列と現在処理中の列を併合する処理と考えることができる。

この処理をするのが _Path_mergePath という関数だ。第1引数 this に、一つ左の列（パス）を、第2引数 another い現在処理中の列（パス）を与えると、この関数は空セルを補完された列（パス）を返す。

        _Path_mergePath, LAMBDA(_this,_another,
          LET(_another_trim, _Path_trim(_another),
            REDUCE(_this, SEQUENCE(ROWS(_another_trim)),
              LAMBDA(_path,_rowIndex,
                LET(_a, _Path_getValueAt(_another_trim, _rowIndex),
                  IF(EXACT(_a,_Null), _path,
                    _Path_setValueAt(_path, _rowIndex, _a))))))),

畳み込み

関数 _Path_mergePath を初期値（0列目）として Null を与えて、_headerArray_act の各列（パス）を左から順に畳み込む。

    _pathArray,
      DROP(
        REDUCE(VSTACK(_Null), SEQUENCE(COLUMNS(_headerArray_act)),
          LAMBDA(_pathArray,_colIndex,
            HSTACK(_pathArray,
              _Path_mergePath(
                CHOOSECOLS(_pathArray, -1),
                CHOOSECOLS(_headerArray_act, _colIndex))))),
        ,
        1),

$\vdots$

そしてこの畳み込みで出来た配列の最初の列（初期値）をDROP 関数で取り除いたものが _pathArray がである。

文字列化

_pathArray の各列の有効値だけを区切り文字 _keySeparator_act で連結したものが、最終的に求めるもの（項目名がディレクトリー・パス文字列のようになった1行だけからなる配列）になる。

        BYCOL(_pathArray,
          LAMBDA(_path,
            TEXTJOIN(_keySeparator_act, TRUE,
              FILTER(_path, NOT(ISERROR(_path)))))))),

`_toColKeys` の全コード

上述の処理をする関数 _toColKeys の定義が下のコードである。上では書いてない補助的な関数（_Path_trim など）の定義もある。階層構造の項目名の配列を第1引数 _headerArray に与えて、パスの区切り文字列を第2引数 _keySeparator に指定して使う。_keySeparator は省略してもよく、省略した場合はタブ文字になる。

=LET(
  _toColKeys,
    LAMBDA(_headerArray,[_keySeparator],
      LET(
        _Null, FALSE,
        _Char_HT, CHAR(9),
        _Char_LF, CHAR(10),
        _Char_CR, CHAR(13),
        _Char_SP, " ",
        _Char_alt, "_",

        _keySeparator_act,
          IF(ISOMITTED(_keySeparator), _Char_HT, _keySeparator),

        _headerArray_act,
          MAP(_headerArray,
            LAMBDA(_v,
              IFS(
                ISNUMBER(_v), _v,
                ISTEXT(_v),
                  LET(
                    _v_trim,
                      TEXTJOIN(_Char_alt, TRUE,
                        TEXTSPLIT(
                          SUBSTITUTE(
                          SUBSTITUTE(
                          SUBSTITUTE(
                          SUBSTITUTE(
                            _v,
                            _keySeparator_act, _Char_SP),
                            _Char_HT, _Char_SP),
                            _Char_LF, _Char_SP),
                            _Char_CR, _Char_SP),
                          _Char_SP)),
                    IF(EXACT("", _v_trim), _Null, _v_trim)),
                TRUE, _Null))),

        _Path_trim, LAMBDA(_this,
          LET(_length, XMATCH(TRUE, NOT(EXACT(IFERROR(_this,_Null),_Null)), ,-1),
            IF(ISERROR(_length), _Null, TAKE(_this, _length)))),

        _Path_setValueAt, LAMBDA(_this,_index,_value,
          LET(_this_rows, ROWS(_this),
            IFS(
              _index=1, _value,
              _index<=_this_rows, VSTACK(TAKE(_this, _index-1), _value),
              _this_rows+1=_index, VSTACK(_this, _value),
              _this_rows+1<_index, VSTACK(_this, MAP(SEQUENCE(_index-_this_rows-1), LAMBDA(_, _Null)), _value)))),

        _Path_getValueAt, LAMBDA(_this,_index,
          LET(_this_rows, ROWS(_this),
            IF(_this_rows<_index, _Null,
              IFERROR(INDEX(_this, _index, 1), _Null)))),

        _Path_mergePath, LAMBDA(_this,_another,
          LET(_another_trim, _Path_trim(_another),
            REDUCE(_this, SEQUENCE(ROWS(_another_trim)),
              LAMBDA(_path,_rowIndex,
                LET(_a, _Path_getValueAt(_another_trim, _rowIndex),
                  IF(EXACT(_a,_Null), _path,
                    _Path_setValueAt(_path, _rowIndex, _a))))))),

        _pathArray,
          DROP(
            REDUCE(VSTACK(_Null), SEQUENCE(COLUMNS(_headerArray_act)),
              LAMBDA(_pathArray,_colIndex,
                HSTACK(_pathArray,
                  _Path_mergePath(
                    CHOOSECOLS(_pathArray, -1),
                    CHOOSECOLS(_headerArray_act, _colIndex))))),
            ,
            1),

        BYCOL(_pathArray,
          LAMBDA(_path,
            TEXTJOIN(_keySeparator_act, TRUE,
              FILTER(_path, NOT(ISERROR(_path)))))))),

  ～～ _toColKeys関数を使う数式をここに書く ～～
)

階層構造の行項目名を平坦にするワークシート関数: `_toRowKeys`

行項目名が階層構造になっているような表も日常的によくある。

この階層構造を平坦にして、項目名がディレクトリー・パス文字列のようになった1列だけからなる配列を求める関数 _toRowKeys は _toRowKeys に対して入力と出力を転置したものなので、こうした。（もちろん _toColKeys 関数と同様にゼロから作るほうがいいだろうが。）

=LET(
  ～～ _toColKeys関数の定義をここい書く ～～

  _toRowKeys,
    LAMBDA(_headerArray,[_keySeparator],
      TRANSPOSE(
        IF(ISOMITTED(_keySeparator),
          _toColKeys(TRANSPOSE(_headerArray)),
          _toColKeys(TRANSPOSE(_headerArray), _keySeparator)))),

  ～～ _toRowKeys関数を使う数式をここに書く ～～
)