Excel関数／数式内のセル番地を別セルの値で変化させたい

Excelの関数についての質問です。

たとえばセルA11に
=sum(A2:A10)
という数式が入っているとします。
ここで、A1に入っている値が「20」であったら、この数式の内容が
=sum(A2:A20)
となるようにコントロールしたいのですが、どのように参照式を作ればよいでしょうか。
なお、実際には複数セルの数式を同時に変化させることを想定しています。ご助言いただければ幸いです。

No.2 ベストアンサー 回答者： hyorono 回答日時： 2007/03/01 21:00

ごめんなさい、SUMが多かったです・・・

正しくは
=SUM(INDIRECT("A2:A"&A1))
です。

この回答へのお礼

ありがとうございます。私の例示が間違っていた（循環参照してる！）のに、適切な答えをいただき感謝します。

お礼日時：2007/03/01 21:13

No.5 回答者： hyorono 回答日時： 2007/03/02 23:53

mayorjuneさんの補足の式を引用していいのなら、

=(B1*COUNT(INDIRECT("B2:B"&$A$1)))-SUM(INDIRECT("B2:B"&$A$1))

こういうことですか？
$A$1に、値を入れると想定してます。
もし、意味を取り違えていたらごめんなさい。

この回答へのお礼

再度のアドバイスありがとうございます。hyoronoさんの先のご助言をもとに、まさにそのような式を入れてみて、実際にちゃんと動作することは既に確認しています。実用上はこれで十分です。改めて御礼申し上げます。

この際なので別のかたちでの表現も知っておきたい、と思って補足質問を書かせていただいた次第です。
indirect関数で文字そのものを置き換える方法のほかに、indexやoffsetのような相対指定方法を使うことで見た目すっきりした数式にならないものか、と思案していたのですが、もともとの式に絶対参照を使用している以上、関数で簡便な式をつくること自体に無理があるテーマなのかも知れない、と思いました。
もっとも実用機能満載のExcelだったらひょっとしてそのへんどうにかなるのでは、という無根拠な期待もあるのですが。

お礼日時：2007/03/03 01:01

No.4 回答者： maron--5 回答日時： 2007/03/01 23:54

◆こんな方法もありますよ

=SUM(A2:INDEX(A2:A100,A1))

この回答への補足

この数式もスマートですね！　ありがとうございました。
No.3さんのところに追加質問を記しましたので、お手すきのときにご覧いただければ幸いです。

補足日時：2007/03/02 02:04

No.3 回答者： imogasi 回答日時： 2007/03/01 22:00

通常の回答は出てますが

C1に数があるとして
=SUM(OFFSET($A$1,0,0,C1,1))
という関数も使えます
14
3
2
4
5
6
7
とするとA1:A5までの和で１０となります。
ーー
発展形　最初と最後を間接相対化する。
B1--２番目
C1－－４番目
があるとき
=SUM(OFFSET($A$1,B1-1,0,C1-B1+1,1))
でA列の２－４行和が求められる。
結果９です。

この回答への補足

なるほど。RC形式にこだわらなくとも、相対指定で合計の範囲をコントロールできるわけですね。とても勉強になりました。

もともとの質問が簡略化しすぎだったので、改めて詳しくお伺いさせて下さい。すみません。

一定額の賞金を、複数の参加者が分捕り合うようなゲームでの計算例です。A列には名前、B列には降順でその時点での獲得金額が入っているものとお考え下さい。
100人いる場合、A1の参加者（順位1位）が100位まで落ちるかどうかを判定するには、残りの参加者全員がA1の賞金額を上回るために必要な金額、つまり

=(B1-B2)+(B1-B3)+...+(B1-B99)+(B1-B100)

を計算すればいいわけで、賞金の残高がこの金額未満であれば自動的にA1は99位以上が確定する、という按配です。

上の式は

=(B1*COUNT(B2:B$100))-SUM(B2:B$100)

と単純化できます。絶対参照にしているのは、2位以下についてもこの数式をそのままコピーしようと思ったからです。実際には100位以上が確定すればその時点で入賞発表、などという運用を考えていました。
この数式を、参加者が何人になった場合でもすぐ使えるように「B$100」のところを変数として扱えないだろうか、というのがそもそもの出発点でした。

甚だわかりにくい説明で恐縮ですが、こういうケースで相対参照を使ってスマートに表現することは可能でしょうか。
そもそも最初から人数に合わせてマクロでシートを生成すればいいのでしょうが、あいにくそこまでのスキルがないために苦労しています。Excelの達人の皆様にご助言いただければうれしく思います。

補足日時：2007/03/02 02:01

No.1 回答者： hyorono 回答日時： 2007/03/01 20:59

こんばんわ。

セルA11に、

=sum(SUM(INDIRECT("A2:A"&A1))

という式をいれてください。