Продолжаем разбирать приемы Excel с функцией ДВССЫЛ. Предыдущая часть статьи - тут.
4. Сбор данных с разных листов
Разберем пару примеров на эту тему.
4.1. Сбор данных с разных листов - первый пример.
Имеем файл с пятью одинаковыми по структуре расположения данных листами. Каждый лист назван в соответствии с годом (2015, 2016 и т.д.).
Задача: на сводном листе собрать все данные в одну таблицу с помощью функции ДВССЫЛ.
Решение:
Первое, на что нужно обратить внимание - на всех листах информация должна быть расположена идентично (один и тот же порядок строк, расположение в одном и том же диапазоне ячеек). Если бы это условие не выполнялось, пришлось бы дополнительно колдовать.
Итак, собирать данные будем в аналогичную таблицу, в которой добавим справа столбцы в соответствии с годом.
Наша задача прописать формулу в ячейку B2 (первая заполняемая ячейка) таким образом, чтобы она корректно работала при протягивании вниз и влево:
- при протягивании вниз должен меняться номер строки. Для этого воспользуемся функцией СТРОКА (ROW). Оставим её аргументы пустыми, чтобы она определяла номер строки текущей ячейки.
- при протягивании вправо должна меняться ссылка на лист. В нашем случае листы именованы годами, как и шапка таблицы, поэтому можем ссылаться на год из шапки. Не забываем зафиксировать в ссылке номер строки, чтобы при протягивании вниз она не менялась (знак $ для абсолютной ссылки)
Наше решение:
Еще раз посмотрим, что получилось:
=ДВССЫЛ("'"&B$1&"'!B"&СТРОКА())
Чтобы понять логику, достаточно самостоятельно выполнить все действия внутри скобок. Написанное выше, Эксель поймет так:
=ДВССЫЛ('2015'!B2)
Причем при протягивании вправо будет меняться год, а при протягивании вниз - номер строки.
4.2. Сбор данных с разных листов - второй пример (выбираем лист)
Предположим, что задача немного изменилась. Листы с исходными данными скрыты. Теперь нужно выводить на сводный лист данные не со всех листов, а по выбору только с одного.
В этом случае прекрасно работает связка с выпадающим списком. Не буду останавливаться на том, как его создать (об этом есть отдельная статься на канале - в конце статьи оставлю на нее ссылку), поэтому сразу к решению:
- В ячейке B1 создаем выпадающий список с элементами 2015, 2016, 2017, 2018, 2019.
- Формулу прописываем так же, как в предыдущем примере.
Теперь при выборе года в ячейке B1 данные в таблице будут обновляться в соответствии со сделанным выбором.
4.3. Сбор данных с разных листов - второй пример (выбираем, какие данные будем собирать)
Чуть усложним задачу: теперь нам нужно собрать на одном листе информацию с каждого, но только по одному выбранному нами критерию (например, только количество или только стоимость).
В этом случае в связку с ДВССЫЛ и выпадающим списком добавим функцию ВПР(VLOOKUP). Функция также ранее разбиралась в другой статье, поэтому подробно на ней не останавливаюсь).
Решение:
- первое - в ячейке A2 создаем выпадающий список, где элементами будут выступать наименования данных из таблиц (количество продаж, общая стоимость, средняя цена и т.д.)
- В ячейке B2 прописываем функцию ВПР так, чтобы диапазон поиска формировался через ДВССЫЛ
Здесь мы используем ДВССЫЛ для смены диапазона поиска (смены года). Признаем, что это не самое элегантное решение задачи, но быть знакомыми с такой возможностью однозначно стоит.
Отмечу и один несомненный плюс: часто бывает, что для некоторых сотрудников не очевидна необходимость соблюдения заданных форматов. Например, при взгляде на данные по одному году не каждый сочтет важным соблюдать порядок строк. В случае, если он на разных листах разный, то пример 4.1 выдаст неверные результаты, а для 4.3 - это не будет проблемой.
Подписывайтесь, задавайте вопросы - разберемся вместе со всем.
Ссылки по теме:
Статья про выпадающий список: ссылка
Статья про функцию ВПР (VLOOKUP): ссылка