[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『数字の分配』（美沙）
F1セルに数字があります。
A7からE10で横軸の表があります。
D列には必ず数字があります。（分配率）
C列には現在=MAX((ROUNDUP($F$1/(SUM($D$7:$D$10))*D7,0)),0)
の数式があります。
F1が20000の場合でD列が上から2,2,1,3の時、C列は上から
5000、5000、2500、7500となります。
そこでE列に分配ボーナス？みたいな欄を作りたいです。
E列に上から-1000、-3000、2000、0と入力し、これをC列の計算式に足した場合
C7は5000-1000で4000、C8は5000-3000で2000、C9は2500+2000で4500
C10はそのまま7500となります。
これだとC列の合計が18000となり、F1の20000より下回ってしまいます。
必ずC列の合計がF1の数字と同じか上になり、なおかつ、C列はマイナスの数字にはならない様にしたいです。
しかし、E列の合計が+5000の場合、F1の20000と合わせて25000を分配するのではなく、あくまでも分配の基準はF1の数字であり、
分配率で分けた場合に小数点以下になる場合のみ繰り上げたいです。
F1の数字を上手く分配するには、C列にどの様な計算式を入力すれば良いですか？
分かりずらいとは思いますが、よろしくお願いいたします。
＜ 使用 Excel：Excel2010、使用 OS：Windows10 ＞
＞レイアウトを示して説明したらどうですか。
A B C D E F
1 20000
2
…
7 5000 2 -1000
8 5000 2 -3000
9 2500 1 2000
10 7500 3
と現在はなっています。
これでE列を含めた計算式をご教授お願い致します。
（美沙） 2024/03/06(水) 10:43:12
> そこでE列に分配ボーナス？みたいな欄を作りたい 「そこで」がどこから繋がってる話なのか分からなかったんですが、 あんまり深く考えないでもいいんですかね? (このせいなのか以降読み進めてもフワフワしてよく分からない)
まあ、D列から按分値した数値を単純に加減調整するものなんだと予想して、 でもそうなると、 > C列はマイナスの数字にはならない様に って、それはE列に入れる数値次第なんじゃないの? ってことになりませんかね...
[___]|___A____|___B____|___C____|___D____|___E____|___F____|___G____|___H____| [ 1]| | | | | | 20000| | | [ 2]| | | | | | | | | [ 3]| | | | | | | | | [ 4]| | | | | | | | | [ 5]| | | | | | | | | [ 6]| | | | | |Dで案分 |Eを足す |再度按分| [ 7]| | | ? | 2| -1000| 5000| 4000| 4445| [ 8]| | | ? | 2| 2000| 5000| 7000| 7778| [ 9]| | | ? | 1| -3000| 2500| -500| -556|←こんな感じ [ 10]| | | ? | 3| | 7500| 7500| 8334| [ 11]| | | | | | | | | [ 12]| | | | 8| | 20000| 18000| 20001|
[F7] =$F$1*D7/D$12 [G7] =F7+E7 [H7] =ROUNDUP($F$1*G7/G$12,)
（白茶） 2024/03/06(水) 16:07:57
こんな事なのかな？
＞C列には現在=MAX((ROUNDUP($F$1/(SUM($D$7:$D$10))*D7,0)),0) ↓ C7セル =MAX(ROUNDUP(($F$1-SUM(E$7:E$10))/SUM($D$7:$D$10)*D7,0),0)+E7
（半平太） 2024/03/06(水) 16:12:12
あれ？ E列は後で足す、と言う話しなら。
＞C7セル =MAX(ROUNDUP(($F$1-SUM(E$7:E$10))/SUM($D$7:$D$10)*D7,0),0)+E7
↓
C7セル =MAX(ROUNDUP(($F$1-SUM(E$7:E$10))/SUM($D$7:$D$10)*D7,0),0)
（半平太） 2024/03/06(水) 16:20:22
皆様、返事が遅くなりすいません。
白茶様。 > 「そこで」がどこから繋がってる話なのか分からなかったんですが、 そこでという表現は間違いでした。「新たに」E列に分配ボーナス？みたいな 欄を作りたい でお願いします。
> って、それはE列に入れる数値次第なんじゃないの? ってことになりませんかね... いくつか表のシートがあり、F1及びD列、E列はそれぞれ別の他の計算シートからvloouupの計算式にて 持って来ています。 また、下に200行程あり、E列を計算しているSheetが他部署から来ているので直すのは難しい状況です。
また提示していただいた計算式ですとマイナスを含めてF1の数値に近づいているため、 再度按分の際もマイナスは0になるようにしたいです。
半平太様 E列の合計がマイナスの場合も、F1-E列で18000になる場合も、 あくまでも分配の基準はF1の数字でお願いします。
よろしくお願いいたします。 （美沙） 2024/03/11(月) 09:12:43
（わからん） 2024/03/11(月) 09:45:51
返信ありがとうございます。
F1、D列、E列は都度変動しています。 F1、D列、E列はそれぞれ別の条件により変動していますので、E列のマイナスのみ補正することは難しいです。 なので、都度目視しながらE列のマイナス値を変更させるのは難しいです。
よろしくお願いいたします。 （美沙） 2024/03/11(月) 10:07:34
質問内容に矛盾を感じているのですが、 過去にうまく行った例があるなら、実例を一つ挙げて頂けませんか？
（半平太） 2024/03/11(月) 10:09:52
返信ありがとうございます。
>質問内容に矛盾を感じているのですが、 上手く説明が出来ずすいません。 矛盾と感じる場所をしてきしていただければ説明させていただきます。
>過去にうまく行った例があるなら、実例を一つ挙げて頂けませんか？ 今回、E列を新たに作成することとなりましたので、実例がありません。 すいません。
よろしくお願いいたします。 （美沙） 2024/03/11(月) 11:25:13
実例がなければ、希望図で結構です。
まず、シンプルに２行だけの例で考えたい。
下図の C7、C8、C9セルは それぞれどうなればいいんですか？ ※３つ全部分からなければ、分かるセルだけでいいです。
<希望図> 行 _A_ ___B___ _C_ _D_ __E__ ___F___ 1 20,000
6 7 C7 3 2,000 8 C8 1 1,000 9 合計 → C9
（半平太） 2024/03/11(月) 14:26:22
返信ありがとうございます。
C7はD列の合計が4、そのうちD7は3ですのでF1の20,000の3/4の15,000が基本 C8は同じ理論で1/4の5,000が基本となります。 そこにE列の査定が入ります。それぞれ2,000と1,000ですので 足すと7行目が17,000、8行目が6,000となります。合計が23,000となり F1の20,000より3,000多いのでそれぞれ3/4の2,250と1/4の750を引いて
C7が14,750、C8が5,250、C9が20,000としたいです。
よろしくお願いいたします。
（美沙） 2024/03/11(月) 15:06:31
7行目 +2000査定 基本 15,000 → 最終 1,4750 その差 -250 8行目 +1000査定 基本 5,000 → 最終 5,250 その差 +250 査定が大きいと最終で減る？
（XX労組） 2024/03/11(月) 15:20:51
返信ありがとうございます。
>査定が大きいと最終で減る？ D列の分配率が大きく影響するのかと思います。 半平太様の例でE7が3,000の場合ですとC7が15,000、C8が5,000 4,000の場合ですとC7が15,250、C8が4,750となります。
よろしくお願いいたします。
（美沙） 2024/03/11(月) 15:32:38
C7セルに下記の式で下にコピーどうだろう。
=($F$1*D7/SUM($D$7:$D$20)+E7-SUM($E$7:$E$20)*D7/SUM($D$7:$D$20))
D列は最大20行目までとしているので、それより多くなる場合は20を数値を増やす （hatena） 2024/03/11(月) 15:40:02
返信ありがとうございます。
E列以外のマイナスは0として計算したいです。
よろしくお願いいたします。 （美沙） 2024/03/11(月) 15:50:53
＞C7が14,750、C8が5,250、C9が20,000としたいです。
それって、私が提示したこの式と同じ結果ですよね？ ↓ C7セル =MAX(ROUNDUP(($F$1-SUM(E$7:E$10))/SUM($D$7:$D$10)*D7,0),0)+E7
<結果図> 行 ___C___ _D_ __E__ ___F___ 1 20,000
6 7 14,750 3 2,000 8 5,250 1 1,000 9 20,000
マイナスの場合に更にどんな再調整をするのか分からないですが、 多分、単にマイナスになったものを0にするだけでは済まない話なんでしょうね。
数式だけでは対処できない気がしますので、私はここまでとします。 m(__)m
（半平太） 2024/03/11(月) 16:17:15
行 ___C___ _D_ __E__ ___F___ 1 20,000 6 7 5,000 2 8 5,000 2 9 2,500 1 10 7,500 3
これを基本とします。 E列に基本C列より少ないマイナスがある場合は
行 ___C___ _D_ __E__ ___F___ 1 20,000 6 7 5,000 2 1,000 8 6,000 2 2,000 9 1,000 1 -1,000 10 8,000 3 2,000
と、したいです。 E列に基本C列より多いマイナスがある場合は
行 ___C___ _D_ __E__ ___F___ 1 20,000 6 7 5,286 2 1,000 8 6,286 2 2,000 9 0 1 -3,000 10 8,429 3 2,000
と、したいです。
よろしくお願いいたします。 （美沙） 2024/03/11(月) 16:37:10
> E列以外のマイナスは0として計算したいです。
その条件を見落としてました。
数式だけでは無理そうですね。 VBAで再帰処理になりそうな気がしますね。 （hatena） 2024/03/11(月) 16:46:48
そういう調整ですか・・
再査定は幾らなんでも３回くらいで収まるとすると、
(1) G7セル =$F$1/(SUM($D$7:$D$10))*D7 (2) H7セル =MAX(0,G7+E7) (3) I7セル =IF(OR(H7=0,H7=""),"",ROUNDUP(H7-(H$11-$F$1)*$D7/SUMIF(H$7:H$10,"<>0",$D$7:$D$10),0)) (4) C7セル =LOOKUP(9^9,G7:K7)
それぞれ、下にコピー(10行目まで）
I7:I10を選択して、右にコピー(K列まで）
<結果図> 行 ___C___ _D_ ___E___ ___F___ ___G___ ___H___ ___I___ ____J____ ____K____ 1 20,000
6 基本 査定後 再査定1 再査定2 再査定3 7 5,286 2 1,000 5,000 6,000 5,286.0 5,286.0 5,286.0 8 6,286 2 2,000 5,000 7,000 6,286.0 6,286.0 6,286.0 9 0 1 -3,000 2,500 0 10 8,429 3 2,000 7,500 9,500 8,429.0 8,429.0 8,429.0 11 20,001 20,000 22,500 20,001 20,001.0 20,001.0
（半平太） 2024/03/11(月) 17:21:32
11行目に合計(SUM関数)をいれてください。 m(__)m
（半平太） 2024/03/11(月) 17:24:20
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.