2013年2月10日 星期日

[ Office 教學 ] Excel 中相當常用的參照函數「VLOOKUP」,幫助你自動將相關資料回填正確的欄位!



不論是財務分析、資料歸檔還是客戶管理,檢視與參照類型的函數都是相當常見且實用的功能,你可以想像在龐大的資料欄位中要填寫幾近相同卻又不同的數筆資訊是多麼頭痛的事情,但 Excel 中的 VLOOKUP 函數可以幫你快速解決這個問題,今天就讓 Boring 透過範例讓你一次就上手!

Office2007Logo_thumb2_thumb2_thumb2_[1]

今日課程: Excel 檢視與參照的功能函數
課程內容: 透過 VLOOKUP 函數功能協助回填資料
備註說明: 可以利用它來回寫薪資、成本、售價、客戶層級等等資訊
 

※ VLOOKUP 的定義

所謂的 VLOOKUP 函數的定義是:在固定的選取範圍中,依照其左方欄位為搜尋對象,然後回傳到指定陣列中某一欄位的值。

※ VLOOKUP 教學範例

首先來看一下今天的範例,這是一份人資的資料表,裡頭有同仁的姓名、職稱、職等、年資和薪資,我們先來想像一下這份資料相當龐大。另一個工作表是職等薪資表,其中每一個職等的相對薪資都是固定的值,如果要逐步回填是很麻煩的,這時候就可以靠 VLOOKUP 函數來幫忙了。(範例僅是隨便打打,如有雷同,純屬巧合 XD)

圖片 1

※ VLOOKUP 教學開始

首先,在你要使用 VLOOKUP 的爛位上插入函數,以這個範例來說,就是薪資空白欄位的位置,選擇插入函數後從類別裡找到檢視與參照

圖片 6

接著再從檢視與參照類型的函數中找到我們今天的主角:VLOOKUP!

圖片 5

這個函數的使用方式很簡單,第一個欄位要帶入需要參照的資訊,第二個則是要參照的範圍,第三個是要回填的值,最後則是選擇絕對參照還是模糊參照!可以直接參考下圖的函數表示以及各函數所代表的位置。

注意事項一:參照的範圍如果為固定不動的範圍,記得要寫入絕對參照的 $ 符號
注意事項二:
FALSE 為絕對參照,若找不到絕對的值則 FALSE;
TRUE 則為模糊參照,如果找不到絕對的值則填入最接近的值。

圖片 8

如果看上圖還不夠清楚的話,我們直接開啟函數視窗來了解,第一格是我們要搜尋的值,在這個範例中就是職等了;第二格是職等參照的範圍,在這個範例中就是薪資工作表的職等和薪資範圍;第三格則代表要回傳的欄位,範例中是要回填第 2 欄的資料;最後則是選擇絕對參照還是模糊參照。

圖片 10

當第 2 欄的資料被我們回填之後,後續的資料就只需要簡單下拉把函數都帶入,就會全部回寫完畢,相當輕鬆寫意啊。

圖片 11

今天的 Office 教學就到這邊,有什麼不懂的地方都可以留言詢問, Boring 會盡量為大家解答。

10 則留言 :

  1. 現在的工作 每月都要下載一個檔案 該檔案有五百~九百多筆資料 它的格式如下


    姓名 日期 日期 內容
    A
    B
    C
    D
    E
    F
    ...


    因為下載的檔案沒有提供每一個人的職稱資訊 (例如 檢察官 法警 工友 書記官 科員...)

    而我需要的資料是"檢察官"或"檢事官"的人就好

    所以變成我必須去一筆一筆配對 (檢察官設代碼1 檢事官設代碼2 其它設代碼3)

    姓名 日期 日期 內容
    A
    B
    C
    D
    E
    F
    ...


    因為下載的檔案沒有提供每一個人的職稱資訊 (例如 檢察官 法警 工友 書記官 科員...)

    而我需要的資料是"檢察官"或"檢事官"的人就好

    所以變成我必須去一筆一筆配對 (檢察官設代碼1 檢事官設代碼2 其它設代碼3)

    姓名 日期 日期 內容
    3 A
    1 B
    1 C
    1 D
    2 E
    3 F
    3 G
    1 H
    2 I
    1 J
    3 K
    3 L
    ... ...


    全部有五百到九百多比@@

    等人工一一配對完後 再用篩選功能由小排到大 才能跑出

    姓名 日期 日期 內容

    1 B
    1 C
    1 D
    1 H
    1 J

    2 E
    2 I
    2 A3 F
    3 G
    3 K
    3 L
    ... ...


    想請問原本一一人工配對的地方 也可以用=vlookup() 嗎?
    因為我對=vlookup的定義沒有很清楚@@
    謝謝!

    回覆刪除
  2. 請問我想參照B欄,若B欄沒資料則參照A欄,要怎麼做呢?
    例如:
    A B C D
    1 文
    2 文
    3 文
    ........

    回覆刪除
  3. 大大您好
    不好意思,有問題想請教您這位excel專家,再請您不吝指導,謝謝。
    我有一份資料需進行填寫,每一項目下方有若干的子題,依這子題內容回覆,但受限路徑的要求,所以若選擇路徑二時,並與路徑一相較,則門市牆、門市介紹的項目不填寫,此時欄位是鎖住,無法將資料輸入;反之若選擇路徑一並與路徑二相較,則網路預約的項目不填寫。再請您告知依如上的說明,這樣此表可如何設計??謝謝大大。
    路徑一:電話禮節+門市牆+門市介紹+物件帶看+電話回訪
    路徑二:電話禮節+網路預約+物件帶看+電話回訪
    路徑三:電話禮節+物件帶看+電話回訪
    或是您方便寄email給我twvan1020@yahoo.com.tw,我將檔案寄給您進行設定,謝謝。

    回覆刪除
    回覆
    1. 聽不太懂您的描述,可能無法幫您,抱歉喔。

      刪除
  4. 根據上述例子的資料排列,如果我想在sheet3 A2儲存格輸入"總統",而B2傳入"馬英九",公式要如何編寫?

    回覆刪除
    回覆
    1. 另開一個 sheet 將職務對應的人名放上去,然後參照即可。

      刪除
  5. 作者已經移除這則留言。

    回覆刪除

還有想知道什麼好用的軟體嗎?說出來 Boring 找給你!

Related Posts Plugin for WordPress, Blogger...