Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2016)
関数で目次作成
投稿日時: 20/04/05 21:38:15
投稿者: アベッチ

目次シートを作成したのですが、シート名「目次」にA1〜A10までのセルに
関数を入れシート名が表示されるように出来ないでしょうか?
 
目次シートのA1には目次シートの1つ右のシート
目次シートのA2には目次シートの2つ右のシート
目次シートのA3には目次シートの3つ右のシート
と決め打ちの関数を入れて、シート名を表示したいです。
シート名は規則性がないのでVBAで言うシートインデックス番号を関数で指定出来れば実現出来るのではないかと考えていますが、なかなか方法が見つからず、、、
 
どなたか良い方法ありませんでしょうか?
よろしくお願い致します!!

回答
投稿日時: 20/04/05 22:17:54
投稿者: WinArrow
投稿者のウェブサイトに移動

ユーザー定義関数を提示します。
 
標準モジュールに
Function SHTNAME(CNT As Long)
Dim sx As Long
    SHTNAME = ""
    For sx = 1 To ThisWorkbook.Sheets.Count
        If sx = CNT Then
            SHTNAME = ThisWorkbook.Sheets(sx).Name
            Exit Function
        End If
    Next
End Function
 
 
先頭のシートのセルA2に
=SHTNAME(ROW())
下へフィルコピー

投稿日時: 20/04/06 02:15:48
投稿者: アベッチ

早速の回答ありがとうございます!
ちょっと伝わりずらかったかもしれませんが、VBAや名前の定義を使用せずに
関数のみで再現出来ないか探しておりました。

回答
投稿日時: 20/04/06 10:18:36
投稿者: んなっと

名前定義を使ってはいけない理由は何ですか?
拡張子 xlsm が使用禁止なのでしょうか。
 
PowerQueryを使う方法です。
 
●目次シートに以下のように
   A
1 Path
2 
 
A1にPathと入力
A2
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",)
 
→ホーム
→テーブルとして書式設定
→「先頭行をテーブルの見出しとして使用する」にチェックを入れてOK
→数式
→名前の管理
→テーブル1(または上で作成したテーブルの名前)を選択して編集
→名前を Bname に変更
→閉じる
  

 データ
→データの取得
→その他のデータソースから
→空のクエリ
→詳細エディター
→「let ... in ..」の部分を全選択して削除
→以下の文字列を貼り付け
  
let
    Nam = Excel.CurrentWorkbook(){[Name="Bname"]}[Content]{0}[Path],
    Src = Excel.Workbook(File.Contents(Nam), null, true),
    Sht = Table.SelectRows(Src, each [Kind] = "Sheet"),
    Shn = Table.SelectRows(Sht, each [Name] <> "目次"),
    Del = Table.SelectColumns(Shn,{"Name"})
in
    Del
 
→完了
→閉じて読み込む

回答
投稿日時: 20/04/06 10:31:32
投稿者: WinArrow
投稿者のウェブサイトに移動

アベッチ さんの引用:
早速の回答ありがとうございます!
ちょっと伝わりずらかったかもしれませんが、VBAや名前の定義を使用せずに
関数のみで再現出来ないか探しておりました。

 
ユーザー定義関数も関数ですよ!
 
名前の定義は、Excel4.0マウロを使いたくないということ?
 
 
※ないものねだりのような気がします。
 

回答
投稿日時: 20/04/06 11:08:35
投稿者: んなっと

関数だけの方法。
右のほうあいているところに作業列(下の例ではK列)を使います。
 
シート名が以下のようになっていたとします。
 
目次 青山 河合 田中
 
 
 画面一番下にあるシートタブの「青山」クリック
→Shiftキーを押しながら最後の「田中」クリック
→K2に
=IF(SHEET(K2)=ROW(),REPLACE(CELL("filename",K2),1,FIND("]",CELL("filename",K2)),),"")
下方向・↓コピー
 
→田中シートの右にシートを挿入して、「ラスト」という名前に変更
 
目次 青山 河合 田中 ラスト
 
 
→目次シートで
A1
=CONCAT(目次:ラスト!K2)
下方向・↓
 
   A
1 青山
2 河合
3 田中

回答
投稿日時: 20/04/06 11:20:35
投稿者: んなっと

最後に「ラスト」シートを非表示にしてもいいかもしれません。

回答
投稿日時: 20/04/06 12:11:58
投稿者: んなっと

ほかに...例えばすでにどのシートもB1にシート名を取得していたとします。
=REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1)),)
 
その場合、上と同じように右端に「ラスト」シートを挿入して
 
A1
=TRIM(MID(TEXTJOIN(REPT(" ",200),FALSE,目次:ラスト!$B$1),1+200*ROW(),200))
下方向・↓
 
でも一覧が取得できると思います。

回答
投稿日時: 20/04/06 21:08:52
投稿者: MMYS

WinArrow さんの引用:

Function SHTNAME(CNT As Long)
Dim sx As Long
    SHTNAME = ""
    For sx = 1 To ThisWorkbook.Sheets.Count
        If sx = CNT Then
            SHTNAME = ThisWorkbook.Sheets(sx).Name
            Exit Function
        End If
    Next
End Function

ループする理由が知りたいです。
 
Function SHTNAME(inx As Long)
    If inx <= ThisWorkbook.Sheets.Count Then
        SHTNAME = ThisWorkbook.Sheets(inx).Name
    Else
        SHTNAME = ""
    End If
End Function


 
質問内容はExcel4.0マクロを名前定義で呼び出せば、Excel機能のみで実現可能。
しかし、そのブックはマクロ無しなのに、マクロブックと同等扱いに。
だったら、ユーザー定義関数のほうが分かりやすい。

回答
投稿日時: 20/05/02 10:55:05
投稿者: WinArrow
投稿者のウェブサイトに移動

MMYS さんの引用:

ループする理由が知りたいです。
 
 
質問内容はExcel4.0マクロを名前定義で呼び出せば、Excel機能のみで実現可能。
しかし、そのブックはマクロ無しなのに、マクロブックと同等扱いに。
だったら、ユーザー定義関数のほうが分かりやすい。

そうですね・・・ループする必要がありませんね・・・

トピックに返信