Excel公式:10个帮助我保住工作的公式

不要在Microsoft Excel中浪费更多的时间来手动操作。有很多方法可以使用Excel公式来减少在Excel中花费的时间,并提高数据和报表的准确性。

你绝对应该知道的Excel公式:

1。和

公式:=和5,5)或=和A1b1)或=和A1B5)

求和公式完全符合您的期望。它允许您将2个或更多的数字相加。在此公式中也可以使用单元格引用。

上面给出了不同的例子。你可以在里面用逗号分隔数字,它会把它们加在一起。你可以有单元格引用,只要这些单元格中有数字,它就会把它们加在一起。或者你可以在两个细胞之间有一个结肠的一系列细胞,它将把所有单元格中的数字相加。

2。伯爵

公式:=计数A1A10)

计数公式计算区域中包含数字的单元格数。

这个公式只适用于数字:

它只计算有数字的单元格。

**了解有关此按需计数功能的更多信息,网络课程。免费预览**

三。康塔

公式:=countaA1A10)

计算区域中非空单元格的数目。它将计算包含数字和/或任何其他字符的单元格。

Counta公式适用于所有数据类型。

它计算非空单元格的数量,无论数据类型如何。

4。伦恩

公式:=A1)

len公式计算单元格中的字符数。不过要小心!这包括空格。

伦恩

注意公式结果的不同之处:单词之间有10个不带空格的字符,12个单词之间有空格。

5。修剪

公式:=修剪A1)

去掉单元格中的任何空间,单词之间只有一个空格。我发现这个公式非常有用,因为我经常遇到从数据库中提取数据的情况,并且出于某种原因,在合法数据的后面或前面放置了额外的空间。如果您尝试使用if语句或vlookup进行比较,这可能会造成很大的破坏。

修剪公式屏幕截图(2)

我在“我爱Excel”后面加了一个额外的空格。修剪公式删除了多余的空间。使用和不使用修剪公式检查字符计数差异。

6。正确的,左,中间

公式:=右文本,字符数),=左文本,字符数),=中文本,起始号,字符数)。

(注:在所有这些公式中,无论它说“文本”在哪里也可以使用单元格引用)

这些公式返回文本字符串中指定的字符数。是的,给了你文本字符串右侧的字符数,Left为您提供左边的字符数,Mid为您提供从单词中间指定的字符数。告诉中间公式从起始\数字开始的位置,然后它将在起始\数字的右侧获取指定数量的字符。


我用左公式得到第一个词。我拥有它在单元格A1中,仅从左侧抓取第一个字符。这给了我们“我”这个词来自“我爱Excel”

我用中间的公式得到中间的单词。我拥有它在细胞A1中,从字符3开始,然后抓取5个字符。这就给了我们“爱”这个词来自“我爱Excel”

我用了正确的公式得到了最后一个词。我拥有它在单元格A1处,从右侧抓取前6个字符。这给了我们“excel”来自“我爱Excel”

7。V查找

公式:=vLookup查找值,表格数组,科尔指数测距查找

到目前为止我最常用的公式。它的官方描述:“在表的最左边的列中查找值,然后从指定的列中返回同一行中的值…。(查看vlookup的完整解释)基本上,为要查找的公式定义一个值(查找值)。它在表的最左边的列(表数组)中查找该值。

注意:如果可能,使用一个数字作为查找值。这使得确保返回的数据正确匹配变得容易得多。

如果它找到“查找值”的匹配项在“table_array”的左列中它将返回使用“index_num”指定的列中的值。“索引nNUM”是相对于最左列。所以,如果您在A列中找到了表“index”,并且希望返回的内容是B列中的内容,“index_num”因为最左边的列,在这种情况下,A列,是表数组中的第一列,B列是第二列(因此索引号为2)。如果您希望返回C列中的内容,您可以将3作为索引编号。“范围查找”是真值或假值。如果你说的是真的,它会给你最接近的匹配。如果你放错了,它只会给你一个精确的匹配。我只在使用vlookup公式时使用false。

例子:

您有两个列表:1个列表包含销售人员的ID和本季度的销售收入。另一个是销售人员的ID和销售人员的姓名。您希望将销售人员的姓名与该季度的销售人员收入数字相匹配。它们都乱七八糟的,所以要手动匹配,即使是对少数销售人员来说,也会留下很大的误差空间,而且需要花费大量的时间。

第一个列表是从A1到B13。第二个列表是从d1到e25。

在单元格c1中,我将把公式=vlookupB18,1美元13美元,2,假)

b18=查找值(销售人员的ID。这是一个出现在两个列表中的数字。)

1美元$B$13=表\数组。这是我希望公式在最左边的列(本例中是E列)中搜索“lookup_value”的区域。我去F是因为如果它发现比赛在E栏中,我要它返回F列中的内容。(货币符号在那里,因此无论公式移动或复制到何处,表_数组都将保持不变。这称为绝对参考。)

2=索引编号。这将告诉公式远离最左在发生以下情况时返回的列比赛.所以,如果在查找值和表数组最左边的列之间找到匹配项,返回表第2列中同一行的内容(第一列始终是最左边的列)。从1开始,不是0)。

false=告诉公式我只希望它在完全匹配时返回值。

然后,我将沿着第一个列表旁边的C列中的所有单元格复制和粘贴该公式。这会给我一个与销售人员的ID完全一致的列表,本季度销售人员的收入,以及销售人员的姓名。

为了得到一个整洁的销售人员ID列表,销售人员姓名,和销售人员的收入相邻,我用vlookup公式比较1名单到另一个。

这是一个复杂的公式,但非常有用。查看其他一些示例:VLoopUp示例微软的官方例子.

**了解有关此点播中vlookup函数的更多信息,网络课程。免费预览**

8。if语句

公式:=逻辑语句,如果逻辑语句为真,则返回此值,返回如果逻辑语句为假,则执行此操作)

当您在Excel中分析大量数据时,您可能会发现许多场景,数据必须根据不同的情况做出不同的反应。

继续销售示例:假设一个销售人员有一个要满足的配额。您使用vlookup将收入放在名称旁边。现在,您可以使用一个if语句:“如果销售人员达到了他们的配额,说“满足配额”,如果不说“不符合配额”(提示:在这样的语句中说可以使创建公式更容易,尤其是当您遇到更复杂的事情时,比如Excel中的嵌套if语句)。

如下所示:

在vlookup的示例中,我们在B列中有收入,在C列中有人员的姓名(与vlookup一起引入)。我们可以将它们的配额放入D列,然后在单元格e1中放入以下公式:

=如果C3>D3,“MET配额”,“未达到配额”)。

这个if语句将告诉我们第一个销售人员是否达到了他们的配额。然后我们将把这个公式复制粘贴到列表中的所有条目上。每个销售人员都会改变。

从if语句得到结果要比手动计算出结果容易得多。

9。苏米夫康迪夫平均值

公式:=sumif范围,标准,萨姆西兰山脉)=计数范围,标准)=平均值范围,标准,平均值范围

这些公式都有各自的函数(和,伯爵平均值)是否符合标准。还有公式:SUMIF,忠告,平均如果他们将在多个标准的基础上做他们各自的功能,你给出的公式。

我用这些公式例子如果一个人达到了他们的配额,就可以看到平均收入(average if)。总收入(SUMIF),仅针对满足其配额的销售人员,以及达到配额的销售人员的数量(countif)

10。连接

将两个(或多个)不同单元格中的数据组合成一个单元格的花哨词。这可以通过连接Excel公式完成,也可以通过简单地将&符号在两个细胞之间。如果我有“史蒂夫”在单元格A1和“quatrani”中在单元格b1中,我可以把这个公式:=a1&”它会给我“史蒂夫·夸特拉尼”。(““放一个在什么之间的空间您正在与&)组合。我可以使用=连接A1““b1)它会给我同样的东西:“史蒂夫·夸特拉尼”


为作业查找正确的Excel公式

Excel中有316个内置函数。你不会坐在那里记住他们都做了什么(或者至少我希望不会!).幸运的是,Excel有一个内置向导,可以帮助您找到要执行的操作(如果有)的正确公式。

点击“外汇“Excel中公式栏旁边

这将显示一个菜单,您可以在其中键入您正在尝试执行的操作的说明,它将显示正确的Excel公式:

我输入了“删除多余的空格”它返回了我们之前讨论过的修剪公式。

其他Excel公式

我经常使用的还有很多,比如时间公式(现在,今天,月,年,天,等)其他公式,如和和或,和许多其他人一起。

真正的力量在于将这些函数组合成复杂的Excel公式。

今天学习Excel公式!

分解复杂的Excel公式

= IFF错误修剪如果伦恩V查找F7,板21美元10000美元,2,假)>0代用品V查找F7,板21美元10000美元,2,假)“““”,“”)“”)

你看到上面的公式了吗?你会尖叫着跑开吗?!!!

我不怪你

即使对于经验最丰富的Excel用户来说,这也是一个非常令人生畏的公式。

我有一个解决方案可以让你们两个都很容易崩溃,建立Excel公式的复杂组合但首先看看这段由Udemy教练同事制作的视频,亚博网站Mynda Treacy这将揭示如何进入Excel公式的思想的技巧:

那么这个excel公式是怎么回事?

让我们分解其中的所有Excel函数:

  • IF错误

  • 修剪

  • 如果

  • 伦恩

  • V查找

  • 代用品

我不会深入研究每一个公式,但你会明白的。

让我们从最里面的公式开始。这是你试图达到的最终结果。

我们想用一个有空间的短语(用vlookup得到的)来代替这个空间。所以需要用到“excel公式”这样的短语并使之“Excel公式“。

代用品V查找F7,板21美元10000美元,2,假)“““”)

我们使用vlookup公式从Excel工作簿中的其他工作表中获取所需的实际短语(如果它与单元格f7中的内容匹配)。

V查找F7,板21美元10000美元,2,假)

我们使用if语句来说明从vlookup返回的字符数(len)是否大于0,然后运行vlookup,否则,这里什么也没有。这样,如果vlookup什么也没有返回,然后什么都不会发生,这将阻止大多数错误。

如果伦恩V查找F7,板21美元10000美元,2,假)>0代用品V查找F7,板21美元10000美元,2,假)“““”、“”

然后用trim函数包装它,去掉所有多余的空格,在单词之间加1个空格。有时从数据库获取数据时空间可以在单词后添加。这使得比较来自不同来源的数据变得非常困难,所以修剪功能非常有用!

修剪如果伦恩V查找F7,板21美元10000美元,2,假)>0代用品V查找F7,板21美元10000美元,2,假)“““”、“”

最后,我们用iError公式把它包装起来。如果其他公式最终出错,将触发此公式。它可能看起来像不适用,而且很难看。这是有正当理由的,但你不想你的老板一张满是“不适用”符号的纸。

如果组合公式返回任何Excel错误,它将使单元格变为空白,否则将显示公式的结果。

= IFF错误修剪如果伦恩V查找F7,板21美元10000美元,2,假)>0代用品V查找F7,板21美元10000美元,2,假)“““”,“”)“”)

给你了,这个长而复杂的公式被分解成简单的逐步函数。

你可以用同样的方法建立一个长公式

我们的目标是用空白代替空白,所以我们从这个开始。然后我们必须把它从另一张纸上取下来,所以我们用vlookup来做这个,等。,等。

你总是从最里面的公式开始,您正在寻找的最终结果。然后使用其他公式来帮助您处理处理处理处理大型数据集的不同情况。

成为Excel专家的资源:

这篇文章是冰山一角。查看这些其他资源了解更多提示。

显示数据的资源:

Excel公式将帮助您处理数据,你如何呈现这些数据是一种完全不同的动物。看看这个。很棒的Excel仪表板课程如果您有兴趣使用Excel进行报告。

Excel帮助资源:

先生。擅长

不管你在Excel上有多好,一个人在Excel所能做的每件事上都做得很好是太多了。这个先生。Excel论坛是提问的地方。他们在那个网站周围有一个非常活跃的社区,你可能很快就能得到Excel问题的答案。

臭氧氧化锌

有很多例子对于怎样关于Excel或VBA中的任何内容臭氧氧化锌.

学习Excel宏和VBA

公式本身就很棒。使用VBA结合Excel公式创建Excel宏是成为真正的Excel专家的方法。

我使用这些方法完全自动化了我在财富500强公司的业务分析师工作。

如何成为“卓越专家”

那是我在大学的第三年,我在一家期权交易公司实习。这里有一些聪明的人,甚至连看门人都比我更了解卓越。在我之前的那个实习生是一个软件工程师,他基本上写了大量的宏,这些宏几乎自动化了我每天必须做的任务。伟大的!我想,我会有最容易的时间做日常无聊的工作,我可以花时间学习如何成为一个交易员。

好,他离开后一周,一切都破裂了。所有宏都不起作用,它们都相互依赖。“我从来没有受过这方面的训练”我想,因为软件工程师只告诉我什么时候按什么按钮,永远不要手动操作。我开始提供错误的信息,需要请忙碌的人花时间帮助我,只是一般的无助。我尽力了,但我甚至不知道大多数Excel公式,更不用说如何读写VBA代码和创建Excel宏了。

必要性,发明之母

有一天我被带到一边,我的经理说,“在你做这些事情之前我认识那个孩子,你不是来这里写代码的,但是如果你不能及时修复并给我们正确的信息,我们必须向不同的方向前进。”什么?!我想。我没有为此做好准备,也没有受过训练,另一个人写的代码不起作用,我会因此被炒鱿鱼吗?我知道他是认真的。我看到实习生从这里被解雇,它不漂亮。这是一个开放的交易大厅,你没有被拉到别人的办公室,你被无礼地护送离开了地板,每个人都知道发生了什么。

所以,我有个目标要实现,我必须尽快见到它。我开始学习vba,Excel公式,以及如何使所有这些Excel宏工作。在凌晨5:30起床两周后,在别人的帮助下挣扎一天,然后自学直到凌晨2点左右,我已经学会了读写的代码。大约一个月后,我就精通了。大约6个月后,我知道可以写我想要的任何Excel公式,读写VBA代码,解决所有Excel问题,不用在Excel中手工操作。

知道这是一个痛苦的屁股!没有单一的资源来学习我需要学习的东西。我不得不在网上到处找东西,或者问他一些问题。擅长。这就是为什么我创建了一个关于学习Excel的课程。我把过去5年所获得的所有知识,我所有的技巧和窍门Excel VBA教程.

作者简介:

Steve Quatrani是Excel专家,拥有多年使用和教学Excel的经验。他用VBA代码创建了高级系统,使用Excel和其他商业智能软件的高级仪表盘,他继续构建新的方法来查看和分析Excel中的数据,并使这些过程自动化。他教Microsoft Excel VBA视频教程关于乌亚博网站迪米。

来自史提夫:

我惊讶于有些人对Excel知之甚少,他们每天都在使用它!我决定开始教人们如何使用Excel,并利用这个程序的真正力量让他们的生活更轻松。在我的视频课程中,我教你成为卓越专家所需的一切,为了减少手工制作Excel的时间,为了让你的生活更轻松,所有这些都使用Excel。

推特:ExelternLinkedin:史提夫·夸特拉尼谷歌+:史提夫·夸特拉尼