Logo GenDocs.ru

Поиск по сайту:  


Загрузка...

Решение производственной задачи в среде Exel - файл Курсовая Колкова C.doc


Решение производственной задачи в среде Exel
скачать (632.9 kb.)

Доступные файлы (1):

Курсовая Колкова C.doc765kb.10.10.2011 17:40скачать

содержание
Загрузка...

Курсовая Колкова C.doc

Реклама MarketGid:
Загрузка...
МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ

ТАМБОВСКИЙ ГОСУДАРСТВЕННЫ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

Кафедра

"Автоматизированное проектирование технологического оборудования"
Курсовая работа по дисциплине:

«Информационные системы»
на тему:
"Редактор электронных таблиц

Microsoft Excel"
Решение производственной задачи


Выполнил:

студентка группы М-24

Колкова Т,В,.
Проверил: преподаватель

Егоров С.Я.
ТАМБОВ 2009 г.

Содержание
ВВЕДЕНИЕ …………………………………………………………… 3
1. Текст задания ……………….………………………………..…….. 5

2. Постановка задачи …………………….……………………..…….. 6

3. Описание решения ……………………………………………...….. 7

3.1. Составление расчетных ведомостей ………………………..... 7

3.2. Формирования платежной ведомости …………………..…… 8

3.3. Анализ показателей выполнения плана ………………..……. 9

3.4. Формирование итоговой таблицы ……………………..……. 12

3.5. Составление справки о доходах ………………………..……. 14
4. Задание по математической статистике …………………………. 17
5. Дополнительный материал …………………………………..…… 18
ВЫВОДЫ ……………………………………………………….……. 19
Приложение 1. Листы книги MS Excel – f.xls

Приложение 2. Слайды презентации – f.ppt

Приложение 3. Контрольная работа N2
ВВЕДЕНИЕ
Microsoft Excel – одна из программ пакета Microsoft Office, которая предназначена для выполнения расчетов и управления электронными таблицами.

Область применения Microsoft Excel широка:

  • Excel часто используют для создания документов без всяческих расчётов, просто имеющих табличное представление;

  • в Excel легко создавать различные виды графиков и диаграмм, которые берут данные для построения из ячеек таблиц;

  • Excel содержит многие математические и статистические функции, благодаря чему его можно использовать для расчетов курсовых, лабораторных работ;

  • Excel интенсивно используется в бухгалтерии – для оформления документов, расчётов и создания диаграмм;

Документ Excel имеет расширение "*.xls" и называется рабочая книга. Рабочая книга состоит из рабочих листов. По умолчанию их создается три. Переключаться между листами можно, используя закладки (ярлыки) в нижней части окна "Лист 1" и т.д.

Рабочий лист - это место для ввода данных. Рабочие листы имеют вид сеток с горизонтальными строками и вертикальными столбцами, т.е. представляют собой таблицу. Таблица состоит из 256 (2 в 8 степени) столбцов и 65536 (2 в 16 степени) строк. Столбцы обозначаются буквами латинского алфавита (в обычном режиме) от "A" до "Z", затем идет "AA-AZ", "BB-BZ" и т.п. до "IV" (256). Строки обозначаются обычными арабскими числами.

На пересечении столбца и строки находится ячейка. Каждая ячейка имеет свой уникальный (в пределах данного листа) адрес, который составляется из буквы столбца (в обычном режиме) и номера строки (например "A1"). Адрес ячейки (ссылка на ячейку) используется в расчётах (и не только) для того, чтобы "достать" данные из этой ячейки, и использовать в формуле. Форматирование выделенной ячейки осуществляется командой Формат→Ячейки.

Все расчеты в Excel выполняют формулы. Формулой Excel считает все, что начинается со знака "=". В формуле можно использовать различные типы операторов (арифметические и т. п.), текст, ссылки на ячейку или диапазон ячеек, круглые скобки, именованные диапазоны. Естественно, в формулах соблюдается приоритет выполнения операций (умножение выполняется раньше сложения и т. п.). Для изменения порядка выполнения операций используются круглые скобки. Если в формуле используется текст, то он обязательно должен быть заключен в двойные кавычки.

В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь используется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок (кода), предназначенный для решения каких-то задач.

При написании сложных формул, особенно использующих вложенные функции, используется мастера функций – Вставка→Функция. Он облегчает и ускоряет ввод формул, и делает многие вещи: автоматически вставляет знак "равно", имя функции, круглые скобки, расставляет точки с запятой. Позволяет просматривать значение ссылок и результаты промежуточных вычислений.

В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций.

Для построения диаграмм используется команда Вставка→Диаграмма.

В данной курсовой работе мы будем работать Microsoft Office Excel 2003.

^ 1. Текст задания.
1) Выбрать любую производственную задачу из любой предметной области, исходная информация, для решения которой и результаты представлена в виде некоторой совокупности таблиц (4-5 штук). В отдельных таблицах должны быть вычисляемые графы (не менее 2-3). Отдельные таблицы должны быть размещены на разных листах книги. Данные таблиц одного листа должны использоваться в таблицах другого листа. Постановку задачи и описание решения обсудить с преподавателем до начала выполнения.
2) При решении задач должны использоваться возможности Excel группировки, фильтрации данных, подведение итогов.
3) Отдельные результаты должны быть представлены в виде различных диаграмм и графиков.
4) Результаты выполнения курсовой работы представить в виде презентации, выполненной в среде Power Point.

Работа считается выполненной, если результаты решения задачи представлены в трех формах:

- электронном в виде книги Excel;

- бумажном;

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

^ 2. Постановка задачи.
В качестве производственной задачи был выбран расчёт заработной платы рабочих виртуального предприятия за квартал и итогового фонда заработной платы.

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

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

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

Дополнительно предусмотрена возможность составления справки о доходах за квартал для каждого рабочего виртуального предприятия.
^ 3. Описание решения.
Решение поставленной задачи было выполнено в виде книги MS Excel 2003 - f.xls, которая состоит из 8-ми листов (приложение 1).

3.1. Составление расчетных ведомостей.
Н
а первых трех листах книги Excel: Январь, Февраль, Март представлены расчетные ведомости за месяцы: январь-март (рис.3.1).
Вычисления проводились в ячейках (рис.3.2):

    • F4:F8 – расчет премии, с учетом разряда и показателем выполнения плана. От оклада считается процент перевыполнения плана и умножается на разряд. В случае не выполнения плана, премия не начисляется (используется функция ЕСЛИ, которая используется при проверке условий для значений и формул, и возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ).

    • G4:G8 – расчет начисленной суммы (оклад + премия).

    • I4:I8 – расчет зарплаты с учетом налога. От начисленной суммы вычитается соответствующий процент (налог) от нее.

    • G9, I9 – расчет итоговых сумм: Начислено и Зарплата (используется функция СУММ, которая суммирует все числа в указанном интервале ячеек).

Кроме того, на листах Февраль и Март в ячейках B4:B8, используется формула, которая ссылается на соответствующие ячейки листа Январь.




3.2. Формирования платежной ведомости.
Ф
ормирование платежной ведомости по месяцам квартала выполняется на листе Ведомость (рис.3.3).
Ячейка C4 оформлена таким образом, что при ее выделении появляется кнопка выпадающего списка, при нажатии на которую можно выбрать нужный месяц. Далее в зависимости от выбранного месяца заполняются ячейки A7:A11 и С7:С11.

Для создания выпадающего списка была выполнена следующая последовательность действий.

  • Ячейкам D1:F1 с помощью команды Вставка→Имя→Присвоить было задано общее имя месяцы.

  • Затем, при выделенной ячейки С4, была выполнена команда Данные→Проверка….

  • В открывшемся диалоговом окне Проверка вводимых значений были заданы следующие параметры: ^ Тип данных: список, Источник: =месяцы.

Для формирования данных в таблице (ячейки A7:A11 и С7:С11) была использована функция ДВССЫЛ, которая возвращает ссылку, заданную текстовой строкой, при этом ссылка немедленно вычисляется для вывода ее содержимого. Например:

B10=ДВССЫЛ(C$4&"!"&"B"&A10+"3") , где

& – оператор склеивания строк;

A10 – порядковый номер в таблице, в нашем случае ^ 4;

+"3" – к порядковому номеру прибавляется константа 3;

C$4 – в рассматриваемом случае Февраль.

Результат выполнения функции в данном случае будет следующим:

Февраль!B7 – в этой ячейки находятся выводимые данные – ФИО рабочего, находящегося в списке под 4 номером.

С10=ДВССЫЛ(C$4&"!"&"I"&A10+"3") , где

& – оператор склеивания строк;

A10 – порядковый номер в таблице, в нашем случае ^ 4;

+"3" – к порядковому номеру прибавляется константа 3;

C$4 – в рассматриваемом случае Февраль.

Результат выполнения функции в данном случае будет следующим:

Февраль!I7 – в этой ячейки находятся выводимые данные – зарплата рабочего, находящегося в списке под 4 номером.

Таким образом, выбирая из выпадающего списка нужный месяц, мы автоматически получаем соответствующую платежную ведомость, которую можно распечатать, выделив соответствующие ячейки (A5:D11) и используя команду Файл→Печать→Выделенный диапазон.

3.3. Анализ показателей выполнения плана.
На листе План представлена сводная таблица по выполнению ежемесячных планов каждым рабочим (рис.3.4).

К первым четырем столбцам шапки таблицы применена команда Данные→Фильтр→Автофильтр, что позволяет сортировать имеющиеся данные, задавать условия для показа строк таблицы.





Н
апример, задав условие в столбце Январь "меньше 100", получим таблицу, состоящую из одной строки (рис.3.5). Для возвращения к первоначальному виду необходимо выбрать команду (Все) из выпадающего меню.
В пятом столбце таблицы F4:F8 анализируется тенденция выполнения плана, каждым рабочим. В том случае если каждый месяц план выполняется более чем на 100%, и эти показатели растут, то принимается решение о поощрении рабочего. Если же на протяжении всех месяцев план не выполнялся, то принимается решение о проведение профилактической беседы с рабочим. Для этого используется функция ЕСЛИ с вложенной функцией ЕСЛИ, которая выполняется если заданное условие дает значение ЛОЖЬ. А так же вложенная функция логического умножения И, которая возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Например:

F5=ЕСЛИ(И(C5>100;D5>100;E5>100;D5>C5;E5>D5);"поощрить"; ЕСЛИ(И(C5<100;D5<100;E5<100);"профилактика";"---"))

Н
а этом же листе по ячейкам B3:E8 c помощью команды Вставка→Диаграмма построены две диаграммы (рис.3.6), одна из которых представляет собой график с маркерами, помечающими точки данных (диаграмма 1), а вторая (диаграмма 2) – гистограмму, отображающую значения различных категорий.
Редактирование полученных диаграмм проводилось с помощью команд выпадающего меню, появляющегося при нажатии правой кнопки мыши на диаграмму или ее элемент.

Полученные диаграммы графически отображают:

  • диаграмма 1 – выполнение плана рабочими по месяцам в виде графика;

  • диаграмма 2 – изменение производительности труда рабочего в течение квартала.

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

3.4. Формирование итоговой таблицы.
Н
а листе Итоги представлена итоговая таблица (рис. 3.7) фонда заработной платы по месяцам и за квартал с учетом квартальной премии.
Ячейки В4:B8, B10:B14, B16:B20, B22:В26 заполняются с помощью формулы, ссылающейся на соответствующие ячейки листа Январь. Например:

В24=Январь!B6

Ячейки D4:D8, D10:D14, D16:D20 заполняются с помощью формулы, ссылающейся на соответствующие ячейки листов ^ Январь, Февраль, Март.

Например: D7=Январь!G7

Ячейки D22:D26 вычисляются с помощью функции ЕСЛИ, которая анализирует соответствующую ячейку листа План и если было принято решение о поощрении рабочего то рассчитывается квартальная премия, равная половине среднего оклада за три месяца, в противном случае записывается 0. Например:
D24=ЕСЛИ(План!F6="поощрить";

СРЗНАЧ(Январь!C6;Февраль!C6;Март!C6)*0,5;0)

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

Для расчета итоговых значений в ячейках D9, D15, D21, D27, D28 используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая в зависимости от указанного параметра (9 – суммирование) выполняет определенные действия с данными указанного диапазона, при этом игнорируя вложенные промежуточные итоги. Например: D28=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D4:D27)

Для удобства просмотра данные итоговой таблицы (ячейки B4:D28) были сгруппированы в 3-х уровневую структуру с помощью команды ^ Данные→Группа и структура→Создание структуры.

Н
а этом же листе с помощью команды Вставка→Диаграмма построена диаграмма 3 (рис.3.8), графически отображающая распределение фонда заработной платы за квартал с учетом квартальной премии, и представляющая собой объемный вариант разрезанной круговой диаграммы. Редактирование диаграммы проводилось с помощью команд выпадающего меню, появляющегося при нажатии правой кнопки мыши на диаграмму или ее элемент.


3.5. Составление справки о доходах.
Н
а листе Справка предусмотрена возможность составления справки о доходах за квартал для каждого рабочего виртуального предприятия (рис.3.9), которая включает данные о начисленной заработной плате по месяцам, квартальной премии, удержанных налогах, итоговых и среднемесячных доходах.
Ячейка В3 оформлена таким образом, что при ее выделении появляется кнопка выпадающего списка, при нажатии на которую можно выбрать ФИО нужного рабочего. Далее в зависимости от выбранного рабочего заполняются ячейки В6:С11.

Для создания выпадающего списка из ФИО рабочих была выполнена следующая последовательность действий.

  • На листе Январь ячейкам В4:В8 с помощью команды Вставка→Имя→Присвоить было задано общее имя список.

  • Затем, на листе Справка при выделенной ячейки В3, была выполнена команда Данные→Проверка….

  • В открывшемся диалоговом окне Проверка вводимых значений были заданы следующие параметры: ^ Тип данных: список, Источник: =список.

Для формирования данных в ячейках В6:В8 была использована следующая формула: B6=ИНДЕКС(ДВССЫЛ(A6&"!B4:G8");

ПОИСКПОЗ(B$3;ДВССЫЛ(A6&"!B4:B8");0);6)

В формуле подчеркнуты, те числа, которые изменяются при переходе к следующей строке. Рассмотрим более подробно вычисления по приведенной формуле и ячейкой B3=Васечкин Василий Васильевич.

Функция ДВССЫЛ возвращает ссылку, заданную текстовой строкой, следовательно: ДВССЫЛ(A^ 6&"!B4:G8")=Январь!B4:G8,

ДВССЫЛ(A6&"!B4:B8")=Январь!B4:В8.

Функция ПОИСКПОЗ просматривает заданный интервал ячеек (второй аргумент) и сопоставляет искомое значение (первый аргумент) со всеми элементами указанного интервала. Если третий аргумент равен 0, то функция находит первое значение в указанном интервале, которое в точности равно первому аргументу и возвращает позицию найденного значения. Следовательно:

ПОИСКПОЗ(B$3;ДВССЫЛ(A6&"!B4:B8");0) =

=ПОИСКПОЗ(B$3;Январь!B4:В8;0) = 1.

Функция ИНДЕКС возвращает значение элемента из заданного диапазона (первый аргумент), с конкретным номером строки (второй аргумент) и столбца (третий аргумент). Следовательно:

В6=ИНДЕКС(Январь!B4:G8;1;6)=8640,00

Таким образом, при выполнении данной формулы на соответствующем листе (Январь, Февраль, Март) в диапазоне B4:G8 в столбце B4:B8 ищется ячейка равная ячейки B3 листа Справка и из этой строки выводится ячейка шестого столбца, т.е. G (начисленная заработная плата указанного рабочего).

Значение ячейки B9 определяется по похожей формуле:

В9=ИНДЕКС(Итоги!B22:D26;ПОИСКПОЗ(B$3;Итоги!B22:B26;0);3)

Т.е. на листе Итоги в диапазоне B22:D26 в столбце B22:B26 ищется ячейка равная ячейки B3 листа Справка и из этой строки выводится ячейка третьего столбца – D (премия указанного рабочего).

Расчет ячеек С6:С8 осуществляется по следующей формуле:

С6=B6*ИНДЕКС(ДВССЫЛ(A6&"!B4:H8");

ПОИСКПОЗ(B$3;ДВССЫЛ(A6&"!B4:B8");0);7)/100

В формуле подчеркнуты, те числа, которые изменяются при переходе к следующей строке.

Функция ИНДЕКС в данном случае работает следующим образом. На соответствующем листе (^ Январь, Февраль, Март) в диапазоне B4:H8 в столбце B4:B8 ищется ячейка равная ячейки B3 листа Справка и из этой строки берется значение из ячейки седьмого столбца, т.е. H (налог указанного рабочего).

Далее от ячейки B6 вычисляется процент, определенный функцией ИНДЕКС.

Ячейка С9=B9*13/100 – налог на премию.

Общие доходы за квартал и уплаченный налог рассчитываются в ячейках B10, C10 как суммы соответствующих диапазонов В6:В9 и С6:С9 (функция СУММ).

Средний доход и налог за месяц в I квартале рассчитывается в ячейках В11 и С11 соответственно (B11=B10/3, C11=C10/3).

Таким образом, на листе Справка, выбирая из выпадающего списка нужного рабочего, мы автоматически получаем соответствующую справку о доходах за квартал, которую можно распечатать, используя команду Файл→Печать→Выделенные листы.

^ 4. Задание по математической статистике.
Таблица представляет из себя один из пунктов курсовой работы по математической статистике. По восьми сделанным измерениям, нужно найти линейное уравнение регрессии, отражающее закон распределения экспериментальных данных. В первых двух столбцах (B, C) вписаны исходные данные, в двух последующих находятся дополнительные параметры, необходимые для нахождения коэффициентов уравнения регрессии и . В пятом столбике мы находим значения полученного уравнения с заданными значениями параметра Х и делаем графическую проверку. Последние два столбика нужны для аналитической проверки решения.




^ 4. Дополнительный материал.
Результаты решения данной производственной задачи представлены в виде презентации, выполненной в программе MS Office Power Point 2003f.ppt.

Презентация состоит из 18 слайдов, которые представлены в приложение 2.
ВЫВОДЫ
В данной курсовой работе при решении производственной задачи, заключающейся в расчёте заработной платы рабочих виртуального предприятия за квартал, определения итогового фонда заработной платы, получения платежных ведомостей по месяцам, начисления квартальной премии и формировании справки о доходах были использованы следующие возможности Microsoft Office Excel 2003.

  • Построение таблиц с различными границами и форматирование ячеек с помощью команды Формат→Ячейки.

  • Построение диаграмм по соответствующим диапазонам данных с помощью команды Вставка→Диаграмма и их редактирование.

  • Применение Автофильтра к столбцам данных (команда Данные→Фильтр).

  • Группировка данных в соответствии с промежуточными итогами с помощью команды Данные→Группа и структура→Создание структуры.

  • Задание общего имени для группы выделенных ячеек с помощью команды Вставка→Имя→Присвоить.

  • Организация выпадающего списка в выделенной ячейки с помощью команды Данные→Проверка….

Для расчета требуемых в постановке задания значений были сформированы формулы в соответствии с требованиями программы MS Excel 2003. При вводе одинаковых формул использовалось автозаполнение ячеек. При составлении формул использовались следующие функции.

  • Функция ЕСЛИ – используется при проверке условий для значений и формул, и возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

  • Функция ^ СУММ – суммирует все числа в указанном диапазоне ячеек.

  • Функция И – логическое умножение, возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

  • Функция СРЗНАЧ – возвращает среднее арифметическое своих аргументов.

  • Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ – в зависимости от указанного параметра (например, 9 – суммирование) выполняет определенные действия с данными указанного диапазона, при этом игнорируя вложенные промежуточные итоги.

  • Функция ^ ДВССЫЛ – возвращает ссылку, заданную текстовой строкой, при этом ссылка немедленно вычисляется для вывода ее содержимого.

  • Функция ПОИСКПОЗ – просматривает заданный интервал ячеек (второй аргумент) и сопоставляет искомое значение (первый аргумент) со всеми элементами указанного интервала. Если третий аргумент равен 0, то функция находит первое значение в указанном интервале, которое в точности равно первому аргументу и возвращает позицию найденного значения.

  • Функция ИНДЕКС – возвращает значение элемента из заданного диапазона (первый аргумент), с конкретным номером строки (второй аргумент) и столбца (третий аргумент).

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

Приложение 1

Листы книги MS Excel – f.xls
Приложение 2

Слайды презентации – f.ppt

Приложение 3

Контрольная работа N2




Скачать файл (632.9 kb.)

Поиск по сайту:  

© gendocs.ru
При копировании укажите ссылку.
обратиться к администрации
Рейтинг@Mail.ru