回覆列表
  • 1 # 大光老師office

    通常情況下,提取學號中的班級資訊是不用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從學號中獲取班級

    這裡需要注意的是:

    第一、引數二和引數三的序列需要用花括號{}括起來,序列中元素之間用逗號隔開。

    第二、引數二和引數三中的序列須一一對應。

  • 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組合鍵完成陣列公式的錄入,外層的{}可不是直接輸入的哦。

  • 中秋節和大豐收的關聯?
  • 如何評價騎士最後時刻和76人的衝突?