实现表格动态求和与累计求和

2020-09-12 14:22:28 电脑爱好者 2020年15期

马震安

我们以如图所示的按月销售表为例(图1)。首先,制作“姓名”、“起始月”、“结止月”等处的下拉菜单。选定F14单元格,切换到“数据”选项卡,点击“数据验证→数据验证”,在弹出窗口的“设置”选项卡下,在“验证条件”的“允许”处选择“序列”,“来源”处选择A2:A11单元格区域,这样,当点击F14单元格时就会出现关于姓名的下拉菜单。同样,在F15、F16单元格也进行数据验证设置,“来源”处选择B1:M1单元格区域,这样,点击F15、F16单元格时就会出现关于月份的下拉菜单。

接下来,为了让所选范围在原数据区域显而易见,可对原数据区域进行条件格式设置,用颜色标定出所选区域。选定A2:A11区域,切换到“开始”选项卡,点击“条件格式→突出显示单元格规则→等于”,在弹出“等于”窗口的“为等于以下值的单元格设置格式”处选择F14单元格,再设置好所需要的颜色,这样,在A2:A11单元格区域按所设置颜色突出显示F14单元格所显示的姓名。选定B1:M11单元格区域,点击“条件格式→新建规则”,在弹出的窗口选择“使用公式确定要设置格式的单元格”,在“为符合此公式的值设置格式”处输入“=AND($A2=$F$14,AND(B$1>=$F$15,B$1<=$F$16))”,设置所需要的格式,这样,在B1:M11单元格区域按所设置起、止月份突出显示所对应的销售数据(图2)。

最后,在“合计”对应的單元格中输入公式“=SUM(OFFSET($A$1,MATCH($F$14,$A$2:$A$11,0),MATCH($F$15,$B$1:$M$1,0),1,INT(SUBSTITUTE($F$16,"月",""))-INT(SUBSTITUTE($F$15,"月",""))+1))”,这样就能实现按月份动态求和、累计求和了(图3)。