функція ДВПОСИЛАННЯ в Microsoft Excel

Зміст

• Застосування формули ДВСИЛ
  • Приклад 1: одиночне застосування оператора
  • Приклад 2: використання оператора в комплексній формулі
• Питання та відповіді

Однією з вбудованих функцій Програми 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. Слідом за виконанням даної дії запускається вікно аргументів оператора сума , єдиним завданням якого є підсумовування зазначених значень. Синтаксис цієї функції дуже простий:

   =sum(число1; число2;...)

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

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


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

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

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


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

11. Знову відкривається вікно аргументів оператора ДВСИЛ . Заносимо в поле " посилання на клітинку» адреса елемента на аркуші, де розташоване найменування місяця, який завершує розрахунковий період. Знову координати повинні бути вписані без лапок. Поле «A1» знову залишаємо порожнім. Після цього клацаємо по кнопці «OK» .

12. Як бачимо, після даних дій програма проводить розрахунок і видає результат складання доходу підприємства за вказаний період (березень &8212; Травень) в попередньо виділений елемент листа, в якому розташовується сама формула.

13. Якщо ми поміняємо в осередках, де вписані найменування місяців початку і кінця розрахункового періоду, на інші, наприклад на «Червень» і «Листопад» , то і результат зміниться відповідно. Буде складена сума доходу за вказаний період часу.

Урок: як порахувати суму в Екселі

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