如何在EXCEL图表中插入注释和交叉脚注-Excel图表与图形-Excel学习网

为了减少Excel报表中的错误，您应该对它们进行调整。但是Excel的浮点运算会遇到麻烦。解决此问题的方法如下。

 

乍看之下，以下报告是一份普通报告。它仅按产品和地区对Excel表进行求和，然后对结果进行交叉计算。

 

一个使用SUMIFS来按区域和产品汇总数据的Excel表。

 

报告的主体使用类似于所示单元格的公式。

 

 

... SUMIFS使用以下语法：

 

 

该报告的结构良好的版本会将测试添加到行和列的总计中，这些测试可确保报告不会忽略自上次生成报告以来添加到源数据中的新区域或产品。

 

在单元格H7中执行这种测试的最简单方法是找到表中A列的总计，然后如果两组报告总计的总和不相等，则返回错误。

 

如果您目视比较单元格H7中的总数和单元格H9中的总计，它们看起来是相等的。但是，请看上面下面两个单元格中显示的公式冲突的结果：

 

 

也就是说，单元格H10表示两个单元格中的值之间有微小的差异，而单元格H11则表示从Excel的角度来看这两个总数不相等。

 

在 Excel中浮点运算可能会给出不正确的结果，Microsoft讨论了这些问题的原因。本文解释说，由于计算机将十进制数据转换为二进制数据的方式，十进制数字中必然会包含一些小的误差。

 

为了说明，当我将单元格F3格式化为小数点后八位时，我看到该单元格中的实际数字为： 3,218,623.46000001

 

正确设置和交叉设置的Excel表。请记住，所有源数据都四舍五入到最接近的美分。

 

因此，当Excel对许多十进制数字求和时，您不能依靠使用普通的比较来测试结果是否正确。

 

如果我们在浮点数学上没有问题，则此公式可以测试单元格F7中的结果：

 

 

在这里，我们使用SUMIFS对所有区域的“帽子”值求和。如果该值等于等效报告总数，则我们将返回快速而又易于计算的任何总数。否则，我们返回“错误”。

 

但不幸的是，浮点问题使该测试不准确。因此，我们需要使用 ROUND 函数将每个汇总计算的结果取整：

 

 

在这里，通过四舍五入所有结果，我们可以使用等号。当然，跨站方式也是如此。

 

正确设置和交叉设置的Excel表。测试总计始终是一个好主意。但是在这里，在单元格H7中，我们必须测试三个值，而不是两个。

 

也就是说，我们需要比较表数据的水平报告总计，垂直报告总计和总计。

 

我们有两个选择。显而易见的选择是使用这样的测试：

 

 

在这里，我们首先检查总计是否等于报告总计之一。接下来，我们测试两个报告总数的四舍五入是否相等。如果两个测试均成功，则其乘积为TRUE，并且我们知道所有三个舍入数字都匹配。因此，我们返回报告总数；否则，我们返回“错误”。

 

先前测试的问题在于我们一次只能测试一对数字，这会产生一个长公式。因此，您可以考虑以下另一种方法：

 

标准偏差是对值与平均值（平均值）的分散程度的度量。因此，除浮点问题外，其他多个相等的标准偏差应接近零。

 

考虑到这一点，我们可以通过测试三个标准差基本为零来测试三个数基本相等。具体来说，此公式检查其标准偏差是否小于.00001：

 

 

我只是在最近才创建了此测试，所以我没有使用太多。但是测试表明，如果我在上表的任何行或列总计中加一分钱， STDEV.S将从其几乎为零的值... 

... 

跳至

... 0.0047140284 ...因此使用临界值.00001来区分本质上相等和不相等的结果是一种安全的方法。

 

关于使用标准偏差测试相等性的真正好处是，您可以使用它通过一个简单的公式来测试任意数量的数字。