Excel | VLOOKUP完成同一部門多個員工信息查詢

  問題情景

做人事管理的某位朋友,說想要用函數查詢單位不同部門員工信息,隨意輸入單位,所在部門的員工信息會自動查詢,結果如下圖:

Excel | VLOOKUP完成同一部門多個員工信息查詢

上圖中的查詢效果是由函數VLOOKUP做到的,過程如下講解:

影片講解

關鍵操作第一步:建立輔助列

在「部門」前增加一列,在A3單元格輸入公式「=COUNTIF($B$3:B3,$H$3)」。

其中:

$B$3:B3是一個起始位置為B3不變,結束位置隨著公式向下填充而增加的動態區域;

$H$3是要查詢的部門。

結果如下:

Excel | VLOOKUP完成同一部門多個員工信息查詢

假設現在要查詢的部門是「市場2部」,A3處的公式「=COUNTIF($B$3:B3,$H$3)」向下填充的結果是:每遇到一個「市場2部」,數字加1,從而將「市場2部」用不同的序號區分,而且只有「市場2部」出現的行,序號才發生變化,變化方式是加1。

第二步:公式做到

在I3單元格輸入公式「=IFERROR(VLOOKUP(ROW(A1),$A$2:$F$25,COLUMN(C1),0),”)」,向下向右填充,即得查詢結果。

公式解析:

ROW(A1)

公式向下填充時,依次變為ROW(A2)、ROW(A3)、ROW(A4)、……即起始數字為1、步長為1的自然數序列;

COLUMN(C1)

公式向右填充時,依次變為COLUMN(D1)、COLUMN(E1)、COLUMN(F1)、……,即$A$2:$F$25數據區域中的第3、4、5……列;

VLOOKUP(ROW(A1),$A$2:$F$25,COLUMN(C1),0)

VLOOKUP使用起始數字為1、步長為1的自然數序列為查詢值,使用$A$2:$F$25為查詢區域,以精確匹配的方式返回第C、D、E……列的姓名、性別、職稱、本月銷售業績。

VLOOKUP函數默認只能返回第一個滿足條件的記錄,而在自然數序列裡,只有「市場2部」出現的行,序號才發生變化,所以,查出的結果是「市場2部」所有的員工信息。

IFERROR(VLOOKUP(ROW(A1),$A$2:$F$25,COLUMN(C1),0),”)

當ROW函數的結果大於A列中的最大數字時,VLOOKUP會因為查詢不到結果而返回錯誤值#N/A,IFERROR函數屏蔽了VLOOKUP函數返回的錯誤值,使之返回空文本。

附:IFERROR的用用法:

語法:IFERROR(value, value_if_error)

中文語法:IFERROR(查找正確時返回值, 查詢出現錯誤返回值)

第三步:隱藏輔助列

隱藏輔助列A列,最簡單的方法是直接把滑鼠放在A與B列之間,直接向左拖動,一直到隱藏;如果是取消隱藏,把滑鼠放在B列左側,直接拖出。如下動圖:

Excel | VLOOKUP完成同一部門多個員工信息查詢

素材下載

鏈接:http://pan.baidu.com/s/1qYDdn6c

密碼:vu1e