Excel公式几乎可以做任何事情。在本文中,您将通过三个有用的示例了解microsoftexcel公式和条件格式的强大功能。
我们已经介绍了一些更好地使用Excel的不同方法,例如使用Excel创建自己的日历模板或将其用作项目管理工具。
无论在电子表格中**什么样的数据,Excel公式和规则都可以用来自动操作数据和信息。
让我们深入了解如何使用公式和其他工具更好地使用Microsoft Excel。
人们不经常使用的工具之一是条件格式。如果您正在Microsoft Excel中查找有关条件格式的更高级信息,请确保查看Sandy关于在Microsoft Excel中使用条件格式格式化数据的文章。
通过使用Excel公式、规则或一些非常简单的设置,您可以将电子表格转换为自动化的仪表板。
要使用条件格式,只需单击“主页”选项卡,然后单击“条件格式”工具栏图标。
在条件格式下,有很多选项。这些都超出了本文的范围,但大多数都是关于基于该单元格内数据的高亮、着色或着色单元格。
这可能是条件格式最常用的用法,例如使用小于或大于公式将单元格变成红色。了解有关如何在Excel中使用IF语句的详细信息。
使用较少的条件格式工具之一是Icon Sets选项,它提供了一组很好的图标,您可以使用这些图标将Excel数据单元转换为仪表板显示图标。
单击“管理规则”时,它将带您进入条件格式规则管理器。
根据您在选择图标集之前选择的数据,您将看到管理器窗口中指示的单元格,以及您刚刚选择的图标集。
当你点击编辑规则,你会看到一个对话框,在那里魔术发生。
在这里,您可以创建逻辑公式和方程式,以显示所需的仪表板图标。
此示例仪表板将显示用于不同任务的时间与预算时间。如果你超过预算的一半,就会显示黄灯。如果你完全超出预算,它就会变成红色。
如您所见,此仪表板显示时间预算并不成功。
几乎一半的时间都花在了超出预算的金额上。
是时候重新集中精力,更好地管理你的时间了!
如果您想使用更高级的microsoftexcel函数,这里还有一个。
您可能熟悉VLookup函数,它允许您在列表中搜索一列中的特定项,并从与该项相同的行中的不同列返回数据。
不幸的是,该函数要求您在列表中搜索的项目位于左列,而您要查找的数据位于右侧,但是如果它们被切换了呢?
在下面的示例中,如果我想从以下数据中找到我在2018年6月25日执行的任务,该怎么办?
在本例中,您正在搜索右侧的值,并希望在左侧返回相应的值—与VLookup通常的工作方式相反。
如果你阅读microsoftexcelpro用户论坛,你会发现很多人说这在VLookup中是不可能的,你必须使用索引和匹配函数的组合才能做到这一点。这不完全正确。
通过在VLookup中嵌套CHOOSE函数,可以让它以这种方式工作。在本例中,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中查找数据的完整文章。
这里有一个更疯狂的Excel公式给你。
在某些情况下,您可以从由分隔数据字符串组成的外部源将数据导入Microsoft Excel。
引入数据后,您需要将数据解析到各个组件中。下面是一个由“;”字符分隔的姓名、地址和电话号码信息的示例。
下面是如何使用Excel公式解析这些信息(看看你是否能在精神上跟随这种疯狂):
对于第一个字段,要提取最左边的项(人名),只需在公式中使用LEFT函数。
"=LEFT(A2,FIND(";",A2,1)-1)"下面是这个逻辑的工作原理:
在本例中,最左边的文本是“Ryan”。任务完成了。
但是其他部分呢?
可能有更简单的方法可以做到这一点,但由于我们想尝试创建最疯狂的嵌套Excel公式(实际上是可行的),我们将使用一种独特的方法。
要提取右侧的部分,需要嵌套多个右函数,以将文本的部分向上抓取,直到第一个“;”符号,然后再次对其执行左函数。下面是提取地址的街道号部分的方法。
"=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)"看起来很疯狂,但拼凑起来并不难。我所做的只是做了这个功能:
RIGHT(A2,LEN(A2)-FIND(";",A2))把它**左函数中每个有“A2”的地方。
这将正确地提取字符串的第二部分。
字符串的每个后续部分都需要创建另一个嵌套。所以现在你只需要用你为上一节创建的疯狂的“右”等式,然后把它传递到一个新的右公式中,在你看到“A2”的地方粘贴上一个右公式。这就是它的样子。
(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))))))然后取这个公式,把它放到原来的左公式中,只要有“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)"这个公式正确地从原始字符串中提取了“波特兰,我04076”。
要提取下一个部分,请再次重复上述过程。
你的Excel公式可能会变得很循环,但你所做的只是将长公式剪切和粘贴到自身中,生成实际工作的长嵌套。
是的,这符合“疯狂”的要求。但老实说,有一种更简单的方法可以用一个函数完成同样的事情。
只需选择包含分隔数据的列,然后在“数据”菜单项下,选择“文本到列”。
这将打开一个窗口,您可以在其中按所需的任何分隔符拆分字符串。
在几次点击中,你可以做与上面疯狂公式相同的事情。。。但这其中的乐趣何在?
所以你有了它。上面的公式证明了一个人在创建microsoftexcel公式来完成某些任务时是多么的过分。
有时候,那些Excel公式实际上并不是完成事情的最简单(或最好)的方法。大多数程序员都会告诉你保持简单,Excel公式和其他公式一样简单。您甚至可以使用内置的功能,如powerquery。从阅读我们的指南开始,生成您的第一个microsoftpower查询脚本。
如果您真的想认真使用Excel,您将希望阅读我们的初学者指南,以使用Microsoft Excel。它有一切你需要开始提高你的生产力与Excel。之后,请务必查阅我们的Excel基本功能备忘表。
...不要沉湎于你的荣誉!花点时间扩展你的知识。你会得到疯狂的Excel公式,可以做惊人的事情。 ...
女士们,先生们:是时候开始你们的电子表格,微调这些公式,并为首届Excel世界锦标赛做好准备了!没错——世界上最受欢迎的电子表格软件现在有了一个由微软赞助的竞赛,总冠军将获得大奖。 ...
...创建个人联邦所得税计算器电子表格。首先,有几个Excel公式需要学习: ...
如果你不使用microsoftexcel公式和函数,那你就完全错了。如果你学到了一些,他们可以大大增加应用程序的功能,加快你的工作流程。 ...
... 由于所有不同的格式选项可用,它可以诱惑去有点疯狂的颜色。但请记住,少就是多。对于格式化来说,这是绝对正确的。 ...