Лабораторна робота №10: " Автоматизація розрахунків та аналізу даних за допомогою Microsoft Excel"

Вид материала

Документы

Содержание 1.2.Питання, на які треба звернути увагу при виконанні роботи Порядок виконання роботи Контрольні запитання Варіанти індивідуальних завдань для удоскона­лення матеріалу

Подобный материал:

• Лабораторна робота №9: "Табличний процесор Microsoft Excel", 130.45kb.
• Урок 3 практична робота №1 тема: Створення структури бази даних І введення даних, 27.48kb.
• Центр професійно-технічної освіти у вінницькій області вище професійне училище №11, 1018.49kb.
• Применение Microsoft Excel для обработки табличных данных. Выполнение расчетов в таблицах, 14.68kb.
• Урок 9 практична робота №3 тема: Створення запитів, форм та звітів у базі даних, 29.82kb.
• Лабораторна робота №13: “ ms ассеss. Проектування бази даних”, 356.07kb.
• Пособие для начинающих Воробьев В. В. Microsoft Excel, 439.68kb.
• Курс лекций по дисциплине " основы компьютерных технологий" Часть II. Microsoft Excel, 457.54kb.
• Методика оформление учебно-методической документации учителем географии в текстовом, 644.79kb.
• Лабораторная работа №4 Тема: Панели Microsoft Excel, 44.05kb.

Лабораторна робота №10: " Автоматизація розрахунків та аналізу даних за допомогою Microsoft Excel"

Мета роботи: Навчитися налагоджувати та використовувати MS EXCEL при розв’язанні задач та розробляти системи для обробки даних на грунті ЕТ.

Теоретичні відомості

1.1.Терміни та визначення

• Функції робочого листа – це функції, що можуть використовуватися у формулах на робочому листі. Вони поділяються на стандартні (вбудовані) функції з надбудов, та функції, що створені користувачем у наданій книзі. Кожна функція має ім’я, а більшість з функцій ще й список аргументів у круглих дужках. Аргументами можуть бути константи, посилання на комірки та їх діапазони, інші функції. При застосуванні функцій треба звертати увагу на те, щоб значення аргументів відповідало встановленим типам. Функції вводяться у формули вручну або з використанням майстра функцій.
  
• Табличні функції – це такі функції робочого листа, що повертають результат не у одну комірку, а у обмежену область у вигляді вектору чи матриці. При застосуванні таких функцій треба чітко визначитись з розміром області результатів, інакше останні не будуть вірними. Вставка табличної функції виконується за один раз у всі комірки виділеної області результатів за допомогою гарячих клавіш “Ctrl-Shift-Enter”
  
• Надбудова – це зовнішня бібліотека функцій, призначених для вирішення окремого класу задач. У стандартну поставку входять надбудови “Поиск решения”, “Диспетчер отчетов”, “Автосохранение” тощо. Надбудови можна вмикати та вимикати, змінюючи коло доступних функцій (команда Сервис/Надстройки).
  
• Майстер функцій – це діалогова програма, що підказує користувачеві, які функції доступні, їх призначення та типи аргументів.
  
• Сортування даних – це автоматичне пересування рядків або стовпчиків таблиці для досягнення впорядкованості значень у ключових комірках. Таких груп ключових комірок може бути декілька. Наприклад, можливо сортування рядків таблиці з тим, щоб прізвища у першому стовпчику розташувалися у алфавітному порядку, а при наявності осіб з однаковими прізвищами впорядкувати їх по імені у другому стовпчику тощо.
  
• Фільтрація даних – це відбір рядків таблиці у відповідності до умов, яким мають відповідати значення у ключових комірках. Можливо використання простих умов (Автофильтр) або складних умовних виразів (Расширенный фильтр).
  
• Автофільтр – (команда Данные/Фильтр/Автофильтр) застосовується, коли треба скрити всі рядки крім тих, що містять у визначених (ключових) стовпчиках належні дані. При цому перелік можливих умов для кожного ключового стовпчика надається автоматично і користувачеві достатньо вибрати одне з них. Недоліком автофільтра є те, що діапазон таких умов обмежено.
  
• Розширений фільтр – (команда Данные/Фильтр/Расширенный фильтр) дозволяє робити умови необмеженої складності і не тільки скривати рядки а й копіювати відібрані рядки на нове місце.
  
• Аналіз зв`язків – це графічне відображення ланцюжків посилань від формули до формули, що виникають при .розробці електронної таблиці. Завдяки такому відображенню стає легким спостереження впливу вихідних значень на результати обчислень і пошук помилок у алгоритмі.
  
• Підбір параметрів –.це простіша операція зворотного розрахунку, коли користувач задає результат обчислення, а система автоматично розраховує належне вихідне значення у вказаній комірці.
  
• Пошук рішення – більш потужна операція зворотного розрахунку, коли користувач може задавати не тільки результат обчислення, але й деяку мету, – наприклад отримання максимального або мінімального значення результату. Крім того він може вказати обмеження на варіацію вихідних даних. Система автоматично робить покроковий підбір значень вхідних даних у вказаному діапазоні доки не буде досягнута мета, або не виконане обмеження на кількість ітерацій
  
• Зведена таблиця використовується для аналізу даних. Це таблиця, у яку виведені підсумки даних з іншої таблиці по заданих категоріях. Наприклад – звіт по вартості проданих товарів, згрупованих по видам ї продавцям: Семеновим продано книг на суму 650 грн., журналів на суму 320 грн., Яковлевим продано книг на суму 405 грн., журналів на суму 620 грн.
  

1.2.Питання, на які треба звернути увагу при виконанні роботи

• На які класи поділяються функції робочого листа і як ефективно використовувати майстер функцій при введенні формул?
  
• Чим застосування табличних функцій відрізняється від простих функцій?
  
• При розв’язанні яких завдань можуть бути використані сортування та фільтрація даних?
  
• У яких випадках достатньо користування автофільтром, а коли потрібен посилений фільтр?
  
• Для чого потрібен аналіз взаємозв’язку комірок? У чому полягає автоматизація такого аналізу?
  
• При розв’язанні яких завдань потрібні пошук рішення та підбір параметрів? У чому полягає автоматизація цього процесу?
  
• Для чого будуються зведені таблиці? Як вони перетворюються після побудови та як ними користуватися при аналізі даних?
  
• Яки види діаграм існують та як вони застосовуються при аналізі даних?
  

Порядок виконання роботи

1. Робота починається з виклику MS EXCEL. Розпочніть нову книгу і запам’ятайте її у каталозі STUDENT під назвою LAB_10.XLS. Назвіть перший робочий лист “Функции”.
   
2. Ознайомтесь з можливостями функцій категорії “Дата и время”. Для цього створіть таблицю, у комірках якої автоматично відбивається поточна дата та час і підраховується інтервал часу, що вже пройшов від вказаного моменту або залишився до нього.
   

Приклад такої таблиці наведено у рис.1. Щоб відтворити його, введіть у комірку С3 текст “Зараз”.

Установіть маркер на комірку D3 та викличте майстер функцій за допомогою кнопки fx панелі інструментів. Буде розпочато діалог майстра функцій. У лівому вікні оберіть категорію “Дата и время”, а у правому – функцію “ТДАТА”. Натисніть кнопку “ОК”. Ви побачите повідомлення про властивості цієї функції. Ознайомтесь з ними, та натисніть кнопку “ОК”. Тепер у комірці D3 є формула “ =ТДАТА()” і ви бачите її результат – поточну дату та час. Перенесіть копію цієї формули у комірки D4 та E4. Змініть формат комірки D4 так, щоб було видно тільки місяць та рік, а E4 – тільки години, хвилини та секунди. Тепер натисніть клавішу “F9” для переобчислення формули. Якщо все було зроблено правильно, то поточний час у комірках повинен змінитися.

У


Рис. 1

комірку С5 введіть назву “Пройшло з початку 1998 року”, у комірку D5 – формулу “=ТДАТА()-ДАТА(1998,1,1)” Зверніть увагу на те, що в залежності від обраних національних символів-подільників у формулі ДАТА можуть використовуватися не коми а крапки з комами для розмежування параметрів. Змініть формат комірки D5 на “Числовой”. Тепер у неї повинна відображатися кількість діб, що пройшли з початку 1998 року.

Тим же шляхом отримайте у комірці Е5 кількість діб, що залишилися до кінця поточного року.

В

Рис. 2

ведіть у комірку D6 формулу, що показує номер сьогоднішнього дня у поточному місяці, а у E6 – у поточному тижні.

3. Ознайомтесь з можливостями функцій категорії “Математические”. Для цього створіть таблицю з назвами стовпчиків “Угол в градусах”, “Угол в радианах”, “Синус угла”, “Косинус угла” (Рис 2).
   

Заповніть перший стовпчик значеннями куту від 0 до 360 градусів з кроком в 5 градусів. За допомогою майстра функцій введіть відповідні формули у другий, третій та четвертий стовпчики.

Знайдіть у майстра функцій формулу Суммквразн та за її допомогою розрахуйте у комірці А1 суму квадратів різниць синусів та косинусів відповідних кутів у діапазоні 0 360.

Побудуйте графіки для синусу та косинусу.

4. Ознайомтесь з застосуванням табличних функцій на прикладі статистичних функцій "ТЕНДЕНЦИЯ" і "РОСТ". Ці функції використовують при прогнозуванні. Вони розраховують залежність змінної Y від змінної Х та відтворюють невідомі значення Y для відомих Х.
   

На новому листі створить таблицю залежності з назвами першого рядка "X=" і другого рядка "Y=".

У перший рядок, починаючи з комірки В1 введіть значення змінної Х : 1,2,3,4,5,6,7. У другий рядок введіть відомі значення Y: 10,20,30,40. Невідомими залишилися останні три значення у комірках F2, G2 і H2. Відмітьте маркером разом всі три комірки з невідомими значеннями, введіть табличну функцію "= ТЕНДЕНЦИЯ(,,) ", введіть перед першою комою посилку на діапазон комірок з відомими значеннями Y (B2:E2), перед другою комою - на відповідний діапазон для Х (B1:E1), а після другої коми - на діапазон комірок F1:H1 для Х, відповідний коміркам з невідомими значеннями Y.

Нажміть разом клавіші “Ctrl-Shift-Enter”. У рядку формул з’явиться вираз типа “{=ТЕНДЕНЦИЯ(B2:E2;B1:E1;F1:H1)}”, а у виділеному діапазоні – результат прогнозу : 50,60,70. Це лінійний прогноз (тренд). Щоб отримати експоненціальний тренд (для порівняння), знов відмітьте маркером комірки з невідомими значеннями, замініть у рядку формул назву функції на "РОСТ" і Нажміть разом клавіші “Ctrl-Shift-Enter”. Простежте, як змінився результат прогнозу.

5. Навчіться застосовувати табличні формули. Для цього розберіть приклад з перемноженням матриць розміру 2х3.
   
   
   

Введіть матрицю та матрицю .


Тепер виділіть маркером область для результату розміром 2х2 комірки. Викличте майстер функцій і знайдіть функцію МУМНОЖ. Як аргументи застосуйте області першої та другої матриць. Для завершення вставки табличної функції натисніть разом клавіші Ctrl, Shift та Enter.

У області результату повинна з'явитися матриця .

6. Навчіться застосовувати автофільтр (“Данные/Фильтр/Автофильтр”). Застосуйте автофільтр до таблиці з п.2.3 і відберіть такі рядки, для котрих “Угол в градусах” знаходиться у інтервалі 20-120, а “Синус угла” менший від 0,8.
   
7. Навчіться застосовувати посилений фільтр (“Данные/Фильтр/Расширенный фильтр”).
   

Зніміть з таблиці з п.2.3 автофільтр і поруч з нею створіть область критеріїв з назвами стовпчиків “Угол в радианах”, “Критерий 1”, “Критерий 2”. Занесіть до комірок критеріїв такі формули, щоб було відібрано

• рядки з кутами, не меншими ніж 1 радіан
  
• і значеннями синуса, більшими за косинус але меншими за 2,5 косинуса,
  
• а
  
  
  

  Рис. 3

  
  бо з кутами, меншими від 1 радіану
  
• і значеннями синуса, меншими за 0,5 косинуса.
  

На це вам будуть потрібні два рядка області критеріїв.

Застосуйте до цих таблиць посилений фільтр (не забудьте ввімкнути режим копіювання результату у іншу область). Результат повинен мати вигляд, як на рис. 2.

8. З’ясуйте питання, пов’язані з автоматизацією підбору даних для отримання бажаного результату обчислень. Для цього назвіть робочий лист “Зависимости” і на ньому створіть експериментальну таблицю, подібну до наведеної у рис.3. Це таблиця розрахунку зростання вкладу у банк з врахуванням зміни кредитної ставки по роках. Перший стовпчик містить рік вкладу, другий – ставку, що прогнозується на цей рік, третій – формулу розрахунку набутку по вкладу на кінець попереднього року, а четвертий – суму накопиченого заощадження за попередній рік (для першого року це початковий внесок).
   

При підборі вихідних даних, по перше, треба знати, які з них впливають на значення результату. Тобто зробити наявним зв’язок між формулами у комірках. Для цього вам буде у нагоді механізм Аналізу зв`язків. Щоб ефективно користуватися ним треба встановити на робоче поле панель інструментів “Зависимости”.

Якщо це зроблено, то для пошуку залежності достатньо встановити маркер на комірку з формулою та натиснути належну кнопку декілька разів. Стрілки, що при цьому з’являються, вказують на вплив даних з одних комірок на результати у інших. Кожне натиснення на кнопку поширює обрій демонстрації зв’язків.

У прикладі на рис.3 маркер було встановлено на комірці D8, після чого 4 рази натиснуто кнопку “Влияющие ячейки”.

Тепер спробуємо вирішити питання, що треба зробити для того, щоб сума вкладу на початок 2002 року досягла 3000. З аналізу ланцюжків на рис. 2 випливає, що для цього можна змінити початковий внесок або ставки за 1999 – 2001 роки.

Для початку обмежимо завдання підбором початкового вкладу. Вирішіть це завдання за допомогою команди “Сервис – Подбор параметра”. Результатом повинно бути число 2213.52.

Я


Рис. 4

кщо зайвих грошей для початкового вкладу у вас немає, треба йти іншим шляхом – змінювати ставку. Але оскільки вона не може суттєво зростати, треба враховувати істотні обмеження. Для автоматизації цього існує “Пошу­к рішення ”. Для його опанування поверніть початковий внесок до 2000 і здійсніть пошук з обмеженнями на ставки на кожний рік до 12%. Для цього встановіть цільову комірку $E$7; оберіть діапазон зміни комірок $C$4:$C$7; додайте обмеження <= 0.12 на кожну з цих комірок і натисніть кнопку “Выполнить”. Як можна бачити, результат за такими обмеженнями недосяжний – максимальне накопичення = 2809.86. Повторіть пошук з новими обмеженнями – 15%. Тепер усе гаразд – рішення знайдено при ставках 14.47%.

9. Застосування зведених таблиць. Для знайомства з технікою їх використання створіть таблицю для аналізу пропозиції товарів на ринку: "Дата предложения", "Название фирмы", "Название товара", "Размер партии", "Цена единицы". Занесіть у таблицю дані по 3 фірмам, що постачають 2 найменування товарів, у хронологічному порядку. Приклад такої таблиці наведено на рис. 4.
   

Треба для кожного виду товару за весь термін підрахувати кількість пропозицій, загальний обсяг, та обчислити середню ціну. Для цього викличте майстер зведених таблиць ("Данные – Сводная таблица").

Пропустіть перший крок. На другому крокові оберіть область даних для обробки – створену вами таблицю разом із заголовками.

Н


Рис. 5

а третьому крокові перетягніть належні кнопки до відповідних областей, як це зображено на рис. 5: щоб здійснити групування товарів по видам розмістіть "Название товара" у полі "Строка"; щоб здійснити задані розрахунки – перетягніть кнопки "Название товара", "Размер партии", "Цена единицы " до поля "Данные". При цьому назви кнопок самі зміняться на назви операцій, наприклад - "Название товара" на "Кол-во значений по полю Название фирмы". Зверніть увагу на те, що операцією над полем "Цена единицы" має бути усереднення. Щоб так сталося, натисніть на перенесену кнопку два раза і у списку, що відкрився, оберіть операцію "Среднее "

На четвертому крокові треба вказати, які підсумки робитимуться системою і де треба розташувати результати. Відімкніть всі пропозиції на створення загальних підсумків і погодьтеся на розташування таблиці на новому листі. Натисніть кнопку "Готово". Має з'явитися таблиця, що зображена на рис.6.

Зробіть декілька перетворень цієї таблиці: змініть назви стовпчиків (через подвійне натиснення на поле з назвою), назви полів у стовпчику "Данные" (через локальне меню для цих полів), транспонуйте таблицю (через пересування поля "Данные" вліво).

Результати аналізу відобразіть на діаграмах.




Рис. 6

Контрольні запитання

1. Як записати формули, що обчислюють у годинах та добах інтервал між заданими моментами часу?
   
2. Створіть таблицю натуральних та десяткових логарифмів розміром 20 рядків. Перше значення аргументу задається у комірці А1, а крок до наступного значення аргументу – у В1.
   
3. Які функції використовуються при прогнозуванні зміни рядів даних?
   
4. Як за допомогою автофільтру знайти порожні комірки у таблиці?
   
5. Наведіть 2 приклади застосування табличних формул
   
6. Як створюється область критеріїв у посиленому фільтрі?
   
7. Що таке "Залежність комірок" і як її простежити?
   
8. Як здійснюється підбір параметрів?
   
9. Як здійснюється пошук рішення?
   
10. З якою метою створюються зведені таблиці і як вони будуються? Наведіть 2 приклади.
    
11. Які перетворення можна робити зі зведеними таблицями після їх побудови і в чому полягає користь таких перетворень?
    

Варіанти індивідуальних завдань для удоскона­лення матеріалу

Варіант 1. Розробіть таблицю та формули для розрахунку заробітної плати та податків бригади працівників з 5 осіб на рік. Врахуйте тарифну ставку, надбавки та квартальні премії. Застосуйте підбір параметрів для розрахунку надбавок і премій.


Варіант 2. Створіть таблицю-довідник по наявності товарів на складі та їх характеристикам – ціна, вага одиниці, кількість у упаковці, термін збереження, дата поставки на склад, тощо. Всього три групи товарів по 4 найменування у кожній. Зробіть зведену таблицю по групам товару та терміну збереження.


Варіант 3. Розробіть таблицю для аналізу динаміки продажу товарів. Врахуйте, що у вас працюють три продавці, у продажі є 5 найменувань товарів і дані заносяться до таблиці щоденно. Застосуйте механізм фільтрації та графічного відображення щоб проаналізувати ефективність праці кожного продавця за місяць.

Рекомендована література

1. Руденко В.Д. та ін. "Практичний курс інформатики". – К.: Фенікс, 1997. – с.203-212.
   
2. А.Колесников. Excel 7.0 для Windows 95. - К.: BHV, 1996. - с. 193-308.
   
3. Додж М. Кината К. Стинсон К. “Эффективная работа с Microsoft Excel 97”, - СПб: Питер, 1998. – с. 424-604.