Excel数据分析超详细实战攻略 微课视频版

目录 素材下载 教程篇 第1章Excel基础 1.1Excel及其构成 1.1.1Excel及其主要用途 1.1.2Excel的基本构成 1.2工作簿的基本操作 1.2.1新建工作簿 1.2.2打开工作簿 1.2.3保存和关闭工作簿 1.2.4Excel的文件格式 1.2.5同时使用多个Excel工作簿 1.3工作表的基本操作 1.3.1新建和插入Excel工作表 1.3.2重命名工作表和设置工作表标签的颜色 1.3.3选择、移动和复制Excel工作表 1.3.4隐藏、显示和删除Excel工作表 1.3.5拆分和冻结窗口以查看复杂的工作表 1.4单元格的基本操作 1.4.1单元格和单元格区域的选择 1.4.2插入和删除单元格 1.4.3单元格的合并、拆分与分列 1.4.4调整行高和列宽 1.4.5隐藏与显示Excel行和列 1.5应用举例 习题 第2章数据的输入与验证 2.1Excel的数据类型 2.1.1数值类型 2.1.2文本类型 2.1.3日期类型 2.1.4逻辑类型 2.2基本数据的输入 2.2.1数值数据的输入 2.2.2文本数据的输入 2.2.3日期和时间数据的输入 2.2.4公式的输入 2.2.5在多张工作表中输入或编辑相同的数据 2.2.6插入批注 2.3使用记录单输入数据 2.4数据的复制 2.5数据序列和数据的填充 2.5.1Excel的数据序列 2.5.2自定义填充序列 2.5.3序列的填充方法 2.5.4使用Ctrl+Enter键填充相同的数据 2.5.5使用RAND和RANDBETWEEN函数产生仿真 数据 2.5.6神奇的快速填充 2.6外部数据的传统导入方法 2.6.1启用传统导入方法 2.6.2导入文本文件 2.6.3导入Access数据库表 2.6.4导入其他来源的数据 2.7单元格输入的约束 2.7.1单元格的数据有效性规则 2.7.2设置数值的输入范围 2.7.3设置日期或时间有范围限制 2.7.4设置文本的有效长度 2.7.5设置输入为序列值(下拉列表框) 2.7.6设置输入为自定义验证 2.7.7数据的有效性审核 2.7.8数据验证应用举例 习题 第3章数据的编辑与格式化 3.1数据的编辑 3.1.1修改单元格的内容 3.1.2清除单元格的内容 3.1.3单元格数据的分行处理 3.1.4单元格数据的分列处理 3.1.5多列数据合并成一列 3.1.6多行数据合并成一行 3.1.7删除重复的行 3.2设置单元格格式 3.2.1字体 3.2.2对齐 3.2.3数字格式 3.2.4边框 3.2.5填充 3.2.6单元格样式 3.2.7自动套用表格格式 3.2.8复制和删除格式 3.2.9设置文档主题 3.2.10格式化应用举例 3.3数字格式 3.3.1预定义数字格式 3.3.2自定义数字格式 3.3.3数字格式化应用举例 3.4条件格式化 3.4.1设置条件格式 3.4.2新建条件格式 3.4.3删除条件格式 3.4.4管理条件格式 3.4.5使用“快速分析”工具栏创建条件格式 3.4.6使用公式自定义条件格式 3.4.7条件格式化应用举例 3.5页面布局和打印设置 3.5.1常用页面设置 3.5.2页眉和页脚 3.5.3打印区域、换页和打印顺序 3.5.4复制页面设置到其他工作表 3.5.5工作簿视图和自定义工作簿视图 3.5.6打印预览和打印 习题 第4章公式和函数的基本使用 4.1Excel公式 4.1.1引例 4.1.2公式的组成和特点 4.1.3公式的输入和编辑 4.2公式中的常量 4.3单元格的引用 4.3.1A1引用样式 4.3.2R1C1引用样式 4.3.3单元格的名称引用 4.3.4三维引用样式 4.3.5绝对引用与相对引用 4.4公式中的运算符 4.4.1算术运算符 4.4.2比较运算符 4.4.3文本连接运算符 4.4.4引用运算符 4.4.5运算符的优先级 4.5函数 4.5.1函数概述 4.5.2内置工作表函数 4.5.3用户自定义函数 4.5.4在公式中使用函数 4.6公式审核和公式求解 4.6.1使用公式和函数产生的常见错误信息 4.6.2公式审核 4.6.3公式求值 4.6.4使用监视窗口监测单元格 4.7数组和数组公式 4.7.1数组 4.7.2数组公式 4.7.3数组常量 4.7.4数组公式的基本运算 4.7.5数组公式的函数 4.8逻辑函数和信息函数 4.8.1条件判断函数IF 4.8.2条件判断函数IFS 4.8.3逻辑函数 4.8.4信息函数 4.8.5错误判断函数 4.8.6CELL函数 习题 第5章使用数学和统计函数处理数据 5.1计数、求和与平均 5.1.1计数与求和函数 5.1.2COUNT、COUNTA和COUNTBLANK函数 5.1.3COUNTIF和COUNTIFS函数 5.1.4FREQUENCY函数 5.1.5SUM函数 5.1.6SUMIF和SUMIFS函数 5.1.7SUMPRODUCT和SUMSQ函数 5.1.8AVERAGE、AVERAGEIF和AVERAGEIFS 函数 5.1.9TRIMMEAN函数 5.2统计函数 5.2.1MAX、MIN、MAXA和MINA函数 5.2.2LARGE和SMALL函数 5.2.3RANK、RANK.AVG和RANK.EQ函数 5.2.4PERCENTRANK、PERCENTRANK.INC和 PERCENTRANK.EXC函数 5.2.5PERCENTILE和QUARTILE函数 5.2.6MEDIAN和MODE函数 5.2.7STDEV和VAR函数 5.2.8SUBTOTAL函数 5.3数学函数和三角函数 5.3.1舍入和取整函数 5.3.2常用数学函数 5.3.3随机函数 5.3.4三角函数 习题 第6章使用日期和文本函数处理数据 6.1日期和时间函数 6.1.1获取日期和时间 6.1.2获取日期和时间数据的信息 6.1.3计算两个日期之间的时间差 6.1.4计算若干年、月、日之前或之后的日期 6.1.5计算时间 6.1.6日期函数应用举例 6.2文本函数 6.2.1文本字符串的比较 6.2.2文本字符串的拼接 6.2.3文本字符串的长度 6.2.4从文本字符串中提取子字符串 6.2.5查找和替换文本 6.2.6大小写字母的转换 6.2.7全角和半角字母的转换 6.2.8删除文本中的空格和非打印字符 6.2.9字符和代码的转换 6.2.10将文本转换为数值 6.2.11将数值格式化为文本 6.2.12其他文本函数 6.2.13文本函数应用举例 习题 第7章使用财务函数处理数据 7.1财务函数概述 7.2投资计算函数 7.2.1FV函数与投资未来值计算 7.2.2PMT等函数与相应计算 7.2.3PV函数与年金的现值计算 7.2.4NPER函数与期数计算 7.2.5RATE函数与利率计算 7.2.6RRI函数与投资回报率计算 7.2.7NPV函数与投资净现值计算 7.2.8IRR函数与内部收益率计算 7.2.9投资计算函数综合应用举例 7.3折旧计算函数 7.3.1SLN函数与年限平均法 7.3.2DB函数与余额递减法 7.3.3DDB函数与双倍余额递减法 7.3.4SYD函数与年限总和折旧法 7.3.5VDB函数与可变余额递减法 7.3.6折旧计算函数综合应用举例 习题 第8章使用查找与引用函数查找数据 8.1使用CHOOSE函数从列表中选择数据 8.2使用VLOOKUP函数在垂直方向上查找数据 8.3使用HLOOKUP函数在水平方向上查找数据 8.4使用LOOKUP函数查找数据 8.5使用INDEX和MATCH函数查找数据 8.5.1MATCH函数 8.5.2INDEX函数 8.5.3MATCH和INDEX函数的组合使用 8.6使用OFFSET和MATCH函数查找数据 8.7使用INDIRECT、MATCH和ADDRESS函数查找数据 8.7.1ADDRESS函数 8.7.2INDIRECT函数 8.7.3MATCH、ADDRESS和INDIRECT函数的组合使用 8.7.4其他查找与引用函数 8.8查找和引用函数应用示例 8.8.1职工津贴查询 8.8.2学生成绩查询器 8.8.3反向查询 8.8.4双条件查询 8.8.5加班补贴信息查询 8.8.6隔行提取数据 8.8.7随机排位 8.8.8打印工资条 8.8.9学生成绩等级查询和判断 习题 第9章其他工作表函数 9.1度量单位转换 9.1.1CONVERT函数 9.1.2CONVERT函数应用举例 9.2数制转换 9.2.1数制转换函数 9.2.2数制转换函数应用举例 9.3复数运算函数 9.3.1COMPLEX函数与复数 9.3.2复数的运算 9.3.3其他复数函数 9.4数据库函数 9.4.1数据库函数概述 9.4.2数据库函数参数说明 9.4.3数据库函数应用举例 9.4.4数据库函数使用说明 习题 第10章数组公式的高级应用 10.1数组公式用于统计 10.1.1统计包含错误的单元格区域 10.1.2统计单元格区域中错误单元格的个数 10.1.3统计单元格区域中n个*大值、*小值 10.1.4统计单元格区域中除0外的单元格的平均值 10.1.5统计单元格区域中数组取整后的和 10.1.6统计单元格区域中每隔n个数的和 10.1.7整数的各数位求和 10.2数组公式用于查找和比较 10.2.1在单元格区域中查找指定项目 10.2.2在单元格区域中查找极值数据的位置 10.2.3在单元格区域中查找长度*大的文本 10.2.4在单元格区域中查找与指定值*接近的值 10.2.5返回行或列中*后位置的值 10.2.6比较两个单元格区域中数据的异同 10.3数组公式用于数据筛选和处理 10.3.1单条件数据筛选 10.3.2多条件数据筛选 10.3.3数据反序 10.3.4数据动态排序 10.3.5筛选非重复项 10.4数组公式的综合应用举例 10.4.1VLOOKUP批量查找 10.4.2提取不重复值 10.4.3条件计数 10.4.4多条件计数 10.4.5多条件求和 10.4.6多条件求和以及不重复数据计数 10.4.7足球赛连胜场次 第11章公式和函数的综合应用实例 11.1数学函数的综合应用 11.1.1人民币面额张数 11.1.2奇偶数个数以及奇偶数之和 11.1.3学生信息统计 11.2文本函数的综合应用 11.2.1生成大写字母 11.2.2身份证信息的抽取和计算 11.2.3日记账报表 11.3日期与时间函数的综合应用 11.3.1推算节日日期 11.3.2统计停车费用 11.3.3统计日期信息 11.3.4制作万年历 11.4统计函数的综合应用 11.4.1运动会排名奖牌 11.4.2重复和不重复信息的抽取和计算 11.5公式和函数在数据验证中的应用 11.5.1编号格式、小数位数、整数和日期范围 11.5.2禁止重复输入和性别限制 11.5.3限制产品类别编码 11.5.4同一订购日期不允许出现相同产品ID 11.5.5动态引用信息 11.6公式和函数在条件格式中的应用 11.6.1标记重复数据 11.6.2标识各科成绩的*高分 11.6.3标识至少两科不及格的学生 11.6.4分类间隔底纹 11.6.5连续排列分类间隔底纹 11.6.6标识连胜3场及3场以上的球队得分 第12章数据的组织和管理 12.1使用Excel表格管理数据 12.1.1Excel表格的基本概念 12.1.2创建Excel表格 12.1.3表格的格式化 12.1.4表格的编辑 12.1.5表格中重复数据的处理 12.1.6表格的汇总 12.1.7表格的名称与引用 12.1.8将表格转换为区域 12.1.9删除表格及其数据 12.2数据的筛选 12.2.1自动筛选 12.2.2高级筛选 12.2.3切片器 12.2.4清除筛选和取消筛选 12.3数据的排序 12.3.1排序规则 12.3.2自动排序(单列) 12.3.3高级排序和多列排序 12.3.4排序选项： 中文按拼音排序还是按笔画排序 12.3.5按自定义序列排序 12.3.6按颜色排序 12.3.7按条件格式图标排序 12.3.8按辅助列排序 12.4数据的分类汇总 12.4.1分类汇总的含义 12.4.2分类汇总的方法 12.4.3分类汇总的嵌套和级 12.4.4取消分类汇总 12.5数据的分级显示管理 12.5.1创建和取消分组 12.5.2显示和隐藏分级按钮 12.6链接与合并数据 12.6.1链接其他工作簿数据 12.6.2合并计算 习题 第13章使用数据透视表分析数据 13.1数据透视表的基本概念 13.1.1数据透视表概述 13.1.2数据透视表的数据源 13.1.3分类字段和汇总字段 13.2创建和删除数据透视表 13.2.1自动创建数据透视表 13.2.2手工创建数据透视表 13.2.3删除数据透视表 13.3数据透视表的设计和格式化 13.3.1自动套用数据透视表样式 13.3.2设置数据透视表样式选项 13.3.3设置数据透视表布局选项 13.3.4数据透视表的内容设计 13.3.5分类字段的设置 13.3.6值字段的设置： 汇总方式和显示方式 13.3.7计算字段和计算项 13.3.8设置数据透视表选项 13.3.9应用条件格式化 13.3.10清除和移动数据透视表 13.4数据透视表的分析和操作 13.4.1数据透视表中数据的筛选 13.4.2数据透视表中数据的排序 13.4.3数据透视表中数据的分组和取消分组 13.4.4查看汇总数据的明细数据 13.4.5更改数据透视表的数据源区域 13.4.6创建数据透视图 13.5数据透视表函数 习题 第14章数据的决策与分析 14.1Excel加载项 14.1.1Excel加载项概述 14.1.2查看加载项 14.1.3管理和安装加载项 14.1.4设置加载项的安全选项 14.1.5在应用商店查找安装加载项 14.2模拟运算表 14.2.1模拟运算表概述 14.2.2单变量模拟运算表 14.2.3双变量模拟运算表 14.2.4模拟运算表应用示例 14.3单变量求解 14.3.1单变量求解概述 14.3.2单变量求解运算的步骤 14.3.3单变量求解应用示例 14.4规划求解 14.4.1规划求解概述 14.4.2创建规划求解模型 14.4.3规划求解的结果报告 14.4.4规划求解应用示例 14.5方案分析 14.5.1方案分析概述 14.5.2创建方案 14.5.3合并方案 14.5.4方案总结 14.5.5方案分析和规划求解综合应用示例 14.6数据分析工具 14.6.1数据分析工具概述 14.6.2直方图统计 14.6.3描述统计 14.6.4移动平均 习题 第15章图表与数据可视化 15.1认识图表对象 15.1.1Excel图表 15.1.2Excel图表的组成部分 15.2图表的基本操作 15.2.1创建图表 15.2.2编辑图表 15.3图表的布局与格式 15.3.1快速布局Excel图表 15.3.2手动布局Excel图表 15.3.3调整数据源 15.3.4更改图表类型 15.3.5移动图表位置 15.3.6图表浮动工具栏 15.3.7自动格式化图表 15.3.8格式化图表元素 15.3.9添加形状 15.3.10重用图表格式 15.4Excel基本图表类型 15.4.1柱形图 15.4.2折线图 15.4.3饼图 15.4.4条形图 15.4.5面积图 15.4.6散点图和气泡图 15.4.7股价图 15.4.8曲面图 15.4.9雷达图 15.4.10地图 15.4.11树状图 15.4.12旭日图 15.4.13直方图 15.4.14箱形图 15.4.15排列图 15.4.16漏斗图 15.4.17瀑布图 15.4.18自定义图表模板 15.5使用图表分析线显示数据趋势 15.5.1系列线 15.5.2垂直线 15.5.3高低点连线 15.5.4涨跌柱线 15.5.5误差线 15.5.6趋势线 15.6使用迷你图显示数据趋势 15.6.1迷你图概述 15.6.2创建迷你图 15.6.3编辑迷你图 15.7Excel复杂图表的建立 15.7.1组合图 15.7.2金字塔图和橄榄形图 15.7.3甘特图 15.7.4步进图 15.7.5瀑布图 15.8动态图表 习题 第16章使用Power Pivot数据模型管理和分析数据 16.1Power系列功能概述 16.1.1自助式商业智能解决方案 16.1.2基于Excel的自助式商业智能解决方案 16.1.3基于Power BI的自助式商业智能解决方案 16.1.4加载Power系列加载项 16.1.5安装Power BI Desktop 16.2使用Power Query导入和转换数据 16.2.1Power Query概述 16.2.2创建查询 16.2.3管理查询 16.2.4使用Power Query编辑器转换和清洗数据 16.2.5合并查询 16.2.6追加查询 16.2.7加载查询数据 16.2.8导出和共享查询 16.3使用Power Pivot管理数据模型 16.3.1Power Pivot概述 16.3.2添加数据表到数据模型 16.3.3管理数据模型中表的关系 16.3.4创建计算列 16.3.5创建度量值 16.4使用数据透视表分析数据模型中的数据 16.5使用Power View实现数据可视化仪表盘 16.5.1Power View概述 16.5.2Power View准备数据 16.5.3创建Power View报表 16.5.4在Power View报表中创建表格 16.5.5把Power View表格转换为其他可视化效果 16.5.6通过筛选实现可视化交互 16.5.7通过突出显示实现可视化交互 16.5.8应用举例： 销售仪表盘 16.6使用Power Map实现三维地图可视化演示 16.6.1Power Map概述 16.6.2三维地图数据准备 16.6.3打开“三维地图”窗口 16.6.4创建和打开三维地图演示 16.6.5创建和设置三维地图演示的场景 16.6.6创建三维地图图层 16.6.7探索和分析三维地图 16.6.8导出三维地图演示视频 16.6.9应用举例： 销售额和销售利润三维地图演示 16.7使用Power BI实现自助式商业智能 16.7.1Power BI Desktop概述 16.7.2使用Power Query 16.7.3使用Power Pivot 16.7.4使用可视化对象创建报表 习题 第17章宏与VBA程序入门 17.1Excel宏与VBA概述 17.2宏的录制和运行 17.2.1录制宏 17.2.2宏的管理 17.2.3宏的安全性配置 17.2.4宏的选择 17.3VBA编辑器 17.3.1VBA编辑器界面 17.3.2VBA工程 17.3.3设计器/编辑器 17.3.4运行和调试工具 17.3.5帮助系统 17.4VBA语言基础 17.4.1标识符和关键字 17.4.2变量、常量和数据类型 17.4.3表达式和运算符 17.4.4VBA语句概述 17.4.5模块、过程和函数 17.4.6变量的作用域 17.4.7VBA内置函数 17.4.8选择结构 17.4.9循环结构 17.4.10数组 17.5Excel对象模型 17.5.1对象、属性、方法和事件 17.5.2Excel对象模型概述 17.5.3Application对象 17.5.4Workbook对象 17.5.5Worksheet对象 17.5.6Range对象 17.5.7其他常用的Excel对象 17.6VBA窗体和控件 17.6.1VBA窗体概述 17.6.2创建VBA窗体的一般步骤 17.6.3窗体和常用控件 17.6.4用户窗体应用案例 习题 第18章数据的保护与共享 18.1工作簿的保护 18.1.1设置工作簿的打开权限和修改权限 18.1.2保护工作簿的结构 18.2工作表的保护 18.2.1激活“保护工作表”状态 18.2.2取消“保护工作表”状态 18.2.3取消单元格的“锁定”属性以允许修改 18.2.4设置单元格的“隐藏”属性以隐藏公式 18.2.5设置“允许用户编辑区域” 18.2.6设置激活“保护工作表”状态时允许的操作 18.2.7应用实例 18.3Excel协作概述 18.3.1Excel协作方法 18.3.2用户标识 18.3.3为协作准备工作簿 18.3.4工作簿的分发 18.4工作簿共享和跟踪修订 18.4.1多用户同时打开未共享的工作簿 18.4.2共享工作簿以支持多用户同时编辑 18.4.3取消共享工作簿 18.4.4多用户同时使用共享工作簿的操作流程 18.4.5使用跟踪修改的异步审阅操作流程 18.4.6使用跟踪修改的同步审阅操作流程 18.4.7设置保护共享工作簿 18.4.8取消跟踪修改 18.4.9应用实例 18.5使用Excel Web Application进行协作 18.5.1注册Office账户和登录Office 18.5.2保存工作簿到SharePoint服务器网站 18.5.3使用浏览器编辑OneDrive上的工作簿 18.6使用Inquire加载项分析和合并工作簿 18.6.1加载Inquire加载项 18.6.2使用Inquire分析工作簿 18.6.3使用Inquire比较两个工作簿 18.6.4使用Inquire查看单元格公式引用关系 18.7保护VBA源代码 习题 第19章Excel综合应用案例 19.1系统首页 19.2学生信息表 19.3信息查询维护表 19.4学生学籍卡 19.5学生成绩单 19.6统计报表 19.7系统首页的美化和保护 实验篇 实验1数据的输入与验证 实验2数据的编辑与格式化 实验3使用公式和函数处理数据 实验4数学和统计函数的应用 实验5文本函数和日期与时间函数的应用 实验6财务函数的应用 实验7查找与引用函数的应用 实验8其他工作表函数的应用 实验9公式和函数的综合应用(1) 实验10公式和函数的综合应用(2) 实验11数据的组织和管理 实验12使用数据透视表分析数据 实验13数据的决策与分析 实验14图表与数据可视化 实验15基于Power Pivot的数据分析 实验16宏与VBA程序入门 附录A习题参考解答 参考文献