Освоение возможностей табличного процессора Excel

Содержание

 

Введение……………………………………………………………………..2

Описание программы Microsoft Office 2010……………………………..6

Microsoft  Exсel 2010……………………………………………………….7

Задача 1. Решение оптимальной  производительной программы………8

Задача 2. Решение штатного расписания………………………………..19

Использования возможности  ACCESS………………………………….24

Программа ACCESS……………………………………………………...24

Решение штатного расписания при помощи программы ACCESS…..27

Задача 3.Решение транспортной задачи………………………………...33

Microsoft Power Point……………………………………………………..41

Заключение………………………………………………………………...43

Список использованной литературы…………………………………....44

 

 

 

 

 

 

 

Введение

 

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

Задача достижения экономических  целей приводит к проблеме рационального  использования ограниченных ресурсов (материальных, сырьевых, энергетических, финансовых, трудовых и др.). Для решения  этой проблемы человеку необходимо принимать  определенные решения, как правило, свойственно стремление выбрать  наилучшее для него решение.

Большую часть своих усилий человек тратит на поиск наилучшего т.е. оптимального решения поставленной задачи. Как, располагая определенными ресурсами, добиваться наиболее высокого жизненного уровня, наивысшей производительности труда, наименьших потерь, максимальной прибыли, минимальной затраты времени – так ставятся вопросы, над которыми приходится думать каждому члену общества.

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

Под моделированием понимается процесс  построения, изучения и применения моделей. Оно тесно связано с  такими категориями, как абстракция, аналогия, гипотеза и др. Процесс  моделирования обязательно включает и построение абстракций, и умозаключения  по аналогии, и конструирование научных  гипотез.

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

Целью данного курсового  проекта является практическое освоение возможностей табличного процессора Excel для решения задач экономического и управленческого характера.

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

Модели всех задач на оптимизацию  состоят из следующих элементов:

1. Переменные - неизвестные величины, которые нужно найти при решении  задачи.

2. Целевая функция - величина, которая  зависит от переменных и является  целью, ключевым показателем эффективности  или оптимальности модели.

3. Ограничения - условия, которым  должны удовлетворять переменные.

Наиболее известны следующие  оптимизационные модели:

• модели определения оптимальной производственной программы;
• модели оптимального раскроя;
• модели формирования штатного расписания предприятия;
• модели транспортной задачи и др.

Для решения оптимизационных  задач в Excel используется надстройка Поиск решения.

В курсовом проекте необходимо решить три задачи:

1. определить оптимальную производственную программу;
2. распределить месячный фонд заработной платы организации;
3. решить транспортную задачу;
4. с помощью PowerPoint подготовить презентацию курсового проекта.

Актуальность проблемы:

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

Основные цели, преследуемые при автоматизации любого процесса–   это повышение производительности труда, снижение издержек, повышение качества выпускаемой продукции и повышение безопасности.

Автоматизация управления персоналом позволяет компании решать такие  задачи, как:

• планирование структуры организации, штатного расписания и кадровой политики;
• расчет заработной платы, оперативный учет движения кадров;
• ведение административного документооборота по персоналу и учету труда, аттестациям и определению потребностей (обучение, повышение квалификации) работников;
• рекрутинг персонала на вакантные должности;
• ведение архивов без ограничения сроков давности и многое другое.

 

 

 

Описание программы Microsoft Office 2010

 

Для решения нашей задачи мы использовали пакет программ Microsoft Office 2010. Программы в нем имеют общий пользовательский интерфейс и единообразные подходы к решению типовых задач по управлению файлами, форматированию, печати, работе с электронной почтой и т. д. Они облегчают и увеличивают скорость выполнения анализа, распределения данных и определения оптимального решения.  Для решения нашей задачи используется:

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

-Microsoft Access 2010, который  помогает создавать и хранить информацию в базах данных, так же предоставлять отчеты в удобном виде. Все это входит в основные функции программы Access.

-Microsoft Word 2010, мощный текстовый редактор, позволяющий быстро создать документ любой сложности из разрозненных заметок и довести до совершенства предоставленную информацию, а так же можем вставить различные таблицы, графики, диаграммы и т.д.

-Microsoft Power Point 2010,позволит профессионально подготовить презентацию, щегольнув броской графикой и эффектно оформленными тезисами . Но что самое замечательное, что сможем превратить документ, подготовленный в редакторе Word, в презентацию всего лишь одним щелчком мыши.

 

Microsoft  Exсel 2010

 

Microsoft Office Excel широко применяется в различных сферах, позволяет осуществлять расчеты, обладает графическими инструментами, позволяющими формировать отчеты в виде графиков. Таким образом, Microsoft Office Excel используют практически все, кто сталкивается с делопроизводством и расчетами, с экономическими и математическими вычислениями.. Microsoft Excel применяется при решении планово-экономических, финансовых, технико-экономических и инженерных задач, при выполнении бухгалтерского и банковского учета, для статистической обработке информации, для анализа данных и прогнозирования проектов, при заполнении налоговых деклараций.

Электронные таблицы Excel позволяют обрабатывать статистическую информацию и представлять данные в виде графиков и диаграмм. Их можно использовать и в повседневной жизни для учета и анализа расходования денежных средств: при ежедневной покупке продуктов и хозяйственных товаров, при оплате счетов и т.д.

Электронная таблица имеет  вид прямоугольной матрицы, разделенной  на столбцы и строки. (см.рис.1)

 

Задача 1. Решение  оптимальной производительной программы.

Выполнение первого  задания предполагает следующие  действия:

1. построение математической модели задачи;
2. получение решения задачи;
3. выполнение анализа отчетов по результатам и устойчивости
4. ответы на вопросы задания;

 

 

 

 

 

 

 

 

Вариант 1/16

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

1. Как изменится общая  стоимость выпускаемо продукции  и план ее выпуска при увеличении  запасов каждого вида сырья  на 50 ед.?

2. Целесообразно ли включать  в план изделие IV вида, на изготовление которого расходуется по 4 ед. каждого вида ресурсов ценой 40 единиц?

Решение:

Построение математической модели задачи.

Введем следующие обозначения:

x₁  - количество продукции A

x₂ – количество продукции Б

x_3- количество продукции В

Прибыль от реализации продуктов  вида А составляет- 25 Х₁, вида Б – 30 X₂, вида В – 15 Х_3,. Запишем критерий оптимальности:

F(x)=25*х₁+30*x₂+15*x₃            max

Ограничения имеют вид:

3*х₁+7*х₂+1*х₃ <=150 ограничение по I типу сырья

4*х₁+4*х₂+2*х₃ <=70 ограничение по II типу сырья

2*х₁+9*х₂+1*х₃ <=100 ограничение по  III типу сырья

Выбор и указание адресов  ячеек, в которые будет помещен  результат решения (изменяемые ячейки).

В нашей задаче х1, х2, х3, обозначают нормы расходов сырья на изделие каждого типа. Для оптимального значения вектора Х=( х1, х2, х3) зарезервируем ячейки B2:D2, а для оптимального значения целевой функции (максимальная прибыль) – ячейку E4.

Ввод зависимости для целевой функции.

Поместите курсор в ячейку E4. С помощью мастера функций введите функцию СУММПРОИЗВ. В окне функции в строку Массив1введите В2:D2 (ячейки искомых переменных). Этот массив будет использоваться при вводе зависимостей для ограничений, сделайте на него абсолютную ссылку с помощью клавиши F5. В строку Массив2вводим B4:D4.

После нажатия кнопки ОК, то есть после выполнения функции СУММПРОИЗВ вид экрана показан на рисунке:

 

 

Далее вызываем команду ПОИСК РЕШЕНИЙ. Вводим следующие обозначение и ограничения.

 

В диалоговом окне Поиск решения нажимаем кнопку Параметры. На экране появится диалоговое окно Параметры поиска решения. Устанавливаем флажки в окнах Линейная модель (это обеспечит применение симплекс-метода) и Неотрицательные значения. Нажмите на кнопку ОК. На экране появится диалоговое окно Поиск решения. Нажмите на кнопку Выполнить.

Через короткий промежуток времени появится окно Результаты поиска решения и исходная таблица с заполненными ячейками В2:D2 для значений хi, и ячейка E:4 с максимальным значением целевой функции:

 

 

 

Указываем тип отчетов Результаты и Устойчивость. В результате получим на отдельных листах Отчет по результатам и Отчет по устойчивости.

Отчет по результатам включает исходные и конечные данные (оптимальные) значения целевой функции и изменяемых ячеек, а также формул ограничений  и дополнительных сведений о наложенных ограничениях.

Отчет по устойчивости содержит сведения о чувствительности решения (нормируемая стоимость и теневая  цена) к изменениям значений влияющих ячеек и ячеек, содержащих формулы  ограничений, а также предельные изменения целевых коэффициентов  и правых частей ограничений, определяющие границы устойчивости найденных решений. Нормируемая стоимость показывает на сколько изменится целевая функция в случае принудительного включения единицы соответствующей продукции в оптимальный план выпуска.  

 

 

Анализ отчета по результатам.

 

В разделе Целевая ячейка указана максимальная суммарная прибыль – 525 денежных единиц.

В разделе Изменяемые ячейки показан оптимальный план:

 х1 (количество изделий А) – 0;

 х2 (количество изделий Б) – 9;

 х3 (количество изделий  В) – 16;

В разделе Ограничения показано, что при оптимальном плане ресурсы Сырье и Оборудование используются полностью (Статус – связанное), а Труд недоиспользуется, о чем сообщает  Статус – несвязанное.

 

1. План выпуска продукции, обеспечивающий максимум выпуска продукции в стоимостном выражении.

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

 

У недефицитных ресурсов (труд и оборудование) теневая цена равна нулю.

Сырье: 7,5х9=67,5  обеспечивает максимум выпуск продукции. 

 

2.Ценность каждого ресурса  и его приоритет при решении  задачи увеличения запасов ресурсов.

      Для того, чтобы узнать, в какой ресурс следует вкладывать средства, то есть увеличить его запасы до предельного значения, нужно для каждого ресурса вычислить произведение его теневой цены на допустимое увеличение:

 

Сырье :  7,5х130= 975денежных единиц

Оборудование: 0х57,5= 0денежных единиц

Заметим, что значение 1Е+30 в допустимом увеличении Труда свидетельствует  о том, что данный параметр не влияет на целевую функцию.

Отсюда делаем вывод, что  выгоднее всего вкладывать средства в закупку Сырье .

3.Максимальный интервал  изменения запасов каждого вида  ресурсов, в пределах которого  структура оптимального решения,  то есть номенклатура выпускаемой  продукции, остается без изменений.

В разделе Ограничения есть параметры Допустимое увеличение и Допустимое уменьшение. С помощью этих параметров можно узнать:

  а) изменение запасов  ресурса Труд изменяется от 150 до 150-68=82

  б) изменение запасов  ресурса Сырье  изменяется  от 70+130=200 до 

    70-25=45

   в) изменение запасов  ресурса Оборудование изменяется  от 100+57,5=157,5 

    до 100-65=35

 

4.Суммарная стоимостная  оценка ресурсов, используемых при  производстве единицы каждого  изделия. Выпуск какой продукции нерентабелен?

 

В разделе Изменяемые ячейки есть параметр Нормированная стоимость. С помощью этого параметра сравниваются затраты ресурсов на производство единицы продукции(путем умножения теневой цены на норму расхода) и прибыль от единицы продукции.

По тем видам изделий, где  нормированная стоимость больше или равна нулю, делается вывод, что  выпуск этих изделий предприятию  выгоден (в нашем случае это 2 и 3 вид). Если нормированная стоимость отрицательна, то выпуск этого изделия предприятию невыгоден, и выпуск каждой единицы этого изделия снижает суммарную прибыль на указанное значение нормированной стоимости (в нашем  случае это 1 вид).

5.Насколько уменьшится стоимость  выпускаемой продукции при принудительном  выпуске единицы нерентабельной  продукции?

   Затраты ресурсов на производство единицы продукции  вычисляются путем умножения теневой цены каждого ресурса на нормы его расхода на единицу изделия.

 а) для изделия 1вида:

     0х3 +7,5х4 +0х2=30

     Прибыль от  реализации одного изделия 1 вида  составляет 25 денежных единиц, тогда  нормированная стоимость будет  равна разности прибыли и затрат  на одно изделие:

Прибыль-затраты=25-30=-4,999999999

Это означает, что производство изделия А предприятию не рентабельно.

  б) для изделия 2вида:

    0х7 + 7,5х4 + 0х9=30

 Прибыль от реализации  одного изделия  2 вида составляет 30 денежных единиц, тогда нормированная  стоимость будет равна разности  прибыли и затрат на одно  изделие:

Прибыль-затраты=  30-30=0

 

      По тем  видам изделий, где нормированная  стоимость больше или равна  нулю, делается вывод, что выпуск  этих изделий предприятию выгоден,  это означает, что производство  изделия 2 вида предприятию рентабельно.

в) для изделия 3 вида:

  0х1 + 7,5х2 + 0х1=15

    Прибыль от  реализации одного изделия 3 вида  составляет 15 денежных единиц, тогда  нормированная стоимость будет  равна разности прибыли и затрат  на одно изделие:

Прибыль-затраты=  15-15=0

Это означает, что производство изделия 3 вида предприятию рентабельно.

6.Интервал изменения цен  на каждый вид продукции, при  котором сохраняется структура  оптимального плана.

В разделе Ограничения есть параметры Допустимое увеличение и Допустимое уменьшение. С помощью этих параметров можно узнать интервал изменение запасов каждого вида ресурсов:

 

 а) прибыль от реализации  одного изделия 1 вида изменятся  от   25+4,999999999  до 25 денежных    единиц.

    б) прибыль от  реализации одного изделия 2 вида  изменятся от 30+105=135 до 30-0=30 денежных  единиц.

    в) прибыль от  реализации одного изделия 3 вида  изменятся от 15+0=15

 до 15-2,5=12,5  денежных  единиц.

7. Как изменится общая  стоимость продукции и план  ее выпуска при увеличении  запасов каждого вида сырья  на 50 единицы?

Для этого вычислим выражение:

     0×50+7,5×50+ 0х50=375

    То есть, в данном  случае общая стоимость продукции  увеличится на 375 единицы.

8.Целесообразно ли включать  в план изделие IV вида, на изготовление  которого расходуется по 4 ед. каждого  вида ресурсов ценой 40 единиц?

Рассчитываем нормированную  стоимость:

0х4+7,5х4+0х4=30      затраты на одно изделие

40-30=10                       нормированная стоимость

Следовательно, включать в  план изделие IV вида предприятию выгодно.

 

 

Решение штатного расписания.

Задача 2

Определение штатного расписания организации, что включает в себя следующие действия:

1. построение математической модели решения задачи;
2. создание штатного расписания в Excel;
3. создание базы данных в Access «Штатное расписание»;
4. Экспорт штатного расписания из Excel в СУБД Access;
5. Создание нескольких вариантов запросов и организация на их.

 

Вариант 2/17

 

                 Условие задачи:

Штатное расписание сотрудников показано на рисунке.

 

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

Найти решение при следующих  ограничениях: коэффициент В уборщицы должен находится в пределах от 0,12 до 0,23, младшего продавца – от 0,21 до 0,35, старшего продавца – от 0,3 до 0,45, менеджера и товароведа – от 0,51 до 0,62, директора – от 0,75 до 0,8, заместителя директора – от 0,55 до 0,75

 

 

Решение:

Построение математической модели задачи.

За основу возьмем коэффициент  надбавки уборщицы, а остальные коэффициенты будем вычислять, исходя из него: во столько-то раз или на столько-то больше.

Для распределение надбавки в сумме 540 тыс.рублей , необходимо рассчитать коэффициент  надбавки и надбавку к з/п .

Надбавка к окладу рассчитывается путем умножения коэффициента надбавки на среднюю заработную плату за последний  квартал.

В режиме просмотра формул эта таблица имеет вид:

Следует также вычислить  суммарную надбавку, путем умножения  надбавки з/п на количество работников

 

 

Затем нужно вызвать команду Поиск решения. В появившемся диалоговом окне Поиск решения: в качестве целевой ячейки необходимо установить адрес $J$3, где находится сумма надбавки:

 

 

Установим значение целевой  ячейки равное числу 540.

 Изменяемыми  ячейками, то есть ячейками результата, являются ячейки, коэффициент  надбавки и  суммарная надбавка  ($F$3:$F$9;$I3)

Затем нужно установить ограничения. В качестве ограничений нужно  установить допустимый диапазон варьирования изменяемых ячеек. Например, уборщицы должен находится в пределах от 0,12 до 0,23 . Ограничения устанавливаются с помощью кнопки Добавить.

В левой части окна указывается  адрес изменяемой ячейки, например адрес $F$3, содержащий коэффициент надбавки. Затем указывается нужный знак, например, <=, в правой части вводится предельно допустимое значение, по условию задачи равное числу 0,23. Таким же образом, устанавливаются условия: $F$3>=0,12. Аналогично заполняются остальные ограничения. Путем нажатия кнопки Выполнить получим решение задачи:

 

 

 

 

 

 

Использование возможности  ACCESS

Программа ACCESS

Microsoft Access в настоящее время является одной из самых популярных среди настольных (персональных) программных систем управления базами данных Среди причин такой популярности следует отметить:

- высокую степень универсальности  и продуманности интерфейса, который  рассчитан на работу с пользователями  различной квалификации. Реализована  система управления объектами  базы данных, позволяющая гибко  и оперативно переходить из  режима конструирования в режим  их непосредственной эксплуатации;

- глубоко развитые возможности  интеграции с другими программными  продуктами, входящими в состав  Microsoft Office, а также с любыми программными продуктами, поддерживающими технологию OLE;

- богатый набор визуальных  средств разработки.

Работы с объектами  базы данных унифицирован. По каждому из них предусмотрены стандартные режимы работы:

- Создать - предназначен для создания структуры объектов;

- Конструктор - предназначен  для изменения структуры объектов;

- Открыть (Просмотр, Запуск) - предназначен для работы с  объектами базы данных.

Важным средством, облегчающим  работу с Access для начинающих пользователей, являются мастера - специальные программные надстройки, предназначенные для создания объектов базы данных в режиме последовательного диалога. Для опытных и продвинутых пользователей существуют возможности более гибкого управления ресурсами и возможностями объектов СУБД в режиме конструктора.

Специфической особенностью СУБД Access является то, что вся информация, относящаяся к одной базе данных, хранится в едином файле. Такой файл имеет расширение *.mdb. Данное решение, как правило, удобно для непрофессиональных пользователей, поскольку обеспечивает простоту при переносе данных с одного рабочего места на другое. Внутренняя организация данных в рамках mdl формата менялась от версии к версии, но фирма Microsoft поддерживала их ее вместимость снизу вверх, то есть базы данных из файлов в формате ранних вер сии Access могут быть конвертированы в формат, используемый в версиях боле поздних.

Основными понятиями или  объектами этой системы являются: таблицы, запросы, формуляры, отчеты, макросы  и модули.

Запросы служат для отбора данных по какому-либо признаку или  комбинации признаков. Выборка данных выполняется для просмотра записей  или их изменения. Запросы можно  создавать либо с помощью мастера, либо в режиме конструктора.

Отчеты – это данные, представленные для печати. Они могут  основываться как на основной таблице, так и на запросах. Чтобы представить  результаты запросов в наглядном  виде, создаются документы – отчеты. Отчеты можно создавать в режиме конструктора или с помощью специальной  программы, входящей в состав СУБД –  мастера отчетов. Режим конструктора предназначен для подготовленных пользователей.

База данных это набор  данных, который связан с определенной темой или назначением. Когда  Вы создаете базу данных в Microsoft Access, Вы создаете один файл, который содержит все таблицы, запросы, формуляры, отчеты и другие объекты, которые помогают Вам использовать информацию в базе данных.

Созданную базу данных можно  наполнить объектами различного рода и выполнять операции с ними. Но с базой данных можно выполнять  операции как с неделимым образованием. Все операции такого рода - операции управления базой данных - сосредоточены  в меню File прикладного окна Access или в окне базы данных.