5.4计算数据课件

第五章

电子表格处理软件应用5.41日常生活中，我们经常需要处理大量数据，例如：学生学习成绩总分和平均分的计算；销售部门、财会部门的统计报表等，应该怎样通过电脑来帮助我们处理这些大量的数据呢？5.4计算数据2数据计算的两种方法：使用自定义公式计算1使用函数计算231.公式的概念Excel的公式由运算符、数值、字符串、变量和函数组成。公式必须以等号“=”开头，即在Excel的单元格中，凡是以等号开头的输入数据都被认为是公式。在等号的后面可以跟数值、运算符、变量或函数，在公式中还可以使用括号。例如：=10+4*6/2+(2+1)*50就是公式，可以在任何单元格中输入此公式．Excel会把公式的计算结果显示在相应的单元格中．使用自定义公式计算142、运算符在Ｅxcel公式中可以使用运算符。运算符运算功能优先级()括号1-负号2%百分号3^乘方4*与/乘、除法5+与-加、减法6&文本连接7=、<、>、<=、>=、<>等于、小于、大于、小于等于、大于等于、不等于853、说明括号的运算级别最高，在Excel的公式中只能使用小括号，无中括号和大括号。小括号可以嵌套使用，当有多重小括号时，最内层的表达式优先运算。同等级别的运算符从左到右依次进行。“&”为字符连接运算，其作用是把前后的两个字符串连接为一串。例如，"ABC"&"DEF"的结果为“ABCDEF”，"财务"&"经济"的结果为“财务经济”。6使用自定义公式计算1相对引用的例子【例1】某班某次期末考试成绩如下图所示，计算各同学的总分，即F列的数据。在F2计算公式=C2+D2+E2然后向下复制该公式！7使用自定义公式计算1单元格公式的引用：相对引用和绝对引用和混合引用相对引用是基于包含公式和引用的单元格的相对位置而言的。如果公式所在单元格的位置改变，引用也将随之改变，如果多行或多列的复制公式，引用会自动调整。默认情况下，新公式使用相对引用。绝对引用则总是在指定位置引用单元格(例如＄F＄5).如果公式所在单元格的位置改变，绝对引用的单元格也始终保持不变，如果多行多列地复制公式，绝对引用将不作调整。混合引用在复制公式时，如果要求行不变但列可变或者列不变而行可变，那么就要用到混合引用。例如：＄F1则表示对F列的绝对引用和对第一行的相对引用

F＄1则表示对第一行的绝对引用和对A列的相对引用81.相对引用相对引用也称为相对地址引用，是指在一个公式中直接用单元格的列标与行号来取用某个单元格中的内容。92.绝对引用绝对引用总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。

绝对引用的形式是在引用单元格的列号与行号前面加“$”符号。比如，$A$1就是对A1单元格的绝对引用。10绝对引用案例【例1.3】某品牌皮鞋批发商3月份的销售数据如图所示，每双皮鞋的单价相同，计算各皮鞋代销商场应支付的总金额，。在D6输入公式：=$c$3*C6然后向下复制此公式！113.混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。比如，$A1、$B1案例124.三维引用对同一工作薄内不同工作表中相同引用位置的单元格或区域的引用称为三维引用。引用形式为：Sheet1:Sheetn!单元格（区域）例如，Sheet1:sheet8!C5和Sheet1:Sheet8!B2:D6都是三维引用，前者包括Sheet1~sheet8这8个工作表中每个工作表的C5单元格，后者包括此8个工作表中每个工作表的B2:D6区域。135、内部引用与外部引用

1）引用相同工作表中的单元格例：=G3+G5+G10*102）引用同一工作簿不同工作表中的单元格例=Sheet1!G3+Sheet2!G5+Sheet2!E273）引用已打开的不同工作簿中的单元格例：=[Book1]Sheet1!$IL$4+[Book2]Sheet2!$E$74）引用未打开的不同工作薄中的单元格，='C:\dk\[Book1.xlsx]Sheet2'!$B$4+'C:\dk\[Book1.xlsx]Sheet1'!$C$65）同一公式中存在几中不同的引用，例：=[Book1]Sheet1!$A$4+Sheet1!G7+F9141、函数的概念函数是Excel已经定义好了的一些特殊公式，它们可以对一个或多个数据进行计算，然后把计算的结果存放在某个单元格中。2、Excel函数类型工作表函数、财务函数、日期函数、时间函数、数学与三角函数、统计类函数、数据库管理函数、文本函数及信息类函数等。使用函数计算2151、SUM和Average用法：sum(x1,x2,x3……x255)Average(x1,x2,x3……x255)其中，x1,x2……x255可以是数字，单元格或单元格区域的引用，也可以是表达式。功能：

sum求所有参数x1,x2……x255的总和．Average求所有参数x1,x2……x255的平均值常用工作表函数16Sum和Average的应用案例某单位的工资数据如下图所示，已知基本工资、奖金、水费、电费，计算总额、总计与平均数17２、max和min用法：max(x1,x2,x3……x255)min(x1,x2,x3……x255)其中，x1,x2……x255可以是数字，单元格或单元格区域的引用，也可以是表达式。功能：max求所有参数x1,x2……x255的最大值．min求所有参数x1,x2……x255的最小值常用工作表函数18３、count用法：count(x1,x2,x3……x255)其中，x1,x2……x255可以是数字，单元格或单元格区域的引用，也可以是表达式。功能：统计x1,x2……x255中数字的个数．常用工作表函数191、要是写成=COUNT(B1,D1)，那就是计算机B1和D1两个单元格中有几个数字（不包括C1单元格），2、但是如果我写成=COUNT(B1:D1)，注意，中间用冒号了，那就是计算机从B1单元格到D1单元格中数字的个数了，（这就包括数字单元格了）3、再有，我写成=COUNT("B1","D1","123","hello")，那结果就是1,因为只有"123"一个数字，B1和D1因为加了引号，所以是字符了，不是单元格。4、如果A1为1，A5为3，A7为2，其他均为空，则：COUNT(A1:A7)等于3备注：计算出A1到A7中，数字的个数COUNT(A4:A7)等于2备注：计算出A4到A7中，数字的个数COUNT(A1:A7,2)等于4备注：计算A1到A7单元格和数字2一起，一共是多少个数字（A1到A7中有3个，加上数字2，一共4个）count例：20count与counta都是：返回非空单元格的个数区别：count当单元格内容是数值时起作用；counta无论单元格是什麽内容都起作用；例：AB1-12a2-12asd3-13a4-14asd5-15a6-16asd7-17a8-18asd9-1912310asdasd1121aCOUNT(A1:A11)=10COUNTA(B1:B11)=11214、countif

用法：COUNTIF(单元格区域,“条件”)其中条件可以是比较式，但必须用双引号括起来．如“>90”，“<=60”

功能:

统计指定单元格区域中满足条件的数字的个数．COUNTIF函数应用举例假设存在如下图的学生成绩表，现在，让我们一起来使用COUNTIF函数进行统计，下面统计的是不及格的人数:22如下图，首先，选择F列中的F3单元格，之后，在其上方fx函数输入区域，直接输入=COUNTIF(E3:E55,“<60“)，之后按回车键确认，这样，就得到不及格的人数了！=COUNTIF(E3:E55,“<60“)分析：E3:E55代表统计的数据区域是E3到E55，“<60“统计的条件是成绩小于60；知识扩展：“<60“改为：“<80“是什么意思呢？表示的是，统计分数小于80的人数；“<60“改为“>60“是什么意思呢？表示的是：统计及格的人数！23５、IF用法IF(条件,表达式1,表达式2)其中表达式可以是数字、函数、单元格或单元格区域。功能条件成立时，函数结果为表达式１的值；条件不成立时，函数的结果为表达式２的值．常用工作表函数24

excel函数中if函数的使用非常广泛，特别是在单条件判断的时候，用好if函数可以帮我们完成很多功能。最简单的excelif函数应用例：又图数据在d列显示如下结果：如果数据1大于60则显示合格，否则显示不合格。那么在d2单元格输入以下公式：=if(a2>60,"合格","不合格")然后向下拖拽，自动生成数据，如下图D列效果。25if函数必须的条件：1.每一个excelif函数必须使用英文的括号括起来；2.括号内为三个数据，第一个数据是条件（如上例中的a2>60），第二数据为满足第一个数据后返回的结果，通常使用英文的引号括起来，第三个数据是不满足第一个数据时需要返回的结果；经常出现的错误：其中的符号如逗号和引号皆为英文（也就是所谓的半角），若输入中文符号则错误；if的右括号放在了条件的后面；（这是在多个条件使用if函数进行嵌套时非常容易犯的错误）26if函数嵌套用法（七层）例：下图数据，在e列显示如下结果：如果A列的数据1小于60则显示不合格，如果大于等于60而小于80则显示合格，如果大于等于80而小于90显示良好，如果大于等于90则显示优秀。这是经典的if嵌套应用例子，需要我们使用if函数的嵌套。if嵌套书写前，首先你要理解要求，并将要求数学化，也就是使用数学的模式表达出来，if函数多重嵌套一般情况下我们可以将它看做分段函数，那么问题就很容易解决了。例子可以在E2单元格使用如下代码：=if(a2<60,"不合格",if(a2<80,"合格",if(a2<90,"良好","优秀")))当数据1小于60时，显示不合格，这时在不合格逗号的右侧默认就是>=60的情况，那么根据题意，只需再满足<80即可显示合格，于是我们将最简单的if函数的第三个数据变成了一个if函数，依次类推，每一次可以将一个if函数作为每一个基本函数的第三个数据，从而形成多种嵌套。（图例中多余在最后一个if前后加了一个括号，当然这种方法也正确，但不是最简单的。）27其实还有另一种写法，也就是将嵌套的if写在基本if函数的第二个数据的位置，如下图，不过这种写法不常用，也比较不好理解，并且容易写错，不推荐大家使用。28INT函数的用法INT函数的用法是将数值向下取整为最接近的整数.使用INT函数注意三点：第一，INT函数是取整；第二，小数部分不进行四舍五入，直接去掉。第三，INT函数处理负数的小数时总是向上进位，这一点和TRUNC函数不同。例如：=INT(18.89)，结果为18。=INT(-5.8)，结果为-6。=TRUNC(-5.8)，结果为-5。29INT函数具体使用举例：取整求和对上表中员工的工资小数部分直接去掉，不进行四舍五入，取整然后求总共需要发放的工资是多少。解题思路：先用INT函数对数据进行取整，然后再求和。在B6单元格输入下面公式之一均可：普通公式：=SUM(INT(B2),INT(B3),INT(B4),INT(B5))数组公式：=SUM(INT(B2:B5))数组公式需要按下ctrl+shift+enter三键结束。显然数组公式比普通公式简洁许多。30Excel中mod函数是一个求余函数。返回两数相除的余数。MOD函数一、两个异号整数求余1.函数值符号规律(余数的符号)mod(负,正)=正mod(正,负)=负结论：两个整数求余时，其值的符号为除数的符号。

2.取值规律先将两个整数看作是正数，再作除法运算①能整除时，其值为0②不能整除时，其值=除数×(整商+1)-被除数例：mod(5,-4)=-3即：5除以4的整数商为1，加1后为2;其与除数之积为8;再与被除数之差为(5-8=-3);取除数的符号。所以值为-3。MOD函数使用大致分为下面两种情况。31二、两个小数求余取值规律：被除数-(整商×除数)之后在第一位小数位进行四舍五入。例：mod(9,1.2)=1即：9除1.2其整商为7;7与除数1.2之积为8.4;8.4四舍五入之后为8;被除数9与8之差为1。故结果为1。例：mod(9,2.4)=0即：9除2.2其