[[20231121164348]]　『特定の値が重複する行(2行以上)の比較について』（新人）　ページの最後に飛ぶ

『特定の値が重複する行(2行以上)の比較について』（新人）

早速質問失礼します。
エクセルで、

A列に番号
B列に氏名
C列に日付(開始日)
D列に日付(終了日)

という表があります。

001　吉田　4/1　7/1
002　山田　4/7　6/3
003　佐藤　6/5　8/10
001　吉田　6/6　9/30
004　鈴木　6/15 9/30
005　高橋　6/21 6/21
006　小林　7/25 9/25
001　吉田　7/25 9/25
007　田中　8/1 9/30
001 吉田　8/1 9/30

というようなイメージです。

このとき、重複する氏名があれば(今回だと吉田の計4行)、４行すべての開始日と終了日を比較し、開始日の一番早い日から一番遅い終了日が半年(6ヵ月)を超えていないか判定し、その結果をE列の表示させたいと思っています。

別シートに検索用シートを作り、001などと番号を打って吉田の４行をすべて表示させる方法も一案かと思ったのですが、できれば別シートに番号を打つなどの手作業は発生させないで、数式等で自動判定させたいです。

何かご助言いただけないでしょうか。
状況についてうまく説明できていなければ申し訳ありません。
よろしくお願いいたします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

表のお手伝い

     |[A] |[B] |[C]   |[D]   
 [1] |番号|氏名|開始日|終了日
 [2] |   1|吉田|4/1   |7/1   
 [3] |   2|山田|4/7   |6/3   
 [4] |   3|佐藤|6/5   |8/10  
 [5] |   1|吉田|6/6   |9/30  
 [6] |   4|鈴木|6/15  |9/30  
 [7] |   5|高橋|6/21  |6/21  
 [8] |   6|小林|7/25  |9/25  
 [9] |   1|吉田|7/25  |9/25  
 [10]|   7|田中|8/1   |9/30  
 [11]|   1|吉田|8/1   |9/30

（IT） 2023/11/21(火) 19:33:39

 6ヵ月ジャストの判定ができないと処理できないのですが、
 適当な判定でいいんでしょうか？

 厳密な判定が必要な場合、人や会社でそれぞれ基準が違いますので、
 以下の開始日に対する6カ月後の日(正解)を教えてください。

  開始日      6ヶ月後の候補日
  3/15        9/14、9/15
  1/1       　6/30、7/1
  2/28(末)    8/27、8/28、8/31(末)

（半平太） 2023/11/21(火) 19:44:04

    Sub main()
    Dim c As Range, k As Variant, S_date As Object, E_date As Object
    Set S_date = CreateObject("Scripting.Dictionary")
    Set E_date = CreateObject("Scripting.Dictionary")
    For Each c In Range("B:B").SpecialCells(2)
        If S_date(c.Value) = "" Then
            Set S_date(c.Value) = c.Offset(, 1)
            Set E_date(c.Value) = c.Offset(, 2)
        Else
            Set S_date(c.Value) = Union(c.Offset(, 1), S_date(c.Value))
            Set E_date(c.Value) = Union(c.Offset(, 2), E_date(c.Value))
        End If
    Next c
    For Each k In S_date
        If WorksheetFunction.EDate(WorksheetFunction.Min(S_date(k)), 6) < WorksheetFunction.Max(E_date(k)) Then
            E_date(k).Offset(, 1).Value = "6月超え"
        Else
            E_date(k).Offset(, 1).Value = ""
        End If
    Next k
    End Sub

（mm） 2023/11/22(水) 10:03:42

IT様、半平太様

レスありがとうございます。

開始日からの６ヵ月の基準について
3/15の場合は、9/14までが6ヵ月の範囲内(15日以降は6ヵ月超)
1/1の場合は、6/30までが6ヵ月の範囲内(7/1日以降は6ヵ月超)
2/28の場合は、8/27までが6ヵ月の範囲内(8/28日以降は6ヵ月超)
という考え方になります。

mm様

マクロコードありがとうございます。
すみませんマクロ勉強不足のため即レスができず申し訳ございません。
いただいたものをコピーして使わせていただきます。

（新人） 2023/11/22(水) 11:50:56

 その考え方だと「吉田」は「4月1日 ～9月30日」なので「6ヵ月超」にならないのでは？

（まる2021） 2023/11/22(水) 12:38:34

 ＞いただいたものをコピーして使わせていただきます。

 ふーむ、結局、厳密な判定は必要ないってことですか。。

（半平太） 2023/11/22(水) 12:42:42

まる2021様、半平太様

レスありがとうございます。
おっしゃる通りです、最初の書き方を誤りました申し訳ありません。
質問をリセットさせていただいてもよろしいでしょうか。
もう少し詳しく書かせていただきます。用途等は何卒お察しくださいませ。

今回の表から知りたい情報が、
・開始日の一番早い日から一番遅い終了日が半年(6ヵ月)以内であるかどうか
・もし6ヵ月を超えていた場合、開始日の一番早い日から一番遅い終了日までの間に１か月間の空白期間があるか
という２つを知りたいです。最初の質問と変わってしまうことお許しください。

表は例として11列ほどにとどめていますが、実際のデータは600件近くあり重複者も大勢いるような状況です。(1人につき最大10件ほど重複することもあります)
これまではフィルター等で重複列のみ表示させ、期間被りがないか1か月の空白があるか人間の目でチェックをしていたようなのですが、見落としもあるでしょうし、何とかして改善できないものかと悩んでおりました。

すみません長くなってしまいましたが、皆様のお力をお貸しいただきたく、よろしくお願いいたします。

（新人） 2023/11/22(水) 13:22:54

＞・もし6ヵ月を超えていた場合、開始日の一番早い日から一番遅い終了日までの間に１か月間の空白期間があるか

後出しがひどいけど、これも具体的な例を挙げないと回答する側と認識がズレるでしょうが。

（外野から） 2023/11/22(水) 13:55:07

 ＞・もし6ヵ月を超えていた場合、開始日の一番早い日から一番遅い終了日までの間に１か月間の空白期間があるか
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　~~~~~~~~~~~~~~~~~~			
 同じく…これでは意味が良くわかりません

     |[A] |[B] |[C]      |[D]      |[E]    |[F]    			
 [1] |番号|氏名|開始日   |終了日   |180日超|6ヶ月超			
 [2] |仮1 |吉田|2023/1/1 |2023/5/20|超過   |超過   			
 [3] |仮2 |斉藤|2023/4/5 |2023/9/19|       |       			
 [4] |仮3 |仲田|2023/3/15|2023/9/8 |超過   |       			
 [5] |仮4 |吉田|2023/4/4 |2023/5/18|超過   |超過   			
 [6] |仮5 |菊池|2023/4/30|2023/9/17|       |       			
 [7] |仮6 |森  |2023/2/28|2023/7/1 |超過   |超過   			
 [8] |仮7 |吉田|2023/4/16|2023/7/1 |超過   |超過   			
 [9] |仮8 |仲田|2023/4/17|2023/9/12|超過   |       			
 [10]|仮9 |森  |2023/5/18|2023/8/28|超過   |超過

 ちょっと試しにしてみました。			
 E2の式=IF(AND(IFERROR(DATEDIF(IF(COUNTIF($B:$B,$B2)>1,MINIFS($C:$C,$B:$B,$B2),""),IF(COUNTIF($B:$B,$B2)>1,MAXIFS($D:$D,$B:$B,$B2),""),"D"),"")<>"",IFERROR(DATEDIF(IF(COUNTIF($B:$B,$B2)>1,MINIFS($C:$C,$B:$B,$B2),""),IF(COUNTIF($B:$B,$B2)>1,MAXIFS($D:$D,$B:$B,$B2),""),"D"),"")>=181),"超過","")		
 F2の式=IF(AND(IFERROR(DATEDIF(IF(COUNTIF($B:$B,$B2)>1,MINIFS($C:$C,$B:$B,$B2),""),IF(COUNTIF($B:$B,$B2)>1,MAXIFS($D:$D,$B:$B,$B2),""),"M"),"")<>"",IFERROR(DATEDIF(IF(COUNTIF($B:$B,$B2)>1,MINIFS($C:$C,$B:$B,$B2),""),IF(COUNTIF($B:$B,$B2)>1,MAXIFS($D:$D,$B:$B,$B2),""),"M"),"")>=6),"超過","")

 下にコピー
（あみな） 2023/11/22(水) 14:11:10

閲覧してくださった皆様

大変申し訳ございません。ご指摘真摯に受け止めます。

１か月の空白期間判定のことも加えて再度状況を書かせていただきます。
ITさんの表をお借りして

     |[A] |[B] |[C]   |[D]   
 [1] |番号|氏名|開始日|終了日
 [2] |   1|吉田|4/1   |7/1   
 [3] |   2|山田|4/7   |6/3   
 [4] |   3|佐藤|6/5   |8/10  
 [5] |   1|吉田|6/6   |9/30  
 [6] |   4|鈴木|6/15  |9/30  
 [7] |   5|高橋|6/21  |6/21  
 [8] |   6|小林|7/25  |9/25  
 [9] |   1|吉田|7/25  |9/25  
 [10]|   7|田中|8/1   |9/30  
 [11]|   1|吉田|8/1   |9/30

この表の状況であれば、吉田の一番早い開始日が4/1、一番遅い終了日が9/30なので、吉田のE列(E2,E5,E9,E11)には期間超過無という判定結果を表示させる。

     |[A] |[B] |[C]   |[D]   
 [1] |番号|氏名|開始日|終了日
 [2] |   1|吉田|4/1   |7/1   
 [3] |   2|山田|4/7   |6/3   
 [4] |   3|佐藤|6/5   |8/10  
 [5] |   1|吉田|6/6   |9/30  
 [6] |   4|鈴木|6/15  |9/30  
 [7] |   5|高橋|6/21  |6/21  
 [8] |   6|小林|7/25  |9/25  
 [9] |   1|吉田|7/25  |9/25  
 [10]|   7|田中|8/1   |9/30  
 [11]|   1|吉田|8/1   |9/30 
 [12]|   1|吉田|9/15  |10/15

となったときに、12行目の吉田は6ヵ月を超過しているのでE12セルに期限超過という判定結果を表示させる。

     |[A] |[B] |[C]   |[D]   
 [1] |番号|氏名|開始日|終了日
 [2] |   1|吉田|4/1   |7/1   
 [3] |   2|山田|4/7   |6/3   
 [4] |   3|佐藤|6/5   |8/10  
 [5] |   1|吉田|6/6   |9/30  
 [6] |   4|鈴木|6/15  |9/30  
 [7] |   5|高橋|6/21  |6/21  
 [8] |   6|小林|7/25  |9/25  
 [9] |   1|吉田|7/25  |9/25  
 [10]|   7|田中|8/1   |9/30  
 [11]|   1|吉田|8/1   |9/30 
 [12]|   1|吉田|11/20 |12/31

となったときは、吉田の一番早い開始日は4/1、一番遅い終了日は12/31で、6ヵ月は超過しているのでE12セルに期限超過という判定結果を表示させる。
ただし、この吉田は10/1～11/19の期間は開始・終了とも該当しないお休み期間になり、その空白期間が1か月であればF12セルに1か月空白ありと表示させたい。

というイメージです。
この1か月の空白期間というのは、今回の吉田の場合、4/1が最も早い日、9/30が6ヵ月範囲内の最終日となるので、10/1～11/1までが開始・終了期間に含まれていなければOKとしたいです。

状況をより複雑にさせてしまい本当に申し訳ありません。
また不足情報あれば何卒ご指摘いただければ幸いです。

（新人） 2023/11/22(水) 15:54:36

＞・・・・OKとしたいです。

うーん。何なんOKって。ちゃんと書いたら。紛らわしい。

（新人イジメ） 2023/11/22(水) 16:14:01

1か月の空白期間を判定するにあたり、1か月をいつからいつまでとするのかというと、今回の吉田の場合、4/1が最も早い日、9/30が6ヵ月範囲内の最終日となるので、10/1～11/1までが開始・終了期間に含まれていなければ、「1か月の空白あり」と判定できるような式を作りたいです。
（新人） 2023/11/22(水) 16:22:46

※2016用に修正しました。11/22 19:35

 こういう事？
 12，13行目の「1か月の空白あり」判定をどうするのか、が自信ないです。
 式が長くなるのでE列に作業列を設けました。

      __A_  __B_  __C_____  __D_____  __E_______  __F_____  __G________
  1   番号  氏名  開始日    終了日    開始日最小  半年超え  1ケ月空白有
  2   001   吉田    4月1日   11月8日      4月1日  ●                   
  3   002   山田    4月7日    6月3日      4月7日                       
  4   003   佐藤    6月5日   8月10日      6月5日                       
  5   001   吉田    6月6日   9月30日      4月1日                       
  6   004   鈴木   6月15日   9月30日     6月15日                       
  7   005   高橋   6月21日   6月21日     6月21日                       
  8   006   小林   7月25日   9月25日     7月25日                       
  9   001   吉田   7月25日   9月25日      4月1日                       
 10   007   田中    8月1日   9月30日      8月1日                       
 11   001   吉田    8月1日   9月30日      4月1日                       
 12   001   吉田  10月31日  12月31日      4月1日  ●                   
 13   001   吉田   11月1日  12月31日      4月1日  ●        ●

 [E2]  =AGGREGATE(15,6,$C$2:$C$1000/($A$2:$A$1000=A2),1)　↓コピー
 [F2]  =IF(DATEDIF(E2,D2,"M")<6,"","●")　↓コピー
 [G2]  =IF(F2="●",IF(MAX(0,MIN(D2,EDATE(EDATE(E2,6),1))-MAX(C2,EDATE(E2,6))),"","●"),"")　↓コピー

（まる2021） 2023/11/22(水) 18:05:36

まる2021さん

レスありがとうございます。
6ヵ月超え判定については数式理解できました。
自分でもいろいろ調べてみて、配列数式を使って解決できそうです。
ありがとうございます。

そして1ヵ月の判定についていろいろと考えているのですが未だ苦戦中です。
原因などもう少し勉強します。

遅くなりましたが数式をご提示いただきましたことに改めて御礼申し上げます。

（新人） 2023/11/24(金) 17:03:05

 +1するのを、忘れてました。
 [G2]  =IF(F2="●",IF(MAX(0,MIN(D2,EDATE(EDATE(E2,6),1))-MAX(C2,EDATE(E2,6))+1),"","●"),"")

 最初、在職期間の中で休職期間が「1ケ月有/無」を調査したいのかと思いましたが、
 いまいち、何がしたいのか分らないので、一般論を書いて、これにて失礼します。

 任意の「開始日～終了日」と「10/1（10/1含む）～11/1（11/1含む）」のラップ日数は以下で求まります。
 両端の日を「含める/含めない」（開区間、閉区間、半開区間)は、仕様に応じて決定します。
 最初にMax(0,）にしているのは、ラップ期間がない場合、負数が返るので、それを0にするためです。
 なので、この値が「0」なら「ラップ期間なし」=「1か月の空白あり」と判定できます。

=MAX(0,MIN(終了日,"11/1")-MAX(開始日,"10/1")+1)

 以下の、表で確認してみてください。

     __A_  __B____  __C_____  __D_______________________________  __E________________________
 1   氏名  開始日   終了日    10/1(含む)～11/1(含む)のラップ日数                             
 2   吉田   4月1日   9月30日                                   0  ★「0」なのでラップ期間なし
 3   吉田   4月1日   10月1日                                   1                             
 4   吉田   4月1日   10月2日                                   2                             
 5   吉田  9月30日  10月31日                                  31                             
 6   吉田  9月30日   11月1日                                  32                             
 7   吉田  9月30日   11月2日                                  32

 [D2]  =MAX(0,MIN(C2,"11/1")-MAX(B2,"10/1")+1)  ↓コピー

（まる2021） 2023/11/24(金) 19:14:43

すいません通りすがりでが、ここに入力する方法が知りたいです。
まる2021さんがきれいに列が整った表を書かれていますが、それはどのように入力されているのですか？

ここのサイトの「初めての方へ」のリンク先に

*****************************************************************

(5) Wikiに書き込む場合の整形ルール
通常は入力した文字がそのまま表示されますが、以下のルールに従ってテキスト整形を行うことができます
空行から空行までがひとつの段落として整形され途中の改行は無視されます
行頭を半角スペースで始めると書き込みの改行位置が反映されます
HTMLのタグは書けません

http://www.hyuki.com/ のようなURLは自動的にリンクになります

*****************************************************************

とあり、これが関係あるのかなとと思うんですが、言っている意味がよくわからなくて。
もし、違うようであればどのようにしたら列が整った書き込み方ができるのでしょう？
（しっち） 2023/11/24(金) 22:41:42

コメント返信：

[ 一覧(最新更新順) ]

[[20231121164348]] 『特定の値が重複する行(2行以上)の比較について』（新人） ページの最後に飛ぶ

*****************************************************************

*****************************************************************

[[20231121164348]]　『特定の値が重複する行(2行以上)の比較について』（新人）　ページの最後に飛ぶ