[МУЗЫКА] [МУЗЫКА] Сегодня мы с вами рассмотрим следующий пример. В нашей компании товар — она торгует чаем — хранится на нескольких складов. В частности, у нас два склада: склад 1 — мы видим таблицу, в которой указано, сколько товара хранится на этом складе, и склад 2. Нам необходимо собрать эти данные вместе, чтобы получить итоговое значение, количество товара на каждом складе. В Excel такая задача решается с помощью функции консолидации. Она находится на вкладке «Данные», в группе «Работа с данными» — «Консолидация». Однако эта функция имеет некоторые недостатки, в частности она не работает с интерактивными данные, то есть если мы вносим какие-то изменения в наши исходные данные. Давайте посмотрим, как решить эту задачу с помощью надстройки Power Query. Сегодня для удобства мы будем работать в том же самом файле, где находится наши исходные данные. Поэтому наши таблицы необходимо превратить в умные. Переходим на вкладку «Главная», встаем в любое место таблицы — «Форматировать как таблицу», выбираем ее дизайн. Проверяем, что табличка захватилась правильно. OK. И назовем ее не «Таблица 1», а «Склад 1». [ШУМ] Точно так же для второй таблицы: «Форматировать как таблицу», OK, и назовем ее «Склад 2». Переходим во вкладку Power Query и загружаем данные. Сначала «Склад 1», встаем в любое место, «Из таблицы или диапазона». [ШУМ] Итак, загрузилась наша таблица. Выбираем на вкладке «Главная» «Закрыть и загрузить в» и создаем подключение к книге — «Создать только подключение». И «Загрузить». Точно так же склад 2 — встаем в любое место таблицы, вкладка Power Query, «Из таблицы или диапазона». Наша таблица загрузилась, «Закрыть и загрузить в», «Создаем только подключение». «Загрузить». Теперь необходимо эти данные собрать в единый отчет. Итак, в группе «Объединить» выбираем команду «Добавить». Первичная таблица, это будет склад 1. И вторая таблица — это склад 2. Назовем ее «Сумма со складов». Итак, у нас пока просто данные собрались — одна табличка под другой, мы видим, что у нас повторяются, например, вот фруктовый чай «Апельсин». На первом складе у нас было три наименования, во втором — семь. Теперь нам нужно эти данные собрать вместе. Для этого во вкладке «Преобразования» мы выбираем команду — сейчас мы ее найдем. Так, не вижу. А вот у нас есть «Таблица», группа, раскрываем ее, выбираем команду «Группировать по». Мы будем группировать по категории, и выбираем, поставим точечку в группе «Дополнительно». Тогда у нас появляется возможность добавить новую команду группировки. Добавляем и выбираем наименование товара. Итак, теперь у нас группировка будет не только по категории, но и наименованию товара. То есть если эти два поля совпадают оба, тогда мы можем группировать. Выбираем «Сумма», для остатка по складку. И имя нового столбца — «Количество» нас устраивает. Нажимаем OK. И теперь вкладка «Главная», «Закрыть и загрузить». Мы получаем итоговый отчет, в котором имеются все наименования товаров со склада, причем товары просуммировались, находящиеся те, которые имеются и на первом, и на втором складах. Но, может быть, что такой отчет нам будет не совсем удобен. Возможно, мы хотим видеть отдельно количество товаров с первого склада, количество товаров второго склада и итоговое количество товаров. Давайте решим эту задачу. Перейдем обратно в окно Power Query. И давайте, например, выберем «Сумма со складов», правой клавишей мыши нажмем и выберем команду «Изменить». Мы тогда перешли в рабочее окно Power Query и зайдем к запросам этой книги. Нам необходимо тогда, например, в запросе «Склад 1» изменить название категории «Остаток». Но для того чтобы это не испортило наш итоговый отчет мы этот запрос нажмем правой клавишей мышки и выберем команду «Дублировать». То есть теперь у нас отчет о товаре склада 1 продублирован. И здесь мы столбец «Остаток» переименуем и назовем «Остаток 1». «Закрыть и загрузить в». Создаем только подключение. И теперь вкладка Power Query — мы опять выбираем команду «Добавить». В данном случае мы выбираем склад 1 (второй), склад 2, OK. И обратите внимание, в итоговом отчете у нас появилось два столбца: «Остаток 1» и «Остаток 2». Мы опять точно так же выбираем вкладку «Преобразование», группа «Таблица», «Группировать по». И так же, как в предыдущем случае, выбираем «Категория», «Наименование товара». И теперь нам нужно агрегирование по двум столбцам. Первый столбец будет «Склад 1» и команда «Сумма», «Остаток 1». Вторая: «Склад 2», агрегирующая операция — это «Сумма». И столбец «Остаток». Нажимаем OK. И обратим внимание, теперь у нас те товары, которые можно найти, есть у нас, содержатся в «Склад 1» и «Склад 2». Они встречаются дважды в нашем столбце. Обратим внимание, что в Power Query пустые строки не воспринимаются как нулевые значение, в отличие от Excel. Поэтому мы выбираем столбец «Склад 1», вкладка «Преобразование» и замена значений. И заменим пустые значения нулевыми. То же самое для второго склада. Пустые значения заменим на нулевые. OK. И теперь уже можно добавить пользовательский столбец. Мы назовем его «Итого». Так. Склад 1 + Склад 2. OK. И нам осталось только сначал переименовать. Назовем это «Отчет 2». И загрузить наши новые получившиеся данные. Итак, мы с вами получили два интерактивных отчета. В первом данные с обоих складов просто просуммированы — единый отчет. А второй отчет содержит более детальные данные. При этом эти оба отчета впитывают в себя все изменения наших исходных данных.
