Excel Home
全球知名的Excel资源网站之一

汇总全年报表绝招,让老板刮目相看

ExcelHome阅读(964)

先看数据源,是12个月的销售记录,分别存放在不同的工作表里。

现在,咱们要汇总出每个客户在各个月份的业务金额。

接下来咱们以Excel 2016为例,说说具体的操作步骤:

步骤一:

依次单击【数据】→【获取数据】→【自文件】→【从工作簿】,找到存放数据的文件,双击导入。

按下图步骤加载到数据查询编辑器。

步骤二:

单击Data列,右键→删除其他列,然后按下图步骤展开数据。

步骤三:

将日期转换为月份名称,删除暂时用不到的订单号所在列。

步骤四:

单击日期所在列,单击【转换】选项卡下的【透视列】,值列选择“金额”,最后将文件上载到工作表中,OK了。

此方法一劳永逸,如果数据源发生了变化,只要保存一下,然后在汇总表中右键刷新,就可以获取最新的汇总结果了。

今天的练习文件在此:
https://pan.baidu.com/s/1K6lLGxdNf2cgy0Td1UACuQ

听说有人靠这一个函数打下了Excel半壁江山?

ExcelHome阅读(1993)

今天给大家分享一个很常用也很实用的函数:SUMPRODUCT。众所周知,条件求和与计数是表格使用者最经常碰到的两个问题,而该函数不但集合了条件求和与计数两大功能于一身;还可用于复杂场景下的排名处理,甚至听说有人靠它一个函数就打下了Excel半壁江山……于是便不可不学了。

 

基础语法

先来看基础语法,SUMPRODUCT的官方语法说明是在给定的几组数组(array)中,将数组间对应的元素相乘,并返回乘积之和。语法格式如下:
=SUMPRODUCT(array1,array2,array3, …)

——SUM是求和的意思,PRODUCT是相乘的意思,参数之间相乘之后再求和,你看,SUMPRODUCT确实人如其名了。

看我手,歪、图、斯瑞……总结起来,SUMPRODUCT函数具有以下三个特点:

1> 它本身默认执行数组运算。
2> 它会将参数中非数值型的数组元素作为0处理。
3> 参数必须有相同的尺寸,否则返回错误值。

 

特点解析

看完了SUMPRODUCT的简历,想必很多朋友是雾里看花,仅仅对它有个模糊的认知,它的这些特点是啥意思?它到底能够胜任什么样子的工作?其实并不了然。

打个响指,我举几个例子。

如上图所示的数据表,C列是商品单价,D列是销售数量,现在需要在C9单元格计算销售总额。

C9输入以下公式,即可得出结果11620.60
=SUMPRODUCT(C3:C7, D3:D7)

这便是一个简单的SUMPRODUCT函数了。它的运算过程是:C3:C7和D3:D7两个区域数组内的元素分别相乘,也就是C3*D3,C4*D4,C5*D5……直至C7*D7

等于先将每个商品的销售金额计算出来,最后汇总求和。

由于SUMPRODUCT函数第一个特点,本身是支持数组间运算的,所以虽然该公式执行了多项运算,但并不需要按数组三键<Ctrl+Shift+Enter>结束公式输入。

有的朋友说啦,公式也可以写成这样:
=SUMPRODUCT(C3:C7*D3:D7)

或者使用以下数组公式,也是可以的。
=SUM(C3:C7*D3:D7)

那么这三个公式之间有什么区别呢?
首先,大部分情况下,SUMPRODUCT函数都不需要数组三键结束公式输入即可执行数组运算,而SUM函数是需要的。
其次,就要说到SUMPRODUCT函数另一个非常重要的特点了。

……

我们将上面的表稍做改动,将“钢笔”的销售数量更改为:暂未统计。同样需要在C9单元格计算销售总额。

这时候,如果使用公式:
=SUMPRODUCT(C3:C7*D3:D7)

或者数组公式:
=SUM(C3:C7*D3:D7)

都将返回错误值#VALUE!
返回错误值的原因在于D4单元格“暂未统计”为文本值,文本值是无法直接参与数学运算的,于是C4*D4返回错误值#VALUE!,进而造成整个公式的结果返回错误值。
而使用以下公式就没有这方面的困扰,会直接返回正确结果:
=SUMPRODUCT(C3:C7,D3:D7)
这便是SUMPRODUCT函数的第二个特点:将非数值型的数组元素作为0处理。

以该示例来说,D4单元格的值“暂未统计”为文本,并非数值,SUMPRODUCT将其主动视为零,于是C4*D4,结果亦为零,其余数组元素照常计算,得出11385.60的结果。
需要特别说明的是,SUMPRODUCT将非数值型的数组元素作为0处理,所谓的非数值型数组元素,包含逻辑值、文本,但并不包含错误值,如果数组元素中包含错误值,该公式亦返回错误值,比如该示例的第一条公式。
……

说完了SUMPRODUCT函数的两个特点,我们就再来聊聊它的第三个特点:数组参数必须有相同的尺寸,否则返回错误值。
我们依然用上述图片的例题为例,继续计算商品的销售总额。如果我们在C9输入公式:
=SUMPRODUCT(C3:C7,D3:D6)

结果会是怎么样的呢?
错误值:#VALUE!

为什么?
细心的你肯定已经注意到了,两个区域数组,C3:C7明显显比D3:D6多了一个元素,C3和D3结对子,C4和D4结对子……那么C7和谁结对子呢?女人们都嫁了,结果剩下一个光棍,这日子没法过了!一个萝卜一个坑,只有萝卜没有坑,这不是要萝卜死吗?
——于是SUMPRODUCT就不高兴了,它给你一个错误值#VALUE!,明确告诉你,和谐时代幸福岁月,日子不能这么过。
这就是SUMPRODUCT函数的第三个特点:数组参数必须有相同的尺寸,否则返回错误值。
下面是一道练习题,你看看,能用SUMPRODUCT函数做出来吗?

 

案例拓展

假设下面这张图,是某个公司工资发放的部分记录表(数据纯属虚拟,如有雷同,那是穿越)。A列是工资发放的时间,B列是员工所属的部门,C列是员工姓名,D列是相关员工领取的工资金额。

——那么,问题和广告都来了:

1
员工西门庆领取了几次工资?
这是一个单条件计数的问题,通常我们使用COUNTIF函数,但如果使用SUMPRODUCT函数,一般写成这样:
=SUMPRODUCT((C2:C13="西门庆")*1)

先判断C2:C13的值是否等于”西门庆”,相等则返回TRUE,不等则返回FALSE,由此建立一个有逻辑值构成的内存数组。
上文已经说过,SUMPRODUCT有一个特性,它会将非数值型的数组元素作为0处理,逻辑值自然是属于非数值型的数组元素,为了避免SUMPRODUCT函数把逻辑值视为0,造成统计错误,我们使用*1的方式,把逻辑值转化为数值,TRUE转化为1,FALSE转化为0,最后统计求和。

2
员工西门庆领取了多少工资?
这是一个单条件求和的问题,通常我们使用SUMIF函数,如果使用SUMPRODUCT函数,我们可以写成这样:
=SUMPRODUCT((C2:C13="西门庆")*D2:D13)

依然首先判断C2:C13的值是否等于”西门庆”,得到逻辑值FALSE或TRUE,再和D2:D13的值对应相乘。TRUE乘以数值,得到数值本身。FALSE乘以数值返回0。最后统计求和得出结果。
看完了上面两个问题,有些朋友可能会在心里想,貌似SUMPRODUCT能干的事,SUMIF和COUNTIF也能做到,而且做的更好,那么还要SUMPRODUCT干啥嘞?

乡亲们呐,话不能这么说,SUMPRODUCT可是上得厅堂下得厨房,对工作环境不挑不拣,它对参数类型没有啥特别要求,COUNTIF和SUMIF就不同了,他俩要求个别参数,必须是区域(Range型),不支持数组,比如下面这两个问题,COUNTIF和SUMIF就要绕了。

3
二月份外交部发放了几次工资?总额是多少?
第1个问题,二月份外交部发放了几次工资?
这是一个多条件计数的问题。
第一个条件,发放工资的时间必须是二月份;第二个条件,发放工资的部门必须是外交部。
如果使用多条件计数函数COUNTIFS,判断发放工资的时间是否属于六月份,会简单问题复杂化。而使用SUMPRODUCT函数,咱们可以把公式写成这样:
=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部"))
……

第2个问题,统计二月份外交部发放了多少工资?
这是一个常见的多条件求和问题。
如果使用SUMIFS函数,判断发放工资的时间是否属于六月份,也会简单问题复杂化。
SUMPRODUCT跃然而至:
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部"),D2:D13)

或者:
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部")*D2:D13)

打个响指,关于这两个形式的SUMPRODUCT函数的区别,咱们上文已有详细说明——你还记得吗?
上面这个公式可以说是SUMPRODUCT多条件求和的典型用法啦,可以归纳为:
=SUMPRODUCT((条件一)*(条件二)……,求和区域)

4
二月份外交部和步兵部合计发放了多少工资?
解决了上面的问题,相信大家已经晓得如何计算二月份外交部发放多少工资了,那么二月份外交部和步兵部合计发了多少工资,又当怎么计算呢
我们经常见有些性格朴素的表亲们把公式写成这样:
=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部")*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="步兵部")*D2:D13)

这些表亲们估计心想,不就是计算两个部门吗?甭说两个,二十个咱也能算,一个加一个,一直加到二十个,世上无难事,只怕有心人嘛,一砖加一砖,长城就建成了,一泡加一泡,长江就奔流了……
呃……公式写的那么长,先不谈计算速度,首先它累手啊,万一写错了,又要修改,那也是麻烦他妈哭麻烦——麻烦死了。
其实我们可以写成这样:
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={"外交部","步兵部"})*D2:D13)

5
排名应用
认识了SUMPRODUCT函数在条件计数和求和方面的用法,最后,咱们再来看一个它在排名上的使用方法。

如上图所示,某个月某个公司某些人领了某些工资,然后呢,他们想看看自己的工资,在部门内的排名情况,比如说步兵部的鲁智深都是老员工了,非常想知道自个工资在各自部门排几号。
当然啦,不排不知道,一排就傻掉。
SUMPRODUCT是这么解决这个问题的,D2输入公式向下复制:
=SUMPRODUCT(($A$2:$A$9=A2)*(C2<$C$2:$C$9))+1

(思考,为什么公式的最后+1,而不是直接写成如下:)
=SUMPRODUCT(($A$2:$A$9=A2)*(C2<=$C$2:$C$9))

结束语
唠唠叨叨说了这么多,眼睛都说酸麻了,是到了该结束的时候啦。
最后,请思考两个小问题:

第1个问题:下面SUMPRODUCT函数有几个参数?
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")*D2:D13)

下面这个SUMPRODUCT函数又有几个参数?
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部"),D2:D13)

第二个问题:
SUMPRODUCT为什么有时候比SUMIF/COUNTIF计算速度慢?
……

出处:https://mp.weixin.qq.com/s/0VZMQtCqN-Ez1FdR8i4J1g

任意条件查数据,简单才是硬道理

ExcelHome阅读(1864)

分享一个数据查询有关的内容,先看数据源:

左侧是员工信息表,右侧是查询区域,希望在G4输入任意姓名或者任意部门,就能在右侧提取出符合条件的全部记录。

要实现这样的数据提取效果,其实很简单,接下来咱们就看看具体的操作步骤。

步骤1
在G4单元格输入要查询的内容,比如 销售部。

步骤2
在第一行数据的左侧,本例是A4单元格,输入以下公式,向下拖动:
=(C4=G$4)+(B4=G$4)+A3

公式是什么意思呢?
如果C4单元格中的部门等于G4要查询的部门,或者是B4单元格中的姓名等于G4单元格要查询的姓名,就在上一个单元格的基础上加上1,否则仍然是上一个单元格中的内容。
观察公式下拉后的效果,可以看到,只要C列的部门名称等于G4单元格中的部门,得到的结果正好是一串递增的序号1……2……3……
这些序号干嘛用呢?不着急,往下看。

步骤3
在I4单元格输入公式,向右向下复制,就可以得到查询结果了:
=IFERROR(VLOOKUP(ROW(A1),$A:$E,COLUMN(B1),0),"")

这个公式又是啥意思呢?
这里面的主角是VLOOKUP函数,要查询的内容是ROW(A1),ROW的作用是返回参数所在的行号,这里得到的结果就是A1的行号——1,当公式向下复制时,会依次变成ROW(A2)、ROW(A3)……,结果就是从1开始的递增序号1、2、3……。
也就是说,在不同行中VLOOKUP函数的查找内容是不一样的,在第四行时,查找的内容是1,公式到了第五行,查询的内容就是2了。

再看看VLOOKUP函数查询的区域是哪里呢?$A:$E ,这个写法表示A~E列的整列区域,并且使用了绝对引用。

说到这里,有小伙伴们已经知道咱们前面用公式得到的那些序号是干嘛用的了,对,就是协助VLOOKUP查询用的。
前面的序号特点是每遇到一个符合条件的记录,序号就加上1,而VLOOKUP要查询的内容就是序号1、2、3……

再来看COLUMN(B1)是干啥用的呢?她的作用和ROW函数类似,是返回参数的列号的,COLUMN(B1),得到的就是B1的列号2。公式向右复制时,会依次变成COLUMN(C1)、COLUMN(D1)……,结果就是从2开始的递增序号2、3、4……
得到的序号再给VLOOKUP函数当小三,不对不对,是当成第三参数,用来指定返回查询区域中哪一列的内容。

当公式在I列时,返回查询区域第二列的内容,公式向右复制到了J列,那就是返回查询区域第三列的内容了,以此类推。

小伙伴可能会说了,A列还有那么多重复的序号啊,没关系,因为VLOOKUP函数有个特点,就是如果有多个符合条件的记录,只会返回第一条。所以在I4单元格查询1的时候,得到序号1对应的姓名兰花,在I5单元格查询2的时候,得到序号2对应的姓名枣花……

最外面的这个IFERROR是干啥用的呢? 她啊,是用来给VLOOKUP屏蔽错误值的。
因为VLOOKUP每一行中的查询序号都不一样,当公式一直下拉时,这个序号就会不断增加,当查询的序号在A列没有出现时,说明左侧记录中没有这么多符合条件的内容了,公式会返回错误值。所以,咱们使用IFERROR函数把错误值变成了空文本。
图片

练手文件在此,你也试试吧:
链接:https://www.aliyundrive.com/s/H3ZRUvxawRL

VBA之正则表达式:提取日期和金额

ExcelHome阅读(1075)

实例需求:数据保存在A列中,需要将其中的日期和金额分别提取至B列和C列。其中日期也两种不同格式,金额可能包含小数点、千分符和货币标识。

示例代码如下:
Sub RegExp_Date_Num()
Dim Res()
Dim objRegEx As Object
Dim objMH As Object
Dim j As Integer
Set objRegEx = CreateObject("vbscript.regexp")
objRegEx.Pattern = "(\d{4}-\d{2}-\d{2}|\d{4}.\d{2}.\d{2}).*?(([A-Z]{3})*\d+[\d.,]*元)"
objRegEx.Global = True
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
form = Cells(i, "A")
Set objMH = objRegEx.Execute(form)
If objMH.Count > 0 Then
Cells(i, 2) = CStr(objMH(0).submatches(0))
Cells(i, 3) = CStr(objMH(0).submatches(1))
End If
Next
Set objRegEx = Nothing
Set objMH = Nothing
End Sub

【代码解析】
第6行代码使用后期绑定创建正则对象。
第7行代码指定正则匹配字符串,

如果直接使用[\d.,]+匹配金额中的数字,则会匹配到单个小数点或者逗号,例如数据为:旧的一年结束,元旦将要来临,此模式可以将匹配到,元。由此可见组织一个严谨的正则表达式需要考虑很多情况。
第8行代码设置为全局搜索模式。
第9行代码第16行代码循环处理工作表中的数据。
第11行代码执行正则匹配,返回结果为MatchCollection集合对象。
第13行和第14行代码分别将日期和金额写入工作表中

Excel版倒计时牌

ExcelHome阅读(1601)

在Excel中制作倒计时牌,先来看效果:

在日常工作中,经常会有一些倒计时的应用,例如常见的距高考还有n天、距项目结束还有n天等。使用Excel中的日期函数结合按指定时间刷新的VBA代码,即可制作出倒计时牌。

步骤1 假设假期结束日期为2021年12月27日0时,在C2单元格输入以下公式,得到剩余的整数天数。
=INT("2021-12-27"-NOW())&"天"

步骤2 设置D2单元格自定义格式为:
hh小时mm分ss秒

然后在D2单元格中输入以下公式:
="2021-12-27"-NOW()

虽然NOW函数属于易失性函数,但是如果在工作表中没有执行能够引发重新计算的操作,公式结果并不能自动实时刷新,因此需要添加定时刷新的VBA代码。

步骤3 按<Alt+F11>组合键打开VBE编辑器,依次单击【插入】→【模块】命令,在【工程资源管理器】中单击选中刚刚插入的“模块1”,在右侧的代码窗口中输入以下代码。

Sub Macro1()
Application.OnTime Now + TimeValue("00:00:01"),"Macro1"
Calculate
End Sub
Private Sub workbook_open()
Macro1
End Sub

代码中的”00:00:01″,表示刷新时间为1秒,实际使用时可根据需要设置。例如要设置刷新时间为1分钟,可将此部分修改为”00:01:00″。

步骤4 在【工程资源管理器】中单击选中“ThisWorkbook”,在右侧的代码窗口中输入以下代码,然后按F5键,即可在单元格中实现倒计时效果。

Private Sub workbook_open()
Call Macro1
End Sub

最后将文件保存为Excel启用宏的工作簿,即xlsm格式。

再次打开文件时,如果出现如图 所示的安全警告,记得要点击【启用内容】按钮哦。

制作完成之后,你就可以打开Excel,看着时间一点点的减少,心里对某个人的思念却越来越强烈……

一个实用的图表制作技巧

ExcelHome阅读(3411)

今天和大家一起分享一个图表制作的技巧,先来看看效果:

这个图表里,其实有三个数据系列,分别是一深一浅两个颜色的条形图,再就是大大的圆圈儿,其实是用散点图模拟出来的。

先来看数据源:
各车间不同月份的优质率指标:

首先建立一个下拉菜单。
单击H2,【数据】 →【数据验证】→ 设置【序列】来源为B21:F21单元格区域的车间名称。

接下来要使用公式来构建图表数据源了。
如下图所示,B2公式为:
=HLOOKUP(H$2,$21:$33,ROW(A2),0)
也就是根据H2单元格中的部门,从21至33行这个范围中,返回不同月份的指标数据。
B列的数据,在图表中就是深色的条形图数据系列。

在C2输入以下公式,得到浅色的条形图数据系列:
=1-B2

辅助列的意思其实就是把产品指标看作是1的话,去掉优质率部分,剩余的那些了。

接下来就是要构建散点图的x轴和y轴数据了。
x轴,咱们先用等式等于B列的优质率。
再用0.5 1.5 2.5……这样递增的数据得到y轴数据。
y轴数据的尾数设置成0.5,生成图表后,散点图的位置能恰好位于各个条形的中间。

选中A1:C13单元格区域,插入堆积条形图。

单击图例项,按Delete键删除。然后双击横坐标轴标签,设置最大值为1。

下面的步骤是添加散点图的数据系列。
右键单击图表,【选择数据】,在【选择数据源】对话框中单击【添加】按钮,在【编辑数据系列】对话框中输入系列名称,例如“标签”。

再添加散点图的数据系列。
右键单击条形图,【更改系列图表类型】,在弹出的对话框中将“标签”系列的图表类型设置为散点图。

接下来给散点图选择数据来源。
右键单击图表→【选择数据】,在【选择数据源】对话框中选中【标签】系列,再单击【编辑】按钮,在【编辑数据系列】对话框中,选择x轴系列值为D2:D13区域的数据,y轴选择E2:E13区域的数据。

双击图表右侧的次要垂直轴标签,设置最大值为12。

从【设置坐标轴格式】窗格底部,设置标签位置为“无”

此时的图表已经有了大致的效果,但是默认的黄蓝配色实在不咋样:

对图表进行简单的美化。单击选中蓝色的系列,设置填充颜色:

同样的方法,再选中黄色的系列,设置填充颜色,可以设置成和前一个系列相同色系,只是稍淡一些,这样看起来比较顺眼。

现在的条形图看起来很瘦弱,粗壮一些会更好看。

散点系列也要再大一些。
单击选中散点系列,分别设置标记类型、填充颜色以及边框颜色和线型。

右键单击散点系列,添加数据标签。

右键单击数据标签,设置数据标签格式。
标签选择“单元格中的值”,然后选择D列的x轴数据。
设置标签位置为“居中”。

此时由于标签的字体颜色是黑色的,和散点图的大圆圈儿在一起看不清了。
咱们要设置一下标签的字体和颜色。

在I2单元格输入公式,生成一个动态更新的图表标题。
=H2&”上年度产品优质率”

单击图表标题,编辑栏中输入等号,再单击I2单元格,按回车。

至此,咱们的图表就制作完成了,一起来看看效果吧:

有小伙伴可能注意到了,条形图中的月份和实际的数据源中的顺序是相反的,如何设置成顺序一样呢?

今天的练手文件在此:
https://pan.baidu.com/s/1KqVjN3i_cPWW-EESKg7ObQ
提取码: v3yd

折叠表格,不会你就out啦

ExcelHome阅读(6307)

先来看一个表格的展示效果。

这样查看数据是不是非常方便啊,炫酷又方便哦。
有好多小伙伴肯定还不知道这是怎么设置的,其实这个就是久负盛名的分类汇总功能,今天咱们就学习一下。
在日常的工作中,常常需要对数据进行快速分类,并以一定的方式来汇总项目。
而分类汇总这个功能就能够帮助我们实现这样的要求。
比如下面的一个数据:

这样的数据源算是十分规整的,而我们的要求仅仅是把这个数据,按照级别汇总各级别的人数,同时还要对补贴项目求和。
对分类汇总有所了解的小伙伴可能已经迫不及待了:
全选数据区域, 点开【数据】【分类汇总】~

STOP!

别急别急,分类汇总最重要的一步【排序】还没有做哦。
对,就是排序,分类的参照级别如果不经过排序,直接分类汇总的后果就乱套了:

这样的结果,显然不符合日常工作中的要求。~
首先要对需要汇总的参照列【级别】进行排序,排序处理后,相同类别的内容都会集合到一起。

然后再进行分类汇总,就妥妥的了。

注意这里勾选了上【级别】,目的是为了计算每个级别的人数~
点击【确定】按钮 即可初步完成了。
点击汇总单元格,里面有自动生成的汇总公式。

每个级别的补贴等内容都完成了求和汇总,但是我们看B列的每个级别的计数还是显示为0的。

这里额外插一下关于SUBTOTAL这个函数的一些小说明,更多内容看官们自行脑补哦~

相信这个时候,有些看官已经知道了, SUBTOTAL第一参数使用3就是计数,如果第一参数选择9,就是求和计算了。
怎么把计算级别个数的公式批量修改为计数呢?可以这样操作~
选中【级别】所在的B列,按Ctrl + H,调出替换对话框。

点击左侧的加号或是减号或者是左上角的数字,就可以分级显示啦。

大牛函数:AGGREGATE

ExcelHome阅读(4449)

AGGREGATE函数用法与SUBTOTAL函数类似,但在功能上比SUBTOTAL函数更加强大,不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE、MAX等19个函数的功能,而且还可以忽略隐藏行、错误值、空值等,并且支持常量数组。

该函数的第一参数是1到19之间的数字,用于指定要使用的汇总方式:

第二参数是介于0到7之间的数字,指定在计算区域内要忽略哪些类型的值:

接下来咱们就说说这个函数的一些典型用法:

 

1、多个不连续区域忽略错误值直接求和

这个函数的强大之处就是在于2参可以指定参数来忽略错误值直接统计
如下图,蓝色区域中包含有不同的错误值,现在要对这几个不连续的区域求和。
公式为:
=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

 

2、筛选状态下忽略错误值

如下图,在筛选后的数据区域中包含有错误值,如何对可见单元格进行统计呢?
公式为:
=AGGREGATE(9,7,B6:B18)

第一参数使用9,表示求和,第二参数使用7,表示忽略隐藏行和错误值。

 

3、一个公式解决多种统计效果

如下图,A3:B14单元格区域中是筛选后的的数据,要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。
只要一个公式就够了:
=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

注意是区域数组公式,先选取c17:d22区域,然后在编辑栏写上公式,最后按ctrl+shift+enter三键录入。

 

4、向上求和你们都会,哪怕是筛选下的,向下呢?

=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)

除了向下求和的方向外还有隐藏和错误值,这是subtotal+sum(if)都无法实现的统计效果
(录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)

 

5、这条开始才是重点-条件极值统计

这个函数提早五年就实现了2016才有的maxifs和minifs函数的统计效果,而且不需要三键。
如下图,要计算1车间对应的最小值,公式为:
=AGGREGATE(15,6,B4:B15/(A4:A15="1车间"),1)

公式中的第一参数使用15,表示使用SMALL函数,第二参数使用6,表示忽略错误值。要统计的区域是B4:B15/(A4:A15=”1车间”)
A4:A15=”1车间”部分,先对比A列的车间是不是等于指定的条件。如果A4:A15单元格区域中等于”1车间”,就返回逻辑值TRUE,否则返回逻辑值FALSE。然后再用B4:B15除以这组内存数组,结果为:
{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;19;47}
最后,AGGREGATE函数忽略里面的错误值,得到第一个最小值。
如果要计算1车间对应的第三个最小值,只需要将最后的1,变成3就好了。
如果要计算1车间对应的最大值,咱们可以修改一下第一参数,使用14,就是第k个最大值了。

 

6、 一对多查询

如果想要一对多查询,很多人想到的是INDEX+SAMLL+IF函数的三键客组合。其实,用aggregate函数替代也是能实现的。
如下图,要提取出二车间的所有工号,可以使用以下公式:
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($3:$12)/(A$3:A$12=D$3),ROW(A1))),"")

这个公式的思路和第五个公式基本相同。

 

7、统计同一单元格中的最大值

如下图,B列多人的考核情况被写到同一个单元格内,要统计其中的最大值。公式为:
=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)

公式中的MID(B4,ROW($1:$50),COLUMN(A:AZ))部分,使用MID函数,依次从第1~50个字符处开始,各提取长度为1~50的字符串,得到一个巨长的内存数组。再使用两个负号,把内存数组中的文本变成错误值,数值仍然是其本身的值。
最后使用AGGREGATE函数,忽略内存数组中的错误值,计算出其中的第一个最小值。

 

8、同时统计指定条件的最大最小值

如下图所示,要同时统计1车间对应的最大和最小值。
先同时选中F4:G4单元格,编辑栏输入以下公式,按Ctrl+Shift+回车。
=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

AGGREGATE第一参数使用常量数组{16,15},表示分别使用最大值和最小值的计算规则。
最终的结果也是一个内存数组,所以要同时选中两个单元格输入。

这个函数的特性在于第一参数为14~19时,可以使用第四参数,此时的第四参数是支持数组的,因此就能玩出各种应用,来替代不能直接忽略错误值的SMALL、LARGE等函数。

今天的练习文件在此:
链接:  https://pan.baidu.com/s/1hOJgByy-IdALhGoHBNLgRw
提取码: mu5n

给Word表格设置下拉菜单

ExcelHome阅读(29783)

下面这个Word表格是一份电子版的员工信息表,要求每个员工据实填写:

这个表格中包含有姓名、性别、籍贯、学历以及政治面貌等等必填项。

姓名、性别、籍贯、学历这几项在填写时不会有啥问题,二逼青年也知道自己的姓名和性别的。在政治面貌这一栏中就会出问题了,填写结果往往是五花八门。

既然是电子版的信息表,咱们可以给他设定一个输入的下拉选项,避免出现什么贫农、地主、反动派这样的信息:

单击需要填写政治面貌的单元格,【开发工具】【下拉列表内容控件】


接下来就要设置控件属性了:
然后添加需要在下拉列表中显示的内容:

OK,完工~~

单击单元格就会出现“请在下拉列表中选择一项”的提示,再单击单元格右侧的按钮,就可以选择内容了。

光说不练假把式,动手试试吧。

 

咋了,你家Word上找不到【开发工具】?

不急,在这里:

数据验证是个宝,数据录入没烦恼

ExcelHome阅读(5065)

用数据有效性,能够帮助我们对输入的内容进行检测,不符合要求的数据不允许录入。今天老祝就和大家再分享几个数据有效性(也叫数据验证)的实用技巧,点滴积累,也能提高效率。

1、各项预算不能超过总预算

如下图所示,是某人的三胎育儿计划表,从幼儿园到结婚计划预算180万元,要求各分项预算之和不能超过总预算。

选中B2:B7单元格区域,数据→数据验证→自定义,输入以下公式。
=SUM($B$2:$B$7)<=$D$2

设置完成后,B列各分项之和超过D2单元格的预算,就会弹出错误提示。

 

2、根据其他列内容限制输入

如下图所示,是某地三胎意愿调查表,D列的配偶姓名填写时,要求C列的婚否一项中必须为“是”,否则禁止录入。

选中D2:D6单元格区域,数据→数据验证→自定义,输入以下公式。
=C2=”是”

 

3、限制录入周末日期

如下图所示,是某人的工作计划表,B列的拟定日期填写时,要求不能录入周末日期。

选中B2:B6单元格区域,数据→数据验证→自定义,输入以下公式。
=WEEKDAY(B2,2)<6

WEEKDAY(B2,2) ,根据B2单元格的日期,返回对应的星期。第二参数使用2,用数字1~7来表示周一到周日。WEEKDAY(B2,2)<6,就是限定录入日期小于周六了。

 

4、动态扩展的下拉菜单

如下图所示,要根据A列的对照表,在D列生成下拉菜单,要求能随着A列数据的增减,下拉菜单中的内容也会自动调整。

选中要输入内容的D2:D10单元格区域,数据→数据验证→序列,输入以下公式。
=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

公式表示以A2作为基点,向下偏移0行,向右偏移0列,新引用的行数为COUNTA函数统计到的A列非空单元格个数,结果-1,是因为A1是表头,计数要去掉。
这样就是A列有多少个非空单元格,下拉菜单中就显示多少行。

 

5、动态二级下拉菜单

如下图所示,A、B列是客户城市和县区的对照表,在D列已经生成一级下拉菜单,要求在E列生成二级下拉菜单,要求能随着D列所选不同的一级菜单,E列下拉菜单中的内容也会自动调整。

选中要输入内容的E2:E6单元格区域,数据→数据验证→序列,输入以下公式。
=OFFSET($B$1,MATCH($D2,$A$2:$A$16,0),0,COUNTIF($A:$A,$D2))

公式表示以B1为基点,以MATCH函数得到的城市首次出现的位置作为向下偏移的行数。
向右偏移的列数为0。
新引用的行数为COUNTIF($A:$A,$D2)的计算结果。
COUNTIF($A:$A,$D2)的作用是,根据D列以及菜单中的城市名在A列统计有多少个与之相同的城市个数。有多少个城市名,OFFSET函数就引用多少行。

关于我们联系我们
本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号

征信