月単位で追加したデータ毎に課題IDの重複を取り去り

質問

タイトル＝【重複行を１としてカウント 月が重複した総数を求める】でkkkkkmさんには大変お世話になりました、nnirosanです。

実は、今実施しております課題ID数をピポットテーブル上で集計する時に、ご回答頂いていました下記のご教示通りの結果となりまして、
対処方法を模索しているのですが、具体的な方法が分からず困っております。

2022/09/02 22:30
回答No.13　kkkkkmさん
『4月と5月で同じパターンのデータがあれば5月のデータは無いこととして「0」になります。
そういう事が無いのであればいいですが。』
=IF(OR(AND(V$1>3,$Q2>V$1)),0,IF(AND(V$1>3,V$1<=12,OR($Q2=1,$Q2=2,$Q2=3)),0,IF(AND(V$1=1,OR($Q2=2,$Q2=3)),0,IF(AND(V$1=2,$Q2=3),0,IF(COUNTIFS($Q$2:$Q2,MONTH($E2),$D$2:$D2,$D2,$L$2:$L2,$L2,$M$2:$M2,$M2,$P$2:$P2,$P2)=1,1,0)))))

・課題ＩＤの重複を取り去る為の式は、今現在、下記の数式を使用しております。
　下記の式では、ある課題ＩＤが4月～9月分データ中に複数ある場合、4月～8月分では結果は『0』、9月分で1行『1』が出ます。
　ピポットテーブル上で4月～8月分の課題ＩＤ数の集計を求めた時、上記のある課題ＩＤは集計されない事になってしまっています。
　=IF(OR(AND(AU$1>3,$F2>AU$1)),0,IF(AND(AU$1>3,AU$1<=12,OR($F2=1,$F2=2,$F2=3)),0,IF(AND(AU$1=1,OR($F2=2,$F2=3)),0,IF(AND(AU$1=2,$F2=3),0,IF(COUNTIFS($C$2:$C2,$C2,$M$2:$M2,$M2,$Q$2:$Q2,$Q2)=1,1,0)))))

・4月～8月分について、課題ＩＤの重複を取る為に下記の式を試して見ましたが、旨く出来ませんでした。
　=IF(AND(AU$1>3,($F2<=8)),IF(COUNTIFS($C$2:$C2,$C2,$M$2:$M2,$M2,$Q$2:$Q2,$Q2)=1,1,0))

・重複無しの課題ＩＤの集計については、下記のように、1か月ずつ追加したデータ毎にピポットテーブル上で集計を出したいのですが、具体的な方法がお分かりでしたら、ご教示頂けると大変助かります。
　4月分で重複無しの課題ＩＤの集計。
　4月～5月分の重複無しの課題ＩＤの集計。
　4月～6月分の重複無しの課題ＩＤの集計。
　4月～7月分の重複無しの課題ＩＤの集計。
　4月～8月分の重複無しの課題ＩＤの集計。
　4月～9月分の重複無しの課題ＩＤの集計。
　　　　｜
　4月～来年3月分の重複無しの課題ＩＤの集計。

回答No.1

kkkkkm · ベストアンサー

現状の式は以前とセル範囲が違うと思われますので具体的にどうやればいいのか分かりませんが

最後に月を確認している部分を追加してみてください。

式の最後の以下の部分を修正してください。
IF(COUNTIFS($C$2:$C2,$C2,$M$2:$M2,$M2,$Q$2:$Q2,$Q2)=1,1,0)
↑この部分を以下のような状態にしてください。
MONTH($E2)のE2に現状の日付が入っているセルを指定
Q2に
=MONTH(E2)
で月を取得しているところがあったと思いますので
$Q$2:$Q2を現状の月を取得しているセルを指定しください。
それ以降のセル指定は課題ＩＤなど重複を確認したいセルを指定してください。
IF(COUNTIFS($Q$2:$Q2,MONTH($E2),$D$2:$D2,$D2,$L$2:$L2,$L2,$M$2:$M2,$M2,$P$2:$P2,$P2)=1,1,0))

お礼

大変お世話になっております。
質問にかみ砕いたご教示を頂きまして、誠に有難うございました。
丁寧な説明で良く理解致しました。
データを日付順に並べて置けば、ご教示頂いた重複判定の数式を用いて課題総数が正しく求められる事を確認しました。

kkkkkmさんには、マクロの自動実行など、自分には到底実現出来なかった事をご教示頂き、心より感謝を申し上げます。
お陰様で、4月より今現在まで仕事を続ける事が出来ております。本当に命拾いをしました。
かみ砕いて、何時もお優しくご教授下さり本当に有難うございました。

補足

大変お世話になっております。
kkkkkmさんには、沢山の事をご教授頂き、心より感謝申し上げます。

早速のご教示ありがとうございました。
あれから、担当者が変わり課題数の求め方も変化しまして、部署毎の所属区分毎で重複無しの課題数を求める事になりました。
課題IDの集計は、下記の通りに行います。
　4月分で重複無しの課題ＩＤの集計。
　4月～5月分の重複無しの課題ＩＤの集計。
　4月～6月分の重複無しの課題ＩＤの集計。
　4月～7月分の重複無しの課題ＩＤの集計。
　4月～8月分の重複無しの課題ＩＤの集計。
　4月～9月分の重複無しの課題ＩＤの集計。
　　　　｜
　4月～来年3月分の重複無しの課題ＩＤの集計。

以下の式をデータの最後の列(AU列)へ挿入し課題IDの重複判定(0,1)をしますが、E列の利用日は日付順(4月～9月と)に並べてから下記の式を実行すると、正確な課題数の集計が出ました。
しかしながら、以下の式が本当に正しいのか、自分には判断する力がまだ有りません。
面倒で大変恐縮なのですが、お分かりでしたらご教示頂けると大変助かります。
=IF(OR(AND(AU$1>3,$F2>AU$1)),0,IF(AND(AU$1>3,AU$1<=12,OR($F2=1,$F2=2,$F2=3)),0,IF(AND(AU$1=1,OR($F2=2,$F2=3)),0,IF(AND(AU$1=2,$F2=3),0,IF(COUNTIFS($C$2:$C2,$C2,$M$2:$M2,$M2,$Q$2:$Q2,$Q2)=1,1,0)))))

ピポットテーブルでは、フィルター＝(月)、列＝(所属区分)、行＝(部署、部ループ)、値＝(合計/課題ID-4)で課題ID数を集計します。

下記はデータの例題になります。
C列＝部署
E列＝利用日
F列＝月(＝MONTH(E2)関数で利用日から月を求める)
G列＝開始時刻
H列＝終了時刻
I列＝利用時間
M列＝所属区分
O列＝利用区分
Q列＝課題ID
,,C列,,E列,F列,G列,H列,I列,,,,M列,,O列,,Q列,,,,,,,,,,,,,,,,,,,,,,,,,,,,,　
,,Aグループ,,2022/4/1,4,9:00,17:00,8,,,,管理部,,使用不可,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,B グループ,,2022/4/11,4,12:00,17:00,5,,,,SSS,,構内利用,,5067,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,B グループ,,2022/5/13,5,9:00,13:00,4,,,,SSS,,構内利用,,5067,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,B グループ,,2022/4/18,4,9:00,12:00,3,,,,AAA,,外部利用,,8029,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Dグループ,,2022/4/18,4,15:00,18:00,3,,,,AAA,,外部利用,,5030,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Dグループ,,2022/8/29,8,10:00,13:00,3,,,,SSS,,構内利用,,5337,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Cグループ,,2022/8/29,8,13:00,16:00,3,,,,SSS,,構内利用,,5337,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Cグループ,,2022/6/30,6,13:00,16:00,3,,,,WWW,,部署利用,,9337,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Aグループ,,2022/3/31,3,13:00,16:00,3,,,,CCC,,内部利用,,5129,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Aグループ,,2022/8/31,8,16:00,17:00,1,,,,WWW,,部署利用,,9337,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Aグループ,,2022/5/27,5,11:00,11:30,0.5,,,,WWW,,部署利用,,5103,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Cグループ,,2022/5/5,5,12:30,16:00,3.5,,,,SSS,,構内利用,,5125,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Cグループ,,2022/7/6,7,13:30,13:45,0.25,,,,CCC,,内部利用,,5129,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Cグループ,,2022/7/28,7,9:30,10:00,0.5,,,,管理部,,使用不可,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Eグループ,,2022/7/29,7,11:15,11:45,0.5,,,,CCC,,内部利用,,5129,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Eグループ,,2022/8/19,8,13:30,14:30,1,,,,CCC,,内部利用,,5177,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Eグループ,,2022/9/6,9,20:00,21:00,1,,,,SSS,,構内利用,,5176,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Gグループ,,2022/9/6,9,21:00,21:30,0.5,,,,SSS,,構内利用,,5176,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Gグループ,,2022/6/7,6,11:00,11:45,0.75,,,,CCC,,内部利用,,5333,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

回答No.2

kkkkkm

たとえば4月と5月で同じパターンのデータがあれば5月のデータは無いことになってしまっているという話ではないのでしょうか。

お分かりだとは思いますが、とりあえず式の意味を記載してますので、それで正しいかどうかはご自身で判断してください。

IFは左から判定されて条件の結果が「真」の場合「真」の結果を実施し、以降の「偽」の部分は無視されます。

IF(OR(AND(AU$1>3,$F2>AU$1)),0,IF(AND(AU$1>3,AU$1<=12,OR($F2=1,$F2=2,$F2=3)),0,IF(AND(AU$1=1,OR($F2=2,$F2=3)),0,IF(AND(AU$1=2,$F2=3),0,

ここまでで重複をチェックしない月(6月なら7月以降)を除外(0にする)すると考えています。
どれかに引っかかると以降の重複確認の式は実施されません。

IF(OR(AND(AU$1>3,$F2>AU$1)),0,
1行目が4月以降で1行目の月よりF列の月が大きい場合除外(0にする)、以降の式は無視

IF(AND(AU$1>3,AU$1<=12,OR($F2=1,$F2=2,$F2=3)),0,
1行目が4月から12月まではF列の1月から3月は除外(0にする)、以降の式は無視

IF(AND(AU$1=1,OR($F2=2,$F2=3)),0,
1行目が1月ならF列の2月と3月は除外(0にする)、以降の式は無視

IF(AND(AU$1=2,$F2=3),0,
1行目が2月ならF列の3月は除外(0にする)、以降の式は無視

IF(COUNTIFS($C$2:$C2,$C2,$M$2:$M2,$M2,$Q$2:$Q2,$Q2)=1,1,0)))))

単にCOUNTIFS($C$2:$C2,$C2,$M$2:$M2,$M2,$Q$2:$Q2,$Q2)を各行に展開して実施すると、C列M列Q列を一纏めとして重複のあるデータの行に上から順にそれぞれ纏めたデータごとにカウントアップされた値が入ります。

値の「1」以外は重複したデータですので除外するために「0」にしています。

なお、現在OKWAVEでの回答は控えていますので以後回答ができないかもしれません。

お礼

大変お世話になっております。
質問にかみ砕いたご教示を頂きまして、誠に有難うございました。
丁寧な説明で良く理解致しました。
データを日付順に並べて置けば、ご教示頂いた重複判定の数式を用いて課題総数が正しく求められる事を確認しました。

kkkkkmさんには、マクロの自動実行など、自分には到底実現出来なかった事をご教示頂き、心より感謝を申し上げます。
お陰様で、4月より今現在まで仕事を続ける事が出来ております。本当に命拾いをしました。
かみ砕いて、何時もお優しくご教授下さり本当に有難うございました。