Excelでシート一覧を作成したい
が、現在Excelにはそんな機能が無い
2019年末スピルという機能が導入され、セル数を可変に出力できるようになった。
しかし未だにシート一覧を表示する機能が無いのが現状。
そりゃVBAマクロ使えば出力できます。
ただ、そのファイルを複数人に共有したりマクロ有効を使ってもらったりと
あまり好まれないのも事実です。
またマクロの更新トリガーを使って更新すると「元に戻す」いわゆるUndoが使えなくなったりして不便を強いられます。
どんな人向けか
- プロジェクト向けに未だにExcelで文書を書くことを強要されている人
- マクロは使わずにExcelを作成することを強要されている人
- やたらとExcelで資料を書くことを強要されている人
-
上司がExcelハッピーなのでExcelを強要されている人
スピルを使うことでマクロ付きファイル(*.xlsm)を回避しつつシート一覧を作成します
BLUF
表示概要
表示方法としては
各シートの共通セルにシート名を出力し
まとめて結合して文字列化、分解配列化、スピル出力します
コード
-
ExcelBookを保存しておく
-
シート一覧に表示したいシートのA1セル
=TEXTAFTER(CELL("filename",A1),"]")
を入力しシート名をメタ空間からシート内へ持ち込んでおく -
- シート一覧を表示したいセルに以下を入力
=TEXTSPLIT(TEXTJOIN("/", TRUE, 表題:後書き!A1), , "/")
または - リンクとして一覧を表示したい場合はセルに以下を入力
=LET( names, TEXTSPLIT(TEXTJOIN("/", TRUE, 表題:後書き!A1), , "/"), MAP(names, LAMBDA(n, HYPERLINK("#'" & n & "'!A1", n))) )
- シート一覧を表示したいセルに以下を入力
表示例
メリット
- 冒頭に記載通りマクロ不要
- 表示したいシートを厳選できる
デメリット
- 表示したい全シートのA1かどこかにシート名を表示するための領域が占有される
- 先頭シートと末尾シートを固定する必要がある
コード解説
シート名取得
=TEXTAFTER(CELL("filename",A1),"]")
言わずもがなシート名をセルに出力します
例としてA1に関数を入れて表示しています。
対象は一覧に出力したいシートに入力しておきます
全シート名
関数を分解説明します
TEXTJOIN
=TEXTJOIN("/", TRUE, 表題:後書き!A1)
表題～後書きにあるシートのA1を3D選択します。
一覧に含める場合はこの間にシートを置いてA1にセル名取得を入れます。
TEXTJOINは3D選択も展開してくれます
ここでは/でシート名を結合しています
実行結果は以下の様になります
シート1/本文/シート2/後書き
TEXTSPLIT
=TEXTSPLIT(TEXTJOIN("/", TRUE, 表題:後書き!A1), , "/")
シート1/本文/シート2/後書きと結合された文字列を/を基準に分解、スピル化して出力します
これで一覧をスピル出力できるようになります
LET / MAP
=LET(
names, TEXTSPLIT(TEXTJOIN("/", TRUE, 表題:後書き!A1), , "/"),
MAP(names, LAMBDA(n, HYPERLINK("#'" & n & "'!A1", n)))
)
まあLambdaですね
- LETの第2引数の出力Arrayをnamesに入れて
- 第3引数をArray実行
- LETのnamesをMAP第1引数で受けて
- Array数分 LAMBDAを個別実行
- HYPERLINKでリンク型セルへ成形
これでジャンプ付き目次かつセル数増減で自動更新されるようになります。
セル名はA1以外でも大丈夫です。
全セル同じセルに入力してください。
その場合表題:後書き!A1を書き換えてください。