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

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

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

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


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

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

Как ввести формулу массива

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

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

Итак, для того чтобы ввести в форму финансового отчета, в ячейку G22, формулу, показанную на рис. 1, активизируйте на листе "Отчет" ячейку G22. В строке формул введите первые функции "=СУММ(ЕСЛИ(".

Рисунок 1

После того как вы введете вторую открывающую круглую скобку (рис. 2), в меню "Вставка" выберите строку "Имя", а затем строку "Вставить". В открывшемся окне "Вставка имени" выберите имя "ДатаОтчета" и нажмите "ОК" (рис. 2). В строке формул появится аргумент "ДатаОтчета". Аналогично вводятся другие имена-аргументы.

Рисунок 2

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

Если в вашем рабочем листе в блоке "Незавершенное строительство" будут введены данные, то в ячейке G22 появится итоговая сумма, записанная в ячейке L8 (при условии, что дата баланса, введенная в ячейку G5 на листе "Отчет", будет соответствовать периоду ячейки В8 на рабочем листе, см. рис. 1). Если в рабочий лист данные еще не введены, в ячейке G22 будет записан 0.

Ошибки в формулах

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

Оно появится в ячейке, если вы в формуле, например, нарушите правописание функции ("СУМ" вместо "СУММ") или используете имя, которое не содержится в списке имен ("ДатаОтчет" вместо "ДатаОтчета").

Может появиться сообщение "#Н/Д". В этом случае Excel предупреждает, что не может выполнить вычисления в формуле. У вас такое сообщение появится, если, например, интервалы ячеек на листе "РЛОтчет", которым присвоены имена, неодинакового размера.

Убедитесь, что:

имя "ДатаРЛОтчета" имеет ссылку

"=РЛОтчет!$B$7:$B$263",

имя "КодРядРЛОтчета" - "=РЛОтчет!$A$7:$A$263",

имя "ВсегоРЛОтчет" - "=РЛОтчет!$L$7:$L$263".

То есть все интервалы ячеек, которые обрабатываются в формуле массива на одном листе (в данном случае - на листе "РЛОтчет"), должны начинаться на одной строке (строка 7) и заканчиваться тоже на одной (строка 263). У вас интервалы могут быть другими. Однако, если цифровые значения в приведенных ссылках будут отличаться между собой, появится сообщение об ошибке вида - "#Н/Д".

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

Если после ввода в ячейку формулы массива (например, ячейка G22 листа "Отчет") в ней появится сообщение об ошибке, воспользуйтесь функцией отслеживания зависимостей.

В меню "Сервис" выберите строку "Зависимости", а затем строку "Источник ошибки" (рис. 3). После этого на листе "Отчет" появятся стрелки, направленные к активной ячейке из нескольких ячеек рабочего листа, являющихся аргументами формулы. Аргументы, находящиеся на других листах, схематично обозначаются значком таблицы (рис. 3).

Рисунок 3

Щелкните дважды ЛКМ, например, на линии, исходящей из такого значка таблицы. На экране появится окно "Переход", в поле которого будут представлены все интервалы ячеек, используемые в формуле массива. Это значительно облегчает поиск ошибки. В данном случае можно сразу заметить, что не все интервалы ячеек имеют одинаковый размер (ссылка "[Баланс.xls]РЛОтчет!$В$7:$В$262" имеет другой адрес последней ячейки: $В$262, а не $В$263). Щелкните дважды ЛКМ на строке с этой ссылкой.

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

После устранения ошибки можно удалить стрелки, выбрав строку "Убрать все стрелки" (рис. 3).

Вы можете столкнуться также с ошибкой иного рода.

После ввода в ячейку G22 приведенной формулы вы можете увидеть в ячейке нулевое значение (0 или 0,00) вместо значения 725,00 (рис. 1). Проверьте, все ли аргументы в формуле заданы правильно, содержится ли в ячейке L8 рабочего листа значение 725,00. Если таким способом ошибку не нашли, сделайте следующее.

Откройте список в поле имен и выберите в нем имя "КодРядОтчета", щелкнув на нем ЛКМ. На листе "Отчет" инверсным цветом выделится интервал ячеек, которому вы присвоили это имя. Затем откройте окно "Формат ячеек" и в поле "Числовые форматы" выберите строку "Текстовый". Таким же способом установите текстовой формат и для интервала ячеек, которым присвоено имя "КодРядРЛОтчета". Если после этого в ячейке G22 не появится значение 725,00, проверьте на листах "Отчет" и "РЛОтчет" нумерацию кода строк - она должна совпадать (рис. 4).

Рисунок 4

Отредактируйте на рабочем листе содержимое ячеек столбца "Код рядка". В соответствующих блоках рабочего листа добавьте ноль к кодам 20, 30, 31, 32, 40, 70 и 80 (ячейки в этом случае должны иметь текстовый формат). После этих операций введенная формула массива даст нужный результат.

Может случится, что после ввода последнего аргумента формулы и подтверждения ее ввода у вас на экране появится окно (рис.  5), информирующее об ошибке во введенной формуле.

Рисунок 5

Такое сообщение может появиться, если вы ввели количество круглых скобок, не соответствующее необходимому (например, в конце формулы массива после аргумента "ВсегоРЛОтчет" не поставили скобку). Аналогичное сообщение появится, если синтаксические ошибки незначительны, и Excel может их выявить и исправить.

В окне будет предложен исправленный вариант формулы, который вы можете принять, щелкнув на клавише "Да", или отказаться, нажав клавишу "Нет".

В последнем случае появится окно, в котором будет указана причина ошибки (рис. 5).

После нажатия кнопки "ОК" курсор опять переместится в строку формул, и вам придется устранять ошибку самостоятельно.

Внимание! Логические выражения, вводимые после функции "ЕСЛИ", обязательно должны быть заключены в круглые скобки, например: "ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;1;0)".

Если не соблюсти это требование и не поставить, например, круглую скобку после 0, после введения всей формулы тоже появится сообщение об ошибке с предложением принять исправления (рис. 5). В данном случае программа предложит поставить еще одну круглую скобку в конце формулы. Если принять такое исправление, результат вычисления формулой массива будет неправильным. Поэтому нажмите кнопку "Нет" и исправьте формулу вручную.

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

Синтаксис формулы массива

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

Значение функции "СУММ" в приведенной формуле будет рассмотрено ниже.

А сейчас разберем смысл формулы пофрагментно.

Итак, первый фрагмент формулы: "ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;1;0)".

Он означает:

- если дата, введенная в ячейку с именем "ДатаОтчета" и уменьшенная на 1, совпадает со значением любой ячейки, входящей в интервал под именем "ДатаРЛОтчета", в ячейку G22 будет возвращено значение 1. Если равенство не выполняется, в ячейку G22 возвращается значение 0.

В нашем случае, вычисляя формулу массива, Excel запомнит все строки на листе "РЛОтчет", которые содержат дату, соответствующую дате, на которую составлен баланс (если в ячейке "ДатаОтчета" введено "1 апреля 2001 года", будут выбраны ячейки, содержащие дату "31.03.2001"; в задаче это строки 8, 16, 23, 30, 38, 46 и т. д., рис. 6) .

Рисунок 6

Может показаться странным, что будто бы в данной формуле производятся математические действия с нечисловыми значениями ("ДатаОтчета-1"). Однако это не так. В Excel даты, введенные в ячейки, запоминаются в виде последовательности чисел, и поэтому их можно вычитать, слагать и использовать в других вычислениях. Например, если в ячейке G5, носящей имя "ДатаОтчета", введено "1 апреля 2001 года", Excel оперирует значением 36982, которое используется в вычислениях (отсчет ведется с 01.01.1900 г.).

Следующий фрагмент формулы:

ЕСЛИ(КодРядОтчетаТек=КодРядРЛОтчета;1;0)

означает:

- если код строки, в которой находится введенная формула массива, совпадет при сканировании интервала ячеек с именем "КодРядРЛОтчета" по крайней мере с одним таким же кодом, то в ячейку возвратится значение 1. Если в интервале ячеек "КодРядРЛОтчета" не будет найден такой же код, в формулу возвратится значение 0. В нашей задаче двум приведенным условиям соответствует строка 8 на листе "РЛОтчет" (рис. 6).

Внимание! Если вы в этот фрагмент формулы вместо аргумента "КодРядОтчетаТек" вставите имя "КодРядОтчета", в ячейку будет записан 0.

Так произойдет потому, что программа будет сравнивать два массива, а не искать совпадения одного критерия в одном массиве. Причем в ячейке, в которую вы введете такую формулу, не будет автоматического указания на ошибку типа "#ИМЯ?" или "#Н/Д".

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

Формула массива выберет значение тех ячеек интервала "ВсегоРЛОтчет", которые будут находиться в строках, удовлетворяющих двум рассмотренным условиям: "ДатаОтчета-1=ДатаРЛОтчета" и "КодРядОтчетаТек=КодРядРЛОтчета".

В рассматриваемой задаче этим критериям соответствует ячейка L8 на листе "РЛОтчет" (рис. 6). Если допустить, например, что в ячейке А46 (рис. 6) стояло бы значение 020, то формула массива просуммировала бы ячейки L8 и L46, в балансе в ячейке G22 (рис. 1) появилось бы значение 1023,00 (725,00+298,00).

И наконец, в формуле массива используется функция "СУММ". Положение этой функции в начале данной формулы обязательно.

Функция "СУММ" учитывает только числа, пропуская пустые ячейки, логические значения и тексты. Поэтому при выполнении в формуле массива условия (например, "ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета") обязательно нужно использовать числовое значение 1 вместо "ИСТИНА" при выполнении условия и 0 вместо "ЛОЖЬ" - при невыполнении условия.

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

=a*b*(еn), где

a, b - значения, полученные при выполнении первого и второго условий "ЕСЛИ";

n - значения ячеек из интервала "ВсегоРЛОтчет", удовлетворяющих выполненным выше условиям.

Мы уже выяснили, что формула массива выполняет несколько вычислений и возвращает либо несколько значений, либо одно значение. Выше мы рассматривали ввод формулы в выделенный интервал ячеек. Однако в данном случае такой способ неприемлем, так как результатом ввода формулы массива должно быть получение нескольких значений. Чтобы вернуть несколько значений, формулу необходимо ввести в несколько ячеек.

Итак, у вас уже введена в ячейку G22 формула массива. Скопируйте ее в остальные ячейки финансового отчета. Для этого выделите ячейку G22 (в строке формул появится введенная формула массива) и щелкните ЛКМ на пиктограмме "Копировать". После этого рамка ячейки выделится бегущей пунктирной линией. Затем выделите группу ячеек, в которые вы сначала введете формулу (например, G25:G29), и щелкните на пиктограмме "Вставить" (рис. 7).

Рисунок 7

Формула массива запишется в выделенные ячейки. Аналогично скопируйте формулу в остальные последовательные группы ячеек, используя пиктограмму "Вставить" (она доступна, пока ячейка-источник выделена бегущей пунктирной линией).

Теперь введите формулы в ячейки 4-й графы с итоговыми данными.

В ячейки актива финансового отчета введите формулы:

- в ячейку G24 (Остаточная стоимость основных средств) - "=G25-G26";

- в ячейку G29 (Итого по разделу I) - "=G22+G24+G27+G28";

- в ячейку G34 (Чистая реализационная стоимость) - "=G35-G36";

- в ячейку G44 (Итого по разделу II) - "=СУММ(G31:G34;G37:G43)";

- в ячейку G46 (Баланс) - "=G29+G44+G45".

В ячейки пассива финансового отчета введите формулы:

- в ячейку G56 (Итого по разделу I) - "=СУММ(G51:G54)-G55";

- в ячейку G68 (Итого по разделу IV) - "=СУММ(G60:G67)";

- в ячейку G70 (Баланс) - "=СУММ(G56:G58)+G68+G69".

На этом введение формул в графу 4 финансового отчета закончено.

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

Для того чтобы в финансовом отчете заполнить графу 3 ("На начало отчетного года"), в ячейку F22 необходимо ввести формулу, которая лишь немногим отличается от введенной в ячейку G22 (рис. 8).

Рисунок 8

Формула выглядит следующим образом:

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

Как видите, отличие только в первом условии:

ЕСЛИ(МЕСЯЦ(ДатаРЛОтчета)=1;1;0).

Суть его заключается в следующем:

- если месяц любой ячейки, входящей в интервал с именем "ДатаРЛОтчета", равен 1, то будет выполняться следующее условие. В противном случае в ячейку F22 будет записано нулевое значение.

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

Обратите внимание на то, что аргумент функции "МЕСЯЦ" должен быть заключен в круглые скобки: "МЕСЯЦ(ДатаРЛОтчета)".

После редактирования формулы подтвердите ее ввод нажатием клавиш Ctrl + Shift + Enter.

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

И наконец, в ячейки, в которых суммируются данные (F24, F29, F34, F44, F46, F56, F68, F70) из соседних ячеек соседней графы 4.

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

Как изменять размерность данных в финансовой отчетности

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

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

Мы уже выяснили, что введенные в финансовый отчет формулы представляют собой произведение нескольких сомножителей. Поэтому для уменьшения единицы измерения данных на 3 порядка достаточно уменьшить во столько раз один из сомножителей. Например, первый сомножитель формулы можно записать в следующем виде: "СУММ(ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;0,001;0)...".

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

Введите, например, в ячейку I17 значение 0,001, а первый фрагмент формулы, записанной в ячейку G22, отредактируйте следующим образом:

=СУММ(ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета);I17;0)...

После такого редактирования (не забудьте нажать комбинацию клавиш Ctrl + Shift + Enter) значение, записанное в ячейку G22, уменьшится в тысячу раз, то есть будет представлено в тысячах гривен.

Однако не спешите копировать эту формулу в остальные ячейки 4-й графы баланса. Для того чтобы она работала и в других ячейках, необходимо относительную ссылку на ячейку I17 заменить абсолютной:

=СУММ(ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;$I$17;0)...

Аналогично отредактируйте формулу массива, введенную в графу 3 финансового отчета. Ее измененная часть будет выглядеть следующим образом:

=СУММ(ЕСЛИ(МЕСЯЦ(ДатаРЛОтчета)=1;$I$17;0)...

Теперь для изменения размерности валюты баланса достаточно ввести необходимый коэффициент только в ячейку I17 (рис. 9).

Рисунок 9

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

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


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

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