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

Выпустил очередное обновление моей надстройки PLEX для Microsoft Excel до версии 2021.1. Из новых "плюшек" имеются:
Дубликаты и уникальные

Вместо старых инструментов Извлечение уникальных и Удаление дубликатов теперь будет новый - Дубликаты и уникальные:

 

Возможности стали на порядок мощнее:

 

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

Работа с гиперссылками и адресами email
 В раздел Книга добавлен новый инструмент для работы с гиперссылками в ячейках листа:

 

 

Умеет:

  • «оживлять» текстовые ссылки, превращая их в полноценные рабочие
  • Удалять гиперссылки, оставляя в ячейках только текст (т.е. ссылки перестают работать)
  • Открывать все ссылки в выделенном диапазоне в браузере на отдельных вкладках или в Outlook в виде новых сообщений.
  • Заменять текст гиперссылок на их фактический адрес вида http…
  • Трансформировать прямые гиперссылки в функцию Excel ГИПЕРССЫЛКА (HYPERLINK).
  • Исправить, заменяя в ссылке один фрагмент на другой, например “yandx” на “yandex”
  • Извлечь ссылки или адреса эл.почты из выделенного диапазона, содержащего мешанину из текста и ссылок.
Заполнение пустых ячеек во всех направлениях

Инструмент Заполнить пустые теперь умеет делать это не только вниз, но и в остальных направлениях:

 

Фильтр + действие

Вместо трёх старых инструментов Фильтровать и копировать / перенести / удалить добавлен один универсальный:

 

Выделяете любую ячейку с данными и запускаете - выводится вот такое диалоговое окно:

 

Умеет фильтровать на листе или в текущей таблице строки по любому из заданных условий:

  • Равные/не равные заданному значению
  • Содержащие/не содержащие заданную подстроку
  • Соответствующие заданному шаблону (с использованием символов *#? и т.д.)

Найденные строки можно скопировать или перенести на новый лист или в новую книгу или же удалить.

АнтиФильтр

В раздел Фильтры добавлен инструмент Антифильтр:

 

Выполняет инвертирование всех отфильтрованных в данный момент строк,  т.е. скрытые фильтром строки делает видимыми, а видимые – скрывает.

Удаление скрытых / не скрытых фильтром строк

Добавлены два инструмента для выполнения простых, но весьма частых  операций – удаления скрытых или, наоборот, не скрытых в данный момент  фильтром строк:

 

Сравнение диапазонов цветом

Доработан имеющийся инструмент Парная подсветка дубликатов:

 

Теперь его можно использовать при выделении двух (или более)  диапазонов для цветового выделения совпадающих значений и визуального  сравнения:

 

Также увеличено с 20 до 90 количество цветовых пар заливка-шрифт для выделения найденных дубликатов.

Типы ссылок для аргументов в Библиотеке Формул

Важное усовершенствование, о котором давно просили. Для пользовательских формул в Библиотеке формул теперь можно задавать тип ссылки (абсолютная, относительная, смешанные…) для каждого аргумента:

 

Удаление не-ASCII символов

В инструмент Текст на вкладку Очистить добавлена  возможность удаления всевозможных нестандартных Unicode-символов не из  ASCII набора: иероглифов, диакритических знаков и т.п.

 

Также добавлена аналогично работающая пользовательская функция OnlyASCII:

 

Выбора стандарта транслитерации

В инструменте Текст на вкладке Другое добавлена возможность выбора системы при транслитерации кириллицы:

 

Функции GetEmail и GetEmail2 для извлечения адресов эл.почты

Добавлены несколько новых пользовательских функций для извлечения из текста адресов электронной почты и гиперссылок.

 GetEmail(ячейка; разделитель) – извлекает из текста в исходной  ячейке все найденные адреса эл.почты и выводит их одной строкой через  заданный символ-разделитель:

 

Если символ-разделитель не задан, то по умолчанию используется точка с  запятой с пробелом. При желании, можно указать любой разделитель или  использовать функцию Excel СИМВОЛ (CHAR) с кодом нужного знака, например СИМВОЛ(10) вставит между адресами символ переноса строки (Alt+Enter).

Также добавлена функция GetEmail2, выполняющая аналогичное  извлечение адресов эл.почты из текста, но возвращающая сразу все  результаты в виде горизонтального динамического массива (работает только  в Office 365 с установленными обновлениями поддержки динамических  массивов):

 

Если нужно, чтобы результаты располагались вертикально в столбец –  можно использовать эту функцию в связке со стандарной экселевской  функцией ТРАНСП (TRANSPOSE):

 

Функции GetURL и GetURL2 для извлечения адресов сайтов

Эти функция делает то же самое, что и предыдущие, но для URL-адресов веб-страниц:

 

Новые функции регулярных выражений

 RegExpExtract2(Текст; Шаблон; УчитыватьРегистр) – работает  аналогично уже имеющейся функции RegExpExtract, т.е. извлекает из  исходного текста все фрагменты соответствующие заданному шаблону  регулярного выражения, однако выводит сразу все результаты в виде  динамического массива (работает только в Office 365 с установленными  обновлениями поддержки динамических массивов).

 RegExpTest(Текст; Шаблон; УчитыватьРегистр) – проверяет  соответствие текста заданному шаблону регулярного выражения. Последний  аргумент определяет, нужно ли (1) или нет (0) учитывать регистр (по  умолчанию не учитывается). На выходе функция выдаёт логические значения  ЛОЖЬ (FALSE) или ИСТИНА (TRUE), соответственно.

 RegExpReplace(Текст; Шаблон; Новый_текст; УчитыватьРегистр) – ищет в исходном тексте фрагменты соответствующие заданному шаблону регулярного выражения и заменяет их на новый текст.

Среднее арифметическое по цвету заливки/шрифта

К имеющимся функциям суммирования и подсчета количества ячеек с определенным цветом заливки (SumByCellColor, CountByCellColor) и шрифта (SumByFontColor, CountByFontColor) добавлены аналогичные функции расчета среднего арифметического: AverageByCellColor и AverageByFontColor.

Также теперь во втором аргументе всех этих функций в качестве ячейки с  образцом цвета теперь можно указывать диапазон из нескольких ячеек с  разными цветами.

14 новых формул в Библиотеке формул

Помимо пользовательских макрофункций PLEX, в Библиотеку формул добавлены новые полезные формулы:  
  1. Извлечение текста в скобках
  2. Кол-во совпадений в двух диапазонах (без учета регистра)
  3. Кол-во точных совпадений с заданным значением (с учетом регистра)
  4. Кол-во уникальных числовых значений в диапазоне
  5. Кол-во уникальных текстовых значений в диапазоне
  6. Подсчет кол-во определенных символов в ячейке
  7. Нормализация текста (удаление знаков препинания, лишних пробелов…)
  8. Проверка вхождения числа в заданный интервал
  9. Гиперссылка на первое вхождение заданного значения в список
  10. Создание гиперссылки для отправки email с заданными параметрами
  11. Вывод имени текущего листа (без макросов)
  12. Вывод имени текущей книги (без макросов)
  13. Вывод названия текущей папки (без макросов)
  14. Вывод полного пути к текущему файлу (без макросов)
Плюс много мелких доработок, улучшений и исправлений ошибок. Детальное описание всех новых функций и изменений можно посмотреть здесь.

Скачать последнюю версию надстройки PLEX.

Новая статья: 5 способов поиска ближайшего числа в Microsoft Excel

На практике весьма часто возникают случаи, когда нам с вами нужно  найти ближайшее значение в наборе (таблице) по отношению к заданному  числу. Это может быть, например:

  • Расчет скидки в зависимости от объема.
  • Вычисление размера бонусов в зависимости от выполнения плана.
  • Калькуляция тарифов на доставку в зависимости от расстояния.
  • Подбор подходящей тары для товара и т.д.

Причем окргуление может требоваться как в меньшую, так и в большую сторону - в зависимости от ситуации.

Есть несколько способов - очевидных и не очень - для решения такой задачи.



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

Новая статья: Разделение таблицы по листам

Как разобрать данные из одной таблицы сразу на несколько листов по  заданному критерию.Разбираем два способа - с обновлением (через Power  Query) и без (только VBA).

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

Новый онлайн-курс: Сводные таблицы от и до

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

Выложил очередной и долгожданный многими курс - по сводным таблицам в Microsoft Excel. 30 уроков, более 3 часов видео, файлы упражнений и самостоятельной отработки и финальный тест для самоконтроля - всё как положено.

Основные темы курса:
  • Подготовка данных для анализа в сводной. Как правильно организовать исходную информацию, чтобы с ней удобно было работать в сводной таблице.
  • Построение сводной и первичные настройки. Как строить, трансформировать, допиливать внешний вид под корпоративные требования.
  • Визуализация. Добавляем наглядность и красоту вашим отчетам с помощью условного форматирования, спарклайнов, гистограмм, значков и т.п.
  • Хитрости сортировки, фильтрации и группировки данных в сводных таблицах. О, тут много нюансов!
  • Вычисления в сводной - как считать что-то посложнее банальной суммы. Рассмотрим вычисления долей, процентов, нарастающих итогов, рангов, индексов значимости и прочей экзотики, включая вычисления по собственной логике с помощью вычисляемых полей и объектов.
  • Основы работы в Модели Данных Power Pivot и преимущества построения сводной по ней по сравнению с классической сводной. Будущее уже здесь :)

Открыть страницу с описанием курса

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

Новая статья с видео о том, как реализовать одновременную фильтрацию сразу нескольких сводных таблиц одним общим срезом. Если сводные построены на основе одного источника, то всё просто. Если на основе разных, то придётся немного пошаманить с Power Pivot и Power Query :)


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

Новая статья: Картинки как подписи на диаграмме

Простой, но красивый трюк с добавлением на диаграмму картинок в качестве подписей. Подойдет для визуализации логотипов компаний, брендов, флагов стран и т.п.



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

Новая статья: Преимущества сводной по Модели Данных

Новая статья и видео с подробным разбором плюсов и преимуществ построения сводных таблиц по Модели Данных Power Pivot по сравнению с классическими сводными в Excel.



Из самого сочного:
  • Связи между таблицами без ВПР.
  • Любые сложные вычисления в сводной на языке DAX.
  • Подсчёт количества уникальных значений.
  • Пользовательские наборы элементов по строкам и столбцам вместо фильтрации.
  • Конвертация сводной в функции кубов
  • ... и т.д.
Читать статью полностью

Новая статья: Поиск последнего вхождения (инвертированный ВПР)

Все классические функции поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP), ПОИСКПОЗ (MATCH) и  им подобные имеют одну важную особенность - они ищут от начала к концу,  т.е. слева-направо или сверху-вниз по исходным данным. Как только  находится первое подходящее совпадение - поиск останавливается и  найденным оказывается только первое вхождение нужного нам элемента.

Что же делать, если нам требуется найти не первое, а последнее  вхождение? Например, последнюю сделку по клиенту, последний платёж,  самую свежую заявку и т.д.?

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

Новая статья: Парсим нетабличные данные с сайта

Сплошь и рядом встречаются сайты, где при попытке импортировать с них данные в Excel Power Query просто в упор не видит там таблиц с нужной нам информацией. Причин для этого может быть несколько, но  чаще всего это происходит потому, что веб-дизайнер при создании таблицы  использовал в HTML-коде страницы не стандартную конструкцию с тегом <TABLE>, а её аналог - вложенные друг в друга теги-контейнеры <DIV>.  Это весьма распространённая техника при вёрстке веб-сайтов, но, к  сожалению, Power Query пока не умеет распознавать такую разметку и  загружать такие данные в Excel.   Тем не менее, есть способ обойти это ограничение ;)

Давайте разберемся с этой задачей на примере парсинга цен и описаний товаров с маркетплейса Wildberries:




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

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

Имеем список объектов (например, товаров) с пометкой, к какому набору  (корзине) каждый из них относится. Необходимо разложить объекты по своим  наборам, сформировав таблицу как на рисунке справа:

 

Похожие задачи встречаются на практике весьма часто - в случаях, когда приходится распределять те или иные ресурсы:

  • сотрудников по командам
  • водителей по маршрутам
  • клиентов по менеджерам
  • товары по корзинам и т.д.

В прошлом я уже делал статью и видео о похожей задаче, где мы  разбирали как переложить одномерный столбец с данными в двумерную  таблицу, но там ситуация была проще, т.к. каждый набор (строка) имел  одинаковый размер (число столбцов). Здесь же количество элементов в  наборах заранее не известно и не равно друг другу, так что подход  придётся изменить.

Чтобы у вас был выбор, давайте разберем несколько способов решения этой задачи.

Новая статья: Сводная по таблице с многострочной шапкой

Одна из самых распространенных проблем (и частых вопросов на тренингах) в том, как построить сводную таблицу, если в качестве исходных данных тебе досталась вот такая "красота":



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



Хороший пример, если вы осваиваете возможности Power Query - тут они раскрываются во всей красе.
Ну и, само-собой, видео для тех, кому лень читать :)

Новая статья: Выпадающий список с показом изображений

Обновил старую статью о том, как создать в Microsoft Excel выпадающий список с показом выбранного изображения.
В новых версиях Excel описанный там подход уже не работает и нужен другой метод:



Само-собой, к статье приложен и видеоурок:

Читать статью и смотреть видео

Новая статья: Как правильно использовать Стили в Excel

Стиль в Microsoft Excel - это сохраненная совокупность параметров  форматирования ячейки. Единожды создав стиль, его затем можно  многократно применять к другим ячейкам, моментально оформляя их нужным  вам образом, что неимоверно ускоряет повседневную работу в Excel.
Главная прелесть стилей в том, что с их помощью можно не просто раскрашивать ячейки, а создавать крайне полезные в работе нестандарные форматы:
  • Стили для своих единиц измерения
  • Стили тысяч или миллионов
  • Стили с цветом и процентами для плана
  • Стили со спецзначками и спецсимволами
  • Стили со сложными условиями и эмодзи
Разбираем всё это в очередной статье с видео:


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

Новая статья: Вафельная диаграмма в Excel

Разбираем два способа построить вафельную диаграмму (waffle chart) в Excel - с помощью условного форматирования и отрихтованной линейчатой диаграммы (bar chart), чтобы получить вот такую красоту:



Отлично подойдет для отображения прогресса по любым задачам.

Читать статью полностью и смотреть видео
 

Новая статья: Массовая замена текста в Power Query функцией List.Accumulate

Продолжая начатую в прошлой статье тему про массовую замену текста, разбираемся с решением этой задачи уже в Power Query.


Использовать будем одну из не самых простых для понимания функций языка М - List.Accumulate:


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