Пример отчетной ведомости по расчету просроченных платежей

Создайте новый лист и переименуйте в «Расчет просроченных платежей».

Рассмотрим пример составления отчетной ведомости фирмы, продающей компьютеры, позволяющей определить количество и сумму просроченных клиентами платежей (рис. 8.7)

Рис. 8.7. Расчет просроченных платежей.

Дата переучета введена в ячейку F2 с помощью формулы: =ДАТА(98;7;31)

Функция ДАТА возвращает дату в числовом формате.

Синтаксис: ДАТА(год; месяц; день).

Аргументы: год – число от 1900 до 2078; месяц – число, представляющее номер месяца в году. Если оно больше 12, то прибавляется к первому месяцу указанного года. Например, ДАТА (96; 14;2) возвращает числовой формат даты 2 февраля 1997 года; день – число, представляющее номер дня в месяце. Если оно больше числа дней в указанном месяце, то прибавляется к первому дню указанного месяца.

В ячейку Е2 введена формула, определяющая срок просрочки:

=ЕСЛИ(D2=0;$F$2-С2;""),

которая протаскивается на диапазон ЕЗ:Е20.

В ячейки G8, G9 и G10 введены следующие формулы:

(=СУММ((Е2:Е20>0)*(Е2:Е20<=29)*(В2:В20))}

{=СУММ((Е2:Е20>=30)*(Е2:Е20<=39)*(В2:В20))}

{=СУММ((Е2:Е20>=40)*(В2:В20))},

вычисляющие суммарные стоимости просроченных оплат сроком до 29 дней, от 30 до 39 дней и свыше 40 дней.

Дадим пояснения к третьей из этих формул. Excel в формуле массива возвращает условие (Е2:Е20>=40) в виде массива, состоящего из 0 и 1,

где 0 стоит на месте ячейки со значением меньше 40 и 1 – на месте ячейки со значением не меньше 40. Следовательно, данная формула вычисляет сумму произведений элементов массива (Е2:Е20>=40) (с единицами в случае просрочки на указанный срок и нулями – в противном случае) и массива В2:В20 (с ценами процессоров). Таким образом, третья формула возвращает суммарную стоимость заказов, просроченных не менее чем на 40 дней.

В ячейки G2, G3 и G4 введены формулы:

{=СУММ((Е2:Е20>0)*(Е2:Е20<=29))}

{=СУММ((Е2:Е20>=30)*(Е2:Е20<40))}

=СЧЁТЕСЛИ(Е2:Е20;">=40"),

вычисляющие количество просроченных оплат сроком до 29 дней, от 30до 39 дней и свыше 40 дней.

Функция СЧЁТЕСЛИ возвращает количество ячеек внутри указанного интервала, удовлетворяющих заданному критерию.

Синтаксис: СЧЁТЕСЛИ(интервал; критерий).

Аргументы: интервал – интервал, в котором нужно подсчитать ячейки; критерий – критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать (например, критерий может быть выражен следующим образом: 17, "17", ">17","Компьютер")