Excelワークシート関数: 階層構造の項目名を平坦にする (ネ申Excel表)

2024年6月29日

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/3158b09d-76c7-c377-15d1-4c5396cbb63d.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/ce45549b-5d48-6f3a-576a-f3e2b23c3462.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/ed9611bf-998e-6f73-2fe8-cf67280addea.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/1f9554ba-d3c7-d823-fac2-5547e5ebf784.png)

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

### 前処理（正規化）

問題なのは上位階層が空になっている空セルがあることだ。空セルといっても実際にはスペース文字が入っていたり、エラー値が入っている場合などの「揺れ」あり得るので、その揺れを取り除いて正規化しておいて、特別な値 ``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`` はこんな感じだ。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/8046775b-3575-1d14-6401-2656a263cd49.png)

例えば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),

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/aeae94c6-8613-bd47-bac7-14662e051ed5.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/ccef1be4-43cb-8dce-bb7a-568eeb92e693.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/97979a62-8948-6bce-81e4-7207c1602b99.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/71723ae5-05c5-182f-17a8-50b7aee9baa8.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/0659db44-860c-4e27-07d2-9b16d5154fbf.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/cf8d91fd-29e2-bc95-1ad5-729a22bb054b.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/9ff6f898-6b50-1002-9cf8-3b5bd41ee903.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/79e3db99-75a9-2c58-6fec-4057d0d830e5.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/24608ca2-0b7e-54cb-fd49-70c294cb7fd8.png)

$\vdots$

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/3ea62742-4c1d-49fa-34f7-80e93702a482.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/bb06f6da-4118-3984-f3d4-fbb0eb7c7eec.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/18779f39-58f0-78e0-6ee2-3d9c3ea4fb05.png)

### 文字列化

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/ed9611bf-998e-6f73-2fe8-cf67280addea.png)

### ``_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``

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/e657f006-ef45-9e0f-6d36-c06fc5e9dace.png)

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

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3759695/e085ce8e-c74a-e798-c26e-f630c4d40246.png)