Excel-如何根据两个字段计算分数 [英] Excel - how to calculate a score based on two fields

问题描述

我试图弄清楚如何计算基于两个不同字段的名为风险分数"的字段，而无需使用嵌套的if循环.

I'm trying to figure out how to calculate a field called "Risk Score" that is based on two different fields without using nested if loops.

这两个字段是风险可能性"和风险影响"，其中可能性将以0-100％之间的百分比形式输入，而影响将以高，中，低形式输入.

The two fields are "Likelihood of Risk" and "Impact of Risk" where the likelihood will be inputted as a % between 0-100% and the impact inputted as either high, medium, low.

下面是两个字段和计算字段的示例:

Below is an example of the two fields and the calculated fields:

以下是我想在基于两个字段的第三个字段中得出的得分.

Below is the scoring I would like to derive in the third field based on the two fields.

我不认为我可以使用索引匹配匹配"，因为可能性得分基于范围而不是特定值.

I don't think I could use Index Match Match since the likelihood score is based on a range and not a specific value.

如果您对如何创建风险评分公式有任何想法，请告诉我.谢谢！

Please let me know if you have ideas on how I should create the formula for Risk Score. Thanks!

推荐答案

将％s的范围更改为最小值:

Change the ranges of %s to the minimums:

然后是简单的INDEX/MATCH/MATCH:

Then a simple INDEX/MATCH/MATCH:

=INDEX($C$3:$E$5,MATCH(H3,$B$3:$B$5,1),MATCH(G3,$C$2:$E$2))

这篇关于Excel-如何根据两个字段计算分数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！