На бирже курсовых и дипломных проектов можно найти образцы готовых работ или получить помощь в написании уникальных курсовых работ, дипломов, лабораторных работ, контрольных работ, диссертаций, рефератов. Так же вы мажете самостоятельно повысить уникальность своей работы для прохождения проверки на плагиат всего за несколько минут.

ЛИЧНЫЙ КАБИНЕТ 

 

Здравствуйте гость!

 

Логин:

Пароль:

 

Запомнить

 

 

Забыли пароль? Регистрация

Повышение уникальности

Предлагаем нашим посетителям воспользоваться бесплатным программным обеспечением «StudentHelp», которое позволит вам всего за несколько минут, выполнить повышение уникальности любого файла в формате MS Word. После такого повышения уникальности, ваша работа легко пройдете проверку в системах антиплагиат вуз, antiplagiat.ru, etxt.ru или advego.ru. Программа «StudentHelp» работает по уникальной технологии и при повышении уникальности не вставляет в текст скрытых символов, и даже если препод скопирует текст в блокнот – не увидит ни каких отличий от текста в Word файле.

Результат поиска


Наименование:


курсовая работа Microsoft Excel

Информация:

Тип работы: курсовая работа. Добавлен: 03.07.2012. Сдан: 2011. Страниц: 6. Уникальность по antiplagiat.ru: < 30%

Описание (план):


Содержание 

     Введение 3
    1. Построение  графика функций 4
    2. Выбор метода  решения системы линейных уравнений 8
    2.1 Использование  статистических функций для решения  системы линейных уравнений 10
    3. Решение  Задач оптимизации с помощь  надстройки Поиск решения 12
     3.1 Построение  математической модели задач 14
     3.2 Использование  команды Поиск решения в решении  задания 3 17
     3.3 Анализ  отчета по результатам, устойчивости, пределам 20
    4. Листы Microsoft Excel с исходными данными, формулами,  диаграммами и полученными результатами решения 21
     Заключение 30
     Литература 30 

 

Введение 

      Microsoft Excel – ведущая программа обработки электронных таблиц. Первая версия MS Excel появилась в 1985 году и обеспечивала только простые арифметические операции в строку или в столбец. В 1993 году вышла пятая версия Excel, ставшая первым приложением Microsoft Office. Приложение включало язык Visual Basic for Application (VBA), основанный на языке Microsoft Visual Basic. Начиная с Office 97 Microsoft включает VBA во все приложения Microsoft Office.
      В настоящее время MS Excel представляет собой мощное и гибкое средство разработки информационных систем, содержащее в  себя как электронные таблицы (со средствами финансового и статистического  анализа, набором стандартных математических функций, доступными в компьютерных языках высокого уровня, рядом дополнительных функций, встречающихся только в библиотеках дорогостоящих инженерных программ), так и средства визуального программирования (Visual Basic for Applications). Электронные таблицы позволяют производить обработку чисел и текста, задать формулы и функции для автоматического выполнения, прогнозировать бюджет на основе сценария, представлять данные в виде диаграмм, публиковать рабочие листы и диаграммы в Web. С помощью VBA можно автоматизировать всю работу, начиная от сбора информации, ее обработки до создания игровой документации, как офисного пользования, так и на Web-узле.
      В данной курсовом проекте будут рассмотрены  вопросы построения графиков функций  с использованием мастера диаграмм, решение системы линейных уравнений с использование мастера функций, а также решение оптимизационной задачи средствами надстройки Поиск решения электронной таблицы Excel.
 

1. Построение графика  функций 

      При построении графиков математических функций  значения функций задаются в конечном числе точек. Если просто соединить эти точки отрезками прямых линий, то полученная ломаная совсем не будет похожа на истинный график. Если начать увеличивать число точек, в которых вычисляются значения функции, то это потребует введения большого количества данных и построение графика потеряет наглядность.
      При построении графиков функций предполагается следующие действия:
    В ячейки А5,А44:А45 введем названия тех функций, графики которых будем строить. В дальнейшем они составят текст легенды.
    В строки 4 и 43 введем значения независимой переменной t с шагом 0.2 в промежутке [-2, 2]. Если ввести в две соседние ячейки значения -2 и -1.8, затем выделив их в правом нижнем углу появится крестик. Наведем на него курсор мыши который в результате примет указатель в виде крестика. Нажав левую кнопку мыши и удерживая тянем в правую сторону. В результате получается интервал с шагом 0.2.
    В 5, 44 и 45 строках введите значения функций в соответствующих точках. Технически это делается следующим образом: в ячейку B5 вводится формула =LN(1/(2+2*B4+B4*B4)), в ячейку B5 вводится формула =$B$42*КОРЕНЬ(B43*B43+4), в ячейку B5 вводится формула =($B$42*SIN(R43))/LN(1+R43*R43). После этого этого формулы переносятся из данных ячеек в остальные.
    После этого выполняем команду Вставка ? Диаграмма. В результате на данном листе появляется Мастер диаграмм Шаг1 из 4 который имеет вид как показано на рисунке 1. Выбираем тип диаграммы, вид и нажимаем кнопку Далее.
    На втором шаге выделяем диапазон ячеек на основании которых будет строится график. Вид Шага 2 показан на рисунке 2.

Рисунок 1. Диалоговое окно «Мастер диаграмм Шаг 1 из 4» 


Рисунок 2. Диалоговое окно «Мастер диаграмм Шаг 2 из 4» 

       Как только буден введен диапазон Диалоговое окно «Мастер диаграмм Шаг 2 из 4»  примет вид как показано на рисунке 3

Рисунок 3 Диалоговое окно «Мастер диаграмм Шаг 2 из 4»
      На  данном этапе будет показан предварительный  вид диаграммы. После этого нажимаем кнопку Далее.
    На 3 шаге как показано на Рисунке 4 предлагается ввести названия

Рисунок 4 Диалоговое окно «Мастер диаграмм Шаг 3 из 4»
диаграмм, название осей координат. Так же на этом шаге предлагается возможность изменения линий сетки, легенды, таблицы данных, подписи данных и оси координат. После ввода всех корректировок нажимаем кнопку Далее.
    На 4 шаге предлагается выбрать где будет размещаться данная диаграмма: на отдельном листе или на новом. Данное диалоговое окно 4 шага представлена на Рисунке 5. После этого нажимаем кнопку Готово.

Рисунок 5 Диалоговое окно «Мастер диаграмм Шаг 4 из 4» 

      В результате, если было выбрано размещение диаграммы на имеющемся листе, то на данном листе будет вставлена  диаграмма.
      Если  диаграмма построена, это не значит что ее нельзя редактировать. При двойном нажатии кнопки мыши на тексте названия откроется окно диалога форматирования название диаграммы. В результате на вкладке шрифт можно установить вид шрифта, стиль и размер. Также двойным щелчком можно редактировать легенду, оси координат, линии графика.
      Таблицы с данными и построенные графики функции с помощью MS Excel указаны в разделе 4 «Листы Microsoft Excel с исходными данными, формулами, диаграммами и полученными результатами решения».
 

2. Выбор метода решения системы линейных уравнений 

      Уравнение с одной независимой переменной, описывающее прямую линию, имеет вид
y=mx+b,
      где x – независимая переменная;
          y – зависимая переменная;
          m – характеристика наклона прямой;
          b – точка пересечения прямой с осью Y.
      В случае множественной регрессии  имеется несколько независимых  переменных и уравнение прямой становится многомерным:
y=m1x1 + m2x2 + … mnxn + b,
      где x1, x2, …, xn – независимые переменные;
          y – зависимая переменная;
          m1, m2, …, mn – соответствующие коэффициенты;
          b – константа.
      Функция
= ЛИНЕЙН(известные_x; известные_y; конст; стат)
вычисляет коэффициенты m1, m2, …, mn и значение b  по известным множествам значений y и соответствующим значениям каждой независимой переменной.
      Аргумент  известные_y задает множество значений y, которые могут располагаться в одном столбце, в одной строке или в прямоугольном интервале ячеек. Если известные_y заданы в виде одного столбца (одной строки), то каждый столбец (соответственно, каждая строка) массива известные_x задает значения независимой переменной. Если же аргумент известные_y задает прямоугольный интервал, то предпологается, что имеется только одна независимая переменная, при этом значения аргумента известные_x должны быть организованы тоже в виде интервала того же размера и формы, что и известные_y.
      При опущенном аргументе известные_x Excel использует последовательность 1; 2; 3; 4 и т.д.
      Аргумент  конст и стат необязательны и по умолчанию считаются равными логическим значениям ИСТИНА и ЛОЖЬ соответственно. Вместо логических значений ИСТИНА и ЛОЖЬ можно использовать числа 1 и 0. Если аргумент конст имеет значение ЛОЖЬ, то пологается, что параметр b=0. При значении аргумента стат, равном ИСТИНА, возвращаемый массив включает следующие контрольные статистические параметры:
          se1, …, sen – значения стандартной погрешности для каждого коэффициента уравнения
          seb – значения стандартной погрешности для константы b
          r2 – коэффициент детерминирования;
          b – константа.
          sey – значения стандартной погрешности для y
          FF-статистика
          df – число степеней свободы
          ssreg – сумма квадратов регрессии
          seresid – остаточная сумма квадратов
      Перед вводом формулы с функцией ЛИНЕЙН() нужно выделить интервал ячеек, соответствующий размеру возвращаемого ею массива.
      Если  аргумент стат опущен (или имеет значение ЛОЖЬ), то результирующий интервал массива содержит по одной ячейке для каждой независимой переменной и одну ячейку для b. Если же включены контрольные статистические параметры, то результирующий интервал массива будет выглядеть следующим образом:
mn mn-1 m2 m1 b
sen sen-1 se2 se1 seb
r2 sey        
F df        
ssreg seresid        
      Это прямоугольный интервал из n+1 столбцов и 5 строк, в котором блок в правом нижнем углу не используется и заполняется значениями ошибки #Н/Д.
      Выделив нужный интервал, следует напечатать формулу обращения к функции  и нажать Ctrl+Shift+Enter, чтобы ввести ее как табличную формулу. 
 

2.1 Использование статистических функций для решения системы линейных уравнений 

      Для решение системы уравнения в  Задании 2 будем использовать ЛИНЕЙН функцию так как она наиболее подходит для решения уравнений с несколькими независимыми переменными. Перед вводом формулы с функцией ЛИНЕЙН () нужно выделить интервал ячеек, соответствующий размеру возвращаемого ею массива. Затем выполняем команду Вставка ? Функция. В результате появляется диалоговое окно «Мастер функций Шаг 1 из 2», который имеет вид на рисунке 6.

Рисунок 6 Диалоговое окно «Мастер функций Шаг 1 из 2»
      В окне «категория» выбираем Статистические, а в окне «выберите функцию» выбираем ЛИНЕЙН. Затем нажимаем ОК. В результате появляется новое диалоговое окно «Аргументы функции» которое имеет вид на рисунке 7. В этом диалоговом окне нам необходимо заполнить следующие поля: «известные_значения_y», «известные_значения_x», «конст», «статистика». В поле известные_значения_y вводим диапазон ячеек которые соответствуют значениям y, это диапазон ячеек D12:D14. В поле известные_значения_x вводим диапазон ячеек которые соответствуют значениям m, это диапазон ячеек A12:C14. В поле конст устанавливаем значение ЛОЖЬ так, как у нас аргумент b=0 и нам надо найти не контрольные статистические параметры а значения x1, x2, x3.

Рисунок 7 Диалоговое окно «Аргументы функций» 

      После ввода всех данных нажимаем Ctrl+Shift+Enter чтобы ввести формулу как табличную. В результате вычисления получаем значения независимых переменных x1, x2, x3.
      Решение системы линейных уравнений с  помощью MS Excel представлены в разделе 4 «Листы Microsoft Excel с исходными данными, формулами, диаграммами и полученными результатами решения».
 

3. Решение Задач оптимизации с помощь надстройки Поиск решения 

       Многие  проблемы производства, проектирования, прогнозирования сводятся к широкому классу задач оптимизации, для решения которых применяются математические методы. Типовыми задачами такого плана являются, например, следующие:
      Ассортимент продукции. Максимизация выпуска товаров при ограничениях на сырье для производства этих товаров.
      Штатное расписание. Составление штанного расписания для достижения наилучших результатов при наименьших расходах.
      Планирование перевозок. Минимизация затрат на транспортировку товаров.
      Составление смеси. Достижение заданного качества смеси при наименьших расходах.
      Размер емкости. Определение размеров некоторой емкости с учетом стоимости материала для достижения максимального объема.
      Случайные величины. Разнообразные задачи, в которые входят случайные величины.
      Разнообразные задачи оптимального распределения ресурсов и оптимального проектирования и т.д.
       В MS Excel существует возможность с помощью  надстройки Поиск решения найти решение, оптимальное в некотором смысле при некоторых входных значениях и наборе ограничений на решение. Диспетчер сценариев может запомнить несколько решений, найденных данным средством и сгенерировать на этой основе отчет. С помощью надстройки Поиск решения можно решать как линейные задачи (задачи линейного, целочисленного и стохастического программирования), так и нелинейные (задачи нелинейного программирования). Окно поиска решения имеет вид как показано на рисунке 8, а опции окна Поиск решения указан в таблице 1

Рисунок 8 Диалоговое окно Окно Поиск решения

Рисунок 9 Диалоговое окно Добавления ограничения
Таблица 1 Опции окна Поиск решения
Название Описание
Установить  целевую ячейку Указывается ячейка, содержащая целевую функцию (критерий оптимизации) рассматриваемой задачи
Равной Следует выбрать  из трех переключателей (максимальному  значению, минимальному значению, значению) тот, который определяет тип взаимосвязи между решением и целевой ячейкой
Изменяя ячейки Указывает ячейки, которые должны изменяться в процессе поиска решения задачи (т.е. ячейки, которые являются переменными задачи)
Ограничения Отображаются ограничения, налагаемые на переменные задачи. Допускаются ограничения в виде равенств, неравенств, а также – требование целочисленности переменных. Ограничения добавляются по одному с помощью кнопки Добавить. Вид меню добавить показан на Рисунке 9
Продолжение таблицы 1
Название Описание
Параметры Позволяют изменять условия и варианты поиска решений  исследуемой задачи, а также загружать  и сохранять оптимизируемые модели. Значения и состояния элементов  управления, используемые по умолчанию, подходят для решения большинства задач.
 
3.1 Построение математической модели задач 

      Работа  по решению некоторой оптимизационной  задачи всегда начинается с построения математической модели, для чего следует  ответить на следующие вопросы:
      каковы переменные модели (для определения каких величин строится модель)?
      в чем состоит цель, для достижения которой из множества всех допустимых значений переменных выбираются оптимальные?
      каким ограничениям должны удовлетворять неизвестные?
      Стоит также учесть, что при конструировании  модели формулировка ограничений является самой ответственной частью конструкции. В некоторых случаях ограничения  очевидны, например, ограничение на количество сырья. Другие же ограничения  могут быть менее очевидны и могут быть указаны неверно. Например:
      в модели с несколькими периодами времени величина материального ресурса на начало следующего периода должна равняться величине этого ресурса на конец предыдущего периода;
      в модели постановок величина запаса на начало периода плюс количество полученного должна равняться величине запаса на конец период плюс количество отправленного;
      многие величины в модели по своему физическому смыслу не могут быть отрицательными, например, количество полученных единиц товара.
      Таким образом, на данном этапе делаются выводы об исходных данных (детерминированные или случайные), искомых переменных (непрерывные или дискретные), о пределах, в которых могут находится значения искомых величин, о зависимостях между переменными (линейные или нелинейные), о критериях, по которым необходимо находить оптимальное решение. Сюда же входит преодоление несовместимости, а также – неограниченности целевой функции: при максимизации целевой функции область допустимых решений должна быть ограничена сверху, при минимизации – ограничена снизу.
      При подготовке рабочего листа MS Excel для решения задачи оптимизации рекомендуется корректно разместить все исходные данные на Рабочем листе, грамотно ввести необходимые формулы для целевой функции и для других зависимостей, выбрать место для значений переменных.
      При решении задачи с помощью надстройки Поиск решения нужно правильно ввести все ограничения, переменные, целевую функцию и другие значения в окно Поиск решения. Большую часть задач оптимизации представляют задачи линейного программирования, т.е. такие, у которых критерий оптимизации и ограничения – линейные функции. Поиск решения может работать также и с нелинейными зависимостями и ограничениями. Это, как правило, задачи нелинейного программирования или, например, решение системы нелинейных уравнений.
      Для успешной работы Поиска решения следует стремиться к тому, чтобы зависимости были гладкими или по крайней мере, непрерывными.
      В результате поиска решения могут  быть получены не только оптимальные  значения параметров (изменяемых ячеек) задачи, но и отчеты трех типов: Результаты, Устойчивость и Пределы. Для создания нужных отчетов выделите их названия в списке Тип отчета диалогового окна Результаты поиска решения, приведенного на рисунке 10, и щелкните кнопку OK. (При выделении нескольких отчетов следует удерживать клавишу Ctrl.) Каждый отчет выводится на от дельном листе текущей рабочей книги.

Рисунок 10 Диалоговое окно Результаты поиска решения 

      В отчете Результаты выводятся значения целевой ячейки, изменяемых ячеек и ограничений. Для каждого ограничения включается также информация о состоянии и невязке. Состояние может быть связанным, не связанным или не удовлетворенное. Невязка есть разница между значением решения в ячейках с ограничениями и правой частью формулы ограничения. Ограничение является связанным, если невязка равна 0. Ограничение является не связанным, если решение удовлетворяет ему при невязке, отличной от 0.
      В отчете типа Устойчивость выводится информация о чувствительности целевой ячейки к изменениям ограничений. Отчет содержит два раздела: Изменяемые ячейки и Ограничения. Данные по каждой изменяемой и ограничиваемой ячейке выводятся на отдельной строке. Правый столбец каждого раздела содержит информацию об устойчивости. В разделе Изменяемые ячейки – это Нормированный градиент, характеризующий изменение значения целевой ячейки при увеличении на единицу значения соответствующей изменяемой ячейки. Аналогично, в разделе Ограничения столбец Множитель Лагранжа характеризует изменение значения целевой ячейки при увеличении на единицу значения соответствующего ограничения.
      При установке флажка Линейная модель в отчет типа Устойчивость включаются дополнительные данные. В разделе Изменяемые ячейки столбец Редуцированная стоимость характеризует изменение целевой ячейки при увеличении на единицу значения изменяемой ячейки. Столбец Целевой коэффициент характеризует корреляцию значений изменяемой и целевой ячеек. Столбцы допустимое увеличение и Допустимое уменьшение характеризует пределы, в которых может изменяться Целевой коэффициент, не затрагивая изменяемые ячейки.
      В разделе Ограничения Столбец  Теневая цена характеризует изменение  значения целевой ячейки при увеличении на единицу значения ограничения. В  столбце Ограничение Правая часть  просто выводятся значения используемых ограничений. Столбцы Допустимое увеличение и Допустимое уменьшение характеризует пределы, в которых может изменяться ограничение (Столбец Ограничение Правая часть), не затрагивая изменяемые ячейки.
      В отчете типа Пределы приводятся оптимальные значения каждой изменяемой ячейки вместе с нижним и верхним пределами ее изменения, при которых не нарушаются ограничения модели. 

3.2 Использование команды Поиск решения в решении задания 3 

      Данные  которые описаны в задании представим с помощью таблицы MS Excel.(Таблица 2) В данной таблице найдено общее количество сырья для выполнения плана по выпуску изделий, а также прибыль от продажи всех изделий.
Таблица 2
  A B C D E F
13 Изделие Количество  сырья на единицу изделия, кг План выпуска  изделий Прибыль Количество сырья для выполнения плана по изготовлению всех изделий Прибыль от продажи  всех изделий
14 М1 3 20 8 60 160
15 М2 6 10 5 60 50
16 ?   30 13 120 210
             
  Количество  пряжи которым обеспечено производство, кг 180        
 
      Итак, выберем команду Поиск решения из меню Сервис – открывается диалоговое окно, показанное на рисунке 11. В поле Установить целевую ячейку устанавливаем адрес ячейки которая используется в качестве критерия в нашем случае количество пряжи Е16.Но так как для выполнения плана нам достаточно 120 кг пряжи, а производство обеспечено 180 кг пряжи, то в графе равной установим переключатель значению и введем в поле справа 180.

Рисунок 11 Диалоговое окно поиск решения с заданными параметрами 

      Следующий шаг заключается в задании  изменяемых ячеек, т.е. ячеек, представляющих параметры модели. В нашем задании такими являются ячейки C14:C15, которые определяют количество выпускаемых изделий. Задать ячейки можно, напечатав их адреса, напечатав имена ячеек или выделив ячейки непосредственно на рабочем листе. Если параметры задаются в несмежных ячейках, то отдельные ячейки или интервалы должны разделяться точкой с запятой. (Выделять несмежные интервалы непосредственно на рабочем листе следует при нажатой клавише Ctrl.) Можно также щелкнуть кнопку Предложить и Excel предложит выбрать изменяемые ячейки на основе заданной целевой ячейки. Обязательно должна быть задана по крайней мере одна изменяемая ячейка, иначе команда Поиск решения выполняться не будет. Изменяемые ячейки являются влияющими по отношению к целевой ячейке, т.е. ячейками, от которых зависит формула в целевой ячейке.
      Последний шаг – задание ограничений  является необязательным. Если ограничения  нужны щелкаем в разделе  Ограничения кнопку Добавить которая имеет вид показанный на рисунке 12
      Каждое  ограничение состоит из трех частей: ячейки, подлежащей ограничению, оператора  сравнения и самого ограничения. Ограничиваемая ячейка задается в поле Ссылка на ячейку (в нашем случае C14), оператор сравнения выбирается в раскрываемом списке в центре диалогового окна (в нашем случае >=), а само ограничение задается в правом поле – в виде константы или ссылки на ячейку рабочего числа (в нашем случаи количество изделий должно быть больше либо равно 20)
и т.д.................


Перейти к полному тексту работы


Скачать работу с онлайн повышением уникальности до 90% по antiplagiat.ru, etxt.ru или advego.ru


Смотреть полный текст работы бесплатно


Смотреть похожие работы


* Примечание. Уникальность работы указана на дату публикации, текущее значение может отличаться от указанного.