|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
|
|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
По курсу: «Информационные технологии»Стр 1 из 6Следующая ⇒
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ ЮЖНЫЙ ФЕДЕРАЛЬНЫЙ УНИВЕРСИТЕТ
ТЕХНОЛОГИЧЕСКИЙ ИНСТИТУТ ЮЖНОГО ФЕДЕРАЛЬНОГО УНИВЕРСИТЕТА В г. ТАГАНРОГЕ Лабораторная работа «Знакомство с табличным процессором MS Excel» По курсу: «Информационные технологии» Таганрог 2011 г. Содержание ВВЕДЕНИЕ. 3 1 РАБОЧАЯ КНИГА И РАБОЧИЙ ЛИСТ. СТРОКИ, СТОЛБЦЫ, ЯЧЕЙКИ.. 3 2 СОДЕРЖАНИЕ ЭЛЕКТРОННОЙ ТАБЛИЦЫ.. 4 3 ВОЗМОЖНОСТИ РЕДАКТИРОВАНИЯ ЭЛЕКТРОННЫХ ТАБЛИЦ.. 7 4 КОНСОЛИДАЦИЯ ДАННЫХ.. 8 5 ПРЕДСТАВЛЕНИЕ РЕЗУЛЬТАТОВ В ГРАФИЧЕСКОМ ВИДЕ. 10 6 РАБОТА С ФУНКЦИЯМИ.. 10 7 ЗАДАЧИ НА ПОДБОР ПАРАМЕТРА.. 11 8 ЗАДАЧИ НА ПОДВЕДЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ.. 12 9 СВОДНЫЕ ТАБЛИЦЫ.. 13 10 ПОИСК РЕШЕНИЯ.. 15 11 РЕШЕНИЕ ЗАДАЧ ПО ОБРАБОТКЕ ЭКОНОМИЧЕСКОЙ ИНФОРМАЦИИ 26 12 МАКРОСЫ.. 29 13 СЦЕНАРИИ.. 29 14 ТАБЛИЦЫ ПОДСТАНОВКИ.. 30 15 ЗАЩИТА ИНФОРМАЦИИ НА РАБОЧИХ ЛИСТАХ.. 30 ВАРИАНТЫ ЗАДАНИЙ.. 31 ВВЕДЕНИЕ Цель: Ознакомиться с основными понятиями электронных таблиц, изучить функции и научиться применять их на практике. Для представления данных в удобном виде используют таблицы. Компьютер позволяет представлять их в электронной форме, а это дает возможность не только отображать, но и обрабатывать данные. Класс программ, используемых для этой цели, называется электронными таблицами. Особенность электронных таблиц заключается в возможности применения формул для описания связи между значениями различных ячеек. Расчет по заданным формулам выполняется автоматически. Изменение содержимого какой-либо ячейки приводит к пересчету значений всех ячеек, которые с ней связаны формульными отношениями и, тем самым, к обновлению всей таблицы в соответствии с изменившимися данными. Программа MS Excel предназначена для работы с таблицами данных, преимущественно числовых. При формировании таблицы выполняют ввод, редактирование и форматирование текстовых и числовых данных, а также формул.
РАБОЧАЯ КНИГА И РАБОЧИЙ ЛИСТ. СТРОКИ, СТОЛБЦЫ, ЯЧЕЙКИ Документ Excel называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц. Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от А до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536. Ячейки и их адресация. На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (в этом порядке), на пересечении которых она расположена, например: А1 или D23. Обозначение ячейки (ее номер) выполняет функции ее адреса. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. Ввод, редактирование и форматирование данных Отдельная ячейка может содержать данные, относящиеся к одному из трех типов: текст, число или формула. Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Если эти данные можно интерпретировать как число, программа Excel так и делает. В противном случае данные рассматриваются как текст. Ввод формулы всегда начинается с символа «=» (знака равенства). Ввод текста и чисел. Ввод данных осуществляют непосредственно в текущую ячейку или в строку формул, располагающуюся в верхней части окна программы под панелями инструментов. Если начать ввод нажатием алфавитно-цифровых клавиш, данные из текущей ячейки заменяются вводимым текстом. Если щелкнуть на строке формул или дважды на текущей ячейке, старое содержимое ячейки не удаляется и появляется возможность его редактирования. Вводимые данные в любом случае отображаются как в ячейке, так и в строке формул. Чтобы завершить ввод, сохранив введенные данные, используют клавишу ENTER. Чтобы отменить внесенные изменения и восстановить прежнее значение ячейки, используют клавишу ESC. Форматирование содержимого ячеек. Текстовые данные по умолчанию выравниваются по левому краю ячейки, а числа — по правому. Чтобы изменить формат отображения данных в текущей ячейке или выбранном диапазоне, используют команду Формат > Ячейки. Вкладки этого диалогового окна позволяют выбирать формат записи данных (количество знаков после запятой, указание денежной единицы, способ записи даты и прочее), задавать направление текста и метод его выравнивания, определять шрифт и начертание символов, управлять отображением и видом рамок, задавать фоновый цвет.
СОДЕРЖАНИЕ ЭЛЕКТРОННОЙ ТАБЛИЦЫ Формулы. Вычисления в таблицах программы Excel осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций. Правило использования формул в программе Excel состоит в том, что, если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию легко можно выполнить в «уме». Это гарантирует, что последующее редактирование таблицы не нарушит ее целостности и правильности производимых в ней вычислений. Ссылки на ячейки. Формула может содержать ссылки то есть адреса ячеек, содержимое которых используется в вычислениях. Это означает, что результат вычисления формулы зависит от числа, находящегося в другой ячейке. Ссылку на ячейку можно задать разными способами. Во-первых, адрес ячейки можно ввести вручную. Другой способ состоит в щелчке на нужной ячейке или выборе диапазона, адрес которого требуется ввести. Ячейка или диапазон при этом выделяются пунктирной рамкой. Все диалоговые окна программы Excel, которые требуют указания номеров или диапазонов ячеек, содержат кнопки, присоединенные к соответствующим полям. При щелчке на такой кнопке диалоговое окно сворачивается до минимально возможного размера, что облегчает выбор нужной ячейки (диапазона) с помощью щелчка или протягивания (рисунок 1).
Рисунок 1 – Диалоговое окно в развернутом и свернутом виде
Для редактирования формулы следует дважды щелкнуть на соответствующей ячейке или нажать клавишу F2. При этом ячейки (диапазоны), от которых зависит значение формулы, выделяются на рабочем листе цветными рамками, а сами ссылки отображаются в ячейке и в строке формул тем же цветом. Это облегчает редактирование и проверку правильности формул. КОНСОЛИДАЦИЯ ДАННЫХ Консолидация позволяет обобщать информацию, расположенную на нескольких рабочих листах. ПРИМЕР. На рабочих листах с именами " январь", " февраль", " март" приведены фамилии торговых агентов и количество сделок, которые они совершили в течение месяца. Построить сводку за первый квартал. Решение. Необходимо создать новую рабочую книгу, переименовать листы: " Январь", " Февраль", " Март", " 1 квартал". Внесение заголовков одновременно в несколько листов . Выделите все листы с названиями месяцев: для этого перейдите на лист " Январь", нажмите клавишу Shift и, не отпуская ее, щелкните по ярлычку листа " Март". Будут выделены все листы рабочей книги, при этом активным листом останется " Январь". Введите в ячейку А1 " Фамилия И.О.", в ячейку В1 " Сделки", в С1 " Объем". Щелкните по ярлычку листа " Февраль", выделение с нескольких листов будет снято. Убедитесь, что в ранее выделенные листы внесен один и тот же текст в ячейки А1, В1, С1. Для иллюстративных целей поменяйте на листе " Февраль" содержимое ячеек: в В1 " Объем", а в С1 " Сделки". Ввод информации в листы . Введите в листы с названиями месяцев информацию в соответствии с таблицей (названия месяцев вводить не нужно, они на ярлычках листов).
Консолидация . Прежде всего нужно выделить ячейку, которая будет служить верхней левой ячейкой для блока с результатами консолидации. Для этого перейдем на лист " 1 квартал" и выделим ячейку А1. Выберем в меню " Данные/Консолидация". Появится диалоговое окно " Консолидация". В выпадающем списке " Функция: " выставлено " Сумма", т.е. выбранные данные будут суммироваться. Но можно выбрать и другую итоговую функцию: посмотрите список. Следующее поле: " Ссылка". Выполним последовательность действий: установив фокус ввода в этом поле, будем по очереди выделять диапазоны для консолидации; когда в этом поле появится очередной диапазон, щелкнем кнопку " Добавить" — адрес диапазона переместится в окно " Список диапазонов". Итак, щелкнем мышью в поле " Ссылка: ". Щелкнем по ярлычку листа " Январь" (в поле ввода появится " Январь! " — формируется адрес). Выделим блок А1: С4 (в поле ввода " Январь! $А$1: $С$4" ) — вокруг блока бегущая пунктирная рамка. Щелкнем кнопку " Добавить" — адрес диапазона окажется в поле " Список диапазонов: ". Аналогично добавим диапазоны " Февраль! $А$1: $С$3" и " Март! $А$1: $С$5". Список диапазонов консолидации сформирован. В диалоговом окне имеется блок " Использовать в качестве имен" из двух флажков " подписи верхней строки" и " значения левого столбца". Установим оба флажка. Эти флажки нужно установить, потому что информация в таблице будет идентифицироваться по названиям строк и столбцов. Если бы таблицы по месяцам имели одинаковую структуру, но разные названия столбцов, например, на одном листе столбец называется " Сделки", а на другом — " Количество сделок", но их расположение в таблице одинаково, тогда следовало снять флажок " подписи верхней строки". Установите флажок " создавать связи с исходными данными". После щелчка по кнопке " ОК" на рабочем листе появится таблица:
РАБОТА С ФУНКЦИЯМИ ЗАДАЧИ НА ПОДБОР ПАРАМЕТРА Средство Подбор параметра – инструмент, предназначенный для вычисления входного значения некоторой формулы для того, чтобы формула возвращала требуемый результат. Пример. В ячейку А1 введите 10 – расстояние в милях. В ячейку А2 введите формулу =ПРЕОБР(А1; ''mi''; ''m'')/1000, преобразующую значение милей в километры. В ячейке А2 должно быть число 16, 09. Сколько миль будет соответствовать 20 километрам? Можно подобрать нужное значение, последовательно вводя в ячейку А1 значения 10, 11, 12 и т.д., пока в ячейке А2 не отобразится число 20 (или близкое к нему). ý Такой подбор нерационален. Задачу можно быстро решить, используя средство Подбор параметра. Средство Подбор параметра применяется тогда, когда вы знаете значение, которое должна возвращать формула, но не знаете входное значение для формулы, обеспечивающее желаемый результат вычислений. Чтобы применить средство Подбор параметра, выберите закладку в основном меню Данные " Подбор параметра. Откроется диалоговое окно, в которое надо заполнить все поля ввод, а затем щелкнуть на кнопке ОК. В диалоговом окне Подбор параметра нужно заполнить всего три поля ввода: Установить в ячейке, Значение, Изменяя значения ячейки. В поле Установить в ячейке введите адрес ячейки, содержащей формулу, для результата вычислений которой вы хотите задать значение. В поле Значение введите число, которое вы хотите увидеть в результате вычисления формулы. В поле Изменяя значение ячейки введите адрес ячейки, содержащей числовое значение, которое вы хотите подобрать. В результате появится диалоговое окно Результат подбора параметра. В диалоговом окне Результат подбора параметра отображается два числа: Подбираемое значение ( это значение, которое вы указали в поле Значение ) и Текущее значение (значение, которое Excel смогла добиться от формулы, указанно в поле Установить в ячейке ). Если числа Подбираемое значение и Текущее значение совпадают, то задача решена. Вернитесь к примеру. Подберите значение расстояния в километрах, чтобы расстояние в милях было равно 20. Порядок действия. 1. Данные " Подбор параметра. 2. В поле Установить в ячейке – А2. 3. В поле Значение – 20. 4. В поле Изменяя значение ячейки - А1. 5. Щелкните кнопку ОК.
СВОДНЫЕ ТАБЛИЦЫ
Сводные таблицы предназначены для обобщения (объединения, переработки) информации, хранящейся в базе данных. Они также позволяют отображать табличные данные в виде двух мерной или трехмерной таблицы. Кроме того, с их помощью можно вывести промежуточные итоги с любым уровнем детализации. Каждая сводная таблица состоит из 4 областей: страница, строка, столбец, данные. Назначение областей следующее: · Cтрока. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков строк в сводной таблице. Если в эту область помещено одно поле, то количество строк в сводной таблице (без учета итогов) равно числу уникальных значений этого поля. · Столбец. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков столбцов в сводной таблице. Если в эту область помещено одно поле, то количество столбцов в сводной таблице (без учета итогов) равно числу уникальных значений этого поля. · Данные. Значения полей, помещенных в эту область, используются для заполнения ячеек сводной таблицы итоговыми данными (суммирование, подсчет количества, вычисление среднего значения и т. д.). · Страница. Уникальные значения полей, помещенных в эту область, и элемент «все» используются для построения раскрывающихся списков. В поле страницы можно выбрать только одно значение в каждом из списков. В области данных будут отображены итоговые данные, для выбранного значения. Использование этого элемента сводной таблицы позволяет, в некоторой мере, реализовать отображение трехмерной таблицы. Пример задачи 1. На основании следующей таблицы:
Построить таблицу, показывающую объем прибыли полученной от продажи разных видов продукции разными исполнителями по месяцам в разрезе регионов; Выполнение. Рассчитайте значение поля «Прибыль», записав соответствующую формулу. Выполните команду Данные / Сводная таблица. Установите флажок – В списке или базе данных Microsoft Excel; Укажите диапазон, содержащий построенный список. Если список был построен правильно, нужный диапазон будет выбран автоматически.
Перетащите кнопки «Продукция» и «Менеджер» в область « Строка ». При этом важен порядок перетаскивания – поле «Менеджер» будет вложенным по отношению к полю «Продукция». Затем в область « Столбец » перетащите кнопку « Месяц » и в область страниц – кнопку « Регион ». В область данных перетащите кнопку «Прибыль». Построенная сводная таблица будет иметь следующий вид как указано на рисунке. Пример задачи 2. На основании построенного списка построить таблицу, показывающую объем прибыли полученной от продажи разных видов продукции разными исполнителями по кварталам в разрезе регионов; Выполнение. Скопируйте сводную таблицу задания 1 на второй лист. Отметьте диапазон С4: Е15. Для этого достаточно сделать текущей ячейку С4 (выделится столбец сводной таблицы за январь месяц) и нажав клавишу « Shift » щелкнуть по ячейке E4. Выполните команды « Данные » « Группа и структура » « Группировать ». В поле столбца появиться новое поле « Месяц 2 » и в сводную таблицу добавится строка, в которой для выделенных трех столбцов присвоится название « Группа 1 ». Выполните аналогичные действия для столбцов сводной таблицы за апрель и май месяцы. Для этих столбцов должно появится название « Группа 2 ». Удалите поле «месяц». Для этого вызовите контекстное меню или перетащите его из области сводной таблицы. Исправьте название « Месяц 2 » на « Квартал » (дважды щелкните мышкой по названию), « Группа 1 » – на « Первый » (просто введите новое название в ячейку), « Группа 2 » – на « Второй ». Полученная таблица должна иметь следующий вид:
ПОИСК РЕШЕНИЯ Средство Поиск решения позволяет находить для одной формулы такие значения ее входных переменных, которое приводили бы к точно заданному значению, либо минимально или максимально возможному значению. Это средство позволяет также налагать ограничения на значения любых переменных, от которых зависит результат вычисления формулы. Средство поиска решения является надстройкой (Надстройка. Вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.) Microsoft Office Excel, которая доступна при установке Microsoft Office или Microsoft Excel. Чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее. 1. Нажмите кнопку Microsoft Office 2. Выберите команду Надстройки, а затем в окне Управление выберите пункт Надстройки Excel. 3. Нажмите кнопку Перейти. 4. В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК. Совет: Если Поиск решения отсутствует в списке поля Доступные надстройки, чтобы найти надстройку, нажмите кнопку Обзор. В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее. 5. После загрузки надстройки для поиска решения в группе Анализ на вкладки Данные становится доступна команда Поиск решения.
Пример задачи 1 (производственные расчеты). Вычислим оптимальные цены и количество театральных билетов разной категории таким образом, чтобы стоимость всех проданных билетов достигала определенной величины. В театре имеются билеты трех категорий: детские, для взрослых и льготные. Найдем, сколько необходимо продать театральных билетов для того, чтобы в кассе оказалось ровно 240 тыс. руб., при выполнении следующих ограничений:
Рабочий лист выглядит так:
Параметры окна Поиск решения:
Пример задачи 2. Задача об оптимальном ассортименте Предприятие выпускает 2 вида продукции. Цена единицы 1 вида продукции – 25 000, 2 вида продукции – 50 000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57, 6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице.
Требуется определить плановое количество выпускаемой продукции таким образом, чтобы стоимость произведенной продукции была максимальной Выполнение. Математическая модель задачи. Пусть продукция производится в количестве: 1-й вид – x1 единиц, 2-й вид – x2 единиц. Тогда стоимость произведенной продукции выражается целевой функцией: f(x1, x2)=25000 x1+50000x2, для которой необходимо найти максимум. При этом следует учесть ограничения по запасам сырья: 1, 2 x1+1, 9 x2 £ 37, 2, 3 x1+1, 8 x2 £ 57, 6, 0, 1 x1+0, 7 x2 £ 7 и по смыслу задачи x1, x2 должны быть неотрицательными и целыми: x1³ 0, x2 ³ 0. Пример задачи 3. Сетевая транспортная задача На складах имеется груз, количество которого определяется в следующей таблице:
Этот груз необходимо перевезти в пункты назначения в соответствии с таблицей:
Стоимость перевозок определяется таблицей:
Необходимо составить план перевозок так, чтобы стоимость перевозок была минимальной. Пример задачи 4. Задача на составление диеты Необходимо составить диету, состоящую из двух продуктов А и Б. Дневное питание этими продуктами должно давать не более 14 единиц жира, но и не менее 300 калорий. В одном килограмме продукта А содержится 15 единиц жира и 150 калорий, а в одном килограмме продукта Б - 4 единицы жира и 200 калорий. При этом цена одного килограмма продукта А равна 15 у.е., а цена одного килограмма продукта Б - 25 у.е. Какое количество продуктов в день необходимо употреблять для соблюдения диеты, чтобы вложенные средства были минимальны? Выполнение Математическая модель задачи Пусть потребление продуктов составляет количество Вид А – x1 кг, вид В – x2 кг. Затраты на продукты составят: F=15x1+25x2, которые должны быть минимальными. Ограничение на калорийность: 15x1+4x2£ 14 150x1+200x2³ 300 и по смыслу задачи x1, x2 должны быть неотрицательными: x1³ 0, x2 ³ 0. Задачи на линейную оптимизацию также решаются с помощью надстройки «поиск решения».
Изучение рынка сбыта показало, что суточный спрос на материал В никогда не превышает спроса на материал А более чем на 1 т. Кроме того, установлено, что спрос на материал А никогда не превышает 2 т в сутки. Оптовые цены одной тонны материалов равны: 3000 у.е. для В и 2000 у.е. для А. Какое количество материала каждого вида должна производить фабрика, чтобы доход от реализации был максимальным? Выполнение Построение математической модели задачи: Формулировка математической модели задачи: 1) переменные для решения задачи. x1 - суточный объем производства материала А, Х2 - суточный объем производства материала В; 2) определение функции цели (критерия оптимизации). Суммарная суточная прибыль от производства X1 материала А и x2 материала В равна: поэтому цель фабрики - среди всех допустимых значений х1 и х2 найти такие, которые максимизируют суммарную прибыль от производства материалов. ограничения на переменные: а) объем производства материалов не может быть отрицательным, т.е.
в) ограничения на величину спроса на материалы: Таким образом получаем следующую математическую модель задачи: Найти максимум функции: При ограничениях: Подготовка рабочего листа На рабочий лист вводим необходимый текст, данные и формулы:
Пример задачи 5. Определение состава удобрений. Для получения удобрений видов 1 и 2 используются химических вещества А, В, С и D, требования, к содержанию которых в минералах, приведены в таблице 1.
Характеристики и запасы минералов, используемых для производства химических веществ А, В, С и D, указаны в следующей таблице 2.
Цена 1 т удобрения вида 1 равна 200 у.е., а 1 т удобрения вида 2 - 210 у.е. Необходимо максимизировать прибыль от продажи удобрений видов 1 и 2. Выполнение: Математическая модель задачи: Пусть: xA1, xB1, xC1, xD1 – количество химических веществ A, B, C, D, используемых для получения удобрений вида 1. xA2, xB2, xC2, xD2 – количество химических веществ A, B, C, D, используемых для получения удобрений вида 2. Yi, i=1, 2, 3 – количество используемого i-го минерала. Математическая модель данной задачи будет иметь вид: Найти максимум функции: F=200(xA1 + xB1 + xC1 + xD1)+210(xA2 + xB2 + xC2 + xD2)-30y1-40y2-50y3. При ограничениях: А) На состав вида удобрений (таблица 1): Б) На характеристики минералов (таблица 2): Таблица 1 Таблица 2
Подготовка рабочего листа:
Примеры задач о назначениях: Пример задачи 6. Четверо рабочих могут выполнять четыре вида работ. Стоимости су выполнения i-м рабочим j-й работы приведены в таблице. Составить план выполнения работ так, чтобы все работы были выполнены, каждый рабочий был загружен только на одной работе, а суммарная стоимость выполнения работ была минимальной.
Решение. 1. Проверка задачи на сбалансированность. Задача является сбалансированной, т.к. количество рабочих соответствует числу возможных работ. В случае несбалансированности задачи необходимо ввести недостающее число фиктивных рабочих (строчек) или работ (столбцов). 2. Построение математической модели задачи. Пусть xij=1, случае выполнения i-м рабочим j-й работы, и xij=0 - в случае невыполнения работы. Тогда математическая модель задачи примет вид: найти минимум функционала при следующих ограничениях: 3. Решение задачи с помощью надстройки Поиск решения . Подготовка рабочего листа может быть произведена в соответствии с рисунком
Пример решения транспортной задачи: Пример задачи 7. Производство продукции осуществляется на 4-х предприятиях, а затем развозится в 5 пунктов потребления. Предприятия могут выпускать в день 200, 150, 225 и 175 единиц продукции. Пункты потребления готовы принимать ежедневно 100, 200, 50, 250 и 150 единиц продукции Стоимость перевозки единицы продукции с предприятий в пункты потребления приведена в таблице:
Необходимо минимизировать суммарные транспортные расходы по перевозке продукции. Решение: 1. Проверка сбалансированности модели задачи. Модель является сбалансированной, т.к. суммарный объем производимой продукции в день равен суммарному объему потребности в ней: 200+150+225+175=100+200+50+250+150. Поэтому при решении этой задачи не учитываются издержки, связанные со складированием и недопоставкой продукции. 2. Построение математической модели. Объемы перевозок - это неизвестные задачи. Пусть хij - объем перевозок с i-го предприятия в j-й пункт потребления. Суммарные транспортные расходы - это критерий цели: Неизвестные в данной задаче должны удовлетворять следующим ограничениям: объемы перевозок не могут быть отрицательными; т.к. модель сбалансирована, то вся продукция должна быть вывезена с предприятий, а потребности всех пунктов потребления должны быть полностью удовлетворены.
Постановка задачи Директор магазина должен составитьштатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Общий месячный фонд зарплаты составляет $ 15000. Построим модель решения этой задачи. Поясним, что является исходными данными. Казалось бы, что ничего не дано, кроме общего фонда заработной платы. Однако директору магазина известно больше: он знает, что для нормальной работы магазина нужно: 3 уборщицы; 2-4 грузчика; 2 товароведа; 1 секретарь; 5-10 сервисная служба; 8-10 продавцы-консультанты; 1 бухгалтер; 1 директор; На некоторых должностях число людей может меняться. Например, зная, что найти работников сервисной службы трудно, руководитель может принять решение сократить их число, чтобы увеличить оклад каждого из них. Итак, директор принимает для себя следующую модель задачи. За основу берется оклад уборщицы, а все остальные вычисляются через нее: во столько раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада уборщицы: А*С+В, где С - оклад уборщицы; А и В - коэффициенты, которые для каждой должности определяются решением совета трудового коллектива. Допустим, совет решил, что
грузчик должен получать в 1, 5 раз больше уборщицы товаровед - в 3 раза больше уборщицы сервисная служба - на 30 $ больше, чем товаровед секретарь - в 2 раза больше уборщицы бухгалтер - на 40 $ больше грузчика продавец-консультант - в 4 раза больше уборщицы директор - на 20 $ больше продавца-консультанта А=1, 5 В=0 А=3 В=0 А=3 В=30 А=2 В=0 А=1, 5 В=40 А=4 В=0 А=4 В=20 Задав количество человек на каждой должности, можно составить уравнение: N1*(A1*C+B1)+N2*(A2*C+B2)+.....+N8*(A8*C8+B8)=8000, где N1 - количество уборщиц; N2 - количество грузчиков и т.д. А1...А8 и В1...В8 - коэффициенты для каждой должности. В этом уравнении нам известны А1...А8 и В1...В8, а не известны С и N1...N8. Популярное:
|
Последнее изменение этой страницы: 2016-05-03; Просмотров: 828; Нарушение авторского права страницы
Внесите изменения на одном из листов книги. Проверьте, изменилось ли значение в консолидированной таблице.
Укажите, начиная с какой ячейки текущего листа должна располагаться сводная таблица.
, а затем щелкните Параметры Excel. 
Цена детских билетов постоянна и равна 6000
Пример задачи 4. Фирма выпускает два типа строительных материалов: А и В. Продукция обоих видов поступает в продажу. Для производства материалов используются два исходных продукта I и II. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн соответственно. Расходы продуктов I и II на 1 тонну соответствующих материалов приведены в таблице
.
б) расход исходного продукта для производства обоих видов материалов не может превосходить максимально возможного заданного исходного продукта, т.е. 
.
Работа с надстройкой Поиск Решения – вызовите окно «Поиск решения» (см. в теории).
Работа с надстройкой Поиск решения 
Установка ограничений в окне Поиск решения (в окне Параметры поиска решения необходимо также установить флажок Линейная модель) и результат поиска решенияприведены на рисунке
, где сij— стоимость перевозки единицы продукции с i-го предприятия в j-й пункт потребления.
3. Решение задачи с помощью окна Поиск решения. Подготовка листа рабочей книги.
