Урок 8. Объединение запросов (Merge) в Power Query или прощай ВПР
ฝัง
- เผยแพร่เมื่อ 5 ต.ค. 2019
- Объединить запросы (Merge) Power Query вместо функции Excel ВПР для объединения таблиц. Можно использовать разные типы соединения, включая нечеткое соответствие для слияния, т.е. не полностью совпадающие критерии.
Файл с примером и другие уроки о Power Query по ссылке statanaliz.info/uroki-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
Благодарю за такой информативный плейлист по Power query - пусть Всевышний воздаст Вам благом за Ваш труд в этой жизни и жизни вечной!
И смотреть приятно, и слушать. Никакой воды, никакого долго вступления, и никакой мать его рекламы
Хоть кто-то заметил!! А ведь мне много раз предлагали вставить в ролик прямую рекламу. Спасибо за комментарий!
Сегодня просто праздник какой-то!!! Начал поиск в нете одной инфы, а случайно забрёл на канал и на это видео наткнулся, теперь хожу и улыбаюсь, как та девочка в бородатом анекдоте, так как нечёткое соответствие это реально что-то с чем-то, особенно когда получаешь данные от очень "продвинутых" коллег с такими кривыми данными что "мама не горю", а приходится работать с чем есть. Большое спасибо за урок, подписка, лайк, коммент и обязательных пару просмотров!!!
Вот это я понимаю - комментарий! Спасибо! ))
Дмитрий, чрезвычайно полезно. Большое спасибо. Продолжайте, пожалуйста.
Спасибо, оставайтесь на связи.
Дмитрий, благодарю за видео, для себя нашла решение проблемы. Хочу сказать, что у Вас просто талант преподавания, никакой воды, все по делу. Обязательно посмотрю все Ваши уроки! Ещё раз спасибо!
Вам спасибо за отзыв. )
Спасибо! Чрезвычайно полезно, а нечеткое соответствие - просто праздник какой-то)
Пожалуйста. Согласен на 100% . Многим облегчит работу и сэкономит кучу времени.
огромное просто спасибо за ваш труд!! посмотрела все уроки и поняла все! и здорово что вы разобрали нестандартные (для новичка) запросы. спасибо!!! здоровья вам , сил и вдохновений на новые уроки и курсы!!!
Спасибо! Взаимно всех благ и удачи!
Дмитрий, Вы прекрасно подаете материал. Выражаю Вам огромную благодарность.
Спасибо за отзыв. ))
Дмитрий, очень всё понятно, чётко и полезно! Благодарю за такой курс!
Курс для начинающих СУПЕР !!!! Всё четко и по существу! Благодарю!
Дмитрий, Вы КРАСАВЧЕГ! Видео просто огонь!
Да это жестко. Спасибо , ждём ещё уроки.
Вам спасибо. Оставайтесь на связи.
Благодарю вас, очень приятно было посмотреть и сделать для себя определеные выводы.
Спасибо
Большое спасибо за видеоурок!
Не за что, приходите еще! )
Супер, очень помогли! 😊Четко, лаконично, доходчиво!!!👍
Пожалуйста. Спасибо за отзыв!
Очень полезное видео! Спасибо Дмитрий!
Пожалуйста!
Огромное человеческое спасибо!
Вам также спасибо за отзыв! )
Она очень полезная,и уроки Ваши очень полезные
Дмитрий, большое спасибо за ваш труд. Вы очень помогаете людям. Я с Вами учусь с нуля.
Пожалуйста и спасибо за комментарий!
Это просто ракета!!!! Спасибо за урок!
Благодарю за отзыв. Оставайтесь на связи. В смысле подписывайтесь, чтобы следить за выходом новых видео ).
Большое спасибо! Такое подробное и понятное объяснение! Мне очень пригодится! Подписалась) Удачи и ещё раз спасибо!!!
Рад, если помог научиться полезному. Спасибо за подписку!
Спасибо! Очень полезное видео! 👍🏻
Супер изложение материала!
Рад, что понравилось!
Дмитрий, спасибо большое за уроки! Шикарный курс, прекрасное объяснение! Примеры для скачивания - супер! Освоил за один день с нуля! Чуть позже найду время - обязательно возьму продвинутый курс в кассе! Успехов Вам, ждем новые уроки!
Спасибо большое за отзыв!
Нечеткое соответствие - это очередная попытка "автоматизировать бардак" ☺ Спасибо за прекрасный ролик по вариантам объединения запросов. ОЧЕНЬ ценная информация.
Спасибо за отзыв. Да, PQ очень сильно облегчает жизнь и экономит много времени.
у меня при объединении не показывает такое поле, чтобы выбрать нечеткое соответствие...не знаете в чем дело?
Это шикарно, супер, спасибо большое!
Спасибо за отзыв. )
Очень помогло. Спасибо.
Спасибо большое за книгу!!!
Большое спасибо Дмитрий
Пожалуйста! )
Четко и по делу. Спасибо
отличные видео - очень информативные. Спасибо!
Спасибо за комментарий. Рад, что понравилось )
0:50 Дмитрий, спасибо за понятное объяснение сути объединения запросов.
И за примеры для скачивания - отдельное Спасибо!
@@1iuh да не за что. Вам спасибо за комментарий.
Спасибо !!! Неделю ходила и думала , как подставить данные из двух разных таблиц, полученных из разных систем по 20000 строк в каждой табличке и так удачно наткнулась на это видео , за минут 30 все получилось в лучшем виде !!! Подписываюсь на ваш канал буду оптимизировать работу по вашим видео !!!!!!
Юлия, спасибо за отзыв и за то, что рассказали об успешном решении своей задачи.
Большое спасибо за уроки, очень интересно и информативно! Покопалась в настройках, если в параметрах нечеткого совпадения поставить 1 , то как раз все идеально объединяется - и кол-во строк неизменно, и "аэропорт" подтягивается)
огромное спасибо !!! очень просто и доступно !!
Вам спасибо за отзыв.
Огонь!
Абсолютно точно!
Спасибо за отличный урок очен помогла
Огромное спасибо!
Пожалуйста )
Отличный приём! Напоминает Fuzzy Lookup, но его надо отдельно устанавливать.
Совершенно верно. В Power Query многие сложные задачи решаются значительно проще.
Спасибо! Да, нечеткое - четкое!
Отличное даже )
Большое спасибо за разъяснения! Но в будущем думаю посмотрю ещё, что бы лучше усвоить когда столкнусь с подобной задачей)
Тоже верно. Сэкономите время на поиски. Спасибо за комментарий.
Пришел за Unpivot искал решение для целей Power B(теперь знаю даже как это называется, до этого ломал копья Транспонированием и чуть было не отчаялся) и чет залип)) Жирнейший Лойс!
Спасибо за отзыв! ) Рад, если помог!
Огромное спасибо, посмотрел все 8 уроков. Надеюсь, это поможет на собеседовании:)
Обязательно поможет. Рекомендую пересмотреть - лучше запомнится.
Пригодилось Вам полученное знание?)
спасибо за ваш труд, лайк лайк лайк
Спасибо! Приходите еще!
Sposiba, çox əla dərs idi. Təşəkkürlər
Power query неспроста имеет приставку Power. Это мощно!
Еще бы!. Спасибо за отзыв )
супер🙏
Спасибо!
Не уверен, что это законно выпускать аж 3 видео в 1 день. Спасибо большое, Дмитрий! Надеюсь снова не пропадёте на 4 месяца...
Акцент на этом видео ). Про хи-квадрат и критерий Стьюдента - перезапись старых. Спасибо на добром слове, оставайтесь на связи.
Спасибо вам, удачи^2 ( в квадрате).
А у нас старый Excel. Информационное начальство не хочет слышать, что по сравнению с 2007 поменялся функционал. Хоть здесь посмотреть и порадоваться за счастливых людей. Спасибо Дмитрий!
Покажите начальству этот ролик ))
На авито аккаунт на Ofiice 365 новейший я за 400 руб. покупал...... Всё ок...
Задачу сравнения легко решить использую сводную таблицу. Для этого достаточно создать один новый столбец в каждой таблице c со значением "Таблица 1" и "Таблица 2" и тд соответственно, затем поместить одну таблицу под другой и сделать сводную, закинув в поле Столбцы созданный нами столбец.
Можно и так. Но подойдет не всегда да и лишних действий много.
При повторном просмотре Урока я понял, что имея Полное внешнее объединение, мы можем получить все остальные 5 видов объединения путем фильтрации такой таблицы, отсеивая пустые строки в соответствующих столбцах. Так что *Полное внешнее объединение* - самое универсальное.
Да, но при больших данных это может замедлить выполнение запроса.
Дмитрий, с удовольствием посмотрела все 8 уроков, обратила внимание, что у вас не подключена бесплатная надстройка от Microsoft Fuzzy Lookup. Тоже как вариант для сравнения данных двух таблиц. Попробуйте с ней для чистоты эксперимента. Хотя, имея инструмент PQ - остальное уже моветон. Спасибо.
Да, при наличии неточного поиска в PQ Fuzzy Lookup стал менее актуален. Я пользовался пару раз этой надстройкой, но потом за ненадобностью удалил. Спасибо за уточнение. Может быть, кому-то из читающих комментарии пригодится. )
Спасибо вам за ваши уроки! Много полезного узнал. А вообще я ищу решение одной задачи, может вы подскажете как ее можно решить с помощью excel. Есть несколько таблиц с размером окна (ключевое значение) и стоимостью определенной опции, нужно сделать таблицу возможных вариантов для определенного размера или в идеале конфигуратор стоимости от выбора опций.
при нечетком поиске, можно поставить галочку "только первое соответствие" , там же где выставляется коэфф. соответствия. Это поможет сузить кол-во соответствий при низком коэффициенте
Спасибо за уточнение!
Дмитрий, спасибо за полезный урок и прекрасную подачу!!! Существует ли объединение таблиц в Query по типу ВПР с интервальным просмотром (например, есть таблица заказов и таблица скидки (пошаговая) в зависимости от количества заказов, нужно проставить соответствующую скидку в таблице заказов). Заранее благодарю!
Добрый день. Очень подробно и понятно. Спасибо вам за видео. У меня есть задача. Подскажите пожалуйста, могу ли я в книге Exel 2016 сделать сводную таблицу по уникальному значению, например "2020", в которой были бы все строки из трех таблиц, содержащие это значение. Таблицы располагаются, соответственно, в 3-х отдельных листах этой книги
Добрый день. Да, можно. Сводная таблица из нескольких источников делается в Power Pivot или в модели данных. Посмотрите прием по ссылке th-cam.com/video/anbDFiauiGQ/w-d-xo.html
ВПР тянет первое значение (если столбец содержит не уникальные значения). при объединении будет сюрприз для объединяющего ))))
Внутреннее соединение по сути, как определение условным форматированием совпадений))
Вроде того, да. Только при большом объеме данных будет полезнее.
Приветствую! Когда следующие уроки?) Будут-ли? Условные столбцы типа Функции "если".. или осветите Power Pivot и power BI ?
Приветствую. Планирую еще пару уроков. Power Pivot и BI в ближайшее время не будет.
Спасибо за Ваш труд, очень полезная вещь!!
Подскажите пожалуйста, а можно ли объединять более чем 2 таблицы? И есть ли такой вариант объединения, когда нужно найти соответствие не только по критерию "строка", но и по названию столбца? Чтобы если в одной из таблиц есть данные по столбцу, который уже был в предыдущих таблицах, значение подгружалось бы в этот столбец, в противном случае создавался новый столбец.
Пожалуйста. За один раз можно соединить только две таблицы. Для более сложных правил объединения придется копаться в коде M, стандартных средств нет.
для сложных объединений можно использовать python + pandas, это уже не совсем эксель и требует соответствующей квалификации пользователя.
Спасибо большое автору канала за урок! Сто пудов лайк и подписка.
Евгений, огромное спасибо за Ваши уроки! Подскажите пожалуйста, может кто-то из коллег сталкивался с таким вопросом, офис 2019 нет пункта "галочки" использовать нечеткие соответствия ?
Здравствуйте. Не нашли ответ на свой вопрос? Такая же проблема.
🎉🎉🎉🎉🎉
Дмитрий здравствуйте помогите пожалуйста, В разных таблицах мне нужно сопоставить ближайшую предыдущую дату, например, чтобы сравнить проданный товар с аналогичным купленным товаром, который я купил в последний раз и у меня есть в наличии, чтобы определить маржу
0:44 Только с вашим объяснением понял что такое 6 способов объединения. Прежде где-то смотрел и читал, но было непонятно.
Рад, что удалось объяснить. Спасибо за отзыв.
Здравствуйте! Спасибо за урок!
Скажите пожалуйста, почему у меня нет крыжика "использовать нечёткие соответствия для слияния" в окне объединения запросов?
Здравствуйте. Эту фичу недавно запустили. Она точно есть в подписке Microsoft 365. В других версиях - не знаю.
01:46 Внешнее слева это же просто выдаст левую таблицу, а внешнее справа - правую?
Основы sql в excel😊
Дмитрий добрый день. Можно обьеденить таблицы из разных файлов?
Сейчас пойду это пробовать, мне очень надо слиять 18 одношапочных листов
Здесь более подходящий урок th-cam.com/video/ueUaHQYfdk4/w-d-xo.html
Все отлично, спасибо. Только все примеры на таблицах в несколько строк. А у меня таблица в тысячи строк. Она в Power query обрезается. Что делать?
а не возникало у вас случайно такой вот проблемы: есть несколько подключенных запросов. Хочу присоединить данные из одного запроса к другому по ключевому полю. Выбираю второй запрос и хочу выбрать столбец, по которому соединять, но пишет что предварительный просмотр недоступен.
Добрый день! Подскажите, есть задача объединить факт и план, причем в факте есть неуникальные значения. Для этой задачи мне сначала подошел тип соединения- внешний полный, тк в плане есть суммы по статье, которых нет в факте и наоборот. НО когда все объединила, увидела, что из за того, что в факте неуникальные значения план по ним задвоился-затроился и тд по числу неуникальных значений в факте. Может я что то не так сделала или без дополнительной обработки факта перед слиянием невозможно эту задачу решить?
В 2016 офисе нет поля Тип соединения. На всех уроках у всех есть это поле, как и условный столбец, которого тоже нет в версии моего рабочего офиса.
Excel обновляется регулярно. Версии могу не совпадать.
Дмитрий, очередное спасибо за урок. Единственное - при "Нечетком соответствии", у меня не появилась опция "Параметры нечеткого слияния". Подскажите, в чем может быть причина? Может из-за того что у меня Эксель 2016 года?
Да. Эта опция появилась относительно недавно. Нужно обновить MS Office. Причем, скорее всего, сейчас эта опция есть только в подписке microsoft 365.
Подскажите, как производить дальнейшие вычисления с уже объединёнными запросами, чтобы при обновлении не пропадали формулы, дополнительные строки и тому подобное. Или это невозможно?
Если нужны запросы, которые объединились, можно сделать их дубликаты (в ролике есть пример).
Добрый день. MS Office 2019. Нет возможности установить галочку "Использовать нечеткие соответствия" и "Параметры нечеткого соответствия". Их у меня просто нет. Где они настраиваются? Заранее благодарен.
Добрый день. Есть в подписке офис 365.
@@statanaliz поставила специально офис 365 - все равно нет :( Не подскажете, мб на какую волшебную кнопку дополнительно нажать надо?
@@user-qe5oe7ep3d офис 365 бывает разный. У меня про плюс да еще с ежемесячным обновлением. Функцию эту добавили недавно. Поэтому нужно подождать, пока пришлют обновления.
А в новом excel уже отсутствуют параметры слияния? нет возможности выбрать параметры и тип слияния. при объединении запросов сразу получилась таблица с нужным совпадением.
Тоже самое нет таких настроек при слиянии
Тоже, хотелось бы уточнить, на какой версии Excel можно выбрать параметры и тип, для сопоставления адресов, фамилий
Прошу проконсультировать. При слиянии полей (типа ВПР) PQ добавляет строки с дублированием в них данных. Вчём проблем, никак не пойму.
У меня Microsoft Office 2021 (и Windows 10). Прежде был 2013 и 2016, где нет новых формул Excel из Office "365" и 2019, а в 2016 даже вкладка Power Query как-то пропала - и не смог найти.
Огромное Вам спасибо! Подскажите, пожалуйста, есть ли порог на количество символов в ключевом поле? И есть ли ограничения по количеству ключевых полей? Этот урок уже несколько раз посмотрела, затерла до дыр, так сказать 😅. Оооочень полезный! Уже вовсю применяю в работе. Добра, здоровья, успехов и удачи Вам!!!😊👍👍👍☀️
Пожалуйста. На практике я не сталкивался с такими ограничениями. Как правило, они делаются с большим запасом. Вряд ли потребуется склеивать таблицы по абзацу текста. 1-2 поля с кодами, адресами и именами. Этого достаточно, как правило. Или у Вас ошибка при соединении?
Спасибо большое за ответ!😊 Пока нет, просто до этого я должна была по 3 ключевым столбцам подтягивать данные из разных файлов (9 файлов). Я делала это с помощью ВПР и ключа, созданного по этим трем столбцам. Колличество символов в таком ключе иногда зашкаливало и данные не подтягивались, приходилось вручную добавлять. Сейчас у меня частая ошибка в PQ - это (точно не помню название) переустановленные столбцы. Но результат при этом верный, поэтому я эти ошибку игнорирую 😅. И ещё почему-то не находит файлы по ранее созданным подключениям. Они до этого нормально работали, нажала "обновить" - всё равно не сработало.🤷 Ещё раз огромное Вам спасибо!!!!😊☀️
@@mandarina2016 Если не находит файлы, то проверьте их название или даже наличие по указанному адресу. Остальное нужно проверять по факту, если вдруг не работает. Спасибо за вопрос и комментарий )
Мечтал об этом. В 1С все это есть. Так же пишешь запрос к какому то справочнику и вытаскиваешь данные и клеишь к какому нибудь документу. А тут знал только ВПР. СПАСИБО! С какой версии Excel он доступен?
А что делать если нет галочки использовать нечеткие соотвествия для слияние?
Мне нужно к продажам подтянуть планы, в продажах много дублирующихся строк с разными суммами. При слиянии планы дублируются. Как добавить так чтобы только в первую строку добавлялся план?
В Эксель 2019 не вижу параметры для выбора "Использовать нечеткие соответствия.... " и далее следующие меню
Недавно появилось в подписке офис 365.
@@statanaliz А в простом офисе нет?
@@user-er6gc9zs6m Нет пока.
Дмитрий, можно ли связать таблицы с более чем одним критерием, например, я хочу связать продажи с покупками, чтобы связать проданный товар с покупкой ближайшей даты. заранее спасибо
В Excel, наверное, проще сделать новую колонку, состоящую из комбинации других полей. Потом новая колонка используется, как ключевое поле.
Отличный урок и очень полезная информация. Есть вопрос: я не могу в редакторе запроса найти опцию выбора способа слияния. У меня его почему-то нет. На сайте support.office.com в примерах тоже нет. Может подскажете, какие настройки есть чтобы включить эту опцию?
Какой у Вас Excel? Скорее всего, нужно обновить MS office. Спасибо за комментарий и вопрос.
@@statanaliz MS Excel 2016 MSO 16.0.4266.1001 64bit
@@user-jg4mo7ug6i Этот шифр версии не понимаю, но, скорее всего, достаточно будет обновиться до последней версии. У меня подписка microsoft 365
Напоминает программу SQL с JOIN.
Дмитрий добрый день, почему то у меня в источнике нет функции "Использовать нечёткие соответствии"
а если нужно все с правой и слевой, но то что одинаково сложить в одну строку, а не дублировать
В окне "слияние" нет списка в выбором типа соединения. Как включить эту возможность?
Какая версия Excel? Видимо, нужно обновиться.
Самое главное преимущество это то что не нужно постоянно протягивать ВПРы если они установлены на нескольких столбцах, тут просто берешь и при изменении таблиц в запросе обновляешь и все обновляется в соответствии с исходными таблицами!
Совершенно верно. Но есть и другие преимущества. Например, можно связать сразу несколько столбцов, а не один, как через ВПР.
топ
Спасибо )
Подскажите пжлст, у меня в екселе нет пункта (при слиянии запросов) ИСПОЛЬЗОВАТЬ НЕЧЕТКИЕ СООТВЕТСТВИЯ ДЛЯ СЛИЯНИЯ. Пробовал 2016 и 2019 офисы. Что я не так делаю? - ага - почитал комментарии - нужен именно офис 365 про плюс... верно?
У меня без про плюс. Обычная семейная 365 (бывшая домашняя, вроде).
Подскажите пожалуйста, что делать если у меня при слиянии не отображается выбор типа слияния (4:26 минут), эксель 2016?
Это глюк или оно по другому работает?
Спасибо!
Нужна более свежая версия Excel. Эта опция была добавлена около года назад в подписку microsoft 365.
В офисе 2019 нет параметра для неточного слияния, как быть?
Установить подписку microsoft 365
Думал только Павлов мегаэксперт. Оказывается еще и Езепов.
Так и есть - Николай мегаэксперт. Но спасибо за отзыв ))
Учитель, на каком Excel показывается пример из этого урока? В Excel 2010 на панели "Данные" нет кнопки "Из таблицы диапазона"... Есть "Из Access", "Из Интернета", "Из других источников" и "Существующие подключения". Помогите, пожалуйста.
После выхода 2010 версии прошло уже 13 лет. Показывается в 2016, насколько помню. Обновите MS Office, короче ))
Дмитрий, есть ли простой способ объединения данных из двух таблиц по следующему условию: нужно взять все данные из первой таблицы и данные из второй, которых нет в первой. Удаление дубликатов не применимо, поскольку и первая таблица может содержать дубликаты, и вторая. Логика подсказывает, что нужно слить в одну таблицу два запроса: запрос на выборку всех данных из первой таблицы и внешний правый запрос. При этом есть одно «но». Предположим ситуацию, когда в первой таблице есть запись «товар1 50шт.», а во второй таблице две записи «товар1 50шт.». В итоге получим игнорирование обеих записей из второй таблицы, а по условию это делать нельзя. Голова идёт кругом.
P.S. Все, разобрался. Задача является неразрешимой, поскольку связь «один ко многим» нельзя обработать в обратном направлении. Суть вопроса, имеются несколько баз данных, одна из них является главной, в неё частично перенесены записи из других баз (идентификаторы, ясное дело, не используются), количество записей измеряется десятками тысяч, нужно все свести в одну таблицу с учетом «задвоений». Задали, блин, задачу. :(
Так нужна еще помощь? Нужно перечитывать условие? ))
Почему-то нет кнопки использовать нечёткое слияние( может что-то в настройках нужно проставить?
Нужна подписка Office 365. Это относительно новое обновление Excel.