Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Excel 2021)
2021と365でのシート名の一覧を
投稿日時: 22/05/05 14:13:55
投稿者: toma100

こんにちは
 
ブック内のシート名一覧を抽出するために
いろいろしらべて
2019までは
名前に =GET.WORKBOOK(1)
と入力して
シートには =REPLACE(INDEX(シート名,ROW(A1)),1,FIND("]",シート名),"")
 
として、シートの数だけ下へコピーして一覧を求めていました
 
ですが、2021/365ではエラーになって求められません。
2021/365でシート名一覧を抽出するには、どうしたら求められますでしょうか?
よろしくお願いいたします。

回答
投稿日時: 22/05/05 16:25:36
投稿者: 半平太

VBAを使うしかないんじゃないですかねぇ・・
VBAは禁止ってことだと厄介な話になりそう。

投稿日時: 22/05/05 16:34:18
投稿者: toma100

半平太様
 
ありがとうございます!
VBAは使えないのと諸事情で禁止となっております。
数式でなんとかしたいです
2021/365で急にできなくなって困ってしまって・・

回答
投稿日時: 22/05/05 16:46:48
投稿者: んなっと

関係ないかもしれませんが、セキュリティの向上のため4.0マクロは既定では無効になるような方針になりました。
有効にするには
 ファイル
→一番左下のオプション
→トラストセンター
→トラストセンターの設定
→マクロの設定
→VBAマクロが有効な場合にExcel4.0のマクロを有効にする にチェックを入れる
ただし、今回の問題の解決にはならないような気がします。
 
 
【代替案@】
 
Sheet1〜Sheet5が左からこの順に並んでいるとして、
 
1. Sheet1〜Sheet5をグループ化した状態でK1を選択して以下の式を入れる
 
=REPLACE(CELL("filename",K1),1,FIND("]",CELL("filename",K1)),)
 
  →対象シートすべてのK1にシート名が入ります。
 
2. どこかのシートをクリックしてグループ化を解除し、任意のセルに
 
=FILTERXML("<y><x>"&TEXTJOIN("</x><x>",TRUE,Sheet1:Sheet5!K1)&"</x></y>","//x")
 
  これでシート名一覧が縦に並びます。
 
  
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4
5 Sheet5

回答
投稿日時: 22/05/05 18:45:59
投稿者: んなっと

【代替案A】
  
●どこかのセルに
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",)
→上のセルを選択した状態で、数式バーの左の名前ボックスに次の名前を入れる
Fname
   

 データ
→データの取得
→その他のデータソースから
→空のクエリ
→詳細エディター
→「let ... in ..」の部分を全選択して削除
→以下の文字列を貼り付け
   
let
    Fnm = Excel.CurrentWorkbook(){[Name="Fname"]}[Content]{0}[Column1],
    Src = Excel.Workbook(File.Contents(Fnm), null, true),
    Flt = Table.SelectRows(Src, each ([Kind] = "Sheet")),
    Del = Table.SelectColumns(Flt,{"Name"})
in
    Del
  
→閉じて読み込む
 
シート名が変わったら データ→すべて更新

投稿日時: 22/05/05 19:07:11
投稿者: toma100

んなっと様、いつもありがとうございます!!
 
オプションを変更しても変わらなかったので
VBAの記述とかまったくわからないので
代替案@でうまくできました!
 
しかし、"<y><x>"とか"</x><x>"はどういう意味になるのでしょうか?
以前、サイトを教えてもらったのですが、
さっぱりわからなくて・・
ヒントだけでも教えていただけないでしょうか?(´;ω;`)

回答
投稿日時: 22/05/08 18:56:29
投稿者: んなっと

代替案Aが基本だと思いますよ。もう一度コピー貼り付けして試してください。

投稿日時: 22/05/08 20:15:08
投稿者: toma100

んなっと様
 
ご返信ありがとうございます!
HTMLは難しいので、
代替案Aを使うことにしました、うまくできました!
本当にありがとうございました/

投稿日時: 22/05/08 20:18:12
投稿者: toma100

閉じるのをわすれました