Конспект урока «Excel (транспортные задачи)» по информатике для 11 класса
Муниципальное общеобразовательное учреждение
«Средняя общеобразовательная школа № 93»
Новокузнецкого района Кемеровской области
Методическая разработка
по информатике в 11 классе
«Excel (транспортные задачи)»
Подготовил:
учитель информатики
Ожигова Ольга Сергеевна
Новокузнецк 2010
Пояснительная записка
Тема: «Excel (транспортные задачи)»
Цели:
-
образовательная: научить решать транспортные задачи в электронных таблицах Microsoft Office Excel;
-
воспитательная: вызвать интерес к учебному материалу, способствовать укреплению привычки внимательно и аккуратно выполнять учебную работу;
-
развивающая: способствовать развитию устной и письменной речи учащихся.
Контингент: 11 класс, профильный уровень.
Данная методическая разработка является частью общего курса информатики средней общеобразовательной школы. Эта работа посвящена рассмотрению транспортных задач в электронных таблицах Microsoft Office Excel и, в общей сложности, рассчитана на 4-5 часов. Она включает в себя теоретические основы по данной теме, фронтальную лабораторную работу, индивидуальную лабораторную работу, а также контрольную работу.
Требования к ЗУНам до изучения темы: основные понятия ЭТ Microsoft Office Excel и навыки работы с ними.
Требования к ЗУНам после изучения темы: учащиеся должны уметь решать транспортные задачи, используя Microsoft Office Excel.
Требования к программному и аппаратному обеспечению
В процессе изучения темы потребуется компьютерный класс, оснащённый компьютерами на базе процессоров от Pentium II, имеющих не менее 64 Мб ОЗУ и частотой 200 МГц и выше, на которых установлено следующее программное обеспечение: операционная система Microsoft Windows XP Professional версия 2002 и выше.
Тематическое планирование
Тема урока | Тип урока | Кол-во часов | |
1 | Excel (транспортные задачи) | Лекция | 1 |
2 | Фронтальная лабораторная работа | 2 | |
3 | Индивидуальная лабораторная работа | 1 | |
4 | Контрольная работа | 1 |
Лекционный материал
Рассмотрим следующую транспортную задачу.
Задача: Для строительства четырех объектов используется кирпич, изготавливаемый на трех заводах. Ежедневно каждый из заводов может изготовить 100, 150 и 50 условных единиц кирпича (предложение поставщиков). Потребности в кирпиче на каждом из строящихся объектов ежедневно составляют 75, 80, 60 и 85 условных единиц (спрос потребителей). Тарифы перевозок одной условной единицы кирпича с каждого из заводов к каждому из строящихся объектов задаются матрицей транспортных расходов С.
С =
Требуется составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок будет минимальной.
Для решения транспортной задачи на персональном компьютере с использованием EXCEL необходимо:
-
Ввести исходные данные в ячейки рабочего листа EXCEL;
-
Разметить блоки ячеек на рабочем листе EXCEL, необходимые для моделирования объемов перевозок, а также для формирования элементов математической модели и целевой функции;
-
Сформировать на рабочем листе EXCEL элементы математической модели и целевую функцию;
-
Настроить программу " Поиск решения" и выполнить ее.
1. Ввод исходных данных
Исходными данными для решения транспортной задачи являются:
- матрица транспортных расходов; | |
| - предложение поставщиков; |
| - спрос потребителей; |
Напомним, что для ввода данного в ячейку рабочего листа EXCEL необходимо:
-
Селектировать ячейку;
-
Набрать вводимое данное на клавиатуре;
-
Нажать клавишу Enter.
Для наглядности блоки ячеек с введенными данными желательно обвести рамками.
Рабочий лист EXCEL с введенными исходными данными для решения транспортной задачи показан на рис 1.
A | B | C | D | E | F | G | H | I | |
1 | | | | | | | | | |
2 | | | | | | | | | |
3 | | | Матрица транспортных расходов | | | Предложение | |||
4 | | | | | | | | | поставщиков |
5 | | | | | | | | | |
6 | | | 6 | 7 | 3 | 5 | | | 100 |
7 | | | 1 | 2 | 5 | 6 | | | 150 |
8 | | | 8 | 10 | 20 | 1 | | | 50 |
9 | | | | | | | | | |
10 | Спрос потребителей | 75 | 80 | 60 | 85 | | | | |
11 | | | | | | | | | |
12 | | | | | | | | | |
13 | | | | | | | | | |
14 | | | | | | | | | |
15 | | | | | | | | | |
16 | | | | | | | | | |
17 | | | | | | | | | |
рис 1.
2. Разметка блоков ячеек рабочего листа EXCEL
Кроме исходных данных на рабочем листе EXCEL для решения транспортной задачи необходимо предусмотреть:
1.Блок ячеек "Матрица перевозок", в котором будут моделироваться объемы перевозок;
2.Блок ячеек "Фактически реализовано", в котором будет моделироваться фактическая реализация продукции;
3.Блок ячеек "Фактически получено", в котором будет моделироваться фактическое удовлетворение спроса;
4.Блок ячеек "Транспортные расходы по потребителям", в котором будут подсчитываться транспортные расходы по каждому потребителю;
5.Ячейку "Итого расходы", в которой будут моделироваться итоговые транспортные расходы по всем потребителям (целевая ячейка).
Для наглядности указанные блоки ячеек целесообразно обвести рамками. Рабочий лист EXCEL с размеченными блоками ячеек показан на рис.2.
Теперь в этих блоках ячеек можно формировать элементы математической модели и целевую функцию.
A | B | C | D | E | F | G | H | I | |
1 | | | | | | | | | |
2 | | | | | | | | | |
3 | | | Матрица транспортных расходов | | | Предложение | |||
4 | | | | | | | | | поставщиков |
5 | | | | | | | | | |
6 | | | 6 | 7 | 3 | 5 | | | 100 |
7 | | | 1 | 2 | 5 | 6 | | | 150 |
8 | | | 8 | 10 | 20 | 1 | | | 50 |
9 | | | | | | | | | |
10 | Спрос потребителей | 75 | 80 | 60 | 85 | | | | |
11 | | | | | | | | | |
12 | | | | Матрица перевозок | | | | | |
13 | | | Потреб.1 | Потреб.2 | Потреб.3 | Потреб.4 | | | |
14 | | поставщик 1 | | | | | | | |
15 | | поставщик 2 | | | | | | | |
16 | | поставщик 3 | | | | | | | |
17 | | | | | | | | | |
18 | Фактически получено | | | | | | | | |
19 | | | | | | | | | |
20 | Транспортные расходы | | | | | | | расходы | |
21 | по потребителям | | | | | | Итого | | |
22 | | | | | | | | | |
23 | | | | | | | | | |
Рис. 2.
3. Формирование элементов математической модели
Элементами математической модели транспортной задачи являются следующие суммы:
ij - фактически реализовано i-ым поставщиком ;
, - фактически получено j-ым потребителями ;
Для нашей задачи m=3, n=4.
Рассмотрим процесс формирования этих сумм на рабочем листе EXCEL.
Вначале сформируем , в блоке "Фактически реализовано".
1.Заполните ячейки блока "Матрица перевозок" (С14:F16) числом 0,01.
2.Селектируйте первую ячейку блока "Фактически реализовано" (ячейка I14);
3.Наведите курсор на кнопку - автосуммирование и щелкните левой клавишей мыши;
4.Нажмите клавишу Delete;
5.Селектируйте первую строку блока "Матрица перевозок" (строка С14:F14);
6.Нажмите клавишу Enter;
7.Скопируйте формулу = СУММ (С14:F14) из первой ячейки блока "Фактически реализовано" на все остальные ячейки этого блока.
Сформируем теперь ,- в блоке "Фактически получено".
Для этого выполните следующие действия:
1.Селектируйте первую ячейку блока "Фактически получено" (ячейка С18);
2.Наведите курсор на кнопку - автосуммирование и щелкните левой клавишей мыши;
3.Нажмите клавишу Delete;
4.Селектируйте первый столбец блока "Матрица перевозок" (Столбец С14:C16);
5.Нажмите клавишу Enter;
6.Скопируйте формулу = CУММ (С14:С16) из первой ячейки блока "Фактически получено" на остальные ячейки этого блока.
4. Формирование целевой функции
Для формирования целевой функции введем вначале формулы, отражающие транспортные расходы по каждому потребителю, т.е. формулы:
ijCij в ячейки блока “Транспортные расходы по потребителям”
Для ввода этих формул выполните следующие действия:
1.Селектируйте первую ячейку блока “Транспортные расходы по потребителям” (ячейка С21);
2.Наведите курсор на кнопку - автосуммирование и щелкните левой клавишей мыши;
3.Нажмите клавишу “Delete ”;
4.Селектируйте первый столбец блока “Матрица Транспортных расходов” (столбец С6:С8);
5.Нажмите клавишу *;
6.Селектируйте первый столбец блока “Матрица перевозок” (столбец С14:С16);
7.Активируйте строку формул, наведя на неё курсор и щелкнув затем левой клавишей мыши;
8.Нажмите одновременно три клавиши: “CTRL”+“SHIFT”+“ENTER”;
9.Скопируйте формулу {=СУММ (С6:С8*С14:С16)} в остальные ячейки блока “Транспортные расходы по потребителям”;
Сформируем теперь целевую функцию транспортной задачи, выражаемую формулой ijXij, в ячейку “Итого расходы”. Для этого:
Селектируйте ячейку “Итого расходы” (ячейка I21);
1. Наведите курсор на кнопку - автосуммирование и щелкните левой клавишей мыши;
2. Нажмите клавишу “Delete”;
3. Селектируйте блок ячеек “Транспортные расходы по потребителям (С21:F21);
4. Нажмите клавишу “Enter”;
После формирования элементов математической модели и целевой функции транспортной задачи рабочий лист EXСEL примет вид, показанный на рис. 3.
Теперь можно приступить к настройке программы “Поиск решения”.
A | B | C | D | E | F | G | H | I | |
1 | | | | | | | | | |
2 | | | | | | | | | |
3 | | | Матрица транспортных расходов | | | Предложение | |||
4 | | | | | | | | | поставщиков |
5 | | | | | | | | | |
6 | | | 6 | 7 | 3 | 5 | | | 100 |
7 | | | 1 | 2 | 5 | 6 | | | 150 |
8 | | | 8 | 10 | 20 | 1 | | | 50 |
9 | | | | | | | | | |
10 | Спрос потребителей | 75 | 80 | 60 | 85 | | | | |
11 | | | | | | | | | |
12 | | | | Матрица перевозок | | | | | |
13 | | | Потреб.1 | Потреб.2 | Потреб.3 | Потреб.4 | | | |
14 | | поставщик 1 | 0,01 | 0,01 | 0,01 | 0,01 | | | 0,04 |
15 | | поставщик 2 | 0,01 | 0,01 | 0,01 | 0,01 | | | 0,04 |
16 | | поставщик 3 | 0,01 | 0,01 | 0,01 | 0,01 | | | 0,04 |
17 | | | | | | | | | |
18 | Фактически получено | 0,03 | 0,03 | 0,03 | 0,03 | | | | |
19 | | | | | | | | | |
20 | Транспортные расходы | | | | | | | расходы | |
21 | по потребителям | 0,15 | 0,19 | 0,28 | 0,12 | | Итого | 0,74 | |
22 | | | | | | | | | |
23 | | | | | | | | | |
Рис. 3.
5. Настройка программы Поиск решения
Для настройки программы “Поиск решения” на решение транспортной задачи выполните следующие действия:
1.Селектируйте целевую ячейку “Итого расходы” (ячейка I21);
2.Установите курсор в строке главного меню на пункте “Сервис” и щелкните левой клавишей мыши;
3.Установите курсор на пункт "Поиск решения" меню "Сервис", щелкните левой клавишей мыши и убедитесь, что в поле “Установить целевую ячейку” окна диалога программы “Поиск решения” указана ячейка $I$21 (см. рис. 4)
Рис.4.
4.Установите курсор на переключатель “Равной Минимальному значению” и щелкните левой клавишей мыши;
5.Установите курсор в поле “Изменяя ячейки” и щелкните левой клавишей мыши;
6.Селектируйте блок ячеек “Матрица первозок” (блок С14:F16);
7.Установите курсор на кнопку “Добавить” и щелкните левой клавишей мыши;
Появившееся окно диалога команды “Добавление ограничения” показано на рис.5.
Рис.5.
8.Селектируйте блок ячеек “Фактически реализовано” (блок I14:I16);
9. Убедитесь, что оператор сравнения
10.Установите курсор на поле “Ограничение”и щелкните левой клавишей мыши;
11.Селектируйте блок ячеек “Предложение поставщиков” (блок I6:I8) и убедитесь, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рис.6.
Рис.6.
12.Установить курсор на кнопку “Добавить” и щелкните левой клавишей мыши;
13.Селектируйте блок ячеек “Фактически получено” (блок С18:F18);
14.Установите курсор на стрелку прокрутки значений оператора сравнения и щелкните левой клавишей мыши;
15.Установите курсор на значение >= (больше или равно) и щелкните левой клавишей мыши;
16.Установите курсор на поле “Ограничение” и щелкните левой клавишей мыши;
17.Селектируйте блок ячеек “Спрос потребителей” (блок С10:F10) и убедитесь, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рис.7.
Рис.7.
18.Установите курсор на кнопку “Добавить” и щелкните левой клавишей мыши;
19.Селектируйте блок ячеек “Матрица перевозок” (блок С14:F16);
20.Установите курсор на стрелку прокрутки значений оператора сравнения и щелкните левой клавишей мыши;
21.Установите курсор на значение >= (больше или равно) и щелкните левой клавишей мыши;
22.Установите курсор на поле “Ограничение” и щелкните левой клавишей мыши;
23.Наберите на клавиатуре цифру 0 и убедитесь, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рис.8.
Рис. 8.
24.Установите курсор на кнопку “Добавить” и щелкните левой клавишей мыши;
25.Установите курсор на кнопку "Отмена" и щелкните левой клавишей мыши;
26.Установите курсор на кнопку “Параметры” и щелкните клавишей мыши;
27.В появившемся окне диалога “Параметры поиска решения” (см. рис.9), установите курсор на флажок “Линейная модель” и щелкните левой клавишей мыши;
28.Установите курсор на кнопку “ОК” о щелкните левой клавишей мыши;
29.В появившемся окне "Поиск решения" установите курсор на кнопку "Выполнить" и щелкните левой клавишей мыши.
Рис. 9.
30.Убедитесь, что на рабочем листе EXCEL в блоке "Матрица перевозок" появляется решение транспортной задачи, показанное на рис.10.
A | B | C | D | E | F | G | H | I | |
1 | | | | | | | | | |
2 | п | | | | | | | | |
3 | | | Матрица транспортных расходов | | | Предложение | |||
4 | | | | | | | | | поставщиков |
5 | | | | | | | | | |
6 | | | 6 | 7 | 3 | 5 | | | 100 |
7 | | | 1 | 2 | 5 | 6 | | | 150 |
8 | | | 8 | 10 | 20 | 1 | | | 50 |
9 | | | | | | | | | |
10 | Спрос потребителей | 75 | 80 | 60 | 85 | | | | |
11 | | | | | | | | | |
12 | | | | Матрица перевозок | | | | | |
13 | | | Потреб.1 | Потреб.2 | Потреб.3 | Потреб.4 | | | |
14 | | поставщик 1 | 0 | 5 | 60 | 35 | | | 100 |
15 | | поставщик 2 | 75 | 75 | 0 | 0 | | | 150 |
16 | | поставщик 3 | 0 | 0 | 0 | 50 | | | 50 |
17 | | | | | | | | | |
18 | Фактически получено | 75 | 80 | 60 | 85 | | | | |
19 | | | | | | | | | |
20 | Транспортные расходы | | | | | | | расходы | |
21 | по потребителям | 75 | 185 | 180 | 225 | | Итого | 665 | |
22 | | | | | | | | | |
23 | | | | | | | | | |
Рис. 10.
Фронтальная лабораторная работа
Решить задачу: Имеется три поставщика и четыре потребителя. Мощности поставщиков, спросы потребителей и затраты на перевозку единицы груза от поставщика к потребителю представлены в виде матрицы
20 | 110 | 40 | 110 | |
60 | 6 | 5 | 7 | 3 |
120 | 8 | 10 | 9 | 5 |
100 | 3 | 2 | 20 | 1 |
Требуется составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок будет минимальной.
Индивидуальная лабораторная работа по вариантам.
Цель: проверить уровень усвоения знаний учащихся по новой теме; выявить с целью устранения пробелов в знаниях.
Ход урока:
Самостоятельная работа проводится с целью: выявить пробелы в знаниях учащихся по теме изученной теме.
Ученикам предлагаются карточки с практическими заданиями двух вариантов.
Вариант 1.
Решить задачу: Имеется три поставщика и четыре потребителя. Мощности поставщиков, спросы потребителей и затраты на перевозку единицы груза от поставщика к потребителю представлены в виде матрицы
9 | 20 | 16 | 25 | |
25 | 3 | 1 | 5 | 2 |
25 | 4 | 6 | 7 | 3 |
15 | 2 | 8 | 4 | 5 |
Требуется составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок будет минимальной.
Вариант 2.
Решить задачу: Имеется три поставщика и четыре потребителя. Мощности поставщиков, спросы потребителей и затраты на перевозку единицы груза от поставщика к потребителю представлены в виде матрицы
45 | 50 | 35 | 17 | |
85 | 6 | 7 | 2 | 4 |
90 | 2 | 9 | 8 | 1 |
70 | 3 | 5 | 7 | 9 |
Требуется составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок будет минимальной.
Контрольная работа
Цель: проконтролировать степень усвоения обязательных знаний по изученной теме.
Ход урока:
Учитель раздает карточки с заданиями 2х вариантов. Контрольная работа рассчитана на 40 минут.
После выполнения контрольной работы ученики показывают работу с выполненной контрольной работой учителю.
Вариант 1
Решить транспортную задачу: Имеется три поставщика и три потребителя. Мощности поставщиков, спросы потребителей и затраты на перевозку единицы груза от поставщика к потребителю представлены в виде матриц:
Ai = (100;150;200) Cij =
Bj= (75;125;50;100)
Вариант 2
Решить транспортную задачу: Имеется три поставщика и три потребителя. Мощности поставщиков, спросы потребителей и затраты на перевозку единицы груза от поставщика к потребителю представлены в виде матриц:
Ai = (20;50;70) Cij =
Bj= (10;20;30;80)
Контрольная работа оценивается по следующим критериям:
-
Если задание выполнено полностью правильно без поправок, то ставится оценка «5».
-
Если выполнена большая часть задания и имеется не более двух поправок то ставится оценка «4».
-
Если задача решена на половину, то ставится оценка «3».
-
Во всех остальных случаях работа считается не принятой.
Список использованной литературы
-
Валеева, Ю.И. Экономические расчеты в Excel. Учебное пособие / Ю.И. Валеева, М.С. Можаров – Новокузнецк: Изд-во КузГПА, 2007. – 142с.
-
Угринович, Н.Д. Информатика и ИКТ. Профильный уровень: учебник для 11 класса / Н.Д. Угринович – М.: БИНОМ. Лаборатория знаний, 2009. – 478с.
Здесь представлен конспект к уроку на тему «Excel (транспортные задачи)», который Вы можете бесплатно скачать на нашем сайте. Предмет конспекта: Информатика (11 класс). Также здесь Вы можете найти дополнительные учебные материалы и презентации по данной теме, используя которые, Вы сможете еще больше заинтересовать аудиторию и преподнести еще больше полезной информации.