七周成为数据分析师—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。

原文链接

时间: 07-05

七周成为数据分析师—Excel函数篇的相关文章

七周成为数据分析师04_Excel

Excel适用于敏捷.快速.需要立即响应的需求: 而 Python.BI 等适用于常规.频繁.可复用可工程化的需求 设计到 Excel 的内容主要需要进行实操练习,这里只做一个陈列,具体知识请参考: 如何七周成为数据分析师01:常见的Excel函数全部涵盖在这里了 如何七周成为数据分析师02:Excel技巧大揭秘 如何七周成为数据分析师03:手把手教你Excel实战 1. Excel 常用的函数 1)文本清洗类 2)关联匹配类 3)逻辑运算类 4)计算统计类 5)时间序列类 原文地址:https

七周成为数据分析师07_统计学基础

拿到数据之后,我们应该怎么做? 直接套用各种图表公式进行分析并不是一个好做法.因为众多分析思路和公式都是基于数据服从一定分布的前提,如果不了解数据质量和分布情况,做推断分析是事倍功半的. 正确的处理方法是先使用描述统计. 描述统计学 描述统计学是一种概括数据集的方式,包括数据的加工和显示,数据集的分布特征等.它与推断统计相呼应. 首先把数据分为分类数据和数值数据.二者最显著的区分在于分类数据不可做加减,而数值数据可以. 分类数据主要应用频数统计,对各分类数据进行计数. 数值数据则是描述统计的重点

七周成为数据分析师05_数据可视化

数据可视化之美 数据可视化的目的是为了让数据更高效,让读者可以更高效阅读,而不单是自己使用. 数据可视化首先需要突出数据背后的规律和重要因素,其次才是美观. 数字可视化应该基于实际业务背景,比如地铁线路图并没有按照真实的地理距离来划分,但它却准确直观的传递给用户最关注的重点:应该如何乘车才能到达某地. 图表绘制部分主要为实操,这里就不做分享.原课程可以参考如下链接: 如何七周成为数据分析师:Excel技巧之甘特图绘制(项目管理) 图表的基本概念 1. 维度和度量 首先需要对维度和度量的概念进行区

七周成为数据分析师01_数据分析思维

在学习数据分析知识的过程中发现了一个七周成为数据分析师课程班,讲解的比较基础和全面,相对于常见的一些数据分析课程,突出的优点在于它把重心放在了数据分析的思维和业务能力,更加符合实际需求.毕竟对于数据分析师来说,最难的地方在于思维业务能力,否则只会使用各种各样炫酷的软件代码而不知所措. 这里记录一下七周成为数据分析师课程的学习笔记,教学视频是在万能的b站上发现的.可能涉及到版权原因这里就不贴上链接. 对应的文字教学资料可以查看:互联网数据分析能力的养成,需一份七周的提纲--秦路 接下来是第一周的课

七周成为数据分析师02_业务分析指标

成为一名合格的数据分析师,业务知识很重要.光有分析技巧但没有业务知识支撑,分析出的结果也只会是空中楼阁,难以落地推行. 只有理解业务,才能建立业务数据模型. 经典的业务分析指标 指标:如果你不能衡量它,你就无法增长它. 将业务场景用指标来衡量,更有利于对业务知识的把握. 指标建立的要点: 建立核心指标(明确当前核心需求) 好的指标应该是比率 好的指标应该能带来显著效果(易于执行落地) 好的指标不应该虚荣(纯粹为了数字好看,没有考虑实际情况) 好的指标不应该很复杂(直接干净简单利落) 1. 市场营

七周成为数据分析师03_业务分析框架

如何建立业务分析框架 从指标的角度出发 从业务的角度出发 从流程的角度出发 1. 市场营销模型 以'潜在客户-机会客户-新客-老客-忠诚客户'为主轴进行扩展. 以"机会客户"为例,可对其进行结构化思维划分: 2. AARRR 模型 付费推广渠道,先下载再注册:社交传播渠道,先注册再下载. 以"二次激活"为例进行扩展: 3. 用户行为模型(内容平台) 4. 电子商务模型 5. 流量模型 6. 如何应对各类业务场景 多加练习 熟悉业务 应用三种核心思维 归纳和整理出指标

第一周:数据分析师思维

一,前言 思维缺失---造成"不知道,不确定"(问题发生没?问题在哪里?为什么?不确定对不对?不确定执行结果?不知道老板是否满意给不给加薪?....) 要拥有三种核心思维: 结构化 公式化 业务化 数据分析思维7大技巧:1.象限法 2.多维法 3.假设法 4.指数法 5.二八法则 6.对比法 7.漏斗法 在业务时间锻炼分析能力----好奇心 二,三种思维详细内容 1.结构化 重点:找出核心论点,将核心论点逐一分解,再去拆解到穷尽(金字塔塔顶到塔底部) 工具:单人作战----思维导图 团

第七周作业 POI操作Excel,world文档

先来说说jxl与poi的区别. 相同点都是操作EXcel的工具,但jxl不常用原因有:jxl没有人维护,操作excel效力低下,只支持03及其以前的版本的excel,对图片的支持不完整. 同poi比较而言,poi效率高功能强大,支持xlsx,xls即支持所有的版本.而且操作大数据效率比较高,因为他对大数据的操作做了相应的优化. 下面来个简单地实现:poi操作Excel的功能 简单的单元测试类:实现的功能是向excel文件中写入数据 1 @Test 2 public void test1() th

数据分析师之Excel数据处理与分析实战

Excel数据处理与分析实战 课程学习地址:http://www.xuetuwuyou.com/course/191 课程出自学途无忧网:http://www.xuetuwuyou.com 课程简介 本门课程详细介绍了 Microsoft Excel 的应用知识,Excel也称为电子表格,是Microsoft office 套装软件的一个重要组成部分.利用它可以进行各种数据的混合运算外,还可以应用于财务会计.统计分析.证券管理.决策管理以及市场营销等众多领域.正因为它具有如此广泛的应用,所以才得