3 个非常有用的疯狂 Microsoft Excel 公式

Microsoft Excel 公式几乎可以做任何事情。在本文中,您将通过三个有用的示例了解 Microsoft Excel 公式和条件格式的强大功能。

了解 Microsoft Excel

我们已经介绍了许多不同的方法来更好地使用 Excel,例如在哪里可以找到优秀的可下载 Excel 模板,以及如何将 Excel 用作项目管理工具

Excel 的大部分功能都隐藏在 Excel 公式和规则的背后,这些公式和规则可帮助您自动处理数据和信息,无论您在电子表格中插入什么数据。

让我们深入了解如何使用公式和其他工具来更好地使用 Microsoft Excel。

使用 Excel 公式进行条件格式设置

人们不经常使用的工具之一是条件格式。通过使用 Excel 公式、规则或一些非常简单的设置,您可以将电子表格转换为自动化仪表板。

要使用条件格式,您只需单击“主页”选项卡,然后单击“条件格式”工具栏图标。

在条件格式下,有很多选项。其中大部分内容超出了这篇特定文章的范围,但其中大部分内容是关于根据单元格内的数据突出显示、着色或着色单元格。

这可能是条件格式最常见的用法——比如使用小于或大于公式将单元格变成红色。详细了解如何在 Excel 中使用 IF 语句

较少使用的条件格式工具之一是图标集选项,它提供了一组很棒的图标,可用于将 Excel 数据单元格转换为仪表板显示图标。

当您单击“管理规则”时,它会将您带到“条件格式规则管理器”

根据您在选择图标集之前选择的数据,您将在“管理器”窗口中看到带有您刚刚选择的图标集的单元格。

当您单击Edit Rule 时,您将看到魔术发生的对话框。

您可以在此处创建逻辑公式和方程式,以显示所需的仪表板图标。

此示例仪表板将显示花费在不同任务上的时间与预算时间。如果您超过预算的一半,则会显示黄灯。如果你完全超出预算,它会变红。

如您所见,此仪表板显示时间预算不成功。

几乎有一半的时间花费在超出预算的金额上。

是时候重新集中注意力并更好地管理您的时间了!

1. 使用 VLookup 函数

如果您想使用更高级的 Microsoft Excel 函数,那么这里有一些供您尝试。

您可能熟悉 VLookup 函数,它允许您在列表中搜索一列中的特定项目,并从与该项目相同的行中的不同列返回数据。

不幸的是,该函数要求您在列表中搜索的项目位于左列,而您要查找的数据位于右侧,但如果它们被切换了怎么办?

在下面的示例中,如果我想从以下数据中找到我在 6/25/2018 执行的 Task 怎么办?

在这种情况下,您正在搜索右侧的值,并希望返回左侧的相应值。

如果您阅读 Microsoft Excel 专业用户论坛,您会发现很多人说 VLookup 无法做到这一点。您必须结合使用 Index 和 Match 函数来执行此操作。这并不完全正确。

您可以通过将 CHOOSE 函数嵌套到其中来让 VLookup 以这种方式工作。在这种情况下,Excel 公式将如下所示:

 "=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)"

该函数意味着您要在查找列表中查找日期 6/25/2013,然后从列索引中返回相应的值。

在这种情况下,您会注意到列索引为“2”,但正如您所见,上表中的列实际上是 1,对吗?

没错,但是您使用“ CHOOSE ”函数所做的是操作这两个字段。

您正在为数据范围分配参考“索引”编号 – 将日期分配给索引号 1,将任务分配给索引号 2。

因此,当您在 VLookup 函数中键入“2”时,实际上是指 CHOOSE 函数中的索引号 2。酷,对吧?

VLookup 现在使用日期列并从任务列返回数据,即使任务在左侧也是如此。

既然你知道了这个小花絮,想象一下你还能做什么!

如果您正在尝试执行其他高级数据查找任务,请查看这篇关于使用查找函数在 Excel 中查找数据的文章。

2. 解析字符串的嵌套公式

这是一个更疯狂的 Excel 公式!在某些情况下,您可以将数据从包含分隔数据字符串的外部源导入 Microsoft Excel。

引入数据后,您希望将该数据解析为各个组件。下面是用“;”分隔的姓名、地址和电话号码信息的示例。特点。

以下是使用 Excel 公式解析此信息的方法(看看您是否可以在精神上跟随这种疯狂):

对于第一个字段,要提取最左边的项目(人名),您只需在公式中使用 LEFT 函数即可。

 "=LEFT(A2,FIND(";",A2,1)-1)"

下面是这个逻辑的工作原理:

  • 从 A2 中搜索文本字符串
  • 找到“;”分隔符
  • 为该字符串部分末尾的正确位置减去一个
  • 抓取最左边的文本到那个点

在这种情况下,最左边的文本是“Ryan”。任务完成。

3. Excel 中的嵌套公式

但是其他部分呢?

可能有更简单的方法来做到这一点,但由于我们想尝试创建最疯狂的嵌套 Excel 公式(实际上有效),我们将使用一种独特的方法。

要提取右侧的部分,您需要嵌套多个 RIGHT 函数来抓取文本部分,直到第一个“;”符号,并再次对其执行 LEFT 函数。下面是提取地址的街道号码部分的样子。

 "=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)"

看起来很疯狂,但拼凑起来并不难。我所做的就是使用这个功能:

 RIGHT(A2,LEN(A2)-FIND(";",A2))

并将其插入到上面LEFT函数中每个有“A2”的地方。

这正确地提取了字符串的第二部分。

字符串的每个后续部分都需要创建另一个嵌套。现在您需要做的就是将您在上一节中创建的“ RIGHT ”方程粘贴到一个新的 RIGHT 公式中,并将之前的 RIGHT 公式粘贴到您看到“A2”的位置。这就是它的样子。

 (RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))))))

然后,您需要采用那个公式并将其放入原始的 LEFT 公式中,只要有“A2”。

最终令人费解的公式如下所示:

 "=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),FIND(";",(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),1)-1)"

该公式正确地从原始字符串中提取了“Portland, ME 04076”。

要提取下一部分,请再次重复上述过程。

您的 Excel 公式可能会变得非常混乱,但您所做的只是将长公式剪切并粘贴到自身中,使长嵌套仍然有效。

是的,这符合“疯狂”的要求。但老实说,有一种更简单的方法可以用一个函数完成同样的事情。

只需选择带有分隔数据的列,然后在“数据”菜单项下,选择“文本到列”

这将打开一个窗口,您可以在其中按所需的任何分隔符拆分字符串。只需输入' ; ' 并且您会看到所选数据的预览会相应更改。

只需点击几下,您就可以做与上面那个疯狂的公式相同的事情……但这有什么乐趣呢?

疯狂使用 Microsoft Excel 公式

所以你有它。上面的公式证明了一个人在创建 Microsoft Excel 公式以完成某些任务时可以达到的程度。

有时,这些 Excel 公式实际上并不是完成任务的最简单(或最佳)方法。大多数程序员会告诉您要保持简单,Excel 公式和其他任何公式都是如此。

如果您真的想认真使用 Excel,则需要通读我们的 Microsoft Excel 初学者指南。它拥有开始使用 Excel 提高工作效率所需的一切。之后,请务必查阅我们的基本 Excel 函数备忘单以获得更多指导。

图片来源:kues/Depositphotos