Решение прикладных задач в Excel

 Использование программы «Поиск решения»

И. Л. СОБОЛЕВА

МОУ «ТВЕРСКОЙ ЛИЦЕЙ»

РЕШЕНИЕ ПРИКЛАДНЫХ ЗАДАЧ В EXCEL

В Тверском лицее изучение Информационных технологий осуществляется в течение нескольких лет. По программе на эту тему отводится в 8 и 9 классе всего 12 часов, в 10 физико-математическом классе – еще 12 часов. На средней ступени изучаются основные возможности электронных таблиц, а именно, основные типы и форматы данных, относительные, абсолютные и смешанные ссылки, использование формул, встроенные функции, построение диаграмм различных типов. Все эти вопросы изучаются на простых доступных примерах. На старшей ступени в физико-математическом классе наряду с повторением пройденного материала изучаются и другие возможности программы Excel, например, использование сложных (вложенных) функций, в том числе логических.

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

Решение таких задач на уроках информатики позволяет осуществлять основные принципы педагогической деятельности:

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

2. Знакомство и изучение научной литературы по соответствующей тематике.

3. Изучение накопленного педагогического опыта.

4. Ведущая роль теоретических знаний в содержании обучения.

5. Изучение требований ВУЗов по предмету.

6. Проблемный подход, направленный на формирование творческого отношения к решению задач.

7. Связь обучения с практикой.

8. Использование межпредметных связей при изучении темы.

9. Систематичность и последовательность преподавания и усвоения знаний.

10. Доступность.

11. Наглядность.

12. Индивидуализация и дифференциация обучения.

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

14. Прочность усвоения знаний.

15. Развитие навыков самостоятельной работы.

16. Формирование творческого отношения к учебе и работе.

Использование программы «Поиск решения»

Программа «Поиск решения» позволяет получить результат на основе изменения значения нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия – ввести ограничения. Эти возможности позволяют использовать программу Excel для решения системы уравнений и уравнений, при решении которых необходимо учитывать область допустимых значений, для нахождения точек, в которых достигается максимум или минимум значения целевой функции нескольких переменных, определенных на множестве с линейными и нелинейными ограничениями. Другими словами – находить оптимальное решение задачи с ограничениями.

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

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

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

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

Поиск решения такой модели рассмотрим на примерах.

Пример 1. Решить уравнение 5x - 8lnx = 8

Для решения данного уравнения с помощью инструмента «Поиск решения» нужно:

1. В ячейку C7 занести значение x=0,0000001(исходное значение параметра). Присвоить этой ячейке имя X.

2. В ячейку B7 записать формулу
   =5*X-8*LN(X) (Рис. 1). Рис.1

3. В меню Сервис выбрать пункт Поиск решения… Если этого пункта в меню нет, то его следует загрузить, выполнив команду меню Сервис - Надстройки. В открывшемся диалоговом окне следует поставить флажок Поиск решения. В результате выполнения этой команды появится окно  Поиск решения.

4. В поле Установить целевую ячейку указать ссылку на ячейку с целевой функцией, значение которой необходимо оптимизировать, т. е. $B$7 (Рис.2). При поиске решения целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с ячейками с изменяемыми значениями. Рис.2

5. В поле Изменяя ячейки: указать ячейки, отведенные под переменные целевой функции – это ячейка X, ее адрес $C$7.

6. Кнопка Удалить удаляет ограничение из списка, а кнопка Редактировать дает возможность вносить изменения.

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

8. В списке Ограничения: указать дополнительные условия, которые необходимо учитывать при поиске решения. Ограничения вводятся с помощью кнопки Добавить (Рис.3).

Рис.3 Рис.4

1. В ячейке параметр (X) получим решение данного уравнения (Рис.4).

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

Пример 2. Решить систему уравнений x₁ + 5x₂ + x₃ = -7

2x₁ – x₂ – x₃ = 0

x₁ – 2x₂ – x₃ = 2

Для поиска корней системы уравнений также следует воспользоваться инструментом Поиск решения (Рис.5).

)

Рис.5 Рис.6

Результаты решения системы уравнений появятся в соответствующих ячейках (Рис.6).

Кроме того, зачастую нас интересует не конкретный результат, а минимально или максимально возможный. В таких задачах при поиске решения также накладываются дополнительные условия. Такие задачи в Excel решают с помощью инструмента «Поиск решения».

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

СПИСОК ЛИТЕРТУРЫ

1. Безручко В.Т. Практикум по курсу «Информатика». Работа в Windows, Word, Excel: Учеб. пособие. - М.: Финансы и статистика, 2002.

2. Информатика. 9 класс. Простейшие статистические характеристики. Начальные сведения из теории вероятностей. Решение прикладных (экономических) задач в Excel: сборник элективных курсов / авт.-сост. А.А.Чернов. - Волгоград: Учитель, 2006.

5