易库易 发表于 2017-9-14 14:36:14

28个Excel技巧,3秒完成采购报表!(二)

前几天小易分享了《17个excel技巧,3秒完成采购报表!(一)》,非常的强大,获得了采购小伙伴们的大量收藏与转发。所以今天接着分享采购爱用的excel小技巧(二)
采购报表-SUM函数
很多采购童鞋在做报表时一定会用到SUM函数来求和。但是SUM函数还有多种求和方式。
1.如下图所示,要求在**单元格设置求和公式传统做法是设置一个SUM公式,然后复制复制。其实只需要按CTRL+G定位空单元格,然后点一下∑(或按住“ALT”和“=”)

2.设置总计公式
如下图所示,在设置总计公式时,不需要用小计1+小2+小3...了,只需要设置公式:
=SUM(B2:B14)/2

3.多表求和
栗:合计从1日到31日的所有B2单元格之和。
=SUM(1日:31日!B2)
4.多条件求和
虽然不是SUM函数的强项,但可以给一个小攻是
{=SUM((a1:a100=“A产品”)b1:b100)}

5.合并单元格求和
合计单元格的求和是一个难点,如果用一般的方法公式很复杂,如果用SUM函数的倒减法,就会变简单=SUM(C2:C13)-SUM(D3:D13)
在输入公式时不能拖动复制,需要选取D2:D13,先在D3输入公式,然后把光标放在公式结尾处,按ctrl+回车键批量输入
6.任意多表求和如果N个不相邻的表的同一个单元格求和,不能直接用SUM,需要用SUM(SUM(结构,这里等同与SUM(N,提取三维引用的单元格求和)=SUM(SUM(INDIRECT({1,2,3}&“!A1”)))
=SUM(N(INDIRECT({1,2,3}&“!A1”)))采购报表-Countif函数如果评工作中最常用的函数是哪个,Vlookup函数是采购公认的NO.1函数,但它只能用于查找。在Excel中还有一个函数比它更有用,是Excel中最重要的一个函数。那就是Countif函数——


1.精确统计
1)返回A列包含值12的单元格数量
=COUNTIF (A:A,12)
2)返回A列部门为“采购部”的数量=COUNTIF(A:A,“采购部”)字符在公式中需要加双引号
3)返回包含值;逻辑值为TRUE的单元格数量=COUNTIF(A:A,TRUE)
4)返回A1:A10空单元格的数量=COUNTIF(A1:A10,“=”)
5)返回A列非空单元格的个数(相当于counta函数)=COUNTIF(A1:A10,“<>”)
6)返回A1:A10区域,真空+假空的数量=COUNTIF(A1:A10,“”)
7)返回A1:A10区域所有单元格的数量(非空+空值)=COUNTIF(data,“<>“””)
8)返回A1:A10假空单元格数量
=COUNTIF(A2:A32,“”)-COUNTIF(A2:A32,“=”)真空+假空-真空=假空
9)统计A列身份证号410105198504182965出现的数量=COUNTIF(A:A,“410105498504182965*”)默认情况下,countif函数对数字只识别前15位,而带上通配符*后可以识别其为文本型格式。
2.模糊计数1)返回A列单元格包含“采购部”的数量(如:A公司采购部)=COUNTIF(A:A,“*采购部*”)
2)返回包含以单词“AB”(部分大小写)开头内容的单元格数量=COUNTIF(A:A,“AB*”)通配符*是表示任意多个字符,放在字符后面表示,该字符后可以有任意多个字符
3)返回A列单元格中5个字符的数量=COUNTIF(A:A,“?????”)通配符?表示占用一个字符位置,五个?表示五位字符长度
4)返回包含文本内容的单元格数量=COUNTIF(A:A,“*”)
3.区间统计1)返回包含负值的单元格数量=COUNTIF(A:A,“<0”)
2)返回不等于0的单元格数量=COUNTIF(A:A,“<>0”)
3)返回大于5的单元格数量=COUNTIF(A:A,“>5”)
4)返回大于单元格A1中内容的单元格数量=COUNTIF(DATA,“>”&A1)在引用单元格值时,一定要用&链接,千万不要表示为:“>A1”
5)返回>10并且<=15的数量=SUM(COUNTIF(A1:A10,“>”&{10,15})*{1,-1}){}表示常量数组,数组的值可以在公式内分别进行运算,每次运算返回一个值。*{1,-1}的目的是上前面的计算结果一个为正,一个为负,用sum求和时达到相减的目的。
6)返回>=10并且<=15的数量=SUM(COUNTIF(A1:A10,{“>=10”,“>15”})*{1,-1})
7)返回包含值为3或-3的单元格数量
=SUM(COUNTIF(A1:A10,{10,-3}))
4.引用函数结果统计1)返回包含当前日期的单元格数量=COUNTIF(A:A,TODAY())
2)返回大于平均值的单元格数量=COUNTIF(A:A,“>”&AVERAGE(A:A))
28个Excel技巧,3秒完成采购报表!(二)完
后续将持续更新,敬请期待~
文中excel公式及GIF图已获Excel精英培训授权使用由易库易小易重新收集整理




页: [1]
查看完整版本: 28个Excel技巧,3秒完成采购报表!(二)