Як відомо, в таблицях Excel існує два види адресації: відносна і абсолютна. У першому випадку посилання змінюється у напрямку копіювання на відносну величину зсуву, а в другому - є фіксованою і при копіюванні залишається незмінною. Але за умовчанням всі адреси в Ексель є абсолютними. У той же час, досить часто присутня необхідність використовувати абсолютну (фіксовану) адресацію. Давайте дізнаємося, якими способами це можна здійснити.
Застосування абсолютної адресації
Абсолютна адресація нам може знадобитися, наприклад, в тому випадку, коли ми копіюємо формулу, одна частина якої складається із змінної, яка відображається в ряду чисел, а друга має постійне значення. Тобто, дане число грає роль незмінного коефіцієнта, з яким потрібно провести певну операцію (множення, ділення і т.д.) всьому ряду змінних чисел.В Excel існує два способи задати фіксовану адресацію: шляхом формування абсолютної посилання і за допомогою функції ДВССИЛ. Давайте розглянемо кожен із зазначених способів докладно.
Спосіб 1: абсолютне посилання
Безумовно, найвідомішим і часто вживаним способом створити абсолютну адресацію є застосування абсолютних посилань. Абсолютні посилання мають відмінність не тільки функціональне, а й синтаксичну. Відносний адреса має такий синтаксис:
= A1
У фіксованого адреси перед значенням координат встановлюється знак долара:
= $ A $ 1
Знак долара можна ввести вручну. Для цього потрібно встановити курсор перед першим значенням координат адреси (по горизонталі), що знаходиться в осередку або в рядку формул. Далі, в англомовній розкладці клавіатури слід клікнути по клавіші «4» у верхньому регістрі (з затиснутою клавішею «Shift»). Саме там розташований символ долара. Потім потрібно ту ж процедуру проробити і з координатами по вертикалі.
Існує і більш швидкий спосіб. Потрібно встановити курсор в осередок, в якій знаходиться адресу, і клацнути по функціональній клавіші F4. Після цього знак долара моментально з'явиться одночасно перед координатами по горизонталі і вертикалі даної адреси.
Тепер давайте розглянемо, як застосовується на практиці абсолютна адресація шляхом використання абсолютних посилань.
Візьмемо таблицю, в якій розраховується заробітна плата працівників. Розрахунок проводиться шляхом множення величини їх особистого окладу на фіксований коефіцієнт, який однаковий для всіх співробітників. Сам коефіцієнт розташований в окремій клітинці аркуша. Перед нами стоїть завдання розрахувати заробітну плату всіх працівників максимально швидким способом.
- Отже, в перший осередок шпальти «Заробітна плата» вводимо формулу множення ставки відповідного працівника на коефіцієнт. У нашому випадку ця формула має такий вигляд:
= C4 * G3
- Щоб розрахувати готовий результат, клацаємо по клавіші Enter на клавіатурі. Підсумок виводиться в клітинку, яка містить формулу.
- Ми розрахували значення зарплати для першого співробітника. Тепер нам потрібно це зробити для всіх інших рядків. Звичайно, операцію можна записати в кожну осередок шпальти «Заробітна плата» вручну, вводячи аналогічну формулу з поправкою на зміщення, але у нас стоїть завдання, як можна швидше виконати обчислення, а ручне введення займе багато часу. Та й навіщо витрачати зусилля на ручне введення, якщо формулу можна просто скопіювати в інші осередки?
Для копіювання формули можна застосувати такий інструмент, як маркер заповнення. Стаємо курсором в нижній правий кут комірки, де вона міститься. При цьому сам курсор повинен перетворитися в цей самий маркер заповнення у вигляді хрестика. Затискаємо ліву кнопку миші і тягнемо курсор вниз до кінця таблиці.
- Але, як бачимо, замість коректного розрахунку заробітної плати для інших співробітників, ми отримали одні нулі.
- Дивимося, в чому причина такого результату. Для цього виділяємо другий осередок в стовпці «Заробітна плата». У рядку формул відображається відповідне даному осередку вираження. Як бачимо, перший множник (C5) відповідає ставці того працівника, зарплату якого ми розраховуємо. Зсув координат в порівнянні з попередньою осередком сталося через властивості відносності. Втім, в конкретно даному випадку це нам і потрібно. Завдяки цьому першим множником стала ставка саме потрібного нам працівника. Але зсув координат сталося і з другим множником. І тепер його адресу посилається нема на коефіцієнт (1,28), а на порожню комірку нижче.
Саме це і послужило причиною того, що розрахунок заробітної плати для наступних співробітників зі списку вийшов некоректним.
- Для виправлення ситуації нам потрібно змінити адресацію другого множника з відносною на фіксовану. Для цього повертаємося до першої осередку шпальти «Заробітна плата», виділивши її. Далі подорожуємо у рядок формул, де відобразилося потрібне нам вираз. Виділяємо курсором другий множник (G3) і тиснемо на функціональну клавішу на клавіатурі.
- Як бачимо, близько координат другого множника з'явився знак долара, а це, як ми пам'ятаємо, є атрибутом абсолютної адресації. Щоб вивести результат на екран тиснемо на клавішу Enter.
- Тепер, як і раніше викликаємо маркер заповнення, встановивши курсор в правий нижній кут першого елемента стовпця «Заробітна плата». Затискаємо ліву кнопку миші і тягнемо його вниз.
- Як бачимо, в даному випадку розрахунок був проведений вірно і сума заробітної плати для всіх працівників підприємства розрахована коректно.
- Перевіримо, як була скопійована формула. Для цього виділяємо другий елемент стовпця «Заробітна плата». Дивимося на вираз, розташоване в рядку формул. Як бачимо, координати першого множника (C5), який як і раніше є відносним, зрушили в порівнянні з попередньою осередком на один пункт вниз по вертикалі. Зате другий множник ($ G $ 3), адресацію в якому ми зробили фіксованою, залишився незмінним.
У Ексель також застосовується, так звана змішана адресація. В цьому випадку в адресі елемента фіксується або стовпець, або рядок. Досягається це в такий спосіб, що знак долара ставиться тільки перед одним з координат адреси. Ось приклад типової змішаної посилання:
= A $ 1
Ця електронна адреса була теж вважається змішаним:
= $ A1
Тобто, абсолютна адресація в змішаній посиланням використовується тільки для одного з значень координат з двох.
Подивимося, як таку змішану посилання можна застосувати на практиці на прикладі все тієї ж таблиці заробітної плати співробітників підприємства.
- Як бачимо, раніше ми зробили так, що всі координати другого множника мають абсолютну адресацію. Але давайте розберемося, чи обов'язково в цьому випадку обидва значення повинні бути фіксованими? Як бачимо, при копіюванні відбувається зміщення по вертикалі, а по горизонталі координати залишаються незмінними. Тому цілком можна застосувати абсолютну адресацію тільки до координат рядка, а координати стовпчика залишити такими, якими вони є за замовчуванням - відносними.
Виділяємо перший елемент стовпця «Заробітна плата» і в рядку формул виконуємо вищевказану маніпуляцію. Отримуємо формулу такого вигляду:
= C4 * G $ 3
Як бачимо, фіксована адресація в другому множнику застосовується тільки по відношенню до координат рядка. Для виведення результату в комірку клацаємо по кнопці Enter.
- Після цього за допомогою маркера заповнення копіюємо цю формулу на діапазон комірок, який розташований нижче. Як бачимо, розрахунок заробітної плати по всім співробітникам виконаний коректно.
- Дивимося, як відображається скопійована формула в другій осередку шпальти, над яким ми виконували маніпуляцію. Як можна спостерігати в рядку формул, після виділення даного елемента листа, незважаючи на те, що абсолютну адресацію у другого множника мали тільки координати рядків, зсув координат стовпчика не відбулося. Це пов'язано з тим, що ми виконували копіювання не по горизонталі, а по вертикалі. Якби ми виконали копіювання по горизонталі, то в аналогічному випадку, навпаки, довелося б робити фіксовану адресацію координат стовпців, а для рядків ця процедура була б необов'язковою.
Урок: Абсолютні і відносні посилання в Ексель
Спосіб 2: функція ДВССИЛ
Другим способом організувати абсолютну адресацію в таблиці Excel є застосування оператора ДВССИЛ. Зазначена функція належить до групи вбудованих операторів «Посилання та масиви». Її завданням є формування посилання на зазначену осередок з висновком результату в той елемент листа, в якому знаходиться сам оператор. При цьому посилання прикріплюється до координат ще міцніше, ніж при використанні знака долара. Тому іноді прийнято називати посилання з використанням ДВССИЛ «суперабсолютнимі». Цей оператор має наступний синтаксис:
= ДВССИЛ (ссилка_на_ячейку; [a1])
Функція має в наявності два аргументи, перший з яких має обов'язковий статус, а другий - ні.
Аргумент «Посилання на осередок» є посиланням на елемент листа Excel в текстовому вигляді. Тобто, це звичайна посилання, але укладена в лапки. Саме це і дозволяє забезпечити властивості абсолютної адресації.
Аргумент «a1» - необов'язковий і використовується в рідкісних випадках. Його застосування необхідно тільки тоді, коли користувач вибирає альтернативний варіант адресації, а не звичайне використання координат по типу «A1» (стовпці мають буквене позначення, а рядки - цифрове). Альтернативний варіант передбачає використання стилю «R1C1», в якому стовпці, як і рядки, позначаються цифрами. Переключитися в даний режим роботи можна через вікно параметрів Excel. Тоді, застосовуючи оператор ДВССИЛ, як аргумент «a1» слід вказати значення «БРЕХНЯ». Якщо ви працює в звичайному режимі відображення посилань, як і більшість інших користувачів, то в якості аргументу «a1» можна вказати значення «ІСТИНА». Втім, дане значення мається на увазі за замовчуванням, тому набагато простіше взагалі в даному випадку аргумент «a1» не вказувати.
Погляньмо, як буде працювати абсолютна адресація, організована за допомогою функції ДВССИЛ, на прикладі нашої таблиці заробітної плати.
- Виробляємо виділення першого елемента стовпця «Заробітна плата». Ставимо знак «=». Як пам'ятаємо, перший множник у зазначеній формулі обчислення зарплати повинен бути представлений відносною адресою. Тому просто натискаємо на клітинку, яка містить відповідне значення окладу (C4). Слідом за тим, як її адресу відобразився в елементі для виведення результату, тиснемо на кнопку «помножити» (*) на клавіатурі. Потім нам потрібно перейти до використання оператора ДВССИЛ. Виконуємо клацання по іконці «Вставити функцію».
- У вікні Майстра функцій переходимо в категорію «Посилання та масиви». Серед представленого списку назв виділяємо найменування «ДВССИЛ». Потім клацаємо по кнопці «OK».
- Проводиться активація віконця аргументів оператора ДВССИЛ. Воно складається з двох полів, які відповідають аргументам цієї функції.
Ставимо курсор в поле «Посилання на осередок». Просто натискаємо по тому елементу листа, в якому знаходиться коефіцієнт для розрахунку зарплати (G3). Адреса тут же з'явиться в поле вікна аргументів. Якби ми мали справу зі звичайною функцією, то на цьому введення адреси можна було б вважати завершеним, але ми використовуємо функцію ДВССИЛ. Як ми пам'ятаємо, адреси в ній повинні мати вигляд тексту. Тому обертаємо координати, які розташуйся в поле вікна, лапками.
Так як ми працюємо в стандартному режимі відображення координат, то поле «A1» залишаємо незаповненим. Клацаємо по кнопці «OK».
- Додаток виконує обчислення і виводить результат в елемент листа, що містить формулу.
- Тепер виробляємо копіювання цієї формули в усі інші комірки стовпчика «Заробітна плата» за допомогою маркера заповнення, як ми це робили раніше. Як бачимо, все результати були розраховані вірно.
- Подивимося, як відображається формула в одній з комірок, куди вона була скопійована. Виділяємо другий елемент стовпця і дивимося на рядок формул. Як бачимо, перший множник, який є відносною посиланням, змінив свої координати. У той же час, аргумент другого множника, який представлений функцією ДВССИЛ, залишився незмінним. В даному випадку була використана методика фіксованою адресації.
Урок: Оператор ДВССИЛ в Ексель
Абсолютну адресацію в таблицях Excel можна забезпечити двома способами: використання функції ДВССИЛ і застосування абсолютних посилань. При цьому функція забезпечує більш жорстку прив'язку до адресою. Частково абсолютну адресацію можна також застосовувати при використанні змішаних посилань.