Excel IF语句未返回适当的值 [英] Excel IF statement Not returning the appropriate Value

问题描述

我正在尝试根据他们的分数给他们A或B给学生评分.如果某人的得分是absent而不是分数，我将返回该单元格的值.

I'm trying to grade students by giving them A or B depending on their score. If someone is having absent instead of a score, I return a value of the cell.

但是，它不返回单元格的值.参考记录位于称为raw的单独工作表中.我认为可能是因为我正在尝试返回字符串数据.

However, it does not return the value of the cell. The reference records are in a separate sheet called raw. I think it may be because I'm trying to return a string data.

我正在使用Excel2007.这是公式:

I am using Excel 2007. Here's the formula:

=IF(raw!E6>=75,"AA",IF(raw!E6>=70,"AB",IF(raw!E6>=60,"B",IF(raw!E6>=50,"C",IF(raw!E6>=40,"D",IF(raw!E6<40,"RT",raw!E6))))))

推荐答案

如果可以避免，请不要使用嵌套IF.相反，请使用带状的VLOOKUP:它的效率要高很多倍，并且故障排除要简单得多.像这样:

Don't use Nested IFs if you can avoid it. Instead, use a banded VLOOKUP: it's many times more efficient, and a heck of a lot simpler to troubleshoot. Something like this:

=IF(ISNUMBER([@Score]),VLOOKUP([@Score],Table1,2,TRUE),"Absent")

注意:

• 以上内容使用表格和关联的表格符号.我总是用 可以的时候使用表格，因为它们减少了电子表格的管理，并且 结构化表引用具有内在的含义.
• VLOOKUP必须将TRUE作为第四个参数，并进行查找 表必须按升序排序.
• 最低分数必须为零，以便低于40的任何事物都将获得重考"等级.

• The above uses Tables and the associated Table Notation. I always use Tables when I can, because they reduce spreadsheet administration and the Structured Table References have intrinsic meaning.
• The VLOOKUP must have TRUE as the forth argument, and the lookup table must be sorted in ascending order.
• The lowest score must be zero, so that anything below 40 gets a "Retake" grade.

这篇关于Excel IF语句未返回适当的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！