EXCEL的运算与数据处理.ppt

财务管理电算化,第二章EXCEL的运算与数据处理,第一节EXCEL2003公式的运用第二节EXCEL的函数及使用第三节EXCEL的数据管理与分析实验：EXCEL函数的使用,财务管理电算化,教学要求：通过本章教学，使学生进一步熟练掌握Excel2003相关公式的使用、函数的使用和数据管理与分析等内容，为学习Excel在财务管理中的应用打基础。重点：公式、函数的运用，数据分析与管理难点：函数的运用，数据分析,第二章EXCEL的运算与数据处理,财务管理电算化,第一节EXCEL公式的使用第二节EXCEL函数及使用第三节EXCEL的数据管理与分析,第二章EXCEL的运算与数据处理,财务管理电算化,一、公式的创建公式是度单元格中数据进行运算的等式，可以对数据进行+、-、*、/和比较等多种运算。公式以“=”开始，后接公式的表达式。输入公式的方法运算符的使用包括算术运算符、比较运算符、文本连接符、引用运算符运算符的优先级引用-算术-连接-比较,2.1EXCEL公式的使用,财务管理电算化,公式1.公式要以“=”开头2.公式中可以包括函数、变量、运算符、常量常量：不用计算的值变量：如引用的单元格运算符：指定公式中各元素之间的运算关系。包括引用运算符；算术运算符；文本连接符；比较运算符,财务管理电算化,（1）引用运算符“：”区域运算符B5：D5“，”联合运算符B5，E9“空格”交集运算符A1：D2B2：C7,运算符,财务管理电算化,“+”加“-”减或负号“*”乘“/”除“”乘方,（）算术运算符,财务管理电算化,（）文本连接符,“”连接一个或多个文本,财务管理电算化,“”等于“”大于“”小于“”大于等于“”小于等于“”不等于注：运用比较运算符时，结果为逻辑值TRUE或FALSE。运算符的优先级见课本P47，可以用括号改变运算优先级,（）比较运算符,财务管理电算化,思考,=2=5返回值？=A1：A2A2：B2,财务管理电算化,二、单元格的引用相对引用绝对引用混合引用三维引用三、自动求和与快速运算自动求和快速计算,2.1EXCEL公式的使用,财务管理电算化,在函数当中我们可以直接输入数字，也可以引用单元格内容。单元格的引用包括：外部引用（链接）、相对引用、绝对引用、混合引用、三维引用。,单元格的引用,财务管理电算化,1.外部引用：SUM（sheet1！A1：A9）表示引用sheet1工作表中A1到A92.相对引用：引用的是单元格的相对位置SUM（A1：A2）3.绝对引用：引用的是特定位置的单元格SUM（A1：A2）4.混合引用：既有相对引用又有绝对引用=A2或=A2,财务管理电算化,思考：,=sheet3!C2+sheet4!C8+成绩单!A4表达的意思？,财务管理电算化,四、数组公式输入数组公式数组公式的编辑,2.1EXCEL公式的使用,财务管理电算化,数组公式对一组或多组值执行计算，并返回一个或多个结果。数组公式在大括号“”中，按Ctrl+Shift+Enter组合键开始输入数组，重复按这组组合键结束数组输入。例如：=B1：B3+C1：C3,数组公式,财务管理电算化,一、函数的基本语法=函数名（参数序列）参数序列可以是数值、文本、逻辑值、单元格（区域）地址或名称，各种表达式和函数等，可以一个或多个，也可没有。如是多个，中间用“，”分开。二、函数的输入直接输入或粘贴函数,2.2EXCEL函数及使用,财务管理电算化,Excel除了能运用公式，还能进行大量的函数运算。这些函数运算，能够满足各种日常的办公、财务、统计和各种报表等所需要的计算。函数的表达式：=函数名（参数1，参数2）例如：求和SUM（number1，number2）或SUM（B2：C5）,函数预定义的公式,财务管理电算化,两种方法：直接输入：直接在要用公式的单元格输入函数，动态下拉列表可以帮助我们减少错误插入函数：1.单击编辑栏前fx2.开始编辑3.公式点击某函数对话框将显示函数名称、各个参数、函数及其参数的说明等,输入函数的操作,财务管理电算化,即，函数可以作为另一函数的参数使用。注意问题：1.被嵌套函数的返回值，要与其代替参数类型一致2.公式中函数嵌套级别最多为七级,函数可以嵌套,财务管理电算化,三、数学函数的基本语法及使用求和函数应用最多无条件求和函数：SUM()求和函数应用最多无条件求和函数：SUM()SUM(NO1,NO2,NO3)（NO1为1-30个参数）返回某一单元格区域中所有数字之和,2.2EXCEL函数及使用,财务管理电算化,条件求和函数:SUMIF()SUMIF(条件范围，求和条件，求和范围)根据制定条件对若干单元格求和多组数据相乘求和函数:SUMPRODUCT()在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT(array1,)（array1为2-30个数组），数组参数应有相同的维数，非数值型数组元素作为0处理。,2.2EXCEL函数及使用,财务管理电算化,求平均值函数Average()，用法同SUM()Average(NO1,NO2,NO3)（NO1为需要计算平均值的1-30个参数）求最大、最小值函数MAX()、MIN()，用法同SUM()ABS()函数:返回参数的绝对值，参数可以是数字和表达式；,2.2EXCEL函数及使用,财务管理电算化,RAND()函数：无参数，产生0和1之间的一个随机数；INT()函数：取整函数，用于把一个数舍入到小于它的最大整数；参数可以是数字或表达式；MOD()函数：返回参数进行出发运算后的余数MOD（被除数，除数）POWER()函数：对数字进行乘幂运算POWER(参数1，参数2)，参数1为底数，参数2为指数,财务管理电算化,SQRT()函数：返回数值的平方根，参数必须是非负数的数。ROUND()函数：用于其参数引用的数值四舍五入到指定的小数位。ROUND(参数，保留的小数位数)参数可以是数值，含有数值的单元格引用或计算结果是数值的表达式；保留小数可以是任意整数，为正数时，舍入到小数点后；为0时，舍入到整数的个位；为负数是，表示保留小数点左边的位数。,2.2EXCEL函数及使用,财务管理电算化,四、统计函数的基本语法及使用COUNT(range)函数：统计指定区域是数字型数据的个数。COUNTIF(range,criteria)函数：计算区域中满足给定条件的单元格的个数。这两个函数多用于数据汇总统计分析中。五、文本函数的基本语法及使用VALUE()函数：用于将指定文本转化为数字。TEXT()函数：用指定的格式将数字转换为文本字符串。TEXT(参数，格式)，参数可以是任意数字、表达式或内容是数字的单元格引用，格式用EXCEL规定的格式化符号表示,2.2EXCEL函数及使用,财务管理电算化,LEN()函数:用于求指定文本字符串的长度。LEN(“字符串”)CODE()函数：用于求指定字符的ASC码值。参数可以是任意字符和数字，当为字符串时，返回第一个字符的ASC码值。CHAR()函数：用于求出任一个ASC码值相对应的字符。参数是数字0-127。TRIM()函数：将字符串的前导空格、后接空格及字符间多余一个的空格滤去。,2.2EXCEL函数及使用,财务管理电算化,EXACT()函数：区分大小写字母来比较两个字符串是否完全相同。返回值为FALSE或TURE。RIGHT()函数：用于从指定字符串提取右边N个字符。RIGHT(字符串，N)LEFT()函数：用于从指定字符串提取左边N个字符。LEFT(字符串，N)MID()函数：用于从指定字符串中第M个字符开始提取N个字符。LEFT(字符串，M，N),2.2EXCEL函数及使用,财务管理电算化,六、逻辑函数的基本语法及使用IF()函数：执行真假值判断，根据逻辑计算的真假值，返回不同结果。IF(条件表达式，表达式1，表达式2)说明：条件表达式表示计算结果为TRUE或FALSE的任意值或表达式，本参数可以使用任何比较运算符。条件表达式为TRUE时返回的表达式1的值，否则返回表达式2的值。,2.2EXCEL函数及使用,财务管理电算化,IF()函数的嵌套使用：可以嵌套7层IF(条件表达式1，表达式1，IF(条件表达式2，表达式2，IF(条件表达式3，表达式3，IF()练习：如何利用IF函数在工资表中计算个人所得税，并将工作表转化为工资条？,2.2EXCEL函数及使用,财务管理电算化,AND()函数表示逻辑与，当所有条件都满足时，返回TRUE，否则返回FALSE。OR()函数表示逻辑或，只要有一个条件满足时就返回TRUE，只有所有条件均不满足时才返回FALSE。NOT()函数不是逻辑非。,2.2EXCEL函数及使用,财务管理电算化,公式中的常见出错信息与处理,财务管理电算化,查找与引用函数,LOOKUP()从单行或单列或从数组中查找某一个值。条件是向后兼容性HLOOKUP(lookupvalue,tablearray,rowindexnum,rangelookup)搜索数组区域首行满足条件的元素，确定待检索单元格区域中的列序号，再进一步返回选定单元格的值。INDEX（）在给定的单元格区域中，返回特定行列交叉处单元格的值或引用Row(reference)column(reference)返回一个引用的行号返回一个引用的列号Rows(array)columns(array)返回某一引用或数组的行数返回某一引用或数组的列数VLOOKUP(lookupvalue,tablearray,colindexnum,rangelookup)搜索数组区域首列满足条件的元素，确定待检索单元格区域中的行序号，再进一步返回选定单元格的值。默认下表是升序排序,财务管理电算化,说明,财务管理电算化,一、记录的增加、删除、修改和查找“选择单元格-数据/记录单-记录单对话框”二、数据排序常用工具栏或者菜单命令三、数据的筛选自动筛选、高级筛选四、数据的分类汇总五、数据分析单变量数据分析，多变量数据分析六、数据透视表：透视表的建立、删除七、数据的合并计算,2.3EXCEL的数据管理与分析,财务管理电算化,实验目的1.掌握单元格的引用方法。2.学会公式和函数的使用,实验2Excel函数的使用,财务管理电算化,实验2.1实验资料,某地所属个公司税款缴纳情况如下：5月3日，纺织厂应纳税所得额980000元；5月5日，地产公司应纳税所得额7856000元；5月6日，天泽公司应纳税所得额3500000元；5月10日，八达公司应纳税所得额620000元；5月11日，丝绸公司应纳税所得额780000元；5月13日，四海公司应纳税所得额500000元；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实验结果,编制某地所属公司税款缴纳情况表,财务管理电算化,实验2.2实验资料,某企业职工工资及奖金发放资料和规定如下，请按要求完成相应工作。,财务管理电算化,说明：1.奖金按部门发放，每人每月车间200元，保卫科150，其他100；2.津贴按职称发放，高级1000元，副高800，中级500，初级200元；3.女职工每月保健费50元，男职工每月30元；4.公司代缴个人所得税，应税所得额=应发工资-2000，税率按现行税法规定执行。要求：1.根据所个资料建立包括奖金、津贴、保健费、应发工资、个人所得税和实发工资项目在内的职工工资表；注意公式