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

Don't waste any more hours in Microsoft Excel doing things manually.There are many ways to use Excel formulas to decrease the amount of time you spend in Excel and increase the accuracy of your data and your reports.

Excel Formulas You Should Definitely Know:

1。SUM

公式:=和5,5)或=和A1B1) or =SUMA1B5)

求和公式完全符合您的期望。It allows you to add 2 or more numbers together.在此公式中也可以使用单元格引用。

The above shows you different examples.You can have numbers in there separated by commas and it will add them together for you,你可以有单元格引用,只要这些单元格中有数字,它就会把它们加在一起。或者你可以在两个细胞之间有一个结肠的一系列细胞,它将把所有单元格中的数字相加。

2.COUNT

公式:=计数A1A10)

The count formula counts the number of cells in a range that have numbers in them.

这个公式只适用于数字:

It only counts the cells where there are numbers.

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

三。COUNTA

公式:=countaA1A10)

Counts the number of non-empty cells in a range.它将计算包含数字和/或任何其他字符的单元格。

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

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

4。伦恩

Formula: =LENA1)

len公式计算单元格中的字符数。Be careful though!这包括空格。

伦恩

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

5。TRIM

公式:=修剪A1)

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

trim-formula-screenshot (2)

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

6。正确的,LEFT,中间

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

(Note: In all of these formulas,无论它说“文本”在哪里也可以使用单元格引用)

这些公式返回文本字符串中指定的字符数。RIGHT gives you the文本字符串右侧的字符数,Left为您提供左边的字符数,and MID gives you the specified number of characters from the middle of the word.You tell the MID formula where to start with the start_number and then it grabs the specified number of characters to the right of the start_number.


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

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

我用了正确的公式得到了最后一个词。我拥有它at cell A1 and grab the first 6 characters from the right.This gives us "Excel"来自“我爱Excel”

7。V查找

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

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

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

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

例子:

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

The first list goes from A1 to B13.第二个列表是从d1到e25。

In cell C1 I would put the formula =VLOOKUPB18,1美元13美元,2,FALSE)

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

1美元$B$13=表\数组。This is the area I want the formula to search the leftmost column (column E in this case) for the "lookup_value".我去F是因为如果它发现比赛in column E,I want it to return what's in column F.(货币符号在那里,因此无论公式移动或复制到何处,表_数组都将保持不变。This is called an absolute reference.)

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

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

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

In order to get a nice neat list of Sales Person ID,Sales Person Name,和销售人员的收入相邻,我用vlookup公式比较1名单到另一个。

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

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

8。IF Statements

公式:=logical_statement,如果逻辑语句为真,则返回此值,返回this if logical statement is false)

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

Continuing with the sales example: Let's say a salesperson has a quota to meet.您使用vlookup将收入放在名称旁边。现在,您可以使用一个if语句:“如果销售人员达到了他们的配额,说“满足配额”,如果不说“不符合配额”(提示:在这样的语句中说可以使创建公式更容易,尤其是当您遇到更复杂的事情时,比如Excel中的嵌套if语句)。

如下所示:

在vlookup的示例中,我们在B列中有收入,在C列中有人员的姓名(与vlookup一起引入)。We could put their quota in column D and then we'd put the following formula in cell E1:

=如果C3>D3,“MET配额”,"Did Not Meet Quota")

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

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

9。SUMIF,康迪夫AVERAGEIF

公式:=sumifrange,标准,萨姆西兰山脉)=计数range,标准)=AVERAGEIFrange,标准,average_range)

These formulas all do their respective functions (SUM,伯爵AVERAGE) IF the criteria are met.还有公式:SUMIF,COUNTIFS,AVERAGEIFS where they will do their respective functions based on multiple criteria you give the formula.

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

10.CONCATENATE

将两个(或多个)不同单元格中的数据组合成一个单元格的花哨词。这可以通过连接Excel公式完成,也可以通过简单地将&symbol在两个细胞之间。如果我有“史蒂夫”在单元格A1和“quatrani”中in cell B1 I could put this formula: =A1&"它会给我“史蒂夫·夸特拉尼”。(The"“放一个在什么之间的空间you are combining with the &).我可以使用=连接A1““b1)它会给我同样的东西:“史蒂夫·夸特拉尼”


为作业查找正确的Excel公式

Excel中有316个内置函数。You're not going to sit there and memorize what all of them do (or at least I hope not!).幸运的是,Excel有一个内置向导,可以帮助您找到要执行的操作(如果有)的正确公式。

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

This brings up a menu and in there you can type in a description of what you are trying to do and it will bring up the correct excel formula:

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

其他Excel公式

我经常使用的还有很多,比如时间公式(现在,今天,月,年,天,etc.),其他公式,如和和或,along with many others.

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

今天学习Excel公式!

分解复杂的Excel公式

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

Do you see formulas like the one above and run away screaming AHHHHHHHHHHHHHHHHHHHHHHHHH!!!

我不怪你ya

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

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

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

Let's break down all the Excel functions in it:

  • IF错误

  • TRIM

  • 如果

  • 伦恩

  • V查找

  • 代用品

我不会深入研究每一个公式,but you'll get the point.

让我们从最里面的公式开始。This is the end result that you are trying to accomplish.

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

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

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

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

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

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

然后用trim函数包装它,去掉所有多余的空格,besides 1 space in between words.有时从数据库获取数据时空间can be added after a word.这使得比较来自不同来源的数据变得非常困难,so the TRIM function comes in handy a lot!

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

Finally we wrap it in the IFERROR formula.如果其他公式最终出错,将触发此公式。它可能看起来像不适用,而且很难看。这是有正当理由的,但你不想你的老板一张满是“不适用”符号的纸。

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

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

给你了,this long complicated formula broken down into simple step by step functions.

You can use the same method for building up a long formula

Our goal is to SUBSTITUTE the spaces for nothing so we start with that.然后我们必须把它从另一张纸上取下来,所以我们用vlookup来做这个,等。,等。

You always start with the innermost formula,the end result that you are looking for.然后使用其他公式来帮助您处理处理处理处理大型数据集的不同情况。

成为Excel专家的资源:

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

显示数据的资源:

Excel Formulas will help you deal with data,how you present that data is a whole other animal. Check out this很棒的Excel仪表板课程如果您有兴趣使用Excel进行报告。

Excel帮助资源:

先生。Excel

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

臭氧氧化锌

有很多例子对于how关于Excel或VBA中的任何内容Ozgrid.

学习Excel宏和VBA

公式本身就很棒。Using VBA to create Excel Macros in combination with Excel Formulas is how you become a true Excel expert.

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

如何成为“卓越专家”

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

好,the week after he left,一切都破裂了。None of the macros were working and they all depended on each other.“我从来没有受过这方面的训练”I thought,因为软件工程师只告诉我什么时候按什么按钮,永远不要手动操作。I started giving out wrong information,需要请忙碌的人花时间帮助我,and was just generally helpless.我尽力了,但我甚至不知道大多数Excel公式,much  less how to read and write VBA code and create Excel Macros.

必要性,the Mother of Invention

有一天我被带到一边,我的经理说,“在你做这些事情之前我认识那个孩子,and you weren't brought here to write code,但是如果你不能及时修复并给我们正确的信息,我们必须向不同的方向前进。”什么?!我想。我没有为此做好准备,也没有受过训练,另一个人写的代码不起作用,and I'm going to get fired for it?我知道他是认真的。我看到实习生从这里被解雇,it wasn't pretty.It was an open trading floor,你没有被拉到别人的办公室,你被无礼地护送离开了地板,每个人都知道发生了什么。

所以,我有个目标要实现,and I had to meet it quickly.我开始学习vba,Excel公式,以及如何使所有这些Excel宏工作。在凌晨5:30起床两周后,在别人的帮助下挣扎一天,然后自学直到凌晨2点左右,我已经学会了读写的代码。大约一个月后,我就精通了。After about 6 months I knew enough to write any Excel formula I wanted,读写VBA代码,解决所有Excel问题,不用在Excel中手工操作。

知道这是一个痛苦的屁股!没有单一的资源来学习我需要学习的东西。I had to go all over the web to find things or ask questions on Mr.擅长。这就是为什么我创建了一个关于学习Excel的课程。我把过去5年所获得的所有知识,我所有的技巧和窍门Excel VBA教程.

作者简介:

Steve Quatrani is an excel expert with years of experience using and teaching Excel.他用VBA代码创建了高级系统,使用Excel和其他商业智能软件的高级仪表盘,他继续构建新的方法来查看和分析Excel中的数据,并使这些过程自动化。他教Microsoft Excel VBA视频教程关于乌亚博网站迪米。

From Steve:

I was astounded by how little some people knew about Excel and they use it every day!我决定开始教人们如何使用Excel,并利用这个程序的真正力量让他们的生活更轻松。在我的视频课程中,我教你成为卓越专家所需的一切,to spend less time doing stuff manually Excel,and to make your life a whole lot easier all using Excel.

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