エクセル初心者です。順位の高い順に、別の列の数値を合計していきたいので

質問

エクセル初心者です。順位の高い順に、別の列の数値を合計していきたいのですが、どういう関数を使えばよいか教えていただけませんか？

順位の高い順（1,2,3,,,,,）に、別の列の数値を合計していき、最終的には別途指定した数値からオーバーしたセルは、オーバーした分を加味して、数値を再表示したいです。

例えば、指定した数値が「２７」の場合、最終的にD列の数値を算出したいです。

A列（順位）___B列（数値）___C列（Bの合計）＿D列
７______＿＿_____６_______＿＿____２８______＿＿____５*
１＿＿___________３_____＿＿______３_____＿＿_______３
３_______＿＿____６____＿＿_______１１_______＿＿___６
８_____＿＿______４_______＿＿____３２____＿＿______０**
２______＿＿_____２________＿＿___５________＿＿____２
４______＿＿_____４__＿＿_________１５______＿＿____４
６______＿＿_____２______＿＿_____１７__________＿＿２

（*C１が指定した数値を「１」オーバーしたため）
（**既にこの前の順位で指定した数値をオーバーしたため）

この場合、どのような関数を使えばよいのでしょうか。
上の説明ではC列を書きましたが、C列がなくても良いです。

行数は最大150行で、順位や数値は変わっていきます。（指定した数値も、途中で変わる可能性があります）

どなたか、エクセル詳しい方、教えていただけませんか？
他のサイトでも質問をしていますが、まだ回答がないため、こちらでも質問させていただきました。
よろしくお願いいたします。

回答No.3

KURUMITO · ベストアンサー

回答Ｎｏ２です。
回答Ｎｏ1で補足されていますが、すべてこちらで考えていたことです。回答Ｎｏ2でお望みの結果が得られると思いますね。ぜひお試しください。

お礼

ご回答いただきありがとうございます。
・間違いだらけの質問だったのに早くに回答をいただけたこと
・直接D列の関数を教えていただけたこと、
・A列がブランクだった場合も対応できるようご回答いただけたこと、
この3点が決め手でベストアンサーに選ばせていただきました。

急いでいたため、とても助かりました。ありがとうございました。

回答No.6

kagakusuki

回答番号:ANo.1です。
　今仮にA列に7が入力されているのがA1セルだとします。
　まず、C1セルに次の数式を入力して下さい。

=B1+IF(A1>1,VLOOKUP(A1-1,A:C,3,FALSE),0)

　次に、D1セルに次の数式を入力して下さい。

=(B1-(C1-$E$1)*(C1>$E$1))*(B1>C1-$E$1)

　そして、C1セルとD1セルをコピーして、2行目以下に貼り付けて下さい。
　以上で完成です。
　それから、C1セルとD1セルに入力する数式は、別の回答にある数式を組み合わせても、正常に作動すると思います。
（例えば、C1セルには回答番号:ANo.5様の数式を入力し、D1セルにはこの回答の数式を入力するなど）

お礼

ご回答いただきありがとうございます。
C列にはVLOOKUPも使えるのですね、勉強になります。

SUMIFやMINやVLOOKUPなど、良く使っている関数でこの問題が解けることには全く気付かず…
関数は、発想の転換？が大事なのだと思いました。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

>他のサイトでも質問をしていますが、
マルチポストといってマナー違反です。
好ましい行為ではありません。
(掲示板によっては許可しているところもありますが。)

C1セル
=SUMIF($A$1:$A$8,"<="&A1,$B$1:$B$8)
D1セル
=IF($E$1>C1,B1,MAX(0,B1+$E$1-C1))
E1セルに 指定値 27
範囲は適宜変更してください

この投稿のマルチメディアは削除されているためご覧いただけません。

お礼

ルールをよくわかっておらず、違反してしまい申し訳ございませんでした。
以後気をつけます。

ご回答の方もありがとうございます。
C列がSUMIFで出せることに驚いたのと共に、とても勉強になりました。

回答No.4

web2525

まずはC列に関して
=SUMPRODUCT((RANK(A1,$A$1:$A$8,1)>=$A$1:$A$8)*$B$1:$B$8)
A列のデータ範囲を【順列範囲】、B列のデータ範囲を【集計範囲】と名前付けして
=SUMPRODUCT((RANK(A1,順列範囲,1)>=順列範囲)*集計範囲)
とすると分かりやすいかも

D列は上記の応用で
=MIN(B1,(27-SUMPRODUCT((RANK(A1,順列範囲,1)>順列範囲)*集計範囲)>0)*(27-SUMPRODUCT((RANK(A1,順列範囲,1)>順列範囲)*集計範囲)))
こんな感じで

お礼

ご回答いただきありがとうございます。
MINを使ってもD列が出せるなんて、とても勉強になりました。

回答No.2

KURUMITO

データがＡ列およびＢ列の1行目から下方に有るとして最大で150行目までに有るとします。
答えをＣ列に表示させるとしてＣ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",IF(SUMPRODUCT((A$1:A$150<=A1)*(B$1:B$150))<=27,B1,IF((B1-(SUMPRODUCT((A$1:A$150<=A1)*(B$1:B$150))-27))<0,0,(B1-(SUMPRODUCT((A$1:A$150<=A1)*(B$1:B$150))-27)))))

回答No.1

kagakusuki

すみませんが、御質問の内容に不明瞭な点が多々ありますので、このままでは回答がつかないと思います。
　このサイトにログインされた後、この御質問のページを開かれると、この回答欄に[補足する]と記されたボタンが現れますから、そのボタンをクリックされて、以下の不明な点に対する補足情報をお知らせ願います。

(1)A列に入力されている順位には、5がありませんが、順位は連続していない場合もあるのでしょうか。

(2)順位が6番目のC列の値である17に、順位が7番目のB列の値である6を加えた値が、順位が7番目のC列に表示される筈ですが、何故23ではなく、28になっているのでしょうか。（23は27をオーバーしていません）

(3)「オーバーしたセルは、オーバーした分を加味して、数値を再表示」するのであれば、順位が7番目のB列の値である6に、28が27をオーバーした分である1を加えて、7になると思うのですが、何故C1は5になるのでしょうか。

(4)「**既にこの前の順位で指定した数値をオーバーしたため」とありますが、再表示するのであれば、何故、同じ行のB列の値である4を表示せずに、0が表示されるのでしょうか。（それとも、再表示は行わないのでしょうか）

(5)8番目のD列の値が0という事は、9番目やそれ以降の順位におけるD列の値も0なのでしょうか、もし、0ではない場合は、どの様な数値になるのでしょうか。

補足

ご連絡ありがとうございます。

質問文を読み返してみて、一部抜け落ちていることに気付きました！
↓が正しいです、申し訳ございません。

A列（順位）___B列（数値）____C列（Bの合計）_D列
７______＿＿_____６_______＿＿____２８______＿＿____５*
１＿＿___________３_____＿＿______３_____＿＿_______３
３_______＿＿____６____＿＿_______１１_______＿＿___６
８_____＿＿______４_______＿＿____３２____＿＿______０**
２______＿＿_____２________＿＿___５________＿＿____２
４______＿＿_____４__＿＿_________１５______＿＿____４
６______＿＿_____５__＿＿_________２２______＿＿____５
５______＿＿_____２__＿＿_________１７______＿＿____２

（順位6番目のB・C・D列、順位5番目のA列を追加しました）

（1）順位は必ず連続しています。

（2）順位6番目のB・C・D列、順位5番目のA列を追加修正したため、順位7番目以降のC列に矛盾がなくなりました。

（3）（4）「加味して」では「＋」の意味になってしまいますね。「調整して」に修正します。
C列が指定した数値をオーバーした場合、D列には、「B列－（C列－「指定した数値」）」を表示させ、その値が「－」の時は「０」を表示させたいです。順位7番目ですと6－（28－27）＝5、順位8番目ですと4-（32-27）＝-1⇒0、のような感じです。

（5）9番目以降も「0」となります。

色々と不備がありまして申し訳ありません。また疑問点があれば教えてください。
よろしくお願いします。