湊數求和的簡便演算法「excel自動湊數求和教程」

相信辦公中的小夥伴有時會遇到這個難題,有時真的是一臉懵逼,不知如何下手,特別是數學本身就不好的人,更是無奈啊。於是我左思右想結合網路資源,最終找到了幾個最適合簡單的方法。

1.Excel中一個「規劃求解」功能,不過這個有個局限性,所選數據不能超過200個,而且數據越多,求解越慢,效率越低。

2.插件 這裡推薦2個,個人覺得求解速度蠻快的插件「EXCEL必備工具箱」和「Excel精靈」原來的名稱為E靈

3.藉助「川香群子老師」的輔助工具

首先聲明一下,插件「EXCEL必備工具箱」和「Excel精靈」部分功能原創作者是收費的,所以有下載後遇到收費提示或者部分功能不能用,不能吐槽我哦

一、先詳解一下規劃求解功能:

有N個數,如果想知道哪些數加起來等於1000,估計手機屏幕前的你也不會吧。昨天蘭色在excel第一教室微信平台做了個調查,結果會使用規劃求解同學的還不到1%,而這個問題就是需要用「規劃求解」來解決

【例】如下圖所示,需要找出A列哪些數字加在一起等於目標值1000。

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

操作步驟:

1、添加「規劃求解」功能。

文件(excel2007是點office按鈕) – excel選項 – 載入項 – 點最下面「轉到」按鈕,打開【載入宏】窗口,選取「規劃求解載入項」再點確定按鈕。

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

2、在任一個單元格中設置公式

=SUMPRODUCT(A2:A7,B2:B7)

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

3、【數據】選項卡中點擊新增的「規劃求解」命令,打開規劃求解窗口,進行以下設置:

  • 設置目標:選取剛設置公式的單元格D4
  • 目標值:輸入1000;
  • 通過更改可變單元格:選取B2:B7
多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

4、在上面的窗口中點擊「添加」按鈕,打開【添加約束】窗口,選取B2:B7的約束條件為Bin(二進位,只有0和1兩種類型的數字)

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

最後點【規劃求解】窗口中的「求解」按鈕後,在B列會生成0和1兩種數字。所有填充1的單元格所在行的A列數字即可我們最終所求。

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

最終結果:100+500+150+250=1000

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

二、EXCEL必備工具箱–自動湊數功能(已知合計數求加數)

XCEL必備工具箱–自動湊數功能(已知合計數求加數)

功能介紹:

當一個數據擺在你的面前,你需要知道是當前選擇區域中哪些數據的和,用此功能會輕鬆實現。

界面:

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

功能界面:

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

12.30版改進內容:在某些特殊情況下,未完成自動湊數而退出程序;數據含有分節符,比如:2,333.25,不能自動湊數,已改進。並且增加了一種備選演算法,進一步提供湊數成功率。同時,允許指定組成合計數的元素的個數。

15.54版改進:對要湊的合計數允許是一個區間。

具體操作說明:

1、在進行本操作之前要事先選擇數據區域:

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

進入「自動湊數」功能後,如果想找出157是由已選定區域哪幾個數據之和組成的,合計數的框內就填入157,

可能組成157的方案有很多種,你可以選擇方案個數,比如10個:

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

上圖中下面兩個選項分別為:

1、在工作表上選中第一種方案對應數據所在單元格,在操作完成後,對直接選擇B3、A3、B5這三個單元格,57+58+42=157,這樣會比較一目了然:

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

2、單獨顯示找出的多個組合方案的具體數據,如果只要1組方案,可以不選這個選項,但如果是需要多組方案,就需要知道各方案的具體組成,會新建一個工作表存放結果:

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

在數據較多,長時間找不到結果時,可以點擊「放棄」終止操作:

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

動畫演示:

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

三、《E靈》/《Excel精靈》快捷湊數

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

名稱:快捷湊數

功能: 指定一個合計值,本工具可以羅列出來區域中哪些值相加等於這個合計值。支持小數,支持範圍(例如合計值在1000到1010之間),當有多組數據符合條件時可以設定全部羅列出來還是只列出部分組合。

四、川香群子老師的「最牛湊數字湊金額的最佳遞歸程序」

多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備
多種Exce快速簡單湊數求和技巧 #excel #職場#辦公技巧#辦公必備

原創文章,作者:投稿專員,如若轉載,請註明出處:https://www.506064.com/zh-tw/n/273952.html

(0)
打賞 微信掃一掃 微信掃一掃 支付寶掃一掃 支付寶掃一掃
投稿專員的頭像投稿專員
上一篇 2024-12-17 14:09
下一篇 2024-12-17 14:09

相關推薦

發表回復

登錄後才能評論