七周成为数据分析师—Excel技巧篇

2023-10-17 07:30

本文主要是介绍七周成为数据分析师—Excel技巧篇,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

本文是《 七周成为数据分析师》的第二篇教程,如果想要了解写作初衷,可以先行阅读七周指南。温馨提示:如果您已经熟悉Excel,大可不必再看这篇文章,或只挑选部分。
上一篇文章《 七周成为数据分析师—Excel函数篇》教了大家函数,今天讲解Excel的技巧。本次讲解依然是提纲,图文部分引用自百度经验。如果有疑问或建议,可以留言给我,也可以网上搜索。内容方面照旧会补充SQL和Python。

快捷键
Excel的快捷键很多,以下主要是能提高效率。
  • Ctrl+方向键,对单元格光标快速移动,移动到数据边缘(空格位置)。
  • Ctrl+Shift+方向键,对单元格快读框选,选择到数据边缘(空格位置)。
  • Ctrl+空格键,选定整列。
  • Shift+空格键,选定整行。
  • Ctrl+A,选择整张表。
  • Alt+Enter,换行。
  • Ctrl+Enter,以当前单元格为始,往下填充数据和函数。
  • Ctrl+S,快读保存,你懂的。
  • Ctrl+Z,撤回当前操作。
如果是效率达人,可以学习更多快捷键。Mac用户的Ctrl一般需要用command替换。

格式转换
Excel的格式及转换很容易忽略,但格式会如影随形伴随数据分析者的一切场景,是后续SQL和Python数据类型的基础。
通常我们将Excel格式分为数值、文本、时间。数值常见整数型 Int和小数/浮点型 Float。两者的界限很模糊。在SQL和Python中,则会牵扯的复杂,涉及运算效率,计算精度等。文本分为中文和英文,存储字节,字符长度不同。中文很容易遇到编码问题,尤其是Python2。Win和Mac环境也有差异。大家遇到的乱码一般都属于中文编码错误。时间格式在Excel中可以和数值直接互换,也能用加减法进行天数换算。
时间格式有不同表达。例如2016年11月11日,2016/11/11,2016-11-11等。当数据源多就会变得混乱。我们可以用自定义格式规范时间。这里了解一下时间格式的概念,列举是一些较通用的范例(不同编程语言还是有差异的)。
  • YYYY代表通配的四位数年格式
  • MM代表通配的两位数月格式
  • DD代表通配的两位数日格式
  • HH代表通配的的两位数小时(24小时)格式
  • hh代表通配的两位数小(12小时制)格式
  • mm代表通配的两位数分格式
  • ss代表通配的两位数秒格式
例如2016/11/11可以写成:yyyy/MM/dd,2016-11-11 23:59:59可以写成:yyyy-MM-dd HH:mm:ss。
七周成为数据分析师Excel技巧篇
数组
数组很多人都不会用到,甚至不知道有这个功能。依旧是数据分析越往后用到越多,它类似R语言的Array和Python的List。数组由多个元素组成。普通函数的计算结果是一个值,数组类函数的计算结果返回多个值。数组用大括号表示,当函数中使用到数组,应该用Ctrl+Shift+Enter输入,不然会报错。
先看数组的最基础使用。选择A1:D1区域,输入={1,2,3,4}。记住是大括号。然后Ctrl+Shift+Enter。我们发现数组里的四个值被分别传到四个单元格中,这是数组的独有用法。
七周成为数据分析师Excel技巧篇
我们再来看一下数组和函数的应用。利用{},我们能做到1匹配a,2匹配b,3匹配c。也就是一一对应。专业说法是Mapping。
  • =lookup(查找值,{1,2,3},{"a","b","c"})
Excel的数组具体应用,大家可以搜索学习,可以提高一定的效率。但是Python的数组更为强大,我的重点就不放在这块了。

分列
Excel可以将多个单元格的内容合并,但是不擅长拆分。分列功能可以将某一列按照特定规则拆分。常常用来进行数据清洗。
七周成为数据分析师Excel技巧篇
上文我有一列地区的数据,我想要将市和区分成两列。我们可以用mid和find函数查找市截取字符。但最快的做法就是用“市”分列。SQL和Python中有类似的spilt ( )函数。
七周成为数据分析师Excel技巧篇
合并单元格
单元格作为报表整理使用,除非是最终输出格式,例如打印。否则不要随意合并单元格。一旦使用合并单元格,绝大多数函数都不能正常使用,影响批量的数据处理和格式转换。合并单元格也会造成Python和SQL的读取错误。

数据透视表
数据透视表是非常强大的功能,当初学会时惊为天人。数据透视表的主要功能是将数据聚合,按照各子段进行sum( ),count( )的运算。下图我选择我选择想要计算的数据,然后点击创建透视表。
七周成为数据分析师Excel技巧篇
此时会新建一个Sheet,这是数据透视表的优点,将原始数据和汇总计算数据分离。数据透视表的核心思想是聚合运算,将字段名相同的数据聚合起来,所谓数以类分。列和行的设置,则是按不同轴向展现数据。简单说,你想要什么结构的报表,就用什么样的拖拽方式。聚合功能有一点类似SQL中的gorup by,python中则有更为强大的pandas.pivot_table( )。
七周成为数据分析师Excel技巧篇
删除重复项
一种数据清洗和检验的快速方式。想要验证某一列有多少个唯一值,或者数据清洗,都可以使用。类似SQL中的distinct ,python中的set

条件格式
条件格式可以当作数据可视化的应用。如果我们要使用函数在大量数据中找出前三的值,可能会用到rank( )函数,排序,然后过滤出1,2,3。用条件格式则是另外一种快速方法,直接用颜色标出,非常直观。
七周成为数据分析师Excel技巧篇
冻结首行首列
Excel的首行一般是各字段名Header,俗称表头,当行数和列数过多的时候,观察数据比较麻烦。我们可以通过固定住首行,方便浏览和操作。Header是一个较为重要的概念。在Python和R中,read_csv函数,会有一个专门的参数header=true,来判断是否读取表头作为columns的名字。

自定义下拉菜单(数据有效性)
数据有效性是一种约束,针对单元格限制其输入,也就是让其只能固定几个值。下拉菜单是一种高阶应用,通过允许下拉箭头即可。
七周成为数据分析师Excel技巧篇
七周成为数据分析师Excel技巧篇
自定义名称
自定义名称是一个很好用的技巧,我们可以为一个区域,变量、或者数组定义一个名称。后续要经常使用的话,直接引用即可,无需再次定位。这是复用的概念。
七周成为数据分析师Excel技巧篇
我们将A1:A3区域命名为NUM,直接使用=sum(NUM) ,等价于sum(A1:A3)。
七周成为数据分析师Excel技巧篇
新手们理解数据库,可以将其想象成无数张表sheet。每一张表都有自己唯一的名字,就像上图的NUM一样。数据库操作就是引用表名进行查找、关联等操作。使用sum,count等函数。

查找公式错误
公式报错也不知道错在哪里的时候可以使用,尤其是各类IF嵌套或者多表关联,逻辑复杂时。查找公式错误是逐步运算的,方便定位。
七周成为数据分析师Excel技巧篇
分组和分级显示
分组和分级显示,常用在报表中,在报表行数多到一定程度时,通过分组达到快速切换和隐藏的目的。越是专业度的报表(咨询、财务等),越可以学习这块。在数据菜单下。
七周成为数据分析师Excel技巧篇
分析工具库
分析工具库是高阶分析的利器,包含很多统计计算,检验功能等工具。Excel是默认不安装的,要安装需要加载项,在工具菜单下(不同版本安装方式会有一点小差异)。
七周成为数据分析师Excel技巧篇
分析工具库是统计包,规划求解是计算最优解,类似决策树。这两者的分析方法以后详细论述。Mac似乎有阉割。

第三方应用
Excel是支持第三方插件的,第三方插件拥有非常强大的功能。甚至完成BI的工作。应用商店里微软的Power系列都挺好的。下图就是Power Map
七周成为数据分析师Excel技巧篇
第三方应用商店Mac没有,非常可惜。Win用户请用最新版本,老版本是没有插件的。

主要的Excel技巧和函数已经都教授给大家。Excel博大精深,有一句说的挺好,我们大部分实际用到的功能只有20%。熟练掌握这20%功能,日常工作足够应付。重要的还是解决问题的能力。接下来是Excel实战内容,下一篇文章会直接用到5000行真实的数据分析师的职位数据。没错,用数据分析师的数据进行分析,有点拗口。

本文转载自阿里云合作媒体51CTO,作者秦路,微信tracykanc。
相关文章:
七周成为数据分析师!https://yq.aliyun.com/articles/112192
七周成为数据分析师—Excel函数篇  https://yq.aliyun.com/articles/117168

这篇关于七周成为数据分析师—Excel技巧篇的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/223866

相关文章

Python获取中国节假日数据记录入JSON文件

《Python获取中国节假日数据记录入JSON文件》项目系统内置的日历应用为了提升用户体验,特别设置了在调休日期显示“休”的UI图标功能,那么问题是这些调休数据从哪里来呢?我尝试一种更为智能的方法:P... 目录节假日数据获取存入jsON文件节假日数据读取封装完整代码项目系统内置的日历应用为了提升用户体验,

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Java利用JSONPath操作JSON数据的技术指南

《Java利用JSONPath操作JSON数据的技术指南》JSONPath是一种强大的工具,用于查询和操作JSON数据,类似于SQL的语法,它为处理复杂的JSON数据结构提供了简单且高效... 目录1、简述2、什么是 jsONPath?3、Java 示例3.1 基本查询3.2 过滤查询3.3 递归搜索3.4

java中使用POI生成Excel并导出过程

《java中使用POI生成Excel并导出过程》:本文主要介绍java中使用POI生成Excel并导出过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录需求说明及实现方式需求完成通用代码版本1版本2结果展示type参数为atype参数为b总结注:本文章中代码均为

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

Python Dash框架在数据可视化仪表板中的应用与实践记录

《PythonDash框架在数据可视化仪表板中的应用与实践记录》Python的PlotlyDash库提供了一种简便且强大的方式来构建和展示互动式数据仪表板,本篇文章将深入探讨如何使用Dash设计一... 目录python Dash框架在数据可视化仪表板中的应用与实践1. 什么是Plotly Dash?1.1

Redis 中的热点键和数据倾斜示例详解

《Redis中的热点键和数据倾斜示例详解》热点键是指在Redis中被频繁访问的特定键,这些键由于其高访问频率,可能导致Redis服务器的性能问题,尤其是在高并发场景下,本文给大家介绍Redis中的热... 目录Redis 中的热点键和数据倾斜热点键(Hot Key)定义特点应对策略示例数据倾斜(Data S

利用Python开发Markdown表格结构转换为Excel工具

《利用Python开发Markdown表格结构转换为Excel工具》在数据管理和文档编写过程中,我们经常使用Markdown来记录表格数据,但它没有Excel使用方便,所以本文将使用Python编写一... 目录1.完整代码2. 项目概述3. 代码解析3.1 依赖库3.2 GUI 设计3.3 解析 Mark

Python实现将MySQL中所有表的数据都导出为CSV文件并压缩

《Python实现将MySQL中所有表的数据都导出为CSV文件并压缩》这篇文章主要为大家详细介绍了如何使用Python将MySQL数据库中所有表的数据都导出为CSV文件到一个目录,并压缩为zip文件到... python将mysql数据库中所有表的数据都导出为CSV文件到一个目录,并压缩为zip文件到另一个