Створення посилань у Microsoft Excel

Зміст

• Створення різних типів посилань
  • Спосіб 1: Створення посилань у складі формул в межах одного аркуша
  • Спосіб 2: Створення посилань в складі формул на інші листи і книги
  • Спосіб 3: функція ДВСИЛ
  • Спосіб 4: створення гіперпосилань
• Питання та відповіді

Посилання & 8212; один з головних інструментів при роботі в Microsoft Excel. Вони є невід'ємною частиною формул, які застосовуються в програмі. Інші з них служать для переходу на інші документи або навіть ресурси в Інтернеті. Давайте з'ясуємо, як створити різні типи посилаються виразів в Ексель.

Створення різних типів посилань

Відразу потрібно зауважити, що всі посилаються вирази можна розділити на дві великі категорії: призначені для обчислень в складі формул, функцій, інших інструментів і службовці для переходу до зазначеного об'єкту. Останні ще прийнято називати гіперпосиланнями. Крім того, посилання (лінки) діляться на внутрішні і зовнішні. Внутрішні-це посилаються вирази всередині книги. Найчастіше вони застосовуються для обчислень, як складова частина формули або аргументу функції, вказуючи на конкретний об'єкт, де містяться оброблювані дані. У цю ж категорію можна віднести ті з них, які посилаються на місце на іншому аркуші документа. Всі вони, в залежності від їх властивостей, діляться на відносні і абсолютні.

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

Від того, який саме тип потрібно створити, і залежить обираний спосіб створення. Давайте зупинимося на різних способах детальніше.

Спосіб 1: Створення посилань у складі формул в межах одного аркуша

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

Найпростіший посилальний вираз виглядає таким чином:

=A1

Обов'язковим атрибутом виразу є знак «=» . Тільки при установці даного символу в осередок перед виразом, воно буде сприйматися, як посилається. Обов'язковим атрибутом також є найменування стовпця (в даному випадку A ) та номер стовпця (у цьому випадку 1 ).

Вираз «=A1» говорить про те, що в той елемент, в якому воно встановлено, підтягуються дані з об'єкта з координатами A1 .

Якщо ми замінимо вираз у комірці, де виводиться результат, наприклад, на «=B5» , то в неї буде підтягуватися значення з об'єкта з координатами B5 .

За допомогою лінків можна виробляти також Різні математичні дії. Наприклад, запишемо наступний вираз:

=A1+B5

Клацнем по кнопці Enter . Тепер, в тому елементі, де розташований цей вираз, буде проводитися підсумовування значень, які розміщені в об'єктах з координатами A1 і B5 .

За таким же принципом проводиться ділення, множення, віднімання і будь-яке інше математичне дію.

Щоб записати окрему посилання або в складі формули, зовсім не обов'язково забивати її з клавіатури. Досить встановити символ «=» , а потім клацнути лівою кнопкою миші по тому об'єкту, на який ви бажаєте послатися. Його адреса відобразиться в тому об'єкті, де встановлено знак " дорівнює» .

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

Вираз R1C1 рівнозначно A1 , а R5C2 - B5 . Тобто, в даному випадку, на відміну від стилю A1 , на першому місці стоять координати рядка, а стовпця – на другому.

Обидва стилі діють в Excel рівнозначно, але шкала координат за замовчуванням має вигляд A1 . Щоб її Переключити на вигляд R1C1 потрібно в параметрах Excel у розділі «Формули» встановити прапорець навпроти пункту " стиль посилань R1C1» .

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

=R[2]C[-1]

Якщо ж записати вираз вручну, то воно прийме звичайний вигляд R1C1 .

У першому випадку був представлений відносний тип ( =R[2]C[-1] ), а в другому ( =R1C1 ) – абсолютний. Абсолютні лінки посилаються на конкретний об'єкт, а відносні – на положення елемента, щодо осередку.

Якщо повернуться до стандартного стилю, то відносні лінки мають вигляд A1 , а абсолютні $A$1 . За замовчуванням усі посилання, створені в Excel, відносні. Це виражається в тому, що при копіюванні за допомогою маркера заповнення значення в них змінюється щодо переміщення.

1. Щоб подивитися, як це буде виглядати на практиці, пошлемося на осередок A1 . Встановлюємо в будь-якому порожньому елементі листа символ «=» і клацаємо по об'єкту з координатами A1 . Після того, як адреса відобразився в складі формули, клацаємо по кнопці Enter .

2. Наводимо курсор на нижній правий край об'єкта, в якому відобразився результат обробки формули. Курсор трансформується в маркер заповнення. Затискаємо ліву кнопку миші і простягаємо Покажчик паралельно діапазону з даними, які потрібно скопіювати.

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

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

1. Щоб провести перетворення, досить близько координат по горизонталі і вертикалі поставити символ долара ( $ ).

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

Крім абсолютних і відносних, існують ще змішані лінки. У них знаком долара відзначені або тільки координати стовпця (приклад: $A1 ),

Або тільки координати рядка (приклад: A$1 ).

Знак долара можна вносити ВРУЧНУ, натиснувши на відповідний символ на клавіатурі ( $ ). Він буде висвічений, якщо в англійській розкладці клавіатури у верхньому регістрі клікнути на клавішу «4» .

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

В Excel послатися можна не тільки на конкретну комірку, але і на цілий діапазон. Адреса діапазону виглядає як координати верхнього лівого його елемента і нижнього правого, розділені знаком двокрапки ( : ). Наприклад, діапазон, виділений на зображенні нижче, має координати A1:C5 .

Відповідно лінк на даний масив буде виглядати як:

=A1:C5

Урок: абсолютні та відносні посилання в Microsoft Excel

Спосіб 2: Створення посилань в складі формул на інші листи і книги

До цього ми розглядали дії тільки в межах одного аркуша. Тепер подивимося, як послатися на місце на іншому аркуші або навіть книзі. В останньому випадку це буде вже не внутрішня, а зовнішня посилання.

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

Для того, щоб послатися на значення на іншому аркуші, потрібно між знаком «=» і координатами осередку вказати його назву, після чого встановити знак оклику.

Так лінк на клітинку на листі 2 з координатами B4 буде виглядати наступним чином:

=Лист2!B4

Вираз можна вбити вручну з клавіатури, але набагато зручніше вчинити наступним чином.

1. Встановлюємо знак «=» в елементі, який буде містити посилається вираз. Після цього за допомогою ярлика над рядком стану переходимо на той лист, де розташований об'єкт, на який потрібно послатися.

2. Після переходу виділяємо даний об'єкт (осередок або діапазон) і тиснемо на кнопку Enter .

3. Після цього відбудеться автоматичне повернення на попередній лист, але при цьому буде сформована потрібна нам посилання.

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

У зв'язку з цими особливостями відрізняється і вид Лінка на інші книги. Якщо ви впроваджуєте його в інструмент, що працює виключно з запущеними файлами, то в цьому випадку можна просто вказати найменування книги, на яку ви посилаєтеся. Якщо ж ви припускаєте працювати з файлом, який не збираєтеся відкривати, то в цьому випадку потрібно вказати повний шлях до нього. Якщо ви не знаєте, в якому режимі будете працювати з файлом або не впевнені, як з ним може працювати конкретний інструмент, то в цьому випадку знову ж краще вказати повний шлях. Зайвим це точно не буде.

Якщо потрібно послатися на об'єкт з адресою C9 , розташований на листі 2 у запущеній книзі під назвою «Excel.xlsx» , то слід записати наступний вираз в елемент листа, куди буде виводитися значення:

=[excel.XLSX] Лист2!C9

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

='D:\Новая папка\[excel.XLSX] Лист2'!C9

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

1. Ставимо символ «=» в тій комірці, де буде розташовано посилається вираз.

2. Потім відкриваємо книгу, на яку потрібно послатися, якщо вона не запущена. Клацаємо на її аркуші в тому місці, на яке потрібно послатися. Після цього натискаємо по Enter .

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

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

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

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

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

=ДВПОСИЛАННЯ(посилання;a1)

»посилання" &8212; це аргумент, що посилається на клітинку в текстовому вигляді (загорнутий лапками);

«A1» & 8212; необов'язковий аргумент, який визначає, в якому стилі використовуються координати: A1 або R1C1 . Якщо значення даного аргументу «правда» , то застосовується перший варіант, якщо «брехня» &8212; другий. Якщо даний аргумент взагалі опустити, то за замовчуванням вважається, що застосовуються адресація типу A1 .

1. Відзначаємо елемент листа, в якому буде знаходитися формула. Клацаємо по піктограмі " Вставити функцію» .

2. В Майстер функцій в блоці " посилання та масиви» відзначаємо " ДВСИЛ» . Жати «OK» .

3. Відкривається вікно аргументів даного оператора. У полі " посилання на клітинку» встановлюємо курсор і виділяємо кліком мишки той елемент на аркуші, на який бажаємо послатися. Після того, як адреса відобразився в поле, «обертаємо» його лапками. Друге поле ( «A1» ) залишаємо порожнім. Натискаємо по «OK» .

4. Результат обробки даної функції відображається у виділеній комірці.

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

Урок: функція ДВСИЛАННЯ в Microsoft Excel

Спосіб 4: створення гіперпосилань

Гіперпосилання відрізняються від типу посилань, які ми розглядали вище. Вони служать не для того, щоб «підтягувати» дані з інших областей в ту клітинку, де вони розташовані, а для того, щоб здійснювати перехід при кліці в ту область, на яку вони посилаються.

1. Існує три варіанти переходу до вікна створення гіперпосилань. Згідно з першим з них, потрібно виділити осередок, в яку буде вставлена гіперпосилання, і клікнути по ній правою кнопкою миші. У контекстному меню вибираємо варіант " гіперпосилання...» .

   Замість цього можна, після виділення елемента, куди буде вставлена гіперпосилання, перейти у вкладку «Вставка» . Там на стрічці потрібно клацнути по кнопці «гіперпосилання» .

   

   Також після виділення комірки можна застосувати натискання клавіш CTRL+K .

2. Після застосування будь-якого з цих трьох варіантів відкриється вікно створення гіперпосилання. У лівій частині вікна існує можливість вибору, з яким об'єктом потрібно зв'язатися:
   • З місцем у поточній книзі;
   • З новою книгою;
   • З веб-сайтом або файлом;
   • З e-mail.

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

   Якщо є потреба зробити зв'язок з веб-сайтом, то в цьому випадку в тому ж розділі Вікна створення гіперпосилання в поле «адреса» потрібно просто вказати адресу потрібного веб-ресурсу і натиснути на кнопку «OK» .

   

   Якщо потрібно вказати гіперпосилання на місце в поточній книзі, то слід перейти в розділ " Пов'язати з місцем у документі» . Далі в центральній частині вікна потрібно вказати лист і адресу тієї комірки, з якою слід зробити зв'язок. Натискаємо по «OK» .

   

   Якщо потрібно створити новий документ Excel і прив'язати його за допомогою гіперпосилання до поточної книзі, то слід перейти в розділ " Пов'язати з новим документом» . Далі в центральній області вікна дати йому ім'я і вказати його місце розташування на диску. Потім клікнути по «OK» .

   

   При бажанні можна пов'язати елемент листа гіперпосиланням навіть з електронною поштою. Для цього переміщаємося в розділ " Пов'язати з електронною поштою» і в полі «адреса» вказуємо e-mail. Клацаємо по «OK» .


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

Крім того, гіперпосилання можна створити за допомогою вбудованої функції, яка має назву, яка говорить сама за себе – «гіперпосилання» .

Даний оператор має синтаксис:

=гіперпосилання(адреса; ім'я)

«адреса» &8212; аргумент, що вказує адресу веб-сайту в інтернеті або файлу на вінчестері, з яким потрібно встановити зв'язок.

«Ім'я» &8212; аргумент у вигляді тексту, який буде відображатися в елементі аркуша, що містить гіперпосилання. Цей аргумент не є обов'язковим. При його відсутності в елементі листа буде відображатися адреса об'єкта, на який функція посилається.

1. Виділяємо осередок, в якій буде розміщуватися гіперпосилання, і клацаємо по іконці " Вставити функцію» .

2. В Майстер функцій переходимо в розділ " посилання та масиви» . Відзначаємо назву "гіперпосилання" і натискаємо по «OK» .

3. У вікні аргументів у полі «адреса» вказуємо адресу на веб-сайт або файл на вінчестері. У полі «Ім'я» пишемо текст, який буде відображатися в елементі листа. Клацаємо по «OK» .

4. Після цього гіперпосилання буде створено.

Урок: як зробити або видалити гіперпосилання в Екселі

Ми з'ясували, що в таблицях Excel існує дві групи посилань: застосовуються в формулах і службовці для переходу (гіперпосилання). Крім того, ці дві групи поділяються на багато менших сортів. Саме від конкретної різновиди Лінка і залежить алгоритм процедури створення.