检查 Excel 中的组合? [英] check for combinations in Excel?
问题描述
我有两个多项选择题,有 9 种可能的选择,这构成了 20 种答案组合.每个组合都分配了一个值.在 excel 中,我需要检查不同组合的答案并告诉我该组合的值.
I have two multiple choice questions with 9 possible choices, which makes up for 20 combinations of answers. Each combination is assigned a value. In excel, I need to check answers for different combinations and tell me the value for that combination.
这怎么可能?我需要使用什么公式?
How is this possible? What formula do I need to use?
示例问答
可能的答案
这是我尝试过的方法,但我需要做 20 个这样的 IF 语句.有没有更好的办法?
Here's what I've tried it works but i need to make 20 such IF statements. Is there a better way?
=IF(B2 ="Squeaky clean",IF(C2="Just once","Normal"),IF(B2="A little tingly. Sometimes it stings",IF(C2="Just once","Sensitive")))
推荐答案
好的,所以问题 1 有 4 个可能的答案,问题 2 有 5 个可能的答案.每个答案都有可能是唯一的,也可能不是唯一的.
Alright, so question 1 has 4 possible answers, Question 2 has 5 possible answer. Each answer has a possible response which may or may not be unique.
我将这种方法视为将每个结果都视为独一无二的.没有什么可以阻止您重复结果.
I took the approach as treating each result to be unique. There is nothing stopping you from repeating results though.
为自己建立一个问题和结果表.然后,在您希望结果出现的位置,使用以下公式并调整单元格引用以匹配您的问答表.
Build your self a table of Questions and Results. Then where you want the results to appear, use the following formula and adjust the cell references to match your Q&A table.
=INDEX($F$2:$F$21,SUMPRODUCT((A2=$D$2:$D$21)*(B2=$E$2:$E$21)*ROW($D$2:$D$21))-ROWS(1:1))
sumproduct 返回找到问题 1 和问题 2 答案的行号.
The sumproduct returns the row number where bother your Question 1 and Question 2 answers are found.
-ROW(1:1) 减去您的标题行数.如果您的问答表有两个标题行,则可以使用 -2 或 -ROW(1:2).
The -ROW(1:1) subtracts your header row count. if you have two header rows about your Q&A Table you could use either -2 or -ROW(1:2).
Index 查看 Q&A 表的结果列,并返回与 sumproduct - headers 行中的行号相对应的结果
Index looks through the results column of your Q&A table and returns the result corresponding to the row number from the sumproduct - headers rows
现在,如果您按照示例并用句子代替字母,并将结果放在 F 列中,那么您应该有一些适合您的东西.请记住,没有什么可以阻止您多次获得相同的结果.在一个小数据表上,这还不错.如果您在大型数据表上多次获得相同的结果,我们就可以考虑不同的方法.
Now all if you follow the example and substitute you sentences for the letters, and put your results in columns F you should have something that works for you. Remember there is nothing stopping you from having the same result multiple times. On a small data table this is not so bad. IF you have the same result repeated multiple times on a large data table we could then look at a different approach.
这篇关于检查 Excel 中的组合?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!