I. технологии обработки текстовой информации
МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РОССИЙСКОЙ ФЕДЕРАЦИИ
Федеральное государственное бюджетное образовательное учреждение высшего образования
«БЕЛГОРОДСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ ИМЕНИ В.Я. ГОРИНА»
Кафедра информатики и информационных технологий
МЕТОДИЧЕСКИЕ УКАЗАНИЯ И ЗАДАНИЯ
Для лабораторных занятий
и самостоятельной работы по дисциплине
«ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В ПРОФЕССИОНАЛЬНОЙДЕЯТЕЛЬНОСТИ»
для студентов направления 35.03.06 «Агроинженерия»
Белгород, 2015
УДК
ББК
Методические указания и задания для лабораторных занятий и самостоятельной работы по дисциплине «Информационные технологии в профессиональной деятельности».
Предназначено для студентов инженерного факультета, обучающихся по направлению 35.03.06 «Агроинженерия» - Белгород: - Издательство Белгородский ГАУ им.В.Я. Горина, 2015. - 74 с.
Составители:
ст. преподаватели Филиппова Л.Б., Павлова О.В., Тюкова Л.Н.
Рассмотрено на заседании кафедры информатики и информационных технологий
«16» февраля 2017 г., протокол № 8
Зав. кафедрой ____________________________________ Петросов Д.А.
Утверждено на заседании методической комиссии инженерного факультета
«___» _____________., протокол № ___
Председатель методической комиссии
инженерного факультета _____________________ Слободюк А.П.
©Федеральное государственное бюджетное образовательное учреждение высшего образования Белгородский государственный аграрный университет имени В.Я. Горина, 2017 г.
ВВЕДЕНИЕ
В современных условиях развитая личность, владеющая знаниями и умениями использования средств информационных технологий в профессиональной деятельности, становится востребованной обществом на всех ступенях ее развития. Это особенно актуально для инженеров, чья деятельность связана с выполнением различных задач, решение которых зачастую наиболее продуктивно осуществимо с использованием средств информационных и коммуникационных технологий. Постоянные изменения, происходящие в обществе, требуют от инженеров качеств, позволяющих творчески и продуктивно подходить к решению любых проблем. В связи с этим главной ценностью при обучении инженеров становятся развитие их способностей творчески использовать знания, полученные при изучении информационных технологий, решать профессиональные задачи, возникающие в повседневной профессиональной деятельности.
Теоретический и практический материал данных методических указаний направлен на формирование знаний и умений, в полной мере отвечает требованиям к результатам освоения учебной дисциплины «Информационные технологии в профессиональной деятельности» в соответствии с ФГОС ВО третьего поколения.
Методические указания рекомендованы для изучения дисциплины «Информационные технологии в профессиональной деятельности» по направлению подготовки бакалавров очной и заочной формы обучения «Агроинженерия».
I. ТЕХНОЛОГИИ ОБРАБОТКИ ТЕКСТОВОЙ ИНФОРМАЦИИ
I. ИЗУЧАЕМ ОСНОВЫ EXCEL
Итак, начинаем
1.1. Что такое ввод
1.2. Ввод данных
1.2.1. Ввод текста
1.2.2. Ввод чисел в формат текста
1.2.3. Ввод чисел
1.2.4. Ввод даты и времени
1.3. Перемещение по таблице
1.4. Выбор диапазонов
1.5. Как давать программе Excel ценные указания
1.5.1. Использовать меню
1.5.2. Использовать контекстное меню
1.5.3. Использование панелей инструментов
1.5.4. Использование сочетание клавиш
1.6. Сохранение книги Microsoft Excel
1.7. Получение помощи
Правка и форматирование листов
2.1. Открытие существующих книг
2.2. Основы редактирования
2.2.1. Изменение содержимого ячеек
2.2.2. Копирование содержимого ячеек
Выполняем вычисления
3.1. Простые вычисления
3.1.1. Арифметические действия
БОЛЬШЕ ОПЫТА - БОЛЬШЕ ВОЗМОЖНОСТЕЙ
Наглядное представление данных
4.1. Составляем бюджет
4.2. Автоматическое форматирование
4.3. Вычерчиваем диаграмму
4.3.1. Изменяем размеры и расположение диаграммы
Извлекаем информацию из листа
5.1. Создаем копию листов
5.1.1. Добавляем поле сортировки
Углубляемся в вычисления
Алфавитный указатель
Методические указания по выполнению задания.
Для построения этого списка наберите первую строку и выделите ее. Выполните команды Формат/Список/Многоуровневый и выберите нужный вид списка и нужную нумерацию. Установите курсор в конец первой строки и нажмите клавишу Ввод. Добавленная строка будет иметь тот же уровень вложенности, что и предыдущая. Для увеличения уровня вложенности нажмите клавишу Tab , для уменьшения – Shift+Tab. Последовательно наберите нужные строки, устанавливая нужный уровень вложенности. В случае, если уровень вложенности будет увеличиваться не последовательно, уменьшите размер табуляции по умолчанию до 0,5см.
Этот список можно построить и иначе. Для этого необходимо набрать только текст, нажимая в конце каждой строки клавишу Enter.Выделяя строки, находящиеся ниже первого уровня сдвигаем их вправо на одну или две позиции табулятора ( в зависимости уровня вложенности) с помощью кнопки Увеличить отступ на панели Форматирование или с помощью клавиши Tab. Затем выделяем весь список и выполняем команды Формат/Список /Многоуровневый. Выбираем нужную нумерацию и нажимаем кнопку OK.В случае, если уровень вложенности не будет нужным, уменьшите размер табуляции по умолчанию до 0,5см. повторите предыдущие действия.
Построить многоуровневый список можно и не используя табуляцию. В этом случае строки каждого уровня нужно набирать с помощью подчиненных стилей, например Заголовок 1, Заголовок 2, и заголовок 3.
18. Сохранить работу по именем ЛР_1 в папке со своей фамилией. Оформить отчёт. Приготовиться к защите.
Контрольные вопросы для допуска и защиты работы
1. Назначение текстового процессора Word.
2. Как осуществить преобразование формата документа при открытии и при сохранении документа?
3. Как можно разбить текст на абзацы?
4. Как разделить документ на страницы, убрать разделение на страницы?
5. Как можно узнать, какой использовался формат абзаца и шрифта?
6. Что такое колонтитулы? Как вставить в документ колонтитул?
7. Как изменить границы рисунка, его размер? В чем разница?
8. Как редактировать колонтитулы?
9. Как оформить абзац текста буквицей?
10. Какими способами можно изменить один стиль на другой?
11. Как можно сформировать в документе оглавление?
12. Как можно обновить оглавление?
13. Как удалить нумерацию страниц?
14. Как удалить колонтитул?
15. Какими способами можно создавать таблицы?
16. Какие действия со структурными частями текста можно выполнить в режиме структуры документа?
17. Добавление строки или колонки в таблицу.
18. Выполнение вычислений в таблицах.
19. Использование панели Символы и объекта Microsoft Equation 3.0 для вставки формул в документ.
20. Как создать маркированный список?
Задание 1.
Постановка задачи.
1. Составить блок-схему алгоритма для расчета подоходного налога.
2. Рассчитать подоходный налог на доходы физических лиц.
Выполнение задания.
Для построения блок-схемы введем следующие обозначения:
· размер налога обозначим N = 13%
· необлагаемая база для лиц, имеющих заработную плату меньше 20000 руб. – S = 400 руб.
· размер заработной платы работников – Y
Рис. 2.1. Блок-схема алгоритма разветвляющегося вычислительного процесса
Microsoft Excel содержит несколько логических функций, которые используются для сравнения различных данных в выражениях.
Для решения задач разветвляющейся структуры используют логическую функцию ЕСЛИ.
Функция ЕСЛИ выполняет проверку условия, заданного первым аргументом, и в зависимости от результата проверки обеспечивает вычисление с использованием одного выражения (если результат – ИСТИНА) либо другого (если результат – ЛОЖЬ).
Синтаксис логической функции ЕСЛИ:
= ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь).
Функция ЕСЛИвозвращаетзначение_если_истина,если лог_выражениеимеет значениеистина,изначение_если_ложь–если лог_выражениеимеет значениеложь.
Аргументами функции могут быть числа, адреса, формулы, различные функции, а также текстовые данные.
Функция ЕСЛИ допускает вложения. На месте выражений второго и третьего аргументов, в свою очередь, может снова записываться функция ЕСЛИ.
Всего допускается до семи вложений функции ЕСЛИ.
Технология выполнения задания в MS Excel
1. Установить курсор в ячейку А1 и ввести заголовок таблицы.
2. В диапазон ячеек А3:В4 ввести исходные данные, для ввода текста в несколько строк в ячейку выполнить форматирование в меню Формат ячеек… – Выравнивание,установить переключатель переносить по словам.
3. Для ввода заголовков столбцов таблицы выделить диапазон ячеек А7:С7, выбрать в меню Формат ячеек… – Выравнивание.
Установить выравнивание и отображение:
4. В диапазон ячеек А8:В12 ввести фамилии работников и размер заработной платы (формат – Денежный в меню Формат ячеек… – Число).
5. Установить курсор в ячейку С8 для ввода формулы, щелкнуть по кнопке 
Вставить функциюв строке формул.В открывшемся диалоговом окне Мастер функций установить Категория – Логические,функция–ЕСЛИ,щелкнуть по кнопкеOk.
6. В открывшемся диалоговом окне Аргументы функции заполнить все текстовые поля, условия и иллюстрация решения задачи приведены на рисунке 2.2.
7. После щелчка по кнопке Ok в ячейке С8 появится результат вычислений, а в строке формул будет отображена формула:
=ЕСЛИ(B8<=20000;(В8-$B$4)*$B$3;B8*$B$3)
Рис. 2.2. Исходные данные для решения задачи
8. Скопировать формулу в ячейки С9:С12 способом автозаполнения, для этого установить курсор в ячейку С8, подвести указатель мыши к маркеру заполнения (к правому нижнему углу ячейки) и когда курсор примет вид +, нажать левую кнопку мыши и протащить указатель до ячейки С12.
9. Выделить диапазон ячеек А3:В4 для обрамления таблицы и щелкнуть по кнопке 
(Границы) на вкладке Шрифт.Аналогично обрамить таблицу в диапазоне ячеек А7:С12.
Задание 2.
Постановка задачи.
Составить блок-схему алгоритма и вычислить функцию g=f(x, y, z).
Выполнение задания.
Рис. 2.3. Блок-схема алгоритма разветвляющегося вычислительного процесса
Технология выполнения задания в MS Excel
1. Выделить диапазон ячеек А1:D1, в который будет помещен заголовок таблицы и щелкнуть по кнопке 
(Объединить и поместить в центре)на панели инструментов.В указанный диапазон с клавиатуры ввести заголовок таблицы (рис. 2.4) и нажать клавишу Enter.
2. В ячейку А2 ввести название переменной – Х, в ячейку В2 – Y, С2 – Z, D2–g(x,y,z).В диапазон ячеек А3:С4ввести исходные данные(рис. 2.4).
Рис. 2.4. Ввод исходных данных
3. Установить курсор в ячейку D3 для ввода формулы для вычисления, щелкнуть по кнопке Вставка функциив строке формул.В открывшемся диалоговом окне Мастер функций установить Категория–Логические,функция–ЕСЛИ,щелкнуть по кнопкеOk.
4. В диалоговом окне Аргументы функции (рис. 2.5) ввести:
Рис. 2.5. Диалоговое окно Аргументы функции
1. После щелчка по кнопке Ok в ячейке D3 появится результат вычислений, а в строке формул будет отображена формула:
=ЕСЛИ(A3<=B3;C3*СТЕПЕНЬ(A3+1;1/3);3*B3/(КОРЕНЬ(A3)+LN(C3)))
2. Скопировать формулу в ячейку D4, для этого установить курсор в ячейку DЗ, подвести указатель мыши к маркеру заполнения и когда курсор примет вид +, нажать левую кнопку мыши и протащить указатель до ячейки D4.
3. Выделить рассчитанную таблицу (диапазон ячеек А2:D4) и обрамить ее кнопкой (Границы) на панели инструментов.
Рис. 2.6. Вычисление функции g=f(x, y, z)
Задания для самостоятельной работы
Постановка задачи.
Составить блок-схему алгоритма и вычислить функцию g=f(x, y, z) в Microsoft Excel.
Постановка задачи.
1. Составить блок-схему алгоритма табулирования функции одного аргумента.
2. Вычислить значения функции 
,на интервале измененияаргумента х от 1 до 3 с шагом 0,2. При а=2,5; b=0,5. Исходные данные и результаты поместить в таблицу.
3. Построить график функции Y.
Выполнение задания.
Рис. 3.1. Блок-схема алгоритма циклического вычислительного процесса
Технология выполнения задания в MS Excel
1. В диапазон ячеек А1:D1 ввести заголовок таблицы «Табулирование функции Y=f(x)».
2. В ячейку А2 – обозначение аргумента Х, в ячейку В2 – обозначение функции Y, в ячейку С2 – обозначение переменной a, в ячейку D2 – обозначение переменной b.
3. В диапазон ячеек А3:А13 ввести значения переменной Х: от 1 до 3. Для ввода числового ряда использовать способ автозаполнения. Для этого в ячейку А3 ввести 1, в ячейку А4 ввести 1,2. Выделить эти ячейки. Установить указатель мыши на маркере заполнения выделенного диапазона, указатель изменится на +, протащить его вниз до тех пор, пока не получится числовой ряд нужной длины.
4. В ячейку С3 ввести значение 2,5, в ячейку D3 ввести значение 0,5.
5. В ячейку В3 ввести формулу для вычисления функции Y с помощью Мастера функций:
=EXP(A3+$C$3)*КОРЕНЬ($D$3*A3)
Для изменения способа адресации при редактировании формулы необходимо выделить ссылку на ячейку и нажать клавишу F4. Если формула введена верно, то в ячейке В3 вместо формулы появится результат вычислений, а в строке формул будет отображена формула.
6. Установить курсор в ячейку В3, подвести указатель мыши к маркеру заполнения этой ячейки и протащить его вниз до ячейки В13. В ячейках В3:В13 получим значения функции Y при соответствующих значениях аргумента (рис. 3.2).
7. Выделить рассчитанную таблицу (диапазон ячеек А2:В13 и С2:D3) и обрамить ее кнопкой 
(Границы) на панели инструментов.
Рис. 3.2. Результат решения задачи
Построение графика функции Y=f(x)
1. Для построения графика функции выделить диапазон ячеек В3:В13. Во вкладке Вставка в группе Диаграммы выбрать График с маркерами
2. На вкладке Работа с диаграммами выбрать Конструктор, затем в группе Данные нажать кнопку Выбрать данные.
3. В появившемся диалоговом окне Выбор источника данных заменить элементы легенды Ряд 1 на Y. Подписи горизонтальной оси (нажав кнопку Изменить) указать диапазон ячеек а1:а13. Нажать OK.
4. На вкладке Работа с диаграммами выбрать Макет, в группе Подписи выбрать Название диаграммы → Над диаграммой и ввести название диаграммы График функции Y=f(x).
Рис. 3.4. График функции Y=f(x)
Задания для самостоятельной работы
Постановка задачи.
1. Составить блок-схему алгоритма и вычислить значения функции для значений аргумента, изменяющихся от начального значения до конечного значения с заданным шагом.
2. Построить график функции.
Контрольные вопросы для допуска и защиты работы
1. Для чего предназначен табличный процессор Excel?
2. Как называется документ Excel?
3. Что называют активным листом? Активной ячейкой?
4. Что может содержаться в ячейке Excel?
5. Из чего состоит адрес ячейки?
6. В чем различие относительной и абсолютной адресации ячеек? При какой операции оно проявляется?
7. Как записывается диапазон ячеек?
8. Из чего состоит строка формул?
9. Как выглядят заголовки строк и столбцов?
10. Какого типа данные могут быть введены в ячейку?
11. В чем заключается достоинство использования ссылок и формул со ссылками?
12. Каковы правила записи формулы в ячейку таблицы Excel?
13. Что такое автозаполнение ячеек?
14. Как на листе Excel выделить несколько несмежных диапазонов?
15. Как автоматизировать нумерацию строк списка?
16. Что такое диаграмма? Какие средства есть в Excel для построения диаграмм?
17. Что такое Легенда в диаграмме Excel?
Задание 1
Анализ результатов
В результате обработки статистических данных графическими методами и алгоритмом наименьших квадратов получено уравнение зависимости объема продаж от затрат на рекламу. Получены числовые значения параметров и оценка их достоверности. С увеличением затрат на рекламу объемы продаж увеличиваются. Модель готова для применения в прогнозировании и планировании.
Задание 2
Задание
На основе заданных статистических данных построить математическую модель зависимости объемов продаж от цены товара и численно оценить параметры модели.
Методика выполнения задания
Полностью совпадает с представленной выше методикой оценки параметров модели зависимости объема продаж от затрат на рекламу.
Заполнить второй лист вашей книги данными в соответствии с рис. 4.4.
Рис. 4.4. Таблица с исходными данными, графиками и формулами с оценкой
параметров нелинейной однофакторной модели зависимости объемов
продаж от цены товара
Имея опыт решения первой задачи, выполнить решение второго задания самостоятельно. Выполнить прогноз объема продаж, задав конкретное значение цены изделия.
Анализ результатов
В результате обработки статистических данных графическими методами и алгоритмом наименьших квадратов получено уравнение зависимости объема продаж от цены товара. Получены числовые значения параметров и оценка их достоверности.
С увеличением цены объемы продаж уменьшаются, сокращается платежеспособный спрос. Модель готова для применения в прогнозировании, планировании и исследовании рыночного равновесия.
Задание 3.
Другим способом вычисления коэффициентов а1 и а0 является использование функций НАКЛОН И ОТРЕЗОК. Названия этих функций отвечают геометрическому смыслу коэффициентов регрессии: а1 – это тангенс угла наклона прямой регрессии, а а0 - отрезок, отсекаемый этой прямой на оси ординат.
Общий синтаксис вызова данных функций имеет вид:
НАКЛОН(известные_значения_у;известные_значения_х);
ОТРЕЗОК(известные_значения_х;известные_значения_у).
Вычислить для задания 1 коэффициенты а1 и а0 с помощью функций НАКЛОН и ОТРЕЗОК.
Задание 4.
Построение функциональной зависимости необходимо, чтобы вычислить отклики для новых значений факторов, для чего в Excel существуют функции ТЕНДЕНЦИЯ и ПРЕДСКАЗ, рассчитывающие значения у без определения коэффициентов уравнения.
Функция ТЕНДЕНЦИЯвычисляет значения в соответствии с прямой линией по методу наименьших квадратов. Она возвращает значения у, в соответствии с этой прямой для заданного массива новые_значения_х на основе известные_значения_у. Синтаксис функции ТЕНДЕНЦИЯ имеет следующий вид:
ТЕНДЕНЦИЯ(известные_значения_у;известные_значения_х; новые_значения_х;конст),
где конст – логическое значение, которое указывает, требуется ли, чтобы константа а0 была равна 0.
Функция ПРЕДСКАЗ вычисляет будущее значение по существующим значениям и имеет следующий синтаксис:
ПРЕДСКАЗ(х;известные_значения_у; известные_значения_х), где х – точка данных, для которой предсказывается значение.
Предсказать объем продаж в задании 1 с помощью функций ТЕНДЕНЦИЯ и ПРЕДСКАЗ.
Задание 5.
Анализ результатов
В результате обработки статистических данных функцией ЛИНЕЙН методами и алгоритмом наименьших квадратов получены числовые значения параметров и оценка их достоверности (рис. 4.6). Получено уравнение зависимости объема продаж от цены товара и затрат на рекламу.
Рис. 4.6. Таблица с исходными данными и применением функции
ЛИНЕЙН для оценки параметров модели зависимости
объемов продаж от затрат на рекламу и цены товара
С увеличением цены объемы продаж уменьшаются, сокращается платежеспособный спрос, а с увеличением затрат на рекламу продажи увеличиваются.
Модель готова для применения в прогнозировании, планировании и исследовании рыночного равновесия.
Задания для самостоятельного решения
Задание 1.
Построить и исследовать динамику роста производства продукции, используя данные:
| Годы | Уровень производства |
| 16,9 | |
| 17,1 | |
| 18,9 | |
| 19,7 | |
| 19,8 | |
| 19,9 |
Определить уровень производства в 2017 году. Обосновать выбор уравнения регрессии для расчета прогноза
Задание 2.
Построить функции, наилучшим образом аппроксимирующие зависимости:
| х | 1,0 | 1,5 | 3,0 | 4,5 | 5,5 |
| у | 1,25 | 1,4 | 1,5 | 1,75 | 2,25 |
Обосновать выбор уравнения регрессии для расчета прогноза
Задание 3
В таблице приведены данные численности студентов ВУЗа за период с 2011 по 2016 годы.
| Годы | ||||||
| Фактическая численность, чел. |
Рассчитать численность студентов на следующие три года.
Обосновать выбор уравнения регрессии для расчета прогноза.
Задание 4
Вложенные в производство средства дают прибыль:
| Средства | ||||||
| Прибыль |
Определить зависимость прибыли от вложенных средств и вычислить прибыль для вложений, равных 10000 руб.
Задание 5
В таблице приведены данные численности персонала организации за период с 2011 по 2016 годы:
| Годы | |||||||||
| Фактическая численность, чел. |
Рассчитать численность персонала на следующие три года.
Обосновать выбор уравнения регрессии для расчета прогноза
Задание 6
В таблице приведены статистические данные опроса населения по их затратам на приобретение продуктов питания
| Доход на члена семьи | ||||||||||
| Затраты на питание |
Рассчитать, какие будут затраты на питание, если доход на члена семьи будет равняться 5000 рублей.
Обосновать выбор уравнения регрессии для расчета прогноза.
Задание 7
В таблице приведены данные по продаже электробытовых товаров (холодильники, пылесосы, обогреватели и др.) 2009 – 2016 гг., полученные на основе опроса населения
| Годы | ||||||||
| Товарооборот | 1,8 | 2,27 | 4,3 | 6,4 | 5,7 | 7,2 | 8,2 | 8,4 |
Найти уравнение регрессии наилучшим образом описывающее данные опроса, на 2017 и 2018 годы полагая, что процессы определяющие спрос населения на электробытовые товары остается неизменным в течение всего последующего времени.
Контрольные вопросы для допуска и защиты работы
1. Что такое «линия тренда»?
2. Как построить линию тренда для заданного ряда наблюдений?
3. Какие типы линии тренда позволяет использовать Excel?
4. Как показать на диаграмме (графике) уравнение линии тренда?
5. Что характеризует статистика R2? В каких пределах она может изменяться?
6. Вы получили два уравнения регрессии. Одно из них имеет R2 = 0,45, а другое 0,95. Какое из уравнений Вы используете для прогноза?
Лабораторная работа №5. «Решение транспортной задачи. Нахождение Оптимального плана перевозки грузов с целью Получения минимальных издержек»
Задание
Разработка табличной модели
В верхней строке электронной таблицы Excel даны имена колонок A,B,C и т.д. В первой колонке номера строк. В колонке А - имена заводов-поставщиков. В строке 2 - имена потребителей.
Общие плановые затраты в ячейке АI5 надо минимизировать. Искомая плановая матрица объемов перевозки грузов от каждого поставщика к каждому потребителю расположена в диапазоне СЗ:G5.
В диапазоне ВЗ:В5 вычисляются планы поставок от каждого завода всем потребителям как суммы по строкам. Необходимо, чтобы эти суммы не превысили мощностей заводов-поставщиков. В строке 7 вычисляются планы поставок каждому потребителю от всех заводов как суммы по столбцам. Необходимо, чтобы эти суммы были равны или не меньше заказов потребителей.
В строках 9, 11:13 представлены исходные данные для расчетов. В диапазон В11:В13 вводятся мощности заводов-поставщиков. В матрицу C11:G13 надо ввести стоимость перевозки единицы груза от каждого поставщика к каждому потребителю. В строку 9 вводятся плановые потребности складов.
В строке 15 вычисляются стоимость перевозок для каждого склада и общие затраты по транспортировке.
Формулы табличной модели
После составления плановой таблицы необходимо связать показатели формулами для вычислений. Представление формул и чисел исходных данных дано в таблице
Поиск оптимального плана
Скопировать в буфер обмена исходную таблицу 1, открыть Лист 2 и вставить из буфера обмена таблицу 1. Переименовать Лист 2 в «Оптимальный план». Лист 1 назвать «Исходные данные»
1. Выполнить команду меню Данные → Поиск решения.
2. В появившемся диалоговом окне Поиск решения выполнить настройку модели (математическая постановка задачи для оптимизации):
· В поле целевой ячейки ввести ее адрес А15.
· В поле Ограничения ввести 3 строки неравенств значений диапазонов: поставки от заводов не должны превышать мощности заводов, поставки потребителям не должны быть меньше потребностей, значения плана не могут быть отрицательными.
Свод параметров модели дан в таблице
| Параметр задачи | Ячейки | Семантика |
| Результат | А15 | Цель – уменьшение всех транспортных расходов |
| Изменяемые данные | C3:G5 | Объемы перевозок от каждого из заводов к каждому складу |
| Ограничения | B3:B5<=B11:B13 | Количество перевезенных грузов не может превышать производственных возможностей |
| C7:G7>=C9:G9 | Количество поставляемых грузов не должно быть меньше потребностей складов | |
| C3:G5>=0 | Число перевозок не может быть отрицательным |
3. После настройки модели и установки параметров алгоритма нажать кнопку Выполнить окна Поиск решения.
4. Проверьте, чтобы в полученном решении было m+n-1=7 не нулевых перевозок, где m - количество заводов, n – количество складов. В противном случае задача является вырожденной.
Задания для самостоятельного решения
Задание 1.
Пусть имеются S=4 поставщика и D=5 потребителей. Издержки перевозки единицы груза от i-го поставщика в j-й пункт назначения, запасы поставщиков и заказы потребителей приведены в таблице.
| D1 | D2 | D3 | D4 | D5 | Запасы | |
| S1 | ||||||
| S2 | ||||||
| S3 | ||||||
| S4 | ||||||
| Заказы |
Требуется оптимизировать план перевозок.
Задание 2
Пусть имеются производители продукции в городах Курск, Калуга, Воронеж и Орел и торговые склады в городах Москва, Смоленск, Киров, Тверь. Издержки перевозки единицы груза от i-го производителя в j-й пункт назначения, запасы производителей и заказы потребителей приведены в таблице.
| Москва | Смоленск | Киров | Тверь | Запасы | |
| Курск | |||||
| Калуга | |||||
| Воронеж | |||||
| Орел | |||||
| Заказы |
Требуется оптимизировать план перевозок.
Задание 3.
Имеется три поставщика с запасами товара и 3 потребителя данного товара. Требуется определить оптимальный план перевозок, при котором суммарные затраты на транспортировку грузов будут минимальны. Сведения о возможностях поставщиков и запросах потребителей, а также цены на перевозку единицы товара от поставщиков к потребителям сведены в таблицу.
| Потребители Поставщики | D1=41 | D2=49 | D3=40 |
| S1=61 | |||
| S2=39 | |||
| S3=56 |
Составить экономико-математическую модель решения задачи, учитывая, что:
Si – возможности i-го поставщика в условных единицах;
Di – потребности j-го потребителя в тех же единицах;
xij – объем планируемых перевозок.
Задание 4.
Привести открытую транспортную задачу, представленную в таблице, к закрытой. Составить экономико-математическую модель, оптимизирующую план перевозок.
| Поставщики | Мощности поставщиков | Потребители и их спрос | |||
Задание 5.
Пусть требуется минимизировать затраты на перевозку товаров от 3 предприятий-производителей на торговые склады 5 городов, куда необходимо поставить 180, 80, 200, 160 и 220 единиц товара соответственно. При этом необходимо учесть возможности поставок каждого из производителей при максимальном удовлетворении запросов потребителей и затраты на перевозку единицы товара.
| Заводы: | Поставки | Склад 1 | Склад 2 | Склад 3 | Склад 4 | Склад 5 |
| I | ||||||
| II | ||||||
| III |
Задание 6.
Привести открытую транспортную задачу, представленную в таблице, к закрытой. Составить экономико-математическую модель, оптимизирующую план перевозок.
| Поставщики | Мощности поставщиков | Потребители и их спрос | |||
Задание 7.
Привести открытую транспортную задачу, представленную в таблице, к закрытой. Составить экономико-математическую модель, оптимизирующую план перевозок.
| Поставщики | Мощности поставщиков | Потребители и их спрос | ||
Контрольные вопросы для допуска и защиты работы
1. Каково назначение программы-надстройки "Поиск решения"?
2. Как установить надстройку Поиск решения
3. Перечислите основные элементы оптимизационной математической модели.
4. Опишите возможный порядок построения оптимизационной модели.
5. Пояснить структуру плановой таблицы.
6. Перечислить исходные данные, переменные и результирующие показатели модели.
7. Дать краткую технологию решения транспортной задачи в программе Excel Поиск решения.
Задание.
Сформировать структуру БД по грузоперевозкам, состоящей из таблиц:
· ВОДИТЕЛИ: справочный массив, содержащий общие сведения о водителях (табельный номер, фамилия, имя, отчество, паспорт, водительское удостоверение, дата приёма на работу, дата рождения);
· ГРУЗЫ: справочный массив, содержащий общие сведения о перевозимых грузах (код груза, название груза);
· ЗАКАЗЫ: справочный массив, содержащий общие сведения о заказах (дата выезда, время выезда, дата прибытия, время прибытия, шифр организации, код транспортного средства, код груза, код услуги, вес (кг), пункт отправления, пункт назначения, стоимость);
· ОРГАНИЗАЦИИ: справочный массив, содержащий общие сведения об организациях (шифр организации, название, адрес, дата создания, фамилия руководителя, телефон);
· ТЕХНИЧЕСКИЕ СРЕДСТВА (ТС): справочный массив, содержащий общие сведения об автомобилях (код ТС, госномер, марка, табельный номер водителя, грузоподъёмность (в тоннах), износ