プロが教えるわが家の防犯対策術!

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

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

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

③「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は、Eグループの9名まで
    (AからDまでは全員+Eグループの9人)

    H6の5は、Kグループの5名
    (Eグループの残り+FからJグループの全員+Kグループの5人)

    という感じです。
    よろしくお願いします。

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

    ありがとうございます!
    C2、D2に入れる関数について、大変参考になりました。
    J5、J6、L5、L6、L7の部分についてはちょっと認識が違うようです・・・

    というのも、今回は3グループですが、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

A 回答 (4件)

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



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パターン)等、人数を変更して検証してみてください。
「指定した数字まで累計する方法や文字例の抽」の回答画像3
    • good
    • 1
この回答へのお礼

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

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

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

No.3です。


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

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

J5=IF(D5="","",$E$2+IF(COUNT(J$4:J4)<MOD($B$19,$E$1),1,0))
下へフィルコピー
「指定した数字まで累計する方法や文字例の抽」の回答画像4
    • good
    • 1

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


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

添付図参照(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)
「指定した数字まで累計する方法や文字例の抽」の回答画像2
この回答への補足あり
    • good
    • 2
この回答へのお礼

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

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

確認させてください。


添付図中のセル H5、H6 のそれぞれの数値 9、5 は何を指していますか?
この回答への補足あり
    • good
    • 1

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング