Копіювання формул. Відносні й абсолютні посилання

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

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

У ході копіювання формул виникає необхідність керувати зміною адрес комірок або посилань, тому що вони є відносні. Для цього перед символами адреси комірки під час редагування встановлюють символи “ $ ” (вручну або за допомогою клавіші F4) і тоді під час копіювання змінюються тільки ті частини адреси комірки, перед якими немає символу “ $ ”. Якщо перед всіма частинами адреси комірки поставити символ “ $ ”, то в разі копіювання формули адреса не зміниться, таке посилання називабть абсолютним. Посилання, зафіксовані частково, називаються змішаними. Абсолютні адреси під час копіювання формул не змінюються, а у відносних адресах відбувається зсув на величину переносу. Якщо застосувати текстове ім’я комірки у формулі, то воно буде абсолютним посиланням на неї, тобто замінить абсолютну адресу і не потребуватиме фіксації.

Приклад, у якому наведено результати використання неправильного (незафіксованого, у комірці С9) і правильного (фіксованого, у комірці D9) посилань після копіювання формули в ході обчислення відсоткової частки показано на рис. 13.

Рис. 13. Приклад неправильної і правильної фіксації посилань

Наприклад, для створення таблиці множення (за допомогою лише однієї формули), у комірку B2 необхідно ввести формулу =$A2*B$1, тобто зафіксувати перший стовпець A і перший рядок таблиці 1 (рис. 14), а потім скопіювати формулу до всіх комірок таблиці перетягуванням маркера автозаповнення спочатку до кінця стовпця В, а потім, не знімаючи виділення, до кінця таблиці.

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

Застосування функцій

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

Аргументи функції записують у круглих дужках відразу за назвою функції та відокремлюють один від одного символом «;». Як аргументи можна використовувати числа, текст, логічні значення, масиви або посилання. Аргументи можуть бути як константами, так і формулами. У свою чергу, ці формули можуть містити інші функції. Функції, що є аргументами інших функцій, називають вкладеними (складними). В Excel 2010 кількість рівнів вкладення функцій збільшена з 7 до 64, а кількість аргументів функції збільшена з 30 до 255. Деякі функції мають необов'язкові аргументи, які можна не задавати під час введення аргументів і обчислення значення функції.

Найпростішою і часто застосовуваною є функція підсумовування (автосума). Щоб підсумувати числа за стовпцем або рядком, достатньо натиснути кнопку
Автосума в групі Редагування вкладки стрічки Головна, при цьому з'являється пунктирна рамка, що показує комірки, значення яких будуть складені. Для зміни області обчислення суми достатньо виділити потрібні комірки і натиснути клавішу Enter. Найшвидшим способом застосування автосуми є такий: виділити діапазон підсумовуваних комірок, включаючи комірки, у які буде записано результат, і натиснути кнопку автосуми.

Excel містить більше 400 вбудованих функцій, тому безпосередньо вводити з клавіатури у формулу назви функцій і значення аргументів не завжди зручно. В Excel є спеціальний засіб для роботи з функціями – Майстер функцій. У процесі роботи з цим засобом спочатку пропонується вибрати потрібну функцію зі списку категорій, а потім у вікні діалогу ввести вхідні значення. Майстер функцій в
Excel 2010 викликається натисненням на кнопку Майстер функцій , яка розташована в рядку формул. Інший спосіб викликати Майстра функцій – вибрати Інші функції зі списку, який відкривається на позначці автосуми (рис. 15).

Рис. 15. Вибір Майстра функцій

При цьому з'явиться вікно вибору функцій (рис. 16). Усі функції у вікні поділені на категорії. Найбільш корисна є категорія 10 нещодавно використаних - це ті функції, які останніми застосовував користувач. Крім того, є інші категорії функцій: статистичні, текстові, математичні, логічні та ін. Для створення складеної функції необхідно під час редагування формули перейти в адресну частину рядка формул, у якій при цьому відкривається список 10 нещодавно використаних функцій, останній елемент цього списку Інші… відкриває вікно Майстра для повторного вибору функції.

 

Рис. 16. Вікно Майстра функцій

Наприклад, запис формули складеної функції для обчислення значення виразу за заданих аргументів має такий вигляд (рис. 17):
КОРІНЬ(СУМ(СТЕПІНЬ(B1;2);СТЕПІНЬ(B2;2))). Однак не завжди зручно застосовувати складені функції, в даному прикладі для складення і піднесення до степеню можна використати позначення арифметичних дій і тоді формула набуде спрощеного вигляду: КОРІНЬ(B1^2+B2^2).

Рис. 17. Обчислення складеної функції

Серед безлічі функцій Excel певний інтерес для вирішення економічних завдань становить функція ЯКЩО, яка належить до категорії логічних і дозволяє виконати обчислення з перевіркою умови. Синтаксис функції:

ЯКЩО(логічний_вираз;значення_якщо_істина;значення_якщо_неправда), де

логічний_вираз - це умова, яку перевіряють - будь-яке значення або вираз, що набуває значення ІСТИНА або НЕПРАВДА;

значення_якщо_істина - це значення, яке повертається, якщо логічний_вираз дорівнює ІСТИНА, тобто якщо умова виконана;

значення_якщо_неправда - це значення, яке повертається, якщо логічний_вираз дорівнює НЕПРАВДА, тобто якщо умова не виконана.

Приклад – обчислення комісійних для менеджерів із продажів, виходячи з умови, що звичайні комісійні становлять 5,5% від обсягу продажів. Якщо обсяг продажів перевищує $150 000, то виплачують комісійні за преміальною
ставкою 7%.

Розв’язання прикладу обчислення комісійних за допомогою функції ЯКЩО наведено на рис. 18.

Рис. 18. Застосування логічної функції ЯКЩО

Для перевірки декількох умов потрібно застосувати вкладення функції ЯКЩО. Слід нагадати, що в Excel 2010 допускається до 64 вкладень функцій.

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

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

Імена деяких статистичних функцій приведено у відповідність до термінів, використовуваних науковим співтовариством, а також до інших імен функцій Excel. Нові імена більш точно описують призначення відповідних функцій. Книги, створені в попередніх версіях Excel, будуть продовжувати працювати, незважаючи на ці зміни, оскільки вихідні функції, як і раніше, доступні в категорії Сумісність.