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

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

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

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

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

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

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

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

Рис. 10. 8. Результаты выполнения расчетов (после добавления данных о новых дебиторах)

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

- Скопируйте ячейки «КОД», «НОМЕР», «ИМЯ КЛИЕНТА» в ячейки В40:H40 (рис. 10.9).

- В ячейки B41:B53 и H41:H53 введите цифры «1» и «-1» для задания признаков дебетовой и кредитовой сделок.

- В ячейки С41:С53 и G41:G53 скопируйте номера счетов клиентов, имеющих дебиторскую или кредиторскую задолженности.

- В ячейки D41:D53 скопируйте соответствующие им имена клиентов, имеющих задолженности.

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

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

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

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

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

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

Таким образом, каждый раз Excel будет подсчитывать дебетовый баланс для N организаций, и вычитать из него сумму накопленных сделок для всех N-1 предыдущих структур.

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

- Аналогично подсчета дебетового баланса выполните учет стоимости товара, отданного нами на реализацию (кредитового баланса) подсчитайте его конечную величину.

Для ячейки F41 формула будет иметь вид:

Для ячейки F42:

Для ячейки F43:

- Постройте график удельных весов общих задолженностей по периодам, как показано в задании (рис. 10.11).

- На отдельном листе постройте график удельных весов дебетовых задолженностей по фирмам (рис. 10.12).

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

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