Иногда бывают ситуации, когда заранее неизвестно сколько именно и каких строк нужно импортировать из исходных данных. Допустим, мы должны загрузить в Power Query данные из текстового файла, что, на первый взгляд, не представляет большой проблемы. Сложность в том, что файл регулярно обновляется, и завтра в нем может быть другое количество строк с данными, шапка из трех, а не двух строк и т.д.:
То есть мы заранее не можем с определенностью сказать, начиная с какой строки и сколько именно строк нужно импортировать. А это проблема, т.к. эти параметры жестко прописываются в М-коде запроса. И если сделать запрос по первому файлу (импорт 5 строк начиная с 4-ой), то он уже не будет правильно работать со вторым. Было бы здорово, если бы наш запрос мог сам определять начало и конец «плавающего» текстового блока для импорта.
Еще с 2011 года для Excel существует крайне полезная (и совершенно бесплатная!) надстройка Fuzzy Lookup от Microsoft, которая умеет искать ближайшие похожие текстовые строки в двух списках, т.е. работает как известная функция ВПР (VLOOKUP), но при неточном совпадении названий:
В некоторых ситуациях (например, при поиске похожих, но не точно совпадающих адресов) эта надстройка совершенно незаменима. Давайте рассмотрим подробнее как она работает.
Досрочно (спасибо ЦБ РФ) выпустил второе обновление своей надстройки PLEX в этом году. Что изменилось:
Автогруппировка из столбцов
В выпадающий список Трансформация добавлен инструмент Автогруппировка - для автоматического создания многоуровневой иерархической группировки строк по исходному положению подписей в столбцах:
Обратите внимание, что последний столбец подписей (уровень 4) выделять не нужно, иначе получим лишний уровень группировки. Также, любые группировки на листе всегда можно легко удалить с помощью команды Разгруппировать – Удалить структуру с вкладки Данные (Data – Ungroup – Clear Outline).
Разгруппировать в столбцы
Инструмент для обратного преобразования (аналогично предыдущему) – по имеющейся многоуровневой структуре группировки раскладывает подписи в разные столбцы:
Перехват ошибок в формулах
В группе Формулы появился инструмент для быстрого добавления ко всем формулам в выделенном диапазоне стандартной Excel’евской функции перехвата ошибок ЕСЛИОШИБКА (IFERROR):
После его применения вокруг текущих формул будет аккуратно дописана эта полезная функция, заменяющая любые ошибки (#ЗНАЧ, #Н/Д, #ДЕЛ/0 и т.д.) на указанное желаемое значение:
Если оставить поле ввода пустым, то ошибки будут заменены на “” (пустые кавычки), т.е. ячейки с ошибками будут выглядеть как пустые.
Функции проверки правильности ИНН, ОГРН и СНИЛС
Добавлены пользовательские функции для проверки в Excel правильности ИНН (для юрлиц и физлиц), ОГРН (для ООО и ИП) и СНИЛС. Проверяют контрольные числа и суммы по официальным алгоритмам и выдают на выходе логическую ИСТИНУ или ЛОЖЬ:
Должны пригодиться кадровикам и не только. Как обычно, ввести эти функции можно либо напрямую вручную в ячейки, либо воспользоваться кнопкой Библиотека Формул с вкладки PLEX.
Исправления ошибок
Как всегда, обновление содержит общую оптимизацию кода и исправление обнаруженных ошибок, в частности:
Исправлена ошибка в инструменте Курс валют и функции CBR, связанная с изменением адреса и редизайном сайта ЦБ РФ.
Исправлена ошибка вывода диалоговых окон PLEX за пределы рабочей области монитора, возникающая при подключении второго экрана или проектора.
Убрано хранение параметров надстройки в реестре, чтобы уменьшить вероятность ложного срабатывания некоторых антивирусов на PLEX.
Исправлена ошибка Менеджера Листов, возникающая в том случае, если в текущем файле нет ни одного листа.
Исправлена ошибка инструмента Очистить, связанная с удалением примечаний.
Как всегда, для всех уже купивших надстройку - обновление бесплатно. Заходите на и качайте по желтой ссылке в правом верхнем углу страницы.
Если вы столкнулись с задачей переноса данных из файла PDF в Microsoft Excel, то у вас есть несколько вариантов. Если вы счастливый обладатель недешевого FineReader, то все проще. А если нет? На самом деле, вполне можно выкрутиться с помощью связки Word + Power Query:
Выпустил первое в этом году обновление для своей надстройки PLEX для Microsoft Excel. Что изменилось: Макросы для работы с файлами Добавлен набор инструментов для работы с открытыми книгами – выпадающий список Файлы:
В набор входят следующие макросы:
Сохранить все – сохраняет все открытые книги одним движением. Будет полезно, если, например, только что в комнату, где вы работаете, вбежал человек со словами "Пожарная тревога! Сейчас отключат электричество!" Ну, или вы торопитесь куда-нибудь.
Закрыть все без сохранения – закрывает все открытые книги без сохранения.
Закрыть все сохраненные – закрывает только те книги, которые не имеют несохраненных изменений.
Заново открыть эту книгу – закрывает текущую книгу без сохранения и открывает ее заново. Очень полезно, если вы во время работы увлеклись и настолько накосячили в файле, что уже проще начать все заново
Текущая папка – открывает в Проводнике папку, где находится текущая рабочая книга.
Поддержка регулярных выражений Добавлена пользовательская функция для работы с регулярными выражениями RegExpExtract в следующем синтаксисе: =RegExpExtract(Txt; Pattern; Item; Delimiter; CaseSensitive) где
Txt – ячейка с текстом, из которого нужно извлечь заданную подстроку
Pattern – шаблон на основе регулярных выражений (подробнее о языке регулярных выражений можно
Item – порядковый номер извлекаемой подстроки. Для извлечения первой встречной подходящей под шаблон подстроки используйте 1, для второй 2 и т.д. Для извлечения последней используйте -1, второй с конца -2 и т.д. Если Item=0, то будет извлечен весь массив найденных подстрок через заданный символ-разделитель (см. следующий аргумент). По умолчанию этот аргумент принимается равным 1.
Delimiter – символ-разделитель в случае, если извлекается весь набор найденных подстрок (по умолчанию – запятая)
CaseSensitive – нужно (1) или нет (0) учитывать регистр при поиске по шаблону. По умолчанию – 0.
Функция запроса курсов с сайта НБУ Аналогично уже имеющейся функции CBR к сайту ЦБ России, реализована функция запроса курса заданной валюты на заданную дату с сайта Нац.Банка Украины. =NBU(InputDate; Money) где
InputDate – дата, на которую необходимо получить курс
Money – стандартный банковский код валюты (“EUR”, “USD”, “RUB” и т.д.) По умолчанию – “USD”
Прочие улучшения и доработки
Мини-калькулятор теперь работает с учетом отфильтрованных ячеек.
Уровни вложенности функций для инструмента Авто-отступы увеличены с 10 до 50.
В инструмент Случайно добавлена возможность создания списка названий штатов США.
Инструмент Сортировка по цвету теперь умеет работать с текстом, где символы имеют разное начертание или цвет внутри одной ячейки.
Появилась возможность добавлять свой префикс и суффикс к имени файла при сохранении листов как отдельных файлов в инструменте Сохранить как файлы из группы Листы.
В инструменте Сборка листов появилась возможность быстро добавлять все файлы из текущей папки и задавать желаемые имена для собираемых листов:
Плюс, само-собой, исправление ошибок и общая оптимизация кода и дизайна по-мелочи.
Как всегда, все купившие надстройку, могут смело по желтой ссылке в верхней части страницы. Обо всех обнаруженных ошибках и глюках можно смело писать здесь же в комментариях, в или мне на почту.
Среди задач работы с текстом, которые мы неоднократно уже разбирали, немного особняком стоит вопрос извлечения из текста последнего слова или фрагмента по заданному разделителю. Вытащить первое слово - не проблема, а вот с последним все не так легко и очевидно, т.к. количество слов в каждой ячейке может различаться. Давайте разберем несколько принципиально разных способов это сделать, а именно:
Написал подробную статью о том, как создать и использовать Личную Книгу Макросов - удобное хранилище персональной коллекции макросов, рано или поздно образующейся у каждого уважающего себя пользователя Excel
Эта статья родилась после вопроса одного из моих слушателей с тренинга "". Вопрос был о том, как из вот такой таблицы по проектам:
... получить вот такую:
Т.е. "размазать" бюджеты по дням для каждого проекта, получив на выходе что-то похожее на диаграмму Ганта. Формулами такое делать скучно, макросами - сложно, вручную - лениво, а вот бесплатная надстройка Power Query для Excel в такой ситуации как раз блещет всей своей мощью и красотой.
Если вам часто приходится работать с текстом, анализируя и разбирая его на отдельные фрагменты по заданным шаблонам и правилам, то определенно имеет смысл внедрить в свою работу регулярные выражения (RegExp или, в народе, "регулярки";) - супермощный инструмент обработки текста, давно применяемый в современных языках программирования и текстовых редакторах. Excel не поддерживает регулярные выражения по умолчанию, но это можно легко исправить с помощью простой функции на VBA.
Спектр задач, которые могут решать регулярки - очень широк. Разберем несколько примеров:
Ну, что - всех с Наступившим! Надеюсь, что все вы хорошо отдохнули, отъелись (ха-ха) и готовы к новым трудовым подвигам. Хочется начать этот год с чего-нибудь не совсем обычного...
Что такое средневзвешенное значение (weighted average) и чем оно отличается от обычного среднего арифметического? Как рассчитать средневзвешенное в Excel? Формулами - легко, а вот с расчетом средневзвешенного в сводной придется повозиться и привлечь тяжелую артиллерию в виде Power Pivot и DAX.
Друзья, коллеги, форумчане, постоянные и случайные гости сайта! Позвольте поздравить вас с Наступающим Новым Годом и пожелать:
Здоровья вам и вашим близким - это самое главное. Заботьтесь о своем теле, кормите его правильной едой, гоняйте на спорт. Помните - оно у вас одно
Взаимопонимания и терпения, особенно при общении с идиотами. И поменьше этих самых идитов вокруг, а побольше - хороших людей. Помните, что вы - среднее арифметическое тех людей, которыми себя окружаете.
Стойкости и смелости в своих решениях. Вы не обязаны никому нравиться и не перед кем (кроме себя) отчитываться. Ваша жизнь - ваши правила. Оставайтесь собой, развивайтесь и жмите вперед.
Не терять фокус. Наметьте цели на год, декомпозируйте их на отдельные мелкие задачи и держите все это перед глазами. За дневной текучкой легко потерять истинные ориентиры и потратить время и энергию на рутину - не дайте этому произойти.
А чтобы было проще планировать и реализовывать свои планы - вот вам в подарок традиционный календарь в Excel на 2018 год с праздниками, выходными днями, нормами рабочих часов и списком задач:
Добавил в него Календарик-Пинарик - шикарный инструмент для личной мотивации от . Попробуйте - вам понравится
Ну что, пора подводить итоги года по традиции? Дабы не терять ориентиров и отслеживать динамику.
Тренинги
В 2017 году мы с коллегами провели 1402 академических часов тренингов (это примерно 175 тренинговых дней) = 44 корпоративных тренинга провел я лично + 29 тренинга коллеги. Обучили, в общей сложности, порядка 1100 человек из 33 компаний и организаций.
Съездили с коллегами на выездные сессии в Питер, Белгород, Астану, Алматы, Нижний Новгород, Киров, Воронеж, Шую, Гагарин, Махачкалу, Ульяновск
Две трети заказов от старых клиентов - и это здорово: Национальный Рекламный Альянс, Мегафон, Takeda, Egger, Globus, НижФарм (Stada), ВТБ, Pirelli, Renova, Xylem, Газпромнефть, Casio, Yokogawa, KIA, РосАтом...
Подружились с несколькими новыми компаниями: Brenntag, Flatglass, Geberit, Камеди Клаб, Белая Птица, Меркурий, Банк СОЮЗ, Tele2, Bridgestone, EDC Eurasia Management, Альфа-Банк, Фасад-Сервис, CNH Indastrial, ГК ВИК, Илим Групп, Абипродукт, ВерхнеЛенское речное пароходство... прошу простить, если кого не упомянул.
Собрал и провел 14 открытых тренингов для всех желающих, где обучил еще 198 человек (физлиц и одиночек от компаний). уже есть.
Новые курсы
Запустил два новых тренинга - "Скульптор Данных в Excel с Power Query" и продолжение тренинга по макросам "VBA Pro: Профессиональная разработка на VBA в Excel". В следующем году увеличу первый из них до 2 дней, т.к. материала и плюшек по Power Query оказалось сильно больше, чем умещается в один день
Почти готов однодневный тренинг по анализу данных в Excel с Power Pivot и DAX - планирую провести его . В разработке тренинг по красотам Power BI - будет логичным продолжением цепочки Excel + Power Query + Power Pivot.
Консалтинг и разработка
Выполнили с коллегами 2 проекта по консалтингу (дизайн презентаций) и два по разработке и автоматизации на VBA.
Впервые в своей практике сделал проект на чистом Power Query без VBA - кайф!
Сайт
В 2017 году на "Планете" побывало 5.1 млн уникальных пользователей, просмотревших 18 млн. страниц, т.е. чуть больше полумиллиона пользователей в месяц.
На моем канале YouTube перешагнул 43 тыс. подписчиков и 6 млн. просмотров. Ужас какой
Написал 17 новых статей (большинство из них с видео) в . Также теперь наравне с классическими формулами и макросами, стараюсь везде, где можно показывать решение проблем с помощью Power Query - за ним будущее, однозначно.
Провел , где ежедневно (почти) выкладывал новую статью с подробным описанием и примерами тридцати самых нужных функций Excel.
Надстройка PLEX для Excel 2007-2016
Выпустил 4 обновления своей надстройки в этом году.
Наконец-то перевел весь интерфейс на английский, так что теперь есть оба варианта.
Книги
Вышла в печать и в электронном виде моя вторя книга для тех, кто перерос ВПР.
Первая книжка , как ни странно, до сих пор продается. Буду готовить в этом году обновление и второе издание, т.к. за последние пару лет много что поменялось в версиях - есть, что добавить
Вот как-то так. На следующий год уже планов громадьё, корпоративными заказами забит до мая под завязку.
Как просчитать прибыльность инвестиционного портфеля криптовалюты, загрузив в Excel с помощью Power Query обновляемый курс покупки с сайта обменника, биржи или торговой площадки. Посвящяется тем 100500 человекам, что за последние пару месяцев написали мне в почту или в личку на форуме этот вопрос
Ничего особо сложного или выдающегося тут нет - для Power Query это "семечки", делается за пару минут. Кроме того, подобным образом можно импортировать в Excel курс не только биткойна, но и любой другой валюты - принцип совершенно аналогичный.
Выпустил очередное, четвертое в этом году обновление своей надстройки PLEX для Microsoft Excel. Что нового?
Мини-калькулятор Добавлен мини-калькулятор для выполнения простых арифметических действий над выделенными ячейками «на лету» без формул:
Выделяете ячейки (можно несколько несмежных диапазонов, удерживая Ctrl), выбираете нужно математическое действие, вводите число и жмете кнопку со знаком «равно». Умеет складывать, вычитать, умножать, делить и прибавлять или вычитать N-е кол-во процентов:
Удобно для быстрого подсчета НДС, скидок, наценок, деления на 1000 и т.п. Кроме чисел в поле ввода также поддерживаются несложные математические формулы и выражения со знаками +, -, *, /, например:
Если в исходных ячейках не числа-константы, а формулы, то при выполнении математической операции они сохраняются и к ним добавляется соответствующий фрагмент. Так после умножения ячейки с функцией суммирования, например, на 2 получится:
Автоотступы в сложных формулах Добавлена возможность делать автоматические переносы строк и отступы в длинных и сложных формулах – это позволяет наглядно отображать аргументы, вложенность функций и сильно облегчает процесс отладки и поиска ошибок в тяжелых вычислениях, формулах массива и т.д.
Так, например, выглядит сложная формула до применения авто-отступов: А так – после:
Умеет работать как с обычными, так и с формулами массива, поддерживает «умные таблицы». Разделение объединенных ячеек с сохранением текста В пару к уже имеющемуся в PLEX макросу объединения ячеек с сохранением текста, добавился аналогичный инструмент для разделения объединенных ячеек с последующим дублированием текста в каждой получившейся отдельной ячейке:
Просто выделяете диапазон с объединенными ячейками:
... затем выполняете команду Разделить (Unmerge) и получаете:
Создание листов с именами из ячеек по шаблону В инструмент создания листов с именами из выделенных ячеек добавлена возможность создавать не только пустые листы, но и использовать при создании копию имеющегося лист как шаблон, на основе которого создаются новые листы:
Умное автозаполнение вниз и вправо В группу Формулы добавлены кнопки для макросов У много автозаполнения вниз и вправо:
По сути, это более совершенный и удобный вариант "протягивания" формул или значений двойным щелчком по черному крестику в правом нижнем углу ячейки. Но, в отличие, от классического автозаполнения, этот инструмент:
позволяет протягивать формулы и значения не только вниз, но и вправо до упора
не «спотыкается» на пустых и заполненных ранее ячейках
умеет тянуть сразу несколько диапазонов (выделенных с Ctrl)
Дата прописью Добавлена пользовательская макрофункция для вывода даты прописью PropisDate:
Синтаксис предельно простой:
первый аргумент - ячейка с любой корректной датой (начиная с 01.01.1900 до 31.12.2099)
второй необязательный аргумент =1 - надо ли выводить дату в родительном, а не именительном падеже
Новые функции для нарезки текста В Excel давно есть стандартные функции для извлечения из текста нужного количества символов – ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT) и ПСТР (MID). Однако, использовать их бывает не всегда удобно, а для подсчета количества извлекаемых символов часто приходится еще вкладывать в них функцию поиска и т.п. Для облегчения жизни в подобных ситуациях в PLEX 2017.4 добавлены три новых макро-функции для извлечения подстрок из текста:
TextLeft – выдает все символы от начала текста до заданного разделителя
TextRight – выдает все символы от конца до заданного разделителя
TextMid – выдает все символы между двумя заданными разделителями
У всех функций можно вводить порядковый номер вхождения разделителей (если не указано, то подразумевается 1) и, при необходимости, учитывать регистр. Прочие доработки и изменения
В инструмент Текст добавлен универсальный вариант Авто для преобразования чисел-как-текст в полноценные числа.
Во все диалоговые окна добавлены оранжевые круглые кнопки с вопросительным знаком для вызова соответствующих разделов онлайн-справки и получения подробной информации по каждому инструменту надстройки.
Инструмент Редизайн кросс-таблицы в плоскую переехал из меню Сводная таблица в выпадающий список Трансформация:
Плюс, как всегда, множество мелких доработок, исправлений ошибок, оптимизация кода и т.д. Купившие полную версию, как и всегда, качают обнову бесплатно по желтой ссылке в заголовке на . Английская версия там же - в архиве.
Есть много способов сравнить две таблицы в Microsoft Excel и на эту тему я уже неоднократно писал статьи и делал видеоуроки. Теперь же захотелось "подвести черту" и подробно описать три самых любимых мной способа сравнения:
функцией ВПР (VLOOKUP) - классика жанра
с помощью сводной таблицы - хороший вариант для больших таблиц
с помощью надстройки Power Query - самый красивый способ, да еще и с автоматическим обновлением
Разбор вариантов будем делать на двух версиях прайс-листа - старой и новой. Задача: быстро найти отличия, т.е. новые и удаленные товары и товары, у которых изменилась цена:
Иногда при выгрузке из каких-либо корпоративных программ, данные достаются нам в не очень удобном виде. Один из самых неприятных вариантов - это вся информация в одном длинном столбце, из которого придется делать нормальную двумерную таблицу:
Давайте разберем несколько способов сделать это быстро и красиво, а именно:
формулами и, в частности, функцией ДВССЫЛ (INDIRECT)
Иногда при работе в Microsoft Excel приходится искать самую длинную последовательность значений, удовлетворяющих заданному условию. Например, самую длинную непрерывную цепочку поставок, самую длинную последовательность сделок, рабочих дней в табеле и т.д. В спорте подобную штуку называют еще "победной серией" (winning streak):
Решить проблему можно с помощью изящной формулы массива, но чтобы разобраться как именно и почему она работает, придется зайти немного издалека...
На практике часто приходится сталкиваться с ситуациями, когда с одним файлом Excel должны одновременно работать несколько пользователей. В Microsoft Excel всегда была такая возможность, но в прошлых версиях реализована она была слабо. Начиная с Excel 2016 появился новый режим совместного редактирования (Co-Authoring) - более удобный и мощный:
Но обратной стороной медали стало Автосохранение - переключатель которого появился в левом верхнему углу окна на панели быстрого доступа:
Наравне с плюсами, Автосохранение имеет и много неприятных минусов, о которых стоит рассказать подробно.
Для автоматического выделения ячеек цветом по условию в Excel есть условное форматирование. А как реализовать похожую технику, но для столбцов в диаграмме? Чтобы некоторые столбцы автоматически выделялись цветом при выполнении определенного условия. Например, подсвечивать:
все столбцы со значениями выше среднего
три самых больших (Top-3)
минимальное и максимальное значение
столбец, выбранный в выпадающем списке в ячейке листа и т.д.
В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!
Сделать это можно двумя основными способами: при помощи макросов и через Power Query.
