Пример выполнения практического задания

В качестве примера рассмотрим оборудование – колонки Genius и Defender (табл. 6).

Таблица 6 – Исходные данные

Колонки Genius	Вид	Опт. цена (у. е.)
Genius SP-M06A	SP	11
Genius SP-306	SP	14
Genius SP-F10	SP	15
Genius SP-710	SP	18
Genius SP-330	SP	20
Genius SP-G16	SP	25
Genius SP-715	SP	27
Genius SP-718	SP	45
Колонки Defender
Defender Blaze 25	DB	31
Defender Blaze 20	DB	32
Defender Blaze 30	DB	38
Defender Diamond 17	DB	39
Defender Blaze 50	DB	40
Defender Blaze 40	DB	47
Defender Diamond 20	DB	49
Defender Blaze 60	DB	59

На основе исходной таблицы создается база данных (см. рис. 1).

Первые три столбца (А,В,С) заполняются исходными данными (табл. 6). Для определения розничной цены (столбецD) используется генератор случайных чисел. Для получения случайного числа в пределах от р до q используется формулаСЛЧИС( )*(q–p)+p. Данные столбцаD рассчитываются по формуле=Сn+СЛЧИС( )*(q–p)+p, гдеn– номер строки. В рассматриваемом примере N=0, поэтому цена увеличивается на величину в диапазоне от 0 до 1. Случайное число в диапазоне от 0 до 1 получается с помощью функцииСЛЧИС( ).Используя командуСпециальная вставкаи флагЗначения,содержимое столбцаDкопируется в соседний столбецЕ. КомандуСпециальная вставкаможно вызвать на вкладкеГлавная,щелкнув стрелку под кнопкойВставить в группеБуфер обмена

Столбец D скрывается.

В ячейку А2 вводится значение курса 1 у.е. в рублях. Ячейке присваивается имяКурс. Для этого выделив ячейкуА2 щелкают мышкой в поле Имя и вводят слово «Курс».

В столбце Fрассчитывается стоимость оборудования в рублях. В формуле вместо абсолютного адреса используется имя ячейкиКурс.

Полученные данные в столбцах ЕиFокругляются с точностью до центов и копеек соответственно.

На рис. 8 представлен лист Задние 1в режиме отображения формул.

Рис. 8. Лист Задние 1в режиме отображения формул

2. На листе 2, используя команду Промежуточные итогивкладкиДанные,определяется: в 1-й копии таблицы – минимальная цена (в рублях) оборудования сравниваемых видов, во 2-й копии – максимальная цена, в 3-й копии – средняя цена и в 4-й – количество оборудования каждого вида. Результат выполнения задания 2 смотри на рис. 2.

3. На листе 3 перед выполнением задания из исходной таблицы удаляются записи об оборудовании 2-го вида. В рассматриваемом примере из таблицы с помощью расширенного фильтра необходимо выбрать оборудование 1-го вида по стоимости наиболее близкое к среднему значению цены колонок (Р=0) (условие отбора выбирается из табл. 2). Для решения поставленной задачи необходимо произвести дополнительные вычисления. Для этого к исходной таблице добавляется столбец G, в котором вычисляется абсолютное значение разности цены в рублях и средней цены оборудования 1-го вида. После чего формируется диапазон критериев для расширенного фильтра (рис. 9). Для исключения совпадения стоимости поусловию Кс минимальным значением стоимости оборудования 1-го вида, используется вычисляемый критерий (Цена 1). После создания диапазона критериев вызывается командаДополнительно вкладки Данные.

Рис. 9. Лист Задние 3в режиме отображения формул

4. На лист 4, с помощью функций из категории Работа с базой данных,извлекаются наименование и цена оборудования 1-го вида с минимальной и максимальной ценой, а так же с ценой, удовлетворяющейусловию К(рис. 4). ЛистЗадание 4в режиме отображения формул представлен на рис. 10.

Рис. 10. Лист Задние 4в режиме отображения формул

5. Чтобы проанализировать характер изменения объема продажи оборудования на Листе 5создается новая таблица (табл. 3), в которой отражаются три наименования оборудования: с минимальной (Колонки SP-М06), максимальной (Колонки SP-718) стоимостью и стоимостью поусловию К(Колонки SP-330). Наименования оборудования извлекаются из соответствующих ячеек листаЗадание4.

В ячейки J6:J11, K6:K11 и L6:L11 заносятся случайные числа с учетом варианта задания, которые определяют объёмы продаж оборудования. Например, для N=0 и М=6:

• в ячейки J2:J7 заносятся случайные числа от 6 до 306;

• в ячейки К2:К7 – от 6 до 106;

• в ячейки L2:L7 – от 6 до 206.

Для получения целочисленных значений используется функция ОКРУГЛ для введенных случайных чисел: =ОКРУГЛ(СЛЧИСЛ( );0).

Полученные данные из столбцов J, К и L копируются в соответствующие ячейки В6:В11, D6:D11 и F6:F11 командой Специальная вставкас флагомЗначения.

Стоимости продаж трёх видов колонок (в руб.) рассчитываются исходя из объёмов продаж и стоимостей за единицу оборудования, взятых из соответствующих ячеек на листе Задание 4.

Результат выполнения задания смотри на рис. 5.

6. С помощью мастера диаграмм для данных таблицы 3 строится диаграмма продаж Колонок SP-M06А и SP-718 (при М=0) за 6 месяцев, которая приведена на рис. 6.

7. Для прогноза продаж колонок SP-M06А используется функция ТЕНДЕНЦИЯ, колонок SP-718 –РОСТи колонок SP-330 –ПРОГРЕССИЯ. ЛистЗадание 5-6в режиме отображения формул представлен на рис. 11.

Заполнение ячеек F12:F17 функциейПРОГРЕССИЯпроизводится с помощью командыПрогрессия.Открывается одноименное окно командойЗаполнитьиз группыРедактированиевкладкиГлавная. В окнеПрогрессиявыбирается переключательАвтозаполнение.

Рис. 11. Лист Задние 5-6в режиме отображения формул (фрагмент)

По результатам табл. 3 строится диаграмма, которая приведена на рис. 7. На диаграмму, добавляются соответствующие линии тренда.

8. Чтобы рассчитать суммарную стоимость оборудования с минимальной ценой, за месяцы в которые объем продаж находился в пределах от106 до 206 (для Р=6), можно использовать функцию БДСУММ.

П

Рис. 12. Диапазон критериев

еред применением функцииБДСУММ в свободных ячейках листаЗадание 5-6необходимо создать диапазон критериев (рис. 12). В другие свободные ячейки, напримерA20 и А21, ввести поясняющий текст и функциюБДСУММ(рис. 13).

9. Для того чтобы найти месяц, в котором оборудования с ценой по условию Кпродали на самую большую сумму используется функцияБИЗВЛЕЧЬ (рис. 13)и критерий –максимальное значение столбцеСтоим. по условию К (рис. 12).

Рис. 13. Применение функций БДСУММ и БИЗВЛЕЧЬ