Використання формул. 
Розробка навчально-контролюючої програми з інформатики

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

+ - додавання, — - віднімання, * — множення, / — ділення, ^ — піднесення до степеня; операції відношень > — більше, < — менше, = — дорівнює, = — більше або дорівнює, — не дорівнює.

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

Посилання на клітинки. Як відомо, адреса клітинки включає назву колонки і номер рядка. Адреси клітинка (посилання на клітинки) можна використовувати у формулах. Можливі відносні, абсолютні і змішані посилання.

Посилання, яке включає назву колонки і номер рядка, є відносним. При копіюванні формули, а також редагуванні листа таке посилання буде модифікуватись. Наприклад, якщо в якійсь клітинці є формула, яка посилається на клітинку В4, то при вилученні, припустимо, рядка 2 і зсуву по таблиці вгору формула буде модифікуватись таким чином, щоб посилатись на клітинку ВЗ. В абсолютних посиланнях перед назвою колонки і номером рядка стоїть символ $. Такі посилання не модифікуються. У змішаних посиланнях абсолютною є назва колонки і відносною — номер рядка або навпаки (наприклад, $А1, А$ 1). У них модифікується тільки відносна частина посилання. У формулі можуть бути посилання на діапазон клітинка. Діапазон може бути тільки прямокутним. Вказуючи діапазон клітинка, задається адреса верхньої лівої клітки і через двокрапку — адреса нижньої правої клітки. Якщо у формулі є посилання на клітинки, які знаходяться на іншому листі, то посилання повинно містити ім'я листа, знак оклику та адресу клітинки, наприклад, лист1! А1.

Імена клітинка і діапазонів. Excel дає можливість назначати імена виділеним клітинкам і діапазонам. Для цього слід виконати команду Вставка/Имя/Присвоить. На екрані з’явиться діалогове вікно Присвоить имя, в полі Имя якого можна набрати ім'я. Ім'я повинно починатися з літери, може включати будь-які літери і цифри, а також знак підкреслення, знак питання, крапку. Адреса виділеної клітинки або діапазону з’явиться в полі Формула цього вікна. За замовчуванням створене ім'я діє по всій книзі. Якщо треба обмежити дію імені яким-небудь листом, то імені, яке набирається в полі Имя, повинно передувати ім'я листа і знак оклику, наприклад лист1!Ім'я₁. Для вилучення імені слід розкрити список Имя діалогового вікна Присвоить имя, виділити ім'я і натиснути кнопку Удалить.

Слід відзначити, що коли створюється нове ім'я, припустимо, для клітинки А1, а у формулах уже було посилання на цю клітинку, то Excel автоматично не модифікує формулу під це ім'я. Для встановлення імен у посиланнях на клітинки в діючих формулах виділеного діапазону слід виконати команду Вставка/Имя/Применить і в діалоговому вікні Применение имен клацнути мишею по іменах, які ви хочете використати.

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

Функції. Excel містить більше ніж 400 вбудованих функцій. Функція має ім'я і список аргументів у круглих дужках. Аргументами можуть бути числові та текстові константи, клітинки, діапазони клітинка. Деякі функції доступні лише тоді, коли відкрита відповідна надбудова. Для відкриття надбудови слід виконати команду Сервис/Настройки і у діалоговому вікні Настройки вказати надбудови, які треба відкрити.

Ввести функції у формулу можна вручну або з використанням майстра функцій. Для роботи з майстром функцій слід натиснути кнопку Мастер функций панелі інструментів Стандартная або виконати команду Вставка/Функции. При цьому відкривається діалогове вікно Мастер функций — шаг 1 из 2, в якому можна вибрати категорію функцій. При виборі категорії в поле Функции виводиться список функцій даної категорії. У цьому списку можна вибрати потрібну функцію. У рядку стану виводиться короткий опис функції.

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

Після натискування кнопки Готово формула вставляється в активну клітинку.

Обчислення формул. Коли встановлено автоматичний режим обчислень (цей режим встановлюється за замовчуванням), зміна вмісту клітинка веде до перерахунку формул, які використовують ці клітинки. Для встановлення ручного режиму обчислень слід у вкладці Вычисления діалогового вікна Параметры у блоці Производить перерасчет встановити режим Вручную. У цьому режимі Excel виводить слово Вычислить у рядок стану всякий раз, коли в листі з’являється необчислена формула. Для переобчислення формул слід натиснути клавішу [F9] (формули переобчисляться в усіх відкритих книгах) або [Shift-F9](формули переобчисляться тільки в активному листі). У полі клітинки може відображатись або формула, або значення. Вид відображення залежить від значення опції Формула вкладки Вид вікна діалогу Параметры. При встановленій опції в клітинці буде відображатись формула.

Якщо при обчисленні формули сталася помилка, то в клітинку виводиться повідомлення про помилку, яке починається із символу «#». Excel виводить такі повідомлення про помилки: # дел 0 — спроба поділити на нуль або на порожню клітинку; # имя? — формула використовує неіснуюче ім'я; # н/д — формула посилається на клітинку з невизначеними даними; # число ! — помилка у числі, число неможливо подати в Excel; # ссыл ! — формула посилається на неіснуючу клітинку; # знач ! — помилка при обчисленні функції.