這個Excel題目我輸了600元,今天和大家分享確實是一個這樣的故事,三年前,我的函數水平也算一箇中級用戶了,有一個Excel題目,姓名對應的數量,按數量拆分姓名,當時那個網友叫做“真誠”,如果那個用函數做出來,我就免費讓他學我的vba課程,第2天,他是做出來了,不過用了輔助列,當然也算他對了,我實現我自己的諾言,讓他免費學習我們的vba課程.,因此我輸了600元,今天是直接一個函數出來,不用輔助列實現。
具體的效果大家先看動畫效果
看完動畫想學的朋友跟我一起來,不是vba做的,是函數 實現的。
公式:
=LOOKUP(ROW()-2,SUMIF(OFFSET($B$1,,,ROW($B$1:B4)),">0"),$A$2:A4)&""
公式解釋:
公式=OFFSET($B$1,,,ROW($B$1:B4))從B1開始,行不偏移,列也不偏移,行高用了數組{1;2;3;4},列寬是1,可以不寫,包括第5參數那個逗號。這樣我們就構建了一個三維引用,有的也叫做多維,大家可以理解offset得到4個結果不在同一個平面,有的同學可以會F9測試,F9也看不到正確的結果,因為是多維。按也是白按
公式
=sumif(OFFSET($B$1,,,ROW($B$1:B4)),">0")
首先大家要理解第1個知識點,sumif有降維的作用
第2個知識點,當sumif第1參數和第3參數是一樣時,第3參數可以不寫
第3個知識點,sumif第3參數有文本參數,文本參數計0
sumif第2參數條件用了“>0",這樣我們就找到每一個姓名之間相差多少個值,對應的就是姓名後面的數量
=sumif(OFFSET($B$1,,,ROW($B$1:B4)),">0")返回{0;1;4;5}
公式
=LOOKUP(ROW()-2,SUMIF(OFFSET($B$1,,,ROW($B$1:B4)),">0"),$A$2:A5)
row()-2,因為公式寫在第2行,查找值要從0開始,所以-2
lookup的第參數是$A$2:A5
lookup函數的第2參數SUMIF(OFFSET($B$1,,,ROW($B$1:B4)),">0")返回的{0;1;4;5}
當查找值是0時,找到0在第1個位置,對應第3參數是“佛山小老鼠"
當查找值是1時,找到1在第2個位置,對應第3參數是“曹麗"
當查找值是2時,沒有2,也是找到1在第2個位置,對應第3參數是“曹麗"
當查找值是3時,沒有3,也是找到1在第2個位置,對應第3參數是“曹麗"
當查找值是4時,找到4在第3個位置,對應第3參數是“天津丫頭"
當查找值是5時,找到5在第4個位置,對應第3參數是0,這時我們為了屏蔽0,所以在公式的最後添加&""