http://www.pptjcw.com

excel入门教程:如何使用函数寻找总和为某个值的组合?

    今天给大家分享一下如何使用函数寻找总和为某个值的组合。

    我举个例子。
    如下图所示,A~B列是数据源,A列是发票号,B列是发票金额。现在需要寻找总和为F1单元格指定值,比如20,089的发票组合。

    excel入门教程:如何使用函数寻找总和为某个值的组合?

    关于这个问题,很久以前给大家分享过规划求解的方法;今天再给大家分享一下函数的方法。
    在C2单元格输入以下公式,并复制到C2:C21区域。
    =IF(SUM(C$2:C$21)=F$1,C2,B2*RANDBETWEEN(0,1))

    公式输入后系统会提示循环引用,这是由于公式引用了公式所在单元格的值,不过别管他,谁稀罕爱她。
    依次点击Excel左上角的「文件」→「选项」命令,打开文件选项对话框,切换到「公式」选项卡,选中「启动迭代计算」复选框,将「最多迭代次数」设置为30000。「确定」后关闭对话框。

    excel入门教程:如何使用函数寻找总和为某个值的组合?

    此时C列公式会自动重算,重算结果非0的项即为发票组合。可以在F2单元格输入一个SUM函数公式进行验证。

    excel入门教程:如何使用函数寻找总和为某个值的组合?

    给大家解释一下公式的意思。
    =IF(SUM(C$2:C$21)=F$1,C2,B2*RANDBETWEEN(0,1))

    公式首先判断C2:C21区域的总和是否等于F1单元格指定的目标值,如果相等,则返回C2自身的值,否则,返回B2单元格发票金额乘以0或1。当乘以0时,结果返回0,表示该发票金额未被选中,当乘以1时,返回发票金额自身,表示被选中。

    由于公式引用了公式所在单元格的值,比如C2单元格的公式SUM(C$2:C$21),也就会触发循环引用。此时我们启用迭代计算,系统会反复计算该公式,直至停止迭代的条件成立(C2:C21的总和等于目标值),或迭代次数用尽。
    就这么回事。

    最后留个练习题。

    excel入门教程:如何使用函数寻找总和为某个值的组合?

    如上图所示,已知总和25,由10个数值构成,每个数值大于等于1,且小于等于5,请列出这10个数值的任一组合。

    图片

    参考答案:

    A3:A12输入公式 ▼
    =IF(SUM(A$3:A$12)=B$1,A3,RANDBETWEEN(1,5))

    提示:如果您觉得本文不错,请点击分享给您的好友!谢谢

    上一篇:excel表格制作教程:条件格式顶呱呱,领导看了把我夸 下一篇:excel表格教程:点击文字看图片,操作其实很简便

    郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。