План конспект занятия Подготовка и применение электронных таблиц при решении задач оптимизации (на примере функции "Поиск решения" MS Excel)»

Тема:

«Подготовка и применение электронных таблиц при решении задач оптимизации (на примере функции "Поиск решения" MS Excel)»

Примечание

Вид занятия:

Комбинированный урок

Время

Литература:

1. Практикум по информационным технологиям в профессиональной деятельности: Учеб. пособие для сред. проф. образования / Елена Викторовна Михеева.- 2-е изд., стер.- М.: Издательский центр "Академия", 2004.- 256 с.

2. Информатика. Базовый курс. 2-е издание / Под ред. С.В. Симоновича. – СПб.: Питер,2004.-640 с.: ил.

3. Информатика и информационные технологии. Учебник для 10-11 классов / Н.Д. Угринович. – 3-е изд. – М.: БИНОМ. Лаборатория знаний, 2006.- 511 с.: ил.

4. Леонтьев В.П. Персональный компьютер. Карманный справочник. – М.: ОЛМА-ПРЕСС, 204. – 928с.: ил.

Цель:

Изучение и первичное закрепление использования технологии поиска решения для задач оптимизации.

Задачи:

Образовательные:

- изучить основы работы в надстройке "Поиск решения";

- организовать деятельность учащихся по восприятию, осмыслению и первичному закреплению новых знаний и способов деятельности по овладению основами работы в среде табличного процессора MS Excel;

- научить решать задачи с помощью "Поиска решения".

Воспитательные:

- воспитание потребности в знаниях;

- воспитать внимание, точность;

- воспитывать инициативу и самостоятельность трудовой деятельности;

- воспитывать бережное отношение к персональному компьютеру;

- рационально использовать рабочее время.

Развивающие:

- развивать у учащихся профессиональные интересы;

- развивать специальные способности;

- развивать личностные качества (аккуратности, целеустремленности, ответственности, образованности);

организовать умения вводить данные различных типов в таблицу.

Методы проведения:

- словесные: объяснительно-иллюстративное изложение.

- наглядные: демонстрация.

- практические:

Межпредмет-ные связи:

- Информатика, связана почти со всеми науками

Материал и оборудование:

Оснащение урока:

- компьютер;

- табличный процессор MS Excel;

- экран;

- проектор;

- презентация Microsoft Officе.

Ход занятия:

I

Организационная часть

Здравствуйте группа 11Д, меня зовут Алина Олеговна, сегодня занятие по информатике проводить буду я. Сейчас давайте проверим отсутствующих. А теперь перейдем к теме нашего занятия, она называется «Подготовка и применение электронных таблиц при решении задач оптимизации (на примере функции "Поиск решения" MS Excel)».

Время - 5

минуты

II

Актуализация знаний

На сегодняшнем занятии мы будем работать с табличным процессором MS Excel. Думаю, что каждый из вас работал в этой программе и знаком с ее основными функциями. Я считаю, что работать в ней достаточно сложно, ее конечно не сравнить с MS Word, в нем работать гораздо проще и интереснее. Но как быть, если нужна полноценная таблица, база данных, с использованием различных типов полей, с большим количеством информации, или необходимо решить какую-нибудь задачу, например задачу оптимизации? Для этого вам, как раз, и понадобится MS Excel. На сегодняшнем занятии мы познакомимся с табличным процессором MS Excel немного поближе и попробуем решить в нем несколько несложных задач.

Сейчас я попрошу вас ответить на несколько вопросов:

-Что же вообще такое электронная таблица?

Электронная таблица – это компьютерный эквивалент обычной таблицы, состоящей из строк и столбцов, на пересечении которых располагаются клетки, содержащие числовую информацию, рисунки, текст.

- Какие элементы встречаются в окне программы MS Excel?

Окно программы Excel содержит:

- Адрес ячейки - уникальный адрес, состоящий из буквы столбца и номера строки.

- Строка формул – служит для ввода и редактирования формул и функций

-Указатель ячейки - светящийся прямоугольник, определяющий текущую ячейку.

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

- А как вы думаете, для чего нужна специальная программа, работающая с электронными таблицами?

Она представляет новые возможности, работы с таблицами.

Время - 7

минут

III

Мотивация предстоящей деятельности

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

Время –2 минута

IV

Лекционная часть. Изложение материала

Какие же возможности представляет программа MS Excel при решении задач оптимизации?

Большинство задач, решаемых с помощью электронной таблицы, предполагают нахождение искомого результата по известным исходным данным. Но в Excel есть инструменты, позволяющие решить и обратную задачу: подобрать исходные данные для получения желаемого результата. Одним из таких инструментов является «Поиск решения», который особенно удобен для решения так называемых "задач оптимизации".

Для начала давайте узнаем, что же такое «Задача оптимизации».

Задача оптимизации – это математическая модель определенного процесса производства продукции, его распределение, хранение, переработки, транспортирования, покупки или продажи, выполнение комплекса сервисных услуг и т.д. Это обычная математическая задача типа: Дано/Найти/При условии,  но которая имеет  множество возможных решений. Таким образом, задача оптимизации – задача выбора из множества возможных вариантов наилучшего, оптимального. 
Решение такой задачи называют планом или программой, например, говорят – план производства или программа реконструкции. Другими словами, это те неизвестные, которые нам надо найти, например,  количество продукции которое даст максимальную прибыль. Задача оптимизации – это поиск экстремума, то есть, максимального или минимального значения определенной функции, которую называют целевой функцией, например, это может быть функция прибыли – выручка минус затраты. Так как и всё в мире ограничено (время, деньги, природные и человеческие ресурсы), в задачах оптимизации всегда есть определенные ограничения, например, количество метала, рабочих и станков на предприятии по изготовлению деталей.

Решать такие задачи, лучше всего, с помощью Надстройки "Поиск решения" в MS Excel.

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

Мощным средством анализа данных Excel является надстройка Solver (Поиск решения). С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета.

Итак, как вы поняли, что же такое процедура «Поиск решения»?Давайте запишем.

Процедура «Поиск решения» (MS Excel) представляет собой мощный инструмент для выполнения сложных вычислений. Она позволяет находить значения переменных, удовлетворяющих указанным критериям оптимальности, при условии выполнения заданных ограничений.

Размер задачи, которую можно решить с помощью базовой версии программы "Поиск решения", ограничивается такими предельными показателями:

• количество неизвестных (decision variable) – 200;

• количество формульных ограничений (explicit constraint) на неизвестные – 100;

• количество предельных условий (simple constraint) на неизвестные – 400.

Рассмотрим основные понятия темы и запишем их:

- Целевая ячейка — ячейка на рабочем листе с таблицей исходных данных, куда занесена формула целевой функции. 

- Изменяемые ячейки — ячейки из таблицы исходных данных, отражающие значения переменных, которые необходимо найти в результате оптимизации. Ячейки не должны содержать формулы, их значения должны влиять на значение целевой ячейки. 

Модели всех задач на оптимизацию состоят из следующих элементов (запишем их):

1. Переменные - неизвестные величины, которые нужно найти при решении задачи.

2. Целевая функция - величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели.

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

Под ограничениями понимают соотношения вида А1B1, A1=A2, A1=0, по крайней мере одна из ячеек в соотношении должна зависеть от переменных, в противном случае это соотношение не влияет на решение задачи.

Очень важно правильно сформулировать ограничения. Это является самой ответственной частью постановки задачи.

Ограничения имеют тот же синтаксис, что и формулы, но воспринимаются надстройкой иначе: формулы выполняются точно, а ограничения с некоторой погрешностью.

Благодаря мировой популярности табличного процессора MS Excel, встроенная в его среду программа Solver является наиболее распространенным инструментом для поиска оптимальных решений в различных сферах. Например с ее помощью можно вычислить сколько можно выпустить определенного товара при ограничении сырья или как составить штатное расписание для достижения лучшего результата и при наименьших расходах. Как минимизировать затраты перевозки, как составить смесь заданного качества при наименьших затратах.

Теперь я попрошу вас пересесть за компьютеры и мы вместе попробуем решить одну из таких задач.

По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

В Excel 2003 и ниже выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да.

Задание 1. Задать связи между файлами.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

2. Создайте таблицу "Отчет о продажах 1 квартал" по образцу. Введите исходные данные (Доходы и расходы).

Доходы = 234,58р.;

Расходы = 75,33р.

и проведите расчет Прибыли: Прибыль = Доходы - Расходы. Сохраните файл под именем "1 квартал".

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

Доходы = 452,6р.;

Расходы = 185,8р.

Обратите внимание как изменился расчет прибыли. Сохраните файл под именем "2 квартал".

4. Создайте таблицу "Отчет о продажах за полугодие" по образцу, в виде нового файла. Для этого создайте новый документ (Файл\Создать)и скопируйте таблицу отчета о продаже за первый квартал, после чего подправьте заголовок таблицы и в колонке В удалите все значения исходных данных и результаты расчетов. Сохраните файл под именем "Полугодие".

5. Для расчета полугодовых итогов свяжите формулами файлы "1 квартал" и "2 квартал".

Для связи формулами файлов Excel выполните действия:

• откройте эти файлы (все три файла);

• начните ввод формулы в файле-клиенте (в файле "Полугодие" введите формулу для расчета "Доход за полугодие").

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

Доход за полугодие = Доход за 1 квартал + Доход за 2 квартал.

Чтобы вставить в формула адрес ячейки или диапазона ячеек из другого файла (файла-источника), щелкните мышью по этим ячейкам, при этом расположите окна файлов на экране так, чтобы они не перекрывали друг друга.

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

В ячейке В3 файла "Полугодие" формула для расчета полугодового дохода имеет следующий вид:

= '[1 квартал.xls]Лист1'!$B$3+'[2квартал.xls]Лист1'!$B$3.

Аналогично рассчитайте полугодовые значения Расходов и Прибыли, используя данные файлов "1 квартал" и "2 квартал". Сохраните текущие результаты расчетов.

Если файл-источник закрыт, в формуле, которая на него ссылается, будет указан весь путь для этого файла.

Время – 27 минут

Проверка первичного понимания

Теперь Вы можете ответить на вопросы.

1. Что такое задача оптимизации?

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

2. Что такое процедура "Поиск решения"?

- эта процедура представляет собой мощный инструмент для выполнения сложных вычислений.

3. Укажите типы задач, для которых применяется оптимизация.

- как минимизировать затраты перевозки, как составить смесь заданного качества при наименьших затратах.

4. Укажите этапы решения задач оптимизации в табличном процессоре Microsoft Excel.

- для решения задач оптимизации нужно указать целевую ячейку, изменяющиеся ячейки и поставить ограничения.

5. Какие действия необходимо выполнить для подключения надстройки «Поиск решения».

- для включения надстройки «Поиск решения» нужно выбрать Праметры Excel\Надстройки потом в окне управление выбрать надстройки Excel и выбрать Перейти, далее поставит галочку рядом с «Поиск решения» и нажать ок.

Время – 7 минут

V

Практическая работа за компьютером

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

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

Время - 27 минут

VI

Выявление обратной связи

У вас есть ко мне какие-нибудь вопросы?

Какие возникли проблемы при выполнении задания?

Как вы исправляли возникающие ошибки?

Время – 5 минут

VII

Домашнее задание

Попробуйте дома, самостоятельно продумать ход решения данной задачи и решить ее:

Издательский дом "Геоцентр-Медиа" издаст два журнала: "Автомеханика" и "Инструмент", которые печатаются в трех типографиях: "Алмаз-Пресс", "Карелия-Принт" и "Hansaprint"(Финляндия), где общее количество часов, отведенное для печати и производительность печати одной тысячи экземпляров ограничены и представлены в таблице (рис.1). Спрос на журнал "Автомеханик" составляет 12 тысяч экземпляров, а на журнал "Инструмент" - не более 7,5 тысяч в месяц. Определите оптимальное количество издаваемых журналов, которое обеспечит максимальную выручку от продажи.

Время – 3 минуты

VIII

Итог занятия

Подводятся итоги занятия, выставляются оценки за выполненную работу.

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

Время – 5 минут