私信回復(fù)關(guān)鍵詞【工具】，獲取Excel高效小工具合集！

讓你的Excel效率開掛~

大家好，我是最近在家搞畢業(yè)相關(guān)的種種雜事，還不忘記學(xué)習(xí) Excel 的小E~

最近在梳理 Excel 知識點(diǎn)的時候，不經(jīng)意發(fā)出了一個疑問：

Word 中有目錄導(dǎo)航，我們通過點(diǎn)擊標(biāo)題，就可以跳轉(zhuǎn)到對應(yīng)的文檔位置。

PPT 中我們可以通過新增節(jié)，點(diǎn)擊對應(yīng)的節(jié)，就可以跳轉(zhuǎn)到相對應(yīng)的 PPT 頁面。

為什么在 Excel 中，對于工作表，沒有一個類似于導(dǎo)航目錄的玩意？？？

Excel 雖然也有導(dǎo)航欄，但是當(dāng)工作表數(shù)量很多，我們想找到指定工作表就太麻煩了！

緊接著，我就在想，既然 Excel 中沒有，那我們可不可以自己創(chuàng)建一個目錄頁？

所以根據(jù)這個思路，我做了一個這樣的目錄頁：

本文就介紹一下常用的 2 種創(chuàng)建超鏈接目錄的方法：

利用 HYPERLINK 函數(shù)創(chuàng)建目錄頁（適用于所有版本）

利用兼容性檢查創(chuàng)建目錄頁（適用于Office 2003 以上的版本）

大家就跟著我的思路，繼續(xù)往后面看吧~

現(xiàn)在工作簿中有如下的工作表，我們需要在目錄頁中創(chuàng)建工作表目錄。

01利用 hyperlink 函數(shù)創(chuàng)建目錄頁

創(chuàng)建目錄頁前，我們需要先獲取工作表名稱。

一個個輸入工作表名稱也是可以的，不過有點(diǎn)麻煩，下面我先介紹 2 種獲取工作表名稱的方法。

▋第一步：獲取工作表名稱，這里我們介紹兩種方法。

? 方法一：利用方方格子函數(shù)庫

之前我們介紹過方方格子函數(shù)庫，這里我們就用到函數(shù)庫里的 GetSheetName 函數(shù)：

GetSheetName 函數(shù)的語法規(guī)則為：

=GetSheetName(序號,[是否忽略隱藏表]))

比如說：

=GetSheetName(1)，就是獲得第一張表的名稱

=GetSheetName(2)，就是獲得第二張表的名稱

……

我們可以利用 ROW 函數(shù)獲得連續(xù)序號。

我們在目錄頁 A2 單元格中輸入公式：

=IFERROR(GetSheetName(ROW(A2)),"")

下拉填充，此時所有的工作表名稱都出來了。

? 方法二：自定義函數(shù)公式。

除了用函數(shù)庫，我們也可以自己寫一個自定義函數(shù)。

① 按住快捷鍵【Alt F11】打開 VBA 編輯器，右鍵插入一個模塊。

② 單擊模塊，將 VBA 代碼復(fù)制到編輯器里面。

Function getName(ByVal sheet_no As Integer)

getName = Worksheets(sheet_no).Name

這個的含義就是:Worksheets(1).Name，獲取第一張工作表的名稱；Worksheets(1).Name，獲取第一張工作表的名稱，以此類推……

End Function

現(xiàn)在在工作表中，就存在 GetName 函數(shù)了，我們直接使用就可以啦~

注意：

使用自定義函數(shù)的話，文檔需要另存為 xlsm/xls 格式。

不想改的話，可以直接獲取工作表名稱后，將名稱直接復(fù)制粘貼為值，

到這里，我們就得到工作表的名稱了，接下來我們就直接利用 hyperlink 函數(shù)創(chuàng)建目錄~

▋第二步：使用 hyperlink 函數(shù)創(chuàng)建目錄

hyperlink 函數(shù)基本語法：

=HYPERLINK(地址,[友好名稱])

我猜，肯定會有小伙伴直接這樣寫公式：

=HYPERLINK("#"&A2&"!A1",A2)

不過點(diǎn)開，結(jié)果會出錯，原因是 HYPERLINK 函數(shù)在引用單元格的時候，第一參數(shù)前面需要加個#號。

整合起來我們就可以直接編寫公式：

=HYPERLINK("#"&A2&"!A1",A2)

此時，目錄頁就大致做成了。

對于目錄頁的美化，我們可以將 A 列中的工作表名稱設(shè)置為白色，C 列取消下劃線，修改顏色。

02利用兼容性檢查創(chuàng)建目錄頁

PS：關(guān)于兼容性檢查：Office 可以檢查文檔與其他版本的 Office for Mac 和 Windows 版本的 Office 的兼容性，并創(chuàng)建兼容性報告。

你可以打開兼容性報告以了解有關(guān)任何兼容性問題的詳細(xì)信息，并嘗試修復(fù)。

我們先來看一下具體操作！

▋第一步

? 選中除目錄頁以外的所有的工作表。

點(diǎn)擊第 2 個工作表，按住【Shift】鍵不松開，點(diǎn)擊最后一個工作表。

? 在 A1 單元格中輸入：

=XDF1

? 按住快捷鍵【Ctrl Enter】批量填充。

▋第二步：打開兼容性檢查，將生成的目錄鏈接復(fù)制到目錄表上的 C2 單元格上。

? 選擇【文件】選項卡。

? 選擇「信息」-「檢查問題」-「檢查兼容性」。

? 此時會彈出兼容性檢查器對話框，選擇「復(fù)制到新表」，此時會出現(xiàn)一個工作表名稱為「Sheet2」的工作表。

▋第三步：將生成連接區(qū)域復(fù)制到目錄頁工作表 B2 單元格上，通過替換和字體格式設(shè)置，美化目錄頁。

? 將鏈接區(qū)域進(jìn)行復(fù)制。

? 將區(qū)域粘貼到目錄頁 C2 單元格。

?選中區(qū)域，按住快捷鍵【Ctrl H】調(diào)出替換窗口，將!A1 全部替換為（空）。

?去掉下劃線，更改字體顏色，修改單元格邊框。

此時，目錄頁就完成了。

鼠標(biāo)懸停在文字上面會出現(xiàn)小指頭，單擊之后可以跳轉(zhuǎn)到對應(yīng)的工作表。

最后將 Sheet2 工作表直接刪除即可。

▋第四步：為除目錄頁之外的工作添加跳轉(zhuǎn)鏈接。

選中除目錄頁之外的工作表。

在 A1 單元格中輸入公式：

=HYPERLINK("#目錄頁!A1","回到目錄頁")

【Ctrl Enter】批量填充，將字體設(shè)置為加粗綠色字體。

最后將目錄進(jìn)行簡單美化，就能做出開頭所示的效果啦~

看到這里，你肯定有一些小小的問號？

XDF1 是什么意思？

我們點(diǎn)擊 A1 單元格，按住快捷鍵【Ctrl →】，這個時候可以跳轉(zhuǎn)到最后一列，也就是 XFD1。

XDF1 跟 XFD1 一樣，其實就是一個單元格，列標(biāo)為 XDF，行標(biāo)為 1。

輸入公式：

=column(XDF1)

結(jié)果為 16334。

也就是 XDF1 的列數(shù)為 16334，工作表的最大列就是 XFD，也就是 16384 列。

為什么要輸入=XDF1 呢？

兼容格式的最大列數(shù)為 256 (IV)列，當(dāng)前格式的最大列數(shù)為 16384(XFD)列。

當(dāng)我們在工作表中使用公式=XDF1 后，再檢查兼容性問題。

由于兼容性格式最大列數(shù)為 256，引用不到列數(shù)為 16334 的單元格，所以會出現(xiàn)窗口提示。

我們將檢查到的內(nèi)容復(fù)制到新表中，就可以看到對應(yīng)提示問題中的超鏈接位置。

所以，除了引用 XDF1 這個單元格，我們還可以引用在 256（IV）到 16384(XFD)列之間的單元格。

03

好啦，最后總結(jié)一下本文介紹的 2 種方法：

利用 hyperlink 函數(shù)創(chuàng)建目錄。

涉及知識點(diǎn)：

? 獲取工作表名稱。

自定義函數(shù)的編寫，主要是利用 Worksheets(1).Name，表示第一個工作表的名稱。

你會發(fā)現(xiàn)其實 VBA 也不是特別難；

? 利用 hyperlink 函數(shù)創(chuàng)建超鏈接目錄，其中第一參數(shù)，在跳轉(zhuǎn)單元格的時候前面需要加一個#號。

利用兼容性檢查創(chuàng)建目錄。

涉及知識點(diǎn)：

? 兼容格式（03 版本）的最大列數(shù)為 256 (IV)列，除兼容版本以外的格式最大列數(shù)為16384（XFD）。

所以我們可以利用引用單元格構(gòu)造兼容性問題。

在檢查兼容性問題時，復(fù)制新表，得到我們創(chuàng)建目錄時所需要的超鏈接。

考考你：

下面這樣寫公式對嗎？如果不對，需要怎么改呢？

歡迎留言說出你的看法哦~回答正確的同學(xué)，獎勵一朵小紅花！

學(xué)會了今天的小技巧，當(dāng)別人還在一個個翻找工作表時，你通過目錄，一秒就能找到指定工作表啦！

如果你工作中經(jīng)常用到 Excel，熟練掌握這些基礎(chǔ)操作，能幫你大大提升效率！

私信回復(fù)關(guān)鍵詞【工具】，獲取Excel高效小工具合集！

讓你的Excel效率開掛~