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

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

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

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

Для каждого объекта учета (строки) укажите коды счетов, на которые должна быть отнесена амортизация (рис. 11).

Рис. 10 - Установка форматирования для колонки ввода счетов отнесения амортизации.

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

Переименуйте «Лист 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 («Амортизация за месяц») листа «Картотека» (рис. 12).

Рис. 12 - Данные и формулы листа «Проводки» расчетной таблицы.

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

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