Робота з формулами

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

Формулою в Excel називається послідовність символів, що починається зі знака рівності й містить обчислюють элементы, що (операнды) і оператори.

Операндами можуть бути: постійні значення, посилання на осередки (діапазон осередків), імена, функції.

Існують чотири види операторів:

• арифметичні;

• оператори порівняння;

• адресні оператори;

• текстовий оператор «&», що використається для позначення операції об'єднання декількох послідовностей символів в одну.

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

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

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

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

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

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

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

Наприклад, якщо в осередку З3 була записана формула =З1*З2, то при копіюванні вмісту З3 в осередки D3 й E3 нові формули з обновленими посиланнями приймуть наступний вид: =D1*D2, =E1*E2.

Крім відносних посилань, у редакторі Excel часто використаються абсолютні посилання, де крім назви стовпця й номера рядка використається спеціальний символ «$», що фіксує частина посилання (стовпець, рядок) і залишає її незмінної при копіюванні формули з таким посиланням в інший осередок. Звичайно абсолютні посилання вказують на осередки, у яких утримуються константи, використовувані при обчисленнях.

Наприклад, якщо необхідно зафіксувати у формулі =А1*В1 значення осередку А1, що не повинне змінюватися у випадку копіювання даної формули, те абсолютне посилання на цей осередок буде мати такий вигляд: $1. Таким чином, при копіюванні формули з осередку В2 в осередок З2 формула прийме вид =$1*З1.

Якщо потрібно зафіксувати в посиланні тільки рядок або тільки стовпець, у якому перебуває використовуваний осередок, зокрема стовпець А або рядок 1, то посилання прийме вид або А$1 відповідно.

Змінити тип посилання можна в такий спосіб: виділити осередок з формулою;

• у рядку формул виділити посилання, яку потрібно змінити;

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

А1 при використанні клавіші F4 така:

• $1-абсолютне посилання (фіксований осередок);

• А$1 - змінюваний стовпець і незмінний рядок;

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

• А1 - відносне посилання.

У формулах можна використати посилання на осередки як поточного аркуша, так й інших аркушів робочої книги. Наприклад, посилання на осередок А5, розташовану на аркуші з ім'ям Аркуш2, буде виглядати так: Аркуш2!А5.

Формули з посиланнями можуть бути отримані двома способами:

• шляхом безпосереднього уведення посилань із клавіатури (уводяться латинськими буквами), що часто використається при редагуванні формул;

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

Другий спосіб містить у собі наступні дії:

• у режимі редагування встановити курсор у ту частину формули, куди необхідно вставити посилання, але обов'язково після математичного оператора або дужки;

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

• нажати клавішу Enter.

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

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

Всі функції використають однакові основні правила синтаксису. У випадку, якщо порушені ці правила, Excel видасть повідомлення про те, що у формулі є помилка.

Математичні, фінансові й інші функції. Для зручності роботи користувача при побудові формул функції в Excel розбиті по категоріях: функції керування базами даних і списками, функції дати й часу, фінансові, статистичні, текстові, математичні, логічні.

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

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

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

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

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

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

Функції округлення:

ОКРУГЛИЙ - округляє число по звичайних математичних правилах до зазначеного числа десяткових знаків;

ОКРУГЛВВЕРХ - округляє число нагору до зазначеного числа десяткових знаків;

ОКРУГЛВНИЗ - округляє число вниз до зазначеного числа десяткових знаків.

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

Приклад:

ОКРУГЛИЙ(10,649;2) - результат 10,65. ОКРУГЛВВЕРХ(10,643;2) - результат 10,65. ОКРУГЛВНИЗ(10,689;2) - результат 10,68. ОКРУГЛИЙ(5,99;0) -результат 6.

Функція ПРОИЗВЕД(арг.1;арг.2;...) - перемножує аргументи. Як аргументи можуть указуватися числа, посилання на осередки, посилання на діапазони осередків. Можна вказувати до 30 аргументів.

Функція КОРІНЬ(число) - обчислює квадратний корінь із числа.

Функція СТУПІНЬ(число;ступінь) - зводить число в ступінь.

Функція ОТБР(число;кількість цифр) - залишає в числі зазначена кількість десяткових цифр, інші відкидає.

Приклад:

ОТБР(5,389;2) -результат 5,38. ОТБР(5,389;1) -результат 5,3.

ОТБР(5,389;0) - результат 5.

Функція ОСТАТ(число;дільник) - обчислює залишок від розподілу 'числа' на 'дільник'.

Приклад:

ОСТАТ(7;6) -результат 1.0СТАТ(32;15) -результат 2.

Функція ЦІЛЕ(число) - округляє число до найближчого меншого цілого.

Приклад:

ЦІЛЕ(10,999) -результат 10. ЦІЛЕ(-10,999) -результат -11.

Функція СУММЕСЛИ(діапазон;умова;діапазон підсумовування) - підсумує осередку в 'діапазоні підсумовування', які відповідають осередкам в 'діапазоні', що задовольняє 'умові'. Якщо аргумент 'діапазон підсумовування' не вказується, підсумуються осередки в 'діапазоні'.

Приклади:

СУММЕСЛИ(А1^1;»>50») - підсумуються числа більше 50 з діапазону А1^1.

СУММЕСЛИ(А1:А20;»інженер»;В1:В20) - підсумуються осередки з діапазону В1:В20, що відповідають осередкам діапазону А1:А20, що містить текст «інженер».

СУММЕСЛИ(З5:З17;0р5^17) - підсумуються осередки з діапазону D5:D17, що відповідають осередкам діапазону З5:З17, що містить 0.

Функція СУММПРОИЗВ(діапазон1;діапазон2;...) - обчислює суму добутків діапазонів. Всі діапазони повинні мати однаковий розмір. Можна задавати від 2 до 30 аргументів.

Правила синтаксису при записі функцій. Якщо функція з'являється на самому початку формули, їй повинен передувати знак рівності, як це має місце на початку будь-якої формули.

Після цього вводиться ім'я функції й відразу за ним - список аргументів у круглих дужках. Аргументи відокремлюються друг від друга крапкою з коми «;».

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

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

Розглянемо роботу функції ОКРУГЛИЙ(арг1;арг2), що округляє число до заданої кількості знаків після коми й має два аргументи:

арг1-адреса осередку із числом (або саме число), яких потрібно округлити;

арг2 - кількість цифр після коми в числа після округлення.

Щоб округлити число 3,25688, що перебуває в осередку А1, з точністю до одного, двох або трьох знаків після коми й записати результати обчислень відповідно в осередки Bl, C1 й D1, необхідно діяти в такий спосіб:

• Увести число 3,25688 в осередок А1.

• Увести в осередки Bl, C1 й D1 такі формули (мал. 5.7):

=ОКРУГЛИЙ(А1;1) =ОКРУГЛИЙ(А1;2) =ОКРУГЛИЙ(А1;3)

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

=СУМ(ОКРУГЛИЙ(А1;2);ОКРУГЛИЙ(А2;2))

Тут функція ОКРУГЛА є вкладеною. Excel дозволяє використати у формулах не більше семи рівнів вкладеності функцій.

В Excel існують функції, які не мають аргументів. Прикладами таких функцій є ПІ (повертає значення числа п, округлене до 15 знаків) або СЬОГОДНІ (повертає поточну дату). При використанні подібних функцій треба в рядку формул відразу після назви функції ставити круглі дужки. Інакше кажучи, щоб одержати в осередках значення числа Ш або поточну дату, потрібно ввести формули такого виду:

=ПІ()

=СЬОГОДНІ()

Використання логічних функцій. Логічні функції варто використати в тих випадках, коли для різних варіантів вихідних даних потрібні різні дії.

Функція ЯКЩО(логічне_вираження; значення_ес-ли_істина; значення_якщо_неправда).

Функція аналізує логічне вираження; якщо значення вираження - ІСТИНА, то виконуються дії, задані другим аргументом функції; якщо значення логічного вираження - НЕПРАВДА, то виконуються дії, задані третім аргументом.

Приклади логічних виражень:

В34>50; А1=0; З12=«юрист»; Б2 <> «юрист».

У якості другого й третього аргументів функції ЯКЩО можуть використатися константи, посилання на осередки, формули.

Приклад:

=ЯКЩО(В34>50;В34*2;»Значення занадто мало!») -якщо число в осередку В34>50, те це число множиться на 2; якщо число в осередку В34 <= 50, то виводиться текст «Значення занадто мало!»

Якщо для виконання дій потрібно проаналізувати не одне, а кілька умов, то як перший аргумент функції ЯКЩО можна використати функції

І й АБО.

І(логич_выраж._1;логич._выраж._2;...)

АБО(логич._выраж._1;логич._выраж. 2;...)

Функції И и АБО можуть містити від 2 до 30 аргументів.

Функція И має значення ІСТИНА, якщо щирі всі її аргументи; якщо хоча б один аргумент має значення НЕПРАВДА, то функція має значення НЕПРАВДА.

Приклад: в осередках утримуються значення: А1=50, В1=0, З1 = 100.

І(А1>0;В1=0;З1<150) -ІСТИНА И(А1=0 У1=0;З1 = 100) -НЕПРАВДУ Функція АБО має значення ІСТИНА, якщо хоча б один її аргумент має значення ІСТИНА; функція має значення НЕПРАВДА, якщо помилкові всі її аргументи. Приклад: осередок А1 містить текст «юрист»;

АБО(А1 = «юрист»;А1=«економіст») - ІСТИНА; АБО(А1 = «економіст»;А1=«психолог») - НЕПРАВДА. У якості другого й третього аргументів функції ЯКЩО можна використати функцію ЯКЩО. Можливо до 7 рівнів вкладеності.

Створення вкладених функцій. За допомогою Майстер функцій можна також створювати й складні формули із вкладеними функціями. Для приклада розглянемо таблицю (мал. 5.13), у якій необхідно підрахувати суму значень стовпця Зібрано картоплі, т, округливши результат до двох десяткових знаків. При цьому функція СУМ буде вкладеної у функцію ОКРУГЛИЙ.

Щоб створити складну формулу, необхідно зробити наступне:

• виділити осередок В7 і вставити функцію округлення ОКРУГЛИЙ, як це вже було описано;

• у діалоговому вікні, що відкрилося, для уведення параметрів функції ОКРУГЛИЙ у поле Число вставити функцію підсумовування, а в поле Число_розрядів увести значення 2;

• в діалоговому вікні встановити курсор у поле Число верба^-брати в поле Функція, розташованому ліворуч від рядка формул, за допомогою списку, що розкривається, функцію

СУМ;

• як аргумент функції СУМ указати діапазон осередків В2:В6;

• для продовження редагування формули перемістити курсор у рядку формул на назву тієї функції, уведення параметрів якої ще не завершений, у цьому випадку — ОКРУГЛИЙ. При цьому у вікні Майстер функцій відобразяться параметри уведення функції ОКРУГЛИЙ;

• для уведення остаточного варіанта формули нажати кнопку ОК вікна Майстер функцій. Після цього в осередку В7 відобразиться обчислене значення.

Редагування формул. При створенні або зміні

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

Для внесення змін у формулу необхідно провести наступні операції:

• виділити необхідний осередок;

• клацнути мишею в рядку формул або нажати клавішу

• внести всі необхідні зміни. Це можна здійснити або безпосереднім редагуванням у рядку формули, або за допомогою Майстер функцій. Щоб скористатися ним, треба щигликом миші в рядку формул установити курсор на ту функцію, параметри якої потрібно змінити, нажати кнопку Вставити функцію, після чого в діалоговому вікні, що з'явилося, можна змінювати й додавати параметри поточної функції;

• після внесення у формулу всіх необхідних змін нажати кнопку ОК.