9012年最重要的10个Excel公式

01

表格功能及结构化引用

如果你不知道如何有效地和数据沟通，那你的函数公式多半是令人崩溃的。只就是为什么火箭君把一个不能称之为函数的功能列于此，以突显其重要性。要学着去引用数据表中的数据，尤其是表格形式的数据。你可以使用 表格名[列名] 这样的方式来引用整列数据，你也可以使用 [@列] 这样的表达形式也获取某一列的列值。

比如，你的公式可以是这样：

• SUM(销售数据[客户标号])找到我们究竟有多少客户

• SUMIFS(销售数据[客户标号],销售数据[产品],"铅笔")找到究竟有多少客户买了“铅笔”
  

02

再见无限嵌套的IF

你可能已经知道了IF函数。我们常用它来评价两个逻辑条件，然后输出一个结果。但是如果你有一个非常繁琐的情景，要求使用多个IF函数，那你该怎么办？简单，只要使用IFS()替代就可以了。它可以代入任何数量的条件，以及对应的输出结果。

比如这么一个IF公式：=IF(A1>20, “非常高”, IF(A1>15, “高”, IF(A1>10, “中等”, IF(A1>5, “低”, “非常低”))))

同样的公式，如果使用IFS公式则是这画面：=IFS(A1>20,"非常高", A1>15,"高", A1>10,"中等", A1>5,"低", A1<=5,"非常低")

是不是简单很多呢？

03

SUMIFS 以及 COUNTIFS

几乎所有的商业分析情景都会涉及这样的问题，比如“在满足条件A,B……N的情景下，这些事物的总数和总和是多少？”当然要回答他们也是可以同样地优雅和迅速，这时你就需要SUMIFS或者COUNTIFS。

SUMIFS函数的例子：

=SUMIFS(数据[采购总量], 数据[渠道],"在线", 数据[数量], ">3")

通过这个公式，我们可以求得当[渠道]为“在线”且数据[数量]大于3时，[采购总量]的和

04

SWITCH函数：新一代的CHOOSE()

SWITCH对大家来说有些陌生，因为它在Excel2016才被纳入进来。这个多功能函数可以帮助你基于任意条件选择众多输出选项中的一个。在某些情况下，SWITCH相当于IFS，但它允许设置一个default选项。如果不能满足任何一个SWITCH条件，你可以得到一个default参数。

SWITCH函数的例子：

=SWITCH([@省],"江苏","东部","广州","南部","四川","西部","其他")

也就是说查询[@省]的值，分别相应地得到“东部”、“南部”、“西部”以及“其他”。

05

永远时髦的VLOOKUP

永远记住学习VLOOKUP是必须的。这是一个绝对经典的数据分析函数。

06

SUBTOTAL：筛选你想要的

你知道SUM(), COUNT(), AVERAGE()这类的函数可以提供基础的数据统计结果。但是如果你想要找到“人力资源部”或者“年龄在25至40岁之间”。你的SUM()函数可能就不灵光了。

这是SUBTOTAL()函数的作用就提现出来了。SUBTOTAL函数能够排除掉被过滤掉的数据。因而你可以看到你想要的结果。

SUBTOTAL函数的例子：

=SUBTOTAL(9,数据[采购总量])

这样可以根据数据[采购总量]列来求和。

07

MAXIFS/MINIFS

MAX()，MIN()这样的函数绝大多数时显得如此简单。但是如果你想要知道在满足一系列条件时的最大值，还应该用MAX()函数吗？不，请使用MAXIFS()。如果你已经知道了SUMIFS怎么用，我相信你也可以很快学会这个简单易懂的函数。

MAXIFS函数的例子：

=MAXIFS(数据[采购总量], 数据[渠道],"在线")

通过这条公式，我们可以得到[渠道]为“在线”时，[采购总量]的最大值。

08

FIND/SEARCH函数

Excel中存在着一大批有关文本的函数。但是如果你初出茅庐，先学会FIND()吧。这个函数可以在一个文本类数据中找到某个特定的文本。如果找到相匹配的结果，FIND()会返回该文本的起始位置，否则显示为“#VALUE!”。

要注意的是FIND()函数对大小写敏感，如果你不想管大小写，那请使用SEARCH()函数。

FIND()及SEARCH()函数的例子：

• =FIND(“l”, “Hello people..”) 得到结果3

• =FIND(“P”, “Hello people..”) 得到结果 #VALUE! 错误值，因为大写P找到不到

• =FIND(“p”, “Hello people..”) 得到结果 7

• =SEARCH(“P”, “Hello people..”) 得到结果 7，因为SEARCH函数不区分大小写

• =FIND(“p”, “Hello people..”, 8) 得到结果10，因为设置了条件查找起始位置8之后的字母p。

09

TODAY/NOW函数

又是一个商业常识，就是我们总是会在数据中标记日期。因此学会如何在Excel中使用日期时间值，也是大有益处的。如果你还没开始研究，那就从TODAY()函数开始。正如其名，TODAY()函数会告诉你现在的日期，但是这是一个动态的函数。如果你输入了=TODAY(），那这个值每天都会发生变化。

另外，你也可以使用NOW()函数来查看当前的日期和时间。

例子：计算员工的工作天数

假设在A1单元格已经输入了员工的起始工作日期，你可以使用TODAY()函数来计算他们的工作天数。

=TODAY()-A1

10

IFERROR：当#N/A出现时

错误总是不可避免，但是如果善于使用IFERROR函数，那你的读者看到的可能不在是无聊的#VALUE!错误提示。IFERROR函数检查你的表达式是否存在错误，若恰巧出现了错误提示，那它可以显示一个你预设的提示信息。

IFERROR函数例子：

=IFERROR(VLOOKUP("THIS", Customers, 2, false), "Customer not found")

这里的公式在客户表格中寻找名为"THIS"的那位，如果找到则返回第二列的值，否则显示“Customer not found”