[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日付、担当者、時間帯の重複チェック』(ケーヒル)
下記のようなデータが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の関数の例は、過去ボードにて一度みつけ、
行ってみましたがデータ量が多すぎるせいかフリーズして
しまいまともに使えませんでした。
自分の理解が追い付いていないところもあるので、
記載を読み替えさせていただきながら試していきたいと思います。
(ケーヒル)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.