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

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

Кроки

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

1. 

   1

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

2. 

   2

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

3. 

   3

   Натисніть клавіші Alt і F11. Відкриється редактор Microsoft Visual Basic.

4. 

   4

   У лівому верхньому куті на панелі "Project-VBA Project" двічі клацніть на "ThisWorkbook". У головній секції редактора відкриється вікно з вихідним кодом.

5. 

   5

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

6. 

   6

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

7. 7

   Напишіть код для кожного поля введення в таблиці. Для кожного поля введення ви напишете два рядки коду.
   • Перший рядок коду виглядає так: "Range (" cellname "). Select", де "cellname" являє собою осередок, у якому буде зберігатися вміст поля введення. Це повинна бути комірка праворуч від надпісі- якщо текстова напис розташована в комірці A2, то вам слід розташувати полі введення в комірці B2 (Range ("B2"). Select). Ім`я осередку укладіть в лапки, але не укладайте в лапки саму рядок коду.

     

   • Другий рядок коду виглядає так: "ActiveCell.Value = InputBox (" InputPrompt ")", де "InputPrompt" являє собою текст, який буде підказувати користувачу, який тип даних необхідно ввести. Наприклад, якщо комірка для введення даних потрібна для підрахунку вартості обіду, замініть "InputPrompt" на "Введіть повну вартість обіду, включаючи чайові." (Укладіть в лапки текст підказки, але потрібно брати в лапки всю команду.)

     

8. 

   8

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

9. 

   9

   Додайте рядок коду, щоб зберегти інтерактивну таблицю. Її формат наступний: "ActiveWorkbook.SaveAs Filename: =" Filename.xls "," де "Filename" представляє собою назву вашої інтерактивної таблиці. (Залиште лапки навколо "Filename.xls," але не потрібно брати в лапки весь рядок.)
   • Якщо у вас версія Excel 2007 або пізніша, ви можете замінити ".xls" на ".xlsx", але тоді деякі користувачі вашої інтерактивної таблиці, у яких встановлено Excel 2003 або більш ранній, не зможуть користуватися вашою таблицею без спеціальної підключається програми для читання.

10. 10

    Натисніть клавіші Alt і Q. Вікно редактора Visual Basic закриється.

11. 

    11

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

12. 

    12

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

13. 

    13

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





14. 

    14

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

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

1. 1

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

2. 2

   Напишіть скрипт на Visual Basic для відкриття і закриття звіту. Ваш скрипт повинен виконувати функції, перераховані нижче. Буде дано опис кожної функції і в дужках вказано код для їх реалізації. Коли ви будете писати код, пишіть його одним блоком, замініть найменування з прикладу на свої власні найменування і не включайте дужки, зазначені в прикладі.
   • Відкрийте таблицю в режимі тільки для читання. [DIM XLAppSet XLApp = CreateObject ("Excel.App") xlapp.visible = falsexlapp.workbooks.open excelloc filename.xls, 3,]
   • Оновлення дані і збережіть звіт, в цьому прикладі у вигляді PDF з міткою часу. [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, pdfloc reportname_ DatePart ("yyyy, Now ()) "-" Right ("0" DatePart ("m", Now ()), 2) "-" Right ("0" DatePart ("d", Now ()), 2) ".pdf"] Якщо ваш документ буде в іншому форматі, замініть розширення в прикладі з ".pdf" на потрібне вам розширення.
   • Закрийте таблицю, що не зберігаючи її, потім закрийте Excel. [XlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
   • Використовуйте ".xlsx" замість ".xls" як розширення таблиці, якщо вона була збережена в Excel 2007 або пізніших форматах на основі XML.

3. 3

   Напишіть bat-файл, який буде запускати скрипт на Visual Basic. Це потрібно зробити, щоб скрипт на Visual запускався автоматично. Без bat-файлу скрипт на VB доведеться запускати вручну.
   • Ваш скрипт повинен бути в цьому форматі, зазначені папку та ім`я файлу замініть на ваші власні і не включайте дужки: [cscript / nologo fileloc script.vbs]

4. 4

   Напишіть 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) ]

5. 5

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

6. 6

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

Поради

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