В начало

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

Часть III. Автоматизация бухгалтерского учета

Глава 7. Начинающий бизнес и Excel

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

Журнал хозяйственных операций

Рассмотрим на практике простейшие случаи бухгалтерского учета, используя в качестве основы примеры, рассмотренные в главах 3 и 4.

Для начала выберем самую простейшую систему учета - журнал учета хозяйственных операций.

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

Теперь отразим все перечисленное в журнале операций. В качестве примера на рис. 7.1 показан фрагмент странички из книги Excel, в которой создан Журнал операций.

Рис. 7.1. Журнал хозяйственных операций

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

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

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

Заполнение журнала операций

Прежде всего необходимо автоматизировать занесение сумм из столбца С в те ячейки, которые определяются номерами счетов с столбцах D и Е. Для этого нужно применить формулу, в основе которой лежит логическая функция ЕСЛИ(). Функции можно заносить вручную или с помощью Мастера функций. Делается это следующим образом. Выберем ячейку в первой строке дебета 51 - это ячейка Е3 - и вызовем окно Мастер функций с помощью кнопки , где в категории Логические выберем функцию ЕСЛИ (рис. 7.3).

Функция ЕСЛИ, когда Логическое_выражение "номер счета в ячейке С3 равен номеру счета столбца Е" истинно, возвращает значение суммы из ячейки В3. В противном случае функция возвращает пустое значение, так как в поле Значение_если_ложь введены две кавычки, означающее пустую текстовую строку.

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

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

=ЕСЛИ(С3=Е1;В3;"")

Такую же операцию необходимо, по идее, выполнить со всеми ячейками журнала операций. Конечно, вручную для каждой операции это выполнять не имеет смысла, поэтому следует воспользоваться функцией автозаполнения (см. рис. 2.2). Но вот незадача - при копировании ячейки Е3 в ячейку Е4 формула примет следующий вид:

=ЕСЛИ(С4=Е2;В4;"")

Рис. 7.2. Модифицированный журнал операций

Рис. 7.3. Логическая функция ЕСЛИ

Если в формуле с ячейками С4 (номер счета) и В4 (сумма) все в порядке, то ячейка Е2 содержит совсем не то, что мы хотели. То есть программа Excel буквально выполнила наше требование: откорректировала все переменные нашей формулы. А ведь требовалось оставить адрес ячейки Е1 в копии формулы без изменений. Чтобы при перетаскивании формулы адрес ячейки Е1 не менялся, следует применить абсолютную адресацию, для этого ее адрес модифицируем с помощью символа $ (доллар) следующим образом:

Е$1

Формула после такой коррекции будет выглядеть так:

=ЕСЛИ(С3=Е$1;В3;"")

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

=ЕСЛИ(С4=Е$1;В4;"")

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

=EСЛИ($C3=G$l;$B3;"")

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

=ЕСЛИ($03=Е$1;$В3;"")

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

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

=СУММ(F3:F9)

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

Фрагмент автоматизированного модифицированного Журнала операций показан на рис 7.4 в режиме отображения формул. Чтобы таблица была более понятна, на рисунке показаны только два столбца с формулами, которые относятся к счету 51.

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

=СУММ(В3:В9)

Рис. 7.4. Формулы в журнале операций

Рис 7.5. Расчет оборотов по Дебету и Кредиту

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

=F10+H10+J10+L10+N10+P10+R10

Эту формулу копируем в ячейку S10, как показано на рис. 7.5.

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

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

Рис. 7.6. Вкладка Вид окна Параметры

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

В частности, в арсенале Excel имеется прекрасная функция СУММЕСЛИ. По ее названию нетрудно догадаться, что она складывает цифры, если они отвечают определенным условиям. Структура функции такова:

СУММЕСЛИ(интервал;критерий;сумм_интервал)

Значения параметров функции следующие:

• Интервал - это массив ячеек, по которому проверяется условие суммирования;
• Критерий - это условие суммирования в форме числа, выражения или текста, - например, критерий может быть выражен как 32, "32", ">32", "яблоки";
• Сумм_интервал - это массив ячеек, параллельный массиву Интервал, по которому осуществляется суммирование; ячейки в сумм_интервал суммируются, только если соответствующие им ячейки в аргументе интервал удовлетворяют критерию.

Вернемся к Журналу операций в его первоначальном варианте. Занесем в столбец В, чуть ниже нашего первоначального журнала операций, изображенного на рис. 7.1, перечень всех используемых в примере счетов (рис. 7.7).

В первую строку этого перечня справа от значения счета (то есть в столбец С) занесем формулу:

=СУММЕСЛИ(С$2:С$8;$В12;$В$2:$В$8)

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

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

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

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

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

Рис. 7.7. Учетный регистр

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

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

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

=ЕСЛИ((D12-E12+F12-G12)>=0;(D12-E12+F12-G12);0)

Рис. 7.8. Оборотно-сальдовая ведомость

Аналогичным образом помещаем в ячейку I12 формулу расчета кредитового конечного остатка:

=ECЛИ((E12+G12-D12-F12)>0;(E12+G12-D12-F12);0)

После этого выделяем диапазон H12-I12 и копируем его в ячейки Н13-Н18, массив конечных остатков нами сформирован полностью. В результате получаем оборотно-сальдовую ведомость, изображенную на рис. 7.8.

Примеры деловых расчетов

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

Выборка информации

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

Если рассматривать вопрос чуть шире, функция СУММЕСЛИ решает общую задачу следующего содержания:

У меня есть много всего разного, а мне надо знать, чего и сколько.

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

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

Какая информация, представленная на рис. 7.9, может интересовать менеджера? Во-первых, покупатели; во-вторых, сколько и каких товаров предприятие должно поставить. Для решения таких вопросов создадим еще два листа. Один назовем Покупатели, другой - Товары.

На лист Покупатели поместим в левый столбец наименования покупателей, скопировав их с листа Продажи (рис 7.10), а в столбец справа от него уже знакомую функцию СУММЕСЛИ. В данном случае она будет выглядеть так:

=СУММЕСЛИ(продажи!А1:А19;А2;продажи!Р1:Р19)

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

Рис. 7.9. Лист Продажи

Рис. 7.10. Лист Покупатели

Лист Товары организован аналогично, но по товарам. Кроме их общей стоимости нас интересует их количество, поэтому в эту таблицу добавлено поле Количество (рис. 7.11.)

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

Рис. 7.11. Лист Товары

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

=СУММЕСЛИ(продажи!С1:С19;А2;продажи!Е1:Е19)

В ячейке С2:

=СУММЕСЛИ(продажи!С1:С19;А2;продажи!Р1:Р19)

Поскольку список покупателей и номенклатуру товаров мы устанавливали вручную, постольку что-то мы могли упустить, поэтому для контроля просчитаем суммарные результаты по столбцам Сумма на всех трех листах. На рис. 7.9, 7.10 и 7.11 эта величина везде равна 43340000, это правильно. Аналогичным образом поступим с полем Количество на страницах Продажи и Товары, получив равный результат.

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

Автофильтр

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

В Excel есть функции Автофильтр и Расширенный фильтр. Первый - проще, и в основном обеспечивает все, что необходимо, поэтому будем ориентироваться на него. Для автофильтрации списка необходимо выполнить команду Данные >Фильтр > Автофильтр (рис. 7.12).

Рис. 7.12. Функция Автофильтр

В результате работы этой команды в строке заголовков появляются характерные кнопки раскрывающихся списков. С их помощью допустимо выбрать:

• конкретное значение данного поля;
• пустые значения;
• непустые значения;
• условие фильтрации.

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

Рис. 7.13. Лист Продажи с примененным автофильтром

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

Промежуточные итоги

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

Чтобы получить результат фильтрации, можно вместо простой функции СУММ() использовать функцию Промежуточные итоги(). От простой функции суммирования ее отличает следующий ряд интересных возможностей. Она может осуществлять не только суммирование, но и еще 10 операций: произведение, среднее значение, максимум, минимум и др. Причем при фильтрации она осуществляет операции только с видимой частью. Эта функция может вызываться как обычным способом (вручную или через мастер функций), так и из меню Данные, как специальная операция Итоги; при этом открывается диалоговое окно, изображенное на рис. 7.14.

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

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

Рис. 7.14. Выбор параметров для функции Промежуточные итоги

Рис. 7.15. Лист Продажи с промежуточными итогами

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

Сводная таблица

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

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

Создадим сводную таблицу на основе нашего примера. Для этого выполним команду Данные > Сводная таблица (рис. 7.16), в результате откроется окно Мастер сводных таблиц (рис. 7.17).

Рис. 7.16. Команда Сводные таблицы в меню Данные

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

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

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

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

Рис. 7.17. Указание диапазона сводной таблицы

Рис. 7.18. Окно макета сводной таблицы

Рис. 7.19. Окно макета сводной таблиц - шаг 3

Рис. 7.20. Окно макета сводной таблицы

Рис 7.21. Окно макета сводной таблицы, вариант 1

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

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

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

Рис. 7.22. Сводная таблица, вариант 1

Рис. 7.23. Окно макета сводной таблицы, вариант 2

Рис. 7.24. Сводная таблица, вариант 2

Рис. 7.25. Сводная таблица, вариант 3

Критерии выбора метода обработки информации

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

• функции рабочего листа (в данном случае функция СУММЕСЛИ);
• фильтрация;
• итоги;
• сводная таблица.

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

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

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

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

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

Выбор системы налогообложения

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

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

Рис. 7.26. Лист исх_данные

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

Рис. 7.27. Окно, в котором присваиваются имена

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

В главе 5 сравнивались уровни налогообложения при обычной и упрощенной системе для трех вариантов налогообложения предприятия при обычной системе: освобождение от НДС, обычный НДС и НДС по ставке 0 %. При этом сравнивалось обычное налогообложение с упрощенным в плане общей суммы платежей для различных вариантов процента основных производственных затрат и фонда оплаты труда в общей структуре расходов. В Excel это достаточно удобно получается с помощью таблицы, изображенной на рис. 7.28.

Рис. 7.28. Сравнительный расчет систем налогообложения

При рассмотрении примера на рис. 7.28 следует обратить внимание на ряд моментов.

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

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

Расчет неустойки

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

Ha рис. 7.29 приведена таблица оказания услуг клиенту, с которым требуется выяснить отношения. В столбце А - дата оказания услуг, в столбце В - стоимость этих услуг. Обратите внимание, что на одну дату может приходиться более одной позиции по оказанным услугам. В ячейке С2 - допустимая по договору отсрочка платежа, после которой начинает начисляться неустойка. На другом листе этой же книги создадим таблицу для расчета неустойки (рис. 7.30). Значащие формулы этой таблицы показаны на рис. 7.31.

Рис. 7.29. Оказание услуг клиенту

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

Рис. 7.30. Таблица расчета неустойки

Рис. 7.31. Формулы для расчета неустойки

Заключение

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

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

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

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

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

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