エクセルでのグループ分け（グループ数の求め方）

「いくつのグループができるのか？」をエクセルで計算しようとしています。
グループの構成は、
・5年生がリーダーで1名
・4年生が班員で3名
とし、また、
・6年生が1名で1から５グループの監視役
とします。（監視役の6年生は、全員が同数のグループを監視することとし、監視するグループ数に応じた全体の数量を求めたい）

このとき、監視役になれなかった6年生はリーダーか、班員となり、また、リーダーになれなかった5年生は班員となります。（4年生は、必ず班員で、5年生は監視役はできません。）

以上の条件で『最大何グループができるか』を求めようとしていますが、良い計算式が思いつきません。どなたか教えてくださいませんか？？？よろしくお願いいたします。

なお、一般式を作りたいので、あえて各学年の生徒数は入れてありません。

No.10 回答者： tom04 回答日時： 2009/05/16 21:32

何とお詫びしていいやら・・・

またまた・・・数式間違いに気づきました。
B5セルのINT関数の分母を間違っていました。

最終的に間違いのない数式をもう一度書き込ませてもらいます。
表は以前の配置どおりです。

B4セル　=INT(D2/3)
C5セル　=MOD(D2,3)
B5セル　=IF(C5=0,INT((C2-B4)/4),INT((C2-B4-(3-C5))/4))
C6セル　=IF(C5=0,MOD(C2-B4-B5*4,4),MOD(C2-B4-(3-C5)-B5,3))
B6セル　=IF(C6<>0,1,0)
C7セル　=IF(C6=0,B2,B2-(3-C6)-B6)
B8セル　=SUM(B4:B6)
です。

これでもかっ！という位に何度も顔をだしてしまいまして、
申し訳ございませんでした。m(__)m

No.9 回答者： tom04 回答日時： 2009/05/16 19:52

No.1.7.8です！

ほんとぉ～！にごめんなさい。
もう一つ訂正です。
C7セルも
=IF(C6=0,B2,B2-(3-C6)-B6)
としてください。

ちゃんと動作確認せずに投稿してしまって・・・
ほんとうにごめんなさいね！m(__)m

No.8 回答者： tom04 回答日時： 2009/05/16 19:43

たびたびごめんなさい！

No.1・7です。

先ほどのNo.7の回答で
C6セルの数式を下記のように変更してください。

=IF(C5=0,MOD(C2-B4-B5*4,4),MOD(C2-B4-(3-C5)-B5,3))

どうも何度もごめんなさいね。m(__)m

No.7 回答者： tom04 回答日時： 2009/05/16 19:22

こんばんは！

No.1です。
希望に添えるかどうかわかりませんが・・・
↓の画像を元に説明させていただきます。
尚、6年だけのグループは考えていません（監視役があるため）

基本は4年が3人の班に5年のリーダーが1人で一つのグループと考えています。
まず、4年生3人の班が何班出来るか表示させ、もし3で割り切れない場合（4年生が3人に足りない場合）は不足人数分を5年生が班員となり、
残りの5年生全員で班員3人リーダー1人のグループを作ります。
尚、この場合も5年生が3で割り切れない場合のみ不足人数分を6年生が班員とリーダーになる。
という表で、6年はグループ数に応じて監視役のグループ数を担当しなければならないという理由で、
残りの6年生だけはグループに属さない人数ということです。

ここで残りの6年生数とグループ数で1人の監視役が何グループ監視すればいいか判断するようにすればどうでしょうか？

それぞれのセルの数式を書きますので、参考にしてください。
各学年の人数は好きな数を入力してください。（数に差がありすぎるとマイナス表示になることがあると思います。）

B4=INT(D2/3)
B5=IF(C5=0,INT((C2-B4)/4),INT((C2-B4-(3-C5))/3))
B6=IF(C6<>0,1,0)
C5=MOD(D2,3)
C6=MOD(C2-B4-(3-C5)-B5,3)
C7=B2-(3-C6)-B6
B8=SUM(B4:B6)

以上、参考になれば幸いですが、
的外れの回答なら読み流してくださいね。m(__)m

No.6 回答者： okdeath 回答日時： 2009/05/16 18:46

連続すみません。

よく見たら説明ないので補足します。

計算３の数字の結果の意味ですが、
Ｃ１０：監視員の人数
Ｄ１０：リーダーの人数
Ｅ１０：班員の人数
です。
ちゃんと結果にタイトル付けとくべきでしたねすみません。

No.5 回答者： okdeath 回答日時： 2009/05/16 18:35

こんにちわ。

いじっているうちに理想に近いものができてしましました。
※参考図は以前と同じ人数にしたので計算３の違いを見てください。
質問も回答します。

■優先順位（6年生は、監視役＞リーダー＞班員。5年生はリーダー＞班員。4年生は必ず班員）がある。
・出来てます。

■全ての生徒は監視役、リーダ、班員になる
・上の条件や、人数により割り切れないなどの制約があるためどうしてもあまった人が出てしまいます。（実現不可）ですので「可能な最大数」で対応しています。それともサボる人はいないって意味なんですかねえ？

■監視役は一律同数のグループを監視する（グループ数と監視役で割り切れない場合は、仕方ないけどバランスはとりたい！）
・１人～５人というあいまいな条件だったため難しかったので、考え方を変更し、「固定」の監視数を入力してもらうことで「全員が一律同数のグループを監視する」の条件に対応した。

■監視するグループ数は、定まっていない（適宜数を指定した際のグループ数を求めたい）が条件としてあります。

・先ほどの上の質問に示したとおり、「固定」です。１～５なら、５回で済むので入力してください。（グループ数は自動で最大数になります。）
もしくは、マクロ（５回なのでキー記録程度でいいと思いますが、結果を値貼り付けで別の場所に貼り付けるようにすればよいのでは？）

結構変わったので数式を記載します。

Ｃ４：好きな数字を入力してください。
Ｄ４：好きな数字を入力してください。
Ｅ４：好きな数字を入力してください。
Ｃ７：好きな数字を入力してください。
Ｄ７；好きな数字を入力してください。
Ｅ７：好きな数字を入力してください。
Ｄ８：=INT((C7+D7+E7)/(1+C4*D4+C4*D4*E4)*(C4*D4))
Ｄ９：=IF(D8>D7+C7,INT((C7+D7)/(C4+1))*C4,D8)
Ｃ１０：=IF(D9/C4>C7,C7,INT(D9/D4/C4))
Ｄ１０：=+C10*C4*D4
Ｅ１０：=+D10*E4
Ｅ１１：=+D10
Ｅ１２：=SUM(C7:E7)-SUM(C10:E10)

No.4 回答者： okdeath 回答日時： 2009/05/16 15:58

こんにちわ。

一応、前提条件ですが・・・

■全ての生徒は監視役、リーダ、班員になる
コレは人数により割り切れないときは無理ですし、班員やリーダーが多い場合も当然あふれます。ので、「全ての」っていうのは「出来るだけ多く」と解釈しました。

■監視役は一律同数のグループを監視する（グループ数と監視役で割り切れない場合は、仕方ないけどバランスはとりたい！）
先に書きましたが、俺にはコレは無理っぽい。まあ、さらに計算４、５と進めば出来るよーな気もしますが、
「監視役が一律同数のグループを監視する」というのは、最大公約数でも求めればいいんですかねえ？
他の前提を考慮しつつコレをするのは俺にはむり。白旗です。orz

さて、気を取り直してご質問の計算式の意味ですが、以下のとおり、順番に判定する方法にしてあります。

計算１：4年5年6年全員の合計人数だけを考慮した最大グループ数
=INT((C4+D4+E4)/(1+5+15)*5)
・(1+5+15)は、１人の監視役がいた場合の最大人数です。
この数でわかるのは、「１人の監視役、５グループ、１５人の班員」という、監視役１人の場合の最大構成が何個出来るのかということです。
つまり、下サンプル図だと、
（１＋２４＋１００）÷（１＋５＋１５）＝５．９５２３８１・・・
この結果により、５．９５個の最大構成が出来るようです。
つまり５．９５・・人の監視員のときが最も効率が良いようです。
これを監視役、グループ、班員で分けて考えると
監視役（×１）５．９５・・人、グループ（×５）２９．７６・・人、班員（×１５）８９．２８・・人となります。
知りたいのはグループなのでグループを取り出します。（つまり、５．９５・・×５です）しかし、人数に小数点はおかしいので、int（）で切り捨てます。
結果、全学年合計１２５人からは最大２９グループが可能です。

計算２：計算１の結果プラス5年と6年の人数を考慮した最大グループ数

計算３：計算２の結果プラス6年の人数を考慮した最大グループ数

計算２、３については質問がなかったので、コメントのみにしています。
後から考えると既に何やってたのか忘れてました。歳か・・・・＾＾；

No.3 回答者： okdeath 回答日時： 2009/05/16 04:41

連続失礼します。

式が間違ってましたので訂正します。

・計算２
Ｄ６　=IF(D5>D4+C4,INT((C4+D4)/6)*5,D5)

別の数値のサンプル図もついでに貼っておきます。

この回答へのお礼

おはようございます、ご回答ありがとうございました。
質問が明確でなく、みませんでしたが、
今回のグループ分けは
■優先順位（6年生は、監視役＞リーダー＞班員。5年生はリーダー＞班員。4年生は必ず班員）がある。
■全ての生徒は監視役、リーダ、班員になる
■監視役は一律同数のグループを監視する（グループ数と監視役で割り切れない場合は、仕方ないけどバランスはとりたい！）
■監視するグループ数は、定まっていない（適宜数を指定した際のグループ数を求めたい）
が条件としてあります。

これを前提にご回答を確認させていただくと、
>Ｄ５　=INT((C4+D4+E4)/(1+5+15)*5)
の式の「(1+5+15)*5」がわかりません。教えていただけますでしょうか？

お礼日時：2009/05/16 09:27

No.2 回答者： okdeath 回答日時： 2009/05/16 03:37

こんにちわ。

一応、大体合ってる表を作ってみました。
計算３が、監視役、リーダー、班員の人数です。

計算式の入っているセルは以下のとおりです。
・計算１
Ｄ５　=INT((C4+D4+E4)/(1+5+15)*5)
・計算２
Ｄ６　=IF(D5>C4+D4,D4+C4,D5)
・計算３
Ｃ７　=IF(D6/5>C4,C4,ROUNDUP(D6/5,0))
Ｄ７　=IF(D6/5>C4,C4*5,D6)
Ｅ７　=+D7*3
・最大グループ
Ｅ８　=+D7
・残り人数
Ｅ９　=+D7

＞全員が同数のグループを監視することとし
これはすみませんが無理でした。
なんとなく眺めて調整してください。＾＾；

添付図では、6年の監視員（Ｃ７）は３人となっていますが、余った人数が（E9）４人いますので、6年の監視員は５人としても正解となり、答えが複数になります。（この場合、どちらも最大グループ数は１５）
なので、実現は難しいんじゃないかとおもいます。でも、単に俺がアホなだけのような気もします。orz

少しでも参考になれば幸いです。

No.1 回答者： tom04 回答日時： 2009/05/16 00:58

こんばんは！

質問内容を一生懸命読んで、理解しようと努力したのですが・・・
間違って解釈しているかもしれませんけど、
何か、連立方程式を解くような感じです。
（監視役の6年生は必ず1人は必要なわけですよね？）

少し整理しようと思い、↓の画像のような表を作ってみました。

1グループ作るためには最低5名は必要で、6・5・4年の人数比は
下側の表のようになると思います。

上側の表はグループ数が１～５で監視役の人数を横項目にしています。

質問内容だけから判断すると、
6年：5年：4年の人数比と6～4年生の総人数を見比べながら、
上側の表の中から選ぶ方法しか思い浮かびませんでした。

また、人数によっては表のいずれかにも当てはまらない（割り切れない）場合があると思います。
（この場合の方が多いのかも・・・）

結局　Excelで解決というよりも
いかに連立方程式を作るかというような問題だと思います。
回答になっていなくて、ごめんなさいね。
以上、長々と失礼しました。m(__)m

この回答へのお礼

おはようございます。ご回答ありがとうございました。
昨日は質問後、どうでも良くなり深酒をしてしまい、ただいま起床です。
アドバイスの通り、何らかの連立方程式で、グループ数の解が求まるのかもしれません。

また、質問が明確でなくすみませんでしたが、
■グループの構成は、5年生が1人と4年生の3人が基本
■6年生は監視役（必ずしも1つのグループを監視するわけではない）
■監視役になれない6年生は、必ずリーダー、か、班員になる
■リーダーになれない5年生は、必ず班員になる
■4人構成のグループを最大つくり、余りは、班員とする
が質問の趣旨でした。

4から6年の総人数を求め、それを4で割り大まかなグループ数を求め、目で調整する方法で解決することはできますが、多数の学校を対象としているので、『一般式』がほしかったのですが・・・

ありがとうございました！

お礼日時：2009/05/16 08:54