如何在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))

然后我发现这是一个张贴在这里的

:

返回符合条件的最小唯一值

在这里:

多个条件的小型函数excel

但是我无法让他们去做我想做的事. 任何帮助将不胜感激.

谢谢

解决方案

对于它的价值，这里的公式比@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:

Return smallest unique value that meets criteria

and here:

multiple criteria small function excel

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屋！