嵌套的IF语句具有精确的OR值 [英] Nested IF statements with exact OR values

问题描述

我是Excel IF语句的新手，并且遇到了我认为称为嵌套IF函数的问题。我在这里查看了其他IF问题，它们对于我的新手大脑来说太复杂了。

我有一列有数字的列。大约有十几个不同的数字。数字代表一个特定的团队。例如，100队是红队，101队是黄队队员，102队是绿队队员等。我需要创建一个IF声明，告诉我每个队伍都基于这些数字。我有数百行过滤，我不想手动执行它们，如果该函数存在在Excel中自动执行。

我试图写我自己的嵌套IF声明并且不起作用。我做错了什么？

  = IF（B2 =100，红色，IF（B2 =101，蓝色，IF（B2 =103，绿色，IF（B2 =104，黄色，））））
  

帮助？

解决方案

嵌套 IF 绝对让人头晕目眩，至少在我的经验中，我仍然感到困惑:)你也遇到了某些版本的Excel的问题，其中嵌套的 IF 是你的数量被允许是有限的。与此同时，它们变得难以调试添加的更多层次的嵌套。关于你当前的公式，@DaveSexton建议将字符串改为数字（删除引号）应该有所帮助。另外，请确保 在公式中引用了您的颜色值，因为使用 red （而不是red ）会导致 #NAME？错误（假设您尚未定义 red 已存在）。



在你的情况中，你有没有想过试试 VLOOKUP 公式？而不是在公式本身对团队名称进行硬编码，而是建立一个有团队编号和相应团队名称的表格：






这里的公式（列<$ c
$ b

  = VLOOKUP（A2，$ D $ 2：$ E $ 6） ，2，FALSE）
  

这样做是看单元格 A2 ，然后进入范围 D2：E6 的表格，查找 A2 中的值列 D 。当它找到它时，它会返回列 E 中的值，这将是团队名称。

I'm new to Excel IF statements and am having trouble with what I believe is called a nested IF function. I've looked at other IF questions on here and they're too complicated for my novice brain to understand.

I have a column that has numbers in it. There are about a dozen different numbers. The numbers represent a specific team. For example, 100 is team red, 101 is team yellow, 102 is team green, etc. I need to create an IF statement that will tell me what each of the teams are based on the numbers. I have hundreds of rows to filter through and I don't want to do them manually if the function exists to automate it in Excel.

I tried to write my own nested IF statement and it's not working. What am I doing wrong?

=IF(B2="100",red,IF(B2="101",blue,IF(B2="103",green,IF(B2="104",yellow,""))))

Help?

解决方案

Nested IF statements are definitely mind-bending, and at least in my experience I still get confused :) You also run into an issue with certain versions of Excel where the number of nested IF's you are allowed is limited. In conjunction with that, they become difficult to debug the more levels of nesting that are added. Regarding your current formula, @DaveSexton's suggestion of changing the strings to numbers (removing the quotes) should help. Also, make sure that you are quoting your color values in the formula, because using red (and not "red") will cause a #NAME? error (assuming you haven't defined red already).

In your situation, have you thought about trying a VLOOKUP formula? Instead of hard-coding the team names in the formula itself, you would set up a table that would have a team number and the corresponding team name:

The formula here (in column B2) is:

=VLOOKUP(A2,$D$2:$E$6,2,FALSE)

What this does is looks at cell A2 and then goes to the table in range D2:E6, looking for the value of A2 in column D. When it find it, it returns the value in column E, which would be the team name.

这篇关于嵌套的IF语句具有精确的OR值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！