Если вы уже начали использовать в работе инструменты в Microsoft Excel, то очень скоро столкнётесь с одной узкоспециальной, но весьма частой и надоедливой проблемой, связанной с постоянно ломающимися ссылками на исходные данные. Суть проблемы в том, что если в своём запросе вы ссылаетесь на внешние файлы или папки, то Power Query жёстко прописывает абсолютный путь к ним в тексте запроса.
У вас на компьютере всё работает прекрасно, но если вы решите отправить файл с запросом своим коллегам, то их ждёт разочарование, т.к. у них на компьютере путь к исходным данным уже другой, и наш запрос работать не будет. Что же сделать в такой ситуации? .
Весьма частый случай на практике: вам нужно запускать один или несколько ваших макросов в заданное время или с определенной периодичностью. Например, у вас есть большой и тяжелый отчет, который обновляется полчаса и вы хотели бы запускать обновление за полчаса до вашего прихода на работу утром. Или у вас есть макрос, который должен делать автоматическую рассылку сотрудникам с заданной периодичностью. Или, работая со сводной таблицей, вы хотите, чтобы она обновлялась "на лету" каждые 10 секунд и т.д.
Реализовать такое можно двумя способами:
макросом, используя метод OnTime из VBA
использовать Планировщик Задач (Task Scheduler), встроенный в Windows
У каждого метода есть свои плюсы и минусы. Давайте рассмотрим их подробно.
С летними обновлениями 2018 года Excel 2016 получил революционно новую возможность добавления в ячейки данных нового типа - биржевой (Stocks) и географической информации (Geography). Соответствующие иконки появились на вкладке Данные (Data) в группе Типы данных (Data types):
Что это такое и с чем это едят? Как это можно использовать в работе? Какая часть этого функционала применима для нашей российской действительности? Давайте разберемся.
Выпустил очередное 4-е в этом году обновление своей надстройки PLEX для Microsoft Excel. Доработано несколько моментов - крупных и по-мелочи, а именно:
Курс валют Нац. Банка Республики Беларусь Аналогично функциям CBR и NBU добавлена функция NBRB, загружающая с сайта Национального Банка Республики Беларусь курс любой заданной валюты на любую заданную дату:
Если дата не указана, то берется сегодняшний день. Если не указан код валюты, то по-умолчанию загружается курс доллара США.
Пакетная обработка текста Переработан инструмент для обработки текста (кнопка Текст) – все его функции разнесены на разные вкладки в более логичной последовательности, соответствующей типичному рабочему процессу: загрузка – зачистка – обработка:
Кроме того, все функции и инструменты в этом окне теперь можно использовать пакетно, т.е. сразу за один заход, например, зачистить текст, убрать из него ненужные данные, добавить недостающие и навести красоту с регистром. Копирование и вставка только видимых ячеек – не только значения Доработан инструмент копирования-вставки только видимых ячеек. Теперь он умеет вставлять не только значения (как раньше), но и формулы, форматы, ссылки на исходные ячейки или вообще всё:
Объединение ячеек – теперь в любом виде Очень существенно переработан и дополнен инструмент объединения ячеек с сохранением их содержимого:
Теперь он умеет:
Объединять ячейки как по вертикали, так и по горизонтали.
Целиком или с заданным шагом или только ячейки с одинаковыми значениями.
Текст можно сливать в единое целое через заданный разделитель, а числа – суммировать.
Быстрый бэкап В окно Диспетчера горячих клавиш добавлен макрос для быстрого бэкапа:
Он моментально и молча создает резервную копию текущего файла с добавлением даты-времени к названию при нажатии любого удобного вам сочетания клавиш. Сохранение производится в ту папку, которая была указана в окне Резервная копия и запоминается индивидуально для каждой книги.
Точный ввод минут По просьбе пользователей добавлены кнопки (+/-) для точной настройки количества минут в инструменте для быстрого ввода времени:
Переход к источнику по ссылке В группу Формулы добавлена кнопка для быстрого перехода к источнику ссылки:
При нажатии на неё происходит переход к той ячейке, ссылка на которую содержится в формуле текущей ячейки. Например, если вы стоите на ячейке с формулой =Лист5!G20, то произойдет переход на Лист5 и будет выделена ячейка G20. Если в формуле упоминается несколько ячеек, то переход произойдет к первой из них. Если это ссылка на другой файл, то он будет открыт после запроса. По-сути, это имитация стандартного сочетания клавиш Ctrl+[, которое работало в старых версиях Microsoft Excel, но почему-то часто отказывается работать в новых.
Библиотека Формул – пользовательские формулы в отдельном файле Изменена логика хранения пользовательских формул в Библиотеке формул. Раньше все формулы – и готовые, встроенные в PLEX и те, что создавал сам пользователь – хранились в одном файле flib.xlsx в папке с надстройкой. Это вызывало сложности при обновлении и переходе на новую версию PLEX, т.к. при перезаписи этого файла терялись пользовательские формулы. Теперь все встроенные формулы хранятся внутри самой надстройки (т.е. в файле PLEX.xlam) и их набор автоматически обновляется вместе с ней. В отдельном файле flib.xlsx теперь сохраняются только формулы, созданные пользователем. Это позволит при обновлении надстройки не терять созданные вами формулы и использовать их в будущем.
Восстановление активации PLEX при переустановке Windows В папку надстройки добавлен файл Activator.xlsm, позволяющий сохранить в него лицензионный ключ активированной надстройки PLEX и восстановить его потом, при необходимости, на данном компьютере. Это позволит не производить повторную активацию PLEX после переустановки Windows:
Прочие доработки и изменения
В инструмент Случайно добавлена возможность заполнять любой заданный диапазон случайно сгенерированными английскими именами и фамилиями.
Из-за проблем с защитой исходного кода убрана возможность встраивать в книгу пользовательские функции PLEX.
В окно сборки диапазонов с листов добавлен флажок Игнорировать скрытые листы.
Вот такие изменения. Как обычно скачать последнюю версию всегда можно в по ссылке .
И как всегда, буду очень благодарен за информацию о любых найденных ошибках, багах и идеи по улучшению PLEX, чтобы она стала ещё удобнее для вас
Запилил большую и подробную статью + аж получасовое видео с пошаговым подробным разбором всего процесса создания своей собственной надстройки в Microsoft Excel. Основные этапы:
создание файла надстройки
наполнение его макросами и пользовательскими функциями
различные способы запуска макросов надстройки
создание отдельной вкладки с кнопками для надстройки
Так что теперь вы можете запросто сделать свой вариант PLEX
Для тех, кто не в теме (если такие ещё остались): 1 сентября в России впервые выступал коуч, тренер, спикер и бизнесмен Тони Роббинс. За последние 30 лет он работал с президентами, звездами спорта, известными актерами, телеведущими, бизнесменами всех уровней и т.д. Владеет полсотней компаний, написал кучу книг, на его семинарах побывало больше 3 млн. человек. Выступление собрало больше 26 тыс. зрителей в "Олимпийском" в Москве.
Я, откровенно говоря, не часто хожу на бизнес-форумы, но такое пропустить не мог, потому как:
я сам тренер и шанс посмотреть на работу специалиста такого уровня пропускать нельзя, учиться у других тренеров - это святое
читал его книги и смотрел его видеоуроки на YouTube и на TED - понравилось, многое резонирует, хотелось увидеть как это всё происходит вживую
После семинара народ в интернетах реагировал по-разному: спектр разнится от восхищения до лютой ненависти. Даже приличные сайты типа Forbes, Esquire и VC не погнушались погреть на этой теме руки, выкатив желтоватые статейки, а уж тролли всех мастей на ТВ и в соцсетях рвали гармони по-полной. Причем подавляющее большинство хейтеров на самом мероприятии даже не были, о чем откровенно и заявляют. Вспоминается легендарное: "Давайте спорить о вкусе устриц с теми, кто их ел" (с) Жванецкий
Знакомые спрашивают про мои впечатления от посещения. Специально попробую сформулировать не по горячим следам, а спустя несколько дней, когда "муть осела".
1. Организация, конечно, подкачала (мягко выражаясь). Я был в "Олимпийском" на концертах на ДДТ и Deep Purple. Оба раза был полный зал, но такого бардака на входе не видел никогда. Два часа стоять на жаре в две очереди (на регистрацию и потом еще на вход) - это нечто. У кого-то из соседей не было устройств перевода, кому-то их дали, но без батареек. В довершение всего выяснилось, что моё место в зале уже занято - нам обоим продали билеты на одно место! Причем таких людей было достаточно много и их организаторы, в итоге, просто рассаживали на любые соседние свободные места. Одним словом, колхоз.
2. Выступающий на разогреве Алан Пиз весьма порадовал. Бодрый дядька, чью книжку "Язык телодвижений", изданную каким-то левым издательством на туалетной бумаге, я прочитал, наверное, еще лет в 15 и частично помню до сих пор. Выступил коротко, интересно и с юмором, рассказав о своих принципах создания списков личных целей на примерах из своей жизни. Живая легенда.
3. А потом вышел Тони. Ну, что сказать? Надо честно признать: харизма и энергетика у него, конечно, термоядерные. И всё это помножено на знание НЛП, отлично поставленную речь, жестикуляцию и врожденный талант актера и психолога. И мастерски приправлено подаваемыми в нужные моменты светом и музыкой. Убийственное сочетание - проймет и мертвого. Начинаешь понимать, почему некоторые ездят за ним по всему миру, посещая каждый его семинар. Сразу вспомнились многочисленные убогие отечественные подражатели-коучи-самоучки из разных "б.молодостей" - повторить такое нереально, даже если тупо копировать всё слово-в-слово. Такой уровень энергии и харизму не скопируешь. 4 часа зал слушал, записывал, прыгал, делал упражнения, орал во весь голос и ни о еде, ни о туалете, такое ощущение, никто и не вспоминал.
4. Что касается самой программы семинара и его смыслового наполнения, то КПД тут, конечно, весьма скромный и некое разочарование имело место. Однако, подумав, я пришел к мысли, что ожидать чего-то большего было наивно. В Штатах Тони ведет семинар 4-5 дней с утра и до позднего вечера. Все участники разбиваются на группы по 10-15 человек и работают под руководством специальных инструкторов из его команды. Плюс еще и домашние задания получают каждый день. Это совершенно другой уровень проработки материала и совершенно другая эффективность. А тут - всего 4 часа и 26 тыс. человек без всяких помощников, да еще и с синхропереводом. Так что были с десяток слайдов, пара-тройка простых упражнений в малых группах и много движения, чтобы держать всех в тонусе. После прочитанных его книжек и просмотренных видео, я ничего революционно нового для себя не открыл и никаких качественных прорывов не заметил, честно говоря. Но посмотреть на всё это вживую было интересно, да.
А что касается токсичных статеек и хайпа, раздутого на всем этом в соцсетях, то думаю, что многие ждали волшебной таблетки. Чтобы р-р-рр-аз - и все! И сразу счастье, много денег и нет лишнего веса. Так не бывает. Для настоящих перемен в жизни нужна воля и постоянная работа над собой, а не селфи с Тони на заднем плане.
Выпустил новую версию моей надстройки PLEX для Microsoft Excel - третье обновление в этом году (v.2018.3) Исправлено несколько ошибок и по-мелочи допилены некоторые моменты, но главное изменение коснулось системы лицензирования. Если вы уже используете надстройку или собирались её приобрести в ближайшее время - обязательно прочитайте этот текст до конца.
Новая система лицензирования Раньше у надстройки PLEX было две версии - полная платная, со всеми последними фишками и инструментами и без каких-либо ограничений и бесплатная демо-версия, отстающая от полной на 1.5-2 года по возможностям, с закрытыми исходными кодами макросов и ограниченная 500 запусками. В этом было два отрицательных момента.
Во-первых, устаревшая демо-версия не показывала все последние возможности надстройки, т.е. не выполняла свою главную функцию. А во-вторых, открытый код и отсутствие системы защиты привело к тому, что единожды купленную надстройку безлимитно копировали на десятки ПК. Некоторые особо талантливые даже умудрялись перепродавать её от своего имени, да ещё и с подсаженными внутрь "троянцами" в качестве бонуса.
Так что теперь, начиная с версии PLEX v.2018.3, ситуация меняется: будет единая самая последняя версия PLEX с максимальным функционалом и закрытыми исходными кодами макросов, которая при первой установке на компьютер сначала работает в бесплатном триальном режиме 30 дней, а затем работа надстройки прекращается, и её надо либо отключить, либо ввести лицензионный ключ и активировать полную версию в появившемся окне:
Для активации необходим доступ в интернет. При желании, можно не ждать окончания триального периода и активировать полную версию досрочно, используя на вкладке PLEX команду О программе ( About):
Единожды активированная надстройка больше не будет спрашивать ключ и будет пожизненно работать на данном компьютере, в т.ч. после установки новых версий PLEX. Проверить текущее состояние лицензии и версию можно всё там же – в окне О программе ( About). Разрешается производить до 5 активаций на каждого пользователя. Чтобы не терять активацию при переустановке Windows в папке с надстройкой есть специальный файл Activator, позволяющий сохранить резервную копию лицензионного ключа и восстановить её потом после переустановки системы. Где получить лицензионный ключ Если вы уже покупали полную версию надстройки ранее, то ваш лицензионный ключ всегда можно найти на сайте в разделе PLEX по ссылке (если зайти на сайт под вашим логином-паролем). Новым покупателям лицензионный ключ также будет продублирован по почте в письме после покупки.
Как теперь обновлять надстройку Так же, как и раньше, только скачать последнюю версию надстройки теперь может любой (даже без регистрации на сайте) по ссылке в разделе PLEX. А дальше просто заменяете все файлы из папки PLEX на файлы из распакованного скачанного архива - и все. Повторно активировать лицензионный ключ (на данном компьютере) уже не потребуется.
Восстановление активации PLEX при переустановке Windows В папку надстройки добавлен файл Activator.xlsm, позволяющий сохранить в него лицензионный ключ активированной надстройки PLEX и восстановить его потом, при необходимости, на данном компьютере. Это позволит не производить повторную активацию PLEX после переустановки Windows:
Сильно обновил (по факту, практически полностью переписал статью) про . Стандартными средствами такое Excel не умеет, но, при желании, все-таки можно реализовать с помощью Power Query, макросов или Мастера Сводных таблиц из старых версий Excel:
P.S. Забавно, как недоступные или трудоемкие задачи, которые пару лет назад можно было решить только хардкорным программированием на VBA, сейчас легко и изящно решаются с помощью надстройки Power Query. Будущее уже здесь, да
Термины "Power Query", "Power Pivot", "Power BI" и прочие "пауэры" все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel. Давайте разберемся.
Иногда бывают ситуации, когда заранее неизвестно сколько именно и каких строк нужно импортировать из исходных данных. Допустим, мы должны загрузить в 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.
В инструмент Случайно добавлена возможность создания списка названий штатов США.
Инструмент Сортировка по цвету теперь умеет работать с текстом, где символы имеют разное начертание или цвет внутри одной ячейки.
Появилась возможность добавлять свой префикс и суффикс к имени файла при сохранении листов как отдельных файлов в инструменте Сохранить как файлы из группы Листы.
В инструменте Сборка листов появилась возможность быстро добавлять все файлы из текущей папки и задавать желаемые имена для собираемых листов:
Плюс, само-собой, исправление ошибок и общая оптимизация кода и дизайна по-мелочи.
Как всегда, все купившие надстройку, могут смело по желтой ссылке в верхней части страницы. Обо всех обнаруженных ошибках и глюках можно смело писать здесь же в комментариях, в или мне на почту.
