Как автоматизировать отчеты в Excel

Из этой статьи вы узнаете, как в Microsoft Excel автоматизировать создание отчетов о данных. Здесь описывается, как в таблице Excel запросить данные из внешнего источника (MySQL, Postgres, Oracle и так далее) и создать отчет с помощью специального плагина, который свяжет таблицу Excel с внешними источниками данных.

Чтобы создать отчет о данных в таблице Excel, воспользуемся макросом, который к тому же позволяет сохранять отчеты в различных файловых форматах одним нажатием клавиши. Имейте в виду, что в Excel встроена функция, которая позволяет создавать макросы без необходимости писать программный код.

Метод 1

Метод 1 из 2:

Для данных в Excel

Загрузить PDF

Если данные, о которых необходимо составить отчет, уже находятся в таблице Excel, автоматизируйте процесс создания отчета с помощью макросов. Макросы поддерживаются встроенной в Excel функцией, которая позволяет автоматизировать сложные и повторяющиеся задачи.
2
Запустите Excel. Дважды щелкните (или просто щелкните на компьютере Mac) по значку в виде белой буквы «Х» на зеленом фоне, а затем нажмите «Пустая книга» на странице шаблонов.
- На компьютере Mac вам, возможно, придется нажать «Файл» > «Пустая книга».
- Если у вас уже есть отчет Excel, который нужно автоматизировать, дважды щелкните по файлу отчета, чтобы открыть его в Excel.
Если вы еще не добавили заголовки столбцов и числа, о которых нужно составить отчет, сделайте это сейчас.
4
Активируйте меню Разработчик. По умолчанию это меню не отображается в верхней части окна Excel. Чтобы включить меню «Разработчик»:
- Windows — нажмите «Файл» > «Параметры» > «Настроить ленту» (на левой панели), установите флажок у «Разработчик» в нижнем правом углу (если вы не видите эту опцию, прокрутите страницу вниз), а затем нажмите «ОК».^{[1]XИсточник информации}
- Mac — нажмите «Excel» > «Параметры» > «Лента и панель инструментов», установите флажок у «Разработчик» в списке «Основные», а затем нажмите «Сохранить».^{[2]XИсточник информации}
Вы найдете эту вкладку вверху окна Excel. Откроется панель инструментов «Разработчик».
Это опция на панели инструментов. Появится всплывающее окно.
7
Введите имя макроса. Сделайте это в текстовом поле «Имя макроса». Так впоследствии вам будет проще определить, что это за макрос.
- Например, если макрос будет строить график на основе доступных данных, назовите его «График1» или как-то аналогично.
8
Создайте сочетание клавиш для запуска макроса. Нажмите клавишу ⇧ Shift, а затем нажмите другую клавишу (например, T), чтобы создать сочетание клавиш.
- На компьютере Mac сочетанием клавиш будет ⌥ Option+⌘ Command и еще одна клавиша (например, ⌥ Option+⌘ Command+T).
9
Сохраните макрос в открытой таблице Excel. Для этого нажмите «Сохранить в» и в меню выберите «Эта книга», чтобы макрос был доступен для всех, кто откроет данную таблицу.
- Файл Excel нужно сохранить в специальном формате с поддержкой макросов.
Это кнопка внизу окна. Настройки макроса будут сохранены и включится режим записи. С этого момента все ваши действия будут записываться (до момента, когда вы остановите запись).
11
Выполните действия, которые хотите автоматизировать. Excel будет отслеживать каждый щелчок, каждое нажатие клавиши и введенные параметры форматирования; все это будет вносится в список макроса.
- Например, чтобы выбрать данные и создать диаграмму, выделите нужные данные, нажмите «Вставка» в верхней части окна Excel, выберите тип диаграммы и отредактируйте диаграмму так, как нужно.
- Другой пример: чтобы создать макрос для построения графика из данных, которые находятся в ячейках с A1 по A12, щелкните по пустой ячейке, введите =СУММ(A1:A12) и нажмите ↵ Enter.
Эта опция находится на панели инструментов «Разработчик». Запись будет остановлена, а все ваши действия, которые вы совершили во время записи, будут сохранены в виде отдельного макроса.
13
Сохраните таблицу Excel как файл с поддержкой макросов. Нажмите «Файл» > «Сохранить как» и измените формат файла на «xlsm» (вместо «xls»). Затем введите имя файла, выберите папку для его сохранения и нажмите «Сохранить».
- Если не сделать этого, макрос не будет сохранен как часть электронной таблицы, то есть им не смогут пользоваться другие люди на других компьютерах.
14
Запустите макрос. Для этого нажмите сочетание клавиш, выбранное при создании макроса. Будут выполнены процессы, записанные в макросе.
- Также, чтобы запустить макрос, можно нажать «Макросы» на вкладке «Разработчик», выбрать имя макроса и нажать «Выполнить».
Реклама

Метод 2

Метод 2 из 2:

Для внешних данных (MySQL, Postgres, Oracle и так далее)

Загрузить PDF

Скачайте плагин Kloudio. С его помощью вашу таблицу Excel можно соединить с внешней базой данных или источником данных. Этот плагин также работает с Google Таблицами.
Для этого нажмите кнопку «+» на портале Kloudio. Введите информацию о своей базе данных (тип базы данных, учетные данные) и выберите параметры безопасности/шифрования, если работаете с конфиденциальными данными или данными компании.
Когда ваша таблица будет соединена с базой данных, запрашивать внешние данные и создавать отчеты можно будет непосредственно в Excel. Создайте отчет на портале Kloudio, а затем выберите его в меню Excel. Затем примените дополнительные фильтры и задайте периодичность обновления отчета (чтобы таблица автоматически обновлялась каждую неделю, день или даже час).
Более того, вводить данные можно в подключенную таблицу и обновлять данные во внешнем источнике данных. Создайте шаблон загрузки на портале Kloudio и получите возможность вручную или автоматически загружать изменения, сделанные в вашей электронной таблице, во внешний источник данных.
Реклама

Советы

Загружайте плагины Excel только с сайта Microsoft AppSource, если не доверяете сторонним разработчикам.
Макросы можно использовать для автоматизации как простых задач (например, для добавления значений или создания диаграмм), так и сложных (например, для вычисления значений в ячейках, создания диаграммы на основе результатов вычислений, маркировки диаграммы и печати полученного результата).
Если открыть электронную таблицу с макросом, вам, возможно, придется нажать «Включить содержимое» на желтой полосе в верхней части окна, чтобы получить возможность пользоваться макросом.

Предупреждения

В макрос добавятся абсолютно все действия, которые вы выполните в режиме записи. Поэтому не вводите ошибочные значения, не запускайте ненужные программы и не удаляйте файлы.
Макросы могут быть вредоносными (например, созданные, чтобы удалять файлы на компьютере). Не запускайте макросы, которые получены из ненадежных источников.