Способи абсолютної адресації в Microsoft Excel

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

Застосування абсолютної адресації

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

В Excel існує два способи задати фіксовану адресацію: шляхом формування абсолютної посилання і за допомогою функції ДВССИЛ. Давайте розглянемо кожен із зазначених способів докладно.

Спосіб 1: абсолютне посилання

Безумовно, найвідомішим і часто вживаним способом створити абсолютну адресацію є застосування абсолютних посилань. Абсолютні посилання мають відмінність не тільки функціональне, а й синтаксичну. Відносний адреса має такий синтаксис:

=A1

У фіксованого адреси перед значенням координат встановлюється знак долара:

=$A$1

Знак долара можна ввести вручну. Для цього потрібно встановити курсор перед першим значенням координат адреси (по горизонталі), що знаходиться в осередку або в рядку формул. Далі, в англомовній розкладці клавіатури слід клікнути по клавіші «4» у верхньому регістрі (з затиснутою клавішею «Shift»). Саме там розташований символ долара. Потім потрібно ту ж процедуру проробити і з координатами по вертикалі.

Існує і більш швидкий спосіб. Потрібно встановити курсор в осередок, в якій знаходиться адресу, і клацнути по функціональній клавіші F4. Після цього знак долара моментально з'явиться одночасно перед координатами по горизонталі і вертикалі даної адреси.

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

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

1. Отже, в перший осередок шпальти «Заробітна плата» вводимо формулу множення ставки відповідного працівника на коефіцієнт. У нашому випадку ця формула має такий вигляд:

   =C4*G3

2. Щоб розрахувати готовий результат, клацаємо по клавіші Enter на клавіатурі. Підсумок виводиться в клітинку, яка містить формулу.
3. Ми розрахували значення зарплати для першого співробітника. Тепер нам потрібно це зробити для всіх інших рядків. Звичайно, операцію можна записати в кожну осередок шпальти «Заробітна плата» вручну, вводячи аналогічну формулу з поправкою на зміщення, але у нас стоїть завдання, як можна швидше виконати обчислення, а ручне введення займе багато часу. Та й навіщо витрачати зусилля на ручне введення, якщо формулу можна просто скопіювати в інші осередки?

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

4. Але, як бачимо, замість коректного розрахунку заробітної плати для інших співробітників, ми отримали одні нулі.
5. Дивимося, в чому причина такого результату. Для цього виділяємо другий осередок в стовпці «Заробітна плата». У рядку формул відображається відповідне даному осередку вираження. Як бачимо, перший множник (C5) відповідає ставці того працівника, зарплату якого ми розраховуємо. Зсув координат в порівнянні з попередньою осередком сталося через властивості відносності. Втім, в конкретно даному випадку це нам і потрібно. Завдяки цьому першим множником стала ставка саме потрібного нам працівника. Але зсув координат сталося і з другим множником. І тепер його адресу посилається нема на коефіцієнт (1,28), а на порожню комірку нижче.

   Саме це і послужило причиною того, що розрахунок заробітної плати для наступних співробітників зі списку вийшов некоректним.

6. Для виправлення ситуації нам потрібно змінити адресацію другого множника з відносною на фіксовану. Для цього повертаємося до першої осередку шпальти «Заробітна плата», виділивши її. Далі подорожуємо у рядок формул, де відобразилося потрібне нам вираз. Виділяємо курсором другий множник (G3) і тиснемо на функціональну клавішу на клавіатурі.
7. Як бачимо, близько координат другого множника з'явився знак долара, а це, як ми пам'ятаємо, є атрибутом абсолютної адресації. Щоб вивести результат на екран тиснемо на клавішу Enter.
8. Тепер, як і раніше викликаємо маркер заповнення, встановивши курсор в правий нижній кут першого елемента стовпця «Заробітна плата». Затискаємо ліву кнопку миші і тягнемо його вниз.
9. Як бачимо, в даному випадку розрахунок був проведений вірно і сума заробітної плати для всіх працівників підприємства розрахована коректно.
10. Перевіримо, як була скопійована формула. Для цього виділяємо другий елемент стовпця «Заробітна плата». Дивимося на вираз, розташоване в рядку формул. Як бачимо, координати першого множника (C5), який як і раніше є відносним, зрушили в порівнянні з попередньою осередком на один пункт вниз по вертикалі. Зате другий множник ($ G $ 3), адресацію в якому ми зробили фіксованою, залишився незмінним.

У Ексель також застосовується, так звана змішана адресація. В цьому випадку в адресі елемента фіксується або стовпець, або рядок. Досягається це в такий спосіб, що знак долара ставиться тільки перед одним з координат адреси. Ось приклад типової змішаної посилання:

=A$1

Ця електронна адреса була теж вважається змішаним:

=$A1

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

Подивимося, як таку змішану посилання можна застосувати на практиці на прикладі все тієї ж таблиці заробітної плати співробітників підприємства.

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

   Виділяємо перший елемент стовпця «Заробітна плата» і в рядку формул виконуємо вищевказану маніпуляцію. Отримуємо формулу такого вигляду:

   =C4*G$3

   Як бачимо, фіксована адресація в другому множнику застосовується тільки по відношенню до координат рядка. Для виведення результату в комірку клацаємо по кнопці Enter.

2. Після цього за допомогою маркера заповнення копіюємо цю формулу на діапазон комірок, який розташований нижче. Як бачимо, розрахунок заробітної плати по всім співробітникам виконаний коректно.
3. Дивимося, як відображається скопійована формула в другій осередку шпальти, над яким ми виконували маніпуляцію. Як можна спостерігати в рядку формул, після виділення даного елемента листа, незважаючи на те, що абсолютну адресацію у другого множника мали тільки координати рядків, зсув координат стовпчика не відбулося. Це пов'язано з тим, що ми виконували копіювання не по горизонталі, а по вертикалі. Якби ми виконали копіювання по горизонталі, то в аналогічному випадку, навпаки, довелося б робити фіксовану адресацію координат стовпців, а для рядків ця процедура була б необов'язковою.

урок: Абсолютні і відносні посилання в Ексель

Спосіб 2: функція ДВССИЛ

Другим способом організувати абсолютну адресацію в таблиці Excel є застосування оператора ДВССИЛ. Зазначена функція належить до групи вбудованих операторів «Посилання та масиви». Її завданням є формування посилання на зазначену осередок з висновком результату в той елемент листа, в якому знаходиться сам оператор. При цьому посилання прикріплюється до координат ще міцніше, ніж при використанні знака долара. Тому іноді прийнято називати посилання з використанням ДВССИЛ «суперабсолютнимі». Цей оператор має наступний синтаксис:

=ДВССЫЛ(ссылка_на_ячейку;[a1])

Функція має в наявності два аргументи, перший з яких має обов'язковий статус, а другий - ні.

Аргумент «Посилання на осередок» є посиланням на елемент листа Excel в текстовому вигляді. Тобто, це звичайна посилання, але укладена в лапки. Саме це і дозволяє забезпечити властивості абсолютної адресації.

Аргумент «a1» - необов'язковий і використовується в рідкісних випадках. Його застосування необхідно тільки тоді, коли користувач вибирає альтернативний варіант адресації, а не звичайне використання координат по типу «A1» (стовпці мають буквене позначення, а рядки - цифрове). Альтернативний варіант передбачає використання стилю «R1C1», в якому стовпці, як і рядки, позначаються цифрами. Переключитися в даний режим роботи можна через вікно параметрів Excel. Тоді, застосовуючи оператор ДВССИЛ, як аргумент «a1» слід вказати значення «БРЕХНЯ». Якщо ви працює в звичайному режимі відображення посилань, як і більшість інших користувачів, то в якості аргументу «a1» можна вказати значення «ІСТИНА». Втім, дане значення мається на увазі за замовчуванням, тому набагато простіше взагалі в даному випадку аргумент «a1» не вказувати.

Погляньмо, як буде працювати абсолютна адресація, організована за допомогою функції ДВССИЛ, на прикладі нашої таблиці заробітної плати.

1. Виробляємо виділення першого елемента стовпця «Заробітна плата». Ставимо знак «=». Як пам'ятаємо, перший множник у зазначеній формулі обчислення зарплати повинен бути представлений відносною адресою. Тому просто натискаємо на клітинку, яка містить відповідне значення окладу (C4). Слідом за тим, як її адресу відобразився в елементі для виведення результату, тиснемо на кнопку «помножити» (*) на клавіатурі. Потім нам потрібно перейти до використання оператора ДВССИЛ. Виконуємо клацання по іконці «Вставити функцію».
2. У вікні Майстра функцій переходимо в категорію «Посилання та масиви». Серед представленого списку назв виділяємо найменування «ДВССИЛ». Потім клацаємо по кнопці «OK».
3. Проводиться активація віконця аргументів оператора ДВССИЛ. Воно складається з двох полів, які відповідають аргументам цієї функції.

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

   Так як ми працюємо в стандартному режимі відображення координат, то поле «A1» залишаємо незаповненим. Клацаємо по кнопці «OK».

4. Додаток виконує обчислення і виводить результат в елемент листа, що містить формулу.
5. Тепер виробляємо копіювання цієї формули в усі інші комірки стовпчика «Заробітна плата» за допомогою маркера заповнення, як ми це робили раніше. Як бачимо, все результати були розраховані вірно.
6. Подивимося, як відображається формула в одній з комірок, куди вона була скопійована. Виділяємо другий елемент стовпця і дивимося на рядок формул. Як бачимо, перший множник, який є відносною посиланням, змінив свої координати. У той же час, аргумент другого множника, який представлений функцією ДВССИЛ, залишився незмінним. В даному випадку була використана методика фіксованою адресації.

урок: Оператор ДВССИЛ в Ексель

Абсолютну адресацію в таблицях Excel можна забезпечити двома способами: використання функції ДВССИЛ і застосування абсолютних посилань. При цьому функція забезпечує більш жорстку прив'язку до адресою. Частково абсолютну адресацію можна також застосовувати при використанні змішаних посилань.

• ← Зведення числа в квадрат в Microsoft Excel
• Складання кошторису в Microsoft Excel →