多表條件求和,以前都使用SUMIF+INDIRECT函式公式,如下圖所示:
這個公式很復雜,同時還有兩個缺陷:
1、表名必須要有規律的數位,如1,2,3,4....
2、每個表格的列順序要完全一致。
今天蘭色介紹的這個函式則可以完美相容這兩種情況,無論表格有多少,無法表格的格式差別有多大,都可以按各個表的標題行自動求和。
【例】如下圖所示,有A,B,C.....N個表格( 行順序、行數、列順序、列數均不 同),需要把這N個表格數據匯總到一個表中。
這樣的問題,之前很多高手都以為除了PQ和VBA,用公式基本上不可能完成。蘭色偏不信邪,經過4個小時的努力,竟然利用新函式寫出來了格式不同的任意多表的求和公式,而且用LAMBDA函式封裝成了一個 DBSUM 函式。
語法:
=DBSUM( 匯總的列標題 , 匯總的項 , 多表區域 )
本例公式:
=DBSUM( C1 , B2 , 'A:C'!B1:J13 )
公式設定演示:
C2的公式是對所有表(
'A:C'!B1:J13
)的張三(
B2
)的基本薪資(
C1
)進行求和
最後加上絕對參照後就可以復制後表中所有黃色區域,多表求和結果也出來了。
功能夠不夠強大,使用是不是很簡單?嘿嘿,我猜你也想馬上試試這個函式,蘭色和你說怎麽才能有這樣的函式(
不過你的版本要是OFFICE365或WPS表格哦
)
公式 - 名稱管理器 - 新建 - 名稱輸入DBSUM,參照位置貼上以下公式
=LAMBDA(行標簽,列標簽,多表區域,LET(x,VSTACK(多表區域),SUM(TOCOL(TOCOL(x,,1)*(SCAN("",TOCOL(x,,1),LAMBDA(A,B,IF(ISTEXT(B),B,A)))=行標簽)*(TOCOL(IF(x,INDEX(x,,1),0),,1)=列標簽),2))))
完工,以後你就可以在這個表中使用DBSUM函式了。
估計大家會有兩個疑問
1、在其他表中可以用這個函式嗎?
需要如上定義名稱操作,就可以。
2、發給別人還可以用嗎?
需要他的版本為WPS表格或OFFICE365。如果他沒有,公式不能更新結果。
蘭色說
:本例中蘭色大量的使用了新版本函式,(
如果你還在用excel老版本,建議安裝WPS最新版本
)。這些新函式功能有多強大,毫不誇張的說可以顛覆以前版本大多數公式,並且以前無法完成的新函式也可以。
本周日蘭色將在視訊號直播間講解(免費)部份新函式的用法,如 Textsplit、Tocol、Lambda、Scan 等,想聽課的同學趕緊點下面 預約 哦
另外,這些新函式的用法蘭色也會陸續錄成教程更新到函式公式大全教程中,如果你想學習更多函式, 可以購買蘭色四合 一大全套教程( 含 圖表185集 + 函式144集+透視表50集+技巧大全119集 ) ( 辦理年卡可免費學習和Excel課件 )