​数据分析，除了Excel数据透视表，还有什么工具？
经常听到同事说：
“Excel很难，做个简单图表还行，一看到透视表就晕。”
“作为表哥表姐，每天的工作就是复制粘贴，不懂技巧，一行一行审查数据，枯燥没有含金量。”
…….
那么excel分析数据真的有那么难么？难！而且用excel做数据分析，筛选、过滤、排序，简单公式，数据透视表、简直头大。那么，除去Excel，除去数据透视表，有没有可替代的工具呢？有。比如Smartbi大数据分析工具 --Excel融合分析。
什么是Smartbi Excel融合分析？
Smartbi Excel分析是一款面向excel用户的自助式分析工具，它等于"Smartbi 数据服务 + Excel分析插件 + Excel自身功能”，用户在使用时简单易上手，并且该功能延续了Excel的优点，解决了Excel的缺点，同时融入了BI软件的强大能力；


为什么需要 Smartbi Excel融合分析

· 用户已经习惯使用Excel，学习新的工具成本高，使用不顺手；
· Excel提供了非常强大灵活的数据处理、数据分析以及数据可视化的能力;
· 用户存在大量基于Excel的报表模板，这些模板积累了大量的业务智慧;换成其他的分析工具，Excel业务模板无法重用;
· 市面上分析工具给用户提供分析能力的同时，也限制了用户想象力;在功能的灵活性上远不如Excel，无法满足业务用户的分析功能需求。
  但Excel也存在一些问题，长期困扰一线业务用户：

· 首先是性能问题。对于大数据量，Excel处理起来很慢，甚至超过100万行，就完全不支持;
· 数据获取的过程麻烦。特别是周期性的数据获取，每次都要找IT人员帮忙，再粘贴到excel中去;
· 共享的安全性和便利性问题。用户大都是把Excel文件通过邮件或即时通讯软件共享;首先，文件满天飞，不好管理；其次，权限不好控制，没法做到只共享分析结果，而不共享明细数据；最后，数据没法做到每个人的数据不同，根据用户权限动态更新数据。
   Smartbi Excel 分析就是面向Excel用户的数据分析工具，它结合了Excel的优点，解决了Excel的问题;

   真正做到赋能企业一线业务用户，让人人都是自助分析师，促进企业的全民数字化运营。


Smartbi Excel融合分析的用户价值

· 零学习门槛，会Excel就能自助分析，上手快、数据分析不求人;
· 对企业来说，节约人力资源，提高了生产效率;
· 为业务赋能，为管理赋能;


Smartbi Excel融合分析的优势
Smartbi Excel功能定位是基于Excel自助分析工具;    业务用户自助做报表/做分析；最大的优点是自助取数;
变换参数就能刷出新数据，提升工作效率，用户不用学其他BI工具。
功能亮点：



· 支持Excel直连数据库，数据准备自己搞定
· 支持使用Excel计算公式对线上线下数据做联合分析
· 支持使用Excel图形、数据透视表来分析和展现数据
· 支持模板和数据分离，重用分析模板，动态更新数据
· 支持对Excel中的数据进行二次加工，告别依赖于IT人员处理的困境
· 支持超大数据量处理，支持在个人Excel端运算，也支持在服务器（集群）运算
· 支持全面的权限管控，提供细粒度的权限控制，精确控制每一个用户的数据权限

                                       图：通过数据透视表进行数据准备


图：通过公式将线上线下数据联合到一起
思迈特商业智能与大数据分析软件—Smartbi是企业级商业智能和大数据分析平台，经过多年的持续发展，整合了各行业的数据分析和决策支持的功能需求。Smartbi满足最终用户在企业级报表、数据可视化分析、自助分析平台、数据挖掘建模、AI智能分析等大数据分析需求。
Smartbi提供个人版长期免费使用，有兴趣的小伙伴可以上Smartbi官网免费体验。

Excel分析链家二手房数据

本次数据分析旨在使用Excel工具分析网上爬取的链家北京而二手房数据，我们都知道爬虫爬取到的数据不出意外有很多都是混乱、错误、或者乱码的数据，所以这里使用Excel工具对数据进行清洗、整理、转换、构造等操作，同时还对数据进行可视化分析，最后采用python构建预测模型对二手房房价进行学习训练，并且预测房价。
首先导入数据：

可以从工具点击“数据”选项卡–>获取外部数据–>选择“自文本”选项

然后根据文本的特征选择相应的格式就行了



观察一下数据集，数据大体详情如下：



由于特征名看起来有点乱，可以将数据调成自己习惯观察的顺序，这里就不贴出具体操作了：
数据集一共有23677条数据，其中Elevator（电梯）特征有明显的缺失值

在Excel里有个快速填充缺失值的方法：快捷键Ctrl+G导出定位操作栏，选择定位位置和空值定位条件则会选完所有的缺失值，然后写入相应的填充值如（平均值，众数、中位数等），按Ctrl + Enter键即可全部填充完成。

不过本数据集的缺失值为类型数据，且考虑到生活中常见的房子特点，这里电梯缺失值填充方法为：将Floor（楼层）大于6层的填充为有电梯，小于或等于6层的为无电梯，可以利用函数实现：

将空值筛选出来，写入公式:IF(E2>6,“有电梯”,“无电梯”)，将公式下拉填充所有缺失值，缺失值填充完成
现在暂时也看不出什么数据的异常值，可以先进行数据可视化分析，等发现异常值再处理：
首先可以看一下Price（房价）与Size（面积）的关系，这里用散点图进行分析：



通过图表可以观察得到：

发现 Size 特征基本与Price呈现线性关系，符合基本常识，面积越大，价格越高。但是有两组明显的异常点：1.面积不到10平米，但是价格超出1000万；2. 一个点面积超过了1000平米，价格很低，需要查看是什么情况。

筛选Size，选择Size小于10平米的数据来观察：



经过查看发现这组数据是别墅，出现异常的原因是由于别墅结构比较特殊（无朝向无电梯），字段定义与二手商品房不太一样导致爬虫爬取数据错位。也因别墅类型二手房不在我们的考虑范围之内，故将其移除再次观察Size分布和Price关系。
筛选Size，选择大于1000万的来观察：



经观察这个异常点不是普通的民用二手房，很可能是商用房，所以才有1房间0厅确有如此大超过1000平米的面积，这里选择移除。

再次观察图表：



重新进行可视化发现就没有明显的异常点了。
接下来可以观察一下不同地区的价格是如何分布的：

这里可以添加一个特征，Perprice（每平米价格），直接用公式求即可，此外还需保留两位小数：Perprice=ROUND(Size/Price,2)

创建数据透视表–>选择地区作为行标签，每平米价格作为计算值，计算出各个地区的平均价格，绘制出相应的图表：





二手房均价：西城区的房价最贵均价大约11万/平，因为西城在二环以里，且是热门学区房的聚集地。其次是东城大约10万/平，然后是海淀大约8.5万/平，其它均低于8万/平。
二手房房数量：从数量统计上来看，目前二手房市场上比较火热的区域。海淀区和朝阳区二手房数量最多，差不多都接近3000套，毕竟大区，需求量也大。然后是丰台区，近几年正在改造建设，有赶超之势。

Layout(户型）分析：



不看不知道，原来房子的户型这么多，不过平常我们都是说房子几房几厅或者几室几厅，很少会听到几房几卫，而且这里也大多数是几室几厅的类型的数据，所以为了统一数据格式和方便观察，需要需要使用特征工程进行相应的处理，现在先暂时不处理。
Renovation（装修程度） 特征分析



可以看到二手当中总体还是精装修比较多，简装修次之；



从装修程度与价格的箱式图来看：

对于价格来说：精装修价格比较高，其次反而是毛坯房。

Elevator（电梯） 特征分析





结果观察到，有电梯的二手房数量居多一些，毕竟高层土地利用率比较高，适合北京庞大的人群需要，而高层就需要电梯。相应的，有电梯二手房房价较高，因为电梯前期装修费和后期维护费包含内了（但这个价格比较只是一个平均的概念，比如无电梯的6层豪华小区当然价格更高了）。

Year（年份）特征分析：



图表采用年份做维度，分别绘制出不同装修程度价格随减分推移的价格趋势

从图中可以看出：


总体房价是随着年份的增长而上升的；


2000年以后建造的二手房房价相较于2000年以前有很明显的价格上涨；


从装修程度细分来看，精装修和简装修上升趋势最为明显，其他装修一般，而毛坯房必将趋于平稳，从侧面可以看出，房子装修成本也越来越高；






从以上两张图可以看出：

1980年之前几乎不存在有电梯二手房数据，说明1980年之前还没有大面积安装电梯；
2005年后有无电梯二手房的数量都出现了明显的降低，猜测应该是买二手房的人数增加，导致空置的二手房数量减少；

Floor（楼层）特征分析：



可以看到，6层二手房数量最多，但是单独的楼层特征没有什么意义，因为每个小区住房的总楼层数都不一样，我们需要知道楼层的相对意义。另外，楼层与文化也有很重要联系，比如中国文化七上八下，七层可能受欢迎，房价也贵，而一般也不会有4层或18层。当然，正常情况下中间楼层是比较受欢迎的，价格也高，底层和顶层受欢迎度较低，价格也相对较低。所以楼层是一个非常复杂的特征，对房价影响也比较大。

以上就是本次二手房数据的可视化分析，接下来需要做更多的特征工程处理，方便建立房价预测模型。
特征工程

特征工程包括的内容很多，有特征清洗，预处理，监控等，而预处理根据单一特征或多特征又分很多种方法，如归一化，降维，特征选择，特征筛选等等。这么多的方法，为的是什么呢？其目的是让这些特征更友好的作为模型的输入，处理数据的好坏会严重的影响模型性能，而好的特征工程有的时候甚至比建模调参更重要。

前面主要是做了两个特征工程：

缺失值填充：

根据楼层 Floor 来判断有无电梯，一般的楼层大于6的都有电梯，而小于等于6层的一般都没有电梯。

数据清洗：

移除结构类型异常值和房屋大小异常值：将房屋类型为别墅（Size<10）、Size>1000的数据筛选掉了；

接下来继续，在前面的户型数量汇总图中可以现，二手房户型种类比较多，各种厅室组合搭配，竟然还有9室3厅，4室0厅等奇怪的结构。其中，2室一厅占绝大部分，其次是3室一厅，2室2厅，3室两厅。但是仔细观察特征分类下有很多不规则的命名，比如2室一厅与2房间1卫，还有别墅，没有统一的叫法。这样的特征肯定是不能作为机器学习模型的数据输入的，需要使用特征工程进行相应的处理。这里可以把数据统一成几室几厅的类型，由于数据量还是比较大的，可以将不符合类型的去掉。

具体操作如下：

新建一列：M列，在M2输入公式：=IF(RIGHT(H2,1)=“厅”,True,False),然后下拉填充即可



接着筛选O列，将为False的数据删除掉



提取“室”和“厅”创建新特征	

操作如下:

新建两列Layout_room_num（房间数）、Layout_hall_num（客厅数），分别输入公式：=LEFT(H2,1) 、=MID(H2,3,1)



根据已有特征构建新特征

Layout_total_num（总房间数）= Layout_room + Layout_hall_num、

Size_room_ratio（房间平均面积）= Size / Layout_total_num
删除无用特征

Layout、PerPrice、Garden
Year特征处理：

我们还有一个 Year 特征，为建房的年限时间。年限种类很多，分布在1950和2018之间，如果每个不同的 Year

值都作为特征值，我们并不能找出 Year 对 Price 有什么影响，因为年限划分的太细了。因此，我们只有将连续数值型特征 Year

离散化，做分箱处理。

根据各个时间段的二手房数量来分析，个人认为可以将年份分为8个区间：如下图所示：



具体操作如下：

将上图的数据创建出来，然后新建一列Year_label，利用VLOOKUP函数匹配出新特征



最后删除掉Year特征
还剩下一些特征工程因为使用Excel来操作很复杂，难于实现，所以以下将采用python的pandas模块来进行操作，同时也要在python上建立随机森林预测模型来预测房价的价格。

将数据整理保存为csv格式，然后在Jupyter notebook环境下导入数据进行操作。
Direction特征处理：

1.导入数据
import pandas as pd
data= pd.read_csv('C:\\Users\\Master\\Desktop\\lianjia.csv')
#观察数据
data.head()

输出：



2.查看Direction特征：
data.Direction.value_counts()

输出：



由于各种朝向都有，非常之多，所以这里只能贴出一小部分，但是这些在链家的信息上就是这样，没办法，为了是预测准确，这里需要将他们进行处理；

代码如下：
#两个字的以此为准
d_list_two = ['东西','东南','东北','西南','西北','南北']
#三个字的以此为准
d_list_three = ['东西南','东西北','东南北','西南北']
#四个字的朝向毫无疑问只能是东南西北了
for i in range(data.shape[0]):
    list_data = []
    for d in data.iloc[i,0]:
        list_data.append(d)
    list_data = set(list_data)
    if len(list_data) >= 4:
        data.iloc[i,0] = '东西南北'
    if len(list_data) == 2:
      for str1 in d_list_two:
        n_list = [a for a in list_data if a in str1]
        if len(n_list) == 2:
          data.iloc[i,0] = str1
          break
    if len(list_data) == 3:
      for str1 in d_list_three:
        n_list = [a for a in list_data if a in str1]
        if len(n_list) == 2:
          data.iloc[i,0] = str1
          break

#再看一下数据：
data.Direction.value_counts()          

输出：



是不是感觉清爽了很多。。。
One-hot coding

最后的特征工程就剩下 One-hot 独热编码了，因为像 Region，Year（离散分箱后），Direction，Renovation，Elevator等特征都是定类的非数值型类型，而作为模型的输入我们需要将这些非数值量化。

由于这里的操作用Excel来实现很是复杂，所以这里依然是用python来完成这一部分的操作
# 对于object特征进行onehot编码
data1 = pd.get_dummies(data)
data1.head()

输出：



一步到位，是不是很简单，好了，特征工程暂时到这里就告一段落了，接下来就是模型的构建了。
构建模型
1.导入相关的模块
from sklearn.ensemble import RandomForestRegressor
from sklearn.model_selection import train_test_split

2.构建训练数据和测试数据：
X = data1.loc[:,data1.columns != 'Price']
y = data1.loc[:, data1.columns == 'Price']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=0)

3.训练数据并预测
rf_Model = RandomForestRegressor(n_estimators=1000,random_state=42)
rf_Model.fit(X_train, y_train.values.ravel())
y_pred = rf_Model.predict(X = X_test)

4.使用R2评分来给模型打分
from sklearn.metrics import r2_score
score = r2_score(y_test, y_pred)
print(score)

输出：

0.8885695774040159
R2决定系数（拟合优度）：



模型越好：r2→1

模型越差：r2→0
呼，到这里就大功告成了。
总结	

这就是本次的数据分析与挖掘项目全部流程，说是使用Excel，后面又偷偷用了python，还是不够专业，且因为没有明确的需求目标，所以相关分析不仔细、深入，并且没能合成分析报告；可提升改进的地方非常多，可以有更好更健壮的方案代替，一些改进思考如下：

获取更多有价值的特征信息，比如学区，附近地铁，购物中心等
完善特征工程，如进行有效的特征选择
使用更优秀的模型算法建模或者使用模型融合

在Excel进行数据分析的时候，经常要分析两组数据的相关性。求出的值越接近1，那就说明相关性越大；越接近0，说明相关性越小。下面就说说具体步骤。
具体步骤如下所示：
1. 数据分析，以比较男女两组身高差异为例，首先打开excel，输入数据，然后点击左上角“文件”
2. 首先，单击Excel左上角的office图标，然后找到下方的“excel选项 ”并点击。
3. 然后在弹出的窗口中，选择“ 加载项 ”，在下方的“ 管理 ”选项中，再选择”excel加载项“，然后点击" 转到 "。
4. 在弹出的“加载宏”界面中，勾选“分析工具库”，然后点击确定即可。
5. 界面里勾选”分析工具库“，点击确定即可
6. 现在右上角已经成功添加了“数据分析”选项卡，然后点击“数据分析”选项卡，在方差分析里面选择“无重复双因素分析”。
7. 接下来在”输入区域“将所有数据包括分组名称全部选进去，在“输出区域”中点击文中空白位置即可，最后点击确定。
8. 弹框就是结果分析
9. 其实就只有三个部分，(图中已经表示出了)。
F ，P-value， F crit
如果F 大于 F crit，那么恭喜你，已经比对出了差异，在结合P-value，
若P-value大于0.01，小于0.05，表示差异显著；若P-value小于0.01，则表示差异极显著。
如果F 小于 F crit，那么P-value肯定高于0.05，则表示两组数据无差异。

​在Excel中通过数据间的关系选择合适的图表，轻松创建折线图、柱状图、饼图使其表达的主题和内容更加简单清晰。
下面我们通过Smartbi大数据分析工具介绍excel分析图表制作方法，如何制作常用的图形折线图、柱状图、饼图。
excel分析图表制作操作步骤
1、在excel上拖出数据集，并且【刷新数据】如下图

2、刷新数据后，全选数据，在【插入】选项卡上选择【数据透视表】，如下图
点击【确定】，操作数据透视表，可以拖动字段在【行】、【列】里面，如下图，

选择【插入】选项卡

选择【推荐的图表】

2.3 先插入柱形图，效果如下:

折线图：

饼图：


总结

本文通过Smartbi数据服务能力+Excel本身的图表实现的，在Smartbi平台还可以通过其他方式实现不同的业务需求、图表展现形式。有兴趣的朋友可以通过Smartbi官网免费试用。