[[20110129232704]] 『日付、担当者、時間帯の重複チェック』(ケーヒル) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『日付、担当者、時間帯の重複チェック』(ケーヒル)

 下記のようなデータが30000列ほどあります。
 この中から、日付、担当者、時間帯(が重複するものを
 色を変える、エラーを示すなどの方法で抽出したいのですが、
 何か良い方法がありませんでしょうか。
 いろいろと考えてみたのですが、ちょっと難しく…。

 日付   担当者 開始時間 終了時間  開始日時      終了日時
  A    B    C     D          E        F
 1月21日	 田中	9:00	 9:18	2011/1/21 9:00	2011/1/21 9:18
 1月21日	 田中	9:20	 9:40	2011/1/21 9:20	2011/1/21 9:40
 1月21日	 佐藤	9:20	 9:40	2011/1/21 9:20	2011/1/21 9:40
 1月20日	 佐藤	9:00	 9:30	2011/1/20 9:00	2011/1/20 9:30
 1月20日	 佐藤	9:20	 9:40	2011/1/20 9:20	2011/1/20 9:40
 1月20日	 田中	9:39	10:00	2011/1/20 9:39	2011/1/20 10:00
 1月19日	 田中	9:00	 9:30	2011/1/19 9:00	2011/1/19 9:30
 1月19日	 佐藤	9:00	 9:30	2011/1/19 9:00	2011/1/19 9:30

何卒、ご教授いただけましたら幸いです。

Excel2007、WindowsXPです。


 ご提示のサンプルだと、どこに色がつけばいいんですか?

 いずれにしてもXL2007なら、条件付き書式に「重複」がありますので、
 それが活用できるように工夫するのが簡便でいいと思います。

 (半平太) 2011/01/31 10:48

半平太様 

ありがとうございます。

たとえば、

 日付   担当者 開始時間 終了時間  開始日時      終了日時

  A    B    C     D          E        F

 1月21日	 田中	9:00	 9:18	2011/1/21 9:00	2011/1/21 9:18
 
 1月21日	 田中	9:20	 9:40	2011/1/21 9:20	2011/1/21 9:40

 1月21日	 佐藤	9:20	 9:40	2011/1/21 9:20	2011/1/21 9:40

 1月20日	 佐藤	9:00	 9:30	2011/1/20 9:00	2011/1/20 9:30 ←重複(黄色)

 1月20日	 佐藤	9:20	 9:40	2011/1/20 9:20	2011/1/20 9:40 ←重複(黄色)

 1月20日	 田中	9:39	10:00	2011/1/20 9:39	2011/1/20 10:00

 1月19日	 田中	9:00	 9:30	2011/1/19 9:00	2011/1/19 9:30

 1月19日	 佐藤	9:00	 9:30	2011/1/19 9:00	2011/1/19 9:30

上記の場合は←の2行を重複と判定したいのです。

条件付き書式、試してみます。


 > 日付、担当者、時間帯(が重複するものを色を変える
  
 > 日付   担当者 開始時間 終了時間  開始日時      終了日時
 >  A    B    C     D          E        F
 > 1月20日 佐藤   9:00   9:30   2011/1/20 9:00 2011/1/20 9:30 ←重複(黄色)
 > 1月20日 佐藤   9:20   9:40   2011/1/20 9:20 2011/1/20 9:40 ←重複(黄色)

 上の例で、「時間帯が重複」するとの判定はどこで行うのですか?
 人によっては、重複していないと考えると思いますけど。。

 (半平太) 2011/01/31 11:11

 ↑
 すみません。分かりました。
 オーバーラップがあると云うことですね。

 これはちょっと厄介です。簡単には出来ません。
 回答は気長に待った方がいいです。

 ところで、そのデータは日付順になっていないのですか?
 (何の順番なんですか?)

 日付順 > 名前順 > 開始日時順 > 終了日時順 と云う風に並んでいれば、

 難度が下がると思うのですけど、そうはいかないのですか?

 (半平太) 2011/01/31 11:19

 後ろでマクロ案が提示されましたので、
 ここにあった私の中途半端な案は自己削除します。(半平太)

 条件付書式で、重複を表示させるものは、どうでしょう?

 B2を選択しB8まで範囲を拡大し、書式、条件付書式として条件1に数式が、その右に
 =SUM((B1=B$1:B$8)*(E1<F$1:F$8)*(F1>E$1:E$8))>1として、

 書式をクリックし、パターンで色を黄色に指定して、OK、OK、としますと、
 重複した行の担当者のセルが、黄色に着色するように思います。     (PENSIONER)

 PENSIONERさん
            このデータ量ですよ?
             ↓
 > 下記のようなデータが30000列ほどあります。

 (半平太) 2011/01/31 14:22

 ご質問の表がよくわからないのですが、30000列というのは、30,000行ではないのでしょう? (PENSIONER)

 興味本位ですが・・・
 重複判定が分単位限定でのVBA案を

  Sub test()
  Dim i As Long, j As Long
  Dim tbl As Variant
  Dim myTime() As String, buf As String
  Dim myC As New Collection
  Dim myR As Range
  tbl = Worksheets("Sheet1").Range("A1").CurrentRegion.Value
  With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(tbl)
      buf = Format(tbl(i, 1), "yyyymmdd") & vbTab & tbl(i, 2)
      If .Exists(buf) Then
        myTime = .Item(buf)
        For j = Int(tbl(i, 3) * 1440) To Int(tbl(i, 4) * 1440)
          If myTime(j) = "" Then
            myTime(j) = i
          Else
            If myTime(j) Like "*" & vbTab & "*" Then
              myC.Add Rows(i)
            Else
              myC.Add Rows(myTime(j))
              myC.Add Rows(i)
            End If
            myTime(j) = myTime(j) & vbTab & i
          End If
        Next j
        .Item(buf) = myTime
      Else
        ReDim myTime(1439)
        For j = Int(tbl(i, 3) * 1440) To Int(tbl(i, 4) * 1440)
          myTime(j) = i
        Next j
        .Add buf, myTime
      End If
    Next i
  End With
  For Each myR In myC
    myR.Interior.ColorIndex = 6
  Next myR
  End Sub

 1日分の1440分の配列に該当行を入れつつオーバーラップ判定してます。
 (momo)

 もしも、30,000行ということでしたら、特定の担当者別や日付の範囲に区切ってから、
 空いた列に数式で、重複をマークし、数式を同列に値貼り付けしてから、次の区切りの処理するなどの
 配慮が、必要なように思います。 その場合の数式例は、以下のようになります。
 =TEXT(SUMPRODUCT((B1=B$1:B$8)*(E1<F$1:F$8)*(F1>E$1:E$8)),"[>1]重複;;;")
                          (PENSIONER)


たくさんの返信を頂き、皆様ありがとうございます。

PENSIONERさんのご指摘の通り30000行でした。
SUMPRODUCTの関数の例は、過去ボードにて一度みつけ、
行ってみましたがデータ量が多すぎるせいかフリーズして
しまいまともに使えませんでした。

自分の理解が追い付いていないところもあるので、
記載を読み替えさせていただきながら試していきたいと思います。

(ケーヒル)


一度区切ってからの方法を試してみたいとおもいます。
VBA案もありがとうございました。
試してみたいと思います。

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.