检查Excel中的组合？ [英] check for combinations in Excel?

问题描述

我有两个选择题，有9个可能的选择，它们可以组合20个答案。每个组合都被赋值。在excel中，我需要检查不同组合的答案，并告诉我该组合的价值。

这可能如何？我需要使用什么公式？

示例问答A

可能的答案

这是我尝试过的工作，但我需要做20个这样的IF语句。有没有更好的方法？

  = IF（B2 =Squeaky clean，IF（C2 =Just once正常），IF（B2 =有点刺痛，有时候会刺，IF（C2 =Just once，Sensitive）））
  

解决方案

好的，所以问题1有4个可能的答案，问题2有5个可能的答案。每个答案都有一个可能的反应，可能是也可能不是唯一的。

我采取的方法是将每个结果视为唯一的。没有任何东西阻止你重复结果。

建立自己的问题和结果表。然后，您想要显示结果，请使用以下公式，并调整单元格引用以匹配您的Q& A表。

  = 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的答案被发现。

-ROW（1：1）减去你的标题行数。如果您的Q& A表有两个标题行，则可以使用-2或-ROW（1：2）。

索引通过您的Q& A表，并从sumproduct - 标题行返回与行号相对应的结果

现在所有如果您按照示例替换句子的字母，并将结果放在列F中，您应该有一些适合您的东西。记住没有什么可以阻止你多次获得相同的结果。在一个小数据表上，这不是很糟糕。如果您在大数据表上重复多次重复相同的结果，我们可以查看其他方法。

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?

Example Q&A

Possible Answers

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")))

解决方案

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))

The sumproduct returns the row number where bother your Question 1 and Question 2 answers are found.

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 looks through the results column of your Q&A table and returns the result corresponding to the row number from the sumproduct - headers rows

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屋！