超讚的Excel動態查詢表,太多人想學!

2021-02-24 excel教程

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技能呢？留下在下方，瓶子將會根據大家的留言寫教程！

表格中圖片來源於網絡，侵權請聯繫刪除！

如您因工作需要學習Excel，不妨掃下面二維碼，訂閱滴答老師的《一周Excel直通車》視頻課程，包含Excel技巧、函數公式、數據透視表、圖表。永久學習，不限時間，不限次數，配套練習課件，伴隨輔導答疑。

 

爆文推薦：

《17集excel函數公式，滴答老師主講！》

《萌新小白快速進步的42集excel教程》

公號轉載請聯繫授權！

哎呀！聽說「閱讀原文」可以學到更多知識

可是我點不到啊。。。。。。

相關焦點

  • excel函數應用技巧：這個銷售統計模板，能動態變色查詢
    今天向大家分享一個銷售統計表模板。該模板支持動態查詢功能，並且在查詢的時候，相應數據會變色，如此，查詢結果一目了然。統計模板將使用SUM、AND、COLUMN、MATCH、OFFSET函數並結合條件格式和數據驗證。學習更多技巧，請收藏關注部落窩教育excel圖文教程。
  • excel圖片處理：利用PS做帶照片的動態查詢系統 下
    本篇為《excel圖片處理：利用PS做帶照片的動態查詢系統》的下篇。本篇我們就做出查詢表。查詢表的最終效果如下：第三步：完成動態查詢系統我們根據上一步驟完成的帶有圖片的數據源，做一個動態查詢檔案，輸入姓名即可查詢到照片、性別、出生日期等
  • Excel教程:手把手教你製作excel動態交叉查詢
    支持微信公眾號+小程序+APP+PC網站多平臺學習用excel製作動態查詢表，你會嗎？看完上圖，心中是否有疑惑，查詢的公式是怎麼寫的，下拉列表是怎麼出來的，結果高亮顯示又是怎麼製作的？下面就這幾個問題一一解惑！交叉查詢的公式很多，今天介紹兩種，一種是使用函數INDEX+MATCH組合，另一種是使用函數VLOOKUP+MATCH組合。
  • excel圖表技巧：切片器加透視表製作動態圖表
    小姐姐告訴我，她當時剛從學校出來找工作，自己啥也不會，就只能面試行政崗位，然而工資才兩千多的公司都不願意要她，被拒11次，簡直懷疑人生~甚至有一家公司面試當場讓她做一個動態圖表，而她完全不會，不用說，還是沒面試上，回家後她便拼命學習excel，後來找工作也越來越順利。小編回到辦公室趕緊給大家準備了一篇Excel圖表教程。記著動手操作喲！
  • excel查詢技巧：如何用數據透視表進行一對多查詢
    Excel一對多查詢，你能夠想到用什麼函數？excel一對多查詢不僅可以使用函數公式，還可以數據透視表。我承認我只是一個普通人或者是懶人，儘管高手的方法很多，但我只衷情於數據透視表進行一對多查詢，因為它快並且不用動腦筋！有這樣一份Excel一對多的查詢案例，需要返回對應的多個值。B、C列是數據源，根據E2的部分在F列返回對應的部門成員。
  • 怎樣在EXCEL中製作動態圖表
    之前小白通為大家介紹過如何製作動態排班表，我們可以用Vlookup函數製作動態圖表，讓圖表內容根據我們選擇的項目而變化。01製作動態表（1）製作下拉菜單新複製一個表頭，選擇姓名下方的空白單元格，再選擇「數據-數據驗證」，在「驗證條件」中選擇「序列」，「來源」選擇原表的人名。
  • 如何用EXCEL表實現倉庫出入庫和庫存動態管理
    對於常用的辦公軟體excel表格是一個比較實用和方便的管理軟體、它能夠進行一些代碼編寫操作進行倉庫出入庫管理、但是庫存動態管理需要人為的手動輸入庫存進和出的數量然後軟體會自行結算出庫存量的結果。但是現在的動態管理需要做到實時多人共享和協同管理，和一些移動性的操作就需要藉助手機來完成。其中完成的比例時間和效率可以增倍的增加。所以我們可以借用手機上的掃碼和提醒功能、包括加好友功能實美化PC端的excel表的進出倉庫管理。那麼如何實現呢只要打開微信發現小程序搜索庫存表、打開就可以實現了。
  • excel操作技巧：幾個實現數據透視表動態刷新的方法
    不如先從學一些避免加班的技巧開始吧！數據透視表是EXCEL中常用的技能，它能幫助我們快速統計分析大量數據。並且隨著布局的改變，數據透視表會立即按照新的布置重新計算數據，在日常工作中非常實用。但是數據源如果有新增，數據透視表是無法同步更新的。那今天就給大家介紹幾個實現數據透視表動態刷新的方法。如圖，這個數據源列出了不同城市的銷售額。
  • excel動態目錄文件設置步驟介紹
    excel常常用來存儲數據資料，便於用戶查看、編輯，其實大家還可以為將這些文件製作成一個動態目錄，這樣想找目標文件時直接點擊連結就能跳轉到文件夾，想知道操作方法就接著往下看吧。excel創建動態目錄文件教程 1、首先我們點擊數據選項卡下的 新建查詢 ，選擇從文件，點擊 從文件夾
  • 用Excel製作動態查詢信息系統
    通過準備數據源以及查詢表格兩個步驟我們準備好了圖片並批量導入了數據源表中。本篇我們就做出查詢表。查詢表的最終效果如下：動態查詢系統我們根據上一步驟完成的帶有圖片的數據源，做一個動態查詢檔案，輸入姓名即可查詢到照片、性別、出生日期等。做好了之後是這樣的：怎麼操作呢？
  • 擺脫了Excel重複做表,換個工具輕鬆實現報表自動化,漲薪三倍
    更令人恐懼的是每天的日報，定期的周報、月報，要是遇上季末，周報、日報、月報、季報全撞一起，簡直做表做到想自殺，辛辛苦苦加班趕出來的表，老闆也不一定看就這樣，經歷了噩夢般的幾個月……終於在有一天，我知道了一個詞：報表自動化什麼是自動化報表？
  • excel篩選技巧：如何做一個動態篩選自動化圖表？
    目標樣式：現在我們需要利用excel，將上表做成動態可篩選的模式，變成一個可匯報的動態數據表，數據表內容會隨被篩選欄位的變化而變化，如下圖所示：*本著讓大家深入理解的精神，給大家解釋一下，這幾個套路的形成思路（同類問題均可按此方法進行分析）：1、「動態篩選」，可以拆分為：動態+篩選2、涉及篩選部分，需要想到excel具有「篩選」功能的操作或按鍵有哪些：（1）excel自帶的篩選（2）數據透視表本身的篩選功能，外加切片器（3）數據有效性
  • excel篩選技巧:如何做一個動態篩選自動化圖表?
    目標樣式：現在我們需要利用excel，將上表做成動態可篩選的模式，變成一個可匯報的動態數據表，數據表內容會隨被篩選欄位的變化而變化，如下圖所示：分析思路：在動手操作之前呢，我們先來分析一下「*本著讓大家深入理解的精神，給大家解釋一下，這幾個套路的形成思路（同類問題均可按此方法進行分析）：1、「動態篩選」，可以拆分為：動態+篩選2、涉及篩選部分，需要想到excel具有「篩選」功能的操作或按鍵有哪些：（1）excel自帶的篩選
  • Excel動態圖表|讓你的圖表動起來,一個控制項選擇多樣化圖表
    俗話說字不如表、表不如圖、圖不如自動化。每天學習一個圖標的小技巧，畢竟咱們公司領導口味多變，讓你的excel圖表動起來~使用控制項選擇多樣化圖表1、點擊開發工具——選項按鈕（窗體控制項）——繪製一個選項按鈕，更改名稱——按住CTRL+SHIFT向下複製3個2、選擇第一個選項按鈕設置控制項格式——連結單元格——選擇G1
  • excel動態圖表：一看就會的動態圖表入門篇
    盟主接過ipad見一張excel表，喜上眉梢，洋洋得意。修煉excel之道分為內功和外功，今天，老衲就帶領大家修煉內功心法之入門篇——動態圖表最簡單的效果製作。動態圖表第一式：製作下拉菜單第一式本質上是外功修煉的內容，所以說內功修煉要有一定的外功修煉基礎，內外結合，方可練就上稱功力。
  • excel動態圖表:一看就會的動態圖表入門篇
    盟主接過ipad見一張excel表，喜上眉梢，洋洋得意。修煉excel之道分為內功和外功，今天，老衲就帶領大家修煉內功心法之入門篇——動態圖表最簡單的效果製作。動態圖表第一式：製作下拉菜單第一式本質上是外功修煉的內容，所以說內功修煉要有一定的外功修煉基礎，內外結合，方可練就上稱功力。
  • 學EXCEL 必學的 index+match 查詢函數
    在EXCEL的查詢函數裡 ，VLOOKUP 和INDEX,MATCH 函數是最重要的查詢函數，也是我們平時在人力資源的數據處理中用的做多的函數，在我們做一些人員的數據分析和動態圖表的設計的時候都會用到這些函數，可以提升我們的數據處理的效率，今天我們就來講講這幾個查詢函數。
  • Excel多表合併:學會這招從此事半功倍
    大傢伙我是雨果，在講之前希望大家做好心理準備，這節課比較長，因為我想儘量講的詳細一點，希望大家能耐心的看下去，因為今天要講的內容真的是非常重要，如果大家如果能耐心的看完，用心去體會的話，我保證大家真的會受益匪淺。今天跟大家分享的是Excel多表合併，這節課非常重要，因為多表合併在工作中是會經常用到的。
  • excel圖片處理：利用PS做帶照片的動態查詢系統 上
    編按：教程結合PS（Photoshop）軟體和Excel軟體，教大家製作一套包含圖片的動態查詢系統。這套系統關鍵只有三步：一、保持圖片不變形的情況下批量統一圖片大小；二、批量的插入圖片；三、圖片的動態查詢。教程非常詳細，即使不懂PS，也完全可以做出圖片不變形的查詢系統。教程比較長，將分成上下兩篇，本篇為上篇。——本文第一步的圖片處理就是上次發表的《ps實用技能：如何統一裁剪圖片大小》，如果已閱讀了解，可直接跳到第二步。
  • Excel如何製作動態圖表效果?
    excel如何製作動態圖表效果？動態圖表效果指的是我們在進行數據篩選的時候，它的圖表就會跟著我們的需要進行變化，方便我們進行統計數據，下面就來給大家操作一下，如何通過數據透視表製作動態圖表效果，非常簡單。