Как с помощью Excel контролировать исправления в бюджете движения денежных средств

Дмитрий Щербаков, консультант по продуктам Microsoft Office, владелец сайта http://www.excel-vba.ru

Если бюджет движения денежных средств в Excel ведут несколько человек, отследить их правки можно с помощью примечаний к измененным ячейкам (см. рисунок 1). В примечаниях можно автоматом выводить:

• дату и время правок;
• имя пользователя (учетную запись) – кто внес правки;
• значение ячейки до изменения.

Рисунок 1. Примечания к измененным ячейкам

Для примечаний потребуется добавить макрос. Проверьте, не блокирует ли Excel макросы. Кликните по кнопке «Офис» в верхней левой части экрана, выберите «Параметры Excel», в появившемся окне зайдите в «Центр управления безопасностью» и кликните по опции «Параметры центра управления безопасностью». Далее укажите «Разрешить все макросы» и нажмите ОК. После замены параметров перезагрузите Excel, только после этого они вступят в силу.

Как с помощью Excel контролировать дату и время изменения ячеек отчета о движении денежных средств

Чтобы в примечаниях к ячейкам бюджета движения денежных средств автоматически записывалась дата и время их изменений, откройте файл с бюджетом. Нажмите ALT + F11. В появившемся окне Microsoft Visual Basic (VBA) найдите папку Microsoft Excel Objects. Если не видите окно проводника Project Explorer, тогда нажмите Ctrl + R либо откройте его через меню редактора VBA-View-Project Explorer (см. рисунок 2).

Рисунок 2. Как отобразить проводник Project Explorer

Выберите лист, на котором надо отслеживать изменения (в примере это лист 1), и дважды кликните по нему (см. рисунок 3), скопируйте в появившееся окно макрос:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oComment As Comment

On Error Resume Next

Set oComment = Target.Comment

If oComment Is Nothing Then

Set oComment = Target.AddComment(Target.Text & " " & Format(Now, "dd.mm.yy HH:MM"))

Else

oComment.Text oComment.Text & Chr(10) & Target.Text & " " & Format(Now, "dd.mm.yy HH:MM")

oComment.Shape.TextFrame.AutoSize = True

End If

End Sub

Рисунок 3. Ввод макроса для записи изменений в примечания

Закройте редактор VBA. Теперь, как только кто-то исправит хоть одну цифру или букву в отчете, программа автоматически создаст примечание к измененной ячейке. Что касается существующих примечаний, в них информация об изменениях допишется также автоматом.

Как после правки отчета о движении денежных средств сохранить исходные значения его показателей с помощью Excel

Есть способ сохранить после правки отчета предыдущее значение показателя и имя пользователя, внесшего изменения .

Откройте файл с бюджетом. Зайдите в редактор VBA, нажав сочетание клавиш Alt + F11, далее в левой части открывшегося окна (Project Explorer) выберите лист с отчетом, где нужно сохранять исходные значения, дважды кликните по нему. Если в модуле листа уже есть макрос, записывающий дату и время изменений, удалите его и вставьте следующий:

Option Explicit

Public sValue As String

'заносим в переменную значение ячейки

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 Then

sValue = Target.Value

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

'отслеживаем изменения только в диапазоне "A1:K10"

If Intersect(Target, Me.Range("A1:K10")) Is Nothing Then Exit Sub

'сравниваем новое значение с прежним

If CStr(Target.Value) <> sValue Then

Dim oComment As Comment

On Error Resume Next

Set oComment = Target.Comment

If oComment Is Nothing Then

Set oComment = Target.AddComment(Application.UserName & ":" & Chr(10) & sValue & " " & Format(Now, "dd.mm.yy HH:MM"))

Else

oComment.Text oComment.Text & Chr(10) & Application.UserName & ":" & Chr(10) & sValue & " " & Format(Now, "dd.mm.yy HH:MM")

End If

oComment.Shape.TextFrame.AutoSize = True

End If

End Sub



«Как с помощью Excel контролировать исправления в бюджете движения денежных средств». Д. Щербаков
© Материал из ФСС "Система Финансовый директор".
Подробнее: https://www.1fd.ru/#/document/173/2372/bssPhr50/?of=copy-e7b6591f11

По умолчанию в макросе задан диапазон отслеживания изменений A1:K10 (см. рисунок 4). Это условие можно убрать, тогда макрос будет проверять весь лист, или указать свой диапазон. Например, для контроля всех правок в столбце А задайте А:А.Рисунок 4. Как изменить диапазон отслеживания изменений

В примечаниях макрос сохранит имя пользователя, прежнее значение, дату и время изменений.

Вопрос:Как выгрузить информацию из примечаний к отчету в Excel

Дмитрий Щербаков, консультант по продуктам Microsoft Office, владелец сайта http://www.excel-vba.ru

Чтобы информация из примечаний попала в ячейки отчета, зайдите в редактор VBA Project, нажав клавиши Alt + F11. В левой части редактора кликните правой кнопкой мыши по файлу с отчетом, из которого нужно выгрузить примечания. В открывшемся списке команд выберите Insert, далее Module. В появившееся окно стандартного модуля скопируйте макрос:

	
Function Get_Text_from_Comment(rCell As Range)

On Error Resume Next

Get_Text_from_Comment = rCell.Comment.Text

End Function

Закройте окно редактора, перейдите в отчет, кликните по ячейке, в которую нужно выгрузить примечание. Перейдите на панели задач во вкладку «Формулы» и выберите команду «Вставить функцию». В открывшемся окне «Мастер функций – шаг1 из 2» в поле «Категория» укажите «Определенные пользователем», в поле ниже появится функция под названием Get Text From Comment, выберите ее и нажмите «ОК». Автоматически откроется окно «Аргумент функции», укажите в поле RCell ячейку, из которой необходимо получить примечание, и нажмите «ОК». Примечания автоматически выгрузятся в указанную ранее ячейку.

Если нужно, чтобы при перемещении из примечаний удалялось поле «Автор», замените первый макрос на этот:

	
Function Get_Text_from_Comment(rCell As Range) As String

Dim sTxt As String

On Error Resume Next

sTxt = rCell.Comment.Text

Get_Text_from_Comment = Mid(sTxt, InStr(sTxt, ":") + 2)

End Function

Запустить его можно так же, как и остальные.