Лекція 6. Робота з формулами в табличному процесорі Excel

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

Елементи формул

Формула, що вводиться в осередок, може складатися з наступних елемен-

тів:

•Оператори. Символи, наприклад "+" (додавання) і "*" (множення).

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

•Значення або рядки. Наприклад, 7,5 або "Результати на кінець року".

•Функції і їхні аргументи. Сюди відносяться функції, такі як СУМ або СРЗНАЧ і їхні аргументи.

•Дужки. Задають порядок виконання дій у формулі

Уведення формул

Якщо ввести в осередок знак рівності, програма сприймає наступну за цим знаком запис як формулу (формули в Excel завжди починаються зі знака рівності). На початку формули припустиме також використання знаків "плюс" і "мінус". Однак Excel автоматично замінить кожної з них на знак рівності, як тільки ви закінчите уводити формулу.

Ручне уведення формул

Ручне уведення формул означає, що ви просто активізуєте осередок і вводите в ній знак рівності (=), а за ним – саму формулу. Символи, що вводяться, одночасно з'являються в осередку й у рядку формул. При уведенні формул ви, звичайно ж, можете використовувати клавіші, призначені для редагування. Закінчивши уводити формулу, натисніть <Enter>.

Зауваження: Виключення складають формули масивів. По закінченні уведення формул масиву варто натиснути комбінацію клавіш <Ctrl+Shift+Enter>.

Після того як ви натиснете <Enter>, в осередку буде відображений результат виконання формули. Сама ж формула буде з'являтися в рядку формул, коли відповідний осередок буде активна.

Уведення формул із указівкою посилань на осередки

Цей спосіб також припускає ручне введення деяких елементів, однак з'являється можливість указати посилання на осередки, замість того щоб задавати них вручну. Наприклад, щоб увести формулу =А1+А2 в осередку A3, необхідно виконати наступні дії:

1.Виділите осередок A3.

2.Уведіть знак рівності (=). Зверніть увагу, Excel відображає слово Введення в

56

лівій частині рядка стану.

3.Двічі натисніть на клавішу зі стрілкою нагору. Натиснувши на цю клавішу, ви заметете навколо осередку рамку. Посилання на осередок (А1) з'являться в осередку A3 і в рядку формул (Якщо ви волієте використовувати мишу, наведіть покажчик на осередок А1 і клацніть мишею). Зверніть увагу на слово Вкажіть у рядку стану.

4.Уведіть знак плюс (+). Рамка, що рухається, зникне, а в рядку стану знову

з'явиться Введення.

5.Ще раз натисніть клавішу зі стрілкою нагору. До формули додасться А2.

(Якщо ви волієте використовувати мишу, наведіть покажчик на осередок А2 і клацніть кнопкою миші)

5.Закінчивши уведення формули, натисніть <Enter>. Точно так само, як і при ручному уведенні формул, результат виконання формули відобразиться в осередку. Формула з'явиться в рядку формул, коли відповідний осередок буде активна.

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

Задавати посилання на осередки – менш утомливо, чим уводити формулу вручну. Крім того, це допомагає уникнути помилок при уведенні формул.

У Excel 97 і Excel 2000 існує панель формул1 , що допоможе вам при введенні і редагуванні формул. Для того, щоб викликати панель формул, клацніть на кнопці Змінити формулу, розташованої в рядку формул (на цій кнопці зображений знак рівності). Панель формул дозволяє звертатися до формул вручну або застосовувати технікові завдання посилань, описану вище.

Граничний розмір формул

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

Застосування операторів у формулах

Оператор – це символ, що позначає операцію. Excel підтримує наступні операторы:

1 В Excel 2002 панель формул отсутствует.

57

>= Логічне порівняння (більше або дорівнює) <= Логічне порівняння (менше або дорівнює) <> Логічне порівняння (не дорівнює)

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

Пріоритет операторів

У формулах можна (і потрібно) використовувати дужки. Дужки дозволяють контролювати порядок виконання обчислень.

Для того щоб зрозуміти принцип, відповідно до якого варто розставляти дужки, необхідно ознайомитися з таким поняттям, як пріоритет операторів. Пріоритет операторів – це звід правил, згідно яким Excel робить обчислення. Спочатку виконуються операції з більш високим пріоритетом, потім – з менш високим (табл. 6.1).

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

Використання дужок дозволяє контролювати порядок виконання дій. Спочатку завжди виконуються ті частини формули, що укладені в дужки.

Вкладені дужки

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

= ((В2*С2) + (ВЗ*СЗ) + (В4*С4))*В6

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

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

58

=А1*А2+1

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

=(А1*А2)+1

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

Якщо у формулі маються непарні дужки, Excel, у деяких випадках, може запропонувати варіанти виправлення помилки (у Excel 97 з'явилася функція автоматичного виправлення помилок у формулах).

Помилки у формулах

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

1 Excel помогает следить за скобками в формулах. При вводе или редактировании формул желательно внимательно следить за текстом. Когда курсор располагается возле скобки, программа на мгновение выделяет ее и парную ей скобку полужирным шрифтом. Будьте внимательны – это продлится менее секунды.

59