如何在Excel中计算#Div/0值的数量 [英] how to count the number of #Div/0 value in excel

问题描述

我希望计算excel中#Div/0"出现的次数，其中1/0导致excel中#Div/0"值的出现，请问有人知道如何计算它的数量吗?

I wish to count the number of " #Div/0" occurrence in excel, where 1/0 results in "#Div/0" value in excel, may I know anyone has experience how to count the number of it?

谢谢.

推荐答案

您可以使用 ERROR.TYPE 函数.

例如，以下数组公式将计算 D1:D10 范围内的#DIV/0！错误数.

For example, the following array-formula, will count the number of #DIV/0! errors in the range D1:D10.

=SUM(IFERROR(IF(ERROR.TYPE(D1:D10)=2,1,0),0))

必须在按下 enter 的同时按住 ctrl + shift 来输入数组公式.如果正确执行此操作，Excel会将括号 {...} 放在在公式栏中看到的公式周围.

An array formula must be entered by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

下面是一个示例，该示例显示#DIV/0 作为文本输入时不被计数.请注意 D9:D10 中的两个更改.一个被直接输入并变成错误，另一个被输入为文本字符串.另请注意，与 D9:D10 相邻的公式表示 text vs error

Here is an example showing that #DIV/0, when entered as text, is not counted. Note the two changes in D9:D10. One was entered directly and became an error, the other was entered as a text string. Note also the formulas adjacent to D9:D10 indicating text vs error

这篇关于如何在Excel中计算#Div/0值的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！