2022年最新Python大数据之Excel基础

Python大数据之Excel基础

数据引用

引用当前工作表数据

•在B2单元格中输入”=“

•点击要引用的单元格或范围

引用当前工作表数

•使用Enter键结束键入，E2单元格内即引用了B2单元格内的数据

•此时，B2单元格为被引用单元格，E2单元格为引用单元格，被引用单元格修改，引用单元格同样变化。

引用其他工作表数据

•在E1单元格中，输入”=“

注意：只能针对单个单元格的引用

引用其他工作表数据

•点击另外一张数据表，在该表中找到要引用的数据，选中对应单元格即可。

与当前工作表引用相同，被引用单元格修改，引用单元格同样变化。

数据清洗

数据去重

用删除重复项功能

删除重复项是Excel提供的数据去重功能，可以快速删除重复项。

•选中要计算的区域

•在数据菜单下点击删除重复值按钮

•选择要对比的列，如果所有列的值均相同则删除重复数据

•点击确定，相容内容则被删除，仅保留唯一值

条件格式删除重复项

使用排序的方法删除重复项有一个问题，当数据是一串编码时，依然难以用肉眼看出重复的编码。

用条件格式可以自动找出重复的数据，并手动删除。

数据->删除重复项->选择删除条件

缺失值处理

三种处理缺失值的常用方法

1.填充缺失值，一般可以用平均数/中位数/众数等统计值，也可以使用算法预测。

中位数：是指将数据按大小顺序排列起来，形成一个数列，居于数列中间位置的那个数据。

众数：众数是指一组数据中出现次数最多的那个数据，一组数据可以有多个众数,也可以没有众数。

2.删除缺失值，如果数据缺失比例过高，可以考虑删除，比如某一列数据>50%都是缺失，可以考虑删除这一列。

3.忽略默认值，不去处理

用平均值填充缺失值

•选择B列数据，计算平均值

•将平均值单独复制一行（选择值粘贴），务必复制，否则将会出现循环引用。

循环引用：A单元格中的公式应用了B单元格，B单元格中的公式又引用了A

•Ctrl+G唤出定位菜单，选的定位空值，找到B列的所有空值

•应用平均值数据，按住Ctrl+Enter同时填充所有缺失值位置

数据加工

数据计算

1.简单计算

在Excel中，使用函数要学会为单元格“命名”。单元格名称加上运算符号可以进行单元格数值的简单计算。

如第B列的第3个单元格，名称为“B3”。

常用的运算符与我们数学中使用的完全一致，加（+），减（-），乘（*），除（/）

那么A1单元格与A2单元格数据之和的计算公式，就为“=A1+A2”。

1.常用函数计算

使用函数计算数据，需要名称区域单元格的命名方法。

如A1单元到B6单元格区域，命名方法是在两个单元格名称中间加“：”号，写法为“A1:B6”。

用函数计算数据，公式写法如下：英文“=”号加函数再加数据区域。

如计算A1单元到B6单元格区域的和，求和函数为SUM，那么公式写法为：=SUM（A1:B6）。

举例：

Excel【公式】选项卡中提供了常用函数的快捷插入，在记不住常用函数的前提下，可以通过插入的方式进行

数据转换

1.数据分类

使用VLOOKUP进行数据分组，要设置一个条件区域，目的是告诉函数，用什么依据来为数据进行分组。

2.数据重组

根据数据分析目标的不同，所需要的数据项目也不同。在数据分析时，会将所有可能用到的数据都统计到一起。

此时可能出现数据多余、数据项目符合需求等情况，此时就需要重新组合现有数据。

输入: =vlookup会自动弹出函数vlookup，用鼠标单机函数，不要enter回车。然后输入三个参数（数据，规则，返回结果列）

3.数据合并

数据拆分是指将一列数据分为多列，而数据合并是指将多列数据合并为一列。

例如将省份列、城市列数据，合并为省份+城市列数据,又例如，将年份、月份数据合并为年龄+月份数据。

在进行数据合并时，需要灵活使用逻辑连接符和文本转换函数：

在使用逻辑连接符和函数合并数据时，逻辑符与函数可以联合使用。

并且可能出现合并的数据文字表述不清晰，需要添加个别字词连接的情况。

此时可以将字词放在英文双引号中进行合并。

输入： conca自动提示，选择第一个字符串合并

选择要合并的字符串用英文逗号分隔，额外添加的字符串也用逗号分隔，用英文单引号或者双引号包起来

保留原百分号，需要用到文本的格式化

数据排序

按数值大小排序

对数据进行简单排序的方法是，选中数据的字段单元格，单击【升序】或【降序】按钮。

填充序号，此时数据已经排序，只需要在第一个单元格输入”1”，再用拖动复制的方法，即可快速填充排名。

按颜色排序

在数据分析前期，可以将重点数据标注出来，如改变单元格填充底色、改变文字颜色。

然后在分析过程中，可以将分散在数据表不同位置的重点数据再集中进行查看。

此时可以通过单元格底色、文字颜色进行排序。

按字母笔/画排序

数据分析的情况各有各的不同，有时需要排序的对象并不是数据，而是文字或英文字母。可以通过笔画和字母的方式进行排序。

数据筛选

普通筛选

对表格数据进行筛选，需要先进入筛选模式。

方法如左下图所示，选中第一行的某个单元格，单击【开始】选项卡下【排序和筛选】菜单中的【筛选】按钮。此时第一行的字段名称单元格会出现三角形按钮，通过该按钮可以实现筛选操作。

自定义筛选

普通筛选只能按照一种标准进行筛选，如果需要筛选出满足两个条件的数据，就需要用到自定义筛选。

Excel图表类型

为了揭示数据规律

为了有说服力、促进沟通

专业的图表可以展示专业素养

了解有哪些图表类型

柱状图

折线图

饼图

面积图

雷达图

Excel图表使用

图表的创建方式

图表的数据源一般是统计汇总表或者是数据量比较少的明细表

根据数据源的不同，基础图表创建的方法有2种：

1.利用固定数据区域创建图表，即根据工作表中某个固定的数据区域创建图表

2.利用固定常量创建图表，即创建图表的数据为固定的常量数据

利用固定数据区域创建图表

1.利用连续区域的所有数据

使用工作表中连续区域的所有数据，只需单击该数据区域的任一单元格，通过插入图表命令插入图表即可

选定部分数据

先选择建表要用到的数据，再通过插入图表命令插入选定类型的图表

利用固定常量创建图表

•选中任意单元格，点击插入标签，选择合适类型的图表

•此时会插入一个空白的图表，我们选择图表，切换图表设计标签，点击选择数据图标。

•选择添加按钮，添加数据源

输入坐标名称和数据。

•选择要在图表上显示的数据信息，点击编辑对周标签进行编辑

点击确定，生成图表

图表介绍

图表创建完成后。用户如果发现创建的图表与实际需求不符，还可以对其进行适当的编辑

更改图表类型

1.图表创建完成后，发现图表类型不能满足可视化分析的需求，可以更改图表的类型

在图表上右键点击，唤出菜单，选择更改图表类型。

•选择你要修改的图表类型，点击确定，图表类型更换完成

1.有些情况下，需要把某个数据系列设置为另一种图表类型

•同样右键点击，唤出下拉菜单，点击更改图表类型。

1.增加数据系列

通过图表设计中的选择数据对话框，重新选择数据

•选中所要添加数据系列的图表

编辑数据系列

•右键或切换到图表设计标签，点击选择数据图标

点击向上箭头，重新框选数据区域。

•此时，新选中的数据系列已经在列表当中，点击确定，完成系列增加。

1.当然，还有一种更简便的方法通过ctrl+c ctrl+v 快捷键添加数据列

•鼠标选中要添加的数据序列，按ctrl+c

选中图表，按ctrl+v

并不是所有图表都需要图例，图表上一般默认带有图例，可以对它进行删除/添加/修改位置等操作

•可以通过菜单操作，也可以通过鼠标拖动选择

除了饼图和圆环图外，其它的标准图图表一般至少有两个坐标轴

新创建的图表默认没有坐标轴标题，添加坐标轴标题可以使坐标轴意义更加明确

•通过图表设计选项卡 —> 添加图表元素 —> 坐标轴标题选项 可以设置坐标轴标题

添加数据标签

1.数据标签是指图表中显示图标有关信息的数据。

•选择图表设计标签，选择添加图表元素标签—>数据标签—>最佳匹配，可以自动适配数据标签。

1.添加的数据标签默认都是数值，某些情况下需要用百分比等其它形式展示，可以进行修改

右键图表，唤出菜单，选择设置数据标签格式。

•将空色框内的标签进行修改，将”值“改为”百分比“，则修改成功。

格式化图表

保证图表的完整性

一个完整的图表必须包含以下基本元素：图表标题、数据系列、图例、坐标轴、数据单位

格式化图表区/绘图区

图表区格式的设置主要包括字体、背景填充、边框、大小、属性等

格式化图表标题、坐标轴标题、图例

图表标题、坐标轴标题和图例的设置比较简单，一般包括字体、边框、对齐方式、位置等的设置

格式化坐标轴

坐标轴包括分类轴和数值轴，设置的项目包括字体，线条，填充，对齐方式，坐标轴选项等

格式化数据序列

数据序列需要格式化的内容可能包括：①设置边框/填充色 ②分类间距和重叠比例 ③坐标轴位置

数据标签主要包括标签内容、标签位置、字体、对齐等

Excel数据透视表

数据透视表对原始数据的要求

所有数据在一张表里

透视表的原始数据需要放在一张工作表里，而不是分多张工作表放置。

如果数据是按月份/品类/规格放在不同的工作表，将先将不同工作表合并到同一张表中再建立数据透视表

数据必须是一维表格，不是二维表

数据透视表的原始数据应该是一维表格，即表的第一行是字段名，下面是字段对应的数据。

二维表将无法顺利建立数据透视表。

表中不要有空值

原始数据不要出现空行/空列。如数据缺失，或为“0”值，建议输入“0”而非空白单元格。

如下图所示，表的第一行为空白，会导致透视表字段出错，表中间有空行，会导致透视表中有空值。

表中不要有合并单元格

数据透视表的原始表格中不要有合并单元格存在，否则容易导致透视分析错误

填充合并单元格办法：取消合并单元格 ->选中要填充的空单元格 ->输入公式->按Ctrl+Enter键重复操作。

数据格式要正确

原始数据表中，数据格式要正确设置，尤其是日期数据，不能设置成文本数据，否则无法使用透视表汇总统计日期数据，也不进一步使用切片器分析数据。

创建数据透视表

•使用推荐的透视表

在原始数据表中，单击【插入】选项卡下【表格】组中的【推荐的数据透视表】按钮，即可出现一系列推荐的透视表

。如下图所示，选中不同的透视表，在右边可以看到透视表的明细。

•自定义建立透视表

自定义建立透视表的方法是，单击【插入】选项卡下【数据透视表】按钮，出现如下图所示的对话框。

数据透视表字段布局

概述

透视表成功创建后，需要对字段进行合理设置，灵活更改数据展现形式，用不同的视角进行数据分析

必要时，还可以结合图表，可视化展现、分析数据。

理解字段

创建透视表后，Excel面板分为三个区域，左边是透视表显示区，右上方是字段列表区，右下方是字段设置区

字段布局步骤 ： 勾选需要的字段 => 设置字段 =>（筛选，计算方式） => 查看透视表是否符合需求

理解字段

字段列表中显示了原始数据中所有的字段，在这里可根据需求勾选需要的字段。

如需要分析不同业务员不同商品的销量，那么就需要勾选“业务员”、“品名”、“销量”三个字段。

如需要分析不同业务员，不同商品类别的销售额，就需要勾选“业务员”、“类别”、“金额（元）”三个字段。

理解字段

字段勾选完后，就需要对字段进行设置。

字段设置有以下两个要点：即，透视表的列和行分别显示什么数据、数据的统计方式是什么。

字段设置

•移动字段

首先，字段可以从字段列表中直接拖拽添加到下方区域。如下面两张图所示，左图从字段列表中选中字段，往下拖动，拖动到如右图所示的区域，再松开鼠标，就完成了字段添加。

字段设置

其次，添加到下方的字段，可以通过拖拽的方式进行调整。

如左下图所示，“日期”在【行】区域内，选中“日期”进行拖拽，可以拖动到【列】区域内。

字段设置

•设置字段的值

透视表是一种可以快速汇总大量数据的表格。在透视表的字段设置区域，【值】区域内的字段会被进行统计

默认情况下统计方式是求和。如将“金额（元）”字段放到该区域内，就会对销量数据进行求和计算。

最终效果

分析不同业务员不同商品的销量

分析不同业务员，不同商品类别的销售额

添加数据透视图

添加透视图的方法：选中透视表区域的单元格，在【数据透视表分析】选项卡下【工具】组中选择【数据透视图】

数据透视图内容筛选

数据透视图是动态图表，可以通过坐标轴和图例附近的下拉菜单调整图表内容

数据透视图是动态图表，可以通过坐标轴和图例附近的下拉菜单调整图表内容

将数据透视图变成普通图表

数据透视图会随着数据透视表数据变化而变化，如需讲数据透视图变成普通静态图，只需将透视表复制一份，删除原始数据，透视图即可变成图通图表