Файл: Информационные бухгалтерские системы.docx

Расхождение итогов только указывает на наличие ошибок. А как выявить проводки, в которых использовались счета, не определенные в расчетной таблице? Конечно, это можно сделать методом «пристального разглядывания». Однако есть способ и получше. Можно «заставить» Excel автоматически помечать те проводки, в которых используются счета, отсутствующие в расчетной таблице.

Для этого перейдите на лист «Проводки» и перед столбцом комментария к проводкам вставьте два столбца. Во второй строке одного из них задайте формулу:

=ЕСЛИ(ВПР(A2;Счета!$A$3:$A$1000;1)=A2;»«;»?»)

Приведенная формула проверяет наличие дебетуемого счета проводки в списке счетов на листе «Счета». Для этого здесь используется функция ВПР – «Вертикальный просмотр». Подробное описание этой функции можно найти в справочной системе Excel. Мы же остановимся на особенностях ее применения в рассматриваемом нами случае. В данном контексте она работает следующим образом.

Отыскивается значение, заданное первым аргументом, в списке значений, задаваемом вторым аргументом. Если оно найдено, то в качестве значения возвращается та же самая величина. В противном случае результатом выполнения функции ВПР является значение наиболее «близкое» к тому, которое ищется. Поскольку в качестве первого аргумента функции выступает ячейка, содержащая дебетуемый счет проводки, а в качестве области поиска выступает список кодов счетов (Счета!$A$3:$A$1000), то если дебетуемый счет проводки имеется в списке счетов, то в качестве значения эта функция вернет код того же счета. Если этот счет в списке счетов отсутствует, то будет возвращено иное значение. Какое – нас не волнует (детали поиска см. в справочной системе Excel). Главное то, что возвращенное значение будет отличаться от кода дебетуемого счета.

Таким образом, если дебетуемый счет занесен в список кодов счетов на листе «Счета», то выражение ВПР(A2;Счета!$A$3:$A$1000;1)=A2 будет истинно.

В противном случае, это выражение будет ложно, так как код счета, возвращенный функцией ВПР, будет отличаться от кода дебетуемого счета. В первом случае функция ЕСЛИ занесет в ячейку пустое значение, а во втором – поставит знак вопроса. Иными словами, если код дебетуемого счета отсутствует в списке счетов листа «Счета», то рядом с проводкой будет поставлен знак вопроса. Тем самым мы задали правило контроля дебетуемого счета.

Аналогичное правило можно задать и для кредитуемого счета. Для этого в соответствующую колонку следует занести формулу:

=ЕСЛИ(ВПР(B2;Счета!$A$3:$A$1000;1)=B2;»«;»?»)

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

Рисунок 6.23 – Проводки, содержащие счета, не проставленные в расчетной таблице, автоматически помечены знаком вопроса

Теперь сразу видно, где и что надо исправить.

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

Рисунок 6.24 – Ошибки исправлены

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

Кстати отметим, что свойство АВТОМАТИЧЕСКОГО равенства итогов по оборотам свойственно также практически всем российским бухгалтерским программам в силу заложенных в них алгоритмов и способов представления записей на счетах. В некоторых западных системах автоматизации за счет использования иных моделей представления информации возможности контроля подобного рода ошибок более развиты.

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

Переход к следующему периоду

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

Закройте файл рабочей книги с данными текущего периода, если он открыт.

Скопируйте его, присвоив другое имя. В условиях нашего примера мы назвали исходный файл GL9901.xls. Поэтому новому файлу мы дали имя GL9902.xls.

Загрузите новый файл в Excel.

Теперь требуется перенести значения колонки «Исходящий остаток» (F) листа «Счета» в колонку «Входящий остаток» (C). Отметим, что нам нужно переносить именно полученные значения, но не формулы.

Напомним порядок выполнения указанных действий.

Выделите блок ячеек, содержащий искомые значения. В нашем примере это диапазон F3:F20. Скопируйте его в буфер промежуточного хранения (Правка ® Копировать). Вокруг выделенного блока появится «бегущая» пунктирная линия.

Установите активной ячейку C3. Выберите пункт меню «Правка ® Специальная вставка». В окне параметров специальной вставки пометьте пункт «Значения» и нажмите клавишу OK.

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

Теперь можно вносить новые проводки, а обороты счетов и исходящие остатки будут рассчитываться автоматически.

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

Расчет итогов по одинаковым проводкам

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

Создайте лист «Обороты». В ячейку A1 введите текст «Дебет», в ячейку B1 – «Кредит», в ячейку C1 – «Сумма». Столбцы A и B должны быть отформатированы так, чтобы все вводимые сюда данные воспринимались как текстовые.

Структура таблицы на листе «Обороты» во многом повторяет структуру таблицы ввода проводок на листе «Проводки». Так же, как и там, в столбце A должен задаваться дебетуемый счет проводки, а в столбце B – кредитуемый счет. Отличие состоит в том, что на листе «Проводки» любая пара корреспондирующих счетов может фигурировать многократно, а на листе «Обороты» она должна размещаться лишь единожды.

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

Порядок заполнения листа «Обороты» произволен. Для примера мы поступим следующим образом. Введем в ячейку A2 листа «Обороты» код счета 19, а в ячейку B2 – код счета 60 и попробуем задать формулу, которая бы подвела итоги по всем проводкам листа «Проводки» в которых дебетуемым счетом является счет 19, кредитуемым – счет 60. Как всегда при вводе однотипных формул в электронных таблицах, формулу можно ввести единожды, а потом скопировать на все необходимые ячейки.

Итак, в ячейке C2 листа «Обороты» наберем следующую формулу:

=СУММ(ЕСЛИ(Проводки!$A$2:$A$5000=$A2;1;0)*
ЕСЛИ(Проводки!$B$2:$B$5000=$B2;1;0)*Проводки!$C$2:$C$5000)

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

Обратите внимание, что после нажатия Ctrl+Shift+Enter в строке формул наша формула заключена в фигурные скобки (рис. 6.25).

Рисунок 6.25 – Отображение формулы массива в строке формул Excel

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

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

Сначала остановимся на сути решаемой задачи.

А суть состоит в том, что надо подвести итог по всем суммам проводок на листе «Проводки», у которых код дебетуемого счета совпадает с кодом счета из данной строки столбца A («Дебет») листа «Обороты», а код кредитуемого счета листа «Проводки» с кодом счета данной строки столбца B («Кредит»). Например, проводка в дебет счета 19 с кредита счета 60 в нашем примере на листе «Проводки» встречается дважды – в строке 3 и в строке 12. В первом случае сумма проводки равна 2000, а во втором 4000. Именно эти числа и должны быть просуммированы.

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

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

Первым сомножителем является функция ЕСЛИ:

ЕСЛИ(Проводки!$A$2:$A$5000=$A2;1;0)

Эта функция, в свою очередь, имеет три аргумента. Первый определяет условие (Проводки!$A$2:$A$5000=$A2), второй – значение, которое следует использовать при выполнении этого условия (1), а третий – значение, которое соответствует случаю, когда условие не выполняется (0).

Проверяемое условие задано в форме сравнения диапазона (Проводки!$A$2:$A$5000) с единственным значением ($A2). Именно использование диапазона и позволяет Excel сформировать промежуточный массив. Фактически, значение первого из сомножителей суммируемого массива определяется последовательным сравнением значения из ячейки $A2 листа «Обороты», с ячейками из диапазона $A$2:$A$5000 листа «Проводки». Если их значения совпадают, то первый сомножитель равен 1, в противном случае – нулю. Таким образом, функция ЕСЛИ, заданная в приведенной выше форме, создает массив значений, состоящий из нулей и единиц. Далее, каждый элемент этого массива используется как один из сомножителей при получении массива элементов, суммируемых функцией СУММ.

В нашем примере код счета, содержащегося в ячейке $A2 листа «Обороты» равен 19. Поэтому первый сомножитель, используемый Excel при формировании массива суммируемых значений, равен 1 тогда и только тогда, когда дебетуемый счет проводки на листе «Проводки» имеет код 19. В противном случае первый сомножитель равен нулю.

Второй сомножитель используемый при неявном формировании массива суммируемых функцией СУММ значений также задается функцией ЕСЛИ:

ЕСЛИ(Проводки!$B$2:$B$5000=$B2;1;0)

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

Третий аргумент функции СУММ (Проводки!$C$2:$C$5000) определяет массив значений, в точности соответствующий столбцу C («Сумма») листа «Проводки». Его элементы являются третьим сомножителем при формировании массива значений, суммируемых функцией СУММ.

Таким образом, аргументы функции СУММ строят три массива одинаковой длины. Их значения, имеющие одинаковые номера перемножаются. В результате получается массив такого же размера, содержащий произведения соответствующих элементов. Каждый элемент равен сумме соответствующей проводки тогда и только тогда, когда ее дебетуемый счет совпадает с кодом счета из ячейки $A2, а кредитуемый счет – с кодом счета из ячейки $B2 листа «Обороты». В противном случае элемент суммируемого массива равен нулю, поскольку равен нулю хотя бы один из первых двух сомножителей. Следовательно, получается именно то, что нам нужно: итог по всем проводкам с заданной корреспонденцией счетов.

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

Таблица 6.1 – Процесс формирования Excel промежуточных массивов, используемых при расчете итога по проводкам в дебет счета 19 с кредита счета 60 на основе данных нашего примера