Power query собрать данные из нескольких листов

Добавил пользователь Владимир З.
Обновлено: 19.09.2024

Проблема не в том, что у нас нехватка данных, проблема в том, что у нас их слишком много; что просто уже сложно найти суть в этих данных. Вам нужен способ переорганизации и очистки ваших данных, чтобы сделать их полезными. Microsoft Excel это очень удобный инструмент, но вам всё равно нужно почистить данные, прежде чем их можно будет анализировать и просматривать.

Зачем преобразовывать столбцы в строки?

Прежде чем мы начнём, вам может быть интересно, почему полезно использовать данные, которые расположены в столбцах и преобразовать их в строки.

Ответ немного технический. В принципе, предпочтительнее, если каждая строка данных подобна записи, единственному указателю данных. Данные в столбцах смешиваются по несколькими атрибутами, распределенным в столбцах.

В приведенном ниже примере вы можете увидеть, как выглядят данные в Excel, если преобразовать их из столбцов в строки. С левой стороны, находится набор записей о клиентах и разными типами проектов в столбцах. С правой стороны я отменил свёртывание данных и конвертировал их в формат строки.

Преобразование столбцов в строки в Microsoft Excel.

Кроме того, если вы пользуетесь сводными таблицами, очень важно, чтобы ваши данные были в строках, а не столбцах, при создании сводной таблицы. Просто данные в столбцах загружаются в сводные таблицы не правильно.

Вкратце: преобразование данных из столбцов в строки облегчает работу. Power Query делает это запросто, в пару кликов. Давайте узнаем, как:

Как быстро преобразовать столбцы в строки в Excel (смотри и учись)

Если вы никогда раньше не использовали Power Query, я настоятельно рекомендую посмотреть этот быстрый двухминутный видеоурок ниже, чтобы посмотреть, как я работаю с ним. Вы узнаете, как преобразовать ваши данные в столбцах в строки (записи) в Excel, чтобы упростить работу с ними.

Знакомство с Microsoft Power Query для Excel

Power Query — это мощный инструмент, который располагается в Excel. Вместо того, чтобы вручную переупорядочивать данные и повторять этот процесс, Power Query позволяет вам создать набор шагов, которые вы можете применить к данным повторно.

Установка Power Query

Power Query встроен в Excel 2016, но вы можете установить его для более ранних версий Excel в качестве надстройки. К сожалению, Power Query доступен только для Excel в Windows.

Чтобы установить Power Query для Excel в Windows (требуется только для Excel 2013 и Excel 2010, поскольку эта функция встроена в Excel 2016) перейдите на веб-сайт Microsoft, чтобы загрузить и установить надстройку Power Query.

Отправка данных в Power Query

Power Query используется в Excel, но он открывается в новом окне, которое находится поверх Excel.

Чтобы отменить свёртывание данные в Excel, вам сначала необходимо конвертировать данные Excel в таблицу, если они ещë не в этом формате. Для работы с данными, вы можете передать в Power Query таблицу данных.

Если вы хотите следовать со мной в этом уроке, я сделал пример данных в этом файле Excel, который доступен бесплатно.

Преобразование в таблицу

Чтобы преобразовать данные в таблицу, кликните в любом месте таблицы, а затем найдите параметр Форматировать как таблицу, это находится на ленте Excel. Вы можете нажать на любую из миниатюр стилей, чтобы преобразовать ваши обычные ячейки в таблицу с даными.

Чтобы преобразовать ваши данные в таблицу, выберите кнопку Форматировать как таблицу на ленте Excel.

Передача в Power Query

Это отправляет данные в новое окно, которое открывается поверх Microsoft Excel. Это совершенно новое окно с множеством функций.

Отмена свертывания данных

На этом этапе вы должны увидеть что-то похожее на снимок экрана ниже. Это окно, в котором находятся инструменты, необходимые для отмены (или сброса данных).

Чтобы отменить свёртывание данных в Excel, выделите все столбцы, которые вы хотите сбросить. В принципе, Power Query преобразует каждый из этих столбцов в самостоятельные строки. Для данного здесь примера, я выделил каждый из столбцов с типом проекта:

Выделите все столбцы, которые вы хотите развернуть в строки, затем щелкните Отменить свёртывание столбцов, чуть повыше ваших данных.

Как только вы нажмете на Отменить свёртывание столбцов, Excel преобразует ваши данные из столбцов в строки. Каждая строка является самостоятельной записью, готовой перейти в сводную таблицу или подойдёт для таблицы данных.

Перевернутые данные появляются в строках вместо столбцов.

Эта особенность кажется волшебной. Теперь давайте вернем данные в Microsoft Excel, чтобы начать с ними работать.

Закрыть и загрузить

После того, как вы закроете и загрузите свой запрос, Excel поместит перевёрнутые данные на новый лист и, с правой стороны, покажет запрос, который мы только что построили.

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

Обновление запроса

А что если ваши исходные данные изменились? Если в исходные данные добавлено больше строк, вам не нужно повторять весь этот процесс, чтобы перевернуть эти строки.

Вместо повторного создания запроса с начала, вам необходимо использовать параметр Обновить данные. На скриншоте ниже, я добавлю совершенно новую строку с новым клиентом, к исходному источнику данных:

В этом примере, я добавил нового клиента в исходный набор данных, в выделенной строке.

Это произведёт возврат к исходной таблице и повторит шаги запроса. Поскольку мы добавили новые строки в нашу исходную таблицу данных, Power Query добавит эти новые строки в запрос и запустит по ним преобразование.

Это одна из моих любимых функций Power Query. Мы добавили данные в исходный исходный файл, но в один клик обновили и преобразовали новые данные.

Повторите и продолжайте дальнейшее обучение по использованию Microsoft Excel

Из этого урока вы узнали, как легко преобразовать столбцы в строки в Microsoft Excel. Power Query — это гибкий и надежный инструмент для получения и изменения данных на лету. Изучите другие приёмы Microsoft Excel из этих уроков на Envato Tuts+:

Если вы импортируете данные в Power Query или Power BI из файла Excel, обращаясь к листу целиком, будьте осторожны – вас может поджидать ловушка.

При подключении к стороннему файлу Excel нам доступны три варианта извлечения данных:

Таблица (форматированный как таблица диапазон ячеек на листе)
Именованный диапазон (диапазон ячеек, которому присвоено пользовательское имя)
Лист целиком

Однако очень часто нужные данные не находятся в форматированной таблице или именованном диапазоне, и преобразовать их в такой вид затруднительно. Причин может быть много, например, необходимо сохранить форматирование (объединение ячеек теряется при преобразовании в таблицу), либо файлов слишком много для ручного преобразования в нужный формат.

Данные с неразмеченного листа

Листы Excel доступны в качестве источника наравне с таблицами и именованными диапазонами

Как же Power Query определяет нужный диапазон данных? Ответ может быть достаточно очевиден, если у вас есть достаточный опыт программирования на VBA и вы хорошо знакомы с объектной моделью Excel (и ответ вас не обрадует). А именно, Power Query использует специальный диапазон ячеек листа, который называется UsedRange.

Непредсказуемый UsedRange

Если вы не настолько хорошо знакомы с VBA и UsedRange, ниже я привел пояснение того, как работает этот объект.

Мы не видим UsedRange в списке пользовательских имен, и не можем к нему обратиться иначе, как с помощью редактора Visual Basic. Чтобы узнать его адрес на текущем листе, нажмите Alt-F11, затем Ctrl-G, и в окошке Immediate введите следующую команду:

UsedRange – это диапазон, определенный автоматически на основе содержимого ячеек, их форматов и истории редактирования. Его левая верхняя ячейка определяется как пересечение:

самой верхней строки, имеющей какое-то значение, формулу или формат ячейки, и
самого левого столбца, имеющей какое-то значение, формулу или формат ячейки

Аналогично определяется правая нижняя ячейка UsedRange:

Диапазон включает в себя все использованные ячейки

Или, например, если в ячейке задать форматирование границ толстой линией, то это, как правило, приводит к включению в UsedRange дополнительно ячейки сверху от форматированной, но расширение диапазона вниз, вправо или влево произойдет не всегда:

Ничего не предвещало беды, мы только изменили толщину границы

Так где ловушка?

Такое непредсказуемое поведение UsedRange при импорте с неразмеченного листа в Power Query или Power BI необходимо учитывать. Нам вряд ли помешают пустые строки и столбцы, находящиеся после данных, но пустые строки и столбцы, находящиеся перед данными – настоящая проблема , которую нельзя упускать из виду.

Представьте, что у вас есть несколько (или много) файлов Excel, которые заполняют разные пользователи. Файлы имеют одинаковую структуру данных, по крайней мере, в той части, которую вы хотите извлечь. Например, искомые данные всегда находятся в диапазоне E3:H9 (4 столбца и 7 строк). Первые четыре столбца и первые две строки – пустые, не содержат значений (как на рисунках выше).

Чтобы добраться до нужного нам диапазона, мы используем подключение к папке, в которой лежат нужные нам файлы. В каждом из файлов нам нужно:

Выбрать конкретный лист,
Оставить только столбцы с пятого по восьмой (E:H),
Удалить первые две строки (так как данные начинаются с третьей строки),
Оставить первые 7 строк в получившейся таблице.

Обычно все эти шаги не вызывают больших затруднений. Мы можем написать пользовательскую функцию или воспользоваться встроенным механизмом комбинирования данных из нескольких источников (на основе примерного файла). Однако результат и в том и в другом случае может оказаться обескураживающим.

Начиная со второго шага, мы ориентируемся на структуру листа: нам нужны данные в столбцах 5-8, соответственно мы будем пытаться удалить первые 4 столбца. Однако, как мы уже увидели выше, диапазон данных, переданных в Power Query, может как включать, так и не включать пустые столбцы (в зависимости от того, были ли изменены ячейки в нем).

Соответственно, если Power Query загрузит лист начиная со столбца A, то нужно удалять четыре первых столбца – они пустые и лишние. Если же в первых 4 столбцах листа нет данных или форматирования (то есть они не попали в UsedRange), они не загрузятся в Power Query. Первым загруженным окажется пятый столбец E, с которого начинается нужный диапазон данных. Но тогда четыре первых столбца нельзя удалять !

То же самое касается и строк: даже если первые две строки не содержат значений, они могут быть загружены или не загружены в зависимости от того, включает ли их в себя UsedRange. Всегда удаляя первые две строки в редакторе запросов, мы рискуем случайно удалить и нужные нам данные. В результате дальнейшая обработка может привести к ошибкам или стать просто невозможной.

Как видите, на листе Sample4 импортируемый диапазон начинается в ячейке А1 – это четыре лишних пустых столбца. Если при разворачивании столбца [Data] мы оставим только 4 первых столбца (как нам предложит Power Query по умолчанию), данные с листа Sample4 полностью потеряются:

Куда делись данные с четвертого листа?

Если попросить Power Query загрузить все заголовки столбцов и развернуть таблицы полностью, то мы увидим, куда исчезли наши данные:

Данные совсем не там, где ожидалось их увидеть!

Резюме

Теперь вы можете представить глубину проблемы. Хорошо, если у нас есть какие-то признаки, по которым мы можем опознать левую верхнюю ячейку нужного диапазона. Однако если ее значение не фиксировано и может быть любым (например, изменяющаяся дата или имя менеджера), задача в рамках Power Query становится почти невыполнимой.

В любом случае, решить ее средствами Power Query на данный момент очень сложно. Даже если написать функцию, проверяющую первые столбцы/строки на пустоты и удаляющие их, пока не наткнемся на заполненную строку, мы не избавлены от ситуации, когда ненужные нам строки или столбцы на самом деле не пустые и содержат какие-то значения: тогда мы не сможем удалить строки/столбцы по признаку пустоты. Каким-то способом заставить Power Query получать данные, начиная с ячейки А1, на данный момент невозможно.

Если у вас есть возможность разметить данные на листе как таблицу или именованный диапазон, обязательно делайте это, не полагайтесь на импорт данных с листа.

Относительно практичный способ для предварительной обработки неразмеченных листов – вручную или посредством макроса присвоение имен нужным диапазонам. Это может быть сделано несколькими строками кода VBA, но подходит уже не для всех, и далеко не всегда возможно (что если нужный файл недоступен для редактирования?).

Я считаю использование UsedRange для определения диапазона импорта ошибочным решением. Power Query не различает форматы ячеек, и смысла в UsedRange никакого нет.

По моему мнению, необходимо, чтобы Power Query при импорте с неразмеченного листа брал данные начиная с ячейки А1 и до пересечения последней строки и последнего столбца, содержащих какие-либо значения. В таком случае мы можем гарантировано ориентироваться на начало блока, содержащего данные, и не будем брать лишние (пустые) столбцы и строки, идущие после последних заполненных ячеек. В крайнем случае, такой вариант импорта может быть опциональным, но это будет в любом случае лучше, чем сейчас.

PS. Если вы хотите поднять свои знания о Power Query для Excel и Power BI, и научиться применять этот инструмент правильно, очень рекомендую следующие книги:

Такой анализ выполняется в масштабе всей компании либо для отдельных центров финансовой активности (бизнес-единиц), проектов или направлений деятельности. Это позволяет уточнить, из-за каких именно структурных элементов бизнеса возникают отрицательные или положительные отклонения плановых статей бюджетов от фактических.

План-фактный анализ представляет собой сравнение плановых и фактических показателей финансового бюджета и выявление причин отклонений.

Исходные данные для план-фактного анализа

1) планируемые величины статей доходов и расходов (бюджет доходов и расходов БДР);

2) фактические величины статей доходов и расходов (Отчет по доходам и расходам).

Аналитика статей доходов и расходов приведена в табл. 1 в разрезе бизнес-единиц и направлений их деятельности.

Power Query — это надстройка, которая обеспечивает удобный поиск, трансформацию и обновление данных для аналитиков, дашборд[2]-профессионалов и других пользователей.

Power Query представляет собой новую вкладку в ленточном интерфейсе Excel, где можно импортировать, преобразовывать и объединять данные из различных источников.

Еще одно значительное преимущество Power Query в том, что таблицы (модели данных), сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия надстройки похож на связанные таблицы Excel.

Преимущества Power Query:

1. Можно использовать различные форматы исходных данных: xml, csv, xls, doc.

2. Можно загружать данные в таблицу Excel из различных источников:

Интернет;
базы данных SQL, Oracle, Access, IBM DB, Mysql, Sybase и т. д.;
веб-службы, протоколы, интерфейсы и облачные хранилища.

3. Возможность слияния нескольких таблиц в одну с установкой связей по определенным критериям

4. Поиск данных по каталогам.

Эти возможности используются также в надстройке Excel Power BI[3]., а также в надстройке Power Query Add-In, доступной для предыдущих версий Excel.

Основные этапы подключения и преобразования данных в Excel посредством Power Query представлены на схеме.

Несмотря на то что некоторые виды анализа применяют лишь некоторые из этих шагов, каждый шаг важен в процессе анализа и преобразования данных.

Подключение и преобразование данных

Чтобы использовать возможности надстройки Power Query, необходимо создать запрос (query) в рабочей книге Excel. Запрос позволяет подключить, просмотреть и преобразовать данные из различных источников. Затем преобразованные данные загружаются в таблицу (лист) Excel или во встроенную модель данных в Excel, далее при необходимости данные обновляются. Также есть возможности редактировать и пересылать сформированный запрос.

Этап 2. Формируем запросы для план-фактного анализа в Power Query

Шаг 3. Формируем запрос [БДР-план] в модели данных

Запрос [БДР-план] включает в себя следующие операции (рис. 1):

5) после подтверждения (нажимаем ОК) запрос [БДР план][4] загружается в модель данных[5].

Шаг 4. Формируем запрос [Отчет-факт] в модели данных

Запрос [Отчет-факт] включает следующие операции (аналогично запросу [БДР план]):

Этап 3. Объединяем запросы для план-фактного анализа в Power Query

[1] БДР (бюджета доходов и расходов), БДДС (бюджета движения денежных средств), ББЛ (бюджета по балансовому листу).

[2] Дашборд (англ. Dashboard) — аналитический инструмент, наглядное представление информации о бизнес-процессах, о состоянии какого-то объекта в виде динамических онлайн-диаграмм.

[3] Power BI — это комплексное программное обеспечение бизнес-анализа (BI) компании Microsoft, объединяющее несколько программных продуктов, имеющих общий технологический и визуальный дизайн, соединителей (шлюзов), а также web-сервисов. Power BI относится к классу self-service BI, и BI с резидентным вычислением (англ. in-memory computing). Является частью единой платформы Microsoft Power Platform.

Ключевой и самый первый продукт линейки — Power BI Desktop состоит из трех интегрированных компонентов, имеющих каждый свой интерфейс:

Power Query (редактор запросов) — выполняет загрузку и очистку данных (ETL);
PowerPivot (наборы данных и модели данных) — интерфейс работы с табличными данными в оперативной памяти где выполняются запросы к данным, агрегация, расчёты и т. п.;
Power View — подсистема визуализации и построения отчётов (Reporting).

[4] Квадратными скобками (напр., [БДР план]) обозначены объекты модели данных.

Данила Тихонов

Ух замудрили,
Гайд как делать в 3 шага
1. Выделили всю таблицу и потянули за размер строчек и столбцов(сделали из равными по размеру
2.нажали кнопочку "отмена объединения ячеек"
3 удалили лишние столбцы
Поздравляем вы великолепны
Всю жизнь так работаю и все идеально

Читайте также: