问题描述
我有一个表来查找这样的值:
I have a table to lookup a value like this:
logical test | points
-------------|-------
<= 0 | 1
<= 10 | 2
<= 20 | 4
> 20 | 5
如果我的单元格为(例如A1)< = 0，则结果为1 ...
如果我的单元格< = 10，则结果为2 ...
如果我的单元格> 20，则结果为5.
If my cell is (A1 for example) <= 0 the result is 1...
if my cell is <=10 the result is 2 ...
if my cell is > 20 the result is 5.
为此，我可以使用多个if函数，但是我想防止用户在表更改时不得不更改公式.例如，如果由于某种原因第一行更改为> 0，则无需更改公式.
我曾尝试在if函数中使用concatenate(mycell;logical_operator;logical_value)，但它似乎不起作用，我更喜欢没有VBA的解决方案...
有什么建议吗?
I could use several if functions for that, but I want to prevent the user to have to change the formula if the table changes. For example, if, for some reason, the first line changes to > 0 it wouldn't be necessary to change the formula.
I have tried using concatenate(mycell;logical_operator;logical_value) inside the if function but it doesn't seem to work, and I prefer a solution without VBA...
Any suggestions?
推荐答案
我了解您希望能够更改运算符的类型.我假设您正在使用的唯一运算符是数值比较:< ;、< =，=，> =，>
I understand you want to be able to change the types of operators. I'm assuming the only operators you're working with are numerical comparisons: <, <=, =, >=, >
使用命名范围更容易设置.按照我的屏幕截图设置运算符"，值"和分数"范围的名称.
This is easier to set up with named ranges. Set the name for the ranges of Operators, Values and Scores as per my screenshot.
例如我的运算子范围是"$ C $ 2:$ C $ 5"，值是"$ D $ 2:$ D $ 5"，分数是"$ E $ 2:$ E $ 5"
E.g. my range of Operators is "$C$2:$C$5", Values is "$D$2:$D$5" and Scores is "$E$2:$E$5"
然后，使用此数组公式获取A1中值的得分.
Then, use this array formula to get the score for the value in A1.
=INDEX(Scores,MATCH(TRUE,IF(Operators="<",A1<Values,(IF(Operators="<=",A1<=Values,IF(Operators="=",A1=Values,IF(Operators=">=",A1>=Values,A1>Values))))),0))
我已将此公式放入A3单元格中.
I've put this formula into cell A3.
注意:这是一个数组公式.您需要以特殊的方式输入它:双击单元格，粘贴公式，然后按CTRL-SHIFT-ENTER.每次编辑公式时都需要执行此操作，只有在需要编辑条件范围时才需要这样做.如果正确完成此操作，则在选择单元格时，公式栏将显示带有自动放置在公式周围的{curly括号}的公式.
NOTE: This is an array formula. You need to enter it in a special way: Double click into a cell, paste the formula, then press CTRL-SHIFT-ENTER. You need to do this everytime you edit the formula, which should only be required if you need to edit the range of conditions. If this is done correctly, when you select the cell, the formula bar will show the formula with {curly braces} automatically placed around the formula.
该公式检查您在C列中具有哪个运算符，然后对D列执行适当的比较.它对每个条件都执行此比较，并找到第一个为TRUE的条件.然后返回该条件的分数.
The formula checks which operator you have in column C, then performs the appropriate comparison against column D. It performs this for every condition and finds the first condition that is TRUE. It then returns the score for that condition.
如果存在多个匹配条件，则仅返回第一个匹配条件的分数.如果没有匹配条件，则它将返回#N/A. (或者，如果条件范围内有任何空白行，它将返回0.)
If there is more than one matching condition, then it returns the score for only the first matching condition. If there are no matching conditions, then it will return #N/A. (Or, if you have any blank rows in your range of conditions, it will return 0.)
这篇关于擅长使用单元格引用作为逻辑运算符并查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！