Посилання в Excel

Рекламний блок

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

Абсолютна і відносна посилання на клітинку в Excel

Як Ви вже помітили, вставляючи формул в документ Excel, у рядку формул зазначаються адреси клітинок, у яких містяться дані. За замовчуванням вставляються відносні посилання на клітинки.

Ссылки в Excel

Відносне посилання дозволяє змінювати адресу клітинок за рядками і стовпцями при копіюванні формули в інше місце документа. Тобто, якщо скопіювати формулу з комірки А3 у комірку С3, то для розрахунку суми візьмуться нові адреси комірок: С1 і С2.

Використання відносних посилань Вам стане у нагоді, якщо потрібно вводити в документ багато однакових формул, наприклад, при заповненні таблиці.

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

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

Формула копіюється, і значення будуть пораховані. Якщо хочете перевірити правильність формули, виділіть будь-яку комірку з результатом, і в рядку формул подивіться, які комірки використовувалися для розрахунку. Можете також клацнути двічі мишею по клітинці.

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

Щоб зробити абсолютне посилання на комірку Excel, потрібно додати знак «$» на адресу клітинки перед назвою стовпця і рядка. Або ж поставити курсор в рядку формул після адреси потрібної комірки і натиснути «F4». У прикладі, для розрахунку суми в комірці А3, використовується тепер абсолютне посилання на клітинку А1.

Давайте порахуємо суму клітинки D1 і D2. В комірку D3 скопіюємо формулу з А3. Як бачите, результат замість 24 – 25. Все із-за того, що у формулі була використана абсолютне посилання на комірку $A$1. Тому в розрахунку використовувалися не комірки D1 і D2, а комірки $A$1 і D2.

Розглянемо для прикладу таку таблицю: є найменування товару та його собівартість. Щоб визначити ціну товару для продажу, потрібно порахувати ПДВ. ПДВ – 20%, і значення написано в комірці В9. Вписуємо формулу для розрахунку в клітинку С6.

Якщо ми скопіюємо формулу в інші комірки, то не отримаємо результат. Так як в розрахунку будуть використовуватися клітинку В10 і В11, які не заповнені значеннями.

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

Якщо в рядку формул поставити курсор після адреси комірки і натиснути «F4» другий і третій раз, то вийде змішана посилання в Excel. У цьому випадку, при копіюванні може не бути змінені або рядок – А$1, або стовпець – $А1.

Посилання на інший аркуш в Excel

Посилання на інший аркуш в Ексель стане у нагоді, якщо Вам потрібно використовувати в розрахунках для формули значення, які знаходяться на інших аркушах документа Excel.

Посилання на комірку з іншого листа у формулі буде виглядати наступним чином: Лист1!А1 – назву листа, знак оклику, адреса клітинки. Якщо в назві листа використовуються прогалини, то його потрібно взяти в одинарні лапки: 'Підсумкові суми' – 'Підсумкові суми'!А1.

Наприклад, розрахуємо значення ПДВ для товарів. Таблиця, в якій буде розраховуватися формула, що знаходиться на аркуші " аркуш1, значення ПДВ перебуває на аркуші з назвою Усі константи.

На аркуші Всі константи для розрахунку формули нам необхідно буде значення, записане в комірці В1.

Повертаємося на Лист1. У комірку С6 пишемо формулу для розрахунку ПДВ: ставимо «=», потім виділяємо комірку В6 і робимо посилання на комірку В1 з іншого листа.

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

Якщо змінити назву аркуша Усі константи на Всі константы1111, то воно автоматично зміниться і у формулі. Точно також, якщо на аркуші Всі константи змінити значення в клітинці В1 з 20% на 22%, то формула буде перерахована.

Для того, щоб зробити посилання на іншу книгу Excel у формулі, візьміть її назву в квадратні дужки. Наприклад, зробимо посилання в комірці А1 у книзі з назвою Книга1 на комірку А3 книжки з назвою Посилання. Для цього ставимо в комірку А1 «=», в квадратних дужках пишемо назву книги з розширенням, потім назва аркуша з цієї книги, ставимо «!» та адресу комірки.

Книга, на яку ми посилаємося, повинна бути відкрита.

Посилання на файл або гіперпосилання в Excel

В документах Excel іноді з'являється необхідність посилатися на зовнішні файли або на інші книги Excel. Реалізувати нам це допоможе використання гіперпосилання.

Виділіть комірку, в яку потрібно вставити гіперпосилання. Це може бути як число, так і текст або формула розрахована, або порожня клітинка. Перейдіть на вкладку «Вставка» і натисніть на кнопку «Гіперпосилання».

Зробимо посилання на іншу книгу Ексель. У полі «Зв'язати з» вибираємо «файлом, веб-сторінкою». Знайдіть потрібну папку на комп'ютері та виберіть файл. В полі «Текст» можна змінити напис, яка буде відображатися у комірці – це тільки в тому випадку, якщо клітинка спочатку була порожня. Натисніть «ОК».

Тепер при натисканні на створену гіперпосилання буде відкриватися книга Excel з назвою Список.

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



Рекламний блок