【画像】関数にあるセル範囲を自動的に変更する

質問

タイトルの件、質問いたします。

下記の図のとおり、Sheet１に下記の関数があります。

●B2セル=SUMPRODUCT((Sheet2!$A$2:$A$4="佐藤")*(Sheet2!$B$2:$B$4<>""))
→Sheet2のデータを計算しています。佐藤さんの、訪問回数を数えています。

Sheet2の【データ１】は、自動的に、【データ2】に変わります。

その時、Sheet1のB2セルの関数の範囲が

●【$B$2:$B$4】から【$B$2:$B$6】

に変更する事はできないでしょうか？？

ご存知の方、いらっしゃいましたら、宜しくお願いします。

【エクセルバージョン】
２００３
２００７

回答No.1

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

何をしたいのか説明があれば、最適な方法を提示できたと考えられます。

案1.根本的に
(B2セルに)
=COUNTIF(Sheet2!A:A,"佐藤")

案2.参照範囲を可変にする方法
(B3セルに)
=SUMPRODUCT((Sheet2!$A$2:INDEX(Sheet2!$A:$A,MAX(2,COUNTA(Sheet2!$A:$A)))="佐藤")
*(Sheet2!$B$2:INDEX(Sheet2!$B:$B,MAX(2,COUNTA(Sheet2!$A:$A)))<>""))

案3.参照範囲を可変にする方法2
(B4セルに)
=SUMPRODUCT((Sheet2!$A$2:INDEX(Sheet2!$A:$A,MAX(2,MATCH("ー",Sheet2!$A:$A)))="佐藤")
*(Sheet2!$B$2:INDEX(Sheet2!$B:$B,MAX(2,MATCH("ー",Sheet2!$A:$A)))<>""))

さて、どのような結果がほしいのでしょうか？

お礼

ご回答ありがとうございます。
セル範囲が、可変した時に、関数のセル範囲も自動的に、可変になるようにしたいと
かんがえていました。

案２、案３で実現する事ができました。
ありがとうございます。

回答No.3

imogasi

これも前問と同じく質問の表現が判りにくい。
データの増減によって関数式（の範囲表現部分）を変えたくない、を、しっかり表現するｊこと
ーー
例データ
Ｓｈｅｅｔ１　ＡとＢ列のデータは
佐藤 1
近藤 2
田中 3
佐藤 1
佐藤 3
とする。
質問の関数を尊重すると
Ｓｈｅｅｔ１の何処かのセルに
=SUMPRODUCT((OFFSET(Sheet2!A2,0,0,COUNTA(Sheet2!$A$2:$A$100),1)="佐藤")*(OFFSET(Sheet2!B2,0,0,COUNT(Sheet2!$B$2:$B$100),1)<>""))
結果　3
Ａ列は文字列、Ｂ列は数の列なので、ＣＯＵＮＴＡ、ＣＯＵＮＴを使い分けている。
上記データの行を増減すると（佐藤を）、即座に反応するだろう。
佐藤 1
近藤 2
田中 3
佐藤 1
佐藤 3
田中 1
佐藤 1
佐藤 1
結果　５　に変わる。
ーー
しかしこんな式で、２００７ではしない。
ＳＵＭＰＲＯＤＵＣＴはＣＯＵＮＴＩＦＳが使えるだろう。

お礼

いつも、ご回答ありがとうございます。
さらに、質問の仕方について、ご指導いただきありがとうございました。

回答No.2

merlionXX

> ●【$B$2:$B$4】から【$B$2:$B$6】

> に変更する事はできないでしょうか？？

画像のような配置なら、範囲を変更しなくとも最初から$B$2:$B$6にしておけば両方に対応できると思いますが・・・。

お礼

ご回答ありがとうございます。
セル範囲が、可変する事に対して、自動で、変更する事ができないか、質問しておりました。