Excel 2007数据统计与分析范例应用PDF电子书下载

Chapter01对公司常用数据进行基本统计 1

1.1员工年假表 14

1.1.1知识点及效果图 14

1.1.2操作步骤 14

1.填充员工所属的部门 14

知识链接——TODAY（）函数 16

2.计算员工的年龄和工龄 16

知识链接——MONTH（）、YEAR（）函数 16

知识链接——INT（）函数 18

3.计算员工的年假天数 18

1.2员工加班记录表 20

1.2.1知识点及效果图 20

1.2.2操作步骤 20

1使用数据有效性填充部门和职位 20

知识链接——设置数据有效性 20

2.计算加班时间 22

知识链接——DATE（）函数 22

3.计算加班费 24

1.3 员工月度出勤统计表 24

1.3.1知识点及效果图 25

1.3.2操作步骤 25

1创建动态标题辅助表格 25

知识链接——插入和设置数字调节按钮 27

2.完善月度考勤表 29

3计算员工缺勤和缺席天数 30

更改步长值 35

Chapter02按照分类汇总进行统计 38

2.1产品生产单的简单分类汇总 38

2.1.1知识点及效果图 38

2.1.2操作步骤 38

1对产品名称进行排序 38

知识链接——简单排序 39

2按产品名称对总成本进行分类汇总 39

知识链接——设置单级分类汇总 39

知识链接——隐藏和显示明细数据 40

3.按生产日期对产品总成本进行分类汇总 41

2.2产品生产单的多级分类汇总 42

2.2.1知识点及效果图 42

2.2.2操作步骤 42

1.分别对生产车间和产品名称进行排序 42

知识链接——复杂排序 43

2.创建嵌套分类汇总 44

知识链接——设置多级分类汇总 44

3.隐藏和显示各车间各产品的汇总情况 45

知识链接——多级分级显示 45

2.3 使用基他方式统计 45

2.3.1知识点及效果图 46

2.3.2操作步骤 46

1.使用分类汇总函数对生产单进行统计 46

知识链接——显示公式 47

知识链接——SUBTOTAL（）函数 48

2.插入批注对汇总结果进行说明 49

知识链接——插入批注 49

3利用组合方式汇总产品总成本 52

求最大生产成本 55

Chapter03使用统计函数统计数据 58

3.1 使用统计函数分析销量 58

3.1.1知识点及效果图 58

3.1.2操作步骤 58

1.计算销量的最大值和最小值 58

2.计算销量的两个最大值和最小值 60

3.计算销量的众数、中位数和平均值 61

4.计算销量的25％、75％处的数据以及标准偏差 63

3.2 使用统计函数按销量排名并进行分段统计 65

3.2.1知识点及效果图 65

3.2.2操作步骤 66

1定义销售员和销量 66

知识链接——定义名称 66

2.按销售量排名 68

3分段统计销量 69

3.3使用统计函数统计保单销量 72

3.3.1知识点及效果图 72

3.3.2操作步骤 72

1.计算各种险种的投保率 72

知识链接——选择性粘贴 73

2.计算各种保险金额的比例值 75

3.分别按销售员和车型统计销售量 76

按销售员和车型统计 78

Chapter04使用图表统计 80

4.1 经营收入与生产成本对比折线图的制作 80

4.1.1知识点及效果图 80

4.1.2操作步骤 80

1.完善企业经营数据表 80

知识链接——插入公式 81

2.创建企业经营、生产成本对比图 82

知识链接——插入和设置折线图 82

3.设置企业经营、生产成本对比图 84

知识链接——添加趋势线 86

4.2 经营收入按年份对比柱状图的制作 87

4.2.1知识点及效果图 87

4.2.2操作步骤 88

1.创建经营收入的按年份对比图 88

知识链接——插入柱形图 88

知识链接——更改图表的效果 88

2.美化经营收入的按年份对比图 90

知识链接——编辑数据系列 91

3.移动经营收入的按年份对比图 92

4.3 企业长期经营状况的饼图制作 92

4.3.1知识点及效果图 93

4.3.2操作步骤 93

1.完善企业经营数据表 93

2.创建生产效益饼图 94

知识链接——插入饼图 94

知识链接——更改数据源 94

3.美化企业生产效益饼图 95

知识链接——在图表中设置 96

艺术字 96

圆环图的使用 98

Chapter05数据的描述统计分析 100

5.1 利用描述统计工具分析企业各月销售情况 100

5.1.1知识点及效果图 100

5.1.2操作步骤 100

1.计算销售额全距和方差 100

2.计算销售额标准差和四分位数 102

3.利用描述统计工具分析企业各月 103

销售额 103

知识链接——添加分析工具库 104

知识链接——描述统计工具 104

5.2 利用直方图和动态图表分析各月的销售额 105

5.2.1知识点及效果图 106

5.2.2操作步骤 106

1创建各月份销售额分布直方图 106

知识链接——直方图工具 107

2.计算销售额增长率、定基发展速度和环比发展速度 109

知识链接——公式的各种引用方法 109

3.企业各月销售额的动态图表分析 112

知识链接——折线图的创建和设置 112

5.3 利用泊松分布制定企业销售目标 113

5.3.1知识点及效果图 114

5.3.2操作步骤 114

1.计算销售额平均值和高于该平均值的周数 114

知识链接——AVERAGE（）函数 115

2.利用泊松分布函数计算概率 116

3.通过泊松函数计算不同周数的置信度 117

制作销售动态散点图 119

Chapter06使用排序和筛选分析数据 122

6.1 对销售记录表进行排序 122

6.1.1知识点及效果图 122

6.1.2操作步骤 122

1.对销售金额进行排序 122

2.对销售组别、销售月份和销售型号进行排序 123

3.对销售型号按照自定义序列进行排序 125

知识链接——自定义排序 125

6.2 对销售记录表进行筛选 126

6.2.1知识点及效果图 127

6.2.2操作步骤 127

1.对销售记录表进行自动筛选和自定义筛选 127

知识链接——自动筛选和自定义筛选 128

2.对销售记录表进行“与条件筛选 131

知识链接——“与条件筛选 131

3.对销售记录表进行“或条件筛选 134

知识链接——“或条件”筛选 134

6.3 利用筛选函数对销售记录表进行筛选 136

6.3.1知识点及效果图 136

6.3.2操作步骤 136

1.使用筛选函数对销售部数进行筛选 136

2.使用筛选函数对销售金额进行筛选 138

知识链接——IF（）、 GESTEP（）函数 138

3.计算热销型号 138

条件格式的使用 140

Chapter07数据的透视分析 142

7.1 企业员工性别和年龄透视分析 142

7.1.1知识点及效果图 142

7.1.2操作步骤 142

1.创建员工性别结构透视表 142

知识链接——创建数据透视表 142

知识链接——设置数据透视表 143

2.创建员工性别结构透视图 145

知识链接——创建数据透视图 145

3.创建员工年龄透视表 147

7.2 企业员工学历层次透视分析 150

7.2.1知识点及效果图 150

7.2.2操作步骤 151

1创建学历层次透视表 151

2.更新和排序学历层次透视表数据 153

知识链接——更新数据透视表 153

知识链接——排序数据透视表 154

3.美化学历层次透视表 155

知识链接——套用数据透视表格式 155

7.3 企业人力资源综合分析 155

7.3.1知识点及效果图 155

7.3.2操作步骤 156

1.企业人力资源综合透视表分析 156

知识链接——设置数据透视表中的值字段 157

2.企业人力资源综合透视图分析 158

知识链接——移动和更改数据透视图 158

3.美化企业人力资源综合透视图 159

知识链接——设置数据透视图 159

更改数据透视图的布局 162

Chapter08使用复合图表和双轴图表分析数据 162

8.1 企业资金结构分析 164

8.1.1知识点及效果图 164

8.1.2操作步骤 164

1.创建企业资金结构分布图 164

知识链接——创建复合饼图 164

2.设置企业资金结构分布图的数据系列 165

知识链接——设置数据标签选项 165

3.美化企业资金结构分布图 166

8.2 企业收支分析图表 167

8.2.1知识点及效果图 167

8.2.2操作步骤 168

1.企业收支图表分析 168

知识链接——创建双轴图表 169

2.更新企业收支图表 170

知识链接——添加数据系列 170

3.创建月利润与增长率的双轴折线图 173

知识链接——创建双轴折线图 173

8.3 创建企业收支层次分析图表 174

8.3.1知识点及效果图 174

8.3.2操作步骤 175

1.创建企业月利润分析图表 175

知识链接——创建条形图 175

2组合企业收支层次分析图表 178

知识链接——组合条形图与饼图 178

3.创建柱状-面积图分析企业收支情况 180

知识链接——创建柱状-面积图 180

使用柱状-散点图进行企业收支趋势分析 183

Chapter09使用交互图表分析数据 186

9.1 创建销售额滚动图表 186

9.1.1知识点及效果图 186

9.1.2操作步骤 186

1.定义计划与实际销售额表格中的部分字段 186

2.创建实际与计划销售额图表 187

3.创建销售额滚动图表 190

知识链接——创建滚动条 190

知识链接——设置控件格式 191

9.2 创建带下拉列表的销售统计图表 192

9.2.1知识点及效果图 192

9.2.2操作步骤 192

1创建各产品销售额比例图 193

知识链接——INDEX（）函数 193

2编辑各产品销售额比例图 194

3.创建带下拉列表的各产品销售额比例图 194

知识链接——在图表中创建组合框 195

知识链接——设置组合框 195

9.3 创建带复选框的销售统计图表 196

9.3.1知识点及效果图 196

9.3.2操作步骤 197

1.为销售统计表部分数据定义名称 197

知识链接——根据内容定义名称 197

2.创建动态销售统计图中的复选框 198

知识链接——创建复选框 198

知识链接——设置复选框的格式 199

3.动态显示各产品的销售统计情况 201

更改折线图中的数据点 204

Chapter10数据的假设检验分析 206

10.1 产品生产的单、双因素差分析 206

10.1.1知识点及效果图 206

10.1.2操作步骤 206

1.产品生产的单因素方差分析 207

知识链接——单因素方差分析 207

2产品生产的可重复双因素方差分析 209

知识链接——可重复双因素分析 209

3.产品生产的无重复双因素方差分析 210

知识链接——无重复双因素分析 210

10.2 产品生产的t检验法 211

10.2.1知识点及效果图 211

10.2.2操作步骤 211

1.平均值的成对二样本的t检验假设方法 211

2.使用平均值的成对二样本的t检验方法测试产品稳定性 212

知识链接——平均值的成对二样本分析 212

3.使用双样本等方差假设法测试 214

知识链接——t-检验：双样本等方差假设 214

4.使用双样本异方差假设法测试 215

知识链接——t-检验：双样本异方差假设 215

10.3 产品生产的Z检验和F检验分析 216

10.3.1知识点及效果图 217

10.3.2操作步骤 217

1.产品生产的Z检验法 217

知识链接ABS（）SQRT（）函数 218

2.产品生产的双样本平均差检验 220

知识链接——应用Z检验法 220

3.产品生产的F检验法 221

知识链接——应用F检验法 222

F检验分析的更改 223

Chapter11规划求解与最优化方案选择 223

11.1 生产成本最小化规划求解 226

11.1.1知识点及效果图 226

11.1.2操作步骤 226

1.加载““规划求解”分析工具 227

知识链接——加载“规划求解”分析工具 227

2.创建成本最小化求解模型 228

3.进行生产成本最小化规划求解 229

知识链接——创建规划求解 229

知识链接——设置规划求解选项 229

知识链接——添加约束条件 229

4.创建成本最小化规划求解报告 231

知识链接——生成敏感性报告 232

11.2 利润最大化规划求解 233

11.2.1知识点及效果图 233

11.2.2操作步骤 234

1.创建销售额利润最大化求解模型 234

2.进行销售利润最大化规划求解 235

3.创建利润最大化规划求解报告 237

知识链接——生成运算结果报告 237

11.3创建并管理方案 239

11.3.1知识点及效果图 239

11.3.2操作步骤 239

1.将规划求解结果保存为方案 240

知识链接——创建方案 241

2.创建方案摘要 242

知识链接——创建方案摘要 243

3.合并方案 244

知识链接——合并方案 244

方案数据透视表 245

Chapter12数据的预测分析 248

12.1 使用直线法预测销售成本 248

12.1.1知识点及效果图 248

12.1.2操作步骤 248

1.线性拟合法预测销售成本 248

知识链接——数组公式的使用 249

2使用TREND（） 函数预测销售成本 252

12.2 使用指数法和图表法预测销售成本 253

12.2.1知识点及效果图 254

12.2.2操作步骤 254

1.使用指数法预测销售成本 254

知识链接——LOGEST（）函数 255

2.使用图表法预测销售成本 256

知识链接——利用趋势线预测数据 259

12.3 使用分析工具预测销售成本 262

12.3.1知识点及效果图 262

12.3.2操作步骤 262

1.“移动平均法”预测销售成本 263

知识链接——加载分析工具库 263

知识链接——“移动平均”分析工具 264

2.“指数平滑法”预测销售成本 265

知识链接——“指数平滑”分析工具 266

比较预测销售成本与实际销售成本的差异 269

Chapter13问卷调查分析 272

13.1 创建日常护肤品市场调查问卷 272

13.1.1知识点及效果图 272

13.1.2操作步骤 272

1.创建日常护肤品使用调查问卷 272

知识链接——添加各种表单控件 273

知识链接——设置表单控件 274

2.美化日常护肤品使用调查问卷 277

知识链接——插入背景 277

13.2 调查结果统计 278

13.2.1知识点及效果图 278

13.2.2操作步骤 278

1.创建资料库 278

2.设置自动接收问卷结果 279

知识链接——删除数据有效性 281

知识链接————VLOOKUP（函数 282

3调查结果统计 283

知识链接——选择性粘贴值和格式 283

13.3 问卷调查结果综合分析 284

13.3.1知识点及效果图 284

13.3.2操作步骤 284

1.我国消费者的基本资料结构分布情况 284

2.消费者肤质状况分析 288

3.各类护肤品市场拥有量分析 289

知识链接——SUMPRODUCT（）函数 291

知识链接——创建柱状-折线双轴图 292

4.主要品牌市场占有率分析 293

知识链接——创建并设置动态饼图 294

5.销售渠道分析 296

6购买因素分析 296

品牌理念的理解率比较 298

Chapter14数据的抽样分析 300

14.1 抽样调查员工的性别构成 300

14.1.1知识点及效果图 300

14.1.2操作步骤 300

1设置公式指代员工性别 300

知识链接——使用IF（）函数指代数字 301

2.随机抽样员工性别 301

知识链接——RAND（）函数 302

3.变换抽样性别的数据 302

知识链接——使用 INDEX（）函数计算数组公式 303

14.2 抽样调查员工的年龄构成 304

14.2.1知识点及效果图 304

14.2.2操作步骤 304

1随机抽样员工年龄 304

知识链接——随机抽样公式的应用 304

2.创建频数分布表 306

知识链接——FREQUENCY（）函数 306

3.创建各年龄段员工分布图 307

14.3 通过抽样分析工具分析员工的工资 310

14.3.1知识点及效果图 310

14.3.2操作步骤 310

1.通过抽样统计法分析员工工资 310

知识链接——抽样分析工具 311

2.通过抽样回归分析企业员工的工资 314

知识链接——回归分析工具 316

置信工资区间 318

Chapter15企业销售统计与图表分析 320

15.1 按商品类别进行统计分析 320

15.1.1知识点及效果图 320

15.1.2操作步骤 320

1.按商品类型进行分类汇总 320

知识链接——移动和复制工作表 321

2.创建按类别统计销售额图表 323

知识链接——用汇总结果创建簇状柱形图 323

3.创建销售量结构分析图表 325

知识链接——用汇总结果创建分离型三维饼图 325

15.2 铵销售员和商品型号进行统计分析 327

15.2.1知识点及效果图 327

15.2.2操作步骤 327

1.创建按销售员分析工作表 328

知识链接——SUMPRODUCT（）函数 328

2.创建各销售员销售各类产品统计图表 329

知识链接——创建并设置簇状柱形图 329

3.创建各销售员销售业绩比例图表 330

知识链接——创建并设置分离型三维饼图 330

15.3 日销售额走势分析 332

15.3.1知识点及效果图 332

15.3.2操作步骤 332

1.对销售类别进行自定义排序 332

知识链接——自定义排序 333

2.对日销售记录表进行分类汇总 334

知识链接——嵌套分类汇总 335

3.创建日总销售额和总销售量走势图 336

知识链接——手动设置折线图 338

按商品类型统计数据 341

Chapter16设备采购情况统计与分析 344

16.1 企业采购清单的汇单求解 344

16.1.1知识点及效果图 344

16.1.2操作步骤 344

各类用品购买成本的单级分类汇总 344

2.各采购日期内采购数据的二级分类汇总 346

3.使用分类汇总函数对采购数据进行统计 348

知识链接——删除分类汇总 349

知识链接——利用SUBTOTAL（）函数求和 349

知识链接——创建组 351

16.2 各用品采购数据的透视分析 352

16.2.1知识点及效果图 352

16.2.2操作步骤 352

1.创建采购数据透视表 353

2设置采购数据透视表 354

4知识链接——在区域间移动字段 354

知识链接——选择值显示方式 356

知识链接——设置透视表数字格式 356

3.美化采购数据透视表 357

16.3 各用品采购数据的透视图描述 358

16.3.1知识点及效果图 358

16.3.2操作步骤 358

1.创建采购数据透视图 358

2.设置采购数据透视图 359

知识链接——堆积柱形图的应用 360

知识链接——更改坐标轴数字格式 361

知识链接——在透视图中设置筛选 363

办公用品数量透视图 364