17个高级Excel公式——所有专业人士都必须知道

已发表: 2019-07-28

如果您发现自己经常弯曲双手并浪费时间在 Excel 中手动执行操作，那么如果您知道如何以正确的方式使用它，您就会错过 Excel 的强大功能。 Microsoft Excel 以其公式而闻名，该公式可以有效地处理单元格中的大量数据，而无需在该过程中进行太多人工干预。

我们编制了 17 个高级 Excel 公式，它们具有将冗长的平凡手动任务转换为几秒钟工作的不可思议的能力。这些是每个专业人士都应该触手可及的公式，以节省自己宝贵的时间，或者在关键时刻给老板留下深刻印象。

浏览列表，告诉我们你最喜欢的！

学习者平均获得 58% 的加薪，最高可达 400%。

1. 索引匹配

公式 = INDEX(C3:E9,MATCH(B13, C3:C9,0),MATCH(B14,C3:E3,0))

Excel 上 VLOOKUP 或 HLOOKUP 公式的绝佳替代方案，它在执行查找任务时有一些缺点。 INDEX MATCH 是 2 个独立函数的组合公式：

INDEX：此公式根据列和行号返回表中单元格的值。

MATCH：此公式返回单元格在行或列中的位置。

INDEX 和 MATCH 公式组合的一个示例是：当您查找并根据姓名返回一个人的身高时，您可以使用此公式通过 INDEX MATCH 公式更改两个变量（在本例中为姓名和身高） .

资源

让我们一步一步地分解它：

如何结合 INDEX 和 MATCH

类型索引
选择要索引的区域
用 F4 锁定区域
找到要从中查找数据的行
用系列信息输入 MATCH 并用 F4 锁定区域
键入 0 表示完全匹配，关闭括号
按回车

有趣的数据科学项目创意

现在，您在工作表中拥有一组完全动态且功能齐全的列和行，其中所有通过单元格和行进行正确数据的梳理都是自动完成的。

2. 平均

要执行平均公式以找到任何数字范围的平均值，您需要遵循以下步骤：

在格式中输入您正在计算的值、单元格或单元格的比例。

公式应以 =AVERAGE(number1, number 2, etc.) 开头

如果要对工作表中的一系列单元格进行平均，请执行以下步骤：

输入您在格式中计算的值、单元格或单元格的比例，就像您在上面所做的一样
公式应类似于 =AVERAGE(Start Value: End Value)。

如果您想知道如何输入单元格区域的值，您可以用鼠标选择工作表上的区域并用 F4 锁定它。这将使 Excel 为您完成其余的工作，而无需对数字与组中的项目数进行任何求和和除法。

3. 苏米夫

这个公式在 excel 中表示为 SUMIF(range, criteria, [sum range])。这将导致满足您设置的要求的所需单元格范围内的值的总和。例如，=SUMIF(C3: C12, ">70,000) 将仅从值大于 70,000 的单元格返回 C3 和 C12 的单元格之间的值之和。

在财务、工资甚至成本计算的情况下，您需要根据您设置的条件确定与特定员工相关联的潜在客户的价值。手动执行此操作非常耗时并且会减慢速度。

上述条件的公式可以是 =SUMIF(range, criteria, [sum_range])，其中 Range 可以定义为您需要从中选择值的工作表的范围。

[sum_range] 定义为除了输入的第一个 Range 之外，您要添加的附加或可选 Range。

这是一个例子：

资源

4. 与 SUM 相结合的偏移量

单独使用 OFFSET 函数可能不方便，但是当与其他服务结合使用时，如果您想创建一个可以对任意可变数量的单元格求和的动态角色，您可以获得一个具有更快结果的复杂公式，即常规 SUM 公式，即static 需要与 OFFSET 功能结合使用。

因此，为了找出变量单元格中值的总和，公式应该是：

=SUM(B4:OFFSET(B4,0,E2-1))

资源

这里 SUM 函数的结束引用被 OFFSET 函数替换。从 B4 开始的 SUM 公式以变量结尾，并且是从 B4 开始的 OFFSET 公式，以 E2 (“3”) 中的值减一继续。这有助于该方法移动两个单元格并汇总三年的数据。在上面的参考中，您可以看到单元格 F7 包含单元格 B4 的总和：D4 = 15。