- Excel (транспортные задачи)

Конспект урока «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 необходимо:

  1. Ввести исходные данные в ячейки рабочего листа EXCEL;

  2. Разметить блоки ячеек на рабочем листе EXCEL, необходимые для моделирования объемов перевозок, а также для формирования элементов математической модели и целевой функции;

  3. Сформировать на рабочем листе EXCEL элементы математической модели и целевую функцию;

  4. Настроить программу " Поиск решения" и выполнить ее.

1. Ввод исходных данных

Исходными данными для решения транспортной задачи являются:

- матрица транспортных расходов;

- предложение поставщиков;

- спрос потребителей;

Напомним, что для ввода данного в ячейку рабочего листа EXCEL необходимо:

  1. Селектировать ячейку;

  2. Набрать вводимое данное на клавиатуре;

  3. Нажать клавишу 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.


Фронтальная лабораторная работа

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

Аi Bj

20

110

40

110

60

6

5

7

3

120

8

10

9

5

100

3

2

20

1

Требуется составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок будет минимальной.


Индивидуальная лабораторная работа по вариантам.

Цель: проверить уровень усвоения знаний учащихся по новой теме; выявить с целью устранения пробелов в знаниях.

Ход урока:

Самостоятельная работа проводится с целью: выявить пробелы в знаниях учащихся по теме изученной теме.

Ученикам предлагаются карточки с практическими заданиями двух вариантов.

Вариант 1.

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

Аi Bj

9

20

16

25

25

3

1

5

2

25

4

6

7

3

15

2

8

4

5

Требуется составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок будет минимальной.

Вариант 2.

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

Аi Bj

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».

  • Во всех остальных случаях работа считается не принятой.


Список использованной литературы


  1. Валеева, Ю.И. Экономические расчеты в Excel. Учебное пособие / Ю.И. Валеева, М.С. Можаров – Новокузнецк: Изд-во КузГПА, 2007. – 142с.

  2. Угринович, Н.Д. Информатика и ИКТ. Профильный уровень: учебник для 11 класса / Н.Д. Угринович – М.: БИНОМ. Лаборатория знаний, 2009. – 478с.


Здесь представлен конспект к уроку на тему «Excel (транспортные задачи)», который Вы можете бесплатно скачать на нашем сайте. Предмет конспекта: Информатика (11 класс). Также здесь Вы можете найти дополнительные учебные материалы и презентации по данной теме, используя которые, Вы сможете еще больше заинтересовать аудиторию и преподнести еще больше полезной информации.

Список похожих конспектов

Решение задачи оптимального планирования в MS Excel

Решение задачи оптимального планирования в MS Excel

МБОУ «Учхозская средняя общеобразовательная школа» Краснослободского муниципального района Республики Мордовия. Конспект урока по информатике в ...
Построение диаграмм и графиков на основе электронных таблицах Excel

Построение диаграмм и графиков на основе электронных таблицах Excel

ПЛАН-КОНСПЕКТ УРОКА. . . . ФИО (полностью). . Карцев Александр Викторович. . . . . Место работы. . ОГБОУ СПО Ульяновский ...
ПОСТРОЕНИЕ ДИАГРАММ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ MS EXCEL

ПОСТРОЕНИЕ ДИАГРАММ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ MS EXCEL

МБОУ Покровская средняя общеобразовательная. школа №1 с УИОП. Кузьмина Светлана Егоровна. . учитель ...
MS Excel

MS Excel

. Конспект интегрированного урока. (экономика + информатика). Обобщение и контроль знаний по разделу «Система кредитования в ...
Фильтрация списка в MS Excel

Фильтрация списка в MS Excel

. Жаманова Мадина Мухтархановна. г. Семей. Средняя общеобразовательная школа №43. Учитель информатики. Разработка открытого урока. ...
Создание кроссворда, используя программу Microsoft Excel

Создание кроссворда, используя программу Microsoft Excel

Конспект урока на тему. «Создание кроссворда, используя программу. Microsoft. . Excel. ». Автор: Карабанова Елена Александровна. Учитель географии ...
Применение формул для вычислений в табличном процессоре MS Excel

Применение формул для вычислений в табличном процессоре MS Excel

Кобец Оксана Александровна. Учитель информатики. ГУ «Школа – гимназия № 10 города Рудного». Урок «Применение формул для вычислений в табличном ...
Вычислительные таблицы. Расчеты в Excel

Вычислительные таблицы. Расчеты в Excel

Вычислительные таблицы. Расчеты в Excel. . . 7-й класс. . Цели:. . . систематизировать и обобщить сведения, полученные учащимися на предыдущем ...
Графики математических функций в Microsoft Excel 2007

Графики математических функций в Microsoft Excel 2007

Графики математических функций в Microsoft Excel 2007. Урок 1. К этому уроку ученики должны обладать основными сведениями по программе Microsoft ...
Встроенные математические и логические функции в Excel

Встроенные математические и логические функции в Excel

Автор: М. акарова Наталия Анатольевна, учитель информатики. Муниципальное бюджетное общеобразовательное учреждение Сосновская средняя общеобразовательная ...
Вычисление площади поверхности прямоугольного параллелепипеда с использованием программы Microsoft Excel

Вычисление площади поверхности прямоугольного параллелепипеда с использованием программы Microsoft Excel

Государственное бюджетное учреждение средняя образовательная школа № 63 города Москвы. Конспект интегрированного урока. . по ...
Время и дата в VBA Excel

Время и дата в VBA Excel

Время и дата в VBA Excel. Тема:.  Время и дата в VBA ExcelЦели:.  Образовательные:. Cформировать представление о работе с датой. . Cформировать ...
Ввод и редактирование данных в ЭТ Excel

Ввод и редактирование данных в ЭТ Excel

Муниципальное бюджетное учреждение средняя общеобразовательная школа №2. Конспект урока. По теме: «Ввод и редактирование данных в ЭТ Excel. ...
Абсолютная адресация в ЭТ Excel

Абсолютная адресация в ЭТ Excel

МБОУ «Майминская СОШ №3 им. В.Ф. Хохолкова» Васильева Е.В., учитель информатики. . Муниципальное бюджетное образовательное учреждение. «Майминская ...
Практикум. Технология накопления данных и их обработки в Excel

Практикум. Технология накопления данных и их обработки в Excel

Информатика и ИКТ 10 класс. Урок №. Дата проведения. . . Тема урока. Практикум. Технология накопления данных и их обработки в Excel. Ход урока:. ...
Графические возможности MS EXCEL

Графические возможности MS EXCEL

Автор: Папшева Виктория Владимировна. учитель информатики. МОУ "ООШ № 100 им. С.Е. Цветкова". Урок на тему. «Графические возможности MS. ...
Применение программы электронная таблица Excel

Применение программы электронная таблица Excel

Ачитский филиал государственного бюджетного образовательного учреждения среднего профессионального образования Свердловской области. «Красноуфимский ...
Графический способ решения систем уравнений с 2-мя переменными средствами Microsoft Excel

Графический способ решения систем уравнений с 2-мя переменными средствами Microsoft Excel

Урок по теме:. «Графический способ решения систем уравнений с 2-мя переменными средствами. Microsoft. . Excel. ». Класс: 9. . . Кол-во часов: ...
Расчет суточного рациона питания в MS Excel

Расчет суточного рациона питания в MS Excel

. ГБОУ НПО «Лысьвенский профессиональный лицей». Методическая разработка учебного занятия по предмету. «Информационные технологии в профессиональной ...
Информатика как наука. Цели, задачи, предмет информатики

Информатика как наука. Цели, задачи, предмет информатики

5. . . Тема: «. Информатика как наука. Цели, задачи, предмет информатики». Цель:.  . 1.Углубить знания по разделу «Информация и информатика». ...

Информация о конспекте

Ваша оценка: Оцените конспект по шкале от 1 до 5 баллов
Дата добавления:14 марта 2017
Категория:Информатика
Классы:
Поделись с друзьями:
Скачать конспект