平时我们经常使用Excel统计数据,如果统计的品类较多,在查询统计结果时就需要在列表中进行费力地查找和排序。以下面的表格为例,如果要统计PVC-1产品销售和,因为有多个部门销售,求和就需要先对产品进行排序,然后再求和。重新排序后会破坏原来表格的排列,而且每查询一个产品还需要重新排序、求和,操作起来十分不便。现在我们可以使用“数据验证的下拉列表 统计求和”的方式解决这个问题,这样只要在下拉列表选择品类即可快速看到统计结果(图1)。

统计员常用excel技巧(Excel数据汇报即拉即统计)(1)

图1 下拉 统计示例

从示例图表可以看到,这个组合主要是由“数据验证” “求和”组成,因此要实现这一效果,我们需根据原始数据将对应的类别整合在“数据有效性”下拉列表中,同时将对应数值的和统计出来。

首先设置数据有效性下拉列表,因为这里有多个部门销售同一种产品(比如销售一部和销售二部都有销售PVC-1)。为了方便进行筛选,先将表转化为动态表格,全选表格内容后,点击“插入→表格”,切换到“表格工具→设计”,勾选其中的“标题行”、“镶边行”、“筛选按钮”(图2)。

统计员常用excel技巧(Excel数据汇报即拉即统计)(2)

图2 转换表格

接着复制D2:D25单元格中的内容到M2:M25单元格,在M1单元格中输入“序列”,选中M2:M25单元格中的内容,点击菜单栏的“数据→删除重复项”,在打开的窗口中勾选“全选”和“数据包含标题”,点击“确定”(图3)。

统计员常用excel技巧(Excel数据汇报即拉即统计)(3)

图3 删除重复产品

经过上面的操作后,重复产品的数据会自动删除,只保留其中唯一的产品值,这些数值就可以作为数据有效性的序列数据了(图4)。

统计员常用excel技巧(Excel数据汇报即拉即统计)(4)

图4 保留唯一产品值

定位到J1单元格中输入“选择查询产品”,K1单元格中输入“销售额”,接着定位到J2单元格,点击菜单栏的“数据→数据验证→设置”,在允许列表中选择“序列”,在“来源”后面点击数据源,接着选择“M2:M6”数据(即上述去重后的序列数据),点击“确定”完成设置(图5)。

统计员常用excel技巧(Excel数据汇报即拉即统计)(5)

图5 数据验证设置

现在从J2单元格展开下拉列表中就可以依次选择上述的产品内容了。接下来就要在K2单元格中设置求和数值。求和借助SUMIF函数完成,定位到K2单元格中输入公式“=SUMIF(表1[产品],J2,表1[金额])”,当我们在J2单元格的下拉列表中选择产品时,在K2单元格中就会自动显示对应的金额,如此一来查询数据明显方便了很多(图6)。

统计员常用excel技巧(Excel数据汇报即拉即统计)(6)

图6 求和函数设置

小提示:

上述公式中,“表1[产品]”参数表示的是求和的条件范围是在“表1的产品字段列”,这里的“表1”是上述执行“插入→表格”操作中动态表格默认的名称(切换到“表格工具→设计→表名称”,可以自定义设置)。参数“J2” 表示条件,求和的条件是在产品序列里J2显示的指定产品(随着下拉列表的选择会动态进行变化,求和条件也同步进行变化)。参数“表1[金额]”求和范围是“表1的产品金额列”,即在J2选择产品后对H列对应产品的金额进行求和。

因为我们使用的是动态表格(求和条件和范围是通过表格的字段来设置),完成上述设置后,以后如果需要添加数据,比如在A26:H26单元格中增加了PVC-6的销售数据,那么K2单元格中的求和也会同步发生变化。

SUMIF是单条件的求和,如果是多条件的求和,我们还可以借助SUMIFS来完成。假设现在需要同时查询部门和指定产品的销售数据和,如查询销售一部的PVC-1销售数据。同上在I1单元格中输入“部门查询”,在I2单元格中再设置一个数据有效性验证序列(序列的内容为销售一部到销售三部)。定位到K2单元格输入函数“=SUMIFS(表1[金额],表1[部门],I2,表1[产品],J2)”,即可同时对部门和产品两个条件进行查询(图7)。

统计员常用excel技巧(Excel数据汇报即拉即统计)(7)

图7 多条件求和查询

小提示:

参数“表1[金额]”表示“求和的范围”是表1[金额]字段下的数值,参数“表1[部门]”表示条件的范围是[部门]字段,参数“I2”表示求和的条件是单元格显示的具体部门,参数“表1[产品],J2”则分别对应范围是[产品]字段,求和条件是“J2”显示的产品名称。如果有多个条件,继续添加“条件范围”、“条件”参数即可,比如可以添加“表1[订单ID],N2”,增加产品对应的订单ID的查询。

如果部门和产品很多,可以进入“数据→数据验证→设置”,在允许列表中选择“任意数值”,这样只要在I2和K2单元格中自行输入部门和产品数值即可进行查询。如果统计的报表很多,我们可以新建一个工作表专门用于查询,同上在每个原来有数据的工作表中插入动态表,比如在Sheet2中插入“表2”,依此类推。那么只要在“查询表”中的C3单元格中输入公式“=SUMIFS(表2[金额],表2[部门],A3,表2[产品],B3)”(需要查询哪张表格数据,这里就将表名称和字段修改为对应的名称即可),就可以在一张专用表中非常方便地查询其他工作表的所有数据了(图8)。

统计员常用excel技巧(Excel数据汇报即拉即统计)(8)

图8 专门查询表