点击上方「 韩老师讲office 」可快速关注

敬请阅读与分享，让更多学习者一起进步！

问题来源

一位做电商数据分析的朋友说，他要对所管理的六个仓库的销售额进行对比统计，统计出每个仓库的最高与最低销售额。他有几万行的数据，韩老师简化到下面几行，以方便讲清楚统计公式。

关键操作

公式实现

在E2单元格输入公式：“=MAX(IF($A$2:$A$16=D2,$B$2:$B$16))”，按“Ctrl+Shift+Enter”结束，向下填充，即可计算出每个仓库的最高销售额。

公式解析

其中：

IF($A$2:$A$16=D2,$B$2:$B$16)

使用IF函数，判断$A$2:$A$16区域中每个单元格是不是等于 D2，如果等于 D2单元格指定的仓库，返回 $B$2:$B$16区域中对应 单元格内容，否则返回逻辑值FALSE(因IF第三个参数省略)。

所以该部分返回一个数组（数组1）：

{ 25215;FALSE;FALSE;FALSE;FALSE;3200;FALSE;FALSE;FALSE;FALSE;4521;FALSE;FALSE;FALSE;FALSE }

MAX(IF($A$2:$A$16=D2,$B$2:$B$16))

MAX函数忽略逻辑值FALSE，在 数组1中计算出最大值。

因为是数组计算，所以用“Ctrl+Shift+Enter”完成公式输入。

最低销售额计算

同理，在F2单元格中输入“=MIN(IF($A$2:$A$16=D2,$B$2:$B$16))”，按“Ctrl+Shift+Enter”结束，向下填充，即可计算出每个仓库的最低销售额。

互动

各位朋友，还有其他公式完成以上示例的计算吗？请留言哦！

素材下载

链接：http://pan.baidu/s/1cKXM3s

密码：hh7r

往期相关推荐

Excel | 员工姓名和业绩挤在一个单元格里，怎样统计业绩最大值

Excel | N多商品，计算相同商品对应数据的最大与最小值

EXCEL ｜ MAX函数：不规则合并单元格填充序号

我们经常在1些excel函数字字算计算公式2边看到加有大括孤号{},到底这大括孤号是什么神秘符号，今天蓝色很有必还能提前介绍1下。想学好函数字字的同学也1定还能耐心把下面的教程看完,以免以完教程里算计算公式看不懂。

先从11个简单的算算计算公式关于起：

=A1*B1

它的结果是20，回结果有1数字字目。

而如果给多数字字目与B1相乘，还能是什么结果呢？

=A1:A5*B1

结果是分别回11个相乘的结果值。即回的是1组值：20;40;50;60;30，这组数字字保存在电脑内存里。由于单元格无法同时显示多1个结果，所以显示是错误值。

如果给1列数字字与另1列数字字相乘是什么结果呢？

=A1:A5*B1:B5

结果是相对应的行1对1相乘，几行数字字还能回几1个结果：20;08;35;408;27

关于了这么多，同学们只需还能了解：excel里的算完回值的数字字目有2种：1数字字目 与 1组数字字。

那么，如果11个算计算公式里有回1组数字字的表达式时，就需还能以数字字组算方法。即在算计算公式完按ctrl+shift+enter三键自动加大括孤号{}。当然也有例外，象lookup、sumproduct函数字字就还能直接运行数字字组算，而不还能加大括孤号。

关于到这里有些同学还是有些迷惑，这倒底作以，蓝色下面举21个小例子。

【例1】如下图所示表销售统计表里，标准按照销售数字字量，算所有者提成之与（提成 十元/1个）

如果以1般的方法，算计算公式应该是：

=2*十+4*十+5*十+6*十+3*十=200

以数字字组方法：

{=SUM(B2:B6*十)}

套以开始的理论，因是B2:B6*十算完回多1个结果，所以算计算公式还能加大括孤号。

【例2】计划B2：B2区域总共有多少字数字字。

算计算公式：{=SUM(LEN(B2:B4))}

len(B2:B4)还能回每个1个单元格的字符数字字，回的是1组数字字，所以该算计算公式也还能加大括孤号。

蓝色关于：通过今天的教程，同学们还能大概知道，在什么情况下加大括孤号即可。给数字字组算计算公式，在以完蓝色将还能有比较深入的介绍。

1.如果固定资产使用60个月，到第61个月的时候，能否让“使用月份”一列，不超过60个月。（也就是不超过最长使用期限）

普通的解决办法是嵌套一个IF函数进行判断是否超过60个月，如果超过就返回60，否则返回实际月份。其实，嵌套一个MIN函数会更完美，公式更加简洁。

=MIN(60,DATEDIF(B3,$D$1+1,"m"))

2.下个月的第一天公式怎么写呢？

上个月最后一天的公式：

=EOMONTH(TODAY(),-1)

这个月最后一天的公式：

=EOMONTH(TODAY(),0)

下个月第一天的公式：

=EOMONTH(TODAY(),0)+1

以上公式，都需要将单元格设置为日期格式。学习的过程中，要学会举一反三，多动下脑筋，很多问题就会迎刃而解。

3.计算某个日期下个季度的15号，比如日期是1月1日到3月31日的日期，计算出来的时间就是4月15日。

这个难吗？说真的，不懂思路很难。不过经过几分钟时间的思考，思路已经出来了。

先判断日期的季度，每3个月为一个季度，这个很好理解。

=ROUNDUP(MONTH(A2)/3,0)

第一季度的日期返回4月15日，第二季度的日期返回7月15日，第三季度的日期返回10月15日，第四季度的日期返回1月15日。也就是说每个季度的日期在最开始1月15日的基础上加3个月。

=EDATE("1-15",ROUNDUP(MONTH(A2)/3,0)*3)

在用Excel统计、计算商品的销售金额、销售量、生产成本等数据时，经过公式计算后显示出来的的结果都是数值，其单位可能是元、万元、公斤、吨、万吨等，在把相关报表拿给客户或者领导查看时，如果表格较大、数据较多，可能会让客户或者领导对表中数据的具体单位产生疑问。此时可以考虑让Excel自动在数值后添加中文的单位。这里以Excel2007为例介绍两种添加方法，以供参考。

方法一：

这种方法相对简单，适用于给已有的数值添加中文单位。

●例如要给下图表格中“销量”列中的数值添加中文单位“公斤”，给“销售额”列中的数值添加单位“万元”。

●先在销量列右侧插入两列空行，其中一列的列首单元格中输入要添加的单位，本例中即“公斤”，然后用下拉填充柄或者复制粘贴等方法在该列的所有单元格中都输入“公斤”。

●在另一个列的列首单元格中输入一个等号。

●用鼠标点击选择要添加单位的数值所在的单元格。

●这样，列首单元格的等号后就会自动输入单元格名称，然后再输入一个字符“&”（按住键盘的Shift键的同时按键盘上方的数字7键即可输入），再用鼠标点击中文单位“公斤”所在的单元格。则本例中的编辑栏会完成输入“=C3&D3”。

●再按键盘的回车键，该单元格中就会显示出数值加中文单位。

●然后在用下拉填充柄或者选择性粘贴公式等方法完成该列中其他单元格的输入。

●这时我们可以选中包含数值和中文单位的两列，然后点击鼠标右键，选择右键菜单中的“隐藏”，把这两列隐藏起来。

●这样，销量列就完成中文单位“公斤”的自动添加了。

●再用同样的方法完成销售额列中单位“万元”的添加，则该表格中的数值就都加上中文单位了。

●如果需要经常改变表格中的数值，还可以把最终显示的列和数值及中文单位所在的列分别保存在两个表格中，这样当数值改变时，自动添加中文单位的列中的数值也会自动随之改变。打印报表时只选择打印包含中文单位的表格即可。

方法二

我们还可以利用Excel中的“TEXT”函数来完成中文单位的自动添加。TEXT 函数可以将数字转换为文本，可以指定数值的小数点后保留数字的个数。还可以将数字与文本或符号相组合。而且TEXT 函数中还可以嵌套公式，可以自动在公式的计算结果后加上中文的单位。

●例如给方法一中的表格数据添加中文单位，可以先在销量列右侧插入一个空列，然后在列首单元格中输入=TEXT(C3,"0")&"万元"，其中的“C3”就是要添加中文单位的数值所在的单元格，“0”表示小数点后不保留数字“&“万元””表示在数值后添加“万元”。

●输入TEXT函数后，按键盘回车键，该单元格中就会显示出添加了中文单位的数值。

●如果显示的数值中要保留两位小数，则可以参考下图中的参数。输入=TEXT(E3,"0.00")&"万元"，其中的"0.00"即表示保留两位小数。

●这样，按键盘回车键后，该单元格中的数值就保留两位小数了。

●TEXT 函数中还可以嵌套公式，自动给公式的计算结果添加中文单位。例如要根据下图表格中的销量和单价计算出总销售额，并且自动给公式的计算结果添加中文单位“万元”。则可以在总销售额列中输入=TEXT(C3*D3,"0.00")&"万元"。其中的“C3*D3”就是计算公式，"0.00"即表示保留两位小数，“&“万元””表示在数值后添加“万元”。

●按键盘回车键后，该单元格中就会显示出公式的计算结果并自动添加“万元”。

●之后仍然可以用下拉填充柄或者选择性粘贴公式等方法完成该列中其他单元格的输入。

但要注意的是：TEXT 函数会将数字转换成文本，这可能导致其结果不能在其他的计算中加以引用，所以TEXT函数最好用在显示最终结果上。

转载自百家号作者：KUSO课堂

财务在日常工作中经常会使用Excel表格，处理大量的数据，比如人员工资、个人所得税等等，下面来介绍五个财务人员常用到的工资计算公式，简单又高效：

1、个人所得税计算公式：

=ROUND(MAX((I3-3500)*

{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-

{0,105,555,1005,2755,5505,13505},0),2)

2、根据实发工资，倒推税前工资：

=IF(K3<=3500,K3,ROUND(MAX((K3-3500-5*

{0,21,111,201,551,1101,2701})/(1-5%*

{0.6,2,4,5,6,7,9}))+3500,2))

3、根据实发工资，获取大写金额：

=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(K3)),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(K3),2),"[dbnum2]0角0分;;"&IF(ABS(K3)>1%,"整",)),"零角",IF(ABS(K3)<1,,"零")),"零分","整")

4、根据期限和调整日期，查询利率：

数组公式，按Ctrl+Shift+Enter三键结束。

=INDEX(C:C,MAX(IF(($A$3:$A$10=E3)*

($B$3:$B$10<=F3),ROW($3:$10))))

5、获取每个日期的时间段：

=LOOKUP(DATEDIF(B2,TODAY(),"m"),{0,1,2,3,6,12},{"不足1个月","1-2个月","2-3个月","3-6个月","6-12个月","1年以上"})

1、最快求和：选择区域，按快捷键ALT+=，瞬间完成。2、累计求和：=SUM(B$2:B2)=N(C1)+B2N是最简单的函数，就是将文本转换成0。跟这个类似的是T，将数字转换成空文本。 =N(C1) =T(C1)3、跨列求和：=SUM(A2:F7)。

4、单条件求和：统计销售额大于500的人的总销售额=SUMIF(B:B,">500",B:B)=SUMIF(B:B,">500")。5、避开错误值求和：A列的数字中包含错误值，如何避开这些错误值对数字求和=SUMIF(A:A,"<9E+307")。9E+307是接近最大的数字，而错误值比最大的数字还大，小于最大值的数字，就能对数字进行求和。6、通配符求和：统计最后字符为河营业部的总金额=SUMIF(A2:A11,"*河",F2:F11)。 通配符有2个，*代表全部字符，?代表一个字符。如果是2个字符，最后一个字是河，可用"?河"。7、多区域条件求和：多个相同的区域，统计型号为B03的数量=SUMIF(A2:G10,"B03",B2:H10)。8、乘积求和：获取数量*单价的总金额=SUMPRODUCT(B2:B4,C2:C4)。9、多条件求和：统计营业部为天河，数量大于20的总金额=SUMIFS(F2:F11,A2:A11,"天河",D2:D11,">20")。10、统计每个月的金额：根据每天的销售明细，统计每个月的金额=SUMPRODUCT((MONTH($A$2:$A$11)=F2)*$D$2:$D$11)。 MONTH函数就是获取月份。

公司要求把近三年的销售额按照年份与月份进行汇总，三年的销售数据有四万多行，有没有函数可以实现快速统计？

下图所示的简单数据，讲一下汇总方法：

数据

关键操作

公式：

在F2单元格输入公式：

=SUMPRODUCT((YEAR($A$2:$A$15)=D2)*(MONTH($A$2:$A$15)=E2)*($B$2:$B$15))，向下填充，即得所有指定年份与月份的销售总额。

公式分解：

(YEAR($A$2:$A$15)=D2)：

YEAR函数计算$A$2:$A$15单元格的年份，并与D2单元格的年份进行比较，如果等于D2年份，返回TURE，否则返回FALSE。所以此部分返回一组TURE与FALSE的数组（数组1）：

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}。

(MONTH($A$2:$A$15)=E2)：

MONTH函数计算$A$2:$A$15单元格的月份，并与E2单元格的月份进行比较，如果等于E2月份，返回TURE，否则返回FALSE。所以此部分返回一组TURE与FALSE的数组（数组2）：

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}。

($B$2:$B$15)：

{182865;78980;217647;151397;118792;172058;1818;42762;51971;12345;46367;151659;184879;62688}。

=SUMPRODUCT((YEAR($A$2:$A$15)=D2)*(MONTH($A$2:$A$15)=E2)*($B$2:$B$15))：

相当于=SUMPRODUCT（(数组1)*(数组2)*($B$2:$B$15))，数组1、数组2与($B$2:$B$15)对应位置相乘然后相加和。

结果：

Excel中的函数公式非常的强大，全部掌握很显然是不现实的，但是对常用的Excel函数公式，我们必须掌握。

一、COUNTIFS函数：多条件计数。

目的：计算上海地区销售额>=50的人数。

方法：

在目标单元格中输入公式：=COUNTIFS(C3:C9,">=50",E3:E9,"上海")。

解读：COUNTIFS函数是多条件计数函数。其语法结构为：=COUNTIFS（条件区域1，条件1，条件区域2，条件2,……条件区域N，条件N）。

二、SUMIFS函数：多条件求和。

目的：

1、计算“上海”地区“男”销售员的销售总额。

2、计算“上海”和“北京”地区“男”销售员的销售总额。

方法：

1、在目标单元格中输入公式：=SUMIFS(C3:C9,D3:D9,"男",E3:E9,"上海")。

2、在目标单元格中输入公式：=SUM(SUMIFS(C3:C9,D3:D9,"男",E3:E9,{"上海","北京"}))。

解读：SUMIFS函数是多条件求和函数。其语法结构为：=SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2……条件区域N，条件N）。如果同一区域中有多个条件，我们就采用公式：=SUM(SUMIFS(求和区域，条件区域1，条件1，条件区域2，{条件2-1，条件2-2,……条件2-N}……条件区域N，条件N))来计算。因为公式SUMIFS(求和区域，条件区域1，条件1，条件区域2，{条件2-1，条件2-2,……条件2-N}……条件区域N，条件N)返回的是一个数组，所以还需要用SUM函数二次求和。

三、VLOOKUP:多层级条件查找。

目的：根据指定的成绩等级表查询实际的等级。

方法：

在目标单元格中输入公式：=IF(D3>=60,VLOOKUP(D3,$A$3:$B$6,2),"不及格")。

解读：

1、从等级表中我们可以看出没有给出小于60分的情况，所以我们要判断具体的成绩，当成绩大于等于60分时，执行VLOOKUP(D3,$A$3:$B$6,2)，否则返回“不及格”。

2、公式：VLOOKUP(D3,$A$3:$B$6,2)是返回D3在范围$A$3:$B$6中的第2列的对应值。而且必须是模糊查询，这就是省略第四个参数的原因。

四、SUMIF:隔列分类汇总。

目的：统计出1-4月的计划数和实际完成数。

方法：

在目标单元格中输入公式：=SUMIF($C$3:$J$3,K$3,$C4:$J10)。

解读：

SUMIF函数的语法结构：=SUMIF(条件范围，条件，求和范围)。上表中，我们要求和的条件范围为：$C$3:$J$3，条件范围固定不变，所以用绝对引用。条件为“计划”和“实际”，所以用K$3。求和范围为$C4:$J10。

五、VLOOKUP+MATCH:灵活的多列查找。

目的：查找相应人员的所有成绩。

方法：

在目标单元格中输入公式：=VLOOKUP($H$3,$B$3:$F$9,MATCH(I$2,$B$2:$F$2,0),0)。

解读：

1、VLOOKUP函数的语法结构为：=VLOOKUP(查找值，查找范围，返回第X列的值，查找匹配模式）。返回查找值在第X列对应的值。

2、MATCH函数的语法结构为：=MATCH(定位置，定位的范围，匹配模式）。作用是返回查找值相对应的位置。

3、用MATCH(I$2,$B$2:$F$2,0)来定位学科在$B$3:$F$9中的相对位置。然后用VLOOKUP函数返回对应的值。

喜欢、有用就点点关注

函数COUNT家族有5个成员，今天先介绍其中的三个家庭成员，它们分别是COUNT、COUNTA、COUNTBLANK。

一、函数定义

COUNT：计算区域中数字的单元格个数。COUNTA：计算区域中非空单元格的个数。COUNTBLANK：计算区域中空单元格的个数。

二、函数实例

▲

计算有数字的单元格个数

01

公式：=COUNT(A3:A11)不能转换为数字的文本、空白单元格、逻辑值、错误值都不计算在内。

计算非空单元格的个数

02

公式：=COUNTA(A17:A25)参数值可以是任何类型,包括空字符(""),但不包括空白单元格。

计算空单元格的个数

03

公式：=COUNTBLANK(A31:A39)空白单元格和空文本（""）会被计算在内。

求成绩大于等于60的个数

04

公式：=COUNT(0^(B45:B51>=60))按三键结束(B45:B51>=60)部分成立的返回TRUE,不成立的返回FALSE。发生四则运算时TRUE相当于1，FALSE相当于0，利用0的任何次方等于0，0的1次方返回错误值的特性，将(B45:B51>=60)部分作为0的次方，得到{0;#NUM!;#NUM!;0;#NUM!;0;#NUM!}，COUNT函数忽略错误值只统计有数字的单元格个数。

求缺勤率

05

公式：=COUNTBLANK(B57:B63)/COUNTA(B57:B63&"")，记得带上花括号哦！先用COUNTBLANK函数算出未打卡的人数为2，因为有两个空白单元格，所以用COUNTA计算时加"",算出总人数为7，然后相除。

添加序号

06

在A69单元格输入公式：=IF(B69="","",COUNTA(B$69:B69))，向下填充。COUNTA(B$69:B69)部分是统计不为空的单元格个数，注意引用方式；然后用IF函数判断是不是空，为空就返回空，否则返回COUNTA函数计算的个数。

合并单元格添加序号

07

COUNTA法:选中区域A81:A87输入公式=COUNTA(A$80:A80)按Ctrl+Enter键结束

COUNT法:选中区域B81:B87输入公式=COUNT(B$80:B80)+1按Ctrl+Enter键结束

求平均销售额

08

在F93单元格输入公式：=SUM(B93:E93)/COUNT(B93:E93)，向下填充。先用SUM函数求和算出总销售额，然后用COUNT算出有销售额的数目，再用SUM/COUNT得到平均销售额。

三、函数总结

1、COUNT：计算区域中数字的单元格个数。

①如果参数为数字、日期或者代表数字的文本，则将被计算在内；②逻辑值和直接键入到参数列表中代表数字的文本被计算在内；③如果参数为错误值或不能转换为数字的文本，则不会被计算在内；④如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将不计算在内。

2、COUNTA：计算区域中非空单元格的个数。

①参数值可以是任何类型,可以包括空字符(""),但不包括空白单元格；②如果参数是数组或单元格引用,则数组或引用中的空白单元格将被忽略；③如果不需要统计逻辑值、文字或错误值,请使用函数COUNT。

3、COUNTBLANK：计算区域中空单元格的个数。

①包含返回 ""（空文本）的公式的单元格会计算在内；②包含零值的单元格不计算在内。

爱上Excel合伙人2017出品

我们一直秉承简洁、优雅、高效的为读者分享工作中遇到的每一个Excel问题，不论是Excel技巧、函数、图表、VBA，甚至是有关于Excel的开发，只要你能提出来问题，我们总能给你一个满意的答案！

每天准时来一篇Excel在职场中的案例

销售薪资受行业影响，一向都是多劳多得，与销售额挂钩。即：

销售总工资=基础工资+销售额*提成比例

很多人事财务习惯使用Excel手动计算销售工资，操作麻烦易出错。

本篇通过简道云搭建简便的计算表，自动累计销售额、计算销售总工资，减轻核算负担！

搭建步骤如下：

（1）制作销售表，录入每日销售额

（2）汇总计算每月每人销售总额

（3）制作工资表

（4）调用每月销售额并计算提成

移动端效果图：工资表、销售表录入

销售表：录入销售数据

制作好“销售表”后，录入或导入销售数据。

ps. 通过 “日期转文本”，可以提取销售日期的年月，作为聚合表的辅助字段，以汇总每年每月的销售数据。

聚合表：计算每月销售额

聚合表的数据来源为“销售表”，把“销售员”和“日期转文本”两个字段添加到行表头中，数据会按照这两个条件进行分组。

接下来把销售额添加到指标中，聚合表就会自动汇总每个销售员每个月的销售总额。

工资表

月份通过编写公式能够自动获取当前年月，销售员可以根据月份调取出“销售表”中对应的姓名。

调用每月销售额并计算提成

以“年月”和“销售员”为条件，调用出聚合表中对应的销售额，可以计算提成。

编写提成公式

提成系数：若销售额低于5000，提成系数为0.2；若销售额低于10000，提成系数为0.3；若销售额不低于10000，提成系数为0.4。

表单优化

如果不想被填表人看到，可以不勾选“可见”。

如果不可见，那么同时一定要设置表单属性 > 不可见字段赋值 >始终重新计算，该字段才能正常计算。

受Excel折磨的你，试试这套系统吧。成果分享：点击“了解更多”。