Як автоматизувати звіти в excel

Однією з багатьох особливостей Microsoft Excel є його здатність автоматизувати звіти. Ви можете створювати інтерактивні електронні таблиці для спрощення можливості введення даних в ваші книги Excel іншими людьми, і ви також можете автоматизувати генерацію звітів. Обидві ці функції вимагають деяких знань Visual Basic. Кроки для виконання обох завдань описані нижче.

кроки Правити

Метод 1 з 2:
Створення інтерактивної електронної таблиці Правити

Вирішіть, як буде виглядати ваша таблиця. Таблицю слід розміщувати так, щоб інші люди могли легко знайти поля, в які потрібно вводити дані.

• Ви можете розташувати таблицю горизонтально або вертикально. Більшість користувачів вважає, що легше працювати з вертикальними таблицями, особливо якщо ви хочете їх потім роздруковувати.

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

В меню "Вставка" виберіть пункт "Процедура". Відкриється діалогове вікно "Додати процедуру".

В поле імені введіть назву процедури. Дайте процедурі осмислену назву, наприклад "SumExpenses", якщо таблиця буде використовуватися для звітності за витратами на поїздки. Натисніть OK, щоб закрити діалогове вікно.

• Назва процедури не може містити пробіли, але замість пробілів ви можете використовувати символ підкреслення (_).
• Після закриття вікна "Додати процедуру" ви побачите рядок з написом "Public Sub" і ім'ям процедури. Під цим рядком буде вільний простір і напис "End Sub."

Напишіть код для кожного поля введення в таблиці. Для кожного поля введення ви напишете два рядки коду.

• Перший рядок коду виглядає так: "Range (" cellname "). Select", де "cellname" являє собою осередок, в яку буде зберігатися вміст поля введення. Це повинна бути комірка праворуч від напису; якщо текстова напис розташована в осередку A2, то вам слід розташувати поле введення в осередку B2 (Range ( "B2"). Select). Ім'я осередку укладіть в лапки, але не укладайте в лапки саму рядок коду.
• Другий рядок коду виглядає так: "ActiveCell.Value = InputBox (" InputPrompt ")", де "InputPrompt" являє собою текст, який буде підказувати користувачу, який тип даних необхідно ввести. Наприклад, якщо комірка для введення даних потрібна для підрахунку вартості обіду, замініть "InputPrompt" на "Введіть повну вартість обіду, включаючи чайові." (Укладіть в лапки текст підказки, але потрібно брати в лапки всю команду.)

Напишіть код для кожного обчислюваного поля. Ви можете використовувати два рядки, описані вище, але в цей раз ActiveCell.Value - це результат обчислення або чисельна функція, наприклад SUM, замість функції InputBox, яка використовувалася вище для виведення підказки.

Одночасно натисніть клавіші Alt і F8. Відкриється діалогове вікно "Макрос".

Натисніть на назву вашої процедури в списку макросів. Якщо в списку тільки ваша процедура, вона буде обрана автоматично.

Натисніть на кнопку Опції. Вам буде запропоновано ввести букву, яка буде використовуватися в поєднанні з клавішею Ctrl як гаряча комбінація клавіш. Виберіть букву, яка не використовується в якості гарячої клавіші, наприклад "з" для "запису."

Натисніть "OK", щоб закрити діалогове вікно Макрос. Тепер ви можете поширювати вашу інтерактивну таблицю серед тих, хто буде нею користуватися. Після її відкриття вони зможуть використовувати гарячу клавішу для додавання записів і слідувати вашим підказкам щодо заповнення даних.

Метод 2 з 2:
Автоматизація генерації звітів Правити

Зведіть ваш звіт в зведену таблицю. Зведені таблиці призначені для узагальнення даних, щоб допомогти вам порівнювати числа і виявляти тенденції. Ваша зведена таблиця повинна бути пов'язана з будь-якими даними з таблиці або імпортованими з бази даних.

Напишіть bat-файл, який буде запускати скрипт на Visual Basic. Це потрібно зробити, щоб скрипт на Visual запускався автоматично. Без bat-файлу скрипт на VB доведеться запускати вручну.

• Ваш скрипт повинен бути в цьому форматі, зазначені папку й ім'я файлу замініть на ваші власні і не включайте дужки: [cscript / nologo \\ fileloc \ script.vbs]

Напишіть bat-файл, який буде перевіряти одержуваний файл після створення. Ваш скрипт повинен виконувати описані нижче функції. Після кожної функції в квадратних дужках буде приведений код, який її реалізує. Коли ви будете писати код, пишіть його одним блоком, замініть найменування з прикладу на свої власні назви і не включайте дужки, зазначені в прикладі.

• Перевірте, що отримується файл існує. [For / f "tokens = 2-4 delims = /" %% a in ( 'date / t') do set rreport = reportname _ %% c - %% a - %% b.pdf)] Якщо формат одержуваного файлу не PDF, то в прикладі замініть ".pdf" на потрібний формат.
• Якщо одержуваний файл / звіт існує, відправте його по електронній пошті людям, яким необхідний цей звіт. [If exist \\ pdfloc \% rreport% (sendemail -f [email protected] -t [email protected] -u Scheduled Report -m Report %% report% is attached. -a \ pdfloc \% rreport% -s yourserver: port -xu username -xp password)]
• Якщо одержуваний файл / звіт не існує в зазначеному місці, у вас повинна бути процедура, яка відправляє вам повідомлення про те, що створення файлу не виконано. [Else (sendemail -f [email protected] -t [email protected] -u Report did not run -m file% rreport% does not exist in \\ pdfloc \ -s yourserver: port -xu username -xp password) ]

Перевірте, що на комп'ютері існує папка "Desktop" ( "Робочий стіл"). Вам треба перевірити наявність папки Desktop як на 32-бітної, так і на 64-бітної системі. Якщо ви це не зробите, Excel і вашу таблицю доведеться відкривати вручну.

• Розташування в 32-бітної системі: c: \ windows \ system32 \ config \ systemprofile
• Розташування в 64-бітної системі: c: \ windows \ syswow64 \ config \ systemprofile

Заплануйте завдання, яка буде в міру потреби запускати скрипт. Bat-файл повинен запускатися періодично на постійній основі, незалежно від того, використовує хтось комп'ютер чи ні. При цьому для завдання повинні бути встановлені гранично допустимі привілеї.