2.2 Расчет просроченных платежей

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

Таблица с исходными данными имеет вид.

В ячейку F2 введена дата переучета с помощью формулы =ДАТА(2002;7;31). Так же можно использовать встроенную функцию категории Дата и время =ДАТА.

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

=ЕСЛИ (D2=0;$F$2-C2;””), которая протаскивается на диапазон Е3:Е20. Так же можно использовать встроенную логическую функцию =ЕСЛИ.

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

{=СУММ((E2:E20>0)*(E2:E20<=29)*(B2:B20))}

{=СУММ((E2:E20>=30)*(E2:E20<=39)*(B2:B20))}

{=СУММ((E2:E20>=40)*(B2:B20))},

вычисляющие суммарные стоимости просроченных оплат сроком до 29 дней, от 30 до 39 дней и свыше 40 дней. Фигурные скобки означают, что после ввода формулы необходимо нажать комбинацию клавиш Ctrl+Shift+Enter.

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

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

{=СУММ((E2:E20>=30)*(E2:E20<40))}

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

вычисляющие количество просроченных оплат сроком до 29 дней, от 30 до 39 дней и свыше 40 дней. Так же можно использовать встроенную статистическую функцию =СЧЁТЕСЛИ.

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

Выполните команду \Сервис\Параметры, и на вкладке Вид активизируйте режим формулы.

Тогда в режиме индикации формул ведомость будет выглядеть так.

2.3 Расчет затрат на производство

Пусть фирма производит CD-диски. Упаковка диска обходится фирме в 1 руб./шт., стоимость материала – 4 руб./шт. Готовые диски фирма продает по цене 10 руб./шт. Технические возможности фирмы позволяют выпускать до 5 тысяч дисков в день. Оплата труда рабочих является сдельной и зависит от количества выпущенных дисков. За первую тысячу дисков оплата труда составляет 0,3 руб./шт., за вторую тысячу дисков – 0,4 руб./шт., за третью тысячу дисков – 0,5 руб./шт., за четвертую тысячу дисков – 0,6 руб./шт. и свыше 4000 дисков – 0,7 руб./шт.

Фирме поступил заказ на изготовление 4500 CD-дисков. Необходимо подсчитать суммарные издержки и прибыль от выполнения данного заказа.

Таблица с исходными данными приведена ниже.

Для упрощения чтения формул присвоим диапазону D2:D7 имя ДискиШт, диапазону E2:E7 имя ОплатаРубШт, диапазону F2:F7 имя ОплатаРуб и ячейке B1 имя ЗаказШт. Для этого будем использовать команду /Вставка/Имя/Присвоить.

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

Зарплата рабочих, в зависимости от объема выпущенных дисков, находится в диапазоне F2:F7 по формуле

{=ЕСЛИ(ЗаказШт-1000>ДискиШт;1000*ОплатаРубШт;

ЕСЛИ(ЗаказШт>ДискиШт;(ЗаказШт-ДискиШт)*ОплатаРубШт;0))}.

Эта формула массивов, поэтому вначале, перед вводом формулы диапазон F2:F7 необходимо выделить, а после ввода формулы нажать комбинацию клавиш Ctrl+Shift+Enter.

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

Стоимость упаковки и материалов вычисляются в ячейках В6 и В7 по формулам: =В1*В3 и =В1*В4 соответственно.

Зарплата, общие издержки и прибыль вычисляются в ячейках В8, В9 и в10 по формулам: =СУММ(ОплатаРуб), =СУММ(В6:В8) и =В1*В2-В9.

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

Эта же таблица в режиме индикации формул.

Продолжение таблицы.