大家都知道WORD內(nèi)可以自動生成索引目錄，以方便查詢使用，但在EXCEL里是否可以實現(xiàn)呢？答案是可以的，EXCEL自身無此功能，我們需要借助VBA來實現(xiàn)：下面office學(xué)院教你如何使用excel制作類似word的目錄索引！
首先打開要創(chuàng)建目錄的EXCEL文件 —— Alt+F11——插入——模塊——把下面的代碼拷貝到模塊中：
Sub mulu()
On Error GoTo Tuichu
Dim i As Integer
Dim ShtCount As Integer
Dim SelectionCell As Range
ShtCount = Worksheets.Count
If ShtCount = 0 Or ShtCount = 1 Then Exit Sub
Application.ScreenUpdating = False
For i = 1 To ShtCount
If Sheets(i).Name = ”目錄” Then
Sheets(“目錄”).Move Before:=Sheets(1)
End If
Next i
If Sheets(1).Name <> ”目錄” Then
ShtCount = ShtCount + 1
Sheets(1).Select
Sheets.Add
Sheets(1).Name = ”目錄”
End If
Sheets(“目錄”).Select
Columns(“B:B”).Delete Shift:=xlToLeft
Application.StatusBar = ”正在生成目錄…………請等待！”
For i = 2 To ShtCount
ActiveSheet.Hyperlinks.Add Anchor:=Worksheets(“目錄”).Cells(i, 2), Address:=”", SubAddress:= _
Sheets(i).Name & ”!R1C1″, TextToDisplay:=Sheets(i).Name
Next
Sheets(“目錄”).Select
Columns(“B:B”).AutoFit
Cells(1, 2) = ”目錄”
Set SelectionCell = Worksheets(“目錄”).Range(“B1″)
With SelectionCell
.HorizontalAlignment = xlDistributed
.VerticalAlignment = xlCenter
.AddIndent = True
.Font.Bold = True
.Interior.ColorIndex = 34
End With
Application.StatusBar = False
Application.ScreenUpdating = True
Tuichu:
End Sub
關(guān)閉VBA編輯界面，在你要插入的電子表格里按ALT+F8快捷鍵，選好你剛才寫的模塊mulu，點執(zhí)行，OK。
