Введення та редагування формул

 

Формула повинна починатись із знаку “ = ”. Існують два способи введення формул — введення адрес даних вручну або вказуючи адреси використовуваних в них даних безпосередньо в робочому листку.

Введення формул вручну. Набирається знак “ = ” і сама формула. По мірі набору символи будуть з’являтись як у комірці, так і в рядку формул.

Введення формул шляхом зазначення (посилання). Зазначення стосуються адрес комірок та імен.

Для введення адреси комірки (діапазону) у формулу табличний курсор необхідно перемістити у комірку (діапазон), адреса якої фігурує в формулі як операнд, клацнути по ній (при цьому лінії рамки курсора будуть мати вигляд бігучих ліній) і набрати наступний у формулі оператор. Адреса потрібної комірки (діапазону) автоматично з’явиться у формулі.

В формулі замість адреси комірки (діапазону) можна використовувати її ім ’ я. Імена можна набирати замість адрес або вибирати їх із списку, щоб програма Excel автоматично вставила імена у формулу. Способи вставки імен у формулу:

® виконати команду “Вставка Þ Ім’я Þ Вставити” (Insert Þ Name Þ Paste) і в діалоговому вікні “Вставка імені” (Paste Name), яке містить перелік всіх імен, визначених в даній книзі, клацнути на потрібному імені. Подвійне клацання приведе і до закриття діалогового вікна.

® натиснути клавішу < F3 >, що також призведе до відкриття діалогового вікна “Вставка імені”.

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

® формат запису адреси комірки з іншого робочого листка, який належить до цієї ж робочої книги —

< ім’я листка >! < Адреса комірки>

Якщо в імені листка є один чи кілька пропусків, то в посиланні це ім’я розміщується в одинарних лапках —

‘ < ім’я листка > ‘.

* формат запису адреси комірки з іншої робочої книги —

[ < ім’я робочої книги > ] < ім’я листка >! < Адреса комірки >.

Якщо в імені робочої книги є один чи кілька пропусків, то в посиланні ім’я книги і ім’я листка розміщуються в одинарних лапках —

‘[ < ім’я робочої книги > ] < ім’я листка > ‘.

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

‘c: \ < ім’я папки > \ … \ < ім’я папки > \ [ < ім’я робочої книги > ] < ім’я листка > ‘! < Адреса комірки >.

 

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

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

У випадках, коли необхідно, щоб координати адрес комірок не змінювались, застосовується абсолютне посилання — перед буквою стовпчика і номером рядка ставиться символ “ $ ”.

У випадках, коли необхідно зафіксувати тільки одну з координат, застосовуються змішані посилання — символ “ $ ” ставиться тільки перед тією координатою, яка неповинна змінюватись. Приклади:

 

Адреса	Тип
A1	Відносне посилання
$A$1	Абсолютне посилання
$A1	Змішане посилання (абсолютною є координата стовпчика)
A$1	Змішане посилання (абсолютною є координата рядка)

 

При ручному введенні у формулі невідносних посилань можна в потрібних місцях ввести символ “ $ ” або після введення відносної адреси кілька разів натиснути клавішу < F4 >, змінюючи цим тип посилання.

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

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

® двічі клацнути на комірці і відредагувати формулу безпосередньо у комірці;

® виділити комірку, натиснути клавішу < F2 > івідредагувати вміст у комірці або в рядку формул;

® активізувати комірку, клацнути на рядку формул і внести зміни в рядку формул;

® за певними установками команд “Правка Þ Очистити”;

® виділити комірку з формулою, клацнути на кнопці “Змінити формулу” (Edit Formula) , яка знаходиться в рядку редагування, щоб отримати доступ до засобу “Палітра формул” (Formula Palette).

Перетворення формули у значення. Якщо є формули, які завжди дають один і той же результат (так звані “мертві формули”), то можна перетворити їх у значення. Для цього необхідно застосувати команду “Правка Þ Спеціальна вставка” (Edit Þ Paste Special), в діалоговому вікні “Спеціальна вставка“ (Paste Special) встановити перемикач “Значення” (Values) і клацнути на кнопці “ОК”. Щоб відмінити режим вставки, треба натиснути клавішу < Enter > або < Esc >.

 

Режими обчислень в Excel

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

· Формули у робочій таблиці вираховуються миттєво. Якщо змінити значення в будь-якій комірці, що використовується у формулі, або саму формулу, то ця формула одразу видає новий результат.

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

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

Для встановлення ручного режиму обчислень треба через команди “Сервіс Þ Параметри” в діалоговому вікні “Параметри” (Options) на вкладці “Обчислення” (Calculations) встановити прапорець опції “Вручну” (Manual).

Ручний режим передбачає обчислення за формулами після натиснення кнопки “ Обчислити” на вкладці “ Обчислення”. Після установки перемикача в положення вручну Excel автоматично встановлює прапорець “ Перерахунок” перед збереженням.

 

Циклічні посилання

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

Як правило, циклічні посилання є помилковими. Якщо з’являється повідомлення про виникнення циклічного посилання, у вікні повідомлення треба клацнути на кнопці “ОК” і за допомогою панелі інструментів “Циклічні посилання” (Circular Reference), яка після цього виникає, виявити помилку.

В деяких випадках можливе усвідомлене вживання циклічного посилання. Наприклад: чистий прибуток складає доход без витрат і без відрахування, яке складає певний процент від чистого прибутку. Для, все ж таки, розв’язання такого циклічного посилання треба в діалоговому вікні “Параметри” (Options) на вкладці “Обчислення” (Calculations) встановити прапорець опції “Ітерації” (Iteration). Тоді повідомлення про циклічні посилання з’являтись не буде і Excel продовжуватиме обчислення до тих пір, доки результати формул перестануть змінюватись, тобто послідовність проміжних результатів зійдеться до остаточного розв’язку.