我有一个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>
我唯一会在中使用&方法的情况是,如果我确实希望在单个范围内找到字符串的串联,例如像这样:
= 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中返回满足两个条件的行值
发布评论