3个出色的疯狂Excel公式

Excel公式几乎可以执行任何操作。在本文中,您将通过三个有用的示例来学习Microsoft Excel公式和条件格式的功能。

深入Microsoft Excel

我们介绍了许多不同的示例。更好地利用Excel的方法,例如使用它来创建自己的日历模板或将其用作项目管理工具。

很多功能都可以在Excel公式和规则中找到,您可以编写这些公式和规则不管您将什么数据插入电子表格中,都将自动操作数据和信息。

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

有条件的使用Excel公式进行格式化

条件格式化是人们不经常使用的一种工具。如果您要查找有关Microsoft Excel中条件格式的更高级信息,请确保查看Sandy的文章有关使用条件格式在Microsoft Excel中格式化数据。

使用Excel公式,规则或只需进行一些非常简单的设置,就可以将电子表格转换为自动仪表板。

要进入条件格式设置,只需点击首页标签,然后点击< strong>“条件格式设置" 工具栏图标。

在“条件格式设置"下,有很多选项。其中大多数超出了本文的范围,但是其中大多数是关于基于单元格中数据的突出显示,着色或着色单元格。

这可能是条件格式最常见的用法-使用小于或大于公式将单元格变为红色的事情。了解有关如何在Excel中使用IF语句的更多信息。

使用较少的条件格式设置工具之一是 Icon Sets (图标集)选项,该选项提供了很多图标,可用于将Excel数据单元格变成仪表板显示图标。

当您点击管理规则时,它将带您到条件格式规则管理器 >。

根据选择图标集之前选择的数据,您将看到“管理器"窗口中指示的单元格以及刚刚选择的图标集。

何时您点击编辑规则,您会看到魔术发生的对话框。

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

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

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

几乎一半的时间

1。花费时间重新安排预算并更好地管理您的时间!

1。使用VLookup函数

如果您想使用更高级的Microsoft Excel函数,那么这里还为您提供了另一个功能。

您可能对VLookup函数很熟悉,它可以您需要在列表中的某一列中搜索特定项目,然后从该项目所在的同一行的另一列中返回数据。

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

在下面的示例中,如果我想找到任务该怎么办?我是在2018年6月25日从以下数据中执行的?

在这种情况下,您正在搜索右侧的值,并且想要在左侧返回对应的值– VLookup正常工作的方式。

如果您阅读Microsoft Excel亲用户论坛,您会发现很多人说VLookup无法做到这一点,并且您必须使用com索引和匹配功能的组合可以做到这一点。并非完全如此。

您可以通过将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使用 Date (日期)列,并从 Task (任务)列返回数据,即使Task位于

现在,您已经知道了这个小窍门,请想象一下您还能做什么!

如果您要执行其他高级数据查找任务,请务必签出Dann有关使用查找功能在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公式中,那么无论您看到“ A2"如何,都将先前的RIGHT公式粘贴到自身中。就是这样。

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

    然后,您将THAT公式放入存在“ A2"的原始LEFT公式中。

    最终弯腰的公式看起来像

    "=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公式可能会变得很loop回,但您要做的就是将长公式剪切并粘贴到自身中,使长嵌套有效地起作用。

    是的,这满足了“疯"。但是说实话,有一个简单的方法可以用一个功能完成相同的事情。

    只需选择带有定界数据的列,然后在 Data 菜单项下,选择文本到列

    这将打开一个窗口,您可以在其中使用所需的任何定界符来分割字符串。

    单击鼠标,您可以执行与上面那个疯狂的公式相同的操作……但是,这样做的乐趣在哪里?

    使用Microsoft Excel公式变得疯狂

    因此,您可以使用它。上面的公式证明了创建Microsoft Excel公式来完成某些任务时人们会获得怎样的顶峰。

    有时候,这些Excel公式实际上并不是完成事情的最简单(或最好)的方式。大多数程序员都会告诉您,请保持简单,而Excel公式与其他方法一样。您甚至可以使用内置功能,例如Power Query。首先阅读我们的指南以生成第一个Microsoft Power Query脚本。

    如果您真的想认真使用Excel,则需要通读我们的初学者指南以使用Microsoft Excel。

    标签: Microsoft Excel Microsoft Office 2016 电子表格