Автоматизация расчетов в бухгалтерском учете с помощью MS Excel

6. Автоматизация расчетов в бухгалтерском учете с помощью MS Excel Рассмотрение примера автоматизации расчетов в бухгалтерском учете с помощью MS Excel организации технологии обработки учетных данных с использованием электронных таблиц, конечно же, не претендует на полноту охвата задач бухгалтерского учета. В особенности если проводить сравнение с возможностями, реализованными в специализированных бухгалтерских программах. Однако использование электронных таблиц имеет и свои преимущества. Это наглядность представления данных, оперативность пересчетов, полная управляемость процессом вычислений, причем достигаемая простыми средствами. Главное же то, что используется стандартный и привычный инструмент вычислений – электронные таблицы, которые широко используются во всех офисах мира. В этой главе показано, как бухгалтер может автоматизировать с помощью Excel учет основных средств, учет материальных ценностей и других направлений бухгалтерского учета. (Более подробно о возможностях использования MS Excel в бухгалтерском учете можно посмотреть в пособии Шуремов Е.Л. Решение задач бухгалтерского учета средствами Microsoft Excel. М.:ФА, 1999. 46 с.) Автоматизация расчетов по учету основных средств Технология учета основных средств с использованием MS Excel предполагает выполнение следующих действий. Формируется таблица, строки которой содержат основные сведения об имеющихся основных средствах, необходимые для выполнения расчетов по начислению износа. Принцип заполнения картотеки таков: одна строка на один объект учета. Таблица содержит формулы, позволяющие по данным о балансовой стоимости и норме амортизации рассчитать износ за текущий период (месяц или квартал) и, с учетом износа на начало периода, рассчитать износ на конец периода. По соответствующим колонкам (балансовая стоимость, износ за период, износ на начало и конец периода) автоматически подводятся итоги, необходимые для отражения информации по основным средствам в главной книге. При необходимости отнесения износа по разным объектам на разные счета затрат таблица может быть дополнена колонкой, в которой проставляются соответствующие счета, на другом листе рабочей книги должны быть определены шаблоны проводок, включающие эти счета и формулы подсчета их сумм, в результате чего проводки формируются автоматически и также автоматически изменяются при внесении изменений в данные картотеки. При завершении периода файл рабочей книги копируется, ему присваивается новое имя (например, соответствующее названию нового периода), выполняется перенос остатков из колонки, соответствующей износу на конец предыдущего периода в колонку данных износа на начало периода. Далее выполняется необходимая корректировка исходных данных, а данные на текущий период рассчитываются автоматически. Данная схема весьма напоминает те процедуры, которые выполняются при ведении ручного учета. Отличия состоят в следующем. При ведении ручного учета никому не придет в голову каждый раз переписывать картотеку при переходе на следующий период. Здесь же нет никаких проблем – копирование выполняет компьютер. В результате мы сохраняем в актуальном виде информацию за каждый отчетный период. Перенос данных из одной колонки Excel в другую (износ на конец периода в колонку износа на начало периода) также не составляет никаких проблем. Один раз введенные формулы работают всегда. После переноса данных конца прошлого периода на начало текущего они срабатывают автоматически, в результате чего сразу получаются результаты на конец данного периода. Также автоматически формулы срабатывают и при внесении изменений в картотеку. Естественно, рассматриваемая технология имеет определенные ограничения. Однако она достаточно проста, вполне работоспособна и может использоваться во многих относительно небольших организациях. Основная расчетная таблица Для создания основной расчетной таблицы выполните следующие действия. Создайте новую рабочую книгу Excel. В ячейку A1 введите текст «Инвентарный номер». В ячейку B1 - «Наименование основного средства». В ячейку C1 - «Стоимость». В ячейку D1 - «Износ на начало текущего периода». В ячейку E1 - «Норма амортизации (% в год)». В ячейку F1 - «Износ за период». В ячейку G1 - «Износ на конец периода». В ячейку H1 - «Износ по норме». Пояснения по содержанию колонок будут даны позже, а их названия могут быть выбраны по вашему усмотрению. Выделите ячейки A1:H1, отформатируйте их по своему вкусу и установите удобную для просмотра информации ширину колонок. В нашем примере мы выбрали режим форматирования со следующими параметрами выравнивания (Формат  Ячейки, вкладка «Выравнивание»): горизонтальное – по центру, вертикальное – по центру, а также установили флажок «Переносить по словам» (рис. 6.1). Рисунок 6.1 – Установка параметров выравнивания текста в заголовке расчетной таблицы Далее, в ячейку A2 введите текст «Итоги». В ячейку C2 - формулу =СУММ(C3:C1000) В ячейку D2 - формулу =СУММ(D3:D1000) В ячейку F2 - формулу =СУММ(F3:F1000) В ячейку G2 - формулу =СУММ(G3:G1000) Поскольку формулы однотипные, то можно ввести формулу только в ячейку C2, а в другие просто скопировать ее – Excel автоматически подставит идентификаторы нужных колонок. В указанных ячейках будут суммироваться значения по стоимости, износу на начало периода, износу за период и износу на конец периода по всем учитываемым объектам. Диапазон суммирования в нашем примере ограничивается строкой 1000. Если в организации более 1000 объектов основных средств, то следует установить большее значение, если меньше – то меньшее. Однако мы настоятельно советуем указывать диапазон с «запасом», чтобы впоследствии, при пополнении таблицы строками уже не думать, попадают ли нужные показатели в итоги. Для строки итогов мы выбрали жирный шрифт и пометили ее другим цветом (Формат  Ячейки, вкладка Вид, щелкнуть мышью по нужному цвету и по кнопке OK), чтобы она лучше выделялась. Можно выбрать и другое оформление. Итоги по картотеке намеренно вынесены наверх таблицы для того, чтобы они были видны сразу при входе в нее. Кроме того, так будет удобнее пополнять картотеку новыми записями: в этом случае их можно просто вписывать в ближайшую пустую строку. В противном случае (при размещении итогов внизу таблицы) пришлось бы выполнять лишнюю операцию вставки строк. Подготовительные действия выполнены. Наша таблица приняла вид, показанный на рис. 6.2. Рисунок 6.2 – Расчетная таблица с установленными формулами расчета итогов Теперь можно вводить данные по строкам. В каждой строке должна быть сосредоточена необходимая информация по одному объекту учета. Начнем со строки 3. Порядок заполнения колонок A («Инвентарный номер»), B («Наименование основного средства») и C («Стоимость») очевиден и в комментариях не нуждается. В колонке D нужно проставить величину износа по данному основному средству на начало периода, начиная с которого предполагается вести учет с использованием электронных таблиц. В колонку E вводится норма амортизации данного основного средства в процентах за год. Если по какому-то объекту учета износ за текущий период не должен начисляться, то здесь должен быть проставлен ноль. Следующие три колонки являются расчетными и содержат формулы. Для лучшего понимания последовательности расчетов начнем с колонки H («Износ по норме»). Данная колонка – чисто технологическая и мы используем ее только для того, чтобы упростить ввод формул в колонках F и G. Введите в ячейку H3 формулу: =C3*E3/1200. Она означает, что данная графа рассчитывается как произведение стоимости основного средства на норму амортизации, деленную на 1200. В нашем примере в качестве расчетного периода выбран месяц. Поэтому мы используем константу 1200, поскольку для расчета износа за месяц норму амортизации, выраженную в процентах за год, нужно поделить на 12 месяцев и 100%. При начислении износа сразу целый за квартал следует использовать константу 400. После ввода формулы будет автоматически рассчитано значение износа за данный период. Теперь подумаем: всегда ли это значение можно считать величиной износа за месяц. Нет. Не всегда. Если сумма износа на начало периода, сложенная с этой величиной больше балансовой стоимости объекта учета, то износ в таком размере за данный период начислен быть не может и должен быть принят только в сумме разницы между стоимостью и износом на начало периода. В противном случае износ на конец периода превысит стоимость. Данная ситуация не очень типична, но возможна если в предшествующих периодах износ начислялся по другой норме. В любом случае мы должны предусмотреть все возможные варианты, чтобы гарантировать себя от возможных ошибок. Поэтому данная графа является только отправной точкой при выполнении последующих расчетов. Для корректного выполнения расчета износа за текущий период в ячейку F3 должна быть введена формула: =ЕСЛИ(D3+H3>C3;C3-D3;H3) Она означает, что если износ на начало периода (D3), сложенный с износом, рассчитанным в соответствии с нормой амортизации (H3), больше стоимости данного объекта учета, то износ за текущий период может быть начислен в сумме, не превышающей его остаточной стоимости (C3-D3). В противном случае износ за месяц составляет величину, рассчитанную в соответствии с нормой амортизации (H3). Более подробно об использовании функции ЕСЛИ() см. справочную систему Excel. Износ на конец периода равен износу на начало периода, сложенному с износом за период. Поэтому в ячейку G3 следует ввести формулу: =D3+F3. После ввода этой формулы строка таблицы полностью сформирована. В качестве примера мы ввели в нее данные, представленные на рис 6.3. Рисунок 6.3 – Запись о первом объекте картотеки основных средств Поскольку расчетные графы остальных строк должны содержать идентичные формулы мы просто скопируем имеющиеся формулы, а «умный» Excel самостоятельно «подправит» фигурирующие в них индексы с тем, чтобы они соответствовали номерам строк. Для этого выделите ячейки F3:H3 и далее «уцепитесь» мышкой за правый нижний угол выделенной области. «Мышиный курсор» должен принять форму черного креста. Теперь «потяните» мышь вниз на столько строк, сколько у Вас есть основных средств (один объект - одна строка). Не повредит «размножить» формулы с запасом. Все. Таблица готова. Остается только перенести в нее данные картотеки (рис. 6.4.). Поскольку формулы скопированы с «запасом», в тех строках, где не введены необходимые данные, соответствующие ячейки имеют нулевое значение. Рисунок 6.4 – Пример заполнения расчетной таблицы данными картотеки основных средств По мере заполнения строк сведениями об объектах основных средств (инвентарный номер, наименование, стоимость, износ на начало периода норма амортизации) величины износа за период и износа на конец периода будут автоматически рассчитываться, а итоги по соответствующим колонкам изменяться. Автоматизация составления проводок по начислению износа Если износ по различным объектам учета относится на единственный счет, то можно ограничиться тем, что мы уже сделали, поскольку все необходимые итоговые суммы уже имеются и их можно просто перенести в главную книгу. Если же износ должен относиться на разные счета или субсчета, то следует дополнить таблицу и выполнить следующие действия. В заголовке (1-ая строка) колонки I напишите «Счет отнесения износа» и перенесите на него формат заголовка таблицы. Отформатируйте эту колонку I так, чтобы все введенные в нее значения воспринимались как текстовые. Для этого выделите колонку I, вызовите карточку настройки форматов ячеек Excel (Формат - Ячейки), выберите раздел «Число», в списке форматов выберите строку «Текстовый» и нажмите клавишу OK (рис. 6.5). Установите для данного столбца выравнивание по центру. Рисунок 6.5 – Установка форматирования для колонки ввода счетов отнесения износа. Хотя коды счетов представлены числами, мы намеренно используем текстовый формат, поскольку обработку кодов счетов удобнее выполнять, рассматривая их как текст. Для каждого объекта учета (строки) укажите коды счетов, на которые должен быть отнесен износ (рис. 6.6). Рисунок 6.6 – Расчетная таблица с установленными кодами счетов отнесения износа Переименуйте «Лист 1» в «Картотека» (Формат  Лист  Переименовать), потом перейдите на «Лист 2» и переименуйте его в «Проводки». В ячейку A1 листа «Проводки» впишите текст «Дебетуемый счет», в ячейку B1 - «Кредитуемый счет», а в ячейку C3 - «Сумма». Отформатируйте колонки A и B так, чтобы все вводимые в них значения воспринимались как текстовые и размещались по центру. Начиная со строки 2, впишите все проводки, которые используются при начислении износа. В колонке A – «Дебетуемый счет», должны быть перечислены все счета, которые хоть один раз были указаны в колонке I листа «Картотека». В противном случае итоги по износу за месяц и итоги по проводкам не сойдутся. В ячейку C2 листа «Проводки» впишите формулу: =СУММЕСЛИ(Картотека!I$3:I$1000;A2;Картотека!F$3:F$1000) Функция СУММЕСЛИ() суммирует ячейки, специфицированные заданным критерием. В общем виде она имеет следующую структуру: СУММЕСЛИ(интервал_просмотра; критерий; суммируемый_интервал) Интервал просмотра – это интервал ячеек, среди которых осуществляется поиск определенной информации. Критерий – это условие поиска, которое может быть в форме числа, выражения или текста. Критерий определяет правило выбора данных для суммирования. Суммируемый интервал – это ячейки, из которых выбираются данные для суммирования. При этом они суммируются только тогда, когда ячейка из интервала просмотра удовлетворяет условию, заданному критерием. Более подробно о функции СУММЕСЛИ см. справочную систему Excel. В нашем случае функция СУММЕСЛИ() суммирует числа из диапазона F3:F1000 листа «Картотека» в том случае, если значение ячейки A2 листа «Проводки» совпадает с соответствующим значением интервала поиска I3:I1000. Содержательно это означает, что суммируются величины износа за месяц (колонка F) по тем объектам, у которых счет отнесения износа (колонка I) совпадает со счетом, дебетуемым в данной проводке (A2). Поскольку формула для расчета суммы проводки записана и действует на листе «Проводки», а интервал просмотра и суммируемый интервал заданы на листе «Картотека», то перед обозначениями границ этих интервалов заданы названия данного листа. В противном случае Excel рассматривал бы эти интервалы, как относящиеся к листу на котором размещена формула. Кроме того, границы диапазонов заданы в смешанной форме, при которой используется абсолютная адресация строк начала интервалов. Это нужно для того, чтобы при копировании формулы для других проводок, границы интервалов оставались бы неизменными. Более подробно о порядке адресации ячеек см. справочную систему Excel. Выбор нижней границы интервалов просмотра и суммирования функции СУММЕСЛИ() лучше установить равным интервалу подсчета итогов на листе «Картотека». Скопируйте формулу во все строки столбца C, содержащие проводки. Их суммы будут автоматически рассчитаны. Для контроля правильности задания формул расчета сумм проводок и кодов счетов отнесения износа в столбце C под проводками разместите формулу расчета суммы по колонке. Полученный здесь итог должен совпасть с итогом по колонке F («Износ за месяц») листа «Картотека» (рис. 6.7). Рисунок 6.7 – Данные и формулы листа «Проводки» расчетной таблицы Если вы сопоставите данные нашего примера, приведенные на этом и предыдущем рисунках, то принцип действия функции СУММЕСЛИ станет окончательно ясен. Теперь изменяя данные в картотеке, мы можем не заботиться о пересчете сумм проводок. Единственное о чем надо помнить, так это о том, что при использовании новых кодов счетов отнесения износа на листе «Картотека» для них нужно определять макеты проводок на листе «Проводки», вставляя новые строки, записывая в них корреспонденции счетов и копируя формулы расчета сумм проводок. Переход к следующему периоду Для перехода к новому периоду необходимо выполнить следующие действия. Закройте файл рабочей книги с данными текущего месяца, если он открыт. Скопируйте его, присвоив другое имя. В условиях нашего примера мы назвали исходный файл 1998-11.xls. Поэтому новому файлу логично дать имя 1998-12.xls. Загрузите новый файл в Excel. Теперь требуется перенести значения колонки «Износ на конец периода» (G) в колонку «Износ на начало периода» (D). Отметим, что нам нужно переносить только полученные значения, а не формулы, по которым рассчитывается износ на конец периода. Для этого выделите блок ячеек, содержащий искомые значения. В нашем примере это диапазон G3:G8. Скопируйте его в буфер промежуточного хранения (Правка  Копировать). Вокруг выделенного блока появится «бегущая» пунктирная линия. Установите активной ячейку D3. Выберите пункт меню «Правка - Специальная вставка». В окне параметров специальной вставки пометьте пункт «Значения» и нажмите клавишу OK(рис. 6.8). Рисунок 6.8 – Установка параметров специальной вставки при установке значений износа на начало нового периода Данные на конец прошлого периода будут перенесены в колонку данных об износе на начало периода. После выполнения переноса сработают расчетные формулы и будут рассчитаны новые значения износа за период и износа на конец периода (рис. 6.9). Рисунок 6.9 – Расчетная таблица после установки значений износа на начало периода Теперь можно вносить в картотеку изменения: вводить записи о вновь поступивших основных средствах, не забывая устанавливать для них нулевую норму амортизации для первого месяца эксплуатации, удалять записи о выбывших в прошлом периоде объектах, устанавливать значения норм амортизации для объектов, поступивших в прошлом месяце, корректировать нормы амортизации и т.д. Значения износа и суммы проводок будут пересчитываться автоматически. Рассмотренная технология ведения учета основных средств с использованием Microsoft Excel технология имеет несколько достаточно существенных ограничений. Во-первых, не поддерживается разделение основных средств по подразделениям и материально-ответственным лицам с получением соответствующих итогов. Для преодоления этого ограничения данные различных подразделений можно размещать на разных листах рабочей книги, а итоговый свод получать функцией консолидации данных на отдельном листе или иными способами. Во-вторых, не поддерживается расчет износа по автотранспортным средствам в зависимости от их пробега. Эта задача также допускает довольно простое решение. В основную расчетную таблицу можно ввести еще одну колонку, в которую будет проставляться пробег за текущий период. При этом следует модифицировать формулу расчета технологической колонки H, названной нами «Износ по норме» таким образом, чтобы для тех строк, где пробег задается (соответствующая ячейка не пуста), расчет выполнялся бы по формуле учитывающей пробег, а для других строк (там, где пробег не указывается) – по обычным правилам. В этом случае для расчета следует использовать функцию ЕСЛИ(), а для проверки наличия значения в колонке пробега можно применить функцию ЕПУСТО(), проверяющую задано ли значение в той или иной ячейке (см. справочную систему Excel). В-третьих, рассматривается только линейный способ начисления износа. Эта проблема также легко преодолевается корректировкой формул расчета технологической колонки H. В ряде случаев может потребоваться ввод дополнительной колонки, где может быть проставлен срок эксплуатации или другие необходимые для расчета параметры. Автоматизация базовых расчетов по учету материальных ценностей Рассмотрим простейшую технологию ведения учета товарно-материальных ценностей (ТМЦ) на основе MS Excel. Она состоит в следующем. В рабочей книге Excel ведутся четыре отдельных листа. На первом отражаются основные сведения из фактурной части приходных документов: коды и наименования поступающих материальных ценностей, их количество и стоимость. На втором листе вводятся данные расходных документов. Третий лист содержит справочник материальных ценностей, совмещенный с оборотной ведомостью, в которой автоматически рассчитываются обороты и исходящие остатки по каждому наименованию в натуральной и стоимостной форме. Стоимостные данные по расходу оцениваются по средневзвешенным ценам, которые также отображаются на листе оборотной ведомости и автоматически пересчитываются при вводе приходных документов и внесении изменений в остатки на начало периода. Четвертый лист предназначен для формирования проводок по отраженным в таблицах операциям. При переходе к следующему периоду рабочая книга копируется в новый файл и в нем значения остатков на конец предшествующего периода стандартными средствами Excelпереносятся в колонку остатков на начало периода. Далее очищаются сведения о приходах и расходах предыдущего периода, и цикл операций повторяется заново. Указанная технология является базой для решения задач по учету движения ТМЦ. Она позволяет решать задачу обобщения сведений о движении ценностей и автоматически дает оценку себестоимости их заготовления на основе метода средневзвешенных цен. Для более полного охвата задач учета готовой продукции, реализации товаров, учета малоценных и быстроизнашивающихся предметов (МБП) данная технология требует специфических дополнений, зависящих экономической природы учитываемых объектов и специфики учетной политики предприятия.   Справочник материальных ценностей Присвойте листу 1 рабочей книги имя «Приход», листу 2 – «Расход», листу 3 – «Обороты», листу 4 – «Проводки». Перейдите на лист «Обороты». Здесь будут содержаться справочник материальных ценностей, сведения об их оборотах, остатках на начало и конец периода, средневзвешенные цены заготовления. Порядок заполнения этой таблицы мы рассмотрим в несколько этапов. Сначала остановимся на формировании справочника материальных ценностей. Он должен включать коды и наименования всех материальных ценностей, движения которых предполагается включать в данную оборотную ведомость. В принципе учет товаров, материалов, готовой продукции и МБП можно вести в одной рабочей книге Excel, но в ряде случаев лучше выделить для этого отдельные рабочие книги. На листе «Обороты» в ячейку A1 введите текст «Код», а в ячейку B1 – текст «Наименование». Установите удобную ширину колонок. В столбец A мы будем вводить коды материальных ценностей, а в столбец B – их полные наименования. Естественно, коды должны быть уникальны. Настоятельно рекомендуется отформатировать столбец A таким образом, чтобы все вводимые здесь значения воспринимались как текстовые (Выделить столбец, Формат  Ячейки, закладка «Число», выделить пункт «Текстовый», нажать Ok.) Если предполагается использовать только числовые коды, то можно этого не делать. Однако мы строим таблицу в предположении, что коды ТМЦ могут иметь смешанное алфавитно-цифровое кодирование. В ряде случаев это и удобнее и нагляднее. Рекомендуем начать заполнение справочника на листе «Обороты» со строки 4, поскольку вторую и третью строки в последующем будем использовать для вспомогательных целей. В качестве примера мы указали сведения о материальных ценностях, приведенные на рис. 6.10. Рисунок 6.10 – Пример ввода данных справочника ТМЦ. Далее мы дополним лист справочника ТМЦ колонками и формулами, позволяющими рассчитывать их средневзвешенные цены, обороты и остатки. Но сначала займемся листами для ввода сведений о приходе и расходе (списании). Приход Перейдите на лист «Приход». Здесь должны вводиться сведения о поступлении материальных ценностей в соответствии с приходными документами. Рекомендуется разделять вводимые документы одной-двумя пустыми строками и предварять их указанием номеров и дат в произвольном формате. В ячейку A1 введите текст «Код», в B1 – «Наименование», в C1 – «Кол-во», а D1 – «Сумма по док-ту». Для компактного размещения данных мы отформатировали первую строку таблицы так, чтобы информация в ячейках строки заголовка переносилась по словам и была отцентрирована и по вертикали и по горизонтали. Кроме того, мы подобрали нужную высоту первой строки и ширину столбцов для компактного отображения информации. Вы можете отформатировать таблицу по своему вкусу. В столбец A должны вводиться коды учитываемых объектов. Он должен быть отформатирован так, чтобы все вводимые в него значения воспринимались как текстовые. Коды должны вводиться на основании фактурных частей приходных документов и в соответствии с кодированием, определенным в справочнике. Мы построим таблицу так, чтобы в столбцеB автоматически появлялись наименования объектов учета из справочника с листа «Обороты». Для этого введите в ячейку B2 формулу: =ВПР(A2;Обороты!$A$4:$B$2000;2;ЛОЖЬ) В соответствии с ней в ячейку B2 будет подставлено наименование материальной ценности, код которой указан в ячейке A2 листа «Приход». Напомним, что функция ВПР осуществляет поиск значения, указанного первым аргументом, в первом столбце диапазона ячеек, заданного вторым аргументом. При этом возвращается значение из ячейки, размещенной на пересечении найденной строки и столбца, номер которого относительно первого столбца диапазона поиска указан третьим аргументом. Четвертый аргумент содержит условие поиска. Подробности см. в справочной системе Excel. В нашем случае ячейка A2 листа «Приход» содержит код объекта учета (вида материальной ценности). Диапазон «Обороты!A$4:B$2000» содержит два столбца. В первом (A) находятся коды материальных ценностей. Один из этих кодов обязательно должен совпадать с кодом объекта, который фигурирует в данной строке данного приходного документа. Во втором столбце (B) листа «Обороты», как мы договорились, находятся названия материальных ценностей. Таким образом, функция ВПР в приведенной нами форме ищет название кода, указанного при вводе приходного документа и возвращает наименование соответствующего объекта. Если код в справочнике на листе «Обороты» не найден, то в соответствующей ячейке столбца B наименование не появится. Если поступает новый вид материальных ценностей, то сведения о нем следует обязательно ввести в справочник. Диапазон строк с 4 по 2000 выбран произвольно. Если предполагается учитывать большее число видов материальных ценностей, то следует установить иную границу диапазона. В целом же, выбор диапазона не имеет особого значения. Главное, чтобы он перекрывал область с реально имеющимися данными. Из этих же соображений выбраны диапазоны, используемые в других формулах. После ввода формулы в ячейку B2 рекомендуется скопировать ее в другие ячейки столбца B. Следует иметь в виду, что Excel – достаточно «капризная» программа. В частности, можно столкнуться с ситуацией, когда функция ВПР никак «не хочет» правильно интерпретировать коды объектов и даже для кодов, имеющихся в справочнике на листе «Обороты», не возвращает правильного названия ценности на листе «Приход». В чем проблема, наверное, не знают даже разработчики из Microsoft. Однако в этом случае обычно помогает следующий прием. Вместо того чтобы вводить код ТМЦ на листе «Приход» вручную следует, находясь на листе «Обороты», скопировать нужный код в буфер промежуточного хранения (выделить ячейку, Правка  Копировать), а потом вставить это значение в нужную ячейку на листе «Приход» (позиционировать ячейку, Правка  Вставить). Можно использовать и соответствующие пиктограммы. Мы рекомендуем при вводе информации размещать листы «Приход» и «Обороты» в двух окнах на одном экране, нужным образом размещая окна и подбирая в них нужный масштаб. В этом случае рассмотренный только что прием легко осуществлять путем манипуляций мышью. В столбце C на листе «Приход» вводятся данные о количестве поступивших объектов учета, а в столбце D – о совокупной стоимости их партии в соответствии с приходным документом. Для корректного выполнения расчетов единицы измерения по документам должны быть приведены к тем же, которые предполагаются в справочнике. Пример ввода данных о поступлении материальных ценностей приведен на рис. 6.11. Рисунок 6.11 – Пример ввода данных о поступлении ТМЦ. Как следует из рис. 6.11 мы разделяем отдельные документы пустой строкой. В принципе это необязательно, но так данные лучше обозреваются. Кроме того, перед данными нового документа можно вводить его номер и дату, поскольку они позволяют идентифицировать, к какому документу относятся данные. Значения #Н/Д в некоторых ячейках означают, что данного кода нет в справочнике. В частности, это значение проставляется функцией ВПР для тех ячеек, где код задан пустым значением. Выделение НДС Если данные по суммам поступающих материальных ценностей вводятся без учета НДС, то никакой проблемы нет и можно ограничиться рассмотренным выше порядком заполнения таблицы приходов. Однако если в приходном документе суммы включают НДС, то целесообразно предусмотреть возможность автоматического выделения НДС из вводимых данных. Это нужно, хотя бы для того, чтобы получать стоимостные оценки суммы прихода и средневзвешенные цены без учета НДС. Для решения поставленной задачи мы модифицируем таблицу приходов таким образом, чтобы при необходимости Excel автоматически вычислял сумму без НДС, оставляя ее без изменений в тех случаях, когда введенная сумма не включает НДС. Добавим к таблице на листе «Приход» три колонки. В ячейку E1 введите текст «Вкл. НДС?», в ячейку F1 – текст «Сумма без НДС», а в ячейку G1 – текст «НДС». Мы используем горизонтальное и вертикальное форматирование по центру с переносом по словам. Мы организуем вычисления таким образом, чтобы при пустом значении ячейки столбца E сумма из столбца D просто копировалась бы в соответствующую ячейку столбца F, а в противном случае в ней вычислялось бы значение суммы без НДС. Для организации таких вычислений следует ввести в ячейки столбца F формулы, которые проверяют, является ли пустой соответствующая ячейка столбца E и в зависимости от этого, либо копируют значение из столбца D, либо на основании ставки НДС и суммы из столбца D вычисляют сумму без НДС. Если предприятие всегда работает с одной и той же ставкой НДС, то проще всего указать ее прямо в расчетной формуле. Для ячейки E2 при ставке 20% эта формула должна быть задана в следующем виде: =ЕСЛИ(ЕПУСТО(E2);D2;ОКРУГЛ(D2*100/120;2)) Формула работает следующим образом. Функция ЕПУСТО(E2) проверяет, является ли ячейка E2 пустой. Если это так, то функция ЕСЛИ() в качестве своего значения возвращает значение из ячейки D2. В противном случае возвращается значение, очищенное от НДС (D2*100/120). При этом с помощью функции ОКРУГЛ производится округление до двух десятичных разрядов. Подробную информацию о функциях ЕСЛИ() и ОКРУГЛ() можно найти в справочной системе Excel. Здесь и везде далее для компактности представления данных мы используем форматирование чисел в виде целых с разделителями тысяч. Исключение составляет только описываемый далее показатель средневзвешенной цены, который форматируются с тремя знаками после запятой. При желании можно выбрать иное форматирование чисел, например, с двумя разрядами после запятой, обеспечивающее представление чисел с копейками. Таким образом, если в ячейку E2 что-либо введено (то есть ее значение не пусто), то предполагается, что сумма по документу включает НДС и в ячейку F2 попадает сумма без НДС. В противном случае считается, что сумма в ячейке D2 не включает НДС, и она просто копируется в ячейку F2. В соответствии с нашими соглашениями для расчета суммы НДС в ячейку G2 должна быть введена следующая формула: =ЕСЛИ(ЕПУСТО(E2);ОКРУГЛ(D2*0,2;2);D2–F2) После ввода формул в ячейки F2 и G2 их следует скопировать на весть диапазон ввода приходных документов. Пример построения таблицы с автоматическим отделением НДС приведен на рис. 6.12. Рисунок 6.12 – Пример ввода документов, включающих и не включающих НДС в проставленные суммы Обращаем внимание на то обстоятельство, что формула будет работать правильно только тогда, когда ячейка E2 действительно пустая, то есть не содержит никакого значения. Поэтому нельзя «сбрасывать» ячейку, вводя в нее пробел, поскольку пробел считается непустым значением. Для того чтобы сделать ячейку пустой нужно полностью удалить ее содержимое. Проще всего это сделать, установив на нее курсор и нажав клавишу Delete. В этом случае ячейка полностью «сбрасывается» и наша формула отработает корректно. Отметим, что ставку НДС можно задавать не только непосредственно в расчетных формулах. Иногда желательно параметризовать формулы. В этом случае можно было бы в формуле ссылаться не на конкретное значение, а на ячейку, в которой задана ставка. Например, можно создать отдельный лист рабочей книги, на котором задаются все важные для расчетов константы. Если этот лист называется «Константы», а значение ставки НДС содержится на нем в ячейке B2, то формулу для ячейки F2 листа «Приход» можно было бы задать в виде: =ЕСЛИ(ЕПУСТО(E2);D2;ОКРУГЛ(D2*100/(100+Константы!$B$2);2)) При использовании параметризованной формулы ее не пришлось бы править, если с какого-либо периода начнет действовать другая ставка НДС. В этом случае, перейдя к следующему периоду, можно было бы исправить только значение константы. В нашем же случае при изменении ставки придется заменить все формулы в столбце F листа «Приход». Впрочем, это то же несложно. Теперь рассмотрим вопрос о том, как поступать в тех случаях, когда предприятие работает с несколькими ставками НДС. Здесь самым простым и гибким решением будет задание в ячейках столбца E конкретных значений ставок НДС по поступающим ценностям и модификация формул для столбца Fтаким образом, чтобы они использовали для расчета явно заданные ставки. Для того чтобы предусмотреть возможность обработки стоимости с включенным или не включенным НДС, да к тому же при разных ставках, применим следующий прием. Будем считать, что ставки задаются в виде целого числа для каждой вводимой в таблицу строки приходного документа по следующим правилам. Если НДС входит в сумму, то ставка проставляется со знаком «минус». В противном случае ставка проставляется со знаком «плюс» (без знака). В этих предположениях формула расчета суммы без НДС для второй строки столбца F может быть задана так: =ЕСЛИ(E2>0;D2;ОКРУГЛ(D2*100/(100–E2);2)) Вычитание (100–E2) соответствует нашему соглашению о том, что при указании суммы с НДС, ставка задается со знаком «минус». Расчет суммы НДС в этом случае также зависит от знака числа в ячейке E2. Соответствующая формула в ячейке G2 будет выглядеть так: =ЕСЛИ(E2>0;ОКРУГЛ(D2*E2/100;2);D2–F2) Пример применения данного подхода приведен на рис. 6.13. Рисунок 6.13 – Пример реализации подхода с явным заданием ставки НДС для каждой строки приходных документов. Помимо рассмотренного только что подхода с явным указанием ставки НДС может быть реализовано и другое решение, которое предполагает задание ставки НДС для каждого объекта учета в справочнике ТМЦ на листе «Обороты». Соответствующее значение может быть выбрано с помощью функции ВПР(). Мы не будем рассматривать способ задания формул в этом случае, и предлагаем читателю сконструировать их самостоятельно. Расход На листе «Расход» мы будем вводить сведения о движении материальных ценностей в соответствии с расходными документами. Так же, как и в случае с приходными документами рекомендуется разделять вводимые документы одной-двумя пустыми строками и предварять их указанием номеров и дат. В ячейку A1 введите текст «Код», в B1 – «Наименование», а в C1 – «Кол-во». В данном варианте технологии обработки данных по учету материальных ценностей мы не рассматриваем случая продажи товара и потому здесь не задаются отпускные цены. Для учета реализации таблицы рабочей книги требуют определенной модификации. При наличии читательского интереса соответствующие решения будут рассмотрены в последующих публикациях. Также как и на листе «Приход» мы будем вводить в ячейки столбца A коды материальных ценностей, а их наименования будут подставляться автоматически на путем выбора из справочника на листе «Обороты». Столбец A должен быть отформатирован так, чтобы все вводимые в него значения воспринимались как текстовые. Принципы реализации данного подхода рассматривались выше, поэтому мы не будем подробно на них останавливаться. Итак, в ячейку B2 листа «Расход» следует ввести формулу =ВПР(A2;Обороты!$A$4:$B$2000;2;ЛОЖЬ) Легко видеть, что она полностью идентична формуле выборки наименования ранее размещенной нами на листе «Приход». Далее, следует скопировать ее во все строки столбца B, в которые предполагается заносить сведения расходных документов. Пример заполнения таблицы на листе «Расход» приведен на рис. 6.14. Рисунок 6.14 – Пример ввода данных на листе «Расход». Оборотная ведомость Теперь перейдем к реализации основных расчетных функций. Как указывалось ранее, мы дополним справочник ТМЦ, размещенный на листе «Обороты», колонками, включающими сведения о входящих остатках, а также формулами для расчета оборотов, исходящих остатков и средних цен заготовления по каждому виду ТМЦ. Для этого дополним таблицу несколькими столбцами. В колонки C и D будут вводиться сведения об остатках по каждому виду ТМЦ на начало периода в натуральном и стоимостном выражении, соответственно. Для первого периода они вводятся вручную, а в дальнейшем их значения просто переносятся из столбцов остатков на конец периода. В столбцах E и F будут рассчитываться итоги по приходу ТМЦ в натуральном (E) и стоимостном (F) выражении. В столбцах G и H будут рассчитываться итоги по расходу каждого вида ТМЦ, а в столбцах I и J остатки на конец периода также в натуральном и стоимостном выражении, соответственно. В столбце K будут рассчитываться средневзвешенные цены заготовления. Они являются базой для расчета стоимостной оценки расхода. Мы разметили заголовок таблицы так, как показано на рис. 6.15. Рисунок 6.15 – Разметка заголовка таблицы оборотной ведомости Ничто не мешает присвоить колонкам другие наименования. При разметке заголовка в нашем примере мы исходили из того, что он должен быть «двухслойным». Поэтому он и размещен на двух строках. При этом центрирование надзаголовков «Остаток на начало», «Приход», «Расход», «Остаток на конец» получено путем выполнения операции «Центрирование по выделению» (ввести текст, выделить соседние столбцы, на которых он должен быть центрирован, Формат  Ячейки, Выравнивание, Горизонтальное, По центру выделения, Ok). Заголовки во второй строке просто центрированы по горизонтали. Для дальнейшего изложения форматирование и название столбцов не имеет никакого значения. В третью строку таблицы в ячейки D3, F3, H3 и J3 введем формулы для подсчета итогов по стоимостным показателям в соответствующих столбцах. Не забудьте указать диапазон суммирования, включающий все виды ТМЦ, а лучше – с запасом. Теперь зададим основные расчетные формулы. В ячейку E5 введем формулу, подсчитывающую итог по приходу данного вида материальных ценностей: =СУММЕСЛИ(Приход!$A$2:$A$5000;$A5;Приход!C$2:C$5000) С функцией СУММЕСЛИ() мы уже знакомы. В данном случае она выполняет суммирование данных в тех строках столбца C листа «Приход», у которых код ТМЦ совпадает с кодом из данной строки листа «Обороты» (A5). Поскольку в столбце C листа «Приход» мы вводим данные о поступлении ценностей в натуральном выражении в соответствии с приходными документами, то данная формула подсчитывает суммарный приход в натуральном выражении по их данному виду, задаваемому кодом в ячейке A5 листа «Обороты». В ячейке F5 следует задать формулу: =СУММЕСЛИ(Приход!$A$2:$A$5000;$A5;Приход!F$2:F$5000) Здесь критерий отбора тот же, но суммируются данные из столбца F листа «Приход». В этом столбце сосредоточены «очищенные» от НДС данные по приходу в стоимостном выражении. Поэтому результатом действия формулы является итог по поступлению данного вида ТМЦ в стоимостном выражении без НДС. В ячейке G5 задаем формулу для расчета итогов по расходу данного вида ТМЦ в натуральном выражении. Она выглядит так: =СУММЕСЛИ(Расход!$A$2:$A$5000;$A5;Расход!C$2:C$5000) Все то же самое, но только выбираются и суммируются данные на листе «Расход». Пропустим пока ячейки H5, I5, J5 и перейдем к ячейке K5. Как было указано выше, здесь должна рассчитываться средневзвешенная цена заготовления единицы ТМЦ данного вида. Для расчета этой величины мы должны сложить остаток на начало периода (D5) с приходом за период (F5) в стоимостном выражении и поделить полученный результат на сумму остатка на начало периода (C5) и прихода за период (E5) в натуральном выражении. В рамках соглашений о размещении показателей в нашей таблице эта формула выглядела бы так: =(D5+F5)/(C5+E5) Однако следует предусмотреть случай, когда C5+E5 = 0. Эта ситуация может возникнуть если еще не заданы ни количество входящего остатка, ни данные о приходе. Ведь мы же можем «размножать» формулы впрок, до ввода сведений о ТМЦ или приходных документов. В этом случае возникнет ситуация деления на ноль. В принципе, ничего страшного, но таблица примет весьма непрезентабельный вид и, кроме того «испортятся» итоги по колонкам со стоимостными показателями. Можете проверить это сами. Поэтому мы зададим формулу для ячейки K5 в ином виде: =ЕСЛИ(C5+E5>0;(D5+F5)/(C5+E5);0) Она проверяет, является ли сумма входящего остатка и итогов по приходу в натуральном выражении неотрицательной и если это так, то рассчитывает среднюю цену обычным порядком. В противном случае, формула выдает значение ноль, как указание на то, что средневзвешенная цена не может быть рассчитана. Теперь уже можно задать формулы для оценки себестоимости расхода, оцениваемой по средневзвешенной цене, и остатков на конец периода. Для расчета себестоимости расхода в ячейку H5 введем формулу: =G5*K5 В соответствии с ней себестоимость расхода оценивается как произведение общего расхода в натуральном выражении (G5) на средневзвешенную цену заготовления. Формулы для расчета остатков на конец периода чрезвычайно просты. Остаток в натуральном выражении (ячейка I5) определяется по формуле: =C5+E5-G5 Она суммирует остаток на начало периода с приходом за период и вычитает из полученной величины расход за период. Аналогично задается и формула для остатка в стоимостном выражении (ячейка J5): =D5+F5-H5 Теперь все основные формулы введены. Однако перед тем как копировать их в другие строки зададим форматирование ячеек остатков таким образом, чтобы в случае возникновения в них отрицательных величин они отображались красным цветом. Для этого нужно выделить ячейки I5 и J5, выбрать пункт меню Формат  Ячейки, выбрать закладку «Число», в списке числовых форматов выбрать пункт «Числовой», далее перейти в поле «Отрицательные числа», пометить там один из примеров с отображением числа красным цветом и нажать клавишу Ok. Дело вкуса просто отображать отрицательные числа красным цветом или еще и выводить перед ними знак минус. Для нашего примера мы выбрали второй случай. Теперь, при возникновении достаточно типичного случая, когда сведения по расходным документам вводятся раньше данных приходных документов и/или остатков на начало периода отрицательные исходящие остатки будут отображаться красным цветом, и их легче будет замечать. Вот теперь уже можно выполнить операцию копирования формул пятой строки в последующие строки. В условиях нашего примера мы получили результат, представленный на рис. 6.16. Рисунок 6.16 – Пример расчета оборотной ведомости   Можно видеть, что происходит автоматический расчет средних цен, а также итоговых значений оборотов и остатков. Кроме того там, где мы заранее ввели «перерасход» (см. предыдущие рисунки), отображаются красные отрицательные остатки, а там, где не введены сведения о приходе и начальные остатки, средняя цена и расход в стоимостном выражении равны нулю. Формирование проводок Если справочник – оборотная ведомость – содержит сведения о материальных ценностях, учитываемых на одном синтетическом счете, то проблемы формирования проводок не существует. Действительно, на листе «Обороты» мы имеем всю необходимую информацию для отражения движений по данному счету в синтетическом учете: ячейка F3 содержит стоимостной итог по поступлению ТМЦ за текущий период – дебетовый оборот счета, в ячейке H3 содержится итог по списанию себестоимости ТМЦ – кредитовый оборот счета. Для того, чтобы получить итоги по поступлению сумм НДС достаточно на листе «Приход» вставить формулу суммирования столбца G. Мы рассмотрим более общий случай, когда справочник на листе «Обороты» включает сведения о ТМЦ разной экономической природы, учитываемых на разных счетах. Для решения этой задачи справочник должен быть дополнен сведениями о счетах, на которых учитываются конкретные виды материальных ценностей. Мы будем хранить эту информацию в столбце L. Введите в ячейку L1 текст «Счет» и отформатируйте столбец L так, чтобы все вводимые в него значения воспринимались как текстовые. Далее, для каждого вида ТМЦ проставьте код счета, на котором он учитывается. В нашем примере мы ввели данные, приведенные на рис. 6.17. Рисунок 6.17 – Справочник ТМЦ с включенными сведениями о счетах. Теперь следует перейти на лист «Проводки». В ячейку A1 введите текст «Дебет», в ячейку B1 – «Кредит», в ячейку C1 – «Сумма», а в ячейку D1 – «Комментарий». Методика заполнения таблицы на этом листе состоит в следующем. Задаются все проводки, которые так или иначе возникают в связи с движением материальных ценностей, занесенных в справочник на листе «Обороты». Дебетуемый счет проводки вводится в соответствующую строку столбца A, а кредитуемый счет – в столбец B. Формулы для расчета суммы проводки зависят от ее содержания. Формула для расчета суммы «приходной» проводки имеет следующий вид: =СУММЕСЛИ(Обороты!$L$4:$L$2000;A2;Обороты!$F$4:$F$2000) Она суммирует значения из столбца F (приход в стоимостном выражении) листа «Обороты» по тем строкам, в столбце L которых содержится код счета, совпадающий с кодом счета, дебетуемым в данной проводке (A2). Данная формула может быть скопирована для всех «приходных» проводок на листе «Проводки», то есть тех, в которых дебетуется счет, на котором учитывается какой-либо вид материальных ценностей, из представленных в таблице на листе «Обороты». Формулы расчета сумм «расходных» проводок, то есть тех, в которых кредитуется тот или иной счет учета материальных ценностей, составляются по идентичным правилам, но в качестве критерия отбора (второй аргумент функции СУММЕСЛИ) используется ссылка на ячейку из столбца B, а суммирование производится по столбцу H. Например, в рассматриваемом нами примере, для строки 5 формула будет выглядеть следующим образом: =СУММЕСЛИ(Обороты!$L$4:$L$2000;B5;Обороты!$H$4:$H$2000) Подсчет сумм проводок по учету НДС, полученному с разными видами материальных ценностей, можно организовать исходя из тех соображений, что субсчета 19-го счета в точности соответствуют счетам учета разных категорий материальных ценностей. На наш взгляд, эту задачу удобнее всего решать путем предварительного «сведения» итоговых сумм НДС по всем приходам на листе «Обороты». Само по себе «сведение» сумм по отдельным позициям учета материальных ценностей не представляет особого интереса. Однако «привязав» полученные суммы НДС к конкретным видам ценностей легко «разбросать» их впоследствии по конкретным субсчетам счета 19 «НДС по приобретенным материальным ресурсам». Итак, в колонке M листа «Обороты» будем подсчитывать итоги по НДС, полученному при поступлении каждого вида материальных ценностей. Введем в ячейку M1 текст «НДС», в ячейку M3 формулу подсчета итогов по колонке =СУММ(M4:M2000), а в ячейку M5 формулу подсчета итогов по суммам НДС, поступившим с данным видом ТМЦ: =СУММЕСЛИ(Приход!$A$2:$A$5000;A5;Приход!$G$2:$G$5000) Далее указанная формула должна быть скопирована во все последующие строки, содержащие сведения о материальных ценностях. Теперь можно на листе «Проводки» задать формулы для расчета сумм проводок по учету НДС, поступившего с материальными ценностями. Если на счете 19.1 учитывается НДС, поступивший с товарами, то для проводки Д19.1 К60 формула для расчета суммы может быть записана в следующем виде: =СУММЕСЛИ(Обороты!$L$4:$L$2000;»41»;Обороты!$M$4:$M$2000) Ее отличие от ранее рассмотренных формул состоит в том, что она суммирует числа в столбце M листа «Обороты», а критерий поиска задан в виде константы – кода счета «41». Данная формула суммирует НДС для товаров, то есть тех строк справочника на листе «Обороты», в которых проставлен код счета 41. Аналогично следует построить формулы и для материальных ценностей, учитываемых на других счетах. В условиях нашего примера были получены результаты, представленные на рис. 6.18. Рисунок 6.18 – Автоматически сформированные проводки по движению материальных ценностей и смежным операциям Таблица легко может быть дополнена для формирования и подсчета сумм проводок других типов. В частности, для МБП можно составить проводки, отражающие начисление износа по передаваемым в эксплуатацию ценностям, базируясь на общих суммах расхода по ценностям, относящимся к данной категории. Переход к следующему периоду Переход к следующему периоду осуществляется по методике уже известной нам по предыдущей главе. Рабочая книга копируется в новый файл и в нем значения остатков материальных ценностей на конец периода стандартными средствами Excel переносятся в колонку остатков на начало периода как значения (но не как формулы!). Далее, из новой рабочей книги удаляется вся оперативная информация, относящаяся к предшествующему периоду (данные листов «Приход» и «Расход»). В результате обороты обнуляются, итоги на начало периода становятся равными итогам на конец предшествующего периода и цикл операций повторяется заново для текущего периода.   Обработка проводок В данной главе мы рассмотрим простую технологию ведения синтетического учета с использованием MS Excel. Она позволяет на основе вводимых проводок и входящих остатков счетов на начало периода автоматически получать обороты за этот период, исходящие остатки счетов на его конец и наглядно представлять эту информацию. Суть предлагаемого подхода состоит в следующем. На одном листе рабочей книги Excel строится макет оборотной ведомости счетов. В нем для каждого используемого счета (субсчета) указываются его код, наименование, входящий остаток на начало текущего периода и задаются формулы для автоматического расчета дебетового и кредитового оборотов и исходящего остатка на конец данного периода. На другом листе рабочей книги вводятся проводки, которые и являются основанием для расчета оборотов счетов. Ввод новой проводки автоматически инициирует пересчет оборотов и исходящих остатков счетов. В целях упрощения расчетных формул и компактности представления данных на экране остатки представляются свернуто, одной колонкой: дебетовые со знаком «плюс», а кредитовые – со знаком «минус». Для первого периода входящие остатки счетов вводятся вручную. Далее, при переходе к следующему периоду (месяцу или кварталу – как кому нравится) файл рабочей книги копируется, ему присваивается новое имя (например, соответствующее названию нового периода) и выполняется перенос остатков из колонки исходящих остатков файла предшествующего периода в колонку входящих остатков файла текущего периода. Эта операция выполняется стандартными средствами копирования значений Excel. В новом файле ячейки с проводками, относящимися к старому периоду, очищаются. Вследствие этого обороты нового периода обнуляются. Далее начинается ввод новых проводок, которые автоматически обновляются обороты, исходящие остатки и т.д. Естественно, приведенная технология имеет определенные ограничения, которые подробнее будут рассмотрены далее. Однако она вполне работоспособна и, как показала практика, вполне применима в практической учетной работе относительно небольших предприятий. Таблица для ввода проводок Создайте новую рабочую книгу Excel. Листу 1 присвойте имя «Счета» (Формат  Лист  Переименовать). Далее, перейдите на Лист 2 и присвойте ему имя «Проводки». На листе «Проводки» в ячейку A1 введите текст «Дебет», в ячейку B1 – «Кредит», в ячейку C1 – «Сумма», в ячейку D1 – «Комментарий». В эту таблицу, начиная со второй строки, будут вводиться проводки по принципу: одна строка – одна проводка. При этом в столбце A должен задаваться код дебетуемого счета, в столбце B – код кредитуемого счета, в столбце C – сумма проводки, а в столбце D – текстовый комментарий в произвольной форме. В качестве кодов счетов в нашей таблице, можно использовать, вообще говоря, любые комбинации символов. В рассматриваемом примере мы будем использовать счета без субсчетов и с субсчетами. Для правильной интерпретации счетов в формулах обязательно отформатируйте столбцы A и B так, чтобы все значения в них воспринимались как текстовые. Для этого выделите колонки A и B, вызовите карточку настройки форматов ячеек Excel (Формат  Ячейки), выберите раздел «Число», в списке форматов выберите строку «Текстовый» и нажмите клавишу OK. Для чего мы требуем форматировать столбцы A и B как текстовые? Дело в том, что сюда будут вводиться коды счетов и, несмотря на то, что они имеют числовые значения значительно удобнее, если их числовые коды будут восприниматься как текст. Если этого не сделать, то, например, код «68.1» или «68/1» (счет с субсчетом) Excel будет воспринимать как текст, а код «50» (счет без субсчета) как число. В этом случае расчетные формулы, которые будут использоваться на листе «Счета» будут отрабатывать неверно. Конечно, можно каждую ячейку указанных колонок форматировать отдельно. Но это явно не рационально. Поэтому лучше сразу задать единообразное форматирование для всех ячеек колонки. Теперь можно вводить проводки. Мы в качестве примера ввели данные, представленные на рис 6.19. Они пригодятся нам при проверке правильности задания формул на листе «Счета». Рисунок 6.19 – Пример ввода данных на листе «Проводки» Таблица оборотной ведомости счетов Перейдите к листу «Счета». Лист «Счета» должен включать таблицу для расчета оборотов и исходящих остатков счетов. Для ее создания выполните следующие действия. В ячейку A1 введите текст «Счет». В ячейку B1 - «Название счета». В ячейку C1 - «Входящий остаток». В ячейку D1 - «Дебетовый оборот». В ячейку E1 - «Кредитовый оборот». В ячейку F1 - «Исходящий остаток». Поскольку столбец A будет содержать коды счетов, то по рассмотренным выше соображениям его следует отформатировать так, чтобы все его ячейки воспринимались как текстовые. Ячейки B1:F1 отформатируйте по своему вкусу и установите удобную для просмотра информации ширину этих колонок. В нашем примере для первой строки мы установили горизонтальное и вертикальное выравнивание «по центру» (Формат – Ячейки, вкладка «Выравнивание»). Во второй строке расчетной таблицы будут подводиться итоги по входящим и исходящим остаткам, дебетовым и кредитовым оборотам. Они нужны для выполнения контрольных функций. В ячейку A2 введите текст «Итоги». В ячейку C2 - формулу =СУММ(C3:C1000) В ячейку D2 - формулу =СУММ(D3:D1000) В ячейку E2 - формулу =СУММ(E3:E1000) В ячейку F2 - формулу =СУММ(F3:F1000) Поскольку формулы однотипные, то можно ввести формулу только в ячейку C2, а в другие просто скопировать ее – Excel автоматически подставит идентификаторы нужных колонок. В указанных ячейках будут суммироваться значения остатков на начало периода, дебетового и кредитового оборотов и остатков на конец периода по всем используемым счетам и субсчетам. В нашем примере диапазон суммирования ограничивается строкой 1000. Если на предприятии используется большее число счетов и субсчетов, то следует установить большее значение. Если меньше, то можно установить меньший диапазон суммирования. Однако лучше указать диапазон с «запасом», чтобы впоследствии, при пополнении таблицы строками уже не думать, попадают ли нужные показатели в итоги. Для строки итогов мы выбрали жирный шрифт и пометили ее другим цветом (Формат  Ячейки, вкладка Вид, щелкнуть мышью по нужному цвету и по кнопке OK), чтобы она лучше выделялась. Вы можете выбрать иное оформление. Итоги намеренно вынесены наверх таблицы для того, чтобы они были видны сразу при входе в нее. Кроме того, так удобнее пополнять таблицу записями о новых счетах: в этом случае их можно просто вписывать в ближайшую пустую строку. В противном случае (при размещении итогов внизу таблицы) пришлось бы выполнять лишнюю операцию вставки строк. Подготовительные действия выполнены. На рис. 6.20 показан фрагмент того, что получилось. Рисунок 6.20 – Заголовок расчетной таблицы с установленными формулами подсчета итогов Теперь заполним строку 3. В ячейку A3 введем код счета. Напоминаем, что используемая нами модель компьютерного учета такова, что в качестве кода счета можно вводить произвольную комбинацию символов. В нашем примере будут использоваться как синтетические счета, так и субсчета, составные элементы которых разделяются символом «точка». Например, «50», «68.1», «68.2» и т.д. (кавычки не вводятся). Можно использовать субсчета второго, третьего и более высоких уровней, а также сокращенные текстовые наименования. Например, «41.1.101», «60.1.25.133», «61.Инфополис.Дог№34» и т.д. В нашем примере в ячейку A3 введен код «01». В ячейку B3 следует ввести название счета. В нашем примере это текст «Основные средства». Ячейка C3 должна содержать остаток данного счета на начало периода. Для первого периода он вводится вручную. В нашем примере мы задали значение 10000. Для счетов, имеющих дебетовое сальдо, входящий остаток вводится со знаком «плюс», для счетов, имеющих кредитовое сальдо – со знаком «минус». В принципе, можно было бы использовать и более привычную двухколоночную запись, но это сделало бы таблицу хуже обозреваемой и усложнило бы расчетные формулы. Обращаем внимание, что используемый нами одноколоночный принцип записи остатков счетов исключает возможность использования развернутых сальдо, то есть имеющих одновременно ненулевое значение и по дебету и по кредиту. Это может иметь место для некоторых счетов учета взаиморасчетов, например, для счета 76 «Расчеты с разными дебиторами и кредиторами». В принципе, проблема преодолима, но данной публикации мы не будем ее рассматривать. Теперь введем формулу для расчета дебетового оборота. В ячейку D3 листа «Проводки» впишите формулу: =СУММЕСЛИ(Проводки!A$2:A$5000;$A3;Проводки!$C$2:$C$5000) Подробно порядок использования функции СУММЕСЛИ() разбирался ранее. Поэтому здесь мы не будем подробно останавливаться на структуре параметров и правилах использования этой функции и ограничимся лишь общими комментариями. Приведенная формула просматривает диапазон строк 2-5000 листа «Проводки» и в тех строках, где значение в столбце A совпадает со значением ячейки A3 листа «Счета» суммирует числа столбца C. Поскольку на листе «Проводки» в столбце A мы записываем код дебетуемого счета, в столбце C – сумму проводки, а в ячейке A3 листа «Счета» у нас содержится код счета, то это означает, что приведенная формула будет складывать суммы проводок только тогда, когда дебетуемым счетом проводки является счет из текущей строки расчетной таблицы листа «Счета». Тем самым, будет рассчитан дебетовый оборот счета, указанного в данной строке расчетной таблицы. Поскольку формула для расчета дебетового оборота записана и действует на листе «Счета», а интервал просмотра и суммируемый интервал заданы на листе «Проводки», то перед обозначениями границ этих интервалов заданы названия данного листа. В противном случае 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) Внимание! Ввод формулы должен быть завершен одновременным нажатием клавиш 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 на основе данных нашего примера Проводка Первый сомножитель Второй сомножитель Третий сомножитель Результат Дебет Кредит Сумма 08 60 10000 1 10000 19 60 2000 1 1 2000 2000 01 08 10000 10000 60 51 12000 12000 68.1 19 2000 2000 62 46 60000 60000 46 68.1 10000 10000 46 41 40000 40000 51 62 50000 50000 41 60 20000 1 20000 19 60 4000 1 1 4000 4000 60 51 24000 24000 68.1 19 4000 4000 Результат функции СУММ 6000   Обратите внимание, что в формуле диапазоны для листа «Проводки» заданы в абсолютной форме, а обозначения ячеек на листе «Обороты» – в смешанной. Мы специально не фиксируем номер строки для того, чтобы иметь возможность скопировать формулу на все нужные строки столбца C («Сумма») этого листа. На рис. 6.26 приведены результаты формирования итогов по некоторым из используемых нами в проводках корреспонденций счетов.   Рисунок 6.26 – Итоги по проводкам с одинаковой корреспонденцией счетов