• Больше нет двух разных файлов PLEX.xlam и PLEX_Eng.xlam для русской и английской версии надстройки соответственно. Теперь оба языка и механизм локализации живут в одном файле PLEX.xlam. Нужный вам язык интерфейса можно выбрать на вкладке PLEX в меню Помощь - Параметры.
  
• Добавлено меню Аудит с инструментами для проверки, отладки и поиска ошибок в сложных Excel-книгах, включающее в себя новую Цветовую карту для подсветки ячеек с разными типами формул, ссылок и содержимым, а также удобную пометку ячеек различными значками.
  
• Сравнение диапазонов и поиск дубликатов теперь можно производить не по одному, а сразу по нескольким столбцам.
  
• Добавлена возможность быстрой отправки выделенных листов или диапазонов по электронной почте - как вложения или сразу в теле письма в разных форматах.
  
• При сборке листов теперь можно указывать несколько имён листов, в т.ч. используя маски (например, *отчет*)
  
• Добавлены команды скрытия и удаления всех строк-столбцов кроме выделенных
  
• Настройки горячих клавиш теперь можно экспортировать-импортировать при смене компьютера.
  
• Добавлена безопасная имитация объединения ячеек.
  
• Гиперссылки на лист теперь можно вставлять непосредственно после копирования нужных файлов/папок из Проводника.
  
• Инструмент работы с текстом теперь понимает аббревиатуры при изменении регистра.
  
• Добавлена автоматическая загрузка курсов валют ЦБ Узбекистана на заданную дату.
  
• В функцию исправления-склонения фамилий FIO добавлена поддержка творительного падежа.
  
• В инструмент Запрос к нейросети добавлены новые провайдеры: Deepseek, Grok, Gemini, Polza.ai (агрегатор с оплатой в рублях) Из списка доступных моделей убраны старые неактуальные модели и добавлены новые, появившиеся за последнее время.
  

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

В Microsoft Excel подобная задача легко решается с помощью стандартной функции РАНГ (RANK), первый аргумент которой - это число, для которого мы определяем ранг, второй - весь список значений, а третий определяет сортировку (0 - наивысший ранг получает наибольшее значение, 1 - наивысший ранг получает наименьшее значение):

Однако, вычисление рангов с помощью мер на языке DAX в Power Pivot и Power BI уже не так очевидно. Давайте разберемся в этом вопросе.

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

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

Реализовать такое, на самом деле, несложно - особенно, если вам не страшно копнуть чуть глубже интерфейса Power Query, в язык М, который работает у него "под капотом". Давайте разберём пару типичных сценариев:

• мы знаем точный адрес нужной ячейки
  
• мы не знаем точное местоположение ячейки (плавающая ячейка) и её сначала нужно найти
  

Создание большого количества вложенных папок по определённой заданной структуре - весьма типовая и крайне скучная задача. Представьте на секунду, что для сбора данных от каждого отдела в вашей компании вам нужно на сетевом или локальном диске создать отдельную папку, где внутри должны быть подпапки с названиями дирекций (Коммерция, Производство, Финансы), а внутри них отделы (Закупки, Маркетинг...), а внутри некоторых отделов третьим уровнем ещё и рабочие группы:

Легко сообразить, что подобная структура может быть весьма многоуровневой и создание такого "монстра" вручную займёт массу времени. Давайте попробуем автоматизировать подобный процесс с помощью парочки несложных формул и макроса на Visual Basic.

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

Давайте научим Power Query выполнять запросы к нейросетям для обработки и анализа загруженных данных. Делать это будем на примере DeepSeek - она в России пока ещё работает без VPN, стоит недорого, а по качеству отстаёт от флагманов в лице OpenAI или Claude не так сильно. Кроме того, весь разобранный далее процесс и код подключения можно легко адаптировать к любой другой нейросети - принципы у всех плюс-минус одинаковые.

Весь процесс разбивается на несколько простых шагов:

1. Получить API-ключ для подключения к нужной ИИ
   
2. Написать М-функцию запроса к нейросети в Power Query
   
3. Применить написанную функцию
   

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

Как обычно, в Excel есть несколько способов решить эту задачу - весьма различающихся по изяществу и сложности:

• Динамические массивы (в Excel 2024 и новее)
  
• Удобная лямбда-функция (если эту задачу приходится решать часто)
  
• Формула массива (работает в любой версии Excel)
  

В прошлом я уже делал разбор решения транспортной задачи на примере оптимизации доставки товаров со складов в магазины. Теперь же давайте попробуем использовать Microsoft Excel для решения другой классической задачи из того же раздела - прокладки кратчайшего маршрута из пункта А в пункт Б через несколько промежуточных точек транспортной сети. Частным случаем такой задачи ещё называют "задачу коммивояжера", когда нам нужно по кратчайшему маршруту обойти все точки, не заходя ни в одну из них дважды, а затем вернуться обратно в пункт отправления.

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

Подробно разбираемся с тем, как можно упростить и ускорить расчёты в Microsoft Power BI с помощью двух мощных инструментов - групп вычислений (calculation groups) и параметров полей (field parameters). Это позволит не создавать в вашей модели кучу однообразных мер:

Как собрать разношёрстные названия одних и тех же объектов в группы (кластеры) по максимальной похожести?

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

На помощь приходит Power Query, а точнее специальная функция нечёткой текстовой кластеризации из встроенного в Power Query языка М.

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

В 2020 году Microsoft выпустила обновление, добавляющее в Microsoft Excel 365 новую функцию ПРОСМОТРХ (XLOOKUP), пришедшую на замену классическим функциям поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP) и связке функций ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH). В отличие от предшественников, новая функция имеет простой и понятный синтаксис, умеет искать как по вертикали, так и по горизонтали, а также может искать снизу вверх, а не сверху вниз. Базовый синтаксис и возможности функции ПРОСМОТРX я уже подробно разбирал в этой статье с сопутствующим видеоуроком.

Теперь же давайте посмотрим на более продвинутые техники использования функции ПРОСМОТРX и скрытые её возможности:

• Множественные условия
  
• Использование массива искомых значений
  
• Результат в виде массива
  
• Двумерный поиск
  
• ПРОСМОТРX внутри СУММЕСЛИ
  
• Поиск на нескольких листах
  

• В инструменте работы с текстом появилась возможность массовой замены текста по справочнику, а также аналогичная по смыслу пользовательская функция TextReplace.
  
• Заодно сделал похожие функции ReplaceChars и RemoveChars, чтобы заменять в тексте ненужные символы на нужные и удалять ненужные сразу целым списком - быстро и удобно.
  
• Добавились инструменты для визуального поиска совпадений в двух выделенных диапазонах - теперь их можно выделять цветом (каждое совпадение - своим) и связывать линией.
  
• Кардинально улучшен запрос к нейросетям - теперь он поддерживает прокси (VseGPT.ru и ProxyAPI.ru) для работы с зарубежными нейросетями, которые ранее были недоступны в России без VPN и оплата которых была возможна только с зарубежных банковских карт. Теперь всё гораздо проще: регистрируетесь на сайте прокси, пополняете баланс любой российской картой, получаете API-ключ и выполняете любые ИИ-запросы прямо в Excel. Кроме ChatGPT и YandexGPT добавлены еще несколько моделей Antropic Claude.
  
• Появилась возможность вставлять в выделенные ячейки флажки-чекбоксы для быстрого создания анкет, чек-листов, списков дел и т.п.
  
• Добавил фильтрацию по содержимому Буфера обмена. Можно заранее скопировать в Буфер одну или сразу несколько ячеек со значениями, которые требуется отфильтровать, затем встать в любую ячейку нужного столбца и использовать эту функцию.
  

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

Основным инструментом анализа в таких случаях является матрица кросс-продаж (cross-selling matrix) - таблица, которая помогает визуализировать возможности взаимных продаж между различными продуктами или категориями товаров. Она показывает, какие продукты хорошо продаются вместе, а какие - нет, и стоит ли, например, клиенту, купившему товар А, рекомендовать товар Б, или поставить эти товары рядом на сайте или на полке в магазине.

Структура этой таблицы предельно проста - по строчкам и по столбцам откладываются один и тот же набор интересующих нас товаров (или категорий), а на пересечении - числовой показатель, характеризующий кросс-продажи, например, количество клиентов, купивших оба товара, отложенных по осям X и Y:

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

Давайте разберём как можно рассчитать подобную таблицу с помощью языка DAX в Power BI или Power Pivot в Microsoft Excel.

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

Галочки, они же флажки, они же чекбоксы и в особо запущенных случаях даже крыжики - всё это разные названия для одного простого, но очень полезного объекта, позволяющего удобно отмечать сделанные задачи, выделять элементы различных списков, играть роль бинарного переключателя "вкл-выкл" и т.д. Все вы с ними, конечно же, знакомы.

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

• Встроенные флажки в новых версиях Excel
  
• Элементы управления VBA
  
• Имитация флажков символами шрифта
  

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

Всех поступивших к нам в этом, например, месяце 250 потенциальных клиентов (лидов) мы берём в оборот и сначала с ними связываемся (контакт). Тем, кого мы сумели заинтересовать высылаем нашу презентацию, а затем коммерческое предложение (КП). Ну, и какая-то часть из них в итоге (ура!) соглашается на сделку. Естественно, от этапа к этапу мы теряем часть потенциальных покупателей - кто-то в процессе передумает, кому-то не понравится наши цены, кого-то не "дожмут" наши менеджеры и т.д. И вот этот отсев критически важно отслеживать в любом бизнесе, чтобы понимать свои слабые места и докручивать скрипты продаж, коммуникацию с клиентами и т.д.

В Microsoft Excel есть несколько способов построить подобную визуализацию - от встроенных и простых до навороченных динамических:

• Фигуры SmartArt (без масштаба)
  
• Встроенная диаграмма (начиная с Excel 2016)
  
• Имитация линейчатой диаграммой (в любой версии)
  
• Динамическая воронка на сводной таблице и Power Pivot
  

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