7. Расчет дебиторской задолжности (с использованием функций базы данных)

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

Для этой цели составлена таблица дебиторской задолженности за первую половину 2005 года – лист Дебиторская задолженность (рис. 7.1).

В столбце Код сделки занесены два числа 1 или -1.

1 означает, что товары нашей фирмы находятся на реализации.

-1 означает, что ваша фирма приняла на реализацию товары.

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

Определите значения в ячейках столбца Фактический баланс

• Значения столбца определяются из следующей зависимости:

Фактический баланс = Сумме сделки * Код сделки

• Полученной формулой заполните ячейки G8:G30.

Найдите количество дней, прошедших со дня заключения сделки

• Для этого, от значения Текущей даты отнимите Дату сделки и заполните полученной формулой ячейки H8:H30.

В зависимости от длительности периода задолженности в колонках с 9 по 12 поместите значения Фактического баланса с данной фирмой или 0.

• Расчет до 30 дней и свыше 90 дней не вызывает затруднений. Для этого используется функция ЕСЛИ (рис. 7.2).

• Расчет для интервала от 31 до 60 и от 61 до 90 дней выполните с использованием логических функций ЕСЛИ – И. Вариант задания функция для интервала 31 – 60 дней приведен на рис. 7.4 и 7.5.

• Выберите в категории Логические функции ЕСЛИ.

Рис. 7. 1. Таблица с исходными данными по старению счетов

Рис. 7. 2. Диалоговое окно аргументов функции ЕСЛИ

Курсор установите в поле Лог_выражение и вставьте в него функцию И. Для этого в списке функций, находящемся в левом углу строки формул (рис. 7.3) выберите логическую функцию И. Вставьте в ее поля следующие выражения (рис. 7.4).

Рис. 7. 3. Выбор функции И для вставки ее поле Лог_выражение в функции ЕСЛИ

Примечание. Если при открытии списка функция И отсутствует, то надо щелкнуть на опции Другие функции и найти ее в категории Логические функции

Рис. 7. 4. Диалоговое окно аргументов функции И

• Щелкните в строке формул на тексте функции ЕСЛИ. Тем самым вы вернетесь из окна функции И в окно функции ЕСЛИ. Далее впишите выражения, как указано на рис. 7.5.

• Щелкните на кнопке ОК. Полученной формулой заполните ячейки J8:J30.

Рис. 7. 5. Диалоговое окно аргументов функции ЕСЛИ - И

• Аналогично подсчитайте задолженность для интервала 61 – 90 дней.

Результаты расчетов приведены на рис. 7.8.

В 31 строке вычислите итоги по колонкам 6 и 7, а также с 9 по 12.

По каждой временной группе (колонки с 9 по 12) подсчитайте количество сделок, по которым имеются задолженности.

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

Рис. 7. 6. Диалоговое окно аргументов функции СЧЕТЕСЛИ

• Скопируйте полученную формулу в остальные ячейки диапазона J32:L32.

• С точностью до двух знаков определите долю задолженностей по каждой группе (ячейки I33:L33).

• Произведите сортировку строк таблицы по возрастанию значений столбца Номеров счетов. Результат выполнения будет иметь следующий вид (рис. 7.8).

После расчета таблицы добавьте в нее информацию о трех новых дебиторах, приведенных в строках 40…42 листа Дебиторская задолженность – рис. 7.7. (начиная с 30 строки - ), и вновь пересчитайте таблицу (рис. 7.8).

Рис. 7. 7. Данные о новых дебиторах

Рис. 7. 8. Результаты выполнения расчетов

Для выполнения расчетов по функции базы данных БДСУММ:

• Скопируйте ячейку "НОМЕР", "ИМЯ КЛИЕНТА" и "БАЛАНС" в ячейки С37 Е37 (рис. 7.9).

• В ячейки С38 ... D50 скопируйте номера счетов и имена клиентов, имеющих дебиторскую задолженность.

Рис. 7. 9. Создание области критериев и области вывода для вычисления суммарной задолженности каждого клиента

• В ячейку Е38 впишите формулу БДСУММ, которая суммирует числа в диапазоне ($А$6: $L$32), по полю 7, Фактическому балансу, по критерию Номер первого клиента (абсолютное значение) - имя первого клиента ($С$37:С38). В критерий должен входить адрес с наименованием столбца исходной таблицы - $С$37 (рис. 7.10).

Рис. 7. 10. Диалоговое окно аргументов функции БДСУММ

• В ячейку E39 впишите формулу, напоминающую предыдущую, но отличающуюся третьей частью: - СУММ (E38), что позволит просуммировать итоги для двух первых фирм и вычесть итоги для первой фирмы, чтобы оставить информацию только для второй фирмы.

• В ячейку E40 впишите формулу, напоминающую предыдущую, но отличающуюся третьей частью: - СУММ ($E$38:E39), что позволит просуммировать итоги для трех первых фирм и вычесть итоги для двух предыдущих фирм

Содержимое ячейки E40 скопируйте в оставшиеся ячейки графы E.

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

• Постройте график удельных весов задолженностей по временным интервалам, как показано в задании (в цвете – рис. 7.11).

• На отдельном листе постройте график удельных весов задолженностей по фирмам (в цвете – рис. 7.11).

Рис. 7. 11. График удельных весов задолженностей по временным интервалам и график удельных весов задолженностей по фирмам

Ответьте на вопросы:

1. Сколько аргументов использует функции баз данных?

2. Что означает аргумент поле в функциях баз данных?

3. Что означает аргумент критерий в функциях баз данных?

4. Какая функция позволяет извлечь отдельное значение из столбца списка, которое удовлетворяет заданным условиям?

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

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