Функція ДВССИЛ в Microsoft Excel

Однією з вбудованих функцій програми Excel є ДВССИЛ. Її завдання полягає в тому, щоб повертати в елемент листа, де вона розташована, вміст комірки, на яку вказана в ній у вигляді аргументу посилання в текстовому форматі.

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

Застосування формули ДВССИЛ

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

Даний оператор відноситься до категорії функцій «Посилання та масиви» і має наступний синтаксис:

=ДВССЫЛ(ссылка_на_ячейку;[a1])

Таким чином, формула має всього два аргументи.

Аргумент «Посилання на осередок» представлений у вигляді посилання на елемент листа, дані містяться в якому потрібно відобразити. При цьому зазначена посилання повинне мати текстовий вигляд, тобто, бути «обгорнута» лапками.

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

Якщо говорити просто, то ДВССИЛ є своєрідним еквівалентом посилання одного осередку на іншу після знака «дорівнює». Наприклад, в більшості випадків вираз

=ДВССЫЛ("A1")

буде еквівалентно висловом

=A1

Але на відміну від виразу «= A1» оператор ДВССИЛ прив'язується ні до конкретної осередку, а до координат елемента на аркуші.

Розглянемо, що це означає на простому прикладі. В осередках B8 і B9 відповідно розміщена записана через «=» формула і функція ДВССИЛ. Обидві формули посилаються на елемент B4 і виводять його вміст на лист. Природно це вміст однакове.

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

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

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

Тепер давайте розглянемо конкретні приклади застосування оператора.

Приклад 1: одиночне застосування оператора

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

Маємо довільну таблицю. Стоїть завдання відобразити дані першої осередки першого шпальти в перший елемент окремої колонки за допомогою досліджуваної формули.

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

   В поле «A1», так як ми працює в звичайному типі координат, можна поставити значення «ІСТИНА», а можна залишити його взагалі порожнім, що ми і зробимо. Це будуть рівнозначні дії.

   Після цього клацаємо по кнопці «OK».

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

урок: Майстер функцій в програмі Excel

Приклад 2: використання оператора в комплексній формулі

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

Маємо помісячну таблицю доходів підприємства. Нам потрібно підрахувати суму доходу за певний період часу, наприклад березень - травень або червень - листопад. Звичайно, для цього можна скористатися формулою простого підсумовування, але в цьому випадку при необхідності підрахунку загального результату за кожен період нам весь час доведеться міняти цю формулу. А ось при використанні функції ДВССИЛ можна буде виробляти зміна підсумованого діапазону, просто в окремих осередках вказавши відповідний місяць. Спробуємо використовувати даний варіант на практиці спочатку для обчислення суми за період з березня по травень. При цьому буде використана формула з комбінацією операторів СУМ і ДВССИЛ.

1. Перш за все, в окремих елементах на аркуші вносимо найменування місяців початку і кінця періоду, за який буде проводитися розрахунок, відповідно «Март» та «Май».
2. Тепер дамо ім'я всім осередкам в стовпці «Дохід», яке буде аналогічною назві відповідного їм місяця. Тобто, перший елемент в стовпці «Дохід», який містить розмір виручки, слід назвати «Січ», другий - «Февраль» і т.д.

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

3. Запускається вікно створення імені. В поле «Ім'я» вписуємо найменування «Січ». Більше ніяких змін у вікні проводити не потрібно, хоча про всяк випадок можна перевірити, щоб координати в поле «Діапазон» відповідали адресою осередки містить розмір виручки за січень. Після цього клацаємо по кнопці «OK».
4. Як бачимо, тепер при виділенні даного елемента у вікні імені відображається не її адресу, а то найменування, яке ми їй дали. Аналогічну операцію проробляємо з усіма іншими елементами стовпчика «Дохід», присвоївши їм послідовно імена «Февраль», «Март», «Квітень» і т.д. до грудня включно.
5. Вибираємо комірку, в яку буде виводитися сума значень зазначеного інтервалу, і виділяємо її. Потім клацаємо по піктограмі «Вставити функцію». Вона розміщена зліва від рядка формул і праворуч від поля, де відображається ім'я осередків.
6. В активувати віконці Майстра функцій переміщаємося в категорію «Математичні». Там вибираємо найменування «СУМ». Клацаємо по кнопці «OK».
7. Слідом за виконанням даного дії запускається вікно аргументів оператора СУМ, єдиним завданням якого є підсумовування зазначених значень. Синтаксис цієї функції дуже простий:

   =СУММ(число1;число2;…)

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

   Встановлюємо курсор в поле «Число1». Потім тиснемо на піктограму у вигляді перевернутого трикутника праворуч від поля найменування діапазонів. Розкривається список останніх використовуваних функцій. Якщо серед них присутній найменування «ДВССИЛ», то відразу клікаєм по ньому для переходу у вікно аргументів цієї функції. Але цілком можливо, що в цьому списку ви його не знайдете. У такому випадку потрібно клацнути по найменуванню «Інші функції ...» в самому низу списку.

8. Запускається вже знайоме нам віконце Майстра функцій. Переміщаємося в розділ «Посилання та масиви» і вибираємо там найменування оператора ДВССИЛ. Після цього дії клацаємо по кнопці «OK» в нижній частині вікна.
9. Відбувається запуск вікна аргументів оператора ДВССИЛ. В поле «Посилання на осередок» вказуємо адресу елемента листа, який містить найменування початкового місяця діапазону призначеного для розрахунку суми. Зверніть увагу, що як раз в цьому випадку брати посилання в лапки не потрібно, так як в даному випадку в якості адреси будуть виступати не координати осередку, а її вміст, який вже має текстовий формат (слово «Март»). Поле «A1» залишаємо порожнім, так як ми використовуємо стандартний тип позначення координат.

   Після того, як адресу відобразився в полі, не поспішаємо тиснути на кнопку «OK», так як це вкладена функція, і дії з нею відрізняються від звичайного алгоритму. Клацаємо по найменуванню «СУМ» в рядку формул.

10. Після цього ми повертаємося у вікно аргументів СУМ. Як бачимо, в поле «Число1» вже відобразився оператор ДВССИЛ зі своїм спорядженням. Встановлюємо курсор в цей же поле відразу після останнього символу в запису. Ставимо знак двокрапки (:). Даний символ означає знак адреси діапазону комірок. Далі, не витягуючи курсор з поля, знову натискаємо по значку у вигляді трикутника для вибору функцій. На цей раз в списку недавно використаних операторів найменування «ДВССИЛ» має точно бути присутнім, так як ми зовсім недавно використовували цю функцію. Клацаємо по найменуванню.
11. Знову відкривається вікно аргументів оператора ДВССИЛ. Заносимо в поле «Посилання на осередок» адреса елемента на аркуші, де розташовано найменування місяця, який завершує розрахунковий період. Знову координати повинні бути вписані без лапок. Поле «A1» знову залишаємо порожнім. Після цього клацаємо по кнопці «OK».
12. Як бачимо, після даних дій програма проводить розрахунок і видає результат складання доходу підприємства за вказаний період (березень - травень) в попередньо виділений елемент листа, в якому розташовується сама формула.
13. Якщо ми поміняємо в осередках, де вписані назви місяців початку і кінця розрахункового періоду, на інші, наприклад на "Червень" і «Ноябрь», то і результат зміниться відповідно. Буде складена сума доходу за вказаний період часу.

урок: Як порахувати суму в Ексель

Як бачимо, незважаючи на те, що функцію ДВССИЛ не можна назвати однією з найбільш популярних у користувачів, проте, вона допомагає вирішити завдання різної складності в Excel набагато простіше, ніж це можна було б зробити за допомогою інших інструментів. Найбільше даний оператор корисний в складі складних формул, в яких він є складовою частиною вираження. Але все-таки потрібно відзначити, що всі можливості оператора ДВССИЛ досить важкі для розуміння. Це як раз і пояснює малу популярність даної корисної функції у користувачів.

• ← Заливка комірок в залежності від значення в Microsoft Excel
• Вставка тексту в комірку з формулою в Microsoft Excel →