如何在Excel中返回满足两个条件的行值 [英] How to return row value that meets two criteria in Excel
问题描述
我有一个Excel电子表格来跟踪项目活动.
在A列中,我有一个项目名称,它重复了几次,因为同一个项目有多个活动.
在B列中,我有与该项目相关的活动.
在C列中,我具有每个活动的完成百分比.
我想完成的是:在D列中返回不是100%的项目的 first 活动.
这是我要完成的事情的图片:
我没有运气就尝试过这个公式:
=INDEX($B$2:$B$19,MATCH(TRUE,INDEX($C$2:$C$19<>100%,),0))
然后我发现这是一个张贴在这里的
:在这里:
但是我无法让他们去做我想做的事. 任何帮助将不胜感激.
谢谢
对于它的价值,这里的公式比@AlexisOlson的公式更准确:
=INDEX($B$2:$B$19,MATCH(1,($A$1:$A$19=A2)*($C$1:$C$19<>1),0))
通常,在MATCH
中包含连接不是一个好主意,因为它可能导致错误的结果.
例如:
= MATCH("12"&"34",{"1","12"}&{"234","34"},0)
当您实际希望它返回2时,该公式将返回1.
更改为此:
= MATCH(1,({"1","12"}="12")*({"234","34"}="34"),0)
将消除任何错误地选择错误的查找值的风险.
当然,在这种特殊情况下,这没有什么区别,因为$C$1:$C$19<>1
不可能返回TRUE
或FALSE
以外的任何东西,但这只是需要注意的事情. /p>
我唯一会在<c0>中使用&
方法的情况是,如果我确实希望在单个范围内找到字符串的串联,例如像这样:
= MATCH("John"&" "&"Doe",{"John Doe","Bill Miller","Mike Jones"})
I have an excel spreadsheet to keep track of project activities.
In Column A I have the Project Name and it repeats several times because the same project has several activities.
In Column B I have the Activities related to that project.
In Column C I have the % Complete of each activity.
What I would like to accomplish is: in Column D return the first Activity of the Project that is not 100%.
Here is a picture of what I would like to accomplish:
I was trying this formula with no luck:
=INDEX($B$2:$B$19,MATCH(TRUE,INDEX($C$2:$C$19<>100%,),0))
Then I found this one posted here:
and here:
But I haven't been able to get them to do what I want. Any help would be much appreciated.
Thanks
For what it's worth, here is a slightly more accurate formula than @AlexisOlson's:
=INDEX($B$2:$B$19,MATCH(1,($A$1:$A$19=A2)*($C$1:$C$19<>1),0))
Typically, it is not a good idea to have concatenations within MATCH
because it may lead to incorrect results.
For example:
= MATCH("12"&"34",{"1","12"}&{"234","34"},0)
This formula would return 1 when you would actually want it to return 2.
Changing to this:
= MATCH(1,({"1","12"}="12")*({"234","34"}="34"),0)
Will remove any risk of falsely picking the wrong lookup values.
Of course, in this particular case, it makes no difference, because there is no possible way that $C$1:$C$19<>1
could return anything other than TRUE
or FALSE
, but it is just something to be aware of.
The only time I will use the &
approach inside MATCH
is if I actually desire to find the concatenation of strings in a single range, e.g. something like:
= MATCH("John"&" "&"Doe",{"John Doe","Bill Miller","Mike Jones"})
这篇关于如何在Excel中返回满足两个条件的行值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!