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

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

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

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


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

Продолжаем рассмотрение автоматизации составления финансовой отчетности малого предприятия. Запускайте программу Excel и открывайте созданную вами в прошлый раз книгу "Баланс".

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

Внесение данных в рабочий лист

Итак, на предыдущем уроке мы создали рабочий лист финансового отчета, функцией которого будет сбор и группировка данных, входящих в соответствующие статьи баланса. Рабочий лист содержит одинаковые по структуре блоки, названия и коды строк которых соответствуют строкам баланса. В этих блоках размещены рабочие ячейки. Например, для блока "Прочие необоротные активы" (А43:L50) такими ячейками являются D45:L49, содержащие дебетовые сальдо счетов 16, 17, 18 и кредитовое сальдо счета 19, а также ячейки L45:L49 с итоговыми данными (рис.1).

Рисунок 1

Ячейки В50:L50 являются вспомогательными и могут быть использованы для систематизации данных по другим признакам. Так как, согласно Закону о бухучете, промежуточная отчетность составляется ежеквартально, в рабочих ячейках каждого блока данные группируются тоже поквартально, нарастающим итогом. Например, в ячейки D45:K45, относящиеся к блоку "Прочие необоротные активы", введены сальдо счетов 16, 17, 18 и 19 на начало года, в ячейки D46:K46 введены сальдо на конец первого квартала, в ячейки D47:K47 - сальдо на конец второго квартала, в ячейки D48:K48 - сальдо на конец третьего квартала, а в ячейки D49:K49 - сальдо на конец четвертого квартала, то есть данные по итогам за год.

В рабочие ячейки блоков, относящиеся к статьям актива баланса, вводятся, как правило, дебетовые сальдо, а в ячейки, относящиеся к статьям пассива баланса, - кредитовые сальдо.

Однако следует учесть, что в некоторые статьи пассива баланса включаются также и дебетовые сальдо, а в некоторые статьи актива - наоборот, кредитовые.

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

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

Код строки Наименование статьи баланса Сальдо счетов, входящих в статью баланса
Актив
020 Незавершенное строительство ДС 15
030 Остаточная стоимость основных средств ДС 10 + ДС 11 + ДС 12 - КС 13
031 Первоначальная стоимость основных средств ДС 10 + ДС 11 + ДС 12
032 Износ основных средств КС 13
040 Долгосрочные финансовые инвестиции ДС 14
070 Прочие необоротные активы ДС 16 + ДС 17 + ДС 18 - КС 19
100 Производственные запасы ДС 20 + ДС 21 + ДС 22 + ДС 23
130 Готовая продукция ДС 26 + ДС 27 + ДС 28
160 Чистая реализ. стоимость дебиторской задолженности ДС 34 + ДС 36 - КС 38
161 Первоначальная стоимость дебиторской задолженности ДС 34 + ДС 36
162 Резерв сомнительных долгов КС 38
170 Дебиторская задолженность по расчетам с бюджетом ДС 641 + ДС 642 + ДС 644
210 Прочая текущая дебиторская задолженность ДС 37 + ДС 63 + ДС 65 + ДС 66 + ДС68
220 Текущие финансовые инвестиции ДС 352
230 Денежные средства и их эквиваленты в нац. валюте ДС 301 + ДС 311 + ДС 313 + ДС 331 + ДС 333 + ДС 3511
240 Денежные средства и их эквиваленты в инвалюте ДС 302 + ДС 312 + ДС 314 + ДС 332 + ДС 334 + ДС 3512
250 Прочие оборотные активы ДС 24 + ДС 25 + ДС 27
270 Затраты будущих периодов ДС 39
Пассив
300 Уставный капитал КС 40
320 Дополнительный капитал КС 41 + КС 42
340 Резервный капитал КС 43
350 Нераспределенная прибыль (непокрытый убыток) КС 441 - ДС 442 - ДС 443
360 Неоплаченный капитал ДС 45 + ДС 46
430 Обеспечение будущих затрат и целевое финансирование КС 47 + КС 48
480 Долгосрочные обязательства КС 50 + КС 51 + КС 52 + КС 53 + КС 54 + КС 55
500 Краткосрочные кредиты банков КС 60
510 Текущая задолженность по долгосроч. обязательствам КС 61
530 Кредиторская задолженность по товарам, работам, услугам КС 36 + КС 62 + КС 63
550 Текущие обязательства по расчетам с бюджетом КС 641 + КС 642 + КС 643
570 Текущие обязательства по расчетам по страхованию КС 65
580 Текущие обязательства по расчетам по оплате труда КС 66
610 Прочие текущие обязательства КС 67 + КС 68
630 Доходы будущих периодов КС 69

где ДС - дебетовое сальдо по счету,

КС - кредитовое сальдо по счету.

На субсчете 3511 - эквиваленты денежных средств в национальной валюте.

На субсчете 3512 - эквиваленты денежных средств в иностранной валюте.

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

Данные, которые войдут в соответствующую строку отчетной формы баланса граф "На начало отчетного года" и "На конец отчетного периода", мы будем брать из ячеек рабочего листа в столбце L ("Всего").

Для получения результирующих данных по каждой статье в ячейки столбца "Всего" необходимо ввести формулу суммы "=СУММ(Dn:Kn)", где D, K - обозначения столбцов, n - номер строки.

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

Однако, прежде чем вводить формулы в ячейки, для ускорения этого процесса спрячьте 3-й уровень структуры рабочего листа, щелкнув ЛКМ на кнопке управления уровнем с цифрой 2 (рис.2). Рабочие ячейки будут скрыты, а символы структуры будут представлены кнопками со знаком "+" (плюс).

Рисунок 2

Откройте первый блок ячеек, щелкнув на соответствующей кнопке со знаком "+" (она расположена напротив строки 12).

А теперь активизируйте ячейку L7 и введите в нее формулу "=СУММ(D7:К7)". Затем известным вам способом скопируйте ее содержимое во все ячейки рабочего листа (щелкнув ЛКМ на маркере заполнения и протащив курсор от ячейки L7 до ячейки L263).

Таким способом вы скопировали эту формулу во все 254 ячейки раздела "Всего" (у вас количество ячеек может быть другим).

Теперь откройте все уровни структуры, щелкнув ЛКМ на кнопке структуры с цифрой 3. Вы увидите, что во всех ячейках столбца "Всего" появились числовые значения. Затем в ячейках столбца L в каждом блоке удалите данные, которые не относятся к рабочим ячейкам. Например, в блоке "Прочие необоротные активы" (рис.1) в ячейке L43 находится значение 0,00, а в ячейке L44 - значение 540,00 (сумма показанных на рис.1 номеров счетов, входящих в данную статью баланса). Естественно, данная информация ничего не отражает, и ее следует убрать.

Как вы уже убедились, скопированная в другие ячейки формула "=СУММ(D7:К7)" изменила аргументы.

Так, например, в ячейке L45 (рис.1) она приобрела вид "=СУММ(D45:К45)". Однако, учитывая то, что среди входящих в статью баланса "Прочие необоротные активы" присутствует счет, кредитовое сальдо которого вычитается из валюты данной статьи, нужно отредактировать эту формулу.

Для этого активизируйте ячейку L45 для редактирования (щелкните дважды ЛКМ на ячейке) и измените аргументы записанной в нее формулы. Она должна иметь такой вид: "=СУММ(D45:J45)-К45".

Затем скопируйте эту формулу в ячейки L46:L49.

Аналогичное редактирование необходимо провести и для блока "Нераспределенная прибыль". Вместо введенной в ячейку L170 формулы "=СУММ(D170:K170)" необходимо ввести формулу "=D170-J170-K170" (рис.3). После этого скопируйте ячейку L170 в ячейки L171:L174. На этом предварительное редактирование формул на рабочем листе закончено.

Рисунок 3

Как перенести данные в финансовый отчет

Следующим шагом будет перенос итоговых данных рабочего листа в финансовый отчет.

Сделать это можно двумя способами.

Первый способ - ввести в каждую ячейку финансового отчета отдельную формулу, аргументом в которой была бы ссылка на соответствующую ячейку раздела "Всего" рабочего листа. Для этого необходимо активизировать, например, ячейку G22 на листе "Отчет" (рис.4): дважды щелкнув на ней ЛКМ, введите знак "=" и откройте лист "РЛОтчет", щелкнув ЛКМ по его ярлычку. Затем ЛКМ щелкните на ячейке L8 и нажмите клавишу Enter.

Рисунок 4

В результате в ячейке G22 листа "Отчет" появится содержимое ячейки L8 листа "РЛОтчет" (725,00), а в строке формул - введенная формула "=РЛОтчет!L8" (рис.4).

Простой и на первый взгляд вполне приемлемый вариант. Однако у него есть несколько недостатков.

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

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

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

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

Формула массива

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

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

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

Использование формулы массива можно проиллюстрировать на простом примере.

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

Выделите ячейки L7:L11 (рис.5) и в строку формул введите формулу, показанную в строке формул на рис.5. Однако прежде чем нажать клавишу Enter, нажмите клавиши Ctrl и Shift и, удерживая их, нажмите клавишу Enter. Во всех выделенных ячейках появятся числовые значения, равные сумме рабочих ячеек соответствующей строки (например, L7=D7+E7+F7+G7).

Рисунок 5

В ячейках L7:L11 будет записана одинаковая формула массива, отличительным признаком которой будет наличие фигурных скобок, охватывающих ее. Формулу массива, как и обычную формулу, можно отредактировать в любой ячейке массива, однако ее изменение не забудьте подтвердить нажатием комбинации клавиш Ctrl + Shift + Enter. В противном случае в ячейку будет записана обычная формула. Прикладное к нашей задаче применение формулы массива будет рассмотрено ниже.

Присвоение имени ячейке и интервалу ячеек

Вы обратили внимание, что в приведенной формуле массива в качестве аргументов формулы используются группы ячеек. Присвоенные ячейкам или группам ячеек имена позволяют легко находить их на рабочем листе для использования и, что более важно, исключают ошибку при введении их адресов. Если присвоить ячейкам D7:D11, например, имя "Дс151" (дебетовое сальдо субсчета 151), ячейкам Е7:Е11 - "Дс152", ячейкам F7: F11 - "Дс153", а ячейкам G7: G11 - "Дс154", формула массива приобретет вид "{=Дс151+Дс152+Дс153+Дс154}".

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

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

Установите в нее курсор (в поле имен появится адрес ячейки - G5). Затем щелкните ЛКМ на поле имен, введите в нем нужное имя и нажмите клавишу Enter.

Так как при составлении отчетности мы будем активно использовать в формулах адрес именно этой ячейки, выберем для нее узнаваемое имя, например, "ДатаОтчета".

В ячейку "ДатаОтчета" нужно записать формулу "=D16" (рис.6), чтобы при изменении даты, на которую составляется баланс, изменялось также и ее значение.

Рисунок 6

Не забудьте - эти две ячейки должны иметь формат даты. Для ячейки D16 выберите формат, выделенный в поле "Тип" диалогового окна "Формат ячеек", а для ячейки G5 - формат, указанный в первой строке поля "Тип" (рис.7).

Рисунок 7

Так как Excel не различает в именах строчных или прописных букв, то, если вы присвоите какой-либо ячейке, например, имя "Датаотчета", программа заменит имя "ДатаОтчета" на "Датаотчета". Тем не менее, для удобства восприятия имени рекомендую начинать в нем ключевые слова с прописной буквы, как, например, в имени "ДатаОтчета".

Внимание! Присваивая имя, учитывайте, что оно не должно содержать пробелов. Имя должно начинаться с буквы или символа подчеркивания (например, имя "_код"). Остальными символами, входящими в имя, могут быть буквы, числа, точки и символы подчеркивания (например, "Баланс.Итого"). Имя может содержать не более 255 символов.

Операция присвоения имени интервалу ячеек не отличается от присвоения имени одной ячейке.

Присвоим имена интервалам ячеек, адреса которых будут входить в формулу массива, которую мы будем использовать для переноса данных из рабочего листа в финансовый отчет. Эта формула массива содержит 6 имен интервалов ячеек, три из которых на листе "РЛОтчет" и три - на листе "Отчет".

На листе "РЛОтчет" присвойте имена таким трем интервалам ячеек:

"КодРядРЛОтчета" - ячейкам А21:А263,

"ДатаРЛОтчета" - ячейкам В21:В263,

"ВсегоРЛОтчет" - ячейкам L21:L263.

Внимание! Для удобства выделения длинных интервалов ячеек можно скрыть часть структуры, щелкнув ЛКМ на кнопке управления структурой с цифрой 2 (рис.5).

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

Рисунок 8

Присвоим имя интервалу ячеек, содержащему коды строк финансового отчета (второе из трех имен на листе "Отчет"). На листе "Отчет" выделите интервал ячеек Е21:Е69. Для этой цели можно использовать способ, отличный от того, который вы использовали раньше. Выделите первую ячейку интервала (ячейка Е21), щелкнув на ней ЛКМ, затем установите курсор мышки на бегунок полосы прокрутки и переместите его вниз до появления в окне последней строки "Баланс". Нажмите на клавишу Shift и, удерживая ее нажатой, щелкните ЛКМ на последней ячейке интервала (ячейка Е69).

В результате нужный интервал станет выделенным (рис.9).

Рисунок 9

В поле имен введите, например, имя "КодРядОтчета".

Пусть вас не смущает, что в интервал ячеек "КодРядОтчета" вошли ячейки, непосредственно не содержащие кодов (Е47:Е50). На работу формулы массива это не повлияет, но зато соблюдается условие непрерывности интервала ячеек.

Иногда во время операции присвоения имени необходимо изменить некоторые аргументы, входящие в ссылку на ячейку. В таких случаях имя ячейке необходимо присваивать с помощью меню. Этот способ мы рассмотрим на примере присвоения ячейке Е21 на листе "Отчет" имени "КодРядОтчетаТек". Хочу обратить ваше внимание на особое значение аргументов такого рода при вычислении формул массива. Условно такие аргументы можно назвать позиционирующими.

Как формула массива выбирает нужные данные

Excel дает возможность перенести из регистров бухгалтерского учета данные, необходимые для заполнения финансового отчета, с помощью всего двух формул массива - одной формулы для заполнения графы 3, второй - для графы 4. Составление таких формул предусматривает выполнение некоторых важных условий:

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

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

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

Итак, выделите на листе "Отчет" ячейку Е21 (в любом случае это должна быть первая ячейка интервала "КодРядОтчета"). В меню "Вставка" выберите строки "Имя" и "Присвоить". В появившемся окне "Присвоение имени" (рис.10) в поле "Имя" введите имя "КодРядОтчетаТек". В принципе, в это поле можно ввести любое другое имя, однако желательно отразить в нем содержание ячейки (в нашем случае имя ячейки говорит о том, что она является текущей (переменной) и находится в интервале "КодРядОтчета").

Рисунок 10

В поле "Формула" вы увидите ссылку на ячейку в том виде, в котором она будет использоваться в формуле массива. Скорректируйте ее, удалив второй знак доллара "$" после буквы Е, и нажмите кнопку "Добавить". Окно не закроется, и вы сможете еще раз проверить введенную формулу.

Ссылка должна иметь такой вид: "=Отчет!$Е21". После проверки нажмите кнопку "ОК".

Данная корректировка ссылки на ячейку Е21 - очень важная операция, смысл которой заключается в следующем.

Формула массива, использующая имя "КодРядОтчетаТек", и введенная в любую ячейку листа "Отчет", при считывании аргумента "КодРядОтчетаТек" будет ссылаться на ячейку столбца Е, причем именно в той строке, в которой находится формула массива.

Если формула имени "КодРядОтчетаТек" введена правильно, вы можете проверить ее действие. Установите курсор, например, на ячейке G27 и щелкните ЛКМ на стрелке поля имен. В открывшемся окне, которое содержит все введенные имена (рис.11), щелкните на имени "КодРядОтчетаТек". В результате этих действий указатель ячейки переместится в ячейку Е27.

Рисунок 11

Если, например, в ячейку F28 или ячейку G28 ввести формулу "=КодРядОтчетаТек", то в них появится значение 70 (значение кода ячейки Е28).

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

{=СУММ(ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;1;0)*
ЕСЛИ(КодРядОтчетаТек=КодРядРЛОтчета;1;0)*ВсегоРЛОтчет}

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


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

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