[[20091029220828]]　『SUMPRODUCTとMATCHの使用方法について』（だいきょ）　ページの最後に飛ぶ

『SUMPRODUCTとMATCHの使用方法について』（だいきょ）

A列に"関数"と記述されていて
且つ
B列に"○"と記述されている

個数を求めたい場合

以下の関数式にて求められますが、

=SUMPRODUCT((A1:A10="関数")*(B1:B10="○"))

「A1、A10」の箇所をMATCH関数を用いて

取得したいと考えています。

たとえば、A5に"関数開始"と記述されている場合

=MATCH("関数開始",A:A,0)

の結果は「5」が得られます。

この「5」を元に以下のように変更したいのですが、

その方法がわかりません。

=SUMPRODUCT((A5:A10="関数")*(B1:B10="○"))
とするのではなく、

=SUMPRODUCT(A(MATCH("関数開始",A:A,0):A10="関数")*(B1:B10="○"))

                ^^^^^^^^^^^^^^^^^^^^^^^^^^
のようにしたいです。

もちろん他の関数を使ったほうがよければ
その方法でもかまいません。

エクセルのバージョンは2003です。
よろしくお願いします。

 まず基本として
 配列数式 論理値演算
 これらのことを理解しているという前提で話を進めます。

 数式の変更という話の前に
 具体的に「何をしたい」「何をカウントしたい」のでしょうか？

 具体的なデータを提示して
 説明していただけますでしょうか。

 （ていおう）2009/10/29 22:58

 こんなことかな

 =SUMPRODUCT((A1:A10="関数")*(B1:B10="○")*(ROW(A1:A10)>=MATCH("関数開始",A:A,0)))

 By しげちゃん

 申し訳ありません。説明不足でした。
 以下のような構成になっています。

 ある関数内の処理をエクセルにコピーし、
 その処理（ログ）が正常系なのか、異常系なのか
 B列に記入しています。

 そこで、A列が"異常系"　且つ　B列が"○"
 の行をカウントしたいと思い、以下の方法では確認していますが、
 SUMPRODUCT((A10:A15="正常系")*(B10:B15="○"))

 関数開始から終了までのボリュームが関数によって
 異なるため、"関数開始"から"関数終了"までの範囲を
 MATCH("関数開始",A:A,0)、MATCH("関数終了",A:A,0)
 のようにして求め、その結果を上記SUMPRODUCT()で
 使用したいと思っています。

 上記２つの関数を用いて、以下のようにして実行する方法を探しています。
 （例：A10をMATCHで求め、それをSUMPRODUCT()の引数に指定する）
 SUMPRODUCT(A(MATCH("関数開始",A:A,0):A15="正常系")*(B10:B15="○"))

     A列        B列
 10  関数開始	
 11  正常系	
 12  異常系	○
 13  正常系	
 14  異常系	○
 15  関数終了

 よろしくお願いします。

 もう、回答はしていますが・・・

 回答ではできなかったのかな

 回答が理解できなのかな
 応用ができないのかな

 配列数式 論理値演算 を理解していますか

 By しげちゃん

 すみません。
 ご教授いただいた内容を理解できていません。
 もう少しアドバイスいただけると助かります。

 参照セルを行番号で指定する方法はいくつかあります。Index関数を使うのもその一つです。

 例えば、A列の5行目なら、こんなことになります。→ =INDEX(A:A,5)

 それを利用すると、こんな風になります。

   =SUMPRODUCT((INDEX(A:A,MATCH("関数開始",A:A,0)):INDEX(A:A,MATCH("関数終了",A:A,0))="異常系")*(INDEX(B:B,MATCH("関数開始",A:A,0)):INDEX(B:B,MATCH("関数終了",A:A,0))="○"))

 厳密に考えると、マッチした行番号に、"異常系"がある訳はないので、
 行番号は『±1の微調整が必要』ですが、計算量増加は微々たるものなので無視しています。

 (半平太) 2009/10/30 12:03

 ※だいきょさんが意図する式なら 
 SUMPRODUCT(A(MATCH("関数開始",A:A,0):A15="正常系")*(B10:B15="○"))
    ↓      ^^^^^^^^^^^^^^^^^^^^^^^^^
 SUMPRODUCT((INDIRECT("A"&MATCH("関数開始",A1:A15,0)):A15="正常系")*(INDIRECT("B"&MATCH("関数開始",A1:A15,0)):B15="○"))
           　^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 となりますね。INDIRECTは揮発関数です。

 ちなみに　SUMPRODUCT((A5:A10="関数")*(B1:B10="○")
                       ^^^^^^          ^^^^^^
 ではエラー(#N/A)となるはずです。SUMPRODUCTで使用する範囲は同じでなければなりません。

 ※しげちゃんさんの式
 SUMPRODUCT((A1:A10="関数")*(B1:B10="○")*(ROW(A1:A10)>=MATCH("関数開始",A:A,0)))
                                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 MATCHで検索した行(5行目)以上の行には1(TRUE)が返され、未満の行には0(FALSE)が返されます。
 1行目から4行目までは0、5行目から10行目までは1、それを(A1:A10="関数")*(B1:B10="○")に乗じています。
 したがって4行目までは0を乗じますので、カウントされないことになります。

 説明下手ですが、お役にたてば。。。(gon-2)

 ちょっと流れに逆らってしまいますが

 配列数式や論理値の利用は理解しているけど
 条件が多い場合の数式の組み方がわからないという場合は

 まずは作業列を使用したらどのようになるかを考えてみてはいかがでしょうか。

 今回の条件
 ・A列が 異常系
 ・B列が ○
 ・A列の 関数開始 以上の行
 ・A列の 関数終了 以下の行
 これをAND条件で作業列で数値を出して、合計すればカウントできます。

 （えくせる青年）2009/10/30 12:29

 (gon-2)さんが説明してくれましたので、説明省略

 > (B10:B15="○")
 と範囲が固定されていますが、固定なのですか?

 その上で、関数開始・関数終了　で可変にするわけですよね?
 また、関数開始・関数終了　は範囲の中に出現するのですか
 範囲以外にある場合もありますか
 範囲外に該当列もあるということですよね

 By しげちゃん

 みなさま。
 お忙しいところ、様々なアドバイス、ご教授ほう
 ありがとうございました。
 おかげさまでやりたいことができるようになりました。

コメント返信：

[ 一覧(最新更新順) ]

[[20091029220828]] 『SUMPRODUCTとMATCHの使用方法について』（だいきょ） ページの最後に飛ぶ

[[20091029220828]]　『SUMPRODUCTとMATCHの使用方法について』（だいきょ）　ページの最後に飛ぶ