Огляд формул у програмі Excel

Формула також може містити всі або деякі з таких елементів: функції, посилання, оператори та константи.

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

1. Функції. Функція PI() повертає значення числа пі: 3,142…

2. Посилання. A2 повертає значення клітинки A2.

3. Константи. Це числа або текстові значення, введені безпосередньо у формулу, наприклад 2.

4. Оператори. Оператор ^ (кришка) підносить число до степеня, а оператор * (зірочка) перемножує числа.

Константа – це значення, яке не обчислюється та завжди залишається однаковим. Наприклад, дата 09.10.2008, число 210 або текст "Квартальний прибуток" – це константи. Вираз або значення, отримане в результаті обчислення виразу, – це не константи. Якщо у формулі замість посилань на клітинки використовуються константи (наприклад, =30+70+110), то результат зміниться, лише якщо змінити саму формулу. Зазвичай краще зберігати константи в окремих клітинках, де їх можна легко змінити за потреби, а у формулах використовувати посилання на ці клітинки.

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

• Стиль посилань A1

  За замовчуванням у програмі Excel використовується стиль посилань A1, який позначає стовпці буквами (від A до XFD, загалом 16 384 стовпці), а рядки – числами (від 1 до 1 048 576). Ці букви й числа називаються заголовками рядків і стовпців. Щоб створити посилання на клітинку, введіть букву стовпця, а після неї – номер рядка. Наприклад, "B2" посилається на клітинку на перетині стовпця B та рядка 2.

  Об’єкт посилання	Логічне значення
  Клітинка у стовпці A й рядку 10	A10
  Діапазон клітинок у стовпці A й рядках від 10 до 20	A10:A20
  Діапазон клітинок у рядку 15 і стовпцях від B до E	B15:E15
  Усі клітинки в рядку 5	5:5
  Усі клітинки в рядках від 5 до 10	5:10
  Усі клітинки у стовпці H	H:H
  Усі клітинки у стовпцях від H до J	H:J
  Діапазон клітинок у стовпцях від A до E й рядках від 10 до 20	A10:E20

• Створення посилання на клітинку чи діапазон клітинок з іншого аркуша тієї самої книги

  У наведеному нижче прикладі функція AVERAGE обчислює середнє значення в діапазоні B1:B10 на аркуші "Маркетинг" у тій самій книзі.

  

  1. Посилається на аркуш ''Маркетинг''.

  2. Посилається на діапазон клітинок від B1 до B10.

  3. Знак оклику (!) відділяє посилання на аркуш від посилання на діапазон клітинок.

  : Якщо аркуш, на який посилається посилання, містить пробіли або числа, потрібно додати апострофи (') до та після імені аркуша, наприклад ='123'! A1 або ='Дохід за січень'! A1.

• Різниця між абсолютними, відносними та мішаними посиланнями

  1. Відносні посилання    Відносні посилання на клітинки у формулі, наприклад A1, базуються на відносній позиції клітинки, що містить формулу, і клітинки, на яку вказує посилання. Якщо змінюється положення клітинки, що містить формулу, змінюється й посилання. Якщо скопіювати або заповнити формулу в рядку або у стовпці, то посилання зміниться автоматично. За промовчанням нові формули використовують відносні посилання. Наприклад, якщо заповнити або скопіювати відносне посилання із клітинки B2 до клітинки B3, воно автоматично зміниться з =A1 на =A2.

     Скопійована формула з відносним посиланням   

     

  2. Абсолютні посилання    Абсолютне посилання на клітинку у формулі, наприклад $A$1, завжди посилається на клітинку в певному розташуванні. Якщо змінюється положення клітинки, яка містить формулу, абсолютне посилання залишається без змін. Якщо скопіювати або заповнити формулу в рядку або стовпці, то абсолютне посилання не змінюється. За промовчанням нові формули використовують відносні посилання, тому може бути потрібно змінити їх на абсолютні. Наприклад, якщо заповнити або скопіювати абсолютне посилання із клітинки B2 до клітинки B3, воно буде однаковим в обох клітинках: =$A$1.

     Скопійована формула з абсолютним посиланням   

     

  3. Мішані посилання    Мішане посилання має або абсолютний стовпець і відносний рядок, або абсолютний рядок і відносний стовпець. Абсолютне посилання на стовпець має вигляд $A1, $B1 тощо. Абсолютне посилання на рядок має вигляд A$1, B$1 тощо. Якщо змінюється положення клітинки, яка містить формулу, то відносне посилання змінюється, а абсолютне – ні. Якщо скопіювати або заповнити формулу в рядку або стовпці, то відносне посилання автоматично змінюється, а абсолютне посилання не змінюється. Наприклад, якщо заповнити або скопіювати мішане посилання із клітинки A2 до B3, воно змінюється з =A$1 на =B$1.

     Скопійована формула з мішаним посиланням   

     

• Стиль тривимірних посилань

  Зручне посилання на кілька аркушів    Якщо потрібно проаналізувати дані в тій самій клітинці або тому самому діапазоні клітинок у книзі, використовуйте тривимірне посилання. Тривимірне посилання містить посилання на клітинку або діапазон, перед яким указано діапазон імен аркушів. Програма Excel використовує всі збережені аркуші між першим і останнім іменем у посиланні. Наприклад, =SUM(Аркуш2:Аркуш13!B5) додає всі значення, які містяться у клітинці B5 на всіх аркушах між аркушами 2 та 13 включно.

  • Тривимірні посилання можна використовувати для посилання на клітинки на інших аркушах, визначення імен і створення формул за допомогою таких функцій: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA і VARPA.

  • Тривимірні посилання не можна використовувати у формулах масиву.

  • Тривимірні посилання не можна використовувати з оператор перетину (один пробіл) або у формулах, які використовують неявний перетин.

  Що відбувається під час переміщення, копіювання, вставлення або видалення аркушів    Наведені нижче приклади пояснюють, що відбувається під час переміщення, копіювання, вставлення або видалення аркушів, які входять до тривимірного посилання. У прикладах використовується формула =SUM(Аркуш2:Аркуш6!A2:A5) для додавання клітинок від A2 до A5 на аркушах із 2 по 6.

  • Вставлення або копіювання    У разі вставлення або копіювання аркушів діапазону між Аркушем2 й Аркушем6 (у цьому прикладі це кінцеві точки), до обчислення долучаються всі значення у клітинках від A2 до A5 із доданих аркушів.

  • Видалення     У разі видалення аркушів діапазону між Аркушем2 й Аркушем6 значення видалених аркушів буде видалено з обчислення.

  • Переміщення    У разі переміщення аркушів із діапазону між Аркушем2 й Аркушем6 до розташування поза діапазоном аркушів, на який посилається формула, значення переміщених аркушів буде видалено з обчислення.

  • Переміщення кінцевої точки    У разі переміщення Аркуша2 або Аркуша6 до іншого розташування в тій самій книзі програма Excel змінить обчислення відповідно до нового діапазону аркушів між ними.

  • Видалення кінцевої точки    У разі видалення Аркуша2 або Аркуша6 програма Excel змінить обчислення відповідно до діапазону аркушів між ними.

• Стиль посилань R1C1

  Також можна використовувати стиль посилань, у якому на аркуші пронумеровано як рядки, так і стовпці. Посилання в стилі R1C1 корисні для обчислення позицій рядків і стовпців у макросах. Якщо використовується стиль R1C1, програма Excel позначає розташування клітинки буквою "R", після якої йде номер рядка, і буквою "C", після якої йде номер стовпця.

  Посилання	Значення
  R[-2]C	відносне посилання на клітинку на два рядки вище в тому самому стовпці
  R[2]C[2]	Відносне посилання на клітинку на два рядки нижче й два стовпці праворуч
  R2C2	Абсолютне посилання на клітинку в другому рядку та в другому стовпці
  R[-1]	Відносне посилання на весь рядок над активною клітинкою
  R	Абсолютне посилання на поточний рядок

  Під час записування макросу програма Excel записує деякі команди за допомогою стилю посилань R1C1. Наприклад, якщо записати команду, наприклад натиснути кнопку "Автосума ", щоб вставити формулу, яка додає діапазон клітинок, програма Excel записує формулу за допомогою стилю R1C1, а не стилю A1, посилань.

  Щоб увімкнути або вимкнути посилання у стилі R1C1, у діалоговому вікні Параметри в категорії Формули в розділі Робота з формулами встановіть або зніміть прапорець Стиль посилань R1C1. Щоб відобразити це діалогове вікно, перейдіть на вкладку Файл .

  На початок сторінки