5.4计算数据

excel函数中 if函数的使用非常广泛，特别是在单条件判断的时候，用好 if函 数可以帮我们完成很多功能。 最简单的 excel if函数应用 例：又图数据在d列显示如下结果：如 果数据1大于60则显示合格，否则显示 不合格。 那么在d2单元格输入以下公式： =if(a2>60,"合格","不合格") 然后向下拖拽，自动生成数据，如下 图D列效果。
绝对引用案 例
【例1.3】某品牌皮鞋批发商3 月份的销售数据如图所示， 每双皮鞋的单价相同，计算 各皮鞋代销商场应支付的总 金额， 。
在D6输入公式： =$c$3*C6 然后向下复制此公式！
3.混合引用
混合引用具有绝对列和相对行，或是绝对行和相对列。比如，$A1、$B1 案例
4.三维引用
对同一工作薄内不同工作表中相同引用位置的单元格或区域的引用称为三维引用。 引用形式为：
数据计算的两种方法：
1 使用自定义公式计算
2
使用函数计算
1
使用自定义公式计算
1.公式的概念
Excel的公式由运算符、数值、字符串、变量和函数组成。 公式必须以等号“=”开头，即在Excel的单元格中，凡是以等号开头的输入数据都 被认为是公式。在等号的后面可以跟数值、运算符、变量或函数，在公式中还可 以使用括号。 例如： =10+4*6/2+(2+1)*50 就是公式，可以在任何单元格中输入此公式．Excel会把公式的计算结果显示在 相应的单元格中．
2、运算符
在Ｅxcel公式中可以使用运算符。
运算符
() % ^ *与/ +与-
运算功能
括号 负号 百分号 乘方 乘、除法 加、减法
优先级
1 2 3 4 5 6
&
文本连接
7
8
=、<、>、 等于、小于、大于、小于 <=、>=、<> 等于、大于等于、不等于
3、说明
括号的运算级别最高，在Excel的公式中只能使用小括号，无中括号和大括号。 小括号可以嵌套使用，当有多重小括号时，最内层的表达式优先运算。同等级别 的运算符从左到右依次进行。 “&”为字符连接运算，其作用是把前后的两个字符串连接为一串。 例如，"ABC"&"DEF"的结果为“ABCDEF”，"财务"&"经济"的结果为“财务经济”。
例如： =INT(18.89)，结果为18。 =INT(-5.8)，结果为-6。 =TRUNC(-5.8) ，结果为-5。
INT函数具体使用举例：取整求和
对上表中员工的工资小数部分直接去掉，不进行四舍 五入，取整然后求总共需要发放的工资是多少。 解题思路：先用INT函数对数据进行取整，然后再 求和。 在B6单元格输入下面公式之一均可： 普通公式： =SUM(INT(B2),INT(B3),INT(B4),INT(B5)) 数组公式：=SUM(INT(B2:B5)) 数组公式需要按下ctrl+shift+enter三键结束。显然 数组公式比普通公式简洁许多。
当数据1小于60时，显示不合格，这时在 不合格 逗号的右侧默认就是>=60的情况，那么根据 题意，只需再满足<80即可显示合格，于是我们将最简单的 if函数的第三个数据变成了一个if函 数，依次类推，每一次可以将一个if函数作为每一个基本函数的第三个数据，从而形成多种嵌 套。 （图例中多余在最后一个 if前后加了一个括号，当然这种方法也正确，但不是最简单的。）
1.函数值符号规律(余数的符号)
mod(负,正)=正 mod(正,负)=负 结论：两个整数求余时，其值的符 号为除数的符号。
用法： sum(x1,x2,x3……x255) Average (x1,x2,x3……x255) 其中，x1,x2 ……x255可以是数字，单元格或单元格区域的引用，也可以是表达 式。 功能： sum求所有参数x1,x2 ……x255的总和． Average求所有参数x1,x2 ……x255的平均值
2）引用同一工作簿不同工作表中的单元格 例=Sheet1!G3+Sheet2!G5+Sheet2!E27
3）引用已打开的不同工作簿中的单元格 例：=[Book1]Sheet1!$IL$4+[Book2]Sheet2!$E$7 4）引用未打开的不同工作薄中的单元格， ='C:\dk\[Book1.xlsx]Sheet2'!$B$4+'C:\dk\[Book1.xlsx]Sheet1'!$C$6 5）同一公式中存在几中不同的引用， 例：=[Book1]Sheet1!$A$4+Sheet1!G7+F9
2
使用函数计算
1、函数的概念
函数是Excel已经定义好了的一些特殊公式，它们可以对一个或多个数据进行计 算，然后把计算的结果存放在某个单元格中。
2、Excel函数类型
工作表函数、财务函数、日期函数、时间函数、数学与三角函数、统计类函数、 数据库管理函数、文本函数及信息类函数等。
常用工作表函数
1、SUM和Average
MOD函数
Excel中mod函数是一个求余函数。返回两数相除的余数。 MOD函数使用大致分为下面两种情况。 一、两个异号整数求余 2.取值规律 先将两个整数看作是正数，再作除法运算 ①能整除时，其值为0 ②不能整除时，其值=除数×(整商+1)-被除数 例：mod(5,-4)=-3 即：5除以4的整数商为1，加1后为2;其与除数之积为 8;再与被除数之差为(5-8=-3);取除数的符号。所以值为-3。
其实还有另一种写法，也就是将嵌套的if写在基本if函数的第二个数据的位置，如下图，不过这种写法不常 用，也比较不好理解，并且容易写错，不推荐大家使用。
INT函数的用法
INT函数的用法是将数值向下取整为最接近的整数.
使用INT函数注意三点： 第一，INT函数是取整； 第二，小数部分不进行四舍五入，直接去掉。 第三，INT函数处理负数的小数时总是向上进 位，这一点和TRUNC函数不同。
COUNT(A4:A7) 等于 2 备注：计算出A4到A7中，数字的个数
COUNT(A1:A7, 2) 等于 4 备注：计算A1到A7单元格和数字2一起，一共是多少个数字（A1到 A7中有3个，加上数字2，一共4个）
count与counta 都是：返回非空单元格的个数 区别： count 当单元格内容是数值时起作用；
常用工作表函数
３、count
用法：count(x1,x2,x3……x255)
其中，x1,x2 ……x255可以是数字，单元格或单元格区域的引用，也可以是表达 式。
功能： 统计x1,x2 ……x255中数字的个数．
counΒιβλιοθήκη Baidu 例：
1、要是写成=COUNT(B1,D1)，那就是计算机B1和D1两个单元格中有几个数字（不包括C1单 元格），
知识扩展： “<60“改为：“<80“是什么意思呢？
表示的是，统计分数小于80的人数；
“<60“改为“>60“是什么意思呢？ 表示的是：统计及格的人数！
常用工作表函数
５、IF
用法 IF(条件, 表达式1, 表达式2) 其中表达式可以是数字、函数、单元格或单元格区域。 功能 条件成立时，函数结果为表达式１的值；条件不成立时，函数的结果为表达式２ 的值．
if函数必须的条件：
1.每一个excel if函数必须使用英文的括号括起来； 2.括号内为三个数据，第一个数据是条件（如上例中的a2>60），第二数据 为满足第一个数据后返回的结果，通常使用英文的引号括起来，第三个数据 是不满足第一个数据时需要返回的结果；
经常出现的错误：
其中的符号如逗号和引号皆为英文（也就是所谓的半角），若输入中文符号 则错误； if的右括号放在了条件的后面；（这是在多个条件使用if函数进行嵌套时非常 容易犯的错误）
要用到混合引用。 例如：＄F1则表示对F列的绝对引用和对第一行的相对引用 F＄1则表示对第一行的绝对引用和对A列的相对引用
1.相对引用
相对引用也称为相对地址 引用，是指在一个公式中 直接用单元格的列标与行 号来取用某个单元格中的 内容。
2.绝对引用
绝对引用总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引 用保持不变。 绝对引用的形式是在引用单元格的列号与行号前面加“$”符号。 比如，$A$1就是对A1单元格的绝对引用。
位置改变，引用也将随之改变，如果多行或多列的复制公式，引用会自动调整。默认 情况下，新公式使用相对引用。
绝对引用则总是在指定位置引用单元格(例如＄F＄5).如果公式所在单元格的位置改
变，绝对引用的单元格也始终保持不变，如果多行多列地复制公式，绝对引用将不作 调整。
混合引用在复制公式时，如果要求行不变但列可变或者列不变而行可变，那么就
1
使用自定义公式计算
相对引用的例子
【例1】某班某次期末考试成绩如下图所示，计算各同学的总分，即F列的数据。
在F2计算公式
=C2+D2+E2
然后向下复制该 公式！
1
使用自定义公式计算
单元格公式的引用：相对引用和绝对引用和混合引用 相对引用是基于包含公式和引用的单元格的相对位置而言的。如果公式所在单元格的
例： 12345678910 11
counta 无论单元格是什麽内容都起作用；
A 12 12 13 14 15 16 17 18 19 asd 21
B a asd a asd a asd a asd 123 asd a
COUNT(A1:A11) = 10 COUNTA(B1:B11) = 11
4、countif 用法： COUNTIF(单元格区域, “条件”) 其中条件可以是比较式，但必须用双引号括起来．如“>90”，“<=60” 功能: 统计指定单元格区域中满足条件的数字的个数． COUNTIF函数应用举例 假设存在如 下图的学生成绩 表，现在，让我 们一起来使用 COUNTIF函数 进行统计，下面 统计的是不及格 的人数:
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中，数字的个数
Sheet1:Sheetn!单元格（区域）
例如，Sheet1:sheet8!C5和Sheet1:Sheet8!B2:D6都是三维引用，前者包括 Sheet1~sheet8这8个工作表中每个工作表的C5单元格，后者包括此8个工作表中每 个工作表的B2:D6区域。
5、内部引用与外部引用
1）引用相同工作表中的单元格 例：=G3+G5+G10*10
if函数嵌套用法（七层）
例：下图数据，在e列显示如下结果：如果A列的数据1小于60则显示不合格，如果大于等于60 而小于80则显示合格，如果大于等于80而小于90显示良好，如果大于等于90则显示优秀。 这是经典的if嵌套应用例子，需要我们使用 if函数的嵌套。 if嵌套书写前，首先你要理解要求，并将要求数学化，也就是使用数学的模式表达出来，if函数 多重嵌套一般情况下我们可以将它看做分段函数，那么问题就很容易解决了。例子可以在E2 单元格使用如下代码： =if(a2<60,"不合格",if(a2<80,"合格",if(a2<90,"良好","优秀")))
如下图，首先，选择F列中的F3单元格，之后，在其上方fx函数输入区域，直接输入 =COUNTIF(E3:E55,“<60“)，之后按回车键确认，这样，就得到不及格的人数了！
=COUNTIF(E3:E55,“<60“)分析： E3:E55代表统计的数据区域是E3到E55， “<60“统计的条件是成绩小于60；
Sum和Average的应用案例
某单位的工资数据如下图所示，已知基本工资、奖金、水费、 电费，计算总额、总计与平均数
常用工作表函数
２、max和min
用法： max(x1,x2,x3……x255) min (x1,x2,x3……x255) 其中，x1,x2 ……x255可以是数字，单元格或单元格区域的引用，也可以是表达式。 功能： max求所有参数x1,x2 ……x255的最大值． min求所有参数x1,x2 ……x255的最小值