ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[求助] EXCEL大量数据中凑数问题

[复制链接]

TA的精华主题

TA的得分主题

发表于 2024-3-19 11:12 | 显示全部楼层 |阅读模式
本帖最后由 lzc960315 于 2024-3-19 11:14 编辑

各位老师好:现在我手头有9000个单元格(在同一列),每个单元格中有一个数值,我现在想在9000个数值中凑数,分别凑4个数(合计目标值),我利用了规划求解,单元格太多了解不出来,然后我利用了方方格子的凑数功能,可能也是因为数据太多,无法解出相对的方案。我比较小白,求老师和大佬们教学一下,工作不保了

TA的精华主题

TA的得分主题

发表于 2024-3-19 11:34 | 显示全部楼层
上文件玩下

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-3-19 13:07 | 显示全部楼层
在这小9000个数字中,找到目标值(合计数)1021326.82、1571366、1400000、7307.18

明细.rar

83.16 KB, 下载次数: 29

数值及目标值

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-3-19 13:08 | 显示全部楼层

大佬 已发数据。求助求助,看看能不能凑到数之后,使用的数字可以再前面高亮,或者是能知道是用了哪些数据给标注出来,要不然不好找出来啊

TA的精华主题

TA的得分主题

发表于 2024-3-19 14:29 | 显示全部楼层
你这问题,数字大,数量也多,用一般的历遍组合,卡得要死。放弃了!!!!!!!!!!!请高手用 “剪枝递归”去做吧,不会!!!

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-3-19 14:34 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2024-3-19 16:19 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
+1774.68+1670.55+1667+1634.4+1600.35+1599.6+1588.89+1588.83+1573.04+1566.3+1566.3+1566.3+1564.14+1559.98+1559.49+1552.83+1552.68+1552.68+1550.85+1550.85+1548+1544.52+1543.2+1543.2+1541.55+1539.06+1539.06+1532.25+1530.34+1525.53+1522.97+1522.92+1522.26+1515.81+1511.82+1498.2+1498.2+1498.2+1498.2+1498.2+1496.4+1490.01+1489.06+1485.33+1485.33+1484.58+1483.5+1481.22+1479.05+1478.9+1478.9+1478.9+1477.98+1477.05+1477.05+1477.05+1476.99+1476.09+1476.09+1472.47+1471.77+1470.66+1468.75+1468.56+1464.21+1457.7+1456.92+1455.9+1455.9+1455.9+1451.25+1451.25+1450.53+1444.8+1444.14+1431.96+1431.9+1431.9+1431.36+1430.16+1430.1+1430.1+1430+1429.01+1428.3+1425.51+1425.45+1425.45+1425.45+1424.25+1424.25+1424.25+1423.29+1423.29+1422.27+1419+1419+1419+1419+1419+1419+1419+1419+1418.58+1417.92+1416.48+1414.6+1414.6+1414.6+1412.61+1411.59+1409.67+1409.67+1409.67+1405.8+1405.8+1405.8+1405.39+1403.46+1398.93+1397.25+1393.2+1393.2+1392.6+1391.04+1391.04+1389.31+1389.24+1388.88+1386.75+1386.75+1386.75+1386.75+1386.75+1386.27+1382.45+1382.45+1380.3+1378.62+1376.02+1376.02+1375.62+1373.85+1373.85+1373.85+1369.65+1368.81+1367.4+1367.4+1366.2+1366.2+1362+1362+1362+1362+1362+1362+1362+1362+1362+1362+1362+1362+1361.01+1360.95+1360.95+1356.73+1355.19+1354.56+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1353.78+1350.3+1350.3+1348.29+1348.05+1348.05+1347.57+1347.15+1341.9+1341.9+1341.9+1341.9+1341.9+1341.6+1337.44+1335.15+1335.15+1335.15+1335.15+1334.76+1334.76+1331.07+1331.01+1330.39+1329.36+1329.3+1329.3+1329.3+1329.3+1329.3+1328.7+1328.7+1328.7+1328.7+1328.7+1328.7+1328.12+1327.95+1327.95+1327.95+1324.58+1324.58+1322.97+1322.73+1322.31+1322.25+1322.25+1322.25+1322.25+1322.25+1322.25+1322.25+1322.25+1318.15+1318.15+1316.77+1316.64+1316.64+1315.8+1313+1311.72+1311+1310.31+1310.31+1310.31+1310.31+1309.95+1309.95+1309.48+1309.35+1309.35+1309.35+1304.1+1304.1+1303.56+1302.9+1302.9+1302.9+1297.65+1297.65+1297.39+1297.17+1296.45+1296.45+1296.45+1296.45+1295+1293.9+1293.9+1293.9+1293.9+1293.9+1292.43+1292.43+1291.68+1291.32+1290.78+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1286+1286+1286+1286+1286+1286+1286+1286+1285.47+1284.99+1284.99+1284.99+1284.45+1284.39+1283.55+1283.55+1280.35+1280.28+1279.57+1279.57+1278.66+1278+1278+1278+1278+1278+1278+1278+1277.1+1277.1+1277.1+1277.1+1273.14+1273.05+1270.65+1270.65+1270.65+1270.65+1266.77+1266.71+1266.32+1266+1266+1266+1266+1266+1266+1266+1266+1266+1266+1266+1266+1266+1264.2+1264.2+1264.2+1264.2+1260.63+1260.28+1259.91+1259.85+1259.85+1259.85+1259.67+1258.83+1258.83+1258.83+1258.83+1257.81+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1254.54+1253.85+1253.85+1253.85+1253.85+1253.85+1253.85+1253.34+1253.34+1253.34+1253.34+1252.44+1252.44+1251.3+1251.3+1251.3+1248.21+1247.42+1247.01+1247.01+1246.23+1246.05+1246.05+1246.05+1246.05+1246.05+1244.85+1244.85+1244.85+1244.85+1244.85+1244.85+1242+1242+1242+1242+1242+1242+1240.99+1240.68+1240.68+1240.68+1239.66+1238.52+1238.4+1238.4+1234.56+1234.35+1234.35+1234.35+1234.35+1234.35+1234.35+1233.27+1232.61+1231.95+1231.95+1231.82+1229.58+1228.02+1228.02+1226.88+1225.86+1225.8+1225.8+1225.8+1225.8+1225.8+1225.56+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1224.91+1223.37+1223.37+1221.7+1221.7+1221.7+1221.7+1221.7+1220.49+1220.49+1219.05+1219.05+1215.36+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1213.65+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.38+1212.18+1211.61+1210.95+1210+1209.03+1209.03+1209.03+1209.03+1209.03+1207.71+1206.15+1206.15+1206.15+1205.37+1204.74+1202.76+1202.76+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.41+1201.32+1200+1199.7+1199.7+1199.7+1199.7+1199.7+1199.7+1199.7+1199.7+1198.72+1198.56+1198.53+1198.53+1196.37+1196.34+1195.98+1194.93+1193.31+1193.31+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1192.32+1192.32+1192+1191.75+1191.75+1191.75+1191.75+1190.04+1190.04+1190.04+1190.04+1189.55+1189.55+1189.55+1189.55+1189.55+1189.55+1189.55+1189.55+1188.54+1188.54+1186.86+1186.8+1186.8+1186.8+1186.11+1184.94+1183.39+1182.21+1182.15+1182.15+1182.15+1182.15+1182.15+1182.02+1180.35+1180.35+1180.35+1180.35+1180.35+1180.35+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1178.13+1177.38+1175.95+1175.76+1173.9+1173.9+1173.9+1173.69+1173.69+1171.32+1171.05+1171.05+1171.05+1171.05+1170.26+1169.5+1169.43+1169.37+1167.45+1167.45+1167.45+1167.45+1167.45+1167.45+1167.45+1164.72+1164.51+1162.98+1162.98+1162.98+1161.27+1161.27+1161.27+1161.06+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1158.39+1158.39+1158.39+1158.39+1158.1+1157.76+1157.7+1157.7+1157.7+1157.7+1157.7+1157.63+1157.46+1157.46+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1156.59+1156.59+1156.59+1155.24+1154.55+1153+1152.06+1152.06+1150.26+1150.26+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1148.85+1148.85+1148.85+1148.1+1148.1+1148.1+1148.1+1148.1+1148.1+1147.51+1145.73+1144.54+1144.54+1144.54+1144.28+1143.81+1141.65+1141.65+1141.65+1139.65+1139.46+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1138.11+1138.11+1138.11+1138.11+1137.42+1137.42+1137.42+1137.27+1137.27+1136.43+1136.43+1136.43+1135.2+520.56+4.37+2.65+2.05

TA的精华主题

TA的得分主题

发表于 2024-3-19 16:20 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
  1. 1021326.82=1774.68+1670.55+1667+1634.4+1600.35+1599.6+1588.89+1588.83+1573.04+1566.3+1566.3+1566.3+1564.14+1559.98+1559.49+1552.83+1552.68+1552.68+1550.85+1550.85+1548+1544.52+1543.2+1543.2+1541.55+1539.06+1539.06+1532.25+1530.34+1525.53+1522.97+1522.92+1522.26+1515.81+1511.82+1498.2+1498.2+1498.2+1498.2+1498.2+1496.4+1490.01+1489.06+1485.33+1485.33+1484.58+1483.5+1481.22+1479.05+1478.9+1478.9+1478.9+1477.98+1477.05+1477.05+1477.05+1476.99+1476.09+1476.09+1472.47+1471.77+1470.66+1468.75+1468.56+1464.21+1457.7+1456.92+1455.9+1455.9+1455.9+1451.25+1451.25+1450.53+1444.8+1444.14+1431.96+1431.9+1431.9+1431.36+1430.16+1430.1+1430.1+1430+1429.01+1428.3+1425.51+1425.45+1425.45+1425.45+1424.25+1424.25+1424.25+1423.29+1423.29+1422.27+1419+1419+1419+1419+1419+1419+1419+1419+1418.58+1417.92+1416.48+1414.6+1414.6+1414.6+1412.61+1411.59+1409.67+1409.67+1409.67+1405.8+1405.8+1405.8+1405.39+1403.46+1398.93+1397.25+1393.2+1393.2+1392.6+1391.04+1391.04+1389.31+1389.24+1388.88+1386.75+1386.75+1386.75+1386.75+1386.75+1386.27+1382.45+1382.45+1380.3+1378.62+1376.02+1376.02+1375.62+1373.85+1373.85+1373.85+1369.65+1368.81+1367.4+1367.4+1366.2+1366.2+1362+1362+1362+1362+1362+1362+1362+1362+1362+1362+1362+1362+1361.01+1360.95+1360.95+1356.73+1355.19+1354.56+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1354.5+1353.78+1350.3+1350.3+1348.29+1348.05+1348.05+1347.57+1347.15+1341.9+1341.9+1341.9+1341.9+1341.9+1341.6+1337.44+1335.15+1335.15+1335.15+1335.15+1334.76+1334.76+1331.07+1331.01+1330.39+1329.36+1329.3+1329.3+1329.3+1329.3+1329.3+1328.7+1328.7+1328.7+1328.7+1328.7+1328.7+1328.12+1327.95+1327.95+1327.95+1324.58+1324.58+1322.97+1322.73+1322.31+1322.25+1322.25+1322.25+1322.25+1322.25+1322.25+1322.25+1322.25+1318.15+1318.15+1316.77+1316.64+1316.64+1315.8+1313+1311.72+1311+1310.31+1310.31+1310.31+1310.31+1309.95+1309.95+1309.48+1309.35+1309.35+1309.35+1304.1+1304.1+1303.56+1302.9+1302.9+1302.9+1297.65+1297.65+1297.39+1297.17+1296.45+1296.45+1296.45+1296.45+1295+1293.9+1293.9+1293.9+1293.9+1293.9+1292.43+1292.43+1291.68+1291.32+1290.78+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1290+1286+1286+1286+1286+1286+1286+1286+1286+1285.47+1284.99+1284.99+1284.99+1284.45+1284.39+1283.55+1283.55+1280.35+1280.28+1279.57+1279.57+1278.66+1278+1278+1278+1278+1278+1278+1278+1277.1+1277.1+1277.1+1277.1+1273.14+1273.05+1270.65+1270.65+1270.65+1270.65+1266.77+1266.71+1266.32+1266+1266+1266+1266+1266+1266+1266+1266+1266+1266+1266+1266+1266+1264.2+1264.2+1264.2+1264.2+1260.63+1260.28+1259.91+1259.85+1259.85+1259.85+1259.67+1258.83+1258.83+1258.83+1258.83+1257.81+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1257.75+1254.54+1253.85+1253.85+1253.85+1253.85+1253.85+1253.85+1253.34+1253.34+1253.34+1253.34+1252.44+1252.44+1251.3+1251.3+1251.3+1248.21+1247.42+1247.01+1247.01+1246.23+1246.05+1246.05+1246.05+1246.05+1246.05+1244.85+1244.85+1244.85+1244.85+1244.85+1244.85+1242+1242+1242+1242+1242+1242+1240.99+1240.68+1240.68+1240.68+1239.66+1238.52+1238.4+1238.4+1234.56+1234.35+1234.35+1234.35+1234.35+1234.35+1234.35+1233.27+1232.61+1231.95+1231.95+1231.82+1229.58+1228.02+1228.02+1226.88+1225.86+1225.8+1225.8+1225.8+1225.8+1225.8+1225.56+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1225.5+1224.91+1223.37+1223.37+1221.7+1221.7+1221.7+1221.7+1221.7+1220.49+1220.49+1219.05+1219.05+1215.36+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1214.1+1213.65+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.6+1212.38+1212.18+1211.61+1210.95+1210+1209.03+1209.03+1209.03+1209.03+1209.03+1207.71+1206.15+1206.15+1206.15+1205.37+1204.74+1202.76+1202.76+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.7+1202.41+1201.32+1200+1199.7+1199.7+1199.7+1199.7+1199.7+1199.7+1199.7+1199.7+1198.72+1198.56+1198.53+1198.53+1196.37+1196.34+1195.98+1194.93+1193.31+1193.31+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1193.25+1192.32+1192.32+1192+1191.75+1191.75+1191.75+1191.75+1190.04+1190.04+1190.04+1190.04+1189.55+1189.55+1189.55+1189.55+1189.55+1189.55+1189.55+1189.55+1188.54+1188.54+1186.86+1186.8+1186.8+1186.8+1186.11+1184.94+1183.39+1182.21+1182.15+1182.15+1182.15+1182.15+1182.15+1182.02+1180.35+1180.35+1180.35+1180.35+1180.35+1180.35+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1179.9+1178.13+1177.38+1175.95+1175.76+1173.9+1173.9+1173.9+1173.69+1173.69+1171.32+1171.05+1171.05+1171.05+1171.05+1170.26+1169.5+1169.43+1169.37+1167.45+1167.45+1167.45+1167.45+1167.45+1167.45+1167.45+1164.72+1164.51+1162.98+1162.98+1162.98+1161.27+1161.27+1161.27+1161.06+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1161+1158.39+1158.39+1158.39+1158.39+1158.1+1157.76+1157.7+1157.7+1157.7+1157.7+1157.7+1157.63+1157.46+1157.46+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1157.4+1156.59+1156.59+1156.59+1155.24+1154.55+1153+1152.06+1152.06+1150.26+1150.26+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1150.2+1148.85+1148.85+1148.85+1148.1+1148.1+1148.1+1148.1+1148.1+1148.1+1147.51+1145.73+1144.54+1144.54+1144.54+1144.28+1143.81+1141.65+1141.65+1141.65+1139.65+1139.46+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1139.4+1138.11+1138.11+1138.11+1138.11+1137.42+1137.42+1137.42+1137.27+1137.27+1136.43+1136.43+1136.43+1135.2+520.56+4.37+2.65+2.05+0
复制代码

TA的精华主题

TA的得分主题

发表于 2024-3-19 16:24 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
今天刚看到这个论坛大神的帖子下载下来保存的

凑数字凑金额的最佳递归程序by_kagawa.zip

105.33 KB, 下载次数: 51

TA的精华主题

TA的得分主题

发表于 2024-3-19 17:03 | 显示全部楼层
被筛选坑了,不然应该是早就对了的

明细.rar

174.87 KB, 下载次数: 25

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

本版积分规则

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

GMT+8, 2024-11-14 14:54 , Processed in 0.049519 second(s), 13 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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