はじめての親子ハイキングに挑戦!! >>

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

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

このQ&Aに関連する最新のQ&A

A 回答 (5件)

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


正しくは
=SUM(INDIRECT("A2:A"&A1))
です。
    • good
    • 11
この回答へのお礼

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

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

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



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

こういうことですか?
$A$1に、値を入れると想定してます。
もし、意味を取り違えていたらごめんなさい。
    • good
    • 0
この回答へのお礼

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

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

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

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


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

この回答への補足

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

補足日時:2007/03/02 02:04
    • good
    • 2

通常の回答は出てますが


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

この回答への補足

なるほど。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
    • good
    • 1

こんばんわ。



セルA11に、

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

という式をいれてください。
    • good
    • 0

このQ&Aに関連する人気のQ&A

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

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

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qセル番号を返す関数

エクセルで、セル番号を返す関数はありますか??
セルの内容ではなくて、番号です。(A1、B3など)
調べてみましたがなかなか見つからなかったので、
わかる方教えてください。
宜しくお願い致します。

Aベストアンサー

文字として"C4"を返す。
=ADDRESS(ROW(C4),COLUMN(C4),4)

式を入力したセルの位置を文字列で返す。
=ADDRESS(ROW(),COLUMN(),4)

ADDRESSの3つ目の引数を変えると
$C$4
$C4
C$4
C4
の4つの形に出来ます。

QExcel 例A(1+9) のように番地のたし足し算の方法を教えてください

Excelで指定したセルの番地番号に+(9*1),+(9*2)と番地の等比数列の計算方法を教えてくださいお願いいたします。

例 A B C D
1 1 =A(1+1)
2 2 =A(1+2)
3 3 =A(1+3)

上のように番地番号を足したいのですがエラーが出てしまします。

Aベストアンサー

=INDIRECT("A"&(1+1))
では?
因みに、(1+1)のところは、数値が入っているセルを指定してもOK。

Qエクセルでセルの値を数式の範囲として使うには?

タイトル通りなのですが、エクセルでセルの値を数式中で範囲として使うにはどうすればよいのでしょうか?

例えばA1セルに「1」という値が入っており、B2セルに「10」という値が入っているとします。この時、数式中で、「A1」と「B2」を用いて、「E1からE10まで」を指定することは可能でしょうか。可能でしたらどのような数式を組めば良いのでしょうか? よろしくお願いします。

Aベストアンサー

(1)タイプ1
セル範囲を(見かけは文字列で)入れるタイプの関数の場合
見かけで、普通に文字列で、手作業でセルに式を入れるような番地文字列を作ります。
A1に2、B1に10が入っているとします。
例えば D5に ="A"&A1&":B"&B1
するとD5は A2:B10 となります。
関数を手から入れるときは=SUM(A2:B10) ですから
=SUM(D5) で良さそうですが、そうは行きません。
エクセルに番地として解釈してくださいよ、という意味で
=SUM(INDIRECT(D5))
とします。
これで結果11
(A2:B10に
A列  B列
51
6
しか、入っていないときの例)になります。
(2)タイプ2
結果的に範囲を対象にしますが、列や行(の隔たり)の数などを引数に、それぞれ独立して数として入れるタイプの関数の場合
例OFFSET関数
=SUM(OFFSET($A$1,1,0,9,2))

=SUM(OFFSET($A$2,0,0,B1-1,A1))
セルの値をそのまま数字として考えればよい例。
この場合数の変わりにセル番地を入れると良い。

(1)タイプ1
セル範囲を(見かけは文字列で)入れるタイプの関数の場合
見かけで、普通に文字列で、手作業でセルに式を入れるような番地文字列を作ります。
A1に2、B1に10が入っているとします。
例えば D5に ="A"&A1&":B"&B1
するとD5は A2:B10 となります。
関数を手から入れるときは=SUM(A2:B10) ですから
=SUM(D5) で良さそうですが、そうは行きません。
エクセルに番地として解釈してくださいよ、という意味で
=SUM(INDIRECT(D5))
とします。
これで結果11
(A2:B10に
A列  B列
51
...続きを読む

Qシート参照で変数を使いたい(EXCEL)

通常、Excelで別シートのセルを参照する時は「'シート名'!B3」といった数式になりますが、この「シート名」をユーザーに入力してもらうような仕組みを作りたいと考えています。

・あるシートのA3(例)にシート名を入力すると、B4セルに入力されたシートのB3セルの内容が表示される

このようなこと、可能でしょうか?

Aベストアンサー

INDIRECT関数を使うといいでしょう。

セルA1に入力されたシート名の、セルB3を参照する場合

   =INDIRECT(A1& "!B3")


また上記ではセルB3が固定になってますが、
セルA1 に参照したい【シート名】をいれ
セルA2 に参照したい【セル番地】まで入れておくと
なお、使い勝手がよくなるかも知れませんね。

   =INDIRECT(A1& "!" & A2)
 
 
 

Qエクセル 関数の中で他のセルの数値を参照させれませんか?

エクセルについてお教えください。

関数の式の中で、式の一部を他セルの数値から引っ張ることはできないでしょうか?

具体例を示しますと

  =IF(A○>0,B△,C×)

のような関数の際に、「A○」の○の部分を、「D□」に入力した数値に指定したい場合です。

そのようなことは可能でしょうか?

Aベストアンサー

可能です。
=IF(INDIRECT("A"&D□)>0,B△,C×)

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Qエクセルの関数で,たとえばB15のセルの値を返すには?

エクセルの関数の質問です。

たとえば,
A1のセルに7
A2のセルに8
が入力されているとします。

ここで,少し回りくどいのですが,
A1(の7)と,A2(の8)と,関数などを使って,
あるセルに,B15のセルの値を返したいのですが,
可能でしょうか?

間違ってますが,
=B(A1+A2)
のような式を書きたいのですが…。


あっさりと,
=B15
とすればいいのはわかっていますが,
やむえない事情がありまして…。

よろしくお願いします。

Aベストアンサー

=INDIRECT("B"&A1+A2)
で良いと思います。

QEXCELでのセル座標を指定する数式について

EXCELで以下のようなことをやりたいのですが,よくわかりません。
どなたかアドバイスをお願いします。


(1)=セルアドレス1←”セルアドレス1”の値を参照
(2)=セルアドレス1の2行下のセルアドレス←”セルアドレス1の2行下のセルアドレス”の値を参照

(1)の式は全く問題ないのですが,(2)をどのように式で表現すればいいのかがよくわかりません。

Aベストアンサー

=OFFSET(A1,2,0)


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

人気Q&Aランキング