[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『色付けセルの比較』（こと）
お世話になります。
シフト予定と実績を比較し、予定退勤時間より早く退勤しているもの以外の 全ての差異の有無を表示させたいと思っております。
A B C D E F G H・・・
1 9：00 9：15 9：30 9：45 10：00 10：15・・・
2
3 Aさん 予定 -------------------------・・・
4 実績 -------------------------------------・・・
5 Bさん 予定 -------------------------・・・
6 実績 -------------------------・・・
7
勤怠ソフトより上記のようなexcelがダウンロードできます。 予定と実績は色付で、出勤時間から退勤時間まで帯が続いています。
前述したように、予定退勤時間より実績退勤時間が早いものはOKとし それ以外の差異があるものの有無のみどこかのセルで印が出るような 数式がないものかと思い質問させて頂きました。
1sheetに5名程度のスタッフの予定・実績が表示されているexcelデータです。 日別でsheetが分かれており、現在目視で差異がないか確認をしています。 また、それが20店舗ほどあります。 差異の有無さえ、どこかに表示されるようになればそのsheetはとばせるので 時間が大幅に短縮となります。
また、可能であれば有無に加えて差異の場所に何か表示が出るとさらに 効率が上がります。
かなり難しいのではないかと思うのですが、有無の表示だけでもご存じの方 がいらっしゃいましたら、ご教示頂けますと幸いです。
よろしくお願い致します。
＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞
その勤怠システムは、当然、そういった数値情報をもっているわけですから、一番いいのは、 目で見るための、このレイアウトではなく、目的の判定をして目的のレイアウトで落とし込む機能を 追加してもらうということでしょうけど、それはシステムによって追加不可能かもしれませんし、 できたとしても有償？
なんとなく、勤怠システムですから、そういった機能（少なくとも、数値データとして出力する機能）は、すでに、ありそうに思うのですが？ ないのですかね？
で、ＶＢＡでもいいのですか？ それと、たとえば予定セルが赤で実績セルが青だったとして、差異のあったセルに【何か表示】というのは 具体的には、どんな表示にしたいのですか？
（β） 2015/10/06(火) 14:02
元データを触れない我々が、ご提示の情報だけで実現する場合、マクロを利用して1セルずつ色を調べることで最終時間を調べ、
これを比較して、セルに文字を入れる等のコーディングをしなければなりません。
（???） 2015/10/06(火) 16:47
コメントありがとうございました。 おっしゃる通り、目視用のレイアウトに変更することや、差異があるものが一目瞭然となる 機能の追加等、システム会社へ依頼をすることは可能ですが、かなり高額なシステム改修料金が 発生するため目視で確認を続けている状況です・・・
また、システムよりダウンロードしているので、元データは勤怠ソフト画面上で表示 されるだけとなります。塗りの状態でデータが落ちてくる仕様のようでそれを数値や 印へ変更することはやはり有償対応となるそうです・・・
確認致しましたが、数値データとして出力する方法は、やはり見当たりません。
VBAは使用した事がありません。 言葉の意味を検索したところ、マクロコードの作成とありました。 以前こちらで質問させて頂いた際、マクロといわれる英語の文章をここに貼り付けるという事まで ご教示頂いて実行したことはあります。 随分昔の事で前職にて使用していたので、データも現在ありません。
関数を使うくらいしか出来ない私でも、チャレンジする事が出来るようなものであれば スキルアップのためにも是非やってみたいと思います。
また、予定セルも実績セルも同色のグレーで色付けされたデータです。 例えば、上段の予定セルの色付けと比べて、下段の実績セルに色付けが無い場合は、 下段の実績セルに×印が入力される。 または、他色で色付けされる。など、目視で差異の有無がパッとわかるようなものになると とても効率が上がります。
前回の表でお伝えすると、Aさんの実績9：00〜9：30までのセルに×が入力されるなど といったイメージを勝手にしております。 (セル番地ですと、D3とD4に×が入力)
VBAというものが、データ単位もしくはsheetごとで設定するものなのかにもよると思うのですが システムとしては、各店を選択しダウンロードを行うと日別の予実データがsheetごとに 作成される仕様です。
1店舗で31日ある月は31sheetあり、そのエクセルデータが20店舗分なので20コ出来上がります。 20エクセルデータ、各データ31sheetへそのVBA設定を行うというのは、どのくらいの時間を 要するものでしょうか？
初心者で大変恐縮ですが、返答頂けると幸いです。 よろしくお願い致します。 （こと） 2015/10/06(火) 16:59
事情は了解です。 Excel4マクロを利用した関数ベースの処理もできるかもしれませんが、かえって大変そうですので 普通のＶＢＡのコードのほうが早いと思いますし、操作も楽だと思います。
その前に、仕様ですが、アップされた例、縦軸が A,B,C,D・・・横軸が 1,2,3,4,・・・となっていますけど 縦は 1,2,3,4,・・・、横は A,B,C,D・・・ ですよね。
で、構成ですけど、
１．あるフォルダをつくる。 ２．いずれ、こちらから回答案を作成してできあがるマクロブックを、そのフォルダにいれる。 ３．勤怠システムからダウンロードされた店舗別のエクセルブックも、すべて、そのフォルダにいれておく。 ４．マクロを動かすと、そのフォルダにあるマクロブック以外（つまり、各店舗のブック）の全シートに 判定処理を行い、追記したものを、上書き保存。
こんなイメージになりますがいかがですか？
（β） 2015/10/06(火) 17:10
対象は1ファイル毎にシート複数ですね。対象ファイルは閉じた状態で、別のブックに以下のマクロを記述。名前を付けて保存しておいてから、実行してみてください。
（Const cPATH = "c:\tmp\" の箇所は、実際に合わせて変えてください）
Sub test()
Const cPATH = "c:\tmp\"
Dim i As Long
Dim j As Long
Dim k As Long
Dim kMax As Long
Dim iSt As Long
Dim iEd As Long
Dim cFile As String
cFile = Dir(cPATH & "*.xlsx")
While cFile <> ""
If ActiveWorkbook.Name <> cFile Then
With Workbooks.Open(cFile)
For i = 1 To .Sheets.Count
With .Sheets(i)
If .Range("B3") = "予定" Then
kMax = .Cells(1, .Columns.Count).End(xlToLeft).Column
For j = 3 To .Cells(.Rows.Count, "B").End(xlUp).Row Step 2
iSt = 0
iEd = 0
For k = 3 To kMax
If .Cells(j + 1, k).Interior.Pattern <> xlNone Then
iSt = k
Exit For
End If
Next k
For k = kMax To 3 Step -1
If .Cells(j + 1, k).Interior.Pattern <> xlNone Then
iEd = k
Exit For
End If
Next k
For k = iSt To iEd
If .Cells(j, k).Interior.Pattern = xlNone Then
.Cells(j + 1, k).Value = "×"
End If
Next k
Next j
End If
End With
Next i
.Save
.Close
End With
End If
cFile = Dir
Wend
End Sub
（???） 2015/10/06(火) 17:40
（17:55 1店舗1ファイルのようなので、訂正）
一応書きましたのでコードをアップしておきます。 デスクトップ上に "Test" という名前のフォルダをつくり、以下のマクロを書いたマクロブックを保存。 処理すべき、すべての店舗ブックも、このフォルダにいれた上で、マクロ実行。
予定欄に色があり実績欄に色がないものにつき、予定欄に "X" マーク。 また、１つでも色がついた個人の名前（Ａ列）は赤色で塗りつぶします。
動かしてみて思ったんですが、予定があるのに働かなかったというチェックはできますが 予定がないのに働いた（早出とか残業とか）ことに対しては、まったく評価しない、【冷たい？】チェックシステムですね。
Sub Sample()
Dim allR As Range
Dim lstR As Range
Dim fPath As String
Dim fName As String
Dim wb As Workbook
Dim sh As Worksheet
Dim i As Long
Dim j As Long
Dim ng As Boolean
Application.ScreenUpdating = False '処理中の画面の動きを隠す
fPath = ThisWorkbook.Path & "\" 'マクロブックと同じフォルダ
fName = Dir(fPath & "*.xlsx") 'フォルダ内の xlsx ブックを抽出
Do While fName <> "" '抽出完了すると 空白 が返る
Set wb = Workbooks.Open(fPath & fName) 'ブックを開く
For Each sh In wb.Worksheets 'ブック内のシートを順番に取り出す
Set allR = sh.Range("A1", sh.UsedRange) '当該シートの全データ領域
Set lstR = allR.Offset(2, 2).Resize(allR.Rows.Count - 2, allR.Columns.Count - 2) '予定実績データ領域
lstR.ClearContents '前回処理の文字列を消去
allR.Columns("A").Interior.ColorIndex = xlNone '前回処理のワーニング消去
For i = lstR.Row To lstR.Row + lstR.Rows.Count - 1 Step 2 '個人ごと2行単位で処理
ng = False 'NGステータスをクリア
For j = lstR.Column To lstR.Column + lstR.Columns.Count - 1 '各時間帯を処理
If sh.Cells(i, j).Interior.ColorIndex <> xlNone Then '予定に色があり
If sh.Cells(i + 1, j).Interior.ColorIndex = xlNone Then '実績に色がなければ
sh.Cells(i, j).Value = "X" '予定欄に "X"
ng = True 'NGステータス
End If
End If
Next
If ng Then sh.Cells(i, "A").Interior.Color = vbRed '予定時間帯に実績がなかったら名前を赤色
Next
Next
wb.Close True 'ブックを保存して閉じる
fName = Dir() '次のブックを取得
Loop
MsgBox "処理が完了しました"
End Sub
（β） 2015/10/06(火) 18:18
βさん、???さん、コメントありがとうございます。 まず、最初に例表の縦横が間違っておりましたね・・・！修正しました。 失礼致しました。
説明が不足しており、申し訳ありません！ 予定と実績が異なる場合は、すべて×印が表示される形がベストです。 予定が無く、実績がある場合も×が必要です。 （どちらの帯に×が表示されるかはどちらでも結構です。）
そちらのデータで予実確認を行い、予定及び実績が一致しない場合は 早出、遅刻、シフト変更、打刻間違いなどが考えられます。 それらを確認し、勤怠を正しいシフト及びデータへ修正していくための作業です。
一致しなくてもスルーする差異は、１つだけです。 ・予定退勤時間より、早く退勤した場合 （閉店作業がを多く見積もってシフト入力がされているため、早い退勤は頻繁にある）
手順をご教示頂きました件で、確認です。
１．あるフォルダをつくる。 ⇒承知しました。 ２．いずれ、こちらから回答案を作成してできあがるマクロブックを、そのフォルダにいれる。 ⇒マクロブックを入れる。という作業が「？」ですが、マクロブック作成方法は調べてみます。 ３．勤怠システムからダウンロードされた店舗別のエクセルブックも、すべて、そのフォルダにいれておく。 ⇒承知しました。 ４．マクロを動かすと、そのフォルダにあるマクロブック以外（つまり、各店舗のブック）の全シートに 判定処理を行い、追記したものを、上書き保存。 ⇒「マクロを動かす」「判定処理を行う」「追記したものを」というのも、マクロブック作成方法を調べるとに理解したいと思います。
尚、英字横の日本語は説明文を入れて頂いているので、そこはマクロ貼り付けの際、コピー対象外ですよね？ 本当に、無知で申し訳なく思います。
対象は1ファイル毎にシート複数ですね。 ⇒おっしゃる通りです。 対象ファイルは閉じた状態で、別のブックに以下のマクロを記述。 ⇒記述方法から調べます。 名前を付けて保存しておいてから、実行してみてください。 ⇒保存は通常通り行い、実行方法を調べます。 （Const cPATH = "c:\tmp\" の箇所は、実際に合わせて変えてください） ⇒実際のフォルダ保存先に変更ということですよね？
ご丁寧にご教示頂き、感謝しております。 よろしければ、引き続きご指導頂けると大変幸いです。 よろしくお願い致します。
（こと） 2015/10/06(火) 20:33
>予定退勤時間より、早く退勤した場合
この条件、わかりそうでわからないので、以下の場合、どうなりたいか、担当ごとにこたえてください。
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U]
[1] | | |9:00|9:15|9:30|9:45|10:00|10:15|10:30|10:45|11:00|11:15|11:30|11:45|12:00|12:15|12:30|12:45|13:00|13:15|13:30
[2] | | | | | | | | | | | | | | | | | | | | |
[3] |Aさん|予定| | | | |色 |色 |色 |色 |色 |色 |色 |色 |色 | | | | | |
[4] | |実績| |色 |色 |色 |色 |色 | | | | | | | | | | | | |
[5] |Bさん|予定| | | | |色 |色 |色 |色 |色 |色 |色 |色 |色 | | | | | |
[6] | |実績| | | | | | | |色 |色 |色 |色 |色 |色 |色 |色 |色 | | |
[7] |Cさん|予定| | | | |色 |色 |色 |色 |色 |色 |色 |色 |色 | | | | | |
[8] | |実績| | | | | | |色 |色 |色 |色 | | | | | | | | |
[9] |Dさん|予定| | | | |色 |色 |色 |色 |色 |色 |色 |色 |色 | | | | | |
[10]| |実績| | |色 |色 |色 |色 |色 |色 |色 |色 |色 |色 |色 |色 |色 | | | |
[11]|Eさん|予定| | | | |色 |色 |色 |色 |色 |色 |色 |色 |色 | | | | | |
[12]| |実績|色 |色 |色 | | | | | | | | | | | | | | | |
[13]|Fさん|予定| | | | |色 |色 |色 |色 |色 |色 |色 |色 |色 | | | | | |
[14]| |実績| | | | | | | | | | | | | | |色 |色 |色 |色 |
[15]|Gさん|予定| | | | |色 |色 |色 |色 |色 |色 |色 |色 |色 | | | | | |
[16]| |実績| | | | | | | | | | | | | | | | | | |
（β） 2015/10/06(火) 22:09
βさん、詳細な表を作成頂きありがとうございます。 私の説明が上手くいかず、申し訳ありません。
Aさん・・・D3,E3,F3＝× Bさん・・・P5,Q5,R5,G6,H6,I6＝× Cさん・・・G8,H8＝× Dさん・・・E9,F9,P9,Q9＝× Eさん・・・C11,D11,E11,G12,H12,I12,J12,K12,L12,M12,N12,O12＝× Fさん・・・Q13,R13,S13,T13,G14,H14,I14,J14,K14,L14,M14,N14,O14＝× Gさん・・・G16,H16,I16,J16,K16,L16,M16,N16,O16＝×
一度、希望通りに記させて頂きました。 さらに、ワガママを言わせて頂けるなら予定より実績の退勤時間が早い場合×は不要 に条件として、30分以内（2セル分以内）の場合のみとしたいです。 （閉店作業がを多く見積もってシフト入力がされておりますが、 30分以上閉店作業がシフトと比較し短縮することはありえないため）
頂いた表を見ながらワガママ放題×がほしいところを探す作業をしながら、 目視作業を細かく思い起こすと、上段と下段が一致していないセル全てに×印 と共に１つでも×がついた個人の名前（Ａ列）を赤色で塗りつぶされるように 出来れば、今の莫大な目視作業から比べると充分のように思えてきました。 そうであれば、もう少しシンプルな形で進められるものでしょうか？
ご丁寧にありがとうございます。 また、返答頂けると幸いです。
（こと） 2015/10/06(火) 23:42
>>ワガママを言わせて頂けるなら予定より実績の退勤時間が早い場合×は不要 >>に条件として、30分以内（2セル分以内）の場合のみとしたいです。
この例を具体的なサンプルとして例示いただけませんか。
今日は、ちょっと時間がとれないかもしれません。 (もし、時間がとれたら、コード案をアップします）
ところで、アップ済みのコードですけど、
>>また、１つでも色がついた個人の名前（Ａ列）は赤色で塗りつぶします。
このように書きましたけど、表現間違いで
>>また、１つでもマークがついた個人の名前（Ａ列）は赤色で塗りつぶします。
が正しいです。ですので、
>>１つでも×がついた個人の名前（Ａ列）を赤色で塗りつぶされるように
は、すでに対応済みです。
（β） 2015/10/07(水) 05:04
そちらで設定されたＸマークを検討しました。 わかりそうなところもありますし、なぜ？ というところもあります。
予定は、必ず連続してある（飛び飛びに予定が設定されることはない）という前提で、以下のケース、どうなるべきかと、そうなる理由（ルール）を 言葉で定義できませんか？
予定前に実績あり 予定中 実績開始前(実績がない) 予定中 実績開始〜実績終了(実績がある） 予定中 実績終了後(実績がない） 予定中 全く実績無い場合 予定後 実績あり
（β） 2015/10/07(水) 09:06
↑で、もし、判定を分ける必要があるなら
予定前に実績あり
このパターンも
予定前に実績あり、かつ予定開始前に実績終了 予定前に実績あり、予定中も実績が継続して、かつ予定終了前に実績終了 予定前に実績あり、予定中も実績が継続して、かつ予定終了後も実績継続
といった場合別に考えてください。（もし、判断をわける必要があれば）
予定後 実績あり も同様に。
（β） 2015/10/07(水) 09:45
いろいろ、要件整理をお願いしました。 是非、整理した上で説明をもらいたいと思いますが、アップされた説明だけから、思い切って想像をたくましくして。
マクロブックの作成方法や、実行方法について、わからないことがあれば質問してください。
Sub Sample2()
Dim allR As Range
Dim lstR As Range
Dim fPath As String
Dim fName As String
Dim wb As Workbook
Dim sh As Worksheet
Dim i As Long
Dim j As Long
Dim ng As Boolean
Dim actSt As Boolean
Application.ScreenUpdating = False '処理中の画面の動きを隠す
fPath = ThisWorkbook.Path & "\" 'マクロブックと同じフォルダ
fName = Dir(fPath & "*.xlsx") 'フォルダ内の xlsx ブックを抽出
Do While fName <> "" '抽出完了すると 空白 が返る
Set wb = Workbooks.Open(fPath & fName) 'ブックを開く
For Each sh In wb.Worksheets 'ブック内のシートを順番に取り出す
Set allR = sh.Range("A1", sh.UsedRange) '当該シートの全データ領域
Set lstR = allR.Offset(2, 2).Resize(allR.Rows.Count - 2, allR.Columns.Count - 2) '予定実績データ領域
lstR.ClearContents '前回処理の文字列を消去
allR.Columns("A").Interior.ColorIndex = xlNone '前回処理のワーニング消去
For i = lstR.Row To lstR.Row + lstR.Rows.Count - 1 Step 2 '個人ごと2行単位で処理
ng = False 'NGステータスをクリア
actSt = False
For j = lstR.Column To lstR.Column + lstR.Columns.Count - 1 '各時間帯を処理
If sh.Cells(i + 1, j).Interior.ColorIndex <> xlNone Then actSt = True '実績が現れた
If sh.Cells(i + 1, j).Interior.ColorIndex <> xlNone Then '実績に色あり
If sh.Cells(i, j).Interior.ColorIndex = xlNone Then '予定に色がなければ
sh.Cells(i, j).Value = "X" '予定欄に "X"
ng = True 'NGステータス
End If
ElseIf sh.Cells(i, j).Interior.ColorIndex <> xlNone Then '予定に色があり
If sh.Cells(i + 1, j).Interior.ColorIndex = xlNone Then '実績に色がなければ
If Not actSt Then '実績が現れる前
sh.Cells(i + 1, j).Value = "X" '実績欄に "X"
ng = True 'NGステータス
Else '実績が現れた後
If Cells(i, j + 2).Interior.ColorIndex <> xlNone Then '2マス先にも予定あれば
sh.Cells(i + 1, j).Value = "X" '実績欄に "X"
ng = True 'NGステータス
End If
End If
End If
End If
Next
If ng Then sh.Cells(i, "A").Interior.Color = vbRed '予定時間帯に実績がなかったら名前を赤色
Next
Next
wb.Close True 'ブックを保存して閉じる
fName = Dir() '次のブックを取得
Loop
MsgBox "処理が完了しました"
End Sub
（β） 2015/10/08(木) 10:24
マクロ準備方法については、すでに調べておられると思いますが参考までに以下。
１．まず、好きな場所にフォルダをつくってください。これはわかりますよね。 ２．エクセルに開発タブの表示が設定されていない場合は、以下のようなページを参考にして設定してください。
３．開発タブの左のほうにある Visual Basic をクリック。ＶＢＥ画面というものが現れます。 ４．挿入(I) --> 標準モジュール(M) 。からっぽの標準モジュールが現れます。 ５．その標準モジュールに、アップしたコードの Sub から End Sub までを、そのままコピペで貼り付け。 ６．ＶＢＥ画面の右上のＸボタンをクリックしてＶＢＥ画面を閉じてシートに戻ります。 ７．ファイルメニュー、名前を付けて保存で、ファイルの種類を Excel マクロ有効ブック(*.xlsm) を指定。ファイル名は任意のものを。 保存先は１．で準備したフォルダ。 ８．このフォルダに、システムからダウンロードしたエクセルブックも、すべていれておきます。 ９．７．で作成したマクロブックが開かれている状態で、開発タブの左のほうにある、マクロ をクリック。 １０．でてきた小窓のなかの Sample2 を選んで実行。（登録されたマクロが１つだけなら、すでに選ばれていると思います） １１．最初の実行時のみ、コンテンツを有効にするかどうかといったメッセージが出ると思いますので、有効にして実行。
なお、Sub Sample2() と記述してありますが、この Sample2 は、好きな（わかりやすい）名前に変えることができます。（たとえば 出勤状況チェック とか） また、マクロ実行の方法は、これ以外にもたくさんあります。 操作時、わかりやすいのは、シートにボタンを配置して、そこにマクロ登録しておいて、ボタンをクリックして実行。これがいいかもしれません。
（β） 2015/10/08(木) 10:44
Sub test2()
Const cPATH = "c:\tmp\"
Dim i As Long
Dim j As Long
Dim k As Long
Dim kMax As Long
Dim cFile As String
Dim iFlag As Long
cFile = Dir(cPATH & "*.xlsx")
While cFile <> ""
If ActiveWorkbook.Name <> cFile Then
With Workbooks.Open(cPATH & cFile)
For i = 1 To .Sheets.Count
With .Sheets(i)
If .Range("B3") = "予定" Then
kMax = .Cells(1, .Columns.Count).End(xlToLeft).Column
For j = 3 To .Cells(.Rows.Count, "B").End(xlUp).Row Step 2
iFlag = 0
For k = 3 To kMax
If .Cells(j + 0, k).Interior.Pattern <> xlNone And _
.Cells(j + 1, k).Interior.Pattern = xlNone And _
.Cells(j + 1, k - 2).Interior.Pattern = xlNone Then
.Cells(j + 1, k).Value = "×"
iFlag = 1
ElseIf .Cells(j + 0, k).Interior.Pattern = xlNone And _
.Cells(j + 1, k).Interior.Pattern <> xlNone Then
.Cells(j + 0, k).Value = "×"
iFlag = 2
End If
Next k
If 0 < iFlag Then
.Cells(j, "A").Font.Color = RGB(255, 0, 0)
End If
Next j
End If
End With
Next i
.Save
.Close
End With
End If
cFile = Dir
Wend
End Sub
（???） 2015/10/08(木) 16:01
βさん、諸々説明などありがとうございます。 とても親切に対応頂いて心から感謝しております。
こちらの都合で大変恐縮ですが、現在、他業務が立て込んでおり 勤怠管理の業務が放置となってしまっています。
再び取り組む事が出来るのが、10月下旬ころになります。 必ず、こちらでご教示頂いたVBAというものを使って、予実確認の大幅時間短縮を 実行したいと思っております。 取り急ぎ、お礼と状況報告と思い書き込みました。 よろしくお願い致します。
（こと） 2015/10/08(木) 17:02
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.