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

Посилання - один з головних інструментів при роботі в 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

урок: Абсолютні і відносні посилання в Майкрософт Ексель

Спосіб 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)

«Посилання» - це аргумент, який посилається на клітинку в текстовому вигляді (обгорнутий лапками);

«A1» - необов'язковий аргумент, який визначає, в якому стилі використовуються координати: A1 або R1C1. Якщо значення цього аргументу «ІСТИНА», то застосовується перший варіант, якщо «БРЕХНЯ» - то другий. Якщо цей аргумент взагалі опустити, то за замовчуванням вважається, що застосовуються адресація типу A1.

1. Відзначаємо елемент листа, в якому буде знаходитися формула. Клацають по піктограмі «Вставити функцію».
2. У Майстрі функцій в блоці «Посилання та масиви» відзначаємо «ДВССИЛ». Тиснемо «OK».
3. Відкривається вікно аргументів даного оператора. В поле «Посилання на осередок» встановлюємо курсор і виділяємо кліком мишки той елемент на аркуші, на який бажаємо послатися. Після того, як адресу відобразився в поле, «обертаємо» його лапками. Друге поле ( «A1») залишаємо порожнім. Кількома по «OK».
4. Результат обробки даної функції відображається в виділеної осередку.

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

урок: Функція ДВССИЛ в Майкрософт Ексель

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

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

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

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

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

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

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

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

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

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

4. Після того, як гіперпосилання була вставлена, текст в тій комірці, в якій вона розташована, за замовчуванням набуває синього кольору. Это значит, что гиперссылка активна. Чтобы перейти к тому объекту, с которым она связана, достаточно выполнить двойной щелчок по ней левой кнопкой мыши.

Кроме того, гиперссылку можно сгенерировать с помощью встроенной функции, имеющей название, которое говорит само за себя – «ГИПЕРССЫЛКА» .

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

=ГИПЕРССЫЛКА(адрес;имя)

«Адрес» — аргумент, указывающий адрес веб-сайта в интернете или файла на винчестере, с которым нужно установить связь.

«Имя» — аргумент в виде текста, который будет отображаться в элементе листа, содержащем гиперссылку. Этот аргумент не является обязательным. При его отсутствии в элементе листа будет отображаться адрес объекта, на который функция ссылается.

1. Выделяем ячейку, в которой будет размещаться гиперссылка, и клацаем по иконке «Вставить функцию» .
2. В Мастере функций переходим в раздел «Ссылки и массивы» . Отмечаем название «ГИПЕРССЫЛКА» и кликаем по «OK» .
3. В окне аргументов в поле «Адрес» указываем адрес на веб-сайт или файл на винчестере. В поле «Имя» пишем текст, который будет отображаться в элементе листа. Клацають по «OK».
4. После этого гиперссылка будет создана.

урок: Как сделать или удалить гиперссылки в Экселе

Мы выяснили, что в таблицах Excel существует две группы ссылок: применяющиеся в формулах и служащие для перехода (гиперссылки). Кроме того, эти две группы делятся на множество более мелких разновидностей. Именно от конкретной разновидности линка и зависит алгоритм процедуры создания.

• ← Робота з спадаючими списками в Microsoft Excel
• Робота з іменованих діапазоном в Microsoft Excel →