表格数据计算的示例第4章 EXCEL应用

第4章EXCEL应用

1

表格数据计算的示例

•超市收款小票上的计算

•单位的工资表

•学生成绩的计算

•企业经营状况分析

2

超市收款小票上的计算

•在超市买东西，收款后都有打印的小票；票上列出购买的商

品的名称（或代码）、数量、单价、金额等，最后还有应收

总金额、付款现金额、找零等等。

・整张小票上，看不到任何表格线，但这确实是一个最简单、

最典型的表格计算的例子

•我们可以将有关的数据排列在一个表格中：

商品名称或编号数量单价金额

•每一行对应一种商品，金额=数量X单价。

・第四列的数据求和就是总金额；付款现金额减去总金额就是

找零。

3

单位的工资表

•某单位会计在发放下月工资前，需要计算每个职工应当发多

少钱。这不仅与每个职工各自的工资额有关，也与他的业绩

有关，每个职工还有相应的各种福利；而且在发放之前还要

代扣所得税等等。

•可以列一个表：

姓名基本工资业绩工资……实发工资

•每个职工的有关数据排在一行

•这也是表格计算的典型例子。

4

学生成绩的计算

•教师在计算班上学生的总成绩时，要考虑每

个学生的平时作业成绩、期中、期末考试成

绩等：

姓名平时作业期中期末总成绩

•计算每个学生总成绩的方法都是相同的，不

同的是各自的成绩（计算所用的数据值）。

这当然也是典型的表格计算。

5

企业经营状况分析

•某企业会计比较近3年的企业经营状况:

今年去年前年

销售额

成本

税前收入

税款

利润

税前收入=销售额-成本税款=税前收入X税率

利润=税前收入-税款

•这同样是典型的表格计算，各列的计算方法相同。

2.Excel基本功能的应用

•示例表格说明

•工作表的设计

•数据的输入

•公式的使用

7

示例表格说明

第1次第2次第3次平时考试

学号姓名总成绩学分

作业成绩作业成绩作业成绩成绩成绩

S001李凌535138988.33

・计算数据域：

-平时成绩二第一次作业成绩+第二次作业成绩+第三次作业成绩

-总成绩二平时成绩X2+考试成绩义70%

「3如果总成绩次0

_学分二Y

I0否则

工作表的设计

・工作表的命名

-选定工作表：单击工作表标签

格式一＞工作表一》重命名

-右击工作表标签：重命名（快捷菜单）

•数据单元格布局

•输入标题文字

•标题单元格格式

•保存工作表内容

9

数据单元格布局

•学号：在C列

­姓名：在D列

・第1次作业成绩：在E列

・第2次作业成绩：在F列

・第3次作业成绩：在G列

•平时成绩：在H列

•考试成绩：在I列

•总成绩：在J列

•学分：在K列

•各列第1行单元格放项目标题

10

输入标题文字

•直接输入：

-选定单兀格

—键入文字

•填充：

-文字中带有数字，可以拖填充柄填充

•复制后修改：

-利用男贴板。选定，复制，粘贴

-修改：在编辑栏，或双击

11

标题单元格格式

•命令：

—格式一＞单兀格

-右击：设置单元格格式（快捷菜单）

•项目：

-底纹：选图案标签

-文字排列：选对齐标签

折行，文字方向，垂直对齐，水平对齐

•宽度：

-用鼠标调整列标签

12

保存工作表内容

,操作：

-工具栏按钮

-命令：文件一＞保存（或另存为）

----与Word、PowerPoint类似

•文件：一个工作簿

——多张工作表

•扩展名：.XLS

——Excelsheet

13

数据的输入

•单元格的选定

-活动单元格

-选定区域

-选定不连续的多个单元格

-选定多张工作表

•数据输入方法

14

数据输入方法

•在当前活动单元格中输入

-直接键入。在编辑栏中编辑

-在单元格中编辑

•在选定区域中输入

-使用Tab和Enter键在各单元格间移动

•在选定的多个不连续的单元格中输入

•在选定的多个单元格中输入相同的数据（按

住CTRL键击回车）

15

公式的使用

•公式：所在单元格的值由计算得到

•单元格引用：A1引用样式和R1C1引用样式

­公式的组成

-“二”打头

-运算符：

•算术运算符+,*,/,%,八;

•比较运算符二，>,<,>=,<=,<>；

•文本运算符&;

•引用运算符（冒号），（逗号），（空格）。

16

公式中的函数

­函数：可进行比较复杂的运算

-语法格式：函数名(参数表)

IF函数：IF(条件，为真时取值，为假时取值)

•计算学分：IF(J2>=60,3,0)

­函数的嵌套：某个参数是函数

•例：IF(A1<A2,"<",IF(A1=A2,

将这个公式存入K2单元格：

如果A1的值小于A2的值，K2为“v”

如果A1的值等于A2的值，K2为“二”

如果A1的值大于A2的值，K2为

17

公式的输入

■直接键入输入

-常数和运算符只能键入（无法选择）

-引用单元格和区域可用鼠标选定

•公式中函数的输入

-键入―后，名称框中显示函数名（下拉列表），

可以插入选定的函数

-直接单击编辑栏上的“插入函数”按钮也可在

“插入函数”对话框中选则要插入的函数

-可在“函数参数”对话框上输入参数并显示运算

结巢

-津击“确定”完成函数的输入

18

上机练习题（1）:

•将课讲的成绩表制作出来

-自行设计标题单元格的格式，并横向居中显示

-自行为表上学生给出学号、姓名和各项成绩

（至少要有12名学生的成绩）

-计算每个学生的平时成绩及总成绩

-假定这门课程3学分，计算每个学生的学分

•练习讲过的其它功能

19

单元格显示格式

•显示格式的概念

-同一个值，显示格式可以不同

-不同的值，有可能显示为相同的结果

-改变显示格式，不影响存储的值

•一个输入了公式的单元格，具有：

-内容，即输入的公式

-值，即公式的计算结果

-显示，即屏幕上看到的

20

数据长度超过列宽度

・Excel如何处理？

一文字

•右侧单元格空——在两侧继续显示

•右侧单元格不空—只显示一部分

-数字或日期——显示一串“铲

・用户应如何调整

-加大列的宽度

-改变数据显示格式

21

数据类型的识别和转换

•数据类型

-Excel对不同类型数据有不同的存储和处

理，也有不同的显示格式

•输入数据的类型识别

-不同类型的数据输入时有不同的要求

•各类数据的存储

•可能的识别错误

■公式中数据类型的自动转换

22

数据的类型识别

•输入的数据被作为哪一类？

-公式：以“=”开头

.计算的结果可能是数字，也可能是文字。

-常数：数字，文字，日期和时间

•数字：0~9以及+-*/()$%.Ee

­文字：包含非数字符号的字符组合

•日期和时间：按国家和地区习惯格式输入

23

可能的识别错误

•编码被当成数字：

-先将单元格数字格式设置为“文本”

­分数被作为日期

-先键入“0”和一个空格

•上午与下午的区分

-按12小时计时：时间后空一格再键入a或P。

-不加说明就按24小时计时。

•文字中的“回车”被作为输入结束

-用ALT+ENTER代替ENTER

24

数据类型转换

•可以自动转换的：

-文本转换为数字

•=F，，+“2”——结果为3

•="92・6・1''/92・5・1”——结果为31

-数字和逻辑值转换为文本

•=4&TRUE——结果为“4TRUE”

•不能自动转换的

-文本内容不是数字而是公式时

•=SQRT(U8+TJ)——结果出错

25

公式的错误值

•#####•#N/A

-数值太长-没有可用数值

-日期或时间为负•#REF!

•#VALUE!-单元格引用无效

-使用了错误的参数或运算对象

类型•#NUM!

-数字出错

•#DIV/O!

•#NULL!

-公式以o为除数

-引用两个不相交区域

•#NAME?的交点

-公式中有不能识别的文本

26

数据的编辑

­数据复制

­数据填充

•公式的复制与移动

•合并单元格

•插入、删除和清除单元格、行、列和区域

•定位、查找和替换

27

数据复制

•一对一：一个单元格到另一个单元格

•对等：一个区域到另一个形状、大小都相同的区域

•一对多：

-一个单元格到一个区域

-一行的区域到多行的区域（歹U数相同）

-一列的区域到多列的区域（行数相同）

-一个区域到一个由多个同样区域组成的区域

•其余形状不同不能复制

28

数据复制方法

•剪贴板：各种情况下都可以用

-菜单命令

-工具栏按钮

・拖拽：一对一,,或对等

-用鼠标拖选定单元格或者区域的边框

•填充柄：一对多

-在特定情况下可以进行复制：

■数字

•不包含数字的文本

29

数据填充

•填充柄：在选定单元格或区域的右下角

•用左键拖：复制或填充序列，按Ctrl互换

•用右键拖：可选择填充类型

•自定义序列：可定义、编辑和删除序列

-“工具一,逡项〃命令，“自定义〃逡项卡

•菜单栏的填充命令：

—“编辑—＞嫉克—＞图列”命令

-等差、等比，按步衣

30

公式的复制与移动(1)

•复制工具：剪贴板，填充柄，拖拽

・效果：自动修改！

-以复制结果为当前单元格，可以见到与源单元

格的公式并不相同。A1单元格中的公式

=SUM(A2:A5),复制到B2单元格中变成了

=SUM(B3:B6)O

•引用类型：相对，绝对，混合

一例：A1,$A$1,A$1,$A1

31

公式的复制与移动(2)

复制与移动：

用剪贴板：复制——剪切

用填充柄：只能复制

用拖拽：按Ctrl-------直接拖

-移动之后，公式同样自动修改。如果弓用的区域没有

整个随公式一同移动，则公式中的引用不变；若一同

移动，则引用随着调整。这与引用的相对性和绝对性

无关。

32

合并单元格

■合并之前，选定区域只有一个单元格

（无论哪个）中有数据：

-合并之后保留这个数据。

•合并之前选定区域有多个单元格中有数

据：

-只保留最左上角（有数据的单元格中

在区域中最上行的最左列）的数据。

33

插入、删除和清除

•删除与清除的区别：

-删除：下面或右面的单元格上移或左

移

-清除：使单元格中没有数据

­插入或删除时：

-行或列：直接移动

-单元格或区域：提问

34

定位、查找和替换

•定位：

-用于选定满足条件的单元格

•查找和替换：

二在各单元格中找字符串，替换

-搜索范围：公式，值，批注

•单元格中是常数：公式、值无区别

・单元格中是公式：公式、值不同

-在各单元格中查找格式并替换

35

更正错误

•公式中的查找和替换

-书上P153最下行：“不能在公式和批注文本

中进行替换。・・・・・.”是错的

-正确应为：不能在批注文本和公式的计算结

果中进行替换。”并且应当删去“要编辑公

式，应当在编辑栏中进行；”一句。

36

为单元格区域设置名称

选中单元格区域

单击“插入”1“名称”1“区域”

输入定义的名称

单击“确定”

37

模版的应用

已经拥有基本内容和基本格式的工作簿。

上一张下一张38

创建新模版

•打开excel

•单击“文件”|“新建”

•编辑工作表作为模版

•单击“保存”或“另存为”

•拉开文件类型列表

・选择“模版”xlt

•单击“确定”

上一张下一张39

编辑已存在的模版

单击“文件”1“新建”

单击“本机上的模版”

选择要修改的模版

单击“确定”

编辑，修改模版

单击“另存为”

选择文件类型为模版xlt

单击“确定”

上一张下一张40

模版应用

•单击“文件”1“新建”

•单击“本机上的模版”

・选择要应用的模版

•单击“确定”

•编辑，修改工作簿

•单击“保存”或“另存为”

•输入文件名，文件类型为Xis

•单击“确定”

上一张下一张41

高级编辑与排版

编辑设置

单击“工具”1“选项”1“编辑”

单元格内部直接编辑

单元格拖放功能

自动设置小数点

按ENTER键后移动（方向）

记忆式键入

上一张下一张42

选择性粘贴

单击源单元格

单击“编辑”1“复制”

选中目标单元格

单击“编辑”1“选择性粘贴”

全部

数值

格式

上一张下一张43

行列转置

行变成列，列变成行

•选择原始单元格区域

•单击“编辑”1“复制”

・选定目标区域左上角单元格

•单击“编辑”1“选择性粘贴”

・选中“转置”

•单击“确定”

上一张下一张44

自动填充

适用于批量数据的输入

1.拖动填充控点

填充拖动的单元格内容1,1,1,1...

2.按住CTRL拖动填充控点

以单元格内容作为序列起始值填充1,2,3,4…

上一张下一张45

使用填充菜单

•在A1输入1

•右击填充控点拖动至A10,抬起按键

复制单元格：内容相同，格式相同

以序列方式填充：内容填充序列，格式相同

仅填充格式：内容为空，格式相同

不带格式填充：内容相同，格式默认

上一张下一张46

等差自动填充

•在A1输入1

•在A2输入3

•选中A1至A2

・拖动填充控点1,3,5,7,9

上一张下一张47

复制单元格至同组工作表

将sheetl的A1A2复制给sheet2,sheet3

•在sheetl输入A1A2

•按住Ctrl单击表名sheet2,sheet3

•单击“编辑”填充”「至同组工作表”

•选择“全部”

•单击“确定”

上一张下一张48

使用等差填充对话框

•在A1输入4

•选中A1至A10

•单击“编辑”|“填充”|“序列”

•选等差

•输入步长为2

•单击“确定”

4,6,8,10,...

上一张下一张49

使用等比填充对话框

•在A1输入2

•选中A1至A10

•单击“编辑”|“填充”|“序列”

•选等比

•输入步长为2

•单击“确定”

2,4,8,16,...

上一张下一张50

使用日期填充对话框

•在A1输入2006-08-31

•选中A1至A10

•单击“编辑”|“填充”|“序列”

•选日期

•输入步长为1

•选日期单位为“日”

•单击“确定”

2006-08-31,2006-09-01,...

上一张下一张51

使用默认序列填充

•在A3输入星期一

・拖动填充控点至A10

星期一，星期二，星期三，星期四，星期五,

星期六，星期日，星期一

上一张下一张52

使用自定义序列填充

自定义序列

•单击“工具”1“选项"I”自定义序列”

•输入序列赵，钱，孙，李，周，吴，关B，王

・单击“添加”

使用方法与默认序列一样

上一张下一张53

导入自定义序列

将数据表中的单元格内容作为序列导入。

输入A1:上海，A2:北京,A3:南京,A4:天津,

A5:广州

•单击“工具”「选项自定义列表”

•单击“选择单元格”按钮

•选中A1至A5

•单击“折叠”按钮

•单击“导入”

只可以维护（删除）自定义序列

上一张下一张54

设置工作簿属性

1.统计信息

2.可设置标准文件属性

3.自定义文件属性

打开文件属性

方法一：打开工作簿，单击“文件”|“属性”

方法二：打开资源管理器，单击工作簿文件

名，单击“文件”|“属性”

上一张下一张55

属性选项卡

“常规”选项卡。只能查看不能修改。

“摘要信息”选项卡。用户可以编辑。

“统计信息”选项卡。用户不能修改。

“内容”选项卡。用户不能修改。

“自定义”选项卡。用户可以修改，用于查

询。

上一张下一张56

使用自定义属性

•打开chaozhi.xls工作簿

•单击b4单元格

•单击“插入”|“名称”|“定义”

•输入“超支金额”

•在B4

・单元格输入6

上一张下一张57

定义自定义属性

•打开chaozhi.xls工作簿

•单击“文件”|“属性”

•单击“自定义”卡

•在名称位置输入“超支金额”

•在取值位置输入“超支金额”

・选中”链接到内容”复选框

•单击“添加”

上一张下一张58

使用自定义查询文件

•打开EXCEL

•单击“文件”|“打开”

•单击“工具”「查找”

•在“属性”处输入“超支金额”

•在“条件”处输入“等于”

•在“值”处输入4

•单击“添加”

•单击“前往”

•单击要打开的文件，单击“确先二张下一张59

自动提示用户输入摘要信息

•打开EXCEL

•单击“工具”选项”常规”

•选中“提示输入工作簿摘要信息”复选框

第一次保存时提示输入摘要信息

上一张下一张60

单元格数字格式

•打开EXCEL

・选中单元格

•单击“格式”|“单元格”数字”

单击“数值”，设置小数位、千分位、负数

形式

单击“文本”，长文本数字

上一张下一张61

自定义单元格数字格式

•打开EXCEL

・选中单元格

•单击“格式”|“单元格”|“数字”

单击“自定义”

［红色］(#,###.OO);O.OO「sales”@

上一张下一张62

基本数字格式代码

#:只显示有意义的数字，不显示无意义的零

0：用。填补无意义的零。

上一张下一张63

条件格式

根据条件设置单元格格式

例：考试成绩大于等于60的用红色显示。

考试成绩小于60的用绿色显示。

•选中用于设置条件格式的单元格。

•单击“格式”条件格式”

・选单元格数值，大于等于，60

•单击“格式”选红色

・单击“添加”

・选单元格数值，小于，60

•单击“格式”选绿色

•单击“确定”

上一张下一张64

条件格式

例：销售额最高的用红色显示。

销售额最低的用绿色显示。

•选中销售额单元格a1:a10

•单击“格式条件格式”

•选“单元格数值”

•选“等于”

•$BJA=max($a$1:$a$10)

•定义格式为红色

•单击“添加”

•选“单元格数值”

•选“等于”

・输入=min($a$1:$a$10)

•定义格式为绿色

•单击“确定”

上一张下一张65

删除条件格式

•选中销售额单元格a1:a10

•单击“格式”|“条件格式”

•单击“删除”

•选择要删除的条件

•单击“确定”

上一张下一张66

查找有条件格式的单元格

单击表格单元格

单击“编辑”1“定位”

单击“定位条件”

选择“条件格式”1“全部”

单击“确定”

上一张下一张67

样式

是多种格式的组合（字体、字号、颜色…）

使用一种样式，一次完成多种格式设置。

上一张下一张68

使用内置样式

・选中单元格

•单击“格式”1“样式”

・选“常规”

•单击“确定”

上一张下一张69

使用自定义样式

・选中单元格

•单击“格式”1“样式”

•输入样式名（样式1）

•单击“添加”

•单击“修改”

•单击“确定”

上一张下一张70

修改样式

•单击“格式”1“样式”

•选中样式名

•单击“修改”

上一张下一张71

删除自定义样式

•单击“格式”1“样式”

•选中要删除的样式名

•单击“删除”

上一张下一张72

样式合并

复制yangshi1.xls的样式给yangshi2.xls

・打开yangshi1.xls

•打开yangshi2.xls

•单击“格式”|“样式”

•单击“合并”

・选择样式来源yangshil.xls

•单击“确定”

上一张下一张73

设置工作表背景

只作为显示效果，不能打印

•单击“格式”1“工作表”1“背景”

•指定背景图片文件

•单击“确定”

上一张下一张74

保护单元格

・选定要保护的单元格

•单击“格式”1“单元格”

•单击“保护”

•选中锁定和隐藏（隐藏公式）

上一张下一张75

保护工作表

看不见隐藏的公式

•单击“工具”1“保护"I”保护工作表”（撤销

保护）

•输入密码

•重复输入密码

•单击“确定”

上一张下一张76

保护工作簿

•单击“工具”1“保护”1"保护工作簿”

结构保护：避免删除、插入、重命名工作表

窗口保护：窗口不被移动

•输入密码

•重复输入密码

上一张下一张77

保护工作表(1)

・安全措施：

-殁豆打开密码

-筱玖保花密妈

-建衩。兄篌方式打开

•保护措施：

-缭止修改工作表

-桀止修改工作落

-保护并共享工作落

78

保护工作表(2)

・“工具一＞保护”命令：

-保护工作表——撤漪保新工作表

•保护的工作表上，不能编科锁定单元格

•保护的工作表上，不显示隐藏单元格的公式

-保护工作簿——撤漪保新工作簿

-保护并共享工作容——微演……

・“安全性”选项：

-“文件〃菜单“另存，〃命令的“工具”下拉按钮

----注意，此处与Word是否相同？

79

保护工作表(3)

几种口令的不同作用：

-〃工具”菜单〃保护”命令中口令的作用是外

须正确输入口令才怩撤消保护。

-“工具”菜单〃逡项”命令〃妥今但〃逡项卡

中〃打开权限密鸡”的作用是防止工作落女林

被旅法打开。

-〃修改权限密妈”的作用是防止工作落文件被

修改后用原名保方。

-〃工作表保护“被置的口令森撤漪保萨时使用。

80

设置工作簿打开和修改口令

•单击“工具选项”「安全性”

•设置

•打开权限密码

•修改权限密码

上一张下一张81

上机练习题（2）:

•完成实验某公司销售收入预测表

-按照实验指导上的要求建立工作表

-输入数据

-设置格式（边框等）

-按照要求用公式计算

•练习讲过的其它功能：

-例如，可以在成绩表开头插入一个空行，

合并单元格，在其中输入表名

82

3.Excel的进一步学习

­数据显示的问题

■数据的编辑

•表格数据的统计处理J

•数据管理功能V

数据透视表

模拟运算表和数据分析

83

单元格引用

・相对引用a3,b8复制移动时引用发生改变

•绝对引用$a$4复制移动时引用不发生改变

•混合引用$a4,b$9

•,逗号引用

=sum（a1,a10）

•:冒号引用（范围）

=sum（a1：a10）

•一空格引用（重叠部分）

=sum（a1:a10a8:a18）

上一张下一张84

表格数据的统计处理

•多张工作表的操作

•统计函数

・制作统计图表

85

多张工作表的操作

•引用另一张工作表上的数据

-一般格式：〈工作表名》!〈列标X行号》

-例：sheet3!b5

­引用其他工作簿上的数据

-一般格式：［〈文件名〉］〈工作表名〉!〈列标〉〈

行号〉

-例：［Bookl.xls］sheet3!b5

•工作表的插入、复制、移动和删除

——可以制作单独的统计表

86

工作表的插入、复制、移动和删除

­插入新的工作表

-菜单命令：插入一＞工作表

-右击工作表标签（快捷菜单）：插入…

•复制和移动工作表

-菜单命令：编辑一〉移动或复制工作表…

-右击工作表标签：移动或复制工作表…

•删除工作表

-菜单命令：编辑一＞删除工作表

-右击工作表标签：删除

87

统计函数

•Excel中的函数分类

-9大类，列表另有“常用”和“全部”

财务、日期与时间、数学与三角函数、统计、查找与引用、

数据库、文本、逻辑、信息

•统计函数的作用

-进行统计

-常用的：均值、计数、求和等

•条件计数函数

-COUNTIF（区域,条件）

-返回区域中满足条件的单元格的数目

88

本学期要求掌握的函数

,统计函数

-AVERAGE,COUNT,COUNTIF,MAX,MIN

•数学与三角函数

-SUM,SUMIF,RAND,ROUND,TRUNC

•逻辑函数：IF,AND,OR

•文本函数：

-LEN,LEFT,RIGHT

•查找与引用函数:VLOOKUP

•日期与时间函数：TODAY

89

函数

可以完成特定功能的程序模块

函数使用方式

二函数名(参数表)

=SUM(A1:A10)

上一张下一张90

在编辑栏直接输入函数

=sum（a1：a10）

上一张下一张91

使用粘贴函数输入函数

•单击存放函数的单元格

•单击fx

•选择函数类别

•选择函数名

•指定函数参数

•打开参数

•折叠参数

•单击“确定”

上一张下一张92

函数嵌套

一个函数的参数中使用了另一个函数。

=average(a1,a10)

=average(a1:a10)

=average(max(a1:a10),min(a1:a10))

上一张下一张93

常用函数

逻辑函数

逻辑值:逻辑真TRUE,逻辑假FALSE

1.条件判断函数

=IF（条件检测，为真返回，为假返回）

二if（f2>=60,“通过“，“不通过"）

=if（f2>=90,”优”，if（f2>=60,"及格“，”不及

格”））

上一张下一张94

AND“与”函数

=AND(表达式1,表达式2,表达式3...)

=AND(TRUE,TRUE)=〉TRUE

=AND(TRUE,FALSE)=〉FALSE

=AND(FALSE,TRUE)=〉FALSE

=AND(FALSE,FALSE)=〉FALSE

上一张下一张95

OR“或”函数

=OR(表达式1,表达式2,表达式3...)

=OR(TRUE,TRUE)=)TRUE

=OR(TRUE,FALSE)=>TRUE

=OR(FALSE,TRUE)=)TRUE

=OR(FALSE,FALSE)=〉FALSE

上一张下一张96

NOT年”函数

=NOT(表达式)

=NOT(TRUE)=〉FALSE

=NOT(FALSE)=)TRUE

上一张下一张97

数学函数无条件求和函数sum

格式：统计汇总值

=SUM(引用1,引用2,…)

=sum(3,5,8)=>16

=sum(a1:a5)

引用名称：为一组单元格指定名称

单击“插入”名称”「定义”

输入名称为“销售额总计”

输入引用位置=sheetl!b2:e5

单击“添加”

在f6输入二sum(销售额总计)

上一张下一张98

条件求和函数sumif

格式：跟据条件统计汇总值

=sumif（条件区域，求和条件，求和区域）

ABCDE

1姓名性别英语数学平均分

2刘华男8993

3王华女9187

4赵华男8791

计算男生英语成绩合计值

=sumif（b2:b4,"男"，c2:c4）=>176

上一张下一张99

条件计数countif

格式：根据条件统计单元格个数

=countif(计数单元格区域，计数条件)

ABCDE

1姓名性别英语数学平均分

2刘华男8993

3王华女9187

4赵华男8791

=countif(b2:b4,“男“)=>2

上一张下一张100

舍入函数ROUND

格式：四舍五入函数

=round（数值，舍入位数）

舍入位数＞0小数部分舍入

舍入位数=0取整

舍入位数＜0整数部分舍入

上一张下一张101

向下舍入rounddown

格式：

=rounddown(数值，舍入位数)

=rounddown(3.16159,1)=>3.1

=rounddown(3.5689,1)=>3.5

上一张下一张102

向上舍入roundup

格式：

=roundup(数值，舍入位数)

=roundup(3.14159,1)=>3.2

=roundup(3.5689,1)=>3.6

上一张下一张103

平均值函数Average

格式：求平均值

=average(引用1,引用2,…)

=average(a1:a5)

将a1：a5定义名称scores

=average(scores)

注意：0与空的区别

上一张下一张104

计数函数count

格式：统计数值型单元格个数

=count(弓I用)

=count(a1:a10)

上一张下一张105

计数函数counta

格式：统计非空单元格个数

=counta（引用1,引用2,...）

上一张下一张106

空白单元格计数countblank

格式：

=countblank（引用1,引用2,…）

上一张下一张107

最大数值max

格式：计算数值参数中的最大值

=max(引用1,引用2,…)

=max(2,7,4)=>7

上一张下一张108

最小数值min

格式：计算数值参数中的最小值

=min(引用1,引用2,…)

=min(2,7,4)=>2

上一张下一张109

最大值maxa

格式：最大值，文本、逻辑之参加运算

=maxa（引用1,引用2,…）

True为1,False为0

上一张下一张110

最小值mina

格式：最小值，文本、逻辑之参加运算

=mina（引用1,引用2,…）

True为1,False为0

上一张下一张111

返回第k个最小值Small

格式：返回第k个最小值

=small(引用区域，序号)

a1：a10,3,4,5,2,3,4,5,6,4,7

k=4

=small(a1:a10,4)=>4

=small(a1:a10,9)=>6

上一张下一张112

返回第k个最大值large

格式：返回第k个最大值

=large(引用区域，序号)

a1：a10,3,4,5,2,3,4,5,6,4,7

k=4

=large(a1:a10,4)=>5

=large(a1:a10,9)=>3

上一张下一张113

RANK返回数组中排序位置

格式：返回数组中排序位置

=rank(数值，数组引用，排序方式)

排序方式：0或缺省降序，非0为升序

A1:a103,4,523,4,5,6,4,7

=rank(4,a1:a10,1)=>4

114

文本函数char

格式：返回ASCII码字符。

=char(ascii码)

=char(65)=>A

=char(33)=>!

上一张下一张115

转小写函数Lower

格式：将西文字符转成小写

=lower(西文字符文本)

=lower(IAMATeacher)=>iamateacher

上一张下一张116

转大写函数upper

格式：将西文字符转成大写

=upper(西文字符文本)

=upper(Teacher)=>TEACHER

上一张下一张117

截取左端字符串left

格式：从左端截取若干字符串

=left(字符串文本，截取字符个数)

=left("teacher^4)=>teac

上一张下一张118

截取右端字符串right

格式：从右端截取若干字符串

:right(字符串文本，截取字符个数)

=right("teacher"，4)=>cher

上一张下一张119

从指定位置截取字符串mid

格式：从指定位置截取n个字符串

=mid(字符串，开始位置，截取长度)

=mid("teacher"，3,2)=>ac

上一张下一张120

计算字符串长度len

格式：计算字符串的字符个数

=len（字符串文本）

=len（“中国"）=>2

上一张下一张121

词首转大写字符proper

格式：将词首字符转大写

=proper(文本字符)

=proper("iamateacher^)=>lAmA

Teacher

上一张下一张122

字符串替换函数replace

格式：用字符替换字符串

=replace(文本串，开始，字符数，替换文本)

=replace("abcdefg"，3,2,)=>5,ab*efg55

上一张下一张123

重复显示字符串函数rept

格式：重复显示字符串

=rept(文本串，重复次数)

=rept(3)=

上一张下一张124

查找字符串函数search

格式：在字符串中查找另一个字符串的位置

=search(查找字符串，被查找字符串，开始)

=search(uteacherJ\ulamateacher"，1)=>8

Search函数不区分大小写

上一张下一张125

查找字符串函数find

格式：在字符串中查找另一个字符串的位置

=find(查找字符串，被查找字符串，开始)

=find("teacher","Iamateacher”,1)

=>8

find函数区分大小写

上一张下一张126

文本替换函数substitute

格式：在一个文本串中用一个字符串替换文本串中

的另一个字符串

二substitute(文本串，被替换字符，替换字符,次数)

=substitute(ttmay1,1991TT,”2",1)=>may2,1991

=substitute(“may1,1991”,“1“,“2",3)=>may1,1992

上一张下一张127

将数值转换为指定格式文本text

格式：将数值转换为指定格式文本text

:text(数值引用，文本格式)

:text(12.78,”$0.00")=>$12.78

=text(“1991/09/12"，“mmmmdd,yyyy”)

=>September121991

上一张下一张128

删除多余的空格trim

格式：压缩文本字符串中的空格，词与词之间

只保留一个空格

=trim(文本串)

=trim("lamateacher")=>lama

teacher

上一张下一张129

安列查找函数VLOOKUP()

格式：在表格中查找指定的值，并返0不及格

回表格中指定列的值。

60及格

=vlookup(查找值，查找表格，返回指定列)

70中

例：=vlookup(78,$b$10:$c$15,2,true)

80良

90优

100优

130

制作统计图表（1）

•利用图表向导生成（常规方法）

-第一步：选择图表类型

-第二步：确定数据源（区域和系列）

-第三步：设置图表选项

-第四步：指定图表位置

131

制作统计图表（2）

•快速创建图表

-使用默认图表按钮,为对象）

——默认图表按钮要自己添加

-击键盘F11键（作为图表工作表）

----此时图表的类型、选项等是按照默认图表

的设置确定的

132

统计图表类型的选择和更改

•图表类型：

-柱形图：簇状、堆积、百分比堆积、三维等

一条形图：簇状、堆积、百分比堆积、三维等

-折线图：折线、堆积折线、百分比堆积、三维等

-饼图：普通、三维、复合、分离等

-XY散点图：数据点、平滑线、折线、无数据点等

・选定图表，用“图表”菜单的“图表类型”命令。

133

数据管理功能

•数据排序

•数据筛选

・记录单的操作

・工作表的查看和打印

•工作表的保护

134

数据排序

•数据清单

一行——记录（最上方第一行为标题）

一列一属性

----数据库的结构保怩嘴左行左列，

・排序命令

-数据区——清单（选定其中一个单元格即可）

-关键字——最多为三个

-序：升序和降序

135

数据筛选

•自动筛选:单击“数据”1“筛选”1“自动筛选”

一可，各列今别播足杂埼他

一可自定义票列位满足的泰件

——有些特殊的筛选要求不能实现

•高级筛选:单击“数据”「筛选”「高级筛选”

一需猾定微据区域和条件区域

一可将给累复制到其他假置

一泰件区域可微置复盖的条件

136

记录单命令

•可以完成：

一和录的新建

一祀录的删除

一祀隶的修改

一忆录的全找

137

工作表的查看和打印

查看工作表•打印工作表

一拆令窗口—范（8:

一潦精拆今选定区域

•用途：将标题当前工作表

冻结选定的多张工作表

-撤漪潦秸或撤漪整个工作簿

先选定，再打印

•用相应的命令

138

工作表的选项

•选项

-视图选项卡

-颜色选项卡

-自定义序列选项卡

-常规选项卡

-其它选项卡

139

上机练习题（3）:

•完成实验商店销售统计日报表

-下载实验题目的附件

-按照题目要求用公式计算

•练习讲过的其它功能：

-可以对成绩表按总成绩排序

-可以筛选出总成绩达到90分的学生

-可以筛选出考试成绩的前5名

-可以用高级筛选选出考试成绩或总成绩不及格的学

生

140

数据透视表

•什么是数据透视表

-对大量数据进行快速汇总的交互式表格

•创建数据透视表

•编辑数据透视表

•数据透视表的操作

・用数据透视表进行汇总计算

•数据透视图

141

创建数据透视表

•从数据清单（存放原始数据）开始

•数据透视表向导——三步

-数据源和报表的类型

-数据源区域

■报表核置

•拖动字段构建数据透视表版式

一页（最上方）♦:♦行（左侧）

-列（上方J数据项（右下方）

142

编辑数据透视表

•修改版式布局

-拖动字段更改板式

-数据透视表选项中的页面布局

•添加和删除记录

一打开字段的下拉按钮，选定或取消各项前面的复选

框

•设置选项

-向导的第三步——选项按钮

-数据透视表下拉菜单----表选项

——具体自己看书，大玫了斛即可

143

数据透视表的操作

・刷新述据透视表

-数据源改变后，更新报表

・对数据透视表排序

・改变数据透视表的汇总方式

-在数据项的字段设置中选择汇总方式

144

对数据透视表排序

•目的：行、列标题按一定顺序排列

•操作：先选定标题，用排序命令

-选定标题：作为排序的关键字

-挑序命令：即数据菜单的排序命令

-对话框：只有一个关键字（排序依据），

即选定的标题

•排序选项：按字母或按笔画

-普通排序也可以使用

145

川数据透视表进行汇总计算

・显示或隐藏各项总计

数据透视表----表选项-----格式选项

•修改汇总方式

双击汇总方式字段

146

数据透视表

从不同的角度显示、分析数据

编辑一张数据清单

时间，分支机构，产品名称，销售人员,销售额

第一季北京分公司沙发张华24000

第一季深圳分公司办公桌刘华32000

上一张下一张147

创建数据透视表

•单击数据表格

•单击“数据”1“数据透视表和数据透视图”

•选中MicrosoftOfficeExcel数据列表

・选中数据透视表

•单击“下一步”

・选中新建工作表

•单击“完成”

•拖动“时间”到页字段

•拖动“产品名称”到列字段

•拖动“分支机构”销售人员”到行字段

•拖动“销售额”到数据项

上一张下一张148

编辑数据透视表

改变行列字段

拖动字段到新的行列位置

删除页、行、列字段

拖动字段名到透视表外

增加页字段

拖动字段到页字段位置

上一张下一张149

编辑数据透视表

改变数据字段顺序

•单击数据字段名

・拖动数据字段名到新的位置

•或者右击字段名

•单击“顺序”

•单击“右移”

上一张下一张150

编辑数据透视表

增加页字段

•拖动“分支机构”字段到页字段位置

上一张下一张151

编辑数据透视表

页字段排列

•单击”数据透视表”工具栏1“数据透视表”

•单击“表选项”

•拉开“页面布局”

•选择“垂直排列”或“水平排列”

上一张下一张152