220 多个excel快捷键速查表，适用Windows和Mac。工作效率提高10倍。免费下载>>>
下载 “excel快捷键速查表” Excel快捷键速查表.zip – 已下载54次 – 3 MB
通用公式
前言
这是一个令人讨厌的长介绍，但上下文很重要，对不起！
如果您尝试使用 COUNTIF 计算某个范围内的非常长的数字（16 位以上），您可能会看到不正确的结果，这是由于某些函数处理长数字的方式存在错误，即使这些数字存储为文本也是如此。考虑下面的屏幕。D 列中的所有计数都不正确 – 尽管 B 列中的每个数字都是唯一的，但 COUNTIF 返回的计数表明这些数字是重复的。
这个问题与 Excel 如何处理数字有关。Excel 只能处理 15 位有效数字，如果您在 Excel 中输入超过 15 位的数字，您将看到尾随数字默默地转换为零。上面提到的计数问题就是由这个限制引起的。
通常，您可以通过输入长数字作为文本来避免此限制，方法是使用单引号 (‘999999999999999999) 开始数字，或者在输入之前将单元格格式化为文本。只要您不需要对数字执行数学运算，这是一个很好的解决方案，它可以让您为信用卡号和序列号等内容输入超长数字而不会丢失任何数字。
但是，如果您尝试使用 COUNTIF 计算超过 15 位的数字（即使存储为文本），您可能会看到不可靠的结果。发生这种情况是因为 COUNTIF 在处理过程中的某个时间点在内部将 long 值转换回数字，从而触发了上述 15 位限制。在没有所有数字的情况下，使用 COUNTIF 计数时，某些数字可能会被视为重复。
解决方案
一种解决方案是将 COUNTIF 公式替换为使用 SUM 或 SUMPRODUCT 的公式。在所示示例中，E5 中的公式如下所示：
该公式使用命名范围“数据” (B5:B9) 并使用 SUMPRODUCT 为每个数字生成正确的计数。
首先，SUMPRODUCT 中的表达式将命名范围“数据”中的所有值与当前行中列 B 中的值进行比较。这会产生一组 TRUE/FALSE 结果。
=SUMPRODUCT(—({TRUE;FALSE;FALSE;FALSE;FALSE}))
接下来，双重否定将 TRUE/FALSE 值强制为 1/0 值。
最后，SUMPRODUCT 简单地将数组中的项目相加并返回结果。
数组公式变体
您也可以使用 SUM 函数代替 SUMPRODUCT，但这是一个数组公式，必须使用 control + shift + enter 输入：
有此问题的其他功能
我自己没有对此进行验证，但似乎有几个函数存在相同的问题，包括 SUMIF、SUMIFS、COUNTIF、COUNTIFS、AVERAGEIF 和 AVERAGEIFS。
220 多个excel快捷键速查表，适用Windows和Mac。工作效率提高10倍。免费下载>>>
