Проблема очень актуальна для больших организаций, когда одним файлом пользуются несколько человек. И каждый может сделать какие-то свои изменения. И конечно всегда наступает момент когда надо узнать — а кто сделал то или иное изменение? Возможно просто для информации, а бывает и хуже, когда необходимо узнать кто внес конкретное изменение, которое делать было нельзя и по возможности восстановить хоть часть того, что было.
- Отслеживание изменений при помощи встроенных средств — Общий доступ к книге
- Отслеживание изменений и ведение журнала при помощи кода
Есть относительно простой способ отслеживать изменения(если это можно так назвать): дать книге общий доступ
Excel 2007 и выше: вкладка Рецензирование
Файл(File) -Параметры(Options) -либо Панель быстрого доступа(Quick Access Toolbar) либо Настроить ленту
После этого выбираем команду Общий доступ к книге(старые версии)
В появившемся окне поставить галочку разрешить изменять файл нескольким пользователям одновременно
Далее можно настроить срок хранения лога изменений, конфликты и пр — вкладка Подробнее
Регистрация изменений
- Хранить журнал в течение
(keep change history for) : — если необходимо вести журнал изменений(а нам необходимо!) то оставляем этот пункт включенным и устанавливаем количество дней, в течение которых необходимо сохранять историю. По умолчанию это 30 дней. Здесь имеются ввиду последние 30 дней от текущей даты. Т.е. по истечению этих 30 дней более ранние данные истории будут затерты - Не хранить журнал изменений
(don’t keep change history) : после выбора этого пункта и подтверждения журнал будет удален(если он был создан) и история вестись не будет
Обновлять изменения
- При сохранении файла
(When file is saved) — это самый оптимальный вариант. Данные об изменениях в файле будут обновляться только тогда, когда мы сами сохраним файл. - Каждые
(Automatically every) : указывается промежуток времени в минутах, через который книга сама автоматически будет сохраняться и регистрировать изменения. Не очень удобен данный пункт если в файле одновременно работает несколько человек. При этом необходимо будет обязательно выбрать какое действие будет производится по умолчанию:- сохранить мои изменения и просмотреть чужие
(save my changes and see others’ changes) - только просмотреть чужие изменения
(just see other users’ changes)
Пункты говорят сами за себя, можно отметить лишь один нюанс: принимать решение о сохранении тех или иных изменениях будет тот пользователь, который первым открыл файл.
- сохранить мои изменения и просмотреть чужие
Для противоречивых изменений
- запрашивать чьи изменения имеют преимущество
(ask me which changes win) — самый оптимальный вариант. Первый, открывший файл пользователь определяет какие изменения надо принять, а какие отклонить - ранее сохраненные имеют преимущество
(the changes being saving win) — не очень правильный вариант, но все зависит от ситуации. По логике при данном пункте при возникновении конфликта автоматически будут приняты лишь те изменения, которые были сделаны ранее. Может сыграть нехорошую шутку, поэтому надо быть острожным с этим пунктом
Включить в личное представление
-
Данный пункт управляет настройками печати и фильтра общей книги для нескольких пользователей.
- параметры печати
(Print settings) — Обычно, в одном файле когда мы настраиваем параметры печати, они сохраняются внутри файла и при следующем открытии их не надо уже заново настраивать. Здесь тоже самое, но хранится для каждого пользователя отдельно. Т.е. даже если в этом файле один пользователь настроил одни параметры печати, а другой – иные, то для каждого пользователя эти параметры сохраняться. В обычной книге применились бы те параметры, которые были назначены перед последним сохранением книги. - фильтры
(Filter settings) — если один пользователь отфильтровал данные по «Юго-Восточный округ», а другой тот же столбец по «Северный округ», то при установленном данном пункте у каждого пользователя файл откроется с отфильтрованными строками именно по установленным ими параметрам — для каждого свой
Теперь самое главное: как увидеть все сделанные изменения
После того, как пользователи поработали с файлом и стало необходимо увидеть сделанные изменения необходимо перейти на вкладке Рецензирование
Здесь можно выбрать какие изменения показывать
- по времени
(When) — если хотите увидеть только какие-то конкретные изменения, то надо установить галочку на этом пункте и выбрать нужное. Доступно выбрать: Со времени последнего сохранения, Все, Еще не просмотрено, С даты. Пункты достаточно красноречивы и понятны, расписывать каждый не вижу смысла. Если хотите просмотреть все изменения — галочку с этого пункта надо снять - пользователем
(Who) можно показать изменения, сделанные конкретным пользователем, всеми пользователями, или всеми пользователями, кроме того, кто запросил отчет об изменениях(т.е. кроме себя любимого) - в диапазоне
(Where) можно указать конкретный диапазон на листе и отчет об изменения будет выведен только для ячеек этого диапазона.
Выделять исправления на экране
Вносить изменения на отдельный лист
Примечание: После того, как книге дан общий доступ книгу одновременно могут менять несколько пользователей. Однако я не рекомендую делать это без крайней необходимости, т.к. одновременный доступ к файлам Excel реализован очень плохо и работает это нестабильно. В какой-то момент файл может просто отказаться работать и все данные будут утеряны. Так же неизбежно будут возникать спорные ситуации, когда изменения внесли два человека одновременно и не сохранилось в результате ни одно из внесенных.
- невозможно удалять листы
- невозможно создавать диаграммы, можно лишь просматривать созданные ранее
- невозможно создавать сводные таблицы, можно лишь просматривать созданные ранее
- невозможно создавать или изменять группировку данных, можно использовать ранее созданную
- невозможно изменять параметры защиты листов и книги
- невозможно использовать расширенный фильтр
- невозможно использовать Текст по столбцам
- невозможно создавать новые проверки данных, а так же изменять существующие. Допускается лишь обвести или удалить обводку с неверных данных
- невозможно добавлять или изменять ранее созданные формулы массива
- и т.д.
Плюс невозможно не только использовать умные таблицы, но и сделать книгу общей, если в ней есть хоть одна умная таблица. Если будет попытка сделать общий доступ к книге с умной таблицей Excel покажет предупреждение, что этого делать нельзя и проинструктирует как преобразовать такую таблицу в диапазон для возможности использовать общий доступ.
Так же хочу отметить, что есть распространенное заблуждение о невозможности использования макросов в книгах с общим доступом. Это не так, коды Visual Basic for Applications разрешается применять и в большинстве случаев они будут работать корректно и как задумывались, если они только не пытаются произвести действия, перечисленные как запрещенные для книг с общим доступом. Плюс невозможно просматривать и изменять коды в книгах с общим доступом.
Изменения можно отслеживать и при помощи кода. При этом такой метод дает не менее полное представление об изменениях в ячейках и при этом давать общий доступ книге нет необходимости, а следовательно и все ограничения, применимые для книг в общем доступе тоже остаются за бортом, что делает такой подход порой предпочтительнее. Единственное, при таком режиме файл нельзя будет редактировать одновременно нескольким пользователям. Но в большинстве случаев этого и не надо.
Я могу предложить небольшой код, который будет отслеживать следующие параметры:
- Имя пользователя(учетная запись пользователя на компьютере), сделавшего изменения
- адрес ячейки, в которую были внесены изменения
- дата и время внесения изменений
- имя листа, в котором были сделаны изменения
- значение ячейки до изменения(старое значение)
- значение ячейки после изменения(новое значение).
Итак, Вы решили реализовать данный процесс. Изначально необходимо разрешить макросы, без этого данный способ ведения журнала не сработает. Далее необходимо добавить в книгу новый лист с именем LOG и вставить приведенный код в модуль книги, изменения в которойнеобходимо отслеживать:
Option Explicit Public sValue As String Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "LOG" Then Exit Sub Dim sLastValue As String Dim lLastRow As Long With Sheets("LOG") lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1 If lLastRow = Rows.Count Then Exit Sub Application.ScreenUpdating = False: Application.EnableEvents = False .Cells(lLastRow, 1) = CreateObject("wscript.network").UserName .Cells(lLastRow, 2) = Target.Address(0, 0) .Cells(lLastRow, 3) = Format(Now, "dd.mm.yyyy HH:MM:SS") .Cells(lLastRow, 4) = Sh.Name .Cells(lLastRow, 5).NumberFormat = "@" .Cells(lLastRow, 5) = sValue If Target.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0 If Not rRng Is Nothing Then For Each rCell In rRng If Not IsError(Target) Then sLastValue = sLastValue & "," & rCell Else sLastValue = sLastValue & "," & "Err" Next rCell sLastValue = Mid(sLastValue, 2) Else sLastValue = "" End If Else If Not IsError(Target) Then sLastValue = Target.Value Else sLastValue = "Err" End If .Cells(lLastRow, 6).NumberFormat = "@" .Cells(lLastRow, 6) = sLastValue End With Application.ScreenUpdating = True: Application.EnableEvents = True End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "LOG" Then Exit Sub If Target.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0 If rRng Is Nothing Then Exit Sub For Each rCell In rRng If Not IsError(rCell) Then sValue = sValue & "," & rCell Else sValue = sValue & "," & "Err" Next rCell sValue = Mid(sValue, 2) Else If Not IsError(Target) Then sValue = Target.Value Else sValue = "Err" End If End Sub |
Что такое модуль книги и как туда вставить код подробно описано в этой статье. Если кратко: открываем редактор VBA(Alt+F11) -находим в списке объектов ЭтаКнига(ThisWorkbook) -двойной щелчок по ней и в окно редактора справа вставляется этот код.
Лист «LOG» рекомендую сделать скрытым, иначе смысла в отслеживании действий мало, т.к. любой сможет перейти на этот лист и стереть историю своих изменений. Надежно скрыть лист поможет эта статья: Как сделать лист очень скрытым.
Для того, чтобы хранить историю изменений в отдельном текстовом файле или отдельной книге Excel можно применить такой код:
Option Explicit Public sValue As String Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "LOG" Then Exit Sub Dim sLastValue As String Dim lLastRow As Long, wbLOG As Workbook Dim sPath as String Const sLOGName As String = "\LOG.txt" '"\LOG.xls" sPath = Application.DefaultFilePath Application.ScreenUpdating = False '============== только для записи в текстовый файл ====================== If Dir(sPath & sLOGName, vbDirectory) = "" Then Open sPath & sLOGName For Output As #1: Close #1 End If '============== только для записи в отдельный файл Excel ====================== ' If Dir(sPath & sLOGName, vbDirectory) = "" Then ' Set wbLOG = Workbooks.Add ' wbLOG.SaveAs sPath & sLOGName, xlNormal ' End If Set wbLOG = Workbooks.Open(sPath & sLOGName) '============================================================================ With wbLOG.Sheets(1) lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1 If lLastRow = .Rows.Count Then Exit Sub Application.ScreenUpdating = False: Application.EnableEvents = False .Cells(lLastRow, 1) = CreateObject("wscript.network").UserName .Cells(lLastRow, 2) = Target.Address(0, 0) .Cells(lLastRow, 3) = Format(Now, "dd.mm.yyyy HH:MM:SS") .Cells(lLastRow, 4) = Sh.Name .Cells(lLastRow, 5).NumberFormat = "@" .Cells(lLastRow, 5) = sValue If Target.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0 If Not rRng Is Nothing Then For Each rCell In rRng If Not IsError(Target) Then sLastValue = sLastValue & "," & rCell Else sLastValue = sLastValue & "," & "Err" Next rCell sLastValue = Mid(sLastValue, 2) Else sLastValue = "" End If Else If Not IsError(Target) Then sLastValue = Target.Value Else sLastValue = "Err" End If .Cells(lLastRow, 6).NumberFormat = "@" .Cells(lLastRow, 6) = sLastValue End With wbLOG.Close 1 Application.ScreenUpdating = True: Application.EnableEvents = True End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "LOG" Then Exit Sub If Target.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0 If rRng Is Nothing Then Exit Sub For Each rCell In rRng If Not IsError(rCell) Then sValue = sValue & "," & rCell Else sValue = sValue & "," & "Err" Next rCell sValue = Mid(sValue, 2) Else If Not IsError(Target) Then sValue = Target.Value Else sValue = "Err" End If End Sub |
Файл хранится в папке
Чтобы вести изменения в отдельной книге Excel надо будет всего лишь закомментировать строки под «только для записи в текстовый файл» и раскомментировать строки под «только для записи в отдельный файл Excel» и поменять значение для константы
Не следует оставлять оба этих блока — они противоречат друг другу и если оставить оба, то будет создан текстовый файл, но изменения все равно будут заноситься в отдельную книгу Excel.
Если хотите, чтобы файл с историей изменений хранился в папке, отличной от Мои документы, то необходимо
При изменении данного параметра необходимо учитывать, что не у всех пользователей может быть доступ к конкретной папке.
Все чаще стали появляться вопросы типа «А как отследить изменения только в конкретном диапазоне?». На самом деле не очень сложно. Надо добавить пару строк, которые будут определять в каких ячейках были изменения и какие отслеживать. Только добавить строки надо будет в обеих процедурах:
Например, код ниже будет отслеживать только те ячейки, для которых значение изменили только в диапазоне
Option Explicit Public sValue As String Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "LOG" Then Exit Sub Dim rCells As Range 'если изменения произошли не в диапазоне "B:F" - ничего не делаем On Error Resume Next Set rCells = Intersect(Target, Range("B:F")) If rCells Is Nothing Then Exit Sub On Error GoTo 0 Dim sLastValue As String Dim lLastRow As Long, wbLOG As Workbook Dim sPath as String Const sLOGName As String = "\LOG.txt" '"\LOG.xls" sPath = Application.DefaultFilePath Application.ScreenUpdating = False '============== только для записи в текстовый файл ====================== If Dir(sPath & sLOGName, vbDirectory) = "" Then Open sPath & sLOGName For Output As #1: Close #1 End If '============== только для записи в отдельный файл Excel ====================== ' If Dir(sPath & sLOGName, vbDirectory) = "" Then ' Set wbLOG = Workbooks.Add ' wbLOG.SaveAs sPath & sLOGName, xlNormal ' End If Set wbLOG = Workbooks.Open(sPath & sLOGName) '============================================================================ With wbLOG.Sheets(1) lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1 If lLastRow = .Rows.Count Then Exit Sub Application.ScreenUpdating = False: Application.EnableEvents = False .Cells(lLastRow, 1) = CreateObject("wscript.network").UserName .Cells(lLastRow, 2) = Target.Address(0, 0) .Cells(lLastRow, 3) = Format(Now, "dd.mm.yyyy HH:MM:SS") .Cells(lLastRow, 4) = Sh.Name .Cells(lLastRow, 5).NumberFormat = "@" .Cells(lLastRow, 5) = sValue If rCells.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(rCells, Sh.UsedRange): On Error GoTo 0 If Not rRng Is Nothing Then For Each rCell In rRng If Not IsError(Target) Then sLastValue = sLastValue & "," & rCell Else sLastValue = sLastValue & "," & "Err" Next rCell sLastValue = Mid(sLastValue, 2) Else sLastValue = "" End If Else If Not IsError(Target) Then sLastValue = Target.Value Else sLastValue = "Err" End If .Cells(lLastRow, 6).NumberFormat = "@" .Cells(lLastRow, 6) = sLastValue End With wbLOG.Close 1 Application.ScreenUpdating = True: Application.EnableEvents = True End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "LOG" Then Exit Sub Dim rCells As Range 'если изменения произошли не в диапазоне "B:F" - ничего не делаем On Error Resume Next Set rCells = Intersect(Target, Range("B:F")) If rCells Is Nothing Then Exit Sub On Error GoTo 0 If rCells.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(rCells, Sh.UsedRange): On Error GoTo 0 If rRng Is Nothing Then Exit Sub For Each rCell In rRng If Not IsError(rCell) Then sValue = sValue & "," & rCell Else sValue = sValue & "," & "Err" Next rCell sValue = Mid(sValue, 2) Else If Not IsError(Target) Then sValue = Target.Value Else sValue = "Err" End If End Sub |
Tips_Macro_LOG.xls (50,0 КиБ, 8 624 скачиваний)
Так же см.:
Выделение сделанных изменений
Запись изменений на листе в примечания
Мира вашему дому. А можно ли вести журнал с макросом в отдельном файле? (Нужно для сохранения функции отката действий в таблице)
Тагир, в статье приведены коды и как вести журнал в этой же книге и как в другой и даже в текстовом файле. Что нужно Вам из того, чего нет — я не понял.
Сохранить откат действий пытаюсь
А эта статья причем здесь? Тагир, либо старайтесь пояснить точнее что надо и какое отношение это имеет к этой статье, либо обратитесь вфорум , описав свою проблему.Как отменить действия макроса
Про откат действий есть отдельная статья:
Понял. Можно как то формулами отследить изменения в ячейке? Спасибо вам за ваш труд, да воздаст вам Всевышний.
А можете подсказать почему когда открываю кнопку «Доступ к книге», в открывшемся окне «Управление доступом к файлу» не возможно изменить значения пункта «хранить журнал изменения в течении» (горит серым)?
Доброго времени суток! Очень понравился в работе Ваш макрос! Спасибо огромное! Однако, возникла проблема с отслеживанием изменений при копировании данных из любого другого файла (например из таблицы в ворд) и вставки этих данных в Эксель с макросом, в LOG эта вставка прописывается в одной строке сначала как диапазон (например, А1:А12), а потом перечислением вставленных данных (например, 1,2,3,4,5 и т. д). Мне для работы крайне важно, чтобы изменение каждой ячейки, даже если данные для нее скопированы из другого места, отражались построчно. Вшитое отслеживание изменений (через Рецензирование) собственно так и делает, а вот макрос нет. Помогите, пожалуйста, спасете жизнь целых двух испытательных лабораторий (у нас очень много электронных журналов создано на базе Вашего макроса, а проверяющие органы не пропускают указанную выше проблему и грозятся штрафами). Очень жду ответ! Заранее спасибо!
Лидия, здесь много надо переписывать под Вашу задачу и при этом уточнять нюансы, раз это для каких-то проверяющих органов. Думаю, Вам лучше обратиться с заказом на разработку. Можете черезформу моего сайта или на форуме . Ну или вообще за пределами сайта :)