Excel数据处理与分析PDF电子书下载

第1篇 基本处理篇 1

1　数据的输入 3

1.1　案例1：学生成绩登记表 3

1.1.1　文本的输入 5

1.1.2 日期及时间的输入 6

1.1.3　数值的输入 7

1.1.4　公式的输入 8

1.1.5　有效性数据的输入 9

1.2　技巧篇 12

1.2.1　有规律的数据的输入 12

1.2.2　填充数据 14

1.2.3　自定义填充序列 15

1.2.4　冻结和拆分窗格 19

1.2.5　数据记录单 20

1.3　提高篇 22

1.3.1 同时在多个单元格中输入相同的数据 23

1.3.2　同时在多张工作表中输入或编辑相同的数据 23

1.3.3　模板的建立和使用 26

2　数据的格式化 31

2.1　案例2：职工工资表 31

2.1.1　文本的格式设置 32

2.1.2　数字的格式设置 33

2.1.3 日期的格式设置 36

2.1.4　工作表的美化 37

2.2.1　将数据行列对调 41

2.2　技巧篇 41

2.2.2　隐藏单元格中的零值 42

2.3　提高篇 44

2.3.1 自定义格式 44

2.3.2　宏在格式整理中的初步体验 51

3　数据的排序和筛选 57

3.1　案例3：学生成绩名次排名 57

3.1.1　按“总分”进行简单排序 57

3.1.2　多关键字排序 60

3.1.3 自定义排序 61

3.2　在数据表中筛选出需要的数据 63

3.2.1 自动筛选 63

3.2.2 自定义筛选 64

3.3　提高篇：复杂条件的数据筛选 66

3.3.1　数据同时满足多个条件 66

3.3.2　数据只要满足其中的一个条件 69

3.3.3 自定义条件的筛选 70

4　数据的分类与汇总 75

4.1　案例4：各学科成绩分类汇总 75

4.1.1 简单的分类汇总 76

4.1.2　高级分类汇总 79

4.1.3　嵌套分类汇总 80

4.2　技巧篇 82

4.2.1　分页显示分类汇总 83

5.1.1　数据透视表 87

5　数据透视表 87

5.1　案例5：某电器城业务人员全年销售业绩统计 87

5.1.2　查看数据 93

5.1.3　修改数据透视表 95

5.1.4　数据透视图 100

5.2　技巧篇 102

5.2.1　数据透视表的编辑 102

5.2.2　数据透视表的行列置换 102

第2篇 函数和图表篇 105

6　公式和函数的学习方法 107

6.1　案例6：通过Excel帮助来使用函数 108

6.2　错误信息对我们的提示 113

6.3　技巧篇 115

6.3.1　输入数据的保护 115

6.3.2　相对引用、绝对引用和混合引用间的快速切换 116

6.3.3　按步骤计算嵌套公式 118

6.4　提高篇 119

6.4.1　数据的审核和跟踪 120

7　逻辑函数 123

7.1　案例7：逻辑函数的应用——优秀业务员评选 123

7.2　逻辑函数介绍 124

7.2.1　IF函数执行真假值判断 124

7.2.3 FALSE和TRUE函数 125

7.2.4　NOT函数对参数值求反 125

7.2.2　AND和OR函数根据条件判断返回TRUE或FALSE 125

8　日期函数 129

8.1　案例8：日期函数的应用——项目进度统计表 129

8.2　日期函数介绍 135

8.2.1　NOW,TODAY函数显示当前日期和时间 135

8.2.2　YEAR,MONTH函数返回某日期对应的年份和月份 136

8.2.3　DATE函数显示日期编号 137

8.2.4　WEEKDAY函数显示日期的星期几 138

8.2.5　DATEVALUE,DAYS360函数计算两个日期之间的天数 139

8.3　案例9：时间函数的应用——计算上网时间和费用 141

8.4　时间函数介绍 142

8.4.1　HOUR,MINUTE,SECOND函数返回小时，分，秒数 142

8.4.2　TIME,TIMEVALUE函数 143

9　数学函数 145

9.1　案例10：数学函数的应用——根据产品销售情况动态统计库存以及奖金提成 145

9.2　数学函数介绍 154

9.2.1　与舍入有关的函数 154

9.2.2　其他数学函数 156

9.2.3　三角函数 159

9.2.4　数组函数 161

10　信息函数 163

10.1　案例11：信息函数的应用——更加人性化的数据显示方式 163

10.2　信息函数介绍 165

10.2.1 ERROR.TYPE和TYPE函数分别返回错误和数值的类型 165

10.2.2 IS类函数进行判断后返回逻辑值 167

10.2.3 CELL和INFO函数返回指定的信息 168

10.2.4 N,NA和PHONETIC函数 171

11　文本函数 175

11.1　案例12：文本函数的应用——从身份证号码中自动提取性别和生日 175

11.2　文本函数介绍 178

11.2.1 CHAR和CODE函数 178

11.2.2 CLEAN和TRIM函数具有指定的删除功能 179

11.2.3 CONCATENATE函数合并字符串 179

11.2.4 EXACT函数测试两个字符串是否完全相同 180

11.2.5 FIND（FINDB）和SEARCH（SEARCHB）函数查找文本字符串 181

11.2.7 LEFT（LEFTB）、MID（MIDB）和RIGHT（RIGHTB）函数返回特定位置的字符 182

11.2.6 LEN（LENB）函数返回字符串的长度 182

11.2.8 LOWER,PROPER和UPPER函数进行大小写转换 187

11.2.9 REPLACE（REPLACEB）和SUBSTITUTE函数的替换功能 188

11.2.10 REPT函数重复显示文本 190

11.2.11 TEXT和VALUE函数将文本和数字相转换 191

12 数据库函数 195

12.1 案例13：数据库函数的应用——计算数据清单的各种统计指标 195

12.2 数据库函数介绍 202

13 查找和引用函数 205

13.1 案例14：查找和引用函数的应用——工资和奖金的自动计算 205

13.2　查找和引用函数介绍 213

13.2.1 ADDRESS函数返回引用地址 213

13.2.3 CHOOSE函数返回数值参数列表中的数值 214

13.2.2 AREAS函数返回引用中包含的区域个数 214

13.2.4 COLUMN和ROW函数分别返回列标和行号 215

13.2.5 COLUMNS和ROWS函数返回数组或引用的列数和行数 216

13.2.6 GETPIVOTDATA函数返回数据透视表中的数据 217

13.2.7 HLOOKUP,LOOKUP,VLOOKUP实现数据的查找 217

13.2.8 HYPERLINK函数创建跳转 224

13.2.9 INDEX函数返回指定内容 225

13.2.10 INDIRECT函数返回由文本字符串指定的引用 226

13.2.11 MATCH函数找出匹配元素的位置 227

13.2.12 OFFSET函数通过给定偏移量得到新的引用 228

13.2.13 TRANSPOSE函数返回转置单元格区域 229

14.1　案例15：统计函数的应用——员工情况统计 231

14　统计函数 231

14.2　常用统计函数介绍 233

14.2.1 AVERAGE,AVERAGEA函数计算平均值 233

14.2.2　具有统计功能的COUNT＊函数 235

14.2.3　FREQUENCY和MODE函数计算频率分布和频率最高的数值 236

14.2.4　LARGE和SMALL函数返回要求的最值 237

14.2.5　返回最大值、中值、最小值的函数 238

14.2.6　RANK函数返回排序序列 238

14.3　数理统计 240

14.3.1 CONFIDENCE函数计算置信区间 240

14.3.2　CORREL函数返回相关系数 241

14.3.3　FDIST和FINV函数分别计算F概率分布和服从概率的F值 242

14.3.4　NORMDIST和NORMSDIST函数返回正态分布和标准正态分布 242

14.3.6　VAR和VARA函数返回样本方差 243

14.3.5　STDEV和STDEVA函数计算样本的标准偏差 243

15　财务函数 245

15.1　案例16：财务函数的应用——购房贷款方式分析 245

15.2　财务函数介绍 247

15.2.1　计算折旧的函数——DB,DDB,SLN,SYD和VDB函数 247

15.2.2　计算投资的函数——FV,FVSCHEDULE,PV,NPV和XNPV函数 252

15.2.3　计算本金和利息的函数——PMT,IPMT,PPMT,CUMIPMT和CUMPRINC函数 255

15.2.4　计算偿还率的函数——RATE,IRR,MIRR和XIRR函数 256

15.2.5　NPER函数返回某项投资的总期数 258

16　数组公式 261

16.1　案例17：数组公式的应用——奖金分配计算 261

16.2　案例18：数组和函数公式的高级应用——计算数据清单的各种统计指标 266

16.3　技巧篇：利用公式审核帮助理解运算过程 270

17　图表处理 273

17.1　案例19：图表处理入门——某公司生产和销售情况图表 273

17.1.1　4步骤建立图表 273

17.1.2　润饰图表的几个关键对话框 277

17.2　案例20：复杂图表制作——动态显示数据和统计图 283

17.2.1　通过函数创建动态图表 285

17.2.2　通过窗体创建动态图表 288

17.2.3　混合的图表类型 292

17.2.4　统计动态变化的分数段的人数 295

17.3　技巧篇 305

17.3.1　添加数据 305

17.3.2 图表中文本随工作表中文本的变化而变化 307

第3篇 高级应用篇 309

18.1　案例21：模拟运算表的应用——分期付款金额对照表 311

18　变量求解与方案优选 311

18.2　案例22：单变量求解——贷款金额计算 315

18.3　案例23：规划求解的应用——生产计划安排 318

18.4　方案分析 325

19　应用之间的自动化协作 331

19.1　案例24：Excel,Word,PowerPoint之间的协作 331

19.1.1　Excel间的协作 331

19.1.2　合并计算 336

19.1.3　Word,PowerPoint和Excel间的协作 340

19.2　案例25：Excel与Access之间的协作 343

19.2.1　将Excel数据导入Access表中 343

19.3　提高篇：通过MS Query操作外部数据 345

19.2.2　将Access数据导入Excel中 345

20　宏和VBA 357

20.1　案例26：人事工资管理系统 357

20.1.1　设计员工考勤记录窗体 361

20.1.2　设计员工业绩记录窗体 365

20.1.3　工资表的计算和工资条的生成 367

20.1.4　登录系统 369

20.1.5 自定义菜单 374

20.1 6　结果演示和代码清单 377

20.2　系统的复用和保护 390

附录A　常用快捷键 391

附录B　数据分析常用的Excel函数 399