Excel在数据管理与分析中的高级应用初探

开篇：润墨网以专业的文秘视角，为您筛选了一篇Excel在数据管理与分析中的高级应用初探范文，如需获取更多写作素材，在线客服老师一对一协助。欢迎您的阅读与分享！

【摘 要】excel是日常生活中使用频率最高的一款数据处理软件，如果能够根据不同需求，深入挖掘其数据管理与分析的功能，就可以轻而易举地解决现实生活中的许多复杂问题。本文介绍了Excel在“排序”、“高级筛选”、“VBA存储”和“蒙特卡洛”四项功能上的高级应用，对现实问题的处理具有一定的启发作用。

【关键词】Excel；高级应用；数据管理与分析

1.前言

Excel作为微软公司出品的Office系列办公软件中的组件之一，近年来成为日常生活中使用频率最高的数据处理软件，它的魅力在于不仅可以制作外观简洁漂亮的表格和图片，还可以在数据的管理和分析等方面具备强大的功能。与像Access这样专业的数据库软件相比，尽管Excel看起来简单，但是如果能够根据用户的不同需求，深入挖掘它的数据管理与分析功能，充分发挥其数据处理的优势，就可以体验到Excel在实际应用中的巨大魅力。

2.几种常用功能的高级应用

2.1 “排序”功能的高级应用

在Excel实际操作中，“排序”是常用的一项功能，最常用的排序就是使用工具栏中的“排序”选项进行排序。一般地，Excel会按照当前字段内容进行升序或者降序排练，但是我们通常会忽略到如果当前字段内容是中文汉字时，排序默认为以字母为基准，也就是按照当前内容拼音的首字母在英文字母中的出现顺序进行排列。如果我们想以笔画作为排列基准时，可以按照“数据”“排序”操作命令打开“排序”对话框，然后单击对话框左下角“选项”按钮，得到排序选项对话框如图1所示，在该对话框中选取“方式”选项下的“笔画排序”。

图1 排序选项对话框

若遇到笔画相同的情况，Excel会根据内码顺序进行排序操作。在此需要强调一点，在“排序选项”对话框中选择了排序方式后，工具栏中的顺序按钮将会一直按照此方式进行排序操作，若需要回到默认时，还需要在图1所示对话框中进行设置选项。

2.2 “高级筛选”功能的高级应用

在Ecxel实际操作中，“筛选”功能是在处理较复杂数据的情况下常用的功能。尽管普通的“自动筛选”操作简单，但是当面临更具体详细的条件来筛选数据时，就需要启用“高级筛选”功能。“自动筛选”受到对一个字段最多可以设定两个条件进行设置等限制，但这些都可以在“高级筛选”功能中得到解决。“高级筛选”功能是在某一个单元格内记录筛选条件，然后根据这些条件筛选出符合条件的记录。如果初次使用“高级筛选”功能可能会感觉到表达式的书写比较麻烦，但掌握习惯之后就可以很快速地在复杂数据中进行筛选操作。

筛选条件的编写需要遵循最基本的规则：编写作为字段的对象名称，并且需要将筛选条件编写在其下方的单元格中。筛选条件的表达式可以使用运算符与字符串、万用字符等的组合。如下面1所示筛选条件的示例。

2.3 “VBA存储”功能的高级应用

在利用Excel进行数据管理与分析的应用中常常要用VBA存储功能来转存筛选出的数据。通常我们需要将筛选出来的数据复制到其他的位置，而未被筛选出来的数据将会自动排除在被复制的范围之外。一般地我们会将筛选出的范围认定为“可见数据库”来进行复制，例如：

Set myNeed=Range（“B1”）.CurrentRegion

myNeed.AutoFilter Field：=7，Criterial：=”一班”

以第7个字段为基础进行筛选操作

myNeed.SpecialCells（xlCellType Visible）.Copy

仅仅复制可见单元格

按照刚刚提到的特性，也可以将代码改为下列形式，所得结果都是相同的：

Set myRng=Range（“B1”）.CurrentRegion

myNeed.AutoFilter Field：=7，Criterial：=”一班”

myNeed.Copy

这样我们就可以只复制筛选出来的显示范围

在此需要强调，在Excel中使用“隐藏”选项而隐藏的列和因为筛选而成为隐藏的列其操作方式是不相同的。如果使用了“隐藏”选项的单元格范围，复制操作就会连同处于隐藏状态的单元格一起复制。但是如果筛选过程中出现了有隐藏的行列，并且在其中输入了函数的情况，在复制操作时就必须要选择“看得见的单元格”来进行处理。若在实际操作中没有办法根据需要处理数据时，就要留意是否由两种不同隐藏状态造成的。

2.4 “蒙特卡洛”功能的高级应用

蒙特卡洛作为模拟随机现象的一种数学方法，它的基本思路是用一个随机过程去模拟另外一个相同概率的随机过程，也就是用这一事件是否发生来判断另一事件是否发生的过程。现实生活中的随机过程，很多问题采用解析法进行求解非常困难，但采用蒙特卡洛方法进行模拟则较为简单，首先建立随机过程概率模型，该模型的参数等于问题的解，然后对该随机过程概率模型进行观察抽样试验，计算这些参数的某一统计特性，最后根据这些统计特征求出参数的近似解。

基本操作主要有以下5步：

1）依据实际问题，建立相应的随机过程概率模型，要求所求参数的解应该是所建立模型的数学期望；

2）依据问题特点和计算的需要，采取直接模拟的方法或者降低方差的方法，来提高计算效率，降低运算时间；

3）确定伪随机数的产生方法，调用随机数发生器产生满足要求的随机数；

4）使用Excel软件中的数据分析功能，根据所求参数的统计特征，计算其统计近似值及方差；

5）将数据输入到Excel软件中，采用数据分析模块来模拟计算。

比如，设某个航空导弹射击飞机的命中概率为0.78，现在对来袭飞机发射3颗导弹，请判断射击效果。针对这个问题，我们都知道每次发射一颗导弹能否命中来袭飞机是一个随机事件，并且已知命中概率为0.78，那么我们就可以模拟这个作战过程，可以通过调用随机数来确定作战的结果。在Excel实际操作中，可以直接调用“RAND”函数来模拟产生0到1之间均匀分布的随机数，若产生的随机生小于或者等于0.78，我们就认为导弹命中来袭飞机，若随机数大于0.78则认为没有命中来袭飞机。如果我们在Excel软件中进行多次重复模拟，统计意义上的平均结果肯定接近于0.78。

发射三颗导弹，可以产生3个随机数，调用“RAND”函数得出的模拟结果如表2所示。

根据模拟结果可知，第2、3发命中来袭飞机，第1发没有命中，因此这一组3发的命中概率约为0.667。在此需要说明每一次试验都是独立的和随机的，并且还要避免重复。选择模拟次数N也是一个非常重要的问题，一般情况认为模拟次数N应该足够大，只有这样才可以得出一条更加光滑的概率分布图。

3.小结

只有不断深入挖掘Excel软件的功能，探索其在数据管理和分析方面的高级应用，其实在像Access这样专业数据库软件中的常用功能都可以由Excel来实现，尤其是如果能够继续深入探究采用VBA和Excel结合起来模仿数据库应用程序。在运用Excel软件进行蒙特卡洛模拟时既可以免去繁复的公式推导过程，又可以避免复杂程序的编写，同时在产生伪随机数和计算结果分析方面都非常方面。

作为一款卓越的数据处理软件，Excel软件拥有强大的数据管理和分析工具，不仅仅表现在“排序”、“高级筛选”、“VBA存储”和“蒙特卡洛”等功能上，还有如单变量求解工具、模拟运算表和规划求解工具等。但是，大部分用户对于这些管理和分析工具了解的太少，不能熟练运用一些具体操作。如果掌握了这些方法和技巧，并在实际情况下灵活运用这些数据管理和分析工具就能够轻松解决很多复杂的问题。

参考文献：

[1]陈邓安，丛中旖，王子明.在效能分析中应用Excel数据分析模块进行蒙特卡罗模拟[J].科学技术与工程，2012（2）.

[2]刘平.Excel在办公中的高级应用初探[J].电脑知识与技术，2010（27）.

[3]周红晓.Excel数据有效性高级应用探讨[J].福建电脑，2009（10）.

[4]周洪林.Excel函数高级应用[J].福建电脑，2009（2）.

作者简介：唐学军（1969—），女，新疆工程学院实验师，研究方向：计算机基础、多媒体制作。