如下圖所示,根據姓名查找對應的每筆銷售明細
首先,我們在姓名前面加一列輔助列
輸入公式=B2&COUNTIF($B$2:B2,B2)
公式COUNTIF($B$2:B2,B2)對指定區域$B$2:B2中指定條件的單元格B2計數,
這樣相當於給姓名編了個號(讓每個姓名都唯一),第一個$B$2 使用了絕對引用,目的在於下拉的時候起點不變,終點一直變,這樣就能統計出區域中有幾個一樣的名字了。如圖中第一次出現的阿文就是阿文1,第二次出現的阿文就是阿文2
不明白的童鞋自己動手試一試哦!可不可以1阿文,2阿文呢?
然後在G2中使用公式
=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,3,0)
往下拉到足夠多行(比如最大10條數據,你可以拉15行,備用5行)
效果如下圖:
通過顏色對比,我們可以校驗一下返回的結果對不對
公式中函數 Row(A1) 返回A1所在的行號,作用是給查詢值加上一個序號,比如:F2為雨夜時,公式下過程中查找值變成了找雨夜1、雨夜2。。。實現了每行查詢的都是唯一值。
第一列日期“沒問題”了,就開始寫第二列銷售量的公式,它和和一列的區別僅僅是返回的數據列不一樣,
H2中輸入公式
=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,4,0) 看一下效果
我們把兩個公式放一起看看區別吧
=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,3,0)
=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,4,0)
明顯的區別就是參數3 裡的一個是3,一個是4,如果還需要5,6,7,8列的話就複製粘貼接著改。
有的小夥伴看到這裡是不是在偷笑了,小編原來還在複製粘貼改數字呀。要是遇上列數很多腫麼辦?
我們先把難看的 #N/A 給解決掉,回頭再來處理它。
公式外套個IFERROR容錯函數來屏蔽錯誤值,找不到時返回空
=IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$20,3,0),"")
一起來看公式的效果圖
怎麼樣,是不是“完美”了呢?
接下來改參數3,這裡可以用column函數,把參數3換成 column(C$1) 因為C列是第3列,因此該函數會返回3,當我們右拉一列它會變成column(D$1),這正好返回4 ,真是太巧了,正好是我需要的3和4。
公式如下
=IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$20,COLUMN(C$1),0),"")
咱們來看看效果
可有的小夥伴還會有疑問,要是查找時返回的列號是不規律的,這要怎麼辦呢??
那就得請到我們的Match來幫忙數數了,感興趣的同學自己動手試試吧。(可以參考阿文老師的高大上的動態圖表中MATCHT的用法哦)
閱讀更多 Excel自學筆記 的文章