Учебно-методический комплекс дисциплины «информационные технологии управления»

Тема 4.3. Решение управленческих задач в Microsoft Excel В жизни мы часто сталкиваемся с необходимостью решать оптимизационные задачи. Например, каждый раз заходя в магазин, мы стоим перед дилеммой максимального удовлетворения наших потре. соизмеряя их с возможностями нашего кошелька. Что же говорить о менеджерах, экономистах, которые постоянно должны решать разнообразные проблемы, начиная от планирования штата сотрудник фонда зарплаты и заканчивая составлением оптимального плана производства, планированием рекламной кампании по продвижению продукции на рынок и оптимизацией капиталовложений. Несмотря на все многообразие этих задач MS Excel предлагает единый, мощный инструмент их решения - Поиск решения. пользователя требуется сформулировать в MS Excel задачу (разработать формулы для расчета оптимизируемого значения), а оптимальное решение он найдет сам. Данный раздел на примерах демонстрирует возможности решения оптимизационных задач. Задача об оптимальном производстве В данном разделе будет продемонстрировано, как при помощи средства Поиск решения решаются линейные оптимизационные задачи на примере задачи о планирование производства. Постановка задачи. Рассмотрим следующую задачу планирования производства. Пусть фирма производит два вида продукции: столы и стулья. Для изготовления одного стула требуется 15 кг. древесины, для одного стола -35 кг. На изготовление одного стула затрачивается 2 час. рабочего времени, на изготовление стола - - 8 час. Каждый приносит прибыль 1000 руб., а стол - 3000 руб. Кроме того, фирма ежедневно может располагать 2400 кг. древесины и 400 час. рабочего времени. Ставится задача нахождения плана выпуска продукции, который обеспечит максимальную прибыль. Для решения этой задачи необходимо с начал а построить математическую модель, списывающую этот процесс производства. Построение модели необходимо начать с ответа на следующие три вопроса: 1. Для определения каких величин строится модель, т является переменными модели; 2.В чем состоит цель, для достижения которой из множества всех значений переменных выбираются оптимальные (определить целевую функцию); 3. Каким ограничениям, должны удовлетворять неизвестные? В нашем случае фирме необходимо спланировать объемы производства столов и стульев так, чтобы максимизировать прибыль. Поэтому в этой задаче неизвестными переменными являются: х1 — объем производства стульев; х2 — объем производства столов. Целевой функцией в этой задаче является суммарная прибыль, которая рассчитывается по формуле: S = 1000 *x1 + 3000 * х2. Из всех возможных значении следует выбрать такие, которые обеспечат наибольшее значение S. Очевидно, что функция S не ограничена, и чем больше фирма будет выпускать стульев и столов, тем больше будет получать прибыль. Однако фирма ограничена запасами древесины и ресурсом рабочего времени. Запишем это в виде формул. Общий расход древесины при плане производства x1 стульев и х2 столов будет составлять 15 * x1 + 35 * х2 и эта величина не должна превышать 2400 кг. Общий расход времени при плане производства x1 стульев и х2 столов будет составлять 2* xl + 8 * х2 и эта величина не должна превышать 400 часов. Кроме того, величины xl и х2 должны быть положительными и целыми. Таким образом, математическая модель данной задачи имеет следующий вид: максимизировать: S = 1000 * xl + 3000 * х2. при ограничениях: x1 ≥ 0, х2 ≥ 0. х1 х2 — целые, 15 * x1 +35 * х2 ≤ 2400, 2* x1 + 8 * х2 ≤ 400 . Заметим, что данная модель является линейной, т. к. целевая функция и ограничения линейно зависят от переменных x1 и х2. Для решения сформулированной задачи на рабочем листе MS Excel разместим исходные данные и формулы для расчета целевой функции и левых частей ограничений. Отведем ячейки B2 и C2 для размещения плана выпуска продукции. На данном этапе решения задачи в ячейки, отведенные под неизвестные, можно ввести любые данные (например, 1). После решения задачи при помощи средства Поиск решения в ячейках B2 и C2 MS будут находиться оптимальные значения. На рис. 4.31 приведены фрагменты рабочего листа с формулами и результатами расчетов по ним. Рис. 4.31. Рабочий лист для расчета прибыли и ограничений. Решение задачи. Поиск решения является одной из надстроек MS Excel. Если в меню Сервис отсутствует команда Поиск решения, для ее установки необходимо выбрать команду Сервис | Надстройки. На экране отобразится диалоговое окно Надстройки. В списке Список надстроек выберите Поиск решения и нажмите кнопку ОК. Если в этом списке нет средства Поиск решения, то его надо сначала инсталлировать с диска Microsoft Office. Для этого необходимо повторно запустить программу установки Microsoft Office и убедиться, что выбран параметр установки надстройки Поиск решения. Для нахождения оптимального плана необходимо: 1. Выбрать команду Сервис | Поиск решения. На экране отобразится диалоговое окно Поиск решения (рис. 4.32). Рис. 4.32. Окно «Поиск решения» и «Добавление ограничений» Окно Поиск решения имеет элементы, перечисленные в табл. 8.3. Таблица 8.3 Элементы окна Поиск решения Для ввода ограничений в поле Ссылка на ячейку вводится левая часть ограничений — B2:C2, в поле Ограничение — правая часть, в нашем случае — 0. Раскрывающийся список позволяет задать тип соотношения. В нашем случае следует выбрать соотношение >=. Таким образом, требование не отрицательности переменных задано. Далее с помощью кнопки Добавить вновь вызывается окно. Добавление ограничения для ввода других ограничений: вторая группа ограничений — D3:D4 <= E3:E4. Аналогично добавляются условия целочисленности неизвестных. Ограничения удобнее задавать не отдельно для каждого неравенства, а в виде диапазонов, как это сделано в разобранном примере. Для установки параметров поиска решения следует нажать кнопку Параметры. На экране отобразится диалоговое окно (рис. 4.33). Рис. 4.33. Окно параметры поиска решения В этом окне можно изменять условия и варианты поиска решения исследуемой задачи. Значения и состояния элементов управления, используемые по умолчанию, подходят для решения большинства задач. В нашем случае установим флажок Линейная модель, а остальные значения, можно не изменять. При нажатии на кнопку ОК на экране опять отобразится окно Поиск решения. 3. Для запуска процесса поиска решения следует нажать кнопку Выполнить. На экране отобразится окно Результаты поиска решения (рис. 4.34). Рис. 4.34. Окно «Результат поиска решения» После нажатия кнопки ОК. результаты будут внесены в рабочий лист (рис. 4.35). Рис. 4.35. Результат поиска решения MS Excel нашел оптимальный план производства, при котором прибыль будет максимальной. Из рисунка видно, что оптимальным является производство в 104 стульев и 24 столов. Этот план выпуска продукции обеспечит наибольшую прибыль — 176 тыс. руб. Транспортная задача Рассмотрим еще один пример применения средства Поиск решения. Пусть Фирма имеет 4 фабрики и 5 магазинов. Фабрики А1, А2, А3, А4 имеют производственные возможности соответственно 200, 150, 225 и 175 единиц продукции ежедневно. Магазины В1, В2, В3, В4 и В5 имеют потребности в 100, 200, 50, 250 и 150 единиц продукции ежедневно. Стоимости перевозки единицы продукции с фабрик в магазины (cij) приведены в табл. 4.3. Таблица 4.3Транспортные расходы Необходимо так спланировать перевозки, чтобы минимизировать суммарные транспортные расходы. Важно отметить, что данная модель сбалансирована, то есть суммарный объем произведенной продукции равен суммарному объему потребностей в ней. В противном случае в модель надо ввести: — - в случае перепроизводства — фиктивный магазин; стоимость перевозок единицы продукции в него полагается равной стоимости складирования, а объемы перевозок в этот пункт равны объемам складирования излишек продукции на фабриках; — - в случае дефицита — фиктивную фабрику; стоимость перевозок единицы продукции с фиктивной фабрики полагается равной стоимости штрафов за недопоставку продукции, а объемы перевозок с этой фабрики равны объемам недопоставок продукции. Для решения данной задачи построим ее математическую модель. Неизвестными здесь являются объемы перевозок. Пусть хij —объем перевозок с i-й фабрики в j-й магазин. Функцией цели являются суммарные транспортные расходы, т. е. S = , где cij — стоимость перевозки единицы продукции с i-й фабрики в j-й магазин. Кроме того, неизвестные должны удовлетворять следующим ограничениям: — - неотрицательность объема перевозок; — - вся продукция должна быть вывезена с фабрик; — - потребность всех магазинов должна быть полностью удовлетворена. Таким образом, мы имеем следующую модель: минимизировать: S = при ограничениях: x ij≥ 0 i 1,..., 4 j 1,=...,5 = , j=1,…,5 = , i=1,…,4 где ai — объем производства на i-й фабрике, bj — спрос в j-м магазине. Для решения транспортной задачи с помощью средства Поискрешения выполним следующую подготовительную работу (рис. 4.36). На рабочем листе введем исходные данные и формулы расчета функции цели и объемов поставок и вывоза продукции. В ячейки B13:F16 введем единичные значения (можно ввести любые). Вызовем команду Сервис|Поиск решения. В окне диалога установим следующие значения (рис. 4.37): Установить целевую ячейку – G26; Равной -минимальному значению; Изменяя ячейки - B13:F16; Ограничения: B13:F16>=0; B8:F8=B17:F17; G4:G7=G13:g6. Рис. 4.36. Расчетные таблица (формулы) для транспортной задачи Рис. 4.37. Установка параметров команды Поиск решения После запуска процедуры поиска решения («Выполнить»), получим решение нашей задачи оптимального плана перевозок (рис. 4.38). Рис. 4.38. Оптимальное решение транспортной задачи Выводы Табличные процессоры представляют возможности использования богатейшей библиотеки встроенных функций, построения диаграмм и графиков, создания простейших аналогов баз данных и наиболее простых функций управления ими (поиск данных, фильтрация, построение сводных таблиц), обработки численных данных, поиска решения оптимизационных задач. В качестве простой базы данных в MS Excel можно использовать список, для которого имеется набор специальных команд: формы, сортировка, промежуточные итоги, фильтрация и сводные таблицы. Для решения оптимизационных задач в MS Excel имеется надстройка «Поиск решения». От пользователя требуется сформулировать в MS Excel задачу: разработать формулы для расчета функции цели и левых частей ограничений и обратиться к этой команде. Раздел 5. ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В ГОСУДАРСТВЕННОМ И МУНИЦИПАЛЬНОМ УПРАВЛЕНИИ Тема 5.1. Введение в СУБД. Основные понятия баз данных. Модели данных. Система управления базами данных Введение в СУБД База данных- совместно используемый набор логически связанных данных (и описание этих данных), предназначенный для удовлетворения информационных потребностей организации. Чтобы глубже вникнуть в суть этого понятия, рассмотрим его определение более внимательно. База данных -это единое, большое хранилище данных, которое однократно определяется, а затем используется одновременно многими пользователями – представителями разных подразделений. Вместо разрозненных файлов с избыточными данными здесь все данные собраны вместе с минимальной долей избыточности. База данных уже не принадлежит какому-либо единственному отделу, а является общим корпоративным ресурсом. Причём база данных хранит не только рабочие данные этой организации, но и их описания. СУБД- программное обеспечение, с помощью которого пользователи могут определять, создавать и поддерживать базу данных, а также осуществлять к ней контролируемый доступ. СУБД- это программное обеспечение, которое взаимодействует с прикладными программами пользователя и с базой данных и обладает перечисленными ниже возможностями: позволяет создать базу данных, что обычно осуществляется с помощью специального языка определения данных (DDL). Язык DDL представляет пользователям средства указания типа данных и их структуры, а также средства задания ограничений для информации, хранимой в базе данных; позволяет вставлять, обновлять, удалять и извлекать информацию из базы данных, что обычно осуществляется с помощью языка манипулирования данными (DML). Наиболее распространенным языком DML является язык структурированных запросов SQL, который в настоящее время фактически является обязательным языком для любых реляционных СУБД; предоставляет контролируемый доступ к базе данных. Концепция баз данных- это не только идея интегрированного хранения данных, но и идея отделения описания данных от программ их обработки. Интерфейс между ними обеспечивается системой управления базами данных (СУБД). По организации и технологии обработки данных базы данных подразделяются на централизованные и распределённые. В централизованной БД все необходимые для работы специалистов данные и СУБД размещены на одном компьютере вместе с приложением обработки данных. Пользователь вводит входную информацию и команды, результаты отображаются на экране терминала пользователя или выводятся на печать. В распределённой БД обычно используется архитектура «клиент-сервер». При такой архитектуре компьютеры объединены в локальную сеть, в которой имеется сервер баз данных, содержащий общие БД. Функции СУБД разделены на две части. Пользовательские программы, такие, как приложения, для формирования интерактивных запросов и генераторы отчётов, работают на клиентском компьютере. Хранение данных и управление ими обеспечиваются сервером. В этой архитектуре SQL стал стандартным языком, предназначенным для обработки и чтения данных, содержащихся в БД. SQL обеспечивает взаимодействие между пользовательскими программами и ядром БД.

Похожие:

	Учебно-методический комплекс дисциплины «информационные технологии управления» Учебно-методический комплекс составлен в соответствии с требованиями государственного стандарта высшего профессионального образования...		Учебно-методический комплекс дисциплины «Финансовые информационные технологии» Учебно-методический комплекс составлен в соответствии с требованиями государственного образовательного стандарта высшего профессионального...
	Учебно-методический комплекс дисциплины «Бухгалтерские информационные технологии» Учебно-методический комплекс составлен в соответствии с требованиями государственного образовательного стандарта высшего профессионального...		Учебно-методический комплекс дисциплины «Бухгалтерские информационные технологии» Учебно-методический комплекс составлен в соответствии с требованиями государственного образовательного стандарта высшего профессионального...
	Учебно-методический комплекс дисциплины «Экономика» Профиль подготовки информационно-измерительная техника и технологии информационные системы и технологии		Учебно-методический комплекс дисциплины «Информационные технологии в налогообложении» Учебно-методический комплекс составлен в соответствии с требованиями федерального государственного образовательного стандарта высшего...
	Учебно-методический комплекс по дисциплине информационные технологии... Рабочей программы учебной дисциплины «информационные технологии в профессиональной деятельности» 4		Учебно-методический комплекс составлен на основании требований государственного... Учебно-методический комплекс дисциплины обсуждена на заседании кафедры Информационные системы управления «29» июня 2011 г
	Учебно-методический комплекс по дисциплине информационные технологии в бухгалтерском учете При разработке учебно – методического комплекса учебной дисциплины в основу положены		Учебно-методический комплекс дисциплины «информационные системы в экономике» Учебно-методический комплекс составлен в соответствии с требованиями государственного образовательного стандарта высшего профессионального...