excel公式与常用函数实例

1、EXCELEXCEL公式和函数公式和函数 1.1. 公式公式 2.2. 公式的输入公式的输入 3.3. 公式的调整公式的调整 4.4. 常用函数常用函数 1整理ppt Excel Excel表格中不仅可以输入数据，也可表格中不仅可以输入数据，也可 以使用公式对工作表中的数据进行各种数值计以使用公式对工作表中的数据进行各种数值计 算和统计分析等算和统计分析等 Excel Excel的公式是由运算符号和参与计算的公式是由运算符号和参与计算 的运算数组成的表达式的运算数组成的表达式 1.1公式 2整理ppt 1.2公式中的运算符 1.1. 算术运算符：算术运算符：+ - + - * * / % /

2、% 2.2. 字符运算符：字符运算符：& & 连接符连接符 3.3. 比较运算符：比较运算符：= = = = 4.4. 逻辑运算符：逻辑运算符：and or not and or not 以函数形式出现以函数形式出现 5.5. 优先级顺序：算术运算符优先级顺序：算术运算符字符运算符字符运算符比较运算符比较运算符逻辑函数符逻辑函数符 ( (使用括号可确定运算顺序使用括号可确定运算顺序) ) 3整理ppt 1.3公式中的数据类型 输入公式要注意输入公式要注意 1.1. 公式中的运算对象可以包括：数值和字符、单元格地公式中的运算对象可以包括：数值和字符、单元格地 址、区域、函数等址、区域、函数等,

3、,如：如：=A2+B2+C2=A2+B2+C2 2.2. 不要随意包含空格不要随意包含空格 3.3. 公式中的字符要用半角引号括起来公式中的字符要用半角引号括起来 4.4. 公式中运算符两边的数据类型要相同公式中运算符两边的数据类型要相同 如：如：=ab+25 =ab+25 出错出错 #VALUE#VALUE 4整理ppt 2.1公式的输入 公式的基本特性：公式的基本特性： 1.1. 公式的输入是以公式的输入是以“ = ”= ”开始开始 2.2. 公式计算结果显示在单元格中公式计算结果显示在单元格中 3.3. 公式本身显示在编辑栏中公式本身显示在编辑栏中 5整理ppt 2.2公式示例 =1+2

4、+6 =1+2+6 （数值计算）（数值计算） =A1+B2 =A1+B2 （引用单元格地址）（引用单元格地址） =SUM(A1:C4) =SUM(A1:C4) （公式计算）（公式计算） =IF( and(A1A2,B1B2),100=IF( and(A1A2,B1B2),100* *A1/A2, 100A1/A2, 100* *B1/B2) B1/B2) （函数计算）（函数计算） =ABC&XYZ (=ABC&XYZ (字符计算，结果字符计算，结果:ABCXYZ) :ABCXYZ) 公式中常用单元格的引用来代替单元格的具体数据，当公式中公式中常用单元格的引用来代替单元格的具体数据，当公式中 被

5、引用单元格的数据变化时，公式的计算结果会随之变化被引用单元格的数据变化时，公式的计算结果会随之变化 6整理ppt 2.32.3公式的复制公式的复制 1.1.使用填充柄使用填充柄 2.2.菜单菜单 编辑编辑 复制复制/粘贴粘贴 3.3. 复制公式时，公式会作相对调整复制公式时，公式会作相对调整 7整理ppt 3.1公式的调整 1.1. 相对引用相对引用是针对当前单元格的引用，是针对当前单元格的引用， 是随当前单元格变化而变化的。是随当前单元格变化而变化的。 2.2. 绝对引用绝对引用是针对起始单元格的引用，是针对起始单元格的引用， 是不随当前单元格的变化而变化的。是不随当前单元格的变化而变化的。

6、 8整理ppt 公式的调整 相对地址引用相对地址引用: : 在公式复制时将自动调整在公式复制时将自动调整 例如：例如： 将将D2D2单元中的公式单元中的公式 “=A2+B2+C2“=A2+B2+C2“ 复制到复制到D4D4后，后，D4D4中的公式中的公式 ”=A4+B4+C4=A4+B4+C4” 9整理ppt 公式的调整 绝对地址引用绝对地址引用 ：公式复制时不变。：公式复制时不变。 例如：例如： C3C3单元的公式单元的公式=$B$2+$B$3+$B$4=$B$2+$B$3+$B$4 复制到复制到D5D5中，中，D5D5单元的公式单元的公式=$B$2+$B$3+$B$4=$B$2+$B$3+

7、$B$4 ( (在公式复制时绝对地址不变，相对地址按规则调整。在公式复制时绝对地址不变，相对地址按规则调整。) ) 10整理ppt 3.2公式的移动 公式移动：相对和绝对地址都不调整公式移动：相对和绝对地址都不调整 C3C3单元格的公式单元格的公式=B2+B3+B4=B2+B3+B4移动到移动到D5D5中，中， D3D3单元格的公式单元格的公式=B2+B3+B4=B2+B3+B4 ( (被公式引用的被公式引用的单元格移动单元格移动，公式跟踪单元格，值不变，公式跟踪单元格，值不变) ) 11整理ppt 4.14.1常用函数常用函数 12整理ppt 4.1统计函数 1. sum是求参数的和，就是求

8、指定的所有参数的和。 2. sumif是条件求和，Excel中sumif函数的用法是根据指定条件对若干单元格、 区域或引用求和。 sumif函数语法是：SUMIF(range，criteria，sum_range) sumifsumif函数的参数如下：函数的参数如下： 第一个参数：第一个参数：RangeRange为条件区域，用于条件判断的单元格区域。为条件区域，用于条件判断的单元格区域。 第二个参数：第二个参数：CriteriaCriteria是求和条件，由数字、逻辑表达式等组成的判定条件。是求和条件，由数字、逻辑表达式等组成的判定条件。 第三个参数：第三个参数：Sum_rangeSum_ra

9、nge 为实际求和区域，需要求和的单元格、区域或引用。为实际求和区域，需要求和的单元格、区域或引用。 当省略第三个参数时，则条件区域就是实际求和区域。当省略第三个参数时，则条件区域就是实际求和区域。 注：criteria 参数中使用通配符（包括问号 (?)和星号 (*)）。问号匹配任 意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该 字符前键入波形符 ()。 13整理ppt 3.COUNT3.COUNT 用途用途：它可以统计数组或单元格区域中含有数字的：它可以统计数组或单元格区域中含有数字的单元格个数单元格个数。 函数语法函数语法：COUNT(value1，value2，.

10、)。 参数参数：value1，value2，.是包含或引用各种类型数据的参数(130个)，其中只其中只 有数字类型的数据才能被统计有数字类型的数据才能被统计。 实例：实例：如果A1=90、A2=人数、A3=、A4=54、A5=36，则公式“=COUNT(A1:A5) ” 返回得3 14整理ppt 4.COUNTA4.COUNTA 用途用途：返回参数组中非空值的数目。 语法语法：COUNTA(value1，value2，.) 说明说明：参数可以是任何类型，它们包括空格但不包括空白单元格。如果 不需要统计逻辑值、文字或错误值，则应该使用COUNT函数。 实例实例：如果A1=6.28、A2=3.74

11、，A3=“我们”其余单元格为空，则公式 “=COUNTA(A1:A5)”的计算结果等于3。 15整理ppt 5.COUNTBLANK5.COUNTBLANK 用途：用途：计算某个单元格区域中空白单元格的数目。 函数语法：函数语法：COUNTBLANK(range) 参数：参数：Range为需要计算其中空白单元格数目的区域。 实例：实例：如果A1=8888、A2=55、A3=(空格)、A4=72、A5=(空格)，则公式 “=COUNTBLANK(A1:A5)”返回得2。 16整理ppt 6.COUNTIF6.COUNTIF 用途：用途：计算区域中满足给定条件的单元格的个数。 语法：语法：COUN

12、TIF(range，criteria) 参数：参数： Range：需要计算其中满足条件的单元格数目的单元格区域。Criteria：确定哪些单元格将被计算在 内的条件，其形式可以为数字、表达式或文本。 实例：实例：在A1:A7之间，确定数字55被计算在内的条件，返回得2 17整理ppt 7.MAX7.MAX：返回选定数据库项中的最大值返回选定数据库项中的最大值 8.MIN8.MIN：返回选定数据库项中的最小值返回选定数据库项中的最小值 18整理ppt 4.2逻辑（条件）函数 1 1、IFIF 1) IF1) IF函数的语法结构：函数的语法结构：IF(IF(条件条件, ,结果结果1,1,结果结果2

13、)2)。 2)2) IFIF函数的功能函数的功能 对满足条件的数据进行处理，条件满足则输出对满足条件的数据进行处理，条件满足则输出 结果结果1 1，不满足则输出结果，不满足则输出结果2 2。可以省略结果。可以省略结果1 1或结果或结果 2 2，但不能同时省略。，但不能同时省略。 19整理ppt 3)3) 条件表达式条件表达式 把两个表达式用关系运算符把两个表达式用关系运算符( (主要有主要有= =， ， =， =等等6 6个关系运算符个关系运算符) )连接起来就构成条件表达式。连接起来就构成条件表达式。 例如，在例如，在IF(a1+b1+50 b1+c1-50,IF(a1+b1+50 b1+c

14、1-50,1,2)1,2)函数式中，函数式中， 条件表达式是条件表达式是a1+b1+50 b1+c1-50a1+b1+50 b1+c1-50。 4)4) 执行过程执行过程 下面以下面以IF(a1+b1+50 b1+c1-50,IF(a1+b1+50 b1+c1-50,1,1)1,1)函数式为例来说明函数式为例来说明 IFIF函数的执行过程。函数的执行过程。 20整理ppt 21整理ppt 例例2 2： IF(E2=85,IF(E2=85,“优优”,IF(E2=75,IF(E2=75,“良良”，IF(E2=60,IF(E2=60,及格及格 ， 不及格不及格) 公式理解：公式理解： 函数从左向右执

15、行。函数从左向右执行。 首先计算首先计算E2=85E2=85，如果该表达式成立，则显示，如果该表达式成立，则显示“优优”， 如果不成立就继续计算如果不成立就继续计算E2=75E2=75，如果该表达式成立，则显示，如果该表达式成立，则显示“良良”， 否则继续计算否则继续计算E2=60E2=60，如果该表达式成立，则显示，如果该表达式成立，则显示“及格及格”，否则显，否则显 示示“不及格不及格”。 22整理ppt 2.AND2.AND 1. 说明：所有参数的计算结果为 TRUE 时，返回 TRUE；只要有一个参数 的计算结果为 FALSE，即返回 FALSE。 2. 函数语法：AND(logica

16、l1, 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整理ppt 4.3查找和引用函数 参数参数简单说明简单说明输入数据类型输入数据类型 lookup_value要查找的值数值、引用或文本字符串 table_array要查找的区域数据表区域 ro

17、w_index_num返回数据在区域的第几行数正整数 range_lookup模糊匹配/精确匹配TRUE /FALSE（或不填） 1 1、HLOOKUPHLOOKUP： 说明：说明： HLOOKUP函数主要用于在表格或数值组的首行查找指定的数值（即在水平查找）， 并返回表格显示当前列中指定行处的值。 函数语法函数语法： HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 24整理ppt 实例： 用下面这个实例展示Hlookup函数用法 A1:B2表示要在其中查找的区间，公式中的table_array F1到G1中表示我们要查

18、找的对象，公式中的lookup_value 现在我们要查找F1到G1分别对应的数值。 （按F4键，将其变成绝对引用。） 25整理ppt 2 2、VLOOKUPVLOOKUP 说明：说明： VLOOKUP函数是Excel中的一个纵向查找函数，它与LOOKUP函数和HLOOKUP函 数属于一类函数，在工作中都有广泛应用。功能是按列查找，最终返回该列所需查 询列序所对应的值；与之对应的HLOOKUP是按行查找的。 函数语法：函数语法： VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) VLOOKUP(lookup_value,t

19、able_array,col_index_num,range_lookup) 参数参数简单说明简单说明输入数据类型输入数据类型 lookup_value要查找的值数值、引用或文本字符串 table_array要查找的区域数据表区域 col_index_num返回数据在查找区域的第几列数正整数 range_lookup模糊匹配/精确匹配TRUE（或不填）/FALSE 实例： 用下面这个实例展示vlookup函数用法 A1:B7表示要在其中查找的区间，公式中的table_array E1中表示我们要查找的对象，公式中的lookup_value 现在我们要查找E1对应的数值。 （按（按F4F4键，将

20、其变成绝对引用。）键，将其变成绝对引用。） 26整理ppt 4.4排位函数：RANK 说明：说明：rank函数是排名函数。rank函数最常 用的是求某一个数值在某一区域内的排名。 函数语法：函数语法：rank(number,ref,order) 实例：给下列成绩排名实例：给下列成绩排名 27整理ppt 4.5文本函数：REPLACE 1.1.说明：说明： 用新字符串替换旧字符串，而且替换的位置和数量都是指定的而且替换的位置和数量都是指定的。 2. 2. 函数语法：函数语法： =Replace(old_text,start_num,num_chars，new_text) =replace（要替换

21、的字符串，开始位置，替换个数，新的文本） 注意：第四个参数是文本，要加上引号。 3.3.实例：实例： =REPLACE(=REPLACE(替换区域替换区域A2A2，第八个数字开始，替换，第八个数字开始，替换4 4个，替换为个，替换为“xxxxxxxx” 28整理ppt 4.6时间与日期函数 1.TODAY：用于返回当前系统的日期，其完整的格式：=TODAY( ) 2.NOW：用于返回当前系统的时间，其完整的格式：=DAY( ) DATE函数：返回指定日期的序列号 函数语法：DATE(year，month，day) 29整理ppt 4.7数据库函数 一、DAVERAGE 1.说明：返回数据库或数

22、据清单中满足指定条件的列中数值的平均值. 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整理ppt 二、DSUM 1.1.说明：说明：将数据库中符合条件的记录的字段列中的数字的和。使用它可以对数据 进行多条件累加，这种方式可以使条