ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 15834|回复: 88

[原创] 凑数强化版【庆贺2021年5月9日母亲节】

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2021-5-8 18:21 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 香川群子 于 2021-5-9 11:54 编辑

' 《凑数、凑金额的实用多功能强化版》 by kagawa 2014-01-01
'  2017-7-7更新 增加回溯 以便快速计算得到部分结果
'  2021-4-21 数值由Long整数改为CDec十进制数计算模式
'  2021-5-8 增加关联值汇总计算、重复值可选择字典去重复 或不去重复保留所有组合(关联值不同时)

' A列序号
' B列凑数对象:个数或金额或任意含小数的数值
' C列关联汇总对象:B列凑数值组合相加求和在目标和值范围内时、同时输出其C列也能相加汇总的关联值的总和

' 计算完成后 选N列点按钮黄色高亮序号、选O列点按钮黄色高亮B列组合 选第1行后点按钮则消除黄色底色


发现改为十进制计算模式后,小数位的设置已经毫无必要。

顺便在E列添加了简单的参数设置说明。请有兴趣的重新下载附件。

凑数十进制数强化版.rar

44.97 KB, 下载次数: 1651

评分

33

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2021-5-8 18:24 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
希望使用者好好看一下代码变量的简单注释,这样才能搞清楚程序参数的各种具体应用方法。


' 《凑数、凑金额的实用多功能强化版》 by kagawa 2014-01-01
'  2017-7-7更新 增加回溯 以便快速计算得到部分结果
'  2021-4-21 数值由Long整数改为CDec十进制数计算模式
'  2021-5-8 增加关联值汇总计算、重复值可选择字典去重复 或不去重复保留所有组合(关联值不同时)

'  经Excel Home 香川群子 的深入研究, 采用排序以后的差值递归、末位搜索、
'  当前位超过剩余值剪枝、当前位剩余和不足剪枝、递归计算深度参数控制等技
'  最后产生了目前最好的Excel VBA 高速高效的组合求和递归剪枝算法。
'  可处理小数、负数、重复数等各种情况
'  通过指定小数位数、指定目标和值范围、组合个数范围、指定求解个数、递归计算深度等参数使用
'  可以得到很多有趣的效果、可以满足几乎所有之前要求凑数、凑金额的求助帖子
'  我愿意公开此代码以便大家使用和学习
'========================================================================================
Dim sj(), jg(), dic, d&, d2&, h, h0#, h1, h2, h3#, k&, l&, l2&, m&, n&, n2&, t2&, cnt&, cnt0&, cnt1&, cnt2&, tms#

'递归用公用变量定义:
' sj(): 存放递归用原始数据的数组,已经过升序预处理含[原始数值]、[小数位转换为十进制数值]、[剪枝用累计和]
' jg(): 存放递归计算结果的数组,含组合个数、组合总和、组合明细、递归计算深度
' dic:  原始数据有重复时用来排除重复的字典dic
' d:    d=[f3] 指定小数位数d
' d2:   [f12]=d2 元素重复个数(有重复元素时使用字典排除重复组合)
'       起始设置=-1时,不排除重复【关联汇总对象不同时】

' h:    h=[f1] 目标和值 转为Dec十进制数
' h0#:  目标和值下限 双精度原始值
' h1:   [f11]=h1 有负数时负数累计总和h1
' h2:   h2=[f2] 目标和值上限。并计算转换为和值的向上范围 转为Dec十进制数
' h3#:  目标和值上限 双精度原始值
' h和h2 有3种组合类型:
'       [h,0]→[h,h]输出完全匹配=h的组合;
'       [h<h2]输出[h,h2]范围组合;  即以h为下限、h2为上限的求和范围
'       [h2<h]输出[h,h+h2]范围组合。即以h为下限 h2为差值 h+h2为上限的求和范围

' k:    [f7]=k 组合结果存入jg数组时的序号、计算得到的组合结果总数
' l:    l=[f6] jg()数组的最大下标=限制计算以及输出到工作表的最大行数l
'       留空时不限制计算、但输出默认为<65530行
'       l=-1时输出组合结果到本文件所在文件夹下的txt文本文件
' l2:   统计组合结果文本长度最大值超过911时不能进行数组输出,只能逐行输出

' m:    原始数据中待组合元素个数m
' n:    n=[f4] 指定组合元素个数下限n
' n2:   n2=[f5] 指定组合元素个数上限n2
' n和n2 共有4种组合类型:
'       [0,0]→[0,m]输出全部组合;[n,0]→[n,n]匹配=n的组合;
'       [0,n2]→输出0-n2范围组合;[n,n2]→输出n-n2组合。

' t2:   t2=[f14] 允许回溯时的t值限制 有扩大计算深度的效果
' cnt:  [f8]=cnt 递归计算次数
' cnt0: cnt0=[f15] 递归计算深度(默认留空时=2^16即65536次递归)
'       初次计算无解则可改输入=-1 就会遍历计算全部组合(计算量太大可能会陷入假死循环)
' cnt1: 递归计算深度 有解时归零、或未达到允许回溯的t值时归零
' cnt2: [f13]=cnt2 递归计算深度的最大值记录


' A列序号
' B列凑数对象:个数或金额或任意含小数的数值
' C列关联汇总对象:B列凑数值组合相加求和在目标和值范围内时、同时输出其C列也能相加汇总的关联值的总和

' 计算完成后 选N列点按钮黄色高亮序号、选O列点按钮黄色高亮B列组合 选第1行后点按钮则消除黄色底色

评分

6

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2021-5-8 18:39 | 显示全部楼层
首先是数据条件:

1. A列是序号,用于标识计算完成的组合内容的位置
2. B列凑数对象,必须是可以加法汇总的数值对象,可以含小数,可以重复,
   还可以有少量负数(一般不需要吧,因为含负数之后计算量会级数增长)
3. C列关联值,也必须是可以加法汇总的数值对象。这部分可以省略


然后是各种参数设置:
1. 凑数范围:
F1中输入凑数目标值。
如果F2空白,那么计算完全匹配组合。

如果F2有输入且F2>F1,那么凑数值范围是[F1,F2], 即直接求F1,F2之间的范围

如果输入F2且F2<F1,那么凑数值范围是[F1,F1+F2], 即以F2为向上误差的范围
F2中也可以输入公式F2=F1*5%,
这样的凑数范围输入方式,便于灵活使用。

然后,F3可输入小数点位数。默认留空则取整数计算。
但是忽然发现,因为使用了十进制数计算模式,小数点的指定已经无所谓了。

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2021-5-8 18:41 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2021-5-8 18:42 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2021-5-8 18:42 | 显示全部楼层
下面是组合个数n范围的指定:

在全部m个元素中,可指定组合个数范围:
' n和n2 共有4种组合类型:
'       [0,0]→[0,m]输出全部组合;[n,0]→[n,n]匹配=n的组合;
'       [0,n2]→输出0-n2范围组合;[n,n2]→输出n-n2范围组合。

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2021-5-8 18:47 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
在F6中指定输出组合的方式和个数限制:

输入-1时,输出组合到本文件所在文件夹位置下的txt文本文件。(组合数量很大超出工作表范围时)

留空时,默认输出2万行以下的组合。

也可以输入<1048570行的输出限制。

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2021-5-8 18:50 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
致敬仍然活跃在论坛,帮助别人和分享技术的香川老师,陪伴大家十几年了

TA的精华主题

TA的得分主题

 楼主| 发表于 2021-5-8 18:54 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
F15设置深度留空时,仅进行2^16=65536次的快速递归深度计算。

这样可以快速模拟计算,如果组合结果较多,可以节省时间得到一些组合解。

缺点是,会遗漏大量组合,可能计算后得不到精确匹配解。

此时,可以改设置=-1,那就进行全部组合的递归检查了……后果是计算量大,输出结果的速度会很慢。


…………
为了既能得到组合解,又能进行相对快速的计算,我的改进是在F14中设置了递归回溯深度。
输入t大于1的数值,即可在65536次递归深度的基础上,增加t倍的深度搜索。

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2021-5-8 18:57 | 显示全部楼层
当检查B列凑数值有重复值时,会自动启动字典排除组合结果重复的功能。

但是,如果B列凑数值相同时,其对应的C列关联值并不相同时,那么可以设置F12重复项=-1,那么检查有重复值之后,不启用字典排除重复凑数组合。(这样就可以得到B列凑数组合相同,但C列关联项不同的各种组合)

评分

2

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-28 20:58 , Processed in 0.056300 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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