ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 EH云课堂直播课程免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 4314|回复: 1

[技巧操作] N人合作的利益分配(Shapley值方法)

[复制链接]

TA的精华主题

TA的得分主题

发表于 2017-4-21 22:59 | 显示全部楼层 |阅读模式
问题(来源《全日制普通高中地方选修教材 数学模型》):甲乙丙三人经商,若单干,每人获利1元;甲乙合作获利7元;甲丙合作获利5元;乙丙合作获利4元;三人合作获利10元,三人合作时应如何分配10元收入?
我把这道题用EXCEL建模解答,并对合作的人数作了自由扩充,以达到通用解题的目的。
首先,新建一个EXCEL工作簿,建三个表,分别取名“列全集”,“合作利益”,“利益分配”。

”列全集“表的主要功能是把合作者的所有组合列出来。
参与合作的人名在第二行列出来,A4单元格输入公式”=IF((ROW()-3-2^COUNTA($2:$2))<=0,ROW()-4,"已完成")“,目的是列所有子集的序号,B4单元格将序号转成二进制,C4单元格将二进制转换成合作人数的宽度,为后面的提取数值作准备。
l1.png
D4单元格,键入公式”=IF(COLUMN()>(COUNTA($2:$2)+3),"已完成",IF(MID($C4,COLUMN()-3,1)="0","",A$2))“,用MID函数提取左侧单元格的各位值与第二行对应单元格作比较,数值”1”才取值。公式输入后,右拉复制至“已完成”
l2.png
选中A4:G4,下拉复制至出现“已完成”
选中A4:F11,复制,打开“合作利益”表在A2单元格上右键,选择性粘贴-数值。
D2单元用PHONETIC函数把前面单元格内容合并起来,并下拉复制至D9,在E列填入相应的利益值
l3.png

辅助列用公式把后面要计算的参与者(此处是“甲”)所在的行号标出来,没参与的用一个大负数标注。
l4.png

在“利益分配”表中,用书中所提到的Shapley值方法计算。
在B1输入要计算的参与者的名(此处为“甲”)
B2公式键入“=INDEX(合作利益!$D:$D,MATCH(LARGE(合作利益!$F:$F,COLUMN()-1),合作利益!$F:$F,0),0)”,在通进右拉复制,把有“甲”参与全部组合列出来
l5.png
B3单元格用SUMIF函数把有B2单元格,有甲参与的利益(就在“合作利益”表中取值过来)
l6.png
B4单元格“=SUMIF(合作利益!$D:$D,SUBSTITUTE(B2,$B$1,""),合作利益!$E:$E)”,用SUBSTITUTE函数把B2单元格中的“甲”去掉,也同样使用SUMIF函数,这样结合把无“甲”参与的利益取值过来。
B5单元格等前二个单元格的差值,即“甲”对合作贡献。
l7.png
人数比较简单,用LEN函数取得,
l8.png
加权因子则按公式“=FACT(B6-1)*FACT(MAX($B6:$E6)-B6)/FACT(MAX($B6:$E6))”写入
l9.png
最后选中B2:B8,向右拖动复制至无有效数值,此处SUM(B8:E8),即为本题的答案。
l10.png

在解其他题目,请作相应的修改。



评分

参与人数 1财富 +80 收起 理由
祝洪忠- + 80 感谢分享

查看全部评分

TA的精华主题

TA的得分主题

发表于 2019-7-27 23:02 来自手机 | 显示全部楼层
没搞懂加权因子,利益的计算规则。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-11-19 18:47 , Processed in 0.047214 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表