EXCEL進階課堂 · 案例析 開始推出！在工作當(dāng)中，我們會遇到各種具體的問題，有許多問題具有典型代表性。進階君把這些案例提煉出來，通過清晰明了的分析思路和操作步驟，試圖給小伙伴們帶來啟發(fā)和思考，更好地解決工作任務(wù)，提高工作效率，不再做不停加班的表哥，表姐。歡迎各位小伙伴轉(zhuǎn)發(fā)、點贊、討論，更歡迎私信獲取練習(xí)素材，刻意練習(xí)才能學(xué)有收獲。

這是 EXCEL進階課堂 · 案例析 的第1篇教程。

01 案例引入

工資查詢，是一個比較常見且具有典型代表性的任務(wù)。

工資查詢，到底要完成怎樣的一個功能呢？有小伙伴一定會說：很簡單呀，就是從一定量的工資數(shù)據(jù)中，按照查找項，找到相關(guān)人員的信息顯示出來。如果只是從函數(shù)應(yīng)用的方法來看，這個回答是沒有問題的，但是，如果從一個查詢系統(tǒng)層面來看，有五個重要問題需要思考：

（1）所有人的工資信息，能不能讓人隨意看？如何處理？

（2）查詢頁面，任何地方都可以輸入內(nèi)容嗎？如何處理？

（3）查詢項可以隨便選擇嗎？選擇標(biāo)準(zhǔn)是什么？

（4）如何查詢？查詢不到怎么辦？

（5）查詢公式能讓人隨意查看嗎？如何處理？

這些問題，很多小伙伴以前是沒有考慮過的。進階君和大家一起進行案例分析。

02 案例分析

雖然這是一個簡單的查詢系統(tǒng)，但是它的安全性、容錯性、合理性是需要認真思考和規(guī)劃的。

（一）查詢系統(tǒng)安全性

（1）必須隱藏員工工資信息

分析：員工工資信息是非常隱私的數(shù)據(jù)，不能是任意人都能隨意查看的。工資查詢系統(tǒng)是面向所有人的，所以必須對員工工資信息作隱藏處理，而且為了防止人為破壞和篡改數(shù)據(jù)，還需要對數(shù)據(jù)進行保護操作。

關(guān)鍵技術(shù)：工作表隱藏+工作表保護

（2）查詢頁面內(nèi)容輸入?yún)^(qū)域限制

分析：查詢頁面（工作表）中，需要完成輸入內(nèi)容并顯示查詢結(jié)果。輸入內(nèi)容由用戶完成的，但是輸入的區(qū)域（單元格）必須限制，即只能在這里輸入，其它區(qū)域不允許輸入。查詢結(jié)果只能瀏覽，不能修改、刪除等。

關(guān)鍵技術(shù)：允許編輯區(qū)域設(shè)定+工作表保護

（二）查詢系統(tǒng)合理性

（1）查詢項唯一準(zhǔn)則

分析：查詢系統(tǒng)就是根據(jù)某個查詢項去查找相關(guān)的記錄。選擇查詢項的合理性和科學(xué)性，直接影響到查詢結(jié)果。在選擇查詢項時，以查詢項唯一性為準(zhǔn)則進行選擇。如：在姓名和身份證號兩個選項中，姓名有可能出現(xiàn)同名，而身份證號是唯一的，所以選擇身份證號為查詢項是合理的。再如：在姓名和工號兩個選項中，選擇工號為查詢項是合理的。

（2）公式隱藏原則

分析：查詢系統(tǒng)中，除了使用VBA技術(shù)外，自然會用到查詢函數(shù)形成公式，而這個公式如果能在查詢頁面當(dāng)中被看到，既影響美觀效果，也非常不合格。

關(guān)鍵技術(shù)：單元格保護+工作表保護

（三）查詢系統(tǒng)容錯性

所謂容錯性，就是指對出現(xiàn)的各種非正常結(jié)果進行必要的處理，使系統(tǒng)能夠給出各種合理的提示信息等。

（1）查詢無結(jié)果

分析：在查詢系統(tǒng)中，經(jīng)常會遇到查無結(jié)果的情況，對于這種情況需要必要的處理，比如顯示“查無此人”。

關(guān)鍵技術(shù)：IFERROR函數(shù)

（2）查詢項無輸入

分析：在查詢項無輸入時，不應(yīng)該進行查詢，否則會顯得很詭異。

關(guān)鍵技術(shù)：IF函數(shù)

03 案例解決

進階君將與各位小伙伴一起，不用VBA代碼，純函數(shù)來實現(xiàn)工資查詢系統(tǒng)，拋磚引玉，供大家舉一反三參考使用。

第一步：規(guī)劃數(shù)據(jù)表和設(shè)計查詢頁面

（1）規(guī)劃工資數(shù)據(jù)表

以“員工工資信息”為名，建立工作表，規(guī)劃和存放工資數(shù)據(jù)表。效果如下圖所示：

（2）設(shè)計查詢頁面

在單獨工作表中，設(shè)計查詢頁面，決定輸入項，查找顯示項等信息。根據(jù)前面的所講的原則，將工號設(shè)定為查詢項，與工號、姓名和與工資相關(guān)的數(shù)據(jù)形成查找顯示項。效果如下圖所示：

第二步：完成查詢公式設(shè)定

（1）基本公式設(shè)定

在一張工表中輸入內(nèi)容，然后再另一張工表當(dāng)中去查找，找到以后將結(jié)果返回到前表中，這是典型的查找應(yīng)用。實現(xiàn)這一功能的函數(shù)有很多，我們在這里采用vlookup函數(shù)來實現(xiàn)。

在“工資查詢”工作表中，選中B5單元格，然后在里面輸入公式：

公式的結(jié)果為：#N/A。原因有兩個：其一，查詢工號 沒有輸入；其二，查詢工號在沒有輸入的情況下不可能查詢到結(jié)果。

（2）第一次升級公式：排除沒有輸入情況

在“查詢工號”沒有輸入的情況下，不應(yīng)該去進行查詢動作，這需要對公式進行升級處理。如何處理呢？用if函數(shù)進行處理。修改B5單元格里面的公式：

公式修改后，可以使沒有輸入的情況不進行查詢?？墒侨绻休斎?，在沒有找到相應(yīng)記錄的情況下，vlookup函數(shù)仍然回返回#N/A，這個符號對于一般用戶而言非常費解。這種情況需處理。

（3）第二次升級公式：處理查無結(jié)果的情況

在C2單元格輸入查詢工號后，出現(xiàn)查無結(jié)果的時，會得到#N/A這個符號，它在EXCEL中會被認為是一種錯誤。于是我們可以用iferror函數(shù)來進行處理。修改B5單元格里面的公式：

到此為止，B5單元里面的公式已經(jīng)比較完美了。但是，C5單元格至K5單元格仍需要輸入查詢公式，如果每個都去寫，那么效率很低，如果能夠進行公式復(fù)制就會大大提高效率。

（4）第三次升級公式：解決公式拖動復(fù)制問題

在vlookup函數(shù)中，返回查詢結(jié)果是需要指定的，B5返回的是第1列，C5就應(yīng)該返回第2列，如此類推。這樣的指定如何用公式解決呢？大家觀察，B5單元格所在的列是第2列，要返回的是查詢區(qū)域的第1列，C5單元格所在的列是第3列，要返回的是查詢區(qū)域的第2列。于是，我們可以總結(jié)出，要返回的查詢區(qū)域的列數(shù)比單元格所在列數(shù)少1。修改B5單元格里面的公式：

將查找區(qū)域改成絕對引用方式后，采取拖動復(fù)制的方式，將C5單元格至K5單元格填充好公式。具體效果如下動圖所示。

第三步：完善后續(xù)設(shè)定

（1）隱藏“工資查詢”工作表

（2）設(shè)定C2單元格為可編輯區(qū)域

通過 審閱 菜單下面的 允許用戶編輯區(qū)域 進行設(shè)定。

具體操作及效果如下動圖所示。

（3）隱藏單元格公式設(shè)定

對單元格里面公式進行隱藏需要通過兩步完成，這是第一步，需要與下一步進行配合才能真正實現(xiàn)。具體操作及效果如下動圖所示。

（4）對工作表和工作簿進行保護

要使得可編輯區(qū)域和隱藏單元格公式起作用，需要對工作表進行保護。

要使得隱藏的工作表不能被取消隱藏，需要對工作簿進行保護。

具體操作過程及效果如下動圖所示。

04 總結(jié)思考

本案例以工資查詢系統(tǒng)為藍本，按系統(tǒng)規(guī)劃的思路，采取最基本的函數(shù)操作方法，完成了整個系統(tǒng)的實現(xiàn)。小伙伴可以舉一反三，結(jié)合工作實際進行具體查詢實現(xiàn)。

為方便小伙伴們學(xué)習(xí)，進階君將原始素材共享出來，獲取素材的方法：

第一步：關(guān)注 Excel進階課堂。

第二步：私信 Excel進階課堂，因為設(shè)定的是自動回復(fù)，所以內(nèi)容一定要準(zhǔn)確

私信內(nèi)容：練一練

第三步：根據(jù)得到信息打開網(wǎng)盤，找到 案例01 工資查詢系統(tǒng) 工作簿 自行下載