Если вы уже начали использовать в работе инструменты в Microsoft Excel, то очень скоро столкнётесь с одной узкоспециальной, но весьма частой и надоедливой проблемой, связанной с постоянно ломающимися ссылками на исходные данные. Суть проблемы в том, что если в своём запросе вы ссылаетесь на внешние файлы или папки, то Power Query жёстко прописывает абсолютный путь к ним в тексте запроса.
У вас на компьютере всё работает прекрасно, но если вы решите отправить файл с запросом своим коллегам, то их ждёт разочарование, т.к. у них на компьютере путь к исходным данным уже другой, и наш запрос работать не будет. Что же сделать в такой ситуации? .
Сильно обновил (по факту, практически полностью переписал статью) про . Стандартными средствами такое Excel не умеет, но, при желании, все-таки можно реализовать с помощью Power Query, макросов или Мастера Сводных таблиц из старых версий Excel:
P.S. Забавно, как недоступные или трудоемкие задачи, которые пару лет назад можно было решить только хардкорным программированием на VBA, сейчас легко и изящно решаются с помощью надстройки Power Query. Будущее уже здесь, да
Термины "Power Query", "Power Pivot", "Power BI" и прочие "пауэры" все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel. Давайте разберемся.
Иногда бывают ситуации, когда заранее неизвестно сколько именно и каких строк нужно импортировать из исходных данных. Допустим, мы должны загрузить в Power Query данные из текстового файла, что, на первый взгляд, не представляет большой проблемы. Сложность в том, что файл регулярно обновляется, и завтра в нем может быть другое количество строк с данными, шапка из трех, а не двух строк и т.д.:
То есть мы заранее не можем с определенностью сказать, начиная с какой строки и сколько именно строк нужно импортировать. А это проблема, т.к. эти параметры жестко прописываются в М-коде запроса. И если сделать запрос по первому файлу (импорт 5 строк начиная с 4-ой), то он уже не будет правильно работать со вторым. Было бы здорово, если бы наш запрос мог сам определять начало и конец «плавающего» текстового блока для импорта.
Если вы столкнулись с задачей переноса данных из файла PDF в Microsoft Excel, то у вас есть несколько вариантов. Если вы счастливый обладатель недешевого FineReader, то все проще. А если нет? На самом деле, вполне можно выкрутиться с помощью связки Word + Power Query:
Среди задач работы с текстом, которые мы неоднократно уже разбирали, немного особняком стоит вопрос извлечения из текста последнего слова или фрагмента по заданному разделителю. Вытащить первое слово - не проблема, а вот с последним все не так легко и очевидно, т.к. количество слов в каждой ячейке может различаться. Давайте разберем несколько принципиально разных способов это сделать, а именно:
Эта статья родилась после вопроса одного из моих слушателей с тренинга "". Вопрос был о том, как из вот такой таблицы по проектам:
... получить вот такую:
Т.е. "размазать" бюджеты по дням для каждого проекта, получив на выходе что-то похожее на диаграмму Ганта. Формулами такое делать скучно, макросами - сложно, вручную - лениво, а вот бесплатная надстройка Power Query для Excel в такой ситуации как раз блещет всей своей мощью и красотой.
Есть много способов сравнить две таблицы в Microsoft Excel и на эту тему я уже неоднократно писал статьи и делал видеоуроки. Теперь же захотелось "подвести черту" и подробно описать три самых любимых мной способа сравнения:
функцией ВПР (VLOOKUP) - классика жанра
с помощью сводной таблицы - хороший вариант для больших таблиц
с помощью надстройки Power Query - самый красивый способ, да еще и с автоматическим обновлением
Разбор вариантов будем делать на двух версиях прайс-листа - старой и новой. Задача: быстро найти отличия, т.е. новые и удаленные товары и товары, у которых изменилась цена:
Иногда при выгрузке из каких-либо корпоративных программ, данные достаются нам в не очень удобном виде. Один из самых неприятных вариантов - это вся информация в одном длинном столбце, из которого придется делать нормальную двумерную таблицу:
Давайте разберем несколько способов сделать это быстро и красиво, а именно:
формулами и, в частности, функцией ДВССЫЛ (INDIRECT)
В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!
Сделать это можно двумя основными способами: при помощи макросов и через Power Query.
Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов - подсчет количества и вывод совпадающих значений в двух списках. Подробно разберем три основных подхода для решения этой распространенной задачи:
Классическая ситуация: у вас есть два списка, которые надо слить в один. Причем в исходных списках могут быть как уникальные элементы, так и совпадающие (и между списками и внутри), но на выходе нужно получить список без дубликатов (повторений):
Давайте подробно разберем три принципиально разных способа:
Что делать, если нужно быстро собрать данные из большого количества файлов Excel в одну таблицу? Вариантов тут, обычно, несколько:
тупо копипастить вручную (с кучей ошибок в процессе)
писать макрос для сбора данных или заказывать его знакомому VBA-программисту
делегировать задачу подчиненным
На самом деле, начиная с Excel 2013 есть еще один путь - сильно проще и на порядок удобнее - надстройка Power Query. Для Excel 2013 ее можно совершенно бесплатно скачать и подключить, а в Excel 2016 она уже входит по-умолчанию. Написал о том, как использовать ее в подобной ситуации. Основные шаги тут следующие:
импорт одного файла для примера
преобразование процедуры импорта и "причесывания" данных в функцию
импорт всех файлов из папки
добавление созданной пользовательской функции для загрузки данных из каждой книги
Быстро и изящно.
Если у вас пока нет Excel 2013-2016 - все равно гляньте, чтобы оценить красоту решения
