SUMPRODUCT関数 行が増えても自動で

SUMPRODUCT関数　行が増えても自動で最終行を取得するには？
エクセル２００３です。

名前　　サイズ
みかん　　S
みかん　　M
りんご　　S

このような表があったら、
=SUMPRODUCT((A1:A4="みかん")*(B1:B4="S"))
で、複数条件の個数が取得できますが、
行がどんどん増えていったときに、手動で
A4・B4の部分を変更していくしかないのですか？

それとも大体の行を見越して、
=SUMPRODUCT((A1:A100="みかん")*(B1:B100="S"))
のようにするべきなのでしょうか？

=SUMPRODUCT((A:A="みかん")*(B:B="S"))
にしたらエラーになりました。

自動で最終行を取得したいのですが、できますか？

No.1 ベストアンサー 回答者： tom04 回答日時： 2013/03/11 21:42

こんばんは！

今後行が増えていくのであれば

＞それとも大体の行を見越して、
＞=SUMPRODUCT((A1:A100="みかん")*(B1:B100="S"))
＞のようにするべきなのでしょうか？

と仰っているように多めの行の範囲で関数を作成しておいた方が良いと思います。

※　SUMPRODUCT関数は配列数式になってしまいますので、
データ量が極端に多い場合はPCに負担をかけ、計算速度が落ちてしまいます。
1000行程度であれば問題ないと思いますよ。m(_ _)m

この回答へのお礼

パソコンの負荷を考えて数式を作った方がよさそうですね。ご回答ありがとうございました。

お礼日時：2013/03/12 21:15

No.4 回答者： KURUMITO 回答日時： 2013/03/12 14:22

どんなにデータが多くなっても計算に負担を掛けない、しかも範囲を指定する必要もない方法は作業列を用いる方法です。

例えばC１セルに次の式を入力して下方にドラッグコピーします。

=A1&B1

そこでみかんでSの行を数えるのでしたら次の式で良いでしょう。

=COUNTIF(C:C,"みかんS")

最終行を全く気にする必要もありません。

この回答へのお礼

ご回答ありがとうございます。

お礼日時：2013/03/18 22:34

No.3 回答者： keithin 回答日時： 2013/03/11 22:09

＞自動で最終行を取得したいのですが、できますか？

簡易には、上から隙間なくデータが詰めてある前提で
=SUMPRODUCT((OFFSET(A1,0,0,COUNTA(A:A),1)="みかん")*(OFFSET(B1,0,0,COUNTA(A:A),1)="S"))
のように取得できます。

可変範囲を取得する関数は今使ったOFFSET関数やINDIRECT関数が利用できますので、練習しておいてください。


またマニア向けには
=SUMPRODUCT((A1:INDEX(A:A,COUNTA(A:A))="みかん")*(B1:INDEX(B:B,COUNTA(A:A))="S"))
といったのもあります。



＃エクセル２００７以降を使い、SUMPRODUCT関数を使うのはすっぱりやめてCOUNTIFS関数などを利用するのが、最もお勧めの方法です。

この回答へのお礼

自分では作れないような難しい関数ですがうまくできました。
オフセットについて勉強してみます。

お礼日時：2013/03/12 21:16

No.2 回答者： mike_g 回答日時： 2013/03/11 22:06

》 行がどんどん増えていったときに…

式 =SUMPRODUCT((A1:A4="みかん")*(B1:B4="S")) の場合、
行を「どんどん増」やす場所を、範囲 A2:A4 内に行挿入した処にすれば如何かと。

この回答へのお礼

そうすれば自動でエクセルが認識して増えてくれますね。

お礼日時：2013/03/12 21:15