Приветствуем, дорогие читатели! Сегодня будет статья для тех, кто устал собирать данные с разных листов и таблиц вручную. Знаете эту боль: продажи по месяцам разбросаны по разным кускам отчёта, менеджеры прислали файлы кто в лес, кто по дрова, а вам нужно одну общую, красивую и полную таблицу.
Мы покажем вам кнопку-невидимку, о которой знают единицы, но когда узнают — молятся на неё.
И да, никаких сложных сводных таблиц, Power Query и макросов. Всё делается мышкой за 4 шага.
Но как автоматически объединять файлы рассказали в статье про Power Query.
Исходные данные
Представьте, что у нас есть 4 разных таблицы с продажами по продуктам. В одной — продажи за январь, март и май. Во второй — за июль, ноябрь, декабрь. В третьей — ещё что-то. Месяца разрозненные, списки продуктов не совпадают, где-то есть «Монитор», где-то его нет.
Выглядит страшно? Обычно люди начинают копировать строчки, искать ВПР и т.д. А мы покажем самый простой способ.
Шаг 1. Ставим курсор туда, где будет «всё вместе»
Создайте на листе пустую таблицу или просто выберите одну пустую ячейку, с которой начнётся ваш главный отчёт. Например, ячейку A1 или новый лист.
Важно: вокруг неё не должно быть достаточно свободных ячеек, чтобы Excel случайно ничего не затёр.
Шаг 2. Идём во вкладку «Данные»
Открываете вкладку «Данные» (она есть в любой версии Excel — 2016, 2019, 2021, 365). В самом конце, в группе "Работа с данными", находится кнопка, которую многие никогда не нажимают. Называется она "Консолидация" .
Жмите на неё без страха. Откроется окошко, которое выглядит сложным, но на самом деле очень простое.
Шаг 3. Добавляем диапазоны
В окне "Консолидация" по умолчанию выбрана функция "Сумма" (нам она и нужна — будем суммировать продажи по одинаковым продуктам).
А теперь самое интересное — поле "Ссылка" :
- Нажимаете на маленькую стрелочку справа (или просто кликаете в поле).
- Мышкой выделяете первую таблицу на листе — вместе с шапкой (названия месяцев) и левым столбцом (названия продуктов). Обратно в меню через ту же стрелку или Enter.
- Нажимаете кнопку "Добавить". Диапазон появляется в списке внизу.
Повторяете то же самое для второй, третьей и четвёртой таблицы.
Главное правило: у всех таблиц должна быть похожая структура — первый столбец с названиями продуктов, первая строка с названиями месяцев.
Шаг 4. Ставим две галочки — и готово!
Смотрите, здесь кроется 99% успеха. Если их не поставить, Excel просто сложит всё в кучу без шапок. Обязательно отмечаем внизу окна:
- "подписи верхней строки" (чтобы Excel понял, что первая строка — это названия месяцев).
- "значения левого столбца" (чтобы названия продуктов не разъехались).
Если хотите, чтобы при изменении исходных таблиц обновлялась и итоговая — поставьте ещё одну галочку "Создавать связи с исходными данными" (но это опционально).
После этого смело жмите "ОК" .
Что вы увидите?
А увидите вы чудо.
Excel сам найдёт все одинаковые названия продуктов, соберёт все уникальные месяцы из четырёх таблиц и сложит значения там, где они пересекаются. Там, где данных не было — поставит пустоту или ноль.
Итоговая таблица появляется ровно там, куда вы поставили курсор в самом начале. Никакого ручного копирования и никакой головной боли!
Бонус-совет
Этот приём работает не только с суммами. В выпадающем списке "Функция" можно выбрать:
- Среднее (если нужно среднее по одинаковым позициям)
- Количество (сколько раз встречается продукт)
- Максимум / Минимум (экстремальные продажи)
Так что инструмент на все случаи жизни!
Заключение
Друзья, мы искренне не понимаем, почему про "Консолидацию" так редко рассказывают. Это же просто спасательный круг для всех, кто работает с отчётами, прайсами, продажами, остатками — да чем угодно!
Попробуйте этот способ хотя бы раз — и вы забудете про мучительное «копировать-вставить» навсегда.
А вы раньше пользовались консолидацией? Или каждый раз собирали данные руками? Делитесь в комментариях, нам очень интересно!
И, конечно, ставьте лайк и подписывайтесь на канал — впереди ещё много фишек, которые заставят ваших коллег смотреть на вас с уважением 😉