-
1 # 大光老師office
-
2 # 愛學習化學課堂
感謝邀請,其實這個很簡單。vlookup函式查詢內容為學號,返回值為班級。先舉個簡單例子,A2為某一學生學號,B2為其對應班級,vlookup函式從左到右查詢,那麼簡單可以輸入為=vlookup($A2,A2:B35,2,0),ctrl+shift+enter組合鍵。如果返回的班級為A列,查詢學號為B列,則從右向左查詢,那麼可以簡單輸入為=vlookup($B2,if({1,0},B2:B35,A2:A35),2,0)同理按下組合鍵。
多條件查詢舉例如下:查詢同時滿足條件B2和D2列的學生班級C2,切查詢內容在sheet2,對應返回值在sheet1,則可輸入=vlookup($B2&$D2,if({1,0},sheet1!$B$2:$B$35&sheet1$D$2:$D$35,sheet1!$C$2:$C$35),2,0)ctrl+shift+enter組合鍵。
晚上回答沒開電腦,望見諒。
-
3 # Excel樂客
=VLOOKUP(匹配值,範圍-以匹配值為開頭第一列的一個範圍,班級所在的列數,0-精確匹配)
如果A列為姓名 ,H列為班級的話 那麼公式為:=VLOOKUP(張三,A:H,8,0)
-
4 # 傲看今朝
如何利用Vlookup函式獲取學號中的班級資訊。換言之,咱們源資料中放著姓名性別學號班級等資訊,而在另一張表格中一定有學號資訊,但其他資訊就未必有,這需要我們將缺失的資訊自動同步過去。使用vlookup函式的確非常簡單,今天我就再次來剖析一下這個函式吧。
一、利用Vlookup函式返回班級名稱如下圖所示,A表為源資料表,B表則是需要填入缺失班級資訊的表格。我們如何快速完成這個同步呢?
上圖的情形是非常簡單的,利用基本的vlookup函式就可以搞定了。vlookup函式共計4個引數:
=vlookup(lookup_value,Table_array,col_num,type)A表和B表首列都是學號資訊,因此我們可以透過利用B表中的學號(lookup_value)到A表(table_array)中進行查詢,然後返回A表中從左右往右數第4列的資訊(col_num),z只有B表中的學號在A表中存在時,才返回正確的結果(為0,即精確匹配)。因此正確的公式寫法為:
=VLOOKUP(H4,$A$4:$D$15,4,0)因為公式需要向下複製,而查詢的範圍永遠都是A表,因此我們在列號和行號加美元($)將其固定住(絕對引用)。
按照這個思路分析,其實本公式還有其他寫法,你看出來了嗎?
參考答案:=VLOOKUP(I4,$B$4:$D$15,3,0)
二、利用vlookup返回多列資料如下圖所示,A表為資料來源表,C表則為需要填入資訊的表格,我們如何將A表中的內容快速同步到C表中呢?
透過前面的例子的講解,我相信完全可以透過vlookup函式做到了,只是做法可能會比較笨,演示如下:
需要同步三列資訊,因此寫了3條公式,確實夠慢的:
=VLOOKUP(A22,$A$4:$D$15,2,0)=VLOOKUP(A22,$A$4:$D$15,3,0)=VLOOKUP(A22,$A$4:$D$15,4,0)那麼我們可以一條就可以完成所有資訊的同步嗎?答案當然是可以的。透過觀察上述三條公式我們可以發現,三條公式唯一的不同就在於第3引數的不同,如果能利用其它函式得到2,3,4這3個數字,這個公式不就簡化了。因為公式是要向右複製的,因此,我們可以利用column函式來辦到,由於數字是從2開始的,因此我們得到:
=vlookup(A22,$A$4:$D$15,column(b1),0)
然而當我們向右拖動的公式,會發現除了姓名列得到正確的結果,其他列的內容均出現了#NA,這是為什麼呢?
=vlookup(B22,$A$4:$D$15,column(C1),0)
第1引數變成了B22,我們希望的是公式複製到C2單元格,第1引數依然是A22,因此我們需要在列號前加一個美元符號($),這樣當公式向右複製時就不再出錯了。最終的公式如下:
=VLOOKUP($A22,$A$4:$D$15,COLUMN(B1),0)坦白說,前面這種情況算是簡單的,因為A表和C表的列的順序是完全一致的,但假如順序不一致的,使用上述公式顯然就會出錯了,那麼遇到這種情況下,我們該怎麼辦呢?
我們需要找一個函式來代替column函式。那麼這個函式就是match函式(這個函式限於篇幅,我暫不做介紹),演示如下:
=VLOOKUP($A22,$A$4:$D$15,MATCH(B$21,$A$3:$D$3,0),0)大家可以仔細琢磨一下這個公式。
三、利用vlookup函式靈活返回多列資料如下圖所示,如何將A表的資料同步到D表中,也許很多同學會說非常簡單。不就是利用vlookup函式來做嗎?
公式沒有問題啊,為什麼會出錯呢?這是因為vlookup函式要求lookup_value在源資料表中只能位於最左側,而且我們返回的列序號是以lookup_value為起點從左往右數的。那麼遇到D表這種情況我們該如何辦呢?我們需要利用函式將table_array中的lookup_value列調整至最左側。最終公式如下:
{=VLOOKUP(I22,IF({1,0},$B$4:$B$15,$A$4:$A$15),2,0)}我們利用if函式將第2引數中的lookup_value列即學號列調整至了最左側,然後也就達到了目的。但要注意的是,此公式為陣列公式,因此輸入完公式後,我們需要按住Ctrl+Shift+Enter組合鍵完成陣列公式的錄入,外層的{}可不是直接輸入的哦。
回覆列表
通常情況下,提取學號中的班級資訊是不用vlookup函式的,而是用字串擷取結合if函式,或者是使用lookup函式。既然題主問到了在這裡都說一下。
假定下表中學號的中間兩位代表著班級資訊。需要在班級列填充“一班”,“二班”,“三班”
一、使用vlookup函式對照學號中的班級資訊使用vlookup函式獲取班級資訊需要建立輔助對照表,及01對應一班,02對應二班,03對應三班,如下圖所示:當然輔助表的位置可以自由設定。
這時我們在C2單元格,插入vlookup函式,第一個引數為學號的中間兩位即mid(A2,3,4),第二個引數選擇輔助表區域的絕對引用,第三個引數填寫2,第四個引數填寫0.
這裡需要注意的是:
第一、第一個引數,要用來對照的值,不是A2本身,而是A2的第三位和第四位,需要用mid函式獲取。
第二、vlooukup函式的第二個區域一定是絕對引用的。
二、使用MID函式結合IF完成操作這裡有一個非常關鍵的是,學號的中間兩位是班級,MID函式是跑不掉的,包括上面講的vlookup和下面要講的lookup都需要,這裡就不再多說了,如果不熟悉MID可以關注我,看文章。
這裡需要注意的是:
第一、所有標點符號都需要在英文狀態下進行輸入。
第二、函式的文字需要用雙引號引起,比如:01如果用雙引號引起則代表文字01,否則是數字1.
第三、本例經觀察只有三個班級,所以最後判斷完非一非二,沒有繼續判斷,直接返回三班,如果五班六班需要繼續判斷。
三、使用lookup從學號中獲取班級這裡需要注意的是:
第一、引數二和引數三的序列需要用花括號{}括起來,序列中元素之間用逗號隔開。
第二、引數二和引數三中的序列須一一對應。