Корзина
37 отзывов
+7 (499) 390-31-30
Знания для эффективного управления бизнесом!
  • УКЦ АББ
  • Статьи
  • Как в Excel отслеживать изменения одновременно на нескольких листах бюджетной модели

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

Как в Excel отслеживать изменения одновременно на нескольких листах бюджетной модели
Решение поможет отследить, как меняются плановые финансовые результаты, если скорректировать исходные параметры в бюджетной модели. Переключаться между листами в Еxcel не придется – все ключевые таблицы на одном листе.

21.03.16


Допустим, у вас на руках финансовая модель компании. Нужно посмотреть, как повлияет изменение плана продаж на бюджеты доходов и расходов, движения денежных средств. Планы находятся в одной книге, но на разных листах. Есть два способа отследить изменения, не переключаясь между листами:

  • воспользоваться встроенной функцией Excel;
  • добавить макрос.

Способы отличаются только порядком действий. Можно использовать оба или выбрать удобный.

Например, на листе «Sales» (см. рисунок 1) находится план продаж, а под ним отображаются еще два отчета, расположенных на соседних вкладках. Отчеты интерактивны, при изменении данных в плане продаж меняется информация и в них.

Рисунок 1. Отражение на активном листе отчетов из других листов книги


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

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

Рисунок 2. Инструмент «Камера» в настройках


Перейдите на лист с вводными параметрами или функциональным бюджетом, который нужно отобразить на другом листе. Выделите таблицу – удерживая Shift, кликните по первой и последней ячейке. В примере это план движения денежных средств, лист «Cash flow» с диапазоном ячеек A1:M12.

Нажмите на значок «Камера» в верхнем левом углу панели задач (см. рисунок 3) и перейдите на лист, в котором нужно отобразить отчет. В примере это лист «Sales». Кликните по первой пустой ячейке, и в ней автоматически появятся ранее выделенные данные.

 

Рисунок 3. Запуск камеры на панели быстрого доступа


 


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

Когда необходимо, чтобы на листе «Sales» отображалось сразу три отчета (см. рисунок 1), зайдите на лист «Cash flow» и выделите диапазон ячеек A1:M12. Вызовите команду «Камера» и перейдите на лист «PL», где «План доходов и расходов». Кликните по первой пустой ячейке под таблицей с планом доходов и расходов – А16, и отчет о движении денежных средств появится на листе.

Далее выделите оба отчета на листе «PL», нажмите «Камера», перейдите на лист «Sales», кликните по первой пустой ячейке под планом продаж – А9, и автоматически на листе «PL» отобразится сразу два отчета. Оба отчета интерактивны, при внесении изменений в план продаж видно, как меняются данные в плане доходов и расходов, а также в плане движения денежных средств.

 

Какой макрос поможет отслеживать одновременно таблицы из разных листов в Excel

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

  • разрешите Excel доступ к макросам;
  • запишите в модуль листа код;
  • измените в коде названия просматриваемых листов и их количество.

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

Вызовите VBA редактор, нажав Alt + F11. В левом углу найдите книгу с отчетом и кликните по нему мышкой, откроется список листов. Найдите лист, в котором нужно отображать информацию, в примере это лист «Sales». Дважды кликните по нему мышью и в открывшееся слева окно скопируйте макрос:

 

Sub Create_Camera()
Sheets("Лист1").Range("A1:C10").Copy
Sheets("Лист2").Activate
[A1].Select
ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub

 

Измените в строке макроса:
Sheets("Лист1").Range("A1:C10").Copy – название листа, который нужно видеть, и диапазон отчета. Например, чтобы отобразить план движения денежных средств, в этой строке макрос записали так: Sheets("Cash Flow").Range("A1:M12").Copy, где A1:M12 – диапазон, в котором расположен отчет;
Sheets("Лист2").Activate – название активного листа, на котором нужно отобразить отчет. В примере эта строка указана так – Sheets("Sales");
[A1].Select – первую ячейку, куда скопируется отчет. В примере эта строка указана так – Activate [A9].Select, так как первая пустая ячейка плана продаж на листе «Sales» – А9.

Закройте VBA редактор и перейдите на лист, в модуль которого записали код. Нажмите Alt + F8 и в открывшемся окне кликните по макросу с названием Create_Camera, далее «Выполнить». Автоматически в указанном диапазоне появится отчет.

Если нужно отобразить не один, а два или три отчета, скопируйте в макросе строки:


Sheets("Лист1").Range("A1:C10").Copy;
Sheets("Лист2").Activate;
[A1].Select;
ActiveSheet.Pictures.Paste(Link:=True) Select.

 

Вставьте их в код еще раз, замените листы и диапазоны. В примере, чтобы видеть план движения денежных средств и план доходов и расходов на листе «Sales», код изменили так:

 

Sub Create_Camera()
Sheets("Cash Flow").Range("A1:M12").Copy
Sheets("Sales").Activate
[A9].Select
ActiveSheet.Pictures.Paste(Link:=True).Select
Sheets("PL").Range("A1:N14").Copy
Sheets("Sales").Activate
[A30].Select
ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub


 

Предыдущие статьи