excel如何求一列数据中某个数据的方差

点击联系发帖人 时间：2022-05-11 04:58

excel中求两列数据的差的和

今天这篇文章是为回应读者的要求，对如何应用EXCEL的规划求解功能实现投资组合在均值方差方法下的配置优化再次进行实例说明。

假设本投资组合由五只交易所交易基金构成，分别为：

设定的前提条件为这五只基金的原始配置比率均为20%，即等权重配置。为实现优化，必然会对基金的配置权重进行调整，某些基金的权重要增加，有的要减少，但不能净做空，也不加杠杆，还要保持整个投资组合全仓配置，此外，买卖基金的单位交易成本假定为市值的，注意，原始数据下载后，应使用adjusted close，即经调整后的数据，该数据对分红金额进行了除权处理，能更准确地反映回报率的变化。

数据下载整理完成后，结果如下：

第二步是计算月度回报率，公式为本月回报率=LN（本月月度收盘价/上月月度收盘价），也可用“本月月度收盘价/上月月度收盘价）-1”的方法计算，结果应相差不大，同时计算单项资产月度回报率的均值。本例中收盘价数据共有13个，因此月度回报率数据应为12个，如下：

下一步是计算投资组合的方差值，由于整个投资组合的方差值=∑（单项资产回报率的方差），因此需要使用“协方差”功能形成单项资产回报率的协方差矩阵：

打开“协方差”功能后，“输入区域”为五只基金各月度回报率的数值，光标点选红框圈定的区域即可，其它选项参照图示，“输入区域”选EXCEL表中任一较大空白区域即可。

点击确认键后，形成单项资产回报率的协方差矩阵如下：

空白区域需要用相应的协方差数据填满，也就是将左侧红圈区域的纵列数据依次转换放在右侧红圈区域的横排单元格中。以纵列一为例，光标选定红色圈定区域，在第一个单元格内输入公式TRANSPOSE（），（）内为粉色圈定区域的单元格。

然后同时按键“shift+ctrl+enter”，左侧红圈区域的纵列数据依次转换放在右侧红圈区域的横排单元格中，依次填满全部空白区域。

左纵列右横排数据的对应关系如下，确保同颜色的左右区域数值相同。

下一步是分别计算单项资产回报率的方差，最后再将单项资产回报率的方差加总得出整个投资组合回报率的方差。

前面提到，这五只基金的原始配置比率均为20%，最终要实现的目的是：在投资组合优化后的回报率达到预期目标的前提下，通过对各基金的配置权重进行调整，实现投资组合回报率的方差值最小化。

因此，红圈区域的变化值就是基金权重调整的百分比数值，也是即将使用的规划求解功能的“变动项”。

为此需要通过该公式确定逻辑关系：各资产配置权重调整后的新比率=当前的配置比率+变化值，比如下图中XLK基金初始的配置比率为手工设定的20%，在尚未进行调整的时候，变化值为0%，因此新的配置比率=20% +0%=20%，在EXCEL表中单元格数据之间的逻辑关系为L44=K44+M44。

其它四个基金的配置权重调整的逻辑均照此办理，L44单元格中的公式设定完毕后，光标按住该单元格下拉至L48，然后在单元格L49加总计算整个投资组合的配置比率，整个投资组合的配置比率=∑（单项资产的配置比率）。

下一步是计算投资组合优化后的回报率，但是在未调整权重的情况下该值肯定为初始回报率，整个投资组合优化后的回报率=∑（单项资产新的配置比率*相应的配置权重）-单位交易成本*∑（单项资产配置比率调整值的绝对值）。计算结果显示，在这五只交易所交易基金在初始配置权重相等的情况下，整个投资组合的初始回报率为3.71%。

我的目标是想让投资组合优化后的回报率达到3.82%，同时使目前为0.0018113的投资组合回报率的方差值尽可能保持最低，从而达到优化投资组合的目标。为此需使用规划求解功能，规划求解的目标单元格为投资组合回报率的方差所在单元格N40，数值设定为“最小值=0”，可变单元格为单向资产配置比率变化值所在单元格M44:M48，约束条件主要为三大项：1，投资组合优化后的回报率=投资组合预期回报率，注意该预期回报率的数值如果设定过高，有可能规划求解无法得出结果，一般应在当前的回报率与投资组合中单项资产回报率之间寻求一个妥协；2，整个投资组合的配置比率=100%，即单项资产不能净做空，也不加杠杆，还要保持整个投资组合全仓配置；3，单项资产的配置比率≧0%，否则规划求解的计算结果可能会出现负的配置比率，从而违背条件2。

设定好以上各项条件，下一步就是实际计算，规划求解的结果显示，投资组合优化后的回报率达到了3.82%，各基金的配置权重调整的情况见下表中红圈部分，可见在单项资产的配置比率均≧0%，也就是没有出现净做空，加杠杆和整个组合全仓配置的情况下，这个投资组合回报率的方差值为0.0023782。

}

下载资源需要8积分【人民币8元】

支付成功后，系统会自动生成账号（用户名和密码都是您填写的邮箱或者手机号），方便下次登录下载和查询订单；

2、PDF文件下载后，可能会被浏览器默认打开，此种情况可以点击浏览器菜单，保存网页到桌面，既可以正常下载了。

3、本站不支持迅雷下载，请使用电脑自带的IE浏览器，或者360浏览器、谷歌浏览器下载即可。

4、本站资源下载后的文档和图纸-无水印,预览文档经过压缩，下载后原文更清晰

excel下地质数据三层套合方差分析的实现

第34卷第3期 2010年6月物探与化探 GEOPHYSICALGEOCHEMICAL EXPLORATION V0I_34．No．3 Jun．，2010 Excel下地质数据三层套合方差分析的实现张爽，李方林，鲍征宇中国地质大学地球科学学院，湖北武汉430074 摘要套合方差在地质样品数据质量评价中应用广泛，但它的计算过程却比较复杂，目前还没有软件提供套合方差的直接计算。为解决这个问题，应用Excel下的程序开发工具进行VBA编程，实现了实际工作中应用较多的三层套合方差的快捷计算，同时进一步讨论了工作中常出现的只有一次重复采样而未进行重复分析的成对双样本的 t检验及其实现。关键词三层套合方差；Excel程序开发；成对双样本；t检验中图分类号P632 文献标识码A 文章编号在使用地质地球化学数据时，可以用套合方差的方法对样品误差进行定量分析，以辨别自然变差和条件误差，从而对数据质量进行整体评价。目前该方法已经得到了较为广泛的应用，杨泗麟和郑康乐在排除油气地表化探中干扰因素时选用了三层套合方差方法，冯伟华等介绍了三层套合方差在新疆某地化探工作中的应用，童纯菡等在研制国家一级玄武岩标准物质时，用三层套合方差方法检验了元素的均匀性。然而套合方差的计算过程较为复杂，尤其是对于化探、标准样品制备等样品量较大的情况。由于还没有一种软件可以直接计算套合方差，所以在以往的应用中，除冯伟华等提出过用VB程序进行三层套合方差的计算外，多是通过计算器或者Exce1分步计算求得F值、查表获得临界值以进行F检验，过程较为繁琐。目前地质地球化学数据多以Excel数据格式保存，而且Excel提供的VBA等应用开发工具为实现简单的统计计算提供了基础和便捷。综合以上情况，依据套合方差的数学原理，针对实际工作中应用最多的三层套合方差，借助Excel下的程序开发工具进行了VBA编程，并且阐述了临界值的计算机实现，同时补充讨论了工作中常出现的不满足套合情况的成对双样本的T检验及其实现。 1套合方差数学原理简介 1．1数学模型在地质样品分析中，常存在着由于试验条件采样或分析不同而引起的误差条件误差和因收稿日期偶然因素引起的误差随机误差，为了保证数据的精确性，首先要对条件误差进行估量，评价它们的大小是否足以掩盖或歪曲元素在不同样点间真实含量的变化。相对于单因素方差和双因素方差，套合方差综合考虑了采样误差和条件误差，通过设置重复样，即在全区随机、均匀的设置n个样点，一般占全部取样点的3％～5％，每个样点采样次，每个样品分析k次，常用的是 2， 2，即为三层套合方差。对重复样分析结果进行套合方差分析，从而对全部数据进行评估。三层套合方差分析的模型是将分析结果进行分解触 OL 卢，其中， m为第次采样的第k次分析结果；为研究母体的数学期望，用全区样品总平均值代替；O／代表地球化学变差自然变差；为采样误差；为分析误差。假定各项是相互独立的正态变量，根据正态分布的性质，有 ∑∑∑ 一 y ， i k 其中，为第三层次的方差ss，；为第二层次的方差SS；or 为第一层次的方差SS。；总方差为 1．2计算过程根据数据模型的结构，将某个元素的测试数据按表1排列。设 P1／4n∑∑∑ ， i J ∑∑∑ ， ‘ J k R1／2∑∑∑Xijk ， i J k Q1／4∑∑∑Xiik 。 404 sS总TP 4n一1 贡 1．3判别最后构造统计量F。s,／s；和F s 2／s；对两种误差进行显著性检验F n∞- 时，主要是地球化学变差，数据可用；FRn∞,2 时，采样误差是主要的。由于每一个高层次的均方差都包含所有套合其中的低层次的方差，所以可以计算不同层次的方差。其中如果图件稳定性指标值接近1，说明随机误差或噪音干扰占了很大比例，所得图件是没有意义的，只有值大于3时，才能反映自然变差是显著的，图件是可靠的，见表3。表3方差分量计算层次样本量方差分量稳定性指标样品间 4 s 1／4S 一S；样品 2 1／2s；一S；一s；分析 1 5 S； 2在Excel里实现三层套合方差的计算 2．1应用举例针对实际工作中应用较为广泛的三层套合方差，以文献[8]中的练习五数据为例，利用Excel的程序开发工具进行VBA编程，计算F 、、。某地19个取样点cu的重复取样、重复分析的含量数据表4。试通过三层套合方差分析，判断取样误差和分析误差是否显著。 2．2计算过程首先将数据按表1格式输入Excel表格，然后如图1，打开“开发工具”选项，新建好窗体，加入VB 表4 Cu含量测试数据 1o“ 辍 “ ；熬敷。 i 一 xt h-i-t 艟Tlplatts nt ht” 颤‰t2‰”t2j ht0 搬d 。 ■啦一．．’可壹看潦鹞 0 -- 。I 黢I竺～～目4 *册，起目捷I 图1 Excel中程序运行图2三层套合方差的VBA程序设计流程 3期张爽等Excel下地质数据三层套合方差分析的实现 405 图3 Excel运算结果程序运行，程序设计流程见图2。显著性水平of一般取为0．05。点击窗体的“三层套合方差”按钮，可得计算结果图3。通过计算结果易得F。ro．n∞- ，地球化学变差明显比采样、分析误差总和更大，数据可以使用； F 0．8； F。一 [ ， 1一b ～6M≤0．8。其中，分别为第一自由度、第二自由度，a2／ 9f,，b2／，为标准正态分布的O1分位数。由代人法得当 ≤4时，1一b 一bu ≤o．8；当 4时，1一b 一b～20．8M。

}

我就爱股票网