• Архив

    «   Май 2018   »
    Пн Вт Ср Чт Пт Сб Вс
      1 2 3 4 5 6
    7 8 9 10 11 12 13
    14 15 16 17 18 19 20
    21 22 23 24 25 26 27
    28 29 30 31      

Обновление надстройки PLEX v.2018.1

Выпустил первое в этом году обновление для своей надстройки 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.
  • В инструмент Случайно добавлена возможность создания списка названий штатов США.
  • Инструмент Сортировка по цвету теперь умеет работать с текстом, где символы имеют разное начертание или цвет внутри одной ячейки.
  • Появилась возможность добавлять свой префикс и суффикс к имени файла при сохранении листов как отдельных файлов в инструменте Сохранить как файлы из группы Листы.
  • В инструменте Сборка листов появилась возможность быстро добавлять все файлы из текущей папки и задавать желаемые имена для собираемых листов:


Плюс, само-собой, исправление ошибок и общая оптимизация кода и дизайна по-мелочи.

Как всегда, все купившие надстройку, могут смело скачивать новую версию в разделе PLEX по желтой ссылке в верхней части страницы. Обо всех обнаруженных ошибках и глюках можно смело писать здесь же в комментариях, в специальной ветке на нашем Форуме или мне на почту.

Новая статья: Последнее слово

 Среди задач работы с текстом, которые мы неоднократно уже разбирали, немного особняком стоит вопрос извлечения из текста последнего слова или фрагмента по заданному разделителю. Вытащить первое слово - не проблема, а вот с последним все не так легко и очевидно, т.к. количество слов в каждой ячейке может различаться. Давайте разберем несколько принципиально разных способов это сделать, а именно:
  • формулы
  • макросы
  • обновляемый запрос через Power Query



Читать статью полностью

Новая статья: Личная Книга Макросов в Microsoft Excel

Написал подробную статью о том, как создать и использовать Личную Книгу Макросов - удобное хранилище персональной коллекции макросов, рано или поздно образующейся у каждого уважающего себя пользователя Excel :)



Читать статью полностью

Новая статья: Диаграмма Ганта в Power Query

Эта статья родилась после вопроса одного из моих слушателей с тренинга "Скульптор Данных в Microsoft Excel с Power Query". Вопрос был о том, как из вот такой таблицы по проектам:


... получить вот такую:


Т.е. "размазать" бюджеты по дням для каждого проекта, получив на выходе что-то похожее на диаграмму Ганта. Формулами такое делать скучно, макросами - сложно, вручную - лениво, а вот бесплатная надстройка Power Query для Excel в такой ситуации как раз блещет всей своей мощью и красотой.

Читать статью полностью

Новая статья: Анализ текста регулярными выражениями (RegExp) в Excel

Если вам часто приходится работать с текстом, анализируя и разбирая его на отдельные фрагменты по заданным шаблонам и правилам, то определенно имеет смысл внедрить в свою работу регулярные выражения (RegExp или, в народе, "регулярки";) - супермощный инструмент обработки текста, давно применяемый в современных языках программирования и текстовых редакторах. Excel не поддерживает регулярные выражения по умолчанию, но это можно легко исправить с помощью простой функции на VBA.

Спектр задач, которые могут решать регулярки - очень широк. Разберем несколько примеров:
  • извлекаем город из адреса
  • номер телефона из описания контакта
  • ИНН из реквизитов контрагента
  • автомобильный номер
  • сумму и номер счета из банковской выписки и т.д.

Читать статью полностью

Новая статья: Расчет средневзвешенного значения в Excel (формулами и в сводной)

Ну, что - всех с Наступившим! Надеюсь, что все вы хорошо отдохнули, отъелись (ха-ха) и готовы к новым трудовым подвигам. Хочется начать этот год с чего-нибудь не совсем обычного...

Что такое средневзвешенное значение (weighted average) и чем оно отличается от обычного среднего арифметического? Как рассчитать средневзвешенное в Excel? Формулами - легко, а вот с расчетом средневзвешенного в сводной придется повозиться и привлечь тяжелую артиллерию в виде Power Pivot и DAX.


Для пущей наглядности записал еще и видео вдогон:


Читать статью полностью

С Новым годом!



Друзья, коллеги, форумчане, постоянные и случайные гости сайта!
Позвольте поздравить вас с Наступающим Новым Годом и пожелать:
  • Здоровья вам и вашим близким - это самое главное. Заботьтесь о своем теле, кормите его правильной едой, гоняйте на спорт. Помните - оно у вас одно :)
  • Взаимопонимания и терпения, особенно при общении с идиотами. И поменьше этих самых идитов вокруг, а побольше - хороших людей. Помните, что вы - среднее арифметическое тех людей, которыми себя окружаете.
  • Стойкости и смелости в своих решениях. Вы не обязаны никому нравиться и не перед кем (кроме себя) отчитываться. Ваша жизнь - ваши правила. Оставайтесь собой, развивайтесь и жмите вперед.
  • Не терять фокус. Наметьте цели на год, декомпозируйте их на отдельные мелкие задачи и держите все это перед глазами. За дневной текучкой легко потерять истинные ориентиры и потратить время и энергию на рутину - не дайте этому произойти.
А чтобы было проще планировать и реализовывать свои планы - вот вам в подарок традиционный календарь в Excel на 2018 год с праздниками, выходными днями, нормами рабочих часов и списком задач:



Добавил в него Календарик-Пинарик - шикарный инструмент для личной мотивации от Дмитрия Литвака. Попробуйте - вам понравится ;)



Скачать Календарь на 2018 год

Итоги 2017



Ну что, пора подводить итоги года по традиции? Дабы не терять ориентиров и отслеживать динамику.

Тренинги
  • В 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 человек (физлиц и одиночек от компаний). Расписание на первое полугодие 2018 года уже есть.
Новые курсы
  • Запустил два новых тренинга - "Скульптор Данных в 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 - за ним будущее, однозначно.
  • Провел марафон "30 дней = 30 функций", где ежедневно (почти) выкладывал новую статью с подробным описанием и примерами тридцати самых нужных функций Excel.
Надстройка PLEX для Excel 2007-2016
  • Выпустил 4 обновления своей надстройки в этом году.
  • Наконец-то перевел весь интерфейс на английский, так что теперь есть оба варианта.
Книги
  • Вышла в печать и в электронном виде моя вторя книга "Microsoft Excel: Мастер Формул" для тех, кто перерос ВПР.
  • Первая книжка "Microsoft Excel: Готовые решения", как ни странно, до сих пор продается. Буду готовить в этом году обновление и второе издание, т.к. за последние пару лет много что поменялось в версиях - есть, что добавить
Вот как-то так.
На следующий год уже планов громадьё, корпоративными заказами забит до мая под завязку.

А как прошел ваш год? :)

Новая статья: Импорт курса биткойна в Excel через Power Query

Как просчитать прибыльность инвестиционного портфеля криптовалюты, загрузив в Excel с помощью Power Query обновляемый курс покупки с сайта обменника, биржи или торговой площадки. Посвящяется тем 100500 человекам, что за последние пару месяцев написали мне в почту или в личку на форуме этот вопрос :)



Ничего особо сложного или выдающегося тут нет - для Power Query это "семечки", делается за пару минут. Кроме того, подобным образом можно импортировать в Excel курс не только биткойна, но и любой другой валюты - принцип совершенно аналогичный.

Читать статью полностью

Новая версия надстройки PLEX 2017.4 для 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) и, при необходимости, учитывать регистр.

Прочие доработки и изменения

  • В инструмент Текст добавлен универсальный вариант Авто для преобразования чисел-как-текст в полноценные числа.
  • Во все диалоговые окна добавлены оранжевые круглые кнопки с вопросительным знаком для вызова соответствующих разделов онлайн-справки и получения подробной информации по каждому инструменту надстройки.
  • Инструмент Редизайн кросс-таблицы в плоскую переехал из меню Сводная таблица в выпадающий список Трансформация:

Плюс, как всегда, множество мелких доработок, исправлений ошибок, оптимизация кода и т.д.
Купившие полную версию, как и всегда, качают обнову бесплатно по желтой ссылке в заголовке на странице PLEX. Английская версия там же - в архиве.

Новая статья: Сравнение двух таблиц в Excel

Есть много способов сравнить две таблицы в Microsoft Excel и на эту тему я уже неоднократно писал статьи и делал видеоуроки. Теперь же захотелось "подвести черту" и подробно описать три самых любимых мной способа сравнения:
  • функцией ВПР (VLOOKUP) - классика жанра
  • с помощью сводной таблицы - хороший вариант для больших таблиц
  • с помощью надстройки Power Query - самый красивый способ, да еще и с автоматическим обновлением
Разбор вариантов будем делать на двух версиях прайс-листа - старой и новой. Задача: быстро найти отличия, т.е. новые и удаленные товары и товары, у которых изменилась цена:



Читать статью полностью

Новая статья: Трансформация столбца в таблицу

Иногда при выгрузке из каких-либо корпоративных программ, данные достаются нам в не очень удобном виде. Один из самых неприятных вариантов - это вся информация в одном длинном столбце, из которого придется делать нормальную двумерную таблицу:


Давайте разберем несколько способов сделать это быстро и красиво, а именно:
  • формулами и, в частности, функцией ДВССЫЛ (INDIRECT)
  • через Power Query
  • макросом из надстройки PLEX
Читать статью полностью

Новая статья: Самая длинная победная серия

Иногда при работе в Microsoft Excel приходится искать самую длинную последовательность значений, удовлетворяющих заданному условию. Например, самую длинную непрерывную цепочку поставок, самую длинную последовательность сделок, рабочих дней в табеле и т.д. В спорте подобную штуку называют еще "победной серией" (winning streak):



Решить проблему можно с помощью изящной формулы массива, но чтобы разобраться как именно и почему она работает, придется зайти немного издалека...

Читать статью полностью

Новая статья: Совместное редактирование и Автосохранение в Excel 2016

На практике часто приходится сталкиваться с ситуациями, когда с одним файлом Excel должны одновременно работать несколько пользователей. В Microsoft Excel всегда была такая возможность, но в прошлых версиях реализована она была слабо. Начиная с Excel 2016 появился новый режим совместного редактирования (Co-Authoring) - более удобный и мощный:


Но обратной стороной медали стало Автосохранение - переключатель которого появился в левом верхнему углу окна на панели быстрого доступа:


Наравне с плюсами, Автосохранение имеет и много неприятных минусов, о которых стоит рассказать подробно.

Читать статью полностью

Новая статья: Подсветка заданных столбцов диаграммы

Для автоматического выделения ячеек цветом по условию в Excel есть условное форматирование. А как реализовать похожую технику, но для столбцов в диаграмме? Чтобы некоторые столбцы автоматически выделялись цветом при выполнении определенного условия. Например, подсвечивать:
  • все столбцы со значениями выше среднего
  • три самых больших (Top-3)
  • минимальное и максимальное значение
  • столбец, выбранный в выпадающем списке в ячейке листа и т.д.
На самом деле, все очень несложно




Читать статью полностью
 

Новая статья: Генерация дубликатов строк

В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!


Сделать это можно двумя основными способами: при помощи макросов и через Power Query.

Читать статью полностью
Страницы: 1 | 2 | 3 | 4 | 5 | ... | 23 | След.