Як автоматизувати звіти в excel (з ілюстраціями)

З цієї статті ви дізнаєтеся, як в Microsoft Excel автоматизувати створення звітів про дані. Тут описується, як в таблиці Excel зробити запит із зовнішнього джерела (MySQL, Postgres, Oracle і так далі) і створити звіт за допомогою спеціального плагіна, який зв`яже таблицю Excel з зовнішніми джерелами даних.

Щоб створити звіт про дані в таблиці Excel, скористаємося макросом, який до того ж дозволяє зберігати звіти в різних файлових форматах одним натисканням клавіші. Майте на увазі, що в Excel вбудована функція, яка дозволяє створювати макроси без необхідності писати програмний код.

кроки

Метод 1 з 2:

Для даних в Excel

1. Якщо дані, про які необхідно скласти звіт, вже знаходяться в таблиці Excel, автоматизуйте процес створення звіту за допомогою макросів. Макроси підтримуються вбудованої в Excel функцією, яка дозволяє автоматизувати складні і повторювані завдання.

2. запустіть Excel. Двічі клацніть (або просто клацніть на комп`ютері Mac) по значку у вигляді білої літери «Х» на зеленому тлі, а потім натисніть «Порожня книга» на сторінці шаблонів.

На комп`ютері Mac вам, можливо, доведеться натиснути «Файл»> «Порожня книга».

Якщо у вас вже є звіт Excel, який потрібно автоматизувати, двічі клацніть по файлу звіту, щоб відкрити його в Excel.

3. Введіть дані своєї електронної таблиці, якщо необхідно. Якщо ви ще не додали заголовки стовпців і числа, про які потрібно скласти звіт, зробіть це зараз.

4. активуйте меню Розробник. За замовчуванням це меню не відображається у верхній частині вікна Excel. Щоб включити меню «Розробник»:

Windows - натисніть «Файл»> «Параметри»> «Налаштувати стрічку» (на лівій панелі), встановіть прапорець у «Розробник» в нижньому правому куті (якщо ви не бачите цю опцію, перейдіть вниз), а потім натисніть «ОК».

Mac - натисніть «Excel»> «Параметри»> «Стрічки й панель інструментів», встановіть прапорець у «Розробник» в списку «Основні», а потім натисніть «Зберегти».

5. натисніть Розробник. Ви знайдете цю вкладку у верхній частині вікна Excel. Відкриється панель інструментів «Розробник».

6. клацніть по запис макросу. Це опція на панелі інструментів. У спливаючому вікні.

7. Введіть ім`я макросу. Зробіть це в текстовому полі «Ім`я макросу». Так згодом вам буде простіше визначити, що це за макрос.

Наприклад, якщо макрос буде будувати графік на основі доступних даних, назвіть його «Графік1» або якось аналогічно.

8. Створіть поєднання клавіш для запуску макросу. натисніть ⇧ Shift, а потім натисніть іншу клавішу (наприклад, T), Щоб створити поєднання клавіш.

На комп`ютері Mac натисніть сполучення клавіш буде ⌥ Option+⌘ Command і ще одна клавіша (наприклад, ⌥ Option+⌘ Command+T).

9. Збережіть макрос у відкритій таблиці Excel. Для цього натисніть «Зберегти в» і в меню виберіть «Ця книга», щоб макрос був доступний для всіх, хто відкриє цю таблицю.

Файл Excel потрібно зберегти в спеціальному форматі з підтримкою макросів.

10. клацніть по OK. Це кнопка внизу вікна. Налаштування макросу будуть збережені і включиться режим запису. З цього моменту всі ваші дії будуть записуватися (до моменту, коли ви зупините запис).

11. Виконайте дії, які хочете автоматизувати. Excel буде відслідковувати кожен клацання, кожне натискання клавіші і введені параметри форматірованія- все це буде вноситься в список макросу.

Наприклад, щоб вибрати дані і створити діаграму, виділіть потрібні дані, натисніть «Вставка» у верхній частині вікна Excel, виберіть тип діаграми і відредагуйте діаграму так, як потрібно.

Інший приклад: щоб створити макрос для побудови графіка з даних, які знаходяться в осередках з A1 по A12, клацніть по порожній клітинці, введіть = СУММ (A1: A12) та натисніть ↵ Enter.

12. клацніть по зупинити запис. Ця опція знаходиться на панелі інструментів «Розробник». Запис буде зупинено, а всі ваші дії, які ви зробили під час запису, будуть збережені у вигляді окремого макросу.

13. Збережіть таблицю Excel як файл з підтримкою макросів. Натисніть «Файл»> «Зберегти як» і змініть формат файлу на «xlsm» (замість «xls»). Потім введіть ім`я файлу, виберіть папку для його збереження і натисніть «Зберегти».

Якщо не зробити цього, макрос НЕ буде збережений як частина електронної таблиці, тобто їм не зможуть користуватися інші люди на інших комп`ютерах.

14. запустіть макрос. Для цього натисніть клавіші, вбрання при створенні макросу. Будуть виконані процеси, записані в макросі.

Також, щоб запустити макрос, можна натиснути «Макроси» на вкладці «Розробник», вибрати ім`я макроса і натиснути «Виконати».

Метод 2 з 2:

Для зовнішніх даних (MySQL, Postgres, Oracle і так далі)

1. скачайте плагін Kloudio. З його допомогою вашу таблицю Excel можна з`єднати із зовнішнім базою даних або джерелом даних. Цей плагін також працює з Google таблицями.

2. Cоедініте свою таблицю із зовнішнім джерелом даних. Для цього натисніть кнопку «+» на порталі Kloudio. Введіть інформацію про свою базі даних (тип бази даних, облікові дані) і виберіть параметри безпеки / шифрування, якщо працюєте з конфіденційними даними або даними компанії.

3. Коли ваша таблиця буде з`єднана з базою даних, запитувати зовнішні дані і створювати звіти можна буде безпосередньо в Excel. Створіть звіт на порталі Kloudio, а потім виберіть його в меню Excel. Потім застосуєте додаткові фільтри та задайте періодичність оновлення звіту (щоб таблиця автоматично оновлювалася щотижня, день або навіть годину).

4. Більш того, вводити дані можна в підключену таблицю і оновлювати дані в зовнішньому джерелі даних. Створіть шаблон завантаження на порталі Kloudio і отримаєте можливість вручну або автоматично завантажувати зміни, зроблені у вашій електронній таблиці, у зовнішній джерело даних.

Поради

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

попередження

У макрос додадуться абсолютно всі дії, які ви виконаєте в режимі запису. Тому не вводьте помилкові значення, не запускайте непотрібні програми і не видаляйте файли.
Макроси можуть бути шкідливими (наприклад, створені, щоб видаляти файли на комп`ютері). Не заводьте макроси, які отримані з ненадійних джерел.