Посилання на інший аркуш за допомогою ДВССЫЛ

Це глава з книги Білла Джелу Гуру Excel розширюють горизонти: робіть неможливе з Microsoft Excel . Попередня глава книги знайомить з функцією ДВССЫЛ: Посилання на осередок, чию адресу заснований на обчисленнях .

Завдання: вам потрібно отримати значення осередки B4 з одного з багатьох листів. При цьому, визначити, з якого саме листа ви можете на підставі розрахунку. Чи може ДВССЫЛ посилатися на інший аркуш?

Рішення: ДВССЫЛможе посилатися на інший аркуш. Але функція вимагає особливого поводження, якщо ім’я листа містить прогалини або дату. Якщо лист містить пробіл в імені, ви повинні скласти посилання на лист, використовуючи апострофи навколо імені листа, потім знак оклику та адресу клітинки (див. Також Сума однієї і тієї ж осередки на кількох аркушах ). Наприклад, = ' Прибутки та збитки » ! В2. Якщо ім’я листа не містить прогалини, ви можете обійтися без апострофів: = Доходи! В2. Якщо у вас суміш імен аркушів, деякі з яких містять прогалини, у формулі ви повинні спланувати апострофи (рис. 1).

Мал.  1. ДВССИЛ посилається на змінний лист

Мал. 1. ДВССЫЛ посилається на змінний лист

 

Формула в комірці F4: = ДВССЫЛ ( " ' " & F $ 3 & " ' ! " & " B4 " ). Перший частокіл з апострофів — це відкривають лапки " + апостроф ' + закривають лапки " . Другий частокіл — це відкривають лапки " + два символи: апостроф і знак оклику ' + закривають лапки! " .

Проблема з формулами в четвертому рядку в тому, що вони жорстко посилаються на В4, тому при копіюванні в рядки з 5 по 7 вони стають не доречними. Щоб формули могли посилатися на дані з інших рядків, можна використовувати функції ОСЕРЕДОК або АДРЕСА. Ви можете пристосувати функцію ОСЕРЕДОК в лічені секунди, але функція АДРЕСА може, в кінцевому рахунку, виявитися більш зручною, як тільки ви зрозумієте нюанси її використання.

Фрагмент формули ОСЕРЕДОК ( " адреса " ; $ B5) повертає текст $ В $ 5, що вдало підходить для аргументу функції ДВССЫЛ (рис. 2). Знак долара перед буквою В подбає про те, щоб формула на всіх аркушах посилалася на стовпець В. Відсутність знака долара перед цифрою 5 дозволить при копіюванні формули вниз по стовпчику посилатися на рядки 5, 6, 7 … Формула в комірці F5: = ДВССЫЛ( " ' " & F $ 3 & " ' ! " & ОСЕРЕДОК ( "адреса " ; $ B5)).

Мал.  2. Додавання функції ОСЕРЕДОК дозволяє скопіювати формулу на всю таблицю

Мал. 2. Додавання функції ОСЕРЕДОК дозволяє скопіювати формулу на всю таблицю

Ви можете використовувати функцію АДРЕСА замість функції ОСЕРЕДОК. У своїй простій формі -Адреса (номер_рядка; номер_стовпчика) — функція повертає адресу осередки. Наприклад, = АДРЕСА (5, 2) повертає текст $ В $ 5. Може здатися дивним писати = АДРЕСА (СТРОКА (), 2) замість більш простого і короткого ОСЕРЕДОК ( "адреса " ; $ B5), коли ви хочете послатися на стовпець B в поточному рядку. Однак, функція АДРЕСА має три додаткових необов’язкових аргументу.

Примітка: третій і четвертий аргументи функції АДРЕСА в цій темі вас не цікавлять, але ви повинні розібратися з ними, щоб дістатися до п’ятого аргументу.

Третій аргумент визначає, тип посилання, тобто, які посилання будуть абсолютними, а які відносними (докладніше див. Відносні, абсолютні і змішані посилання на комірки в Excel ). Ось простий спосіб запам’ятати, як працює цей аргумент. Число в аргументі відповідає тому, скільки разів ви натисніть клавішу F4, щоб домогтися комбінації знаків долара:

  • АДРЕСА (5; 2; 1) — фіксує, як стовпець, так і рядок, і повертає $ B $ 5;
  • АДРЕСА (5; 2; 1) — фіксує тільки рядок, і повертає B $ 5;
  • АДРЕСА (5; 2; 1) — фіксує тільки стовпець, і повертає $ B5;
  • АДРЕСА (5; 2; 1) — залишає обидві посилання відносними, і повертає B5.

Четвертий аргумент визначає стиль посилання:

  • АДРЕСА (5; 2; 1; 1) — повертає посилання в стилі А1 — $ В $ 5;
  • АДРЕСА (5; 2; 1; 0) — повертає посилання в стилі R1C1 — R5C2.

П’ятий аргумент визначає ім’я листа. Excel сам проаналізує синтаксис імені, і визначить, чи потрібні апострофи:

  • = АДРЕСА (5; 2; 4; 1; " Атланта " ) Atlanta B5 поверне !;
  • = АДРЕСА (5; 2; 4; 1; " Eden Prairie " ) поверне ' Eden Prairie ' ! B5

Примітка: насправді ви не зобов’язані пам’ятати, які параметри потрібні для третього і четвертого аргументів функції АДРЕСА. Просто пропустіть їх (рис. 3). Наприклад, формула = АДРЕСА (5; 2 ;;; " Eden Prairie " ) поверне значення ' Eden Prairie ' ! $ B $ 5.

Мал.  3. Аргументи функції АДРЕСА

Мал. 3. Аргументи функції АДРЕСА

Функція АДРЕСА з використанням п’ятого аргументу повертає текст, який може бути використаний у функції ДВССЫЛ (рис. 4). Формула в комірці D6: = ДВССЫЛ (АДРЕСА (СТРОКА (); 2 ;;; D $ 3)). Ця формула також може бути скопійована на всю таблицю.Перший аргумент в АДРЕСА — СТРОКА () — гарантує, що Excel витягує значення з рядка з тим же номером, що і формула. Другий аргумент фіксований — це 2. Тобто, завжди отримаєте стовпець B. Третій і четвертий аргументи опущені, що говорить функції АДРЕСА повернути абсолютні посилання в стилі А1. П’ятий аргумент містить знак долара тільки перед рядком, що збереже релевантність формули при її копіюванні уздовж стовпчика: при цьому буде збережуться посилання на ім’я листа (з третього рядка). При копіюванні ж уздовж рядка посилання буде змінюється з переходом від стовпчика до колонку (спробуйте в доданому файлі Excel).

Мал.  4. Альтернативний варіант з функцією АДРЕСА

Мал. 4. Альтернативний варіант з функцією АДРЕСА

Додаткові відомості: функція ДВССЫЛ може працювати і з посиланнями в стилі R1C1.Часто це спрощує створення формул. До сих пір ми в усіх прикладах функція ДВССЫЛ використовувала тільки перший аргумент (другий аргумент був опущений), наприклад, ДВССЫЛ ( " В4 " ). Ця формула поверне таке ж значення при використанні в якості другого аргументу значення ІСТИНА: ДВССЫЛ ( " В4 " ; ІСТИНА). Остання формула явно наказує функції ДВССЫЛ використовувати стиль посилань А1. Значення БРЕХНЯ як другий аргумент — ДВССЫЛ ( " В4 " ; БРЕХНЯ) — наказує інтерпретувати посилання в стилі R1C1.

Посилання = RC вказує на поточний рядок і поточний стовпець. Числі після R або C створює абсолютне посилання на певну рядок або стовпець. Так, наприклад, формула = RC2 посилається на стовпець B з того ж ряду. Якщо ви використовуєте посилання в стилі R1C1, вам не знадобляться функції ОСЕРЕДОК або АДРЕСА (рис. 5). Тут в осередку С7 формула = ДВССЫЛ ( " ' " & C $ 3 & " ' ! RC2 " ; БРЕХНЯ). Ця формула також може бути скопійована по всій таблиці. Зверніть увагу, що вам не доведеться перемикатися на використання на аркуші посилань в стилі R1C1 для того, щоб скористатися цією формулою.

Мал.  5. Стиль R1C1 не популярний, але він, безумовно, значно полегшує написання формул

Мал. 5. Стиль R1C1 не популярний, але він, безумовно, значно полегшує написання формул

Джерело: http://baguzin.ru/wp/?p=12907

Прокоментуйте першими

Залишити коментар

Ваш e-mail не буде публікуватись.

*