一個(gè)特殊的求和案例:每個(gè)匯總值所對(duì)應(yīng)的行數(shù)和列數(shù)都不固定
?
作者:老菜鳥(niǎo)來(lái)源:部落窩教育發(fā)布時(shí)間:2022-05-20 15:01:13點(diǎn)擊:1665
編按:
今天來(lái)給大家分享一個(gè)特殊的求和問(wèn)題,即每個(gè)匯總值所對(duì)應(yīng)的行數(shù)和列數(shù)都是不固定,我們最后用到的是SUMPRODUCT函數(shù),沒(méi)思路的同學(xué)趕緊來(lái)看一看吧!
最近看到一位群友的求助,覺(jué)得問(wèn)題很典型,特意拿出來(lái)和大家分享一下。
模擬數(shù)據(jù)如圖所示:
上半部分是銷售數(shù)量的明細(xì)表,我們要按平臺(tái)和商品分類對(duì)銷量進(jìn)行統(tǒng)計(jì),統(tǒng)計(jì)表放在數(shù)據(jù)源的下方,上方黃色的區(qū)域之和對(duì)應(yīng)下方黃色的一個(gè)單元格。
問(wèn)題不難理解,但確實(shí)有點(diǎn)麻煩。因?yàn)槊總€(gè)匯總值所對(duì)應(yīng)的行數(shù)和列數(shù)都不固定。
以下先給出一個(gè)解決方案,然后再做解析。
輸入公式:
=SUMPRODUCT($B$2:$P$18*(LEFT($A$2:$A$18,LEN($A22))=$A22)*(LEFT($B$1:$P$1,LEN(B$21))=B$21))
這個(gè)公式看起來(lái)挺復(fù)雜,原理還是比較好理解的,下面分段進(jìn)行介紹,希望大家能看明白。
這個(gè)問(wèn)題的本質(zhì)是多條件求和,只不過(guò)條件有點(diǎn)特殊。對(duì)于多條件求和的問(wèn)題,用SUMPRODUCT基本能囊括完。
其次要理解的是,這個(gè)問(wèn)題涉及到兩組條件,條件1是平臺(tái)(店鋪),條件2是分類(品名)。
所以公式的主體結(jié)構(gòu)就能確定了,=SUMPRODUCT(數(shù)據(jù)區(qū)域*條件1*條件2)
看上去是不是挺簡(jiǎn)單?
數(shù)據(jù)區(qū)域是$B$2:$P$18,這也好理解,難點(diǎn)就在于兩個(gè)條件的表達(dá)方式,下面來(lái)詳細(xì)解釋一番。
條件1:平臺(tái)(店鋪)
通過(guò)對(duì)比發(fā)現(xiàn),店鋪名稱都是以平臺(tái)名稱作為開(kāi)頭的,所以用LEFT函數(shù)就可以從店鋪名稱里提取出平臺(tái)名稱,問(wèn)題是平臺(tái)名稱的長(zhǎng)度不是固定的,有的兩個(gè)字,有的三個(gè)字,因此只用LEFT還不夠,再組合LEN函數(shù)來(lái)確定要截取的長(zhǎng)度就可以了。
公式中的LEFT($A$2:$A$18,LEN($A22))這部分得到的結(jié)果如圖所示。
這里用到了一個(gè)數(shù)組計(jì)算,按照A22單元格里字符的長(zhǎng)度,在$A$2:$A$18這個(gè)區(qū)域截取內(nèi)容,有五個(gè)是和A22單元格的內(nèi)容一樣,由此確定這五行數(shù)據(jù)是需要匯總的。
因此第一個(gè)條件是:(LEFT($A$2:$A$18,LEN($A22))=$A22)
注意$A22使用了鎖定列的混合引用方式,因?yàn)楣皆谕蟿?dòng)的時(shí)候要始終固定在A列。
同樣的原理,第二個(gè)條件是判斷分類和品名之間的關(guān)系,公式(LEFT($B$1:$P$1,LEN(B$21))=B$21)中唯一要注意的就是B$21變成了鎖定行的混合引用,這是因?yàn)楣皆谕蟿?dòng)的時(shí)候始終要固定在一行。
經(jīng)過(guò)以上分析再來(lái)看整個(gè)公式,就比較清晰了,你看明白了嗎?
總結(jié)一下,這個(gè)公式的難點(diǎn)是兩個(gè)條件都是部分包含的關(guān)系,這與以往的例子有所不同。
不過(guò),好在,最后我們給出了完美的解決方案!
好啦!以上就是今天的所以內(nèi)容啦,你學(xué)會(huì)了嗎?
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
你會(huì)累計(jì)求和嗎?這5個(gè)技巧簡(jiǎn)直太好用了!
版權(quán)申明:
本文作者老菜鳥(niǎo);同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫(xiě)遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)