7周-数据分析3周-Excel

总结

这篇文档主要介绍了学习Excel的原因、Excel的基本函数和技巧，以及如何使用Excel进行数据分析。通过具体的示例，展示了Excel在处理和分析数据方面的强大功能。

Part One: 为什么要学习Excel

1.Excel是解决问题的工具。

2.Excel与SQL、Python、BI和ETL在不同需求场景下的应用。

3.Excel学习路径的介绍。

4.新手如何通过图形界面了解函数和脚本语言了解函数。

Part Two: Excel的常见函数

函数的定义：

函数的基本概念和参数。

常见函数

文本清洗函数：

字符串的概念。例如：‘qinlu 秦路’。

字符编码（防止Excel出现乱码）

        1 bit = 两种可能性，用0戒1存储。

        1 byte = 8 bit，如00000001，一共有256种可能性，1 byte可以存256个字符编码，最初的存储方式ASCII，就存了英文+数字 +符号。

        汉字远远大于256种可能性，于是用2byte组合表示，叫做GB2312。

        后来为了表示更多的汉子，用了GBK，它是拓展版，连繁体字都包括了。

        可是中国还有少数民族啊！少数民族的文字怎么办？于是又多了。

        可是全世界有多少国家多少名族？于是一个国际组织发明了一个万国码，叫做unicode

        UFT-8

字符编码（ASCII、Unicode、UTF-8、GB2312、GBK、GB18030）。

常见文本清洗函数

Find、Left、Right、Mid、Concatenate、Replace、Substitute、Text、Trim、Len

1.Find：Find（find_text,within_text,[start_num]）

=FIND("k",P2,1)，寻找“k”字符，从P2单元格字符串的第一个位置找。

2.Left：Left（text，[num_chars]）

=LEFT(P2,R2)，从P2单元格字符串的左边第一个字符找R2（R2是数字）个字符。

3.Right：Right（text，[num_chars]）

=LEFT(P2,R2)，从P2单元格字符串的右边第一个字符找R2（R2是数字）个字符。

4.Mid：.Mid（字符串，开始的位置，字符个数），是文本截取的意思，但是和left和right不同的是mid是从中间截取。

=MID(P2,U2+1,V2-U2-1)

5.Concatenate：Concatenate（text1，[text2]，...）多个拼接

=CONCATENATE(S2,T2,U2,"k")，把S2,T2,U2,"k"拼接到一起。

&：拼接两个

=T2&"k"，把T2,"k"拼接到一起。

6.Replace：Replace（old_text,start_num,num_chars,nuw_text）替换

=REPLACE(Q2,1,2,"k")，把Q2单元格的字符串的第一个位置开始替换2个字符，换成“k”。

粗糙的替换还是用菜单栏里的‘替换’方便。

7.Substitute：Substitute（text,old_text,new_text,[instance_num]）文本减法函数，substitute是完全匹配，包括大小写。

=SUBSTITUTE(Q2,Y2,"")，用空字符串替换Q2单元格字符串里的Y2单元格字符串。

8.Text：Text（值，数值格式），指将数字进行格式转换，主要只将数字直接转化成时间格式。建议直接在设置‘单元格格式’直接转换。

9.Trim：Trim（字符串），删除字符串中多余的空格（中间的空格删不掉，主要删左右的空格）。

=TRIM(Q2)

10.Len：Len（字符串），求文本里有多少个字符。

=LEN(P2)

关联匹配函数

常见关联匹配函数

Lookup、Vlookup、Index、Match、Row、Column、Offset、Hyperlink

1.Lookup：Lookup（查找值，查找向量，[返回向量]），作用是：从单行或单列或从数组中查找一个值。

F2=8；

=LOOKUP(F2,{0,5,10,20},{1,2,3,4})，结果为2

2.Vlookup：Vlookup（查找值，数据表，列序数，[匹配条件]）

=VLOOKUP(C2,[工作簿1]Sheet1!$A:$B,2,FALSE)

FALSE：精确匹配；TURE：近似匹配

3.Index：Index（数组，行序号，[列序号]，[区域序号]），根据索引（行序号、列序号）查找值。

=INDEX(P2:Q9,3,2)

4.Match：.Match（查找值，查找区域，[匹配类型]），根据值找位置（行、列）。

=MATCH(8,S3:T15,0)，如果查找值有重复值，则返回第一个查找值的位置。

5.Row：Row（[参照区域]），返回单元格在第几行。

=ROW(W7)，W7在第7行。

6.Column：Column（[参照区域]），返回单元格在第几列。

=COLUMN(T7)，T7在第20行。

7.Offset：Offset（参照区域，行数，列数，[高度]，[宽度]），作用是偏移。

=OFFSET(P5,1,1,1,1)，参照P5，行数向下加1，列数向右加1，目标区域是1个高度、1个宽度的位置。

8.Hyperlink：Hyperlink（连接位置，[显示文本]），超链接的作用。

=HYPERLINK(W7)

index和match的组合：index（，match（），）

逻辑运算函数

常见逻辑运算函数

True、False、And、Or、IF、Is、Not

true和false可以进行加减乘除，例如true+true=2，true+false=1，false+false=0。

1.True：，true直接转换成数字，默认值为1；

2.False：，false直接转换成数字，默认值为0；

True and True = True; True and False = False; False and False = False.

True or True = True; True or False = True; False or False = False.

D5=4，=D5>9，结果为false

3.And：

=AND(FALSE,FALSE)，为FALSE；

=AND(FALSE,FALSE) * 99，为0；

=AND(FALSE,FALSE) + 10，为10；

4.Or：

=OR(TRUE,FALSE)，为TRUE；

=OR(TRUE,FALSE) * 1024，为1024；

组合使用True、False、And、Or，满足3个、2个、1个条件：

F2=A2="知乎平台"；G2=B2="数据分析师"； H2=C2="应届毕业生"； =F2+G2+H2。结果可能是0、1、2、3

5.IF：IF（测试条件，真值，[假值]），if可以有嵌套，if（测试条件，if（ ，，），if（ ，，））。

=IF(D2>10,"高","低")；if嵌套。

6.Is系列：

ISNUMBER(值)：检测一个值是否为数值，返回TRUE或FALSE。

ISERROR(值)：检测一个值是否为错误值，返回TRUE或FALSE。

7.Not：

=NOT(1)，为FALSE；=NOT(0)，为TRUE。

计算统计函数

常见的计算统计函数

Sum、Sumproduct、Count、Max、Min、Rank、Rand、Randbetween、Average、Quartile、Stdev、Substotal、Int、Round

1.Sum：Sum（数值1，数值2，...），数值1、数值2都可以是数组；作用是：返回某一单元格区域中所有数值之和。

  Sumif：Sumif（区域，条件，[求和区域]），作用是：对满足条件的单元格求和。

2.Sumproduct：Sumproduct（数值1，数值2，...），数值1、数值2都可以是数组；作用是：在给定的几组数组中，将数组间对应的元素相乘，并返回成绩之和。

结果为：131

3.Count：Count（值1，值2，...），值1、值2都可以是数组；作用是：返回包含数字的单元格以及参数列表中的数字的个数，不计算空值。

=COUNT(D10:D16,D18:D26,D32:D36,D39:D44,)

4.Max：Max（数值1，数值2，...），作用是：返回参数列表中的最大值，忽略文本值和逻辑值。

=MAX(A:A)

5.Min：Min（数值1，数值2，...），作用是：返回参数列表中的最小值，忽略文本值和逻辑值。

=MIN(A:A)

6.Rank：Rank（数值，引用，[排位方式]），作用是：返回某数字在一列数字中相对于其他数值的大小排名。

=RANK(D2,D:D)

7.Rand：Rand（），作用是：返回大于等于0及小于1的均匀分布随机数，每次计算工作表时都将返回一个新的数值。

=RAND()，为0<0.754762<1

8.Randbetween：Randbetween（最小整数，最大整数），作用是：返回位于两个指定数之间的一个随机整数，每次计算工作表时都将返回一个新的数值。

=RANDBETWEEN(1,10)，返回1~10之间的数，包括1和10。

9.Average：Average（数值1，...），作用是：返回所有参数的平均值（算术平均值），参数可以是数值、名称、数组、引用。

=AVERAGE(D:D)

 Averageif：Averageif（区域，条件，[求平均值区域]），作用是：返回某个区域内满足给定条件的所有单元格的算术平均值。

10.Quartile：Quartile（数组，四分位数），作用是：返回数据集的四分位数。

11.Stdev：Stdev（数值1，数值2，...），作用是：计算基于给定样本的标准差。（忽略样本中的逻辑值及文本）。

=STDEV(D:D)

12.Substotal：，作用是：

13.Int：Int（数值），作用是：将数字向下舍入到最接近的整数。

=INT(10.1)，结果是：10；

=INT(10.1)+1，结果是：11。

14.Round：Round（数值，小数位数），作用是：返回某个数字按指定位数取整后的数字，四舍五入法，第二个参数是负数的，往十位百位靠近。

=ROUND(12.1212,3)，结果是：12.121。

15.Countif：Countif（区域，条件），作用是：计算区域中满足给定条件的单元格的个数。

16.Countifs：Countif（区域，条件1，条件2，...），作用是：计算区域中满足给定条件的单元格的个数。

时间序列函数：

时间的本质是数字。

常见时间序列函数

Year、Month、Weekday、Weeknum、Day、Date、Now、Today

例如：B4为2017/2/27

1.Year：Year（日期序号），作用是：返回以序列号表示的某日期的年份，介于1900到9999之间的整数。

=YEAR(B4)，结果为：2017

2.Month：Month（日期序号），作用是：返回以序列号表示的某日期的月份，介于1到12之间的整数。

=MONTH(B4)，结果为：2

3.Weekday：Weekday（日期序号，[返回值类型]），作用是：返回某日期为星期几。默认情况（[返回值类型]=1时）下，其值为1（星期天）到7（星期六）之间的整数；[返回值类型]=2时，其值为1（星期一）到7（星期天）之间的整数。

=WEEKDAY(B4,1)，结果为：2

=WEEKDAY(B4,2)，结果为：1

4.Weeknum：Weeknum（日期序号，[返回值类型]），作用是：返回一个数字，该数字代表一年中的第几周。即本周是今年的第几周。默认情况（[返回值类型]=1时）下，其值为1（星期天）到7（星期六）之间的整数；[返回值类型]=2时，其值为1（星期一）到7（星期天）之间的整数。

=WEEKNUM(B4,2)，结果为：10

5.Day：Day（日期序号），作用是：返回以序列号表示的某日期的天数，介于1到31之间的整数。

=DAY(B4)，结果为：27

6.Date：Date（年，月，日），作用是：返回代表特定日期的序列号。

7.Now：Now（），作用是：返回日期时间格式的当前日期和时间。即返回当前的时间。

=NOW()，结果为：当前的日期和时间，精确到秒。

8.Today：Today（），作用是：返回日期格式的当前日期。可以算距今多少天，因为日期的本质是数字可以直接相加减。例如today的日期减去单元格里的日期。

=TODAY()，结果为：当前的日期和时间，精确到日。

        计算天数也可以用Days函数，Days（终止日期，开始日期），作用是：返回两个日期之间的天数。

Part Three: Excel的常见技巧

Excel的快捷键

提高工作效率的快捷键列表

        Ctrl+方向键，光标快速移动；Ctrl+Shift+方向键，快速框选；Ctrl+空格键，选定整列；Shift+空格键，选定整行；Ctrl+A选择整张表格，Alt+Enter，换行。

excel本身的功能

数据-分列；

数据条、色阶（色阶适用于相关性的可视化），二选一；

数据透视表；

用数据透视表进行切片，插入图；

定义名称(指定名称)；

删除重复值；

下拉菜单（在数据验证里面）；

条件格式和迷你图；

迷你曲线

分列

数据整理的方法。

数组

Excel中的数组操作。

自定义名称

简化复杂公式的方法。

自定义下拉菜单

提高数据输入效率的功能。

冻结

固定表格中某些行或列的功能。

删除重复项

清理数据的方法。

分析工具库

Excel中的高级分析工具。

Part Four: 用Excel进行数据分析

使用Excel分析餐食数据的示例问题。

如何通过Excel找出点评数最高的饭店、人均口味最好的城市、评价最好的餐饮类型等。

对特定数据集进行描述性分析的方法。

案例：

现在有一份的餐食数据，我想通过excel知道

全国点评数最高的饭店是哪家？

哪个城市的饭店人均口味最好？

哪个类型的餐饮评价最好？

类型为川菜的店中，有多少个带「辣」字，又有多少个带「麻」字？

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

上海地区中，各个类型饭店服务前五名？

没有评价的饭店有几家？

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

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

对上海地区的日本料理，做一次描述性分析（开放题）