Excel公式:最流行的函数和工具,有例子

网络远程服务-解决各类疑难杂症-苹果电脑安装windows系统-电脑重装系统 Win7/8/10   远程安装各类大型软件:CAD/PS/3Dmax等...   无论是哪一种情况只收30元:QQ/微信:878805720  

确保这些日期和时间功能,数学技巧和数据处理工具都可以随时使用。

从简单的数学到非常复杂的统计,逻辑和工程任务,例如IF语句(我们常年最喜欢的故事之一),Excel在其函数库中拥有超过475个公式。AND,OR,NOT功能;COUNT,AVERAGE和MIN / MAX。

下面介绍的基本功能是Excel中最受欢迎的公式之一,每个人都应该知道。为了帮助您学习,我们还提供了一个电子表格,其中包含我们下面介绍的所有公式示例。

该电子表格包含该故事中涉及的每个公式的选项卡以及示例数据。京东萨顿

1.今天/现在

在“公式”>“日期和时间”下的下拉菜单上列出了24种“日期和时间”功能:您可以创建11种日期格式,10种时间格式以及许多用户定义的自定义格式。TODAY函数显示当前的月份,日期和年份;而NOW功能则显示当前的月份,日期,年份和时间。如果您是那些总是忘记过时的人,那么这是一个方便的功能。

1.在单元格A1中输入以下公式:=TODAY()并按Enter键。

2.接下来,使用= NOW()在A1中键入该函数。

重要提示:为什么要键入?为了使这两个公式正常工作,必须在“主页”单元格中输入它们,即A1,否则,当电子表格重新计算时它们不会自动更新。按Shift- F9仅计算/重新计算活动电子表格,或者按F9查看整个工作簿。

在A1中输入这些功能之一后,可以重新设置日期和时间的格式或使用系统默认值。TODAY函数的默认格式为8/29/18,NOW的默认格式为8/29/18 21:57。如果这些都不适合您,请更改它们。

3.将光标放在要更改的日期或时间上,然后选择“ 首页 > 格式 >设置单元格格式”。

4.在“设置单元格格式”对话框的窗口中,从“ 数字”选项卡下的“ 类别”面板中选择“ 日期(或时间)” 。

5.在“类型”对话框窗格中滚动“日期/时间”格式列表,然后选择最适合您的项目的格式。

01日期和时间功能和格式
日期和时间功能和格式

2. SUM函数

= SUM可能是Excel(或任何其他电子表格程序)中最常用的函数,它可以做到这一点:它对列,行或数字范围求和,但不只是求和。它还会减去,相乘,除法并使用任何比较运算符返回1(真)或0(假)的结果。

您也可以使用加号(+)代替函数SUM来获得相同的结果。例如,这两个公式均产生相同的答案:=SUM(J7 * 9)和= +(J7 * 9)。在下面的电子表格图形中,请注意,单元格E3至E8使用SUM函数,而单元格E9至E9至E14使用加号(+),结果是相同的。

您可以手动输入SUM函数(或+号),也可以从“ 公式” >“ 数学和触发”(按钮)下的“功能区”菜单中选择它,然后从下拉列表中进行选择。或从功能区菜单中选择“ 公式” >“ 插入函数”,然后向下滚动列表并从中选择它。

如果你只是想添加号码的一列,在该列底部的单元位置光标,点击自动求和按钮> SUM,然后按Enter键。Excel用绿色边框框住数字列,并在当前单元格中显示公式。

02使用多个计算运算符的求和函数
使用多个计算运算符的求和函数

当您需要计算的数字范围在多个像元上具有多个计算运算符而变得复杂时,就会出现问题,例如:=SUM(H1 + I1 * J1-M1 * J1。还记得您的高中数学吗?如果公式中的数字不是正确分组,答案将是错误的,请注意以下屏幕截图(图2)。

在H2到P2中输入以下列标题(使用Alt + Enter在单个单元格中堆叠标题):日薪,加奖金,工作天数,总薪水(公式),每天9.00美元的负餐,每月总收入,公式和注释。

注意:公式列仅供参考,不会为电子表格提供任何内在价值。它们只是为了您的利益“显示”公式(因此您可以查看所使用的每个公式的语法)。

对于本练习,您可以在H3:11,I3:11和J3:11中输入相同的值,中间可以有或没有空白行(再次添加以方便查看)。完成如下操作:$ 86.00,$ 20.00、22.0个工作日,其余为公式。请注意,在构建每个公式时,我们最终将这些步骤组合为一个公式。

我们从三个单独的公式开始。第一种是将每日收入加奖金乘以一个月的工作天数,等于K3单元格中的总工资:=SUM(H3 + I3 * J3)。请注意,答案是$ 526.00。看起来不对。

在将公式复制到列中其余单元格之前,请使用计算器检查公式以确保其正确。

K3中的公式是错误的。它需要根据计算顺序使用逗号或括号将数字分组。

注意单元格K4中的更正公式:=SUM(H4 + I4)* J4。再次检查您的数字(使用计算器),并注意该公式正确。正确的答案是$ 2,332.00。

5.第二个公式(在M4中)为= SUM(J4 * 9)乘以工作日(22)乘以$ 9.00,即每天的用餐费用。正确答案是$ 198.00。

6.第三个公式(在N4中)计算月收入减去餐食:=SUM(K4-M4); 答案是$ 2,134.00。

7.在下一组(H6:N8)中,M6:M8中的公式保持不变:=SUM(J7 * 9)等-同样,这是工作日数乘以用餐费用。但是,将消除K列中的公式,然后将其与M列中的公式组合:=SUM(H7 + I7)* J7-M7。请注意,语法(公式的结构或布局)在单元格N7和N8中正确,但在N6中不正确。

8.下一组(H10:H11)将M列中的公式与N列中的公式结合起来:=SUM(H11 + I11)* J11-(M11 * J11)-请注意,N10中的公式不正确。通过将这些公式组合为一个,可以消除K和L列。

9.此外,您现在可以在通货膨胀指示价格上涨时,在M列(M10:M11)中更改餐点价格,而不用“硬编码”餐点价格(如M3:M4和M6:M8中所示)。更改公式。

03使用多个参数的复杂求和函数
使用多个参数的复杂SUM函数

3. RAND功能

RAND函数非常简单,传统上用于统计分析,密码学,游戏,赌博和概率论以及许多其他事情。在Excel中,RAND函数生成一个介于0和1之间的随机数。但是,每次您输入新数据并按Enter键时,刚创建的随机数列表都会更改。如果需要维护随机数列表,则必须将单元格格式化为值。

1. 在A3到A14列中输入函数= RAND()。选择该列,然后按Ctrl + C(用于复制)或单击“ 主页”选项卡下的“ 复制”按钮,然后从下拉菜单中选择“ 复制 ”。将光标移到单元格B3上,然后选择主页 > 粘贴 > 选择性粘贴。从“ 选择性粘贴”对话框窗口中单击“ 值”按钮,然后单击“ 确定”。

2.现在,列表包含值而不是函数,因此它不会更改。请注意(在编辑栏中),随机数在小数点后(Excel默认为9)后有15位数字,您可以根据需要进行更改(如单元格F3中所示)。只需单击“ 主页”选项卡下“ 数字”组中的“ 增加小数”按钮。

3.如果您喜欢使用整数,请在单元格F3中输入以下公式:=INT(RAND()* 999),您将获得一个3位数的随机数。将公式向下复制到F12,然后在字符串中添加另一个“ 9”以在您的随机数中添加另一个数字,例如,四个9等于四个数字,五个9等于五个数字。同样,您必须复制列表和“粘贴为值”以维护静态列表。

04使用rand函数创建随机数列表 
使用RAND函数创建随机数列表

4. COUNT个功能

使用COUNT函数计算单元格范围内数值的数量;例如:C4:C15包含McGregor先生需要为其商店订购的园艺工具的数量。请注意,答案是10(12个),因为COUNT函数不包含空白单元格。但是,如果输入零,数字代码或日期,Excel会将其视为“已占用”单元格并将其包括在答案中。

在C列(数量)中输入10个数字。用空格替换一个数字(或在空格键上点击),然后用分号替换另一个数字,然后在单元格C7中输入日期。

在数字列表(C16)的底部输入此公式:=COUNT(C4:C15)。答案是10(满分12),因为Excel计算了所有数字和日期,但忽略了空白单元格(包含空格)和单元格C8中的标点符号。

如果要在键盘上包括数字值,逻辑或错误值,文本,空格(来自空格键),标点符号,符号或任何其他字符,请使用COUNTA函数。

1.在D列(价格)中输入12美元。用问号替换一个单元格,用符号替换另一个单元格,并用一些文本替换另一个单元格。

2.在D16中输入此公式:=COUNTA(C4:C15)。答案是12(满分12),因为Excel包含了所有“非数字”值和字符。

3.请注意,第18行(C和D)显示了C和D 16中的实际公式。

05 count count计算包含某物的细胞数量
COUNT&COUNTA计算包含某些内容的单元格的数量

5.平均功能

大多数人都知道,平均值是通过将列表中的所有值相加然后除以列出的值数来确定的;例如4 + 5 + 3 = 12/3 = 4,这是平均值。您可以使用SUM函数并将除法全部添加到一个公式中,也可以只使用AVERAGE函数。语法为:=AVERAGE(range)。

1.在A列中输入一些数字。在列表底部输入AVERAGE函数:=AVERAGE(A4:A13)并注意答案(在本例中为53)。即:=SUM(A4:A13 / 10)= 53。

2.接下来,在C列中输入更多数字,但是这次将一些文本添加到一个单元格,将标点符号添加到另一个单元格,并将空格添加到另一个单元格。输入相同的公式:=AVERAGE(A4:A15),注意答案为78。要进行验证,请输入SUM公式,以忽略包含非数字字符的单元格:

包含文本,逻辑值,标点符号或空单元格的单元格将被忽略;但包含零的单元格(作为数字,但不作为文本)。文本零将在零之前有一个撇号,您在单元格中看不到该撇号,但在公式栏中可以看到该撇号。

重要说明:如果要从大型机或外部外部资源导入大型数据库,则有时数字会以文本形式导出。您怎么知道数字是否真的是文字?通常,文本是左对齐的,数字是右对齐的,但是,因为现在每个人都出于审美目的格式化电子表格,所以这种方法不可靠。另一个选择是快速滚动一长串导入的数字,并观看编辑栏。如果在任何数字前都看到撇号,则这些条目为文本。最后,在单元格的左上角查找绿色三角形。除非电子表格的先前所有者指示Excel忽略此错误,否则单元格的内容为文本。

如果值是文本,则必须立即将它们转换为数字。为此,请移至列表中实际为文本的第一个数字。突出显示模拟数字的文本范围。右键单击该范围内第一个文本单元格左侧的黄色警告标志。从弹出列表中单击“ 转换为数字”,此操作完成。

06平均功能  平均功能

6. MIN / MAX功能

使用MIN函数查找值范围内的最小数字,使用MAX函数查找最大值。这些函数的语法为:=MIN(range); = MAX(range)其中range等于您要计算的数字列表。

此功能的常见用法是:例如,找到教室的最高/最低成绩;商店中最高/最低销售额;您最喜欢的棒球队的最高/最低击球率;等等。

有人会问,为什么不只是对数据排序?您可以,但是每次数字更改时,都必须重新排序。而且,如果您要对具有很多记录/行的多个列/字段进行排序,则sort选项可能会变得很麻烦。

即使您添加更多行(只要使用现有范围内的“插入”>“行”功能添加行,即在包含该行的单元格上方),MIN / MAX函数仍保持不变,无论数据如何变化。式)。

在A4:A11栏中输入一些数字,然后在A13中输入此公式:=MIN(A4:A11)在A14中输入此公式:=MAX(A4:A11)。

注意: MIN / MAX函数忽略空单元格,TRUE / FALSE答案,文本,文本模拟数字,符号和标点符号。

接下来,强大的串联功能结合了多个单元格的数据价值!继续阅读以获取更多出色的公式。

07最小最大功能

7. CONCAT / CONCATENATE

函数CONCAT和CONCATENATE的作用相同:它们都将多个单元格,范围或数据串组合到一个单元格中。此功能最常见的用法是将名字和姓氏合并到一个单元格中,或将城市,州和邮政编码合并到一个单元格中。

注意: CONCAT替换了Excel 2016中的CONCATENATE,但是两个功能仍然可用。请注意,CONCAT仅出现在“公式”>“文本”和“公式”>“插入函数”>“类别”>“文本”下,而CONCAT和“ CONCATENATE”都出现在“公式”>“插入函数”>“类别”>“全部”下。

在A列中输入一些名字,在B列中输入名字。在C列中输入以下公式:=CONCATENATE(A4,“”,B4)或= CONCAT(B4,“”,C4),然后向下复制公式。什么是双引号?请参阅下面的注释#2。

2.在F列中输入一些城市(或滑雪胜地),在G列中输入州,在H列中输入邮政编码。在I列中输入以下公式:=CONCATENATE(F4,“,”,“ ,, G4,” “,H4)。

注意:如果要在名字和姓氏之间留一个空格,则必须在公式中的引号内输入该空格。标点符号也是如此,例如城市和州之间的逗号。在下面的公式中,“,”(用逗号引号引起的红色)告诉Excel在F15(城市)的数据和G15(州)的数据之间插入逗号。在““ (引号空间引号紫色)增加了F15(市)和G15(州)之间的逗号后面输入一个空格和G15(州)和H15(邮政编码)之间的另一空间。

= CONCATENATE(F15,”,”,”“,G15,”“,H15)。

08使用concatconcatenate将多个单元格中的数据合并为一个单元格
使用CONCAT和CONCATENATE将来自多个单元格的数据合并为一个单元格

8.修剪

此功能删除了由于用户错误,从Internet等外部源下载数据或从另一台计算机系统导入数据而导致感染数据的多余(或填充)空间。而且,您不必“告诉Excel”空格在每个单元格中的文本字符串中的位置;它识别出多余的空间并删除它们。注意; 但是,它不会删除单词中间的空格。语法很简单:=TRIM(单元格地址)。

1.在A列中输入一些数据。在多个单词的前面,后面和中间添加一些空格,然后在单元格A4中输入以下公式:=TRIM(A4)。

2.向下复制公式。就这么简单!

注意:在某些情况下,此功能不起作用,并且网页中使用了不间断的空格字符。十进制值为160,HTML代码为&nbsp。您可以结合使用TRIM,CLEAN和SUBSTITUTE删除此字符。

09使用trim从数据中删除多余或填充的空格
使用TRIM删除数据中多余或填充的空格

9.上/下/上

另一个简单的组,这些函数将单元格或单元格范围内的文本转换为大写,小写或适当的大小写。适当的大小写是大写的第一个字母,其余的小写。语法很简单:函数,单元地址。

1.在A列中输入一些大小写混合的数据;例如,加利福尼亚,NEW约克,西班牙文。在B列中输入以下公式:=UPPER(A4),在C列中:=LOWER(A4)和D列中:=PROPER(A4)。

2.请注意,Excel会纠正所有放错位置的大小写错误并正确转换数据。复制公式,仅此而已。

注意:在Word中,您可以使用Shift-F3在大写,小写和适当的大小写之间循环,但是此快捷键在Excel中不可用。请注意,Excel函数= PROPER在Word中称为句子大小写。

10使用大写小写更改字符的大小写
使用大写,小写和大写字母来更改字符的大小写

10. REPT

当Lotus 1-2-3是镇上唯一的游戏时,您可以输入一个反斜杠,后跟任意字符,Lotus会在整个单元中重复该字符。如果像元宽度变大或变小,字符也会变大。在Excel中,此功能由REPT函数处理。它效率不高,因为必须将字符添加到公式中,然后指定要重复该字符多少次。这意味着,如果单元格宽度增加,则重复字符不会增加;如果单元格宽度减小,则重复字符会渗入相邻的单元格中。

该函数的语法:=REPT(“ *”,5) ; = REPT(“ —”,10),= REPT(“ +”,12)。您可以在键盘上重复任何字符以及符号。

11.使用rept功能
使用REPT功能

11. IF声明

IF函数(也通常称为IF语句)的工作方式如下:IF,否则。基本上,这意味着如果条件为真,则做一件事,否则/否则做其他事情。例如,如果小狗是拉布拉多犬,则购买蓝领,否则购买其他红领。

IF语句的语法(命令在公式中的组织方式)为:=IF(logic_test,value_if true,value_if_false)。IF语句在所有编程语言中都使用,尽管语法可能略有不同,但是此函数提供的结果相同。

1.输入以下列标题:已售出的Cookie盒;三等奖=售出数量大于500,少于1000;二等奖=出售数量超过1000,少于1500;一等奖=大于1500售出,小于2000;大奖=超过2000已售

2.在A4:A13栏中输入一些数字。混合起来,以便在所有“已售出”列中获取数据。

3.在B4中输入以下公式:=IF($ A4> 500,$ A4,0)。

注意上式中列字母“ A”前的$符号。将光标放在公式中的第一个“ A”上,然后使用功能键F4循环浏览绝对和相对参考。当$符号在“ A”之前(对于公式中的每个A)停止。这告诉Excel不要更改列字母,而只能在复制此公式时更改行号。如果在列字母和行号之前都加了美元符号,则两者都不会改变。

4.将B4中的公式复制到C4,D4和E4,然后进行如下编辑:C4 = IF($ A4> 1000,$ A4,0) ; D4 = IF($ A4> 1500,$ A4,0) ; 和E4 = IF($ A4> 2000,$ A4,0)。然后抄下来。

5.公式有效,但是您必须查看每列以查看获奖者,因为每一列显示的所有值都大于公式中的值。可以使用一个小的电子表格,但是不能用于比单个屏幕大的文件。

6.为此,我们需要一个嵌套的IF语句。从第20行开始重复上面的数字1、2和3;但是要代替上面3中的公式,请在B20中输入以下公式:=IF(AND($ A20> 500,$ A20 <1000),$ A20,0)。

7.重复上面的数字4,但是编辑如下公式:C20 = = IF(AND($ A20> 1000,$ A20 <1500),$ A20,0) ; D20 = = IF(AND($ A20> 1500,$ A20 <2000),$ A20,0) ; 和E20 = = IF($ A20> 2000,$ A20,0)。是的,最后一个是不同的,因为没有“少于”的数量。然后抄下来。现在,您可以查看每一列,并立即确定该类别的获奖者。

12种基本的if语句和嵌套的if语句

12. AND / OR

AND和OR是程序员环境中的常用函数,也称为布尔运算符(以及NOT)。AND表示查询中的所有条件都必须为true;OR表示至少一个条件必须为真。

例如,寻找具有MS Word和MS Excel经验的申请人意味着该申请人必须具备两种技能才能胜任。此条件将提供TRUE结果。寻找具有MS Word或Excel的申请人意味着申请人必须具有一个或另一个,但不一定两个都拥有。也是TRUE的结果。显然,如果没有这两种技能,那么结果都是假。

1.复制图13中电子表格中的数字,或下载完整的工作簿(下面的链接)。

2.在单元格D4中输入以下AND公式:=AND($ B4> = 501,$ C4 <= 500)。再次注意$符号。然后复制到单元格D13。

3.在单元格F4中输入以下公式:=OR($ B4> = 501,$ C4 <= 500),然后向下复制。注意带边框的行中的结果;即5、8和13。AND结果均为FALSE,因为两个条件均为假(或不为真);而OR结果都为TRUE,因为其中一个条件为true,而另一个条件为false。

如果这看起来令人困惑,请研究B和C列中的数字。然后阅读为AND函数,然后为OR函数计算的公式,这将更有意义。

14,加号或功能
AND加或功能

13.不

上面已经解释了AND和OR函数(也称为布尔运算符)如何工作,该混合中的第三个布尔运算符是NOT函数。是否曾经搜索过很长的数据列表,希望您可以删除所有不适用的条目?例如,我想看到关于除核(或非核)以外的替代能源的所有可用信息。

在Excel中,这是一个简单的任务。创建一个由25家提供各种替代能源以及这些资源是什么的公司组成的列表(A,B,C列;从第4行开始)。在单元格C4中输入以下公式:=NOT(B4 =“ Nuclear”)。然后将公式从C4复制到C5:C28。

如果响应为TRUE,则能源不是核能。如果响应为FALSE,则能源为核能。是的,这是相反的逻辑,您可能不会立即看到此功能的需要,但是,如果您是Excel的狂热用户,您会发现很多将来使用此公式的原因。

提示:请记住,布尔逻辑适用于所有数据库程序,包括您喜欢的搜索引擎。布尔运算符AND和OR必须大写才能用作运算符。在Google中,布尔型NOT运算符是减号;例如,要列出除核能以外的所有替代能源,请在搜索字段框中输入以下内容:列出所有替代能源–核。

到处都有分隔符的文本和数字?继续阅读公式可以解决该问题。

 15n using the not boolean operator

14.权利和文字转栏

想象一下,您的老板刚刚向您发送了一个文件,其中包含100,000个名称和地址,这些文件是从多个不同数据库系统(例如Oracle RDBMS,SAP Sybase ASE,Informix,MongoDB,Redis和Couchbase)的多个数据转储中编译而成的。该文件采用CSV(逗号分隔值)定界格式,因此您可以在Excel中打开它,但是定界符无处不在-有些是逗号,有些是空格,有些是制表符,依此类推。您的工作是将运行数据重新格式化为五个字段:“名称”,“地址1”,“地址2”,“城市”和“州”。

答:首先,将城市,州和邮政编码分为不同的列。选择C列(城市,州/省,邮政编码),然后选择Data > Text to Columns。确保分隔的按钮被选中,然后单击下一步。在下一个屏幕上,确保选中了逗号框,然后单击下一步。浏览列表以确保分隔正确,然后单击完成。Excel将一列分为两部分。

B.现在我们需要将邮政编码与州名分开。对于此任务,“文本到列”选项将不正确,因为唯一可用的定界符是空格。因为某些州有多个名称,例如爱德华王子岛,所以“文本到列”功能会将数据分散到太多列中。

C.解决方案是使用= RIGHT函数。因为有些邮政编码有5位数字,有些邮政编码有6位,所以在单元格E2中输入以下命令:=RIGHT(D2,6),然后从E2向下复制到E3到E16。做完了 E列现在仅包含邮政编码。但是,D列仍同时具有状态和邮政编码。

15&16. LEFT&LEN

使用此功能可将状态与邮政编码分开。在单元格F2中输入以下公式:=LEFT(D2,LEN(D2)-6),然后从F2复制到F3到F16。基本上,此公式表示转到单元格D2,从左侧算起6个位置,然后删除这些字符,将其余字符保留在单元格D2中(不管有多少个字符)。只要一个单元格中的一条信息是唯一的,您就可以使用该信息来添加,删除或替换该单元格中的其他数据。

要移动或操作E或F列(包含公式)中的数据,请突出显示该列并选择复制,将光标移至另一列,然后选择选择性粘贴  > 值,然后单击确定。

16n使用right left和len函数
使用RIGHT,LEFT和LEN功能

17. PMT

使用此方便的功能,确定您刚试驾的那辆新车的付款金额。该过程很简单:输入函数,然后是利率除以12(12个月),期限(或每月还款次数),然后是贷款金额。

在单元格F2中输入以下公式:=PMT(D2 / 12,E2,C2,0,0),然后从F2复制到F3到F6。

要计算预付款,请在F列中输入预付款百分比;在G列中输入公式;在H栏中输入调整后的贷款金额公式;并在I列中使用新的每月付款公式。此外,在E列中将“期限”(以月为单位)更改为“期限”(以年为单位),例如,输入30年而不是“房屋贷款”的360个月。

在单元格G10中输入以下公式:=SUM(C10 * F10)。在单元格H10中输入此公式:=SUM(C10-G10)。然后在单元格I10中输入此公式:=PMT(D10 / 12,E10 * 12,H10,0,0)。请注意,利率仍然除以12,并且期限(以年为单位)乘以12。

17n使用pmt函数计算该新车的自动付款
使用PMT功能计算该新车的自动付款

便捷提示:您是否想知道为什么某些货币格式会将美元金额和美元符号居中($ 1500.00),而另一些货币格式将数字向右对齐而将美元符号向左对齐($ 1500.00)呢?要使数字和美元符号居中,请突出显示该单元格,然后选择“ 主页”  选项卡>“ 数字组”>“ 数字格式” >“ 货币”。要将美元符号向左对齐,并使数字向右对齐,请突出显示该单元格,然后单击“数字”组中的$符号。

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: