在Excel中编写IF语句 [英] Coding IF statements in Excel

问题描述

我在Excel中创建了一个风险日志，有两列，标签为Priority&影响（可以输入高（H），中（M）或低（L））。我想创建一个名为Measure的第三列。
这将根据优先级和这些风险的影响计算一个值。
数字显示在下表中，其中水平轴为Impact，垂直方向为优先级

I have created a Risk Log in Excel, there are two columns, labels Priority & Impact (where High (H), Medium (M) or Low (L) can be inputted). I would like to create a third column called "Measure". This will calculate a value based on the priority and the impact of these risks. The numbers are shown in the table below, where the horizontal axis is Impact, and the Vertical in Priority

 H  3   7   9
 M  2   5   8
 L  1   4   6
    L   M   H
Impact

因此，例如，H的优先级和H的影响会在度量列中给出9，优先级为M，H的影响将给出8，优先级为H和M的影响会给你7.
我不太确定如何编码测量列，以便得到所需的数字。我假设某种如果的陈述，但不确定，因为有这么多的选择。下面是我当前的电子表格，我想在右侧添加度量列。

So for example, a priority of H and an impact of H would give you 9 in the measure column, a priority of M and an impact of H would give you 8, and a priority of H and an impact of M would give you 7. I am not quite sure how to code the "Measure" column so that I get the number desired. I am assuming some kind of "If" statement, but not sure as there are so many options. Below is my current spreadsheet, and I would like to add the measure column to the right.

任何帮助都很棒！

推荐答案

这可以通过索引/匹配查找完成。不需要嵌套IF。

This can be done with an Index/Match lookup. No nesting of IFs required.

=INDEX($B$1:$D$3,MATCH(F2,$A$1:$A$3,0),MATCH(G2,$B$4:$D$4,0))

复制。当然，您可以将查找表放在不同的工作表上。

Copy down. You can place the lookup table on a different sheet, of course.

第一个匹配函数根据以下内容查找行A1到A3中的值。第二个匹配根据B4到D4中的值查找列。 Index返回找到的行和列交叉的单元格。

The first Match function finds the row according to the values in A1 to A3. The second Match finds the column according to the values in B4 to D4. Index returns the cell where the found row and column cross.

这篇关于在Excel中编写IF语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！