Лабораторна робота №5, Виконання обчислень в MS Excel. Робота з формулами та функціями

« Назад

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

Обчислення в таблицях виконуються за допомогою формул. Результатом виконання обчислень є деяке нове значення, що міститься в тій комірці, куди була введена формула. Введення формули в комірку завжди починається зі знаку рівності "=". У формулі можуть використовуватися:

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

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

- посилання на комірку, що можуть бути двох видів – абсолютні та відносні. Абсолютне посилання на комірку – це вказівка на комірку, положення якої щодо інших комірок не змінюється. Відносне посилання на комірку - це вказівка місця розташування комірки відносно іншої. Для позначення абсолютного посилання використовується знак $. Абсолютною може бути як все посилання, так і його частина. Наприклад, якщо записати D$7, то абсолютним буде тільки номер рядка 7; запис $D7 означає незмінність символу стовпчика D; а запис $D$7 - що все посилання є абсолютним. При переміщенні формули не змінюються посилання обох видів. При копіюванні формули не змінюються тільки абсолютні посилання (частини посилань), а відносні посилання змінюються на величину перенесення формули.

- посилання на діапазон комірок. Для звертання до групи комірок використовуються спеціальні символи: двокрапка (:) − формує звертання до діапазону комірок (С4:D6 − звертання до комірок С4, С5, С6, D4, D5, D6); крапка з комою (;) − позначає об'єднання комірок (D2:D4; D6:D8; Е5 − звертання до комірок D2, D3, D4 й D6, D7, D8 та Е5).

- стандартні функції.

Функції в Excel використовуються для виконання стандартних обчислень в робочих книгах. Excel містить більше 300 вбудованих функцій. Для роботи з ними в Excel є спеціальний засіб - Майстер функцій, робота з яким складається з двох кроків: пропонується спочатку вибрати потрібну категорію, ім'я потрібної функції зі списку категорій (перший крок); потім у діалоговому вікні ввести значення аргументів (другий крок). Майстер функцій викликається командою Вставить функцию (вкладка Формулы, група Библиотека функций) або натисканням кнопки Вставка функции  на рядку формул (також можна скористатися комбінацією клавіш Shift+F3).

Значення, що використовуються для обчислення функцій, називаються аргументами. Значення, що повертаються функціями як відповідь, називаються результатами. Щоб використовувати функцію, потрібно ввести її як частину формули в комірку робочого аркуша. Послідовність, в якій повинні розташовуватися використовувані у функції символи, називається синтаксисом функції. Всі функції використовують однакові основні правила синтаксису. Якщо правила синтаксису порушені, Excel виведе повідомлення про те, що у формулі є помилка. Правила синтаксису наступні: якщо функція з'являється на самому початку формули, їй повинен передувати знак рівності "=", оскільки будь-яка формула повинна починатися з цього знаку; аргументи функції записуються в круглих дужках відразу після назви функції і відокремлюються один від одного крапкою з комою ";"; дужки дозволяють Excel визначити, де починається і де закінчується список аргументів; не можна вставляти пробіли між назвою функції і дужками.

Якщо формула в комірці не може бути розрахована, Excel виводить у цю ж комірку повідомлення про помилку:

###### − замала ширина комірки, яка не дозволяє відобразити число в заданому форматі;

#ИМЯ? − Excel не спромігся розпізнати імена, використовувані у формулі;

#ДЕЛ/0! − у формулі зроблена спроба ділення на нуль;

#ЧИСЛО! − порушено правила завдання операторів, прийнятих в математиці;

#Н/Д − як аргумент задане посилання на порожню комірку;

#ПУСТО! − невірно зазначене перетинання двох областей, які не мають спільних комірок;

#ССЫЛКА! − у формулі задане посилання на неіснуючу комірку;

#ЗНАЧ! − використано неприпустимий тип аргументу.

Для зручності й наочності роботи з формулами при виправленні помилок застосовують команди з групи Зависимости формул вкладка Формулы.

Розглянемо деякі команди цієї групи (рис. 2.10). Влияющие ячейки – стрілками вказуються комірки, які впливають на результат розрахунків в активній комірці. Зависимые ячейки — стрілками вказуються комірки, які залежать від даних активної комірки. Проверка наличия ошибок – якщо в поточній комірці міститься повідомлення про помилку, то ця команда виявляє помилку. Показать формулы — в таблиці встановлюється режим перегляду формул, а для активної комірки виділяються кольорами рамки комірок, що впливають на результат розрахунків в активній комірці.

Рис. 2.10 – Група Зависимости формул

Завдання 1

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

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

1. Завантажити програму MS Excel. Створити нову книгу (якщо вона не створилася за замовчуванням). Змінити ім’я першого робочого аркуша на ім’я Виміри.

2. Створити на робочому аркуші таблицю згідно ескізу. Виконати необхідне форматування та налаштувати ширину стовбців та висоту рядків таблиця для коректного відображення даних.

3. У діапазон комірок A3:А13 ввести десять будь-яких чисел (будемо вважати, що це результати вимірювань).

4. Встановити рамку виділення у комірку В3 та ввести у комірку формулу для обчислень подвоєного значення першого вимірювання: =2*A3.

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

5. Зробити активною комірку С3 та ввести у комірку формулу для обчислень квадрату значення: =A3^2.

6. Аналогічно, у комірку D3 ввести формулу для обчислень квадрату наступного числа: =(A3+1)^2.

7. За допомогою маркера заповнення виконати копіювання формул у відповідні комірки таблиці. Для цього виділити діапазон комірок B3:D3, потім маркер заповнення всього діапазону відбуксирувати на комірку D13 та відпустити ліву клавішу миші.

8. Встановити режим перегляду формул, виконавши команду Показать формулы (вкладка Формулы, група Зависимости формул) та переконайтеся, що посилання в формулах автоматично модифікувалися щодо нового місця розташування.

9. Відключити режим перегляду формул (знову натиснути кнопку Показать формулы). Змінити одне або декілька значень у стовпці A і переконатися, що відповідні значення в стовпцях B, C й D автоматично перераховані.

10. Ввести в комірку E3 масштабний множник (наприклад число 5).

11. Ввести в комірку F3 формулу для масштабування значень: =A3*E3, а потім маркером заповнення скопіювати цю формулу в комірки діапазону F4:F13.

12. Переконайтеся, що результати обчислень у діапазоні F4:F13 виявилися помилковими. Це пов'язане з тим, що посилання на комірку E3 у формулі відносне, тому при копіюванні воно автоматично модифікується. Для того щоб посилання не змінювалось воно повинно бути абсолютним. Щоб виправити помилку, та зробити посилання абсолютним, потрібно клацнути на комірці F3, а потім у рядку формул встановити курсор перед посиланням E3 і натиснути на функціональну клавішу F4 на клавіатурі. Після цього формула має вигляд =A3*$E$3. Завершити редагування натиснувши клавішу Enter.

Якщо натиснути функціональну клавішу F4 декілька раз, то посилання спочатку стає абсолютним тільки по рядку, потім – тільки по стовбцю.

13. Маркером заповнення скопіювати відредаговану формулу в комірки діапазону F4:F13. Переконайтеся в правильності обчислень.

14. Розташувати дані в центрі комірок за допомогою кнопки Выравнивание по центру  (вкладка Главная, група Выравнивание).

15. Обрамувати таблицю контуром за допомогою кнопки Границы  (або виконавши команду Формат  - Формат ячеек… – вкладка Граница).

16. Прибрати сітку електронної таблиці знявши прапорець Сетка (вкладка Вид, група Показать).

17. Зберегти робочу книгу (Файл – Сохранить) у власній папці на жорсткому диску під ім’ям Обчислення.

Завдання 2

В таблицю, створену у попередньому завданні, внести значення суми (комірка А14), середнього (комірка А15), мінімального (комірка А16) та максимального (комірка А17) значення всіх результатів вимірювань, які знаходяться в діапазоні комірок А3:А13. Також в комірці А18 підрахувати кількість чисел, а в комірці А19 – кількість значень діапазону комірок А2:F13. Таблицю розмістити на аркуші з ім’ям Статистика.

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

1. Зробити копію робочого аркуша Виміри. Для цього виконати команду Переместить или скопировать… з контекстного меню ярлика аркуша та у вікні, що відкрилося, встановити перемикач Создать копию та натиснути кнопку ОК (також можна перетягнути лівою клавішею миші ярличок листа утримуючи клавішу Ctrl).

2. Змінити ім’я копії аркуша на ім’я Статистика.

3. Зробити активною комірку A14 та клацнути на кнопці Сумма  (вкладка Главная, група Редактирование).

4. Переконатися, що програма автоматично підставила у формулу функцію СУММ і визначила необхідний діапазон комірок А3:А13. Натиснути клавішу Enter.

5. Зробити активною комірку A15 та клацнути на кнопці Вставка функции , яка розташована на рядку формул (також можна використати комбінацію клавіш Shift+F3).

6. У діалоговому вікні Мастер функций – шаг 1, у полі Категория вибрати категорію Статистические, у списку функцій вибрати функцію СРЗНАЧ і клацнути кнопку ОК.

7. Зверніть увагу на те, що автоматично встановився непотрібний діапазон. Виділити діапазон комірок А3:А13 і натиснути клавішу Enter.

8. Аналогічно в комірці А16 обчислити мінімальне число в даному діапазоні (функція МИН), а в комірці А17 – максимальне число (функція МАКС). Для кожної функції потрібно встановити діапазон комірок А3:А13.

9. Зробити активною комірку A18 та ввести в неї функцію, яка підрахує кількість чисел у таблиці (функція СЧЕТ), задати діапазон комірок А2:F13.

10. Аналогічно для комірки А19 підрахувати кількість значень у таблиці не порожніх комірок (функція СЧЕТЗ), діапазон комірок теж встановити А2:F13.

11. Виділити діапазон всіх результатів вимірювань (А3:А13) та за допомогою контекстного меню рядка стану визначити суму, мінімальне, максимальне значення. Порівняти результати з даними, отриманими раніше.

12. Виділити діапазон всієї таблиці (А2:F13) та за допомогою контекстного меню рядка стану визначити кількість чисел та кількість значень у виділеному діапазоні. Порівняти результати з даними, отриманими раніше.

13. Зберегти робочу книгу.

Завдання 3

Розрахувати значення функції y(x)=Asin²(3x), якщо А=4, а значення х змінюється на інтервалі від -3 до 3 з кроком 0,2. Результат оформити за наведеним ескізом та розмістити на аркуші з ім’ям Функція.

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

1. Перейдіть на будь-який вільній аркуш робочої книги Обчислення. Змінити ім’я даного робочого аркуша на ім’я Функція. (У випадку відсутності аркуша створіть новий).

2. Створити на робочому аркуші таблицю згідно ескізу. У комірку В2 ввести значення константи А – число 4.

3. У діапазоні комірок А4:А34 створити арифметичну прогресію від -3 до 3 з кроком 0,2.

4. Зробити активною комірку В4 та ввести формулу для розрахунку – =$В$2*sin(3*А4)^2.

Так як значення константи А знаходиться тільки у комірці В2, то при копіюванні формули посилання на цю комірку не повинно змінюватися. Тобто, повинно бути абсолютним.

5. За допомогою маркера заповнення виконати копіювання формули у відповідні комірки таблиці. Для цього виділити комірку B4, потім маркер заповнення відбуксувати до комірки В34. Відпустити ліву клавішу миші.

6. Встановити режим перегляду формул, виконавши команду Показать формулы (вкладка Формулы група Зависимости формул) та переконатися, що формули автоматично модифікувалися щодо нового місця розташування. Відключити режим перегляду формул.

7. Виконати необхідне форматування та налаштувати ширину стовбців та висоту рядків таблиці для коректного відображення даних.

8. Зберегти робочу книгу.

Завдання 4

На аркуші Масиви створити масив формул обчислення модуля для чисел -25,12; 12,34; -45,6; -87,7 за наведеним ескізом.

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

1. Перейти на будь-який вільній аркуш робочої книги Обчислення. Змінити ім’я поточного робочого аркуша на ім’я Масиви.

2. Створити на робочому аркуші таблицю згідно ескізу. У діапазон комірок В2:Е2 ввести задані для обчислення числа (-25,12; 12,34; -45,6; -87,7). Виконати необхідне форматування та налаштувати ширину стовбців та висоту рядків таблиці для коректного відображення даних.

3. Виділити діапазон комірок В3:Е3, у який буде розташовано масив формул.

4. Клацнути на кнопці Вставка функции , яка розташована у рядку формул (також можна використати комбінацію клавіш Shift+F3)

5. У діалоговому вікні Мастер функций – шаг 1, у полі Категория вибрати тип функції Математические, потім у списку Выберите функцию – ABS (повертає модуль числа). Клацнути кнопку ОК.

6. На кроці 2 – Аргументы функции в поле Число ввести В2:Е2.

7. Замість кнопки ОК натиснути комбінацію клавіш Ctrl+Shift+Enter. У діапазоні комірок В3:Е3 з'являться модулі чисел.

8. Зберегти робочу книгу.

Завдання 5

На аркуші Таблиця створити таблицю множення 10 на 10 за допомогою масиву формул за наведеним ескізом.

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

1. Перейти на будь-який вільній аркуш робочої книги Обчислення або створити його та змінити системне ім’я на ім’я Таблиця.

2. Створити на робочому аркуші таблицю згідно ескізу. У діапазон комірок В2:K2 та A3:A12 ввести натуральні числа від 1 до 10. Ширину стовбців A:K встановити рівною 3,5 пт (Формат  - Ширина столбца…).

3. Виділити діапазон комірок В3:K12, у який буде введено масив формул.

4. Встановити курсор у рядок формул та ввести формулу для розрахунку =B2:K2*A3:A12.

5. Натиснути комбінацію клавіш Ctrl+Shift+Enter. У діапазоні комірок В3:K12 з'явиться таблиця множення.

6. Виконати необхідне форматування та налаштувати ширину стовбців та висоту рядків таблиці для коректного відображення даних.

7. Зберегти робочу книгу.

Завдання 6

Дослідити наочність роботи з формулами за допомогою команд з групи Зависимости формул вкладки Формулы при виправленні помилок та аналізі формул.

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

1. Перейти на аркуш Виміри робочої книги Обчислення.

2. Перейти на вкладку Формулы.

3. Встановити рамку виділення у комірку F5 і натиснути кнопку Влияющие ячейки у групі Зависимости формул. Зробити ScreenShot зображення на екрані і вставити його у документ Ms Word та зберегти його з ім’ям Залежності_формул.docx.

4. Натиснути кнопку Убрать стрелки.

5. Виділити комірку Е3 і натиснути кнопку Зависимые ячейки. Переглянути результат, зробити ScreenShot та вставити зображення в документ Ms Word з ім’ям Залежності_формул.

6. Натиснути кнопку Убрать стрелки.

7. Виділити комірку F4 і натиснути кнопку Показать формулы. Переглянути результат, зробити ScreenShot та вставити зображення в документ Ms Word з ім’ям Залежності_формул.

8. Відключити режим перегляду формул (знову натиснути кнопку Показать формулы).

9. Зберегти документ MS Word та робочу книгу MS Excel.

Завдання для самостійної роботи

1. Для кутів у радіанах значення яких дорівнює 28, 54, 62, 32 створити масив формул косинусів цих кутів. Результат розмістити на аркуші з ім’ям Самостійно у книзі Обчислення.

2. На аркуші з ім’ям Самостійно розрахувати значення функції y(x)=ln(x/2), де значення х змінюються на інтервалі від 5 до 20 з кроком 3.

3. Розрахувати значення функції y(x)=Bsin(x/3)cos(x/2), якщо В=6, а значення х змінюються на інтервалі від 0 до 4 з кроком 0,2. Результат розмістити на аркуші з ім’ям Самостійно.

Результат роботи

1. Робоча книга Обчислення, з аркушами: Виміри, Статистика, Функція, Масиви, Таблиця, Самостійно, на яких розташовані відповідні таблиці з обчисленнями.

2. Документ Ms Word з ім’ям Залежності_формул у якому знаходяться ScreenShot таблиці Виміри у режимах: впливаючи комірки, залежні комірки та перевірки формул.

Питання для самоконтролю

1. Яка послідовність уведення формули в комірку та її виконання?

2. Які існують зручні прийоми введення у формулу змінних?

3. Як можна відобразити формулу, а не результати розрахунків у комірці?

4. Яка послідовність команд включає/виключає режим автоматичного перерахунку формул?

5. Як можна копіювати формули в Excel?

6. Як задати абсолютне посилання на комірку на робочому аркуші?

7. Як виконати підсумок?

8. Як виконати обчислення за необхідною функцією?

9. Як виконати редагування формули в комірці?

10. Що таке абсолютне та відносне посилання на комірку?

11. Вкажіть де знаходиться поле авто розрахунку та які можливості воно надає?