[公告] 痞客豐年終!萬元禮券限量送~[公告] 第一屆痞客邦金點賞登場!2014年最有影響力的部落格即將揭曉[公告] 痞客邦新服務上線 每日星座運勢測算【得獎名單公佈】[公告] 痞客邦應用市集全新改版![公告] 痞客邦「應用市集」新 App 上架-iFontCloud Professional

前一陣子~~工作上的需要~~突然對EXCEL大感興趣了起來!!

那時候需要用EXCEL來協助我~~比對資料...

所以就順便分享給大家囉!!!

當然也得感謝~~培養皿、一整天.......不吝嗇給予參考~~~

 

我想給個人都需要比對資料嗎??

但是如果以人工的方式~~copy& paste& cut 真的是超級麻煩~~

而且萬一自己眼花~~~頭暈或者一不留神...

慘念就會發生~~~(泣.....)

這時VLOOKUP這個函數就是大家的好幫手~~

 

我以工作上面的資料分享給大家吧!!

 

比如說學校學生人數資料裡,

SHEET2裡面的A欄為學校名稱,B欄為學生人數,

但是學生人數資料卻在SHEET1裡面,而且學校名稱排序要毫無規則(非常凌亂),

如果資料少的話~~當然人工比對填一下就可以啦!!

不過資料一多,花人工比對耗時又傷眼,這時EXCEL小天使將協助你完成比對程序囉!!

 

STEP1如下圖所示:SHEET2裡只有A欄位的資料,B欄位的資料為待填數字

而且SHEET1裡的資料順序和SHEET2完全不一樣,所以無法以【剪下】【貼上】就完成

 step1.jpg 

 STEP2這時候就可以用VLOOKUP協助,

點選B3欄位(即希望EXCEL自動被填上資料的空格) 然後插入 vlookup 的函數,如下圖示:

(是VLOOKUP不是VLOOPUP~~懶的改囉!請大家將就將就)

step2.jpg 

 

STEP3vlookup函數裡面的

第一個欄位( Lookup_value )表示你希望和sheet1 (參照表格) 比對的條件值,在此填上A3 欄位。

簡單來說:我們要找後埔國小的一年級學生人數,所以要填上『後埔國小』的A3欄位(who)

(你要找誰??要找什麼??)

 第二個欄位(Table_array)則是表示參照表格(即為你要找的資料範圍)在哪裡,範圍有多大, 

所以跳到sheet 1 的頁面,然後選取 A3 B11 的欄位  (也可直接選B欄全部

條件就是要從sheet1的A3~B11找出後埔國小的1年級學生人數,所以(Table_array)為搜尋資料範圍(where)

就是說~要從哪裡找出後埔國小的學生人數

→→不可以直接選B欄而已喔!!因為EXCEL無法直接由B欄判定出哪一個數值為後埔國小學生人數,

→→必須先從A欄找出後埔國小,在對照至B欄找到學生人數

→→所以要選【A3:B11】

→→也可以選【A:B】

 

(小祕訣:你要從哪裡搜找到這個人或數值??ex:我要在台北找某某某而非高雄找??

 

step3.jpg  

 

 

STEP4

回到 sheet 2活頁

第三個欄位 ( Col_index_num ) 是表示比對到資料之後,要傳回參照表格(參照範圍)內的哪一欄的數值,

這裡填上,表示要傳回從左邊數來的第2欄資料

(你要找的資料是在選擇範圍內的第幾欄???可要算好唷!!不然可是會回傳錯誤)

不過~你在選~~範圍時就可以順便注意~是第幾欄囉~~~excel會顯示

第四個欄位 ( Range_lookup ) 表示比對資料時是否不要精確比對,我們這邊填上0 (表示FALSE),告訴函數需要精確比對資料, 不填或填上非 0 的數字的時候即表示 true

(這裡應該就是是非問題了吧??其實一直不知道該如何解釋啦→我都填0)

STEP4.jpg 

STEP5

按下「確認」的按鈕之後,函數就會自動比對出符合的資料,然後回傳第二欄的數值 577

在這一個步驟我們需要將右上角公式裡面的從  A3:B11改成如下圖所示的$A$3:$B:$11

加上錢字符號則是表示在複製公式的時候不要自動遞增數值,*(lock住) 

因為要尋找的範圍是固定的,所以需要加上$$符號

 STEP.jpg

STEP6

將滑鼠游標移到B3欄位的右下角直到看到一個十字的符號就表示可以複製公式了,按住往下拖曳到 B11的欄位

 

未命名.jpg 

STEP7

透過 EXCEL   VLOOKUP 函數 ,可以迅速的比對出資料,然後回傳我們想要看到的數值 , 這樣就省去了過去用人工比對資料的麻煩囉!!

小秘訣:覺得輸入搜尋範圍(Table_array)很麻煩!或搜尋範圍很廣~~絕對要使用$A$:$B$即可,輕鬆又省力

52999.jpg  

 呼~~~終於完成啦!!

 

 

 


後傳:

這篇文章是集結參考許多格友及書籍+個人演練後的使用心得~~~如有冒犯到原著作的地方真的非常抱歉~~~

 

 

 

 EXCEL小秘笈:四捨五入無條件進位函數條件

Posted by FE at 痞客邦 PIXNET 留言(78) 引用(2) 人氣()


open trackbacks list Trackbacks (2)

  • EXCEL小技巧

    <p>本文引用自<a href="http://felin0630.pixnet.net/blog/post/24888627">felin0630 - EXCEL資料比對小技巧(excel函數-VL
  • VLOOKUP

    <p>本文引用自<a href="http://felin0630.pixnet.net/blog/post/24888627">felin0630 - EXCEL資料比對小技巧(excel函數-VL

留言列表 (78)

Post Comment
  • eileen
  • 太感謝您辣

    一直對於此功能有困擾 謝謝你的提供 受益不潛 謝謝你
  • Private Comment
  • 金金
  • 很感謝您唷!

    謝謝您的詳細解說,我終於了解這ㄍ函數ㄉ作用囉!再次感謝您囉~
  • BLUEMARINE
  • 真的很感謝!!很詳細清楚!!!學會了!!

    真的很感謝!很詳細讓人很清楚的了解~
  • lord
  • 幫你點廣告,外加推一個喔,清晰且詳盡
  • atsww
  • 講解的好清楚,又學到一個涵數技巧了
    謝謝你的辛苦分享
  • 陳小寶
  • 收穫良多

    你好,我在海軍工作,需要比對資料,有一天看到學長運用vlookup,順利地在短時間內將資料比對出來,另我好羨慕,隨即我到書店查找相關的書籍,實在是"看無",直到上網搜尋到貴網站的教學,真是淺顯易懂,好厲害喔~~真是謝謝啦!
  • 能夠幫上忙我很開心~~~

    FE replied in 2009/12/24 14:37

  • 遊客
  • 是 VLOOKUP 還是 VLOOPUP????
    在 step 7 中 淺藍色框框中應該是 $B$11 不是 $8$11
    抱欺想說讓人看得更仔細,寫得很好,謝謝
  • 3Q3Q!!

    FE replied in 2009/12/30 21:54

  • 遊客
  • 這個真的是很實用的技巧~

    之前一直想要知道怎麼使用~

    不然每次都手動實在是費時又容易出錯~

    真的是很棒的教學~謝謝!
  • 喬斯
  • 請教一下,VLOOKUP只能回傳一個值 , 若符合條件的記錄超過一筆以上,如何讓他們都能顯示出來 (字串或數值相加)
  • momo
  • 請問在操作時,比對的資料如是產品編號,表1產品編號欄儲存格顯示是文字,表2儲存格顯示G/通用格式,就無法比對,該怎麼辦?又若表2的產品編號在表1沒有,為何都比對出來的值是表1最後1筆資料?煩請解答!謝謝
  • 親愛的~
    儲存格的格式應該不會影響參照結果喔!!
    你方便e-mail檔案給我嗎??
    看看問題在哪啦!!
    (我也不是專業啦~~都是用我個人的實務分享給大家ㄉ)

    FE replied in 2010/03/11 21:37

  • PYLing
  • 版主 感謝提供
  • 小征
  • 請問我也有些問題,我可以mail資料給您看一下嗎?
  • 親愛的:當然可以囉!!歡迎mail到vionet0630@yahoo.com.tw,
    主旨:excel函數問題(小征),

    FE replied in 2010/03/24 22:55

  • yimi
  • 很詳細的教學!謝謝您~
    我是想用輸入商品名稱就可以回傳價錢,
    用這個很方便呢 :)
  • 希望有幫上妳的忙,我也是一路摸索過來的!!

    FE replied in 2010/04/16 23:20

  • 小零
  • 佩服

    光是整理那些圖文資料所費的時間和精力,就很讓人佩服了
  • 哈哈!!的確..整理超久的..

    FE replied in 2010/05/19 09:50

  • fishwife6688
  • 太感謝您了~超清楚又非常實用~

    謝謝大大的幫忙
  • 希望真的有幫到你喔!!

    FE replied in 2010/06/10 09:07

  • tkbtutor
  • 雖然以前在學校學過,不過因為太久不需要用到都忘的差不多了....今天的複習實在是太清楚了..記憶一下都回來了!!感謝分享!!
  • excel真的需要練習~~~我也是工作才慢慢的喚起學生時的記憶~~

    FE replied in 2010/06/10 20:44

  • d700days
  • "這篇文章是集結參考許多格友及書籍+個人演練後的使用心得~~~如有冒犯到原著作的地方真的非常抱歉~~~"整理出有用的資訊才是重要滴,謝謝分享囉
  • 謝謝喔!!希望有幫到忙!!

    FE replied in 2010/06/24 23:50

  • 吳政茜
  • 大家今天好


    大家今天好
  • landy14002
  • 全新居家網路創業系統
    讓你利用網路的便捷 在家兼職
    除了增加額外的收入外 也能擁有自己的事業
    快來免費體驗90天全新居家網路創系統
    http://jane7458.blogspot.com/

    如有打擾到您 跟你說聲抱歉!!!
    不好意思喔!!
  • 哈比
  • 好文章!
  • 三明治
  • 感謝版大的教學
  • Li
  • 太棒了,又學到一招了,謝謝分享
  • 呆子
  • 你的步驟七說
    這個程式=VLOOKUP(A2,Sheet2!$A$2:$B$16,2,0)
    可換成=VLOOKUP(A3,Sheet1!$A:$B,2,0)
    但EXCEL
    卻出現你輸入的程式有錯誤
    可否解答一下,這裡出現疑惑
  • 2010/10/27
  • 真的受益良多又清楚的圖解
    謝謝你讓我有效率的學習excel:)
  • 郭琬婷
  • hi


    希望能看到更多好東西加油喔
  • Merry~*
  • 太讚了~很詳細的解說~~
    這常常用得到呢~^^
  • start
  • 謝謝分享喔~~
    很清楚明瞭~~~~
  • 1000
  • 我之前的工作也有用到

    後來離職前 還做了pp檔的教學檔留給同事看
  • andy 推薦
  • 謝謝分享 .........我覺得非常好用的方法!!
  • 富
  • 「看個網拍還要一直點下一頁才能看到後面的商品嗎?!」

    現在不用了!!眾多便宜商品,一次直接都讓你看個夠,不必再去點下一頁了!!

    便宜又省時!!! 趕快來~~

    http://caifufu.pixnet.net/blog
  • 888
  • 謝謝分享!!經過你的解說,才發現原來沒有想像中那麼難!!
  • 訪客
  • 謝謝您的詳細教學,但可否提供上述的練習檔呢?
    有練習檔直接運用會比較容易熟記
  • Xiangsuki
  • 太棒了!!!上百筆資料中於不用眼花!!!
    真是太趕謝你了!!
  • James
  • 感謝分享教學唷~
  • weiang
  • 感恩分享教學
  • 徐 小馨
  • 太貼心了^_^都快哭了!
  • skiedeager
  • 這篇寫得太讚了!讓我長了一智,順到借轉~
  • ㄚ芬
  • 希望我下次要用時還記得這個英文字~ 囧
  • yyys77
  • 哈嚕~來看新格友^^留下認識你的足跡
  • 小蕃薯
  • 圖+文解說,真的太詳細了,尤其是公式後面為什麼要打0,我找了好久的答案終於找到你的解說,真的太感恩了,謝謝分享喔!!
  • 訪客
  • 謝謝~~~好好用
  • 各界遊
  • 說不定往後有機會用到,感恩您的分享^^!!!
  • Rick
  • 謝謝 非常受用
  • 迷惑的人兒
  • 謝謝您,這篇真是令我受用無窮!!
  • 訪客
  • Hi! Thank you very much! This is extremely helpful - especially the pictures. Just wondering, would you be able to introduce the Match function? I browsed other articles on the internet but still don't understand anything!

    Thanks again!
  • emily2721413
  • 感謝分享。很好用。歡迎各位大大有空到我家逛逛,互相分享資訊。謝謝。
  • Raymond
  • 與您分享一個故事!
    您覺得努力重要!還是動腦選對方法或工具重要呢?
    聰明的您!想要當影片故事中的誰呢?
    片長9分57秒,請耐心收看!
    影片>> http://goo.gl/ABN56
    PS:謝謝您的閱讀!祝版主心想事成、財源滾滾。
  • O n l y 5 .
  • 學生時期有學過
    但 等出社會真的要用到時
    早就全忘光了
    還好有妳這篇詳盡的解說
    真的是很棒耶!!
    好用心呀 ~(^o^)o

  • 你的講解真是太棒了!
  • 看了好多人的解說都越看越不懂
    你解釋的真清楚明瞭,
    感謝!

    垂死的生管 留
  • miki
  • 真心感謝分享, 一看就懂了. 受益良多.

  • 芙
  • 真感謝你~~ 非常受用呢~~

  • 賴彼得
  • 感謝大大
    受益良多
    四十幾年來
    終於學會了v look up
    因為有您這篇好文章
  • AYUMI
  • 寫的真好~我馬上試做就成功了~謝謝你~感恩
  • 不小心路過
  • 天ㄚ!!!解說的有夠清楚!!!!!!!!!!!!!!!!!網路上一堆教學根本不知道在說啥~~大大真是神先降世阿XDD 受用了
  • 如果&quot;值&quot;的內容是文字呢?
  • 我試過找數字真的很方便,
    但如果要找的"值"的內容是英文字似乎就不行了,
    請問版主,這工具是只能針對數字而已是嗎?
    謝謝囉~~
  • 內文不管是甚麼都可以參照搜尋喔!!! 可以寄檔案給我,我幫你瞧瞧~

    FE replied in 2013/06/20 13:00

  • 語涵
  • 謝謝分享喔^^
  • 西瓜
  • 大大~真的太太太感謝妳了~~~~~
    講得好詳細~~~~~
    學會了~~~真的好感動~~謝謝大大~~
  • 訪客
  • 非常感謝!!幫大忙了~~~
  • 訪客
  • 非常感謝您!!!!我差點就一個一個對了 又5000多條我都快哭了。 謝謝!!!!!
  • Student
  • Thank you!
  • 新光家具設計中心
  • 真的超實用,解說的很明確,不用再一筆一筆核對,真的太棒了,謝謝~
  • Weiting Lareina
  • 這個功能超好用的!!而且。。。目前也很常用到。。。
  • 這功能是我工作好幫手

    FE replied in 2013/09/17 22:07

  • 新手
  • 好文章能不推嗎!!!!!!
  • 逸姍
  • 你超棒的~看了這麼多篇只有這篇我看的懂
  • sex聊天室
  • 你超棒的~看了這麼多篇只有這篇我看的懂
  • 訪客
  • 很好用很好用,謝謝你的分享喔!!!
  • Young Ieong
  • 很實用,謝謝!!
  • 訪客
  • 謝謝你 我終於看懂了!!!!!!
  • 歡喜
  • 您教的每一步驟都非常仔細,感謝您用心分享給初學者^ ^
  • 皮皮
  • 我也有些問題,不知是否能請你幫忙呢?可以Mail給你幫我看看嗎?
  • 當然可以啊!!!我的信箱vionet0630@yahoo.com.tw,但我不算是高手,所以不一定能幫到您的忙喔!!

    FE replied in 2014/11/16 09:43

You haven’t logged in yet, please use guest status to leave message. You can also log in with above service account and leave message

other options