Робота з книгами в Excel

· Створення нової книги (Ctrl+N).

· Збереження книги. Здійснюється за командами Сохранить как (F12) чи Сохранить (Ctrl+S, Shift+F12) з меню Файл Перша команда зберігає файл під новим ім’ям, а друга - під його первинним ім’ям.

· Відкриття робочої книги. Відкриття збереженої раніше книги здійснюється за командою Файл→ Открыть або комбінації клавіш Ctrl+O. При цьому активізується діалогове вікно Откытие документа, де вибирається потрібна папка та переглядається її вміст.

· Закриття книги. Виконується за командою Файл→ Закрыть, за допомогою комбінації клавіш Ctrl+F4, або за допомогою червоної кнопки з хрестиком у верхньому правому куті вікна.

· Перейменування робочої книги. Виконується за командою Переименовать з контекстного меню або за допомогою подвійного натискання лівої клавіші миші на назві листка.

· Передача книги в об’єкти Windows. Ця операція виконується за командою Отправить. У підменю вікна вибирається пункт призначення, наприклад, Диск А, Мои документы, MS Outlook.

· Вилучення робочих книг. Виконується за допомогою команди Удалить.

9.4.3. Друк електронної таблиці

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

Щоб роздрукувати листок робочої книги, необхідно виділити його (перейти на нього) і скористатися меню Файл→ Печать (Ctrl+P) або відповідною кнопкою на панелі інструментів, яка не дає можливості налаштувати параметри друку, тобто без попередніх налаштувань параметрів друку.

Меню Файл→ Печать дає доступ до налаштування параметрів друку, в якому можна вказати, який діапазон сторінок обраного робочого листка буде надруковано, кількість копій друку, вибрати, що буде надруковано: обраний діапазон комірок, листок робочої книги або вся робоча книга. Також можна вказати, на якому принтері відбувається друк, а за необхідності встановити властивості принтера за допомогою кнопки Свойства.

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

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

Можна роздруковувати частину таблиці з листка, для цього необхідно:

1) виділити область на листку, яка буде надрукована;

2) вибрати меню Файл→ Область печати→ Задать;

3) якщо необхідно повернути налаштування, то необхідно скористатися меню Файл→ Область печати→ Убрать

Рисунок 9.4.15 Вікно параметрів програми

На кожному листку книги можна задати налаштування відображення інформації. Для цього необхідно скористатися меню Сервис→ Параметры та вибрати закладку Вид (рис. 9.4.15)Ці параметри дають можливість роздрукувати листок з відображенням формул, сітки або без них. З іншими параметрами налаштування необхідно ознайомитися самостійно.

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

Тема 10. РОБОТА З ФУНКЦІЯМИ EXCEL

10.1. Поняття, призначення та класифікація функцій

10.1.1 Поняття та призначення функцій

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

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

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

10.1.2. Синтаксис функцій

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

Для спрощення роботи з функціями більшість з них була названа від скорочення російськомовних значень цих функцій:

Наприклад:

СУММ – функція, що здійснює додавання елементів.

СРЗНАЧ – функція, що визначає середнє значення.

Формула починається зі знака «=», за яким вводиться ім’я функції, дужка, що відкривається, список аргументів, розділених крапкою з комою «;», далі дужка, що закривається.

Наприклад: =СУММ(В2; С2).

Бувають функції без аргументів, які мають такий синтаксис: =Ім’я_функції().

Наприклад: =СЕГОДНЯ().

Загальний вигляд функції: =ім’я функції (параметр/и).

Існують різні типи аргументів: число, текст, логічне значення (Истина або Ложь), формули чи інші функції. В кожному конкретному випадку необхідно використовувати відповідний тип аргументу.

10.1.3. Введення функцій

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

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

10.1.4. Класифікація функцій

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

Для зручності роботи Excel функції розбиті за категоріями:

1) математичні функції;

2) статистичні функції;

3) логічні функції;

4) фінансові функції;

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

6) перевірка властивостей та значень;

7) функції роботи з базами даних;

8) текстові функції;

9) функції посилання та масивів;

10) функції користувача

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

10.1.5. Майстер функцій

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

Майстер функцій можна викликати таким чином:

1) Вставка→ Функции

2) натискання кнопки Мастер функций (), що розміщена на панелі інструментів Стандартная;

3) Shift+F3.

Рисунок 10.1.1 Вікно майстра функцій

Вікно Мастера функций складається з трьох частин (рис. 10.1.1).

1. У першій можна ввести опис дії, яку необхідно виконати і натиснути кнопку Найти. Цей метод використовується, якщо користувач не знає чи не пам’ятає, як називається потрібна йому функція.

2. Нижче є поле для вибору категорії функцій. Для спрощення роботи з великим обсягом вбудованих функцій всі вони розділені на категорії залежно від призначення. Це значно спрощує пошук потрібної функції. Є окремо виділені категорії: 10 недавно используемых функций та Полный алфавитный перечень для спрощення пошуку функцій.

3. У третій частині є можливість вибору функцій відповідно до категорії.

При виборі функцій в нижній частині вікна відображається коротка інформація про призначення цієї функції.

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

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

10.1.6. Довідки про функції

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

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

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

10.2. Математичні, логічні та статичні функції

10.2.1. Математичні функції

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

Розглянемо деякі із них.

1 СУММ – додає аргументи.

2 КОРЕНЬ – повертає додатне значення квадратного кореня.

3 COS, SIN, TAN – тригонометричні функції cos, sin і tg.

4 ACOS, ATAN – зворотні тригонометричні функції arсcos, arсtg.

5 ГРАДУСЫ – перетворює радіани в градуси.

6 LN – натуральний логарифм числа.

7 ABS – модуль числа.

8 ПИ – повертає число Пі (π =3.14).

9 ЗНАК – повертає знак числа.

10 ПРОИЗВЕД – повертає добуток аргументів.

11 СТЕПЕНЬ – повертає результат піднесення до степеня.

12 ОКРУГЛ – закруглює число до заданої кількості десяткових розрядів.

13 ОСТАТ – повертає залишок від ділення.

14 СЛЧИС – повертає випадкове число в інтервалі від 0 до 1.

15 РИМСКОЕ – перетворює число в арабському записі до числа в римському як текст.

16 СУММЕСЛИ – повертає суму вмісту комірок, яке задовольняє заданий критерій;

17 СУММКВ – повертає суму квадратів аргументів.

18 МОБР, МУММНОЖ, МОПРЕД – зворотна матриця, добуток та визначник матриці.

В електронній таблиці Excel вибрати математичні функції можна з використанням Мастера функций, де в полі Категорія необхідно вибрати Математические і тоді можна буде вибрати необхідну математичну функцію. Якщо виділити курсором мишки будь-яку функцію, то внизу буде написано, що розраховує подана функція та її синтаксис (рис. 10.2.2).

Рисунок 10.2.2 – Функції категорії Математические

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

Завершити введення аргументів матричних функцій слід обов’язково натисканням комбінації клавіш Ctrl+Shift+Enter, а не просто кнопки OK. Часто на аргументи цих функцій накладається обмеження, викликані математичним обґрунтуванням цих операцій (наприклад, фіксована кількість рядків та стовпців).

Для того, щоб обчислити суму значень, введених у деякий діапазон комірок, необхідно виконати таке:

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

2 Зробити активною комірку, в яку буде розміщено суму та натиснути . Excel автоматично запропонує діапазон сумування, який буде відображатися у формулі. За необхідності його можна замінити.

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

Наприклад, сума діапазону комірок рядка від А1 до А20: =СУММ(А1: А20).

У цьому прикладі знак «:» означає діапазон з вказівкою першого і останнього членів діапазону.

У цьому випадку функція СУММ використовується з одним аргументом – діапазоном комірок. Однак вона може мати більше одного аргументу (до 30), розділених «;». При цьому кожний аргумент може бути як числом, адресою комірки, так і діапазоном комірок. Також можна додавати і діапазони комірок, не розміщені поруч, тобто окремі. Для виділення таких діапазонів необхідно утримувати клавішу Ctrl і мишкою виділяти діапазони.

Якщо, наприклад, необхідно перемножити два числа, то треба записати: =ПРОИЗВЕД(А1; В1).

За допомогою функції СУММЕСЛИ можна просумувати значення з діапазону комірок, але при цьому здійснюється додавання лише тих значень, які відповідають певному критерію.

Приклад 4.1 Обчислити значення математичних виразів

.

Розв’язання

Вираз |-2| є значенням модуля числа, тому обчислюється за допомогою функції ABS(), що належить до категорії математичних функцій (рис. 10.2.3). Для розрахунку значення першого виразу необхідно викликати функцію ABS() та в аргумент функції записати -2.

Рисунок 10.2.3 - Функція для обчислення модуля числа

Результатом обчислень буде число 2.

Для обчислення виразу необхідно скористатися функцією КОРЕНЬ(). Для розрахунку необхідно в аргумент функції ввести число 81 (рис. 10.2.4).

Рисунок 10.2.4 – Функція для обчислення квадратного кореня числа

Результатом обчислень буде число 9.

Для обчислення виразу 3⁴ необхідно скористатися функцією СТЕПЕНЬ(), яка має два аргументи. У поле Число слід ввести число, яке необхідно піднести до степеня, тобто для нашого виразу 3. У поле Степень необхідно ввести степінь, тобто для нашого випадку 4 (рис. 10.2.5).

Рисунок 10.2.5 – Функція для обчислення степеня числа

У результаті функція для обчислення виразу буде мати вигляд: =СТЕПЕНЬ(3; 4).

Після виконання якої одержимо число 81.

Для обчислення виразу (2 + |- 3 |) слід скористатися функцією ABS() та знаком додавання (рис. 10.2.6).

Рисунок 10.2.6

У результаті функція для обчислення виразу буде мати вигляд: =2+ABS(-3), після виконання якої одержимо число 5.

Для обчислення виразу слід скористатися функціями СТЕПЕНЬ(), КОРЕНЬ() та знаком додавання (рис. 10.2.7).

Рисунок 10.2.7

У результаті функція для обчислення виразу буде мати вигляд: =КОРЕНЬ(36)+СТЕПЕНЬ(2; 3), після виконання якої одержимо число 14.

Для розрахунку виразу слід скористатися функцією СТЕПЕНЬ(), оскільки немає функції для розрахунку кубічного кореня із числа. У поле Число необхідно ввести число, з якого добуваємо корінь, тобто 27. У поле Степень необхідно ввести степінь 1/3, оскільки корінь тут кубічний (рис. 10.2.8).

Рисунок 10.2.8 – Функція для розрахунку кубічного кореня

У результаті одержимо число 3.

Приклад 4.2 Необхідно визначити сумарну заробітну плату працівників (рис. 10.2.9), які виконали план більше ніж на 100. Інформація про виконання плану знаходиться в стовпці F (з F2 по F10), інформація про заробітну плату – у стовпці E (E2 по E10).

Рисунок 10.2.9 – Відомості про виконання плану

Розв’язання Для розв’язання даної задачі необхідно скористатися функцією СУММЕСЛИ (рис. 10.2.10).

Рисунок 10.2.10 – Аргументи функції СУММЕСЛИ

Серед аргументів функції СУММЕСЛИ задаємо такі:

1 Діапазон: F2: F10 (діапазон, що буде порівнюватися з критерієм).

2 Критерій: «> 100».

3 Діапазон додавання: E2: E10.

Функція СУМЕСЛИ буде мати такий запис: =СУММЕСЛИ(F2: F10; " > 100"; E2: E10).

Результат розрахунків наведений на рис. 10.2.11.

Рисунок 10.2.11

10.2.2. Статичні функції

Статистичні функції призначені для проведення статистичного аналізу. Крім того, їх можна використовувати для факторного та регресійного аналізу.

Спочатку розглянемо найуживаніші:

1 СРЗНАЧ – визначає середнє значення.

2 МИН, МАКС – визначає мінімальне та максимальне значення.

3 СЧЕТ – визначає кількість числових аргументів.

Ці функції винесені на панель інструментів Стандартная.

Приклад 4.3 Необхідно знайти максимальне значення в діапазоні від А2 до А7.

Розв’язання Для розв’язання цієї задачі використовують функцію МАКС(аргументи), що повертає максимальне значення зі списку аргументів: =МАКС(А2: А7).

Розглянемо деякі інші функції:

1 КОРРЕЛ – визначає коефіцієнт кореляції між двома множинами даних.

2 СРГЕОМ – визначає середнє геометричне.

3 СРОТКЛ – повертає середнє абсолютних значень відхилень даних від середнього.

4 СРЗНАЧА – визначає середнє арифметичне аргументів, якими можуть бути як числа, так і текст, логічні значення.

5 СЧЕТЕСЛИ – підраховує кількість значення у переліку аргументів, які задовольняють деяку умову.

6 ДИСП – оцінює дисперсію за вибіркою.

Приклад 4.4 У таблиці наведені дані про працівників фірми «Ернест» (рис. 10.2.12). У стовпці D міститься інформація про посаду. Необхідно визначити кількість працівників на посаді бухгалтер.

Рисунок 10.2.12 – Дані працівників фірми «Ернест»

Розв’язання Для цього необхідно скористатися функцією СЧЕТЕСЛИ (рис. 10.2.13).

Рисунок 10.2.13 – Аргументи функції СЧЕТЕСЛИ

У полі Диапазон необхідно заповнити діапазон: «D2: D10», а в полі Критерий вказати «бухгалтер» (рис. 10.2.14).

Рисунок 10.2.14

У результаті функція СЧЕТЕСЛИ матиме вигляд: =СЧЁТЕСЛИ(D2: D10; " бухгалтер"). Результат розрахунків буде мати вигляд, як зазначено на рис. 10.2.15.

Рисунок 10.2.15– Результат розрахунку

Критерієм можуть бути довільні логічні обмеження, наприклад: > =100, < 0. Звернемо увагу на те, що якщо в критерії повинно стояти обмеження, яке використовує адресу деякої комірки (наприклад, хочемо задати обмеження > А5), критерій повинен задаватися у такому вигляді «>»& A5.

У критерії можна використовувати маски введення «*» замість довільної послідовності символів та знак «?» замість одного довільного символу. Наприклад, щоб визначити кількість працівників, прізвище яких починається з букви А, необхідно задати критерій «А*».

10.2.3. Логічні функції

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

Ці функції приймають логічні значення «Істина» або «Хибно». Ця категорія містить всього шість функцій, але вона є дуже важливою і часто використовуваною.

1 Найбільш важливою є функція ЕСЛИ. Ця функція використовується для розв’язання задач, в яких необхідно перевірити деяку умову, і залежно від того виконується вона чи ні, повертає одне з двох значень.

Ця функція записується так (рис. 10.2.17): ЕСЛИ (логічний вираз; значення, якщо істина; значення, якщо хибна).

Рисунок 10.2.17 – Аргументи функції ЕСЛИ

Якщо умова виконується, то виконується, то виконується вираз «значення, якщо істина». Якщо умова не виконується, то виконується вираз «значення, якщо хибна».

Приклад 4.5 Необхідно перевірити, чи виконано план робітниками заводу «Зірка» (рис. 10.2.18), і вивести значення виконання і не виконання плану. План записаний у стовпці В, а виконання роботи – у стовпці С.

Рисунок 10.2.18 – Працівники заводу «Зірка»

Розв’язання

У даній задачі застосуємо функцію ЕСЛИ. Ми будемо порівнювати значення стовпців В та С. Порівняємо значення стовпця С «виконано» зі стовпцем В «план».

Запис функції: =ЕСЛИ(B3> C3; " Ні"; " Так"). Якщо функція виконується, то одержуємо значення Так, а якщо не виконується – Ні (рис. 10.2.19).

Рисунок 10.2.19 – Результат виконання плану працівниками заводу «Зірка»

2 Функція И повертає значення істина, якщо всі аргументи мають значення істина, тобто коли виконуються всі умови.

Синтаксис: =И(логічне_значення_1; логічне_значення_2;...).

Цю функцію використовують для об’єднання двох і більше умов.

Приклад 4.6 Для перевірки чи належить число з комірок від С2 до С10 до діапазону від 110 до 130 (рис. 10.2.18), використовують функцію И.

Розв’язання

У комірки від D3 до D10 введемо функцію И з такими аргументами: И(С3> 110; С3< 150).

У результаті виконання умови у комірці буде написано ИСТИНА, а у результаті не виконання умови – ЛОЖЬ.

Результат виконання поданий на рис. 10.2.20.

Рисунок 10.2.20 – Результат виконання функції «И»

3 Функція ЛОЖЬ повертає логічне значення ЛОЖ. Синтаксис: =ЛОЖ().

4 Функція НЕ – змінює на протилежне логічне значення аргументу.

Наприклад, якщо є значення «-1», при використанні даної функції значення зміниться на протилежне, тобто буде «1».

5 Функція ИЛИ – повертає логічне значення істина, якщо коли хоч один з аргументів має значення істина.

Приклад 4.7 Необхідно перевірити, чи належить число з діапазону комірок від С3 до С10 діапазону менше 130 або більше 150 (рис. 10.2.18).

Розв’язання У комірки від D3 до D10 введемо функцію ИЛИ з такими аргументами: ИЛИ(С3> 150; С3< 130).

У результаті виконання умови у комірці буде написано ИСТИНА, а у результаті не виконання умови – ЛОЖЬ. Функція ИЛИ набуває значення ИСТИНА, коли хоча б одна з умов виконується.

Результат виконання поданий на рис. 10.2.21

Рисунок 10.2.21 – Результат виконання функції «ИЛИ» 6 Функція ИСТИНА – повертає логічне значення істина.

10.3. Функції дати і часу та вкладені функції

10.3.1. Функції дати і часу

Для роботи зі значенням типу дата та час в Excel використовують функції категорії ДАТА і ВРЕМЯ. Розглянемо деякі із них.

1. Функція ДАТА - повертає значення дати. Загальний вигляд функції: ДАТА(рік; місяць; день). Функція ДАТА(2000; 2; 1) залежно від установленого формату дати повертає значення 01.02.00.

2. Функція ДЕНЬ - повертає день дати в числовому форматі. Наприклад, у комірці F2 вміщена дата 28.10.2003, тоді значення функції ДЕНЬ(F2) дорівнює 28.

3. Функція ДЕНЬНЕД - визначає день тижня, на який припадає дата, визначена як аргумент. Синтаксис функції: ДЕНЬНЕД(дата; тип). При цьому аргумент тип визначає порядок розрахунку і може мати значення:

1 (за замовчуванням) – число від 1 (неділя) до 7;

2 – число від 1 (понеділок) до 7;

3 – число від 0 (неділя) до 6.

Функція =ДЕНЬНЕД(28.10.2003) повертає значення 3, а функція =ДЕНЬНЕД(«23.10.2003»; 2) – значення 2.

4. Функція СЕГОДНЯ - має загальний вигляд СЕГОДНЯ() і повертає значення поточної дати.

5. Функція ЧАС - повертає значення часу в налаштованому часовому форматі. Синтаксис запису: ЧАС(години; хвилини; секунди).

6. Функція ТДАТ - повертає поточну дату та час. Синтаксис функції: ТДАТ().

7. Функція МЕСЯЦ - використовується для визначення місяця. Синтаксис функції: МЕСЯЦ(дата в числовому форматі). Наприклад, МЕСЯЦ(10.01.2007) повертає значення 1.

8. Функція ДНЕЙ360 визначає кількість днів між двома датами, яку вона вираховує на основі 360-денного року.

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

10.3.2. Вкладені функції

Вкладені функції – це функції, аргументами яких можуть бути інші функції.

Розглянемо деякі приклади.

Приклад 4.8 Необхідно визначити поточний рік.

Розв’язання Функція ГОД() визначає значення року від певної дати, а функція СЕГОДНЯ() визначає поточну дату. Тому для визначення поточного року необхідно записати:

=ГОД(СЕГОДНЯ()).

Приклад 4.9 Необхідно визначити, до якої цінової групи належить товар (рис. 10.2.22), за таким правилом:

1) якщо ціна товару < 100 грн. – дешеві товари;

2) якщо 100 < 1000 грн. – середній товар;

3) якщо ціна товару > 1000 грн. – дорогі товари.

Рисунок 10.2.22 – Ціна товару

Розв’язання Нехай у комірці В записана ціна товару. Для розв’язання скористаємося функцією ЕСЛИ(): =ЕСЛИ(C2< 100; " дешеві"; ЕСЛИ(C2< =1000; " середні"; " дорогі")).

Для розв’язання цієї задачі в Excel необхідно вибрати функцію ЕСЛИ(). У вікні Аргументы функции задати такі значення (рис. 10.2.23):

1) Лог_выражение: С2< 100;

2) Значение_если_истина: дешеві;

3) Значение_если_ложь.

Рисунок 10.2.23 – Аргументи функції ЕСЛИ 101

Вибрана функція перевіряє значення комірки В2 та видає попередній результат. Для продовження розмістимо курсор миші Значение_если_ложь та знову викликаємо функцію ЕСЛИ(). Для цього у вікні Excel в адресному вікні знаходимо спадний список і знову вибираємо ЕСЛИ(), де вводимо:

1) Лог_выражение: С2< =100;

2) Значение_если_истина: середні;

3) Значение_если_ложь: дорогі. Результат виконання поданий на рис. 10.2.24.

Рисунок 10.2.24 – Приклад вкладеної функції

10.4. Редагування функцій

10.4.1. Редагування функцій

Для того щоб змінити аргумент функції, можна: виділити комірку, в якій введено функцію; розмістити курсор миші в рядку формул на імені потрібної нам функції (якщо у формулі використовується декілька функцій); натиснути кнопку для виклику функції з панелі інструментів.

За допомогою вкладки Вычисления вікна Параметры можна налаштувати параметри обчислення.

Можна змінювати функцію безпосередньо в рядку формул. При цьому слід пам’ятати, що аргументи функції розділяються символом «;». Слід притримуватися загального синтаксису побудови функцій, описаного в першому розділі теми.

За замовчуванням в Excel встановлено режим автоматичного проведення розрахунків. Якщо в комірку введено формулу, здійснюються відповідні обчислення і відображається результат. Якщо значення однієї комірки визначається через значення іншої (наприклад, у В1 введено формулу =А1+А2), то при внесені змін у комірку А1 чи А2 буде автоматично перераховано значення комірки В1.

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

10.4.2. Повідомлення про помилки

Якщо при обчисленні формули сталася помилка, то в комірку виводиться повідомлення про помилку, яке починається із символу #. Ехсеl виводить такі повідомлення про помилки, що подані у таблиці 4.1.

Таблиця 10.2.1 – Повідомлення о помилках

Можна також викликати функції, визначені користувачем. Для визначення таких функцій потрібно вміти писати програми мовою Visual Basic for Application.

10.4.3. Введення даних, робота з вікнами

Основні методи роботи в Excel для введення і форматування даних. Скористаємося такими позначеннями:

1ЛКМ - однократне клацання лівою кнопкою мишки;

2ЛКМ - подвійне клацання лівою кнопкою мишки;

1ПКМ - однократне клацання правою кнопкою мишки;

НПЛКМ - натиснути й перемістити лівою кнопкою мишки.

Зокрема, запис НПЛКМ А2: В6 відповідає виконанню таких операцій:

1 Перемістити курсор до комірки А2.

2 Натиснути й утримувати ліву кнопку мишки.

3 Перемістити курсор до комірки В6.

4 Відпустити ліву кнопку мишки.

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

Прокрутка вікна з фіксацією заголовків рядків і стовпців

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

2 Меню: Окно - Закрепить области.

3 Виконати прокрутку стрілками або за допомогою смуги прокручування.

Відмінити фіксацію можна за допомогою команди Окно – Снять закрепление областей.

Поділ вікон

1 Вибрати команду Окно - Разделить.

2 Встановити курсор на горизонтальний розділювач. Курсор перетворюється на двосторонню стрілку з горизонтальними лініями.

3 НПЛКМ. Переміщення курсору викликає переміщення подільника.

4 Встановити бажане положення розділювач і відпустити кнопку.

Аналогічно можна робити поділ вікон за допомогою вертикального та горизонтального розділювачів . Після поділу вікна, використовуючи прокрутку, можна викликати на екран одночасно різні частини таблиці, які знаходяться на значній відстані одна від одної. Відмінити поділ можна за допомогою команд Окно - Снять разделение.

Виділення діапазонів:

1. Виділення комірки:

• курсор на потрібну комірку;

• 1ЛКМ.

2. Виділення рядків:

• курсор на лівий стовпчик з номерами рядків;

• 1ЛКМ на потрібному рядку.

3. Виділення стовпчиків:

• курсор на верхній рядок з іменами стовпців;

• 1ЛКМ на потрібному стовпчику.

4. Виділення блока:

• курсор у кут блока;

• НПЛКМ;

• перемістити мишку в протилежний кут блока;

• відпустити кнопку миші.

5. Виділення всієї таблиці:

• курсор у прямокутник у лівому верхньому куті таблиці ;

• 1ЛКМ.

6. Виділення декількох блоків:

• виділити перший блок;

• [Ctrl]+курсор у кут другого блока;

• [Ctrl]+ НПЛКМ у протилежний кут блока.

7. Відмінити виділення:

• курсор у довільне місце таблиці;

• 1ЛКМ.

Під час роботи з даними в електронних таблицях може виникнути необхідність у використанні нижніх або верхніх індексів змінних. Розглянемо порядок введення таких індексів.

Введення нижніх і верхніх індексів

1 Ввести символ основного шрифту.

2 Вибрати команди головного меню Формат - Ячейки

3 Вибрати вкладку Шрифт.

4 У групі команд Эффекты призначити Нижний индекс.

5 Призначити розмір шрифту 8 пт.

6 Натиснути на кнопку ОК.

7 Ввести нижні індекси.

8 Натиснути на [Enter].

Верхні індекси вводяться аналогічно.

Тема 11. ОБЧИСЛЕННЯ МАТЕМАТИЧНИХ ВИРАЗІВ В EXCEL

Постановка завдання. Використовуючи подані нижче математичні вирази, обчислити значення виразів Y₁, Y₂, Y₃ і результати подати у вигляді таблиці. За вхідними та вихідними даними обчислити максимальне, мінімальне та середнє значення.

Значення a = 3.1, b = 5.9, x = -1, 13, 0, 6, 12, 3, 27. При x > 20 та x = 6 функція не визначена.

Значення Y₄ обчислюється так само, як і Y₃, проте використовуються інші способи перевірки умови.

Загальні поради

1. Створюємо заголовок таблиці: Використання математичних функцій для обчислення значення виразів. Формат комірок задається Числовой з двома знаками після коми.

2. У відповідні комірки робочого листка заносяться вхідні значення a = 3.1, b = 5.9, х = - 1, 13, 0, 6, 12, 3, 27 (Рис. 11.1).

3. Під значеннями розміщуються заголовки для визначення мінімального, та середнього значень.

4. Для визначення виразів Y₁, Y₂, Y₃ і Y₄ використовують формули, які містять посилання на адреси зі вхідними значеннями - коефіцієнтів a TAB, значення х та вказані функції. Елементи формули поєднуються відповідними знаками (математичними та пунктуаційними) і дужками для зміни пріоритету виконання обчислень.

1 Обчислення значення Y₁

1 Формули для обчислення Y₁ необхідно занести у комірку D4, а потім скопіювати у комірки D5: D10, тому адреси коефіцієнтів a та b мають бути абсолютними, тобто не змінюватися під час копіювання, а адреса значення x має бути відносною, тобто змінюватися під час копіювання.

2 Формалізація задачі:

1) введення a, b;

2) введення х;

3) розрахунок значення Y₁;

4) виведення Y₁.

3 Алгоритм задачі, зображений на Рис. 11.1.

Рисунок 11.1 – Алгоритм обчислення значення Y₁

Аналогічно формули для визначення Y₂, Y₃ і Y₄ заносять у комірки Е4: Е10, F4: F10, G4: G10 (Рис. 11.2).

Рисунок 11.2 – Початкова таблиця для розрахунків в Excel

4 Формулу для обчислень в комірку D4 можна вводити з клавіатури.

Формула для обчислення виразу Y₁ має вигляд: =$A$4*(LN($B$4+ABS(СТЕПЕНЬ(C4; 3)))/$B$4)-$B$4*СТЕПЕНЬ($A$4+$B$4*C4; 1/3)+C4+SIN($A$4*$B$4*C4), де $A$4 – абсолютна адреса із значенням коефіцієнта a, $B$4 – абсолютна адреса із значенням коефіцієнта b; C4 – відносна адреса поточного значення x; LN(…) – функція для визначення натурального логарифма; ABS(…) – функція для визначення модуля числа; SIN(…) – функція для визначення синуса числа СТЕПЕНЬ(…; значення степеня) – функція для визначення степеня числа.

5 Формулу для обчислень можна вводити, використовуючи Майстер функцій.

Для використання Майстра функцій необхідно:

Активізувати комірку D4, натиснувши =, вибрати мишкою адресу коефіцієнта a і зафіксувати її клавішею F4, натиснути знак * (у рядку формул буде показаний текст =$A$4).

Потім викликається майстер функцій (Рис. 11.3), вибирається категорія математична (Рис. 11.4), функція LN (Рис. 11.5) і натискається Ок (Рис. 11.6), мишкою вказується адреса коефіцієнта b і фіксується (клавіша F4), знак додавання і заноситься вкладена функція ABS (Рис. 11.7). Її ім’я вибирають по стрілці із зони, що розміщена ліворуч від рядка формул, як показано на Рис. 11.7.

Рисунок 11.3 – Виклик майстра функцій

а б

в г

Рисунок 11.4 – Вибір функції за допомогою майстра функцій

Рисунок 11.5 – Вибір функції натурального логарифма

Рисунок 11.6 – Заповнення аргументу функції логарифма

Рисунок 11.7 - Додавання вкладеної функції ABS (модуль числа)

Відкривається Майстер функцій для функції ABS, де необхідно занести аргументи для ABS – вибрати мишкою адресу значення x та математичну функцію СТЕПЕНЬ (Рис. 11.8)

Рисунок 11.8 – Використання математичної функції СТЕПЕНЬ

Для повернення із вкладеної функції необхідно у рядку формул мишкою вибрати ім’я викликаючої функції (у даному випадку LN), перейти у кінець тексту та продовжити набір (Рис. 11.9).

Рисунок 11.9 – Набір формули для обчислення виразу

Далі знову викликаємо функцію СТЕПЕНЬ для обчислення кореня третьої степені, оскільки серед функцій Excel є тільки квадратний корінь, виконуючи раніше описані дії. Функція СТЕПЕНЬ містить два параметри: перший – це вираз, який підноситься до степеня, а другий – це значення самого степеня (Рис. 11.8, 11.5.10).

Рисунок 11.10 – Обчислення степеня

Далі в аргументі функції СТЕПЕНЬ пишемо знак додавання + та викликаємо математичну функцію SIN, в аргументі якої задаємо значення, зазначені на Рис. 11.11.

Рисунок 11.11 – Заповнення аргументу функції SIN

Після закінчення введення формули мишкою необхідно натиснути Ок і результат обчислення заноситься у комірку D4. Використовуючи кнопку автозаповнення, введену формулу у комірки вниз по стовпчику до рядка 10 (Рис. 11.12).

Рисунок 11.12 – Автозаповнення формул

2 Обчислення значення Y₂

1 Для обчислення Y₂, використовують логічну функцію ЕСЛИ Вона реалізує алгоритм із розгалуженням і містить три аргументи:

=ЕСЛИ(умова; дія, якщо ІСТИНА; дія, якщо умова ХИБНА).

2 При обчисленні виразу Y₂ перевіряється, чи поточне значення x більше за 10, і якщо ця умова виконується, то обчислення проводиться за виразом

Y₂=e sinx-x. Якщо ж умова не виконується, обчислення проводиться за виразом:

Y₂=ln(|x| + 1)-cos(x²+x-3.14-a-b).

Формалізація задачі

1 Введення значень a, b.

2 Введення значення x.

3 Перевірка виконання умови x > 10. Якщо умова виконується, то Y₂ = e^sinx -V x ³, якщо умова не виконується, то Y₂ =ln(\x\ + 1)-cos(x² +x-3.14-a-b).

4 Виведення значення Y₂.

Блок-схема алгоритму розв’язання задачі подана нижче на Рис. 11.13.

Рисунок 11.13 – Блок-схема алгоритму для розрахунку виразу Y₂

3 Текст функції можна вводити або з клавіатури або за допомогою Майстра функцій. Якщо формулу вводити з клавіатури, то вона буде мати вираз: =ЕСЛИ(C4> 10; EXP(SIN(C4))-СТЕПЕНЬ(C4; 3/2); LN(ABS(C4)++1)-COS(СТЕПЕНЬ(C4; 2)+C4-3, 14*$A$4*$B$4)).

4 Також можна використовувати Майстер функцій.

Перший рядок аргументів містить умову: C4> 10.

Другий рядок аргументів містить формулу, якщо умова виконується – вираз: EXP(SIN(C4))-СТЕПЕНЬ(C4; 3/2).

Третій рядок аргументів містить формулу, якщо умова не виконується – вираз: LN(ABS(C4)+1)-COS(СТЕПЕНЬ(C4; 2)++C4-3, 14*$A$4*$B$4).

При цьому знову викликаються вкладені функції – EXP, SIN, СТЕПЕНЬ, LN, ABS, COS.

Рисунок 11.14 – Використання Майстра функцій

Після введення всього тексту формули необхідно повернутися у викликану функцію ЕСЛИ і натиснути Ок (Рис. 11.14), або в кінці формули у рядку формул натиснути клавішу Enter.

3 Обчислення значення Y₃

1 Для визначення виразу Y₃ так само використовують функцію ЕСЛИ, проте аргументами функції є не тільки формули для обчислення виразу, а й вкладені функції ЕСЛИ у випадку, якщо введена умова виконується або не виконується.

Формалізація задачі

1 Введення значення a, b.

2 Введення значення x.

3 Визначення умов.

У даному випадку значення функції може визначатися:

якщо

При x > 20 функція Y₃ не визначена. При x = 6 функція Y₃ не визначена. У даному випадку реалізується алгоритм із розгалуженням і перевіркою складної умови.

Спочатку використовується функція ЕСЛИ і перевіряється чи значення х < 6, і якщо так, то за умовою функція Y визначається за позицією 1. У протилежному разі х більше або дорівнює 6. Таким чином, якщо умова х < 6 не виконується, то необхідно перевіряти такі умови, щоб обчислити інші значення функції Y.

Слід знову викликати функцію ЕСЛИ і зробити перевірку, чи значення х = 6. Якщо ця умова виконується то необхідно вивести повідомлення «функція не визначена».

Для перевірки інших значень знову необхідно викликати функцію ЕСЛИ та перевірити умову х > 20. Якщо ця умова виконується то знову необхідно вивести повідомлення «функція не визначена».

Якщо вищезазначені умови не виконуються, то х знаходиться в проміжку від 6 до 20. Отже, знову необхідно викликати функцію ЕСЛИ та перевірити умову х > 14. Якщо ця умова виконується, то ми потрапляємо у проміжок від 14 до 20 і функція Y визначається за позицією 3.

Якщо ж ця умова не виконується, то ми потрапляємо в останній проміжок 6 < х < 14, де функція визначається за позицією 2.

Блок-схема алгоритму наведена нижче (Рис. 11.15).

Рисунок 11.15 – Блок-схема алгоритму для розрахунку виразу Y₃.

4 Виведення значення Y.

2 При введені з клавіатури, повний текст функції Y₃ виглядає:

=ЕСЛИ(C4< 6; СТЕПЕНЬ($B$4*C4; 1/3); ЕСЛИ(C4=6; " не визначена"; ЕСЛИ(C4> 20; " не визначена"; ЕСЛИ(C4> 14; EXP($A$4*$B$4*C4); LN($A$4*$B$4+СТЕПЕНЬ(C4; 3)))))).

3 При використанні майстра функцій існує чотири етапи.

Перший етап перевірки проілюстрований на Рис. 11.16.

Рисунок 11.16 – Перший етап перевірки

Другий етап перевірки проілюстрований на Рис. 11.17.

Рисунок 11.17 – Другий етап перевірки

Третій етап перевірки проілюстрований на Рис. 11.18.

Рисунок 11.18 – Третій етап перевірки

Четвертий етап перевірки проілюстрований на Рис. 11.19.

Рисунок 11.19 – Четвертий етап перевірки

Потім натискається Ок, обчислене значення записується у комірку і копіюється вниз по стовпчику до рядка 10.

У наведеному прикладі всі умови, що перевіряються, є простими, тобто за один раз перевіряється лише одна умова, проте відокремлювалися і визначалися інтервали, в яких функція Y₃ набувала того чи іншого значення (позиції 1 – 5).

4 Обчислення значення Y₄

1 Для обчислення Y₄ скористаємося іншими способами перевірки умов.

Формалізація задачі

1 Функція Y₄ не визначена, якщо х або дорівнює 6, або більше за 20. Тут необхідно використати функцію ИЛИ.

2 Якщо ж значення х лежить у інтервалі, де х більше за 6 та менше або дорівнює 14, то Y₄ обчислюється Y₄ =Ln(a-b + х³). Тут необхідно використати логічну функцію И.

3 Якщо ж х не лежить у жодному з перелічених інтервалів, то залишаються два інтервали. Для визначення одного з них необхідно використати функцію ЕСЛИ. Якщо, наприклад, х > 14, то ми потрапляємо в інтервал 14 < х < 20 Y₄ = е^abx.

4 Якщо ж жодна з вищеперелічених умов не виконується, то ми автоматично потрапляємо в інтервал х < 6, де Y₄ обчислюється за формулою .

2 При обчисленні з клавіатури одержуємо таку формулу: =ЕСЛИ(ИЛИ(C4=6; C44> 20); " не визначена"; ЕСЛИ(И(C4> 14; C4< =20); EXP($A$4*$B$4*C4); ЕСЛИ(И(C4> 6; C4< =14); LN($A$4*B4+СТЕПЕНЬ(C4; 3)); СТЕПЕНЬ($B$4*C4; 1/3))))

3 При обчисленні за допомогою Майстра функцій одержимо три етапи.

Перший етап виконання зображений на Рис. 11.20 та Рис. 11.21/

Рисунок 11.20 – Перший етап обчислення

Рисунок 11.21 – Використання функції ИЛИ

Другий етап виконання зображений на Рис. 11.22 та Рис. 11.23.

Рисунок 11.22 – Другий етап обчислення

Рисунок 11.23 – Використання функції И на другому етапі

Третій етап виконання зображений на Рис. 11.24 та Рис. 11.256.

Рисунок 11.24 – Третій етап обчислення

Рисунок 11.25 – Використання функції И на третьому етапі

Результат обчислень зображений на Рис. 11.26.

Рисунок 11.26 – Результат виконання обчислень за простими (Y₃) та складними (Y₄) умовами

4 Мінімальне, максимальне та середнє значення можна обчислити, використовуючи статистичні функції МИН, МАКС та СРЗНАЧ.

Потім введений текст копіюють з комірок С11: С13 до комірок D – G, праворуч.

У результаті матимемо такі розрахунки, що зображені на Рис. 11.27.

Рисунок 11.27 – Остаточний результат

Тема 12. ГРАФІЧНЕ ПОДАННЯ ДАНИХ

12.1. Призначення, основні поняття та типи діаграм

12.1.1. Основні поняття діаграм

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

Діаграма – це графічний спосіб подання даних.

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

Кожна діаграма пов’язана з таблицею значень або інформаційною таблицею.

Інформаційна таблиця – це сукупність комірок робочої книги, які використовуються для побудови діаграми.

Рядок даних – відображення значення одного рядка стовпчика таблиці на діаграмі.

Маркер – відображення кожної комірки на діаграмі.

Маркером може бути точка на графіку, сегмент круга, стовпчик гістограми та інші залежно від типу діаграми.

Сукупність маркерів одного кольору відображає один рядок. Елементи діаграми подані на рис. 12.1.

Рисунок 12.1 – Елементи площинної діаграми

12.1.2. Призначення діаграм

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

Вісь у називається віссю значень (або віссю рядів даних). Ця вісь відображає значення залежних змінних.

Вісь х – це вісь категорій і відображає значення незалежних змінних.

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

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

Поряд з діаграмою може бути розміщена легенда.

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

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

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

12.1.3. Типи діаграм

Стандартні діаграми бувають: гістограма; лінійна; графік; кругова діаграма; кільцева діаграма; діаграма з областями; точкова діаграма.

У таблиці 12.1 подані основні типи діаграм та їх вигляд.

Таблиця 12.1 – Типи діаграм

Вид	Тип діаграми	Опис
	Гістограма	Тип, що використовується за замовчуванням. Показує зміну даних за певний період часу та ілюструє співвідношення окремих значень даних. Категорії розміщуються по горизонталі, а значення - по вертикалі. Завдяки цьому приділяється більша увага змінам у часі. Підтипи: звичайна, гістограма з нагромадженням, нормована на 100%, їхні об'ємні варіанти
	З областями	Підкреслює величину зміни протягом певного періоду часу, показуючи суму уведених значень. Також відображає внесок окремих значень у загальну суму
	Лінійчата	Нагадує гістограму, у якій категорії розміщуються по вертикалі, а значення - по горизонталі. Завдяки цьому приділяється більша увага зіставленню значень, і менше - змінам у часі. Підтипи: звичайна, лінійчата діаграма з нагромадженням, нормована на 100%, їхні об'ємні варіанти
	Графік	Відбиває тенденції зміни даних за певні проміжки часу
	Кругова	Показує не стільки абсолютну величину кожного елемента ряду даних, скільки його внесок у загальну суму. На круговій діаграмі може бути поданий тільки один ряд даних
	Крапкова	Відображає взаємозв'язок між значеннями декількох рядів чисел. Часто використається для подання даних наукового характеру. При підготовці даних варто розмістити в одному рядку (або стовпці) всі значення змінної Х, а відповідні значення У - у суміжних рядках (або стовпцях)
	Кільцева	Показує внесок кожного елемента в загальну суму, але на відміну від кругової діаграми може містити кілька рядів даних. Кожне кільце подає окремий ряд даних

12.2. Технологія створення діаграм

12.2.1. Майстер діаграм

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

При ство