エクセル関数の質問

質問

よろしくおねがいします。

以下のようなＡ列Ｂ列に数字が何行もあるのですが、

Ａ列の数字（時間なのですが）を二つを別シートに記入すると

Ａ列の二つ分の期間を参照してＢ列の数字の合計や個数を計算したいのですが、

ＶＬＯＯＫでは期間分の値を計算できないので

何かいい方法はないでしょうか？

A 　　　　　　　B
1000 　　　　　1
2000 　　　　-5
3000 　　　　　8
4000 　　　　　1
5000 　　　　　3
6000 　　　　　5
7000 　　　　-3
8000 　　　　　2
9000 　　　　　3

別シートにＡ列の数字を下記の例のよう二つに入力すると
Ａ列の期間を参照してＢ列の値を計算して

Ｂ列に全合計
Ｃ列に＋の合計
Ｄ列に－の合計
Ｅ列に＋の個数
Ｆ列に－の個数

を表示させる。

＜例1＞
Ａ　　　Ｂ（全合計）　Ｃ（＋の合計）　Ｄ（－の合計）　Ｅ（＋の個数）　Ｆ（－の個数）
1000
5000　　8　　　　　　　　13　　　　　　　　-5　　　　　　　　　4　　　　　　　　1

＜例2＞
Ａ　　　Ｂ（全合計）　Ｃ（＋の合計）　Ｄ（－の合計）　Ｅ（＋の個数）　Ｆ（－の個数）
6000
9000　　7　　　　　　　　10　　　　　　　　-3　　　　　　　　　3　　　　　　　　1

のような感じで計算したいのですが

わかりづらくてすいませんが、分かる方がいたらご教示お願います。

回答No.3

keithin · ベストアンサー

ん？既に回答済みの方策と同じですが。丸ごと全部答えを書いてもらわないと出来ませんか？
あぁ、2007向けと言われたんで見もしなかったって事でしょうか。ゴメンナサイ、余計な事を書きました。

プラスの合計
=SUMPRODUCT((Sheet1!A1:A100>=A1)*(Sheet1!A1:A100<=A2)*(Sheet1!B1:B100>0), Sheet1!B1:B100)
マイナスの合計
=SUMPRODUCT((Sheet1!A1:A100>=A1)*(Sheet1!A1:A100<=A2)*(Sheet1!B1:B100<0), Sheet1!B1:B100)

マイナスの個数（ゼロは含めない）：
=SUMPRODUCT((Sheet1!A1:A100>=A1)*(Sheet1!A1:A100<=A2)*(Sheet1!B1:B100<0))

お礼

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

プラス、マイナスの合計を自分でやってみたのですが、

自分の今の能力ではできなかったので補足させていただきました。

完璧+スマートな式です。

ありがとうございました。

回答No.4

noname#204879

添付図参照
Sheet2 における各式は次のとおり
H1: =MATCH(A1,Sheet1!A:A,0)
H2: =MATCH(A2,Sheet1!A:A,0)
B2: =SUM(OFFSET(Sheet1!B1,H1-1,,H2-H1+1))
C2: =SUMIF(OFFSET(Sheet1!B1,H1-1,,H2-H1+1),">0")
D2: =SUMIF(OFFSET(Sheet1!B1,H1-1,,H2-H1+1),"<0")
E2: =COUNTIF(OFFSET(Sheet1!B1,H1-1,,H2-H1+1),">0")
F2: =COUNTIF(OFFSET(Sheet1!B1,H1-1,,H2-H1+1),"<0")

お礼

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

参考にさせていただきます。

回答No.2

keithin

ご利用のエクセルのバージョンがご質問に書かれていませんが、Excel2007以降を使っているならSUMIFS関数やCOUNTIFS関数で計算します。

全合計：
=SUMIFS(Sheet1!B:B,Sheet1!A:A,">="&A1,Sheet1!A:A,"<="&A2)

プラスの合計：
=SUMIFS(Sheet1!B:B,Sheet1!A:A,">="&A1,Sheet1!A:A,"<="&A2,Sheet1!B:B,">=0")

マイナスの個数：
=COUNTIFS(Sheet1!A:A,">="&A1,Sheet1!A:A,"<="&A2,Sheet1!B:B,"<0")

Excel2003以前しか使えない場合は、SUMPRODUCT関数で工夫します。
全合計：
=SUMPRODUCT((Sheet1!A1:A100>=A1)*(Sheet1!A1:A100<=A2), Sheet1!B1:B100)

プラスの個数（ゼロは含めない）：
=SUMPRODUCT((Sheet1!A1:A100>=A1)*(Sheet1!A1:A100<=A2)*(Sheet1!B1:B100>0))

ご相談投稿では、ご利用のソフト名は元より、普段あなたが使うソフトのバージョンまでキチンと明記することを憶えて下さい。

補足

すいませんでした。エクセル2003を使用してます。

全合計、プラスの個数ともにできました。

プラスの合計はできないのでしょうか？

回答No.1

nakamura-a1

データの入力シートのシート名が"Sheet1"とし、
集計シートのA1に1000、A2に5000が入力されているとして、

B2 : =SUM(OFFSET(Sheet1!B$1,MATCH(A1,Sheet1!A$2:A$10,),,MATCH(A2,Sheet1!A$2:A$10,)-MATCH(A1,Sheet1!A$2:A$10,)+1))
C2 : =SUMIF(OFFSET(Sheet1!B$1,MATCH(A1,Sheet1!A$2:A$10,),,MATCH(A2,Sheet1!A$2:A$10,)-MATCH(A1,Sheet1!A$2:A$10,)+1),">=0")
D2 : =SUMIF(OFFSET(Sheet1!B$1,MATCH(A1,Sheet1!A$2:A$10,),,MATCH(A2,Sheet1!A$2:A$10,)-MATCH(A1,Sheet1!A$2:A$10,)+1),"<0")
E2 : =COUNTIF(OFFSET(Sheet1!B$1,MATCH(A1,Sheet1!A$2:A$10,),,MATCH(A2,Sheet1!A$2:A$10,)-MATCH(A1,Sheet1!A$2:A$10,)+1),">=0")
F2 : =COUNTIF(OFFSET(Sheet1!B$1,MATCH(A1,Sheet1!A$2:A$10,),,MATCH(A2,Sheet1!A$2:A$10,)-MATCH(A1,Sheet1!A$2:A$10,)+1),"<0")

お礼

できました。

自分の能力では関数の意味が理解できませんが

こういうやり方もあるんですね！

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