Тема 4: Формули і функції Ехсеl.

1.              Використання формул

4.1. Використання формул

Програма Ехсеl здатна проводити обчислення, використовуючи формули і вбудовані функції.

У Ехсеl під формулою розуміють вираз, який складається з чисел, знаків арифметичних операцій (+, - *, /, ^, % ), операцій відносин (=, <, >, <=,,>=,<>), а також функцій.

Арифметичні операції і операції відносин виконуються над числовими операндами.

Над текстовими операндами виконується одна операція, яка до тексту першого операнда приєднує текст другого. Текстові константи у формулі обмежуються подвійними лапками. Наприклад:

= “White” & “House”            Результат: White House

=  А1&А2                                Результат: якщо клітина" А1 містить значення “White”, а А2 містить значення “House”, то “ White House”

= “Агата” & ” ”& “Крісті”  Результат: Агата  Крісті

В процесі обчислення по формулі в першу чергу виконуються операції в круглих дужках, зведення в ступінь (^), потім арифметичні операції ( *, /, +, -) за ними операції відносин.

Формули можуть містити також адреси елементів таблиці. Наприклад

=А1+В1+3

=В2/(С1+А2)

Формулу в комірці можна створити двома способами:

§   набрати її безпосередньо в комірці за допомогою клавіатури;

§  для введення адрес комірок  використовувати мишу, а для введення знаків операцій - клавіатуру.

У Ехсеl розрізняють абсолютну і відносну адресацію клітин.

 Абсолютна адреса клітини  не змінюється в процесі копіювання і переміщення формули. Відносна адреса при копіюванні і переміщенні змінюється. Форма написання відносної адреси співпадає із звичайним її записом (А1, В4 - відносні адреси). Розрізняють два типи абсолютної адреси: повну і часткову. Ознакою абсолютної повної адреси клітини є наявність символу $ перед ім'ям стовпця і номером рядка: $В$2; $А$5.

Часткова абсолютна адреса клітини містить символ $ або перед заголовком стовпця, або перед номером рядка: $В2; А$5. В процесі копіювання або переміщення формули, яка містить часткову абсолютну адресу клітини, змінюватиметься або заголовок стовпця або номер рядка.

Адреса змінюється за правилом відносної орієнтації клітини з формулою по відношенню до клітини з операндами формули.

Перетворити відносне посилання в абсолютне і навпаки можна за допомогою клавіші F4 при редагуванні формули.

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

Формула може містити адреси комірок з інших листів і навіть з інших книг. Наприклад =Лист2!А1+[Книга З]Лист1!$А$3 - формула для отримання суми значень клітини А1, яка знаходиться на листі Лист2 поточної книги і клітини АЗ листа Лист1 книги Книга З.

Більшість створюваних формул посилаються на інші комірки того ж листа. Ехсеl дозволяє вибирати значення з клітин розташованих на інших робочих листах і навіть в інших робочих книгах. В цьому випадку використовуються сполучені (що зв'язують) формули.

Для створення формули, що зв’язує (що використовує) клітинки іншого робочого листа необхідно:

1.      Ввести формулу в її звичайному вигляді.

2.      Перед адресою клітинки ввести ім'я робочого листа, на якому вона розташована, а потім знак, оклику (клацнути лівою кнопкою миші на корінці листа, що містить клітинку, на який посилається формула).

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

Для створення формули, що використовує комірку з іншої робочої книги необхідно:

1.      Відкрити потрібні робочі книги.

2.      Ввести формулу (знак =, при необхідності функції).

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

Наприклад: =’[Кошторис]Лист5’!B2*25 –добуток значення клітинки В2 на робочому листі Лист5, що входить в робочу книгу КОШТОРИС, і числа 25.

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