Линейное программирование, форматирование таблиц ms excel, статистический анализ функций, исследование сложных функций, диаграммы, решения

Реферат

Курсовой проект: 34 страниц, 40 рисунков, 2 приложения

ЛИНЕЙНОЕ ПРОГРАММИРОВАНИЕ, ФОРМАТИРОВАНИЕ ТАБЛИЦ MS EXCEL, СТАТИСТИЧЕСКИЙ АНАЛИЗ ФУНКЦИЙ, ИССЛЕДОВАНИЕ СЛОЖНЫХ ФУНКЦИЙ, ДИАГРАММЫ, РЕШЕНИЯ


В данном курсовом проекте рассматриваются таблицы MS Excel, приведены способы ввода формул в таблицы, форматирования таблиц при помощи инструментов MS Excel, подготовки к печати отчетов на основе таблиц. Рассматривается создание и редактирование  графиков различного вида на основе данных таблиц, а также графиков на основе сложных функций.




































Содержание
      1. Введение03
      2. Нормативные ссылки04
      3. Основная часть05
3.1 Задание №105
3.2 Задание №2011
3.3 Задание №3024
3.4 Задание №4030
     4. Заключение033
     5. Список использованных источников034










































1. Введение
Данная курсовая работа направлена на углубление знаний в области исследования сложных функций методами статистического анализа, прогнозирования и решения задач линейного программирования при помощи программы для работы с электронными таблицами – MS Excel. Произведены исследования в области составления и редактирования электронных таблиц с формулами, составления графиков и диаграмм на основе этих таблиц.













































2. Нормативные ссылки

В данной пояснительной записке использованы ссылки на следующие нормативные документы:
ГОСТ Р 7.0.5-2008 СИБИД. Библиографическая ссылка. Общие требования и правила составления
ГОСТ 2.104-2006 ЕСКД. Основные надписи
ГОСТ 2.301-68 ЕСКД. Форматы
ГОСТ 7.9-95 СИБИД. Реферат и аннотация. Общие требования
ГОСТ 7.12-93 СИБИД. Библиографическая запись. Сокращение слов на русском языке. Общие требования и правила
СТП КубГТУ 4.2.6-2004 СМК. Учебно-организационная деятельность. Курсовое проектирование
СТП КубГТУ 4.2.8-2002 СМК. Учебно-организационная деятельность. Дипломное проектирование
МР КубГТУ 4.4.3-2012 СМК. Учебно-методическая деятельность. Выпускные квалификационные работы
МР КубГТУ 4.4.4-2008 СМК. Учебно-методическая деятельность. Учебные издания


































3. Основная часть
3.1 Задание №1
Разработаем таблицу на основе образца, данного в задании (Рис.1),

Рис.1.
затем изменим ширину ее ячеек, выделив их и выбрав кнопку «Формат» во вкладке «Главная», в группе «Ячейки» (Рис.2).

Рис.2.
Выберем пункт «Ширина столбца» и в открывшемся окне введем нужную нам ширину для каждого столбца. Также введем начальные значения в столбец
«Розничная цена,руб».
Теперь введем формулу в ячейку D3. Формулу можно ввести непосредственно в саму ячейку, а можно выделить ячейку и вписать формулу в строку формул (Рис.3).

Рис.3.
После того, как мы ввели формулу первую формулу, нам нужно, чтобы формулы появились и в остальных ячейках нашей таблицы. Чтобы не писать формулу в каждой ячейке, выделим ячейку, в которой уже есть формула, подведем курсор к правому нижнему углу этой ячейки и вместо курсора увидим значок «+». Растянем границу ячейки как показано на рисунке (Рис.4) и аналогичная формула появится во всех ячейках выделенного столбца.


Рис.4.
Сделаем то же самое со всеми строками нашей таблицы, и во вкладке «Главная» изменим выравнивание текста (Рис.5). Также изменим формат ячеек. Выделим те ячейки, формат которых мы хотим изменить, нажмем правую кнопку мыши и выберем пункт «Формат ячеек» в контекстном меню.

Рис.5.
Скопируем данную таблицу и используем команду «Автоформат» из меню формат, в итоге получим две одинаковые таблицы (Рис.6).

Рис.6.



Подготовим отчет (Прил.1) в виде фирменного бланка. Вставим в него картинку (логотип фирмы), нажав на вкладку «Вставка», затем «Рисунки», выберем нужную картинку. Добавим текст слева от картинки и в нижний колонтитул (Прил.1).
.
















АО «Трианон»





г.Краснодар,





ул. Станционная, 30а




тел. (383) 255-11-53

































Наименование товара
Розничная цена, руб
От 10 шт.
От 100 шт.
Свыше 1000 шт.
Диллеры
1. Калейдоскоп
150,00
148,50
147,02
145,54
144,09
2. Мини-пазл <<Цветок>>
80,00
79,20
78,41
77,62
76,85
3. Кукла <<Барби>>
600,00
594,00
588,06
582,18
576,36
4. Пазл <<Золотая рыбка>>
350,00
346,50
343,04
339,60
336,21
5. Конструктор <<Лего>>
1200,00
1188,00
1176,12
1164,36
1152,72
6. Пазл <<Принцесса>>
320,00
316,80
313,63
310,50
307,39
7. Кукла <<Кен>>
570,00
564,30
558,66
553,07
547,54
8. Мелки цветные
50,00
49,50
49,01
48,51
48,03
9. Мыльные пузыри
25,00
24,75
24,50
24,26
24,01
10. Плюшевый медведь
800,00
792,00
784,08
776,24
768,48






















Прил.1.






Контрольные вопросы
Чтобы выделить ячейку, нужно нажать на нее левой кнопкой мыши, а чтобы выделить блок ячеек, строку или столбец, нужно выделить одну ячейку, принадлежащую нужной выделяемой области, а затем растянуть границу выделения так, чтобы она захватила все необходимые ячейки.
Чтобы скопировать данные таблицы с одного листа на другой, необходимо выделить все эти данные, нажать комбинацию клавиш «Ctrl+C», открыть тот рабочий лист, куда их нужно вставить, выделить область, куда нужно вставить данные, и нажать комбинацию клавиш «Ctrl+V».
Чтобы присвоить ячейке формат, необходимо нажать на ее правой кнопкой мыши и в контекстном меню выбрать пункт «Формат ячеек» и выбрать необходимый. Виды форматов:
Числовой
Денежный
Финансовый
Дата
Время
Процентный
Дробный
Экспоненциальный
Текстовый
Дополнительный
Чтобы ввести формулу в ячейку нужно нажать на эту ячейку и ввести в нее формулу или нажать на ячейку и ввести формулу в строку формул. Редактировать формулы можно также в строке формул, либо в самой ячейке. Для редактирования формулы в ячейке требуется нажать на нужную ячейку дважды.
Относительная адресация – это запись адреса ячейки в таком виде, при котором значение ее столбца или строки в формуле будет изменяться при копировании формулы в другую ячейку. Такой вид адресации используется в Excel по умолчанию. Пример относительной адресации: A1.
Абсолютная адресация отличается от относительной тем, что при переносе формулы в другую ячейку, адрес ячейки, использованной в этой формуле остается неизменным. Пример относительной адресации: $A$1.
Чтобы отформатировать таблицу в Excel, нужно выделить эту таблицу, нажать правую кнопку мыши и в контекстном меню выбрать пункт «Формат ячеек».
Чтобы подготовить отчет для печати необходимо нажать комбинацию клавиш Ctrl+P.
Чтобы внедрить логотип на рабочем листе Excel необходимо во вкладке «Вставка» на панели «Иллюстрации» выбрать необходимый тип иллюстрации (рисунки/изображения из интернета/фигуры), а затем добавить ее в документ.


















3.2 Задание № 2
Для создания диаграммы необходимо выделить те ячейки, по значениям которых будет строиться диаграммам, а затем во вкладке «Вставка» на панели «Диаграммы» выбрать необходимый тип диаграммы (Рис.7).


Рис.7.

Рассмотрим создание таких диаграмм в MS Excel на примере круговой (Рис.8).







Рис.8.

Мы видим, что на диаграмме нет названия диаграммы и подписей легенд. Сначала назовем данную диаграмму. Для этого необходимо щелкнуть по полю «Название диаграммы» и ввести туда желаемое название.
Также мы можем изменить стиль диаграммы и расположить легенды не снизу, а слева от диаграммы. Чтобы изменить стиль диаграммы, нажимаем на соответствующий значок (Рис.9), находящийся справа от диаграммы.

Рис.9.
Выберем из списка подходящий стиль и круговая диаграмма приобретет более привлекательный вид (Рис.10),


Рис.10.



Чтобы подписать категории, необходимо щелкнуть по ним и в контекстном меню выбрать пункт «Выбрать данные». После появления нового диалогового окна (Рис.11), нажать кнопку «Изменить» (в блоке подписей горизонтальной оси),



Рис.11.
И в следующее диалоговое окно (Рис.12) ввести диапазон таблицы, в котором содержатся подписи к категориям. В данном случае подписи содержатся в ячейках A17 – A26.


Рис.12.
В итоге мы имеем диаграмму с отформатированными категориями, расположением категорий, стилем и названием (Рис.13)


Рис.13.
Чтобы создать комбинированную диаграмму необходимо выделить интересующие нас данные в таблице и выбрать на вкладке «Главная» на панели «Диаграммы» один из видов комбинированных диаграмм (Рис.14). Выберем для этой гистограмму с группировкой и графиком на вспомогательной оси т.к. она предназначена для значений, имеющих большой разброс, как в нашем случае


Рис.14.
В итоге получаем неотформатированную гистограмму следующего вида (Рис.15).

Рис.15.

Подпишем категории, по уже пройденной схеме (нажатие правой кнопки мыши > «Выбрать данные» > «Изменить») , как в случае с первой диаграммой (Рис.16),


Рис.16.

Добавим название гистограммы. Затем изменим цвет столбцов и графика на панели «Формат ряда данных» в подразделе «Заливка», раздела «Параметры ряда», предварительно нажав на нужный элемент диаграммы. Вначале изменим цвет столбцов, отвечающих за цены на цветные мелки (Рис.17). Из предложенных цветов выберем устраивающий нас, либо выберем пункт «Другие цвета» для более широкого выбора.


Рис.17.
Итак, другой цвет выбран, диаграмма изменилась, но все еще остается непонятным значение числовых единиц, справа и слева от гистограммы (Рис.18). 
Рис. 18.
Зафиксируем значение этих чисел при помощи добавления названий осей в нашу гистограмму, для этого нажмем на значок «+», находящийся справа от последней и поставим галочку напротив пункта «Названия осей» (Рис.19).

Рис.19.

Теперь гистограмма выглядит достаточно подробно и наглядно (Рис.20).

Рис.20.

С помощью MS Excel по аналогичной схеме можно создать множество диаграмм другого типа, изменяя тип диаграммы, значения используемых данных, стили и все, что форматировалось выше, на примере круговой диаграммы и гистограммы. Создадим ряд других диаграмм, показывающий многообразие выбора данных элементов и                  способа их форматирования (Рис.21, Рис.22). 
Рис 21.
                                                      Рис.22.




















Контрольные вопросы
Ряд данных – это элементы легенды. Например, ряд значений легенды y.
Категории – подписи горизонтальной оси. Например, категория оси - x.
Легенда – это название оси, на которой размещаются ряды данных. Например, такая ось может называться y, т.е. ее легенда – y.
На круговой диаграмме может быть изображен только один ряд данных, т.к. все значения на круговой диаграмме принимаются за 100%, в то время как на кольцевой диаграмме может быть изображено несколько рядов данных в виде вложенных колец.
Чтобы отредактировать на диаграмме название нужно щелкнуть на поле с надписью «Название диаграммы» и заменить этот текст на нужный.
Легенду можно изменить, щелкнув по ней и выбрав в откроющемся меню пункт «Выбрать данные», затем нажать кнопку «Изменить» (в правом блоке).
Название осей x и y, можно изменить, нажав на ось, а затем вместо существующего названия вписать в блок нужное.
Тип диаграммы можно изменить, щелкнув по ней правой кнопкой мыши и выбрав в откроющемся диалоговом окне новый тип диаграммы.
Чтобы добавить в диаграмму новые данные (и не создавать новую таблицу) можно выделить добавляемые элементы таблицы, скопировать их и вставить в диаграмму.
Можно выделить ключевые значения при помощи изменения их цвета, щелкнув по ключевому ряду данных, и на панели «Формат ряда данных» в подразделе «Заливка», раздела «Параметры ряда», выбрать цвет, которым нужно выделить ряд данных.
Чтобы скомбинировать разные типы диаграмм нужно в исходной диаграмме, состоящей из нескольких рядов данных изменить один тип данных на другой, но при этом нужно помнить, что не все виды диаграммы можно комбинировать. Например, нельзя комбинировать плоскую диаграмму с трехмерной.



























3.3 Задание №3
Построим на новом листе таблицу для значений x и y. Пусть начальное значение x будет равно пяти. Зная, что переменная изменяется с шагом 5 введем в следующую ячейку соответствующую формулу (Рис.23).


Рис.23.

Размножим данную формулу во все ячейки таблицы и введем в строку значений y формулу, данную в задании (Рис.24).

Рис.24.

Вторую формулу также размножим во всю строку (Рис.25).

Рис.25.

Добавим ячейки с минимальным, максимальным и средним значением y. Напишем формулу в строку среднего значения (Рис.26).


Рис.26.

Затем добавим следующие формулы в строки минимального (Рис.27) и максимального (Рис.28) значения



Рис.27.



Рис.28.
В итоге получаем вот такую таблицу значений (Рис.29).

Рис.29.

Следующим шагом будет создание формулы для строки суммы или произведения (Рис.30).

Рис.30.

Затем добавим строку со случайным числом. Выберем небольшое число, в диапазоне от 1 до 10 (Рис.31).





Рис.31.

К первой таблице добавим строку, в которой каждое значение y будет умножаться на случайное число. Запишем формулу в одну из ячеек данной строки (Рис.32), а затем размножим ее.


Рис.32.

Получается таблица такого вида (Рис.33).

Рис.33.

Теперь перейдем к построению графика функции. Выделим ряд значений x и y и построим по ним график. Подпишем оси и введем название данного графика (Рис.34).

Рис.34.
Нам необходимо построить график, используя значение y, умноженное на случайное число. Построим график зависимости y*случайное число(х), скопировав строку со значениями y*случайное число вместе со строкой x, и вставив их в график, подпишем вставляемые ряды (Рис.35).

Рис.35.














Контрольные вопросы
Пакет анализа – это ряд дополнительных возможностей в Excel, расширяющих возможности анализа формул. Пакет анализа позволяет строить аналитические гистограммы, составлять таблицы, делать разного вида выборки данных и находить их статистические характеристики.
Чтобы воспользоваться инструментами анлиза нужно в меню «Сервис» выбрать команду «Анализ данных», и среди предложенного списка выбрать нужный вид анализа.
Коэффициент вариации — это отношение отклонения к средней арифметической.
Составим две таблицы, рассчитывающие стандартное отклонение для y(x) и y*случайное число. Для этого в ячейки напротив стандартного отклонения, средней арифметической и коэффициента вариации запишем формулы соответствующих параметров (Рис.36.).

Рис.36.

Особенность построения гистограммы распределения данных заключается в том, что она строится на основе двух рядов данных из выбираемого диапазона  –  «Карманов» и «Частот».
=ЕСЛИ(ИЛИ(И(B6>=0;И(B7>=0;B8>=0));И(B6<=0;И(B7<=0;B8<=0))); ”Вычислена сумма”;”Вычислено произведение”))













3.4. Задание №4
Создадим таблицу, на основе предложенной, добавив к каждому коэффициенту номер варианта т.е. семь и заполнив столбец «Формула» формулами (Прил.2.).


A
B
C
D
E
F
G
H
1
Переменная
X1
X2
X3
X4
Формула
Знак
Св.член
2
Значение
10
0
6
0



3
Коэф. ЦФ
67
77
127
137
=СУММПРОИЗВ
(В$2:Е$2;В3:Е3)
max

4
Трудовые
1
1
1
1
=СУММПРОИЗВ
(В$2:Е$2;В4:Е4)
<=
16
5
Сырьевые
6
5
4
1
=СУММПРОИЗВ
(В$2:Е$2;В5:Е5)
<=
110
6
Финансы
4
6
10
13
=СУММПРОИЗВ
(В$2:Е$2;В6:Е6)
<=
100
Прил.2.

Создадим эту таблицу на новом листе таблиц Excel и запишем в ячейки F3-F6 формулы: =СУММПРОИЗВ(В$2:Е$2;В3:Е3); =СУММПРОИЗВ(В$2:Е$2;В4:Е4); =СУММПРОИЗВ(В$2:Е$2;В5:Е5); =СУММПРОИЗВ(В$2:Е$2;В6:Е6) (Рис.36).

Рис.37.

А теперь создадим сценарий. Для этого во вкладке «Данные» в пункте «Работа с данными» нажмем кнопку «Анализ «что если»» и выберем строку «Диспетчер сценариев» (Рис.37).


Рис.38.

Создадим новый сценарий с изменяющимися данными в ячейках B2-E2 и назовем его «Сценарий 1» (Рис.38).

Рис.39.

Во вкладке «Данные» в разделе «Анализ» нажмем кнопку «Поиск решений», введем изменяющиеся ячейки переменных, целевую функцию (или просто выделим курсором эти данные в таблице). Также нам следует указать ограничение значений для некоторых ячеек (Рис.39)



Рис.40.
Затем нажмем кнопку «Найти решение» и сохраним его.















4.Заключение
В данной курсовой работе были проанализированы и изучены возможности MS Excel. Эта программа имеет широкий диапазон применения как простых так и трудных задач с применением сложных функций. Оказывается, с помощью MS Excel мы можем не только пользоваться огромным спектром графических редактируемых данных в виде построения графиков различных видов. Мы имеем возможность пользоваться таблицами в качестве не только пространства хранения информации, но и ее обработки: пользоваться функциями разнотипного анализа данных на основе линейного программирования. Ход работы включил в себя рассмотрение и подробный разбор всех вышеописанных функциий и возможностей MS Excel.









































5.Список использованных источников
https://ru.wikipedia.org/wiki/Microsoft_Excel
https://ru.wikipedia.org/wiki/%D0%A1%D0%B2%D0%BE%D0%B4%D0%BD%D0%B0%D1%8F_%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D0%B0
https://support.office.com/ru-ru/article/%D0%9E%D0%B1%D1%89%D0%B8%D0%B5-%D1%81%D0%B2%D0%B5%D0%B4%D0%B5%D0%BD%D0%B8%D1%8F-%D0%BE-%D1%84%D0%BE%D1%80%D0%BC%D1%83%D0%BB%D0%B0%D1%85-%D0%B2-Excel-ecfdc708-9162-49e8-b993-c311f47ca173
https://support.office.com/ru-ru/article/%D0%9A%D1%80%D0%B0%D1%82%D0%BA%D0%BE%D0%B5-%D1%80%D1%83%D0%BA%D0%BE%D0%B2%D0%BE%D0%B4%D1%81%D1%82%D0%B2%D0%BE-%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5-%D1%84%D0%BE%D1%80%D0%BC%D1%83%D0%BB%D1%8B-f6e759f1-bcfd-4f29-99c4-c771cc5743b4
http://www.kgafk.ru/www/kgufk.nsf/html/exceln2.html!OpenDocument&Click=
http://excel2010.ru/kategorii-vstroennyx-funkcij-excel.html
http://excel2.ru/articles/funkciya-sluchmezhdu-sluchaynoe-chislo-iz-zadannogo-intervala-v-ms-excel-sluchmezhdu
http://metallovedeniye.ru/analiz-dannyx-v-excel/postroenie-gistogramm-raspredeleniya-v-excel.html



2


.......................