Один з читачів нашого сайту SirExcel задав питання, яке може зустрічатися дуже часто. Питання полягало в наступному «Як зробити так, щоб на одному аркуші показувалася інформація з певної комірки наприклад $ A $ 1 але з кожного листа з книги? Листів дуже багато треба зробити зведений список вмісту певної осередку з усіх листів. »
Я як раз збирався написати про те, як працює функція ДВССИЛ, чому б не розглянути її саме на цьому прикладі.
Отже, ми маємо файл Excel, в якому багато листів. Давайте розглянемо простий приклад, де назви листів у нас не мінялися. Тобто у нас є Лист1, Лист2, Ліст3 і так далі.
Припустимо, що на одному аркуші (Лист1-Ліст5) у нас є дані по виручці 5 різних магазинів у вигляді однакових таблиць даних.
На аркуші 6 є результуюча таблиця, де нам необхідно заповнити її даними, взявши їх з усіх листів.
Таким чином, щоб заповнити дані за січень нам необхідно взяти дані по виручці магазинів, що знаходяться в осередку B2, але на 5 різних листів.
Звичайно, в нашому прикладі це можна було б зробити в ручну, вказавши просто посилання на відповідні значення, але якщо листів дуже багато, або даних дуже багато, то вручну це зробити буде дуже важко.
Існує різні способи вирішити це завдання, наприклад, за допомогою простенького макросу, який можна записати макрорекодером. Ну а ми з вами для вирішення завдання буде використовувати функцію ДВССИЛ.
ДВССИЛ - функція, яка повертає посилання задану текстовим рядком.
Щоб зрозуміти принцип дії даної функцію, давайте розглянемо такий приклад.
Подивіться на малюнок нижче. В осередку А1 написаний текст D4, а в самій комірці D4 вказано число 9999. Якщо ми напишемо формулу ДВССИЛ (A1), то в результаті отримаємо число 9999. Те ж саме ми отримаємо якщо напишемо формулу так:
= ДВССИЛ ( "D4")
Тобто ми написали текстом адреса комірки D4 і функція ДВССИЛ повернула нам те значення, яке знаходиться за адресою (D4).
Сподіваюся, все стало більш зрозумілим. Тепер давайте розглянемо на прикладі. Щоб заповнити дані за січень з різних листів нам необхідно прописати наступні посилання на комірки.
= Лист1! B2 (Виручка за січень магазину 1)
= Аркуш2! В2 (Виручка за січень магазину 2) і т.д.
Але складність полягає в тому, що якщо осередок просто простягнути вниз, то Аркуш1 не змінюватиметься на Аркуш2 і так далі.
Якщо ми пропишемо формулу
ДВCСИЛ ( "Лист1! B2"), ДВCСИЛ ( "Аркуш2! B2")
і так далі, то функція буде повертати нам те ж саме, але це так само не вирішує нашу проблему, адже «Лист1! B2» прописаний звичайним текстом і так само не буде змінюватися при протягуванні.
Для вирішення нашої задачі ми розділимо текст «Лист1! B2» на дві частини (окремо «Лист1» і окремо «! B2» - зверніть увагу на знак оклику у другій частині тексту) і потім їх склеим. Текст Лист1, Лист2 і так далі ми пропишемо навпаки відповідних магазинів, при цьому, написавши Лист1, ми зможемо просто простягнути осередок вниз і автоматично отримати список Лист1, Лист2 і так далі.
тепер зчепилися цей текст всередині функції ДВССИЛ за допомогою знака & і отримаємо формулу, яка буде автоматично простягатися і при цьому будуть підтягуватися дані з однієї і тієї ж осередки, але різних листів.
= ДВССИЛ (A2 & "! B2")
Підсумкова формула виглядає саме так, але я спеціально розписав з самого початку по кроках, щоб було зрозуміло з чого складається ця формула. В принципі, цього вже достатньо, щоб за короткий термін вирішити задачу, зазначену на початку статті. Щоб заповнити дані за лютий, нам необхідно в комірці D2 написати таку ж формулу, але поміняти текст «! B2» на "! B3"
= ДВССИЛ (A2 & "! B3")
і протягнути вниз, аналогічно за березень.
Але ніхто нам не заважає трохи ускладнити формулу і заповнити її автоматично, простягнувши формулу вниз, а потім вправо. Тут я не буду детально описувати, але якщо ви розібралися з попереднім прикладом, то легко зрозумієте і даний.
Отже, ми вирішили задачу, як швидко заповнити дані за певний місяць, але нам необхідно заповнити дані за інші місяці. Ми знаємо, що дані за січень відповідають осередку B2, лютий - B3, Березень - B4. Тобто буква «B» залишається незмінною, змінюється лише цифра. Знаючи це ми просто розіб'ємо текст всередині посилання ДВССИЛ нема на 2 частини, а на три. Додамо додатково рядок над таблицею і напишемо зверху цифри відповідні осередки місяці. Пропишемо формулу.
= ДВССИЛ (A3 & «! B» & C1), де A3 - це текст «Лист1», «! B» - це незмінний текст і С1 - це цифра 2. Все це об'єднано за допомогою знака & в загальний текст "Лист1! B 2". Нам також потрібно закріпити стовпець A (за допомогою знака $), щоб він не змінювався при протягуванні формули вправо і рядок 1, щоб вона не змінювалася при протягуванні формули вниз. Ми отримуємо наступну підсумкову формулу.
= ДВССИЛ ($ A3 & "! B" & C $ 1)
яку можна протягнути вправо і вниз.
Нотатки на тему:
- Якби листи називалися у нас Магазин 1, Магазин 2 і так далі, то додатковий стовпець зі словами Лист1, Лист2 і так далі нам би не потрібен був.
- Функцію ДВССИЛ використовують часто тоді, коли потрібно змінити посилання на комірку у формулі, не змінюючи саму формулу.
Якщо вам сподобалася стаття, будь ласка, натисніть +1 і «Мені подобається». Так само підписуйтесь на нашу розсилку або вступайте в нашу групу ВКонтакте, щоб не пропустити наші наступні уроки по Excel
SirExcel - безмежні можливості Excel