二维码 购物车
部落窝在线教育欢迎您!

日期是文本与合并单元格的季度汇总

 

作者:小窝来源:部落窝教育发布时间:2023-11-18 18:26:37点击:500

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

日期是文本还是合并单元格,怎么按季度汇总?

 

学员:怎么按季度求和?

小窝:《用函数公式按季度或按周求和》教程都写着呢。

过了10分钟。

学员:教程不对,求不出来。怎么办?

小窝:教程不对?不可能啊……你把数据发过来看看呢。

收到发过来的数据,小窝一看,发现了问题:不但有合并单元格,日期还是文本。

 

 

这样的数据的确无法直接用小窝推荐的教程来求和。

小窝最终提供了两个方案:

1.不修改表格,用长长的公式进行条件求和

输入公式后向右向下填充。低版本需要按三键输入。

=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月等变成12等。

ROUNDUP(/3,0),将其换算成季度数。

ROUNDUP(COLUMN(A1)/2,0),生成季度数序列。

SUMPRODUCT((=)*($B$3:$M$3=B$15)*$B4:$M4),多条件求和。

 

2.修改表格,用短公式。

1)首先复制当前的月份合并单元格粘贴到上方。

 

 

2)将第2行取消合并并删除文本,然后在B2中输入公式=DATE(2023,ROUNDUP(COLUMN(A1)/2,0),1),并向右填充。

 

 

3)使用格式刷将第一行合并单元格的格式应用到日期上。注意修改数字格式为日期。

 

 

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)

 

 

从今天该学员的问题,我们吸取经验:

1)不要合并单元格;

2)真要合并,那就弄一个假合并——看起来像,但实际没有合并;

3)日期一定要是数字格式的。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何用函数公式按季度或按周求和

3种合并单元格查找

各种序列的生成用ROW函数

一文说完自定义格式

版权申明:

本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。