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

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

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

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

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

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

Розглянемо простий приклад. Створимо невеликий набір даних, що складається з двох стовпців: «Співробітник» і «Оклад»:

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

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

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

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

Як бачимо, розібратися з тим, що таке відносна посилання в Excel, зовсім не важко.

Перейдемо до розгляду наступних видів посилань.

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

Абсолютне посилання в Excel - наступний поширений вид посилань. В цьому випадку при копіюванні формули фіксуються рядок і стовпець, на які йде посилання у формулі.

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

Введемо для прикладу ще один набір даних - місячна надбавка до окладу, однакова для всіх працівників:

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

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

Змінимо нашу формулу на наступну:

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

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

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

Розглянемо цей тип виразів.Змішана посилання - це посилання, у якій при копіюванні змінюється номер стовпчика при незмінному номері рядка або навпаки. При цьому знак долара варто відповідно перед номером рядка (А $ 1) або перед номером стовпця ($ А1) - тобто перед тим елементом, який змінюватися не буде.

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

Цікавий факт

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