excel索引 excel索引目錄 _知識經驗

文章插圖
大家好,小跳來為大家解答以上的問題。excel索引目錄，excel索引這個很多人還不知道,現在讓我們一起來看看吧！
【excel索引 excel索引目錄】1、先可新建一個工作表《索引》。
2、插入名稱-定義。
3、運用transpose(get,wordbook(1)提取足夠多的工作表名再用超鏈接公式鏈接用宏就比較簡單了我給你個宏的例子根據你表格的情況還要做部分修改Sub mulu()On Error GoTo TuichuDim i As IntegerDim ShtCount As IntegerDim SelectionCell As RangeShtCount = Worksheets.CountIf ShtCount = 0 Or ShtCount = 1 Then Exit SubApplication.ScreenUpdating = FalseFor i = 1 To ShtCountIf Sheets(i).Name = "目錄" ThenSheets("目錄").Move Before:=Sheets(1)End IfNext iIf Sheets(1).Name <> "目錄" ThenShtCount = ShtCount + 1Sheets(1).SelectSheets.AddSheets(1).Name = "目錄"End IfSheets("目錄").SelectColumns("B:B").Delete Shift:=xlToLeftApplication.StatusBar = "正在生成目錄…………請等待！"For i = 2 To ShtCountActiveSheet.Hyperlinks.Add Anchor:=Worksheets("目錄").Cells(i, 2), Address:="", SubAddress:= _Sheets(i).Name & "!R1C1", TextToDisplay:=Sheets(i).NameNextSheets("目錄").SelectColumns("B:B").AutoFitCells(1, 2) = "目錄"Set SelectionCell = Worksheets("目錄").Range("B1")With SelectionCell.HorizontalAlignment = xlDistributed.VerticalAlignment = xlCenter.AddIndent = True.Font.Bold = True.Interior.ColorIndex = 34End WithApplication.StatusBar = FalseApplication.ScreenUpdating = TrueTuichu:End Sub 。
本文到此分享完畢，希望對大家有所幫助。