ID非公開

2020/10/27 22:43

44回答

エクセルでABCDEFと6個に適当な数値があり、300000円を、その6個な均等に振り分けたいのですが、

エクセルでABCDEFと6個に適当な数値があり、300000円を、その6個な均等に振り分けたいのですが、 振り分けた際に数値は最大100000円以上にならないようにしたいです。もし100000円を超えてしまうようなら超えた分を他の項目に振り分ける数式を作りたいです。最初の300000÷6=50000をそれぞれに足せば良さそうですが、その後のもし100000を超えてしまう場合の際振り分けがどうしてら良いか分かりません。よろしくお願いします。

Excel33閲覧xmlns="http://www.w3.org/2000/svg">500

ベストアンサー

1

D6セルに =MAX(E6-$C6,0) これをD6:D11、F6:F11、H6:H11、J6:J11へコピー E6セルに =MAX($C6,MIN($C6+$C$15,$C$3)) これをE11までコピー G6セルに =MAX($C6,IF(E6>=$C$3,$C$3,IF(AND(D$13<6,RANK(E6,$E$6:$E$11,1)<=D$13),1,0)+MIN(E6+$C$16,$C$3))) これをG11までコピー I6セルに =MAX($C6,IF(G6>=$C$3,$C$3,IF(AND(F$13<6,RANK(G6,$G$6:$G$11,1)<=F$13),1,0)+MIN(G6+$C$17,$C$3))) これをI11までコピー K6セルに =MAX($C6,IF(I6>=$C$3,$C$3,IF(AND(H$13<6,RANK(I6,$I$6:$I$11,1)<=H$13),1,0)+MIN(I6+$C$18,$C$3))) これをK11までコピー D12 =SUM(D6:D11) D13 =IF(COUNTIF(D6:D11,0)=6,0,$C$2-D12) この2つをF12、H12、J12へコピー C15セルに =IFERROR(INT(C2/(6-COUNTIF(C6:C11,">="&C3))),0) C16セルに =IFERROR(INT(D13/(6-COUNTIF(E6:E11,">="&C3))),0) C17セルに =IFERROR(INT(F13/(6-COUNTIF(G6:G11,">="&C3))),0) C18セルに =IFERROR(INT(H13/(6-COUNTIF(I6:I11,">="&C3))),0) 上限額を考慮して割り振ったところ、最終の分配金は均等割りには見えませんでした。 分配金の余りをなくすため作業セルがいくつもありますが、作業セルを減らす方法を思いつきませんでした。

画像

1人がナイス!しています

ThanksImg質問者からのお礼コメント

どうもありがとうございました。私の質問も伝わりにくい中、理想の内容に仕上がっていました。元々上限を超えてしまっている部分に関しても、そのまま表示されており、要望を理解していただきありがとうございました。

お礼日時:10/30 19:30

その他の回答(3件)

1

図の様に入力して下さい。 B2: 合計金額 A3: 最大金額 B6以下: 各所持金 B1: =COUNTA(A6:A11) C3: =B2/B1 C1: =COUNTIF(C6:C11,C3) C2: =$B$2-SUM(C6:C11) D3: =C2/C1+C3 C1 C2 D3を右へコピペ。 C6: =MIN($B$3-$B6,C$3) 右下へコピペ。 F6: =B6+E6 下へコピペ。 これで、お金を沢山持っているA、C、Eさんが合計¥100,000になり、 他の人は残りの金を ¥63,333づつ分けます。 CD列がワークエリアです。目障りなら非表示にして下さい。 端数処理をしていません。1~2円合わないのは、調整して下さい。 連続計算をしているので、ワークエリアが途中に来る変な形になりました。

画像

1人がナイス!しています

0

初期値を与えないと答えは出ないです。 例えば全員90000円所持していたらアウトです。初期値は全員の合計が300000円を超えないように設定しておかないとね。 所持金の平均値との差額を50000円から増減すれば公平になります。(表の作り方で数式は変わりますが) 例として C2 =(B$8/6-B2) 下にコピー D2 =SUM(300000/6,C2) 下にコピー E2 =SUM(B2,D2) 下にコピー B8 =SUM(B2:B7) 右にコピー

画像