EXCEL的运算与数据处理.ppt

《EXCEL的运算与数据处理.ppt》由会员分享，可在线阅读，更多相关《EXCEL的运算与数据处理.ppt（43页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第二章 EXCEL的运算与数据处理n第一节 EXCEL2003公式的运用n第二节 EXCEL的函数及使用n第三节 EXCEL的数据管理与分析n实验：EXCEL函数的使用教学要求教学要求：通过本章教学，使学生进一步熟练掌握Excel2003相关公式的使用、函数的使用和数据管理与分析等内容，为学习Excel在财务管理中的应用打基础。重点：重点：公式、函数的运用，数据分析与管理难点难点：函数的运用，数据分析 第二章 EXCEL的运算与数据处理n第一节 EXCEL 公式的使用n第二节 EXCEL 函数及使用n第三节 EXCEL的数据管理与分析第二章 EXCEL的运算与数据处理一、公式的创建 公式是度单

2、元格中数据进行运算的等式，可以对数据进行+、-、*、/和比较等多种运算。公式以“=”开始，后接公式的表达式。n输入公式的方法n运算符的使用 包括算术运算符、比较运算符、文本连接符、引用运算符n运算符的优先级 引用-算术-连接-比较2.1 EXCEL 公式的使用公式公式1.公式要以公式要以“=”开头开头2.公式中可以包括公式中可以包括函数、变量、运算函数、变量、运算符、常量符、常量 常量：不用计算的值常量：不用计算的值 变量：如引用的单元格变量：如引用的单元格 运算符：指定公式中各元素之间的运算符：指定公式中各元素之间的运算关系。包括引用运算符；算术运算关系。包括引用运算符；算术运算符；文本连接

3、符；比较运算符运算符；文本连接符；比较运算符（1）引用运算符）引用运算符“：”区域运算符区域运算符 B5：D5“，”联合运算符联合运算符 B5，E9“空格空格”交集运算符交集运算符 A1：D2 B2：C7运算符运算符“+”加加“-”减或负号减或负号“*”乘乘“/”除除“”乘方乘方（）算术运算符（）算术运算符（）文本连接符（）文本连接符“”连接一个或多个文本连接一个或多个文本“”等于等于“”大于大于“”小于小于“”大于等于大于等于“”小于等于小于等于“”不等于不等于注：运用比较运算符时，结果为逻辑值注：运用比较运算符时，结果为逻辑值TRUE或或FALSE。运算符的优先级见课本运算符的优先级见课本

4、P47，可以用括号，可以用括号改变运算优先级改变运算优先级（）比较运算符（）比较运算符思考思考n=2=5返回值？返回值？n=A1：A2 A2：B2 二、单元格的引用n相对引用n绝对引用n混合引用n三维引用三、自动求和与快速运算n自动求和n快速计算2.1 EXCEL 公式的使用n在函数当中我们可以直接输入数在函数当中我们可以直接输入数字，也可以字，也可以引用引用单元格内容单元格内容。n单元格的引用包括：外部引用单元格的引用包括：外部引用（链接）、相对引用、绝对引用、（链接）、相对引用、绝对引用、混合引用、三维引用。混合引用、三维引用。单元格的引用单元格的引用1.外部引用：外部引用：SUMSUM（

5、sheet1sheet1！A1 A1：A9 A9）表示引用表示引用sheet1sheet1工作表中工作表中A1A1到到A9A92.2.相对引用：引用的是单元格的相对相对引用：引用的是单元格的相对位置位置SUMSUM（A1A1：A2A2）3.3.绝对引用：引用的是特定位置的单绝对引用：引用的是特定位置的单元格元格SUMSUM（A A1 1：A A2 2）4.4.混合引用：既有相对引用又有绝对混合引用：既有相对引用又有绝对引用引用 =A2A2或或=A=A2 2 思考：思考：=sheet3!C2+sheet4!C8+=sheet3!C2+sheet4!C8+成成绩单绩单!A4!A4表达的意思？表达的

6、意思？四、数组公式n输入数组公式n数组公式的编辑2.1 EXCEL 公式的使用n数组公式对一组或多组值执行计算，并数组公式对一组或多组值执行计算，并返回一个或多个结果。数组公式在大括返回一个或多个结果。数组公式在大括号号“”中，按中，按Ctrl+Shift+Enter组组合键开始输入数组，重复按这组组合键合键开始输入数组，重复按这组组合键结束数组输入。结束数组输入。n例如：例如：=B1：B3+C1：C3数组公式数组公式一、函数的基本语法 =函数名（参数序列）参数序列可以是数值、文本、逻辑值、单元格（区域）地址或名称，各种表达式和函数等，可以一个或多个，也可没有。如是多个，中间用“，”分开。二、

7、函数的输入 直接输入或粘贴函数2.2 EXCEL 函数及使用nExcel除了能运用公式，还能进行大除了能运用公式，还能进行大量的函数运算。这些函数运算，能够量的函数运算。这些函数运算，能够满足各种日常的办公、财务、统计和满足各种日常的办公、财务、统计和各种报表等所需要的计算。各种报表等所需要的计算。n函数的表达式：函数的表达式：=函数名（参数函数名（参数1，参，参数数2）n例如：求和例如：求和 SUM（number1，number2）或）或SUM（B2：C5）函数函数预定义的公式预定义的公式两种方法两种方法：n直接输入：直接在要用公式的单元格输直接输入：直接在要用公式的单元格输入函数，动态下拉

8、列表可以帮助我们减入函数，动态下拉列表可以帮助我们减少错误少错误n插入函数：插入函数：1.单击编辑栏前单击编辑栏前 fx 2.开始开始编辑编辑 3.公式公式 点击某函数对话框将显示函数名称、各点击某函数对话框将显示函数名称、各个参数、函数及其参数的说明等个参数、函数及其参数的说明等输入函数的操作输入函数的操作n即，函数可以作为另一函数的参数使用。即，函数可以作为另一函数的参数使用。n注意问题：注意问题：1.被嵌套函数的返回值，要与其代替参数被嵌套函数的返回值，要与其代替参数类型一致类型一致 2.公式中函数嵌套级别最多为七级公式中函数嵌套级别最多为七级函数可以嵌套函数可以嵌套三、数学函数的基本语

9、法及使用n求和函数 应用最多 无条件求和函数：SUM()n求和函数 应用最多 无条件求和函数：SUM()SUM(NO1,NO2,NO3)（NO1为1-30个参数）返回某一单元格区域中所有数字之和2.2 EXCEL 函数及使用n条件求和函数:SUMIF()SUMIF(条件范围，求和条件，求和范围)根据制定条件对若干单元格求和n 多组数据相乘求和函数:SUMPRODUCT()在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT(array1,)（array1为2-30个数组），数组参数应有相同的维数，非数值型数组元素作为0处理。2.2 EXCEL 函数及使用n求平均值函

10、数Average()，用法同SUM()Average(NO1,NO2,NO3)（NO1为需要计算平均值的1-30个参数）n求最大、最小值函数MAX()、MIN()，用法同SUM()nABS()函数:返回参数的绝对值，参数可以是数字和表达式；2.2 EXCEL 函数及使用nRAND()函数：无参数，产生0和1之间的一个随机数；nINT()函数：取整函数，用于把一个数舍入到小于它的最大整数；参数可以是数字或表达式；nMOD()函数：返回参数进行出发运算后的余数 MOD（被除数，除数）nPOWER()函数：对数字进行乘幂运算 POWER(参数1，参数2)，参数1为底数，参数2为指数nSQRT()函数

11、：返回数值的平方根，参数必须是非负数的数。nROUND()函数：用于其参数引用的数值四舍五入到指定的小数位。ROUND(参数，保留的小数位数)参数可以是数值，含有数值的单元格引用或计算结果是数值的表达式；保留小数可以是任意整数，为正数时，舍入到小数点后；为0时，舍入到整数的个位；为负数是，表示保留小数点左边的位数。2.2 EXCEL 函数及使用四、统计函数的基本语法及使用nCOUNT(range)函数：统计指定区域是数字型数据的个数。nCOUNTIF(range,criteria)函数：计算区域中满足给定条件的单元格的个数。这两个函数多用于数据汇总统计分析中。五、文本函数的基本语法及使用nVA

12、LUE()函数：用于将指定文本转化为数字。nTEXT()函数：用指定的格式将数字转换为文本字符串。TEXT(参数，格式)，参数可以是任意数字、表达式或内容是数字的单元格引用，格式用EXCEL规定的格式化符号表示2.2 EXCEL 函数及使用nLEN()函数:用于求指定文本字符串的长度。LEN(“字符串”)nCODE()函数：用于求指定字符的ASC码值。参数可以是任意字符和数字，当为字符串时，返回第一个字符的ASC码值。nCHAR()函数：用于求出任一个ASC码值相对应的字符。参数是数字0-127。nTRIM()函数：将字符串的前导空格、后接空格及字符间多余一个的空格滤去。2.2 EXCEL 函

13、数及使用nEXACT()函数：区分大小写字母来比较两个字符串是否完全相同。返回值为FALSE或TURE。nRIGHT()函数：用于从指定字符串提取右边N个字符。RIGHT(字符串，N)nLEFT()函数：用于从指定字符串提取左边N个字符。LEFT(字符串，N)nMID()函数：用于从指定字符串中第M个 字符开始提取N个字符。LEFT(字符串，M，N)2.2 EXCEL 函数及使用六、逻辑函数的基本语法及使用nIF()函数：执行真假值判断，根据逻辑计算的真假值，返回不同结果。IF(条件表达式，表达式1，表达式2)说明：条件表达式表示计算结果为TRUE或FALSE的任意值或表达式，本参数可以使用任

14、何比较运算符。条件表达式为TRUE时返回的表达式1的值，否则返回表达式2的值。2.2 EXCEL 函数及使用nIF()函数的嵌套使用：可以嵌套7层 IF(条件表达式1，表达式1，IF(条件表达式2，表达式2，IF(条件表达式3，表达式3，IF()练习：如何利用IF函数在工资表中计算个人所得税，并将工作表转化为工资条？2.2 EXCEL 函数及使用nAND()函数表示逻辑与，当所有条件都满足时，返回TRUE，否则返回FALSE。nOR()函数表示逻辑或，只要有一个条件满足时就返回TRUE，只有所有条件均不满足时才返回FALSE。nNOT()函数不是逻辑非。2.2 EXCEL 函数及使用公式中的常

15、见出错信息与处理错误信息产生原因处理办法#公式计算结果太长，单元格日期、时间公式计算结果为负值增加单元格的宽度；确认日期、时间的格式正确#DIV/0除数为0/空白单元格除数改为非零值；修改单元格引用#VALUE!使用了错误的参数或运算对象类型确认参数或运算符正确以及引用的单元格中包含有效数据#NAME?删除了公式中使用的名称或使用了不存在的名称以及名称拼写错误确认使用的名称确实存在；确认名称拼写正确；#N/A公式中无可用的数值或缺少函数参数确认函数中的参数正确并处于正确位置；#REF!删除了由其他公式引用的单元格或将移动单元格贴到了由其他公式引用的单元格中，造成单元格引用无效确认函数中引用的单

16、元格存在；确认单元格引用正确；#NUM!在需要数字参数的函数中使用了不能接受的参数；公式计算结果的数字太长或太小而无法表示；确认函数中使用的参数类型正确；为工作表函数使用不同的初始值；#NULL!使用了不正确的区域运算符或不正确的单元格引用确认区域引用正确；确认单元格引用正确查找与引用函数nLOOKUP()从单行或单列或从数组中查找某一个值。条件是向后兼容性nHLOOKUP(lookupvalue,table array,row indexnum,range lookup)搜索数组区域首行满足条件的元素，确定待检索单元格区域中的列序号，再进一步返回选定单元格的值。nINDEX（）在给定的单元格

17、区域中，返回特定行列交叉处单元格的值或引用nRow(reference)column(reference)返回一个引用的行号 返回一个引用的列号nRows(array)columns(array)返回某一引用或数组的行数 返回某一引用或数组的列数nVLOOKUP(lookupvalue,table array,col indexnum,range lookup)搜索数组区域首列满足条件的元素，确定待检索单元格区域中的行序号，再进一步返回选定单元格的值。默认下表是升序排序说明一、记录的增加、删除、修改和查找 “选择单元格-数据/记录单-记录单对话框”二、数据排序 常用工具栏或者菜单命令三、数据的

18、筛选 自动筛选、高级筛选四、数据的分类汇总五、数据分析 单变量数据分析，多变量数据分析六、数据透视表：透视表的建立、删除七、数据的合并计算 2.3 EXCEL的数据管理与分析n实验目的 1.掌握单元格的引用方法。2.学会公式和函数的使用实验2 Excel函数的使用实验2.1 实验资料 某地所属个公司税款缴纳情况如下：5月3日，纺织厂应纳税所得额980 000元；5月5日，地产公司应纳税所得额7856000元；5月6日，天泽公司应纳税所得额3500000元；5月10日，八达公司应纳税所得额620000元；5月11日，丝绸公司应纳税所得额780000元；5月13日，四海公司应纳税所得额500000

19、元；5月16日，印染厂应纳税所得额530000元；5月17日，果品公司应纳税所得额86000元；5月18日，服装厂应纳税所得额850000元；5月18日，红星公司应纳税所得额6150000元。说明：说明：1.纳税截止日期为4月10日，10日后缴纳应按规定缴纳滞纳金；2.滞纳金=应纳税款*滞纳天数*2%；3.所得税率25%；4.收款总额=应纳税款+滞纳金。要求：要求：1.用公式或函数计算应纳税款、滞纳天数、滞纳金、滞纳金平均数和最高额、收款总额；2.对滞纳金四舍五入保留2位小数。实验2.1 实验结果 编制某地所属公司税款缴纳情况表 企业编号企业名称应纳税所得额应纳税款纳税日期滞纳天数滞纳金收款总

20、额101102110平均滞纳金滞纳金最高额实验2.2 实验资料 某企业职工工资及奖金发放资料和规定如下，请按要求完成相应工作。编号姓名性别部门职称基本工资编号姓名性别部门职称基本工资1孙林男人事初级120011杨柳男保卫副高17682付刚 男办公室中级177512吴英女财务初级15423田红 男财务初级154213隋安女财务中级17894赵民 男保卫中级175614张仪女车间中级13565林果 男人事副高195615佟玲女车间中级16566童涛 男办公室中级177516李鹏女办公室中级16267胡安 男车间初级106917刘乐女人事高级18598陶明 男车间初级106918张明女保卫中级168

21、99李刚 男保卫中级152619巩俐女办公室中级161210王朝 男车间初级95620付琴女保卫中级1612n说明：1.奖金按部门发放，每人每月车间200元，保卫科150，其他100；2.津贴按职称发放，高级1000元，副高800，中级500，初级200元；3.女职工每月保健费50元，男职工每月30元；4.公司代缴个人所得税，应税所得额=应发工资-2000，税率按现行税法规定执行。n要求：1.根据所个资料建立包括奖金、津贴、保健费、应发工资、个人所得税和实发工资项目在内的职工工资表；注意公式的运用；2.对工资表按部门进行分类汇总；3.将工资表中的中级及以上女职工工资数据筛选出来并复制到空白区域

22、；4.将工资表转换称为工资条形式并存放在一个新的工作表中。实验2.2 实验要求实验2.2 实验结果 编号姓名性别部门职称基本工资奖金津贴保健费应发工资个人所得税实发工资12201.Excel 2007的启动与退出的启动与退出2.插入工作表插入工作表3.重命名工作表重命名工作表4.删除工作表删除工作表5.调整行高、列宽、隐藏单元格调整行高、列宽、隐藏单元格6.6.改变改变EnterEnter的移动方向的移动方向7.7.输入日期、时间输入日期、时间8.8.句柄的使用句柄的使用填充柄填充柄9.9.单元格数据的格式单元格数据的格式上机练习10.10.文字删除线、下标、上标文字删除线、下标、上标11.11.公式输入公式输入12.12.函数的运用函数的运用