Урок в 11 классе "Решение задачи оптимизации в Excel"
план-конспект урока по информатике и икт (11 класс)

Фрагмент открытого урока по теме «Решение задачи оптимизации в Excel»

-Ребята, сегодня на уроке мы рассмотрим следующую задачу: (Слайд 1)

Задача.

Заданы координаты (X,Y) четырех населенных пунктов A(3, 8), B(10, 12), C(14, 10), D(17, 6).

Определите место строительства ЖД станции ST и развилки R, соединяющей ЖД станцию с населенными пунктами. Так чтобы затраты на строительство дорог были бы минимальны. Стоимость строительства 1 км дороги равна 10 000 ед.

-Ребята, как вы думаете, используя какие компьютерные средства, можно решить эту задачу?

-Эта задача относится к классической задаче оптимизации. Её можно решить и с помощью Excel и с помощью языков программирования. Сегодня на уроке мы решим её в Excel. (Слайд 2)

(Слайд 3) Железная дорога проходит по прямой Y = 0. Развилка имеет координаты (X0,Y0). Рассмотрим решение данной задачи. Затраты на строительство дорог будут минимальными, если протяженность всех дорог будем наименьшей. Общая длина дорог будет функцией переменных X0,Y0 ,X, где (X0,Y0) – координаты развилки R, (X, 0) – координаты станции ST.

- Как же нам найти протяженность дорог? Т.е. надо найти длину отрезка, заданного координатами своих концов.

-Как же будет выглядеть формула общей протяженности дорог? (слайд 4)

F(X0,Y0 ,X)

Таким образом, мы должны найти тройку чисел {X0, Y0, X} такую, чтобы в ней достигался минимум функции F(X0,Y0 ,X). (Слайд 5)

Ребята, садимся за компьютеры и приступаем к практической части.

Алгоритм выполнения работы:

1. Откройте файл заготовка.xlsx.
2. Заполните данными таблицу «Координаты населенных пунктов».
3. Отметьте координаты населенных пунктов на плоскости. Для этого постройте Точечную диаграмму без соединения точек.
4. Исходя из расположения населенных пунктов, предположите возможные координаты станции и развилки и занесите их в соответствующие таблицы.
5. Нанесите эти данные на диаграмму и сделайте соответствующие обозначения. Это и будет предполагаемый вами вариант расположения станции и развилки.
6. В ячейку С15 занесите формулу для вычисления протяженности дорог по вашему проекту.
7. Скопируйте эту формулу в ячейку С16, заменив относительную адресацию на абсолютную для ячеек, содержащих координаты X, Y (C8:D11) населенных пунктов, так как они не должны меняться при копировании.
8. Используя функцию Поиск решения (Данные/ Поиск решения), получите оптимальный вариант расположения станции и развилки, при котором протяженность дорог будет минимальной.
9. Сделайте расчеты в таблице «Стоимость строительных дорог».
10. Постройте Точечную диаграмму с оптимально расположенными станцией и развилкой, соедините населенные пункты с развилкой, а развилку - со станцией. Это и будет оптимальный проект.

Задачи для самостоятельной работы.

В районе расположении 4 населенных пункта. По территории района проходит железная дорога. По просьбе жителей района планируется построить ЖД станцию и проложить дороги от нее до каждого населенного пункта (без точки развилки дорог). Определите наиболее удобное расположение ЖД станции.

1. Расположение ЖД станции должно быть выбрано исходя из экономии средств (т.е. сумма длин дорог, соединяющих станцию с населенными пунктами должна быть наименьшей).
2. Расположение ЖД станции должно быть выбрано исходя из максимальной справедливости (место для станции должно быть выбрано так, чтобы наибольшее из расстояний от нее до населенных пунктов было как можно меньше.

Практическая работа «Решение задачи оптимизации»

Задача.

Заданы координаты (X,Y) четырех населенных пунктов A(3, 8), B(10, 12), C(14, 10), D(17, 6).

Определите место строительства ЖД станции ST и развилки R, соединяющей ЖД станцию с населенными пунктами. Так чтобы затраты на строительство дорог были бы минимальны. Стоимость строительства 1 км дороги равна 10 000 ед.

Алгоритм выполнения работы:

1. Откройте файл заготовка.xlsx.
2. Заполните данными таблицу «Координаты населенных пунктов».
3. Отметьте координаты населенных пунктов на плоскости. Для этого постройте Точечную диаграмму без соединения точек.
4. Исходя из расположения населенных пунктов, предположите возможные координаты станции и развилки и занесите их в соответствующие таблицы.
5. Нанесите эти данные на диаграмму и сделайте соответствующие обозначения. Это и будет предполагаемый вами вариант расположения станции и развилки.
6. В ячейку С15 занесите формулу для вычисления протяженности дорог по вашему проекту.
7. Скопируйте эту формулу в ячейку С16, заменив относительную адресацию на абсолютную для ячеек, содержащих координаты X, Y (C8:D11) населенных пунктов, так как они не должны меняться при копировании.
8. Используя функцию Поиск решения (Данные/ Поиск решения), получите оптимальный вариант расположения станции и развилки, при котором протяженность дорог будет минимальной.
9. Сделайте расчеты в таблице «Стоимость строительных дорог».
10. Постройте Точечную диаграмму с оптимально расположенными станцией и развилкой, соедините населенные пункты с развилкой, а развилку - со станцией. Это и будет оптимальный проект.

Задачи для самостоятельной работы.

В районе расположении 4 населенных пункта. По территории района проходит железная дорога. По просьбе жителей района планируется построить ЖД станцию и проложить дороги от нее до каждого населенного пункта (без точки развилки дорог). Определите наиболее удобное расположение ЖД станции.

1. Расположение ЖД станции должно быть выбрано исходя из экономии средств (т.е. сумма длин дорог, соединяющих станцию с населенными пунктами должна быть наименьшей).
2. Расположение ЖД станции должно быть выбрано исходя из максимальной справедливости (место для станции должно быть выбрано так, чтобы наибольшее из расстояний от нее до населенных пунктов было как можно меньше.