excel公式中日期的处理引发的探索
发布时间:2022-12-08 15:26:23
excel公式中日期的处理引发的探索!我们知道,在Excel中,日期是以序号数字来存储的,虽然你在工作表中看到的是“2020-3-31”,而Excel中存储的实际上是“43921.00”,整数部分是日期的序号,小数部分是当天时间的序号。这样方便了日期的表示和存储,但也同样带来了一些问题,例如我们以为是“2020-3-31”,因此会将数据直接与之比较,导致错误的结果。本文举一个案例来解公式中日期的处理方式。
如下所示,计算2020年3月31日对应数据的平均值。
如果使用数组公式:
=AVERAGE($A$2:$A$20=2020-3-31,B2:B20)
得到的结果是不正确的。这个公式相当于求单元格区域B2:B20的数值的平均值:
=SUM(B2:B20)/19
首先看看Average函数的语法:
AVERAGE(number1, [number2], …)
其中:
1. 参数number1,必需,想求平均值的数字、单元格引用或单元格区域。
2. 参数number2, …,可选,其他想求平均值的数字、单元格引用或单元格区域。参数个数最大可达255个。
AVERAGE将对函数或提供的区域中的数字求和,然后除以该区域中的条目或单元格的数量。
我们看上面的公式,给AVERAGE函数提供了两个单元格区域,即:
区域1:$A$2:$A$20=2020-3-31
区域2:B2:B20
1. 对于$A$2:$A$20=2020-3-31,将解析为数组:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
本来我们以为单元格区域A2:A20中的前6个单元格应该与2020-3-31相匹配,但事实上返回FALSE,这表明不匹配。
如果我们将其修改为:$A$2:$A$20=”2020-3-31″,仍会被解析为数组:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
仍然与该区域前6个单元格不匹配。
我们再将其修改为:=$A$2:$A$20=DATE(2020,3,31),将解析为数组:
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
可以看到,已正确与该区域相应的6个单元格相匹配。
2. 对于区域B2:B20,将解析为数组:
{10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;10685.98;10733.67;10779.17;10741.98;10785.89;10888.83;10836.15;10841.21;10850.36;10895.86;10907.42;10856.63;10927.07}
即由该区域单元格中的值组成的数组。
3. 因此,对于数组公式:
=AVERAGE($A$2:$A$20=2020-3-31,B2:B20)
使用上述中间数组替换:
=AVERAGE({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;10685.98;10733.67;10779.17;10741.98;10785.89;10888.83;10836.15;10841.21;10850.36;10895.86;10907.42;10856.63;10927.07})
返回不正确的结果10752.27。
4. 我们来看看正确的公式:
=AVERAGE(IF($A$2:$A$20=DATE(2020,3,31),$B$2:$B$20))
这也是一个数组公式。
如果单元格区域A2:A20中的值与日期“2020年3月31日”匹配,则返回TRUE,否则返回FALSE。传递给IF函数后,返回单元格区域B2:B20中对应的值,并对这些值求平均值。公式解析过程如下:
=AVERAGE(IF($A$2:$A$20=DATE(2020,3,31),$B$2:$B$20))
解析为:
=AVERAGE(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;10685.98;10733.67;10779.17;10741.98;10785.89;10888.83;10836.15;10841.21;10850.36;10895.86;10907.42;10856.63;10927.07}))
转换为:
=AVERAGE({10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})
得到正确的结果10593.82。等价于公式:
=AVERAGE(B2:B7)
5. 我们注意到,上面的公式中我们没有提供IF函数的参数value_if_false的值,这是有原因的。
如果我们添加IF函数的参数value_if_false,将公式修改为:
=AVERAGE(IF($A$2:$A$20=DATE(2020,3,31),$B$2:$B$20,0))
会得到不正确的结果3345.42。
这是因为IF函数会将条件为FALSE的值等于0,这样上述公式解析为:
=AVERAGE({10552.52;10564.38;10567.33;10611.84;10624.69;10642.15;0;0;0;0;0;0;0;0;0;0;0;0;0})
所得结果为上述值求和后除以19,而不是6。
与上述IF函数没有指定参数value_if_false的值相比较,可以看出,AVERAGE函数忽略提供给其的数组中的FALSE值,并且不会将其计入要平均的数值。
6. 其实,Excel 2007及以后的版本中引入了一个函数AVERAGEIFS,可以很好地解决上述问题,其公式为:
=AVERAGEIFS(B2:B20,A2:A20,DATE(2020,3,31))
或者:
=AVERAGEIFS(B2:B20,A2:A20,”2020-3-31″)
猜你喜欢
- word2013怎么合并多个文档?有时候,我们在网上download几篇Word文档,但需要将他们放进一篇文档里,那么此时合并文档就是个不错
- 邮件合并条件格式使用(一)本次讲邮件合并的高级应用,在邮件合并中使用条件格式。此邮件合并功能,可以应用在一个数据源包含多个分类,分类生成Wo
- 首先,右击电脑左下windows窗口,找到-------运行-------选项接着,点击------运行-------出现以下界面
- 我们在编辑文档时,特别是放到网上时,有时为了避免文档被随意转载,我们往往会给文档背景加上相应的水印;或者一些公司文件,往往也会打上公司的lo
- Word2007自动滚动怎么设置1、首先打开一篇长篇文档,单击其左上角的“office按钮”在弹出的快捷菜单中选择“Word选项
- 你是不是有时需要把EXCEL表格中的计算结果复制到其它表格,而不需要带着公式或计算过程?下面教你一个很简单的方法,一起来看看吧使用excel
- 问题原因:PowerDesigner 12.5安装带来的PowerDesigner 12 Requrements COM Add-in fo
- 启动Excel2013,打开要进行提取的表格,看到D列中有很多换行单元格,我要将第二行数据提取出来,在E2单元格输入公式: =REPLACE
- wps表格更改文件默认保存位置的方法:打开电脑上的wps表格 点击wps表格左上角的倒三角下拉按钮打开下拉列表 &
- 日常使用Word的时候,我们经常会使用各种Word主题进行文档的编辑,但这些Word自带的主题往往不能够满足我们的要求,那么word怎么更换
- 相信很多用户在使用Word制作一些文档的时候,经常会去添加页眉,但经常在添加完页眉之后就会发现下面会有一条横线存在,不仅不美观,还无法正常的
- 要打印一张毕业论文的封面,需要用A3纸来打印,可以当打开页面设置时取发现没有A3纸这一选项,奇怪怎么以前有呢?原来是因为打印机的缘故,如果你
- 通常情况下,我们在使用书法字帖时,大都是从书店中购买一些现成的字帖来练习,不过你是否考虑过自己来编制包含特定字词的字帖呢?这当然不是开玩笑,
- 在WPS的word中该怎样排版会比较好看呢?1、在“开始”菜单栏下,可以设置文档的字体,包括字形、字号等。2、在“开始”菜单栏下设置段落,包
- U盘是我们日常生活和工作中经常使用的一种设备,它可以用来保存文件、制作U盘引导盘等,但有时会出现问题,即U盘插入计算机后没有反应,如何解决这
- word怎么按颜色批量替换文字并标记?word文件中文字有很多颜色,想要按照颜色替换文字,该怎么设置呢?下面我们就来看看word按照颜色替换
- Word对表格的制作提供了很好的支持,只需要通过简单的几个步骤即可实现表格的插入操作,那么怎么用word绘制1张简单的表格?下面小编就为大家
- 如果我们的电脑在更新安装了win101909版本系统系统之后,想要后续更新自己的系统版本,但是过程中出现了更新错误代码提示0xc000040
- 这几天有网友求助,在操作win10系统的时候,由于误操作导致电脑右下角win10通知中心不见了怎么办?看不到一些相关的通知信息。其实不见了的
- 在 iOS 14 中,苹果加入了「小组件」功能对 iPhone 的主屏幕进行了重大革新,这些小组件类似 Android 设备上的小部件。而在