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

2 методика:Создание интерактивной электронной таблицыАвтоматизация генерации отчетов

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

Шаги

Метод 1 из 2: Создание интерактивной электронной таблицы

1. 1 Решите, как будет выглядеть ваша таблица. Таблицу следует размещать так, чтобы другие люди могли легко найти поля, в которые нужно вводить данные.
2. Вы можете расположить таблицу горизонтально или вертикально. Большинство пользователей полагает, что легче работать с вертикальными таблицами, особенно если вы хотите их потом распечатывать.
3. 2 Создайте текстовые надписи в таблице. Вам следует добавить наименования столбцов, а также добавить надписи слева от ячеек, в которых будут располагаться поля ввода данных.
4. 3 Одновременно нажмите клавиши Alt и F11. Откроется редактор Microsoft Visual Basic.
5. 4 В левом верхнем углу на панели "Project-VBA Project" дважды щелкните на "ThisWorkbook". В главной секции редактора откроется окно с исходным кодом.
6. 5 В меню "Вставка" выберите пункт "Процедура". Откроется диалоговое окно "Добавить процедуру".
7. 6 В поле имени введите название процедуры. Дайте процедуре осмысленное название, например "SumExpenses", если таблица будет использоваться для отчетности по расходам на поездки. Нажмите OK, чтобы закрыть диалоговое окно.
8. Название процедуры не может содержать пробелы, но вместо пробелов вы можете использовать символ подчеркивания ( _ ).
9. После закрытия окна "Добавить процедуру" вы увидите строку с надписью "Public Sub" и именем процедуры. Под этой строкой будет свободное пространство и надпись "End Sub."
10. 7 Напишите код для каждого поля ввода в таблице. Для каждого поля ввода вы напишете две строки кода.
11. Первая строка кода выглядит так: "Range("cellname").Select", где "cellname" представляет собой ячейку, в которую будет сохраняться содержимое поля ввода. Это должна быть ячейка справа от надписи; если текстовая надпись расположена в ячейке A2, то вам следует расположить поле ввода в ячейке B2 (Range("B2").Select). Имя ячейки заключите в кавычки, но не заключайте в кавычки саму строку кода.
12. Вторая строка кода выглядит так: "ActiveCell.Value = InputBox("InputPrompt")", где "InputPrompt" представляет собой текст, который будет подсказывать пользователю, какой тип данных необходимо ввести. Например, если ячейка для ввода данных нужна для подсчета стоимости обеда, замените "InputPrompt" на "Введите полную стоимость обеда, включая чаевые." (Заключите в кавычки текст подсказки, но нужно заключать в кавычки всю команду.)
13. 8 Напишите код для каждого вычисляемого поля. Вы можете использовать две строки, описанные выше, но в этот раз ActiveCell.Value - это результат вычисления или численная функция, например SUM, вместо функции InputBox, которая использовалась выше для вывода подсказки.
14. 9 Добавьте строку кода, чтобы сохранить интерактивную таблицу. Ее формат следующий: "ActiveWorkbook.SaveAs Filename:="Filename.xls"," где "Filename" представляет собой название вашей интерактивной таблицы. (Оставьте кавычки вокруг "Filename.xls," но не нужно заключать в кавычки всю строку.)
15. Если у вас версия Excel 2007 или более поздняя, вы можете заменить ".xls" на ".xlsx", но тогда некоторые пользователи вашей интерактивной таблицы, у которых установлен Excel 2003 или более ранний, не смогут пользоваться вашей таблицей без специальной подключаемой программы для чтения.
16. 10 Одновременно нажмите клавиши Alt и Q. Окно редактора Visual Basic закроется.
17. 11 Одновременно нажмите клавиши Alt и F8. Откроется диалоговое окно "Макросы".
18. 12 Нажмите на название вашей процедуры в списке макросов. Если в списке только ваша процедура, она будет выбрана автоматически.
19. 13 Нажмите на кнопку Опции. Вам будет предложено ввести букву, которая будет использоваться в сочетании с клавишей Ctrl как горячая комбинация клавиш. Выберите букву, которая не используется в качестве горячей клавиши, например "з" для "записи."
20. 14 Нажмите "OK", чтобы закрыть диалоговое окно Макросов. Теперь вы можете распространять вашу интерактивную таблицу среди тех, кто будет ей пользоваться. После ее открытия они смогут использовать горячую клавишу для добавления записей и следовать вашим подсказкам по заполнению данных.

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

1. 1 Сведите ваш отчет в сводную таблицу. Сводные таблицы предназначены для обобщения данных, чтобы помочь вам сравнивать числа и выявлять тенденции. Ваша сводная таблица должна быть связана с какими-либо данными из таблицы или импортированными из базы данных.
2. 2 Напишите скрипт на Visual Basic для открытия и закрытия отчета. Ваш скрипт должен выполнять функции, перечисленные ниже. Будет дано описание каждой функции и в скобках указан код для их реализации. Когда вы будете писать код, пишите его одним блоком, замените наименования из примера на свои собственные наименования и не включайте скобки, указанные в примере.
3. Откройте таблицу в режиме только для чтения. [DIM XLAppSet XLApp = CreateObject("Excel.App")xlapp.visible=falsexlapp.workbooks.open \excellocilename.xls,3,]
4. Обновите данные и сохраните отчет, в этом примере в виде PDF с меткой времени. [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, \pdfloc eportname_ & DatePart("yyyy,Now()) & "-" & Right("0" & DatePart("m",Now()),2) & "-" Right("0" & DatePart("d",Now()),2) & ".pdf"] Если ваш документ будет в другом формате, замените расширение в примере с ".pdf" на нужное вам расширение.
5. Закройте таблицу, не сохраняя ее, затем закройте Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
6. Используйте ".xlsx" вместо ".xls" в качестве расширения таблицы, если она была сохранена в Excel 2007 или более поздних форматах на основе XML.
7. 3 Напишите bat-файл, который будет запускать скрипт на Visual Basic. Это нужно сделать, чтобы скрипт на Visual запускался автоматически. Без bat-файла скрипт на VB придется запускать вручную.
8. Ваш скрипт должен быть в этом формате, указанные папку и имя файла замените на ваши собственные и не включайте скобки: [cscript /nologo \filelocscript.vbs]
9. 4 Напишите bat-файл, который будет проверять получаемый файл после создания. Ваш скрипт должен выполнять описанные ниже функции. После каждой функции в квадратных скобках будет приведен код, который ее реализует. Когда вы будете писать код, пишите его одним блоком, замените наименования из примера на свои собственные наименования и не включайте скобки, указанные в примере.
10. Проверьте, что получаемый файл существует. [For /f "tokens=2-4 delims=/ " %%a in ('date /t') do set rreport=reportname_%%c-%%a-%%b.pdf)] Если формат получаемого файла не PDF, то в примере замените ".pdf" на нужный формат.
11. Если получаемый файл/отчет существует, отправьте его по электронной почте людям, которым необходим этот отчет. [If exist \pdfloc%rreport% ( sendemail -f sender@senderdomain.com -t recipient@recipientdomain.com -u Scheduled Report -m Report %%report% is attached. -a pdfloc%rreport% -s yourserver:port -xu username -xp password)]
12. Если получаемый файл/отчет не существует в указанном месте, у вас должна быть процедура, которая отправляет вам сообщение о том, что создание файла не выполнено. [ Else ( sendemail -f sender@senderdomain.com -t sender@senderdomain.com -u Report did not run -m file %rreport% does not exist in \pdfloc -s yourserver:port -xu username -xp password)]
13. 5 Проверьте, что на компьютере существует папка "Desktop"("Рабочий стол"). Вам надо проверить наличие папки Desktop как на 32-битной, так и на 64-битной системе. Если вы это не сделаете, Excel и вашу таблицу придется открывать вручную.
14. Расположение в 32-битной системе: c:windowssystem32configsystemprofile
15. Расположение в 64-битной системе: c:windowssyswow64configsystemprofile
16. 6 Запланируйте задачу, которая будет по мере надобности запускать скрипт. Bat-файл должен запускаться периодически на постоянной основе, вне зависимости от того, использует кто-нибудь компьютер или нет. При этом для задачи должны быть установлены максимально возможные привилегии.

Советы

• Запланированные задачи лучше всего запускать с сервера, обычно от имени системного аккаунта с правами администратора. Единственным неудобством запуска задач от имени системного аккаунта является отсутствие пользовательского интерфейса; однако, автоматические задачи обычно подразумевают запуск в фоновом режиме без оповещений пользователя.