Введение
В этой статье рассматривается пример создания приложения для бухгалтерского учета в Excel. Статья носит чисто учебный характер и не претендует на создание полного приложения. Мы познакомимся с инструментом Проверка данных, с функциями СУММ, СУММЕСЛИ и СУММЕСЛИМН.
Подразумевается, что читатель знаком с основами бухгалтерского учета, поэтому некоторые сведения из теории в статье рассматриваются схематично. Но некоторые определения и понятия нам понадобятся при создании приложения, поэтому вкратце остановимся на этих понятиях.
План счетов бухгалтерского учета представляет собой схему регистрации и группировки фактов хозяйственной деятельности в бухгалтерском учете. В нем приведены наименования и номера синтетических счетов и субсчетов (рис. 1) .
Рис.1. Фрагмент таблицы плана счетов бухгалтерского учета.
Счета предназначены для обобщения информации об операциях по данному счету. Для активов – о наличии и движении (изменении) некоторого вида активов, для пассивных счетов – о состоянии и движении обязательств, капитала. Отметим, что каждая компания выбирает те счета, которые она будет использовать в своей работе. Также определяется, какие субсчета необходимо открыть исходя из специфики деятельности компании.
Журнал учета хозяйственных операций служит для регистрации факта проведения хозяйственной операции. Он является первичным учетным документом, который составляется при совершении факта хозяйственной жизни.
Для журнала хозяйственных операций бланка единого нет. Ведь журнал – это учетный регистр, поэтому его форму организация может разработать самостоятельно (ч. 5 ст. 10 Федерального закона от 06.12.2011 № 402-ФЗ). В условиях автоматизации бухгалтерского учета журнал операций входит в стандартный набор отчетов, формируемых в компьютерной системе.
Оборотно-сальдовая ведомость (ОСВ)это таблица с данными об остатках и оборотах по всем счетам бухучета или же только по определённым счетам бухгалтерского учета. Порядок составления оборотно-сальдовых ведомостей ни одним нормативным документом не урегулирован. Вариант ОСВ приведен на рис. 2.
Создание рабочей книги
Для создания приложения Excel для бухгалтерского учета будем использовать online версию MS Offiсe 365 (рис. 3).
Рабочая книга первоначально создается с одним рабочим листом. Нам понадобится всего три рабочих листа. Добавим еще два рабочих листа (рис. 4).
Присвоим имена созданным рабочим листам: Журнал операций, Оборотно-сальдовая ведомость, Справочники (рис. 5). Поочередно включим контекстное меню (правая кнопка мыши) на каждом ярлыке рабочего листа, выберем пункт меню Переименовать, введем имена каждому рабочему листу (рис.5).
Создаем приложение
Приступим к созданию самого приложения. Прежде всего следует создать справочники. Активируем рабочий лист Справочники. В ячейку, положим A2, введем формулу функции =СЕГОДНЯ(). Эта функция вычисляет и возвращает текущую дату. Она понадобится при отражении очередной операции в журнале учета хозяйственных операций.
В справочники включим Перечень счетов бухгалтерского учета, которые необходимы для отражения хозяйственных операций организации (рис. 6).
Далее создадим справочник Основание проводки. Этот справочник необходим для однозначного отражения текста основания проводки в журнале хозяйственных операций (пример приведен на рис. 6).
Теперь перейдем на рабочий лист Журнал операций и создадим шаблон журнала операций (рис. 7).
Поле Справка по счетам имеет вспомогательное назначение И в атрибутах таблицы при выполнении проводки не заполняется.
Обратим внимание на то, что записи значений атрибутов Дата, Счет дебета, Счет кредита и Основание не могут быть произвольными, поэтому для их заполнения будем использовать справочники. С этой целью будем использовать инструмент Excel Проверка данных (последовательность включения показана на рис. 8).
Настройка ввода атрибута Дата
1. Выделим столбец А рабочего листа, кликнув на его заголовке.
2. Включим инструмент Проверка данных.
3. В открывшемся окне диалога (рис.9) заполним:
- поле Разрешить выберем из возможных вариантов Список;
- в поле Источник установим ссылку на ячейку А2 рабочего листа Справочники (там находится функция СЕГОДНЯ(), вычисляющая текущую дату;
- кликнем на кнопке ОК.
Если мы теперь активируем любую ячейку столбца А рабочего листа, то увидим, что рядом с ней появляется раскрывающаяся стрелка. Кликнув по ней можно увидеть весь список доступных для заполнения значений.
Чтобы убрать стрелочки у ячеек А1 и А2, выделим их, включим инструмент Проверка данных и в поле Список установим Любое значение.
Настройка ввода атрибутов Счет дебета, Счет кредита и Основание
Настройка выполняется также как и настройка поля Дата с той лишь разницей, что в поле Источник инструмента Проверка данных указывается ссылка на диапазон ячеек, содержащий нужный справочник. В нашем случае для атрибутов Счет дебета и Счет кредита нужно установить ссылку на диапазон =Справочники!$A$5:$A$24, для атрибута Основание – диапазон ='Справочники'!$D$5:$D$15.
Чтобы настроить справочник по счетам, выделим одну ячейку Н2 и в поле Источник инструмента Проверка данных ввести ссылку на диапазон ='Справочники'!$B$5:$B$24
Закрепим две верхних строки, чтобы при прокрутке рабочего листа они не уходили с экрана. Для этого выделим третью строку, включим меню Вид – Закрепить области.
Создание шаблона Оборотно-сальдовая ведомость
Оборотно-сальдовая ведомость создается по перечню счетов бухгалтерского учета, которые использует организация. В нашем случае она имеет вид как на рис. 10.
Для заполнения значений атрибута Номер счета используем план счетов справочника. Для этого:
- установим формат ячеек в диапазоне А24:А28 как Общий (рис. 11).
- в ячейку А4 введем формулу =Справочники!A5 и скопируем эту формулу в остальные ячейки диапазона.
Такой подход к заполнению значений этого атрибута обеспечивает гибкость – при изменении счетов бухгалтерского учета все изменения отразятся в оборотно-сальдовой ведомости.
Введем необходимые формулы для вычисления итоговых сумм:
- в ячейке D4 формула должна выглядеть так
=СУММЕСЛИ('Журнал операций'!B:B;A4;'Журнал операций'!D:D) и скопируем ее во все ячейки диапазона D5:D23.
- в ячейку Е4 введем формулу
=СУММЕСЛИ('Журнал операций'!C:C;A4;'Журнал операций'!D:D) и также скопируем ее в ячейки диапазона Е5:Е23
В ячейки F4 и G4 введем соответственно формулы =B4+D4 и =C4+E4 также скопировав их в другие ячейки соответствующего диапазона.
Далее введем формулы для вычисления итоговых сумм, например =СУММ(B4:B23).
Приложение в основном создано и его можно использовать в работе. Но есть одно неудобство – оборотно-сальдовая ведомость в созданном приложении считается за весь период работы, то есть по всем записям в журнале операций, а она должна создаваться по периодам (либо за неделю, или за месяц, за квартал или за год). Созданное нами приложение не может формировать документ по периодам. Его следует дополнить подобной функцией.
Модернизация приложения
В первой строке рабочего листа Оборотно-сальдовая ведомость отведем ячейки С1 и Е1 для записи начальной и конечной даты периода (включительно (рис. 12).
В ячейках D4 и Е4 поменяем формулы для вычислений. Прежде вычисления с помощью функции СУММЕСЛИ выполнялись по одному условию отбора, нам следует теперь отбирать записи из рабочего листа Журнал операций по двум критериям – по номеру счета и по дате операции. Для этого мы применим функцию EXCEL СУММЕСЛИМН, позволяющую выполнять суммирование по двум условиям. Применим следующий алгоритм вычисления в диапазоне дат, установленных в ячейках С1 и Е1: вначале отберем все записи, начиная с начальной даты, затем из полученного результата вычтем результат вычислений начиная с даты, следующей за конечной датой периода. В этом случае формула в ячейке D3 будет иметь вид:
=СУММЕСЛИМН('Журнал операций'!D:D;'Журнал операций'!B:B;A4;'Журнал операций'!A:A;">="&$C$1)-СУММЕСЛИМН('Журнал операций'!D:D;'Журнал операций'!B:B;A4;'Журнал операций'!A:A;">"&$E$1)
а в ячейке Е4 формула будет иметь вид:
=СУММЕСЛИМН('Журнал операций'!D:D;'Журнал операций'!C:C;A4;'Журнал операций'!A:A;">="&$C$1)-СУММЕСЛИМН('Журнал операций'!D:D;'Журнал операций'!C:C;A4;'Журнал операций'!A:A;">"&$E$1)
В формулах установим абсолютные ссылки на ячейки С1 и Е1, скопируем их во все ячейки соответствующего диапазона.
Работа с приложением
Проверим работу приложения на примере. Введем данные в журнал операций, например как на рис. 13.
В оборотно-сальдовой ведомости введем по некоторым счетам сальдо на начало периода, Установим даты начала и окончания периода со 2.12.2022 по 8.12.2022. Получим результат, как на рис.14.
Изменим период с 3.12.2022 по 6.12.2022, получим результат как на рис. 15. Отражаются операции только по счетам 50, 60 и 90 верно.