По каждой временной группе (колонки с 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. Какая функция позволяет усреднять значения в столбце списка, которые удовлетворяют заданным условиям.

8. УКАЗАНИЯ К ВЫПОЛНЕНИЮ ТИПОВОГО ВАРИАНТАКОНТРОЛЬНОЙ РАБОТЫ

ТИПОВОЙ ВАРИАНТ КОНТРОЛЬНОЙ РАБОТЫ

Создайте список, содержащий следующие столбцы:

Месяц

Продавец

Продукт

Продано, шт.

Цена, руб.

Сумма, руб.

Столбцы "Месяц" и "Продавец" должны содержать не более трех значений. Например, для "Месяц" – январь, февраль, март. Столбец «Продукт» должен содержать 4 значения. Например, хлеб, булка, молоко, кефир. Каждое наименование продукта может иметь различную цену (например: молоко – за 10 руб. и за 24 руб.). Столбец "Сумма" вычисляется по формуле как произведение "Продано" на "Цену".

Список должен содержать 20 строк, не считая метки столбцов.

1. На этом же рабочем листе отсортируйте список простой сортировкой сначала по "Месяцу", затем по "Продукту", затем по "Цене". Далее примените в к "Месяцу" пользовательский порядок сортировки: март, февраль, январь.

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

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

4. На следующем рабочем листе подведите итоги по "Продавцам" – выведите сумму по столбцу "Сумма".

5. На следующем рабочем листе подведите итоги по "Продуктам" – выведите среднюю цену.

6. По исходному списку постройте на рабочем листе рядом со списком сводную таблицу с макетом, приведенном на рисунке:

7. Поле "Продукт" переместите в строки, поле "Цена" добавьте в столбец.

8. Сгруппируйте Продукты в две группы и переименуйте их.

9. Сгруппируйте цены по интервалам.

10. Из поля "Продавец" скройте одного из Продавцов.

11. Скройте детализирующие данные по одной из групп Продуктов.

12. В область данных добавьте поле "Продано" и измените операцию "Сумма по полю Продано" на "Максимум по полю Продано".

13. Для данных "Сумма по полю Сумма" представьте числа в денежном формате.

14. На основе исходного списка с использованием функций базы данных ДМАКС(),и БИЗВЛЕЧЬ() рассчитайте и сформируйте следующую таблицу:

	январь	март
	Сумма, руб.	Продавец	Сумма, руб.	Продавец
Максимальная сумма