- 締切済み
【excel】2つの表を比較し、変更点を知るには？
excelについての質問です。初心者です。 ・毎月、社員の欠勤日数をまとめたファイルが作成されます。 （例「4月度欠勤.xls」） ・内容は、下記です。 所属部署名／社員ナンバー／社員名／欠勤日数 ・毎月送られてくるものを、別ファイルにまとめて集計したいのです。 （例「欠勤日数一覧表.xls」） 【現状】 ・まずは「4月度欠勤.xls」を元に、「欠勤日数一覧表.xls」を作成しました。 ・「5月度欠勤.xls」が届いた段階で、「欠勤日数一覧表.xls」との相違を目視し、異動等で名前が無くなっていれば削除、名前が増えていればコピーして挿入、という形でやり過ごしています。 ・人数が多く、vlookup関数等を使って簡単かつ的確に処理できるよう指示されたのですが、やり方が全くわからない状況です。 「4月度欠勤.xls」と「5月度欠勤.xls」という様なデータを比較し、直接「欠勤日数一覧表.xls」に反映することはできなくても、変更箇所にエラーが出る、「FALSE」等何かしらの結果が返るなど、最終的に変更点がわかるような方法を教えて頂けないでしょうか？
- みんなの回答 （3）
- 専門家の回答
みんなの回答
- mitarashi
- ベストアンサー率59% (574/965)
A No.2です。ご覧になっているかどうかわかりませんが、単純に変更点が分かる様にする方法も回答いたします。個人的にはまっている、dictionaryシリーズです。複数（列が連続している事が前提）の項目の組み合わせに対応できる様なコードですが、社員ナンバーまたは名前だけの比較でよければ、該当する一列だけ指定すればＯＫです。両ブックのデータで、重複している場合、そのセルに色を付けるというものです。ブックの名前、シートの名前等は環境に合わせて変更して下さい。 'ブック間の比較 両方のブックは開いている事が前提 '各データはA1から入っていて、1行目は見出し行。何列の組み合わせをキーとするかは、 'コード内で記述 Set targetRange = Workbooks("check1.xls").Sheets("Sheet1").Range("a1"). _ ' CurrentRegion.Columns("a:b") これは先頭から2列の例 Sub test() Dim targetRange As Range Dim targetRow As Range Dim keyString As String Dim i As Long Dim myDic As Object Dim oneLine() As String Set myDic = CreateObject("Scripting.Dictionary") Set targetRange = Workbooks("check1.xls").Sheets("Sheet1").Range("a1").CurrentRegion.Columns("a:b") Set targetRange = targetRange.Offset(1, 0).Resize(targetRange.Rows.Count - 1, targetRange.Columns.Count) ReDim oneLine(1 To targetRange.Columns.Count) '一方の値をdictionaryに取り込み For Each targetRow In targetRange.Rows For i = 1 To UBound(oneLine) oneLine(i) = targetRow.Cells(i).Text Next i keyString = Join(oneLine, "☆") If Not myDic.exists(keyString) Then myDic.Add keyString, targetRow End If Next Set targetRange = Workbooks("check2.xls").Sheets("Sheet1").Range("a1").CurrentRegion.Columns("a:b") Set targetRange = targetRange.Offset(1, 0).Resize(targetRange.Rows.Count - 1, targetRange.Columns.Count) '他方の値(をdictionaryに納めた値と)と比較 For Each targetRow In targetRange.Rows For i = 1 To UBound(oneLine) oneLine(i) = targetRow.Cells(i).Text Next i keyString = Join(oneLine, "☆") If myDic.exists(keyString) Then myDic(keyString).Interior.ColorIndex = 6 '一致範囲を着色 targetRow.Interior.ColorIndex = 6 End If Next End Sub
- mitarashi
- ベストアンサー率59% (574/965)
社員の累積欠勤日数を記録したいのだと判断しましたので、A No.1の方の回答同様、「変更点がわかるような」という切り口の回答ではありません。 欠勤日数一覧表をメインにお考えだと思いますが、ご趣旨から、5月度欠勤をメインに考え、処理終了後、欠勤日数一覧表に改名してしまう方が楽と判断しました。処理の都合上、欠勤DBというブックに、両方のデータを複写し、それぞれ5月度欠勤、欠勤日数一覧表というシート名にします。 5月度欠勤に、欠勤日数一覧表と社員ナンバーが一致するものがあった場合は、5月度欠勤の日数に、欠勤日数一覧表の日数を加算します。 5月度欠勤に初めて発生した社員ナンバーのものは変化しません。欠勤日数一覧表にあって、5月度欠勤に無い社員ナンバーのデータは、5月度欠勤の方には反映されませんので、結果として切り捨てられます。 処理が済んだら、欠勤日数一覧表のシートは削除し、5月度欠勤のシートを、欠勤日数一覧表に改名します。 Accessでやれば簡単でしょうが、Excelのワークシートでやる事にチャレンジしてみました。 コードは、5月度欠勤のシートに、欠席日数一覧表の値を加算するところのみです。欠勤DB.xlsから実行すると、不安定だと言われておりますので、別のブックから実行してください。 こんな事もできるのだというご参考まで。Accessが使える環境ならば、そちらで質問すれば、もっと詳しい方が教えてくれるでしょう。 Sub test() 'Dim CN As ADODB.Connection Dim CN As Object Dim mySQL As String On Error GoTo ErrHandle 'Set CN = New ADODB.Connection Set CN = CreateObject("ADODB.Connection") CN.Provider = "Microsoft.Jet.OLEDB.4.0" CN.Properties("Extended Properties") = "Excel 8.0" CN.Open ThisWorkbook.Path & "\" & "欠勤DB.xls" mySQL = "UPDATE [5月度欠勤$] INNER JOIN [欠勤日数一覧表$] ON [5月度欠勤$].[社員ナンバー] = [欠勤日数一覧表$].[社員ナンバー] SET [5月度欠勤$].[欠勤日数] = [欠勤日数一覧表$].[欠勤日数]+[5月度欠勤$].[欠勤日数];" CN.Execute mySQL Set CN = Nothing Exit Sub ErrHandle: MsgBox Err.Number & vbCrLf & Err.Description, vbCritical If CN.State = True Then CN.Close End Sub
- nayuta_lot
- ベストアンサー率64% (133/205)
こんにちは 欠勤日数を年間ないし、半期で累計でみたいということでしょうか？ とりあえずその前提で記載します。 言葉で説明すると少々わかりづらいかと思いますが、参考までに 私なら次のようにします。 ■概要 (1)ファイルを別々にせずに、欠勤管理.xlsというファイルを一つ作成 して、"集計"シート、"累計"シート、"当月"シート、"x月"シート というようにします。 フォーマットは、送られてくるフォーマットも含めて、全て同じに します。 (2)最新月（5月）のファイルが送られてきたら、そのファイルのセル を全てコピーして、5月と当月のシートに値コピーを行います。 あえて、5月と当月のシートにコピーする理由は、集計シートの式 が常に累計と当月のシートを参照するようにして、 "式の修正をしなくてもよくする" ためです。 (3)次月になったら集計シートのセルを全てコピーし、累計シート に値コピーを行います。 (4)6月になったら集計、累計、当月、"6月"、5月、4月、・・という ようにシートを増やしていきます。 (5)6月の処理を(2)へ戻って、同様に行います。 さて、肝心の表の式ですが、質問の内容からしますと、 （いなくなった人は削除する、新たに入った人は挿入する） ということですので、最新の情報（当月）の社員がいればよい ことになります。 間違っていたら補足で教えてください。 ■表の作成 (1)集計、累計、当月、ｘ月の各シートに 所属部署名／社員ナンバー／社員名／欠勤日数 を列とした４列の表を作成します。 縦の長さは社員数の変化に対応できるように少し多めに しておきます。（平均的に100人とかなら120とか余計に） 概要でも述べましたが、表のセル位置（フォーマット）は全て 同じ状態になるように作成します。 ※シートを全て選択して、表を作成すれば同じ操作が全ての シートに適用されます。 ただし、そのセルあった内容は "書き換えられます"ので、ご注意ください。 (3)最新の社員構成で集計されれば良いようですので、集計シートの 所属部署名／社員ナンバー／社員名は当月の同じセル位置を参照 するようにします。 A1からD1が、所属部署名／社員ナンバー／社員名／欠勤日数として 所属部署名 社員ナンバー 社員名 =当月!A2 =当月!B2 =当月!C2 ※当月に何も入っていないと0が表示されます。 なんか、嫌ですよね。 そこで =IF(当月!A2="","",当月!A2)というようにして空白ならば何も 表示しないようにします。 他のセルも同様にします。 ちなみに、まずA2を選択してそこからC最終行まで範囲選択して =IF(と入力して、当月のA2をクリックし続けて上記の式を入力し、 その状態で「Ctrl+Enter」を押せば、一発で全てのセルに式が入 ります。 ※メニューバーの所が、×レ＝ とでている状態、セルを編集して いる状態で使えます。 結構、使えますｗ (4)欠勤日数の集計 さて、いよいよここからが問題です。 結論から言いますと、D列に次のように記入します。 =IF(当月!B2="","",IF(ISNA(VLOOKUP(B2,累計!B$2:D$16,3,0)),VLOOKUP(B2,当月!B$2:D$16,3,0),VLOOKUP(B2,累計!B$2:D$16,3,0)+VLOOKUP(B2,当月!B$2:D$16,3,0))) 順番に説明します。 VLOOKUPは一応、参照される側（累計、当月）を昇順にしておく 必要があります。 検索キーはユニークな値である社員ナンバーが良いでしょう。 ただし、102とか405とか数値だけだと、文字と数値の違いなどで うまく検索できない場合があります。 そこで、キーは 「A102,A405・・・」というような社員ナンバーに変換しておいた ほうが無難です。 後ろの方にある式(1) VLOOKUP(B2,累計!B$2:D$16,3,0)+VLOOKUP(B2,当月!B$2:D$16,3,0) ※累計の検索結果と当月の検索結果を足して値を返します。 集計のB2（社員ナンバー）をキーとして累計シートのB2からD16まで （実際には使用する範囲です。）を検索範囲として、『最左列』を 検索していきます。 B$2:D$16の$は絶対参照で、コピーしても行 の範囲が変わらないようにするために付けます。 検索の結果、一致すると検索範囲の 「最左列から3番目の内容」を返してきます。 VLOOKUP(B2,累計!B$2:D$16,3,0)←3てのがその指定です。 左から3列目のセルですね。 最後の0は完全一致の指定ですので通常0でいいと思います。 ISNAってなんだ？式(2) IF(ISNA(VLOOKUP(B2,累計!B$2:D$16,3,0)),VLOOKUP(B2,当月! B$2:D$16,3,0),式(1)) 急に難しくなりましたが、VLOOKUPは検索結果が一致しないと#N/A （No Answer）を返してきます。 ちょっと見た目嫌ですね＾＾； 累計を検索した結果、#N/Aであれば、社員が新しく増えたことに なります。 従って、VLOOKUP(B2,当月!B$2:D$16,3,0)当月の検索だけでよい はずです。 この＃Ｎ／ＡをするのがISNAという関数です。 #N/Aなら真(TRUE)、そうでないなら偽(FALSE)を返します。 また、減った場合は、基本的に当月と同じキーで検索しているので 当月の検索では#N/Aは発生しないはずです。 従って、累計が#N/Aでなければ累計と当月を足せばよいのです。 最後の=IF(当月!B2="","",式(2)) これは空白の行を設けておくと同じように#N/Aが表示されます。 セルを見えなくしてしまえばいいのですが、最初から当月の社員 ナンバーのところが空白ならば何も表示しないようにしておけば、 行に余裕を持たせておいても、表として美しいということです。 おまけ 余計に取った空白行（列も）は、行全体を選択してメニューの 『データ＞グループとアウトラインの設定＞グループ化』 で左（または上）にでた＋ －で開いたり閉じたりできます。 以上、こんな感じですが参考にしてください。
