Вернуться назад
Распечатать
Абсолютне посилання в 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, зовсім не важко. Перейдемо до розгляду наступних видів посилань.
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 завжди йде посилання поняття змішаної.
Посилання змішана застосовується набагато частіше, ніж істинна абсолютна посилання. Наприклад, навіть у простому попередньому прикладі ми цілком могли б замінити формулу =(B2+$G$3)*100/40 на =(B2+G$3)*100/40 і отримати той самий результат, адже ми виконували копіювання формули по вертикалі, і номер стовпця в будь-якому випадку залишився б незмінним. І це не кажучи вже про ситуації, коли дійсно треба зафіксувати тільки номер рядка або стовпця, а решту залишити доступними для зміни.
Формули в "Ексель"
Формула – це команда, що містить вказівку, які дії зробити з тієї чи іншої осередком. І коли користувач стикається з необхідністю скопіювати, «протягнути» формулу на весь рядок або стовпець, розуміє, що в деяких випадках частина формули або формула цілком повинна залишитися незмінною. Так користувач дізнається, що є в 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) – тобто перед тим елементом, який змінюватися не буде.