EXCELの関数についてご教授ください。

質問

EXCELの関数についてご教授ください。
【質問内容】
以下エクセルのデータがあるとします。

A 　　　　　　B 　C 　D 　 E F
2013/10/1 新 100 2013 10 1
2013/10/1 旧 100
2013/10/1 新 101
2013/10/1 新 102
2013/10/1 旧 102
2013/10/1 新 103
2013/10/1 旧 110
2013/10/1 旧 110
2013/10/1 旧 110
2013/10/2 旧 110

※D1,E1,F1には任意の日付を入力できるイメージ

(1)
D1,E1,F1にそれぞれ上記の値(2013年10月1日)を入力した場合、
A列が「2013/10/1」かつ B列が「新」の件数は 「４件」
と出力されるようにしたいです。
※「100」「101」「102」「103」が集計され計4件
※この場合（B列が新の場合）はC列は同じ値は入らないので考慮しなくてよい

(2)
D1,E1,F1にそれぞれ上記の値(2013年10月1日)を入力した場合、
A列が「2013/10/1」かつ B列が「旧」で C列が「重複しているものは1件でカウント」すると 「３件」
と出力されるようにしたいです。
※「100」「102」「110」が集計され計３件

----------
(1)は以下自力で解決出来るようなのですが、

=SUMPRODUCT((A1:A$10=DATE(D1,E1,F1))*(B1:B10="新"))

(2)が難解すぎて質問させて頂きました。

皆様お忙しいところ、大変申し訳ございませんがお力をお借りしたく思います。

回答No.10

kagakusuki · ベストアンサー

>回答No.7で頂きました図の「G2」に「ノーカウント」、「G8」に「カウント不要」の記載がある場合、それぞれカウントされないようにしたいです。

>※G8の「110」は「カウント不要」の記載があるので集計しないが、その下の行に旧で「110」があるので集計件数としては変わらない。

　良く解らないのですが、その場合、G2セルに「ノーカウント」と入力されている以上、C2セルに入力されている「100」は「新」のカウント数としてはカウントされないのですから、（「新」と重複してカウントされる恐れの無くなった）C3の「100」が「旧」としてカウントされる事になり、「旧」のカウント数は「100」と「110」の2件となりますので、「集計件数としては変わらない」という事にはならないのではないでしょうか？
　ですから、その場合のカウント方法は以下の様になると思います。

　御使いになられているExcelのバージョンが、もしExcel2007以降のバージョンである場合には、(1)の関数は

=COUNTIFS($A:$A,DATE(D1,E1,F1),$B:$B,"新",$G:$G,"<>ノーカウント",$G:$G,"<>カウント不要")

になります。
　そして(2)の関数は

=SUMPRODUCT(ISNUMBER(ROW(A2:A15))*(A2:A15=DATE(D1,E1,F1))*(B2:B15="旧")*(COUNTIFS(OFFSET(B2,,,ROW(A2:A15)-ROW(A2)+1),"旧",OFFSET(C2,,,ROW(A2:A15)-ROW(A2)+1),C2:C15,OFFSET(G2,,,ROW(A2:A15)-ROW(A2)+1),"<>ノーカウント",OFFSET(G2,,,ROW(A2:A15)-ROW(A2)+1),"<>カウント不要")=1)*(COUNTIFS(C2:C15,C2:C15,A2:A15,DATE(D1,E1,F1),B2:B15,"新",G2:G15,"<>ノーカウント",G2:G15,"<>カウント不要")=0))

になります。

　一方、御使いになられているExcelのバージョンが、もしExcel2007よりも前のバージョンである場合、データを入力する列が１列増えましたので、（必ずしも必要な事ではありませんが）作業列も1列ずらして、I列とJ列を作業列として使用するものとします。
　まず、I2セルに次の関数を入力して下さい。

=IF(OR($A2&$B2&$C2="",$G2="ノーカウント",$G2="カウント不要"),"",TEXT($A2,"yyyy/m/d")&"◇"&$B2&"◆"&$C2)

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

=IF($I2="","",IF(OR(AND($B2="旧",COUNTIF($I:$I,SUBSTITUTE($I2,"◇旧◆","◇新◆"))),COUNTIF($I$1:$I2,$I2)>1),"",LEFT($I2,FIND("◆",$I2)-1)))

　次に、I2～J2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、(1)の値を表示させるセルに、次の関数を入力して下さい。

=IF(ISNUMBER((D1&"/"&E1&"/"&F1)+0),COUNTIF($J:$J,D1&"/"&E1&"/"&F1&"◇新"),"")

　次に、(2)の値を表示させるセルに、次の関数を入力して下さい。

=IF(ISNUMBER((D1&"/"&E1&"/"&F1)+0),COUNTIF($J:$J,D1&"/"&E1&"/"&F1&"◇旧"),"")

　以上です。

お礼

kagakusuki様

ご連絡が遅くなり申し訳ございません。
毎回丁寧なご回答ありがとうございます。

仕事が立て込んでおり、まだ検証できておりませんが、
お礼申し上げます。

また気になることがあれば、新規でスレッドを立てさせて頂きます。

この度は、お手数お掛けいたしました。

回答No.11

okMumin

No5です、大変申し訳ないのですが、元々、手許に有るのはが機能が限られたQuicklOfficeのみです。関数もここで紹介されているのが使えない、入力もカタツムリのスピード、画面も一度に一つしか見れず、手に余ります。お付き合い頂きありがとうございました。ご健闘を祈ります。私にとり、勉強の機会になりました。

お礼

okMumin様

ご連絡が遅くなり申し訳ございません。
毎回丁寧なご回答ありがとうございます。

貴重なお時間をこのような質問に割いて頂き
ありがとうございます。

また気になることがあれば、新規でスレッドを立てさせて頂きます。

この度は、お手数お掛けいたしました。

回答No.9

okMumin

No5,No6です。申し訳ないです、列A ・列Bの並べ替え優先順位は逆ですね。
それと、式には続きがあり「 =COUNTA(D5:D9)-COUNTBLANK(D5:D9) 」でした。なんか変だと思いつつボタンを押してしまい、お恥ずかしいしいです。
ところで、補足への答え(になっているか実は未確認です (;^_^A )です。また姑息な手段ですが...
優先順位を、列CBAの順にし、さらに列Cと列Aをフィルタにかける。表示される残りのデータを数える！
こちらこそ(他の回答者の方々からも)教えて頂いております。

お礼

遅いお時間にも関わらず回等頂きありがとうございます。

補足

okMumin様

私のような未熟者にお付き合い頂き大変感謝しております。
そしておこがましく申し訳ございませんが、もうひとつ質問させてください。

----
回答No.7内の図の「G2」に「ノーカウント」、「G8」に「カウント不要」の記載がある場合、それぞれカウントされないようにしたいです。
　※「新」「旧」どちらにも「ノーカウント」もしくは「カウント不要」が入る可能性あり。

図の通り日付が「2013/10/1」で集計した場合は

新が「4」
　⇒「101」「102」「103」「105」がカウントされる。
　　「100」はノーカウントの記載があるので集計しない。
旧が「1」
　⇒「110」がカウントされる。
　※G8の「110」は「カウント不要」の記載があるので集計しないが、その下の行に旧で「110」があるので集計件数としては変わらない。　

例
G8、G9、G10に「カウント不要」の記載があった場合、「2013/10/1」の「旧」の集計は「0」になる。

回答No.8

kagakusuki

済みません、先程の回答には一部入力ミスがありました。

×　作業列を2行使用する
　　　　↓
○　作業列を2列使用する

×　作業列を１行で済ます方法
　　　　↓
○　作業列を１列で済ます方法

×　作業列を2行用いる方法
　　　　↓
○　作業列を2列用いる方法

回答No.7

kagakusuki

回答No.2、3です。

>※「100」「102」「110」が集計され計３件となりますが、このうち、(1)の「新」の集計で含まれている「100」「102」はカウントしない。「110のみ集計」され計1件となる関数を希望

　今仮に、A2:C15の範囲にデータがあるものとします。
　御使いになられているExcelのバージョンが、もしExcel2007以降のバージョンである場合には、、(1)の関数は回答No.2で挙げさせて頂いたものと同一の

=COUNTIFS($A:$A,DATE(D1,E1,F1),$B:$B,"新")

になります。
　そして(2)の関数は、一部を変更した

=SUMPRODUCT(ISNUMBER(ROW(A2:A15))*(A2:A15=DATE(D1,E1,F1))*(B2:B15="旧")*(COUNTIFS(OFFSET(B2,,,ROW(A2:A15)-ROW(A2)+1),"旧",OFFSET(C2,,,ROW(A2:A15)-ROW(A2)+1),C2:C15)=1)*(COUNTIFS(C2:C15,C2:C15,A2:A15,DATE(D1,E1,F1),B2:B15,"新")=0))

になります。

　一方、御使いになられているExcelのバージョンが、もしExcel2007よりも前のバージョンである場合には、作業列を2行使用する事になります。
　因みに、C列の値が同じものに関しては、必ず「旧」よりも先に「新」が現れる事が決まっている場合は、作業列を１行で済ます方法もありますが、「新」が必ず先に来るのか否かが確認出来ておりませんので、今回は作業列を2行用いる方法を御伝え致します。
まず、H2セルに次の関数を入力して下さい。

=IF($A2&$B2&$C2="","",TEXT($A2,"yyyy/m/d")&"◇"&$B2&"◆"&$C2)

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

=IF($H2="","",IF(OR(AND($B2="旧",COUNTIF($H:$H,SUBSTITUTE($H2,"◇旧◆","◇新◆"))),COUNTIF($H$1:$H2,$H2)>1),"",LEFT($H2,FIND("◆",$H2)-1)))

　次に、H2～I2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、(1)の値を表示させるセルに、次の関数を入力して下さい。

=IF(ISNUMBER((D1&"/"&E1&"/"&F1)+0),COUNTIF($I:$I,D1&"/"&E1&"/"&F1&"◇新"),"")

　次に、(2)の値を表示させるセルに、次の関数を入力して下さい。

=IF(ISNUMBER((D1&"/"&E1&"/"&F1)+0),COUNTIF($I:$I,D1&"/"&E1&"/"&F1&"◇旧"),"")

　以上です。

お礼

遅いお時間にも関わらず回等頂きありがとうございます。

補足

kagakusuki様

私のような未熟者に親切丁寧にご教授頂き心より感謝いたします。
理解しやすい文章で頭が下がる思いです。
頂きました関数で期待した値が取得出来ました。

そして失礼ついでにもう一つだけ、質問させてください。
　※おこがましく申し訳ございません。最後の質問にいたします。

----
回答No.7で頂きました図の「G2」に「ノーカウント」、「G8」に「カウント不要」の記載がある場合、それぞれカウントされないようにしたいです。
　※「新」「旧」どちらにも「ノーカウント」もしくは「カウント不要」が入る可能性あり。

図の通り日付が「2013/10/1」で集計した場合は

新が「4」
　⇒「101」「102」「103」「105」がカウントされる。
　　「100」はノーカウントの記載があるので集計しない。
旧が「1」
　⇒「110」がカウントされる。
　※G8の「110」は「カウント不要」の記載があるので集計しないが、その下の行に旧で「110」があるので集計件数としては変わらない。　

例
G8、G9、G10に「カウント不要」の記載があった場合、「2013/10/1」の「旧」の集計は「0」になる。

回答No.6

okMumin

No5です、「3種類の並べ替え」の書き間違いです。ご勘弁を...

補足

okMumin様

丁寧なご回答誠にありがとうございます。
非常に勉強になり心より感謝しております。

ただ、一点(2)の集計方法を誤っておりました。
以下を考慮した関数をご教授願えますでしょうか。

何度もご迷惑をお掛けし申し訳ございません。
----------
(2)
D1,E1,F1にそれぞれ上記の値(2013年10月1日)を入力した場合、
A列が「2013/10/1」かつ B列が「旧」で C列が「重複しているものは1件でカウント」すると 「３件」
と出力されるようにしたいです。
※「100」「102」「110」が集計され計３件

！！！追記！！！
※「100」「102」「110」が集計され計３件となりますが、
このうち、(1)の「新」の集計で含まれている「100」「102」はカウントしない。
「110のみ集計」され計1件となる関数を希望

回答No.5

okMumin

ちょっとズルいかもしれませんが、こんな方法はどうでしょうか。3種類の抽出と見なし、A列・B列の優先順で並べ替え、目的の日付と「旧」のデータを連続させ、その連続表示の行に数式を使います。例のデータ分2～11行目、並べ替え後の表示行の先頭が5行目、最後の行は9行目とします。「=IF(C5=C6,"",C5)」を適当な列5～9行目にドラッグアンドドロップ。その列の1行目などに「=COUNTA(C5:C9)」と入れます。

回答No.4

WindFaller

Excel 2007 以上です。
勘違いしていたら、すみません。

=SUM(IFERROR((MATCH(C1:C10,(A1:A10=DATE(D1,E1,F1))*(B1:B10="旧")*C1:C10,0)=ROW(A1:A10))*1,0))

Shift+Ctrl - Enterで、配列の確定をしてください。

補足

WindFaller様

丁寧なご回答誠にありがとうございます。
非常に勉強になり心より感謝しております。

ただ、一点(2)の集計方法を誤っておりました。
以下を考慮した関数をご教授願えますでしょうか。

何度もご迷惑をお掛けし申し訳ございません。
----------
(2)
D1,E1,F1にそれぞれ上記の値(2013年10月1日)を入力した場合、
A列が「2013/10/1」かつ B列が「旧」で C列が「重複しているものは1件でカウント」すると 「３件」
と出力されるようにしたいです。
※「100」「102」「110」が集計され計３件

！！！追記！！！
※「100」「102」「110」が集計され計３件となりますが、
このうち、(1)の「新」の集計で含まれている「100」「102」はカウントしない。
「110のみ集計」され計1件となる関数を希望

回答No.3

kagakusuki

済みません、間違えました。

　H2に入力する関数は

=IF($A2&$B2&$C2="","",TEXT($A2,"yyyy/m/d")&"◇"&$B2&"◆"&$C2)

ではなく

=IF($A2&$B2&$C2="","",IF(COUNTIF(H$1:H1,TEXT($A2,"yyyy/m/d")&"◇"&$B2&"◆"&$C2),"",TEXT($A2,"yyyy/m/d")&"◇"&$B2&"◆"&$C2))

です。

回答No.2

kagakusuki

1行目からデータが続いていたのでは処理が面倒になりますので、仮に、A1:C10の範囲ではなく、A2:C11の範囲にデータがあるものとして話を進める事に致します。
　御使いになられているExcelのバージョンが、もしExcel2007以降のバージョンである場合には、(1)の関数は

=COUNTIFS($A:$A,DATE(D1,E1,F1),$B:$B,"新")

になります。
　そして(2)の関数は

=SUMPRODUCT(ISNUMBER(ROW(A2:A11))*(A2:A11=DATE(D1,E1,F1))*(B2:B11="旧")*(COUNTIFS(OFFSET(B2,,,ROW(A2:A11)-ROW(A2)+1),"旧",OFFSET(C2,,,ROW(A2:A11)-ROW(A2)+1),C2:C11)=1))

になります。

　一方、御使いになられているExcelのバージョンが、もしExcel2007よりも前のバージョンである場合には、関数のみでは(2)を求める事は出来ませんので、他の列のセルを使って、データの前処理を行ってから、関数を使って値を求める必要があります。(この前処理を行う列の事を通称で作業列と言います)

　今仮に、H列を作業列として使用するものとしますと、まず、H2セルに次の関数を入力して下さい。

=IF($A2&$B2&$C2="","",TEXT($A2,"yyyy/m/d")&"◇"&$B2&"◆"&$C2)

　そして、H2セルをコピーして、H3以下に貼り付けて下さい。
　次に、(1)の値を表示させるセルに、次の関数を入力して下さい。

=IF(ISNUMBER((D1&"/"&E1&"/"&F1)+0),COUNTIF($H:$H,D1&"/"&E1&"/"&F1&"◇新◆*"),"")

　次に、(2)の値を表示させるセルに、次の関数を入力して下さい。

=IF(ISNUMBER((D1&"/"&E1&"/"&F1)+0),COUNTIF($H:$H,D1&"/"&E1&"/"&F1&"◇旧◆*"),"")

　以上です。

補足

kagakusuki様

丁寧なご回答誠にありがとうございます。
非常に勉強になり心より感謝しております。

ただ、一点(2)の集計方法を誤っておりました。
以下を考慮した関数をご教授願えますでしょうか。

何度もご迷惑をお掛けし申し訳ございません。
----------
(2)
D1,E1,F1にそれぞれ上記の値(2013年10月1日)を入力した場合、
A列が「2013/10/1」かつ B列が「旧」で C列が「重複しているものは1件でカウント」すると 「３件」
と出力されるようにしたいです。
※「100」「102」「110」が集計され計３件

！！！追記！！！
※「100」「102」「110」が集計され計３件となりますが、
このうち、(1)の「新」の集計で含まれている「100」「102」はカウントしない。
「110のみ集計」され計1件となる関数を希望

回答No.1

tom04

こんにちは！
単に件数を表示できればよい訳ですよね？

SUMPRODUCT関数が手っ取り早いと思うのですが、
＞(2)が難解すぎて質問させて頂きました
とありますので、判りやすい方法としては、↓の画像のように作業用の列を設けてみてはどうでしょうか？
今回は3列設けています。
作業列が目障りであれば遠く離れた列にするか、非表示にしても構わないと思います。

H2セルに西暦年の数値・I2セルに月の数値・J2セルに日の数値を入力するとします。

作業列1のD2セルに
=IF(COUNTBLANK(A2:C2),"",A2&B2&C2)
作業列2のE2セルに
=IF(AND(A2=DATE(H$2,I$2,J$2),B2="新"),ROW(),"")
作業列(3)のF2セルに
=IF(AND(A2=DATE(H$2,I$2,J$2),B2="旧",COUNTIF(D$2:D2,D2)=1),ROW(),"")
という数式を入れ、D2～F2セルを範囲指定　→　F2セルのフィルハンドルでしっかり下へコピーしておきます。

これで画像のように数値が表示されますので、

日付が一致して、「新」の個数を求めたい場合は、表示したいセルに
=COUNT(E:E)

日付が一致して、「旧」の重複なしの個数を求めたい場合は、表示したいセルに
=COUNT(F:F)

という数式を入れれば、お望みの結果が表示されると思います。

※　余計なお世話かも知れませんが、ここまで作業用の列を設けたのであれば
これを利用してデータを抽出することが可能です。

今回はこの程度で・・・m(_ _)m

補足

tom04様

丁寧なご回答誠にありがとうございます。
非常に勉強になり心より感謝しております。

ただ、一点(2)の集計方法を誤っておりました。
以下を考慮した関数をご教授願えますでしょうか。

何度もご迷惑をお掛けし申し訳ございません。
----------
(2)
D1,E1,F1にそれぞれ上記の値(2013年10月1日)を入力した場合、
A列が「2013/10/1」かつ B列が「旧」で C列が「重複しているものは1件でカウント」すると 「３件」
と出力されるようにしたいです。
※「100」「102」「110」が集計され計３件

！！！追記！！！
※「100」「102」「110」が集計され計３件となりますが、
このうち、(1)の「新」の集計で含まれている「100」「102」はカウントしない。
「110のみ集計」され計1件となる関数を希望