Додаткові засоби аналізу даних в Excel 2010

До засобів, що забезпечують користувачеві додаткові можливості аналізу даних в електронних таблицях, відносять підбір параметра, пошук рішення, функції обробки списків і зведені таблиці.

До складу Excel 2010 входить нова версія надбудови «Пошук рішення», яка має поліпшений користувальницький інтерфейс Evolutionary Solver, заснований на алгоритмах генетичного аналізу, для роботи з моделями, що включають будь-які функції Excel. У ній передбачені нові глобальні параметри оптимізації, поліпшені методи лінійного програмування й нелінійної оптимізації, а також нові звіти про лінійність і допустимість.

4.1. Підбір параметра

Підбір параметра призначений для простого аналізу даних типу «що-якщо». За допомогою цього засобу можна вирішувати рівняння з одним невідомим. Підбір невідомого значення виконують методом «зворотних обчислень» (послідовним наближенням невідомого значення в комірці, що впливає на результат, до певного заданого значення результату в цільовій комірці. Технологія підбору параметра така:

1) за необхідності ввести в комірки аркуша постійні величини, що впливають на результат;

2) зарезервувати комірку під невідому величину (впливова комірка);

3) увести в цільову комірку формулу з посиланням на впливову;

4) установити курсор у цільову комірку;

5) вибрати вкладку стрічки Дані - Аналіз «що якщо» – Підбір параметра;

6) у вікні підбору параметра вказати бажане значення результату та впливову комірку (рис. 26).

Приклад: Продаж кави, відома ціна однієї чашки (1,75 грн), формула для визначення виторгу (виторг = ціна чашки * кількість). Необхідно визначити кількість чашок, за якої виторг досягне значення 5000 грн.

Рис. 26. Підбір параметра

4.2. Пошук рішення

Використовується для розв’язання задач оптимізації - пошуку мінімуму, максимуму або заданого значення функції декількох змінних з урахуванням обмежень на значення змінних та їх співвідношення.

Функція Пошук рішення перебуває на вкладці стрічки Дані і може бути відсутня в Excel 2010 при стандартному варіанті установки пакета Microsoft Office. Для додавання функції Пошук рішення необхідно застосувати настроювання стрічки Файл - Параметри – Надбудови, вибрати в нижній частині вікна розділ Керування: Надбудови Excel, натиснути кнопку [Перейти] і включити опцію «Пошук рішення». За наявності установчих файлів Microsoft Office компонент буде автоматично доданий до функцій вкладки Дані.

Відправною в процесі пошуку оптимального рішення є створена на робочому аркуші модель обчислення, яка включає:

- Цільову комірку, значення в якій повинне бути максимізоване, мінімізоване або ж дорівнювати певному значенню; містить формулу, що прямо або опосередковано посилається на змінювані комірки;

- Змінювані комірки - невідомі задачі, комірки, значення в яких будуть послідовно (методом ітерацій) змінюватися доти, поки не буде отримане оптимальне значення в цільовій комірці;

- Обмеження на співвідношення змінних - формули з посиланнями на змінювані комірки.

У моделі пошуку рішення допускається до 200 змінюваних комірок (змінних), по два обмеження для кожної змінюваної комірки та 200 обмежень для всієї моделі.

Технологія пошуку рішення така:

1) за необхідності ввести в комірки аркуша сталі величини, які впливають на результат;

2) зарезервувати комірки під невідомі величини (змінювані комірки);

3) увести в цільову комірку формулу із прямими або непрямими посиланнями на змінювані комірки;

4) увести формули для обмежень із посиланнями на змінювані комірки;

5) установити курсор у цільову комірку;

6) вибрати функцію вкладки Дані - Пошук рішення;

7) у вікні пошуку рішення вказати вид оптимізації та діапазон змінюваних комірок (рис. 27);

8) для введення обмежень натиснути кнопку [Додати] у вікні пошуку рішення (рис. 28), для введення наступного обмеження натиснути кнопку [Додати] у вікні додавання обмежень, для завершення - кнопку [ОК];

9) натиснути кнопку [Знайти рішення] у вікні пошуку рішення.

 

Рис. 27. Вікно пошуку рішення

Рис. 28. Вікно додавання обмежень під час пошуку рішення

В економічних задачах лінійної оптимізації, як правило, необхідні так звані природні обмеження на змінювані комірки - їх значення повинні бути невід’ємними (>= 0) та цілими, перше обмеження можна задати за допомогою опції у вікні пошуку рішення (рис. 27), а друге – за допомогою кнопки [Параметри] у вікні пошуку рішення (рис. 27) – після її натиснення необхідно відключити опцію «Ігнорувати цілочислені обмеження».

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

Обробка списків (баз даних)

У загальному значенні термін база даних можна застосувати до будь-якої сукупності зв'язаної впорядкованої інформації, об'єднаної за певною ознакою, наприклад телефонні списки, списки клієнтів, транзакцій, активів, пасивів і т. ін. Основне призначення баз даних - швидкий пошук інформації.

Одним з найбільш часто виконуваних за допомогою електронних таблиць завдань є обробка списків. Електронні таблиці мають набір функцій, призначених для аналізу списків, в Excel 2010 ці функції розширено та покращено.

Список - таблиця, побудована за принципом бази даних, складається з рядків і стовпців однакової структури (відсутні об'єднання комірок), які в базах даних називаються записами і полями відповідно.

Кожен рядок таблиці-списку можна розглядати як одиничний запис. Інформація в межах кожного запису міститься в полях (стовпцях) з унікальними заголовками. Кожен стовпець повинен містити однорідну інформацію. Необхідно уникати порожніх рядків і стовпців усередині списку.

На робочому аркуші можна обробляти кілька списків, але все-таки краще помістити кожен список на окремий аркуш робочої книги.

Засоби обробки списків Excel можна використовувати, коли курсор перебуває в межах таблиці-списку, вони вибираються на вкладці стрічки Дані. До функцій обробки списку належать найпростіші функції обробки баз даних: використання форм, сортування, фільтрація та проміжкові підсумки.

Робота з командою Форма. Команда Форма відображає на екрані форму, що являє собою вікно, призначене для перегляду та редагування записів у базі даних, а також для додавання нових і видалення існуючих записів. Крім того, за допомогою форми можна здійснити пошук конкретних записів на підставі складних критеріїв. Для використання форми в Excel 2010 її потрібно додати до вкладки Дані за допомогою настроювання Файл - Параметри – Настройка стрічки, далі слід обрати категорію «Всі команди» і додати команду Форма в нову групу. Після цих настроювань можна використати вікно форми (рис. 29).

Кожному стовпцю таблиці у формі відповідає поле, найменування якого визначається заголовком стовпця. Крім полів форма містить ряд кнопок й індикатор номера запису. Кнопка Додати призначена для введення в базу даних нового запису. У результаті натиснення цієї кнопки Excel очистить всі поля форми, після чого можна ввести необхідні дані. Уведений запис додається в кінець бази даних.

Рис. 29. Вікно форми

Для пошуку записів, що задовольняють певні умови, у вікні форми потрібно натиснути кнопку [Критерії], після цього з'явиться порожнє вікно запису, у відповідних полях якого вводяться умови. Для перегляду записів, що задовольняють критерії, потрібно використати у вікні критеріїв кнопки [Назад] і [Далі].

Сортування бази даних. Процес упорядкування записів у базі даних називається сортуванням. Під час сортування змінюється порядок проходження записів у базі даних або таблиці, для цього використовується функція вкладки Дані - Сортування з групи Сортування і фільтр, яка встановлює порядок рядків у таблиці відповідно до вмісту конкретних стовпців.

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

У разі вибору команди Сортування відкривається вікно діалогу “ Сортування ”, в якому слід додати рівні (стовпці) для сортування та визначити критерій. За допомогою списку, що розкривається, Сортувати по можна вибрати поле для сортування. Порядок сортування встановлюється перемикачами за зростанням або убуванням (рис. 30). Додаткові розділи «Потім за» дозволяють визначити порядок вторинного сортування для записів, в яких є збіжні значення.

 

Рис. 30. Вікно сортування списку

Фільтрація списку. Фільтрація списку дозволяє знаходити і відбирати для обробки частину записів у списку, таблиці, базі даних. У відфільтрованому списку виводяться на екран тільки ті рядки, які містять певне значення або відповідають певним критеріям. При цьому інші рядки виявляються прихованими.

В Excel 2010 для фільтрації даних використовуються функції вкладки стрічки Дані групи Сортування і фільтр - Фільтр (автофільтр) і Додатково – Розширений фільтр. У випадку, якщо критерії прості, то для вибірки потрібної інформації достатньо команди Фільтр. У комірках виділених заголовків з'являться нові елементи , за допомогою яких можна встановлювати критерії фільтрації даних.

Для перевірки одного або декількох умов використовується команда Числові (або інші) фільтри в списку фільтра, що випадає (рис. 31).

Рис.31. Вибір критерію фільтрації

У разі використання складних критеріїв слід застосовувати команду Додатково - Розширений фільтр групи Сортування і фільтр. У такому випадку умови фільтрації задаються у вигляді окремої таблиці (рис.32).

Рис. 32. Застосування розширеного фільтра

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

Підбиття підсумків у базі даних. Один зі способів обробки й аналізу бази даних полягає в підбитті підсумків за різними ознаками. За допомогою команди Проміжний підсумок групи Структура вкладки стрічки Дані можна вставити рядки підсумків у список, здійснивши підсумовування даних потрібним способом. У разі вставки рядків підсумків в електронних таблицях в кінець списку автоматично додається даних рядок загальних підсумків. Перед підбиттям підсумків необхідно відсортувати базу даних за аналізованою ознакою.

У вікні Проміжні підсумки (рис. 33) необхідно вибрати операцію (підсумкову функцію), що буде застосовуватися для груп записів, які утворюються за кожної зміни значення у відсортованому аналізованому стовпці, і вказати, в якому зі стовпців буде відображатися підсумок.

У списку із проміжними підсумками можна застосувати три рівні угруповання даних (рис. 33). Для скасування підсумків у вікні Проміжні підсумки потрібно натиснути кнопку [Видалити все].

 

Рис. 33. Підбиття підсумків

Створення зведених таблиць

Зведена таблиця являє собою інструмент для аналізу великих масивів даних. Джерело даних для зведеної таблиці - список (база даних), зі значень полів якого формується аналітична підсумкова (зведена) таблиця для аналізу за обраними ознаками. В списку є обов'язковою наявність унікальних заголовків стовпців (імен полів). Для створення зведеної таблиці використовується функція Зведені таблиці групи Таблиці вкладки стрічки Вставка, далі визначається вміст і макет зведеної таблиці.

Технологія створення зведеної таблиці така:

1) помістити покажчик у будь-яку комірку в межах таблиці-списку;

2) вибрати вкладку стрічки Вставка, команду Зведена таблиця;

3) вказати джерело даних у вікні «Створення зведеної таблиці», краще розмістити зведену таблицю на новому аркуші (рис. 34);

4) сформувати зведену таблицю перетягуванням заголовків полів у відповідні області (фільтр звіту, рядок, стовпчик, область даних) - значення полів утворять вміст зведеної таблиці, фільтр звіту використовується для фільтрації даних за його вмістом (рис. 35).

У результаті створюється зведена таблиця на окремому аркуші, що буде вставлений перед поточним аркушем у робочу книгу (рис. 36).

Разом зі зведеною таблицею на аркуші автоматично з'являється вікно Список полів зведеної таблиці, яке можна використовувати для зміни її структури, те ж саме можна виконати перетягуванням полів у відповідні частини таблиці.

 

Рис. 34. Вікно діалогу для створення зведеної таблиці

Рис. 35. Створення зведеної таблиці

 

Рис. 36. Зведена таблиця

Для обчислення підсумкових значень полів зведених таблиць автоматично застосовується підсумовування, однак замість нього можна використати інші функції, наприклад «Середнє» або «Максимум». Для цього потрібно клацнути правою кнопкою миші в полі «Загальний підсумок» зведеної таблиці, вибрати в контексті Параметри поля і у вікні «Параметри поля» вибрати зі списку підсумкову функцію (рис. 37).

Рис. 37. Вибір підсумкової функції для зведеної таблиці

Лабораторні роботи

Лабораторна робота 1
Основи роботи в табличному процесорі Excel

Завдання

1. Форматування чисел. Ввести в комірки одне й те саме число - 1234,5.
Звести числа до вигляду, наведеного нижче:

1234,5

1 234,50

$1 235

? 1 234,50

18 травень 03

18.05.03 12:00

1234 1/2

1,235E+03

2. Введення тексту. Ввести наступний текст у комірки (Формат - Комірки, Вирівнювання, Переносити за словами):

Реальні дані 2011 р.

Реальні дані 2011 р.

Реальні дані 2011 р.

3. Застосовуючи метод автозаповнення, створити наведені нижче списки:

Січ	Пн	Товар 1	Тов. 1 Зам.3			2,1	5,4
Лют	Вв	Товар 2	Тов. 1 Зам.4			2,3
Бер	Ср	Товар 3	Тов. 1 Зам.5			2,5	4,6
Кві	Чт	Товар 4	Тов. 1 Зам.6			2,7	4,2
Тра	Пт	Товар 5	Тов. 1 Зам.7			2,9	3,8

4. Форматування таблиць. Створити таблицю, для підсумкових комірок застосувати автосуму. Застосувати різні стилі форматування таблиці.

Обсяг реалізації
	Січ	Лютий	Бер	Усього
Товар 1	300,00 грн	250,00 грн	360,00 грн	910,00 грн
Товар 2	450,00 грн	400,00 грн	500,00 грн	1 350,00 грн
Товар 3	600,00 грн	550,00 грн	640,00 грн	1 790,00 грн
Товар 4	750,00 грн	700,00 грн	780,00 грн	2 230,00 грн
Товар 5	900,00 грн	850,00 грн	920,00 грн	2 670,00 грн
Разом	3 000,00 грн	2 750,00 грн	3 200,00 грн	8 950,00 грн

5. Застосовуючи формули з абсолютними, відносними та змішаними посиланнями, створити: а) таблицю множення; б) таблицю приросту депозитного внеску в 10 доларів для різних процентних ставок і тимчасових періодів за формулою складних відсотків: сума внеску*(1+%)^період.

									Період, років	Відсоткова ставка
									10%	15%	20%	25%
										$11,00	$11,50	$12,00	$12,50
										$12,10	$13,23	$14,40	$15,63
										$13,31	$15,21	$17,28	$19,53
										$14,64	$17,49	$20,74	$24,41
										$16,11	$20,11	$24,88	$30,52
										$17,72	$23,13	$29,86	$38,15

Лабораторна робота 2
Застосування вбудованих функцій в Excel

Завдання

1. Обчислити за допомогою функції КОРІНЬ:
; ; ; .

2. Обчислити для a і b

3. Застосовуючи формули з абсолютними і відносними посиланнями та функцію ЯКЩО, розрахувати комісійні менеджерів із продажів, виходячи з умови, що звичайні комісійні становлять 5,5% від обсягу продажів. Якщо обсяг продажів перевищує $150000, то виплачуються комісійні за преміальною ставкою 7%.

Нормований обсяг продажів	$150 000
Звичайна ставка	5,5%
Преміальна ставка	7,0%
Менеджер	Обсяг продажів	Комісійні
Іванов	$120 000
Петров	$160 000
Сидоров	$150 000

4.

За допомогою математичної функції РИМСЬКЕ, перевести арабські числа в римські:

5. Заповнити таблицю, застосовуючи функцію СУМА, а також статистичні функції МАКС, МІН, СРЗНАЧ:

						Сума	Макс.	Мін.	Средн.
Сума
Макс.
Мін.
Средн.

6. За допомогою функції ДЕНЬНЕД визначити день тижня для зазначених дат:

12/12/1900	5/03/1969	31/01/1985	24/06/1996	8/04/1999	1/01/2012

Лабораторна робота 3
Побудова діаграм в Excel

Завдання

1. Для таблиці обсягів реалізацій товарів (з лабораторної роботи 1) обчислити процентні частки місячних продажів у кварталі. Побудувати кругову діаграму для процентних часток і гістограму для порівняння місячних обсягів реалізації товарів. Забезпечити наявність підписів зі значеннями відсотків для кругової діаграми і легенди для обох діаграм. Додати до таблиці стовпчик зі спарклайнами (інфокривими) для перегляду тенденцій продажу товарів.

2. Використовуючи змішані посилання у формулах, заповнити таку таблицю:

Коефіцієнт редукції R грошових витрат до середнього оптимального попиту на гроші в Україні
Рік	Норма відсотків за строковими депозитами, i	Інфляція цін (дефлятор), p	R за умови k=0.9	R за умови k=0.5	R за умови k=0.1
	3,0%	92,4%
	68,0%	869,7%
	187,3%	3333,6%
	171,0%	954,3%
	61,2%	413,5%
	34,3%	64,1%
	15,0%	25,0%

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

4. Побудувати за таблицею графіки коефіцієнтів редукції. Забезпечити наявність легенди, горизонтальної та вертикальної сітки.

5. Побудувати діаграму із двома осями значень для даних із таблиці (дві осі Y: права вісь для трьох графіків значень R, ліва вісь для гістограми значень дефлятора p). Забезпечити наявність легенди, назви діаграми та вісей, розмістити діаграму на окремому аркуші.

6. Знайти графічно корінь рівняння , якщо x змінюється в діапазоні від 0° до 360° із кроком 5°.

7. Побудувати діаграму типу Поверхня півсфери для x,y у діапазоні від –4 до 4 із кроком 0,5.

8. Графічним способом знайти корінь рівняння

для В від 3 до 6 і С від 2 до 5.

Лабораторна робота 4
Аналіз даних в Excel

Завдання

1. Виконати підбір параметра для такого завдання: відома ціна 1 чашки кави, формула для визначення виторгу (виторг = ціна чашки * кількість), необхідно визначити кількість чашок, за якої виторг досягне значення 30000.

Продаж кави
Ціна 1 чашки	1,75
Кількість чашок
Необх. виторг

2. Виконати пошук рішення для такої задачі: відома ціна однієї чашки для різних видів кави і максимальна кількість чашок, необхідно максимізувати виторг при заданих обмеженнях.

	Продаж кави
Ціна звичайної кави	1,75		Виторг:	0,00
Кількість чашок
Разом	0,00		Звичайний
			Особливий
Ціна кави з вершками	2,00		Разом чашок
Кількість чашок
Разом	0,00		Обмеження:
			Усього чашок	<=500
Ціна кави із шоколадом	2,25		Max особливого	<=350
Кількість чашок			Max шокол.	<=125
Разом	0,00

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

Матеріали	Кількість матеріалів, що витрачають на виріб	Є на складі	Витрата	Залишок
Виріб 1	Виріб 2	Виріб 3	Виріб 4	Виріб 5
Тканина
Червона фарба
Синя фарба
Прибуток з одиниці	15грн	30грн	20грн	25грн	25грн
Кількість
Прибуток
Прибуток із всіх виробів	Цільова комірка

 

 

Лабораторна робота 5
Обробка списків, створення зведених таблиць

Завдання

1. Скопіювати та відкрити в Excel файл
Мережне оточення: comp...01\D:Загальний доступ\Книга продажів.xls.

2. Застосувати форму для перегляду списку.

3. Використати Критерій форми для перегляду записів про замовлення товарів у кількості більше 100 одиниць.

4. Відсортувати список за зростанням дати продажів.

5. Використовуючи Фільтр, вивести записи про продажі з виторгом більше 1000.

6. Використовуючи Фільтр, вивести записи про продажі зі знижкою.

7. Застосувати розширений фільтр для виведення записів про продажі зі знижкою 10% з кількістю проданого більше 100 одиниць.

8. Скопіювати список на новий аркуш, відсортувати за кодом покупця і підбити підсумки для визначення виторгу за кожним покупцем.

9. Одержати дані про кількість замовлених товарів кожного виду.

10. Сформувати зведену таблицю, що включає дані про покупців, товари, дати замовлень та їх сумарну вартість, переглянути дані для окремих покупців.

11. Змінити структуру готової зведеної таблиці.

12. Проаналізувати операції, використовувані для обчислення значень полів зведених таблиць. Застосувати функції «Середнє» та «Максимум» замість підсумкового підсумовування.

13. Сформувати зведену таблицю, що містить елементи деталізації - додати в стовпці Код товару дані про Ціну одиниці.

14. Сформувати зведену діаграму, що відображає дані таблиці, змінити структуру діаграми.

Список рекомендованої літератури

Весь Office 2010. 8 книг в 1. Полное руководство. / Тихомиров А.Н., Колосков П.В., Прокди Р.Г. – СПб.: Наука и Техника, 2011. – 624 с.

Економічна інформатика [Текст]: навчальний посібник / Григорків В.С., Маханець Л.Л., Білоскурський Р.Р., Якутова О.Ю., Верстяк А.В. – Чернівці: Книги – ХХІ, 2008. – 464 с.

Курбатова, Е.А. Microsoft Office Excel 2010. Самоучитель [Текст] / Е.А. Курбатова. – М.: ООО «И.Д. Вильямс», 2010. – 416 с.

Основи комп'ютерних технологій для економістів [Текст]: навч. посібник / Зацеркляний М.М., Мельников О.Ф., Струков В.М. – К.:
ВД «Професіонал», 2006. – 672 с.

Зміст

 

Вступ. 3

1. Основи роботи в табличному процесорі Excel 2010. 3

1.1. Оновлене робоче середовище Excel 2010. 3

1.2. Керування книгами й аркушами. 7

1.3. Введення й редагування даних, автозаповнення. 9

1.4. Форматування даних. 13

2. Обчислення в Excel 2010, формули та функції 15

2.1. Створення формул. 15

2.2. Копіювання формул. Відносні й абсолютні посилання. 17

2.3. Застосування функцій. 18

3. Графічний аналіз даних в Excel 2010. 21

3.1. Створення діаграм.. 22

3.2. Побудова графіків функцій. 24

3.3. Створення нестандартних діаграм.. 24

3.4. Створення діаграм типу Поверхня. 25

3.5. Використання спарклайнів (інфокривих) 26

4. Додаткові засоби аналізу даних в Excel 2010. 27

4.1. Підбір параметра. 27

4.2. Пошук рішення. 28

4.3. Обробка списків (баз даних) 30

4.4. Створення зведених таблиць. 34

5. Лабораторні роботи. 37

Лабораторна робота 1 Основи роботи в табличному процесорі Excel 37

Лабораторна робота 2 Застосування вбудованих функцій в Excel 38

Лабораторна робота 3 Побудова діаграм в Excel 39

Лабораторна робота 4 Аналіз даних в Excel 40

Лабораторна робота 5 Обробка списків, створення зведених таблиць. 41

Список рекомендованої літератури. 41

 

 

 

 

Темплан 2012, поз.

Методичні вказівки

до самостійної роботи над розділом

"Застосування Microsoft Excel 2010"

Укладач старш. викл. Ю.А. Басс

 

 

Редактор

Коректор

Техредактор

 

Підписано до друку Формат 60х84/16.

Папір друкарський. Друк плоский. Ум. друк. арк.

Ум. фарбовідб. Обл.-вид. арк.

Тираж 100 пр. Зам. №

 

РВВ ДНУ, просп. Гагаріна, 72, м. Дніпропетровськ, 49010.

Друкарня ДНУ, вул. Наукова, 5, м. Дніпропетровськ, 49050