Excel2003 特定の条件での合計 自動更新

質問

excel2003について教えてください。
(1)参照する行の1つ上の行に特定の文字があり、かつ、参照する行に別の特定の文字がある場合の組合せの合計数を求めたいと思います。

(2)また、行が更新されたときに自動的に参照範囲も更新したいです。

これらの条件に合うようにするためには、どのようにすればよいでしょうか？

例えば、画像のような場合、
(1)1つ上の行にaが含まれている場合で、かつ、その下の行にbが含まれている組合せの合計数を求める。

(2)この場合は、「A1のa」と「D2のb」で1組目、「C4のa」と「A5のb」で2組目となる。

(3)さらに、今後行が更新されていく。

(4)この答えをE3に出す。

以上になります。よろしくお願いします。

回答No.2

tom04 · ベストアンサー

こんにちは！

データはA1セル以降にあるとします。
作業列を使ってもよいのであれば・・・
F2セル（←1行目ではありません）に
=IF(AND(COUNTIF(A1:D1,"a"),COUNTIF(A2:D2,"b")),1,"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてE3セルには
=SUM(F:F)
としてOK

作業列がダメであればVBAになってしまいますが、一例です。
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, cnt As Long, c As Range, r As Range, myArea As Range
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Set myArea = Range(Cells(i, "A"), Cells(i, "D"))
Set c = myArea.Find(what:="a", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Set r = myArea.Offset(1).Find(what:="b", LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then
cnt = cnt + 1
End If
End If
Next i
Range("E3") = cnt
End Sub 'この行まで

※　マクロの場合はデータ変更があるたびにマクロを実行する必要があります。m（_ _）m

お礼

ご回答頂きありがとうございます。
前半の作業列を使う方法は、関数があまり分からない私でも理解しやすいもので、無事に目的のことができました。
後半のVBAは少しずつ勉強したいと思います。
とても参考になるご回答を頂き、ありがとうございました。

回答No.3

kagakusuki

【方法その1】
　適当な使用していない列（ここでは仮にF列とします）を作業列として使用します。

　まず、F1セルに次の関数を入力して下さい。

=(COUNTIF($A1:$D1,"a")*COUNTIF($A2:$D2,"b")>0)+0

　次に、F1セルをコピーして、F2以下に貼り付けて下さい。
　次に、E3セルに次の関数を入力して下さい。

=SUM(F:F)

【方法その2】
　E3セルに次の関数を入力して下さい。

=SUMPRODUCT((COUNTIF(OFFSET($A$1,ROW($A$1:$A4)-ROW($A$1),,1,COLUMNS($A:$D)),"a")*COUNTIF(OFFSET($A$1,ROW($A$1:$A4)-ROW($A$1)+1,,1,COLUMNS($A:$D)),"b")>0)*1)

【方法その3】
　E3セルに次の関数を入力して下さい。

=SUMPRODUCT(ISNUMBER(FIND("◆a◆","◆"&A1:A4&"◆"&B1:B4&"◆"&C1:C4&"◆"&D1:D4&"◆"))*ISNUMBER(FIND("◆b◆","◆"&A2:A5&"◆"&B2:B5&"◆"&C2:C5&"◆"&D2:D5&"◆")))

お礼

複数の方法をご回答頂き、ありがとうございます。
なるほどいろんなアプローチがあるんだなと、とても参考になりました。
問題解決と、ご回答の時間からほかの方にベストアンサーをつけさせていただきましたが、案内頂いた方法も、様々に応用していくきっかけになると思います。
この度はありがとうございました。

回答No.1

mshr1962

一例です。
E3=SUMPRODUCT((A1:E6="*a*")*(B1:F6="*b*"))

お礼

すばやいご回答を頂きありがとうございます。
実は今回の目的にはSUMPRODUCT関数がうまく使えず、質問させていただきました。
ご回答例も参考にして、今後勉強したいと思います。
この度はありがとうございました。