电子表格中的条件函数

当几乎有人教任何一种编程语言时, 首先出现的就是条件句。那就是经典的IF-ELSEIF-ELSE语句, 它允许程序根据逻辑条件执行不同的操作。但是, 这可能不是Excel中想到的第一件事。但是, 它们在那里, 当你以一定数量的复杂度处理Excel报表时, 它们非常重要。特别是, 在本教程中, 我们将介绍如何在Excel中构造IF-ELSE语句, 以及在某些情况下派上用场的一些更高级的功能, 例如COUNTIF()。

基本的IF语句

首先, 让我们首先概述如何在Excel中编写基本的IF语句。语法如下：

IF(条件, value_if_true, value_if_false)

其中：

• 条件：值或逻辑运算结果为TRUE或FALSE
• value_if_true：条件为TRUE时要返回的值
• value_if_false：条件为FALSE时要返回的值

话虽如此, 让我们跳到一个简单的示例, 说明如何通过玩具表使用基本条件：

简单吧？对于熟悉R的人来说, 你可能已经注意到该语法与ifelse()函数基本相同。但是, 有些事情有些不同。例如, 逻辑运算符。在这个简单的示例中, 我使用了”>”逻辑运算符, 这是表示大于的标准方法。然而, 其他人并不是那么标准。这包括不等于运算符, 写为” <>”

在Excel中使用的所有逻辑运算符的完整说明如下所示：

OR(), AND()和NOT()

比较运算符不是Excel条件语句中可能与你在Python, R或Matlab中使用的可能不同的唯一组件。实际上, 定义布尔运算(如OR, AND和NOT)的方式是不同的。例如, 在Excel中, 你将编写如下的OR表达式：

IF(OR(值_1 = k, 值_2 = y), 值_if_true, 值_if_false)

要查看Excel中所有正在使用的布尔运算符, 请查看以下示例：

嵌套的IF语句

正如你可以在Python, R或Matlab中嵌套IF-ELSEIF-ELSE语句一样, 你也可以在Excel中完成它。为此, 这就像在前一个IF语句中添加另一个IF语句(如果该结果的值为TRUE或FALSE)一样简单。这使你可以测试更多条件并根据这些条件返回更多结果。 Excel最多可以嵌套64个IF语句。但是, 我强烈建议你不要嵌套太多的IF语句, 因为你需要谨慎使用逻辑, 并且对其进行调试或直接更改可能会变得非常麻烦。

话虽如此, 这是Excel自身内部嵌套IF的一个简单用例：

COUNTIF()和COUNTIFS()

除了传统的IF语句, Excel还具有其他功能, 可以根据一组给定条件执行操作。在本节中, 我将着重于条件计数函数, 更具体地说, 着重于函数COUNTIF()和COUNTIFS()。

COUNTIF()允许用户对满足单个条件的单元格进行计数。其语法如下：

COUNTIF(cell_range_to_count, 条件)

其中：

• cell_range_to_count：你要计数的特定单元格范围
• 标准：控制应计数哪些细胞的标准。这些可能非常多样化, 例如：
  • COUNTIF(A1：A10, 10)-这将计算从单元格A1到单元格A10的范围内有多少个单元格等于10
  • COUNTIF(A1：A10, B5)-这将计算从单元格A1到单元格A10的范围内有多少个单元格等于单元格B5中的值
  • COUNTIF(A1：A10, ” Gandalf”)-这将计算从单元格A1到单元格A10的范围内有多少个单元格等于” Gandalf”
  • COUNTIF(A1：A10, “> 20”)-这将计算从单元格A1到单元格A10的范围内有多少个单元格大于20
  • COUNTIF(A1：A10, ” <> Gandalf”)-这将计算从单元格A1到单元格A10的范围中有多少个单元格不等于” Gandalf”

你可能已经猜到了, COUNTIFS与COUNTIF的不同之处在于, 它允许用户计算满足多个条件的单元格。其语法如下：

COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2, … cell_range_to_count_n, criteria_n)

重要的是要注意, 对于在COUNTIFS中指定的每个其他范围, 行和列的数量必须与原始范围(cell_range_to_count_1)相同, 否则会出现错误。

这两个功能非常适合与业务报告一起使用。对我而言, 最好的例子之一就是他们使我们的销售代表每个月进行约会的计数变得多么容易, 以及他们在我们的销售渠道中的位置。

在下面的视频中, 我将说明这两个功能是如何工作的。首先, 让我们从计算一组虚构的销售人员的总任命开始：

那不是那么容易吗？你只计算了每个虚拟销售员的任命总数。从理论上讲, 你也可以仅使用基本的IF语句来完成此操作, 但是要获得相同的准确结果, 则需要更多的工作。

现在, 假设你还希望统计每个销售人员每个月的约会。在这种情况下, COUNTIFS()是答案。你可以在下面的视频中看到它的运行情况：

注意：你可能已经注意到, 我在上一个视频中用$符号覆盖了我想要计数的范围。这样做是为了锁定它并防止在我向下拖动公式时更改它。

Voilá, 我们不遗余力地逐月统计了虚拟销售员的任命。值得一提的是, 如果你使用$符号来锁定某些单元格并在拖动公式时移动范围不大, 则可以以”更高效”的方式执行此操作(即, 我可以在F2中编写公式然后将其拖到其余位置)。但是, 我想更明确地编写一月和二月的COUNTIFS公式, 以提供更多的可见性。但是, 如果你感到好奇, 建议你尝试重写F2中的COUNTIFS()公式, 以便可以将其拖动到其余单元格周围, 同时获得相同的结果。这可能是一个有用的练习, 因为在Excel业务报表变大时, 使用锁定范围和单元格来拖动公式非常方便。这是本教程中使用的数据表。

SUMIF()和SUMIFS()

我们已经看到了如何使用COUNTIF()和COUNTIFS()来计数满足单个条件或多个条件的单元格, 但是如果你需要添加单元格而不是仅仅对它们进行计数呢？输入SUMIF()和SUMIFS()。例如, 当你必须添加给定销售人员在总计或给定月份内产生的收入时, 这些功能特别有用。这两个函数的语法如下：

SUMIF(criteria_range, 条件, (可选)sum_range)

SUMIFS(sum_range, criteria_range_1, criteria_1, criteria_range_2, criteria_2 … criteria_range_n, criteria_n)

其中：

• standards_range：这是将在其中检查条件的单元格范围。
• 条件：用于确定将哪些单元格添加在一起的条件条件。
• sum_range：将添加在一起的单元格。如果未在COUNTIF()中提供, 则criteria_range中的单元格将添加在一起。

现在, 不费吹灰之力, 让我们跳到一个使用SUMIF()和SUMIFS()来计算总收入和我们的假销售代表每月产生的收入的示例：

奇怪的是, 在SUMIFS()的情况下, sum_range是必需的参数, 它是第一个参数, 但它是可选的, 并且是SUMIF()中的最后一个参数。当你同时使用两个公式时, 这可能会引起混乱, 就像你在输入二月份的SUMIFS函数时所看到的那样。因此, 当你同时使用两个公式时, 建议你谨慎使用。确保始终检查添加的单元格范围是否正确。

IFERROR()

对于那些了解编程/脚本语言的人来说, 你知道有诸如try / catch块之类的东西用于错误处理。在Excel中, 我们可以使用IFERROR()函数来做到这一点。 IFERROR函数允许用户”捕获”错误, 例如＃N / A, ＃VALUE！或#REF !, 并提供更有意义的输出。 IRERROR函数的语法如下：

IFERROR(值, value_if_error)

其中：

• value：要检查错误的值或公式
• value_if_error：函数遇到错误时要输出的值。

在下面的视频中, 让我们看看它是如何工作的：

使用业务销售报告时, 通常最好在IFERROR函数中涵盖某些计算。有时, 你会遇到这样的情况, 即新的销售人员几乎没有任命, 合格的机会或完成的交易, 这在计算诸如对给定销售人员变成合格机会的任命百分比之类的事情时, 可能导致被0除。这可能会输出看起来很讨厌的错误, 这可能会使你的报告显得肮脏且难以为决策者阅读。但是, 如果使用IFERROR()语句覆盖这些计算, 则可以用那些错误代替那些错误, 例如破折号, 这将使你的报告更具表现力。此外, IFERROR()语句不仅可以输出更好的错误消息。你还可以嵌套这些语句以执行诸如链接的VLOOKUP之类的操作。

总结

恭喜你！现在, 你已经知道可以在Excel中利用条件的几种方式。在本教程中, 我努力介绍了当我负责完成Excel业务报告时经常看到的那些功能。但是, 如果你仍然想了解有关该主题的更多信息, 那么这里还有其他功能, 例如SWITCH(), AVERAGEIF()或AVERAGEIFS(), 我在这里没有讨论过, 你可能会发现有兴趣。

此外, 在诸如COUNTIFS()之类的函数中使用通配符之类的事情是对部分字符串匹配进行计数的完美有效方法。例如, 假设你有一个包含餐厅菜单项列表的Excel文件, 并且你希望计算其中有多少人提到”鱼”一词。在这种情况下, 你可以编写COUNTIF(cell_range, ” * fish”), 此函数将对剑鱼或海豚鱼等项目进行计数。我强烈建议你学习有关通配符的更多信息, 因为通配符非常有用, 并且你永远都不知道何时可以在下一个Excel项目中使用通配符。

最后, 你可能还想在srcmini上查看”使用电子表格进行数据分析”课程, 因为该课程有专门针对条件函数的部分, 可能有进一步的参考意义。