代码之家 › 专栏 › 技术社区 › adam

索引与不带数组的“小于”条件匹配多个条件

excel-formula excel

1

adam · 技术社区 · 7 年前

我有一个数据列表 Date , Team 及其 Category Score . 类别分数 每个的更改 团队 根据他们每天的实际得分,我需要确定 类别分数 他们陷入了困境。

例如,如果在2018年1月1日,A队的得分是225。A队的总分在200到250之间。指定的 Score Category 然后将四舍五入到最后一个类别,即200

然而,第二天,A队 得分类别 改变了。225分完全取决于 得分类别 因此,分配的类别将是225。

希望这能给我们一个清晰的画面。

为了解决这个问题,数据总是按 日期 和 团队 随升 得分类别

我正在寻找一个不使用数组函数的Excel公式。到目前为止,我已经研究了索引匹配公式,但它们都是完全匹配的,不允许我近似于一个值。

例如

Date        Team    Score Category
1/1/2018    A       100
1/1/2018    A       150
1/1/2018    A       200
1/1/2018    A       250
1/1/2018    A       300
1/1/2018    B       300
1/1/2018    B       400
1/1/2018    B       500
2/1/2018    A       150
2/1/2018    A       200
2/1/2018    A       225
2/1/2018    A       300
2/1/2018    A       350
2/1/2018    B       350
2/1/2018    B       450
2/1/2018    B       550

Date:                       1/1/2018
Team:                       A
Actual Score:               225
Category Score (Output):    200


Date:                       2/1/2018
Team:                       A
Actual Score:               225
Category Score (Output):    225

1 回复 | 直到 7 年前

1

2

ImaginaryHuman072889 7 年前

尝试此数组公式:

=index($c$2:$c$17,match(b21,if(($a$2:$a$17=b19)*($b$2:$b$17=b20),$c$2:$c$17),1))
< /代码> 

注意:这是一个数组公式,必须使用键盘上的ctrl-+shift-+enter->kbd>来输入,而不仅仅是enter->kbd>。

请参见下面的使用数据的工作示例。我还将此单元格从b22复制到b27。




注意事项:

此公式仅在以下情况下有效:您的score categoriesfor a specificdateandteamcombination都是按升序排列的(因为使用的第三个参数是1inmatchrequires that the data be sorted),但从您的问题的表达方式来看,它听起来不像是一个问题


如果您的score categories和/或actual score sare negative,则公式也可能会中断,因为ifstatement effectively“zero's-out”您对此特定的不感兴趣的数据date和teamcombination。但如果实际得分为负,则可能会错误地将其中一个0视为匹配。这只是一个猜测,但我没有测试这个。


要考虑的另一件事是将score categoryof0添加到每个date和teamcombination的顶部。我之所以这样说是因为,例如,如果实际分数低于最低的分数类别(例如,如果实际分数是50但是最低的分数类别是100),那么就不会找到匹配项,我猜公式会返回错误。

数组公式,必须输入Ctrl键+换档+进入在你的键盘上,而不仅仅是进入.


请参见下面的使用数据的工作示例。我还从B22到B27.



几点注意事项

此公式仅在Score Categories对于一个特定的Date和Team组合按升序排列(因为使用1在里面MATCH要求对数据进行排序),但从问题的表达方式来看,这听起来不是一个问题。

如果你的分数类别和/或Actual Scores是负的,因为IF语句有效地“0's-out”您对此特定不感兴趣的数据日期和团队组合。但是如果Actual Score如果是否定的,那么它可能会错误地认为0这是一场比赛。这只是一个猜测,但我没有测试这个。

另一个需要考虑的问题是添加Score Category属于零到每个顶部日期和团队组合。我这么说是因为如果实际分数低于最低值得分类别(例如,如果实际分数是50但最低得分类别是100,那么就找不到匹配项,我猜公式会返回一个错误。