Мастер диаграмм 1 страница

За допомогою кнопки Далее переходять до другого кроку, на якому визначають діапазон даних для побудови графіка і те, як ці дані розміщено в таблиці – у рядках чи в стовпчиках (рис. 53).

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

Після натиснення кнопки Далее відкриється третій крок. Тут визначають параметри майбутнього графіка (рис. 54).

Рис. 53. Діалогове вікно для Рис. 54. Визначення параметрів

визначення діапазону майбутнього графіка

та розміщення даних

Вкладку Заголовки використовують для визначення заголовків усього графіка (зона Название диаграммы) та заголовків для осей графіка (зони Ось Х, Ось У і Ось Z, якщо будують об’ємний графік).

Вкладку Подписи данных використовують у разі, якщо потрібно на графіку показати значення даних (рис. 55): покажчик Значение розміщує дані для Y на графіку; покажчик Доля (для кругової діаграми) розміщує значення відсотків даних Y від їх загальної суми; Категория – дані Х; Категория и доля – значення відсотків даних Y від їх загальної суми, і дані Х; покажчик Размеры пузырьков використовують для побудови булькової діаграми.

Вкладку Легенда – для показу/скасування показу умовних позначень для графіка та їх місцезнаходження (рис. 56).

Рис. 55. Вкладка Подписи данных Рис. 56. Вкладка Легенда

Вкладку Оси ‒ для показу/скасування показу відповідних осей на графіку. Якщо позначка ü є – вісь показана, якщо ні – вісь не показуєть­ся.

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

Рис. 57. Вкладка Таблица данных

Вкладку Линии сетки використовують для показу/скасування показу основних або додаткових ліній на графіку (потрібне слід позначити або зняти позначку).

На четвертому кроці визначають місце для побудова­ного графіка: він може розміщуватися або на окремому аркуші, або на поточному (рис. 58).

Рис. 58. Діалогове вікно Размещение диаграмм ы

Після вибору натискують кнопку Готово – і побудову графіка завершено.

3. Структура та форматування дiаграми. Майже всi дiаграми (крiм кругової й пелюсткової) мають дві головнi осі: горизонтальну – вісь категорiй, вертикальну – вiсь значень; об’ємнi дiаграми – ще третю вiсь (рядiв даних).

Діаграма складається з багатьох елементiв. Вигляд усiх елементiв можна змiнювати. Елементи дiаграми є об’єктами, над якими визна­че­нi дiї перемiщення та форматування. Розмiри дiаграми змiнюють, перетягуючи маркери габаритiв. А вигляд змінюють за допомогою команди Формат элемента... з його контекстного меню. Під час форматування можна, зокрема, замалювати елементи певним кольо­ром чи текстурою. На кожну поверхню дiаграми (сектор, прямокутник, стіну) можна помістити рисунок із файла, наприклад, зображення продукцiї, зокрема автомобiлiв, парфум тощо, чи iсторичних пам’ятни­кiв, що є символами мiст. Можна змiнити текстовi пiдписи елементiв і шрифт, яким вони виконанi.

Щоб уставити в дiаграму пропущений елемент, використовують команди Вставить – Диаграммы – Параметры диаграммы.

Звернiть увагу на те, що тип дiаграми можна будь-коли помiняти. Для цього дiаграму потрібно виокремити, викликати майстра дiаграм, вибрати iнший тип i натиснути на кнопку Готово.

Хід роботи

1. Побудувати таблицю Ріст населення за зразком (рис. 59).

Ріст населення
					Приріст населення
Європа
Африка
Америка

Рис. 59. Ріст населення

2. Підрахувати кількість населення в 1990 р. за такими даними:

У 1990 р. населення збільшилося:

- в Європі – на 6 %;

- в Африці – 35 %;

- в Америці – 18 %.

3. Підрахувати, на скільки відсотків збільшилося населення за останні 10 років, тобто між 2000 р. та 1990 р., за кожним пунктом.

4. Отриману таблицю Ріст населення відсортувати у полі 2000 р. за зростанням (пункт меню Данные / Сортировка). Простежити за переміщенням рядків таблиці.

5. Побудувати гістограму за таблицею Ріст населеня за 1970–2000 рр. Дати назву гістограмі й висвітлити легенду.

6. Побудувати кругову діаграму за одним із пунктів цієї таблиці, наприклад, за рядком “Європа”. Перенести цю діаграму на інший аркуш, збільшити розміри діаграми.

7. За допомогою контекстного меню внести зміни у надписи до діаграми: надписати частки даних чи їхні значення.

8. Уставити колонтитули: у верхньому колонтитулі написати назву таблиці, у нижньому – поточну дату.

9.Зберегти Книгу.

10.У текстовому редакторі Word написати за допомогою фігурного тексту назву статті та 3–4 речення, які б були присвячені темі “Ріст населення”.

11. Перенести за допомогою буфера обміну таблицю Ріст населення та діаграму з процесора Excel до набраного тексту.

12. Уставити готовий малюнок до тексту за цією ж тематикою або намалю­вати його в графічному редакторі. Розміщувати всі ці об’єкти так, щоб вийшла невелика замітка.

13. Покажіть виконане завдання викладачеві. Завершіть роботу.

Лабораторна робота № 22

Тема. МS Ехсеl. Робота з масивами.

Мета. Навчитись використовувати складні функцiї для роботи з маси­вами даних.

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

Одновимiрним масивом чисел називають послiдовнiсть чисел, узятих у фiгурні дужки, наприклад {1; 1,5; 4,2}. Двовимiрний масив містить набори чисел, якi розмежованi символом двокрапка (якщо числа вiдокремлюються крапкою з комою, й кома є десятковим роздiлювачем) або крапкою з комою (якщо роздiлювачі – кома та крапка, вiдповiдно) наприклад {1; 1,5; 4,2:1; 1,5; 4,2). Один набiр чисел вiдповiдає рядку чисел у зображеннi масиву у виглядi матрицi. Дiапазон із числами можна трактувати як масив чисел (матрицю).

Над матрицями-масивами визначено операцiї додавання, вiднiман­ня, множення на число, а також такi функції: для множення матрицi на матрицю – ММU (матриця 1; матриця 2), транспонування ТRАNSPOSE (масив чи дiапазон), обчислення оберненої матрицi – MINVERSE (масив) та детермінованої матриці – MDETERM (матриця).

Зауважимо, що дiапазони можна перемножувати, але це не тотожно добутковi масиви-матриці. Добуток двох дiапазонiв – це дiапазон із покомпонентно перемноженими елементами, що можна використати для розв’язування багатьох задач. Наприклад, вартiсть усiх товарiв (одне число) у задачi про товарний чек можна визначити за допомогою такої формули: {= SUM(Кількiсть × Цiна)).

Формулу для дiй із масивами чи дiапазонами називають формулою масиву. Перед виконанням дiй із масивами потрiбно вибрати порожнiй дiапазон клiтинок, де мiститиметься результат обчислення формули-масиву. Особливiсть дiй користувача така: пiсля набору формули в рядку формул її вводять у ЕТ не простим натисканням клавiшi Еntег, а комбiнацiєю клавiш Shift + Ctrl + Еntег. Формулу масиву буде записано у фiгурних дужках автоматично (їх не набирають).

Хід роботи

1. Розв’яжіть систему рівнянь як матричне рівняння, домно­живши обидві частини рівності на обернену до матрицю зліва: Для цього: введіть на лист матрицю та вектор-стовпчик. Виділіть у вільній частині листа діапазон, який має такий самий розмір, що й матриця (у нашому випадку 3 × 3) та обрахуйте обернену матрицю, використовуючи функцію МОБР (для того, щоб отримати всі коефіцієнти оберненої матриці, не знімаючи виділення, установіть курсор у рядку формул і натисніть сполучення клавіш CTRL + SHIFT + ENTER). Виділіть у вільній частині листа діапазон, який має такий самий розмір, що й вектор невідомих (у нашому випадку – стовпчик 3 × 1) та обрахуйте добуток оберненої матриці на вектор-стовпчик, ско­риставшись функцією МУМНОЖ (для того, щоб отримати всі коефіцієнти стовпчика невідомих, не знімаючи виділення, установіть курсор у рядку формул і натисніть сполучення клавіш CTRL + SHIFT + ENTER).

Варіанти (за номером списка в журналі):

				-5		-8		-8				-1					-8	-10			-5						-4		-7
		-8		-4		-2	-10	-3	-2				-8			-9	-6	-10	-7		-9	-7					-4		-8
	-7	-8	-4			-8	-7	-10	-5		-8	-5		-2					-7			-2	-5				-11	-3
			-4	-6			-4	-4			-8	-10	-4	-6		-9			-3		-3	-7	-6	-5		-3	-4
		-2	-2			-7	-8		-8		-11		-5	-7		-9	-9	-10			-7	-4	-1			-9			-7
	-9	-7	-2	-5				-4	-2			-8		-2		-1			-2			-1		-7		-9			-7
		-5	-8
		-5

2. Знайдіть один із коренів нелінійного рівняння типу

Для цього оберіть дві довільні комірки: 1) для зберігання значен­ня невідомого х; 2) для зберігання значення лівої частини даного нелінійного рівняння. Уведіть у комірку 2) формулу лівої частини нелінійного рівняння (відповідно до варіанта). Знайдіть один із коренів рівняння за допомогою Сервис / Подбор параметра, вказав­ши в першому рядку діалогового вікна Подбор параметра адресу комірки 2); у другому – значення 0; у третьому – адресу комірки 1).

В.	a	B	c	В.	A	b	c	В.	a	b	c
	-18,06	103,72	-191,18		1,72	-85,39	-356,17		3,66	-63,39	-281,65
	7,17	-91,34	-678,38		-3,40	-53,28	-102,70		-14,27	67,28	-104,93
	9,23	-104,39	-964,63		-2,66	-91,66	233,59		6,90	-105,22	-748,13
	1,14	-66,89	-225,26		7,06	-116,63	-827,40		8,39	-63,88	-423,99
	25,28	212,11	590,53

3. Знайдіть розв’язокзадачі при Для цього оберіть три довільні комірки – 1), 2) для зберігання значень невідомих 3) для зберігання значення лінійної форми. Уведіть у комірку 3) формулу даної лінійної форми. Знайдіть розв’язок задачі оптимізації за допомогою програми Сервис / Поиск решения (УВАГА! Наявність програми Поиск решения залежить від повноти установки пакета Microsoft Office. Якщо цієї програми немає в меню Сервис, перевірте, чи встановлено цей компонент: Сервис / Надстройки / Поиск решения. Укажіть адресу комірки 3) як цільової комірки; оберіть положення перемикача макс/мін відповідно до завдання; укажіть адресу комірок 1), 2) як тих, що змінюються; уведіть обмеження згідно з індивідуальним завданням (обмеження вигляду треба записувати як ).

Варіанти (за номером списку в журналі):

В.	a1	a2	max/min	b1	b2	b3	c1	c2	В.	a1	a2	max/min	b1	b2	b3	c1	c2
			min	-10	-6	-2						max	-5	-10
	-7	-10	max	-6	-3	-4				-1		min	-10	-8
	-6	-9	min	-7	-9	-3						max	-2	-4
		-3	min	-9	-5						-6	max	-9	-5	-7
	-10	-10	max	-4	-1					-6	-4	max	-6	-9	-10
	-7	-3	min	-5	-3						-6	min	-10	-7
	-3	-3	min	-1	-7	-8

4.Продемонструйте три аркуші викладачеві та завершіть роботу.

Лабораторна робота № 23

Тема. МS Ехсеl. Задачi апроксимацiї й прогнозування даних. Метод найменших квадратiв. Елементи регресiйного аналiзу. Побудова лiнiй тренду на дiаграмах.

Мета. Навчитися використовувати математичнi функцiї для роботи з масивами даних i статистичнi функцiї для дослiдженяя тенденцiй (тренду) в даних.

План

1. Апроксимація. Регресійний аналіз.

2. Типи ліній тренду. Побудова лiнiй тренду

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

1. Апроксимація. Регресійний аналіз. Для розв’язування задач про згладження експериментальних даних та апроксимацію (набли­ження) даних деякою нескладною аналітичною функцією з метою використання цієї функції для прогнозування подальших змiн даних використовують регресійний аналіз. Будь-якi експеримен­таль­ні данi можна однозначно апроксимувати лінією (функцією рівняння) – дея­кого типу прямою лінією, логарифмічною, поліноміальною чи експо­ненціальною кривою за принципом найменших квадратів – так, щоб сума квадратiв відхилень апроксимованих значень вiд експери­ментальних була мінiмальною.

Заздалегідь складно визначити, який тип функції є оптимальним для конкретних даних, зокрема, якщо їх багато. Тому якiсть апрокси­мації оцiнюють на пiдставi критерію, який називається “Критерій R-квадрат” (використовують також позначення r2). Значення r2 для рiзних функцiй буде рiзним. Апроксимація вважається тим кращою, чим ближче значення r2 до числа 1, та ідеальною, якщо r2 = 1.

Нехай у деякій однофакторнiй задачi кількість експеримен­таль­них даних п, значення фактора (незалежної величини аргументу функції утворюють масив чисел х1, х2, х3,..., хп, значення експериментальних даних утворюють масив у1, у2,..., уп. Нехай для апроксимації вибрано й визначено функцію f(x).

Тодi r2 обчислюють так:

r2 = 1 - Е/Т, де Е = Σ(у1 - f(x1))². Т = Σy²_i -(Σy_i)²/n, а суми – за iндексом i вiд 1 до п.

Найчастiше припускають, що тренд має лiнiйний характер. На основi цього вибирають функцiю f(x) = mx + b. Ріняння вигляду у = f(x) називають рiвнянням регресії. Числа m та b отримують із вхiдних даних за допомогою такого алгоритму:

1) х_с= Σ х_і/п — середнє значення фактора;

2) у_с = Σу_і/п — середнє значення експериментальних даних;

3) m = Σ (х_і — х_с)(у_і —у_с)/ Σ (х_і — х_с)²;

4) b = у_с – m × х_с.

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

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

Лінії тренду можна додати до рядів даних Кожний ряд даних на діаграмі має власний колір або інший спосіб позначення й поданий на легенді діаграми. Діаграми всіх типів, за винятком кругової, можуть містити декілька рядів даних, представлених на ненормованих плоских діаграмах з областями, лінійчаcтих діаграмах, гістограмах, графіках, біржових, точкових і булькових діаграмах. Неможливо додати лінії тренду до рядів даних на об’ємних діаграмах, нормо­ваних, пелюсткових, кругових і кільцевих діаграмах. У разі заміни типу діаграми на одну із названих вище – наприклад, якщо змінити тип діаграми на об’ємну або змінити подання звіту зведеної діаграми чи зв’язного звіту зведеної таблиці, – лінії тренду, які відповідають даним, будуть утрачені.

2. Типи ліній тренду. Є шість різних типів ліній тренду (апрокси­мація та згладжування), які можна додати до діаграми Microsoft Excel. Тип лінії тренду потрібно вибирати, виходячи з типу даних.

Лінійнаапроксимація – це пряма лінія, яка найкраще описує сукупність даних. Її застосовують в найпростіших випад­ках, коли точки даних розміщені близько до прямої. Лінійна апрокси­мація підхо­дить для величини, яка збільшується або зменшується з постійною швидкістю.

У поданому нижче прикладі пряма лінія описує стабільне зро­стання продажу холодильників протягом 13 років. Слід звернути увагу, що R-квадрат дорівнює 0,9036, тобто близький до одиниці, що засвідчує високий ступінь збігання лінії з даними.

Логарифмічнуапроксимацію використовують для опису величини, яка спочатку швидко зростає або зменшується, а потім поступово стабілізується. Логарифмічна апроксимація вико­ристовує як від’ємні, так і додатні значення. У наведеному нижче прикладі логарифмічна апроксимація описує прогнозоване зростання популяції тварин, які живуть в ареалі з фіксованими межами. Швид­кість зростання популяції зменшується через обмеженість їх життє­вого простору. Слід звернути увагу, що значення R-квадрат дорівнює 0,9407, що засвідчує високий ступінь збігання лінії з даними.

Поліноміальну апроксимацію використовують для опису вели­чин, які поперемінно зростають та зменшуються. Наприклад, її використовують для аналізу великої сукупності даних. Ступінь полінома визначається кількістю екстремумів (максимумів і міні­мумів) кривої. Поліном другого степеня може описати лише один максимум або мінімум. Поліном третього степеня має один або два екстремуми. Поліном четвертого степеня може мати не більше трьох екстремумів. У наведеному нижче прикладі поліном другого степеня (один максимум) описує залежність витрати бензину від швидкості автомобіля. Слід звернути увагу, що значення R-квадрат дорівнює 0,9474, тобто близьке до одиниці, що засвідчує високий ступінь збігання лінії з даними.

Степеневу апроксимація використовують для опису монотонно ростучої або монотонно спадної величини, наприк­лад відстані, яку проходить автомобіль під час розгону. Неможливо створити степеневу апроксимацію, якщо дані містять нульові або від’ємні значення. У наведеному нижче прикладі показано залежність відстані, яку пройшов автомобіль під час розгону, від часу. Відстань виражено в метрах, час – у секундах. Ці дані точно описано степене­вою залежністю. Слід звернути увагу, що значення R-квадрат дорівнює 0,9923, що засвідчує високий ступінь збігання лінії з даними.

Експоненційну апроксимацію використовують у тому випадку, якщо швидкість зміни даних безперервно зростає. Неможливо створити експоненційну апроксимацію, якщо дані містять нульові або від’ємні значення. У наведеному нижче прикладі експоненційна лінія тренду описує вміст радіоактивного вуглецю-14 залежно від віку органічного об’єкта. Слід звернути увагу, що значення R-квадрат дорівнює 1, що засвідчує збігання лінії з даними.

Змінне середнє згладжує відхилення в даних і чіткіше показує форму лінії тренду. Лінію будують за певним числом точок (яке визначається параметром Точки). Елементи даних усереднюють, а отриманий результат використовують як середнє значення для апроксимації. Якщо значення параметра Точки дорівнює 2, перша точка кривої визначається як середнє значення перших двох елементів даних, друга – як середнє значення наступних двох елементів і так далі. У поданому нижче прикладі показано залежність обсягу продажу протягом 26 тижнів, її отримано методом обчислення змінного середнього.

Розглянемо алгоритм додавання лінії тренду до дiаграми.

1. Побудувати дiаграму для ряду експериментальних даних.

2. Клацнути правою клавішею на маркері ряду даних, для яких потрібно побудувати лінію тренду – отримаємо контекстне меню ряду даних.

3. Виконати команду Добавить линию тренда.

4. На закладці Тип вибрати тип лінії тренду.

5. На закладці Параметры задати назву кривої (можна не задавати), довжину відрізка (в одиницях зміни аргументу) прогнозу, координату точки перетину з вiссю У (можна не задавати) зобразити рiвняння регресії на дiаграмі, розмістити на дiаграмi значения г2.

6. Натиснути ОК.

Хiд роботи

1. Запустіть програму Excel. Відкрийте нову книжку. Назвіть її Таблиця.

2. Створіть таблицю про температуру повітря протягом двадцяти днів місяця згідно з індивідуальним варіантом (див. Додаток).

3. Побудуйте на наступному аркуші графік за даними таблиці. Збережіть лист як Графік.

4. Спрогнозуйте температуру повітря на інші десять днів місяця, використовуючи лінійну, логарифмічну, степеневу, експонен­ціальну та поліноміальну 6-го степеня апроксимацію.

Виділіть графік, установивши курсор і клацнувши лівою кнопкою мишки. Викличте контекстне меню, оберіть пункт Добавить линию тренда. На вкладці Тип задайте тип лінії тренду, а на вкладці Параметры – Прогноз на 10 дн; Показывать уравнение на диаграмм е; Поместить на диаграмме величину достоверности аппроксимации. Винесіть надпис із формулою і показником R² за межі області побудови та підпишіть його.

Дата добавления: 2015-11-14