八、 Excell二级下拉菜单制作和回归分析

@Author ： By Runsen
@Date ： 2020/5/13

现在是2020/5/13 0:14 。这是一月初写的，同步过来应该不用多少时间

作者介绍：Runsen目前大三下学期，专业化学工程与工艺，大学沉迷日语，Python， Java和一系列数据分析软件。导致翘课严重，专业排名中下。.在大学60%的时间，都在CSDN。

本专栏数据分析全系列：将使用Excel，Powerbi，Python，R，Sql，SPSS，stata以及Tableau，后面还会补充BI。这个从2020年期末考试，由于大三上大学挂了大学以来的两科化工原理和热力学。从此，决定逃离CSDN，但是发现我已经深深爱上了CSDN，这个我从大一奋斗到现在的地方。

于是将每天一直写，不断地回顾和反省，大学三年搞数据的从小白变老手的经验，想看就给钱，就是这么直接。这些是一月份我决定写书，结果别人看不起我写的。

没看上篇的先看上篇

七、Excel中图表制作和展示

1. Excel

1.3 Excel进阶使用

1.3.1 Excel二级下拉菜单制作

制作下拉菜单是通过“数据验证”功能实现的，2013版本也叫做“数据有效性”，该命令位于“数据”选项卡。

在Excel二级下拉菜单制作.xlsx中，如下图1-75是二级下拉菜单原始数据

选中第一行的省市名称，右键选择定义名称，在名称框中输入“省市”两个字，然后按回车，这样就定义了一个“省市”的名称，如下图1-76所示。

CTRL+A全选原始数据，选择公式中的根据所选内容创建，由于标题在第一行，因此选择首行为名称，然后点击确定按钮。操作完毕后，在公式中名称管理器中就可以看到定义的名称了，如下图1-77所示。

在二级下拉列表Sheet的A1单元格，选择数据中的数据验证。选择序列，来源处输入：=省市，然后点击确定，如下图1-78所示。

同样的方法，选中B2单元格，设置数据验证，输入公式：=INDIRECT($A1)，如下图1-79所示。

设置完毕后，A1单元格选择“北京”时，B1的下拉菜单返回“北京”的信息。当然可以通过下拉制作多个二级下拉列表，最终结果如1-80所示。

1.4.2 回归分析

Excel数据分析工具库是个很强大的工具，可以满足基本的统计分析和回归建模，无需使用任何Python或者R代码。

在回归分析.xlsx中，存在价格和利润的数据，下图1-81所示

现在需要求得当价格取得多少时，获得最大的利润。只需要CTRL+A全选数据，选择插入X-Y散点图，绘制带平滑线和数据标记的散点图。点击坐标轴，右键，选择坐标轴选项，设置边界最小值和最大值。点击散点图，添加趋势线。在趋势线选项中，存在指数，线性，对数，多项式，乘幂和移动平均的回归选择，如下图1-82所示。

同时点击显示公式和R平方值，不断地换回归关系的选项，使R平方值靠近1，即拟合程度越高。这里选择二阶多项式。具体方程：y = -4.6716x2 + 286.75x - 2988.4，R² = 0.9201，如下图1-83所示。

现在通过使用Excel中的数据，分析中的规划求解功能（需要在加载项中开启），求出一元两次方程：y = -4.6716x2 + 286.75x - 2988.4。在C1输出X，D1输入Y，C2输入0，D2输入：=-4.6716*(C2^2) + 286.75*C2- 2988.4。如下图1-85所示

点击数据，分析中的规划求解，设置目标$D$2，目标值为10000，因为二次函数的最大不可能达到10000，当达到最大值停止，保存结果，如下图1-86所示。

即当价格为30.69，存在最大利润1411.88，最终结果如下图1-87所示

同样代入抛物线的顶点坐标公式：(-b/2a，[4ac-b^2]/4a)，即当X=-b/2a=286.75/(2*4.6716)=30.69时，Y有最大值1411.88。

对于Excel数据选项卡中的预测和分析中的数据分析都是比较实用的部分。以及Excel对数据库的操作，数据库函数和数据采集都是非常强大的功能。

总结

Excell二级下拉菜单制作和回归分析，是Excel进阶的东西。

数据集下载：我觉得GIthub访问有点慢，就练习文件放在码云上，

https://gitee.com/MaoliRUNsen/data_analysis_series.git

在Excel文件夹中对应的xlsx

现在是2020/5/13 0:22 ，关机睡觉