网站 › 『编程语言讨论求助区』 › Excel多元一次方程线性规划求解

494693793 发表于 2023-7-20 22:43

Excel多元一次方程线性规划求解

求Excel大神解答，如何使用函数求解多元一次不定方程（不是方程组），如460*a+30*b+475*c+350*d=14805，求满足条件的一组abcd分别整数解

魔术使nqy 发表于 2023-7-21 00:46

来人呐，给楼主套四层循环进去跑{:1_918:}

only998 发表于 2023-7-21 06:23

本帖最后由 only998 于 2023-7-21 06:31 编辑

这是一个数学问题，不是一个编程问题{:1_918:}
数学不好，谈一下我的想法，就拿这个例子：460*a+30*b+475*c+350*d=Sum
能整除再约束每个因子对应的整数因子范围，比如a >= 0&& a <= (sum % 460)
生成允许范围的整数数组A{a1, a2,a3}
生成其余的AB   C   D整数，解下来就是暴力组合所有的整数，直到找到满足条件为止。

jyjjf 发表于 2023-7-21 06:45

这个不好弄，弄出来也是像解破解压缩密码一样，暴力模式，把可能的解顺序填进去(譬如从1开始直到最大数)，直到等于那个和，解多时，就会有很多很多行，最后筛选出正确的

小肥元 发表于 2023-7-21 06:55

这 怀疑你是数学题不懂 假装来写excel:lol

surepj 发表于 2023-7-21 07:52

for a in range(101):
    for b in range(101):
      for c in range(101):
            d = (14805 - 460*a - 30*b - 475*c) / 350
            if d.is_integer() and d >= 0:
                print("a = {}, b = {}, c = {}, d = {}".format(a, b, c, int(d)))
运行效果
a = 0, b = 1, c = 9, d = 30
a = 0, b = 1, c = 23, d = 11
a = 0, b = 6, c = 5, d = 35
a = 0, b = 6, c = 19, d = 16
a = 0, b = 11, c = 1, d = 40
a = 0, b = 11, c = 15, d = 21
a = 0, b = 11, c = 29, d = 2
a = 0, b = 16, c = 11, d = 26
a = 0, b = 16, c = 25, d = 7
a = 0, b = 21, c = 7, d = 31
a = 0, b = 21, c = 21, d = 12
a = 0, b = 26, c = 3, d = 36
a = 0, b = 26, c = 17, d = 17
a = 0, b = 31, c = 13, d = 22
a = 0, b = 31, c = 27, d = 3
a = 0, b = 36, c = 9, d = 27
a = 0, b = 36, c = 23, d = 8
a = 0, b = 41, c = 5, d = 32
a = 0, b = 41, c = 19, d = 13
a = 0, b = 46, c = 1, d = 37
a = 0, b = 46, c = 15, d = 18
a = 0, b = 51, c = 11, d = 23
a = 0, b = 51, c = 25, d = 4
a = 0, b = 56, c = 7, d = 28
a = 0, b = 56, c = 21, d = 9
a = 0, b = 61, c = 3, d = 33
a = 0, b = 61, c = 17, d = 14
a = 0, b = 66, c = 13, d = 19
a = 0, b = 66, c = 27, d = 0
a = 0, b = 71, c = 9, d = 24
a = 0, b = 71, c = 23, d = 5
a = 0, b = 76, c = 5, d = 29
a = 0, b = 76, c = 19, d = 10
a = 0, b = 81, c = 1, d = 34
a = 0, b = 81, c = 15, d = 15
a = 0, b = 86, c = 11, d = 20
a = 0, b = 86, c = 25, d = 1
a = 0, b = 91, c = 7, d = 25
a = 0, b = 91, c = 21, d = 6
a = 0, b = 96, c = 3, d = 30
a = 0, b = 96, c = 17, d = 11
a = 1, b = 4, c = 13, d = 23
a = 1, b = 4, c = 27, d = 4
a = 1, b = 9, c = 9, d = 28
a = 1, b = 9, c = 23, d = 9
a = 1, b = 14, c = 5, d = 33
a = 1, b = 14, c = 19, d = 14
a = 1, b = 19, c = 1, d = 38
a = 1, b = 19, c = 15, d = 19
a = 1, b = 19, c = 29, d = 0
a = 1, b = 24, c = 11, d = 24
a = 1, b = 24, c = 25, d = 5
a = 1, b = 29, c = 7, d = 29
a = 1, b = 29, c = 21, d = 10
a = 1, b = 34, c = 3, d = 34
a = 1, b = 34, c = 17, d = 15
a = 1, b = 39, c = 13, d = 20
a = 1, b = 39, c = 27, d = 1
a = 1, b = 44, c = 9, d = 25
a = 1, b = 44, c = 23, d = 6
a = 1, b = 49, c = 5, d = 30
a = 1, b = 49, c = 19, d = 11
a = 1, b = 54, c = 1, d = 35
a = 1, b = 54, c = 15, d = 16
a = 1, b = 59, c = 11, d = 21
a = 1, b = 59, c = 25, d = 2
a = 1, b = 64, c = 7, d = 26
a = 1, b = 64, c = 21, d = 7
a = 1, b = 69, c = 3, d = 31
a = 1, b = 69, c = 17, d = 12
a = 1, b = 74, c = 13, d = 17
a = 1, b = 79, c = 9, d = 22
a = 1, b = 79, c = 23, d = 3
a = 1, b = 84, c = 5, d = 27
a = 1, b = 84, c = 19, d = 8
a = 1, b = 89, c = 1, d = 32
a = 1, b = 89, c = 15, d = 13
a = 1, b = 94, c = 11, d = 18
a = 1, b = 99, c = 7, d = 23
a = 1, b = 99, c = 21, d = 4
a = 2, b = 2, c = 7, d = 30
a = 2, b = 2, c = 21, d = 11
a = 2, b = 7, c = 3, d = 35
a = 2, b = 7, c = 17, d = 16
a = 2, b = 12, c = 13, d = 21
a = 2, b = 12, c = 27, d = 2
a = 2, b = 17, c = 9, d = 26
a = 2, b = 17, c = 23, d = 7
a = 2, b = 22, c = 5, d = 31
a = 2, b = 22, c = 19, d = 12
a = 2, b = 27, c = 1, d = 36
a = 2, b = 27, c = 15, d = 17
a = 2, b = 32, c = 11, d = 22
a = 2, b = 32, c = 25, d = 3
a = 2, b = 37, c = 7, d = 27
a = 2, b = 37, c = 21, d = 8
a = 2, b = 42, c = 3, d = 32
a = 2, b = 42, c = 17, d = 13
a = 2, b = 47, c = 13, d = 18
a = 2, b = 52, c = 9, d = 23
a = 2, b = 52, c = 23, d = 4
a = 2, b = 57, c = 5, d = 28
a = 2, b = 57, c = 19, d = 9
a = 2, b = 62, c = 1, d = 33
a = 2, b = 62, c = 15, d = 14
a = 2, b = 67, c = 11, d = 19
a = 2, b = 67, c = 25, d = 0
a = 2, b = 72, c = 7, d = 24
a = 2, b = 72, c = 21, d = 5
a = 2, b = 77, c = 3, d = 29
a = 2, b = 77, c = 17, d = 10
a = 2, b = 82, c = 13, d = 15
a = 2, b = 87, c = 9, d = 20
a = 2, b = 87, c = 23, d = 1
a = 2, b = 92, c = 5, d = 25
a = 2, b = 92, c = 19, d = 6
a = 2, b = 97, c = 1, d = 30
a = 2, b = 97, c = 15, d = 11
a = 3, b = 0, c = 1, d = 37
a = 3, b = 0, c = 15, d = 18
a = 3, b = 5, c = 11, d = 23
a = 3, b = 5, c = 25, d = 4
a = 3, b = 10, c = 7, d = 28
a = 3, b = 10, c = 21, d = 9
a = 3, b = 15, c = 3, d = 33
a = 3, b = 15, c = 17, d = 14
a = 3, b = 20, c = 13, d = 19
a = 3, b = 20, c = 27, d = 0
a = 3, b = 25, c = 9, d = 24
a = 3, b = 25, c = 23, d = 5
a = 3, b = 30, c = 5, d = 29
a = 3, b = 30, c = 19, d = 10
a = 3, b = 35, c = 1, d = 34
a = 3, b = 35, c = 15, d = 15
a = 3, b = 40, c = 11, d = 20
a = 3, b = 40, c = 25, d = 1
a = 3, b = 45, c = 7, d = 25
a = 3, b = 45, c = 21, d = 6
a = 3, b = 50, c = 3, d = 30
a = 3, b = 50, c = 17, d = 11
a = 3, b = 55, c = 13, d = 16
a = 3, b = 60, c = 9, d = 21
a = 3, b = 60, c = 23, d = 2
a = 3, b = 65, c = 5, d = 26
a = 3, b = 65, c = 19, d = 7
a = 3, b = 70, c = 1, d = 31
a = 3, b = 70, c = 15, d = 12
a = 3, b = 75, c = 11, d = 17
a = 3, b = 80, c = 7, d = 22
a = 3, b = 80, c = 21, d = 3
a = 3, b = 85, c = 3, d = 27
a = 3, b = 85, c = 17, d = 8
a = 3, b = 90, c = 13, d = 13
a = 3, b = 95, c = 9, d = 18
a = 3, b = 100, c = 5, d = 23
a = 3, b = 100, c = 19, d = 4
a = 4, b = 3, c = 5, d = 30
a = 4, b = 3, c = 19, d = 11
a = 4, b = 8, c = 1, d = 35
a = 4, b = 8, c = 15, d = 16
a = 4, b = 13, c = 11, d = 21
a = 4, b = 13, c = 25, d = 2
a = 4, b = 18, c = 7, d = 26
a = 4, b = 18, c = 21, d = 7
a = 4, b = 23, c = 3, d = 31
a = 4, b = 23, c = 17, d = 12
a = 4, b = 28, c = 13, d = 17
a = 4, b = 33, c = 9, d = 22
a = 4, b = 33, c = 23, d = 3
a = 4, b = 38, c = 5, d = 27
a = 4, b = 38, c = 19, d = 8
a = 4, b = 43, c = 1, d = 32
a = 4, b = 43, c = 15, d = 13
a = 4, b = 48, c = 11, d = 18
a = 4, b = 53, c = 7, d = 23
a = 4, b = 53, c = 21, d = 4
a = 4, b = 58, c = 3, d = 28
a = 4, b = 58, c = 17, d = 9
a = 4, b = 63, c = 13, d = 14
a = 4, b = 68, c = 9, d = 19
a = 4, b = 68, c = 23, d = 0
a = 4, b = 73, c = 5, d = 24
a = 4, b = 73, c = 19, d = 5
a = 4, b = 78, c = 1, d = 29
a = 4, b = 78, c = 15, d = 10
a = 4, b = 83, c = 11, d = 15
a = 4, b = 88, c = 7, d = 20
a = 4, b = 88, c = 21, d = 1
a = 4, b = 93, c = 3, d = 25
a = 4, b = 93, c = 17, d = 6
a = 4, b = 98, c = 13, d = 11
a = 5, b = 1, c = 13, d = 18
a = 5, b = 6, c = 9, d = 23
a = 5, b = 6, c = 23, d = 4
a = 5, b = 11, c = 5, d = 28
... ...

Sev7en 发表于 2023-7-21 08:14

规划求解得出的结果

daoye9988 发表于 2023-7-21 08:25

好深奥的感觉

hansole 发表于 2023-7-21 08:45

你搜索下 Excel用 最小二乘公式 求系数就可以了

kingdjh 发表于 2023-7-21 09:21

这还不简单，随便给其他3个变量一个值，不就变成一元一次方程了吗？

查看完整版本: Excel多元一次方程线性规划求解