Расчет кредитных выплат в Excel

Расчет кредитных выплат в ExcelРасчет кредитных выплат с помощью Excel.

Многие из нас брали кредит в банке.

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

Впрочем, поэтому я за них и не берусь.

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

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

  • сумму ежемесячных выплат (аннуитетный платеж);
  • часть из этой суммы, которая идет на погашение основного долга;
  • оставшуюся часть, которая идет на погашение процентов.

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

Построение таблицы

Построить такую таблицу совсем несложно. использовать мы будем три функции:

  • ПЛТ — рассчитывает сумму регулярного платежа при той или иной процентной ставке;
  • ОСПЛТ — рассчитывает часть суммы регулярного платежа, которая уходит на погашение основного долга;
  • ПРПЛТ — рассчитывает часть суммы регулярного платежа, которая уходит на погашение процентов.

Для начала, внесем основные данные, а именно — сумму кредита, срок кредита в годах, и процентную ставку. Например, это будет 200 тыс. руб, на 2 года под 18%.

Составим колонки таблицы расчета:

  1. Месяц — порядковый номер месяца очередной выплаты;
  2. Аннуитетный платеж — общая сумма ежемесячных выплат;
  3. В т.ч. основная сумма — часть общей суммы, уходящей на погашение основного долга;
  4. В т.ч. проценты — оставшаяся часть суммы, уходящей на погашение процентов;
  5. Остаток долга на конец месяца.

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

Внесение формул

В верхнюю ячейку колонки «Аннуитетный платеж» вносим такую формулу: =ПЛТ($B$4/12;$B$3*12;-$B$2). Знак $ перед адресом колонки и номером строки обозначает, что при протягивании этой формулы на другие ячейки, этот адрес не будет изменяться.

  • Первый аргумент функции ПЛТ обозначает процентную ставку. Мы делаем ссылку на B4. Но, поскольку ставка указывается годовая, а нам необходимо вычислить ставку за месяц, мы это значение делим на 12.
  • Второй аргумент должен указывать период, на который взят кредит. В нашем случае это 5 лет, однако, поскольку мы уже заложили ежемесячный расчет, то и период необходимо указать в количестве месяцев. Поэтому ссылку на B3 мы умножаем на 12.
  • Третий аргумент — сумма кредита. Ссылаемся на B2. Однако, если мы просто сошлемся на сумму кредита, то будет выпадать отрицательное значение. И это логично, ведь каждая выплата, это — убыток. Поэтому, если мы не хотим видеть отрицательные значения, то перед ссылкой поставим минус.

В верхнюю строку колонки «в т.ч. основная сумма» вносим формулу: =ОСПЛТ($B$4/12;A7;$B$3*12;-$B$2). Здесь все аналогично, кроме второго аргумента, который указывает на порядковый номер периода (в нашем случае, это порядковый номер месяца).

В верхнюю строку колонки «в т.ч. проценты» вносим формулу: =ПРПЛТ($B$4/12;A7;$B$3*12;-$B$2). Здесь все аргументы соответствуют предыдущей формуле.

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

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

При расчете долга на конец месяца, мы должны учитывать и выплату на погашение процента. Поэтому это не просто остаток долга минус текущий аннуитетный платеж, а остаток долга плюс сумма на погашение процента минус текущий аннуитетный платеж (уж простите меня за сложность). Поэтому формула, которую мы внесем, будет такой: =B2+D7-B7.

  • B2 — это ссылка на сумму кредита (остаток на прошлый месяц);
  • D7 — ссылка на сумму погашения процента, набежавшего к концу первого месяца;
  • B7 — ссылка на сумму первой выплаты по кредиту.

Протянуть на все ячейки ниже для автозаполнения эту формулу мы не можем, т.к. адрес с остатком долга поменялся. Теперь это будет не В2, а значение в ячейке выше. Поэтому формула теперь будет следующей: =E7+D8-B8.

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

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

Анализ «что-если»

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

Для начала, подготовим таблицу для анализа. Нам нужно будет сравнивать суммы ежемесячных выплат по двум критериям — процентная ставка, и срок кредита. Внесем в наименования строк нашей таблицы сравниваемые процентные ставки, а в наименования колонок — различные периоды в годах.

На пересечении наименований строк и колонок необходимо сделать ссылку на зависимую ячейку. В нашем случае это любая ячейка из колонки «Аннуитетный платеж». Поставим ссылку на В7 (появиться значение с этой ячейки — 9986). Теперь выделяем эту нашу таблицу, и нажимаем на кнопку меню «Анализ «что-если«. В выпавшем меню выбираем строку «Таблица данных«.

Появилось окошечко, для определения критериев. Значения по столбцам у нас указывают на срок кредита в годах, поэтому в верхней строке окошка делаем ссылку на ячейку В3. Значения по строкам — это процентная ставка. Значит ссылаемся на В4. Нажимаем ОК.

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

Заполнение формы Т-12 в Excel. I часть

Заполнение формы Т-12 ExcelЗаполнение формы Т-12 в Excel. Часть I.

В серии статей «Учет рабочего времени в Excel» мы настраивали автозаполнение табеля учета рабочего времени в установленной отчетной форме Т-12.

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

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

Внесение первичных данных

Начнем с самого простого — сделаем ссылки на ячейки, которые уже должны быть заполнены на предыдущих страницах. Это колонка «Табельный номер» и графа 22 — «Часы (дни)«. Табельный номер мы заносили на странице «Учет«. Ставим знак «=» и кликаем мышкой на соответствующей ячейке. И так по всему списку.

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

Далее, в графе 19, пишем установленный для каждого сотрудника оклад (о более сложных схемах оплаты труда мы будем говорить в другой статье). Графа 20 для бухгалтеров. В нее заносится счет БУ, на который относятся расходы по оплате труда.

Собственно, расчеты, начинаются с графы 21. Это окладная сумма, которая рассчитывается пропорционально отработанному времени (как правило, в часах). И вот здесь начинается очень интересная тема. Для ежемесячного учета мы отталкиваемся от нормативов рабочего времени, установленных трудовым законодательством и указанных в ежегодно утверждаемом правительством производственном календаре.

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

Подготовка списка для месячного норматива рабочего времени

Для начала дополним список с месяцами. Откроем страницу «Список«, и добавим колонку справа от наименований месяцев. Назовем эту колонку «Норматив«.

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

Поэтому выделяем колонку, в которую будем заносить данные, и через правую кнопку мыши вызываем диалоговое окно «Формат ячеек«. Чтобы лишние поля, типа «Минуты» и «Секунды» нам не мешались, я выбираю пункт «Все форматы» и ставлю в поле «Тип» букву «ч» в квадратных скобках (вот так — [ч]), обозначающей отображение только часов.

Найдем в интернете производственный календарь (я использую эту  ссылку) и перепишем с него нормативы по каждому месяцу для 40-часовой рабочей недели. Только записывать нужно будет с минутами через двоеточие, например — 120:00. Иначе программа будет число воспринимать неадекватно.

Во второй части статьи «Учет рабочего времени в Excel» мы давали имя списку месяцев. Сейчас нам необходимо создать еще один поименованный диапазон. Пусть он будет называться «НормативМесяц» (без пробела). Для этого выделяем диапазон с ячейки A2 до ячейки B13 (в него должен попасть список месяцев и нормативы часов в эти месяцы). В адресную ячейку вносим имя «НормативМесяц» (см. картинку выше).

Настройка автоматического расчета окладной части заработной платы

Теперь мы можем рассчитать окладную сумму, рассчитанную пропорционально отработанному времени сотрудника. Формула арифметически проста — отработанное время делим на нормативное время и умножаем на оклад. Но как это реализовать в Excel?

Нормативное время мы можем взять на основании выбранного на странице «Учет» месяца. Для этого используем функцию ВПР, которая будет выводить то количество часов, которое соответствует выбранному месяцу (для этой функции мы и создавали диапазон «НормативМесяц«). Функцию можно сразу ввести в строку формул: ВПР(Учет!C3;НормативМесяц;2;ЛОЖЬ).

Устанавливаем курсор вначале формулы (после знака равно) и вводим частное — адрес ячейки с суммой отработанного времени. Для первой записи это будет ячейка BI11 (я, обычно, не заношу адрес ячейки вручную, а просто кликаю по ней мышкой).

Затем устанавливаем курсор в конце формулы и умножаем все на ячейку с суммой оклада (М11). Конечная формула для первой строки будет выглядеть так: =BI11/ВПР(Учет!$C$3;НормативМесяц;2;ЛОЖЬ)*M11

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

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

Копируем формулу на все прочие ячейки

Графы с 23 по 32 оставляем пустыми. для нашей формы оплаты труда они не используются. С граф 33 и 34 делаем простые ссылки на графы 21 и 22.

Страница «Расчет» заполнена. Страницу итогов начнем настраивать в следующей статье.

Учет личных финансов в Excel. I

Учет личных финансов ExcelУчет личных финансов в Excel. Часть I.

Старая пословица гласит — «Деньги любят счет».

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

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

Продумывание структуры файла

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

Продумаем названия колонок. Во-первых нам нужна дата осуществления операции. Назовем первую колонку «Дата«. Далее нужно обозначить — доход это, или расход. Так и напишем «Доход/Расход«. Затем пишем «Сумма«. Это, собственно, сумма операции. Основные колонки составили. Однако для дальнейшего упрощения аналитики неплохо было бы добавить колонки с о статьями доходов и расходов и с контрагентами. И, конечно же, примечания, где мы можем уточнить содержание операции.

Итак, у нас получились следующие колонки (См. рисунок):

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

Вот что у нас должно получиться:

Чем меньше мы будем вводить информацию вручную, тем лучше. Для того, чтобы использовать выбор из списков, нам необходимо эти списки создать. Давайте создадим отдельный лист «Списки«. Как минимум там уже напрашивается список «Доход/Расход» и списки статей расходов и доходов.

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

Мои имена диапазонов следующие:

  • «ДоходРасход»;
  • «Доход» — статьи доходов;
  • «Расход» — статьи расходов.

Отдельно создадим лист отчетов. Здесь будут подводиться итоги как промежуточные, так и окончательные. Ну, и для наглядности, пусть будет отдельный лист графиков.

 

Структура таблицы готова. Если что-то понадобиться в ходе работы, мы всегда можем внести дополнения или исправления.

Настройка ввода даты

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

Конечно, самое логичное — использовать диалог. Например, выскакивает окошко с предложением выбрать дату, и дата отображается во всех строках, внесенных в этот день. Но такое возможно сделать с помощью программирования на VBA, а об этом мы будем говорить гораздо позднее.

Сейчас же я предлагаю следующий вариант — мы заносим дату единожды, и все записи, которые будут располагаться ниже, будут соответствовать ей. Для этого нам потребуется добавить колонку, которая будет скрыта, но именно к ней будут привязаны все прочие записи. Назовем ее «ГлавДата«.

Условие, которое будет настроено в этой колонке, предусмотрит отображение той записи, которая будет слева, при ее наличии. Если записи слева не будет, то должна быть скопирована запись сверху. Таким образом, если мы не внесем в колонку «Дата» значение, то в колонке «ГлавДата» будет продублирована верхняя запись, и лишний раз вносить дату не нужно.

Формулу предлагаю внести не в верхней пустой ячейке, а во второй строке таблицы (по адресу C3). Выглядеть она будет так:

=ЕСЛИ(ЕПУСТО(Таблица2[[#Эта строка];[Дата]]);C2;Таблица2[[#Эта строка];[Дата]])

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

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

Чтобы избежать ошибки, в колонке «Дата» можно установить защиту от ошибочного ввода данных с помощью меню Данные-проверка данных (об этом подробнее здесь). Установим значение «Дата» больше, например, вчерашнего дня (если, конечно, вы не предполагаете вносить вчерашние данные.

В следующей статье мы настроим таблицу ввода данных о доходах и расходах для максимально удобного и безошибочного ввода. А затем начнем настраивать отчеты.

Учет личных финансов в Excel. II

Учет личных финансов ExcelУчет личных финансов в Excel. Часть II.

В прошлой статье мы выстроили структуру файла «Учет личных финансов«, и начали настраивать страницу ввода данных.

Продолжим работу.

Настройка выбора из списка

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

Начнем с колонки «Доход/Расход«. Выделяем все ее ячейки, и в меню «Данные» выбираем пункт «Проверка данных«. В открывшемся окне выбираем тип данных «Список«, а в поле «Источник» вписываем наименование диапазона, на который ссылаемся. У меня он называется «ДоходРасход«. Теперь любое значение в колонке будет задаваться выбором из заданного списка.

С настройкой выбора статьи будет чуть сложнее. Нам необходимо сделать так, чтобы выбор в этой колонке зависел от выбора в колонке «Доход/Расход«.  В одном случае используем один список, в другом — другой. Ставим табличный курсор в любое поле колонки «Статья«, и вызываем меню проверки значений.

«Тип данных«, как и прежде, выбираем «Список«. А вот в поле «Источник» нужно будет внести функцию ДВССЫЛ. Она будет ссылаться на ячейку колонки «Доход/Расход» и вызывать тот список, который в ней отображается. Поставим галочку в пункте «Распространить изменения на другие ячейки с тем же условием«. Так формула будет автоматически распространяться на всю колонку таблицы.

Настроим колонку «Сумма«. Нам не нужны здесь любые значения, кроме положительных чисел. Поэтому выделяем колонку, вызываем меню проверки значений и выбираем:

  • «Тип данных» — «Действительное»;
  • «Значение» — «Больше»;
  • «Минимум» — «0».

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

Колонку «ДатаГлав» предлагаю скрыть, чтобы она нас не отвлекала.

Таблица ввода значений готова! Переходим к формированию отчетов. Нам поможет, если мы внесем какие-нибудь пробные данные:

Формирование отчета

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

Находим имя диапазона, которое мы не назначали. Это будет имя «Таблица1» или такое же имя с любой другой цифрой, в зависимости от того, сколько таблиц вы создали до того, как вставили ее на листе «Ввод«. Выделяем этот пункт, и жмем на кнопку «Изменить«. Меняем название, например, на «Ввод» и жмем «ОК«. Затем закрываем окно.

Мы забыли еще про один момент — у нас и доходы и расходы являются положительными числами. А расходы, по логике, должны вычитаться из доходов. Предлагаю правее колонки «Сумма» добавить колонку, в которой числа, соответствующие расходам, будут преобразовываться в отрицательные. Назовем ее «СумПреобр» и внесем формулу:

=ЕСЛИ(Ввод[[#Эта строка];[Доход/ Расход]]=»Расход»;-Ввод[[#Эта строка];[Сумма]];Ввод[[#Эта строка];[Сумма]])

Суть ее проста:

  • если в колонке «Доход/Расход» выбрано «Расход«;
  • то к значению из колонки «Сумма» добавляется знак минус;
  • В противном случае значение из колонки «Сумма» копируется без изменений.

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

Теперь переходим на лист отчеты. Открываем закладку меню «Вставка» и жмем на пункт «Сводные таблицы«. В поле «Таблица или диапазон» заносим имя только что поименованной таблицы — «Ввод«.  Нажимаем «ОК«. Видим слева появилось поле сводной таблицы (оно пока пустое), а справа — список полей для настройки и четыре зоны:

  • Фильтр отчета;
  • Названия столбцов;
  • Названия строк;
  • Значения.

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

Затем находим пункт «Группировка по полю«. Выбираем пункт «Месяцы» и нажимаем «ОК«. Колонки должны сгруппироваться по месяцам.

Теперь необходимо задать имена строк. Они у нас будут сгруппированы по двум параметрам — Доход/Расход и статьи. Поэтому вначале переносим в зону «Названия столбцов» пункт «Доход/Расход«, а затем, ниже, пункт «Статья«.

В зону «Значения» переносим пункт «СумПреобр«. Вот что у нас должно получиться:

Месяц можно задать и в качестве фильтра. Достаточно просто перенести пункт «ГлавДата» в зону «Фильтр отчета«. В сводной таблице появится поле для выбора месяца.

Однако, на мой взгляд, лучше оставить значения поля «ГлавДата» в названиях колонок. Это даст нам больше возможностей, в том числе и фильтрации. Кликните правой кнопкой мыши на поле с датой, и выберите в контекстном меню Фильтр Фильтры по дате.

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

Отчетная таблица готова!

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

Учет личных финансов в Excel. III часть

Учет личных финансов ExcelУчет личных финансов в Excel. Часть III.

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

Осталась самая малость — построение графиков и окончательное оформление.

В конце текста выложен файл, который вы можете скачать.

Построение графиков

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

Итак, идем на страницу «Графики«, открываем вкладку меню «Вставка» и жмем не на саму иконку сводной таблицы, а на надпись. Раскроется небольшой список, в котором мы выбираем «Сводная диаграмма«. Далее — все как и со сводной таблицей:

  • В поле «Таблица или диапазон» вносим имя таблицы — «Ввод«;
  • Нажимаем «ОК«;
  • В зону «Фильтр отчета» заводим вначале пункт «ГлавДата«, а ниже «Доход/Расход«;
  • В зону «Поля осей» заводим пункт «Статья«;
  • В зону «Значения» заводим пункт «Сумма«.

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

У вас должно появиться окно «Область фильтра сводной таблицы«. В поле «Доход/Расход» здесь выбираем «Расход» и нажимаем «ОК«. Отфильтровать по месяцу можно в специально оставленном для этого поле над диаграммой.

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

На панели находим иконку «Изменить тип диаграммы» и жмем на нее. Откроется окошко, в котором выбираем пункт «Круговая» и жмем «ОК«. Название таблицы будет «ИТОГ«. Давайте заменим его на «Расходы«. Кликните мышкой по названию и напечатайте новое имя таблицы.

Подобным образом формируем график доходов. Только на этот раз фильтруем по доходам. Здесь я оставил гистограмму.

Надпись «ГлавДата» лучше заменить на «Месяц». Так будет понятнее. Вот что у меня получилось.

Окончательное оформление и защита

Необходимости в дополнительных оформлениях страниц «Отчеты» и «Графики» я не вижу. На мой взгляд — все итак нормально. А вот страницу «Ввод» нужно еще немного доработать.

Во-первых, скроем не скрытую раньше колонку «СумПреобр«. Она будет заполняться автоматически, как и скрытая нами ранее колонка «ГлавДата«.

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

Также настраиваем условие со словом «Расход«. Заливку, на этот раз, делаем красную.

Ну, пожалуй, и все. Прочие интересности оформляйте уже самостоятельно. А по ссылке можете скачать файл, который у меня получился.

Расчет итогов инвентаризации в программе Excel

Расчет итогов инвентаризации ExcelРасчет итогов инвентаризации в Excel.

Глядя на усилия, затрачиваемые бухгалтерами-ревизорами при расчете итогов инвентаризации, я решил подумать над автоматизацией этого процесса.

Конечно, меня нельзя отнести к знатокам бухгалтерского учета, поэтому, надеюсь, файл, который я разработал, мне помогут довести до ума мои читатели (так же, например, как совместными усилиями мы отточили план-график проекта).

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

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

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

В появившемся окне не забываем поставить галочку напротив пункта «Создать копию». Затем выбираем лист, перед которым будет располагаться наша копия (я выбираю лист «Свод») и нажимаем «ОК». Теперь на этот лист можно вносить данные инвентаризации.

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

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

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

Итак, в первой колонке представлена иерархия – подразделение, покупатель, наименование товара. Затем представлено общее количество по данным документов, сумма в рублях по документам, общее количество по бухгалтерскому учету и сумма по бухгалтерскому учета.

В последних двух колонках представлена разница по количеству и по сумме между бухгалтерскими данными, и данными по документам.

Собственно, это все. Теперь давайте займемся доработкой. Жду предложений в комментариях.

Учет затрат на персонал в программе Excel

Учет затрат на персонал в программе ExcelУчет затрат на персонал в Excel.

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

Сегодня мы поработаем над учетом затрат.

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

Значит — создаем единый файл на все расходы, связанные с персоналом.

Создание таблицы

В таблицу, которую будет вести сотрудник службы персонала, включаем следующие поля:

  • — порядковый номер записи;
  • Дата — дата внесения записи;
  • Тип контрагента — это могут быть СМИ, кадровые агентства, рекламные агентства и т.п. Это поле необходимо для облегчения поиска и группировки контрагентов;
  • Наименование контрагента — собственно, наименовании организации, с которой осуществлялся расчет;
  • Статья затрат — здесь, думаю, объяснять не надо;
  • Предмет оплаты — что именно приобретаем или заказываем;
  • Количество;
  • Стоимость единицы;
  • Сумма.

Вносим эти наименования колонок, выделяем диапазон и вставляем таблицу (см.рис.). Дадим этому листу наименование «Затраты». Теперь займемся настройкой ввода данных.

Настройка ввода данных

Колонка «» будет включать в себя целые числа. Поэтому выделяем эту колонку, во вкладке меню «Данные» нажимаем на пункт «Проверка данных» и в пункте «Тип данных» выбираем «Целое число«. В пункте «Значение» выбираем «Больше«, и в пункте «Минимум» устанавливаем значение «0«.

Аналогичным образом обеспечиваем ввод данных в колонке «Дата«. Только вместо «Целое число» выбираем «Дата«. В качестве начальной даты предлагаю установить первое января текущего года.

Поля «Тип контрагента» и «Наименование контрагента» будут выпадающими списками. Их я предлагаю связать, чтобы в поле «Наименование контрагента» выпадал список, соответствующий выбранному ранее типу. Самый простой способ — использовать функцию ДВССЫЛ, но, в этом случае, придется возится с созданием списков и обеспечить точное совпадение имен групп списков контрагентов с элементами списка «Тип контрагента«. А там, где есть шанс допустить ошибку, она обязательно будет допущена. Поэтому пойдем по более сложному, но и более надежному пути. Но об этом позднее. А сейчас продолжим с остальными полями таблицы.

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

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

В колонке «Количество«, как и в колонке «» установим ограничение на внесение только целых чисел больше 0.

В колонке «Стоимость единицы» у нас будут действительные значения больше 0. А формат настроим в рублях.

В колонке «Сумма» у нас будет произведение значений колонок «Количество» и «Стоимость единицы«. Здесь тоже установим денежный формат.

Создание связанных списков

Для начала создадим лист, на котором будем вести все списки. Так и назовем этот лист — «Списки«.

Теперь вставим туда таблицу следующего вида:

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

Выделяем всю строку с наименованиями типов контрагентов (включая колонку ««) и дадим ему имя «Тип«.

Во вкладке меню «Работа с таблицами» (она появляется в верхней строке меню, если табличный курсор установить в пределах таблицы) ставим галочку напротив пункта «Строка итогов«. В итогах колонки «» выбираем «Количество«. Ячейке с подсчитанным количеством чисел в колонке «» даем имя «СчетКонтрагенты«. Это значение будет задавать высоту списка в основной таблице.

Затем нам необходимо ячейке заголовка первой колонки (в моем случае, это колонка «Агентство«) дать имя «Старт«. От нее будет вестись отсчет смещения в формуле.

Теперь переходим на страницу «Затраты«. Выделяем колонку «Тип контрагента» и жмем на пункт «Проверка данных» во вкладке меню «Данные«. В поле «Тип данных» выбираем пункт «Список«, а в поле «Источник» пишем формулу =Тип. Этот выпадающий список настроен.

Выделяем колонку «Наименование контрагента«, также выбираем тип данных — список, а в поле «Источник» заносим следующую формулу: =СМЕЩ(Старт;1;ПОИСКПОЗ(C2;Тип;0)-2;СчетКонтрагенты;1). Суть формулы в том, что она формирует список на основании координат в таблице «Тип«, соответствующих значению в колонке «Тип контрагента«.

Таблица готова. Можно начинать вводить данные.

Настройка отчета

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

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

Последний штрих — вставка графика. Убедимся, что табличный курсор в пределах сводной таблицы, заходим во вкладку меню «Вставка» и выбираем понравившийся вид графика (я выбрал гистограмму).

База учета затрат готова, можно пользоваться.