Николай Павлов (Планета Excel)

Всем успешно восставшим на работу после праздников спешу сообщить, что за эти длинные выходные допилил и выпустил очередное обновление своей надстройки PLEX для Excel - версия 2025.1

Кратко пройдусь по самому интересному:

✅ В инструменте работы с текстом появилась возможность массовой замены текста по справочнику, а также аналогичная по смыслу пользовательская функция TextReplace.

✅ Заодно сделал похожие функции ReplaceChars и RemoveChars, чтобы заменять в тексте ненужные символы на нужные и удалять ненужные сразу целым списком - быстро и удобно.

✅ Добавились инструменты для визуального поиска совпадений в двух выделенных диапазонах - теперь их можно выделять цветом (каждое совпадение - своим) и связывать линией.

✅ Кардинально улучшен запрос к нейросетям - теперь он поддерживает прокси (VseGPT.ru и ProxyAPI.ru) для работы с зарубежными нейросетями, которые ранее были недоступны в России без VPN и оплата которых была возможна только с зарубежных банковских карт. Теперь всё гораздо проще: регистрируетесь на сайте прокси, пополняете баланс любой российской картой, получаете API-ключ и выполняете любые ИИ-запросы прямо в Excel. Кроме ChatGPT и YandexGPT добавлены еще несколько моделей Antropic Claude.
Также теперь можно попросить нейросеть проанализировать целую таблицу или объяснить непонятную формулу.

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

✅ Добавил фильтрацию по содержимому Буфера обмена. Можно заранее скопировать в Буфер одну или сразу несколько ячеек со значениями, которые требуется отфильтровать, затем встать в любую ячейку нужного столбца и использовать эту функцию.

Ну и много всякой мелочевки, полировки и исправления ошибок как обычно.

Скачать последнюю версию надстройки PLEX всегда можно отсюда https://www.planetaexcel.ru/upload/PLEX.zip
Подробное описание новых функций есть в файле Справка по PLEX.pdf (будет в архиве вместе с надстройкой) или на странице https://www.planetaexcel.ru/plex/version20251.php

P.S. Обо всех найденных ошибках можно писать мне на почту [email protected] 🙏

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

Желаю вам и себе в следующем году:

🥰 Ценить время качественно проведённое с родными и близкими (это не навсегда)

✊ Надеяться на лучшее и не сдаваться (если сдадитесь - легче не станет)

👓 Называть чёрное - чёрным, а белое - белым, даже если кажется, что все вокруг думают иначе (таких точно не большинство)

🌇 Меньше смотреть на экраны и больше смотреть вокруг (тут 3D и 8K, прикинь!)

🗓 Планировать всё, что можно, особенно - отдых (не факт, что сбудется, но сам процесс успокаивает)

💃 Вспомнить то, от чего вас "прёт" и начать снова делать это (верните мне мои цветные карандаши и гитару)

💪 Заботиться о своем организме (он у вас один!) - выгуливать его, кормить вкусной и полезной едой, поить водой, мять на массаже и гонять в спортзале.

🧑‍🎓 Не переставать учиться и совершенствоваться в своем деле (привет всем остальным от нейросетей)

И главное: помнить, что не смотря ни на что, вы живёте лучшие годы своей жизни - здесь и сейчас.

P.S. Прикрепляю традиционный Excel-календарик на 2025 год - со списком задач, всеми возможными праздниками и расчасовкой по месяцам.

P.P.S. Вот вам песня для настроения https://youtu.be/-ilDIAmsJho?si=4fddUoNrZ_9hTnay

С Наступающим.

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

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

В очередном уроке разбираемся как построить и рассчитать такую таблицу на DAX в Microsoft Power BI или Microsoft Excel (с Power Pivot, само собой).

Читать статью, скачать файлы-примеры и смотреть видео (не YouTube!) тут https://www.planetaexcel.ru/techniques/24/49343/
Смотреть на YouTube https://youtu.be/laExJ1M0xbQ
Смотреть на Vimeo, если YouTube тормозит https://vimeo.com/1043084927

Поиск совпадений в двух списках в Microsoft Excel - задача типовая и решается плюс-минус стандартными способами: формулами, через сводные таблицы и даже Power Query.

Но что делать, если нужно найти общие элементы не в двух, а большем количестве списков? Трех, четырех, десяти, ста?! 😱 На первый взгляд кажется, что задача кратно усложняется, но, на самом деле, всё не так страшно.

В новом уроке разбираем три варианта её решения:
1. Обычные формулы (громоздко, зато универсально)
2. Динамические массивы (компактно и красиво)
3. Запрос с парой М-функций в Power Query (самый крутой способ, если списков много)

Читать статью и скачать пример тут https://www.planetaexcel.ru/techniques/14/47576/
Смотреть на YouTube https://www.youtube.com/watch?v=D7joB8I6dJA

Друзья, скоро буду в Астане с открытым тренингом "Создание дашбордов в Microsoft Power BI".
2 полных дня будем погружаться в тему разработки операционных и аналитических дашбордов с помощью Microsoft Power BI:
✅ собирать модели данных из разных источников
✅ писать меры на DAX
✅ наглядно визуализировать KPI для лиц принимающих решения
✅ разбирать основные приемы дизайна дашбордов

Тренинг пройдет 9-10 декабря с 10:00 до 18:00 в аудитории гостиницы Sheraton Astana. Принимающей стороной выступает мой любимый Career Center - единственная компания, которой я доверяю организацию мои тренингов в Казахстане уже много лет.

Большую группу собирать не будем принципиально (не более 20 чел.), т.к. цель - качественно научить. Онлайн-участие не предусмотрено по той же причине - только вживую, только хардкор :)

Кому интересно - звоните им по тел. +7 701 533 2899 и столбите места пока есть ;)

Обновил старую статью про создание и использование в Excel флажков (они же "чекбоксы", "галочки", "птички" и "крыжики"), которыми так удобно отмечать элементы списков или включать-выключать какие-либо опции.

Теперь у нас с вами есть аж три варианта:
1. Встроенные флажки в новых версиях (начиная с июня 2024 г.) - простые и удобные
2. Старые добрые флажки из элементов управления с вкладки Разработчик (Developer) - не так удобно, но работает в любой версии
3. Имитация флажков спецсимволами + небольшой макрос для оживления - тут можно вместо классических галочек ✅ использовать вообще любые символы, типа ❌🔘🔲🥕 и т.д. "Я художник, я так вижу" по полной программе.

Ну и бонусом разобрал нюансы применения флажков к реальным задачам: взаимное включение-выключение, условное форматирование, формат "в тыс руб" и т.д.

Читать статью и скачать файл-пример тут https://www.planetaexcel.ru/techniques/9/61/
Смотреть на YouTube
Смотреть на Vimeo

P.S. Кстати, вопрос на будущее - заливать вам видео прямо сюда в ТГ или лучше давать ссылку на внешний источник (YouTube, Vimeo)? Как сейчас модно делать? 😁

Для всех, кто не смог по каким-то причинам оживить у себя заторможенный YouTube, перелили с него все мои видеоуроки по Excel и Power BI на https://vimeo.com/channels/planetaexcel/videos

Так что можно заходить, смотреть без тормозов и подписываться на уведомления о новых роликах. Их буду теперь дублировать на YouTube, Vimeo и прямо тут, наверное.

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

Скользящее среднее (Simple Moving Average = SMA) - весьма распространенный метод анализа данных. Его используют для сглаживания шума, краткосрочных прогнозов, чтобы "поймать" переломы тренда и во множестве других задач.

Скользящее среднее относительно легко посчитать в Excel, но не так просто на DAX в Power BI - особенно если продажи у вас по дням, а скользящее среднее хочется построить за несколько месяцев/недель. В очередном видео и статье разбираем аж 3 способа это сделать в Power BI + один способ в Excel.

Бонусом покажу новые визуальные вычисления в Power BI - сбывшуюся мечту всех, кто не хочет учить DAX, а "отчет был нужен ещё вчера" 😁

Ссылка на видео: https://youtu.be/cAuUPaDX9Gc?si=_0f3YJrm6-DtANOl
Ссылка на статью и файл-пример: https://www.planetaexcel.ru/techniques/24/42456/

Одна из типовых визуализаций при настройке продаж почти в любом бизнесе - это построение воронки продаж (sales funnel). В Microsoft Excel есть несколько способов решить эту задачу:
1. Использовать инфографику SmartArt (будет красиво, но не очень точно)
2. Использовать встроенную диаграмму (только если у вас Excel 2016 или новее)
3. Использовать классическую линейчатую диаграмму с доработкой (работает в любой версии Excel, максимальная гибкость настроек и дизайна)
4. Построить динамическую воронку по Модели данных Power Pivot (самый навороченный вариант с обновлением и срезами-фильтрами)

Подробно разбираю все 4 способа в новом видеоуроке - прокачивайтесь:
Ссылка на видео https://youtu.be/TPW8S_9E8qQ
Ссылка на статью и файл-пример https://www.planetaexcel.ru/techniques/4/40197/

А вы строили раньше воронки продаж? Если да, то как? 😉

Привет, друзья!

Зафиналил и выложил очередную версию моей надстройки PLEX v.2024.1 для Microsoft Excel.
Главная убер-фича этого обновления - инструмент для выполнения запросов к нейросетям ChatGPT (версия 3.5 Turbo) и YandexGPT (v3), позволяющий использовать выделенные ячейки в качестве исходных данных, добавляя к ним инструкцию для GPT из встроенного набора шаблонов. В общем, Матрица в обнимку с Терминатором всё ближе 😁

Для наглядности записал видео, где подробно разобрал все нюансы применения этого нового инструмента на нескольких реальных задачах: https://www.youtube.com/watch?v=28virjECjng

Само собой, для подключения к обеим нейросетям придётся один раз пройти небольшой квест для получения необходимых настроек - подробную пошаговую инструкцию с картинками можно найти тут https://www.planetaexcel.ru/plex/features/16/38563/

Подробное описание всех изменений и исправлений этой версии https://www.planetaexcel.ru/plex/version20241.php
⬇️ Скачать последнюю версию PLEX можно, как всегда, тут https://www.planetaexcel.ru/upload/PLEX.zip

Буду рад услышать конструктивную критику, пожелания или идеи для усовершенствования - пишите в комментах 😁

В моей практике весьма часто приходится сталкиваться с ситуацией, когда мне в руки попадает большой многоуровневый список с вложенной нумерацией типа 1, 1.1, 1.2 и т.д. Это могут быть строительные сметы, списки задач-подзадач по проектам, бухгалтерские выгрузки статей-подстатей из 1С и т.п. И не знаю, как вам, а мне сразу хочется накостылять автору списка такой список разложить на столбцы по уровням нумерации, чтобы все элементы 1-го уровня ушли в отдельный столбец, все элементы 2-го - в ещё один и т.д. Ну, чтобы нормально потом со всем этим можно было работать по уровням, а не заниматься "вышиванием крестиком по монитору".

Выложил очередную статью и видео про то, как это можно сделать в Microsoft Excel на выбор 3 способами: используя формулы, макросы и Power Query.
Если есть шанс с таким столкнуться - просвещайтесь 😉

Смотреть видео тут: https://youtu.be/Zx5xw2dUiWs?si=GhZt9SZrY13s8Gpo
Читать статью и скачать файл-пример тут: https://www.planetaexcel.ru/techniques/2/36713/

Уже очень давно пользователи просят Microsoft сделать в строке формул Excel правильный шрифт. Чтобы он:
🔸был моноширинным (все символы одинаковой ширины, что крайне удобно для сложных формул)
🔸был плотным, чтобы длинные формулы выглядели компактно
🔸имел хорошо отличимые друг от друга ноль и "О", прописную i и строчную L, кавычки и апострофы и т.п., чтобы легко отлавливать опечатки
🔸поддерживал русский язык и был бесплатным или встроенным в Windows

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

Подробно и пошагово разбираю оба способа в новом видеоуроке - просвещайтесь 😁

Ссылка на видео https://youtu.be/QOR697mnqrg
Ссылка на статью https://www.planetaexcel.ru/techniques/11/35873/

P.S. Тут коллеги подсказывают, что в августе прошлого года Microsoft пообещали-таки сделать правильный шрифт в строке формул - см. https://techcommunity.microsoft.com/t5/excel-blog/excel-s-formula-bar-gets-a-new-look/ba-p/3902462 Но пока всё на этапе тестирования и, возможно, достанется опять не всем, а только обладателям подписки Office 365. Посмотрим.

Осенью 2018 года Microsoft добавила в Excel поддержку динамических массивов, а потом начала потихоньку добавлять новые функции, заточенные для работы с такими массивами. На текущий момент таковых насчитывается уже под 30 штук: FILTER, SORT, UNIQUE, TAKE, GROUPBY, PIVOTBY, WRAPROWS и т.д.
Звучит круто, но есть два "но":
👆 Всё это работает только у счастливых обладателей последней версии Excel 2021, либо у имеющих подписку Office 365 (а таковых сейчас стало заметно меньше)
👆 В этом наборе (на мой взгляд) не хватает нескольких функций, которые, очень бы пригодились в повседневной работе, но Microsoft их не добавила 😢

"Хочешь сделать хорошо - сделай это сам", подумал я.
Поэтому я сел и написал все эти функции (и аналоги встроенных функций и недостающие) 😁 Причем на VBA, так что всё это будет работать у вас в любой версии Excel (только в старых версиях придётся вводить их как формулы массива с помощью Ctrl+Shift+Enter, а в новых можно жать просто Enter).

А чтобы было понятнее - записал для вас видео с подробным разбором и применением этих функций для реальных задач.
https://youtu.be/IbkYRiwFauI

Скачать файл-пример, использованный в видео, можно тут https://www.planetaexcel.ru/upload/PLEX-dynamic-array-functions-demo.xlsx
Скачать последнюю версию надстройки PLEX https://www.planetaexcel.ru/plex/

P.S. Буду благодарен за обратную связь и любые пожелания-предложения по функционалу PLEX, кстати. Если есть, что предложить-подсказать-поругать - пишите смело. Всех выслушаю, всё учту 😉

Если вы работаете в Excel с событиями, привязанными к определённым датам 🗓 (заказы, встречи, задачи и т.д.), то вам, вероятно, рано или поздно захочется наглядно отображать их с подробностями на календарной сетке.

Это можно легко сделать с помощью сводной таблицы, но не обычной, т.к. она ничего кроме чисел в области значений выводить не может, а сводной по Модели Данных из Power Pivot. В этом случае мы можем использовать в области значений меры - формулы на языке DAX, встроенном в Power Pivot. А с помощью мер можно вывести что угодно, даже текст 😉

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

Ссылка на видео https://youtu.be/WepHnmpKywE
Ссылка на статью https://www.planetaexcel.ru/techniques/24/34269/

Ну, что, товарищи? Как настроение - новогоднее? Салаты уже настрогали?

В качестве полезно-приятного мелкого подарка ловите традиционный (уже) календарь на следующий 2024 год в Microsoft Excel:
✅ показывает рабочие и выходные дни по официальному календарю
✅ содержит расчасовку месяцев по производственному календарю (40, 36, 24-часовая раб.неделя)
✅ отображает все праздники и поводы (официальные и православные), памятные даты (типа "День стоматолога")
✅ позволяет сохранять для список дел на каждый день
✅ дизайн и цветовая палитра меняются через Разметка страницы - Цвета

Только не забудьте разблокировать файл после скачивания (щелчок по файлу правой кнопкой мыши - Свойства - Разблокировать) и разрешить макросы при открытии.

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

Обновил старую статью про вычисление количества уникальных значений в массиве. Добавил ещё четыре способа + тестирование на таблице 100тыс.строк, так что теперь их аж пять под любой сценарий:

1. формула массива (красиво, но тормозит на больших таблицах)
2. удаление дубликатов (просто, но надо делать руками)
3. простая сводная и сводная по Модели Данных (самый сок!)
4. новая функция УНИК (только если у вас последние версии Excel 2021/365)
5.макрофункция на VBA (пишем свою УНИК в любой версии Excel!)

Видеоурок: https://youtu.be/vcjtwfjsSzc?si=Qk6rWLc0BvxkZL6j
Статья и файл-пример со всеми формулами и макросом для скачивания: https://www.planetaexcel.ru/techniques/14/187/

В принципе, можно было сюда ещё и Power Query добавить, но на первое время и этого должно хватить, как считаете? 😁

При создании отчётов в Microsoft Power BI часто требуется сравнить текущий год (или месяц) с предыдущим, а какой-то выбранный товар - с его соседями из той же категории. С этим проблем нет - в Power BI есть куча встроенных функций на этот счёт (PREVIOUSMONTH, PREVIOUSYEAR и т.д.)

Но что делать, если хочется сравнивать произвольно выбранные объекты? Т.е. любой выбранный год - с любым другим произвольно выбранным, а не обязательно предыдущим? Или выбранный товар нужно сравнить с каким-то другим, заданным? А может и то, и другое сразу, т.е. хочется выбирать интересные мне любые 2 года и сравнивать любые товары в них?
Такой вопрос я слышу на тренингах весьма часто 😁

Решение такой задачи весьма несложно, хотя и слегка нетривиально, и потребует:
1️⃣ Создания дубликатов-справочников, по которым должна идти фильтрация
2️⃣ Связывания их с таблицей факта неактивными связями
3️⃣ Оживления этих фантомных связей с помощью функций CALCULATE в связке с USERELATIONSHIP

Как обычно, лучше один раз увидеть как кто-то такое делает, чем полдня самому решать это "методом Тыкинга" - ловите видеоурок и статью с подробным и пошаговым разбором всего процесса.

Видеоhttps://youtu.be/xvo2Y0V8lJA
Статья + файл-пример для скачиванияhttps://www.planetaexcel.ru/techniques/24/32563/

Приветствую, коллеги! Рад сообщить, что выпустил очередное обновление своей надстройки PLEX для Microsoft Excel - версию 2023.1.

Добавлены новые "плюшки":
✔️Фиксация содержимого диалоговых окон (для тех, кто часто делает в PLEX одни и те же действия)
✔️ Возможность выбора "умных" таблиц в полях ввода (чтобы не выделять диапазоны мышью, а выбирать имя умной таблицы из списка)
✔️ Новые функции для работы с датами: Quarter, DateInterval, FirstDate, LastDate
✔️17(!) принципиально новых функций для работы с динамическими массивами а-ля Python (для тех, у кого нет Office 365, а новых функций хочется 😁).
✔️ 8 новых действий в Диспетчере горячих клавиш
✔️Курсы валют Армении и Венгрии на любую дату
+ множество мелких улучшений и исправлений ошибок, само собой.

Как обычно, скачать последнюю версию надстройки всегда можно по ссылке https://www.planetaexcel.ru/upload/PLEX.zip