Файл: Автоматизация расчетов в бухгалтерском учете с помощью MS Excel.docx
Добавлен: 25.10.2018
Просмотров: 1427
Скачиваний: 8
Поскольку формула для расчета дебетового оборота записана и действует на листе «Счета», а интервал просмотра и суммируемый интервал заданы на листе «Проводки», то перед обозначениями границ этих интервалов заданы названия данного листа. В противном случае Excel рассматривал бы эти интервалы, как относящиеся к листу на котором размещена формула. Границы диапазонов заданы в смешанной форме, при которой используется абсолютная адресация строк начала интервалов. Это нужно для того, чтобы при копировании формулы для других счетов границы интервалов оставались бы неизменными. Более подробно о порядке адресации ячеек см. справочную систему Excel. Выбор нижней границы интервалов просмотра и суммирования функции СУММЕСЛИ() следует установить, так чтобы диапазон охватывал все проводки листа «Проводки». В нашем случае это интервал строк 2-5000. В зависимости от примерной оценки числа используемых проводок можно установить другую верхнюю границу. Если формула введена правильно, то она автоматически сработает. В нашем примере она даст значение 12000.
Для расчета кредитового оборота в ячейку E3 следует ввести формулу
=СУММЕСЛИ(Проводки!B$2:B$5000;$A3;Проводки!$C$3:$C$5000)
Она отличается от предыдущей формулы лишь тем, что ищет совпадения кода счета из данной строки листа «Счета» со счетами в столбце B листа «Проводки». Поскольку мы договорились, что в этом столбце задаются кредитуемые счета проводок, то это означает, что формула вычисляет кредитовый оборот счета из данной строки расчетной таблицы.
Для расчета исходящего сальдо счета в ячейку F3 введем формулу
=C3+D3-E3
Ее смысл очевиден: входящий остаток складывается с дебетовым оборотом, из которого вычитается кредитовый оборот.
На этом ввод данных для первой строки закончен. В условиях нашего примера получен результат, представленный на рис. 6.21.
Рисунок 6.21 – Остатки и обороты по первой строке расчетной таблицы
Теперь, до заполнения кодов, наименований и входящих остатков счетов можно сразу скопировать формулы столбцов D,E,F во все строки расчетной таблицы, где будут размещаться используемые счета. Проще всего это сделать, выделив ячейки D3,E3,F3 и, «ухватившись» мышью за правый нижний угол ячейки F3 (мышиный курсор должен принять форму креста) «растянуть» выделение на нужное число строк. Число строк для копирования можно оценить «на глазок». Если счетов окажется больше, то можно повторить копирование, если меньше – тоже не страшно, поскольку там где, коды счетов не заданы, формулы просто будут показывать нули.
Можно поступить и иначе. Сначала, не думая о формулах задать коды всех используемых счетов (субсчетов), их названия и входящие остатки и только потом «размножить» формулы для всех нужных строк.
По мере заполнения расчетной таблицы данными о счетах автоматически, на основе информации уже введенных проводок, будут рассчитываться их обороты и исходящие остатки. При вводе новых проводок обороты и исходящие остатки использованных в них счетов также будут пересчитываться автоматически.
Размещение информации на экране
Таблицы проводок и оборотов счетов размещены на разных листах книги Excel. Переключаться между ними несложно. Однако иногда желательно видеть на экране оба листа одновременно, например лист «Проводки» слева, а лист «Счета» справа. Этого можно достичь следующим образом.
Откроем новое окно (Окно Новое). Далее разместим окна на экране так, чтобы они располагались рядом (Окно Расположить, пометить пункт «Рядом» и нажать Ok). Теперь в левом окне выберем лист «Проводки», перейдем в правое окно и выберем в нем лист «Счета». Оба окна размещаются на экране одновременно. Далее можно с помощью мыши «подогнать» размеры окон и размеры колонок таблиц так, чтобы одновременно была видна наиболее значимая информация. Можно также отрегулировать масштаб представления информации в каждом окне (Вид Масштаб).
Мы отрегулировали размеры окон так, как приведено на рис. 6.22. При используемом нами разрешении экрана 800х600 точек для лучшей обозреваемости пришлось установить в правом окне масштаб 75%.
Если применяется экранное разрешение 1024х768 и выше, то для наших таблиц можно оставить масштаб 100% и ограничиться только «подгонкой» размеров окон. Если по каким-либо причинам необходимо работать с экранным разрешением 640х480, то, скорее всего, лучшим решением будет даже и не пытаться одновременно обозревать обе таблицы и просматривать их по очереди.
Рисунок 6.22 – Одновременное представление двух рабочих листов на экране
Контроль правильности ввода информации
При создании расчетной таблицы на листе «Счета» следует ОБЯЗАТЕЛЬНО задать в ней ВСЕ счета (субсчета), используемые в проводках. Если счет имеет субсчета, то в расчетной таблице следует задавать только субсчета. Контроль правильности задания информации можно проводить, сравнивая значения итогов по дебетовому и кредитовому оборотам в строке 2 расчетной таблицы. Если они не совпадают, то это означает, что либо не все коды счетов, используемые в проводках, указаны в таблице, либо какие-то коды счетов на листе «Проводки» заданы не так, как они заданы на листе «Счета». В приведенном на рис. 6.22 примере хорошо видно расхождение между суммами оборотов, вызванное тем, что не все используемые в проводках счета определены в расчетной таблице.
Полезным элементом контроля являются также итоги по колонке C, содержащей входящие остатки счетов. Поскольку мы договорились, что дебетовые остатки задаются со знаком «плюс», а кредитовые – со знаком «минус», то если в таблице отражены все используемые счета, то общий итог по этой колонке должен быть равен нулю (равенство итогов баланса). Ненулевая сумма означает неполноту исходной информации или наличие ошибок в задании остатков. На рис. 6.22 итог по входящим остаткам не равен нулю. Следовательно, остатки не сбалансированы.
Расхождение итогов только указывает на наличие ошибок. А как выявить проводки, в которых использовались счета, не определенные в расчетной таблице? Конечно, это можно сделать методом «пристального разглядывания». Однако есть способ и получше. Можно «заставить» 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)