В начало

Приложение. Тексты программ пользовательских функций

Глава 9. Генерация документов

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

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

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

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

Документы, с которыми бухгалтеру приходится постоянно иметь дело, условно разделим на следующие основные группы:

• денежные;
• по зарплате;
• отчетные;
• прочие.

Платежные поручения

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

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

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

Рис. 9.1. Лист Платежные поручения книги Bank

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

На этот лист для каждого предприятия заносим его ИНН, банк и расчетный счет. Не хватает только банковских реквизитов (кор. счет и БИК). Здесь ситуация аналогична предыдущей. Нет смысла помещать все это в информацию о предприятии, поскольку один и тот же банк может быть у нескольких предприятий, поэтому банковскую информацию также выделяем в отдельную таблицу Банки, приведенную на рис. 9.3.

Рис. 9.2. Лист предприятия

Рис. 9.3. Лист Банки

Рис. 9.4. Реквизиты предприятия

Реквизиты собственного предприятия для упрощения алгоритмов разумнее также держать на отдельном листе. Это лист Реквизиты, представленный на рис. 9.4. Здесь содержится основная информация предприятия, необходимая для платежного поручения: ИНН, КПП, а также регистрационные номера во внебюджетных фондах. Эта, казалось бы, не имеющая отношения к "платежке" информация появилась здесь вот почему. Бухгалтеру часто приходится проводить стандартные платежи, такие как налоги и отчисления с зарплаты; естественно, ему бы хотелось автоматизировать данные операции. Как это сделать, будет показано при рассмотрении генерации документов по зарплате. Здесь же только отметим, что там должна содержаться информация о регистрационных номерах.

Теперь, когда имеются все "стандартные" данные для платежного поручения, можно заняться ее конструированием. Заготовку бланка можно сделать самим или откуда-нибудь позаимствовать (рис. 9.5).

Рис. 9.5. Лист форма платежного поручения

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

Шаг 1

В ячейку G2 помещаем формулу:

=ВПР($Р$2;'плат. поручения'!А:М;2) [1]

Функция ВПР в данном случае ищет значение, равное содержимому ячейки F2 текущего листа в столбце А листа Платежные поручения, и возвращает значение из столбца В той же строки.

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

Шаг 2

Аналогичным образом в ячейку В16 (имя получатель) помещаем формулу:

=ВПР(номер_пл.п.;'плат. поручения'!А:М;3) [2]

где Номер пл.п. - это имя ячейки F2.

Шаг 3

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

=ЦЕЛОЕ(ВПР($Р$2;'плат.поручения'!А:М;8))&ЕСЛИ(ОСТАТ(ВПР($Р$2;'плат. поручения‘!A:M;8);1) >=0,01;("-"&PERSONALXLS!rmrazr(BПP($F$2;'плат. поручения'!A:M;8);-1) &PERSONAL.XLS!rmrazr(BПP($F$2;'плат. поручения'!А:М;8);-2));"=") [3]

Прежде всего в формуле [3] бросается в глаза то, что в ней существенную роль играет функция:

ВПР($F$2;'плат. поручения'!А:М;8)

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

Возвращаемся к формуле [3]. Целую часть числа в любом случае нужно отобразить, поэтому в первую очередь это и записано.

Думается, что функция Целое() в особых комментариях не нуждается.

Далее следует определиться с дробной частью. Специальной такой функции в арсенале Excel нет, но ее может заменить функция остатка от деления чисел, если в качестве делителя взять единицу. Если этот остаток меньше 0,01, то есть одной копейки, следует поставить двойную черту.

Для вывода копеек придется дополнительно помучиться, так как необходимо поставить одинарную черту и цифры двух первых десятичных разрядов. За неимением стандартной требуемой функции авторам пришлось создать пользовательскую функцию PERSONAL.XLS!rmrazr. Эта функция имеет два аргумента - число и разряд - и возвращает значение заданного разряда. При этом положительным номерам разряда соответствуют десятичные знаки до запятой, а отрицательным - после. В данном случае функция возвращает первый и второй разряды после запятой и "сцепляет" их как текст.

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

Шаг 4

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

=ЕСЛИ(ВПР(номер_пл.п.;'плат. поручения'!А:М;4)= "Налог";"";ВПР(номер_пл.п.;'плат. поручения'!А:М;4))&""&ВПР(номер_пл.п.;'плат. поручения'!А:М;5)&ЕСЛИ(ЕТЕКСТ(ВПР(номер_пл.п.;'плат. поручения'!А:М;6));" по "&ВПР(номер_пл.п.;'плат. поручения'!А:М;6);"") [4]

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

ПРИМЕЧАНИЕВ 2003 году введена новая форма платежных поручений, где для налоговых платежей предусмотрены особые кодировки, размещаемые в специальной таблице. Поэтому приведенные ухищрения остаются актуальными только для платежей в соцстрах, тем не менее данный пример полезен для иллюстрации возможностей по автоматизации рутинных операций.

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

ПРИМЕЧАНИЕ Для платежей, не являющихся налогами, но приравненных к ним (соцстрах и т. п.), в поле "Вид платежа" также заносится слово "Налог". Оно не участвует в формулировках, а имеет смысл ключевого слова.

Шаг 5

В ячейке В22 формируем информацию об НДС. Для этого заносим туда следующую формулу:

=ЕСЛИ(ВПР(Р2;'плат. поручения'!А:С;4)="Налог";"";ЕСЛИ(ВПР(Р2;'плат. поручения'!А:Н;8)=0;"НДС не облагается";"В том числе НДС= "&ЦЕЛОЕ (ВПР($Р$2;'плат. поручения'!А:М;8))&"-"&PERSONAL.XLS!rmrazr (ВПР($F$2;'плат. поручения'!А:М;8);-1)&PERSONAL.XLS!rmrazr(BПP($F$2;плат. поручения'!А:М;8);-2))) [5]

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

В данном случае мы проверяем, является ли назначением платежа Налог, и если да, то в ячейку, содержащую информацию об НДС, не помещаем ничего. Если назначение платежа другое, проверяем, чему равна сумма НДС. Если она равна 0, формула возвращает текст НДС не облагается; в противном случае осуществляется сцепка текста В том числе НДС с целой частью суммы НДС и первым и вторым десятичными разрядами после запятой суммы НДС - иначе говоря, отображается сумма НДС в формате, определенном действующими нормативными документами.

Шаг 6

В ячейку К2 заносим формулу, генерирующую вид платежа.

ПРИМЕЧАНИЕВ данном случае имеется вид платежа в смысле банковского платежного поручения. Не путайте его с понятием "вид платежа", введенным ранее в ведомости учета платежных поручений и ведомости банковских операций.

В данном случае вид платежа означает "электронно" или "почтой". При этом действующие на данный момент в Санкт-Петербурге указания банков таковы, что, если вид платежа "почтой", то он не указывается. В основном все внутригородские платежи идут почтой, а иногородние "электронно", поэтому в ячейке К2 реализуется формула:

=ЕСЛИ(Е14<>банки!В2;ЕСЛИ(банк_получателя=банки!А46;"";"электронно");"") [6]

В строке 2 листа Банки книги Bank заносится информация собственного банка (или другого местного), при этом столбец В содержит информацию о городе. Строка 46 страницы Банки содержит информацию об иногороднем банке, платежи по которому идут почтой. В данном случае это единственное исключение, но в каждом конкретном случае оно может быть иным, поэтому к данной формуле следует относиться творчески.

Шаг 7

В ячейку М18 заносим формулу реализации очередности платежа. Логика простая: налоговые и аналогичные платежи - очередность 3, остальные - 6, другого практически не бывает, а если появится что-то новое, доработать формулу нетрудно. Содержимое ячейки М18 выглядит следующим образом:

=ЕСЛИ(ИЛИ(ВПР(Р2;'плат. поручения'!А:Н;4)="Пени";ВПР(Р2;'плат. поручения'!А:Н;4)="Налог";ВПР(Р2;'плат. поручения'!А:Н;4)="Страховые взносы");3;6) [7]

Шаг 8

На этом шаге вносим реквизиты получателя (ИНН, банк, расчетный счет) в соответствующие ячейки таблицы. Ничего принципиально нового здесь нет, используется функция ВПР. В частности, в ячейке В12 (банк_получателя) находится формула:

=ВПР(Получатель;предприятия!А:Р;2;ЛОЖЬ) [8]

Аргумент Ложь - это необязательный аргумент данной функции, указывающий на то, что мы требуем точного или приближенного совпадения. Получатель - это имя ячейки В16, содержащей формулу, возвращающую наименование получателя.

Шаг 9

Вносим банковские реквизиты получателя (город, корр. счет, БИК). Все аналогично пройденному. Та же функция ВПР, только по другому массиву. Например, формула в ячейке Е14:

=ВПР(банк_получателя;банки!А:Р;2;ЛОЖЬ) [9]

где банк_получателя - имя ячейки В12.

Шаг 10

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

Некоторую сложность может представлять ситуация, когда у вас есть расчетные счета в разных банках. Можно сделать для каждого банка все отдельно, но можно иметь и единые ведомость учета платежных поручений и форму платежного поручения для всех банков. В этом случае на листе Платежные поручения необходимо добавить поле Номер банка (рис. 9.6). На лист Реквизиты в столбцы С и D заносим сведения по банкам 2 и 3, аналогично тому, как в столбце В уже занесена информация по банку 1 (рис. 9.7).

Рис. 9.6. Второй вариант листа Платежные поручения

Рис. 9.7. Второй вариант листа Реквизиты

В форме платежного поручения в ячейку В9, названную Банк_плателыцика, вносим формулу:

=ВЫБОР(ПРОСМОТР(номер_пл.п.;'плат. поручения'!А:А;'плат. поручения'!1:1);Реквизиты!В4;Реквизиты!С4;Реквизиты!D4) [10]

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

Таким образом, формула [10] осуществляет следующее: по номеру платежного поручения находит номер банка, а по номеру на листе Реквизиты находит его название, которое и помещает в соответствующую ячейку платежного поручения.

Формулы для БИК и кор. счета остаются неизменными, а вот для расчетного счета задача несколько сложнее. Для отображения расчетного счета в ячейку I7 вносится формула:

=ГПР(банк_плателыцика;Реквизиты!В4:D6;3;ЛОЖЬ) [11]

Использованная функция ГПР аналогична хорошо знакомой нам функции ВПР - с той лишь разницей, что столбцы и строки меняются ролями. В данном случае она ищет название банка в строке 4 листа Реквизиты в диапазоне столбцов B-D и возвращает соответствующее значение из строки 6.

Шаг 11

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

Пользовательские функции

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

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

Чтобы избежать этого, в Excel предоставлена возможность создавать пользовательские функции, которые действуют так же, как и встроенные функции Excel. Для этого существует специальный язык программирования Visual Basic for Application, или, сокращенно, VBA.

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

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

• =PERSONAL.XLS!montext;
• =PERSONAL.XLS!дентекст;
• =PERSONAL.XLS!сумпроп.

Отличие между ними в том, что целое число в первом случае преобразуется в текст без копеек, во втором и третьем появляется число "00". Разница между второй и третьей функцией заключена в полном или сокращенном названии копеек. Все это проиллюстрировано на рис. 9.8.

Рис. 9.8. Результат действия пользовательских функций

Функция =PERSONAL.XLS!montext применяется в платежных поручениях, =PERSONAL.XLS!дентекст - в кассовых документах. Текст программы функции PERSONAL.XLS!montext приведен в приложении. Функция PERSONAL.XLS!дентекст практически аналогична, и читателю нетрудно будет преобразовать одну в другую, не будучи даже знакомым с языком VBA.

Кроме того, в банковских и кассовых документах используются функции PERSONAL.XLS!rmrazr(X;I) и PERSONAL.XLS!rmrazm(X), которые уже встречались в этой главе. Первая возвращает значение 1-го разряда числа х, вторая - размерность (число целых разрядов) числа х. Действие функций проиллюстрировано на рис. 9.9. В ячейках В1 и В2 расположены функции PERSONAL.XLS!rmrazr(A1;0) и PERSONAL.XLS!rmrazm(A2) соответственно. Эти функции применяются как вспомогательные в функциях montext и дентекст, а также для приведения числа к формату, требуемому в денежных документах.

Рис. 9.9. Результат работы функций rmazr и rmrazm

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

Кассовые документы

Здесь наша задача - сгенерировать следующие документы:

• расходный ордер;
• приходный ордер;
• кассовую книгу.

В идеологии здесь много общего с банком, но есть и существенные различия. В основе учета кассовых операций лежит ведомость учета, аналогичная банковской, с небольшими различиями. Ведомость учета выполняет функции базы данных для генерации расходных и приходных ордеров, а также кассовой книги. Поскольку каждая операция по кассе проводится через расходные и приходные ордера, нет необходимости ведения отдельного журнала их учета, этим журналом является сама ведомость. Нет также необходимости разделения программной и учетной части. Все находится в одной книге Excel – Kacca1(2, 3, 4), состоящей из следующих листов:

• форма расходного ордера;
• форма приходного ордера;
• три листа ведомости кассовых операций (месяц1(2, 3));
• результаты;
• три листа кассовой книги (один на каждый месяц);
• информация;
• титульный лист кассовой книги.

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

=МАКС([касса3.xls]месяц3!А:А) [12]

То есть вычисляется максимальный номер листа кассовой книги третьего квартала (в примере мы имеем дело с кассой четвертого квартала). Аналогичные формулы находятся в ячейках М3 и N3.

Далее в ячейки столбцов М и N заносим формулы для расчета очередных номеров кассовых ордеров. В ячейке N6 записана следующая формула:

=ЕСЛИ(Н6>0;(СЧЁТ(Н$4:Н6)+N$3);"") [13]

ПРИМЕЧАНИЕ. Столбцы М и N являются вспомогательными и в текущей работе, а тем более при распечатке документа, должны быть скрыты.

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

Значение номера листа формируется аналогично. В частности в ячейке А9 находится формула:

=ЕСЛИ(ЕЧИСЛО(19);МАКС(А$3:А8)+1;"") [14]

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

И наконец, в ячейки столбца С помещаем формулы, создающие название документа. К примеру, формула в ячейке С6:

=ЕСЛИ(ЕЧИСЛО(М6);"по "&M6;ЕСЛИ(ЕЧИСЛО (N6);"po "&N6;"")) [15]

Функция работает следующим образом. Если в данной строке дебетовая операция, то текст по (приходный ордер) сцепляется с числом из столбца М; если же операция кредитовая, текст ро (расходный ордер) сцепляется с числом из столбца N.

Здесь, как и в предыдущем случае, мы столкнулись с функцией проверки свойств и значений ЕЧИСЛО, которая проверяет, является ли числом содержимое или результат ячейки.

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

Рис. 9.10. Ведомость учета кассовых операций

Теперь перейдем непосредственно к документам. Формы приходного и расходного ордеров стандартны и приведены на рис. 9.11 и 9.12.

Формульная часть по своей идеологии аналогична банковскому платежному поручению, но с некоторыми нюансами. Будем рассматривать ее на примере приходного ордера (см. рис. 9.11).

Сумма прописью (ячейка В18) использует функцию дентекст, формула:

=PERSONAL.XLS!дентекст(I9) [16]

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

Рис. 9.11. Приходный кассовый ордер

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

• в I9 - сумму;
• в J8 - дату;
• в D14 - корреспондирующий бухгалтерский счет;
• в С15 - плательщика;
• в С17 - назначение платежа.

Формулы в перечисленных ячейках аналогичны друг другу. Например, формула в ячейке С17 выглядит следующим образом:

=ЕСЛИ(номер_пл.п.<=МАКС(месяц1!М:М);ПРOСМOТР(номер_пл.п.;месяц1!М:М;месяц1!Е:Е); ЕСЛИ(номер_пл.п.<=МАКС(месяц2!М:М);ПРOСМOТР(номер_пл.п.;месяц2!М:М;месяц2!Е:Е); ЕСЛИ(номер_пл.п.<=МАКС(месяц3!М:М);ПРОСМОТР(номер_пл.п.;месяц3!М:М;месяц3!Е:Е)))) [17]

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

Введенный в ячейку I8 (имя номер_пл.п.) номер ордера сравнивается с максимальным значением 1-го месяца, затем 2-го и 3-го, и таким образом осуществляется выбор. Функция ПРОСМОТР ищет значение номера ордера в столбце М и возвращает значение ячейки, расположенной в той же строке (в данном случае в столбце Е). Как уже говорилось, преимущество ее перед функцией ВПР заключается в том, что расположение столбца, из которого выдается результат, и столбца, в котором осуществляется поиск, может быть произвольным, а в функции ВПР результат может быть выдан только из столбца, расположенном левее того, в котором ищется значение. Формулы в ячейках J8, D14 и С17 аналогичны только что рассмотренной.

В ячейке С15 та же формула [17], но несколько усложненная, так как содержит ссылку на лист Информация, чтобы отразить имя плательщика полностью и в соответствующем падеже:

=ЕСЛИ(номер_пл.п.<=МАКС(месяц1!М:М);ПРОСМОТР(номер_пл.п.;месяц1!М:М;месяц!!Е:Е); ЕСЛИ(номер_пл.п.<=МАКС(месяц2!М:М);ПРОСМОТР(номер_пл.п.;месяц2!М:М;месяц2!Е:Е); ЕСЛИ(номер_пл.п.<=МАКС(месяц3!М:М);ПРОСМОТР(номер_пл.п.;месяц3!М:М;месяц3!Е:Е)))) [18]

Рис. 9.12. Расходный кассовый ордер

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

=ЦЕЛОЕ(I9)&"-"&PERSONAL.XLS!rmrazr(I9;-1)&PERSONAL.XLS!rmrazr(I9;-2) [19]

Корешок приходного ордера содержит в формульной части в основном ссылки на основную часть ордера, за исключением ячейки O11, содержащей формулу:

=ЦЕЛ0Е(I9)&" руб."&PERSONAL.XLS!rmrazr(I9;-1)&PERSONAL.XLS!rmrazr(19;-2)&" коп." [20]

Формула [20] обеспечивает представление денежной суммы в числовом виде с рублями и копейками.

Расходный ордер генерируется аналогично и в дополнительном описании не нуждается. Лист Excel, содержащий расходный ордер, изображен на рис. 9.12.

Кассовая книга

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

Рассмотрим лист первого месяца. Он изображен на рис. 9.13. Как видите, информация в нем полностью соответствует данным ведомости (см. рис. 9.10).

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

Ячейка D4 - это ссылка на сальдо предыдущего листа. Формула для нее:

=ВПР(Е1-1;инф1;9;ЛОЖЬ) [21]

где инф1 - имя интервала, содержащего всю информацию листа месяц1.

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

Ячейка В1 содержит дату, соответствующую номеру листа кассовой книги. Формула в ней выглядит так:

=ВПР(Е1;инф1;2;ЛОЖЬ) [22]

Информация в ячейке В1 отображается в текстовом виде, наиболее близком к привычному. Формулы в столбцах В-Е однотипны и должны быть вам понятны. Они отображают информацию из соответствующих столбцов ведомости кассовых операций из строки, содержащей данный кассовый ордер. Чуть сложнее формула в столбце В, так как она сцепляет информацию из двух полей, но это уже знакомо. Единственный нюанс заключается в том, что необходимо исключить данные об ошибке или нулевую информацию при отсутствии в строке кассового документа. Для этого служит функция ЕНД или ЕПУСТО из уже знакомой категории функций проверки свойств и значений. В качестве примера приводим формулу из ячейки В6:

=ЕСЛИ(ЕНД(ВПР(А6;inf1;2;ЛОЖЬ));"";ВПР(А6;inf1;2;ЛОЖЬ)&""&ВПР(А6;inf1;3;ЛОЖЬ)) [23]

Рис. 9.13. Кассовая книга

Алгоритм работы этой формулы следующий. Если в ведомости кассовых операций есть ордер с таким номером, отображается его информация, если нет - оставляется пустое место.

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

=ВПР(В1;месяц1!В4:Л9;2;ЛОЖЬ) [24]

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

=ЕСЛИ(СТРОКА(А6)-СТРОКА(А$5)<=ПОИСКПОЗ($Е$1;месяц1!А:А;0)-ПОИСКПОЗ(А$5;месяц1!С:С;О);ИНДЕКС(инф1;СТРОКА(А6)-СТРОКА(А$5)+ПОИСКПОЗ(А$5;месяц1!С:С;0)-1;3);"") [25]

Попробуем ее проанализировать. Начнем с самого простого. Функция СТРОКА возвращает просто номер строки аргумента. Функция ПОИСКПОЗ возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. Скажем, первая такая функция в формуле:

ПОИСКПОЗ($Е$1;месяц1!А:А;0)

Она обозначает позицию в столбце А на листе месяц1 ячейки со значением, равным значению ячейки Е1 на данном листе. Параметр 0 означает, что мы ищем точное совпадение (в этой функции еще могут использоваться параметры -1 и +1 для приближенных совпадений).

Если говорить проще, то в данном случае функция ПОИСКПОЗ возвращает просто строку, в которой расположен элемент с данным значением. То есть первая часть формулы [25], представляющая операцию сравнения, проверяет, не является ли положение текущего элемента кассовой книги последним на данном листе, который характеризуется наличием номера листа в соответствующей строке ведомости кассовых операций.

Разность функций ПОИСКПОЗ с аргументами Е1 и А5 есть не что иное, как число кассовых операций, совершенных в данный день. Мы сравниваем количество операций, отраженных в кассовой книге, с соответствующим количеством операций на данную дату в ведомости, и как только обнаруживаем превышение, прекращаем отображение информации. А пока его нет, нам необходимо последовательно выдавать названия документов, прошедших через учет в данный день, то есть задачей является отображение информации из соответствующего массива на листе месяц1. Данную операцию обеспечивает функция ИНДЕКС, которая возвращает значение элемента таблицы или массива, заданного номером строки и номером столбца.

Функция ИНДЕКС из класса Ссылки и массивы может иметь два синтаксиса. В формуле [25] используется следующий:

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

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

Счета-фактуры

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

Некоторые сложности может создавать ситуация, когда в одном счете-фактуре необходимо отразить несколько позиций (например, несколько видов товара). В этом случае заносим их в книгу продаж отдельными строками, но с одинаковыми реквизитами счета-фактуры. Генерация информации во второй и последующих информационных строках осуществляется на тех же принципах и с применением тех же функций СТРОКА, ИНДЕКС и ПОИСКПОЗ, что и при генерации кассовой книги.

Генерация счетов-фактур осуществляется в книге Bank. Это удобно, поскольку там находится вся информация о предприятиях и банках.

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

Процесс выписки счета-фактуры и заполнения книги продаж осуществляется в указанной далее последовательности.

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

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

В столбце Е должен отобразиться ИНН данного предприятия, поскольку туда введена формула:

=ВПР(С14;предприятия!А:Н;5;ЛОЖЬ) [26]

В столбцы O-X заносим необходимые реквизиты счета-фактуры: наименование товара, единицу измерения, количество, сумму и т. д. Автоматическое заполнение столбцов G-L описано ниже. В столбце Н занесена формула

=ЕСЛИ(T14=20%;Q14*R14;0) [27]

Рис. 9.14. Книга продаж (фрагмент 1)

Рис. 9.15. Книга продаж (фрагмент 2)

Формула [27] отображает произведение цены на количество в случае, если ставка НДС равна 20 %. Аналогичные формулы содержатся в столбцах J и L для ставки НДС 10 % и 0 % соответственно. Такой алгоритм выбран исходя из предпосылки, что у предприятия нет деятельности, освобожденной от НДС. Если она есть, но нет НДС по ставке 0 %, последняя формула переносится в столбец М. Если есть и то и другое, то либо вводится дополнительная логика, либо эта информация в данной строке вносится вручную.

В столбцах I и К выделяется сумма НДС по формуле (приводится для столбца I):

=(H14-$S14)*$T14 [28]

Далее в соответствующую ячейку формы счета-фактуры (рис. 9.16) заносим ее номер и получаем готовый счет-фактуру аналогично тому, как это делалось с платежными поручениями и кассовыми ордерами. Собственные реквизиты заносятся путем ссылок на соответствующие ячейки листа Реквизиты. Например, формула в ячейке В4 выглядит просто:

=Реквизиты!В2 [29]

Остальные реквизиты счета-фактуры берутся из книги продаж через функцию ВПР. В частности, в ячейке А17 формула следующая:

=ВПР(D3;'книга продаж'!В:Т;14;ЛОЖЬ) [30]

Если счет-фактура содержит несколько позиций, принцип их отображения аналогичен использованному в кассовой книге. Рассмотрим в качестве иллюстрации формулу в ячейке А18:

=ЕСЛИ(ИНДЕКС(инф_сф;СТРОКА($А18)-СТРОКА($А$17)+ПОИСКПОЗ($O$3;'книга продаж'!В:В;0);2)=$O$3;ИНДЕКС('книга продаж'!O:O;СТРОКА($А18-СТРОКА($А$17)+ПОИСКПОЗ($O$3;'книга продаж'!$В:$В;0);1);"") [31]

Формула [31] находит в книге продаж в столбце В номер, смещенный относительно номера счета-фактуры на количество строк, отделяющих строку текущего элемента от первой строки счета-фактуры, и если они совпадают, отображает соответствующую информацию. Аналогично работают формулы в других ячейках строки.

Зарплата

Учет зарплаты и генерация документов по зарплате осуществляется в книге Excel Зарплата, состоящей из листов:

• Реквизиты;
• Лицевые счета;
• Ведомость;
• Отчисления;
• Уч_рег.

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

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

Рис. 9.16. Счет-фактура

Рис. 9.17. Лицевой счет (фрагмент 1)

Лицевой счет - это основа для учета зарплаты.

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

Лицевой счет

Фрагменты лицевого счета приведены на рис. 9.17, 9.18 и 9.19.

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

Рис. 9.18. Лицевой счет (фрагмент 2)

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

В столбце А в каждой строке лицевого счета проставляется его номер. Это сделано для обеспечения возможности индивидуального учета отчислений на пенсионное страхование и др.

В столбцах B-G содержится информация об основных видах начислений из фонда оплаты труда, причем в столбце Н суммируется информация по всем доходам, облагаемым по основной ставке (на данный момент 13 %). Кроме ФОТ это в основном больничный лист, что и отражено в лицевом счете.

Следующие три столбца предусмотрены для доходов по другим налоговым ставкам и необлагаемым доходам. В столбце М суммируются все полученные доходы.

В столбцы N и O вносятся налоговые вычеты, при этом мы проводим разделение не по видам вычетов (стандартные, социальные, имущественные), а по видам их математического влияния на подоходный налог. Поэтому выделены вычеты без ограничения и с ограничением. При этом думать об этих самых ограничениях нет необходимости, за вас это проделает формула, содержащаяся в столбце Р:

=N5+ЕСЛИ(СУММ($I4:I5)<=реквизиты!$В$27;O5;0) [32]

В ячейке В27 листа Реквизиты находится ограничение по вычетам.

Поле Необлагаемая сумма в нашем примере повторяет Всего вычетов с учетом ограничения. Введено оно для той ситуации, когда сумма вычетов больше начисленной суммы. Приводим для примера формулу в ячейке Q5:

=ЕСЛИ(СУММ(Р4:Р5)<=СУММ(I4:I5);(СУММ(Р4:Р5)-СУММ(Q4:Q4));I5) [33]

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

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

В столбцах Х-АЕ осуществляется расчет индивидуальных отчислений с зарплаты: ЕСН, пенсионный фонд, соцстрах. Сложность здесь представляет пенсионный фонд, поскольку ставка отчислений зависит от пола и возраста человека. Поэтому данная информация обязательно присутствует в общей части каждого лицевого счета. Год - в столбце Е той же строки, где Ф.И.О., а пол - в столбце F той же строки, обозначается буквами "М" и "Ж".

Описанная выше часть лицевого счета показана на рис. 9.19.

Рис. 9.19. Лицевой счет (фрагмент 3)

В столбце X на основании данных пола и возраста формируется номер категории с точки зрения распределения между страховой и накопительной частью в порядке возрастания накопительной части. Формула в ячейке Х5 выглядит следующим образом:

=ЕСЛИ($Р1="м";ЕСЛИ($Е1<реквизиты!В$35;1;ЕСЛИ(Е1<реквизиты!С$35;2;3)); ЕСЛИ($Р1="ж";ЕСЛИ(Е1<реквизиты!В$36;1;ЕСЛИ(Е1<реквизиты!С$36;2;3));0)) [34]

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

=Х5

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

В ячейке Y4 ссылка:

=реквизиты!$В$37 [35]

В ячейке Z4 - формула расчета коэффициента страховой части:

=ЕСЛИ(Х5=1;реквизиты!В$38;ЕСЛИ(Х5=2;реквизиты!С$38;реквизиты!D$38) [36]

В ячейке АА - формула расчета накопительной части:

=реквизиты!$В$22-Y4-Z4 [37]

В формуле [37] из общей суммы вычитается федеральный бюджет и страховая часть.

Значения составляющих отчислений в пенсионный фонд рассчитываются просто как произведения ФОТ (без КНО) на коэффициент. В частности, формула в ячейке Y5 следующая:

=ОКРУГЛ(Y4*($G5-$F5);2) [38]

Формулы в ячейках Z5 и АА5 аналогичны [37], а им также аналогичны формулы других столбцов.

Что же касается расчета остальных отчислений, то там все то же самое, только коэффициенты берутся непосредственно с листа Реквизиты. В частности, формула в ячейке АВ5 следующая:

=ОКРУГЛ(реквизиты!$B$21*($G5-$F5);2) [39]

Расчет ЕСН производится без учета регрессивных ставок. Для малого предприятия, имеющего, как правило, не очень высокий уровень зарплаты, они не очень актуальны. Если они все-таки появляются и есть необходимость автоматизации их расчета, то алгоритм, в принципе, тот же, но таблица в части расчета ЕСН становится более громоздкой. Создаем дополнительные таблицы, аналогичные изображенной на рис. 9.19, и к каждой из них добавляем столбец с налоговой базой по данной ставке.

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

Ведомости

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

Номер месяца равен номеру ведомости. Например, рассмотрим формулу в ячейке С18:

=ВПР($1$12;лсч4;С$14-1;ЛОЖЬ) [40]

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

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

=PERSONAL.XLS'!сумпроп(W42)&"("&W42&"p.)" [41]

Рис. 9.20. Расчетно-платежная ведомость

Формула [41 ] возвращает сумму прописью и в скобках сумму в виде числа. Сумм; прописью реализуется с помощью нашей пользовательской функции Сумпроп.

Вспомогательные документы

На рис. 9.21 приведен лист Отчисления книги Зарплата.

В ячейках С2-С7 осуществляется расчет сумм ЕСН, отчислений в пенсионный фонд отчислений по страхованию от несчастных случаев и налога на доходы физических лиц.

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

=СУММЕСЛИ(Лиц_сч!В:В;ном_мес;Лиц_сч!2:2) [42]

Остальные части рассчитываются аналогично.

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

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

В столбцах D-К осуществляется автоматическое формирование платежных поручений по налогам и отчислениям с зарплаты в соответствии с нормативными документами, действовавшими в 2002 году. В середине 2003 года была принята новая форма платежного поручения, в которой каждый реквизит имеет свое место, но ситуация, когда мы имеем дело со стандартным текстом с отдельными переменными параметрами, достаточно типична и с помощью Excel она решается достаточно просто и изящно. Поэтому мы решили привести данный пример.

Рис. 9.21. Лист Отчисления книги Зарплата

Формула в ячейке D2:

=MAKC('D:\ROM\Бухг2002\[bank.xls]плат.поручения'!$А:$А)+1 [43]

Данная формула генерирует очередной номер платежного поручения.

В ячейках Е2-Е7 формируется дата платежного поручения как текущая плюс смещение, задаваемое в ячейке Е1.

Далее в столбцах F-H располагается формулировка текста платежного поручения в соответствии с тем, как это было принято раньше в файле Bank. Первые два столбца просты, они содержат обыкновенный текст; интерес вызывает столбец Н. Посмотрим на формулу в ячейке Н4:

="1010510; "&реквизиты!В32&"; "&"ТП; МС."&ЕСЛИ (ном_мес<10;0;"")&ном_мес&"."&реквизиты!В1&";0;"&"10."&ЕСЛИ(ном_мес<10;0;"")&ном_мес+1&"."& реквизиты!В1&";"&ЕСЛИ(ном_мес<12;" АВ;";" НС;")&" ""ПФ фед.бюджет (рег. № "&реквизиты! С22&")""" [44]

Формула [44] возвращает текст с назначением платежа, при этом все необходимые реквизиты (процент отчислений, рег. №, месяц и год) формируются автоматически на основании информации, находящейся на листе Реквизиты, и номера расчетно-платежной ведомости. Сформированную таким образом информацию достаточно скопировать в режиме Специальная вставка > Значения на лист Платежные поручения книги Bank. Таким образом можно избавиться от большей части механической работы.

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

Мы рассматривали вопросы начисления зарплаты с точки зрения генерации документов, но книга Excel Зарплата является в то же время и регистром бухгалтерского учета. Бухгалтерские проводки формируются на листе Уч_рег. Форма аналогична форме листа Результаты бухгалтерских регистров, рассмотренных в предыдущей главе. Расчет осуществляется как сумма по критерию номер месяца информации соответствующего столбца лицевых счетов с помощью функции СУММЕСЛИ. Ничего принципиально нового здесь нет, поэтому подробно этот вопрос рассматривать нет смысла.

Бухгалтерская отчетность

Основными формами бухгалтерской отчетности являются бухгалтерский баланс и отчет о прибылях и убытках. Для малого предприятия это единственные формы.

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

Чтобы не усложнять формулы в балансе, в форму сальдовой ведомости, приведенную на рис. 8.15, также заносится эта информация. Осуществляется это привычным способом с помощью функции ВПР или ПРОСМОТР.

Теперь посмотрим, как это реализуется в балансе (рис. 9.22). Рассмотрим формулу в ячейке F10 баланса:

=СУММЕСЛИ('[Гл-кн4.xls]Сальдовая ведомость'!$С$3:$С$80;$В10;'[Гл-кн4.xls]Сальдовая ведомость'!$М$3:$М$80)-СУММЕСЛИ('[Гл-кн4.xls]Сальдовая ведомость'!$D$3:$D$80;-$В10;'[Гл-кн4.xls] Сальдовая ведомость '!$N$3:$N$80) [45]

Все должно быть понятно. Формула [45] суммирует остатки но тем счетам, у которых в поле строка актива проставлен номер данной строки с положительным знаком, и вычитает по тем, у которых номер с отрицательным знаком. В данном случае речь идет об основных средствах, поэтому из остатка по счету 01 будет вычтен остаток по счету 02.

Рис. 9.22. Баланс

Столбцы Е, F, G, Н баланса являются вспомогательными и служат для подготовки информации для отчета. Обычно они скрыты. Собственно отчетные данные формируются в столбцах С и D. В частности, формула в ячейке С10 выглядит так:

=ОКРУГЛ(F10/1000;0)+H10 [46]

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

Для малых предприятий с небольшими суммами оборотов и остатков налоговые органы могут потребовать представить бухгалтерскую отчетность в рублях. В этом случае формула в ячейке С10 должна выглядеть так:

=ОКРУГЛ(Р10;0)+Н10 [47]

ПРИМЕЧАНИЕ. Для быстрого перехода от одного типа формул к другому лучше всего воспользоваться командой Правка > Заменить. Для перехода от тысяч к рублям на вопрос "найти" мы отвечаем "/1000;", а на вопрос "заменить на" - ";", после этого щелкаем на кнопке "заменить все". Естественно, предварительно должен быть выделен весь фрагмент, содержащий формулы, в которых необходимо осуществить замену.

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

Строки, объединяющие информацию, должны содержать соответствующие суммирующие формулы. Например, ячейка D22 содержит формулу:

=СУММ(D3;D8;D11;D15;D19:D21) [48]

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

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

Ячейка G37 (стр. 150):

='[Гл-кн4.xls]Оборотная ведомость'!$N$31"(кредитовый оборот по ссч. 68,3 - налог на прибыль) [49]

Ячейка G36 (стр. 140):

='[Гл-кн4.xls]Сальдовая ведомость'!$М$59-'[Гл-кн4.xls]Сальдовая ведомость'!$М$59" (разность кредитового и дебетового сальдо по сч.99 - прибыль) [50]

Ячейка G26 (стр. 050):

=СУММ(-G28;G29;-G30;-G31;G32;-G34-G35;G36)" (выполняем требования нормативных документов в обратном порядке) [51]

Ячейка G25 (стр. 040):

=ОКРУГЛ('[Гл-кн4.xls]Оборотная ведомость'!$М$15;0)" (оборот по сч. 26 - общехозяйственные расходы) [52]

Ячейка G14 (стр. 010):

='[Гл-кн.xls]Оборотная ведомость'!$N$53/1,2" [53]

Рис. 9.23. Отчет о прибылях и убытках (фрагмент)

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

Налоговая отчетность

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

Любую налоговую декларацию или расчет можно разделить на следующие составляющие:

• общие сведения (шапка документа);
• сведения о налоговой базе;
• ставки налогов;
• сумма налога за отчетный период;
• сведения о начислениях за предыдущие периоды.

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

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

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

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

Заключение

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