用excel进行数据分析

做了秦路老师在线课程里的一个用excel做数据分析的作业，写此篇以回顾总结我解题时所用方法、思路与秦路老师不同的地方，以及从秦路老师的讲述中我学到的分析思路以及方法技巧。

数据源为一份餐饮数据，大概有6000行，有一些数据已被我做了些许处理：

数据源

秦老师所普及数据分析好习惯：做数据分析之前先将原始数据复制一份，已保留原始数据。

---

第一题：全国点评数最高的饭店是哪家？

这道题我做的时候第一反应就是用函数法取查找，我想到了四种函数公式去解这道题：

1.经典的INDEX函数与MATCH函数匹配查找：index(A:D,match(max(D:D),D:D,0),3)；

2.用LOOKUP函数取查找：lookup(max(D:D),D:D,C:C)；

3.用LOOKUP函数的经典查找方式：lookup(1,0/(D:D=max(D:D)),C:C)这种函数的第二参数运算结果返回一串有错误值和0组成的数组，而0将会被当做查找到的值返回；

4.利用VLOOKUP函数的逆向查找公式查询：vlookup(max(D:D),if({1,0},D:D,C:C),2,0）；函数中嵌套的IF函数会生成一个源数据的D列在前，C列在后的两列数据，相当于将D列和C列位置调换。

这四个函数公式基本涵盖了EXCEL中所有的查找相关函数，相比较而言INDEX和MATCH函数这种匹配查找方式适用性更广。

当然第二种用LOOKUP函数的直接查找返回了错误的结果，是因为LOOKUP函数也同VLOOKUP函数一样是不能进行逆向查询的，这也是我通过这道题学到的一个知识点，也暴漏了LOOKUP函数的局限性。

秦路老师思路：直接将点评数列降序排列，从而直接得到了点评数最高的饭店是三宝粥铺。

从这道题得出的经验就是：用最方便快捷的方法去解决问题，而不是刻意追求难度。这样才能提高效率。当然尝试用各种方法解决问题对作为学习者的我是很重要的，只不过从这道题暴漏出我的不足就是一拿到题就去实践所有的能想到的解题方法，而没有去想哪个方法是最简单高效的。解题思路上有缺陷。

第二题：哪个城市的饭店人均口味最好？

这道题的解题思路很简单：插入数据透视表，将城市拖到行标签，口味拖到数值，值汇总依据选择平均值，就可得出如下图所示结果，可以看出人均口味最好的城市是上海：

人均口味最好的城市是上海

秦老师普及数据分析好习惯：不要在透视表上处理数据，而是将透视表选择性粘贴数值到其他区域进行处理。

第三题：类型为川菜的店中有多少带“辣”字的？

这道题我想到了三种计数函数：

1.用COUNTIFS函数计数：countifs(B:B,“川菜”，C:C,“*辣*”)；

2.用SUMPRODUCT函数的计数功能：sumproduct((B:B=“川菜”)*（C:C=“*辣*”）*1)；

3.用SUM函数的计数功能：{=sum((B:B=“川菜”)*（C:C=“*辣*”）*1)}。

但后两个公式都没算出正确结果，我检查出的原因是通配符*的使用导致了返回错误结果，通配符在这两个公式中的使用方式是错误的。

SUM函数公式的花括号是由于SUM在此背景下的应用为数组函数、EXCEL中公式输入完成后按快捷键Ctrl+Shift+Enter会自动生成花括号。

秦老师的方法是：插入空白列，用Find函数：find(“辣”，C2,1)，返回辣字在饭店名称中的位置，然后用筛选功能筛选掉错误值和筛选出川菜，剩下有数字的单元格个数就是川菜类型中带辣的饭店名称的个数。

第五题：口味、环境、服务评价都在8.0以上的饭店有几家？他们在哪个城市的占比最高？

这道题包含两个问题，第一个问题评分均在8.0以上的饭店有几家，我用计数函数COUNTIFS,SUMPRODUCT函数和SUM函数的计数功能分别进行求解:

countifs(G:G,“>=8.0”,G:G,“>=8.0”,I:I,“>=8.0”)

sumproduct((G:G>=8.0)*(H:H>=8.0)*(I:I>=8.0))

{=sum((G:G>=8.0)*(H:H>=8.0)*(I:I>=8.0))}

SUM和SUMPRODUCT返回的结果比COUNTIFS返回的结果大1，原因是SUM和SUMPRODUCT将我的标题行也跟8.0进行了比较，而在excel中文字、字母、逻辑值TRUE和FALSE都是比数值大的，所以标题行也被这两个函数拿去跟数值8.0进行了比较，而COUNTIFS函数不会将文字与数值进行比较。

这些饭店在每个城市的占比利用数据透视表取做，很简单。

第六题：上海地区中各个类型饭店服务前五名？

利用数据筛选功能将上海地区的数据筛选出来复制粘贴在一张新的sheet中然后用透视表进行处理得到结果：

上海地区各个类型饭店服务排名

秦老师讲方法：将上海的数据单独粘贴在一个SHEET后，以类型为主要关键字，服务为次要关键字，对数据表排序，也就是先根据服务列排序，再根据类型列排序：然后用IF函数返回各个饭店在该类型中的名词，用筛选功能选出前五名就得到了结果，这里IF函数的使用较为巧妙：

秦老师方法得出的结果

第七题：没有评价的饭店有几家？

这道题本也是道条件统计题，但是去统计有数据行中点评列为空值的个数，如果我在函数公式中选中整列去统计空值的个数会将没有数据行的空值在统计在内，而如果要选中有数据行区域，估计拖拽选中五千多行数据就得一分钟。

所以有一个简单的方法就是选中点评列，可以在窗口右下方看到有个计数4510，这个数据就是该列非空值的单元格个数，然后单击整表中某个单元格，单后双击单元格下边框，表格会迅速呈现最后一行数据，可以看出总共有5864行数据，所以没有评价的饭店数量=5864-4510+1

点评列非空值单元格有4510个 共有5864行数据

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

第一步先将人均价格划分档次，利用VLOOKUP函数可实现分组功能：

用VLOOKUP函数对人均价格分组

然后插入透视表进行分析得出结果：

各个城市不同价格分组饭店数所占比例