Програма Microsoft Excel: абсолютні і відносні посилання
При роботі з формулами в програмі Microsoft Excel користувачам доводиться оперувати посиланнями на інші осередки, розташовані в документі. Але, не кожен користувач знає, що ці посилання бувають двох видів: абсолютні і відносні. Давайте з'ясуємо, чим вони відрізняються між собою, і як створити посилання потрібного вигляду.
зміст
Визначення абсолютних і відносних посилань
Що ж являють собою абсолютні і відносні посилання в Ексель?
Абсолютні посилання - це посилання, при копіюванні яких координати осередків не змінюються, знаходяться в зафіксованому стані. У відносних посиланнях координати осередків змінюються при копіюванні, щодо інших осередків листа.
Приклад відносної посилання
Покажемо, як це працює на прикладі. Візьмемо таблицю, яка містить кількість і ціну різних найменувань продуктів. Нам потрібно порахувати вартість.
Робиться це простим множенням кількості (стовпець B) на ціну (стовпець C). Наприклад, для першого найменування товару формула буде виглядати так «= B2 * C2». Вписуємо її в відповідну клітинку таблиці.
Тепер, щоб вручну не забивається формули для осередків, які розташовані нижче, просто копіюємо цю формулу на весь стовпець. Стаємо на нижній правий край комірки з формулою, натискаємо лівою кнопкою миші, і при затиснутій кнопці тягнемо мишку вниз. Таким чином, формула скопіюється і в інші елементи таблиці.
Але, як бачимо, формула в нижній клітинці вже виглядає не «= B2 * C2», а «= B3 * C3». Відповідно, змінилися і ті формули, які розташовані нижче. Ось таку властивість зміни при копіюванні і мають відносні посилання.
Помилка в відносної посиланням
Але, далеко не у всіх випадках нам потрібні саме відносні посилання. Наприклад, нам потрібно в тій же таблиці розрахувати питому вагу вартості кожного найменування товару від загальної суми. Це робиться шляхом ділення вартості на загальну суму. Наприклад, щоб розрахувати питому вагу картоплі, ми його вартість (D2) ділимо на загальну суму (D7). Отримуємо наступну формулу: «= D2 / D7».
У разі, якщо ми спробуємо скопіювати формулу в інші рядки тим же способом, що і попередній раз, то отримаємо абсолютно не задовольняють нас результат. Як бачимо, вже у другому рядку таблиці формула має вигляд «= D3 / D8», тобто зрушила не тільки посилання на клітинку з сумою по рядку, а й посилання на осередок, що відповідає за загальний підсумок.
D8 - це абсолютно порожня клітинка, тому формула і видає помилку. Відповідно, формула в рядку нижче буде посилатися на клітинку D9, і т.д. Нам же потрібно, щоб при копіюванні постійно зберігалася посилання на осередок D7, де розташований підсумок загальної суми, а така властивість мають якраз абсолютні посилання.
Створення абсолютної посилання
Таким чином, для нашого прикладу дільник повинен бути відносної посиланням, і змінюватися в кожному рядку таблиці, а ділене повинно бути абсолютним посиланням, яка постійно посилається на одну клітинку.
Зі створенням відносних посилань у користувачів проблем не буде, так як всі посилання в Microsoft Excel за замовчуванням є відносними. А ось, якщо потрібно зробити абсолютне посилання, доведеться застосувати один прийом.
Після того, як формула введена, просто ставимо в осередку, або в рядку формул, перед координатами стовпця і рядка комірки, на яку потрібно зробити абсолютне посилання, знак долара. Можна також, відразу після введення адреси натиснути функціональну клавішу F7, і знаки долара перед координатами рядка і стовпця відобразяться автоматично. Формула в самій верхній клітинці прийме такий вигляд: «= D2 / $ D $ 7».
Копіюємо формулу вниз по стовпчику. Як бачимо, на цей раз все вийшло. В осередках знаходяться коректні значення. Наприклад, у другому рядку таблиці формула виглядає, як «= D3 / $ D $ 7», тобто дільник змінився, а ділене залишилося незмінним.
змішані посилання
Крім типових абсолютних і відносних посилань, існують так звані змішані посилання. У них одна зі складових змінюється, а друга фіксована. Наприклад, у змішаній посилання $ D7 рядок змінюється, а стовпець фіксований. У посилання D $ 7, навпаки, змінюється стовпець, але рядок має абсолютне значення.
Як бачимо, при роботі з формулами в програмі Microsoft Excel для виконання різних завдань доводиться працювати як з відносними, так і з абсолютними посиланнями. У деяких випадках використовуються також змішані посилання. Тому, користувач навіть середнього рівня повинен чітко розуміти різницю між ними, і вміти користуватися цими інструментами.