excel公式与常用函数实例

EXCEL公式和函数公式公式的输入公式的调整常用函数1整理课件Excel表格中不仅可以输入数据，也可以使用公式对工作表中的数据进行各种数值计算和统计分析等Excel的公式是由运算符号和参与计算的运算数组成的表达式1.1公式2整理课件1.2公式中的运算符算术运算符：+-*/^%字符运算符：&连接符比较运算符：=<><=>=<>逻辑运算符：andornot以函数形式出现优先级顺序：算术运算符→字符运算符→比较运算符→逻辑函数符(使用括号可确定运算顺序)3整理课件1.3公式中的数据类型输入公式要注意公式中的运算对象可以包括：数值和字符、单元格地址、区域、函数等,如：=A2+B2+C2不要随意包含空格公式中的字符要用半角引号括起来公式中运算符两边的数据类型要相同如：="ab"+25出错#VALUE4整理课件2.1公式的输入公式的根本特性：公式的输入是以“=〞开始公式计算结果显示在单元格中公式本身显示在编辑栏中5整理课件2.2公式例如=1+2+6〔数值计算〕=A1+B2〔引用单元格地址〕=SUM(A1:C4)〔公式计算〕=IF(and(A1>A2,B1>B2),100*A1/A2,100*B1/B2)〔函数计算〕="ABC"&"XYZ"(字符计算，结果:ABCXYZ)公式中常用单元格的引用来代替单元格的具体数据，当公式中被引用单元格的数据变化时，公式的计算结果会随之变化6整理课件2.3公式的复制使用填充柄菜单[编辑]→[复制]/[粘贴]

复制公式时，公式会作相对调整7整理课件3.1公式的调整相对引用是针对当前单元格的引用，是随当前单元格变化而变化的。绝对引用是针对起始单元格的引用，是不随当前单元格的变化而变化的。8整理课件公式的调整相对地址引用:在公式复制时将自动调整例如：将D2单元中的公式“=A2+B2+C2“复制到D4后，D4中的公式〞=A4+B4+C4〞9整理课件公式的调整绝对地址引用：公式复制时不变。例如：C3单元的公式=$B$2+$B$3+$B$4复制到D5中，D5单元的公式=$B$2+$B$3+$B$4(在公式复制时绝对地址不变，相对地址按规那么调整。)10整理课件3.2公式的移动公式移动：相对和绝对地址都不调整C3单元格的公式=B2+B3+B4移动到D5中，D3单元格的公式=B2+B3+B4(被公式引用的单元格移动，公式跟踪单元格，值不变)11整理课件4.1常用函数12整理课件4.1统计函数sum是求参数的和，就是求指定的所有参数的和。sumif是条件求和，Excel中sumif函数的用法是根据指定条件对假设干单元格、区域或引用求和。sumif函数语法是：SUMIF(range，criteria，sum_range)sumif函数的参数如下：第一个参数：Range为条件区域，用于条件判断的单元格区域。第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。第三个参数：Sum_range为实际求和区域，需要求和的单元格、区域或引用。当省略第三个参数时，那么条件区域就是实际求和区域。注：criteria参数中使用通配符〔包括问号(?)和星号(*)〕。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符(~)。13整理课件3.COUNT用途：它可以统计数组或单元格区域中含有数字的单元格个数。函数语法：COUNT(value1，value2，...)。参数：value1，value2，...是包含或引用各种类型数据的参数(1～30个)，其中只有数字类型的数据才能被统计。实例：如果A1=90、A2=人数、A3=〞〞、A4=54、A5=36，那么公式“=COUNT(A1:A5)〞返回得314整理课件4.COUNTA用途：返回参数组中非空值的数目。语法：COUNTA(value1，value2，...)说明：参数可以是任何类型，它们包括空格但不包括空白单元格。如果不需要统计逻辑值、文字或错误值，那么应该使用COUNT函数。实例：如果A1=6.28、A2=3.74，A3=“我们〞其余单元格为空，那么公式“=COUNTA(A1:A5)〞的计算结果等于3。15整理课件5.COUNTBLANK用途：计算某个单元格区域中空白单元格的数目。函数语法：COUNTBLANK(range)参数：Range为需要计算其中空白单元格数目的区域。实例：如果A1=88、A2=55、A3=(空格)、A4=72、A5=(空格)，那么公式“=COUNTBLANK(A1:A5)〞返回得2。16整理课件6.COUNTIF用途：计算区域中满足给定条件的单元格的个数。语法：COUNTIF(range，criteria)参数：Range：需要计算其中满足条件的单元格数目的单元格区域。Criteria：确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。实例：在A1:A7之间，确定数字55被计算在内的条件，返回得217整理课件7.MAX：返回选定数据库项中的最大值8.MIN：返回选定数据库项中的最小值18整理课件4.2逻辑〔条件〕函数1、IF1)IF函数的语法结构：IF(条件,结果1,结果2)。2)IF函数的功能对满足条件的数据进行处理，条件满足那么输出结果1，不满足那么输出结果2。可以省略结果1或结果2，但不能同时省略。19整理课件3)条件表达式把两个表达式用关系运算符(主要有=，<>，>，<，>=，<=等6个关系运算符)连接起来就构成条件表达式。例如，在IF(a1+b1+50<>b1+c1-50,

1,2)函数式中，条件表达式是a1+b1+50<>b1+c1-50。4)执行过程下面以IF(a1+b1+50<>b1+c1-50,

1,1)函数式为例来说明IF函数的执行过程。20整理课件

21整理课件例2：IF(E2>=85,“优〞,IF(E2>=75,“良〞，IF(E2>=60,"及格"，"不及格")))公式理解：函数从左向右执行。首先计算E2>=85，如果该表达式成立，那么显示“优〞，如果不成立就继续计算E2>=75，如果该表达式成立，那么显示“良〞，否那么继续计算E2>=60，如果该表达式成立，那么显示“及格〞，否那么显示“不及格〞。22整理课件2.AND

1.

说明：所有参数的计算结果为TRUE时，返回TRUE；只要有一个参数的计算结果为FALSE，即返回FALSE。

2.

函数语法：AND(logical1,[logical2],...)

logical1：必需。要检验的第一个条件，其计算结果可以为TRUE或FALSE。

logical2,...：可选。要检验的其他条件，其计算结果可以为TRUE或

FALSE，最多可包含255个条件。

3.实例：

=AND(2+2=4,2+3=5)

所有参数的计算结果均为TRUE

=AND(2+2=4,2+3=6)

所有参数的计算结果均为FALSE

23整理课件4.3查找和引用函数参数简单说明输入数据类型lookup_value要查找的值数值、引用或文本字符串table_array要查找的区域数据表区域row_index_num返回数据在区域的第几行数正整数range_lookup模糊匹配/精确匹配TRUE/FALSE（或不填）1、HLOOKUP：说明：HLOOKUP函数主要用于在表格或数值组的首行查找指定的数值〔即在水平查找〕，并返回表格显示当前列中指定行处的值。。函数语法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)24整理课件实例：用下面这个实例展示Hlookup函数用法A1:B2表示要在其中查找的区间，公式中的table_arrayF1到G1中表示我们要查找的对象，公式中的lookup_value现在我们要查找F1到G1分别对应的数值。〔按F4键，将其变成绝对引用。〕25整理课件2、VLOOKUP

说明：

VLOOKUP函数是Excel中的一个纵向查找函数，它与LOOKUP函数和HLOOKUP函数属于一类函数，在工作中都有广泛应用。功能是按列查找，最终返回该列所需查询列序所对应的值；与之对应的HLOOKUP是按行查找的。函数语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)参数简单说明输入数据类型lookup_value要查找的值数值、引用或文本字符串table_array要查找的区域数据表区域col_index_num返回数据在查找区域的第几列数正整数range_lookup模糊匹配/精确匹配TRUE（或不填）/FALSE实例：用下面这个实例展示vlookup函数用法A1:B7表示要在其中查找的区间，公式中的table_arrayE1中表示我们要查找的对象，公式中的lookup_value现在我们要查找E1对应的数值。〔按F4键，将其变成绝对引用。〕26整理课件4.4排位函数：RANK说明：rank函数是排名函数。rank函数最常用的是求某一个数值在某一区域内的排名。函数语法：rank(number,ref,[order])实例：给以下成绩排名27整理课件4.5文本函数：REPLACE1.说明：用新字符串替换旧字符串，而且替换的位置和数量都是指定的。2.函数语法：=Replace(old_text,start_num,num_chars，new_text)=replace〔要替换的字符串，开始位置，替换个数，新的文本〕注意：第四个参数是文本，要加上引号。3.实例：=REPLACE(替换区域A2，第八个数字开始，替换4个，替换为“xxxx〞28整理课件4.6时间与日期函数1.TODAY：用于返回当前系统的日期，其完整的格式：=TODAY()2.NOW：用于返回当前系统的时间，其完整的格式：=DAY()DATE函数：返回指定日期的序列号函数语法：DATE(year，month，day)29整理课件4.7数据库函数一、DAVERAGE1.说明：返回数据库或数据清单中满足指定条件的列中数值的平均值.2.函数语法：DAVERAGE(database,field,criteria)参数：Database构成数据表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。3.实例：实例1：利用daverage函数单条件求平均值，求电视的平均数量。实例2：求B分店电视的平均数量输入公式=DAVERAGE(A1:C9,C1,E1:E2)数据区域——A1:C9〔整个查找表〕字段名——C1，指定求平均值的数据列。也可以等于3〔参数Field=C1等价于参数Field=3。即表示返回第三列的值。)条件区域——E1:E2，表示品名为电视。30整理课件二、DSUM1.说明：将数据库中符合条件的记录的字段列中的数字的和。使用它可以对数据进行多条件累加，这种方式可以使条件的修改变得方便，因此，相对于SUM和SUMIF函数，DSUM更加灵活。2.函数语法：DSUM(database,field,criteria)参数：Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。列表中的数据列必须在第一行具有标志项。Field可以是文本，即两端带引号的标志项，如“树龄〞或“产量〞；此外，Field也可以是代表列表中数据列位置的数字：1表示第一列，2表示第二列，等等。Criteria为一组包含给定条件的单元格区域。可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。31整理课件2.实例：如下图，利用dsum函数单条件求和，实例1：求电视的总数量。〔实例2：求B分店电视的总数量。〕输入公式=DSUM(A1:C9,F1,D1:D2)数据区域——A1:C9〔整个查找表〕