超讚的Excel動態查詢表:趕緊做起來

2019-02-21 03:42:22

008號是誰?電話多少?

這不是相親,而是員工信息快速查詢!可能你所在公司的人員成百上千,怎么根據工號快速查詢到職員的信息呢?你需要製作一張員工信息動態查詢表!有了信息動態查詢表,別說姓啥、電話,就是長啥樣也可以查到,最終效果如下所示:

單位的員工登記表,一般都非常長,當我們要查詢某個員工信息時,需要左右拖動查看,很容易看錯行。瓶子在這裡只例舉了十個人的情況,而很多公司都是上百人甚至上千人,想快速在員工登記表里查看某個員工信息十分的困難。

下面我們就在sheet2里,單獨製作一個員工信息查詢表。這裡幾乎沒有什麼操作技巧,就是把自己需要查詢的項目名稱輸入進去,其中標題和照片處,使用了合併單元格,最後利用“開始”選項卡的“字型”組裡的“框線”給單元格加上框線。

分析:

我們想要的最終效果是在D3單元格輸入工號,然後下方的信息自動顯示出來,所以可以考慮用VLOOKUP函式,依照工號到員工登記表里查找,並返回需要的選項。

完成過程:

01

由於我們的工號是00開頭的,若直接輸入001隻會顯示1,所以我們先選中D3單元格,將其設定為“文本”格式。

再給工號設定“下劃線”和“居中”的樣式,結果如下。

02

在D4單元格輸入公式:

=VLOOKUP($D$3,員工登記表!$A$1:$R$11,MATCH(C4,員工登記表!$A$1:$R$1,0),0),回車,可以看到工號001對應的員工姓名已經顯示出來了。

公式解析:

1.MATCH(C4,員工登記表!$A$1:$R$1,0)

含義是根據C4單元格,在員工登記表里A1-R1單元格區域精確查找,返回對應的列號。由於我們的公式需要下拉右拉並保持查找區域不變,所以單元格區域A1:R1是絕對引用。

2.VLOOKUP($D$3,員工登記表!$A$1:$R$11,MATCH(C4,員工登記表!$A$1:$R$1,0),0)

含義是:根據D3單元格輸入的工號,在員工登記表里A1-R11單元格區域精確查找,可得到行號,再結合MATCH函式得到的列號,最終返回行列交叉的值。

03

由於在這個表格里,我們需要隔列填充公式,所以不能直接右拉。我們先下拉公式,得到如下所示的結果。

然後按住ctrl鍵,依次選中F列、H列的單元格和第9行的空單元格。

保持按住ctrl鍵,點選D4單元格,在編輯欄公式的後方單擊,可以看到公式後方有游標閃爍。

然後按ctrl+enter,可以看到如下所示的結果。

04

當前表格中所有的日期都顯示成了數字,這是excel中日期原始的樣子。按住ctrl鍵,選中所有日期,然後設定格式為“短日期”。

此時所有日期都正常顯示了。

05

我們可以嘗試改變工號,可以看到下面的詳細信息都會隨之改變。當輸入工號002時,可以看到下方有些信息顯示為0,表示該項信息在員工登記表里是空單元格。

點擊“檔案”-“選項”-“高級”,去掉勾選“在具有零值的單元格中顯示零”。

點擊“確定”後,可以看到若查找到的單元格為空,則返回空單元格。

06

當輸入一個不存在的工號時,所有單元格都會顯示錯誤信息。

我們可以在公式前增加一個IFERROR函式做容錯處理。

選中D4單元格,將公式改為:

=IFERROR(VLOOKUP($D$3,員工登記表!$A$1:$R$11,MATCH(C4,員工登記表!$A$1:$R$1,0),0),"")。回車後再按照前面相同的方式將公式填充至其他單元格。此時可以看到,由於不存在工號013的員工,所以表格都為空。

07

下面我們再來進行照片的動態設定。

選中“照片”單元格,點擊“公式”-“定義名稱”。

在彈出的對話框中輸入公式:

=INDEX(員工登記表!$D:$D,MATCH(員工信息查詢表!$D$3,員工登記表!$A:$A,0)),命名為“照片”,點擊確定。

公式解析:

1.MATCH(員工信息查詢表!$D$3,員工登記表!$A:$A,0),用MATCH函式在員工登記表的A列里尋找員工信息查詢表里的D3單元格(也就是我們輸入的工號),並返回行號。

2.INDEX(員工登記表!$D:$D,MATCH(員工信息查詢表!$D$3,員工登記表!$A:$A,0)),用INDEX函式,返回D列中工號所在行的值(工號所在行由MATCH函式得到)。

在excel自定義功能區中找到“照相機”,並添加到“自定義快速訪問工具列”。

這時excel頁面左上方出現了照相機的按鈕。

點擊“照相機”,並在“照片”單元格內拖動滑鼠,劃出一個矩形框。

點擊編輯欄的公式,將公式更改為:

=照片,回車後,可以看到工號對應的照片顯示了出來。

現在,大家可以嘗試改變工號,表格里的信息和照片都會隨之改變喲!

今天的教程就到這裡,大家還想學習什麼excel技能呢?留下在下方,瓶子將會根據大家的留言寫教程!

原創:胡萍/部落窩教育(未經同意,請勿轉載)

相關文章
精选文章