作者l3112255 (米米)
看板Office
標題[算表] 組合加總最佳化
時間Tue Mar 17 09:33:55 2020
軟體:Excel
版本:2016
大大好，有一問題如下
https://imgur.com/nIb49t9
有10位同學(A欄) 他們可以做的伏地挺身數量(B欄)
現在想分三組競賽，要讓他們的競賽數值平均一點
第一組要3人 第二組3人 第三組4人
有辦法讓他自動計算如何分組後的加總是最平均的(k2,K4,K6越接近越好)
(從折線圖看希望他是平坦一點)
(要知道每一組分別是那些人)(g:j)
再者 折線圖有辦法利用像是交叉分析篩選器 點選幾號同學就自動依組別
加總並畫在折線圖上嗎? 查過資料好像無法
再麻煩大大幫忙 謝謝。
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 124.219.98.170 (臺灣)
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1584408837.A.571.html
1F：→ soyoso: 想法，巨集巢狀迴圈排出組合排列數字(3人)，接來3人一樣巢 03/17 12:29
2F：→ soyoso: 狀迴圈(排除前項已排列數字)，前面二組3、3都確定了，那剩 03/17 12:29
3F：→ soyoso: 餘的就是4人的部分，接著就是判斷加總兩兩相減abs取正值的 03/17 12:29
4F：→ soyoso: 最小值組合，這樣邏輯來看內文連結的話，第一組1,7,8、第 03/17 12:29
5F：→ soyoso: 二組4,5,9、第三組2,3,6,10三組加總都是50 03/17 12:29
6F：→ l3112255: 好 我再試試看 好難QQ 03/17 15:41
7F：→ soyoso: 還是要看原po需求，以巨集迴圈的話，也可抓出多取符合最小 03/18 10:26
9F：→ soyoso: 只一組，就看看那種方式合適 03/18 10:28
10F：→ l3112255: 能請教大概的寫法嗎? 巨集還在學習中 03/18 11:50
11F：→ soyoso: 以內文10筆來看的想法上巢狀迴圈 03/18 14:27
12F：→ soyoso: for 變數a =1 to 8 03/18 14:27
13F：→ soyoso: for 變數b = 變數a+1 to 9 03/18 14:27
14F：→ soyoso: for 變數c = 變數b+1 to 10 03/18 14:27
15F：→ soyoso: next 03/18 14:27
16F：→ soyoso: next 03/18 14:27
17F：→ soyoso: next 03/18 14:27
18F：→ soyoso: 以上就是不重複三人一組的部分，執行上就是120次 03/18 14:27
19F：→ soyoso: 那接著來就是要剔除，舉例好了變數a=1,變數b=2,變數c=3 03/18 14:27
20F：→ soyoso: 也就是1,2,3被歸在一組了，那還沒有被歸在一組有4~10 03/18 14:27
21F：→ soyoso: 這裡的4~10寫入陣列，或是字串取代1,、2,、3,後再split到 03/18 14:27
22F：→ soyoso: 陣列內 03/18 14:27
23F：→ soyoso: 一樣巢狀迴圈，但此時同學剩7位還沒有被分配 03/18 14:27
24F：→ soyoso: for 變數d =1 to 5 03/18 14:27
25F：→ soyoso: for 變數e = 變數a+1 to 6 03/18 14:27
26F：→ soyoso: for 變數f = 變數b+1 to 7 03/18 14:27
27F：→ soyoso: nex 03/18 14:27
28F：→ soyoso: next 03/18 14:27
29F：→ soyoso: next 03/18 14:27
30F：→ soyoso: 又會有三個人被歸在一組了。這裡變數d,e,f都是來對應陣列 03/18 14:27
31F：→ soyoso: 內的值 03/18 14:27
32F：→ soyoso: 沒有被被歸在一組的就是最後4人 03/18 14:27
33F：→ soyoso: 有了這些歸類後，接著就是設個二個變數(變數g[數值]、變數 03/18 14:27
34F：→ soyoso: h[文字])，變數g用於判斷if回文所述的加總兩兩相減abs取正 03/18 14:27
35F：→ soyoso: 值的最小值)來做比較，當最小值小於變數g，then 變數h就寫 03/18 14:27
36F：→ soyoso: 入同學(a欄)的資料；else if 當最小值等於變數g時 then 累 03/18 14:27
37F：→ soyoso: 加變數h = 變數 & ...資料。 03/18 14:27
38F：→ soyoso: 變數g不設初始值的話，可能為0，所以初始值方面要設個大點 03/18 14:27
39F：→ soyoso: 的數值 03/18 14:27
40F：→ soyoso: 變數h的資料會是(3人、3人、4人)一組，因此分隔符號要設一 03/18 14:27
41F：→ soyoso: 下，可用於split到儲存格 03/18 14:27
42F：→ soyoso: 大概邏輯是這樣，原po如果要二筆以上符合表列的話，可以試 03/18 14:27
43F：→ soyoso: 試看，有窒礙難行的地方，就提供檔案，再來討論 03/18 14:27
44F：→ l3112255: 好的 我在嘗試看看 謝謝大大 03/18 15:34
46F：→ l3112255: 原本想說用規劃求解 但速度真的太久了 變數比較多時 03/25 17:18
47F：→ l3112255: 大概要耗費半小時 03/25 17:18
48F：→ soyoso: 迴圈大概就是原po這樣的寫法，變數D、E、F方面也可以用另 03/25 21:00
49F：→ soyoso: 一個程序sub或是函數function來，將變數A、b、c的數值以外 03/25 21:00
50F：→ soyoso: 的，如迴圈執行到A=1,b=2,c=3，那4~10就是傳值到另一個程 03/25 21:00
51F：→ soyoso: 序sub或是function內，目的就是4~10這些數值還有什麼可能 03/25 21:00
52F：→ soyoso: 是三位一組的 03/25 21:00
53F：→ soyoso: 回文的邏輯是第一個巢狀迴圈來回傳不重覆三位一組的可能， 03/25 21:00
54F：→ soyoso: 第二個巢狀迴圈來回傳排除第一個巢狀的三位(剩七位內)的不 03/25 21:00
55F：→ soyoso: 重覆三位一組的可能，有了這些可能就可以去抓取所對的數量 03/25 21:00
56F：→ soyoso: ，來判斷最佳解。 03/25 21:00
57F：→ soyoso: 另外宣告變數F方面，所寫的位置和宣告的類型要調整一下， 03/25 21:00
58F：→ soyoso: 位置需在迴圈變數的上方，類型也非字串 03/25 21:00
59F：→ l3112255: 後者抓取所對應數量來判斷最佳解要如何撰寫呢 03/26 14:48
60F：→ l3112255: 僅用IF ELSE 要怎麼秀出對應的值 好難 03/26 14:49
61F：→ l3112255: 宣告變數F 我打錯了 應該是宣告H 看到頭暈 03/26 14:50
62F：→ soyoso: 最佳解的判斷，我是以回文3/17 12:29的方式，兩兩相減abs 03/26 17:10
63F：→ soyoso: 取正值的最小值組合；當然迴圈執行下會有比前數還要更小的 03/26 17:11
64F：→ soyoso: 值出現，就可以設個變數，當小於前數時寫入變數且記錄是哪 03/26 17:12
65F：→ soyoso: 三組資料，那當等於最小值時，再以連接符號&來累加是哪三 03/26 17:13
66F：→ soyoso: 組，這樣迴圈結束後可以取得多組資料 03/26 17:14
67F：→ soyoso: 另外每當判斷有比之前最小值的出現時，所記錄是哪三組資料 03/26 17:15
68F：→ soyoso: 的變數就要清空，清空方式可寫入空字串"" 03/26 17:15
69F：→ l3112255: 嘗試了蠻久還是沒試出來orz 04/13 11:43
70F：→ soyoso: 這要看原po寫到目前方面巨集是如何寫的了 04/13 18:17
72F：→ l3112255: 研究很久還是不知道要怎麼寫QQ 04/15 10:12
※ 編輯: l3112255 (124.219.98.170 臺灣), 04/15/2020 10:16:22
73F：→ l3112255: 可以給個範例嗎 還是指導一下 麻煩了QQ 04/15 10:24
74F：→ soyoso: 連結來看無法提供什麼指導，因為判斷當小於G，H=cells(2,F 04/16 13:00
75F：→ soyoso: )，變數G是什麼，之後又要有什麼作用，上面迴圈都執行完了 04/16 13:00
76F：→ soyoso: ，在最後才判斷目的是？ 04/16 13:00
77F：→ soyoso: 像這方面的判斷都會是在迴圈內而非在迴圈結束後才執行 04/16 13:00
78F：→ l3112255: 好吧 謝謝大大回覆 04/16 15:41
79F：→ soyoso: 這樣說好了，原po知道為什麼要設二組巢狀迴圈嗎？ 04/16 18:33
80F：→ soyoso: (第一組) 04/16 18:33
81F：→ soyoso: for A=1 to 8 04/16 18:33
82F：→ soyoso: ... 04/16 18:33
83F：→ soyoso: next 04/16 18:33
84F：→ soyoso: (第二組) 04/16 18:33
85F：→ soyoso: for D=1 to 5 04/16 18:33
86F：→ soyoso: ... 04/16 18:33
87F：→ soyoso: next 04/16 18:33
88F：→ soyoso: 以下我就以一組、二組來稱呼 04/16 18:33
89F：→ soyoso: 一組A=1,B=2,C=3時這個資訊就要跟二組說目前1,2,3的同學已 04/16 18:33
90F：→ soyoso: 經被占了 04/16 18:33
91F：→ soyoso: 那只能抓非1,2,3的名額 04/16 18:33
92F：→ soyoso: 二組雖然是D=1,E=2(這裡原po的巨集也是寫錯的，不是變數A+ 04/16 18:33
93F：→ soyoso: 1，是變數D+1),F=3(錯誤亦同) 04/16 18:33
94F：→ soyoso: 這裡1,2,3不是指同學1,2,3而是指非1,2,3後的同學4,5,6,7,8 04/16 18:33
95F：→ soyoso: ,9,10，這不是數值的想法，而是位址的想法 04/16 18:33
96F：→ soyoso: 所以原po第一步是要先讓一組和二組迴圈內是可以正確回傳上 04/16 18:33
97F：→ soyoso: 述資料 04/16 18:33
98F：→ soyoso: 第二步才能用該資料所對應的數量來判斷，不是直接 04/16 18:33
99F：→ soyoso: 拿變數A~F，因為A~F不就只是1~10的數值，以這個來判斷並不 04/16 18:33
100F：→ soyoso: 正確 04/16 18:33
101F：→ soyoso: 另外雖然我03/25 21:00回文變數D、E、F方面用另一個程序 04/16 18:33
102F：→ soyoso: sub或是函數function來處理，而原po要以一個程序內來做也 04/16 18:33
103F：→ soyoso: 可以，就gosub return，設個變數，文字、數字或布林值都行 04/16 18:33
104F：→ soyoso: 執行二組迴圈時先判斷是否是一組迴圈gosub來的；不設也行 04/16 18:33
105F：→ soyoso: ，就一組迴圈執行完就exit sub就行 04/16 18:33