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

Зміст

• Застосування абсолютної адресації
  • Спосіб 1: абсолютне посилання
  • Спосіб 2: функція ДВСИЛ
• Питання та відповіді

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

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

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

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

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

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

=A1

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

=$A$1

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

Існує і більш швидкий спосіб. Потрібно встановити курсор в осередок, в якій знаходиться адреса, і клацнути по функціональній клавіші 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» &8212; необов'язковий і використовується в рідкісних випадках. Його застосування необхідно тільки тоді, коли користувач вибирає альтернативний варіант адресації, а не звичайне використання координат за типом «A1» (стовпці мають буквене позначення, а рядки & 8212; Цифрове). Альтернативний варіант має на увазі використання стилю «R1C1» , в якому стовпці, як і рядки, позначаються цифрами. Переключитися в даний режим роботи можна через вікно параметрів Excel. Тоді, застосовуючи оператор ДВСИЛ , як аргумент «a1» слід вказати значення «брехня» . Якщо ви працює в звичайному режимі відображення посилань, як і більшість інших користувачів, то в якості аргументу «a1» можна вказати значення «правда» . Втім, дане значення мається на увазі за замовчуванням, тому набагато простіше взагалі в даному випадку аргумент «a1» не вказувати.

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

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

2. У вікні, що відкриється майстри функцій переходимо в категорію " посилання та масиви» . Серед представленого списку назв виділяємо найменування " ДВСИЛ» . Потім клацаємо по кнопці «OK» .

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

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

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


4. Додаток виконує обчислення і виводить результат в елемент листа, що містить формулу.

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

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

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

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