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

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

Автоматизация составления финансовой отчетности малого предприятия

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


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

Вы уже, наверное, воспользовались созданными в Excel отчетными формами и поняли, что нет необходимости их покупать. Созданный на компьютере документ, без сомнения, обладает огромным преимуществом перед печатным аналогом, заполненным вручную. Сегодня мы начинаем следующий этап автоматизации бухгалтерского учета в Excel. На очереди - составление Журнала хозяйственных операций и Главной книги.

Форма "Журнал хозяйственных операций"

В книге "Баланс" создайте лист "ЖурналОп" (не пытайтесь ввести имя "Журнал", ничего не выйдет - оно зарезервировано программой). На этом листе создайте Журнал хозяйственных операций, форма которого может быть подобной приведенной на рис. 1. Значение всех граф, за исключением графы "Перевірка", понятно из их названий. А вот в графе "Перевірка" будет заложена сервисная функция по проверке правильности ввода бухгалтерской проводки. Способ реализации этой функции будет приведен ниже.

Рисунок 1

Для обеспечения корректности работы создаваемой системы бухгалтерского учета, последовательность граф журнала должна быть такой же, как на рис. 1. Это условие обязательно, так как журнал хозяйственных операций будет играть ключевую роль в автоматизации бухучета с помощью Excel.

Для использования данных, введенных в журнал, как и в ранее созданных листах книги "Баланс", присвоим группам ячеек имена. Напомню, что эта операция нам необходима прежде всего для того, чтобы автоматизировать заполнение других документов, создаваемых на основе первичных данных.

Присвоение имен

На листе "ЖурналОп" имена будут присвоены четырем интервалам ячеек и двум позиционирующим ячейкам.

Сразу обращаю ваше внимание на то, что числовые данные, внесенные в журнал, будут обрабатываться формулами массива, введенными в другие документы. Учитывая это обстоятельство, напоминаю основное требование к ссылкам в именах ячеек, расположенных на одном листе:

интервалы ячеек должны иметь одинаковую длину, а первые ячейки этих интервалов должны располагаться на одной и той же строке листа.

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

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

Внимание! Если количество внесенных в журнал проводок превысит длину интервала, которому присвоено имя, придется соответственно скорректировать ссылки в формулах всех интервалов ячеек, которым присвоены имена на листе "ЖурналОп".

Итак, решили - ячейкам, в которые будут вноситься даты операций (проводок), присвоим имя "ДатаОпер". Для этого выделите интервал ячеек B6:B106 и в поле имен введите имя "ДатаОпер" (рис. 1).

Второму интервалу ячеек графы "Дебет" присвоим имя "ДебетЖ". Безусловно, и в этом случае можно применить прежний способ присвоения имени. Однако, учитывая, что такие действия необходимо будет повторить еще трижды, предлагаю использовать более простой и надежный способ, состоящий из нескольких шагов.

Первый шаг. После присвоения имени "ДатаОпер" интервал ячеек у вас на листе остался выделенным (если вы уже сняли выделение, в списке имен найдите имя "ДатаОпер" и щелкните на нем ЛКМ).

Второй шаг. В меню "Вставка", используя команды "Имя" - "Присвоить", откройте окно "Присвоение имени". Введите в верхнее поле имя "ДебетЖ" (рис. 2).

Рисунок 2

Третий шаг. В поле "Формула" вы увидите записанную формулу "=ЖурналОп!$B$6:$B$106", являющуюся ссылкой на имя "ДатаОпер". Так как для имен одного листа (которые используются в формуле массива) должно выполняться условие равенства интервалов, отредактируем уже имеющуюся формулу. Для имени "ДебетЖ" она будет такой: "=ЖурналОп!$D$6:$D$106".

Как вы заметили, изменилась только ссылка на столбец, то есть в формуле буквы "B" заменены на "D" (графа "Дебет" расположена в столбце "D").

Внимание! Для внесения исправлений в формулу курсор перемещайте только с помощью "мышки".

Так надо поступать потому, что использование клавиш стрелок клавиатуры перемещает курсор в другую ячейку, ссылка на которую будет немедленно занесена в редактируемую формулу. То есть такое редактирование приведет к некорректной записи формулы. Поэтому установите с помощью "мышки" курсор в формуле перед буквой "B", нажмите клавишу Del

и введите букву "D". Таким же способом введите и вторую букву "D".

Четвертый шаг. В окне "Присвоение имени" нажмите кнопку "Добавить". Если после этого вы не нажмете кнопку "ОК", окно "Присвоение имени" не закроется, и можно будет таким же способом вводить другие имена.

Чтобы присвоить имена другим интервалам, установите курсор в поле "Имя" и присвойте имена:

"КредитЖ" - интервалу ячеек из графы "Кредит", имеющему формулу ссылки "=ЖурналОп!$Е$6:$Е$106";

"СуммаОпер" - интервалу ячеек из графы "Сума операції", имеющему формулу ссылки "=ЖурналОп!$G$6:$G$106".

Осталось создать две "позиционирующих" ячейки: "ДебетЖТек" и "КредитЖТек".

Методика создания таких ячеек нами уже рассматривалась.

Однако, чтобы не было ошибок, вкратце повторим последовательность шагов.

1. Активизируйте первую ячейку интервала "ДебетЖ" (ячейка D6).

2. В окне "Присвоение имени" введите имя "ДебетЖТек".

3. В поле "Формула" отредактируйте формулу ссылки, удалив в ней второй знак доллара (=ЖурналОп!$D$6). Формула должна быть такой: "=ЖурналОп!$D6".

4. Щелкните ЛКМ на кнопке "Добавить". В поле списка имен появится имя "ДебетЖТек".

5. Установите курсор в поле "Имя" и отредактируйте записанное в нем имя "ДебетЖТек", изменив его на "КредитЖТек".

6. Установите курсор в поле "Формула" перед буквой "D" и отредактируйте записанную в нем ссылку "=ЖурналОп!$D6" на "=ЖурналОп!$E6".

7. Щелкните ЛКМ на кнопке "Добавить". В поле списка имен появится имя "КредитЖТек".

8. Подтвердите правильность ввода имен нажатием "ОК".

Ошибка при вводе имен (типа "КредитЖТек")

Одним из условий правильности вычислений в создаваемой вами автоматизированной системе бухучета является корректное задание ссылки для "позиционирующих" ячеек.

Особенно внимательным надо быть при открытии окна "Присвоение имени".

Вот ситуация, которая может сложиться у вас. Например, на рабочем листе активной ячейкой является В9 (рис. 3). Вы открываете окно "Присвоение имени", и, выбрав в поле имен, например, "КредитЖТек", в поле формул увидите ссылку "=ЖурналОп!$E9" вместо введенной ранее ссылки "=ЖурналОп!$E6".

Не редактируйте ссылку "=ЖурналОп!$E9"!

Если вы измените ее на ссылку "=ЖурналОп!$E6" и нажмете кнопку "ОК", использование в формуле массива "позиционирующей" ячейки станет некорректным. Проявится это следующим образом. Теперь, если активной ячейкой будет, например, ячейка G6, ссылаться она будет не на ячейку Е6, как вы ожидаете, а на ячейку Е3 (рис. 3). То есть адресация по отношению к ячейке-источнику переместится на 3 строки вверх. Это произошло потому, что вы отредактировали ссылку "=ЖурналОп!$E9" при активной ячейке, находящейся на 9-й строке. В результате полностью нарушится позиционирование ячейки, что исказит результаты, получаемые с помощью формул массива.

Рисунок 3

Порядок заполнения Журнала

Перед началом заполнения Журнала установите соответствующие форматы для его граф.

Графа "№ п/п". Напомню, что для простоты нумерации записанных в журнал хозяйственных операций можно ввести в ячейку А7 формулу "=А6+1" и затем копировать ее на нужное число строк. Не забудьте в ячейку А6 записать число 1.

Графа "Дата операції". Ячейки этой графы должны иметь формат "Дата", который устанавливается в окне "Формат ячеек" (рис. 4). Для ввода формата выделите весь столбец B. Представление формата можете выбрать по вкусу, это не имеет принципиального значения.

Графа "Зміст операції". В ячейки этой графы будет вводиться текст, причем объем содержания операций (проводок) будет различным. По умолчанию в Excel текст, введенный в ячейку, если он превышает ее длину, накладывается на соседнюю ячейку. Поэтому для автоматического изменения размера ячейки в зависимости от объема содержания операции в окне "Формат ячеек" на вкладке "Выравнивание" установите опции, показанные на рис. 4.

Графы "Дебет" и "Кредит". В ячейки этих граф будут вводиться коды счетов и субсчетов. Выделите два столбца D и Е и установите для них формат "Числовой" с числом десятичных знаков 0 (рис. 4).

Рисунок 4

Графа "Перевірка". Для ее ячеек установите формат "Общий". Назначение этой графы и ее функционирование будет подробно рассмотрено в отдельном разделе.

Графа "Сума операції". Назначение этой графы понятно из названия. Формат ячеек - числовой, с числом десятичных знаков 2.

При заполнении журнала необходимо придерживаться нескольких простых правил:

- при вводе даты операции (проводки) достаточно в соответствующую ячейку ввести только число и номер месяца, используя между ними разделители: точку "." или косую черту "/". Например, при вводе 2.4, или 02.4, или 2.04, или 02.04 (или, соответственно, 2/4) в ячейке будет записано краткое значение даты в зависимости от выбранного вами формата (02.04.01). По умолчанию значения даты выравниваются в ячейке по правому краю.

Если автоматического распознавания формата даты не происходит, то введенные значения интерпретируются как текст, который выравнивается по левому краю. Значения будут тоже восприняты как текст, если вы введете несуществующую дату (например, 31.9 или в не високосном году 29.2). Кстати, вы можете изменить краткий формат даты 02.04.01 на другой, например, 02.04.2001. Для этого установите соответствующую строку в окне "Свойства: Язык и стандарты" (кнопка "Пуск" - "Настройка" - "Панель управления" - "Язык и стандарты") (рис. 5);

Рисунок 5

- запись проводки осуществляется без использования традиционных букв (Д или К) перед кодом счета (например, для операции, приведенной на рис. 6, в ячейку графы "дебет" необходимо внести 104, а не Д104, а в графу "кредит" - 152, а не К152). Введение в данные ячейки буквы приведет к некорректным вычислениям формулами, использующими эти данные. Желательно также в проводках использовать принятые на предприятии коды субсчетов, а не коды синтетических счетов (лучше 104 и 152, чем 10 и 15) (рис. 6);

Рисунок 6

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

Корректные проводки

Введение нового Плана счетов, безусловно, вызывает определенные трудности в составлении бухгалтерских проводок. Довольно часто даже опытному бухгалтеру приходится сверять правильность использования корреспондирующих счетов, заглядывая в Инструкцию по использованию Плана счетов. Те из читателей, которые вместе с нами ведут бухучет с помощью Excel, могут воспользоваться его возможностями и автоматизировать такой важный момент ведения журнала хозяйственных операций, как проверка введенных проводок на корректность.

Для решения этой задачи создайте в вашей книге "Баланс" еще один лист под названием "ПланСчет". Обладатели специального приложения №2 "Новая бухгалтерия" к еженедельнику "Дебет-Кредит" имеют в своим арсенале вкладыш "Табличная форма корреспонденции счетов". Подобную таблицу мы создадим на листе "ПланСчет" под названием "Кореспондуючі рахунки" (рис. 7).

Рисунок 7

Структура таблицы, приведенная на рисунке, включает:

- вертикальную "шапку", объединяющую дебеты всех счетов (слева);

- горизонтальную "шапку", объединяющую и кредиты всех счетов (сверху);

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

В отличии от приведенной в спецприложении таблицы, таблица "Кореспондуючі рахунки" отличается тем, что в ней использована сплошная нумерация дебетов и кредитов счетов (то есть без пропуска номеров счетов, которые в настоящий момент не используются, - 29, 32, 56 и т. д). Это условие необходимо обязательно выполнить для обеспечения автоматической обработки таблицы.

Внимание! Для однозначной интерпретации введенных формул, обрабатывающих таблицу, ячейка, которая находится на пересечении столбца К10 и строки Д10, должна иметь адрес С5.

Чтобы ускорить процесс создания таблицы, выполните следующие действия:

- введите в ячейку С4 значение К10;

- щелкните ЛКМ на маркере заполнения (правый нижний угол ячейки) и, удерживая ЛКМ нажатой, протащите курсор вправо до тех пор, пока в ячейке не появится значение К99. Созданы так называемые "кредитовые" столбцы;

- введите в ячейку В5 значение Д10;

- щелкните ЛКМ на маркере заполнения и, удерживая ЛКМ нажатой, протащите курсор вниз до тех пор, пока в ячейке не появится значение Д99. Созданы "дебетовые" столбцы.

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

- выделите столбцы С:К (зона кредитов счетов) и в меню "Данные" - "Группа и структура" выберите команду "Группировать". После этого появятся знакомые вам символы структуры с кнопкой со знаком минус "-" над столбцом L (если будет выделен и столбец L, то в структурированный первый блок-класс "Необоротні активи" будет включен столбец М, относящийся к блоку "Запаси"). Последовательно выделяйте блоки из 9 столбцов (М:U и т. д.) и используйте команду "Группировать";

- выделите столбцы 5:13 (зона дебетов счетов) и в меню "Данные" - "Группа и структура" выберите команду "Группировать". После этого появятся символы структуры с кнопкой со знаком минус "-" слева от строки 14. Последовательно выделяйте блоки из 9 строк (15:23, 25:33, 35:43 и т. д.) и используйте команду "Группировать".

Приступаем к заполнению таблицы. Для удобства ее заполнения сверните дебетовые блоки таблицы, кроме первого "Необоротні активи", с тем чтобы она приняла вид, показанный на рис. 8. А теперь вам придется поработать с Инструкцией к Плану счетов. Для правильного заполнения таблицы лучше воспользоваться Инструкцией по применению Плана счетов.

Порядок ваших действий должен быть таким:

- установите курсор в ячейку С5 (рис. 8);

Рисунок 8

- используя в Инструкции таблицу корреспондирующих счетов, для счета 10 "Основные средства" (используйте только левую часть таблиц "По дебету с кредитом счетов"!) найдите первый корреспондирующий счет (в данном случае - кредит счета 15 "Капитальные инвестиции");

- переместите курсор в ячейку, находящуюся на пересечении со столбцом Н (в "шапке" кредитов - столбец К15), и введите любой символ, который будет информировать о наличии такой корреспонденции (например, символ "X", хотя это не принципиально);

- не нажимая клавишу Enter, нажмите на клавиатуре правую стрелку. Курсор перейдет в соседнюю правую ячейку, а в ячейке С5 появится введенный символ. Введите символ "X" и нажмите правую стрелку;

- перемещайте таким образом курсор до ячейки, которая будет находится на пересечении строки "дебет10" (Д10) со следующим корреспондирующим счетом (кредитовый столбец К37);

- когда вы достигнете ячейки ВО5 (пересечение строки Д10 со столбцом К74 - это последняя возможная корреспонденция для счета 10), введите символ "X" и нажмите на клавиатуре клавишу Home. В ячейке ВО5 появится введенный символ, а курсор переместится в начало таблицы;

- установите курсор в ячейку Н6 (первая корреспонденция для дебета счета 11 - Д11 К15) и введите символ "X". Все последующие действия аналогичны приведенным выше.

Надо сказать, что по объему таблица будет довольно внушительная (900 корреспонденций), и ее заполнение потребует от вас часа два интенсивной работы. Однако в результате вы получите удобный инструмент, который точно проконтролирует правильность введенных корреспондирующих счетов и оповестит об ошибке. Вопрос - как это сделать?

Как автоматизировать проверку проводок

Как вы уже поняли, источником проверки проводок будет созданная таблица корсчетов.

Автоматизация процесса проверки состоит из двух этапов: создание массива корреспондирующих счетов и введение формул массива в соответствующие ячейки журнала операций.

1 этап. Создаем массив под именем "КорСчет", для чего:

- сверните структуру листа "ПланСчет" и откройте второй уровень только для блока "Необоротні активи" в дебетовой и кредитовой его части (первые кнопки со знаком плюс "+";

- выделите интервал ячеек $А$5:$CN$94 (рис. 9);

- в поле имен введите имя "КорСчет" и нажмите клавишу "ОК".

Рисунок 9

2 этап. В ячейку F6 листа "ЖурналОп" введите следующую формулу массива (не забыв подтвердить ее ввод нажатием клавиш Ctrl - Shift - Enter):

{"ЕСЛИ(И(ЕПУСТО(ДебетСчетаТек);КредитСчетаТек>0);"?" ;ЕСЛИ(И(ДебетСчетаТек<>0;
ЕПУСТО(КредитСчетаТек));"?";ЕСЛИ(ДебетСчетаТек=0;"";ЕСЛИ(ИНДЕКС(КорСчет;
ЛЕВСИМВ(ДебетСчета Тек;2)-9;ЛЕВСИМВ(КредитСчета Тек;2)-9)=0;"?";""))))}.

Учитывая достаточную сложность формулы, рассмотрим подробно четыре ее части, начинающиеся с условных операторов "ЕСЛИ". Рассмотрим вариант, когда данная формула введена в ячейку F6 листа "ЖурналОп".

Тогда первая часть "ЕСЛИ(И(ЕПУСТО(ДебетСчетаТек);КредитСчетаТек>0);"?"" означает:

- если в ячейку D6 не введено никакого числового значения, а в ячейку Е6 введен код кредита счета, в ячейке F6, в которую введена данная формула, появится знак вопроса "?". Этот символ выбран произвольно и только сигнализирует о том, что проводка введена некорректно (отсутствует дебет счета);

- если же в ячейке D6 введено число (дебет счета), а в ячейке Е6 отсутствует код кредита счета, в ячейке F6 тоже появится знак вопроса "?", то есть выполняется следующее условие:

"ЕСЛИ(И(ДебетСчетаТек<>0;ЕПУСТО(КредитСчетаТек));"?""

- если в ячейки D6 и Е6 не введено никакого числового значения, ячейка F6 не отреагирует на это, так как выполнится условие: "ЕСЛИ(ДебетСчетаТек=0;"" ";

- если же в ячейки D6 и Е6 введены корреспондирующие счета, которые не предусмотрены Планом счетов, выполняется следующее условие:

"ЕСЛИ(ИНДЕКС(КорСчет;ЛЕВСИМВ(ДебетСчетаТек;2)-9";

"ЛЕВСИМВ(КредитСчетаТек;2)-9)=0;"?";"")".

Суть последнего условия следующая:

- если в ячейке, имеющей адрес, определяемый с помощью функции "ИНДЕКС", отсутствует какая-либо запись (0), то в ячейку будет записан знак вопроса "?";

- если в ячейку, имеющую адрес, определяемый с помощью функции "ИНДЕКС", введен символ (например, "X"), ячейка останется пустой.

Рассмотрим подробнее последнюю функцию.

Чтобы приведенная функция "ЕСЛИ" проще воспринималась, представим ее в таком виде:

ЕСЛИ(логическое_выражение; ?; 0).

В нашем случае логическим_выражением является функция:

"ИНДЕКС(КорСчет;ЛЕВСИМВ(ДебетСчетаТек;2)-9";

"ЛЕВСИМВ(КредитСчетаТек;2)-9)=0".

Как было сказано выше, базовым элементом проверки проводки на корректность является таблица корсчетов. Excel будет производить данную проверку, определяя, введен или нет в ячейку массива "КорСчет", находящуюся на пересечении соответствующей строки и столбца, какой-либо символ. Такая операция будет выполняться с помощью функции "ИНДЕКС".

Данная функция имеет следующую структуру:

ИНДЕКС(массив;номер_строки;номер_столбца), где

массив - "КорСчет";

номер_строки в массиве "КорСчет"

- "ЛЕВСИМВ(ДебетСчетаТек;2)-9";

номер_столбца в массиве "КорСчет"

- "ЛЕВСИМВ(КредитСчетаТек;2)-9".

Таким образом, аргументы функции "номер_строки" и "номер_столбца" определяют местонахождение ячейки в массиве "КорСчет", ее адрес.

Для определения номера строки и столбца в массиве используется функция "ЛЕВСИМВ", которая относится к категории текстовых функций и имеет следующую структуру:

ЛЕВСИМВ(текст;количество_символов), где

текст - это текстовое содержание ячейки, в которой находятся извлекаемые символы;

количество_символов - количество символов, которое должна извлечь функция "ЛЕВСИМВ" текстового содержания ячейки. Ее работу вы поймете на простом примере.

Например, если в ячейку F22 (рис. 6) ввести функцию "=ЛЕВСИМВ(D22;2)", в ячейку F22 вернется значение 10 (два левых символа в коде счета 104), а если ввести "ЛЕВСИМВ(Е22;2)", то в ячейку F22 будет возвращено значение 15 (два левых символа в коде счета 152).

И, наконец, почему в аргументах "номер_строки" и "номер_столбца" вычитается число 9? В созданной нами таблице "Корреспондирующие счета" дебет счета 10 (Д10) находится в первой строке массива "КорСчет". Поэтому для того, чтобы получить номер строки массива "КорСчет", в которой находится счет, совпадающий с используемым в проводке, необходимо из аргумента "ЛЕВСИМВ(ДебетСчетаТек;2)" вычесть 9. Аналогично, для получения номера столбца необходимо из аргумента "ЛЕВСИМВ(ДебетСчетаТек;2)" вычесть 9. Проверьте, кредит счета 15 (К15) находится в 6-м столбце массива "КорСчет" (15-9) (рис. 10).

Рисунок 10

Давайте теперь подытожим приведенные пояснения на конкретном примере.

Итак, вы ввели в ячейку F6 на листе "ЖурналОп" (рис. 1) формулу:

{=ЕСЛИ(ДебетСчетаТек=0;”?”;ЕСЛИ(КредитСчетаТек=0;”?”;ЕСЛИ(ИНДЕКС(КорСчет;

ЛЕВСИМВ(ДебетСчетаТек;2)-9;ЛЕВСИМВ(КредитСчетаТек;2)-9)=0;”?”;””)))}.

Сразу скопируйте ее во все ячейки графы "Перевірка".

Допустим, вы записываете проводку №20 (рис. 11). После введения дебета субсчета 152 ячейка F25 не реагирует, однако если ошибочно вместо кредита субсчета 131 в ячейку Е25 ввести субсчет 121, в "проверочной" ячейке F6 появится сигнал об ошибке - знак вопроса "?". (Кстати, для повышения его информативности можно окрасить его в другой цвет.

Рисунок 11

Для этого выделите ячейки графы "Перевірка", начиная с ячейки F6, а затем вызовите окно "Формат ячеек" (рис. 12). В нем на вкладке "Шрифт" выберите нужный цвет и формат сигнального символа).

Рисунок 12

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

Созданная вами система проверки будет действовать независимо от того, какого порядка субсчета используются в проводках.

На следующем уроке мы рассмотрим принципы построения электронной Главной книги, ее заполнение на основе введенных в журнал хозяйственных операций проводок.


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

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