Vlookup在excel中神運用,亮瞎眼的操作哦

2021-02-19 會計聯盟超級群

指定條件在指定區域直方向查找
VLOOKUP(要查找的內容,搜索的區域,從查找區域首列開始到要找的內容的列數,指定是近似匹配還是精確匹配查找方式)

還講解了VLOOKUP的出錯因果，

第2個參數區域設置錯誤之2。

如下圖所示根據姓名查找職務時產生查找錯誤。

錯誤原因：本例是根據姓名查找職務，可大家注意一下，第2個參數B1:D6根本就沒有包括E列的職務，當然會產生錯誤了。所以公式應改為：

=VLOOKUP(A9,B1:E6,4,0)

第4個參數少了或設置錯誤。

如下圖所示根據工號查找姓名

錯誤原因：vlookup第四個參數為0時表示精確查找，為1或省略時表示模糊查找。如果忘了設置第4個參數則會被公式誤以為是故意省略，按模糊查找進行。當區域也不符合模糊查找規則時，公式就會返回錯誤值。所以公式應改為。

=VLOOKUP(A9,A1:D6,2,0)

或 =VLOOKUP(A9,A1:D6,2,) 註：當參數為0時可以省略，但必須保留「,」號。





二、數字格式不同，造成查找錯誤。

查找為數字，被查找區域為文本型數字。

如下圖所示根據工號查找姓名，查找出現錯誤。

錯誤原因：在vlookup函數查找過程中，文本型數字和數值型數字會被認為不同的字符。所以造成無法成功查找。

解決方案：把查找的數字在公式中轉換成文本型，然後再查找。即：

=VLOOKUP(A9&"",A1:D6,2,0)

查找格式為文本型數字，被查找區域為數值型數字。

如下圖所示根據工號查找姓名，查找出現錯誤

錯誤原因：同4

解決方法：把文本型數字轉換成數值型。即：

=VLOOKUP(A9*1,A1:D6,2,0)

註：如果你手工把文本轉換成數值類型，就不必在公式中轉換格式了。




三、引用方式使公式複製後產生錯誤。

沒有正確的使用引用方式，造成在複製公式後區域發生變動引起錯誤。

如下圖所示，當C9的公式複製到C10和C11後，C10公式返回錯誤值。

錯誤原因：由於第二個參數A2:D6是相對引用，所以向下複製公式後會自動更改為A3:D7,而A10中的工號A01所在的行，不在A3:D7區域中，從而造成查找失敗。

解決方案：把第二個參數的引用方式由相對引用改為絕對引用即可。

B9公式改為：=VLOOKUP(A9,$A$2:$D$6,2,0）




四、多餘的空格或不可見字符

數據表中含有多餘的空格。

如下圖所示，由於A列工號含有多餘的空格，造成查找錯誤。

錯誤原因：多一個空格，用不帶空格的字符查找當然會出錯了。

解決方案: 1 手工替換掉空格。建議用這個方法

2 在公式中用trim函數替換空格而必須要用數據公式形式輸入。

即：=VLOOKUP(A9,TRIM(A1:D6),2,0)

按ctrl+shift+enter輸入後數組形式為

{=VLOOKUP(A9,TRIM(A1:D6),2,0)}

類空格但非空格的字符。

在表格存在大量的「空格」，但又用空格無法替換掉時，這些就是類空格的不可見字符，這時可以「以其人之道還之其人之身」，直接在單元格中複製不可見字符粘貼到替換窗口，替換掉即可。

不可見字符的影響

如下圖所示的A列中，A列看上去不存在空格和類空格字符，但查找結果還是出錯。

出錯原因：這是從網頁或資料庫中導入數據時帶來的不可見字符，造成了查找的錯誤。

解決方案：在A列後插入幾列空列，然後對A列進行分列操作（數據 - 分列），即可把不可見字符分離出去。

反向查找vlookup不支持產生的錯誤

如下圖所示的表中，根據姓名查找工號，結果返回了錯誤

錯誤原因：vlookup不支持反向查找。

解決方法：

1.用if函數重組區域，讓兩列顛倒位置。

=VLOOKUP(D8,IF({0,1},D2:D4,E2:E4),2,0)

2.用index+match組合實現。

=INDEX(D2:D4,MATCH(D8,E2:E4,0))

通配符引起的查找錯誤

如下圖所示，根據區間查找提成返回錯誤值。

錯誤原因：~用於查找通配符，如果在vlookup公式中出現，會被認為特定用途，非真正的~。如在表格中查找3*6 ，356，376也被查找到。

如果精確查找3*6，需要使用~，如下圖所示。

解決方法：用~~就可以表示查找~了。所以公式可以修改為

=VLOOKUP(SUBSTITUTE(A8,"~","~~"),A2:B4,2,0)

vlookup函數第1個參數不直接支持數組形式產生的錯誤

如下圖所示，同時查找A和C產品的和，然後用SUM求和。

錯誤原因: VLOOKUP第一個參數不能直接用於數組。

解決方法：利用N/T+IF結構轉化一下數組

公式修改為：=SUM(VLOOKUP(T(IF({1},A8:B8)),A2:B5,2,))

weiacc 綜合整理髮布 引用或轉載，請註明以上信息 , 我們致力於保護作者版權，除我們確實無法確認作者外，未註明來源，如涉及侵權，請及時聯繫我們刪除，並表示歉意，謝謝！   

●  財務會計現可暢學130+門課程有乾貨持續上新中

 ●財務玩轉Excel系列-IF函數的使用詳解

  ● 函數入門EXCEl升級圖解練習

  ●  《第15講讓會計人不用加班的28個Excel小技巧！》『

  ● 財務從入門到高手成為牛人的Excel 技巧（與分享）

  ● 財務必看！Excel技巧，每一個都超級實用

  ● 財務常用EXCLE技巧48條

  ● 財務成為牛人的10大 Excel技巧，值得收藏

  ● 分享《EXCLE工資條製作的多種方式》

●（限時免費公開課）會計實務經營中各項債權的核算

● 快速學會講課提升自己口才能力

● 財務會計快速學習的五大方法

● 森淼財稅招會計老師，課代表， 助教人員50人 

 

 

 

要參與老師可以加微信：ayj2006   備註：老師
參與課代表可以加微信：ayj2006   備註：課代表
要參與助教可以加微信：ayj2006   備註：助教

相關焦點

  • 函數vlookup更有價值更高級的運用——跨表查詢
    excel在上一篇文章裡，我們已經通過舉例的方式對函數vlookup的基本用法進行了非常詳細的闡述，並且也對函數vlookup中的四個參數的相關問題進行了解答。相信看過後的朋友肯定會發現，雖然函數vlookup的數據提取功能確實十分突出，但是在這裡似乎非常雞肋，因為這裡的數據挺簡單的，再加上只要提取兩個結果，我們憑藉肉眼也可以完成任務。事實上，為了介紹函數vlookup的基本操作方法，我們無需太過複雜的excel工作表，所以對表格數據做了簡化。
  • excel中vlookup函數的用法筆記
    本篇將介紹excel中vlookup函數的用法，有興趣的朋友可以了解一下！一、前言excel是我們工作中很常用的表格製作工具，它不僅僅只是用來製作表格，還能對數據進行處理（如：排序、運算等）。excel中還給我們提供了很多實用的函數，今天小編要介紹的就是其中一個，也是比較常用的一個函數，即vlookup函數。vlookup函數的功能其實就是用來找東西的，只要是找excel表格中的某個數據，大家都會想到使用vlookup函數。vlookup函數怎麼用呢？相信是很多人關心的問題，接下來就跟著小編一起來學習excel vlookup函數的用法吧！
  • 值得學習的excel操作小技巧,利用vlookup函數實現一對多查詢
    我們在實際工作中，我們經常使用excel表格對數據進行處理和分析，我們都清楚excel具有強大的excel函數和數據處理工具，我們可以憑藉這些工具盒函數對數據進行快速處理，這次我們還是要講解一下有關vlookup函數的相關內容，我們知道vlookup函數是一個查找函數，我們這次講解的是利用
  • excel函數公式的巧妙運用
    excel關於if函數的嵌套使用中，第一篇文章，我們介紹了怎樣運用if函數在兩種不同結果之間進行判斷計算的操作過程，第二篇文章中我們更近一步，函數具體操作可參考下圖：具體操作方法二（方法二繼承了文章「excel關於函數if的巧妙運用」中的方法，使用函數if最簡單的方式進行疊加運算）：在G4單元格輸入「=IF(F4="A級",10000,0)+IF(F4="B級",9000,0)+IF(F4="C級",8000,0)+IF(F4="D級",7000,0)」，按回車鍵，得到業務員丁一的應發獎金數
  • EXCEL強大的vlookup
    vlookup在excel是一個使用頻率相當高的公式，也是我個人最喜歡的公式之一。vlookup公式是查找左側列中的值，如果找到匹配的項，則會在右側的另一列中返回一個信息。公式語法如下：=vlookup(A1,B:C,2,FALSE)其中：A1代表想查找什麼內容。B:C代表想在那裡查找。
  • EXCEL公式-VLOOKUP應用
    excel的功能十分強大，vlookup函數是最基礎的函數之一，其作用非常強大可以幫助我們在眾多雜亂的數據中找到我們想要的答案。那這個函數該如何使用，下面給大家介紹一下excel中vlookup函數的使用方法。
  • 如何在excel中使用vlookup函數?
    vlookup函數通常用於在excel工作簿中搜索某個單元格區域的第一列，然後返回該區域相同行上任何單元格中的值。 當然在使用vlookup函數之前，我們必須對vlookup函數有基本的認識和了解，所以今天小編就來介紹一下vlookup函數的語法，大家可以跟著小編來學習一下。
  • 有趣但又現實的函數vlookup之模糊匹配用法
    在上一篇文章中，我們講述了函數vlookup與通配符相結合時的運用方法，並對其中的誤區和相應的解決方法進行了介紹。這三篇文中都有一個共同點，我們所舉的例子都是對excel工作表中的數據進行精確匹配。第二張表裡有女性年齡和男性編號兩項內容，現在我們假設女性年齡已知，要進行匹配的男性編號未知，現在我們任務就是基於第一張表的數據和第二張表裡的女性年齡，運用函數vlookup為不同年齡的女性匹配上合適年齡段的男性。excel工作表具體如下圖所示：
  • excel中vlookup函數的使用方法
    vlookup函數是excel表格中高級的用法，通過vlookup函數我們可以調用符合條件的數據，在大量調用時可以節省我們查找複製excel數據的時間，今天我就教下大家vlookup函數的使用方法吧。vlookup函數的使用方法如圖我準備了一張員工入職時間表，員工有非常多，如果我要在這裡面一一找出張三李四王五等人的入職時間的話，可以通過查找黏貼的方式，但是這樣的效率就很低了，特別是要找的人多的話，那使用vlookup函數是最簡單的方法。
  • 如何用python實現excel中的vlookup功能?
    今天這篇分享，就是刀哥在做的過程中，遇到的其中一個知識點，即用python來實現excel中的vlookup函數功能。相信大家都知道excel的vlookup函數，也會運用，這是財務審計工作中經常會用到的函數之一，那用python如何實現這一功能呢？1.
  • 函數vlookup與通配符功能的完美結合,助你解決習慣造成的錯誤
    excel在前兩篇文章中，第一篇文章向大家詳細介紹了函數vlookup的語法形式和基本運用方法，第二篇文章在函數vlookup的基本運用方法基礎上進一步介紹了函數vlookup的實用操作方法今天要講述的內容依然是在函數vlookup的基礎用法上進行的拓展。我們在運用excel工作表來解決日常中的問題時，往往所用的函數沒有任何問題，但是由於習慣原因，會導致我們在運用excel工作表中的函數時的過程並不順利，今天的主講內容就是我們在運用excel工作表時的習慣「錯誤」，以及解決錯誤的方法。
  • excel查找數據就是如此簡單,vlookup函數的模糊查找
    我們在實際工作中，我們經常使用excel表格處理數據，處理數據的方法有很多種，查找數據應該是我們在日常工作中使用頻率比較高的操作，這次我們還是分享查找數據的小技巧，這次是對數據進行模糊查找，我們使用vlookup函數對數據進行模糊查找，下面我們就以實例結合視頻的形式將詳細的操作步驟展示出來
  • Excel vlookup篩選兩列的重複項與查找兩個表格相同數據
    以下是vlookup篩選兩列的重複項與查找兩個表格相同數據的具體操作方法，實例中操作所用版本均為 Excel 2016。一、Excel vlookup篩選兩列的重複項1、假如要篩選出一個表格中兩列相同的數據。
  • 函數vlookup匹配單列數據你已學會,那麼同時匹配多列數據呢?
    excel在之前的七篇文章中，我已經對函數vlookup的用法進行了深入淺出的講解，相信看過的朋友們一定對函數vlookup的用法已經爛熟於心，今天我們還將繼續以函數vlookup，基於某列數據運用函數vlookup去尋找另一列數據，總而言之，都是去匹配單列數據。
  • 函數sumif居然會搶函數vlookup的飯碗?真有這種可能
    excel在之前的四篇文章裡，我們從最基礎的函數sum展開，依次向大家介紹了函數sumif的操作方法，函數sumif運用時的誤區，函數sumifs的運用方法以及函數sumif運用時實際求和區域的簡寫第四參數range_lookup是一個邏輯值，1表示近似查找，0表示精確查找（通常我們並不填寫第四參數，這時默認為精確查找）咱們還是按照老規矩來辦事，運用實例的方式來向大家展現函數sumif是怎樣搶函數vlookup的飯碗的。實例一：我們現在有這樣一個excel工作表，其中有兩張表。
  • excel查找函數-vlookup
    大家都會想到 VLOOKUP、LOOKUP等熟悉的查詢函數，但是對於剛入接觸或學習excel的新手不知道怎麼操作，今天分享關於查詢問題通過下面例子進行演示，如下：關鍵詞：vlookup、IFERROR、反方向查找函數使用格式=VLOOKUP
  • 當函數vlookup無能為力時,函數match和index未嘗不是更好的選擇
    excel在前六篇文章，已經大家詳細介紹了函數vlookup的基本用法——近似（模糊）匹配和精確匹配，並且還介紹了函數vlookup與通配符聯合運用來解決習慣性誤區的方法，以及函數vlookup遇到數字格式問題時的應對方法
  • excel數據的模糊查找,vlookup函數與通配符的搭配使用
    今天我們要分享一個比較實用的excel數據的查找技巧，就是通過簡稱來查找全稱，這樣的查找方式叫做模糊查找，我們之前學習過了幾個查找函數，比較常見是vlookup函數和lookup函數以及choose函數等，我們知道這些查找函數各自有各自的優點，今天我們要講解的是vlookup函數與通配符的搭配使用
  • excel中vlookup函數的常見的基本使用方法介紹
    excel函數VLOOKUP的功能就是查找。 主要演示利用vlookup函數在另外一張表中只輸入學生的名次就顯示學生的姓名和總分。 vlookup函數主要是一個縱向的查找函數，最終把想要的列的值給提取到顯示出來。
  • Python實現Excel中vlookup函數功能
    本文作者：王碧琪文字編輯：方   言技術總編：張馨月爬蟲俱樂部將於2020年8月25日至28日在線上舉行Stata數據分析法律與制度專題訓練營，主要是為了讓學員掌握Stata軟體進階操作使用Excel進行數據分析時常常用到vlookup函數。在《Stata實現Excel中vlookup函數功能》中介紹了vlookup函數的基本用法，今天，我們使用Python中的pandas庫來實現該函數的相應功能。