指定した数字まで累計する方法や文字例の抽出について教えてください

全地区の合計人数をグループに分けて
分けた時の区切りの表を作成したいのですが、良い方法が思いつきません。

①青色のセル（F1）に何グループに分けるかを指定（今回は3グループ）

②黄色のセル（F2）に１グループの人数を表示させる
　（総合計を①のグループ数で割って四捨五入しています）

③「D5～J7」を計算できる関数などを教えて欲しいです。

　　緑色のセルの説明をすると・・・
　　①グループには　A地区～D地区までの全員とE地区の9人の90名
　　②グループには　E地区の残り1人とF・G・H・I・J地区の全員、K地区の5人の90名
　　③グループには　K地区の残り9人とL～P地区までの全員の89名

　※グループ数を2にする時は、③の行は空欄としたいです。


分かりにくい質問で申し訳ございませんが、ご教示のほどよろしくお願いします。

質問者からの補足コメント

• ありがとうございます。
  H5、H6には、それぞれ端数を表示しています。
  
  H5の9は、Ｅグループの9名まで
  （AからＤまでは全員＋Ｅグループの9人）
  
  H6の5は、Ｋグループの5名
  （Ｅグループの残り＋ＦからJグループの全員＋Ｋグループの5人）
  
  という感じです。
  よろしくお願いします。

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/10/06 11:35

• 

  ありがとうございます！
  C2、D2に入れる関数について、大変参考になりました。
  J5、J6、L5、L6、L7の部分についてはちょっと認識が違うようです・・・
  
  というのも、今回は３グループですが、2グループになる場合もあったり、各グループの人数が変更になる場合もあったりする為、①がA～E　②がE～K　③がK～Pになるとは限らないんです。
  なので、J5の場合だと「B6-C6」とは限らず、「B5-C5」の時もあったりします。
  
  左側の表で言うと、TMPaの赤いグループを①、黄色いグループを②、緑のグループを③というTMPc列を作って、①グループの開始組（A2セル）の文字列をG5へ、終了組（A6セル）の文字列をI5へのように文字の部分も自動的に変わるようにしたいです。
  
  またお時間あれば回答ください。
  宜しくお願いします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2022/10/06 19:30

No.3 ベストアンサー 回答者： Belltreeriver 回答日時： 2022/10/06 20:55

以下で試してみてください。

E2=ROUNDUP(B19/E1,0)※四捨五入から切り上げに変更
D5=IFERROR(INDEX({"①";"②";"③";"④";"⑤";"⑥";"⑦";"⑧";"⑨";"⑩";"⑪";"⑫";"⑬";"⑭";"⑮";"⑯";"⑰";"⑱";"⑲";"⑳"},IF(COUNTA(D$4:D4)+1<$E$1+1,COUNTA(D$4:D4)+1,999)),"")

E5=A2（最初なので固定です）

E6=IF(D6="","",IFERROR(INDEX(A:A,AGGREGATE(15,6,1/(SUBTOTAL(9,INDIRECT("B1:B"&ROW(B$2:B$17)))>=$E$2*ROW(A1))*ROW(B$2:B$17),1)+IF(H5=0,1,0)),LOOKUP("黑",$A$1:$A$17)))

F5=IF(E5="","","～")

G5=IF(E5="","",IFERROR(INDEX(A:A,AGGREGATE(15,6,1/(SUBTOTAL(9,INDIRECT("B1:B"&ROW(B$2:B$17)))>=
$E$2*ROW(A1))*ROW(B$2:B$17),1)),LOOKUP("黑",$A$1:$A$17)))

H5=IFERROR(IF(SUM(INDEX($B:$B,MATCH($E$5,A:A,0)):INDEX($B:$B,MATCH(G5,A:A,0)))=SUM(J$5:J5),0,SUM(J$5:J5)-SUM(INDEX($B:$B,MATCH($E$5,A:A,0)):INDEX($B:$B,MATCH(G5,A:A,0)-1))),"")

J5=IFERROR(MIN($B$19,AGGREGATE(15,6,($E$2*ROW(INDIRECT("1:"&$E$1))),ROW(A1)))-SUM(J$4:J4),"")

E5以外（D5,E6,F5,G5,H5,J5）は適当数下へフィルコピー

条件付書式（数式）にて塗りつぶし色と罫線を設定
適用先（E5,G5,H5,J5から数式をコピペした範囲）を選択
数式に　=E5<>""　と入力し、書式（塗りつぶし色と罫線）を設定

地区の区切りとGrの区切りが同じ場合（H列が0パターン）等、人数を変更して検証してみてください。

この回答へのお礼

ありがとうございます！
思い通りの動作になりました！！

これで毎回手作業で振り分けていた作業がすごく楽になりました。
大変助かりました！

お礼日時：2022/10/06 22:52

No.4 回答者： Belltreeriver 回答日時： 2022/10/06 22:10

No.3です。

端数人数（切捨て分）を各Grへ割り振るように変更してみました。
次の数式を変更して試してください。

E2=INT(B19/E1)※端数切捨て

J5=IF(D5="","",$E$2+IF(COUNT(J$4:J4)<MOD($B$19,$E$1),1,0))
下へフィルコピー

No.2 回答者： mike_g 回答日時： 2022/10/06 16:04

貴方が期待されるようなスマートな方法とは程遠いのですが、

参考までに。(*^_^*)

添付図参照(Excel 2019)
C2: =MOD(SUM(C1,B2),H$2)
D2: =IF(ISNUMBER(C1),IF(OR(C2<C1,C3=""),A2,""),"")
H2: =ROUND(B18/H1,0)
J5: =B6-C6
J6: =B12-C12
L5: =SUM(B2:B5)+J5
L6: =C6+SUM(B7:B11)+J6
L7: =B18-SUM(L5:L6)

この回答へのお礼

分かりにくい説明にも関わらずヒントを頂きありがとうございました！
大変助かりました！

お礼日時：2022/10/06 22:54

No.1 回答者： mike_g 回答日時： 2022/10/06 11:01

確認させてください。

添付図中のセル H5、H6 のそれぞれの数値 9、5 は何を指していますか？