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

Введем в ячейку L1 текст "Счет" и отформатируем столбец L так, чтобы все вводимые в него значения воспринимались как текстовые. Затем для каждого вида ТМЦ проставим код счета, на котором он учитывается.

В нашем примере мы ввели данные, приведенные на рис.6.7.

Рис.6.7. Справочник ТМЦ со сведениями о счетах

Теперь следует перейти на лист "Проводки".

В ячейку А1 введем текст "Дебет", в ячейку В1 - "Кредит", в ячейку С1 - "Сумма", а в ячейку D1 - "Комментарий".

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

Формула для расчета суммы "приходной" проводки имеет следующий вид:

=СУММЕСЛИ(Обороты! L$4:$L$2000;A2;Обороты!$F$4:$F$2000)

Она суммирует значения из столбца F (приход в стоимостном выражении) листа "Обороты" по тем строкам, в столбце L которых содержится код счета, совпадающий с кодом счета, дебетуемым в данной проводке (А2).

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

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

=СУММЕСЛИ(Обороты! L$4:$L$2000;В5;Обороты!$Н$4:$Н$2000)

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

Итак, в колонке М листа "Обороты" будем подсчитывать итоги по НДС, полученному при поступлении каждого вида материальных ценностей. Введем в ячейку M1 текст "НДС", в ячейку М3 - формулу подсчета итогов по колонке =СУММ(М4:М2000), а в ячейку М5 - формулу подсчета итогов по суммам НДС, поступившим с данным видом ТМЦ:

=СУММЕСЛИ(Приход!$А$2:$А$5000;А5;Приход!$G$2:$G$5000)

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

Теперь можно на листе "Проводки" задать формулы для расчета сумм проводок по учету НДС, поступившего с материальными ценностями. Если на счете 19.1 учитывается НДС, поступивший с товарами, то для проводки Д19.1 К60 формула для расчета суммы может быть записана следующим образом:

=СУММЕСЛИ(Обороты!$L$4:$L$2000;"41";Обороты!$М$4:$М$2000)

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

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

Рис.6.8. Автоматически сформированные проводки по движению ТМЦ и смежным операциям

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

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

6.4. Учет основных средств использованием MS Excel

В данном разделе рассматривается простая технология учета основных средств (ОС) с использованием MS Excel. Она предлагает выполнение следующих действий.

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

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

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

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

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

Для создания основной расчетной таблицы выполним следующие действия:

·  в ячейку A1 введем текст «Инвентарный номер»;

·  в B1 – «Наименование основного средства»;

·  в C1 – «Стоимость»;

·  в D1 – «Износ на начало текущего периода»;

·  в E1 – «Норма амортизации(% в год)»;

·  в F1 – «Износ за период»;

·  в G1 – «Износ на конец периода»;

·  в H1 – «Износ по норме».

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

Выделим ячейки A1:H1, отформатируем их по своему вкусу и установим удобную для просмотра информации ширину колонок. В нашем примере мы выбрали режим форматирования со следующими параметрами выравнивания: Формат→Ячейки, вкладка «Выравнивание»: Горизонтальное - по центру, Вертикальное - по центру, а также установили флажок «Переносить по словам» (рис.6.9.).

Далее в ячейку A2 введем текст «Итоги»;

в ячейку C2 - формулу =СУММ(С3:С1000);

в D2 - формулу =СУММ(D3:D1000);

в F2 - формулу =СУММ(F3:F1000);

в G2 - формулу =СУММ(G3:G1000).

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

Рис.6.9. Установка параметров выравнивания текста в заголовке расчетной таблицы

Для строки итогов мы выберем жирный шрифт и пометим ее другим цветом, чтобы она лучше выделялась (Формат →Ячейки, вкладка «Вид», щелкнуть мышью по нужному цвету и по кнопке OK). Можно выбрать иное оформление. Итоги по картотеке намеренно вынесены наверх таблицы (хотя это и непривычно) для того, чтобы они были видны сразу при входе в нее, кроме того, так будет удобнее пополнять картотеку новыми записями: их можно просто вписывать в пустую ближайшую строку. При размещении итогов внизу таблицы пришлось бы еще осуществлять лишнюю операцию вставки строк.

Подготовительные действия выполнены. Наша таблица приняла вид, показанный на рис.6.10.

Рис.6.10. Расчетная таблица с установленными формулами расчета итогов

Теперь можно вводить данные по строкам. В каждой строке должна быть сосредоточена необходимая информация по одному объекту учета. Информационная часть таблицы начинается со строки 3 (рис.6.10).

Порядок заполнения колонок - А (Инвентарный номер), B (Наименование основного средства) и С (Стоимость) - очевиден и в комментариях не нуждается.

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

Для лучшего понимания последовательности расчетов начнем с колонки H (Износ по норме). Данная колонка - чисто технологическая, и мы используем ее только для того, чтобы упростить ввод формул в колонках F и G. Введем в ячейку H3 формулу = С3*E3/1200.

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

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

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

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

=ЕСЛИ(D3+H3>C3;C3-D3;H3).

Она означает, что если износ на начало периода (D3), сложенный с износом, рассчитанным в соответствии с нормой амортизации (H3), больше стоимости данного объекта учета, то износ за текущий период может быть начислен в сумме, не превышающей его остаточной стоимости (C3-D3). В противном случае износ за месяц составляет величину, рассчитанную в соответствии с нормой амортизации (H3).

Износ на конец периода равен износу на начало периода, сложенному с износом за период. Поэтому в ячейку G3 следует ввести формулу:

=D3+F3.

После ввода этой формулы строка таблицы полностью сформирована. В качестве примера мы ввели в нее данные, представленные на рис.6.11.

Поскольку расчетные графы остальных строк должны содержать идентичные формулы, мы просто скопируем имеющиеся формулы, а Excel самостоятельно «подправит» фигурирующие в них индексы по соответствующим номерам строк.

Для этого выделим ячейки F3:H3 и «уцепимся» мышкой за правый нижний угол выделенной области. «Мышиный курсор» должен принять форму черного креста. Теперь «потянем» мышь вниз на столько строк, сколько имеется ОС (один объект - одна строка). Не повредит размножить формулы с запасом. Таблица готова. Остается только перенести в нее данные картотеки (рис.6.12.). Поскольку формулы скопированы с запасом, в тех строках, где не введены необходимые данные, соответствующие колонки имеют нулевое значение.

Рис.6.11. Запись о первом объекте картотеки ОС

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

Рис.6.12. Пример заполнения расчетной таблицы данными картотеки ОС

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

В заголовке (1-я строка) колонки I напишем «Счет отнесения износа» и перенесем на него формат заголовка таблицы. Отформатируем эту колонку так, чтобы все введенные в нее значения воспринимались как текстовые. Для этого выделим колонку I, вызовем карточку настройки форматов ячеек Excel (Формат→Ячейки), выберем раздел «Число», а в списке форматов - строку «Текстовый» и нажмем клавишу OK. В разделе «Выравнивание» установим позиции для данного столбца «по центру».

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

Рис.6.13. Расчетная таблица с установленными кодами счетов отнесения износа

В ячейку A1 нового листа «Проводки» впишем текст «Дебетуемый счет», в ячейку B1 – «Кредитуемый счет», а в ячейку С3 – «Сумма». Отформатируем колонки А и В так, чтобы все вводимые в них значения воспринимались как текстовые и размещались по центру.

Начиная со строки 2, впишем все проводки, которые используются при начислении износа. В колонке А – «Дебетуемый счет» - должны быть перечислены все счета, которые хоть один раз были указаны в колонке I листа «Картотека». В противном случае итоги по износу за месяц и итоги по проводкам не сойдутся.

В ячейку С2 листа «Проводки» впишем формулу:

=СУММЕСЛИ(Картотека! I$3:I$1000;A2;Картотека! F$3:F$1000)

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

В нашем случае функция СУММЕСЛИ() суммирует числа из диапазона F3:F1000 листа «Картотека» в том случае, если значение ячейки А2 листа «Проводки» совпадает с соответствующим значением интервала поиска I3:I1000.

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

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

Скопируем формулу в строки столбца С, содержащие проводки. Их суммы будут автоматически рассчитаны.

Для контроля правильности задания формул расчета сумм проводок в столбце С под проводками разместим формулу расчета суммы по колонке. Полученный здесь итог (рис.6.14.) должен совпадать с итогом по колонке F (Износ за месяц) листа «Картотека».

Теперь изменяя данные в картотеке, мы можем не заботиться о пересчете сумм проводок. Следует только помнить, что при использовании новых кодов счетов отнесения износа на листе «Картотека» для них нужно определять макеты проводок на листе «Проводки», вставляя новые строки, записывая в них корреспонденции счетов и копируя формулы расчета сумм проводок.

Рис.6.14. Данные и формулы листа «Проводки»

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

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

Далее требуется перенести значения колонки «Износ на конец периода» (G) в колонку «Износ на начало периода» (D). Отметим, что нам нужно переносить только полученные значения, а не формулы, по которым рассчитывается износ на конец периода. Для этого выделим блок ячеек, содержащий искомые значения. Скопируем его в буфер промежуточного хранения (Правка→Копировать). Вокруг выделенного блока появится «бегущая» пунктирная линия. Установим активной ячейку D3. Выберем пункт меню Правка→Специальная вставка, где пометим пункт «Значения» и нажмем клавишу OK.

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

Теперь можно вносить в картотеку изменения:

вводить записи о вновь поступивших основных средствах, не забывая устанавливать для них нулевую норму амортизации;

удалять записи о выбывших в прошлом периоде объектах;

устанавливать значения норм амортизации для объектов, поступивших в прошлом месяце;

корректировать нормы амортизации и т. д.

Значения износа и суммы проводок будут пересчитываться автоматически.

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

Во-вторых, не может поддерживаться расчет износа по автотранспортным средствам в зависимости от их пробега. Данная задача имеет довольно простое решение в основную расчетную таблицу можно ввести еще одну колонку, в которую будет проставляться пробег за текущий период. При этом следует модифицировать формулу расчета технологической колонки H (названной нами «Износ по норме») таким образом, чтобы для тех строк, где пробег задается (соответствующая ячейка не пуста), учитывающей износ, а для других строк (там, где пробег не указывается) - по обычным правилам. В этом случае расчет базируется на функции ЕСЛИ(), а для проверки наличия значения в колонке пробега можно применить функцию ЕПУСТО(), проверяющую, задано ли значение в той или иной ячейке.

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

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

6.5. Обработка проводок средствами MS Excel

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

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

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

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

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

Таблица для ввода проводок. Для начала переименуем листы рабочей книги. Листу 1 присвойте имя «Счета». Далее перейдите на Лист 2 и присвойте ему имя «Проводки». На листе «Проводки» в ячейку А1 введите текст «Дебет», в ячейку В1 – «Кредит», в ячейку C1 – «Сумма», в ячейку D1 – «Комментарии».

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

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

Для чего необходимо форматировать столбцы A и B как текстовые? Дело в том, что сюда будут вводиться коды счетов, и гораздо удобнее, если их числовые значения будут восприниматься как текст. Если этого не сделать, то, например, код «68.1» или «68/1» (счет 68 с субсчетом 1) Excel будет воспринимать как текст, а код «50» (счет 50 без субсчета) - как число. В этом случае расчетные формулы на листе «Счета» будут отрабатывать неверно. Конечно, можно каждую ячейку указанных колонок форматировать отдельно. Но это нерационально, лучше сразу задать единообразное форматирование для всех ячеек колонки.

Теперь можно вводить проводки. В качестве примера данные представлены на рис.6.15. Они понадобятся при проверке правильности задания формул на листе «Счета».

Рис.6.15 Пример ввода проводок

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

В ячейку А1 введите текст «Счет».

В ячейку В1 – «Название счета».

В ячейку С1 – «Входящий остаток».

В ячейку D1 – «Дебетовый оборот».

В ячейку Е1 – «Кредитовый оборот».

В ячейку F1 – «Исходящий остаток».

Поскольку столбец A будет содержать коды счетов, то по рассмотренным выше соображениям его следует отформатировать так, чтобы все ячейки колонки воспринимались как текстовые. Ячейки B1:F1 отформатируйте по своему усмотрению и установите удобную для работы и просмотра информации ширину колонок. В нашем примере для первой строки мы установили горизонтальное и вертикальное выравнивание «по центру».

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

В ячейку А2 введите текст «Итоги».

В ячейку С2 - формулу =CУММ(C3:C1000)

В ячейку D2 - формулу =CУММ(D3:D1000)

В ячейку Е2 - формулу =СУММ(ЕЗ:Е1000)

В ячейку F2 - формулу =CУMМ(F3:F1000)

Поскольку эти формулы однотипные, то формулу можно ввести только в ячейку С2, а в другие просто скопировать ее - Excel автоматически подставит идентификаторы нужных колонок. В указанных ячейках будут суммироваться значения остатков на начало периода, дебетового и кредитового оборотов и остатков на конец периода по всем используемым счетам и субсчетам. В нашем примере диапазон суммирования ограничивается строкой с номером 1000. Если используется большее (меньшее) число счетов и субсчетов, то установите соответственно большее (меньшее) значение. Однако лучше указать диапазон с «запасом», чтобы впоследствии при пополнении таблицы строками уже не думать, попадают ли нужные показатели в итоги.

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

Рис.6.16 Фрагмент таблицы «Счета»

Теперь заполним строку 3. В ячейку A3 введем код счета. Напоминаем, что используемая нами модель компьютерного учета такова, что в качестве кода счета можно вводить произвольную комбинацию символов. В нашем примере будут применяться как синтетические счета, так и субсчета, составные элементы которых разделяются точкой. Например, «50», «68.1», «68.2» и т. д. Можно использовать субсчета второго, третьего и более высоких уровней, а также сокращенные текстовые наименования. Например, «41.1.101», «60.1.25.133», «61.Инфософт. Дог№34» и т. д.

В нашем примере в ячейку A3 введен код «01». В ячейку ВЗ следует ввести название счета. В нашем примере это текст «Основные средства». Ячейка СЗ должна содержать остаток данного счета на начало периода. Для первого периода он вводится вручную. В нашем примере мы задали значение 10000.

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

Заметим, что используемый нами одноколоночный принцип записи остатков счетов исключает возможность использования развернутых сальдо, то есть имеющих одновременно ненулевое значение и по дебету, и по кредиту. Это характерно для некоторых счетов учета взаиморасчетов, например для счета 76 «Расчеты с разными дебиторами и кредиторами». Здесь мы не будем рассматривать такую ситуацию.

Для расчета дебетового оборота в ячейку D3 листа «Счета» впишем формулу:

= СУММЕСЛИ(Проводки!А$2:А$5000;$АЗ;Проводки!$С$2:$С$5000)

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

Приведенная формула просматривает диапазон строк 2-5000 листа «Проводки», и в тех строках, где значение в столбце А совпадает со значением ячейки A3 листа «Счета», суммирует числа столбца С.

Границы диапазонов заданы в смешанной форме, при которой используется абсолютная адресация строк начала интервалов. Это нужно для того, чтобы при копировании формулы для других счетов границы интервалов оставались неизменными. Границы интервалов просмотра и суммирования функции СУММЕСЛИ() следует установить так, чтобы диапазон охватывал все проводки листа «Проводки». В нашем случае это интервал строк 2-5000. Для конкретного применения на практике и реальной оценки числа используемых проводок можно установить и другие границы интервалов суммирования. Если формула введена правильно, то она автоматически будет работать.

Для расчета кредитового оборота в ячейку ЕЗ следует ввести формулу:

= СУМMEСЛИ(Проводки !В$2:В$5000;$АЗ;Проводки!$С$2:$С$5000)

Она отличается от предыдущей лишь тем, что ищет совпадения кода счета из данной строки листа «Счета» со счетами в столбце В листа «Проводки». Как мы условились, в этом столбце задаются кредитуемые счета проводок. Это означает, что формула вычисляет кредитовый оборот счета из данной строки расчетной таблицы.

Для расчета исходящего сальдо счета в ячейку F3 введем формулу = C3+D3-E3. Ее смысл очевиден: входящий остаток складывается с дебетовым оборотом, из которого вычитается кредитовый оборот.

Теперь, до заполнения кодов, наименований и входящих остатков счетов, можно сразу скопировать формулы столбцов D, Е, F во все строки расчетной таблицы, где будут размещаться используемые счета. Сделать это можно следующим образом: выделить ячейки D3, ЕЗ, F3, «ухватившись» мышью за правый нижний угол ячейки F3 (курсор мыши должен принять форму креста), «растянуть» выделение на нужное число строк.

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

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

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

Откроем новое окно (Окно —> Новое). Разместим два окна на экране так, чтобы они располагались рядом (Окно —> Расположить, пометить пункт «Рядом» и нажать ОК). Теперь в левом окне выберем лист «Проводки», а перейдя в правое окно, выберем лист «Счета». Теперь с помощью мыши можно «подогнать» размеры окон и колонок таблиц так, чтобы была видна вся наиболее значимая информация. Кроме того, можно отрегулировать и масштаб представления информации в каждом окне (Вид —> Масштаб). Полученный результат показан на рис. 6.17.

При используемом нами разрешении экрана 800х600 точек для лучшего обзора в правом окне пришлось установить масштаб 75%.

При экранном разрешении 1024х768 и выше для наших таблиц можно оставить масштаб 100% и ограничиться только «подгонкой» размеров окон.

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

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

Рис.6.17 Размещение на экране двух листов «Проводки» и «Счета»

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

Из за большого объема этот материал размещен на нескольких страницах: 1 2 3 4 5 6 7 8 9 10 11 12 13