Excelで数式とSPILLを利用して表の一部を表示する、一部を変更する

ここのところわりと頻繁にExcelを使った集計資料作成をしていたので、そこで知って便利だったテクニックをご紹介。

Problem

表（配列）をフィルタして結果を別の表として示したい。
ただし元表のすべてではなく一部の列だけを示したい。
また特定の列については元表のデータを加工して示したい。

Solution

SPILL（スピル）機能と表（配列）を操作する関数を利用します。

まず元表の例。国土地理院から頂いたデータです（国土地理院「日本の主な山岳一覧」）。安易に「SANGAKU」というテーブル名にしました：

この記事を執筆している2024年6月14日現在1059件のデータが含まれています。

①フィルタ結果を別の表として示す

SPILL機能で簡単に実現できます。
ここではFILTER関数を使いフィルタしています（都道府県名でフィルタ）。あとは何もしなくてもSPILL機能で新表が表示されます：

=FILTER(SANGAKU, (SANGAKU[都道府県]="北海道"), #N/A)

②元表のデータを加工しフィルタした結果を（以下略）

元表の値を使ってフィルタするだけでなく、元表の値を加工して得られた値でフィルタすることもできます（この例では標高を数値化してフィルタ）。加工にはLAMBDA関数を使います：

=FILTER(
    SANGAKU, 
    (SANGAKU[都道府県]="北海道")*(MAP(
        SANGAKU[標高], 
        LAMBDA(
            x, 
            INT(LEFT(x, LEN(x)-1))
        )
    )>=1000), 
    #N/A
)

見ればわかる・・・かもですが、ここでは加工した値はフィルタで使用するのみで元表自体には手を加えていないので、SPILL機能で表示される新表では加工前の値が表示されます。

③一部の列だけ示す（単一の列）

フィルタした結果別の表に示すのが一部の列だけでよいことも多いはずです。1列のみでよければINDEX関数を使えます：

=LET(
  san, 
  FILTER(...),
  INDEX(san, , 1)
)

INDEX関数の第1引数は配列、第2引数は抜き出す行の番号、第3引数は抜き出す列の番号です。ここでは行指定をせず列指定のみ行っています。
可読性のため、LET関数を利用してフィルタ結果の配列を変数に代入していますが、もちろんINDEX関数の引数の直接フィルタ結果を渡しても問題ありません。

④一部の列だけ示す（複数の列）

複数の列が必要な場合はCHOOSECOLS関数を使います：

=LET(
    san, 
    FILTER(...),
    CHOOSECOLS(san, 1, 5)
)

CHOOSECOLS関数の第1引数は配列、第2引数以降は抜き出す列の番号です。ここでは1番目の列と5番目の列を指定しています。

⑤特定の列は元表のデータを加工して示す

フィルタ結果のデータを新表として全部／一部表示するとき、特定の列についてはデータを加工してから表示したいということもあると思います。その場合は BYROW関数とLAMBDA関数による加工済データの配列の作成と HSTACK関数による加工前後の配列のマージ、そして CHOOSECOLS関数による列の取捨選択を行います：

=LET(
    san, 
    FILTER(...),
    CHOOSECOLS(
        HSTACK(
            san, 
            BYROW(
                san, 
                LAMBDA(
                    x, 
                    LET(
                        hyo, 
                        INDEX(x, 0, 5), 
                        LEFT(hyo, LEN(hyo)-1)
                    )
                )
            )
        ), 
        1, 
        10
    )
)

BYROWは配列を行単位で処理するための関数。処理内容は第2引数の LAMBDAで指定します。この例では行の中でも5番目の要素を取り出して加工をしています。
HSTACKは2つ以上の配列（行列）を水平方向に連結する関数。この例では加工前のすべての列を含む配列と、加工後の1列のみからなる配列を連結しています。