1、统计不重复个数
统计不重复姓名的个数,输入公式:
=SUM(1/COUNTIF(A2:A14,A2:A14)),按Ctrl+Shift+Enter三键完成。
说明:1/COUNTIF(A2:A14,A2:A14):可以去掉重复值,假如只出现一个,那么1除以1就等于1,若是出现2个,那么1除以2等于1/2,所有求和则等于1,这样可以获取不重复个数。
2、多条件求和
如下图表格,求广州市A产品和深圳市B产品销售总额,输入公式:=SUM(SUMIFS(C2:C13,A2:A13,{'广州市','深圳市'},B2:B13,{'A','B'}))
说明:先用SUMIFS进行多条件求和,结果返回一组数组,最后结合SUM函数统计求和。
3、一对多查找
如下图表格,查找同一部门的所有人员信息。先添加辅助列,输入公式=B2&COUNTIF($B$2:B2,B2),再利用VLOOKUP函数来查找,输入公式:=IFERROR(VLOOKUP($B$13&ROW(A1),$A$2:$H$10,COLUMN(C:C),0),'')
4、多表数据查找
如下图表格,根据人员姓名查找1到6月份的所有销售业绩。输入公式:=IFERROR(VLOOKUP($B2,INDIRECT(C$1&'!B:C'),2,0),'')
5、多条件查找
如下图表格,根据姓名和月份查找对应销量。输入公式=INDEX(B2:H12,MATCH(C14,A2:A12,0),MATCH(C15,B1:H1,0))
说明:先用MATCH函数分别判断出姓名和月份的具体位置,再用INDEX函数返回指定行列交叉位置的数值。
6、合并单元格求和
如下图表格,统计每个销售组的销量总和,输入公式:
=SUM(C2:C15)-SUM(D3:D15),按Ctrl+Enter组合键填充 。
7、合并单元格计数
如下图表格,统计每个销售组销售员的人数,输入公式:
=COUNTA(C2:C15)-SUM(D3:D15),按Ctrl+Enter组合键填充。
8、统计人数
如下图表格,要统计每天的报考人数,输入公式并向下填充:
=LEN(B2)-LEN(SUBSTITUTE(B2,',',''))+1
说明:先用SUBSTITUTE函数将报考人员之间的逗号替换为空,注意要替换的逗号是中文状态下的,再用LEN函数计算删掉逗号之后的字符串长度。接着计算出包括逗号的字符串总长度,减去删除逗号的字符串长度再加上1,即为报考人数。
9、获取最后一个数据
如下图表格,获取最后一个销量数据,输入公式:=OFFSET(B$1,COUNTA(B$1:B15)-1,0)
公式说明:先用COUNTA函数计算非空单元格数量,再用OFFSET函数进行数据引用。
10、获取满足条件的多个数值
如下图表格,要查找指定销售区域的销售人员及销售业绩,输入公式:
=INDEX(B:B,SMALL(IF($A$1:$A$11=$E$2,ROW($A$1:$A$11),4^8),ROW(A1)))&'',向下向右填充。
说明:用到INDEX + SMALL + IF函数查找组合。
先用SMALL函数定位E2在A列中的位置(从小到大)
4^8这里指的是一个比较大的数,在IF函数公式中,如果单元格区域A1:A11中的值等于E2,就显示E2在A列中所在的行号,否则显示一个较大的数
利用SMALL函数得到行号之后,结合INDEX函数一对多查找需要的值
最后的&''是用来进行容错处理。
11、带单位数据求和
如下图表格,销售额数据带有单位,如何进行求和统计?输入公式:
=SUMPRODUCT(SUBSTITUTE(C2:C11,'元','')*1)&'元'
说明:用SUBSTITUTE(D2:D10,'元','')函数公式先将C列中的“元”全部替换为空值,乘以1将文本转换为数值,最后利用SUMPRODUCT函数求和。
12、指定名次数据求和
如下图表格,求排名前五的销量总和,输入公式:=SUM(LARGE(B2:B15,ROW(1:5))),按Ctrl+Shift+Enter三键输入。
来源:Excel技巧精选;作者:技巧妹
联系客服