Excel数据分析辞典 双色PDF电子书下载

第1章 定义Excel数据分析环境 1

分类检索 1

用户环境 1

建立数据表格 1

安全保护数据 1

名词术语 2

Microsoft账户 2

工作表标签 2

功能区／选项卡／组 2

合并单元格／合并后居中／跨越合并 2

001用户操作环境设置 3

实例1-1：Microsoft用户账户设置 3

实例1-2：定制Office背景和Office主题 5

实例1-3：设置默认工作表数与标准字体 6

实例1-4：自由控制功能区显示 8

实例1-5：自定义选项卡和组 11

实例1-6：自定义界面显示元素 13

002Excel数据表格的建立 15

实例1-7：创建员工通讯录工作表 15

实例1-8：创建周末加班申请表 17

003数据的安全保护 19

实例1-9：设置密码保护员工工资工作簿 19

实例1-10：将部门工作分配工作簿标记为最终状态进行保护 21

实例1-11：保护面试最终结果工作表限制人员编辑 23

实例1-12：设置只允许填写考核成绩表中的成绩 24

第2章 数据的输入 27

分类检索 27

各类数据的输入 27

规律与非规律数据的输入 27

有效数据的输入 28

不同的输入方式 28

名词术语 28

数据类型 28

文本型数字 29

科学计数 29

004数字和文本的输入 29

实例2-1：输入以001开头的学员编号 30

实例2-2：轻松输入身份证号 30

实例2-3：登记职员住房面积时自动插入小数点 32

实例2-4：横向登记员工当月工资明细 33

005日期和时间的输入 34

实例2-5：登记销售流水账 34

006分数的输入 35

实例2-6：会议出席情况统计 35

007金额的输入 36

实例2-7：快速填制费用报销单中的大写金额 36

008特殊字符的输入 37

实例2-8：真写员工考勤记录 37

实例2-9：制作转账凭证 38

009相同数据的输入 39

实例2-10：同职位岗位工资的快速填制 40

实例2-11：快速制作多份记账凭证表单 40

010规律数据的输入 41

实例2-12：复制填充连续的调薪时间 42

实例2-13：序列填充连续的产品编号 43

实例2-14：自动为日常工作日程表填充工作日 44

实例2-15：快速输入几个一组的固定商品名称 45

实例2-16：员工姓与名的快速分列输入 46

011随机数据的输入 47

实例2-17：使用RAND()函数输入随机座位编号 47

012有效数据的输入 48

实例2-18：输入不超过10分的有效评分 48

实例2-19：输入不在当前日期范围的值班日期 49

实例2-20：输入不重复的员工工号 50

实例2-21：有效输入不小于当前时间的签到 51

实例2-22：输入不超过总预算额的单项订购金额 52

实例2-23：最高采购量输入提醒 53

013列表选择输入 54

实例2-24：从右键下拉列表中选择年龄段 54

实例2-25：使用有效性列表选择输入所属部门 55

实例2-26：快速选择员工出生省份和对应的城市 56

014选择性粘贴输入 58

实例2-27：选择性粘贴将各分公司营业收入金额由元转换为万元 59

015记录单输入 60

实例2-28：使用记录单登记产品入库情况 60

第3章 格式化数据让其更易分析 62

分类检索 62

数据的对齐与排列 62

动动鼠标让数据显示更明确 62

让数据按个人意愿显示 62

按条件突出显示数据 63

名词术语 63

货币 63

千位分隔符 63

占位符 63

条件格式 64

数据条 64

色阶 64

图标集 64

公式 64

016数据的对齐 64

实例3-1：对齐产品销售单价中小数点 65

017数据的排列 65

实例3-2：让日记账中的月／日分列显示 66

实例3-3：换行显示计量单位 68

实例3-4：实现销售统计表的行列转换 68

实例3-5：比较产销记录表中两个间隔较远的字段 69

018调整小数位数 70

实例3-6：快速增加员工离职率小数位 70

实例3-7：保证销售回款率计算结果的精确度 71

019数据的货币显示 72

实例3-8：为金额添加人民币符号 72

实例3-9：为工资表中的工资数额应用会计专用格式 74

020显示千位分隔符 75

实例3-10：给应收账款添加千位分隔符以便查看 75

021用文本占位符自动添加文本 76

实例3-11：在产品编号中自动添加固定型号 76

022数字占位符的格式占位 78

实例3-12：将客户编号用“0”补足为4位 78

实例3-13：用“#”统一金额格式 79

023字符的重复显示 80

实例3-14：用“＊”重复填充隐私信息 80

024按条件显示格式 81

实例3-15：用盈利／亏损替代具体的金额数字 82

025灵活显示日期时间 82

实例3-16：客户来电日期时间的灵活设置 83

026将满足一定范围的数据以某种格式显示 84

实例3-17：突出显示能承受的贷款每月固定偿还额 85

实例3-18：标识本周新进人员 86

实例3-19：将兼职人员区别显示 87

实例3-20：突出显示重复的电话号码 88

实例3-21：将积分排名前10％的员工以特定格式显示 89

实例3-22：找出工资表中高于平均收入的员工 90

027将数值大小以数据条长短反映 91

实例3-23：使用实心数据条分析产品销售状况 92

实例3-24：用负值条形图表现收入盈亏 92

实例3-25：隐藏工资条形图的工资数额 94

028将数值高低以色阶过渡呈现 95

实例3-26：用双色刻度分析员工培训成绩 95

实例3-27：用三色刻度分析产品需求量 96

029用图标区分不同范围的数据 97

实例3-28：以箭头形状明示职工业绩等级 97

实例3-29：用象限图标识项目完成进度 98

030自定义为满足公式条件的数据设置格式 99

实例3-30：合同到期的提前提醒 99

实例3-31：最低库存预警 101

第4章 数据的查找与更新 103

分类检索 103

“查找”对话框查找 103

名称与记录单查找 103

定位查找 103

名词术语 104

查找／替换／转到／定位 104

常量 104

关键字 104

匹配查找 105

空值 105

在多个单元格中输入数据 105

031关键字查找 106

实例4-1：查找并快速更新订购商品的单位 106

032按格式查找 107

实例4-2：查找最新进货的生鲜类食品 108

033模糊查找 109

实例4-3：查找护理头部的洗护商品 110

034匹配查找 110

实例4-4：查找已到使用期限的固定资产 110

035名称查找 111

实例4-5：快速查找并更新折扣率 111

036记录单查找 112

实例4-6：使用记录单浏览修改固定资产表 112

实例4-7：查找数量在2台以上的记录 114

037差异查找 115

实例4-8：使用差异查找辅助对账 115

038查找数字 116

实例4-9：查找零售价格表中的单价并统一应用货币格式 116

039查找公式结果 118

实例4-10：查看近期商品出库金额 118

040查找错误值 119

实例4-11：查找排名表中出现的公式错误 119

041查找空值 120

实例4-12：在现有库存的空单元格中快速输入“缺货”字样 120

第5章 排序、筛选和分类汇总 123

分类检索 123

数据的排序 123

数据的筛选 123

数据的分类汇总 124

名词术语 124

关键字／字段 124

排序／筛选／分类汇总 125

分类汇总／分级显示 125

合并计算 125

042单一条件排序 125

实例5-1：将员工健康检查评分由高到低排列 126

043多条件排序 126

实例5-2：按入职时间与当前岗位薪资排序员工信息表 126

044按汉语拼音排序 128

实例5-3：按考场人员姓名的汉语拼音排序编制考场名单 128

045按笔划排序 130

实例5-4：按考场人员姓名的笔划编制考场名单 130

046自定义排序 132

实例5-5：使用自定义排序制作部门工作计划 132

047不规则编号排序 133

实例5-6：按办公用品编号排序整理办公用品表 134

048按颜色排序 136

实例5-7：通过颜色排序区分待办事项是否紧急 136

049按图标排序 138

实例5-8：按图标排序查询日常费用开支程度 138

050按行排序 140

实例5-9：按行排序员工奖金评估表 140

051对合并单元格数据排序 142

实例5-10：将小组当月销售额由高到低排序 142

052自动筛选 144

实例5-11：自动筛选统计公司男员工人数 144

053包含合并单元格的自动筛选 145

实例5-12：筛选查看某日所有工作内容 145

054搜索筛选 149

实例5-13：筛选当月全勤人员 149

实例5-14：筛选某月转正的所有人员记录 149

055两个条件内的自定义筛选 150

实例5-15：查看年龄在201到25之间的员工信息 151

056“且”规则的高级筛选 152

实例5-16：按一段时间的消费次数、消费金额划分客户等级 152

057“或”规则的高级筛选 154

实例5-17：对迟到5次以上、缺席2天或业务额低于1万的员工予以劝退 154

058混合规则的高级筛选 155

实例5-18：查询具有法律专业本科学历或有事业单位工作经验的人员 156

059计算条件的高级筛选 157

实例5-19：筛选工龄超过单位平均工龄的人员记录 157

060不重复记录的高级筛选 159

实例5-20：筛选本月有订单的货品名 159

061简单分类汇总 160

实例5-21：汇总统计不同职位的平均工资 161

062选择多个汇总项的分类汇总 162

实例5-22：统计不同部门的基本工资和工资总额 162

063多重分类汇总 164

实例5-23：统计不同部门的工资总额和平均值 164

064嵌套分类汇总 166

实例5-24：汇总统计各部门中不同职位的工资状况 166

065手动建立分级显示 169

实例5-25：以上、中、下旬时间查询当月销售记录 169

066自动生成分级显示 171

实例5-26：自动建立分级显示划分客户所在区域 171

067按位置合并计算 172

实例5-27：按位置合并计算一季度各商品订单额 173

068按分类合并计算 175

实例5-28：按分类合并计算一季度各商品订单额 175

第6章 交互式分析的数据透视表 179

分类检索 179

数据透视表的创建与刷新 179

数据透视表的整理与查看 179

数据透视表的排序与筛选 179

数据透视表的组合与计算 180

数据透视表的多表分析 180

名词术语 180

数据透视表 180

源数据 180

字段 181

数据项 181

汇总函数 181

行字段／列字段／值字段 181

筛选器 181

切片器 181

日程表 181

069数据透视表的建立 182

实例6-1：建立员工档案数据透视表 182

070快速统计重复项 183

实例6-2：快速统计公司人员情况 184

071调整字段排列多维度分析 185

实例6-3：多角度分析公司人员构成 185

072自动刷新数据透视表 187

实例6-4：自动刷新员工档案数据透视表 187

073以不同汇总方式统计 188

实例6-5：以不同方式统计物品库存情况 189

074以不同显示方式分析 190

实例6-6：统计不同物品库存比例 190

075查看数据透视表明细数据 192

实例6-7：查看某商品不同规格型号的库存明细 192

076更改透视表数据源 194

实例6-8：对滞销品清理库存后重新更新透视表 194

077在数据透视表中排序 195

实例6-9：对现有库存距安全库存的情况进行排序 196

078报表筛选实现分页浏览 197

实例6-10：按缺货物品的紧急状态进行筛选 197

079使用筛选按钮筛选 199

实例6-11：对订购额在一定金额以上的正价商品免运费 199

080使用切片器直观筛选数据 200

实例6-12：按商品名、订购日期查询负责商品的销售员 200

081使用日程表实现时段筛选 203

实例6-13：使用日程表查看商品订购情况 203

082组合透视表中的日期项 204

实例6-14：按月份查看商品的订购情况 204

083在透视表中添加计算字段 206

实例6-15：按月计算各商品的订购额 206

084数据透视表的多表分析 208

实例6-16：建立双透视表分析公司产品的订购情况 208

第7章 统计分析、假设检验与数据预测 210

分类检索 210

统计分析 210

假设检验 210

数据预测 211

名词术语 211

方案 211

规划求解 211

描述统计 212

直方图 212

排位 212

相关性 212

抽样 212

回归分析 212

单变量求解 213

模拟运算表 213

方差 213

t/Z/F检验 213

移动平均法 213

指数平滑法 213

085最优方案分析 214

实例7-1：公司销售的最优方案分析 214

086规划求解最优解 216

实例7-2：求解利润最大化生产方案 216

实例7-3：合理安排员工值班 218

087描述统计数据 220

实例7-4：描述统计分析职员工资分布情况 220

088直方图分段统计发生频率 221

实例7-5：直方图分析公司人员流动情况 222

089排位 223

实例7-6：根据全年业绩排位发放年终奖 223

090抽样分析 225

实例7-7：抽样分析公司工资水平 225

实例7-8：抽样调查产品的市场满意度 227

实例7-9：未收款项的抽样调查分析 229

091相关性与回归分析 231

实例7-10：卖场面积与营业额的相关性分析 231

实例7-11：公司产品生产与销售成本的回归分析 232

092单变量求解 234

实例7-12：使用单变量求解实现目标存款额的最低月储蓄额 234

093单变量模拟运算表 235

实例7-13：建立销售员提成的单变量模拟运算表 236

094双变量模拟运算表 237

实例7-14：建立公司固定资产折旧的双变量模拟运算表 237

095单因素方差分析 239

实例7-15：不同行业收入状况的单因素方差分析 239

096双因素方差分析 241

实例7-16：工人和机器设备对产量影响的双因素方差分析 241

实例7-17：产品品牌和销售地区对销量影响的双因素方差分析 242

097t检验 243

实例7-18：产品标准性的t检验 244

实例7-19：t检验分析两种血压测定仪检测值是否有明显差别 245

098Z检验 246

实例7-20：Z检验法分析产品生产情况是否符合设计标准 246

099F检验 248

实例7-21：部门工作效率的F检验分析 248

100时间序列的趋势外推预测 249

实例7-22：使用趋势线预测明年销售额 250

101时间序列的移动平均预测 252

实例7-23：利用移动平均法预测来年工资增长率 252

实例7-24：利用二次移动平均法预测的未来两年办公费用 253

102时间序列的指数平滑预测 255

实例7-25：利用指数平滑法预测公司明年利润额 256

实例7-26：公司产品销量的二次指数平滑预测 258

第8章 数据计算 260

分类检索 260

公式的输入、编辑与复制 260

单元格引用 260

名称的应用 260

数组常景和数组公式 261

错误分析与检查 261

名词术语 261

公式 261

运算符及运算符优先级 261

A1与R1C1引用样式 262

相对引用、绝对引用、混合引用以及三维引用 262

名称 263

数组、数组常量以及数组公式 263

迭代／循环引用 263

103输入与编辑公式 264

实例8-1：输入编辑公式计算产品销售额 264

104复制公式 266

实例8-2：使用“填充柄”复制公式计算销售额 267

实例8-3：使用“粘贴”功能复制公式计算销售额 268

105自动求和、求平均值计算 269

实例8-4：使用自动求和计算材料采购金额 269

实例8-5：使用“自动求和”统计成绩 270

106相对引用计算 272

实例8-6：使用相对引用根据单价和销量计算销售额 272

107绝对引用计算 274

实例8-7：使用绝对引用计算不同商品相同折扣下的销售额 274

108混合引用计算 275

实例8-8：使用混合引用计算同一商品不同折扣不同销售下的销售额 276

109三维引用计算 277

实例8-9：在工资表中使用三维引用计算工龄工资 277

110使用名称计算 279

实例8-10：定义名称计算固定资产计提折旧额 279

实例8-11：根据所选内容创建名称计算工资总额 282

111运用数组常量计算 284

实例8-12：使用数组常量计算员工缴纳个人所得税 284

112运用数组公式计算 287

实例8-13：运用数组公式计算销售商品总额 287

实例8-14：运用数组公式统计满足条件的员工姓名 288

实例8-15：运用数组公式制作日期 289

113启用迭代计算 290

实例8-16：使用迭代计算生成不变的随机数 291

实例8-17：使用迭代计算获取输入时间 294

114式审核 296

实例8-18：审核固定资产计提折旧额公式的正确性 296

实例8-19：修改显示错误值的公式 297

第9章 数据处理函数 300

分类检索 300

数据的逻辑判断与处理函数 300

日期数据的处理函数 300

文本数据的处理函数 300

财务数据处理函数 301

数据查找函数 301

数据集中趋势统计函数 301

数据离散趋势分析统计函数 302

数据概率统计函数 302

数据汇总计算函数 302

名词术语 302

集中趋势 302

离散趋势 303

概率 303

115数据的逻辑判断与处理 303

实例9-1：按年均消费额划分客户等级 304

实例9-2：判断累积积分是否可以领取季度奖 304

实例9-3：判断培训成绩是否合格 305

116处理日期数据 306

实例9-4：计算工作年限 306

实例9-5：计算报表当前填制时间 307

实例9-6：计算转正日期 308

实例9-7：计算项目执行天数 309

117处理文本数据 310

实例9-8：从身份证号中获取出生信息 310

实例9-9：将金额按设置格式显示 311

实例9-10：从产品编号中获取产品类别 312

118处理财务数据 313

实例9-11：计算固定资产线性折旧费 313

实例9-12：使用固定余额递减法计算折旧值 315

实例9-13：计算公司贷款每期偿还额 316

实例9-14：计算投资的未来值 317

实例9-15：计算某项目投资的现值和净现值 317

实例9-16：计算一系列现金流的内部收益率 318

119查找数据 319

实例9-17：查找某积分对应的奖金金额 320

实例9-18：根据员工编号显示员工个人信息 320

实例9-19：计算哪一个月完成了目标 322

实例9-20：查找游戏点数可兑换的礼物 323

实例9-21：从电话簿中提取姓名 324

实例9-22：将满足一定条件的员工调配到集团工作 325

120集中趋势统计 325

实例9-23：算术与几何平均值计算 326

实例9-24：计算众数 327

实例9-25：计算中位数 328

121离散趋势统计 328

实例9-26：计算方差 329

实例9-27：计算标准差 329

实例9-28：计算四分位数 330

实例9-29：计算偏态值 332

实例9-30：计算峰度值 333

122概率统计 334

实例9-31：使用二项分布检查产品合格率 334

实例9-32：使用泊松分布制定公司生产目标 335

123数据汇总计算 337

实例9-33：统计客户当月投诉次数 337

实例9-34：统计某部门业务量总额 338

实例9-35：统计女职员评为中级职称的人数 338

实例9-36：返回不同年龄段的员工人数 339

第10章 单元格图表和数据图 341

分类检索 341

迷你图 341

常用标准图表的使用 341

高级图表 342

组合图 342

动态图表 342

名词术语 343

迷你图／迷你图组 343

标准图表 343

温度计图 344

甘特图 344

瀑布图 344

漏斗图 344

阶梯图 344

柏拉图 344

124使用柱形迷你图比较数值大小 344

实例10-1：使用柱形迷你图分析每月收入状况 345

实例10-2：创建间隔时间不等的柱形迷你图分析网站访问情况 346

125使用折线迷你图分析数据变动趋势 348

实例10-3：创建折线迷你图分析一周股价变动趋势 348

126使用盈亏迷你图反映金额正负变化 350

实例10-4：创建盈亏迷你图分析信用卡是否欠款 350

127使用REPT函数自定义创建单元格迷你图 352

实例10-5：用REPT函数创建商品信用等级图 352

实例10-6：用REPT函数制作销量条形图 353

128使用柱形图纵向比较数据 355

实例10-7：创建簇状柱形图比较产品销量 355

实例10-8：创建堆积柱形图比较各分公司营业额 356

实例10-9：创建百分比堆积柱形图查看近年新进职员学历倾向 357

129使用条形图横向比较分类项 358

实例10-10：创建三维簇状条形图比较各项社团活动参与人数 358

实例10-11：创建水平圆柱图比较项目计划与实际完成情况 359

130使用折线图表现数据发展趋势 362

实例10-12：创建平滑折线图分析年度营业利润增长变动趋势 362

实例10-13：创建带数据标记的堆积折线图分析不同等级客户近年消费趋势 364

实例10-14：创建三维折线图分析两大产品线营业额成长趋势 365

131使用饼图或圆环图分析数据比例关系 367

实例10-15：创建饼图分析员工出勤状况 367

实例10-16：创建分离型饼图进行员工离职原因分析 369

实例10-17：创建复合饼图分析公司年度福利经费开支 371

实例10-18：创建双层复合饼图分析不同年龄段客户的消费能力 372

实例10-19：创建圆环图分析各子公司的销售额 375

实例10-20：创建圆环图分析各岗位工资构成 376

132使用面积图进行数据量与趋势变化分析 377

实例10-21：创建三维面积图分析公司账款回收状况 378

实例10-22：创建堆积面积图分析公司近年来客户的构成 379

133使用散点图和气泡图进行相关性分析 380

实例10-23：创建散点图分析商品售价与销量的相关性 380

实例10-24：创建带平滑线的散点图进行竞争产品销量比较 382

实例10-25：创建气泡图分析各品牌销售状况及市场地位 383

实例10-26：创建气泡图分析因广告投入不同所呈现的销售额的变化 384

134使用雷达图进行倾向／平衡分析测试 386

实例10-27：创建雷达图分析员工体能状况 386

实例10-28：创建雷达图对比A、B产品营养成分 387

135使用温度计图表分析项目完成比例 388

实例10-29：创建温度计图表分析任务完成进度 389

136使用甘特图表现项目执行进度 392

实例10-30：使用悬浮条形图查看项目阶段 392

137使用瀑布图反应数据增减变动 395

实例10-31：通过堆积柱形图创建瀑布图表现库存商品变动情况 395

实例10-32：通过涨跌柱线创建瀑布图分析现金流 396

实例10-33：创建带负值的瀑布图反映员工实发工资情况 398

138使用漏斗图表现数据阶梯型递减 402

实例10-34：创建漏斗图表现客户等级 402

139使用阶梯图分析数据阶梯变动趋势 405

实例10-35：创建阶梯图表现本年费用递增状况 405

140使用柏拉图找到对数据起关键作用的因素 409

实例10-36：创建柏拉图分析产品不良率 409

141使用不等宽柱形图表现值的范围和分布 414

实例10-37：创建不等宽柱形图反映销量与市场占有率 414

142组合图的使用 416

实例10-38：创建线－柱图比较两事业部销售额差异 417

实例10-39：创建双轴线－柱图分析商品销售与折扣率的关系 418

实例10-40：创建双轴线－柱图进行男女比例与消费状况相关分析 419

实例10-41：创建双轴线－面积图进行男女比例与薪资状况的相关分析 421

143解决值差较大的断层图的制作 423

实例10-42：用模拟Y轴法制作断层图 423

144迷你图与数据图表的组合使用 430

实例10-43：在条形图中建立迷你图分析成本变化 430

145创建动态图交互式分析数据 432

实例10-44：使用辅助区域建立动态图分析不同月份各产品订单额 433

实例10-45：定义名称建立随记录项增加的销量流水动态图 435

实例10-46：创建下拉菜单式图表分析近年各部门人数变动 437

第11章 源数据链接、嵌入与输出 440

分类检索 440

建立链接 440

嵌入或被嵌入 440

获取数据 440

共享与输出 441

名词术语 441

屏幕提示 441

嵌入与链接 441

146使用超链接链接外部数据源 442

实例11-1：制作公司员工通讯簿 442

147通过复制粘贴建立链接 444

实例11-2：制作一式三联对账单 444

148通过公式建立链接 447

实例11-3：销售汇总的跨工作簿计算 447

实例11-4：引用计算保险扣款 449

149在工作表中嵌入其他文件对象 450

实例11-5：在日常费用统计表中嵌入费用类别说明文件 450

150将工作表或图表嵌入到其他程序 452

实例11-6：将Excel销售统计图嵌入到PPT中 452

151获取数据库数据 454

实例11-7：将Access中员工记录导入到员工档案表中 454

152获取外部文本文件数据 456

实例11-8：将文本文件中本周工作计划导入到Excel日程安排表中 456

153获取网站数据 458

实例11-9：从网站获取Excel统计数据 459

154传递工作表记录到Access数据库中 460

实例11-10：将员工通讯簿导入到Access数据库中 460

155将数据表存为文本文件 463

实例11-11：将人事通报表存为文本文件 463

156将Excel数据表保存到云 465

实例11-12：将职务调动申请表保存到云 465

157邀请他人协同编辑数据 468

实例11-13：邀请他人协同进行采购物品登记 468

158数据表的打印输出 471

实例11-14：会议议程安排表的打印输出 471