美食欢迎度分析
先根据文本文件《全国各省地级市名单》，整理出地区城市对照表，如下图。对源数据文件《美食平台数据》加上地区列，并简要说明整理数据的过程。
目录
1.美食欢迎度分析
（复制原数据，选择性粘贴选择“值”类型，不会复制格式）
1.1点评数据分析
1.2菜系分布地区分析
1.3餐饮类型评价分析
2.数据处理与分析
2.1口味、环境、服务，三个评价都在8.0以上的饭店有几家?它们各个地区的分布如何?
2.2潮菜、湘菜对比
2.2.1两菜系以表格及柱状图在所有评价方面的比较
2.2.2 两个菜系在全国地区分布，对比数据并给出自己的分析结果；
2.2.3将人均价格划分成0~ 50 , 50~ 100 , 100~ 150 , 150~ 200 , 200+这几个档次，比较两个菜系在消费档次上的差别。
3.数据分析
3.1综合点评、人均、口味、环境、服务这几个指标加权给出一个综合评价系数,并且给出各地区综合评价指数前十名（开放题）
3.2分别对南、北方的火锅,做一次描述性分析（开放题）
1.美食欢迎度分析
（复制原数据，选择性粘贴选择“值”类型，不会复制格式）
一、导入文本，先用“：”分隔，再用"、"分隔；再断开连接（点“数据”->点“编辑连接”，断开连接）
二、转置，
这个做法不好做：{是针对（外部导入的数据或者从表格或区域获取数据的）：点“数据”->点“获取数据”->点“启动power query编织器”}；
全选数据：ctrl+A 或点A左上角三角号；复制到另一数据表中，右键，在选择性粘贴有转置图像，选择它；
(三）、（下面该图是从外部导入.txt文件，在弹出或选择“启动power query编织器”做的；跟上面复制转置没有什么差别）整理出各个城市所对应的地区：第二列第三列的数据依次放到第一列下面
（查询|连接图表，右键点“除”可以断开连接）；
（四）、 （删掉colmno列）整理出各个城市所对应的地区：第二列第三列的数据依次放到第一列下面
在B12列输入{这样无法删除空值 =OFFSET($C$1,INT(ROW(C3)/3)-1,MOD(ROW(C3),3))&" " }
四、上面做法还没解决，先手动复制
————————————————————————————
————————————————————————————
(五)此做法行不通：在美食平台execl表中，全城市列A2到尽头，区域内点击鼠标右键，选择打开“设置单元格格式”。在数字标签页中找到并点击“自定义”，并在右侧类型输入框中输入需要添加在单元格前面的文本：@市（双引号需在英文状态下输入），然后点击右下角的确定按钮即可。
——————————————————————————————————————
————————————————————————————————————-———
（五）、复制为两列，城市列在前，地区在后，在“美食平台数据”的城市列，鼠标右键，点“插入”,添加一列，列名为地区，在下面A2处输入公式：=VLOOKUP(B3,Sheet1!A:B,2,0)按enter,（Sheet1为城市和地区对应的表）在A2右下角的黑色“+”双击，将会显示全部城市对应的地区；
提示：（本人用execl会卡住，改wps）
上述解决方法:菜单栏选择“公式”找到“计算选项”，点击“手动”
1.1点评数据分析
（先对有空值的列填充为0，使用查找和替换功能。 CTRL+h快捷键调出替换对话框。 在替换一栏空着不要输入内容，在替换为一栏输入0）
要求：得出全国点评量前一百名餐馆相关信息及相应排名。结果以表格方式进行说明对比。
解法：先复制一数据到另一张表，在“人均”列鼠标右键插入一列，名为“点评排名”在下面输入一下公式：
=SUMPRODUCT((E$2:E$5864>=E2)/COUNTIF(E$2:E$5864,E$2:E$5864))
按enter后，鼠标放在得出排名的右下角“+”双击，实现中国式排名，再在上方选项选择“排序”，选择“升序”，选中101名数据行，再按“shift+ctrl+键盘向下箭头的标志”，在鼠标右键点击“隐藏”或删除；
1.2菜系分布地区分析
要求：给出各个美食类型在各地区的分布，以图表方式进行说明对比。
解法：
点"美食平台数据"任一单元格，点菜单栏“插入”，选择“数据透视表”,选择区域为整个数据区域，放置区域为表"1.2菜系分布地区分析"（点方框右边向上箭头可以快速选择）；
点‘数据透视表’，行标签选择“美食类型”，列标签选择“地区”，值选择“地区”（非数值类型默认会计数，数值类型默认求和）；可以更改行列标签名，点“行标签”右边标志符号，按“美食类型排序”；
在菜单栏“数据透视表分析”中选择“数据透视图”，使用“柱状图”；结果如下：
创建新的透视图，行标签选择“美食类型”和“地区”；列标签拖动3次选择“地区”，在第二列地区，点任一单元格，鼠标右键选择“值显示方式”为总百分比，在第三列地区，点任一单元格，鼠标右键选择“值显示方式”为“降序”排列，或者菜单栏“数据”->降序排列；再选择透视图。
1.3餐饮类型评价分析
要求：按餐饮类型得出点评数量汇总，以图表方式说明哪种类型餐饮最受欢迎。
解法：
点"美食平台数据"任一单元格，点菜单栏“插入”，选择“数据透视表”,选择区域为整个数据区域，放置区域为表"1.3餐饮类型评价分析"（点方框右边向上箭头可以快速选择）；
点‘数据透视表’，行标签选择“类型”，列标签选择“点评”两次，（非数值类型默认会计数，数值类型默认求和）；点击第二列求和项“点评”，任一单元格，鼠标右键选择“值显示方式”，选择“降序”排列（按类型），再在菜单栏的“数据”中选择“降序”，可以更改行列名；
在菜单栏“数据透视表分析”中选择“数据透视图”，使用“柱状图”；结果如下：
2.数据处理与分析
2.1口味、环境、服务，三个评价都在8.0以上的饭店有几家?它们各个地区的分布如何?
解法：
(1)复制一份“美食平台数据”到另一张表，点击左上角选择全界面，然后点菜单栏的“数据“”，筛选，依据数字筛选，然后再依次筛选口味、环境、服务大于8.0就可以了
备注：选择上面所有数列自动筛选以后再各个第一行都有下拉的小三角，点击然后选择里面的自定义，然后选择大于等于，后面写8.0就可以了···恩，可以随便选，再多几个条件也不怕
（2）在表格的合适位置输入筛选的三个关键字，口味、环境、服务>8.0
比如筛选三科成绩：
选中整个表格，点击菜单栏上的“数据-筛选-高级”。
在打开的对话框里，点击条件区域右边的箭头图标，选择前面设置的三个筛选条件。
点击确认，关闭对话框。
格内的学生成绩清单根据三个筛选关键字，筛选出三科成绩都大于70分的同学。
再复制筛选好的表到另一张表格中：
选中数据表的任一列右下角可以看到数据共有多少行；或选中第一行按“shift+ctrl+键盘向下箭头”可以直接全选数据到结尾，看到。
点"所在的表格"任一单元格，点菜单栏“插入”，选择“数据透视表”,选择区域为整个数据区域，放置区域为合适位置（点方框右边向上箭头可以快速选择）；
点‘数据透视表’，行标签选择“店名”，列标签选择“地区”，（非数值类型默认会计数，数值类型默认求和）；可以更改行列名；可以看到每间店只分布在有个地区，分布最多在华东地区；
2.2潮菜、湘菜对比
- 两个菜系在点评、人均、口味、环境、服务等方面的综合比较，以表格及柱状图进行比较；
- 两个菜系在全国地区分布，对比数据并给出自己的分析结果；
- 将人均价格划分成0~ 50 , 50~ 100 , 100~ 150 , 150~ 200 , 200+这几个档次，比较两个菜系在消费档次上的差别。
提示：潮菜是否都带“潮”字，湘菜呢？跟“湘”“辣”“麻”？
2.2.1两菜系以表格及柱状图在所有评价方面的比较
解法：
（1）复制一份“美食平台数据”到另一张表，点菜单栏“数据”或"开始" 找到筛选，点击列名右上角的图标，除了湘菜和粤菜/潮州菜，其他都勾选，再在菜单栏“开始”找到“查找和替换”->定位->可见单元格->确定->再鼠标右键点删除；
（2）再复制一份“美食平台数据”到另一张表，点菜单栏“数据”或"开始" 找到筛选，点击列名右上角的图标，除了粤菜，其他都勾选，再在菜单栏“开始”找到“查找和替换”->定位->可见单元格->确定->再鼠标右键点删除；再在菜单栏“开始”里找到“查找和替换”找到替换，查找内容为“粤菜/潮州菜”，替换为“潮州”；
拼接两张表；
（3）数据透视图可以重新根据“数据透视分析”中的更新数据源，再次刷新数据。
点任一单元格，点菜单栏“插入”，选择“数据透视表”,选择区域为整个数据区域，放置区域为合适位置（点方框右边向上箭头可以快速选择）；
点‘数据透视表’，行标签选择“类型”，列标签选择“点评、人均、口味、环境、服务”，（非数值类型默认会计数，数值类型默认求和）；分别点该列单元格鼠标右键，选“值汇总依据”，勾选“平均值”并点击“其他选项”，选“数值”，选两位小数的；再在“数据透视图分析”中选择透视图；
再点‘数据透视表’，行标签选择“类型”，列标签选择“点评、人均”，（非数值类型默认会计数，数值类型默认求和）；再在“数据透视图分析”中选择透视图；更改列名
2.2.2 两个菜系在全国地区分布，对比数据并给出自己的分析结果；
解法：插入‘数据透视表’，行标签选择“类型”，列标签选择“地区、城市”，值选择“城市”（非数值类型默认会计数，数值类型默认求和）；插入数据透视图；点击菜单栏“数据”选择“排序”，选择“降序”，依据城市排序；再点多依次菜单栏“数据”选择“排序”，选择“降序”，弹框下方有摘要显示之前选择的“城市”排序。（或者点行列标签右上角的标志，可以根据字符或者数值进行排序，此处选择城市数值进行排序）
2.2.3将人均价格划分成0~ 50 , 50~ 100 , 100~ 150 , 150~ 200 , 200+这几个档次，比较两个菜系在消费档次上的差别。
3.将人均价格划分成0~ 50 , 50~ 100 , 100~ 150 , 150~ 200 , 200+这几个档次，比较两个菜系在消费档次上的差别。
解法：（用数据透视表做也是可以的，但没有达到想要的效果）
先手动输入一列价格区间，
（1）先创建湘菜的人均价格区间：点菜单栏“数据”，找到“数据分析”，在弹框中选择“直方图”，
输入区域为湘菜的人均价格：“'2.2.1'!$F$204:$F$219”，接受区域为自己手动创建的价格区间“$H$1:$H$5”，输出区域选择合适的位置，勾选“图表输出”。
（2）再创建潮菜的人均价格区间（方法同上述步骤）：
点菜单栏“数据”，找到“数据分析”，在弹框中选择“直方图”，输入区域为湘菜的人均价格：“'2.2.1'!$F$204:$F$219”，接受区域为自己手动创建的价格区间“$H$1:$H$5”，输出区域选择合适的位置，勾选“图表输出”，最后点击“确定”；删除“表中的人均价格”列，将湘菜表和潮菜表价格区间拼起来，更改列名，更改价格区间的行名。
（3）点击价格区间任一单元格或全选整个区间表，找到菜单栏“插入”，选择“推荐的图表”，点确定。
3.数据分析
3.1综合点评、人均、口味、环境、服务这几个指标加权给出一个综合评价系数,并且给出各地区综合评价指数前十名（开放题）
解法：点击“开始”，点击“查找与替换”；定位空值，鼠标右键删除行；
a.点评、口味、环境、服务为正比（将口味、环境、服务求平均数，可适当进行加权处理）; 取三者和的平均值 =(g2+h2+i3)/3,在j2输入按enter后，双击j2
b.由题意，人均为降权（人均取负值。因为价格越低，消费者会越喜欢）：=log(f2,5),双击l2下拉；
c.点评,=log(e2,5),双击k2下拉；
d.由于点评、人均数值较大，对其进行LOG() 函数处理，底数根据情况选择{基底越大，log后差距越小，用户对该属性越不敏感；可以使数据达到比较好的收敛}；
算出综合系数：=j2+k2-l2;双击m2下拉；
e.归一化，让综合评价系数收敛到 0-1 之间：对各个综合数利用【（int - MIN）/(MAX - MIN)】进行收敛。
算出综合评价系数：=（m2-MIN(m:m)）/（MAX(m:m)-MIN(m:m))；在N2输入确定并双击;
(如结果报错可以点旁边的“！”找出错处)
在合适位置插入数据透视表，行为“城市”，列为"综合评价系数",拖动两次；在一列的‘值字段设置’中，对“值汇总方式”选择“平均值”，在该列单元格鼠标右键，选择排序方式为降序（根据系数排序，或在菜单栏的“数据”中选择排序方式，或点击行标签右边的标志选择其他排序方式）；
接着点击行标签右边的标志选择选择“值筛选”，可以根据“综合评价系数”选择前十行。
3.2分别对南、北方的火锅,做一次描述性分析（开放题）
解法：
（1）分别找出南方和北方的城市，分别在菜单栏“数据”选择筛选“高级”，点筛选结果复制到其他区域，列表区域为数据区域，条件区域分别为南方和北方的城市，复制到合适位置，勾选"选择不重复得记录"；
（2）找到菜单栏“数据”中的“数据分析”，选择“描述性统计”，区域选择南方的点评、人均、口味、环境、服务（shift+ctrl+键盘向下箭头），点“逐列”，勾选“汇总统计”和“平均置信度”，最后确定；北方的也按同样操作。可以插入背景色。
本文先筛选出南方、北方的城市，再在此基础上筛选出火锅类型的数据，再运用“数据分析”中的“描述性统计”功能，分别对南北方火锅的点评、人均、口味、环境、服务进行描述性统计。做法同上述步骤。
数据链接: https://pan.baidu.com/s/1WLXAUo5zbZVcFdEXxGrbfA?pwd=2023 提取码: 2023
8155
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
