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

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

Python大数据之Excel基础


数据引用


引用当前工作表数据


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


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


image-20220809154333560.png


引用当前工作表数


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


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


image-20220809154437072.png


引用其他工作表数据


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


image-20220809154514993.png


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


引用其他工作表数据


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


image-20220809154545138.png


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


数据清洗


数据去重


用删除重复项功能


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


•选中要计算的区域


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


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


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


c6aee06e801b70982f0794c4662f4d8c.png


条件格式删除重复项


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


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


image-20220809155016083.png


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


image-20220809155039243.png


缺失值处理


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


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


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


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


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


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


用平均值填充缺失值


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


image-20220809155139410.png


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


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


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


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


image-20220809155439877.png


数据加工


数据计算


1.简单计算


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


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


image-20220809155817256.png


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


image-20220809155840845.png


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


1.常用函数计算


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


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


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


如计算A1单元到B6单元格区域的和,求和函数为SUM,那么公式写法为:=SUM(A1:B6)。


image-20220809155928869.png


举例:


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


image-20220809155959961.png


数据转换


1.数据分类


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


image-20220809160030098.png


2.数据重组


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


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


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


image-20220809160110811.png

image-20220809160119743.png


3.数据合并


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


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


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


image-20220809160210601.png


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


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


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


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


image-20220809160248603.png


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


image-20220809160259546.png


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


image-20220809160349644.png

image-20220809160404295.png


数据排序


按数值大小排序


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


image-20220809160446041.png


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


按颜色排序


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


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


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


image-20220809160909743.png


按字母笔/画排序


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


image-20220809161006365.png


数据筛选


普通筛选


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


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


image-20220809161119066.png


自定义筛选


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


image-20220809161143330.png


Excel图表类型


为了揭示数据规律


为了有说服力、促进沟通


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


了解有哪些图表类型


柱状图


折线图


饼图


面积图


雷达图


Excel图表使用


图表的创建方式


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


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


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


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


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


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


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


image-20220809161632924.png


选定部分数据


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


image-20220809161715921.png


利用固定常量创建图表


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


image-20220809161807132.png


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


image-20220809161934520.png


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


image-20220809164935857.png


输入坐标名称和数据。


image-20220809164952073.png


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


image-20220809165039877.png

image-20220809165048199.png


点击确定,生成图表


image-20220809165328599.png

image-20220809165328599.png


图表介绍


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


image-20220809165352151.png


更改图表类型


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


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


image-20220809165416617.png


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


image-20220809165455155.png

image-20220809165504215.png


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


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


image-20220809165534842.png


1.增加数据系列


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


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


image-20220809165551438.png


编辑数据系列


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


image-20220809165700113.png


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


image-20220809165721540.png


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


55b403de597f916b0da63759fd1dcd65.png


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


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


image-20220809165849419.png


选中图表,按ctrl+v


image-20220809165918695.png


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


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


image-20220809165942584.png


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


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


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


image-20220809170001310.png


添加数据标签


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


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


image-20220809170036835.png


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


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


image-20220809170052925.png


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


image-20220809170120523.png


格式化图表


保证图表的完整性


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


image-20220809170236659.png


格式化图表区/绘图区


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


37ea81cc0de65b7909f3cf157c2b85ff.png


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


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


ec7fb4af0e336844ccaf90c88ea2b044.png


格式化坐标轴


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


image-20220809170606351.png


格式化数据序列


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


image-20220809170642373.png


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


8931a4e809ea7f73361e32c7dd4bbfb2.png


Excel数据透视表


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


所有数据在一张表里


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


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


image-20220809171030068.png


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


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


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


f1d90a8d0cb216aac80008d4fb7fb517.png


表中不要有空值


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


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


image-20220809171152298.png


表中不要有合并单元格


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


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


687dd83ab6a42f2c18c5e5d5a8a674c4.png


数据格式要正确


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


8e7d6b7722d0383b9e8155ca01b56a57.png


创建数据透视表


•使用推荐的透视表


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


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


image-20220809171319175.png


•自定义建立透视表


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


image-20220809171351087.png


数据透视表字段布局


概述


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


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


2776648b68d34dc3c09c67472d00204d.png


理解字段


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


字段布局步骤 : 勾选需要的字段 => 设置字段 =>(筛选,计算方式) => 查看透视表是否符合需求


cee83db0e0ae73bbfe13b9a6943ab30b.png


理解字段


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


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


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


image-20220809171518633.png


理解字段


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


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


f3f5f07422f1d7fe5d8389a2cdd4a98c.png


字段设置


•移动字段


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


image-20220809171612471.png


字段设置


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


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


3a20de44792e5a3538f62edd472754fd.png


字段设置


•设置字段的值


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


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


405fd6658a6813319f868bc69a89bd22.png


最终效果


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


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


bcc754b6167a3ec21719a222ff979381.png


添加数据透视图


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


af48e0b2975aceafe09ed6f6e5358c9d.png


数据透视图内容筛选


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


0362c7bc214b01dd54d442fa14b26b4a.png


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


06c7206e6f32ea9374c7f891dabb9010.png


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


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


0aaad9add602e43cf0b452485bb6f8dd.png

相关实践学习
简单用户画像分析
本场景主要介绍基于海量日志数据进行简单用户画像分析为背景,如何通过使用DataWorks完成数据采集 、加工数据、配置数据质量监控和数据可视化展现等任务。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
目录
相关文章
|
25天前
|
数据采集 机器学习/深度学习 数据可视化
【办公自动化】使用Python批量处理Excel文件并转为csv文件
【办公自动化】使用Python批量处理Excel文件并转为csv文件
28 0
|
1天前
|
自然语言处理 安全 Unix
一键式Excel分词统计工具:如何轻松打包Python脚本为EXE
一键式Excel分词统计工具:如何轻松打包Python脚本为EXE
5 0
|
4天前
|
数据挖掘 数据处理 Python
使用Python自动化处理Excel数据
【2月更文挑战第4天】在现代社会,数据处理已经成为了一项重要的任务。而Excel作为一款广泛应用于数据处理的软件,已经成为了许多人的首选。不过,对于大规模的数据处理任务,手动进行Excel操作可能是低效的。本文将介绍如何使用Python编程语言来自动化处理Excel数据。
21 9
|
6天前
|
分布式计算 并行计算 大数据
Python多进程在数据处理和大数据分析中的应用
Python多进程在数据处理和大数据分析中的应用
|
6天前
|
存储 数据挖掘 数据处理
Python与Excel的交互:读写Excel文件和处理数据
Python与Excel的交互:读写Excel文件和处理数据
|
20天前
|
数据采集 数据可视化 大数据
处理大数据:Python 与数据库的结合
在处理大数据的领域中,Python 凭借其强大的数据处理和分析能力,成为了与数据库结合的理想选择。Python 提供了丰富的数据库接口和工具,可以与各种主流的关系型数据库和 NoSQL 数据库进行高效交互。本文将探讨 Python 在处理大数据方面与数据库结合的一些关键技术和应用。
|
25天前
|
数据采集 机器学习/深度学习 数据挖掘
【办公自动化】用Python在Excel中查找并替换数据
【办公自动化】用Python在Excel中查找并替换数据
23 0
|
25天前
|
机器学习/深度学习 数据可视化 数据挖掘
【办公自动化】使用Python一键提取PDF中的表格到Excel
【办公自动化】使用Python一键提取PDF中的表格到Excel
24 0
|
1月前
|
Java Apache Spring
springboot如何导出Excel某个表的表字段以及字段类型
springboot如何导出Excel某个表的表字段以及字段类型
20 0