[[20120625185015]]　『シート名の一覧表示』（KJS）　ページの最後に飛ぶ

『シート名の一覧表示』（KJS） Excel2003 WindowsXP

 1つのブックに最高で300枚超のシートが有り、シート名等を記入した一覧表を作りたいと思います。

 ２点教えて下さい。

 ①　「シート1」の B1 にブック名を表示したく　過去ログより　
 =MID(CELL("filename",B1),FIND("[",CELL("filename",B1),1)+1,FIND("]",CELL("filename",B1),1)-FIND("[",CELL("filename",B1),1)-1)　を貼付しました。
 表示は、　　ブック名,xls　　　となります。　　最後の4文字　　,xls   を削除して　ブック名　だけを表示する方法を教えて下さい。

 ②　「シート1」の　[A3,B3]:[U32,V32]　の範囲に　「シート9」から「シート338」の　AS1(タイトル),AS3(サブタイトル) の文字を表示する方法を教えて下さい。
     　実際は「シート9」「シート10」・・・ではなく、別の名前が入っています。

 詳細は下記です
 　　「シート 9」 の AS1 の文字を　「シート1」 の  A3 に表示 　　
     「シート 9」 の AS3 の文字を　「シート1」 の  B3 に表示

     「シート10」 の AS1 の文字を　「シート1」 の  A4 に表示 　　
     「シート10」 の AS3 の文字を　「シート1」 の  B4 に表示
                       ・
　　　　　　　　　　　 ・
                       ・
     「シート38」 の AS1 の文字を　「シート1」 の A32 に表示 　　
     「シート38」 の AS3 の文字を　「シート1」 の B32 に表示

     「シート39」 の AS1 の文字を　「シート1」 の  C3 に表示 　　
     「シート39」 の AS3 の文字を　「シート1」 の  D3 に表示

                       ・
　　　　　　　　　　　 ・
                       ・
     「シート68」 の AS1 の文字を　「シート1」 の C32 に表示 　　
     「シート68」 の AS3 の文字を　「シート1」 の D32 に表示

     「シート69」 の AS1 の文字を　「シート1」 の  E3 に表示 　　
     「シート69」 の AS3 の文字を　「シート1」 の  F3 に表示
                       ・
　　　　　　　　       ・
                       ・
 以上　宜しくご指導下さい。

 1番目。

 =REPLACE(LEFT(CELL("filename",A1),FIND(".xls]",CELL("filename",A1))-1),1,FIND("[",CELL("filename",A1)),"")
 で、どうかな？

 LEFTで".xls]"の前までを抜き出してREPLACEで"["のところまでを消してるよ。

 2番目。

 まず、挿入-名前-定義で名前に適当な名前を（例として「シート名」）、参照範囲に「=GET.WORKBOOK(1)&LEFT(NOW(),0)」って設定しておいて。

 そしたらSheet1のA3に
 =INDIRECT("'"&INDEX(シート名,9+ROW(A1)-1+INT((COLUMN(A1)-1)/2)*30)&"'!AS"&MOD(COLUMN(A1)-1,2)*2+1)
 って入力して下と右にフィルコピーしてみて。
 (春日野馨)

 (春日野馨)さん　有り難うございます。
 出来ました。　　ただ、表として　[A3,B3]:[U32,V32]　を作ってあり
 シートが無い部分は　#REF!  が表示されます。
 シートが無い部分を　空白にする方法を教えていただけませんか。
 (KJS)

 2003だったら

 =IF(ISERROR(INDIRECT("'"&INDEX(シート名,9+ROW(A1)-1+INT((COLUMN(A1)-1)/2)*30)&"'!A1")),"",INDIRECT("'"&INDEX(シート名,9+ROW(A1)-1+INT((COLUMN(A1)-1)/2)*30)&"'!AS"&MOD(COLUMN(A1)-1,2)*2+1))
 になるかな？

 2007以降だったら
 =IFERROR(INDIRECT("'"&INDEX(シート名,9+ROW(A1)-1+INT((COLUMN(A1)-1)/2)*30)&"'!AS"&MOD(COLUMN(A1)-1,2)*2+1),"")
 ってできるんだけども。
 (春日野馨)

 (春日野馨)さん  大変お世話になりました。
 2003 なので　上のを使わせていただきました。

 勉強のために　上記関数の解説をしていただけませんか。　　わがまま言って申し訳有りません。
 (KJS)

 まず、
 >まず、挿入-名前-定義で名前に適当な名前を（例として「シート名」）、参照範囲に「=GET.WORKBOOK(1)&LEFT(NOW(),0)」って設定しておいて。
 これで、「シート名」はそのブックに含まれてるシート名の一覧になるよ。

 そこでINDEXでセルに対応したシート名を抜き出してくるよ。
 INDEX内の
 9+ROW(A1)-1+INT((COLUMN(A1)-1)/2)*30
 の式は9から始まって一行下に行くと1増えて2列右に行くと30増える数を返すようになってるんでセルに対応したシート名が求められるよ。

 =INDIRECT("'"&セルに対応したシート名&"'!AS"&MOD(COLUMN(A1)-1,2)*2+1)
 次に
 MOD(COLUMN(A1)-1,2)*2+1
 の式は、一列目が1、2列目が3、3列目が1、4列目が3…を返すんで

 =INDIRECT("'"&セルに対応したシート名&"'!AS"&1か3)
 になってINDIRECTの中に参照したい「シート名!セル番地」（正確には「[ブック名]シート名!セル番地」）の文字列ができて
 その文字列をINDIRECTで実際のセル参照に変換してるよ。
 (春日野馨)

 (春日野馨)さん　解説有り難うございます。
 勉強になります。

 もう１点教えていただきたいのですが

 開くと　　　「このブックには　Micrsoft Excel 4.0 マクロが１つ以上含まれています。・・・・・」　　が表示されます。
 これを表示しないように出来ますか。又は安全のためこのまま使用したほうが良いですか。
 (KJS)

 一応、ツール-マクロ-セキュリティを低にしたらメッセージはでなくなるけど、マクロウィルスが入ってるようなブックを開くときにも
 メッセージがでなくなるんでそのままのほうがいいと思うよ。

 ただ、ここら辺は個人で使ってるとか、社内で使ってるとか身元の明らかな安全なブックしか扱ってないとか
 その人の環境によって変わってくるよ。
 (春日野馨)

 (春日野馨)さん　有り難うございます。
 セキュリティを　中　にして使用します。
 大変お世話になりました。
 (KJS)

コメント返信：

[ 一覧(最新更新順) ]

[[20120625185015]] 『シート名の一覧表示』（KJS） ページの最後に飛ぶ

[[20120625185015]]　『シート名の一覧表示』（KJS）　ページの最後に飛ぶ