Excel:COUNTIF函数将“小于"字符视为运算符 [英] Excel: COUNTIF function treats 'less than' character as an operator

问题描述

预读说明:我使用的是LibreOffice而不是Excel，但是大多数功能应同时应用于这两个方面.

Pre-read note: I'm using LibreOffice rather than Excel, but most functions should apply to both.

我正在制作一个电子表格，其中包含大量数据，并且对于每个属性(例如，雇员人数或姓名)，我需要一个函数来计算包含每个不同值的行数.
我已经提取了不同的值，现在我正在使用以下函数来计算每个值有多少行:=COUNTIF(start:end;value). (请注意，分号是LibreOffice分隔参数的方式，而不是Excel的逗号.)

I'm making a spreadsheet where I have a bunch of data, and for every property (such as # of employees or a name) I need a function that calculates how many rows contain each distinct value.
I already have the distinct values extracted, and now I'm using the following function to calculate how many rows have each value: =COUNTIF(start:end;value). (Note that the semicolon is LibreOffice's way of separating parameters, as opposed to Excel's comma.)

现在，我的问题是我有一些行的值可以是< 50".使用COUNTIF函数，似乎'<'被视为小于"运算符，而不是文本字符.因此，如果我要与之匹配的单元格的值是"<50"，那么根据该公式，我最终将得到一个值为0的值.如何获得COUNTIF将小于号视为文本字符?还是我应该在这里使用另一个功能?

Now, my problem is that I have some rows that can have values such as "< 50". Using the COUNTIF function, it seems that the '<' is treated as a 'less than' operator, rather than as a text character. Therefore, if the cell I'm trying to match the column against has the value "< 50", I end up with a value 0 as result of the formula. How can I get the COUNTIF to treat the less-than sign as a text character? Or is there another function I should use here?

推荐答案

在Excel中，您可以使用SUMPRODUCT，即

In Excel you could use SUMPRODUCT, i.e.

=SUMPRODUCT((start:end=value)+0)

使用=的直接比较将进行精确比较，并且不会与<混淆.或>像COUNTIF

The direct comparison using = will do an exact comparison and isn't confused by < or > like COUNTIF

这篇关于Excel:COUNTIF函数将“小于"字符视为运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！