excel公式中数据处理引发的探索

时间：2021-09-17 来源：互联网编辑：宝哥软件园浏览：次

excel公式中的数据处理引发的探索！我们知道，在Excel中，日期是以序列号的形式存储的，虽然你在工作表中看到的是“2020年3月31日”，而在Excel中存储的实际上是“43921.00”。整数部分是日期的序列号，小数部分是一天中时间的序列号。这样方便了日期的呈现和存储，但也带来了一些问题。比如我们以为是“2020年3月31日”，所以数据会直接与之对比，导致结果错误。本文通过一个例子说明如何处理公式中的日期。

如下图，计算2020年3月31日对应数据的平均值。

如果使用数组公式：

=AVERAGE(2:澳元20澳元=2020-3-31，B2:B20)

结果不正确。该公式相当于对单元格区域B2:B20的值求平均值：

=SUM(B2:B20)/19

首先看看Average函数的语法：

平均值(数字1，[数字2]，…)

其中：

1.参数number1，必选，要取平均值的数字、单元格引用或单元格区域。

2.参数2，可选的、其他数字、单元格引用或要求平均值的单元格区域。参数的最大数量可以是255。

AVERAGE将函数或提供的范围内的数字相加，然后除以该范围内的条目数或单元格数。

根据上面的公式，我们为AVERAGE函数提供了两个单元格区域，即：

1:2:澳元20澳元=2020年3月31日。

2区：B2:B 20。

1.对于2:澳元20澳元=2020-3-31，它将被解析为一个数组：

{ FALSE假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；FALSE}

本来我们以为小区A2:A20的前六个小区应该和2020-3-31匹配，但实际上我们返回了FALSE，表示没有匹配。

如果我们将其修改为：$ a $ 2: $ a $ 20=“2020-3-31”，它仍将被解析为一个数组：

{ FALSE假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；FALSE}

它仍然与该区域的前6个单元格不匹配。

我们将其修改为：=$ a $2: $ a $20=日期(2020年3月31日)，它将被解析为一个数组：

{真；真；真；真；真；真；假；假；假；假；假；假；假；假；假；假；假；假；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(2:澳元20澳元=2020-3-31，B2:B20)

替换为上面的中间数组：

=AVERAGE({ 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日”匹配，则为真，否则为假。将其传递给IF函数，返回单元格区域B2:B20中的相应值，并对这些值求平均值。公式解析过程如下：

=AVERAGE(IF(A $ 2: A $ 20=DATE(2020，3，31)，B $ 2: $ B $ 20))

决心：

=平均值(如果({真；真；真；真；真；真；假；假；假；假；假；假；假；假；假；假；假；假；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})

得到正确的结果10593.82。相当于公式：

=AVERAGE(B2:B7)

5.我们注意到，我们没有在上面的公式中提供IF函数的参数value _ IF _ false的值，这是有原因的。

如果我们将IF函数的参数值if_false相加，将公式修改为：

=AVERAGE(IF(2:澳元20澳元=DATE(2020，3，31)，2:澳元20.0澳元))

将获得不正确的结果。

这是因为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。

与上面没有指定参数value_if_FALSE的值的IF函数相比，可以看出AVERAGE函数忽略了提供给它的数组中的FALSE值，并且不将其计入要求平均值的值中。