Рішення виробничої задачі в середовищі Exel

Курсова Колкова C.doc (1 стор.)
Оригінал


МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ

Тамбовський державний ТЕХНІЧНИЙ УНІВЕРСИТЕТ

Кафедра

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

«Інформаційні системи»
на тему:
"Редактор електронних таблиць

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 має розширення "*. 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):

Крім того, на аркушах лютий і березень в осередках B4: B8, використовується формула, яка посилається на відповідні клітинки аркуша Январь.




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

Для створення списку, що випадає була виконана наступна послідовність дій.

Для формування даних у таблиці (комірки 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%, і ці показники зростають, то приймається рішення про заохочення робітника. Якщо ж протягом всіх місяців план не виконувався, то приймається рішення про проведення профілактичної бесіди з робочим. Для цього використовується функція ЕСЛИ з вкладеною функцією ЯКЩО, яка виконується якщо задана умова дає значення БРЕХНЯ. А так само вкладена функція логічного множення І, яка повертає значення ІСТИНА, якщо всі аргументи мають значення ІСТИНА; повертає значення БРЕХНЯ, якщо хоча б один аргумент має значення FALSE. Наприклад:

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

Н
а цьому ж аркуші по осередках B3: E8 c допомогою команди Вставка → Діаграма побудовано дві діаграми (рис.3.6), одна з яких представляє собою графік з маркерами, позначаю точки даних (діаграма 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.

Для створення списку, що випадає з ПІБ робітників була виконана наступна послідовність дій.

Для формування даних у комірках В6: В8 була використана наступна формула: B 6 = ІНДЕКС (ДВССИЛ (A 6 & "! B4: G8");

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

У формулі підкреслені, ті числа, які змінюються при переході до наступного рядка. Розглянемо більш докладно обчислення за наведеною формулою і осередком B3 = Васєчкін Василь Васильович.

Функція ДВССИЛ повертає посилання, задану текстової рядком, отже: ДВССИЛ (A ^ 6 & "! B4: G8") = січня! B4: G8,

ДВССИЛ (A 6 & "! B4: B8") = січня! B4: В8.

Функція ПОІСКПОЗ переглядає заданий інтервал осередків (другий аргумент) і зіставляє дані значення (перший аргумент) з усіма елементами зазначеного інтервалу. Якщо третій аргумент дорівнює 0, то функція знаходить перше значення у вказаному інтервалі, яке в точності дорівнює першому аргументу і повертає позицію знайденого значення. Отже:

ПОІСКПОЗ (B $ 3; ДВССИЛ (A 6 & "! 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 = B 6 * ІНДЕКС (ДВССИЛ (A 6 & "! B4: H8");

ПОІСКПОЗ (B $ 3; ДВССИЛ (A 6 & "! 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 2003 - f.ppt.

Презентація складається з 18 слайдів, які представлені в п ріложеніе 2.
ВИСНОВКИ
У цій роботі при вирішенні виробничої задачі, що полягає в розрахунку заробітної плати робітників віртуального підприємства за квартал, визначення підсумкового фонду заробітної плати, отримання платіжних відомостей по місяцях, нарахування квартальної премії і формуванні довідки про доходи були використані наступні можливості Microsoft Office Excel 2003.

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

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

Додаток 1

Аркуші книги MS Excel - f.xls
Додаток 2

Слайди презентації - f.ppt

Додаток 3

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

Навчальний матеріал
© uadoc.zavantag.com
При копіюванні вкажіть посилання.
звернутися до адміністрації