Абсолютне посилання в Excel - опис, приклади.

У будь-якому, навіть базовому пакеті "Майкрософт Офіс" міститься потужний табличний редактор Excel ("Ексель"). Список доступних в ньому функцій справді вражає: починаючи від можливості сортування і фільтрації даних і закінчуючи можливістю побудови на їх основі зведених діаграм. І практично неможливо уявити жоден зберігається в редакторі набір даних без обчислюваних за допомогою формул рядків і стовпців.

Формули в "Ексель"

Формула – це команда, що містить вказівку, які дії зробити з тієї чи іншої осередком. І коли користувач стикається з необхідністю скопіювати, «протягнути» формулу на весь рядок або стовпець, розуміє, що в деяких випадках частина формули або формула цілком повинна залишитися незмінною. Так користувач дізнається, що є в Excel абсолютні і відносні посилання. Розглянемо ці поняття більш детально.

Посилання відносна

Запам'ятати, що таке відносне посилання в Excel, простіше простого. Цей вид посилання вибирається за замовчуванням і змінює своє значення при протягуванні (копіювання) формули у суміжні комірки незалежно від того, виконується копіювання вздовж стовпців або рядків. Розглянемо простий приклад. Створимо невеликий набір даних, що складається з двох стовпців: «Співробітник» і «Оклад»:





А



У



1



Співробітник



Оклад



2



Абрамов А. А.



4100



3



Демидова М. П.



3750



4



Закірова Е. М.



8410



5



Ігумнова Т. Л.



3750



6



Ітан П. Н.



4100



7



Кремльов О. П.



9200

У параметрах "Екселю" задамо стиль посилань А1 - така адресація зручна і зрозуміла більшості користувачів. Тут А, В, С – імена стовпців, а рядки пронумеровані. Таким чином, у клітинки з даними «Закірова Е. М.» адреса - А4. Це невеликий відступ знадобиться, коли станемо розбиратися з посиланнями у формулах.


Тепер уявімо, що ми хочемо розрахувати на основі даних по окладу заробітну плату кожного працівника. Поставимо в комірку С2 знак рівності і введемо наступну формулу (грунтуючись на відомостях, що оклад становить 40 % від зарплати): =B2*100/40. Тут В2 – це оклад першого співробітника в таблиці. Натиснемо Enter і підведемо покажчик миші до нижнього правого краю комірки С2 чекаючи, поки курсор не прийме форму тонкого чорного хрестика. Утримуючи натиснутою ліву клавішу миші, простягнемо формулу вниз до кінця таблиці, тобто до комірки С7 (можна замінити дану дію подвійним кліком миші по правому нижньому краю комірки). Стовпець автоматично заповниться даними:

Співробітник



Оклад



Зарплата



Абрамов А. А.



4100



10250



Демидова М. П.



3750



9375



Закірова Е. М.



8410



21025



Ігумнова Т. Л.



3750



9375



Ітан П. Н.



4100



10250



Кремльов О. П.



9200



23000

Формули даних комірок будуть наступними:

Зарплата



=B2*100/40



=B3*100/40



=B4*100/40



=B5*100/40



=B6*100/40



=B7*100/40

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

Посилання абсолютна

Абсолютна посилання в Excel – наступний поширений вид посилань. У цьому випадку при копіюванні формули фіксуються рядок і стовпець, на які йде посилання у формулі. Звичайно, сама по собі абсолютне посилання поодинці не використовується, адже копіювати її особливого сенсу немає. Тому даний тип поширений у комбінованих формулах, де частина їх – абсолютні посилання, інші є відносними. Введемо для прикладу ще один набір даних – місячна надбавка до окладу, однакова для всіх співробітників:





F



G



2



Місяць



Надбавка



3



Січень



370



4









Відповідно, треба змінити і клітинку з розрахунком зарплати С2 тепер вона буде містити наступну формулу:

Зарплата



=(B2+G3)*100/40

Коли ми натиснемо Enter, то побачимо, що в даній комірці зарплата правильно пересчиталась. А ось коли ми простягнемо формулу на всіх співробітників, то у них зарплата не перерахується, адже використовується відносна посилання спробувала взяти значення з G4G8 де абсолютно нічого немає. Щоб уникнути подібної ситуації, необхідно, щоб використовувалася абсолютне посилання в Excel. Щоб зафіксувати стовпець або рядок, які змінюватися при копіюванні формули не мають, необхідно поставити відповідно біля імені стовпця та номера рядка знак долара ($). Змінимо нашу формулу на наступну:

Зарплата



=(B2+$G$3)*100/40

І коли ми її скопіюємо, вся зарплата співробітників перерахується:

Співробітник



Оклад



Зарплата







Абрамов А. А.



4100



11175



=(B2+$G$3)*100/40



Демидова М. П.



3750



10300



=(B3+$G$3)*100/40



Закірова Е. М.



8410



21950



=(B4+$G$3)*100/40



Ігумнова Т. Л.



3750



10300



=(B5+$G$3)*100/40



Ітан П. Н.



4100



11175



=(B6+$G$3)*100/40



Кремльов О. П.



9200



23925



=(B7+$G$3)*100/40

Поряд з поняттям "абсолютне посилання" Excel завжди йде посилання поняття змішаної.

Посилання змішана

Розглянемо цей тип виразів. Змішана посилання – це посилання, у якої при копіюванні змінюється номер стовпця при незмінному номер рядка або навпаки. При цьому знак долара відповідно коштує перед номером рядка, А$1) або перед номером стовпця ($А1) – тобто перед тим елементом, який змінюватися не буде.
Посилання змішана застосовується набагато частіше, ніж істинна абсолютна посилання. Наприклад, навіть у простому попередньому прикладі ми цілком могли б замінити формулу =(B2+$G$3)*100/40 на =(B2+G$3)*100/40 і отримати той самий результат, адже ми виконували копіювання формули по вертикалі, і номер стовпця в будь-якому випадку залишився б незмінним. І це не кажучи вже про ситуації, коли дійсно треба зафіксувати тільки номер рядка або стовпця, а решту залишити доступними для зміни.

Цікавий факт

Цікаво буде дізнатися, що абсолютна посилання в Excel може бути задана не тільки самостійним зазначенням знаку долара перед номером рядка та/або стовпця. "Ексель" дозволяє шляхом натискання клавіші F4 після зазначення адреси комірки вибрати вид посилань – при першому натисканні посилання з відносною зміниться на абсолютну, при другому – на змішану з фіксованим номером рядка, при третьому – на змішану з фіксованим номером стовпця, ну а при наступному натисканні посилання знову прийме вид відносної. Змінювати вид посилання в Excel таким чином дуже зручно, адже при цьому немає необхідності вдаватися до зміни розкладки на клавіатурі.