Робота з функціями і формулами

Абсолютне посилання  може бути задане також шляхом уведення символу $ безпосередньо з клавіатури. Символ $ можна ввести з клавіатури і у режимі правки вмісту клітинки.

Як уже відзначалося, абсолютні та змішані посилання можна задавати і для діапазонів клітинок.

За замовчуванням Excel використовує формат посилання A1: стовпчики робочого аркуша позначаються літерами, а рядки – цифрами. Можна змінити формат, що використовується, задаючи стовпчики своїми номерами. Для цього у вікні діалогу «Параметры», що викликається командою «Сервис\Параметры…», перейдіть на вкладку «Общие» та у групі «Параметры» встановіть прапорець «Стиль ссылок R1C1». При використанні цього формату, наприклад, виразу R2C3 (R – рядок,  C – стовпчик) відповідає абсолютне посилання $B$3.

Для завдання відносного посилання у цьому форматі  після R і C зазначте потрібну кількість рядків і стовпчиків у квадратних дужках (вони визначають розміри зсуву від поточної клітинки). При цьому позитивне значення задає посилання на клітинку, розташовану на вказану кількість рядків (стовпчиків) нижче (праворуч) клітинки, що містить посилання. Наприклад, R[2]C[3] – посилання на клітинку, яка розташована на два рядки нижче та на три стовпчика праворуч клітинки, в якій записана формула. Від’ємні значення задають посилання на клітинку, яка розташована на вказану кількість рядків (стовпчиків) вище (ліворуч) клітинки, що містить посилання. Наприклад, R[–2]C[–1] – посилання на клітинку, яка розташована на два рядки вище та на один стовпчик ліворуч клітинки, що містить посилання.

Обраний формат посилань дійсний для усіх робочих аркушів поточної робочої книги.

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

Для завдання посилання  на клітинки іншого робочого аркуша поточної робочої книги простіше скористатися методом вказівки. Записавши частину формули аж до того місця, у якому повинне бути вказане посилання, виберіть потрібний робочий аркуш, клацнувши на його ярлику, та виділіть у аркуші потрібні клітинку або діапазон клітинок. Як звичайно, уведення усієї формули треба закінчити натисканням клавіші <Enter>. Excel сам підставить у необхідному вигляді посилання на клітинки іншого робочого аркуша. У формулі перед посиланням на клітинку буде відображене ім’я робочого аркуша, після якого вказаний знак оклику (наприклад, Лист2!$D$5). Задати посилання на клітинку іншого робочого аркуша можна також уведенням з клавіатури, проте цей спосіб частіше призводить до помилок. При завданні такого посилання шляхом уведення з клавіатури варто враховувати, що коли ім’я аркуша містить символи пропуску, то перед і після імені аркуша у посиланні потрібно вказати апостроф (‘). При завданні посилання методом вказівки Excel добавить апострофи, у разі потреби, автоматично.

При перейменуванні робочого аркуша його ім’я, що є складовою частиною посилання у формулі, автоматично змінюється. Переміщення клітинок, що впливають на інші робочі аркуші, призводить до автоматичного відновлення імені аркуша у посиланні формули. Видалення такого залежного аркуша призведе до виникнення помилки «#ССЫЛКА!».

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

Зовнішнє посилання  може бути задане аналогічно, методом  вказівки. Для цього необхідно  відкрити обидві робочі книги та задати підходяще розташування їхніх вікон  на екрані. Подальші дії не відрізняються  від розглянутих вище. В отриманому у такий спосіб посиланні буде вказане ім’я робочої книги, ім’я робочого аркуша та адреса клітинки. Шлях, ім’я робочої книги та ім’я аркуша будуть взяті в одинарні лапки (апострофи), а ім’я робочої книги записане ще й у квадратних дужках. Після імені робочого аркуша у посилання вставляється знак оклику. Наприклад, ‘C:\EXCEL\EXAMPLES\[SKLAD.XLS]Продажі_98’!$B$2. Якщо залежна та вихідна робочі книги збережені в одній папці, вказівка шляху не обов’язкова. У випадку перейменування вихідної робочої книги необхідно відчинити залежну робочу книгу. Тільки у цьому випадку зовнішнє посилання буде автоматично поновлене. Можна видалити зовнішнє посилання, замінивши формулу або відповідну частину формули, що містить зовнішнє посилання, результатом її обчислення. При необхідності обновити існуючі зв’язки вручну можна скористатися вікном діалогу «Связи», що активізується командою «Правка\Связи…», у якому перераховані всі зв’язки поточної робочої книги. Зміна зв’язку може бути виконана за допомогою кнопок «Изменить…» та діалогового вікна «Изменить связи», що дозволяє задати інший шлях до документа, з яким встановлено зв’язок. Видалення залежної робочої книги призведе до виникнення помилки «#ССЫЛКА!».

Зовнішнє посилання  можна задати також шляхом уведення з клавіатури. Проте цей шлях досить трудомісткий і тому часто призводить до помилок.

Існує ще одна можливість використання об’ємних (тривимірних) посилань, що дозволяє обробляти за один раз декілька діапазонів різноманітних робочих аркушів. В об’ємному посиланні можна зазначити діапазон клітинок з однаковою адресою декількох суміжних аркушів поточної робочої книги. Задати об’ємне посилання простіше усього методом вказівки. Записавши частину формули аж до того місця, де повинне бути вказане об’ємне посилання, виділіть потрібні аркуші у робочій книзі. Після цього виділіть потрібний діапазон у аркуші. Завершити уведення формули треба, як звичайно, натисканням клавіші <Enter>. У формулі перед посиланням на діапазон клітинок буде представлене посилання на діапазон виділених робочих аркушів, після яких вказаний знак оклику. Наприклад, =СУММ(Лист1:Лист3!B5:F5). Можна задати об’ємне посилання і уведенням з клавіатури, проте цей шлях більш трудомісткий і тому може призвести до помилок. Об’ємні посилання не можуть бути вказані у формулах масиву та при застосуванні оператора перетину діапазонів.

Імена у формулах

Будь-яким клітинкам або  діапазонам клітинок можуть бути присвоєні  імена, які надалі можна вказувати  замість адрес клітинок у посиланнях як у формулах та функціях, так і  у вікнах діалогу. Крім того імена можна використовувати для швидкого переходу до необхідних діапазонів. Можна присвоїти імена і часто з уживаним формулам, що дозволить для вставки формули зазначити її ім’я замість уведення заново самої формули.

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

Ім’я може мати довжину не більш 255 символів. Ім’я може починатися з літери, символу підкреслення (_) або оберненої похилої риски (\). В іменах можна використовувати також цифри, такі спеціальні символи, як крапка, символ підкреслення, обернена похила риска та знак питання. При цьому варто враховувати, що ім’я не повинно збігатися з форматом посилання (наприклад, A1 або W55) та містити пропуски. Малі та великі літери в іменах не розрізняються.

Створення імен діапазонів

Ім’я діапазону може бути задане безпосередньо у полі імені рядка формул. Для цього виділіть потрібний діапазон клітинок, перемістіть курсор миші у поле імені та клацніть нею, замініть представлену тут адресу відповідним ім’ям і натисніть клавішу <Enter>. Присвоєне ім’я буде відображене у полі імен і додане до списку імен.

Інший спосіб іменування діапазону: виділіть потрібний діапазон клітинок та виконайте команду «Вставка\Имя\Присвоить…», введіть відповідне ім’я у поле «Имя» вікна діалогу «Присвоение имени». Вказане вікно можна також активізувати сполученням клавіш <Ctrl+F3>.

У полі «Формула» буде представлене посилання на виділений діапазон клітинок. Кнопка «Добавить» дозволяє поповнити список імен. Задайте наступне ім’я та, помістивши курсор у поле «Формула», зазначте потрібний діапазон клітинок шляхом виділення за допомогою миші або введіть посилання з клавіатури. При необхідності можна також видалити непотрібне ім’я, вказавши його та скориставшись кнопкою «Удалить». Вказане вікно діалогу дозволяє також змінити ім’я або діапазон клітинок. Завершити роботу у вікні діалогу можна натисканням кнопок «ОК» або «Закрыть».

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

Текстові заголовки  діапазонів можуть бути використані  як імена, що значно спрощує роботу з таблицями. Виділивши діапазон клітинок, що містить і клітинки з іменами, виконайте «Вставка\Имя\Создать…», або скористайтесь сполученням клавіш <Ctrl+Shift+F3>. У вікні діалогу, що з’явилося, за допомогою параметрів зазначте, де у виділеному діапазоні розташовані клітинки, текст яких повинний використовуватися як імена.

Присвоєння імені формулі

При присвоєнні імені  формулі сама формула не зберігається у клітинці робочого аркуша, але може бути використана у клітинках робочої книги. Виконавши команду «Вставка\Имя\Присвоить…», у вікні діалогу «Присвоение имени», що з’явилося, введіть ім’я для формули. Правила завдання імен для формул ті ж, що і для імен діапазонів клітинок. У полі «Формула» задайте формулу та натисніть кнопку «Добавить». Іменована формула може бути використана у робочій книзі будь-яке число раз. Для цього достатньо зазначити ім’я формули у потрібній клітинці. За допомогою цього способу можна також присвоїти імена визначеним константам і надалі включати їх у формули шляхом вказування відповідного імені.

Застосування  імен

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

Ім’я можна вставити також, обравши його у вікні діалогу «Вставка имени», що активізується командою «Вставка\Имя\Вставить…» або клавішею <F3>. У цьому вікні діалогу можна вибрати зі списку також ім’я формули. Кнопка «Все имена» дозволяє помістити у робочий аркуш список усіх визначених імен разом із відповідними їм посиланнями.

Якщо формули робочого аркуша містять посилання на діапазони клітинок, яким надалі були присвоєні імена, то після такого присвоєння посилання можуть бути замінені відповідними іменами. При цьому заміна торкнеться виділеної області або всього робочого аркуша, якщо виділена тільки одна клітинка. Виконання команди «Вставка\Имя\Применить…» активізує вікно діалогу «Применение имен».

Виберіть ім’я, що повинно замінити відповідні посилання у формулах, і натисніть кнопку «ОК». При цьому відносні посилання фактично будуть перетворені в абсолютні. Щоб уникнути подібної ситуації, слід виключити параметр «Игнорировать тип ссылки», внаслідок чого буде встановлений режим заміни тільки абсолютних посилань.

Копіювання  формул

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

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

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

При копіюванні формул працюють також команди «Копировать», «Вставить».

Помилки та їх пошук у формулах

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

Формула, що містить  посилання на клітинку зі значенням  помилки, також повертає значення помилки.

Для пошуку помилок Excel надає у розпорядження користувача можливості для відслідковування залежності, шляхом графічного представлення зв’язку між впливаючими та залежними клітинками. Стрілки у робочому аркуші, проте, будуть відображені тільки у випадку, якщо включений параметр «отображать» у полі «Объекты» на вкладці «Вид» вікна діалогу «Параметры» (викликається командою «Сервис\Параметры»).