美食欢迎度分析
先根据文本文件《全国各省地级市名单》,整理出地区城市对照表,如下图。对源数据文件《美食平台数据》加上地区列,并简要说明整理数据的过程。
目录
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