Формули і функції в Excel

Формула =TREND(B1:B3;A1:A3), введена як формула масиву, повертає три значення (22196, 17079 і 11962), обчислені за трьома обсягами продажу за три місяці.

3. Натисніть сполучення клавіш CTRL+SHIFT+ENTER.

Формулу буде автоматично  охоплено фігурними дужками { }.

Примітка. Якщо ввести дужки навколо формули вручну, вона не стане формулою масиву — для створення формули масиву обов'язково потрібно натиснути клавіші CTRL+SHIFT+ENTER.

Увага!  Після редагування формули масиву фігурні дужки { } зникають, і необхідно натиснути клавіші CTRL+SHIFT+ENTER знову, щоб застосувати зміни та додати дужки.

1.7 Видалення формули

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

• Щоб видалити формулу та її результати, виконайте такі дії.
1. Виділіть клітинку або діапазон клітинок, які містить формулу.
2. Натисніть клавішу DELETE.

1.8 Поради зі створення формул

1.8.1 Швидке копіювання формул

Можна швидко ввести одну й ту саму формулу в діапазон клітинок. Виберіть діапазон, для якого потрібно виконати обчислення, введіть формулу та натисніть сполучення клавіш CTRL+ENTER. Наприклад, якщо ввести =SUM(A1:B1) у діапазоні C1:C5 і натиснути сполучення клавіш CTRL+ENTER, формулу буде введено в кожну клітинку діапазону, а A1 буде використано як відносне посилання.

1.8.2 Використання автозаповнення формул

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

1.8.3 Використання підказок для функцій

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

1.9 Порядок введення формул

Для введення формули  у комірку потрібно її виділити  і ввести в рядок формул або у комірку знак «=».

При переході в режим введення формул поле Ім'я (Name) розташоване в лівій частині рядка формул, замінюється кнопкою, на якій відображує остання функція, що використалася. Розташована праворуч від неї кнопка із стрілкою відкриває список, який містить 10 останніх функцій, що використалися, і запис Інші функції (More/functions).

Приклад обчислення за формулою =(A4+15)/B5. Формула буде відображатися в рядку формул.

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

Аби послатися на діапазон комірок, потрібно ввести посилання  на верхню ліву комірку діапазону, поставити  двокрапку (:), а потім — посилання  на правий нижній кут діапазону. Наприклад, А2:C5. Посилання на всі вічка між 6-м і 15-м рядками включно має вигляд 6:15, на всі вічка у 3-му стовпці  — С:С.

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

Найбільш часто як формулу використовують функцію , що знаходить суму чисел у заданому діапазоні. У російськомовних електронних  таблицях вони записується як СУММ , а в англомовних - як SUM.  Ось приклади її використання: 

• =СУММ(Е:E) — визначається сума числових значень, що містяться в стовпці Е;  
• =СУММ(Е2:G5) — підраховується сума числових значень, розташованих в діапазоні від Е2 до G5;  
• =СУММ(5:5) — підсумовуються дані всіх комірок п'ятого рядка;  
• =СУММ(2;4) — підраховується сума 2+4.

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

=СУММ(С8;О4;Е2:Е5;Р5)

Часто при введені  посилань на комірки у формулі використовується миша. Так, якщо необхідно після знаку = у формулі потрібно дати посилання на комірку А4, то можна  мишею клацнути по цій комірці, після чого  довкола комірки з'явиться біжуча рамка, а у формулі відображатиметься адреса цієї комірки . Для введення посилання на діапазон комірок слід клацнути по кутовій комірці діапазону і утримуючи мишку розширити його на весь  діапазон.

1.10 Редагування формул

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

Зміни посилання в  формулі:

• Виділити в рядку адрес комірки подвійним клацанням;
• Клацнути в таблиці комірку, на яку повинне бути посилання.

Зміна типу адресації:

• Виділити адрес комірки подвійним клацанням мишки;
• Натиснути клавішу <F2>.

Для підтвердження внесення змін користуватись клавішею <Enter> або кнопку Ввод в рядку формул; для відміни змін – клавішу <Esc> або кнопку  Отмена в рядку формул.

1.11 Копіювання і переміщення формул.

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

Для копіювання необхідно:

1. виділити групу комірок, вміст яких слід скопіювати;
2. викликати команду Копировать меню Правка або натиснути кнопку (навколо групи комірок з'явиться пунктирна лінія, яка вказує, що фрагмент скопійовано у буфер);
3. виділити групу комірок, в які слід уставити дані;
4. викликати команду Вставить меню Правка або натиснути кнопку .

Фрагмент із буферу скопіюється  в зазначене місце.

При копіюванні комірок, що містять формули з відносними посиланнями, координати комірок аргументів змінюються автоматично. Наприклад, при копіюванні формули =А1+А2 з вічка А3 у вічко В3 її вміст зміниться на =В1+В2, тобто ця формула як і раніше обчислює суму двох комірок ліворуч від вічка з формулою. При копіюванні комірок, що містять формули з абсолютними посиланнями, адреси комірок-аргументів не змінюються.

Для переміщення вмісту комірок необхідно:

1. виділити групу комірок, вміст яких потрібно перемістити;
2. викликати команду Вырезать меню Правка або натиснути кнопку на (навколо групи комірок з'явиться пунктирна лінія, яка позначає, що фрагмент переміщений у буфер);
3. виділити верхнє ліве вічко в області, в якій необхідно уставити фрагмент;
4. викликати команду Вставить меню Правка або натиснути кнопку .

Фрагмент із буферу буде уставлений у зазначене місце.

При переміщенні комірок, що містять формули, координати комірок аргументів не змінюються. Наприклад, при переміщенні формули =А1+А2 з вічка А3 у вічко В3 формула залишиться =А1+А2.

Натискання правою клавішею миші на будь-якому об'єкті таблиці викликає контекстне меню, що містить команди, для обробки даного об'єкта (Малюнок 1.2).

1.12 Відображення формул на робочому листі

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

Перейти до режиму відображення усіх формул на екрані можна в такий  спосіб:

♦ввійти у меню Сервис;

♦вибрати команду Параметры і відкрити вкладку Вид;

♦у розділі Параметры окна встановити прапорець Формулы і натиснути ОК.

У цьому режимі стовпці  автоматично розширюються до розміру  формул так, щоб формули були краще  видні. Якщо потрібно повернутися до колишнього режиму відображення таблиці, то знову виконати команду Сервис → Параметры → вкладка Вид і зняти прапорець Формулы.

 

1.13 Використання тексту в формулах

З текстовим значенням  можна виконувати математичні операції, якщо текстове значення містить тільки слідуючі символи:

Цифри від 0 до 9, +, -,  е,  Е,  /.

Також можна використовувати  5 символів числового форматування:

$, %, (), пробел.

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

Неправильно:  =$55+$33

Правильно: ="$55"+$"33"

Для виконання обчислень Excel перетворить числовий текст в числове значення, так результатом вищевказаної формули буде значення 88.

Для об’єднання текстових  значень служить текстовий оператор & (амперсанд). Наприклад, якщо комірка А1 містить текстове значення «Таня», а комірка А2 -  «Посільська», то ввівши в комірку А3 наступну формулу=А1&А2, отримаємо «ТаняПосільська». Для вставки пробілу між іменем і прізвищем потрібно написати так =А1&" "&А2.

Ампресанд можна використовувати  для об’єднання  комірок з  різними типами даних. Так, якщо в комірці А1 знаходиться число 10, а в комірці А2 – текст «гривень», то в результаті дії формули =А1&А2, ми отримаємо «10гривень». Причому результатом такого об’єднання буде текстове значення.

Для написання коротких не математичних фраз типу «для всіх»  у виразі вигляду |xn-x0|<e  «для всіх»  n³N використовують режим стилю «Текст». В цьому режимі символи алфавіту відформатовані як звичайний текст і можна використовувати клавішу ПРОПУСК для утворення інтервалів між словами.

1. Установіть курсор  туди, звідки слід почати введення тексту.

2. Виконайте одну з  наступних дій: 

• Виберіть команду «Текст» у меню «Стиль».
• Натисніть клавіші CTRL+SHIFT+E.

3. Введіть текст. 

4. Для повернення до  стилю «Математичний» виконайте  одну з таких дій: 

• Виберіть команду «Математичний» у меню «Стиль».
• Натисніть клавіші CTRL+SHIFT+=.

1.14 Імена комірок для абсолютної адресації

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

Ім'я комірки або  діапазону можна використовувати  у формулі. Хай у нас в вічку  А3 записана формула =А1+А2. Якщо присвоїти вічку А1 ім'я "Танюша", а вічку А2 - "Посільська", то запис =Танюша+Посільська поверне те ж саме значення, що і попередня формула.

Для присвоєння імені  комірці (діапазону комірок) треба  виділити відповідний елемент, а потім в полі імені ввести найменування, при цьому не можна використовувати пропуски.

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

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

Гнучкіші можливості присвоювання імен комірок і їх діапазонів, а також заголовків, дає команда "Ім'я" з меню "Вставка".

Правила присвоєння  імен вічкам і діапазонам комірок:

1. Ім'я повинне починатися  з літери, зворотної косої  (\) або символу підкреслення (_).

2. У імені можна  використовувати тільки літери, цифри, зворотну косу  і символ підкреслення.