日期是文本與合并單元格的季度匯總
?
作者:小窩來源:部落窩教育發(fā)布時(shí)間:2023-11-18 18:26:37點(diǎn)擊:663
日期是文本還是合并單元格,怎么按季度匯總?
學(xué)員:怎么按季度求和?
小窩:《用函數(shù)公式按季度或按周求和》教程都寫著呢。
過了10分鐘。
學(xué)員:教程不對(duì),求不出來。怎么辦?
小窩:教程不對(duì)?不可能啊……你把數(shù)據(jù)發(fā)過來看看呢。
收到發(fā)過來的數(shù)據(jù),小窩一看,發(fā)現(xiàn)了問題:不但有合并單元格,日期還是文本。
這樣的數(shù)據(jù)的確無法直接用小窩推薦的教程來求和。
小窩最終提供了兩個(gè)方案:
1.不修改表格,用長(zhǎng)長(zhǎng)的公式進(jìn)行條件求和
輸入公式后向右向下填充。低版本需要按三鍵輸入。
=SUMPRODUCT((ROUNDUP(SUBSTITUTE(LOOKUP(COLUMN($B$2:$M$2),COLUMN($B$2:$M$2)/($B$2:$M$2<>""),$B$2:$M$2),"月","")/3,0)=ROUNDUP(COLUMN(A1)/2,0))*($B$3:$M$3=B$15)*$B4:$M4)
說明:
①LOOKUP(COLUMN($B$2:$M$2),COLUMN($B$2:$M$2)/($B$2:$M$2<>""),$B$2:$M$2),將合并的1月、2月等拆分并填充為1月、1月、2月、2月等。
不理解的看《3種合并單元格查找》最后部分。
②SUBSTITUTE(①,"月",""),將1月、2月等變成1、2等。
③ROUNDUP(②/3,0),將其換算成季度數(shù)。
④ROUNDUP(COLUMN(A1)/2,0),生成季度數(shù)序列。
⑤SUMPRODUCT((③=④)*($B$3:$M$3=B$15)*$B4:$M4),多條件求和。
2.修改表格,用短公式。
(1)首先復(fù)制當(dāng)前的月份合并單元格粘貼到上方。
(2)將第2行取消合并并刪除文本,然后在B2中輸入公式=DATE(2023,ROUNDUP(COLUMN(A1)/2,0),1),并向右填充。
(3)使用格式刷將第一行合并單元格的格式應(yīng)用到日期上。注意修改數(shù)字格式為日期。
(4)選中B2:M2,按Ctrl+1自定義格式,代碼為M"月"。效果如圖:
(5)按季度匯總。
公式=SUMPRODUCT((ROUNDUP($B$2:$M$2/3,0)=ROUNDUP(COLUMN(A1)/2,0))*($B$3:$M$3=B$15)*$B4:$M4)
從今天該學(xué)員的問題,我們吸取經(jīng)驗(yàn):
(1)不要合并單元格;
(2)真要合并,那就弄一個(gè)假合并——看起來像,但實(shí)際沒有合并;
(3)日期一定要是數(shù)字格式的。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
版權(quán)申明:
本文作者小窩;部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!