Дебет-Кредит
Украинский бухгалтерский еженедельник
#27 '2001: Практичная бухгалтерия - Автоматизация бухучета: Excel

Автоматизация бухучета:
Бухгалтерский учёт с помощью Excel.
14-й урок

Заполнение, учет и хранение платежных документов с помощью EXCEL

Владимир ЛАВРЕНОВ


Предыдущие уроки "Автоматизации бухучёта: Excel"

Кнопкой - щелк, один момент... бери готовый документ

Ну как, вы оценили преимущество системы подготовки платежного требования-поручения с помощью переключателей режима? Но и этот вариант не исключает полностью ситуацию, когда пользователь случайно включит одновременно два переключателя или не включит ни одного. Конечно, можно ввести в ячейки документа более сложные формулы, чтобы избежать некорректного заполнения документа. Однако предлагаю вам ознакомиться с более изящным решением этой задачи, которое станет введением в интересный мир макросов.

Этот сложный простой макрос

У непосвященного читателя упоминание слова "макрос" вызывает если не трепет, так ощущение неуверенности. Очень часто слово "макрос" ассоциируется с программными вирусами. С таким сообщением, которое показано на рисунке 1, вы регулярно встречаетесь при запуске в Excel вашей бухгалтерской программы (если у вас на вкладке "Общие" меню "Сервис" - "Параметры" активизирована опция "Защита от макровирусов"). Пока вы не записали ни одного макроса, можно не задумываясь нажимать кнопку "Отключить".

Рисунок 1

Мы же будем иметь дело с полезными макросами. Для чего они нужны бухгалтеру, создающему свою программу? Прежде всего, для замены рутинных, часто повторяющихся операций по ведению бухучета простыми, "в одно касание", действиями. Работая с ПК, вы привыкли выполнять некоторые операции нажатием на нужную кнопку.

Мы не задумываемся, что каждое такое нажатие запускает небольшую программу под названием "макрос". Именно с помощью таких программок вы сможете значительно ускорить свою ежедневную работу. Не волнуйтесь, вам не придется углубляться в дебри программирования, основную работу за вас сделает Excel. Вам достаточно будет один раз правильно выполнить нужную последовательность действий - и Excel с абсолютной точностью ее повторит. А теперь приступаем.

Что скрывается под кнопкой

Не имея даже приблизительного представления о программировании, с использованием возможностей Excel можно записать довольно сложную процедуру. Все, что для этого необходимо, - открыть окно "Запись макроса" (меню "Сервис" - "Макрос"), нажать кнопку ОК (рис. 2) и повторить те действия в вашей Excel-евской рабочей книге, которые вы хотите автоматизировать.

Рисунок 2

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

Наша задача сегодня - обеспечить автоматическое переключение режима создания требования-поручения. До сих пор вы это делали, занося какой-нибудь символ в ячейку W5 или W7. Эти "переключатели" останутся, однако управлять ими станет намного проще. Чтобы вы сразу представили, какой вид примет ваш рабочий лист, взгляните на рисунок 3.

Рисунок 3

Справа от помещенной на листе формы документа разместится блок кнопочных переключателей. Итак, на листе "Требование" должны появляться три варианта представления документа:

- заполнена только верхняя часть документа для отправки плательщику (режим - "для требования");

- заполнена верхняя и нижняя части документа для оплаты по требованию (режим - "для поручения"). Этот режим может использоваться плательщиком для хранения полностью оформленного документа;

- на листе "Требование" остаются заполненными только реквизиты и только нижней части требования-поручения (режим "для печати поручения". Не забывайте, что речь идет не о платежном поручении). На листе остаются значения только четырех реквизитов нижней части, которые могут быть впечатаны в полученный документ. Этот режим используется для компьютерного заполнения полученного требования (впечатывание реквизитов в нижнюю часть документа).

Записанный макрос можно будет запускать различными способами:

- из меню "Макрос" (рис. 2);

- из Excel-евского редактора Visual Basic (VBA);

- с помощью кнопки, рисованного объекта или другого элемента управления графического объекта.

Для переключателей режимов лучше выбрать один из видов управляющих элементов - знакомую вам кнопку.

Как записать макрос

Полный выбор управляющих элементов, которые используются во всех программах Microsoft, содержится на панели "Формы" (меню "Вид" - "Панели инструментов", рис. 4).

Рисунок 4

Щелкните на элементе "кнопка" в панели "Формы" и, переведя курсор на рабочее поле листа, растяните курсор до нужного размера. Рамка превратится в объемную кнопку с надписью на ней "Кнопка1" (порядковый номер может быть и другим), и одновременно появится окно "Назначить макрос объекту", в котором нажмите "Отмена" (рис. 6).

Положение кнопки на листе и ее размер для управления макросом не имеет принципиального значения, поэтому ее предварительно можно оставить в любом месте. Скопируйте "Кнопку1" и вставьте под ней еще две кнопки.

Первую "Кнопку1" переименуйте и назовите "для вимоги", вторую назовите "для доручення", третью - "для друку доручення" (рис. 5).

Рисунок 5

Внимание! Для входа в режим редактирования имени нужно дважды щелкнуть на кнопке. Для выхода - щелкните вне поля кнопки.

Первый макрос будет записан для третьего варианта представления требования-поручения. Для запуска этого макроса будет использоваться кнопка "для друку доручення". Почему мы начинаем с последнего макроса, вы поймете позже.

Как было сказано, при записи макроса важно придерживаться четкой последовательности действий, исключать ненужные повторения. Поэтому не спешите щелкать мышкой: каждая ваша операция будет записана в макросе. Итак, начинаем запись.

Шаг 1. Щелкните ПКМ на кнопке "для друку доручення" и в контекстном меню выберите команду "Назначить макрос".

Шаг 2. В открывшемся окне (рис. 6) введите имя макроса. По умолчанию вам будет предложено имя типа "Кнопка31_Щелкнуть". Можно оставить это имя без изменения, но лучше присвоить имя, которое будет характеризовать производимое кнопкой действие (например "Для_друку_доручення").

Рисунок 6

Внимание! Имя макроса не должно содержать пробелов.

Нажмите на кнопку "Записать...".

Шаг 3. В окне "Запись макроса" вы можете назначить для запуска данного макроса сочетание "горячих" клавиш. В этом случае нужно ввести букву англоязычной клавиатуры. В поле "Описание" можно ввести дополнительные сведения об автоматизируемой операции. После нажатия ОК на рабочем листе появится панель "Останов записи макроса", с помощью которой можно прекратить запись в любой момент. Теперь каждая операция на листе будет записана в макрос.

Шаг 4. Щелкните ПКМ на кнопке "для вимоги" и в контекстном меню выберите команду "Формат объекта" (рис. 7). В открывшемся окне "Формат элемента управления" установите следующие форматы для надписи кнопки: размер шрифта - 10, написание - "обычный", цвет - "белый". Нажмите кнопку ОК. Эти параметры могут быть другими в зависимости от ваших цветовых пристрастий. Учтите только, что эти установки будут определять неактивную кнопку.

Рисунок 7

Внимание! Не используйте для редактирования формата текста кнопки панели инструментов, так как при записи макроса запоминается действие, а не его результат. Например, кнопка "Ж" в главном меню может быть нажата перед записью, и если щелкнуть на ней, будет записан именно щелчок. При выполнении записанного макроса, если предварительно кнопка "Ж" отпущена, будет воспроизведена команда "щелкнуть" и выполнится команда "форматировать полужирным".

Шаг 5. Щелкните ПКМ на кнопке "для доручення" и повторите те же действия.

Шаг 6. Щелкните ПКМ на кнопке "для друку доручення". Войдите в режим редактирования текста (аналогично предыдущим шагам) и установите такие параметры: размер шрифта - 12, написание - "полужирный", цвет - "Авто". Это будут параметры активной кнопки.

Шаг 7. Щелкните ЛКМ на ячейке W5 и удалите ее содержимое (нажмите клавишу Del).

Эта операция обязательна, независимо от того, записан в ячейке символ (переключатель режима) или нет.

Шаг 8. Щелкните ЛКМ на ячейке W7 и нажмите клавишу Del (эта операция обязательна, независимо от того, был ли предварительно записан в ячейке символ или нет). Введите в ячейку W7 любой печатный символ (например, х).

Шаг 9. Выделите ячейки А1:Т39 (границы требования-поручения), откройте меню "Цвет шрифта" и установите в палитре белый цвет (рис. 8). Не снимая выделения, откройте меню "Границы" и выберите вариант "без границ".

Рисунок 8

Шаг 10. Выделите ячейки F26:K27 (зона для записи суммы словами), M26:Q27, M32:Q33 (расчетные счета плательщика и получателя), R26:S27 (сумма платежа), откройте меню "Цвет шрифта" и установите цвет "Авто". При выделении этих ячеек держите клавишу Ctrl нажатой.

Шаг 11. Щелкните на любой ячейке рабочего листа (например, G2) и остановите запись макроса (на панели "Останов записи макроса" нажмите кнопку с черным квадратом).

Можете себя поздравить - вы записали свой первый макрос. Теперь, если к кнопке "для друку доручення" подвести курсор, он из крестика превратится в указательный перст.

Итак, кнопка "для друку доручення" ожила. Теперь при нажатии на нее, независимо от режима, в котором находился документ (заполнен "для требования" или "для поручения"), останутся данные, которые будут впечатаны в полученное требование-поручение.

Последовательность шагов при записи макросов "для требования" и "для поручения" будет подобна описанной выше. Основное отличие заключается в том, что дополнительно потребуются шаги для восстановления графики формы документа. Как видно на рисунке 3, на поле документа в режиме "для печати поручения" отсутствуют рамки реквизитов.

Макрос "для поручения"

Чтобы не повторяться, приведу только отличия при его записи от приведенной выше последовательности действий (описания всех вспомогательных одинаковых действий опущены). Использованные сокращения:

- "то же" - из предыдущего макроса;

- "повторите действия" - из текущего макроса.

Шаг 1. Щелкните ПКМ на кнопке "для доручення" и дальше то же.

Шаг 2. Назначьте имя макросу. Например, "Для_доручення".

Шаг 3. То же.

Шаг 4. То же.

Шаг 5. Щелкните ПКМ на кнопке "для друку доручення" и повторите действия Шага 4.

Шаг 6. Щелкните ПКМ на кнопке "для доручення" и дальше то же.

Шаг 7. То же.

Шаг 8. То же.

Шаг 9. Выделите ячейки А1:Т39, откройте меню "Цвет шрифта" и установите в палитре цвет "Авто".

Шаг 10. Выделите ячейки C7:E8, I9:K10, M9:Q10, R9:S17, C14:E15, M14:Q15, I16:K17, M16:Q17, M26:Q27, R26:S33, M30:Q31, M32:Q33 (если ячейки этих диапазонов объединить, то их выделение значительно ускорится, см. рис. 9). Откройте меню "Границы" и выберите вариант.

Рисунок 9

Шаг 11. Выделите ячейки R2:S2, R19:S20, B23:B24 (место печати) и выберите вариант "Границы".

Шаг 12. Выделите ячейки G2:I2, B17:H17, B21:P21, F23, F24, F37, F39 и выберите вариант "Границы".

Шаг 13. Щелкните на ячейке G2 и остановите запись макроса, щелкнув на.

Макрос "для требования"

Осталось записать макрос для последней кнопки "для требования".

В принципе, все шаги при его записи повторяют запись макроса "Для_доручення". Отличие состоит только в форматировании названия кнопок и замене переключателей в ячейках W5 и W7. Тем не менее, для корректного переключения режима подготовки документа необходимо записать макрос, выполнив все приведенные ниже шаги.

Шаг 1. Щелкните ПКМ на кнопке "для вимоги" и дальше как в макросе "Для_доручення".

Шаг 2. Назначьте имя макросу. Например - "Для_вимоги".

Шаг 3. Как в макросе "Для_доручення".

Шаг 4. Щелкните ПКМ на кнопке "для доручення" и дальше как в макросе "Для_доручення".

Шаг 5. Щелкните ПКМ на кнопке "для друку доручення" и дальше повторите действия шага 4.

Шаг 6. Щелкните ПКМ на кнопке "для вимоги". Войдите в режим редактирования текста (аналогично предыдущим шагам) и установите такие параметры: размер шрифта - 12, написание - "полужирный", цвет - "Авто". Это будут параметры активной кнопки.

Шаг 7. Щелкните ЛКМ на ячейке W5 и нажмите клавишу Del (эта операция обязательна независимо от того, был ли предварительно записан в ячейке символ или нет). Введите в ячейку W5 любой печатный символ (например, х).

Шаг 8. Щелкните ЛКМ на ячейке W7 и удалите ее содержимое, нажав клавишу Del. Эта операция обязательна, независимо от того записан в ячейке символ (переключатель режима) или нет.

Шаг 9. Как в макросе "Для_доручення".

Шаг 10. Как в макросе "Для_доручення".

Шаг 11. Как в макросе "Для_доручення".

Шаг 12. Как в макросе "Для_доручення".

Шаг 13. Щелкните на ячейке G2 и остановите запись макроса.

Вот вы и создали первые графические элементы автоматического управления операциями. Теперь, даже если вы нарушите графическую форму документа, ее можно будет мгновенно восстановить одним нажатием кнопки.

Центр управления требования-поручения

Созданные кнопки-переключатели лучше разместить в удобном месте листа. Предварительно установите необходимый размер для кнопки, для чего щелкните на кнопке ПКМ и растяните рамку кнопки. Не выходя из режима редактирования, переместите кнопки в зону, показанную на рисунке 10. То же самое повторите и с другими кнопками. Для установки одинакового размера для всех переключателей воспользуйтесь вкладкой "Размер" окна "Формат элементов управления" (рис. 10).

Рисунок 10

Можно усилить информативность блока управления, создав своеобразную панель (рис. 10). Для того, чтобы сообщение в панели менялось в зависимости от готовности требования-поручения для обработки (в ячейку G2 должен быть введен номер документа), запишите в ячейку V2 такую формулу:

а затем объедините ячейки V2:X3.

Сравните то, что у вас получилось, с показанным на рисунке 3, и можете работать.

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

=ЕСЛИ(ЕПУСТО(G2);"Платіжна вимога-доручення не підготовлена";
"Платіжна вимога-доручення"&" №"&G2&" підготовлена"),


Исправление ошибки

В 12-м уроке ("ДК" №22) были допущены ошибки в формулах, введенных в блок "Сумма прописью". При записи формулы в ячейку AG6 необходимо ввести дополнительное условие: "ЕСЛИ(Z6=1;"";". Формула должна иметь вид:

=ЕСЛИ(И(Q6>0;Q6<1);"Нуль";ЕСЛИ(Z6=1;"";ЕСЛИ(AA6>0;ВПР(AA6;Табло;2;ЛОЖЬ);""))).

В ячейке AP6 должно быть еще одно условие, выделенное фоном:

=ЕСЛИ(Q6-ЦЕЛОЕ(Q6)=0;"грн 00 коп.";ЕСЛИ(AG6="Нуль";"гривень "&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&" коп.";
ЕСЛИ(ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)<10;"грн "&"0"&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&" коп.";"грн "&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&" коп.")))

После корректировки формулы не забудьте скопировать отредактированные формулы в остальные ячейки таблицы.

Внимание! Файл с материалами 12-го урока исправлен.

Следующий урок

© 2001
"Дебет-Кредит"
Редакция: debet-kredit@gc.kiev.ua