VLOOKUP函數在Excel中的這種秘密功能,不告訴你一輩子也找不到!

點擊上方藍字  免費關注

置頂公眾號或設為星標，否則可能收不到文章

個人微信號 | （ID：LiRuiExcel520）

微信服務號 | 跟李銳學Excel（ID：LiRuiExcel）

微信公眾號 | Excel函數與公式（ID：ExcelLiRui）

Excel查找引用函數VLOOKUP很多人都用過吧，但大家都是用它查詢數據，它還有一種秘密功能，你知道嗎？

今天要講的就是VLOOKUP不為人知的秘密技術，按要求自動生成符合要求的數據，幫助我們自動排布數據，比如生成自動排班表。

下面案例是某企業的值班安排表，裡面包含了值班員工姓名和安排好的值班天數。

統計要求如下：

1、根據左側的值班安排表，在右側自動生成每天的值班員工列表；

動圖演示如下圖所示。

黃色區域的數據都是用公式自動計算生成的，這裡用的就是VLOOKUP函數。

你肯定好奇這是怎麼實現的？下文分解。

構建思路：

首先咱們一起來構建一下思路，當前案例要求按姓名和值班次數生成每日值班表，目前的數據源還缺少什麼？

這就自然想到了數據源中已有值班姓名和值班天數，還缺少對應關係，即沒有哪個日期對應哪個值班姓名，如果有了這個就可以直接查找調用了。

到這一步時，你會發現現有條件無法支持直接得到每個日期和對應姓名，那麼我們可以換個思路，使用倒推法，退而求其次，繼續想，根據現有條件能統計到什麼？

從這個現有條件，我們雖然不能知道每個員工的值班開始天數，但是能統計到每個員工的值班截止天數，比如李銳1值班到第5天，李銳2值班到第7天（5+2=7），李銳3值班到第9天（5+2+2=9），.李銳5值班到第15天。

好了，思路來了，我們可以按照員工最後值班的天數查找對應的值班姓名，當然，前提是數據源中包含這個每人值班截止天數的信息，加個輔助列即可

在原始數據左側插入列，用於標識每人的值班截止天數，在B2單元格輸入以下公式。

（下圖為輔助列公式）

一句話解析：

用SUM函數配合混合引用生成值班天數的累加值，即每個人值班的截止天數。

現在好了，有了這個輔助列，我們就可以在右側的對應天數是5/7/9/10/15時直接VLOOKUP調取對應的員工姓名了。

那麼現在還差中間的那些天數，如何查找對應姓名呢？繼續看下面的解決方案。

解決方案：

先接著上一節思路把第15天值班的當值員工姓名用公式查找出來，後面再順藤摸瓜就輕鬆多了。

在F15單元格輸入以下公式，向上填充公式到F2單元格。

=IFERROR(VLOOKUP(E16,$A$2:$C$6,2,),F17)
注意，這裡為了讓你更容易理解，所以先在下方寫公式，然後向上填充，效果如下圖所示。
在F16的單元格VLOOKUP第一參數是15，肯定可以找得到左側15對應的姓名是李銳5，那麼F15的單元格呢？
F15單元格VLOOKUP第一參數是14，肯定找不到，會返回錯誤值，再次用IFERROR函數返回當前單元格下方的數據。
一句話解析：
由於下方的第15天值班姓名已經出來了，所以只要第14天從左側沒有找到對應姓名，說明還是這個人值班，所以從當前公式所在單元格的下方單元格取值就行了。這個思路非常巧妙，你如果一下沒懂請回顧上方說明，順著我的思路再次突破下自己的思維壁壘。
理解後，我們再價格容錯判斷，形成最終公式。
在F2單元格輸入以下公式，並向下填充。
=IF(E2>MAX($A$2:$A$6),"",IFERROR(VLOOKUP(E2,$A$2:$C$6,2,),F3))
一句話解析：
先用IF判斷將可能返回0的結果返回空，再利用倒推法使用VLOOKUP查找，生成所需的每日值班人員列表。
此案例公式雖然並不複雜，但思路頗為精妙，如若能完全理解，將會對你的功力提升大有裨益，如一時不能參透也屬正常，可以先收藏起來日後再看。
這些常用的經典excel函數公式技巧可以幫你在關鍵時刻解決困擾，有心的人趕快收藏起來吧。
希望這篇文章能幫到你！怕記不住可以發到朋友圈自己標記。
更多方向和領域的Excel實戰技術，從入門到高級都有超清視頻精講，請從微信公眾號「跟李銳學Excel」進底部菜單的「知識店鋪」查看
今天就先到這裡吧，希望這篇文章能幫到你！
更多乾貨文章加下方小助手查看。
如果你喜歡這篇文章
歡迎點個在看，分享轉發到朋友圈
乾貨教程 · 信息分享
歡迎掃碼↓添加小助手進朋友圈查看
關注微信公眾號 「Excel函數與公式」，免費收到Excel職場辦公教程
▼
關注後每天都可以收到Excel乾貨教程
請把這個公眾號推薦給你的朋友
↓↓↓點擊「閱讀原文」進知識店鋪
     全面、專業、系統提升Excel實戰技能