HI,大家好,我是星光。今天給大家聊SCAN。
這貨的基本語法如下:
=SCAN(初始值,資料來源, LAMBDA(引數1,引數2,計算方式))
它可以遍歷資料來源的每一個數據,根據計算方式,返回一個
與資料來源尺寸相等的陣列
。
舉三個常見的用法小栗子。
1
填充空值
如下圖所示,A列存在合併單元格,現在需要建立一個
記憶體陣列
,將A列的資料填充完整
——既然你已經學到SCAN,函式段位起碼也是星耀Ⅴ了,就別問我這個問題為什麼用記憶體陣列,而不是基礎操作或者輔助列了。
SCAN函式解法參考如下:
公式看不全可以左右拖動。。
=SCAN(“”,A1:A12, LAMBDA(_a,_b,IF(_b=“”,_a,_b)))
第1引數是初始值,第2引數是資料來源A1:A12,第3引數是一個LAMBDA表示式,它又有3個引數,前兩個引數分別被命名為_a和_b,其中_a指向初始值,_b指向資料來源的迭代元素,第3個引數是一條IF函式
IF(_b=“”,_a,_b)
IF函式的意思是如果計算元素_b為空,則返回初始值_a,否則返回_b自身。
SCAN遍歷資料來源的每個元素,並執行LAMBDA計算,整個公式的計算過程如下▼
第1次先計算A1單元格的值,此時初始值_a為空,_b指向A1單元格,值為“姓名”。_b不等於空,IF表示式返回_b自身,SCAN將
計算結果作為新的初始值
。初始值_a也就變成了“姓名”。
第2次計算A2單元格的值,此時_b指向A2單元格,值為“看見星光”,它不等於空,IF表示式返回_b自身,SCAN將
計算結果作為新的初始值
。初始值_a就變成了“看見星光”。
第3次計算A3單元格的值,此時_b指向A3單元格,值為空,它等於空,IF表示式返回初始值_a,SCAN將
計算結果作為新的初始值
。初始值_a依然是“看見星光”。
第4次計算A4單元格的值,此時_b指向A4單元格,值為空,IF表示式返回初始值_a,SCAN將
計算結果作為新的初始值
。初始值_a還是“看見星光”。
……
其餘以此類推,把每個元素遍歷完成後,將計算結果以陣列的形式返回。
在各種親疏關係不同的場合裡我們都講過,工作表新函式正在加速向程式語言轉換,學習這類新函式,多少都需要一點程式設計的迴圈思維。如果你學過VBA又或者其它程式語言,再看這類新函式應該簡單很多,畢竟它們只是一類被封裝好的基礎迴圈語句。
SCAN函式不但支援引用,也支援陣列,不但支援查詢,也支援聚合等計算方式。
2
累加求和
如下圖所示,需要將A列的資料按B列的指定次數重複,D列為模擬結果。
(
星光老師的原圖有公眾號二維碼,但是頭條號裡不允許,那就不好意思了…
…)
這題函式解法有十幾種,其中涉及到SCAN函式的解法參考如下:
=XLOOKUP(
SEQUENCE(SUM(B2:B5)), SCAN(0,B2:B5, LAMBDA(_a,_b,_a+_b)), A2:A5, “”,1,-1)
SCAN部分作為XLOOKUP函式的查詢範圍,對B列的資料累加求和。
SCAN(0,B2:B5, LAMBDA(_a,_b,_a+_b))
第1引數是初始值,為0,第2引數是資料來源B2:B5,第3引數是LAMBDA表示式。系統遍歷資料來源的每一個數據▼
第1次時,初始值_a為0,計算元素_b指向B2單元格,為2,計算結果是_a+_b,為2,
作為新的初始值
。
第2次時,初始值_a為2,計算元素_b指向B3單元格,為3,計算結果是_a+_b,為5,
作為新的初始值
。
第3次時,初始值_a為5,計算元素_b指向B4單元格,為4,計算結果是_a+_b,
為9
,作為新的初始值。
第4次時,初始值_a為9,計算元素_b指向B5單元格,為2,計算結果是_a+_b,
為11
,作為新的初始值。
最後返回計算結果組成的記憶體陣列:
XLOOKUP函式查詢SEQUENCE函式生成的序列值,查詢範圍是SCAN函式返回的累加求和的陣列,結果範圍是A2:A5,查詢方式是從後往前找,找下一個較大項。比如,當尋找1時,匹配大於等於1的最小值2,返回對應的人名看見星光。
3
連
勝
次數
如下圖所示,是A列各支隊伍10輪比賽的情況,需要在L列統計每支隊伍最大的連勝次數。
SCAN函式解法參考如下:
L2單元格輸入後向下複製填充:
=MAX(SCAN(0,B2:K2, LAMBDA(_a,_b,IF(_b=“勝”,_a+1,0))))
SCAN第1引數是初始值,為0,第2引數是資料來源B2:K2,第3引數是LAMBDA表示式。遍歷資料來源的每一個元素,
如果為勝,則累加初始值,否則將初始值歸0
。
SCAN函式返回一個記憶體陣列。例如,二肥隊B3:K3返回結果如下:
最後使用MAX函式從中取最大值,也就是相關隊伍的最大連勝次數。
相比於傳統的FREQUENCY函式而言,SCAN的計算過程是不是清晰很多?
最後給大家說一個
好訊息
,在新函式體系裡,SCAN函式並不是必須的,大部分時候,它能做到的事情,
REDUCE函式都能做到(效率和公式長短有差異)
。然後再給大家說一個
壞訊息
,號稱萬能函式的REDUCE相比SCAN函式而言,要更……難一些。
今天給大家分享的內容就這樣, 揮揮手,咱們明天再見。
原載公眾號:Excel星球
作者:看見星光