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

Бухгалтерский учёт с помощью Excel.
Урок 9-й.
Автоматизация составления финансовой отчетности малого предприятия

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


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

Сразу оговоримся, что создаваемая в Excel Главная книга будет отличаться от ее стандартного аналога. Структура электронной Главной книги подчинена главной задаче, которая перед ней ставится, - определение в отчетном периоде оборотов и сальдо по каждому субсчету, используемому в бухгалтерском учете вашего предприятия.

Создание Главной книги

Для создания Главной книги вставьте в файл "Баланс" еще один лист. Назовите его "ГлКнига". Экселевская Главная книга будет довольно объемистым документом, фрагмент общего вида которого можно увидеть на рис. 1. По вертикали он занимает приблизительно 300 строк, а по горизонтали - в зависимости от количества обрабатываемых месяцев (рекомендуемое количество - 12).

Чтобы упростить процесс создания Главной книги, сначала создадим ее структуру для одного месяца. Она будет иметь вид, показанный на рис. 2.

Так как каждая ячейка граф "Оборот по дебету" (столбец F), "Оборот по кредиту" (столбец G), "Сальдо по дебету" (столбец Н) и "Сальдо по кредиту" (столбец і) будет содержать формулу, введем их в соответствующие ячейки первого блока, а затем скопируем их в составе блока в остальные ячейки Главной книги.

Как видно из рис. 1, ячейки "шапки" таблицы, расположенные в столбцах Е, J, O, T и т. д., содержат информацию о дате отчетного месяца. Пока оставьте ячейки Е3 и Е4 незаполненными, мы внесем в них данные после создания структуры таблицы.

Внимание! Наличие пустого столбца Е обязательно, так как в него будет внесена дата соответствующего периода.

А теперь введите в ячейку Н6 формулу:

"=ЕСЛИ((C6-D6+F6-G6)>0;(C6-D6+F6-G6);0)".

Смысл ее понятен без комментария.

Для получения кредитового сальдо субсчета в ячейку і6 введите формулу:

=ЕСЛИ((C6-D6+F6-G6)<0;ABS(C6-D6+F6-G6);0).

Она означает:

- если сумма дебетового сальдо на начало отчетного периода и дебетового оборота субсчета отчетного периода меньше суммы кредитового сальдо на начало отчетного периода и кредитового оборота субсчета отчетного периода, в ячейку будет записана абсолютная величина (АВS) этого выражения;

- в противном случае в ячейку і6 будет записан 0.

Внимание! В приведенных формулах вторым значением, которое возвращается в ячейку после выполнения логического выражения, должен стоять обязательно нуль "0". Если формулу записать в виде "=ЕСЛИ((C6-D6+F6-G6)>0;(C6-D6+F6-G6);"")", при ее копировании в соответствующую ячейку другого месяца всегда будет возвращаться значение ошибки "#ЗНАЧ!".

Как определить оборот по субсчету

Для определения дебетовых и кредитовых оборотов по субсчетам за отчетный период, в ячейки раздела "Оборот за рахунком" необходимо ввести следующие формулы массива:

- в ячейку F6

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ(Е3);ЕСЛИ(ДебетЖ=СчетГКТек;ВсегоЖ)))}

- в ячейку G6

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ(Е3);ЕСЛИ(КредитЖ=СчетГКТек;ВсегоЖ)))}

Как видно, данные формулы имеют одинаковый синтаксис и лишь незначительно отличаются друг от друга. Обратите внимание - в них используется аргумент "МЕСЯЦ(Е3)", в котором аргументом для функции "МЕСЯЦ" используется ссылка на ячейку Е3. В такую ячейку каждого блока (каждого месяца) введите базовую дату, которая будет определять конкретный отчетный период. Так, для первого блока такой датой пусть будет, например, 1 января (не принципиально, какой день месяца будет установлен).

Выделите ячейки Е3 и Е4 и сформатируйте их, как показано на рис. 3. Это даст возможность при свернутой структуре Главной книги легко перейти к нужному месяцу (рис. 5). Для большей информативности запишите в ячейку F3 название соответствующего месяца, для чего введите в нее формулу "=Е3", а на вкладке "Число" установите такой же формат, как и для ячейки Е3.

После ввода всех формул в первый блок выделите ячейки Е3:I6, скопируйте их и введите эти данные в ячейку J3 (рис. 2). Вы получите блок данных для второго отчетного месяца. Данную операцию повторите еще 10 раз (по числу месяцев), последовательно вводя скопированный блок в ячейки O3, T3 и т. д. Необходимо отметить, что при копировании блока в соответствующие ячейки созданных для каждого месяца блоков были скопированы и введенные в первый блок формулы. Для того чтобы формулы массива, с помощью которых определяются обороты по счету, работали корректно, в ячейках каждого блока, в которые скопировалась базовая дата первого блока, скорректируйте ее. Для второго блока в ячейке J3 установите дату 01.02, для третьего блока в ячейке O3 - 01.03 и т. д.

Внимание! Так как используемое в формулах массива имя "СчетГКТек" еще не имеет конкретной ссылки, во всех ячейках, в которые введены эти формулы, вы увидите значение ошибки "#ИМЯ!".

Для более удобного пользования данными Главной книги структурируйте ее по горизонтали. По умолчанию при горизонтальном структурировании Excel создает символы структуры справа от выделенной группы ячеек. То есть если выделить столбцы F:I (рис. 1) и создать структуру первого блока, используя команду "Группировать" в меню "Данные" - "Группа и структура", кнопка сворачивания будет расположена над столбцом J (который относится ко второму блоку). Поэтому в меню "Данные" - "Группа и структура" с помощью команды "Настройка" откройте окно "Структура документа" и уберите переключатель строки "в столбцах справа от детальных" (рис. 4).

Затем выделите столбцы К:N и создайте структуру второго блока. Аналогично структурируйте блоки остальных месяцев.

Итак, вы создали первую рабочую строку Главной книги на 12 месяцев, которую теперь достаточно скопировать для остальных счетов, используемых на вашем предприятии. Однако перед этим сделайте еще одно редактирование. Как вы заметили, в формулах массива каждого месяца аргументом функции "МЕСЯЦ" является относительная ссылка на соответствующую ячейку.

Так как при копировании формул массива содержащиеся в них относительные ссылки изменят первоначальную адресацию, каждый их аргумент вида "МЕСЯЦ(E3)" скорректируйте таким образом - "МЕСЯЦ($E$3)".

Например, для февральского блока в ячейке К6 формула должна иметь такой вид:

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ($J$3);ЕСЛИ(ДебетЖ=СчетГКТек;ВсегоЖ)))},

а в ячейке L6:

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ($J $3);ЕСЛИ(КредитЖ=СчетГКТек;ВсегоЖ)))}.

Аналогично скорректируйте формулы для остальных 11 блоков.

Теперь определите для себя, какое количество субсчетов второго порядка используется на вашем предприятии. Примем условно, что будет использоваться 101­й субсчет. А теперь сверните структуру, щелкнув ЛКМ на кнопке с цифрой 1. Выделите ячейки А6:ВL6 и, установив курсор на маркере заполнения (черный квадрат в правом нижнем углу выделения), протащите его до ячейки ВL106 (рис. 5). Все данные, введенные в первую строку, успешно скопировались во все ячейки Главной книги.

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

При заполнении Главной книги соблюдайте следующие требования:

- при заполнении графы "Назва рахунка" не оставляйте между субсчетами пустых строк! Во-первых, это позволит вам в дальнейшем использовать Главную книгу для автоматического анализа операций (с этим вы ознакомитесь ниже), во-вторых, если возникнет необходимость ввести в Главную книгу новые субсчета, это можно будет сделать, добавив нужное количество пустых строк (выделите в нужном месте Главной книги строку, вызовите контекстное меню и выберите в нем команду "Добавить ячейки").

- в графу "№ рах." вводите субсчета второго порядка (при вводе данных в журнал хозяйственных операций используйте такие же субсчета, как и в Главной книге).

Продолжение этой статьи > > >

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