第三周:excel学习

浏览: 65

从11月14日开始要学习excel了,之前只会一些简单的操作,现在主要是学习掌握一些函数的使用以及数据透视表的用法,会利用excel进行简单的数据分析。excel是很常用很基础的一个数据分析软件,基本上的人都是要使用excel,学习好excel是非常有必要。

一.学习函数

1.文本类函数

学习了视频以及自己在百度详细的看了这些函数使用方法,下面可以用自己的话来总结以下函数的作用。

find:查找字符串的位置

left:从左边截取字符串

right:从右边截取字符串

mid:从中间截取字符串

concatenate:合并多个字符串,和连接符&的作用类似

replace:替换字符串,指定位置与数量

substitute:替换字符串,可以一次性替换多个

text:把数值转换格式,一般用来转换成与时间相关的格式

trim:删除两边的空格

len:获取字符串的长度

把这些函数嵌套使用可以获取我们所需要的内容,例如视频中的薪资下限与上限分别可以用下面两个公式获得。

下限:=LEFT(P2,FIND("k",P2)-1)

上限:=MID(LEFT(P2,LEN(P2)-1),FIND("-",P2)+1,99)

2.关联匹配函数

我觉得叫做查找定位函数更容易理解,这类函数基本上是根据已知条件查找出所对应的字符串是什么。

lookup:根据已知条件查找区域内所对应的值,注意这里条件区域必须为升序排列

vlookup:根据已知条件查找区域内所对应的值,注意这里条件区域必须在第一列

match:返回所查找值在条件区域的相对位置

index:返回区域内所指定行指定列的值,和match配合使用效果强大

row:返回该单元格所在的行

column:返回该单元格所在的列

offset:以指定的应用为参考系,通过上下左右偏移得到新的区域的引用。总结来说就是对区域进行偏移。

hyperlink:百度解释为跳转,用来打开存储在网络服务器、Intranet 或 Internet 中的文件,或跳转到指定工作表的单元格。其实就是我们常说的链接。

这些函数组合使用功能强大,例如:excel练习中的第一问求全国点评数最高的饭店可以用下面的公式

=VLOOKUP(MAX(D:D),IF({1,0},D:D,C:C),2,0)

或者为

=INDEX(C:C,MATCH(MAX(D:D),D:D,0))

3.逻辑运算函数

ture:表示满足该条件,可以用与计算,默认值为1

false:表示不满足该条件,可以用与计算,默认值为0

and:表示同时满足这些条件

or:表示至少满足一个条件

if:最常用的逻辑函数,是根据逻辑计算的真假值,从而返回相应的内容

is:用于检验字符串的类型,分别为:空白(空白单元格)、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用

not:确保一个值不等于某一个特定的值,也就是说不满足条件返回值为ture

逻辑类函数单独使用比较简单,但是其一般和其它函数进行嵌套使用。

4.计算类函数

这类函数平时使用的较多,这里就记录几个自己不常用的吧。

Sumproduct :返回数组对应位置乘积之和

Quartile :返回数据集的四分位数

Substotal:比较万能的计算函数,可以替代平均值、计数、最大最小、相乘、标准差、求和、方差等的计算。

Int:取整函数,注意这里是直接舍去而不是四舍五入取整

Round:四舍五入函数

5.时间序列函数

这类函数光看单词的意思也知道函数表示的是怎么回事了,就不逐一列举了。这里要着重强调的秦老师讲的一句话,“时间的本质就是数字”,也就意味着时间也可以参与一些计算,加减法都可以。

这里举个比较经典的例子(来源于网络):判断某一年是否是闰年,年份写在A1单元格。

如果根据闰年的定义来判断用if函数嵌套比较复杂,=IF((MOD(A1,400)=0)+(MOD(A1,4)=0)*MOD(A1,100),"闰年","平年")

如果根据时间参与计算来判断的话会简洁得多,=IF(MONTH(DATE(A1,2,29))=2,"闰年","平年")

此公式的意思就是判断这一年的2月29日是否存在,如果存在那它的月份就会是2;如果不存在即是2月没有29日,那根据时间会参与计算,2月29日就会向前进一,也就是3月1日,那么返回的月份就会是3.

6.excel的部分技巧及工具(根据秦老师视频操作即可)

分列,数据透视表及数据切片,删除重复项,分析工具库,定义名称,条件格式,查找替换功能的使用

二、练习(详情见附件)

1.全国点评数最高的饭店是哪家? 

 答:方法一:可以用函数公式解决,在逻辑函数学习那里已经介绍过了

=VLOOKUP(MAX(D:D),IF({1,0},D:D,C:C),2,0)

或者为=INDEX(C:C,MATCH(MAX(D:D),D:D,0))

方法二:其实按照秦老师说的排序方法应该更简洁。

Clipboard Image.png

2.哪个城市的饭店人均口味最好? 

 答:方法一:使用数据透视表处理,选择地区为行标签,值为口味设置为求平均值。要得到口味最好的店可以用函数进行查找,也可以对口味列进行降序排列。

方法二:使用AVERAGEIF函数解决,先把城市这列复制到M列,然后使用删除重复项功能,在N2输入函数公式:=AVERAGEIF(A:A,M2,F:F),最后再排序即可得到。

Clipboard Image.png

3.哪个类型的餐饮评价最好? 

 答:这里餐饮评价具体指哪个指标, 在这里我姑且认为是口味,环境,服务三者的平均值,所以应该插入一列名为餐饮评价,然后在建立数据透视表。选择类型为行标签,值为餐饮评价求平均值。(另一种方法与第二题一样的,注意这里因为茶馆和酒吧都没有评价数据,所以使用函数计算会报错,但是不影响结果。)

Clipboard Image.png

4.类型为川菜的店中,有多少个带「辣」字,又有多少个带「麻」字? 

 答:首先类型是川菜,然后还需要店名带辣(麻)字,这里有两个条件并且是求个数,那么可以使用函数countifs。

=COUNTIFS(B:B,"川菜",C:C,"*辣*")

=COUNTIFS(B:B,"川菜",C:C,"*麻*")

Clipboard Image.png

5.口味、环境、服务,三个评价都在8.0以上的饭店有几家?它们在哪个城市的占比最 多? 

 答:这里的第一问就是三个条件求个,同样的是countifs函数。

=COUNTIFS(F:F,">=8.0",G:G,">=8.0",H:H,">=8.0")

第二问也是差不多,它多了个条件就是城市。做法是:先复制城市列到J列,然后删除重复项,再K2中输入函数公式:

=COUNTIFS(A:A,J2,F:F,">=8.0",G:G,">=8.0",H:H,">=8.0")/456

再对K列进行降序排列即可。

Clipboard Image.png

6.上海地区中,各个类型饭店服务前五名? 

答:先对数据源进行筛选出上海地区,然后按类型的升序排序再按服务的降序排序,插入辅助列写出排名并筛选出前五名即可。

Clipboard Image.png

7.没有评价的饭店有几家? 

 答:求饭店的家数,肯定要用到计数函数,这里有两种写法。

=COUNTIF(D2:D5864,"")或者为=COUNTA(C2:C5864)-COUNT(D2:D5864)

8.将人均价格划分成0~50,50~100,100~150,150~200,200+这几个档次, 各个城市分别有几家?其中占比又是多少? 

 答:这里按人均价格划分档次,有部分是人均价格为空值默认为零划为第一档(当然把这些清楚也可以)。

做法是:在L1处建立一个档次表,然后用如下公式确定档次

=LOOKUP(E2,$L$1:$L$6,$N$1:$N$6)或者=VLOOKUP(E2,$L$1:$N$6,3,1)

再利用数据透视表进行确定各城市的家数以及占比。

Clipboard Image.png

9.将点评、人均、口味、环境、服务这几个指标加工出一个综合评价系数,并且计算 哪十家店是最好的(开放题)。 

 答:这里的评价系数就是秦老师之前讲的指数法,主要考虑的是这些指标如何进行计算确定系数,其中口味、环境、服务是类似的指标可以用线性加权,点评与人均数值上相差比较大,应该用log的方法进行处理。在处理之前应该先清除空值。

10.对上海地区的日本料理,做一次描述性分析(开放题)

答:描述性分析是数据选项卡中数据分析里的描述统计,包含统计的一些指标如最大值,最小值,平均数,中位数,方差等。

首先清楚空白数据,接着筛选出上海地区的日本料理,然后对数据区域进行描述统计。

推荐 0
本文由 lizhi914637 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录注册