Задание 4. Расширенный фильтр. Промежуточные итоги.

1. Откройте таблицу, созданную в задании 9.

СОЗДАНИЕ ИНТЕРВАЛА КРИТЕРИЕВ

2. Расположите интервал критериев, начиная с ячейки А19. Для этого выделите диапазон с именами столбцов (A1: F1); скопируйте выделенный диапазон в ячейки А19: F19.

3. Отберите информацию о книгах, которые закуплены или в 1-ом квартале или имеют тематику Экономика. Порядок выполнения:

Ø в ячейку А20 занесите значение 1 кв, в ячейку В21 занесите значение Экономика (т.е. при расположении условий отбора на разных строчках формируется их связь по правилу ИЛИ);

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду Данные – Фильтр – Расширенный фильтр;

Ø в диалоговом окне Расширенный фильтр поле Исходный диапазон будет уже заполнено;

Ø в поле Диапазон условий либо наберите вручную (ввод осуществляется с использованием абсолютных адресов ячеек), либо выделите с помощью мыши диапазон A19: F21 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

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

4. Отмените действие фильтра. Для этого выполните команду Данные – Фильтр – Отобразить все.

5. Отберите информацию о книгах, которые закуплены в первом квартале и имеют тематику Экономика. Порядок выполнения:

Ø переместите значение Экономика из ячейки В21 в ячейку В20 (т.е. при расположении условий отбора на одной строчке формируется их связь по правилу И);

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду Данные – Фильтр – Расширенный фильтр;

Ø выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19: $F$20 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

6. Отберите информацию о книгах, которые закуплены в 1, 2 и 3-м кварталах. Порядок выполнения:

Ø очистите ячейку В20 (т.к. по столбцу Тематика условий отбора нет), в ячейки А21 и А22 занесите значения 2 кв и 3 кв соответственно;

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду Данные – Фильтр – Расширенный фильтр;

Ø выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19: $F$22 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

7. Отберите информацию о книгах, которые закуплены в 1-м квартале по тематике Экономика, а во 2-м квартале по тематике Компьютеры. Порядок выполнения:

Ø очистите ячейку А22, в ячейки В20 и В21 занесите значения Экономика и Компьютеры соответственно;

Ø Первая строка определяет условие отбора записей покупок книг в 1-м квартале по тематике Экономика, а вторая – во 2-м квартале по тематике Компьютеры. Условия в строчках объединены правилом И. Между собой строки объединены правилом ИЛИ;

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду Данные – Фильтр – Расширенный фильтр;

Ø выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19: $F$21 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

ПРИМЕНЕНИЕ ТЕКСТОВЫХ КРИТЕРИЕВ

1. Отберите информацию о книгах, названия которых начинаются с латинских букв. Порядок выполнения:

Ø очистите диапазон ячеек А20: В21, в ячейку С20 занесите условие < А (русская буква), т.е. отберите все названия, которые начинаются с латинской буквы;

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду Данные – Фильтр – Расширенный фильтр;

Ø выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19: $F$20 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

2. Отмените действие фильтра.

ПРИМЕНЕНИЕ ВЫЧИСЛЯЕМЫХ КРИТЕРИЕВ

3. Отберите информацию о книгах, цена которых больше средней цены всех книг. Порядок выполнения:

Ø в ячейку С25 введите формулу определения средней цены всех книг: =СРЗНАЧ(D2: D16), в ячейку В25 введите вычисляемый критерий: =D2> $C$25, где D2 – адрес первой ячейки, содержащей цену (адрес этой ячейки должен быть относительным);

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду Данные – Фильтр – Расширенный фильтр;

Ø выберите поле Диапазон условий, очистите его и введите в него диапазон $B$24: $B$25 (в диапазон ячеек, который определяет интервал критериев, обязательно включается ячейка над формулой), нажмите кнопку ОК.

КОПИРОВАНИЕ ВЫБРАННОЙ ИНФОРМАЦИИ В ДРУГОЕ МЕСТО РАБОЧЕГО ЛИСТА БЕЗ ИЗМЕНЕНИЯ КОЛИЧЕСТВА СТОЛБЦОВ

4. Отберите информацию о книгах, которые закуплены по тематике Компьютеры, и расположите отобранные записи, начиная с ячейки Н19. Порядок выполнения:

Ø очистите ячейку С20, в ячейку В20 введите слово Компьютеры;

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду Данные – Фильтр – Расширенный фильтр;

Ø включите опцию скопировать результат в другое место;

Ø выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19: $F$20 (диапазон ячеек, в котором заданы условия отбора);

Ø выберите поле Поместить результат в диапазон и введите в него диапазон $H$19: $M$19 (количество ячеек в диапазоне равно общему числу столбцов в списке), нажмите кнопку ОК.

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

КОПИРОВАНИЕ ВЫБРАННОЙ ИНФОРМАЦИИ В ДРУГОЕ МЕСТО РАБОЧЕГО ЛИСТА С ИСПОЛЬЗОВАНИЕМ ДАННЫХ ОТДЕЛЬНЫХ СТОЛБЦОВ

5. Отберите информацию о книгах, которые закуплены в 3-м квартале, расположите отобранные записи, начиная с ячейки Н19, используя информацию из столбцов Дата, Название, Стоимость. Порядок выполнения:

Ø очистите ячейку В20, в ячейку А20 введите 3 кв;

Ø очистите ячейки в диапазоне Н19: М24;

Ø в ячейку Н19 скопируйте содержимое ячейки А1 (название столбца Дата), в I19 – из ячейки С1, и в J19 – из ячейки F1;

Ø очистите ячейку С20, в ячейку В20 введите слово Компьютеры;

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду Данные – Фильтр – Расширенный фильтр;

Ø включите опцию скопировать результат в другое место;

Ø выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19: $F$20;

Ø выберите поле Поместить результат в диапазон и введите в него диапазон $H$19: $J$19, нажмите кнопку ОК.

В ячейках под именами столбцов появятся выбранные строки с информацией.

Задание 5. Поиск Решения. Составление плана выгодного производства

ПОСТАНОВКА ЗАДАЧИ

Фирма производит несколько видов продукции из одного и того же сырья – А, В и С. Реализация продукции А дает прибыль 100 р., В – 150 р. И С – 200 р. на единицу изделия. Продукцию можно производить в любых количествах, поскольку известно, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной.

ПОРЯДОК ВЫПОЛНЕНИЯ

1. Создайте расчетную таблицу как показано на рисунке. Введите исходные данные и формулы в электронную таблицу.

Расчетные формулы имеют следующий вид:

Расход_сырья_N = количество_сырья_N * норма_расхода_сырья_А +
+ количество_сырья_N * норма_расхода_сырья_В +
+ количество_сырья_N * норма_расхода_сырья_С,

значит в ячейку F5 нужно ввести формулу:

= В5 * $В$9 + С5 * $С$9 + D5 * $D$9,

а затем скопировать ее в ячейки F6 и F7.

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

Общая_прибыль_по_А = прибыль_на_ед.изделий_А * количество_А,

следовательно в ячейку В10 следует ввести формулу: = В8 * В9, а затем скопировать ее в ячейки С10 и D10.

Итоговая_общая_прибыль =
= Общая_прибыль_по_А + Общая_прибыль_по_В + Общая_прибыль_по_С,

значит в ячейку Е10 следует ввести формулу: =СУММ(В10: D10).

2. В меню Сервис активизируйте команду Поиск решения и введите параметры поиска.

В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (Е10), в качестве изменяемых ячеек – ячейки количества сырья (В9: D9), выделив их. Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:

Расход_сырья1 £ 350; Расход_сырья2 £ 200; Расход_сырья3 £ 100; а также положительные значения количества сырья А, В, С ³ 0.

Установите параметры поиска решения. Для этого кнопкой Параметры откройте диалоговое окно Параметры поиска решения, установите параметры по образцу, задайте линейную модель расчета. Кнопкой Выполнить запустите Поиск решения. Если все верно сделано, то решение будет как показано на рисунке.

Вывод. Из решения видно, что оптимальный план выпуска предусматривает изготовление 5, 56 кг продукции В и 22, 22 кг продукции С. Продукцию А производить не стоит. Полученная прибыль при этом составит 5 277, 78 рублей.