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

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

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

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

Рисунок 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
Рисунок 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. Ввод макроса для записи изменений в примечания
Рисунок 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. Как изменить диапазон отслеживания изменений

Рисунок 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


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

Рубрики: Метки: , ,

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.