[算表] 組合加總最佳化

[l3112255 (米米)]

軟體:Excel

版本:2016

大大好，有一問題如下

https://imgur.com/nIb49t9

有10位同學(A欄) 他們可以做的伏地挺身數量(B欄)

現在想分三組競賽，要讓他們的競賽數值平均一點

第一組要3人 第二組3人 第三組4人

有辦法讓他自動計算如何分組後的加總是最平均的(k2,K4,K6越接近越好)

(從折線圖看希望他是平坦一點)

(要知道每一組分別是那些人)(g:j)

再者 折線圖有辦法利用像是交叉分析篩選器 點選幾號同學就自動依組別

加總並畫在折線圖上嗎? 查過資料好像無法


再麻煩大大幫忙 謝謝。



--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 124.219.98.170 (臺灣)
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1584408837.A.571.html
→ soyoso: 想法，巨集巢狀迴圈排出組合排列數字(3人)，接來3人一樣巢
→ soyoso: 狀迴圈(排除前項已排列數字)，前面二組3、3都確定了，那剩
→ soyoso: 餘的就是4人的部分，接著就是判斷加總兩兩相減abs取正值的
→ soyoso: 最小值組合，這樣邏輯來看內文連結的話，第一組1,7,8、第
→ soyoso: 二組4,5,9、第三組2,3,6,10三組加總都是50
→ l3112255: 好 我再試試看 好難QQ
→ soyoso: 還是要看原po需求，以巨集迴圈的話，也可抓出多取符合最小
→ soyoso: 值的組合，抓出上 https://i.imgur.com/jvRrQtB.jpg 可不
→ soyoso: 只一組，就看看那種方式合適
→ l3112255: 能請教大概的寫法嗎? 巨集還在學習中
→ soyoso: 以內文10筆來看的想法上巢狀迴圈
→ soyoso: for 變數a =1 to 8
→ soyoso:   for 變數b = 變數a+1 to 9
→ soyoso:     for 變數c = 變數b+1 to 10
→ soyoso:     next
→ soyoso:   next
→ soyoso: next
→ soyoso: 以上就是不重複三人一組的部分，執行上就是120次
→ soyoso: 那接著來就是要剔除，舉例好了變數a=1,變數b=2,變數c=3
→ soyoso: 也就是1,2,3被歸在一組了，那還沒有被歸在一組有4~10
→ soyoso: 這裡的4~10寫入陣列，或是字串取代1,、2,、3,後再split到
→ soyoso: 陣列內
→ soyoso: 一樣巢狀迴圈，但此時同學剩7位還沒有被分配
→ soyoso: for 變數d =1 to 5
→ soyoso:   for 變數e = 變數a+1 to 6
→ soyoso:     for 變數f = 變數b+1 to 7
→ soyoso:     nex
→ soyoso:   next
→ soyoso: next
→ soyoso: 又會有三個人被歸在一組了。這裡變數d,e,f都是來對應陣列
→ soyoso: 內的值
→ soyoso: 沒有被被歸在一組的就是最後4人
→ soyoso: 有了這些歸類後，接著就是設個二個變數(變數g[數值]、變數
→ soyoso: h[文字])，變數g用於判斷if回文所述的加總兩兩相減abs取正
→ soyoso: 值的最小值)來做比較，當最小值小於變數g，then 變數h就寫
→ soyoso: 入同學(a欄)的資料；else if 當最小值等於變數g時 then 累
→ soyoso: 加變數h = 變數 & ...資料。
→ soyoso: 變數g不設初始值的話，可能為0，所以初始值方面要設個大點
→ soyoso: 的數值
→ soyoso: 變數h的資料會是(3人、3人、4人)一組，因此分隔符號要設一
→ soyoso: 下，可用於split到儲存格
→ soyoso: 大概邏輯是這樣，原po如果要二筆以上符合表列的話，可以試
→ soyoso: 試看，有窒礙難行的地方，就提供檔案，再來討論
→ l3112255: 好的 我在嘗試看看 謝謝大大
→ l3112255: https://imgur.com/BYL75CH  我好像連巨集的天分都沒有Q
→ l3112255: 原本想說用規劃求解 但速度真的太久了 變數比較多時
→ l3112255: 大概要耗費半小時
→ soyoso: 迴圈大概就是原po這樣的寫法，變數D、E、F方面也可以用另
→ soyoso: 一個程序sub或是函數function來，將變數A、b、c的數值以外
→ soyoso: 的，如迴圈執行到A=1,b=2,c=3，那4~10就是傳值到另一個程
→ soyoso: 序sub或是function內，目的就是4~10這些數值還有什麼可能
→ soyoso: 是三位一組的
→ soyoso: 回文的邏輯是第一個巢狀迴圈來回傳不重覆三位一組的可能，
→ soyoso: 第二個巢狀迴圈來回傳排除第一個巢狀的三位(剩七位內)的不
→ soyoso: 重覆三位一組的可能，有了這些可能就可以去抓取所對的數量
→ soyoso: ，來判斷最佳解。
→ soyoso: 另外宣告變數F方面，所寫的位置和宣告的類型要調整一下，
→ soyoso: 位置需在迴圈變數的上方，類型也非字串
→ l3112255: 後者抓取所對應數量來判斷最佳解要如何撰寫呢
→ l3112255: 僅用IF ELSE 要怎麼秀出對應的值 好難
→ l3112255: 宣告變數F 我打錯了 應該是宣告H 看到頭暈
→ soyoso: 最佳解的判斷，我是以回文3/17 12:29的方式，兩兩相減abs
→ soyoso: 取正值的最小值組合；當然迴圈執行下會有比前數還要更小的
→ soyoso: 值出現，就可以設個變數，當小於前數時寫入變數且記錄是哪
→ soyoso: 三組資料，那當等於最小值時，再以連接符號&來累加是哪三
→ soyoso: 組，這樣迴圈結束後可以取得多組資料
→ soyoso: 另外每當判斷有比之前最小值的出現時，所記錄是哪三組資料
→ soyoso: 的變數就要清空，清空方式可寫入空字串""
→ l3112255: 嘗試了蠻久還是沒試出來orz
→ soyoso: 這要看原po寫到目前方面巨集是如何寫的了
→ l3112255: https://imgur.com/4TSbfD0
→ l3112255: 研究很久還是不知道要怎麼寫QQ
※ 編輯: l3112255 (124.219.98.170 臺灣), 04/15/2020 10:16:22
→ l3112255: 可以給個範例嗎 還是指導一下 麻煩了QQ
→ soyoso: 連結來看無法提供什麼指導，因為判斷當小於G，H=cells(2,F
→ soyoso: )，變數G是什麼，之後又要有什麼作用，上面迴圈都執行完了
→ soyoso: ，在最後才判斷目的是？
→ soyoso: 像這方面的判斷都會是在迴圈內而非在迴圈結束後才執行
→ l3112255: 好吧 謝謝大大回覆
→ soyoso: 這樣說好了，原po知道為什麼要設二組巢狀迴圈嗎？
→ soyoso: (第一組)
→ soyoso: for A=1 to 8
→ soyoso: ...
→ soyoso: next
→ soyoso: (第二組)
→ soyoso: for D=1 to 5
→ soyoso: ...
→ soyoso: next
→ soyoso: 以下我就以一組、二組來稱呼
→ soyoso: 一組A=1,B=2,C=3時這個資訊就要跟二組說目前1,2,3的同學已
→ soyoso: 經被占了
→ soyoso: 那只能抓非1,2,3的名額
→ soyoso: 二組雖然是D=1,E=2(這裡原po的巨集也是寫錯的，不是變數A+
→ soyoso: 1，是變數D+1),F=3(錯誤亦同)
→ soyoso: 這裡1,2,3不是指同學1,2,3而是指非1,2,3後的同學4,5,6,7,8
→ soyoso: ,9,10，這不是數值的想法，而是位址的想法
→ soyoso: 所以原po第一步是要先讓一組和二組迴圈內是可以正確回傳上
→ soyoso: 述資料
→ soyoso:         第二步才能用該資料所對應的數量來判斷，不是直接
→ soyoso: 拿變數A~F，因為A~F不就只是1~10的數值，以這個來判斷並不
→ soyoso: 正確
→ soyoso: 另外雖然我03/25 21:00回文變數D、E、F方面用另一個程序
→ soyoso: sub或是函數function來處理，而原po要以一個程序內來做也
→ soyoso: 可以，就gosub return，設個變數，文字、數字或布林值都行
→ soyoso: 執行二組迴圈時先判斷是否是一組迴圈gosub來的；不設也行
→ soyoso: ，就一組迴圈執行完就exit sub就行