зміст
У багатьох початківців користувачів Excel часто виникає питання: що таке формула Excel і як її ввести в комірку. Багато хто навіть замислюється, навіщо це потрібно. Для них Excel – це електронна таблиця. Але насправді це великий багатофункціональний калькулятор і в деякій мірі середовище програмування.
Поняття формули та функції
А вся робота в Excel базується на формулах, яких величезна кількість. В основі будь-якої формули лежить функція. Це базовий обчислювальний інструмент, який повертає значення залежно від переданих даних після їх попередньої обробки.
Формула — це набір логічних операторів, арифметичних операцій і функцій. Він не завжди містить усі ці елементи. Розрахунок може включати, наприклад, тільки математичні дії.
У повсякденному мовленні користувачі Excel часто плутають ці поняття. Насправді межа між ними досить умовна, і часто вживаються обидва терміни. Однак для кращого розуміння роботи з Excel необхідно знати правильні значення.
Насправді термінологічний апарат значно ширший і включає багато інших понять, які необхідно розглянути більш детально.
- Постійний. Це значення, яке залишається незмінним і не може бути змінено. Це може бути, наприклад, число Пі.
- Оператори. Це модуль, необхідний для виконання певних операцій. Excel пропонує три типи операторів:
- Арифметика. Потрібно додавати, віднімати, ділити та множити кілька чисел.
- Оператор порівняння. Необхідно перевірити, чи дані відповідають певній умові. Він може повертати одне значення: істинне або хибне.
- Текстовий оператор. Він лише один і потрібен для об’єднання даних – &.
- Посилання. Це адреса комірки, з якої будуть взяті дані, всередині формули. Існує два види посилань: абсолютні та відносні. Перші не змінюються, якщо формулу перенести в інше місце. Відносні, відповідно, змінюють комірку на сусідню або відповідну. Наприклад, якщо в якійсь комірці вказати посилання на комірку B2, а потім скопіювати цю формулу в сусідню праворуч, адреса автоматично зміниться на C2. Посилання може бути внутрішнім і зовнішнім. У першому випадку Excel отримує доступ до клітинки, розташованої в тій самій книзі. У другому – в іншому. Тобто Excel може використовувати у формулах дані, розташовані в іншому документі.
Як ввести дані в клітинку
Одним із найпростіших способів вставити формулу, що містить функцію, є використання майстра функцій. Для її виклику потрібно натиснути на іконку fx трохи лівіше рядка формул (вона знаходиться над таблицею, і в ній дублюється вміст комірки, якщо в ній немає формули або формула відображається, якщо так. З’явиться таке діалогове вікно.
Там ви можете вибрати категорію функції та безпосередньо ту зі списку, яку ви хочете використовувати в певній клітинці. Там ви можете побачити не лише список, але й те, що робить кожна з функцій.
Другий спосіб введення формул — використання відповідної вкладки на стрічці Excel.
Тут інший інтерфейс, але механіка та сама. Всі функції розділені на категорії, і користувач може вибрати ту, яка йому більше підходить. Щоб побачити, що робить кожна з функцій, потрібно навести на неї курсор миші та почекати 2 секунди.
Ви також можете ввести функцію безпосередньо в клітинку. Для цього потрібно почати писати в ньому символ введення формули (= =) і вручну ввести назву функції. Цей спосіб підійде для більш досвідчених користувачів, які знають його напам'ять. Дозволяє значно заощадити час.
Після введення перших літер з'явиться список, в якому також можна вибрати потрібну функцію і вставити її. Якщо немає можливості використовувати мишу, то ви можете переміщатися по списку за допомогою клавіші TAB. Якщо так, то достатньо просто подвійного клацання на відповідній формулі. Після вибору функції з’явиться підказка, яка дозволить вам ввести дані в правильній послідовності. Ці дані називаються аргументами функції.
Якщо ви все ще використовуєте версію Excel 2003, то вона не надає випадаючого списку, тому вам потрібно запам'ятати точну назву функції та ввести дані з пам'яті. Те саме стосується всіх аргументів функції. На щастя, для досвідченого користувача це не проблема.
Важливо завжди починати формулу зі знака рівності, інакше Excel вважатиме, що клітинка містить текст.
У цьому випадку дані, які починаються зі знака плюс або мінус, також будуть вважатися формулою. Якщо після цього в комірці буде текст, то Excel видасть помилку #NAME?. Якщо подано цифри чи числа, Excel спробує виконати відповідні математичні операції (додавання, віднімання, множення, ділення). У будь-якому випадку рекомендується починати введення формули зі знака =, як це прийнято.
Подібним чином ви можете почати писати функцію зі знаком @, який буде автоматично змінено. Цей метод введення вважається застарілим і необхідний для того, щоб старі версії документів не втрачали частину функціональності.
Поняття аргументів функції
Майже всі функції містять аргументи, якими можуть бути посилання на клітинку, текст, число і навіть інша функція. Отже, якщо ви використовуєте функцію ЕНЕЧЕТ, вам потрібно буде вказати числа, які будуть перевірятися. Буде повернено логічне значення. Якщо це непарне число, буде повернуто TRUE. Відповідно, якщо парне, то «ЛОЖЬ». Аргументи, як ви можете бачити на скріншотах вище, вводяться в квадратних дужках і розділяються крапкою з комою. При цьому, якщо використовується англійська версія програми, то роздільником служить звичайна кома.
Вхідний аргумент називається параметром. Деякі функції їх взагалі не містять. Наприклад, щоб отримати поточний час і дату в комірці, потрібно записати формулу =ТАТА (). Як бачимо, якщо функція не вимагає введення аргументів, дужки все одно потрібно вказувати.
Деякі особливості формул і функцій
Якщо дані в клітинці, на яку посилається формула, редагуються, вони автоматично перераховуються відповідно. Припустимо, у нас є клітинка A1, у якій записана проста формула, що містить звичайне посилання на клітинку = D1. Якщо змінити інформацію в ньому, то таке ж значення буде відображатися в комірці А1. Так само для складніших формул, які беруть дані з певних комірок.
Важливо розуміти, що стандартні методи Excel не можуть змусити клітинку повернути своє значення іншій клітинці. Водночас це завдання можна вирішити за допомогою макросів – підпрограм, які виконують певні дії в документі Excel. Але це вже зовсім інша тема, яка явно не для новачків, оскільки вимагає навичок програмування.
Поняття формули масиву
Це один із варіантів формули, який вводиться дещо іншим способом. Але багато хто не знають, що це таке. Отже, давайте спочатку розберемося у значенні цього терміна. Набагато простіше зрозуміти це на прикладі.
Припустимо, у нас є формула SUM, який повертає суму значень у певному діапазоні.
Давайте створимо такий простий діапазон, записавши числа від одного до п’яти в клітинки A1:A5. Потім вказуємо функцію = SUM (A1: A5) у комірці B1. У результаті там з'явиться число 15.
Це вже формула масиву? Ні, хоча він працює з набором даних і його можна назвати таким. Давайте внесемо деякі зміни. Припустимо, нам потрібно додати по одному до кожного аргументу. Для цього вам потрібно створити таку функцію:
=СУМ(A1:A5+1). Виявляється, ми хочемо додати одиницю до діапазону значень, перш ніж обчислювати їх суму. Але навіть у такому вигляді Excel не захоче цього робити. Він повинен показати це за допомогою формули Ctrl + Shift + Enter. Формула масиву відрізняється зовнішнім виглядом і виглядає так:
{=СУММ(A1:A5+1)}
Після цього в нашому випадку буде введено результат 20.
Немає сенсу вводити фігурні дужки вручну. Це нічого не дасть. Навпаки, Excel навіть не подумає, що це функція, а просто текст замість формули.
Тим часом усередині цієї функції було виконано наступні дії. Спочатку програма розкладає цей діапазон на компоненти. У нашому випадку це 1,2,3,4,5. Далі Excel автоматично збільшує кожен із них на одиницю. Потім отримані числа складаються.
Є ще один випадок, коли формула масиву може робити щось, чого стандартна формула не може. Наприклад, у нас є набір даних у діапазоні A1:A10. У стандартному випадку буде повернено нуль. Але припустимо, що у нас така ситуація, що нуль неможливо врахувати.
Давайте введемо формулу, яка перевіряє, чи діапазон не дорівнює цьому значенню.
=МИН(ЕСЛИ(A1:A10<>0;A1:A10))
Тут виникає помилкове відчуття, що бажаний результат буде досягнутий. Але це не так, тому що тут потрібно використовувати формулу масиву. У наведеній вище формулі буде перевірятися тільки перший елемент, що нас, звичайно, не влаштовує.
Але якщо ви перетворите це на формулу масиву, вирівнювання може швидко змінитися. Тепер найменше значення буде 1.
Формула масиву також має ту перевагу, що вона може повертати кілька значень. Наприклад, можна транспонувати таблицю.
Таким чином, існує багато різних типів формул. Деякі з них вимагають простішого введення, інші складнішого. Формули масивів можуть бути особливо важкими для розуміння початківцям, але вони дуже корисні.