Используя расширенный фильтр, выведите модели и наименования ксероксов, чья цена находится в пределах от 2 до 6 тыс. руб

  • ID: 45316 
  • 8 страниц

Содержание:


Практическое задание

Книга продаж: Ксероксы.

Модель Название Стоимость

(руб.) Цена (руб.) Кол-во (шт.) Сумма (руб.) Ценовая категория

C100GLS Персональный 827 564

C110GLS Персональный 993 632

C200GLS Персональный+ 1429,5 438

C210GLS Персональный+ 1715,86 645

C300GLS Деловой 2410 437

C310GLS Деловой 2965,3 534

C400GLS Профессиональный 4269,65 409

C410GLS Профессиональный 5123,5 395

C420GLS Профессиональный+ 6415 298

C500GLS Профессиональный+ 7377,9 328

Итого

Средняя стоимость

Коэффициент 1,3

Формулы для расчетов:

Цена = Стоимость * Коэффициент.

Итого = Сумма по графе «Сумма».

Сумма = Цена * Кол-во.

Средняя стоимость = СРЗНАЧ (Стоимость).

Ценовая категория рассчитывается исходя из следующего:

 «низшая», если цена находится в пределах от 1 до 2,5 тысяч рублей;

 «средняя», если цена находится в пределах от 2,5 до 5 тыс. руб.;

 «высшая», если цена выше 5 тыс. руб.

Для заполнения графы Ценовая категория используйте функцию ЕСЛИ из категории «Логические».

2. Используя расширенный фильтр, выведите модели и наименования ксероксов, чья цена находится в пределах от 2 до 6 тыс. руб.

3. Используя функцию категории «Работа с базой данных» БДСУММ вычислите общую сумму от продажи ксероксов с названиями «Профессиональный» и «Профессиональный+».

4. Постройте объемную круговую диаграмму, отражающую количество проданных ксероксов всех моделей.

Математическое описание алгоритма:

Цену ксероксов рассчитаем по формуле:

Рi = Сi * К, i = 1,n

где Сi – стоимость ксерокса;

Pi - цена;

К - коэффициент.

Сумму продаж по каждой модели ксероксов вычислим по формуле:

Si = Pi * Qi, i = 1, n

где Si – сумма продаж;

Pi - цена;

Qi - количество.

Общее значение стоимости по всем моделям рассчитаем по формуле:

(ОС)i = Сi, i = 1, n

где ОСi – общее значение стоимости.

Общее значение стоимости по всем видам продукции в Excel определяется с помощью функции СУММ.

Среднюю стоимость вычислим как среднее арифметическое по формуле:

(SС)i =, i = 1, n

где (SС)i – средняя цена;

Сi – цена модели;

n - количество моделей.

Средняя цена в Excel определяется с помощью функции СРЗНАЧ.

Для заполнения графы Ценовая категория воспользуемся функцией ЕСЛИ.

Решение:

1. Оформим таблицу по образцу, вставим формулы для расчета показателей, электронная таблица с формулами представлена на рисунке 1, результат – на рисунке 2.

Рисунок 1 – Формулы для расчета показателей

Рисунок 2 – Результат вычислений

2. Воспользуемся расширенным фильтром для того, чтобы вывести модели и наименования ксероксов, чья цена находится в пределах от 2 до 6 тыс. руб.

Для этого в свободной области электронной таблицы создадим таблицу критериев и выходной документ.

В таблице критериев укажем имена полей из таблицы, по которым осуществляется поиск и условие поиска. В выходном документе укажем имена тех полей из таблицы, для которых осуществляется поиск.

Создадим таблицу критериев и спроектируем выходной документ (рисунок 3):

Рисунок 3 – Таблица критериев и выходной документ для расширенного фильтра

Для создания выходного документа используется команда меню Данные – Фильтр – Расширенный фильтр.

В окне диалога Расширенного фильтра укажем параметры представленные на рисунке 4.

Рисунок 4 – Условия расширенного фильтра

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

Рисунок 5 – Результат применения расширенного фильтра

3. Вычислим общую сумму от продажи ксероксов с названиями «Профессиональный» и «Профессиональный+» с помощью функции БДСУММ.

Функция возвращает данные из списка, которые удовлетворяют условию, определенному диапазоном критериев. Диапазон критериев включает копию названия поля, для которого выполняется подведение итогов, в списке.

Сформируем таблицу критериев и выходной документ (рисунок 6):

Рисунок 6 – Функция БДСУММ

В ячейке D29 записана формула: =БДСУММ(A2:G12;F2;B26:C28).

4. Построим объемную круговую диаграмму, отражающую количество проданных ксероксов всех моделей (рисунок 7).

Рисунок 7 – Круговая диаграмма

Литература