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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 本文是《七周成为数据分析师》的Excel函数篇教程,如果想要了解写作初衷,可以先行阅读七周指南。温馨提示:如果您已经熟悉Excel,大可不必再看这篇文章,或只挑选部分。世界上的数据分析师分为两类,使用Excel的分析师,和其他分析师。
本文是《七周成为数据分析师》的第一篇教程,如果想要了解写作初衷,可以先行阅读七周指南。温馨提示:如果您已经熟悉Excel,大可不必再看这篇文章,或只挑选部分。
世界上的数据分析师分为两类,使用Excel的分析师,和其他分析师。每一个数据新人的入门工具都离不开Excel。因为Excel涵盖的功能足够多。很多传统行业的数据分析师只要求掌握Excel即可,会SPSS/SAS是加分项。即使在挖掘满街走,Python不如狗的互联网数据分析界,Excel也是不可替代的。
Excel有很多强大的函数,函数可以被我们想象成一个盒子,专门负责将输入转换成输出,不同的函数对应不同的输出。
  • =Vlookup( lookup_value ,table_array,col_index_num,[range_lookup] )
上文的Vlookup就是一个经典函数。函数中包含参数,括号里的部分都是参数。我们可以把参数想象成盒子上的开关。vlookup就有四个开关,不同开关组合决定了函数的输入和输出。
  • =Vlookup( 参数1,参数2,参数3,参数4)
复杂的原理不需要了解。这篇文章是常用函数汇总。甚至你不需要特别记忆怎么使用函数,应用Excel函数最重要的能力是学会搜索。因为绝大部分函数网上已经有相应的解释,图文结合,非常详尽。
学会将遇到的问题转换成搜索语句,在我还是新人时并不会vlookup,我遇到的第一个问题就是关联多张表的数据,我在网上搜索:excel怎么匹配多张表的数据。于是就学会了。这里推荐使用百度,因为前三行的结果基本是百度经验,对新人学习很友好。
在理解函数的基础上,我会适当引入高层次的内容,SQL和Python(内建函数)。将其和Excel结合学习,如果大家吃透了Excel的函数,那么后续学习会轻松不少。

清洗处理类
主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的,需要经过数据分析人员的清理。数据越多,这个步骤花费的时间越长。
  • Trim
清除掉字符串两边的空格。MySQL有同名函数,Python有近似函数strip。
  • Concatenate
=Concatenate(单元格1,单元格2……)合并单元格中的内容,还有另一种合并方式是& 。"我"&"很"&"帅" = 我很帅。当需要合并的内容过多时,concatenate的效率快也优雅。MySQL有近似函数concat。
  • Replace
=Replace(指定字符串,哪个位置开始替换,替换几个字符,替换成什么)替换掉单元格的字符串,清洗使用较多。MySQL中有同名函数,Python中有同名函数。
  • Substitute
和replace接近,区别是替换为全局替换,没有起始位置的概念
  • Left/Right/Mid
=Mid(指定字符串,开始位置,截取长度)截取字符串中的字符。Left/Right(指定字符串,截取长度)。left为从左,right为从右,mid如上文示意。MySQL中有同名函数。
  • Len/Lenb
返回字符串的长度,在len中,中文计算为一个,在lenb中,中文计算为两个。MySQL中有同名函数,Python中有同名函数。
  • Find
=Find(要查找字符,指定字符串,第几个字符)查找某字符串出现的位置,可以指定为第几次出现,与Left/Right/Mid结合能完成简单的文本提取。MySQL中有近似函数 find_in_set,Python中有同名函数。
  • Search
和Find类似,区别是Search大小写不敏感,但支持*通配符
  • Text
将数值转化为指定的文本格式,可以和时间序列函数一起看

关联匹配类
在进行多表关联或者行列比对时用到的函数,越复杂的表用得越多。多说一句,良好的表习惯可以减少这类函数的使用。
  • Lookup
=Lookup(查找的值,值所在的位置,返回相应位置的值)最被忽略的函数,功能性和Vlookup一样,但是引申有数组匹配和二分法。
  • Vlookup
=Vlookup(查找的值,哪里找,找哪个位置的值,是否精准匹配)Excel第一大难关,因为涉及的逻辑对新手较复杂,通俗的理解是查找到某个值然后黏贴过来。
  • Index
=Index(查找的区域,区域内第几行,区域内第几列)和Match组合,媲美Vlookup,但是功能更强大。
  • Match
=Match(查找指定的值,查找所在区域,查找方式的参数)和Lookup类似,但是可以按照指定方式查找,比如大于、小于或等于。返回值所在的位置。
  • Row
返回单元格所在的行
  • Column
返回单元格所在的列
  • Offset
=Offset(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列)建立坐标系,以坐标系为原点,返回距离原点的值或者区域。正数代表向下或向右,负数则相反。

逻辑运算类
数据分析中不得不用到逻辑运算,逻辑运算返回的均是布尔类型,True和False。很多复杂的数据分析会牵扯到较多的逻辑运算
  • IF
经典的如果但是,在后期的Python中,也会经常用到,当然会有许多更优雅的写法。也有ifs用法,取代if(and())的写法。MySQL中有同名函数,Python中有同名函数。
  • And
全部参数为True,则返回True,经常用于多条件判断。MySQL中有同名函数,Python中有同名函数。
  • Or
只要参数有一个True,则返回Ture,经常用于多条件判断。MySQL中有同名函数,Python中有同名函数。
  • IS系列
常用判断检验,返回的都是布尔数值True和False。常用ISERR,ISERROR,ISNA,ISTEXT,可以和IF嵌套使用。

计算统计类
常用的基础计算、分析、统计函数,以描述性统计为准。具体含义在后续的统计章节再展开。
  • Sum/Sumif/Sumifs
统计满足条件的单元格总和,SQL有中同名函数。MySQL中有同名函数,Python中有同名函数。
  • Sumproduct
统计总和相关,如果有两列数据销量和单价,现在要求卖出增加,用sumproduct是最方便的。MySQL中有同名函数。
  • Count/Countif/Countifs
统计满足条件的字符串个数。MySQL中有同名函数,Python中有同名函数。
  • Max
返回数组或引用区域的最大值。MySQL中有同名函数,Python中有同名函数。
  • Min
返回数组或引用区域的最小值。MySQL中有同名函数,Python中有同名函数。
  • Rank
排序,返回指定值在引用区域的排名,重复值同一排名。SQL中有近似函数row_number() 。
  • Rand/Randbetween
常用随机抽样,前者返回0~1之间的随机值,后者可以指定范围。MySQL中有同名函数。
  • Averagea
求平均值,也有Averageaif,Averageaifs。MySQL中有同名函数,python有近似函数mean。
  • Quartile
=Quartile(指定区域,分位参数)计算四分位数,比如1~100的数字中,25分位就是按从小到大排列,在25%位置的数字,即25。参数0代表最小值,参数4代表最大值,1~3对应25、50(中位数)、75分位
  • Stdev
求标准差,统计型函数,后续数据分析再讲到。
  • Substotal
=Substotal(引用区域,参数)汇总型函数,将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化,换言之,只要会了这个函数,上面的都可以抛弃掉了。
  • Int/Round
取整函数,int向下取整,round按小数位取数。round(3.1415,2) =3.14;round(3.1415,1)=3.1。

时间序列类
专门用于处理时间格式以及转换,时间序列在金融、财务等数据分析中占有较大比重。时机序列的处理函数比我列举了还要复杂,比如时区、分片、复杂计算等。这里只做一个简单概述。
  • Year
返回日期中的年。MySQL中有同名函数。
  • Month
返回日期中的月。MySQL中有同名函数。
  • Weekday
=Weekday(指定时间,参数) 返回指定时间为一周中的第几天,参数为1代表从星期日开始算作第一天,参数为2代表从星期一开始算作第一天(中西方差异)。我们中国用2为参数即可。MySQL中有同名函数。
  • Weeknum
=Weeknum(指定时间,参数) 返回一年中的第几个星期,后面的参数类同weekday,意思是从周日算还是周一。MySQL中有近似函数 week。
  • Day
返回日期中的日(第几号)。MySQL中有同名函数。
  • Date
=Date(年,月,日)时间转换函数,等于将year(),month(),day()合并。MySQL中有近似函数 date_format。
  • Now
返回当前时间戳,动态函数。MySQL中有同名函数。
  • Today
返回今天的日期,动态函数。MySQL中有同名函数。
  • Datedif
=Datedif(开始日期,结束日期,参数)日期计算函数,计算两日期的差。参数决定返回的是年还是月等。MySQL中有近似函数 DateDiff。

本文转载自阿里云合作媒体51CTO,作者秦路,微信tracykanc。
相关文章:七周成为数据分析师!https://yq.aliyun.com/articles/112192
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8月前
|
机器学习/深度学习 人工智能 数据可视化
AI时代Excel数据分析提升之道
AI时代Excel数据分析提升之道
337 0
|
1月前
|
数据挖掘 数据安全/隐私保护 Python
【Python数据分析】<数据分析工具>基于Excel的数据分析
【1月更文挑战第22天】【Python数据分析】<数据分析工具>基于Excel的数据分析
|
4天前
|
数据安全/隐私保护
杨老师课堂之Excel VBA 程序开发第八讲使用工作表函数
杨老师课堂之Excel VBA 程序开发第八讲使用工作表函数
10 1
|
7天前
|
人工智能 自然语言处理 安全
ChatGPT高效搞定Excel数据分析
ChatGPT高效搞定Excel数据分析
22 0
|
1月前
|
存储 数据挖掘 BI
Excel 里 COUNT 函数介绍
Excel 里 COUNT 函数介绍
|
1月前
|
数据挖掘 数据处理
Excel 里 ROUND 函数介绍
Excel 里 ROUND 函数介绍
|
1月前
|
数据挖掘 BI 数据处理
Excel 里 COUNTA 函数介绍
Excel 里 COUNTA 函数介绍
|
1月前
|
数据挖掘 数据管理 数据处理
关于 Excel MAX 函数的用法一例
关于 Excel MAX 函数的用法一例
|
27天前
|
数据采集 数据挖掘 数据处理
Python数据分析实战:使用Pandas处理Excel文件
Python数据分析实战:使用Pandas处理Excel文件
100 0
|
1月前
|
数据采集 数据挖掘 关系型数据库
Excel计算函数(计算机二级)(1),2024年最新2024Python架构面试指南
Excel计算函数(计算机二级)(1),2024年最新2024Python架构面试指南