VLOOKUP函數在統計工作中的應用
來源:指數網  日期:2019-09-11
    • 分享到QQ空間

VLOOKUP函數作為EXCEL中查找和引用函數中重要的一員,在批量處理數據時可以收到事半功倍的效果。

01 VLOOKUP函數介紹

1.用途:在表格或數值數組中的首列查找指定的數值,并由此返回表格或數組當前行中指定列處的數值。

2.語法:VLOOKUP(查找目標,查找范圍,返回值的列數,精確或模糊查找)

3.參數:

①查找目標為需要在數據表第一列中查找的數值,它可以是數值、引用或文字串;

②查找范圍為需要在其中查找數據的數據表,可以使用對區域或區域名稱的引用;

③返回值的列數為查找范圍中待返回的匹配值的列序號;

④精確或模糊查找為一邏輯值,指明函數VLOOKUP返回是精確匹配還是近似匹配。若為TURE、省略或1,則返回近似匹配值;若為FALSE0,則返回精確匹配值;若找不到,則返回錯誤值#N/A

02經濟普查中運用VLOOKUP函數查找引用數據案例

1.VLOOKUP函數使用過程

全部數據,為經濟普查數據庫(第二張工作表名)

需要查找的數據(第一張工作表名)

待解決問題:我們需要從“圖一”中查找到“圖二”第一列“單位詳細名稱”所對應的街(村)門牌號、行政區、固定電話等信息,并引用顯示在“需要查找的數據”工作表中。

B2單元格中的公式為:

=VLOOKUP($A2,全部數據!$B:$CI,2,0)

解析:$A2為“需要查找的數據”工作表中第一列“單位詳細名稱”;

全部數據!$B:$CI為絕對引用區域,注意和查找目標相對應的“單位詳細名稱列”必須是“全部數據!$B:$C”中第一列的數值;

2返回“全部數據!$B:$C”中第二列的數值,而非“全部數據”(圖一)工作表中的第二列;

0表示精確匹配值。

我們可以把這個公式復制到后面的CD等列,但是需要把數字2手動替換成34等列數。

為了簡化工作量,可以利用COLUMN函數指定單元格的列序號。

COLUMN函數用途為返回給定引用的列標。舉例:

COLUMNA1)、COLUMNA2)、COLUMNA3)……返回值為1,即A列的列序號;

COLUMNB1)、COLUMNB2)、COLUMNB3)……返回值為2,即B列的列序號;

COLUMNC1)、COLUMNC2)、COLUMNC3)……返回值為3,即C列的列序號。

-C2單元格中的公式為:

=VLOOKUP($A2,全部數據!$B:$CI,COLUMN(C2),0)

解析:其中COLUMN(C2)返回“全部數據!$B:$C”中第三列的列序號,相當于數值3

我們直接復制公式到其他單元格,COLUMN函數可實現自動轉換列序號。

2.VLOOKUP返回值錯誤處理

如果在查找范圍的數據表中找不到相應的目標,則VLOOKUP返回#N/A。如下圖五,在“全部數據”中無法找到“金鄉縣公安局第一派出”這一單位詳細名稱,則B5單元格中返回錯誤值#N/A

有時為了方便后期計算或顯示美觀,我們需要用空值或者0”值來代替#N/A。這時,就可以利用錯誤處理函數IF(ISERROR())進行修正,讓#N/A顯示為空值或者“0”,如下圖六中的C5單元格顯示為空值。

單元格C5顯示為空值,其單元格中公式為:

=IF(ISERROR(VLOOKUP($A5,全部數據!$B:$CI,2,0)),"",VLOOKUP($A5,全部數據!$B:$CI,2,0))

若把C5單元格中公式替換為:

=IF(ISERROR(VLOOKUP($A5,全部數據!$B:$CI,2,0)),"0",VLOOKUP($A5,全部數據!$B:$CI,2,0)),則C5就顯示為“0”。

IF函數是一種重要的邏輯運算函數。

用途:執行邏輯判斷,它可以根據邏輯表達式的真假,返回不同的結果,從而執行數值或者公式的條件檢測任務,該函數廣泛用于需要進行邏輯判斷的場合。

ISERROR函數是IS類函數的一種,語法:ISERROR(參數)ISERROR的參數是任意錯誤值(#N/A#VALUE!#REF!#DIV/0!#NUM!#NAME?#NULL!))。

上述C5中公式,若邏輯判斷ISERROR(VLOOKUP($A5,全部數據!$B:$CI,2,0))為真(#N/A,則返回空值,若邏輯判斷ISERROR(VLOOKUP($A5,全部數據!$B:$CI,2,0))為假(非#N/A),則返回依據查找目標在查找范圍中查找到的相應數值。

3.VLOOKUP返回值錯誤的常見原因有兩種:

①沒有查找到目標,上述實例錯誤顯示就是因為沒有查找到目標數據。

②數值格式不同,查找目標和查找范圍中的數值格式不同導致VLOOKUP返回值錯誤。在實際工作中,從兩個不同程序中導出的數據庫,因其數據源不同往往存在數值格式差異,導致VLOOKUP返回值錯誤,這時就需要對查找數據進行“分列”處理。

具體方法:選中需要轉換格式的一列數值,點擊菜單欄“數據”-“分列”,按照分列向導進行“下一步”操作,一般情況為默認,最后點擊“完成”。數值格式轉換為相同格式后,在利用VLOOKUP函數一般就能找到對應目標。

4.VLOOKUP字符的模糊查找

針對上述圖六中返回值錯誤,我們可以進行“包含”查找,找到最有可能的目標。

全部數據中查找多的最相近的單位詳細名稱“金鄉縣公安局第一派出所”。

圖八單元格D5中的公式:

=VLOOKUP("*"&$A5&"*",全部數據!$B:$CI,COLUMN(D5),0)

“金鄉縣公安局第一派出”在“全部數據”工作表中查找到“金鄉縣公安局第一派出所”,并返回相應行的數值。

解析:查找目標"*"&$A5&"*"包含通配符“*”。VLOOKUP函數第一個參數允許使用通配符“*”來表示包含的意思,把*放在字符的兩邊,即“*&字符&*”,其中&是對字符進行連接的意思。

Vlookup函數的確是一種非常強大的查找引用函數,在統計工作中熟練運用能大大提高工作效率,節約時間成本。

    • 分享到QQ空間

上一篇:已經是第一篇

下一篇:數據分析很痛苦?5類問題、8大方法幫到你

海南省体彩兑奖在哪里