Обновление надстройки PLEX (версия 2020.1 от 30 марта 2020 г.)

Благодаря самоизоляции, закончил и выложил очередное обновление моей надстройки PLEX для Microsoft Excel.
Список новых "плюшек" следующий:

Выделятор
Встроенные возможности Microsoft Excel по выделению ячеек по заданным условиям (Главная – Найти и выделить – Выделить группу ячеек) весьма скромны. Начиная с этой версии, в надстройке PLEX в группе Диапазоны появился новый инструмент Выделятор с гораздо более мощными инструментами в этом плане.  
Идея его работы проста – выделять ячейки по определенным условиям:



Список вариантов по критериям внушительный:
  • Числа: больше, меньше, (не) равно, (не) между.
  • Текст: (не) равен, (не) содержит, (не) начинается с, (не) заканчивается на, соответствует шаблону (можно использовать символы подстановки *?#), (не) совпадает с любой ячейкой из указанного списка, (не) содержит любое слово из указанного диапазона, содержит любой символ из списка, содержит цифры, содержит кириллицу (А-Я), содержит латиницу (A-Z), содержит лишние пробелы, содержит непечатаемые знаки.
  • Даты: равна, до, после, (не) между, попадает на рабочий день, попадает на выходной, попадает на определённый день недели, попадает в заданный год, квартал или месяц.
  • Формат: содержит полужирный текст, курсив, подчеркивание, зачёркнутый текст, есть любая заливка, любой цвет шрифта кроме чёрного, заливка как в ячейке-образце, цвет шрифта как в ячейке-образце.
  • Другое: (не) пустые ячейки, ячейки с формулами, с константами, со ссылками на другие листы/книги, с ошибками, (не) защищенные, с заданным уровнем группировки, с гиперссылками.
При нахождении ячейки удовлетворяющей условию, можно выделить только её или же всю строку/столбец, где она находится.

Склонение ФИО по падежам

По многочисленным просьбам добавлена пользовательская функция FIO, которая умеет:
  • Выстраивать ФИО в правильном порядке, если в исходных данных они стоят неправильно.
  • Склонять ФИО в родительном и дательном падежах.
  • Выводить, при необходимости, сокращенную форму (Фамилия И.О.)
Синтаксис функции:
=FIO(NameAsText; NameCase; ShortForm)
где:
  • NameAsText – ячейка с исходным ФИО (полным или без отчества)
  • NameCase – символьный код падежа ("Д" или "D" – дательный; "Р" или "R" – родительный). По умолчанию – без преобразования.
  • ShortForm – логическое значение, обозначающее нужно ли (1 или ИСТИНА) или нет (0 или ЛОЖЬ) преобразовывать ФИО в сокращенную форму (Фамилия И.О.). По умолчанию, принимается за 0 (без преобразования).
В самом простом варианте эту функцию можно использовать для выстраивания ФИО в правильном порядке:

Со вторым аргументом можно преобразовать ФИО в требуемый падеж:

Третий (необязательный) логический аргумент позволит выводить ФИО в нужном падеже и, дополнительно, в сокращенной форме:

Безусловно, найдутся зубодробительные ФИО, где эта функция сработает некорректно, ибо русский язык велик и могуч. Однако, с 99,9% процентами типичных российских имен все работает вполне успешно (тестировал на 5000+ ФИО реальных людей).

Определение пола по имени

Добавлена функция с романтическим названием GetSex для определения пола человека по имени (полному или частичному ФИО).  Возвращает 1 (женский), -1 (мужской) или 0 (не определен):



Опять же, предусмотреть все возможные несуразные имена, которые родители умудряются давать своим детям, невозможно в принципе. Но на большинстве наших с вами соотечественников (в том числе СНГ) работает вполне надёжно.

Конвертация условного форматирования в статическое


Условное форматирование – красивая, но весьма ресурсоёмкая штука. Чтобы избежать торможения на больших таблицах, можно преобразовать условное форматирование в статическое. Внешний вид ячейки сохранится, но не будет больше меняться при изменении данных (и, соответственно, тормозить).
Обратите внимание на 3 момента:
  • Обратное преобразование невозможно.
  • Такие виды условного форматирования, как гистограммы или значки нельзя преобразовать в статику.
  • Из-за ограничений самого Excel эта функция работает только начиная с 2010-й версии Microsoft Excel.
Исправление УФ-ада

Если у вас есть таблица с несколькими правилами условного форматирования и вы, работая с этой таблицей:
  • удаляете в ней строки
  • меняете строки местами
  • вырезаете-копируете фрагменты таблицы
… то очень быстро вы придёте к тому, что иногда называют "адом условного форматирования", когда исходные правила, размножаясь и копируясь вместе с ячейками, начинают десятками плодиться на вашем листе.
Команда Исправить УФ-ад призвана легко и быстро исправлять такие ситуации. Просто выделите исходный диапазон с правилами условного форматирования и выберите эту команду в списке Формат. Все дублирующиеся правила будут удалены, а правила с первой строки будут распространены на весь диапазон:


Пользовательские стили

Стили в Microsoft Excel - это замечательный и удобный инструмент, которым мало кто (почему-то) пользуется. Стиль представляет собой совокупность числового формата и визуального форматирования (заливки, цвета шрифта и т.д.) для ячейки. Если вы используете в дизайне своих отчетов одни и те же приёмы оформления, то стили могут сильно упростить и ускорить всю вашу работу.
В группе Диапазоны – выпадающий список Форматы – добавлены команды для подгрузки в текущую книгу набора пользовательских стилей из файла styles.xlsx, входящего теперь в состав надстройки (лежит в папке PLEX). После выполнения загрузки на вкладке Главная в группе Стили добавляется больше двух десятков новых стилей для удобного и наглядного форматирования различных типов данных (проценты, план-факт, градусы, квадратные метры и т.д.):


Как выглядят эти стили на практике можно посмотреть в том же файле styles.xlsx, просто открыв его:


При необходимости вы можете сами в будущем добавлять в этот файл свои стили через команду Главная – Стили – Создать стиль ячейки.

Исправления ошибок и мелкие улучшения
  • Исправлена ошибка в инструменте Курс валют, связанная с редизайном сайта ЦБ РФ.
  • В инструменте Текст разделено удаление букв, чисел, знаков препинания и пробелов для большего удобства и наглядности.
  • Большинство веб-запросов (курсы валют, ставки, онлайн-справка и т.д.) переведены на безопасный протокол HTTPS.
  • Исправлена ошибка в функциях SumByCellColor, SumByFontColor, CountByCellColor, CountByFontColor, возникающая при некоторых экзотических оттенках используемых цветов.
  • Добавлена возможность выполнять вычисления с дробными числами в мини-калькуляторе.
  • Исправлено более 30 мелких ошибок, улучшена стабильность и быстродействие.
Как обычно, новую версию можно скачать тут.
Обо всех замеченных ошибках можно смело писать мне на почту или в личку на форуме.
Берегите себя и своих близких, оставайтесь (по возможности) дома и будьте здоровы!
30 марта 2020 0:29
УФ-ад - это да!
Удивляюсь, что Microsoft забил на эту проблему. Спасибо Вам, Николай, огромное!
30 марта 2020 15:01
Да, приходится самим костыли выпиливать :)
30 марта 2020 14:13
Николай, спасибо большущее! функция ФИО просто пушка!
30 марта 2020 15:00
Спасибо, я старался :)
30 марта 2020 16:38
Обновление март 2020
Николай, спасибо за обновление, но есть моменты... По ВЫДЕЛЯТОРУ - да он выделяет, но может Вы рассмотрите чтобы он и выделял и сразу отфильтровывал запрашиваемое? А то в больших файлах колесиком приходится покрутить чтобы в выделенном что-то исправить. И второе: В файле "СТИЛИ" После добавления в него своих стилей в новой книге когда подгружаешь ваш файл с внесенными своими стилями, он почему-то грузит только Ваши... Что не так далаю?
30 марта 2020 18:22
Насчет прокрутки к выделенному - подумаю.
Насчет фильтрации - очень сомневаюсь, ибо могут быть выделены очень разные ячейки и фильтровать их будет затруднительно.
По Стилям загружаться должны все стили из styles.xlsx.

Обратите внимание, что нужно создавать свои стили через команду Стили - Создать пользовательский стиль или же копировать имеющийся там стиль (правой - Дублировать) и потом менять его параметры. Не забудьте на выходе сохранить файл styles.xlsx
31 марта 2020 12:27
Спасибо, Николай за пояснения в работе со стилями. А по вопросу ВЫДЕЛЯТОРа я имел ввиду что-то более крутое чем у уважаемого Фамоса: www.planetaexcel.ru/forum/index.php?PAGE_NAME=profile_view&UID=48563
www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=119756

То что он сделал - это бомба. Такой инструмент очень бы подошел и к Вашей надстройке, так как такого инструмента очень не хватает в штатном Excel.
20 апреля 2020 10:07
Хорошо, что пригодилось - спасибо за оценку))
На самом деле с фильтром в Excel много проблем и основная (на мой взгляд), это то, что тип передаваемых значений для фильтрации меняется от формата ячеек в столбце. Из-за этого я сначала перевожу в общий - передаю список/фильтрую, а потом возвращаю форматы обратно.
PLEX, в отличие от моей формы, продукт универсальный и, что более важно, коммерческий, поэтому Николаю Владимировичу для того, чтобы добавить что-то похожее в свою надстройку, нужно убедиться в стабильности работы такого инструмента при всех возможных условиях (кроме самых редких, возможно).
Универсализация каждого инструмента сильно бьёт по его скорости, а возможность отмены (откатка) действия — и того сильнее.
Именно поэтому, на выходе может получится нечто совсем другое, скорее всего, более медленное, но точно более универсальное и стабильное
Как-то так…

P.S.: Famous (Феймос) - известный))
1 апреля 2020 0:12
Пожелание
Здравствуйте! Спасибо Вам, Коля, за такой душевный труд!
Гармонии в душе и божественной благодати.
Всегда удачи в делах и достижения поставленных  целей в начинаниях.
6 мая 2020 12:05
Спасибо, Геннадий! :)
2 апреля 2020 1:48
Plex 2020.1
Николай, при всех плюсах и минусах, плюсов гораздо больше. Интересен вектор развития Plex. Радует, что родина инструмента все таки РФ. Сил, успехов, здоровья, как всегда! Да, и с прошедшим вас!!! Спасибо за труд! Пользуюсь регулярно. :)
6 мая 2020 12:05
А можно про минусы чуть подробнее и в почту info@planetaexcel.ru
Готов исправить, что смогу :)
6 апреля 2020 11:06
Николай, спасибо большое!
УФ-ад - это просто огонь! Реальная помощь!
7 апреля 2020 11:31
Николай! Спасибо большое!
10 апреля 2020 23:11
Закончатся лицензии - обязательно куплю новую! Спасибо большое за экономию времени!
14 апреля 2020 8:42
Николай! Спасибо большое!
Не могу редактировать формулы в библиотеке?
6 мая 2020 12:06
Встроенные - нет, можно только свои.
17 апреля 2020 13:20
Николай, спасибо Вам за Вашу работу. У меня вопрос перехода таблиц с объединенными ячейками к обычным таблицам, с возможностью превращения их в умные таблицы, может у Вас есть несколько  приемов и примеров к ним как от этого уходит, так как довольно много данных которые объединяются по одному критерию. Спасибо
20 апреля 2020 10:11
здравствуйте!
Представьте, сколько подобных вопросов прилетает автору целого сайта и множеству уроков по Excel…
Гораздо быстрее вам помогут на форуме - он очень активный. Создайте тему, придумайте название, согласно Правил (должно отражать задачу) и прикрепите файл-пример
27 апреля 2020 7:23
Пользовательские стили в PLEX
Николай, доброго дня.
Очень нравится перенос пользовательских стилей. Я чаще всего использую умные таблицы, где так же применяю свои стили. Но как раз стили умных таблиц у меня с помощью этой функции не получается перенести. Это я что-то делаю не так или такой перенос и не предусматривался?
27 апреля 2020 12:33
Максим, эта функция пока переносит только стили ячеек (не умных таблиц).
Но мысль хорошая - попробую реализовать в следующей версии, спасибо! :)
29 мая 2020 10:40
Добрый день Николай. Функция CountByFontColor  работает, как суммирование нарастающим итогом (даже сама автоматически пересчитывает сумму). Но если очистить в расчетном диапазоне какую ни будь ячейку, то значение  суммы не пересчитывается на уменьшение суммы :-(
Наверх