弯道超车之Excel课程5

标签:
文化 |
分类: 茜¥职场女生#学无止境 |
41、整理篇——清洗单位不统一的时长
数据清洗全流程。备份数据源、处理无意义字段、规范列命名、单元格格式、删除重复值、复合属性处理、一致化处理、异常值处理、缺失值处理、数据排序。
定位和处理缺失值
(1)如何找到缺失值。
(2)处理缺失值的方法。A大标题字段缺失,人工判断和补全;B同列相同内容缺失,根据上下文判断,并批量填充;C如果缺失值难以找到来源和参考,可以使用平均值进行替代。D如果缺失值难以找到来源和参考,可以使用平均值进行替代。E如果数据缺失过多,且对分析帮助不大,可以选择删除整条数据。
定位和处理异常值
(1)什么是异常值:异常值指的是样本中某些明显偏离其余数值的个别数值。
(2)异常值如何产生:第一由于数据来源多样化,在导入和迁移的时候,单位容易混淆;第二是手工录入的时候误操作。
(3)如何找到异常值:筛选排序法和标准差法。
(4)处理异常值的方法:A手动修正和补全。B整体删除异常数据。C有一种异常值看起来反常,但真实数据可能就是这么夸张,保留,保持数据的多样性。
42、整理篇——多个数据表的合并
多个工作表合计
(1)使用快捷键alt+d+p打开窗口——多重合并计算数据区域——所需创建的报表类型——数据透视表——下一步——自定义字段——选择区域——添加——下一步——选择新工作表。
(2)Power
Query:数据——获取和转换数据——获取数据——自文件——从Excel工作薄——待合并工作表——导入——转换数据——进入Power
Query编辑器——删除Item、Kind和Hidden——取消勾选使用原始列名作为前缀——确定——将第一行用做标题——取消勾选不需要的文本——关闭并上载。
多个工作薄合并
(1)工作表名称一致:数据——获取数据——来自文件——从文件夹——找到文件夹——打开——选择组合——合并并转换数据——源名称——替换值——输入“分数工作薄”——替换为空白——确定——关闭并上载。
(2)
插件。方方格子,有合并多表、合并多薄、拆分工作表、拆分工作薄等功能。
43、分析篇——快速分析(上)
快速分析在哪里?1选择任何一个数据区域,鼠标拖动后,右下角会出现“快速分析”的按钮。2如果没有出现“快速分析”的按钮,则需要在[文件-选项-常规]里勾选[选择时显示快速分析选项]。3选中数据区域任意一个单元格,按下Ctri+Q。
条件格式。在[快速分析]里,条件格式叫[格式化],是从[条件格式]的功能中挑选了五个最常用的功能简化后放在这里。
简易图表——簇状柱形图。图表元素/样式与颜色/图表筛选器/切换行列/选择数据。
44、分析篇——快速分析(下)
汇总,Ctrl+A选中数据区域,点击右下角的[快速分析],选择汇总,就可以看到数据的汇总方式,包括求和、平均数、计数、汇总百分比和汇总。
表格,Ctrl+A选中数据区域,点击右下角的[快速分析],一个是[超级表],一个是[空白数据透视表]。
迷你图,占地面积小,可以让枯燥的数字立马变得灵动。
45、分析篇——模拟分析(上)
单变量求解,根据所提供的目标值,将引用单元格的值不断调整,直到达到所需要求的公式的目标值时,变量的值才确定。
模拟运算表,通过一个或多个模拟运算设定,就用EXCEL本身公式,显示计算结果。
定投计算器,FV终值函数(Future Value)。
46、分析篇——模拟分析(下)
方案管理器,将不同方案整合并管理的预测工具。位置:[数据]-[模拟分析]-[方案管理器]。
预测工作表,利用收集到的历史数据,对未来走势做出预测,以及在面临多个项目方案时有理有据地做出决策,并对未来项目发展做出合理预估。位置:[数据]-[预测工作表]。
47、分析篇——规划求解
找到规划求解。位置:[文件]-[选项]-[加载项]-[管理Excel加载项]-[转到]-[规划求解加载项]-确定。[数据]选项卡会出现新的项[分析],其中就有[规划求解]。
规划求解的用法。1建立公式,关联其中不确定最终数据的可变单元格和目标单元格。2点击[数据]选项卡,打开[规划求解]认识选项[设置目标]、[可变单元格]以及[遵守约束]。3更改变量,如果有多个不连续区域,用逗号隔开继续添加即可。4设置约束条件。5选择求解方法。
48、透视篇——数据透视表基础入门
建立数据透视表。选中数据区域内任意单元格-[插入]-[数据透视表]-如果数据是外来的,则选择[来自外部数据源],如果数据在本表,则选择[数据透视表]。
数据透视表初识。
1透视表:能够简单、便捷、高效地快速处理复杂海量的数据,一键进行分析、分类、汇总、对比和可视化。
2使用前准备:备份源数据、处理无意义字段、规范列命名、单元格格式处理、删除重复值、处理复合属性单元格、一致化处理、异常值处理。
3、什么时候用:数据量巨大,使用函数复杂;需要快速制作报表;数据源持续变化。
透视表字段设置。A勾选自动匹配字段。B鼠标点住数据类别,拖动到相应的字段区域。
49、透视篇——数据透视表布局和美化
硬装布局,点击[透视表区域]后,会出[透视数据表分析]和[设计]两个选择卡。包括分类汇总/报表布局/空行。
软装布局:样式与颜色/样式选项/显示/设置单元格格式。
50、透视篇——透视表的5个进阶认知
多级分类汇总,[行字段]有多个层级,并且是包含与被包含的关系。
交叉汇总,可以用来显示表中某个字段的汇总值,并将它们分组。
什么是多重汇总?行字段比较单一,列字段分别展示同一个数据的多种汇总类型。
如何在透视表中使用公式?点击[透视表分析]选项卡,找到计算功能组中的[字段项目和集],它的作用就是在透视表中使用计算公式。
如何建立快速查询器?使用GETPIVOTDATA函数。
如何1秒插入50张好名字的工作表。1新建工作簿;2选中目标数据,插入数据透视表,位置选择[现有工作表],放置在新工作簿内。3把字段拖入[筛选字段];4[数据透视表分析],点击[选项]旁边的箭头,点击[显示报表筛选项],选定要显示的[筛选页字段],确定。
后一篇:弯道超车之Excel课程6