Типи адресації

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

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

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

Вид адресації, яка використовується у посиланні для вказівки рядка, не залежить від виду адресації, використаної для вказівки стовпчика. Якщо для рядка та стовпчика використовуються різні способи адресації, одержимо змішане посилання. Наприклад, A$7, $A7. При копіюванні або переміщенні формули абсолютна частина посилання (із символом $) не зміниться, а відносна частина посилання може змінитися відповідно до правил зміни відносних посилань (з огляду на напрямок копіювання або переміщення).

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

За умовчанням 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). Можна задати об'ємне посилання і уведенням з клавіатури, проте цей шлях більш трудомісткий і тому може призвести до помилок. Об'ємні посилання не можуть бути вказані у формулах масиву та при застосуванні оператора перетину діапазонів.