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

• формулы
• макросы
• обновляемый запрос через Power Query
  

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

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


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


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

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

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

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


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


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

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

Тренинги

• В 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: Готовые решения", как ни странно, до сих пор продается. Буду готовить в этом году обновление и второе издание, т.к. за последние пару лет много что поменялось в версиях - есть, что добавить
  

Вот как-то так.
На следующий год уже планов громадьё, корпоративными заказами забит до мая под завязку.

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

Выпустил очередное, четвертое в этом году обновление своей надстройки 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. Английская версия там же - в архиве.

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


Давайте разберем несколько способов сделать это быстро и красиво, а именно:

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

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

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


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


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

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