這是 EXCEL進(jìn)階課堂 · 案例析 的第1篇教程。 01 案例引入工資查詢，是一個(gè)比較常見且具有典型代表性的任務(wù)。  工資查詢，到底要完成怎樣的一個(gè)功能呢？有小伙伴一定會(huì)說：很簡單呀，就是從一定量的工資數(shù)據(jù)中，按照查找項(xiàng)，找到相關(guān)人員的信息顯示出來。如果只是從函數(shù)應(yīng)用的方法來看，這個(gè)回答是沒有問題的，但是，如果從一個(gè)查詢系統(tǒng)層面來看，有五個(gè)重要問題需要思考： （1）所有人的工資信息，能不能讓人隨意看？如何處理？ （2）查詢頁面，任何地方都可以輸入內(nèi)容嗎？如何處理？ （3）查詢項(xiàng)可以隨便選擇嗎？選擇標(biāo)準(zhǔn)是什么？ （4）如何查詢？查詢不到怎么辦？ （5）查詢公式能讓人隨意查看嗎？如何處理？ 這些問題，很多小伙伴以前是沒有考慮過的。進(jìn)階君和大家一起進(jìn)行案例分析。 02 案例分析雖然這是一個(gè)簡單的查詢系統(tǒng)，但是它的安全性、容錯(cuò)性、合理性是需要認(rèn)真思考和規(guī)劃的。 （一）查詢系統(tǒng)安全性 （1）必須隱藏員工工資信息 分析：員工工資信息是非常隱私的數(shù)據(jù)，不能是任意人都能隨意查看的。工資查詢系統(tǒng)是面向所有人的，所以必須對員工工資信息作隱藏處理，而且為了防止人為破壞和篡改數(shù)據(jù)，還需要對數(shù)據(jù)進(jìn)行保護(hù)操作。 關(guān)鍵技術(shù)：工作表隱藏+工作表保護(hù) （2）查詢頁面內(nèi)容輸入?yún)^(qū)域限制 分析：查詢頁面（工作表）中，需要完成輸入內(nèi)容并顯示查詢結(jié)果。輸入內(nèi)容由用戶完成的，但是輸入的區(qū)域（單元格）必須限制，即只能在這里輸入，其它區(qū)域不允許輸入。查詢結(jié)果只能瀏覽，不能修改、刪除等。 關(guān)鍵技術(shù)：允許編輯區(qū)域設(shè)定+工作表保護(hù) （二）查詢系統(tǒng)合理性 （1）查詢項(xiàng)唯一準(zhǔn)則 分析：查詢系統(tǒng)就是根據(jù)某個(gè)查詢項(xiàng)去查找相關(guān)的記錄。選擇查詢項(xiàng)的合理性和科學(xué)性，直接影響到查詢結(jié)果。在選擇查詢項(xiàng)時(shí)，以查詢項(xiàng)唯一性為準(zhǔn)則進(jìn)行選擇。如：在姓名和身份證號兩個(gè)選項(xiàng)中，姓名有可能出現(xiàn)同名，而身份證號是唯一的，所以選擇身份證號為查詢項(xiàng)是合理的。再如：在姓名和工號兩個(gè)選項(xiàng)中，選擇工號為查詢項(xiàng)是合理的。 （2）公式隱藏原則 分析：查詢系統(tǒng)中，除了使用VBA技術(shù)外，自然會(huì)用到查詢函數(shù)形成公式，而這個(gè)公式如果能在查詢頁面當(dāng)中被看到，既影響美觀效果，也非常不合格。 關(guān)鍵技術(shù)：單元格保護(hù)+工作表保護(hù) （三）查詢系統(tǒng)容錯(cuò)性 所謂容錯(cuò)性，就是指對出現(xiàn)的各種非正常結(jié)果進(jìn)行必要的處理，使系統(tǒng)能夠給出各種合理的提示信息等。 （1）查詢無結(jié)果 分析：在查詢系統(tǒng)中，經(jīng)常會(huì)遇到查無結(jié)果的情況，對于這種情況需要必要的處理，比如顯示“查無此人”。 關(guān)鍵技術(shù)：IFERROR函數(shù) （2）查詢項(xiàng)無輸入 分析：在查詢項(xiàng)無輸入時(shí)，不應(yīng)該進(jìn)行查詢，否則會(huì)顯得很詭異。 關(guān)鍵技術(shù)：IF函數(shù) 03 案例解決進(jìn)階君將與各位小伙伴一起，不用VBA代碼，純函數(shù)來實(shí)現(xiàn)工資查詢系統(tǒng)，拋磚引玉，供大家舉一反三參考使用。 第一步：規(guī)劃數(shù)據(jù)表和設(shè)計(jì)查詢頁面 （1）規(guī)劃工資數(shù)據(jù)表 以“員工工資信息”為名，建立工作表，規(guī)劃和存放工資數(shù)據(jù)表。效果如下圖所示：  （2）設(shè)計(jì)查詢頁面 在單獨(dú)工作表中，設(shè)計(jì)查詢頁面，決定輸入項(xiàng)，查找顯示項(xiàng)等信息。根據(jù)前面的所講的原則，將工號設(shè)定為查詢項(xiàng)，與工號、姓名和與工資相關(guān)的數(shù)據(jù)形成查找顯示項(xiàng)。效果如下圖所示：  第二步：完成查詢公式設(shè)定 （1）基本公式設(shè)定 在一張工表中輸入內(nèi)容，然后再另一張工表當(dāng)中去查找，找到以后將結(jié)果返回到前表中，這是典型的查找應(yīng)用。實(shí)現(xiàn)這一功能的函數(shù)有很多，我們在這里采用vlookup函數(shù)來實(shí)現(xiàn)。 在“工資查詢”工作表中，選中B5單元格，然后在里面輸入公式：   公式的結(jié)果為：#N/A。原因有兩個(gè)：其一，查詢工號 沒有輸入；其二，查詢工號在沒有輸入的情況下不可能查詢到結(jié)果。 （2）第一次升級公式：排除沒有輸入情況 在“查詢工號”沒有輸入的情況下，不應(yīng)該去進(jìn)行查詢動(dòng)作，這需要對公式進(jìn)行升級處理。如何處理呢？用if函數(shù)進(jìn)行處理。修改B5單元格里面的公式：   公式修改后，可以使沒有輸入的情況不進(jìn)行查詢?？墒侨绻休斎耄跊]有找到相應(yīng)記錄的情況下，vlookup函數(shù)仍然回返回#N/A，這個(gè)符號對于一般用戶而言非常費(fèi)解。這種情況需處理。 （3）第二次升級公式：處理查無結(jié)果的情況 在C2單元格輸入查詢工號后，出現(xiàn)查無結(jié)果的時(shí)，會(huì)得到#N/A這個(gè)符號，它在EXCEL中會(huì)被認(rèn)為是一種錯(cuò)誤。于是我們可以用iferror函數(shù)來進(jìn)行處理。修改B5單元格里面的公式：   到此為止，B5單元里面的公式已經(jīng)比較完美了。但是，C5單元格至K5單元格仍需要輸入查詢公式，如果每個(gè)都去寫，那么效率很低，如果能夠進(jìn)行公式復(fù)制就會(huì)大大提高效率。 （4）第三次升級公式：解決公式拖動(dòng)復(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ū)域改成絕對引用方式后，采取拖動(dòng)復(fù)制的方式，將C5單元格至K5單元格填充好公式。具體效果如下動(dòng)圖所示。  第三步：完善后續(xù)設(shè)定 （1）隱藏“工資查詢”工作表 （2）設(shè)定C2單元格為可編輯區(qū)域 通過 審閱 菜單下面的 允許用戶編輯區(qū)域 進(jìn)行設(shè)定。 具體操作及效果如下動(dòng)圖所示。 （3）隱藏單元格公式設(shè)定 對單元格里面公式進(jìn)行隱藏需要通過兩步完成，這是第一步，需要與下一步進(jìn)行配合才能真正實(shí)現(xiàn)。具體操作及效果如下動(dòng)圖所示。 （4）對工作表和工作簿進(jìn)行保護(hù) 要使得可編輯區(qū)域和隱藏單元格公式起作用，需要對工作表進(jìn)行保護(hù)。 要使得隱藏的工作表不能被取消隱藏，需要對工作簿進(jìn)行保護(hù)。 具體操作過程及效果如下動(dòng)圖所示。 04 總結(jié)思考本案例以工資查詢系統(tǒng)為藍(lán)本，按系統(tǒng)規(guī)劃的思路，采取最基本的函數(shù)操作方法，完成了整個(gè)系統(tǒng)的實(shí)現(xiàn)。小伙伴可以舉一反三，結(jié)合工作實(shí)際進(jìn)行具體查詢實(shí)現(xiàn)。 為方便小伙伴們學(xué)習(xí)，進(jìn)階君將原始素材共享出來，獲取素材的方法： 第一步：關(guān)注 Excel進(jìn)階課堂。 第二步：私信 Excel進(jìn)階課堂，因?yàn)樵O(shè)定的是自動(dòng)回復(fù)，所以內(nèi)容一定要準(zhǔn)確 私信內(nèi)容：練一練 第三步：根據(jù)得到信息打開網(wǎng)盤，找到 案例01 工資查詢系統(tǒng) 工作簿 自行下載 |
|
|
