
本书主要介绍Excel公式、函数、图表和数据分析的功能与应用。主要内容包括:Excel基础知识、公式与函数的基础知识、文本与逻辑函数、日期与时间函数、数学与三角函数、财务函数、统计函数、查找与引用函数、数据库与信息函数、通过加载宏自定义函数、图表的基础知识、图表的应用与制作技巧、数据透视图表与数据分析等知识。
本书语言浅显易懂,概念和功能的介绍形象、生动,在讲解过程中还采用情景式任务驱动方式引导读者学习,并配以清晰、简洁的图文排版方式和丰富的小栏目,使学习过程变得更加轻松、易上手。同时每章最后配有常见问题解答、上机实战和练习题,用于帮助读者解决学习中遇到的难题和巩固所学知识。
本书可作为有一定基础的Excel操作知识的读者学习Excel的公式、函数、图表与数据分析的参考用书,适用于学生、办公人员、务工人员、电脑培训班和对Excel感兴趣的广大读者使用。
学知识不分先后,玩电脑老少皆宜
快乐电脑一点通,将学习进行到底
亲爱的读者朋友们:
欢迎大家来到“快乐电脑一点通”体验学电脑的快乐、轻松、好玩,在这里,无论你是老人、小孩、自由职业者,还是办公室人员,都可以找到自己的学习需求。因此,我们推出本丛书的目标是让所有想学电脑的朋友都能达到用电脑生活、娱乐、工作的目的。
本丛书有以下6大特点:
? 读者定位准确,主要定位于没有任何相关基础知识的入门者。
? 以“快乐”为主题,版式轻松、时尚,情景式导读和人物对话生动有趣、寓教于乐,让人心领神会。
? 在写作思路上通过举例子、打比方等手法解决“是什么”、“为什么”、“怎么办”的问题,从而体现了容易学、学得快的特点。
? 操作图解,配有丰富的小栏目,拓展知识面并提高知识含量。
? “欢欢”、“爸爸”,这两个人将全程陪同在你的身边,和你一起学电脑,帮你解答疑难。
? 书盘结合,精美的双色印刷。
下面就让我们走进《Excel公式、函数、图表与数据分析》。
本书的内容及特点
Excel是一个功能强大的电子表格制作和数据处理软件。利用它的公式、函数、图表与数据分析功能可处理和分析不同形式的数据,为我们在办公中处理一串串繁琐的数据带来了便捷。
本书将结合实际的工作需要,从Excel的基本操作知识出发,由浅到深、由简到难,循序渐进地讲解Excel公式、函数、图表与数据分析的基本知识与实际操作,使读者熟练掌握Excel公式、函数、图表与数据分析的应用。
本书分为4大部分,共14章,主要内容介绍如下:
? 第1章:介绍Excel的工作簿、工作表与单元格的基本操作,输入与美化数据以及保护表格数据等基础知识。
? 第2、3、4、5、6、7、8、9、10章:介绍公式与函数的基本操作、各类函数的应用及通过加载宏定义相关函数等知识。
? 第11、12、13章:介绍图表的基础知识、不同图表类型的应用与操作技巧及数据透视图表的应用等知识。
? 第14章:介绍数据分析的各类工具等知识。
本书能帮你实现的愿望
本书可以让你认识Excel,了解在Excel中处理数据需使用的工具,掌握公式、函数、图表与数据分析的操作,让你的Excel不再只是制作表格和存储数据的工具;本书可以让你实现快速计算数据,如用公式和函数进行相关的计算等;本书还可以让表格中的数据更直观,如用柱形图、条形图、折线图、饼图及数据透视图表等直观表现表格中的数据;本书可以让你对复杂的数据进行各种分析,从而提高工作效率,如用方差分析、相关系数、协方差、回归分析等。
本书与你的约定
本书每章的写作模式为“导读+正文讲解+上机实战+常见问题解答+练习题”,各部分的使用约定如下:
1.导读。包括情景式导读和内容导读,主要介绍本章的内容。
2.正文讲解。以人物的情景式对话引出一个知识点,再通过举例、配图、比喻来解释这个知识点是什么,用于做什么,接着以图解步骤方式讲解该知识点的具体用法,在讲解过程中再配以“提示”、“注意”、“技巧”和“故事村”小栏目告诉你遇到问题怎么办。
3.上机实战。每章知识讲解完后列举一个综合性的例子,贯穿本章的重要知识点。
4.常见问题解答。以一问一答的形式列出读者在学习本章内容过程中及实际应用中可能会遇到的问题、技巧及相关知识,以帮助新手快速掌握。
5.练习题。主要为上机操作题,对本章内容进行巩固和深化。
其他的语言描述约定有:快捷键用【 】,如【Ctrl+C】键;提示性标注或一些操作提示采用圆角白底方框;操作步骤序号以1、2…表示。
关于我们
本书的作者均已从事电脑基础教育及相关工作多年,拥有丰富的教学经验和实践经验。参与本书编写的人员有:青贤、周长春、任朝东、蒲勇、苟建生、赵旻、蔡文学、王永兵、刘辉、胡森、孙伟、易然、陈强、李锐、徐彬耀、胡慧慧、张印、王慧君、刘智甫、杨富育、马白鹭、秦贵芝、罗传林、姚琳玲、麻建星、李艳兰、唐敏、何其春、赵冠龙、蒲青云等。
由于作者水平有限,书中疏漏和不足之处在所难免,恳请广大读者及专家不吝赐教。若您在阅读过程中遇到困难或问题,可以登录我们的网站http://www.pcbookbbs.com留言,我们力求在24小时内回复(节假日除外)。
本书编委会
2007年7月
第1章 Excel基础知识
1.1 工作簿、工作表与单元格 2
1.1.1 认识工作簿、工作表与
单元格 2
1.什么是工作簿、工作表与单元格 2
2.工作簿、工作表与单元格的关系 2
1.1.2 工作簿的基本操作 3
1.新建工作簿 3
2.保存工作簿 3
3.关闭工作簿 4
4.打开工作簿 5
1.1.3 工作表的基本操作 5
1.选定工作表 5
2.插入工作表 6
3.重命名工作表 7
4.移动与复制工作表 7
5.删除工作表 8
1.1.4 单元格的基本操作 9
1.选中单元格 9
2.合并单元格 10
3.插入与删除单元格 10
4.调整单元格行高与列宽 11
1.2 输入与美化数据 11
1.2.1 输入数据 11
1.数据的类型 11
2.输入文本 12
3.输入数字 13
4.输入特殊符号 13
1.2.2 美化数据 14
1.设置数字格式 14
2.设置对齐格式 15
3.设置字体格式 16
4.设置单元格边框与底纹 17
5.设置工作表背景 18
6.自动套用格式 19
1.3 保护表格数据 20
1.3.1 保护单元格 20
1.3.2 保护工作表 21
1.3.3 保护工作簿 21
1.4 上机实战 22
1.5 常见问题解答 25
1.6 练习题 26
第2章 公式与函数的基础知识
2.1 用公式计算数据 28
2.1.1 认识公式 28
1.什么是公式 28
2.公式常用运算符 28
2.1.2 输入与编辑公式 30
1.输入公式 30
2.编辑公式 31
2.2 用函数计算数据 33
2.2.1 认识函数 33
1.函数的定义与结构 33
2.函数的参数 33
3.函数分类 34
2.2.2 输入与编辑函数 35
1.输入函数 35
2.编辑函数 36
2.3 单元格的引用 37
2.3.1 相对引用 38
2.3.2 绝对引用 38
2.3.3 混合引用 38
2.4 定义与使用名称 39
2.4.1 定义单元格名称 39
2.4.2 使用单元格名称 40
2.5 上机实战 41
2.5.1 利用公式计算工资 41
2.5.2 利用函数计算个人所得税 42
2.6 常见问题解答 44
2.7 练习题 44
第3章 文本与逻辑函数
3.1 文本函数的使用 46
3.1.1 文本与字符串函数 46
1.单字节与双字节字符的相互转换:
ASC与WIDECHAR 46
2.数值与文本的转换:
BAHTTEXT、TEXT、DOLLAR与RMB 46
3.删除不能打印的字符:CLEAN 48
4.返回文本字符串的数字代码:
CODE 48
5.合并文本字符串:
CONCATENATE 49
6.两个字符串的比较:EXACT 49
7.搜索文字串在另一个文字串中的
起始位置:FIND与FINDB 49
8.用小数格式舍入成特定位数
并返回文本格式:FIXED 50
9.返回字符串左右两侧指定的字符:
LEFTB与RIGHTB 50
10.返回包含的字符数:LENB 51
11.文本字符串中大、小写的转换:
LOWER、UPPER与PROPER 51
12.提取指定长度的字符串:MIDB 52
13.根据指定次数重复文本:REPT 52
14.检测给定值是否为文本:T 53
3.1.2 高级文本函数 54
1.返回指定的字符:CHAR 54
2.提取字符串的第一个或最后一
个字符:LEFT与RIGHT 54
3.返回文本字符串中的字符
个数:LEN 55
4.返回指定长度的字符:MID 55
5.替换字符串: REPLACE与
REPLACEB 55
6.替换部分字符串:SUBSTITUTE 56
7.查找指定字符或文本字符串:
SEARCH 57
8.删除字符串的空格:TRIM 57
9.将文本字符串转换为数值:
VALUE 58
3.2 逻辑函数的使用 58
3.2.1 判断逻辑真假值 58
1.返回逻辑真值:TRUE 58
2.返回逻辑假值:FALSE 59
3.2.2 判断逻辑结果:IF 59
3.2.3 计算逻辑交集与并集 60
1.返回逻辑并集的结果:AND 60
2.返回逻辑交集的结果:OR 61
3.2.4 对参数的逻辑值求反:
NOT 61
3.3 上机实战 61
3.4 常见问题解答 65
3.5 练习题 65
第4章 日期与时间函数
4.1 日期函数 68
4.1.1 了解日期系统 68
4.1.2 日期函数的使用 70
1.返回特定日期的系列数:DATE 70
2.将日期值从字符串转化为序列数:
DATEVALUE 71
3.返回日期的天数:
DAY、DAYS360与WEEKDAY 72
4.返回日期的月份与年份值:
MONTH与YEAR 73
5.返回日期格式的当前日期:
TODAY 74
4.2 时间函数的使用 75
4.2.1 返回时间值 75
1.返回小时数值:HOUR 75
2.返回分钟数值:MINUTE 75
3.返回秒数值:SECOND 76
4.返回特定时间的小数值:TIME 76
5.返回日期时间格式的当前日期和
时间:NOW 77
4.2.2 将文本格式的时间转换成时间格式的序列数:
TIMEVALUE 77
4.3 上机实战 78
4.4 常见问题解答 80
4.5 练习题 81
第5章 数学与三角函数
5.1 数学函数的使用 84
5.1.1 基本数学函数 84
1.求绝对值:ABS 84
2.返回指定元素数目的组合数:
COMBIN 84
3.求幂方值:EXP 85
4.求阶乘:FACT 85
5.计算对数值:LN、LOG与
LOG10 85
6.计算矩阵行列式的值:
MDETERM 86
7.计算矩阵的逆:MINVERSE 86
8.计算矩阵乘积:MMULT 87
9.求两数的余数:MOD 87
10.求圆周率的值:PI 88
11.求某数的乘幂:POWER 88
12.计算所有参数的乘积:
PRODUCT 89
13.返回随机数:RAND 89
14.将阿拉伯数字转换成
文本式罗马数字:ROMAN 89
15.返回数字的正负号:SIGN 90
16.求数值的平方根:SQRT 90
5.1.2 舍入数学函数 91
1.向上舍入为最接近的整数
或指定基数的倍数:CEILING 91
2.将数值向下舍入取整:INT 91
3.取整函数:TRUNC 92
4.将数值舍入为奇或偶数:ODD与
EVEN 92
5.按数值向下舍入:FLOOR 92
6.按指定位数四舍五入:ROUND 93
7.向上或向下舍入数字:
ROUNDUP与ROUNDDOWN 93
5.1.3 求和数学函数 94
1.分类汇总计算:SUBTOTAL 94
2.对数值求和:SUM 95
3.按条件求和:SUMIF 97
4.对应元素的乘积之和:
SUMPRODUCT 97
5.求平方和:SUMSQ 97
6.求数值之差的平方和或平方差与和之和:SUMXMY2、SUMX2MY2与
SUMX2PY2 98
5.2 三角函数的使用 99
5.2.1 求弧度与角度值 99
1.将弧度转换为角度:DEGREES 99
2.返回弧度值:RADIANS 99
5.2.2 求余弦与反余弦值 100
1.求余弦值:COS 100
2.求反余弦值:ACOS 100
5.2.3 求正弦与反正弦值 101
1.求正弦值:SIN 101
2.求反正弦值:ASIN 101
5.2.4 求正切与反正切值 101
1.求正切值:TAN 101
2.求反正切值:ATAN与ATAN2 102
5.3 上机实战 102
5.4 常见问题解答 105
5.5 练习题 106
第6章 财务函数
6.1 折旧与投资函数的使用 108
6.1.1 折旧函数 108
1.用固定余额递减法计算折旧值:
DB 108
2.用双倍余额递减法计算折旧值:
DDB 109
3.返回固定资产的每期线性折旧费:
SLN 109
4.返回按年限总和折旧法计算的
每期折旧金额:SYD 110
5.返回指定期间的资产折旧额:VDB 110
6.1.2 投资预算函数 111
1.返回某项投资的未来值:FV 111
2.返回某项投资的偿还额总值:
PV 112
3.返回一项投资的净现值:NPV 113
4.返回某项投资或贷款的期数:
NPER 113
5.返回投资或贷款的每期实际利率:
RATE 114
6.2 本金、利息与报酬率
计算函数 114
6.2.1 本金与利息计算函数 114
1.计算货款的等额分期偿还额:
PMT 114
2.返回给定期次内某项投资回报
的本金部分:PPMT 115
3.返回利息偿还额:IPMT 116
4.返回普通贷款的利息偿还:
ISPMT 117
6.2.2 报酬率计算函数 117
1.返回一系列现金流的内部报酬率:
IRR 117
2.返回分期现金流的内部报酬率:
MIRR 118
6.3 上机实战 119
6.4 常见问题解答 123
6.5 练习题 124
第7章 统计函数
7.1 常用的统计函数 126
7.1.1 单元格数量统计函数 126
1.统计单元格个数:COUNT与
COUNTA 126
2.计算空白单元格的数目:
COUNTBLANK 126
3.按给定条件统计单元格的数目:
COUNTIF 127
7.1.2 数组统计函数 127
1.返回参数的平均值:
AVERAGE与AVERAGEA 128
2.返回数据组中第k个最值:
LARGE与SMALL 128
3.返回最大值:MAX与MAXA 128
4.返回最小值:MIN与MINA 129
5.返回一组数的中值:MEDIAN 129
6.返回一组数据或数据区域中的众数:
MODE 130
7.返回指数的一组纵坐标值:
GROWTH 131
8.以一列垂直数组返回数据的
频率分布:FREQUENCY 132
9.返回一组数据的四分位点:
QUARTILE 133
10.返回某数字在列表中的排位:
RANK 133
7.2 数理统计函数 135
7.2.1 计算置信区间与相关系数
统计函数 135
1.返回总体平均值的置信区间:
CONFIDENCE 135
2.返回两组数值的相关系数:
CORREL 135
7.2.2 F概率分布统计函数 136
1.返回两组数据的F概率分布:
FDIST 136
2.返回F概率分布的逆函数值:
FINV 137
3.返回F检验的结果:FTEST 137
7.2.3 正态分布统计函数 137
1.返回正态分布函数值:
NORMDIST 137
2.返回标准正态分布函数值:
NORMSDIST 138
7.2.4 泊松分布与样本统计函数 138
1.返回泊松分布:POISSON 138
2.估算基于给定样本的标准偏差:
STDEV 139
3.估算基于给定样本的方差:
VAR 140
7.3 上机实战 140
7.4 常见问题解答 145
7.5 练习题 145
第8章 查找与引用函数
8.1 查找函数的使用 148
8.1.1 常用查找函数 148
1.查找数值:LOOKUP 148
2.在首行查找指定的数值:
HLOOKUP 151
3.垂直查找:VLOOKUP 151
4.在数组中查找值的相对位置:
MATCH 152
8.1.2 查找函数的高级应用 153
1.选出参数串中相应值或操作:
CHOOSE 153
2.提取存储在数据透视表中的数据:
GETPIVOTDATA 154
3.转置单元格区域:TRANSPOSE 154
8.2 引用函数 155
8.2.1 引用单元格地址的函数 155
1.显示引用地址:ADDRESS 155
2.返回引用中的区域个数:
AREAS 156
3.返回某引用的行号或列标:
ROW与COLUMN 156
4.返回某引用的行数或列数:
ROWS与COLUMNS 156
5.返回指定单元格的值或引用:
INDEX 157
8.2.2 特殊的引用函数 157
1.返回文本字符串指定的引用:
INDIRECT 157
2.通过给定偏移量返回新的引用:
OFFSET 158
3.创建快捷方式或超级链接:
HYPERLINK 158
8.3 上机实战 159
8.4 常见问题解答 163
8.5 练习题 164
第9章 数据库与信息函数
9.1 数据库函数 166
9.1.1 数据库与数据库函数基础 166
9.1.2 数据库函数的使用 167
1.计算给定列表中数值的平均值:
DAVERAGE 167
2.计算给定条件的数值单元格
与非空单元格数目:DCOUNT 168
3.提取符合指定条件的唯一记录:
DGET 168
4.返回给定条件数据的最值:
DMAX与DMIN 169
5.满足指定条件的值的乘积:
DPRODUCT 169
6.估算标准偏差:DSTDEV与
DSTDEVP 170
7.计算满足给定条件的数据和:
DSUM 170
8.估算数据的方差:DVAR与
DVARP 171
9.2 信息函数的使用 171
9.2.1 返回信息的相关函数 171
1.返回单元格的有关信息:CELL 171
2.返回与错误值对应的数字:
ERROR.TYPE 172
3.返回当前操作环境的有关信息:
INFO 173
4.将不是数值形式的值转化为
数值形式:N 174
5.返回错误值#N/A:NA 174
6.返回参数的数据类型:TYPE 174
9.2.2 IS类函数 175
1.检查是否引用了空单元格:
ISBLANK 175
2.检测错误值:ISERR与
ISERROR 176
3.检测一个值是否为逻辑值:
ISLOGICAL 176
4.检测一个值是否为#N/A:ISNA 176
5.检测一个值是否不是文本:
ISNONTEXT 177
6.检测一个值是否是数值、引用或文本:
ISNUMBER、ISREF与ISTEXT 178
9.3 上机实战 178
9.4 常见问题解答 181
9.5 练习题 181
第10章 通过加载宏自定义函数
10.1 了解宏 184
10.1.1 什么是宏 184
10.1.2 创建宏 184
10.2 加载宏 186
10.2.1 加载宏的方法 186
10.2.2 Excel中包含的加载
宏程序 187
10.3 加载的自定义函数 188
10.3.1 证券函数 188
1.返回有价证券的应计利息:
ACCRINT与ACCRINTM 188
2.返回结算日前后的上一个或下
一个债券日期:COUPPCD
与COUPNCD 189
3.返回有价证券的贴现率:DISC 190
4.返回按周期付息的有价证券
的年周期:DURATION 190
5.返回有价证券的Macauley修正
周期:PRICE 191
6.返回有价证券的收益率:
YIELD 191
10.3.2 工程函数 192
1.贝塞尔(Bessel)函数 192
2.在不同的数字系统间进行数值
转换的函数 193
3.用于筛选数据的函数:
DELTA与GESTEP 193
4.度量衡转换函数:CONVERT 194
5.与积分运算有关的函数:ERF与
ERFC 195
6.与复数运算有关的函数 196
10.4 上机实战 196
10.5 常见问题解答 199
10.6 练习题 200
第11章 图表的基础知识
11.1 认识图表 202
11.1.1 图表的定义 202
11.1.2 创建图表的方法 202
11.2 编辑与美化图表 204
11.2.1 编辑图表 204
1.设置图表位置 205
2.设置图表大小 205
3.更改图表类型 206
4.修改图表数据 207
11.2.2 美化图表 208
1.设置图表区格式 208
2.设置绘图区格式 210
3.设置图例格式 211
4.设置网格线格式 212
5.设置数据系列格式 213
6.设置坐标轴格式 214
7.设置图表标题格式 215
11.3 上机实战 216
11.4 常见问题解答 219
11.5 练习题 220
第12章 图表的应用与制作技巧
12.1 不同类型图表的应用 222
12.1.1 柱形图与条形图 222
1. 什么是柱形图与条形图 222
2. 柱形图与条形图的应用 223
12.1.2 折线图 225
1.什么是折线图 225
2.折线图的应用 226
12.1.3 饼图 229
1.什么是饼图 229
2.饼图的应用 229
12.1.4 其他图表 230
1.XY散点图的应用 230
2.面积图与圆环图的应用 231
3.雷达图与曲面图的应用 231
4.气泡图与股价图的应用 232
5.圆柱图、圆锥图和棱锥图的
应用 232
12.2 图表的制作技巧 233
12.2.1 创建下拉列表框的图表 233
12.2.2 创建滚动条的图表 235
12.2.3 用图片表示系列数据大小 237
12.2.4 为图表创建快照 238
12.3 上机实战 240
12.4 常见问题解答 242
12.5 练习题 242
第13章 数据透视图表
13.1 认识数据透视图表 244
13.1.1 数据透视图表的定义 244
13.1.2 数据透视图与表的关系 244
13.2 数据透视图表的应用 245
13.2.1 数据透视表 245
1.创建数据透视表 245
2.使用数据透视表 247
3.设置数据的汇总方式 248
4.设置数据透视表格式 248
5.显示或隐藏明细数据 249
13.2.2 数据透视图 250
1.创建数据透视图 250
2.使用数据透视图 252
13.3 上机实战 253
13.4 常见问题解答 257
13.5 练习题 257
第14章 数据分析
14.1 认识数据分析 260
14.1.1 什么是数据分析 260
14.1.2 使用数据分析的方法 260
14.2 数据分析的应用 261
14.2.1 常用分析工具 261
1.方差分析 261
2.相关系数 262
3.协方差 263
4.回归分析 264
5.抽样分析 266
14.2.2 其他分析工具 267
1.指数平滑 267
2.F-检验双样本方差 268
3.直方图 268
4.移动平均 268
5.t-检验 269
6.z-检验 270
7.随机数发生器 270
8.描述统计 270
9.傅里叶分析 270
10.排位与百分比排位 271
14.3 上机实战 271
14.4 常见问题解答 273
14.5 练习题 274