Excel2010数据挖掘工具的应用研究

徐军伟　程国忠

摘要：Excel2010作为一种数据挖掘工具，既简单又实用。该文重点研究了Excel2010在数据挖掘方面的功能，并以公共自行车在城市交通网络中的应用为例，介绍了Excel2010数据挖掘方法并对Excel2010数据挖掘算法进行了分析。

关键词：Excel2010；数据挖掘

中图分类号：TP317 文献标识码：A 文章编号：1009-3044（2014）01-0004-04

随着计算机技术的发展和数据库技术的广泛应用，人们积累的数据越来越多，大量数据背后隐藏着许多有价值的信息。挖掘大量数据背后有价值的信息，促成了数据库中知识发现（Knowledge Discovery in Databases，KDD）的产生。数据挖掘（Data Mining）是知识发现（KDD）最核心的部分[1]。数据挖掘在经历了十几年的快速发展后，已经逐渐成为一门独立的应用学科。

Excel作为Microsoft Office的组件，日常工作中经常使用。它以其直观的界面、出色的计算功能和图表工具，再加上Microsoft成功的市场营销，使Excel成为最流行的个人计算机电子制表和数据处理软件。Excel 2010数据挖掘工具是一个功能强大的工具。它提供一个快速直观的界面，可用于创建、测试和管理数据挖掘结构和模型，同时不会降低 SQL Server Analysis Services 中的数据挖掘所提供的强大的自定义功能。[2] Excel 2010数据挖掘工具提供的一些向导和工具，可轻松地从数据中提取有意义的信息。它们可以找出隐藏在复杂数据中的模式和趋势，并通过图表和交互式查看器等方式使这些模式可视化，然后生成可用于演示和业务分析的丰富多彩的汇总信息。它可以对存储在 Microsoft Office Excel 表中的数据进行相关性分析和预测，也可以创建和修改存储在 Analysis Services 实例中的数据挖掘模型，还可以在 Microsoft Office Visio 中以图形方式显示结果。Excel 2010数据挖掘工具除了提供数据建模算法外，还提供一个集测试、预测和绘图于一体的桌面数据挖掘解决方案。因此，Excel 2010数据挖掘工具的有效利用将大幅提高数据挖掘的效率。Excel2010成为最简单实用的数据挖掘软件，使数据挖掘分析方法得到推广和应用。

1 数据挖掘介绍

数据挖掘（Data Mining）是从大量数据中挖掘有趣模式和知识的过程。[3]旨在从大量的、不完全的、有噪声的、模糊的、随机的数据中，提取隐含在其中的、人们事先不知道的而又潜在有用的信息和知识。还有很多和这一术语相近似的术语，如从数据库中知识发现（Knowledge Discovery in Database，KDD）、数据分析、数据融合（Data Fusion）以及决策支持等。

数据挖掘系统的典型结构，如图1所示。数据源包括数据库、数据仓库、Web、其他信息存储库或动态地流入系统的数据。数据挖掘系统经由以下步骤的迭代序列组成：1）数据清理，消除噪声和删除不一致的数据。2）数据集成，多种数据源可以组合在一起。3）数据选择，从数据库中提取与分析任务相关的数据。4）数据变换，通过汇总或聚集操作，把数据变换和统一成适合挖掘的形式。5）数据挖掘，基本步骤，使用智能方法提取数据模式。6）模式评估，根据某种兴趣度度量，识别代表知识的真正有趣的模式。7）知识表示，使用可视化和知识表示技术，向用户提供数据挖掘的知识。

步骤1—4是数据预处理的不同形式，为挖掘准备数据。数据挖掘步骤可能与用户或知识库交互。有趣的模式提供给用户，或作为新的知识存放在知识库中。

2 Excel2010数据挖掘工具介绍

在Excel2010中使用数据挖掘工具之前，需要在适当的操作系统环境下安装好数据挖掘外接程序，并且要有Microsoft SQL Server Analysis Services （SSAS） 的支持。该文使用的操作系统环境是Windows7，Excel版本是2010，数据库版本是Microsoft SQL Server 2012，使用Microsoft SQL Server 2012 Office 2010 数据挖掘外接程序。Microsoft SQL Server 2012 Office 2010 数据挖掘外接程序利用了 Analysis Services 数据挖掘引擎的强大功能。也就是说，可以在熟悉的 Office 环境中使用 Microsoft SQL Server 2012 Analysis Services实例，运行算法，快速处理和执行复杂的分析。Microsoft SQL Server 2012 Office 2010 数据挖掘外接程序有助于揭示数据中隐藏的模式和关系，然后利用它们提高分析质量。

Excel2010采用外接程序的形式来实现数据挖掘功能。Microsoft SQL Server 2012 Office 2010数据挖掘外接程序主要包括三个模块：一是Excel表分析工具，通过简单的鼠标操作，即可检测和分析数据中值的关键影响因素，突出显示与其余数据不符的值；二是Excel 数据挖掘客户端，使用电子表格数据，或使用可通过 Analysis Services 数据库访问的外部数据，在 Excel 内经历完整的数据挖掘模型开发生命周期。三是Visio 数据挖掘模板，以可以加注的 Visio 绘图形式呈现和共享挖掘模型，以提供更好的数据挖掘结果展示。Excel2010数据挖掘外接程序结合了SSAS（SQL Server 2012 Analysis Services）的强大功能，使用起来更加方便。

安装完成Microsoft SQL Server 2012 Office 2010 数据挖掘外接程序后的Excel2010界面如图2所示。在Excel2010的菜单中出现“数据挖掘”选项，选择“数据挖掘”选项后即可看到数据挖掘工具区。endprint

图2 Excel2010数据挖掘工具

3 Excel2010数据挖掘工具的功能

Excel2010数据挖掘工具可以创建、测试和管理数据挖掘结构和模型。主要包括以下部分：

1） 数据准备：浏览、清除、重新标记数据以及为数据分区，查看和清除数据，以便为数据挖掘任务做好准备。浏览数据，查看单个列中数据的分布情况和数据类型。清除数据，通过标识、修改或取消不完整的值来删除离群值。重新标记数据，更改表达值或对值进行分组的方式，以使分析更简单。示例数据，帮助创建新的数据集或定型以及测试数据集。可以使用随机抽样获取一部分代表数据，或调整数据的平衡性以增加特定值的比例。

2） 数据建模：分析数据，分类数据、预测趋势、标识关联或查找分类。用于从数据中派生模式，根据属性对数据行分组或者研究关联。 此工具功能区中的向导基于 Analysis Services的数据挖掘算法。分类，用于生成一个分类模型，它根据模型中其他列的值来预测某一列的值。估计，用于生成一个估计模型，它提取数据模式并使用这些模式来预测连续的数字、日期或时间值。聚类分析，用于生成一个聚类分析模型，它检测具有类似特征的行组。关联，用于生成一个关联模型，它检测同时在多个事务中出现的项之间的关联性：例如，用于购物篮分析。预测，用于生成一个预测模型，它检测一个单元序列中的模式，然后预测其他值。

高级可用于创建挖掘结构，生成支持多面分析的数据结构，并创建自定义数据挖掘模型。在交互式 UI 中创建自定义数据挖掘查询。根据存储在 Excel 中的数据，使用 SQL Server Analysis Services 中的任意数据挖掘算法来生成新的数据挖掘模型。 通过该向导可以使用查询编辑器来自定义参数并生成数据挖掘扩展插件 （DMX） 语句。高级还可以向结构中添加模型，通过创建新的相关模型来修改现有数据结构。 通过这些新模型，可以使用不同的数据挖掘技术来分析相同的数据。

3） 准确性和验证：测试和评估模型，创建用于分析数据挖掘解决方案准确性的图表，以图形方式显示结果，同时显示常规统计度量值。准确性图表，通过生成提升图或散点图来评估数据挖掘模型的性能。分类矩阵，通过创建基于模型的精确预测和不精确预测的汇总图表，评估分类模型的性能。利润图，通过将预测的准确性与基于预测所采取行动的成本和效益进行绘图，以了解数据挖掘模型的影响。交叉验证，用于创建报表，汇总模型在数据集的多个子集间的准确性，以此确定模型的稳定程度。

4） 模型用法：显示模型，使用自定义查看器浏览结果。 使用内置文档向导跟踪和管理分析过程。 浏览模型，用于在包含多个图形和工具的"浏览"窗口中查看现有数据挖掘模型。可以浏览、筛选和自定义数据挖掘结果。文档模型，用于创建提供有关数据挖掘模型详细信息的报表，以便您更好地理解和跟踪模型中的更改。查询，用于针对现有数据挖掘模型创建预测查询。还可以使用"数据挖掘高级查询编辑器"能够以交互方式生成复杂的 DMX 语句。

5） 管理：查看并管理 SQL Server Analysis Services 实例中存储的现有数据挖掘解决方案。管理模型，处理当前连接上的现有挖掘模型和结构。

6） 连接：用于管理与 Analysis Services 实例之间连接的向导。为了使用数据挖掘工具和算法，必须定义与 Analysis Services 实例的连接。使用"跟踪"向导可以监视通过连接发送的所有活动。所有活动作为 DMX 语句存储，这样便于排除数据挖掘会话中的故障，也便于保存信息以备日后使用。连接，用于创建和修改与 Analysis Services 的连接。跟踪，提供对 Excel 客户端和 SQL Server 服务器之间交互的不间断监视。

4 Excel2010数据挖掘工具的应用

统计信息是统计研究的产物，而统计研究的关键问题就是统计分组和频数统计。该文以公共自行车在城市交通网络中的应用为例，在Excel2010中采用数据挖掘方法分析处理数据，分别统计在公共自行车服务系统中，自行车租赁各站点中每天的借车频次和还车频次。在公共自行车管理中心数据库中使用单日数据，对借出车站号进行分类，使用Excel2010数据挖掘模块中的分类， Microsoft Decision Trees算法创建并定型模型以便为数据分类。得到单日公共自行车租赁各站点的借车频次，如图3所示。同法得到单日公共自行车租赁各站点的还车频次。

运用Microsoft SQL Server 2012 数据挖掘外接程序的Excel2010数据挖掘工具创建数据挖掘模型，运用该环境下的数据挖掘算法生成公共自行车在城市交通网络中应用的数据挖掘模型，轻松得到自行车租赁各站点中每天的借车频次和还车频次。

图3 单日公共自行车租赁各站点的借车频次

然后借助Excel2010的数据挖掘功能对用车时长进行数据挖掘分析。对借还车使用时间在1分钟以上的借还车情况进行分析，首先使用Excel2010数据挖掘模块数据准备中的清理数据标记离群值功能，剔除掉1分钟以下的数据。对用车时长进行类别检测，得到检测了5个类别和类别特征，如图4所示。Excel2010对挖掘结果进行直观展现，如图5所示。可以看出，绝大部分自行车借车时长在67分钟以内，因此将公共自行车的借车免费时长定在1小时内是合适的。60 min内免费租用；60 min以上至120 min（含），收取1元租车服务费；120 min以上至180 min（含），收取2元租车服务费；超过180 min按3 元·h-1计费（不足1 h的按 1 h 计）。[4]此收费标准比较合适。

图4 用车时长类别检测结果

图5 用车时长类别配置文件

5 Excel2010数据挖掘工具的算法

Excel2010作为一种数据挖掘工具提供多种数据挖掘功能。数据挖掘模型功能的实现都是通过特定的数据挖掘算法来实现的。一种算法不可能完成所有类型的数据挖掘，对于某一类问题，数据本身的特性往往会影响到用户所选用的数据挖掘算法，所以需要用户从数据中找到最佳的挖掘算法。Excel2010数据挖掘工具中的数据挖掘算法是基于数据创建模式的机制。在创建模型时，必须选择适合于目标和要分析的数据的算法。

SQL Server 2012 Office2010数据挖掘外接程序所使用的算法基于 Analysis Services 提供的算法。包括以下算法类型：

1）"分类算法"基于数据集中的其他属性预测一个或多个离散变量。

2）"回归算法"基于数据集中的其他属性预测一个或多个连续变量。

3）"分段算法"将数据划分为组或分类，这些组或分类的项具有相似的属性。

4）"关联算法"查找数据集中不同属性之间的相关性。 此类算法通常用于创建关联规则。 关联规则可用于市场篮分析。

5） "顺序分析算法"可汇总数据中的常见顺序或事件，如用户在浏览网站时所遵循的路径。

Excel2010数据挖掘工具中的数据挖掘算法都是当前数据挖掘各领域发展比较成熟的常用的算法。这些算法主要有：关联规则、聚类分析、决策树、线性回归、逻辑回归、神经网络、时序分析等。

6 结束语

Excel2010结合Microsoft SQL Server 2012 Office 2010 数据挖掘外接程序，在多种算法的支持下，具有很强的数据挖掘功能，同时能很好的将挖掘结果展现出来。Excel2010数据挖掘工具，以其简便易用性、直观高效性的优势在实际的工作和研究中的应用越来越广泛，能基本满足实际的数据分析需求。

参考文献：

[1] 罗森林，马俊，潘丽敏编著.数据挖掘理论与技术[M].北京：电子工业出版社，2013.

[2] 上田和明，苅田正雄，渕上美喜，等.Excel学数据挖掘[M].孙英英，译.北京：科学出版社，2012.

[3] Jiawei Han，Micheline Kamber.数据挖掘：概念与技术[M].范明，孟小峰，译. 3版.北京：机械工业出版社，2012.

[4] 姚遥，周扬军.杭州市公共自行车系统规划[J].城市交通， 2009（4）：30-38.

图4 用车时长类别检测结果

图5 用车时长类别配置文件

5 Excel2010数据挖掘工具的算法

Excel2010作为一种数据挖掘工具提供多种数据挖掘功能。数据挖掘模型功能的实现都是通过特定的数据挖掘算法来实现的。一种算法不可能完成所有类型的数据挖掘，对于某一类问题，数据本身的特性往往会影响到用户所选用的数据挖掘算法，所以需要用户从数据中找到最佳的挖掘算法。Excel2010数据挖掘工具中的数据挖掘算法是基于数据创建模式的机制。在创建模型时，必须选择适合于目标和要分析的数据的算法。

SQL Server 2012 Office2010数据挖掘外接程序所使用的算法基于 Analysis Services 提供的算法。包括以下算法类型：

1）"分类算法"基于数据集中的其他属性预测一个或多个离散变量。

2）"回归算法"基于数据集中的其他属性预测一个或多个连续变量。

3）"分段算法"将数据划分为组或分类，这些组或分类的项具有相似的属性。

4）"关联算法"查找数据集中不同属性之间的相关性。 此类算法通常用于创建关联规则。 关联规则可用于市场篮分析。

5） "顺序分析算法"可汇总数据中的常见顺序或事件，如用户在浏览网站时所遵循的路径。

Excel2010数据挖掘工具中的数据挖掘算法都是当前数据挖掘各领域发展比较成熟的常用的算法。这些算法主要有：关联规则、聚类分析、决策树、线性回归、逻辑回归、神经网络、时序分析等。

6 结束语

Excel2010结合Microsoft SQL Server 2012 Office 2010 数据挖掘外接程序，在多种算法的支持下，具有很强的数据挖掘功能，同时能很好的将挖掘结果展现出来。Excel2010数据挖掘工具，以其简便易用性、直观高效性的优势在实际的工作和研究中的应用越来越广泛，能基本满足实际的数据分析需求。

参考文献：

[1] 罗森林，马俊，潘丽敏编著.数据挖掘理论与技术[M].北京：电子工业出版社，2013.

[2] 上田和明，苅田正雄，渕上美喜，等.Excel学数据挖掘[M].孙英英，译.北京：科学出版社，2012.

[3] Jiawei Han，Micheline Kamber.数据挖掘：概念与技术[M].范明，孟小峰，译. 3版.北京：机械工业出版社，2012.

[4] 姚遥，周扬军.杭州市公共自行车系统规划[J].城市交通， 2009（4）：30-38.

图4 用车时长类别检测结果

图5 用车时长类别配置文件

5 Excel2010数据挖掘工具的算法

Excel2010作为一种数据挖掘工具提供多种数据挖掘功能。数据挖掘模型功能的实现都是通过特定的数据挖掘算法来实现的。一种算法不可能完成所有类型的数据挖掘，对于某一类问题，数据本身的特性往往会影响到用户所选用的数据挖掘算法，所以需要用户从数据中找到最佳的挖掘算法。Excel2010数据挖掘工具中的数据挖掘算法是基于数据创建模式的机制。在创建模型时，必须选择适合于目标和要分析的数据的算法。

SQL Server 2012 Office2010数据挖掘外接程序所使用的算法基于 Analysis Services 提供的算法。包括以下算法类型：

1）"分类算法"基于数据集中的其他属性预测一个或多个离散变量。

2）"回归算法"基于数据集中的其他属性预测一个或多个连续变量。

3）"分段算法"将数据划分为组或分类，这些组或分类的项具有相似的属性。

4）"关联算法"查找数据集中不同属性之间的相关性。 此类算法通常用于创建关联规则。 关联规则可用于市场篮分析。

5） "顺序分析算法"可汇总数据中的常见顺序或事件，如用户在浏览网站时所遵循的路径。

Excel2010数据挖掘工具中的数据挖掘算法都是当前数据挖掘各领域发展比较成熟的常用的算法。这些算法主要有：关联规则、聚类分析、决策树、线性回归、逻辑回归、神经网络、时序分析等。

6 结束语

Excel2010结合Microsoft SQL Server 2012 Office 2010 数据挖掘外接程序，在多种算法的支持下，具有很强的数据挖掘功能，同时能很好的将挖掘结果展现出来。Excel2010数据挖掘工具，以其简便易用性、直观高效性的优势在实际的工作和研究中的应用越来越广泛，能基本满足实际的数据分析需求。

参考文献：

[1] 罗森林，马俊，潘丽敏编著.数据挖掘理论与技术[M].北京：电子工业出版社，2013.

[2] 上田和明，苅田正雄，渕上美喜，等.Excel学数据挖掘[M].孙英英，译.北京：科学出版社，2012.

[3] Jiawei Han，Micheline Kamber.数据挖掘：概念与技术[M].范明，孟小峰，译. 3版.北京：机械工业出版社，2012.

[4] 姚遥，周扬军.杭州市公共自行车系统规划[J].城市交通， 2009（4）：30-38.