VBA数组公式，用于获得两个值之间的分值

I have the following cells of score ranges and descriptions:

我有以下评分范围和描述的单元格:

Statement  Critical  Significant Average  Minor Non-critical
Grades     0.8       0.6         0.4      0.2   0

I have a data-set where grades will range from 0 to 1 and would like to create a string variable for each observation that will print the above statements based on the grades.

我有一个数据集，其中分数从0到1，我想为每个观察创建一个字符串变量，它将基于分数打印上面的语句。

So, for a random cell X1 my formula is:

对于任意的细胞X1，我的公式是:

={IF(X1>=A2:A6;B2:B6)}

But this array formula returns False if the value is 0.3. Any ideas?

但如果值为0。3，则该数组公式返回False。什么好主意吗?

1 个解决方案

#1

You can use a standard combination of INDEX and MATCH to solve this problem. The formula would be (per screenshot below):

您可以使用索引和匹配的标准组合来解决这个问题。公式为(每截屏):

=INDEX($F$2:$J$2,MATCH(B8,$F$3:$J$3,-1))

=指数(F 2美元:J 2美元,美元匹配(B8,F 3:美元$ J 3美元,1))

Where

在哪里

$F$2:$J$2- the list of 'statements' e.g. critical, significant
$F$2:$J$2-“报表”列表，例如:critical, significant
B8 - the grade in question
B8 -有问题的分数
$F$3:$J$3 - the range of grade ranges
$F$3:$J$3 -等级范围
-1 - as an argument to the MATCH function is 'greater than'
-1 -作为匹配函数的参数，它的值为'greater than'

Screenshot:

截图:

In that example, note that a score where 0.4 < score <= 0.6 gives Significant. However you might want that range to return Average in which case the whole MATCH function needs an offset e.g.:

在该示例中，注意0.4 < score <= 0.6的分数具有显著性。但是，您可能希望该范围返回平均值，在这种情况下，整个匹配函数需要一个偏移量，例如:

=INDEX($F$2:$J$2,MATCH(B8,$F$3:$J$3,-1)+1)

=指数(F 2美元:J 2美元,美元匹配(B8,F 3:美元$ J 3美元,1)+ 1)

But note for a grade of 0 this will give a #REF! error unless you create a new 'statement' e.g. 'less than critical' with a score of -1.

但请注意，如果分数为0，这将给出#REF!除非你创建了一个新的“声明”。“小于临界值”，得分为-1。

Also, note that your array formula:

另外，请注意您的数组公式:

={IF(X1>=A2:A6;B2:B6)}

= {如果(X1 > = A2:A6;B2:B6)}

Is not quite right - normally you enter a formula and then use ctrlshift+enter to create an array formula which in your case would be like this:

通常情况下，你会输入一个公式然后使用ctrl + shift+enter来创建一个数组公式在你的例子中是这样的:

{=IF(X1>=A2:A6;B2:B6)}

{ =如果(X1 > = A2:A6;B2:B6)}

But that doesn't work for me - I think it is easier for you to just the INDEX and MATCH approach unless you absolutely need an array formula.

但这对我来说并不管用——我认为，除非你绝对需要一个数组公式，否则你更容易使用索引和匹配方法。

#1