EXCEL自带加载宏内容及用法简析

hjj0451

一.前言

　　关于EXCEL本身自带的标准加载宏的用法介绍，也许是大家很少用它的原因，这方面的帖子少之甚少。曾经有这方面的求知欲，但苦苦找不到资料。通过这几天的学习，我把其中的大致内容做一个总结和介绍，供大家参考和各位达人指导。在了解该标准加载宏的功能的基础上可以进行拓展，在进入VBE窗口后，输入自带的标准加载宏密码，对其功能进行二次开发和利用。

　　加载宏主要包括三种：Exce加载宏(扩展名为.xla)、COM加载宏、自动化加载宏。Excel可以使用两种类型加载宏：Excel加载宏和COM加载宏。微软在Excel安装程序中提供了15个加载宏程序(大家不一定完全安装)，可在其官方网站中下载其他的加载宏程序。我这里介绍的是EXCEL2003本身自带的标准加载宏。其自带的标准加载宏有：

　　查阅向导(介绍在1楼)

　　条件求和向导(介绍在2楼)

　　分析工具库(介绍在3楼)

　　分析工具库—VBA函数(介绍在4楼)

　　规划求解(介绍在5楼)

　　欧元转换工具(介绍在6楼)

　　Internet AssistantVBA(介绍在7楼)。

　　最后做一个关于防止宏自动加载和运行的小结(介绍在8楼)

　　本文在EXCEL2003的基础上按上面的顺序分别对其进行介绍。

　　EXCEL自带的标准加载宏的使用方法，选择-工具-加载宏，就会出现下面的对话框：
　　
　　顺便提一下那个隐藏的很深很难懂的自动化选项，点自动化选项，可以进入自动化宏加载选项：
　　

二.查阅向导

　　这个就是自动创建关于列表的index+match的查找公式，数据量大的话使用该工具可能比手动写index+match公式快一点。选择-工具-加载宏，勾选查阅向导，工具选项下就会多一个向导菜单，下面向导－查阅下拉菜单。

　　Step1:选择需要分析查找的数据区域，如下表的A1:E7，选择工具－向导－查阅，就会出现下面的对话框，其包含行列标志的区域自动选择完成：

　　

　　Step2:直接点下一步，出现如下对画框：

　　选择所要查找的行、列的值，以进行精确查找。可以在下拉列表里选择，本例选择要查询的是”张3”的”爱好”，点下一步，出现如下对话框：

　　

　　注意，此步若选择没有匹配的行标志或列标志，向导将让你输入带匹配的值，查找时将选择小于等于待匹配的值来进行查找，返回模糊匹配的结果。

　　Step3:选择只显示公式，点下一步，选择存放公式的单元格为D9:

　　Step4:点完成，D9显示查询的结果，并显示具体的index+match公式：

　　

1. =INDEX($A$1:$E$7,MATCH(“张3”,$A$1:$E$7,),MATCH(“爱好”,$A$1:$E$1,))

复制代码

　　

　　若Step2里选择没有匹配的行标志或列标志，此时查询结果将返回模糊匹配的结果，返回小于等于查找值的最大值的查询结果。显示的公式是忽略第4参数d的vlookup+match：

1. =Vlookup(“李4”,$A$2:$E$7,MATCH(“爱好”,$A$1:$E$1,))

复制代码

　　

　　在Step3里，若选择”复制公式及参数”,在Step4里一次选择3个单元格则可存放”张3、爱好、爱好是什么”这一条完整的记录到3个你所选择的单元格。此时index+match公式引用的单元格为你选择的存放查找记录的单元格，因此只要修改查找记录即可得到新的查找结果，而不用修改公式。

　　题外话：在查阅向导得到的公式的基础上可以进一步修改和利用。查阅向导默认采用index+match的精确查找方式和vlookup+match的模糊查找方式，突显该查找组合的效率方面的优越性。

　　(后续)To be Continued

hjj0451

三．条件求和向导

　　提供了对列表中的数据根据不同的条件求和的工具，这个和查阅向导，可以实现自动生成条件求和SUM+IF公式。

Step1:点工具-加载宏，勾选条件求和向导，工具栏里会增加一个菜单：向导-条件求和。选择需要条件求和的区域，选择工具-向导-条件求和，会出现下面的页面：

　　
　　

Step2:选择求和列标志段(这里无法对行标志进行求和)和条件，可以设置多个条件，点添加条件即可。条件的比较运算符有6种。下面对年龄大于等于19的男性的成绩进行求和：

Step3:同查阅向导的Step3，可选择只显示公式，此时只输出SUM+IF求和结果及公式；也可选择复制公式及条件，同时把求和条件和求和结果显示在多个单元格，改变条件即可改变求和结果而不用改变公式。

选择复制公式及条件，依次选择3个单元格(2个放条件+1个放结果)得到下面的结果：

这里有2个条件自动生成SUM+IF组成的数组公式：

1. =SUM(IF($C$2:$C$7>=C10,IF($B$2:$B$7=D10,$F$2:$F$7,0),0))

复制代码

多个条件则多个IF嵌套，1个条件也不能生成SUMIF公式，还是数组公式：

1. =SUM(IF($B$2:$B$7="男",$F$2:$F$7,0))

复制代码

　　可以看到，条件求和向导和查阅向导很相似，一个是SUM+IF条件求和，一个是INDEX+MATCH或VLOOKUP+MATCH查询结果。

hjj0451

三.分析工具库

　　分析工具库大家常常用，因为要用RANDBETWEEN。其提供一组包括金融、统计和工程类的数据分析工具和函数，增添了Excel中没有包含的统计和分析功能。主要有方差分析、相关系数、协方差、描述统计、指数平滑、F-检验 双样本方差、傅利叶分析、直方图、移动平均、随机数发生器、排位与百分比排位、回归、抽样、t检验、z检验等。

　　选择工具-加载宏-勾选分析工具库，则可以使用一些函数，如RANDBETWEEN、QUOTIENT、GCD、LCM、CUMIPMT、XIRR、XNPV，这里顺便对只有加载分析工具库才能使用的函数进行一下归纳，大部分是我们没有见过或用过的。

　　日期函数类：

EDATE、EMONTH、NETWORKDAYS、WEEKNUM、WORKDAY、YEARFRAC；

　　工程函数类：

　　BESSELI、BESSELJ、BESSELK、BESSELY、BIN2DEC、BIN2HEX、BIN2OCT、COMPLEX、CONVERT、DEC2BIN、DEC2HEX、DEC2OCT、DELTA、ERF、ERFC、GESTEP、HEX2BIN、HEX2DEC、HEX2OCT、IMABS、IMAGINARY、IMARGUMENT、IMCONJUGATE、IMCOS、IMDIV、IMEXP、IMLN、IMLOG10、IMLOG2、IMPOWER、IMPRODUCT、IMREAL、IMSIN、IMSQRT、IMSUB、IMSUM、OCT2BIN、OCT2DEC、OCT2HEX。

　　财务函数类：

ACCRINT、ACCRINTM、AMORDEGRC、AMORLINC、COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPNUM、COUPPCD、CUMIPMT、CUMPRINC、DISC、DOLLARDE、DOLLARFR、DURATION、EFFECT、FVSCHEDULE、INTRATE、 MDURATION、NOMINAL、 ODDFPRICE、ODDFYIELD、ODDLPRICE、ODDLYIELD、PRICE、 PRICEDISC、PRICEMAT、RECEIVED、TBILLEQ、TBILLPRICE、TBILLYIELD、XIRR、 XNPV、 YIELD、YIELDDISC、YIELDMAT。

　　信息函数类：

　　ISEVEN、ISODD

　　数学与三角函数类：

　　FACTDOUBLE、GCD、LCM、MROUND、MULTINOMIAL、QUOTIENT、RANDBETWEEN、SERIESSUM、SQRTPI。

　　加载分析工具库宏后除多了一些可以使用的函数之外，还多了以下分析工具：

　　这都是一些统计学上的分析工具，一般很少用，特别专业的问题要借助专业统计分析软件如SPSS来解决。

　　也可以不加载分析工具库，用代码来调用它，见该文http://club.excelhome.net/thread-364162-1-1.html。

　　另外与分析工具库有关的自带加载宏就是分析工具库-VBA了，调用VBA里的函数需要用到它。　　　　　

hjj0451

四.分析工具库—VBA函数

　　允许开发人员用分析工具库的语法发布金融、统计及工程分析工具和函数。为分析工具库提供的VBA函数。

　　在要从VBA代码里调用函数时需要加载该自带宏，以让分析工具库使用该VBA函数，因此只有在加载了分析工具库宏后才能使用VBA函数。加载完分析工具库—VBA函数宏后会在工程窗口会多一个工程模块：

　　
　　该加载宏是为ANALY32.XLL提供接口模块，在下面的文件夹下可以找到：C:\Program Files\MicrosoftOffice\Office11\Library\Analysis\..
　　调用其内部函数有两种方法，例如：

1. Application.Run("FunctionName","Parameters")

复制代码

1. CallMsgbox(Application.Run("atpvbaen.xla!Complex",3,3)) = 3+3i

复制代码

　　该两种方式有细微差别，第一种方法较好。在VBE里点工具-引用，勾选atpvbaen.xls，可以在调用函数时不用带前缀。

1. Call MsgBox(complex(3, 3)) = 3+3i

复制代码

1. Call MsgBox([atpvbaen.xls].complex(3, 3)) = 3+3i

复制代码

　　　　　

hjj0451

五．规划求解

　　规划求解大家也常用，简要介绍一下。规划求解是一种公式求解和优化的工具，对基于可变单元格和条件单元格的假设分析方案进行求解计算。

　　规划求解是对单变量求解的扩展，比较常见的线性规划求解(最大值、最小值、方程)。规划求解最多有200个变量，单变量求解只能有1个，具体介绍可参阅该网站http://www.solver.com/

　　如下的例子：

　　

　　设置好目标单元格和条件：

　　

　　执行完4步迭代后得到：

　　

     规划求解还能设置迭代模型及迭代参数：

　　

     规划求解还是很值得研究的。

　　　　　

hjj0451

六．欧元转换工具

提供用于欧元转换的工具。将数值的格式设置为欧元格式，并增加一个 EUROCONVERT工作表函数，用于转换货币。该工具支持的货币有：

国家/地区	基本货币单位	ISO代码
比利时	法郎	BEF
卢森堡	法郎	LUF
德国	德国马克	DEM
西班牙	西班牙比塞塔	ESP
法国	法郎	FRF
爱尔兰	爱尔兰磅	IEP
意大利	里拉	ITL
荷兰	荷兰盾	NLG
奥地利	奥地利先令	ATS
葡萄牙	埃斯库多	PTE
芬兰	芬兰马克	FIM
希腊	德拉克马	GRD
斯洛文尼亚	托拉尔	SIT
欧盟成员国	欧元	EUR

　　作为两个外部函数之一的EUROCONVERT(另一个是SQL.REQUEST)，其使用方法为：

　　

1. =EUROCONVERT(货币值或单元格引用，源货币代码或包含该代码的单元格引用，目标货币代码或包含该代码的单元格引用，逻辑值)

复制代码

　　逻辑值TRUE或FALSE/0表示计算精度和显示精度。此函数不应用数字格式且无法用于数组公式。
　　个人感觉这个工具是很没有用的东西。

hjj0451

七．InternetAssistant VBA

　　使用Internet Assistant语法，将Excel数据发布到网站上，在EXCEL97里这个叫Internet Assistant Wizard，该标准宏只能在EXCEL97里使用。可以直接把EXCEL文件转化为HTML格式，也可以把EXCEL数据添加到已经存在的WEB页面内容里，并实现WEB页面内容随着你EXCEL表格的内容的改变而自动更新。把使用方法见：

　　　http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17716

　　谁有EXCEL97，可以试一下。

　　这个工具到底现在还留在加载宏选项里是为了什么？只能在EXCEL97里用？

hjj0451

　　　　

八.取消宏自动运行的方法

　　尽管EXCEL自带宏不会自动运行，但有些自定义的宏是很讨厌的，自动运行，让本来很熟悉的东西变的陌生，有以下方法可以尝试。当然更有难度的还有借助VBA代码彻底清除。

　　不需要加载的宏可以取消该宏加载勾选，就不会在打开EXCEL表格时弹出宏安全性警告。

　　彻底删除宏代码所在的模块。

　　彻底删除安装在“Microsoft Office\Office”文件夹下的“Library”文件夹或其子文件夹，或“Windows”文件夹下的“Program Files\User Name\Application Data\Microsoft\AddIns”。

　　为了阻止打开文件时自动运行的宏的运行，选择文件-打开，选择打开的文件名字，在点击“打开”时按住Shift键；选择文件-关闭，在点击关闭时按住Shift键，可以阻止在关闭时自动运行的宏的运行；按住Shift键并点击窗口右上角的”×”也可以在关闭工作薄时阻止宏的运行。

　　运行下面的代码，可以查看你的EXCEL表格所加载的宏的名称及保存的文件夹路径，可以有针对性地删除和修改他们。

1. 
   
2. Public SubAddins_List()
   
3. 　　Dim icount As Integer
   
4. 　　For icount = 1 To Application.Addins.Count
   
5. 　　　　　　Range("A" & icount).Value =Application.AddIns(icount).Name
   
6. 　　　　　　Range("B" & icount).Value =Application.AddIns(icount).FullName
   
7. 　　　　　　Range("C" & icount).Value =Application.AddIns(icount).Installed
   
8. 　　　　　　Range("D" & icount).Value =Application.AddIns(icount).Path
   
9. 　　Next icount
   
10. End Sub

复制代码

　　　　　　　　　　　　　　　　　

hjj0451

留作补充。　　　　　　　　　　　　　　

hjj0451

此贴发在基础版应该很合适不过了