大家好呀，今天我們又來(lái)分享一個(gè)在Excel中能實(shí)現(xiàn)自動(dòng)化的功能——自動(dòng)創(chuàng)建封面目錄。一談到自動(dòng)化，很多人就會(huì)想到用VBA，很多童鞋估計(jì)談之色變，立即就會(huì)想到一堆看不懂的代碼，其實(shí)在Excel比較早期的版本中，是沒(méi)有VBA開(kāi)發(fā)功能的，而是一種叫做宏表函數(shù)來(lái)實(shí)現(xiàn)自動(dòng)化的功能。它比VBA相對(duì)簡(jiǎn)單得多，功能自然會(huì)少些，但有時(shí)我們?cè)谛枰獣r(shí)用到它，比如我們要制作封面目錄，既簡(jiǎn)單，又快捷！

在學(xué)習(xí)之前我們需要了解什么是宏表函數(shù)。

宏表函數(shù)，2003及以上的office版本上已由VBA頂替它的功能，但微軟將仍然保留了它的存在，依舊可以在工作表中使用，不過(guò)要特別注意的是：不能直接在單元格中、只能在'定義的名稱(chēng)'（菜單：插入——名稱(chēng)——定義）中使用。

宏表函數(shù)主要是通過(guò)“定義名稱(chēng)”實(shí)現(xiàn)，對(duì)我們而言，這個(gè)是相對(duì)比較容易的。

今天我們要用的宏表函數(shù)是GET.WORKBOOK(1)，這個(gè)函數(shù)是獲取工作簿中所有的工作表的名稱(chēng)，當(dāng)我們要做封面目錄時(shí)，它的功能就發(fā)揮到極致啦,我們來(lái)看看怎么樣實(shí)現(xiàn).

GET.WORKBOOK(1)獲取工作表名稱(chēng)

• 點(diǎn)擊“公式”菜單，定義名稱(chēng)
  

• 顯示新建名稱(chēng)對(duì)話(huà)框，名稱(chēng)中輸入sheetname

• 范圍選擇工作簿

• 引用位置輸入=GET.WORKBOOK(1)

• 點(diǎn)擊確定，搞定！

看看具體的操作如下：

GIF

定義名稱(chēng)

名稱(chēng)定義好了，那這個(gè)到底怎么用呢？

我們?cè)贐3單元格中輸入=sheetname，先來(lái)看看它到底具體指的是些什么內(nèi)容，了解公式函數(shù)的童鞋知道，選擇sheetname后按F9可以看到，它實(shí)際是所有工作表名稱(chēng)的數(shù)組，只是每個(gè)工作表名稱(chēng)都包含了工作簿的名稱(chēng)，這個(gè)基本就是我們想要的了，如果需要把工作簿名稱(chēng)去掉，可以使用REPLACE函數(shù)，當(dāng)然在制作封面目錄超鏈接時(shí)，可以不用替換。

GIF

F9轉(zhuǎn)化成值顯示

那么在數(shù)組中，我們?cè)趺礃影阉械墓ぷ鞅砻Q(chēng)分離出來(lái)呢，這里要用到的是index函數(shù)，先講講index函數(shù)的用法

INDEX函數(shù)

函數(shù)功能：INDEX 函數(shù)返回表格或區(qū)域中的值或值的引用。

使用格式：=INDEX(array,row_num,column_num)

通俗解釋?zhuān)?INDEX(數(shù)組或數(shù)據(jù)區(qū)域,返回結(jié)果所在的行,返回結(jié)果所在的列)，當(dāng)數(shù)據(jù)區(qū)域只有一行或一列時(shí)，可以省略

我們?cè)贐3單元格中輸入=INDEX(sheetname,ROW(A2))),公式下拉，這時(shí)所有工作表的名稱(chēng)都顯示出來(lái)啦，ROW函數(shù)我們之前講過(guò)，是返回單元格所在的行號(hào)。我們從第二個(gè)工作表開(kāi)始，是因?yàn)榈谝粋€(gè)工作表是“封面”，不必要顯示。

GIF

INDEX函數(shù)分離出工作表名稱(chēng)

這里介紹下REPLACE的用法↓↓↓

REPLACE函數(shù)

函數(shù)功能：根據(jù)指定的字符數(shù)，REPLACE 將部分文本字符串替換為不同的文本字符串。

使用格式：=REPLACE(old_text,start_num,num_chars,new_text)

通俗解釋?zhuān)?REPLACE(原始字符串,替換起始位,替換字符的個(gè)數(shù),新的字符)

我們將B3單元格中的公式改為=REPLACE(INDEX(sheetname,ROW(A2)),1,FIND(']',INDEX(sheetname,ROW(A2)),1),'')，這時(shí)，我們?nèi)〉降淖址褪枪ぷ鞅淼拿Q(chēng)啦。

GIF

REPLACE函數(shù)替換名稱(chēng)中的多余字符

上圖中，公式中包含嵌套了find公式，我們也來(lái)講講它的用法。

FIND函數(shù)

函數(shù)功能：用于在第二個(gè)文本串中定位第一個(gè)文本串，并返回第一個(gè)文本串的起始位置的值，該值從第二個(gè)文本串的第一個(gè)字符算起。

使用格式：=FIND(find_text,within_text,start_num)

通俗解釋?zhuān)?FIND(查找字符串,被查找字符串,查找的起始字符串)

公式FIND(']',INDEX(sheetname,ROW(A2)),1)，意思是在INDEX(sheetname,ROW(A2))中從第一個(gè)字符開(kāi)始查找“]”所在的位置

至此，所有工作表都被提取出來(lái)啦，接下來(lái)要做的是進(jìn)行超鏈接。超鏈接函數(shù)為HYPELINK

HYPELINK函數(shù)

函數(shù)功能：HYPERLINK函數(shù)創(chuàng)建一個(gè)快捷方式, 可跳轉(zhuǎn)到當(dāng)前工作簿中的其他位置, 或打開(kāi)存儲(chǔ)在網(wǎng)絡(luò)服務(wù)器、 intranet 或 Internet 上的文檔。 單擊包含超鏈接函數(shù)的單元格時(shí), Excel 將跳轉(zhuǎn)到列出的位置, 或打開(kāi)您指定的文檔。

使用格式：=HYPERLINK(link_location,friendly_name)

通俗解釋?zhuān)?HYPERLINK(#工作表名稱(chēng)+！+單元格地址,顯示的名稱(chēng))

知道了這個(gè)函數(shù)，繼續(xù)更改公式，將B3單元格公式改為=HYPERLINK('#'&REPLACE(INDEX(sheetname,ROW(A2)),1,FIND(']',INDEX(sheetname,ROW(A2)),1),'')&'!A1',REPLACE(INDEX(sheetname,ROW(A2)),1,FIND(']',INDEX(sheetname,ROW(A2)),1),''))

看看下圖：

GIF

HYPERLINK函數(shù)建立超鏈接

我們點(diǎn)擊超鏈接就可以轉(zhuǎn)至對(duì)應(yīng)表格中的A1單元格位置，我們看到還有一個(gè)問(wèn)題，當(dāng)我們下拉公式的數(shù)量超出工作表的個(gè)數(shù)時(shí)，超出的部分會(huì)顯示錯(cuò)誤“#REF!”,為了顯示更好看，我們需要隱藏錯(cuò)誤值，這里用的是IFERROR函數(shù)。

IFERROR函數(shù)

函數(shù)功能：可以使用 IFERROR 函數(shù)捕獲和處理公式中的錯(cuò)誤。 如果公式的計(jì)算結(jié)果為錯(cuò)誤值，則 IFERROR 返回您指定的值;否則，它將返回公式的結(jié)果。

使用格式：=IFERROR(value,value_if_error)

通俗解釋?zhuān)?IFERROR(顯示的值,值為錯(cuò)誤顯示的內(nèi)容)

繼續(xù)修改B3單元格的公式，下拉：

=IFERROR(HYPERLINK('#'&REPLACE(INDEX(sheetname,ROW(A2)),1,FIND(']',INDEX(sheetname,ROW(A2)),1),'')&'!A1',REPLACE(INDEX(sheetname,ROW(A2)),1,FIND(']',INDEX(sheetname,ROW(A2)),1),'')),'')，錯(cuò)誤的內(nèi)容就不顯示出來(lái)了。

GIF

IFERROR函數(shù)不顯示錯(cuò)誤值

這樣工作表封面目錄就制作完成啦！當(dāng)工作簿里增加了工作表或工作表變動(dòng)，我們只需要往下拖拉填充公式即可自動(dòng)提取工作表名稱(chēng)，工作表的名稱(chēng)就會(huì)自動(dòng)刷新啦。

另外因?yàn)槲覀兪褂昧撕瓯砗瘮?shù)，在普通表格中無(wú)法保存，需要在另存為中選擇“Excel啟用宏的工作簿”，后綴名為 xlsm  或者另存為“Excel 97-2003工作簿”。

總結(jié)：

• 用GET.WORKBOOK(1)獲取所有工作表的名稱(chēng)
  

• 生成的工作表名稱(chēng)包含工作簿名稱(chēng)，如需要替換工作簿名稱(chēng)，可以使用REPLACE函數(shù)

• 隱藏公式錯(cuò)誤值，我們可以使用IFERROR函數(shù)

• 宏表函數(shù)缺點(diǎn)是不能自動(dòng)更新，工作表名稱(chēng)如果更新時(shí)，需要重新下拉公式，刷新下結(jié)果。

• 2007以上的版本帶有宏表函數(shù)時(shí)，需要另存為xlsm格式

以上是在Excel中制作封面目錄并自動(dòng)更新的全部?jī)?nèi)容，因涉及的函數(shù)個(gè)數(shù)比較多，所以最終使用的公式看起來(lái)比較長(zhǎng)，但總體難度還是不大的。

這里是Excel倫特吧，只為提高效率！