Як в excel вказати посилання на одну і ту ж комірку, але з різних листів (функція двссил), sirexcel

Один з Новомосковсктелей нашого сайту SirExcel задав питання, яке може зустрічатися дуже часто. Питання полягало в наступному «Як зробити так, щоб на одному аркуші показувалася інформація з певної комірки наприклад $ A $ 1 але з кожного листа з книги? Листів дуже багато треба зробити зведений список вмісту певної осередку з усіх листів. »

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

Отже, ми маємо файл Excel, в якому багато листів. Давайте розглянемо простий приклад, де назви листів у нас не мінялися. Тобто у нас є Лист1, Лист2, Ліст3 і так далі.

Припустимо, що на одному аркуші (Лист1-Ліст5) у нас є дані по виручці 5 різних магазинів у вигляді однакових таблиць даних.

На аркуші 6 є результуюча таблиця, де нам необхідно заповнити її даними, взявши їх з усіх листів.

Звичайно, в нашому прикладі це можна було б зробити в ручну, вказавши просто посилання на відповідні значення, але якщо листів дуже багато, або даних дуже багато, то вручну це зробити буде дуже важко.

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

ДВССИЛ - функція, яка повертає посилання задану текстовим рядком.

Щоб зрозуміти принцип дії даної функцію, давайте розглянемо такий приклад.

Але складність полягає в тому, що якщо осередок просто простягнути вниз, то Аркуш1 не змінюватиметься на Аркуш2 і так далі.

Якщо ми пропишемо формулу
ДВCСИЛ ( "Лист1! B2"), ДВCСИЛ ( "Аркуш2! B2")
і так далі, то функція буде повертати нам те ж саме, але це так само не вирішує нашу проблему, адже «Лист1! B2» прописаний звичайним текстом і так само не буде змінюватися при протягуванні.

Для вирішення нашої задачі ми розділимо текст «Лист1! B2» на дві частини (окремо «Лист1» і окремо «! B2» - зверніть увагу на знак оклику у другій частині тексту) і потім їх склеим. Текст Лист1, Лист2 і так далі ми пропишемо навпаки відповідних магазинів, при цьому, написавши Лист1, ми зможемо просто простягнути осередок вниз і автоматично отримати список Лист1, Лист2 і так далі.

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

Але ніхто нам не заважає трохи ускладнити формулу і заповнити її автоматично, простягнувши формулу вниз, а потім вправо. Тут я не буду детально описувати, але якщо ви розібралися з попереднім прикладом, то легко зрозумієте і даний.

= ДВССИЛ (A3 «! B» C1), де A3 - це текст «Лист1». «! B» - це незмінний текст і С1 - це цифра 2. Все це об'єднано за допомогою знака до загального текст "Лист1! B 2". Нам також потрібно закріпити стовпець A (за допомогою знака $), щоб він не змінювався при протягуванні формули вправо і рядок 1, щоб вона не змінювалася при протягуванні формули вниз. Ми отримуємо наступну підсумкову формулу.

= ДВССИЛ ($ A3 "! B" C $ 1)
яку можна протягнути вправо і вниз.

Нотатки на тему:

• Якби листи називалися у нас Магазин 1, Магазин 2 і так далі, то додатковий стовпець зі словами Лист1, Лист2 і так далі нам би не потрібен був.
• Функцію ДВССИЛ використовують часто тоді, коли потрібно змінити посилання на комірку у формулі, не змінюючи саму формулу.

SirExcel - безмежні можливості Excel

Спасибі, все дуже дохідливо!

Немає часу вникати в суть. В цілому буде навпаки

Якщо надішліть приклад, можна буде подивитися коли буде час