Импорт и консолидация таблиц в Excel через Power Query

Консолидация данных в Excel с помощью Power Query Excel (Получить и преобразовать данные), консолидация таблиц. Текстовая версия и файл с примером можно скачать на сайте по ссылке statanaliz.info/excel/power-q...
► Корпоративный тренинг "Продвинутый уровень MS Excel":
statanaliz.info/trening-prodv...
► Онлайн курс "Продвинутый уровень MS Excel":
statanaliz.info/kurs-prodvinu...
**************************
** Мой сайт об Excel и статистике **
statanaliz.info/
** Странички и аккаунты в соцсетях **
Facebook: / statanaliz.info
Вконтакте: id_statanaliz_info
Твиттер: / statanaliz_info
Telegram: t.me/statanaliz

[@user-bv7jz1ve2s]

Дмитрий благодарю Вас от души за прекрасный урок, я бы например сам не смог бы разобраться в одиночку , а вам как специалисту гораздо проще, я подвожу к той мысли что вы знаете как это устроено и работает Откуда Что Идет, простой пример в 8 уроке была схема про объединение запросов от общего к частному, и если знать все эти схемы то причину ошибки понять гораздо проще, если на каждом уроке показывать "что откуда и куда" как это всё взаимодействует и работает- эффект от обучения будет гораздо выше, и у вас талант объяснять и показать про Excel, я очень благодарен Вам за ваш труд

[@user-qn7bf2si2f]

Качество изложения материала на высочайшем уровне! Не часто встретишь такого классного преподавателя. И сайт достойный.

[@statanaliz]

Спасибо

[@soda6304]

Супер. Экстраспециалист.

[@biweb13]

Супер!!!

[@statanaliz]

Надеюсь пригодится )

[@SergAlex81]

Круто. Спасибо!

[@identitynotestablished9133]

Очень интересно, спасибо

[@user-xp1bh1et3r]

Спасибо за труды. На 7 уроке я ещё успевал понимать что к чему, на 8 уроке пошли опять функции, что я подумал когда же выйдет анти ПОВЕР КВЕРИ (с функцией читать мысли пользователя). Пивка захотелось от досады.

[@statanaliz]

Не отчаивайтесь. Excel по большому счету, это программа для домохозяек. Просто пересмотрите урок еще раз или два. Все станет понятней. )

[@user-ws2bv1og4l]

создание именованного диапазона в качестве источника пригодится, если в книге нельзя делать умную таблицу (например при общем доступе). если исходный диапазон трудно преобразовать в таблицу, можно сделать так: подключиться через PQ к этому же файлу, и выбирать лист, где диапазон. только надо файл сохранять после добавления чегото в диапазон. чтото типа: Источник = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="путь"]}[Content]{0}[путь]), null, true), где путь - полный адрес файла, лежащий в таблице на каком нибудь листе. можно получить динамически формулой: =ЗАМЕНИТЬ(ЗАМЕНИТЬ(ПСТР(ЯЧЕЙКА("filename";A1);1;ПОИСК("]";ЯЧЕЙКА("filename";A1)));ПОИСК("]";ЯЧЕЙКА("filename";A1));1;"");ПОИСК("[";ЗАМЕНИТЬ(ПСТР(ЯЧЕЙКА("filename";A1);1;ПОИСК("]";ЯЧЕЙКА("filename";A1)));ПОИСК("]";ЯЧЕЙКА("filename";A1));1;"");1);1;"")

[@statanaliz]

Фух, надеюсь, работает. Не проверял! ))

[@alexgr2575]

Спасибо за видео. А если в источнике данных существует ссылка на другой файл Excel (и он со временем меняется), как обновить сформированный с помощью PQ запрос и возможно ли?

[@statanaliz]

Надо делать динамическую ссылку, чтобы она подтягивала адрес по формуле. Сделаю специальный урок.

[@georgesmart]

Здравствуйте, будет ли продолжение видеокурсов по Power Query?

[@statanaliz]

Здравствуйте. Да, будет. Оставайтесь на связи.

[@TanchoraKonukova]

Видео шикарное, но формула для динамического диапазона в 2016 не пересчиталась - выдала #н/д. вроде все так же как в уроке написала :( =$A$1:ИНДЕКС($H:$H;ПОИСКПОЗ(99^99;$H:$H)) И что делать, если в последнем столбце диапазона текст?

[@alishaz27]

Здравствуйте! У вас есть видео про даты?! Я не понимаю как с ними работать, во многих источниках все просмотрела, все сделала как надо, но все равно в итоге выводит ошибку. Подскажите, где это можно просмотреть?!

[@statanaliz]

Где нужно обрабатывать даты? в Power Query или в Excel?

[@adxamimomov2512]

Спасибо за отличное видео. Единственный момент - оказывается если последнее поле таблицы не числовое, например текстовое, или же если заполнены только столбцы до последнего, то метод "99^99" не работает, то есть не ловить последнюю добавленную строку

[@statanaliz]

Поэтому лучше использовать Таблицу Excel и не придумывать себе трудности ))

[@adxamimomov2512]

@@statanaliz К сожалению, я пока не смог найти способ автоматического превращения в электронные таблицы SAPCrosstab без использования VBA. При обновлении, теряется формат таблицы. К счастью в SAPCrosstab последний столбец почти всегда цифровой, так что Вы мне очень помогли.

[@user-ws2bv1og4l]

тогда с помощью СЧЁТ3, например: =ФЦО!$A$2:ИНДЕКС(ФЦО!$A:$A;СЧЁТЗ(ФЦО!$A:$A))

[@adxamimomov2512]

@@user-ws2bv1og4l спасибо за ответ. Попробовал. Функция ПОИСКПОЗ не видит символа "*", а СЧЁТ3 не видит пустых ячеек. Попробовал для этих двух функций добавить функцию МАКС: МАКС(ПОИСКПОЗ(99^99;$B:$B);СЧЁТЗ($B:$B)). Но если и звёздочки и пустые, тогда всё равно до конца не можем отобразить... Всё таки видео отличная, помог способ указанный в самом видео. Оказался перекрестная таблица SAP BW SAPCrosstab1 имеется в рабочей книге как диапазон. СПАСИБО!

[@Albertus99]

спасибо за ролик. считаю было бы правильным разделить обе части одного урока.

[@statanaliz]

Может и так ). Но тогда вторую часть (или первую) многие не увидели бы. Спасибо за рацпредложение! ))

[@user-tu8eq3bg8l]

Подскажите, формула =Excel.currentworkbook() не воспринимается, что может быть не так?

[@user-jn4rt8yu8o]

= Excel.CurrentWorkbook()

[@user-cm6jg8vu2g]

Добрый день. А почему эксель понадобился интернет эксплорер после нажатия клавиши "Из таблицы"? Мне его специально устанавливать чтоли?

[@statanaliz]

Добрый день. Не должно такого быть. Попробуйте закрыть все программы и перезапустить Excel и попробуйте еще раз. Возможно, какой-то конфликт.

[@user-cm6jg8vu2g]

@@statanaliz А у вас какой эксплорер. У меня стоит 8, эксель пишет, что нужен 9

[@statanaliz]

@@user-cm6jg8vu2g Да я вообще экплоереом не пользуюсь. Хотя, возможно, где-то установлен. Попробуйте обновить, как просит.

[@user-cm6jg8vu2g]

@@statanaliz да и мне он не нужен. поставил последнюю версию и заработал PQ

[@statanaliz]

@@user-cm6jg8vu2g Обновление решает многие проблемы )

[@irinairina1266]

Дмитрий, вопрос: почему после извлечения информации и выбора нужных столбцов в Power Query, название файлов (.xlsx) в столбце Item задваивается следующим образом: 1.а.Название файла б.Название фаайла!_FilterDatebasa 2.а.Название фаайла б.Название файла!_hlnm_FilterDatebasa соответственно в Name: 1.а.Название фаайла б._FilterDatebasa 2.а.Название файла б._hlnm_FilterDatebasa в Kind: 1,2.а.sheet б.DefinedName Как это исправить? Спасибо

[@statanaliz]

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

[@irinairina1266]

@@statanaliz, но такой точно нет. Скажите, пожалуйста, можно ли в Еxсеl таблицы данных, расширение csv и xlsx, с помощью Power Query в одну ? Файлы находятся в разных книгах.

[@statanaliz]

@@irinairina1266 csv и xlsx - это разные типы файлов. Если в них есть общее (ключевое) поле, то можно объединить таблицы в одну. Делаете запрос к каждому из файлов и потом объединяете запросы по ключевому полю. Вот видео на тему объединения запросов в Power Query kzbin.info/www/bejne/jXy1fWaEep5lbK8

[@irinairina1266]

@@statanaliz , спасибо. С ошибками все в порядке. За видео, большое спасибо.

[@irinairina1266]

@@statanaliz, то, что это разные типы файлов, я прекрасно понимаю. Уже всё в порядке. Спасибо

[@user-qn5by5iv7u]

2021

[@jusyF]

10:00

[@user-dr4od4tx7i]

Файл с примером не нашел в предложенной ссылке увы...

[@statanaliz]

Поправил. Добавил ссылку под видео внизу статьи. Спасибо.

[@user-dr4od4tx7i]

@@statanaliz спасибо. Теперь совершенно)

[@statanaliz]

@@user-dr4od4tx7i Пожалуйста )

[@user-qn5by5iv7u]

2022