SUMPRODUCTの応用

質問

添付のように『7.2.1.2』のような章の前から3文字（.（コンマ）を除く）でカウントする方法を考えております。前提として他のセルにLEFT関数で3文字だけを取り除くということをせずにSUMPRODUCT関数を使ってするにはどうしたらいいでしょうか。
SUMPRODUCTにLEFT関数を合体などできるものでしょうか。

回答No.5

kagakusuki · ベストアンサー

D列に例えば「12.18.25」や「1.21.82.5」等の様な検索値が入力される可能性も考えますと、回答No.3様の方法が良いと思います。
　尚、使用するExcelのバージョンがExcel2007以降のものである場合には、SUMPRODUCT関数以外にも、COUNTIFS関数を使用した次の様な関数としますと、同様のカウントを行う事が出来ます。

=IF($D3="","",COUNTIFS($A:$A,$D3&"*",$B:$B,E$2))

　上記の関数をE3セルに入力してから、E3セルをコピーして、E3:F4の範囲に貼り付けてみて下さい。

回答No.6

kagakusuki

回答No.5です。
　申し訳御座いません、回答No.5の関数では、例えば検索値としてD列に「7.2.1.2」と入力した場合には、「7.2.1.2」や「7.2.1.2.1」等だけではなく、「7.2.1.22.1」等もカウントしてしまう事に後から気づきました。
　ですから、E3セルに入力する関数を次の様なものに変更して下さい。

=IF($D3="","",COUNTIFS($A:$A,$D3,$B:$B,E$2)+COUNTIFS($A:$A,SUBSTITUTE($D3&".*","..","."),$B:$B,E$2))

或いは

=IF($D3="","",SUMPRODUCT(OR($A$3:$A$8=$D3,LEFT($A$3:$A$8,LEN($D3)+1)=SUBSTITUTE($D3&".","..","."))*($B$3:$B$8=E$2)))

お礼

挑戦してみます。

回答No.4

MarcoRossiItaly

LEFT 関数とか MID 関数を使うなら、No.2 さんのようにできます。

次式でも可。こちらの場合、2 桁以上の数字にも、数式の修正なしで対応できます。

A13　 7　など
B13　 2　など
C13　 1　など
D12　 A
E12　 B
D13　 =countifs($A:$A,$A13&"."&$B13&"."&$C13&"*",$B:$B,D$12)　　…… Excel 2007 以後
　　　あるいは
　　　=sumproduct(countif(indirect("a"&row(A$3:A$8)),$A13&"."&$B13&"."&$C13&"*")*($B$3:$B$8=D$12))　　…… Excel 2003 以前

お礼

トライしてみます。

回答No.3

keithin

Ｅ３に
=SUMPRODUCT((LEFT($A$3:$A$8,LEN($D3))=$D3)*($B$3:$B$8=E$2))
とかでいいです。

＞3文字（.（コンマ）を除く）でカウントする

ふつーに考えて、なぜわざわざコンマ【，】じゃなくピリオドを「除いて」比較しなきゃならないのですか？

補足

カンマを除いて数字だけ数えた場合3文字という意味で、カンマを入れれば5文字です。紛らわしい説明で申し訳ありませんでした。

回答No.2

bunjii

>前提として他のセルにLEFT関数で3文字だけを取り除くということをせずにSUMPRODUCTにLEFT関数を合体などできるものでしょうか。
ご提示の貼付画像のA列は文字列でも数値でも同等に扱えるように考える必要があるでしょう。
貼付画像に表示されている数式では目的に合いません。

=SUMPRODUCT(((LEFT($A$3:$A$8,3)=$A13&"")*($B$3:$B$8=B$12)))

配列値を対象にLEFT関数を使えるようです。
また、数値で入力されている値もLEFT関数を使うことで文字列に置き換わります。
検索文字列は数値のとき 数値&"" の演算をすることで文字列に変換されます。
更に、 論理値*論理値 → 数値 になるので (論理値*1)*(論理値*1) → 数値 のようにしなくても目的に合います。

お礼

ありがとうございます。やってみます。

回答No.1

mshr1962

=SUMPRODUCT((LEFT(SUBSTITUTE($A$3:$A$8,".",""),3)=$A13)*($B$3:$B$8=B$12))