マクロ関数を使用せずにシート名の一覧をスピル形式で生成する方法

この記事ではシート名の一覧をスピル形式で生成させる手順をご紹介します。

なお、「マクロ関数」を使用することが可能な環境(マクロ有効ブックにして「信頼できる場所」へ登録しておける環境)であるならば、以下の記事の方法が簡単です。

この記事では「マクロ関数」を使用せずにシート名の一覧を生成する流れをご紹介します。

シート一覧の最初と最後に認識用のシートを設置する

例では「Sheet1」～「Sheet4」があります。これらのリストを作成する前に、最初と最後に適当な空のシートを設置しましょう。例では「S開始」「S終了」を設置しています。

次にすべてのシートの同じ位置にシート名を表示する計算式をセットします。位置はどこでもかまいません。下の例では一番右のセル「XFD1」へセットしています。

計算式は以下のものになります。ただしこの計算式は「ファイルの保存」がなされていないと有効になりませんので注意してください。

=TEXTAFTER(CELL("filename", A1), "]")

この計算式に関する詳しい説明は下記の記事になります。

ここではすべてのシートの一番右のセル「XFD1」へ計算式をセットする流れを紹介します。

サンプルでは一番右のXFD1へセットしているが、共通した位置ならばどこでもよい。

準備ができたら、シート名の一覧を生成したいセルへ以下の計算式をセットします。

=TEXTSPLIT(TEXTJOIN(",",,S開始:S終了!XFD1),,",")

この式の意味を下で解説します。

まず、TEXTJOIN関数を使って、XFD1セルに表示されたすべてのシート名をカンマ区切りで結合します。区切り文字は別のものでも有効です。

=TEXTJOIN(",",,S開始:S終了!XFD1)

この部分では、「S開始」シートから「S終了」シートまでのすべてのシート名が結合され、1つの文字列として返されます。

最後に、TEXTSPLIT関数を使って、結合されたシート名をカンマで行方向に分割し、個々のシート名をスピル形式で表示します。

=TEXTSPLIT(TEXTJOIN(",",,S開始:S終了!XFD1),,",")

この最終的な計算式により、すべてのシート名が1つのセルにリストとして表示され、スピル形式で展開されます。

先頭の「S開始」と最後の「S終了」を表示させないのならば以下の一例のようにDROP関数で加工するのがよいでしょう。

=DROP(DROP(TEXTSPLIT(TEXTJOIN(",",,S開始:S終了!XFD1),,","),1),-1)