Ребята, всем привет! 👋 В прошлом уроке мы рассмотрели один из способов обработки и анализа базы данных который состоит в подведении различных итогов. Но на практике встречаются ситуации когда требуется провести анализ данных из нескольких диапазонов.
В решении данной задачи нам поможет консолидация данных.
✨ А прежде, чем мы начнем 📣 напомню, теперь все видео 📽 предыдущих уроков доступны в 👉 одном месте.
Консолидация – это объединение значений из нескольких диапазонов в один новый диапазон с выполнением операции.
🔔 Что важно! Объединяемые диапазоны могут находиться как на разных листах, так и в разных книгах.
📝 Изучение рассмотрим на примере.
👉 Пусть мы имеем таблицы содержащие сведения по числу продаж менеджеров, которые находятся на разных листах. Причем:
- сумма продаж по товару, руб определяется как =[@[Число продаж в месяц]]*[@[Сумма за ед.]]
- а заработная плата менеджера как =([@[Сумма продаж по товару, руб]]*[@[% менеджера]])/100, где % менеджера = 10 %
⚠️ Обратите внимание:
🔴 Названия колонок должны быть одинаковыми. При этом допускаются небольшие отклонения от изначального формата.
🔴 Не должно быть ни одной ячейки, которая не содержала бы никаких значений.
🔴 В целом, все таблицы должны быть организованы приблизительно по одинаковому шаблону.
Чтобы выполнить консолидацию данных необходимо:
📍 ШАГ 1. Выделить пустую ячейку листа, начиная с которой будет размещен консолидируемый диапазон
📍 ШАГ 2. На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Консолидация [Consolidate]
📍 ШАГ 3. В окне Консолидация [Consolidate] задать параметры:
- В поле Функция [Function] выбрать функцию, которая будет применена к объединяемым данным.
Например, определим (по каждому менеджеру):
- суммарное количество сделок ;
- общую сумму продаж;
- з/п за месяц:
- Поставить курсор в поле Ссылка [Reference] и выделить первый диапазон консолидации вместе с заголовками, нажать Добавить [Add]:
- Далее повторить для всех исходных диапазонов:
Чаще всего исходные диапазоны выделяются вместе с заголовками, особенно если порядок расположения данных может отличаться.
👉 В случае если в источнике присутствуют объединенные ячейки в строке заголовка, то следует выделять без заголовков (заголовки потом копируются дополнительно).
- В группе Использовать в качестве имен [Use labels in] поставить флажки подписи верхней строки [Top row] (если исходные диапазоны были выделены с заголовками) и значения левого столбца [Left column].
- Если необходимо консолидировать таблицы, сохранив связь с исходными данными, то выбрать флажок Создавать связи с исходными данными [Create links to source data].
В этом случае при изменении данных в исходных таблицах, консолидированная таблица будет также изменяться.
🔔 НО!.. эту связь можно установить при условии, что консолидированная таблица находится на отдельном листе от исходных данных
🔔 Что еще важно помнить!
Обновление происходит связанных ячеек!
- если в исходных таблицах будут изменяться критерии (например, изменится наименование товара) или изменится количество строк (записей), то необходимо построить консолидацию заново.
- если исходные данные находятся в других файлах, то предварительно требуется открыть все эти файлы, а потом выполнять команды консолидации (при этом после добавления выделенного диапазона необходимо очищать поле Ссылка, чтобы иметь возможность выделять следующий диапазон).
📍 ШАГ 4. Жмем 🆗. И получаем результат
Причем здесь Вы можете наблюдать, что в таблице появились элементы управления:
Подробно элементы управления мы разбирали в предыдущем уроке.
Аналогично, мы можем определить число продаж (сделок) по каждому товару и общую сумму:
Применив инструмент быстрого анализа получаем итоговую сумму:
🏆 Подводя итог ...
Основными преимуществами использования инструментов для работы с большими таблицами являются:
- ☑️ преобразование диапазона ячеек в таблицу позволяет быстро переключаться между различными стилями оформления, быстро выполнять расчет новых данных по формулам, а так же осуществлять расчет итогов только для видимых строк.
- ☑️ сортировка данных помогает быстро упорядочить данные, чтобы лучше понимать их, организовывать и находить необходимую информацию, а в итоге – принимать более эффективные решения.
- ☑️ фильтрация данных, особенно срезы, помогают быстро получить нужные сведения из таблицы, отвечающие заданным условиям.
- ☑️ промежуточные итоги позволяют моментально произвести расчет определенных столбцов таблицы с группировкой данных по столбцу, по которому подводятся итоги;
- ☑️ консолидация помогает быстро решать задачи объединения данных, находящихся в разных диапазонах в один с выполнением расчетного действия.
А на этом сегодня все. Теперь работа с большими табличными массивами будет вам под силу 👏 Продолжение следует...
Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
В следующих уроках более подробно рассмотрим:
☑ анализ данных с помощью сводных таблиц, включая:
- создание и преобразование сводных таблиц;
- фильтры, срезы, временные шкалы в сводных таблицах;
- настройку полей сводной таблицы
и др.
За лайк 👍 и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!
#консолидация данных в excel #преобразование диапазона в таблицу #сортировка данных #фильтры в excel #обучение excel #фишки excel #срезы в excel #таблицы excel #как сделать анализ базы данных в excel