Excel 2002中的多重条件 [英] Multiple conditions in excel 2002
问题描述
我正在使用excel 2002创建一个电子表格。我想要做的是使用countif函数,但是我有多个条件。所以我有2列数字列表,我想说的是数字x在一列中的数字,数字y在另一列(在同一行)。
例如
I am using excel 2002 to create a spreadsheet. What I am trying to do is use the countif function but I have more than one condition. So I have 2 columns with a list of numbers and what I want to say is count the number of occurences where the number x is in one column and the number y is in the other column (in the same row). E.g.
1 1
1 1
1 2
2 2
2 3
3 3
所以如果在上面我想计算第一列的编号为1,第二列为第2列,答案应为1.
So if in the above I wanted to count the rows where the first column had the number 1 and the second column had the number 2, the answer should be 1.
我不能使用COUNTIF函数,因为只有允许您指定一个条件,并且COUNTIFS不可用,因为我正在使用excel 2002。
I can't use the COUNTIF function because that only allows you to specify one condition and the COUNTIFS isn't available because I am using excel 2002.
请帮助。
推荐答案
这是一个数组公式的工作。在您的情况下,您可以执行以下操作:
This is a job for an array formula. In your case you can do:
= SUM((A1:A6 = 1)*(B1:B6 = 2))
=SUM((A1:A6=1)*(B1:B6=2))
输入为一个数组forumla(Ctrl-Shift-Enter)
entered as an array forumla (Ctrl-Shift-Enter)
等式测试每个返回一个布尔值数组,乘法执行一个元素这样乘(所以在这种情况下是逻辑AND)。 SUM将布尔值加到数字上,当它加起来产生的数组。
The equality tests each return an array of boolean values, and the multiplication does an element-wise multiply (so a logical AND in this case). SUM coerces booleans to numbers when it adds up the resulting array.
编辑:
在回答这个类似的问题时:
In an answer to this similar question:
https://stackoverflow.com/questions/576569/use -2-condition-in-excel-sumif
Jon Fournier发布了一个链接:
Jon Fournier posted a link to:
http://www.cpearson.com/excel/ArrayFormulas.aspx
其中有更多关于这种事情的细节。
which has a lot more detail on this sort of thing.
这篇关于Excel 2002中的多重条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!