Hello大家好, 我是幫幫。 最近好多小夥伴在微信中問我, 如何運用vlookup函數, 今天結合範例, 我給大家講講日常工作容易遇到的問題, 一看就會(已經是高手的可以跳過^^)。
有個好消息!為了方便大家更快的掌握技巧, 尋找捷徑。 請大家點擊文章末尾的“瞭解更多”, 在裡面找到並關注我, 裡面有海量各類範本素材免費下載, 我等著你噢^^<——非常重要!!!
大家請看範例圖片, 先講講vlookup函數的語法。 他有四個參數, 四個參數分別代表對應的類別, 最後的參數一般輸入0。
這樣看起來或許有點空洞, 我們直接看應用。 我們現在要查詢小張的工號是多少,
這樣就輕鬆查詢出我們想要的結果, 通過複製向下公式, 小紅, 小季批量完成。
然而, 我們初學者最容易犯錯的地方, 類似下圖, 查找小張的部門, 輸入公式=VLOOKUP(E10,D:E,-1,0), 查找的列在查找值的左側, 有些小夥伴按照順序以為輸入-1就能查到, 結果查詢不到結果。
結論:VLOOKUP第一個參數, 也就是需要查找的值, 必須在查找範圍的首列。
我們再來看查詢聘用時間是2014年的人員名單。 我們先輸入公式看看, =VLOOKUP(E11,C:E,3,0)。 這裡需要查找的值, 運用了萬用字元, 2014*代表2014後面跟隨的任意字串。 在C到E列中查詢, 取第3列的資料。
我們複製公式向下, 結果第二個儲存格出現查詢不到結果。
這時候, 我們就要對E列11行進行鎖定, 輸入E$11, 無論你向下如何複製公式, 由於11行被鎖定,
調整公式後, 我們向下複製, 新的問題出來了, VLOOKUP函數僅對目的地區域第一次符合要求的資料進行查詢, 但是複合2014*的字串顯然很多。 VLOOKUP就沒辦法批量提取。
要想用VLOOKUP批量提取,也很簡單,添加一個輔助列。輸入=COUNTIF(D$3:D3,F$11),這裡按2014*為條件,以D3為座標,向下計數,符合條件的進行計數。大家看,本例中,有5個儲存格符合2014*的條件。
然後我們在結果區域,輸入公式=IFERROR(VLOOKUP(ROW(A1),A:F,6,0),"")。這個公式表示A到F列的區域裡面,符合ROW(1)=1的條件,進行查詢,然後生成第6列的值,查詢不到結果返回空格。
這樣小李的結果就出來了。我們下拉公式,一直複製到沒有資料出現,產生空白儲存格為止。
下面這種情況在工作中也非常容易遇到,填寫各種報名表,上崗表,推薦表,申請表。如果你一次需要填寫多份表格,每個空格複製粘貼顯然不科學。我教你如何一鍵生成。
做之前我們先要確定一個查找值,即唯一值(不重複),本例中利用工號進行查詢。
我們先將母表中的工號列剪切,移動到首列。
然後如果母表列數特別多的小夥伴,建議像我這樣,在首行插入空白行,填充一個橫向的序號(後面方便填寫參數)。
在唯一值處,先輸入一個工號(某些表格如果實在無法選取唯一值,你可以在表格列印範圍外,選取一個儲存格,輸入1,就以母表的序號作為唯一值進行查詢)。
接下來將母表和需要填寫的表格同時打開,在姓名的空格處,輸入公式,=VLOOKUP($B$3,[資訊母表.xlsx]Sheet1!$B:$U,6,0),注意引用區域的絕對引用,加上$符號,否則複製公式,函數增量又要發生查詢錯誤。
公式含義,表示以永遠以B3的數值進行查詢,查詢範圍永遠是母表中的B列到U列,大家看,我設置的橫向序號起作用了,姓名在第6列,輸入6,然後0,回車搞定。
將公式複製到性別列,看一眼母表中,性別在第7列,我們只需要將參數6改為7即可。
同理,將所有的表格按照橫向序號,對應修改之後,我們只要改動任意工號,所有的表格資料自動生成,直接點擊打印即可,非常省事方便。
今天的教學針對函數初學者,希望大家能看明白。再次強調母表(資料來源)的重要性。母表科學登記,所有的表格都可以查找引用,只需微調資料,對付批量表格,簡單快捷。
好了,今天的技巧分享就到這裡,拜拜,下課-。-
要想用VLOOKUP批量提取,也很簡單,添加一個輔助列。輸入=COUNTIF(D$3:D3,F$11),這裡按2014*為條件,以D3為座標,向下計數,符合條件的進行計數。大家看,本例中,有5個儲存格符合2014*的條件。
然後我們在結果區域,輸入公式=IFERROR(VLOOKUP(ROW(A1),A:F,6,0),"")。這個公式表示A到F列的區域裡面,符合ROW(1)=1的條件,進行查詢,然後生成第6列的值,查詢不到結果返回空格。
這樣小李的結果就出來了。我們下拉公式,一直複製到沒有資料出現,產生空白儲存格為止。
下面這種情況在工作中也非常容易遇到,填寫各種報名表,上崗表,推薦表,申請表。如果你一次需要填寫多份表格,每個空格複製粘貼顯然不科學。我教你如何一鍵生成。
做之前我們先要確定一個查找值,即唯一值(不重複),本例中利用工號進行查詢。
我們先將母表中的工號列剪切,移動到首列。
然後如果母表列數特別多的小夥伴,建議像我這樣,在首行插入空白行,填充一個橫向的序號(後面方便填寫參數)。
在唯一值處,先輸入一個工號(某些表格如果實在無法選取唯一值,你可以在表格列印範圍外,選取一個儲存格,輸入1,就以母表的序號作為唯一值進行查詢)。
接下來將母表和需要填寫的表格同時打開,在姓名的空格處,輸入公式,=VLOOKUP($B$3,[資訊母表.xlsx]Sheet1!$B:$U,6,0),注意引用區域的絕對引用,加上$符號,否則複製公式,函數增量又要發生查詢錯誤。
公式含義,表示以永遠以B3的數值進行查詢,查詢範圍永遠是母表中的B列到U列,大家看,我設置的橫向序號起作用了,姓名在第6列,輸入6,然後0,回車搞定。
將公式複製到性別列,看一眼母表中,性別在第7列,我們只需要將參數6改為7即可。
同理,將所有的表格按照橫向序號,對應修改之後,我們只要改動任意工號,所有的表格資料自動生成,直接點擊打印即可,非常省事方便。
今天的教學針對函數初學者,希望大家能看明白。再次強調母表(資料來源)的重要性。母表科學登記,所有的表格都可以查找引用,只需微調資料,對付批量表格,簡單快捷。
好了,今天的技巧分享就到這裡,拜拜,下課-。-