教你用Excel做数据分析，分析北京人气美食

作为一名吃货，如果学了数据分析后，并不将其用于寻找美食，那就不是一名合格的吃货。本数据来自DZDP，收录了北京地区评价数最高的前50页（共748条）餐厅数据。数据集特征如下：

 

 

定义问题

在数据分析中，很多人可能会忽略一个重要的步骤，就是“定义问题”。因为只有定义出了问题，我们才能确定哪些特征是需要处理清洗的。数据清洗是耗时非常久的工作，大约要占整个数据分析流程（提出问题——理解数据——数据清洗——构建模型——可视化）的60%，因此我们绝对不可能对数据集中的每一个特征都进行清洗，尤其是当特征量很大的时候，因此定义问题就显得尤为重要。

 

分析数据首要工作是定义好分析的角度，食客和老板，他们看问题的重点肯定是不一样的。而我作为一名食客，最关心的事情自然就是：

 

去哪吃？——商圈

吃什么？——菜系（分类）

去哪家？——餐厅

上面这三个因素，又主要是由那些因素决定的呢？那就是：

可选项多不多？——餐厅数

人气如何？——点评数

评价如何？——各种点评分数

价格如何？——人均

 

围绕这些问题，下面就来开始清洗数据。

 

数据清洗

首先查看数据中有没有重复的记录。在这个数据集中，“餐厅”是记录的唯一标识，但其实对于大众点评来说，应该不会有可能爬取到重复的记录，但为了保险起见，我们还是查看下：

 

点击 数据——删除重复项：我们是根据“餐厅”进行筛选的，因此要取消全选，勾选“餐厅”，然后确定。

 

 

其实当打开数据时，这么一堆绿色的三角就十分引人注意了：

 

 

在进行完数据查重后，可以处理这些绿色三角了，它们表示单元格中的数据是文本类型的。文本类型的数据是不能用于计算的，因此我们要将文本类型转换为常规类型才能进行计算，但是Excel非常有意思的一点是，文本类型不能通过“设置单元格类型”直接转换成常规类型。但是，我们可以使用数据——分列，来处理文本类型的数字：

 

 

注意，前两步什么都不用改，直接下一步。到第三步时，请注意这里选择“常规”然后完成：

 

 

有的餐厅名称后面会附上一个（），里面是分店的地址，其实可以将这个分店地址提取出来，这样在后面分析餐厅的时候，不会造成数据重复。不过为了保持分店和分店间的的差异，这个总名称也建议保留。同样用到的是分列功能，为了避免分裂出来的数据占据右边的数据，我们可以新建两个列——总餐厅（保存餐厅名称），分店（保存提出的分店地址名），并将“餐厅”重命名为“餐厅全称”。

 

第一步依旧不改变设置（分隔符号），下一步：

 

 

这里需要注意，括号是半角的！也就是英文输入状态下的括号。然后下一步：

 

 

还是选择“常规”，下一步：

 

 

因为之前已经对数据进行复制处理，所以这里直接“确定”替换目标单元格内容。“分店”列中还会残留剩下的半个括号，只需要再使用一次“分列”功能即可清除。

 

在“人均”列，会看到下面这个￥表示人民币的标志：

 

 

其实这个标志对于本数据集来说是没有什么用的，而且会影响后面的统计计算，因此需要将其删除。方法就是利用“开始”选项卡中的“替换”功能：

 

 

“替换为”这里不需要输入任何内容，因为目的是删除，而不是真的替换成什么东西。最后点击“全部替换”即可。

 

每个餐厅都有3个评分，也可以使用AVERAGE函数添加一个平均得分特征。（PS：其实直接计算平均分不太靠谱，因为我们选择餐馆的时候，肯定还会考虑人均和评价数。而且对于口味、服务、环境的考量度——权重肯定也是不同的，因此这个评分需要优化。当然这个是后来才考虑到的，因此为了不影响整体文章，在后面会附加上。）但是计算后发现小数点实在影响阅读，而且也不需要那么精确，因此使用ROUND函数四舍五入即可，小数点和前面三个分数一致即可——保留一位小数。

 

可以在红色框出的区域内直接输入函数：

 

 

也可以点击 公式——插入函数：

 

 

当你不确定自己使用什么函数时，可以在“搜索函数”内输入一条简短语句来描述你想做的事情，例如图中的“求平均值”，然后点击“转到”：

 

 

就会显示出和求平均值相关的函数，选择最简单的AVERAGE即可：

 

 

弹出的内容会根据函数不同而不同，只需要按照提示填写即可。

 

对于“餐厅”列的名称之前进行了分列处理，但这不是说所有的数据都要这样处理，例如“商圈”列：

 

 

这里的“/”并不是包含分内容，因此不需要处理。
现在，数据清晰的工作已经完成了，接下来就是分析数据。

 

对于Excel来说，分析数据最好用的利器就是数据透视表了。只要点击 插入——数据透视表，就可以在新建工作表或者原有工作表中插入数据透视表。数据透视表可以说是根据你的要求可以随意使用，下面就先来查看“商圈”：

 

 

部的商圈放入在内考虑，三里屯并不是人均最高的。在平均得分方面，西单和三里屯并不是最高的，而是朝外大街。这里重点看下宇宙中心——WDK，虽然人均价格不高，但是平均得分还是较为可观的。很好。很好。

 

下面查看“菜系”——即分类：

 

 

由于菜系实在是太多了，因此没有办法全部贴上来，但可以看到，餐厅数最多的还是北京菜，毕竟是帝都嘛。不过口味做得最好（或者说大家更接受的）是西北菜，嗯，想想都知道应该是“黄馍馍餐厅”……不过本人非常想吐槽，你家的黄馍馍越来越小了！！！在点评数的均值列可以看到一个很有意思的事情——俄罗斯餐厅貌似人气很高啊！人均方面，其实这里没有截图到，最高的不是西餐，不是海鲜，而是素菜！！！是的，就是因为某兆尹原因，虽然本人吃素，但我绝对不会花那么多钱去吃个素食情调，嗯，我也承认，是因为穷。。。

 

接下来，看一下“餐厅”：

 

 

上面这张图筛选了分店大于5家的餐厅，我们可以看到“黄馍馍”餐厅在口味好、服务好、环境好、价格可接受方面表现得都还不错，能在帝都开20家分店也是有自己的实力的（顺便还想夸，新出的元气蛋糕真的不错~）。四季民福烤鸭店可以说是“一水红”了，味道好、服务好、环境好、价格自然也好，但是毕竟人家卖的是烤鸭嘛。绿茶餐厅貌似表现平平，但是人家价格也算中等，因此也不能苛责太多，不过我也很想吐槽绿茶的服务，就是没有服务。。。在来看东来顺，说句题外话：其实作为“老字号”，那么多年的口碑真的不容易，但是起码从这份数据来看，表现真的不太尽如人意。可以说，也许是现代人口味变了，不喜欢铜锅涮肉了？但是一般的环境分和服务分，也不应该是老字号有的吧，说了这么多，只是希望老字号不要一味的吃老本才好。最后，海底捞红耀耀的服务分，也是显眼得很呐~

 

最后我们来将所有的问题综合成一张表格，最为最后的美食指南：

 

 

商圈——菜系——餐厅，都汇总到一张表格了，方便食客们查找。点评数（应该可以代表人气吧）、人均、得分和推荐菜也都总结出来了，方便大家进行选择。

 

（说明：因为数据透视表中，只能是汇总的数据，因此推荐菜系用的是vlookup函数。然后数据透视表的列名直接在“值字段设置”中更改就好。）

 

对于口味、环境和服务这三个评分，其实个人来讲最看重的就是口味评分，因此将这个评分的权重增加为1.2，剩下两个依旧是1，取平均的时候为了不将得分拉高因此除以3.1作为修正手段。即：

（口味 * 1.2 + 评价 + 服务）/ 3.1

 

对于评价人数，一般会更倾向于评价人数多的餐厅，因此可以说这个评分是“正相关”。但是肯定不能就这么直接用，否则会带来较大的偏差。那么就需要log收敛一下，这里评价人数的区间范围不是非常非常大（3万+到4千+），毕竟都可以属于网红餐厅了，其实对于点评人数超过1000的餐厅，其实主观上已经没有什么太大的分别了。所以敏感度不需要很高，但是也需要有所区别，因此使用log4来收敛一下即可。

 

人均的话依照上面的思路，可以使用log2进行收敛，收敛后的效果为——人均最高888为9.79，人均最少14为3.8，还是可以的。

 

最后将这三个分数按照正负相关相加一下：

平均分+评价人数分-人均分

 

当然这样还是不行，需要进一步的归一化，可以用下面这个公式（tips：记得单元格要绝对引用）：

（分数-MIN）/ (MAX - MIN)

 

为了统一，可以使用round函数保留两位小数然后乘以10，即可得出0到10范围内，小数点后为1位的总特得分了。最后效果如下：

 

 

最后把数据按照总得分降序排列，会发现一个非常有意思的事情：

 

 

这就是太过注重于价廉的后果啊！！！

其实这么大排名是不恰当的，因为小吃一定比正菜便宜得多啊，所以可以进行分类查看。我们筛选出“北京菜”进行查看：