Excelで基準値に対する振り分けについて

申し訳ありません。ご教示ください。
下記支店別人員を7班に振り分ける予定です。
条件として1班ごと最大220名以内に。
これをExcel関数で算出できますでしょうか。
どうかよろしくお願いします。

支店 　　 人員
A支店85
B支店66
C支店43
D支店110
E支店25
F支店53
G支店81
H支店45
I支店37
J支店 65
K支店74
L支店77
M支店42
N支店83
O支店63
P支店51
Q支店24
R支店69
S支店70
T支店73
V支店58
W支店102
X支店52
Y支店63
合計 1510

No.3 ベストアンサー 回答者： enunokokoro 回答日時： 2023/09/20 12:39

支店単位でグループ分けしたいのでしょうか？

それともNo.2の回答者さんが提示したような振り分けですか？

支店単位でグループ分けしたいのなら、ソルバーを使う方法で、
最大数を元に振り分けすることは可能です。
1グループごとの抽出になるので、7回操作が必要になります。
http://office-qa.com/Excel/ex332.htm
解決で組み合わせができたら、「1」の部分をグループ名などに
し並べ替えをすれば、残った範囲を指定し直すことも簡単です。

No.2 回答者： goomania 回答日時： 2023/09/20 11:32

No.1さんも指摘しておられますが、振り分け条件について、

>条件として1班ごと最大220名以内に
としか説明がありません。これだけの条件ならEXCELを使う必要もありません。
実務の世界でよくあるのは、
>各支店の人員を各班にできるだけ分散させて
という条件をつけるケースです。
多くの支店の人員が一緒になるケースでは、普段は一緒にならない異なる支店の人と交流させることも重要な目的になることが多いがらです。

という訳で、勝手に上記の条件を付加してみました。添付画像をご覧ください(見やすくするため、表全体のうち途中の行は表示していません)。
A列に支店名、B列に人員を入れています。入力後、人員の合計を算出するため、画像で言えばB27セルに、
=SUM(B3:B26)
を入れています。ご質問者の投稿では合計人員は1510となっていますが、こちらで入力すると1511となるようです。

2行目は見出し行ですが、支店、人員の右隣りに、1班～7班の班名をいれています。

各支店の人員を各班に振り分けるため、支店人員を7で割って、小数点以下を四捨五入した人数で振り分けることにします。ただし、単純にその人数を各班に割り振ると支店人員に対して過不足が発生します。それをどこかの班で調整することにします。

どの班で調整するかは、支店ごとにサイコロを振って決めます。という訳で、J3セルに
=RANDBETWEEN(1,7)
を入れて1～7のいずれかをランダムに表示させることにします。これを下方向へコピーしています。
その上で、C3セルに、
=ROUND($B3/7,0)+($B3-ROUND($B3/7,0)*7)*(COLUMN(A1)=$J3)
を入れて、右方向、下方向へコピーしています。

これで、支店ごとの各班への振り分け人員が表示されます。

最後に、B27セルの数式を右方向へ、I列までコピーすれば、各班の合計人員が表示されます。

ただし、この状態だと、班の中には220人の上限を超える班が出現する可能性があります。万一、220人を超える班が出現した場合は「F9キー」を押してサイコロを振り直す必要があります。つまり、220人を超える班が出現しなくなるまで「F9キーを押す」を繰り返すことになります。

この回答へのお礼

すごいですねー。本当に詳しく且つこんなにご丁寧にありがとうございます。こんなやり方があるとは感動。改めてお礼申し上げます。m(_ _)m

お礼日時：2023/09/20 21:36

No.1 回答者： fujillin 回答日時： 2023/09/19 23:23

こんばんは

条件が
＞1班ごと最大220名以内に。
しかないので・・

1150/7=215.7 なので、上から順に216人毎に（最後は214人）に区切ってゆくのではダメなのでしょうか？
これなら
＞Excel関数で算出
というほどのことはないかと思いますが。

この回答へのお礼

ご回答本当にありがとうございます。
そうですよねー。
パズルのように自力で並べてみます。

お礼日時：2023/09/19 23:43