期間で重複除く名前のカウントを関数で教えて下さい。

質問

セルE12に、2014年12月22日から2015年1月21日までの期間中、何件あるのかを表示させるため、セルF12のSUMPRODUCT関数で表示させているのですが、データが多く、セルE18にコピーすると、その都度、範囲修正しなければ違った表示となってしまいます。
エクセル機能のフィルターオプション設定を一切使用しないで、エクセル関数で処理できるようにいろいろとチャレンジしましたが挫折しております。
無理難題とは存じますが、ご存知の方がおられましたら、エクセル関数で処理できるようにご教授下さいますようお願いします。

回答No.2

kagakusuki · ベストアンサー

B列の「振込日(基準)」(←質問者様の画像の文字が潰れていて読み難いため、間違っているかも知れません)が、必ず日付順(古い日付が上、新しい日付が下)に並んでいる場合には以下の様な方法を使う事が出来ます。

　今仮に、元の表が存在しているシートがSheet1であるものとします。
　又、Sheet2のA列を作業列として使用するものとします。

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

=IF(AND(INDEX(Sheet1!$A:$A,ROW())<>"",ISNUMBER(DAY(INDEX(Sheet1!$B:$B,ROW())))),IF(COUNTIF(INDEX(Sheet1!$A:$A,ROW()+1):INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$B:$B)+1),INDEX(Sheet1!$A:$A,ROW())),VLOOKUP(INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()+1):INDEX(Sheet1!$B:$B,MATCH(9E+307,Sheet1!$B:$B)+1),COLUMNS(Sheet1!$A$3:$B$3),FALSE),"9999/12/31"+0),"")

　これは、(例えばSheet2のA4セルに入力した場合)Sheet1のA5セルよりも下のセル範囲において、「『Sheet1のA4セルに入力されているのと同じ氏名』が初めて現れる行」に入力されている振替日を返す関数で、Sheet1のA5セルよりも下のセル範囲に同じ氏名が無い場合には9999年12月31日と言う日付が返されます。
　この式をSheet2のA4セルに入力してから、Sheet2のA4セルをコピーして、Sheet2のA5以下に貼り付けて下さい。

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

=IF(AND(INDEX($A:$A,ROW())<>"",ISNUMBER(DAY(INDEX($B:$B,ROW()))),ISNUMBER(DAY(INDEX($D:$D,ROW())))),COUNTIFS($B:$B,">="&INDEX($D:$D,ROW()),$B:$B,"<="&INDEX($B:$B,ROW()),Sheet2!$A:$A,">"&INDEX($B:$B,ROW())),"")

　これは、振込日が「Sheet1のD列に入力されている日付」～「式が入力されているのと同じ行に入力されている振込日」の範囲内の日付となっていて、尚且つ、「Sheet2のA列に入力されている日付」が「式が入力されているのと同じ行に入力されている振込日」よりも後の日付となっている行が何行あるのかをカウントする関数です。
　この式をSheet1のE4セルに入力してから、Sheet1のE4セルをコピーして、Sheet1のE5以下に貼り付けて下さい。

　これで、振込日が「Sheet1のD列に入力されている日付」～「式が入力されているのと同じ行に入力されている振込日」の"期間の中で"「その氏名が最後に現れた行」の行数がカウントされます。(期間内で最後に現れたもののみをカウントするため、重複が排除されます)

　以上です。

お礼

ご回答いただきありがとうございました。
希望どおりの処理ができるようになりました。
エクセル関数は奥が深いのですね。
ところどころわからない関数はございますが、以後勉強してみます。
この度はありがとうございました。

回答No.3

kagakusuki

もし、作業列を使わずにE列に入力された関数だけで結果を求める様にしたいという場合には、SUMPRODUCT関数を使う事になります。(他に配列数式を使う方法もあるのですが、配列数式の場合、計算結果が自動では表示されず、 元データを全て入力し終えてから一々、Ctrl キーと Shift キーを押しながら Enter キーを押さなければ計算が行われません)
　只、SUMPRODUCT関数は指定された行範囲に入力されているデータを、(パソコンが内部で自動的にではありますが)1行ずつ繰り返し計算を行う関数であるため、行範囲があまり大きくなりますと計算に要する負荷が大きくなり過ぎて、計算に時間が掛かる様になるという短所がありますので、あまりお勧めではありません。(配列数式も同様に繰り返し計算を行うものですので重くなります)
　尤も、データが1000行程度という事ですので、その位なら今のパソコンの性能であれば無茶苦茶重く感じる様な事にはならないと思います。

　さて本題ですが、まずE4セルに次の様な関数を入力して下さい。

=IF(AND(INDEX($A:$A,ROW())<>"",ISNUMBER(DAY(INDEX($B:$B,ROW()))),ISNUMBER(DAY(INDEX($D:$D,ROW())))),SUMPRODUCT((INDEX($A:$A,MATCH(LARGE($B:$B,COUNTIF($B:$B,">"&INDEX($D:$D,ROW()))),$B:$B,0)):INDEX($A:$A,MATCH(INDEX($B:$B,ROW()),$B:$B))<>"")*(COUNTIF(OFFSET(INDEX($A:$A,MATCH(LARGE($B:$B,COUNTIF($B:$B,">"&INDEX($D:$D,ROW()))),$B:$B,0)),,,ROW(INDEX($A:$A,MATCH(LARGE($B:$B,COUNTIF($B:$B,">"&INDEX($D:$D,ROW()))),$B:$B,0)):INDEX($A:$A,MATCH(INDEX($B:$B,ROW()),$B:$B)))-MATCH(LARGE($B:$B,COUNTIF($B:$B,">"&INDEX($D:$D,ROW()))),$B:$B,0)+1),INDEX($A:$A,MATCH(LARGE($B:$B,COUNTIF($B:$B,">"&INDEX($D:$D,ROW()))),$B:$B,0)):INDEX($A:$A,MATCH(INDEX($B:$B,ROW()),$B:$B)))=1)),"")

　次に、E4セルをコピーして、E5以下に貼り付けて下さい。

　以上です。

お礼

関数だけのご回答いただきありがとうございます。
今の自分ではとても理解し難い関数の組み合わせですが、勉強して対応できるようになりたいと思います。
この度はありがとうございました。

回答No.1

doraneko66

＞エクセル機能のフィルターオプション設定を一切使用しない

ってなると相当めんどくさいです。

1つ1つデータを2014年12月22日から2015年1月21日なのかを判定をさせて
そのTUREの数を数えます。