Сервис / Параметры / закладка Вид.

3. Уведіть дані, як показано на рис. 37:

№ з/п

Груші

Звіт із продажу фруктів

Рис. 37. Дані до завдання 3

У стовпці Сума застосуйте формулу. Наприклад, = С3 × D3 і т. д. Використайте метод копіювання формул.

4. Відформатуйте стовпець Сума.

Відцентруйте дані, збільшіть розрядність числового формату, виділіть дані жирним шрифтом і візьміть у рамку. Скористайтеся командою Формат / Ячейки …

5. Повторіть усе це для стовпця D.

6. Скопіюйте таблицю на Аркуш 2

7. Поверніться на Аркуш 1 і продовжіть ручне форматування таблиці. Рядок із заголовком виділіть жирним шрифтом червоного кольору. Фон зробіть синім, а шрифт – білим кольором. Стовпець Товари затонуйте в жовтий колір, а Кількість – у зелений. Розграфіть таблицю.

8. На Аркуші 2 проведіть автоформатування таблиці. Тип авто­форма­тування виберіть довільний, поекспериментуйте. За допо­могою режиму відображення формул перевірте правильність формул у стовпці Сума.

9. Скопіюйте дані з Аркуша 2 на Аркуш 3.

10. Змініть дані в стовпцях Кількість і Ціна. Значення в стовпці Сума зміняться.

11. Виділіть комірку Е14 і знайдіть суму значень у стовпці Сума.

12. На Аркуші 4 проведіть консолідацію трьох таблиць із листів 1, 2 та 3.

Скористайтеся командою Данные / Консолидация. Збережіть книжку під назвою Консолідація.

13. Уведіть дані, як показано на рис. 38.

Рис 38. Дані до завдання 13

14. Перший стовпець заповніть, користуючись командою Правка / Заполнить / Прогрессия ….

15. Під який відсоток річних було взято кредит? Обчислення прове­діть у комірці Е15.

16. Через скільки років кредит сплатять, якщо суму внеску збільшити на 120 грн. Обчислення проведіть у комірці Е16.

17. Скопіюйте формулу з комірки D5 в D6. Чи змінився результат?

18. Який внесок потрібно робити щороку, щоб виплатити кредит у той самий термін, якщо відсоток річних становитиме 20 %. Результат занесіть у комірку Е17.

19. Збережіть книжку під назвою Відсотки.

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

 

Лабораторна робота 19–20

Тема. Технологія застосування функцій у таблицях. Операції реда­гування таблиць. Фільтрація та сортування даних. Підбиття підсумків.

Мета. Ознайомитися із загальними правилами редагування таблиць. Отримати навички роботи з логічними, статистичними функціями. Навчитися використовувати автофільтр та розширений фільтр, упорядковувати й шукати дані, підбивати підсумки, будувати мате­матичні вирази.

План

1. Використання функцій Excel.

2. Редагування даних.

3. Фільтрація даних.

4. Пiдсумки в таблицях.

5. Сортування даних.

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

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

За призначенням функції поділяють на декілька категорій:

· математичні;

· статистичні;

· фінансові;

· функції дати та часу;

· для роботи з посиланнями й масивами;

· текстові;

· для роботи з базою даних;

· логічні;

· для перевірки властивостей та значень.

Будь-яка функція виконується або внесенням її тексту з клавіатури в активну комірку, або її викликом з активної комірки за допомогою Майстра функцій.

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

Майстер функцій можна завантажити через пункт меню Вставка / Функция … або за допомогою кнопки на панелі інстру­ментів. У будь-якому випадку з’явиться вікно Мастер функций (рис. 39).

 

Рис. 39. Діалогове вікно Мастер функций

 

Як будь-який із Майстрів, він містить кроки для вибору та виконання функцій.

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

Категорія 10 недавно использовавшихся містить імена функцій, які користувач нещодавно вже викликав. Із неї також можна вибрати по­трібну для роботи функцію. Після цього вибору натискають OK.

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

Рис. 40. Діалогове вікно для занесення параметрів функції

Вікно параметрів (рис. 40) здебільшого закриває собою частину таблиці. Для того, щоб дістатися до даних таблиці, його можна “захопити” мишкою та відсунути або натиснути мишкою червоно-синьо-білу кнопку праворуч у рядку параметра ‒ і вікно мінімізується (“згорнеться”) у рядок (рис. 41).

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

Тепер усі дані таблиці доступні й можна вибирати ті, які потрібні для роботи. Після вибору знову натискають червоно-синьо-білу кнопку, – вікно відновлює попередній розмір.

Якщо всі дії були правильними, можна побачити отримане значення.

Для остаточного виконання функції натискають OK – і результат з’являється в комірці.

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

Microsoft Excel сам визначить найближчий діапазон чисел та запропонує виконати обчислення. Якщо вибраний програмою діапа­зон задовольняє користувача, потрібно просто натиснути Enter, якщо ж ні – спочатку виділити потрібний діапазон і натиснути на Enter.

2. Редагування даних. Дані, уведені в комірку, можна змінити, замінити або вилучити.

Заміна даних: заносять нове значення й натискають Enter.

Зміна даних: вибирають комірку,

· натискають клавішу F2, вносять зміни й натискають Enter, або

· двічі натискають ліву кнопку мишки, вносять зміни та натискають Enter, або

· вносять зміни в рядку формул і натискають Enter.

Зміна даних відбувається в режимі Правка.

На відміну від зміни та заміни даних, вилучення даних можна провадити не з однієї, а з декількох комірок (діапазону).

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

Несуміжні діапазони складаються з декількох суміжних і позначаються їхніми адресами, розділеними крапкою з комою: A2:C6; K3:K10; E2:G2.

Для роботи з діапазоном його потрібно виділити за допомогою мишки або клавіатури.

Виділення суміжного діапазону:

8 – натискають й утримують ліву кнопку мишки та протя­гують її по потрібних комірках;

7 – натискають та утримують клавішу Shift, а потім за допо­могою клавіш переміщенням курсора показують потрібний діапазон (виділений діапазон чорного кольору, а комірка, з якої починалося виділення, лишається світлою).

Виділення несуміжного діапазону:

8+7 – виділяють перший суміжний діапазон, що входить до складу несуміжного, і при натисненій клавіші Ctrl виділяють усі інші суміжні діапазони.

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

Тому, якщо використовують метод повного знищення, то дані вилучаються повністю: і дані, і їх формат, а під час часткового знищення можна вилучити або зміст даних (формат лишається), або формат даних (зміст лишається). Пункт меню Правка / Очистить дає змогу вибрати спосіб знищення:

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

3. Фільтрація даних. Фільтр – це конструкція, призна­чена для відбору тих рядків таблиці, що задовольняють задану умову, і тимчасового приховання інших. Основою фільтра є список, що міс­тить умови відбору рядків. Передусім потрібно виділити діапа­зон, для якого буде створено фільтр. Вибрати команду Данные / Фильтр / Автофильтр. Після цього автоматично в комірках верхнього рядка виділеного діапазону створюється спеціальна кнопка ▼, що розкриває список фільтра.

Натиснувши на кнопку ▼, можна вибрати один із варіантів відбо­ру даних: перші десять рядків списку, задати умову фільтрації та ін. Щоб зняти фільтр потрібно повторно вибрати команду Данные / Фильтр / Автофильтр. Режим фільтрації можна скасувати командою Данные / Фильтр / Показать все.

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

Звертання до розширеного фільтра виконується за допомогою команди Данные / Фильтр / Расширенный фильтр. На екрані з’явля­ється діалогове вікно, де треба вказати діапазон комірок, у яких міс­тяться дані, що їх потрібно фільтрувати, та вказати діапазон умов або критеріїв, за якими будується фільтр. Критерій у цій операції ство­рю­ється як таблиця (блок умов для даних), що може зберігатися в будь-якому місці робочої книги або навіть в іншому файлі. Резуль­тат фільтрації можна показати в поточній таблиці, а можна вивести в інше місце, так звану цільову область, яку користува­ч теж оформлює як самостійну таблицю. Знімають фільтр командою Данные / Фильтр / Показать все.

4. Пiдсумки в таблицях. Пiдсумки пiдбивають для знаход­ження загальної суми, кількості, мінімального та максимального значення по числових стовпчиках. Для отримання пiдсумкiв до вибра­ної впо­рядкованої таблицi застосовують команду Данные / Итоги... Отри­мують вікно, де задають: 1) назву поля, що мiстить об’єкти, для яких створюють пiдсумки, наприклад Країна; 2) операцію підсумову­вання і 3) назву поля, що містить данi, якi підлягають підсумовуванню. Скасувати пiдсумки можна командою Убрать все.

5. Сортування даних. Сортування даних проводять за однією чи декількома ознаками. Для цього потрібно скористатися командою Данные / Сортировка…

Хід роботи

1. Запустіть програму Excel. Відкрийте нову книжку.

2. Відкрийте збережену книжку Консолидация (попередня робота, рис. 36).

3. Скопіюйте таблицю з Аркуша 1 книжки Консолідація на аркуш 1 нової книжки.

4. Додайте до таблиці два рядки й заповніть її довільними даними.

5. Додайте ще один рядок Усього та обчисліть значення суми в тре­тьому стовпчику.

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

7. Додайте ще два рядки з назвами Максимум та Мінімум. Обчис­літь максимальне й мінімальне значення в тих стовпчиках, які містять числові дані. Скористайтесь уведенням формули.

8. Збережіть лист під назвою Значення.

9. Скопіюйте таблицю з листа Значення на новий лист.

10. Дайте йому назву Сортування.

11. Скопіюйте таблицю з листа Значення на лист Сортування.

12. Проведіть сортування за алфавітним порядком назв фруктів. Виді­літь потрібний діапазон і скористайтеся командою Данные / Сортировать.

13. Проведіть сортування за спаданням цін на фрукти.

14. Заховайте стовпець Сума. Скористайтеся командою Формат / Столбец / Спрятать.

15. Поверніть захований стовпчик командою Формат / Столбец / Показать, виділивши спочатку два стовпці, між якими розміще­но захований стовпець.

16. Скопіюйте таблицю з аркуша Сортування на новий аркуш і дайте йому назву Фільтр.

17. Використавши Автофильтр, показати рядки, у яких кількість перевищувала 100.

18. Використавши Автофильтр, показати рядки, у яких вартість перевищувала 15, але не перевищувала 25.

19. Використавши Автофильтр, показати рядки, у яких кількість перевищувала 50, а вартість не перевищувала 20.

20. Візьміть останню таблицю за основну й відформатуйте її на ваш смак. Застосуйте автопідбір ширини стовпців і додайте ще один стовпець, який би відображав дати продажу (задайте відповідний формат). Скористайтеся командою Формат / Ячейки…

21. Збережіть книжку під назвою під назвою Функції.

22. Відкрийте нову книжку.

23. Скопіюйте таблицю на аркуші Значення на два нові аркуші.

24. На аркуші 1 нової книги створіть підсумок для визначення назви фрукта, кількість якого найбільша. Скористайтеся командою Данные / Итоги.

25. На аркуші 2 створіть підсумок, щоб визначити, ціна якого фрукта є найменшою. Скористайтеся командою Данные / Итоги.

26. Проведіть табулювання функції y = 1 + 2sin3x з кроком 0,5, якщо х змінюється в межах від -3 до 3. Знайти площу під усією кривою. Дані оформіть у таблицю. Самостійно відформатуйте її. Щоб знайти значення площі, скористайтеся Автосумою всіх знайдених частинок площ (рис. 42).

Рис. 42. Зразок розв’язування задачі пункту 26

27. Побудуйте графік функції. Скористайтеся командою Вставка / Диаграммы / График …

28. Поекспериментуйте зі значенням площі, якщо крок табулювання змінити з 0,5 на 1, 2. Значення скопіюйте на наступні аркуші.

29. Збережіть книжку під назвою Табулювання.

30. Продемонструйте викладачеві виконані завдання.

31. Виконайте самостійні завдання за вказівкою викладача.

32. Завершіть роботу.

 

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

Тема. МS Ехсеl. Побудова дiаграм.

Мета. Ознайомитися з видами дiаграм i навчитися будувати їх за числовими даними з побудованих ранiше таблиць.

План

1. Головнi типи дiаграм.

2. Створення дiаграми.

3. Структура й форматування дiаграми.

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

1. Головнi типи дiаграм. Оскiльки рiзновидiв дiаграм є декiлька десяткiв, ми розглянемо лише найважливi.

Кругова дiаграма вiдображає один виокремлений рядок чи стов­пець числових даних із таблиці у вигляді круга із секторами (рис. 43).

Груші

Рис. 43. Кругова діаграма

Така діаграма демонструє спiввiдношення частин і цiлого, де цiле відпо­вiдає 100 %. Є декiлька видiв кругових діаграм (рис. 44).

Груші

Рис. 44. Різновид кругової діаграми

Якщо серед даних є порiвняно незначні, то їх можна об’єднати в один сектор, для якого буде створено окрему – меншу – дiаграму об’єд­на­них даних.

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

Точкову дiаграму призначено для побудови традиційних графiкiв з довiльними значеннями аргумента (рис. 45).

Рис. 45. Точкова діаграма

На одн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аграма) (рис. 46) показує числовi данi таблицi у виглядi вертикальних стовпчикiв. Її найчастiше використо­вують для iлюстрацiї змiн даних у часi чи просторi.

Лінійні звичайнi дiаграми складаються з горизонтальних смужок. Вони подiбнi до гiстограм, але повернутi на 90 градусiв, що дає змогу вiзуально оцiнювати величини, порiвнюючи довжини смужок (рис. 47).

Рис. 46. Гістограма Рис. 47. Лінійна діаграма

Бульковi дiаграми подiбнi до точкових, але маркери даних у них – це круги чи сфери, дiаметри яких пропорцiйнi до даних, якi вони вiдображають.

Рис. 48. Булькова діаграма

Б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лькостi стовпцiв (мiсяцiв) (рис. 49).

Груші

Рис. 49. Пелюсткова діаграма

2. Створення діаграм. Робота Майстра (рис. 50) складається з чотирьох етапів (кроків). На першому користувач має вибрати графік за типом і зов­нішнім виглядом. Якщо перед викликом Майстра було виділено дані для побудови, то, натискуючи й утримуючи мишкою кнопки Просмотр результата, побачимо, яким буде майбутній графік (рис. 51).

 

Рис. 50. Діалогове вікно Рис. 51. Перегляд результатів

Мастер диаграмм

За допомогою кнопки Далее переходять до другого кроку, на якому визначають діапазон даних для побудови графіка і те, як ці дані розміщено в таблиці – у рядках чи в стовпчиках (рис. 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мальною.

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