EXCEL表格公式培训.ppt

常用常用EXCELEXCEL表格函数表格函数讲解人：谷晓宗 电话：[phone] 固话：0319-8797010Excel基本操作1公式和函数2函数的组合应用3Excel数据处理与分析 Excel基本操作excel文件打开、关闭、保存、重命名等 单元格的合并、拆分、设置行高、列宽、删除、清除等单元格的数据格式、对齐方式、字体、边框、底纹等 数据的输入、修改、复制等 工作薄的新增、修改、移动、复制等 工作薄的新增、修改、移动、复制等 公式与函数公式的组成元素常用运算符及单元格的引用地址常用计算公式和函数常用计算公式和函数 公式与函数公式由下列几个元素组成：公式由下列几个元素组成：运算符，如“+”，“*”；单元格（或区域）引用；数值或文本；工作表函数，如“sum”，“average”；例如：=sum(A1*B1,A2*B2,A3*B3)/3函数是可以嵌套的函数是可以嵌套的 例如：=sum(average(A1：C1),average(A2:C2)ExcelExcel公式中常用运算符公式中常用运算符 公式与函数类型类型表示形式表示形式优先级优先级算术运算算术运算符符+（加）、-（减）、*（乘）、/（除）、%（百分比）、（乘方）从高到低分为3个级别：百分比和乘方、乘和除、加和减关系运算关系运算符符=（等于）、（大于）、=（大于等于）、=（小于等于）、（不等于）优先级相同文本运算文本运算符符&（文本的连接）引用运算引用运算符符：（区域）、，（联合）、空格（交叉）从高到低依次为：区域、联合、交叉：包括两个引用在内的所有单元格的引用，如SUM（A1：C3），对多个引用合并为一个引用，如SUM（A1，C3）空格 产生同时隶属于两个引用的单元格区域的引用，如SUM（A1：C4 B2：D3）单元格的引用地址单元格的引用地址 公式与函数用列号行号表示，如：B1、A2：C4等，其特点是公式复制和移动时，该地址会根据一动的位置自动调节。在列号和行号前都加上美元符号“$”，如$B$1，其特点是公式复制和移动时，该地址始终保持不变。相对引用绝对引用混合引用在列号或行号前加上美元符号“$”，如$B1和B$1，$B1是列不变，行变化；B$1是行不变，列变化。常用计算公式和函数常用计算公式和函数 公式与函数函数名函数名称称功能描述功能描述AVERAGE参数的平均值SUM求和COUNT计算参数列表中值得个数IF逻辑函数，判断是否满足某一条件MAX返回一组数中的最大值MIN返回一组数中的最小值VLOOKUP在数组第一列中查找，然后在行之间移动以返回单元格的值 公式与函数AVERAGE函数的功能。AVERAGE的意思就是求平均值。它的参数可以是常数，也可以是表格里包含数字的区域。如果区域里有空白表格，就自动忽略。AVERAGE的语法。AVERAGE（a,b,c,.）或AVERAGE（rang）其中，a,b,c,.中的各个字母可以是任何数字或是单元格的地址。rang表示Excel表格中的一块区域。公式与函数第一种：对数字求平均值比如我要计算1，23，4，567，890这些数字的平均值。任意单元格中输入：=average（1，23，4，567，890）回车此时，表示计算1，23，4，567，890的平均值并显示到该单元格中。（见下图）公式与函数第二种：对区域内求平均值例如：在A2，E4这一块区域中分别输入上述数字，中间可以空白，系统自动无视它们，然后在这一区域外的任意空白单元格内输入=average（A2:E4）回车此时，表示计算左上角为A2右下角为E4的这个区域中所有单元格中的值的平均值并显示到该单元格中。（见下图）公式与函数sum函数的功能。什么叫sum函数呢，其实简单的说，就是几组数据的求和，就像我们数学常用到的加法一个意思，他们的区别在于，加法需要自己一个一个的去加，但是sum函数的话，只要你输入公式，就可以把你这一组的数据全部求和。SUM的语法。SUM（a,b,c,.）或SUM（rang）其中，a,b,c,.中的各个字母可以是任何数字或是单元格的地址。rang表示Excel表格中的一块区域。公式与函数count函数的功能。计算参数列表中的数字项的个数,但只有数字类型的数据才被计数。count的语法。COUNT(a,b,c,.)或COUNT(rang)其中，a,b,c,.中的各个字母可以是单元格的地址。rang表示Excel表格中的一块区域。公式与函数count函数的衍生函数countif。countif函数是指计算某个区域中给定条件的单元格的数目 公式格式：=COUNTIF（Range,Criteria)Range 要计算其中非空单元格数目的区域 Criteria 以数字、表达式或文本形式定义的条件 公式与函数MAX/MIN函数。MAX/MIN函数的功能基本类似，主要是选择范围内的最大/最小值，其语法、使用方式与SUM等函数相同。在此不再赘述COUNT函数。COUNT函数的功能为统计范围内符合条件的单元格数，其语法、使用方式与SUM等函数相同。公式与函数IF函数的功能。对满足条件的数据进行处理，条件满足则输出结果1，不满足则输出结果2。可以省略结果1或结果2，但不能同时省略。IF的语法。IF(logical_test,value_if_true,value_if_false)其中，logical_test表示判断条件，value_if_true表示若符合条件时返回的值，value_if_false表示不符合条件时返回的值。公式与函数IF函数的基本用法。普通公式的单条件表达假定经理级别员工年龄超过35岁为“超龄”，年龄低于35岁为“未超龄”，则可编订函数：=IF(D835,超龄,未超龄)如图 公式与函数IF函数的基本用法。普通公式的双条件表达假定经理级别员工年龄超过35岁为“超龄”，年龄低于35岁为“未超龄”；而主任级别员工年龄超过30岁为“超龄”，低于30岁为“未超龄”则可编订函数：=IF(B3=经理,IF(D335,超龄,未超龄),IF(D330,超龄,未超龄)如图 公式与函数IF函数的基本用法。普通公式的多条件表达假定经理级别员工，大专年龄超过35岁为“超龄”，大专年龄低于35岁为“未超龄”；而本科年龄超过40岁为“超龄”，低于40岁为“未超龄”主任级别员工，大专年龄超过25岁为“超龄”，低于25岁为“未超龄”；而本科年龄超过30岁为“超龄”，低于30岁为“未超龄”则可编订函数：？=IF(B8=经理,IF(C8=本科,IF(D840,超龄,未超龄),IF(D835,超龄,未超龄),IF(C8=本科,IF(D835,超龄,未超龄),IF(D830,超龄,未超龄)公式与函数IF函数的特点。IF函数最大的特点在于他的可嵌套性和可混合性，在EXCEL2003中 函数 IF 可以嵌套七层，在EXCEL2007中可以嵌套64层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。公式与函数VLOOKUP函数的功能。首列查找函数，即以某个要查找的条件，在区域的首列里面查找，若有匹配的，则返回该数据对应的第几列数据。IVLOOKUP的语法。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。表述就是VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）在此，在我们的工作中，几乎都使用精确匹配，该项的参数一定要选择为false。否则返回值会出乎你的意料。公式与函数VLOOKUP函数的基本用法。对目标条件的筛选，如在某一个数据繁琐的表格中找出某一项内容。如查找王五等人的学历如图 公式与函数VLOOKUP函数的常见用法。VLOOKUP函数一般情况下不会单独使用，会用于在多个表格之间进行数据的引用和逻辑分析和判断。函数的组合利用上述几个基本的函数相对简单，但在日常工作中，我们往往需要处理许多更加复杂的情况。如：统计经理级大专以上员工人数 统计经理级本科员工的最大年龄等 或者筛选未超龄员工的学历等Excel表格函数的最大优势在于它的可组合型，几个简单的函数进行组合，就可以解决大部分计算复杂、步骤混乱的工作。thank you