今天給大家分享一下如何使用函數尋找總和為某個值的組合。
我舉個例子。
如下圖所示,A~B列是數據源,A列是發(fā)票號,B列是發(fā)票金額,F(xiàn)在需要尋找總和為F1單元格指定值,比如20,089的發(fā)票組合。
關于這個問題,很久以前給大家分享過規(guī)劃求解的方法;今天再給大家分享一下函數的方法。
在C2單元格輸入以下公式,并復制到C2:C21區(qū)域。
=IF(SUM(C$2:C$21)=F$1,C2,B2*RANDBETWEEN(0,1))
公式輸入后系統(tǒng)會提示循環(huán)引用,這是由于公式引用了公式所在單元格的值,不過別管他,誰稀罕愛她。
依次點擊Excel左上角的「文件」→「選項」命令,打開文件選項對話框,切換到「公式」選項卡,選中「啟動迭代計算」復選框,將「最多迭代次數」設置為30000!复_定」后關閉對話框。
此時C列公式會自動重算,重算結果非0的項即為發(fā)票組合?梢栽贔2單元格輸入一個SUM函數公式進行驗證。
給大家解釋一下公式的意思。
=IF(SUM(C$2:C$21)=F$1,C2,B2*RANDBETWEEN(0,1))
公式首先判斷C2:C21區(qū)域的總和是否等于F1單元格指定的目標值,如果相等,則返回C2自身的值,否則,返回B2單元格發(fā)票金額乘以0或1。當乘以0時,結果返回0,表示該發(fā)票金額未被選中,當乘以1時,返回發(fā)票金額自身,表示被選中。
由于公式引用了公式所在單元格的值,比如C2單元格的公式SUM(C$2:C$21),也就會觸發(fā)循環(huán)引用。此時我們啟用迭代計算,系統(tǒng)會反復計算該公式,直至停止迭代的條件成立(C2:C21的總和等于目標值),或迭代次數用盡。
就這么回事。
最后留個練習題。
如上圖所示,已知總和25,由10個數值構成,每個數值大于等于1,且小于等于5,請列出這10個數值的任一組合。
參考答案:
承擔因您的行為而導致的法律責任,
本站有權保留或刪除有爭議評論。
參與本評論即表明您已經閱讀并接受
上述條款。