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

Регулярно встречаю на тренингах даже весьма продвинутых пользователей (не говоря уже о начинающих), которые весьма поверхностно знакомы с именованными диапазонами в Microsoft Excel и почти не используют их в своей работе. А это весьма печально, т.к. именованные диапазоны - это очень красивый и удобный инструмент, который можно применять для решения множества задач. В очередном видео и статье подробно разбираемся с этой темой по пунктам: - Как создать именованный диапазон и использовать имена в формулах - Как реализовать быструю навигацию по большой книге с помощью имён и увидеть карту диапазонов - Как использовать именованные константы в роли глобальных переменных - Массовое создание имён и поиск пересечения диапазонов - Имена-невидимки и проблемы с ними - Что такое относительные "плавающие" имена и как они могут помочь в итогах - Как делать именованные диапазоны с формулами и динамические ("резиновые") именованные диапазоны - Как создавать собственные функции в именах с помощью LAMBDA ✔️ Смотреть видео, скачать пример и читать статью у меня на сайте (без VPN) ✔️ Смотреть видео на YouTube

Когда начинаешь работать с данными в Power Query, то очень быстро привыкаешь мыслить не ячейками и их адресами а-ля "морской бой" (как в Excel), а столбцами и целыми таблицами, т.к. именно ими и оперирует Power Query. И это нормально 😁 Тем не менее, с завидной регулярностью возникают ситуации, когда в Power Query нам нужно ссылаться именно на конкретные отдельные ячейки в наших данных. Причём иногда положение этих ячеек даже не статическое, а плавающее - а значит их сначала придётся найти в каждом конкретном случае. Реализовать такое, на самом деле, несложно - особенно, если вам не страшно копнуть чуть глубже интерфейса Power Query, в язык М, который работает у него "под капотом". В новом уроке подробно разбираемся с этой задачей на примере сборки файлов из папки с дополнительными столбцами, куда нужно извлечь содержимое заданных одиночных ячеек. ✔️ Читать статью, скачать пример и смотреть видео у меня на сайте (без VPN): https://www.planetaexcel.ru/techniques/24/69373/ ✔️ Смотреть видео на YouTube https://youtu.be/-xU6DMMbKxM

Сводные таблицы в Excel хороши всем, кроме одного маленького, но иногда очень неприятного ограничения: они всегда строят симметричную структуру. Если мы, например, закинули в строки поля Категория и Бренд, то Excel честно покажет бренды сразу для всех категорий. А если в столбцы добавили поля Год, Квартал и Месяц, то такая 3-х уровневая структура тоже будет применяться ко всем годам одинаково. Без вариантов. Но в реальной жизни часто хочется чуть большего. Например: - по одной категории показать детализацию до товаров, а по остальным оставить только итоги; - вывести в разных столбцах не одни и те же поля значений, а разные; - сделать сводную, где в строках или столбцах идут не все элементы подряд, а только вручную выбранные комбинации; - собрать «красивую» управленческую форму отчёта, а не типовую симметричную "простыню". Обычными настройками сводной таблицы такую конструкцию собрать, к сожалению, не получится. Зато можно использовать малоизвестный инструмент Excel под названием наборы - это вручную заданный список элементов или их комбинаций, которые Excel должен показать в строках или столбцах. В новом уроке разбираемся, как это реализовать: ✔️ смотреть видео, читать статью и скачать файл-пример у меня на сайте (без VPN) ✔️ смотреть видео на YouTube

Создание большого количества вложенных папок по определённой заданной структуре - весьма типовая и крайне скучная задача. Представьте на секунду, что для сбора данных от каждого отдела в вашей компании вам нужно на сетевом или локальном диске создать отдельную папку, где внутри должны быть подпапки с названиями дирекций (Коммерция, Производство, Финансы), а внутри них отделы (Закупки, Маркетинг...), а внутри некоторых отделов третьим уровнем ещё и рабочие группы и т.д. Легко сообразить, что подобная структура может быть весьма многоуровневой и создание такого "монстра" вручную займёт массу времени. В подобных ситуациях может здорово выручить простой макрос, который сгенерит всю эту вложенную структуру за вас по готовому списку из Microsoft Excel. В новом уроке подробно разбираемся, как такой макрос написать и как сформировать для него список путей к создаваемым папкам с наименьшими усилиями. ✔️ Читать статью, скачать файл-пример и смотреть видео (без VPN) у меня на сайте - https://www.planetaexcel.ru/techniques/3/67913/ ✔️ Смотреть видео на YouTube - https://youtu.be/lE3LPyFbwHk

Чемпионат по дашбордам в Power BI Если кто не в курсе, то ещё в прошлом 2025 году был запущен международный чемпионат по визуализации данных на платформе Power BI - Power BI Dataviz World Champs (не без помощи Microsoft, конечно). Участники конкурса создают отчёты по заданным темам и исходным данным, соревнуясь в красоте, наглядности, креативности и т.д. На других посмотреть и себя показать, как говорится. В этом году случился второй сезон - с января по февраль шли отборочные раунды и голосования пользователей, а потом финалистов выбирало почтенное жюри. Финал прошёл очно в Атланте в середине марта, где четверо финалистов в прямом эфире соревновались на сцене конференции FabCon. Как и в прошлом году, мне лично очень интересно было посмотреть на галерею присланных работ и дашборды финалистов - там же можно скачать исходники в pbix и вдумчиво их поковырять. Однозначно, есть, чем вдохновиться, потренировать насмотренность и украсть пару-тройку идей в свой багаж :) Так, что если практикуете создание отчётов в Power BI - очень рекомендую. Галерея лучших работ

Поиск и подстановка данных из одной таблицы в другую по совпадению ключевых значений в общих столбцах - это суперчастая задача при работе в Excel и решить её можно массой способов: функциями ВПР, ПРОСМОТРХ, ИНДЕКС+ПОИСКПОЗ, запросом в Power Query и т.д. Уверен, что для большинства из вас, мои дорогие читатели, это не проблема 😎 Но что делать, если нужно не просто подтянуть данные из одной таблицы в другую не только по совпадению ключевого значения, но и по дате, т.е. с учётом периода, когда это значение действует? Примеров масса: ✔️ в клиентской базе у людей иногда меняются контактные данные ✔️ служебный автомобиль может быть закреплён сначала за одним сотрудником, а потом - за другим, третьим... ✔️в разные периоды времени могут действовать различные рекламные акции и скидки и т.д. В базах данных такую ситуацию называют медленно меняющиеся измерения (slowly changing dimensions). Подробно разбираемся с этой задачей несколькими способами (формулы, Power Query) на примере подтягивания цен за определённый период. Читать статью, скачать пример и смотреть видео у меня на сайте https://www.planetaexcel.ru/techniques/2/67378/ Смотреть видео на YouTube https://youtu.be/G5pxttSO1MM

Как поженить Power Query с нейросетями? Так, чтобы внутри нашего запроса Power Query на любом шаге иметь возможность вызвать необходимую нейросеть, скормить ей данные и промпт и тут же получить обратно результаты для обработки на следующих шагах? Реализовать подобное достаточно легко, написав единожды пользовательскую функцию на языке М, который работает "под капотом" у Power Query. Эта функция будет выполнять API-запрос к выбранной нейросети, получать ответ в формате JSON и возвращать его в виде дополнительного столбца к вашей таблице в Power Query. Ловите очередной урок и видео с подробным разбором всего процесса на примере DeepSeek (но можно легко адаптировать код и к другим нейросетям). ✔️ Читать статью и смотреть видео у меня на сайте (VPN не нужен) https://www.planetaexcel.ru/techniques/24/66370/ ✔️Смотреть видео на YouTube https://youtu.be/7gxoPfCj0w0

Привычно открываю вчера Power BI Desktop, чтобы поработать и вижу в левом верхнем углу на месте привычных иконок представлений какие-то черные бяки 😮 Поднимаю брови. Вздыхаю. Мысленно говорю всякие разные слова. Ну, ОК. Перезапускаю Power BI - не помогает. Перезагружаю компьютер - то же самое. Обновляю Power BI Desktop до последней версии - ноль реакции. Обновляю драйверы видеокарты до последней версии, перезагружаюсь - никакого эффекта. Начинаю тихо закипать. Конечно, можно и забить на это (кнопки при этом работают), но нужно делать скриншоты для статей. Да и вести онлайн-тренинг с такой "красотой" как-то не комильфо. Наконец, делаю то, с чего надо было начать - лезу гуглить. Выясняется, что я не один такой - за последние пару дней эта проблема вылезла у огромного количества пользователей. Причина в том, что Microsoft недавно по-тихому обновила WebView2 - программный компонент из браузера Microsoft Edge, отвечающий за рендеринг веб-компонентов в пользовательских приложениях. При этом баги новой версии WebView2 вылезли в виде чёрных иконок аж в Power BI. Если у вас такая же проблема, то решений два: 1. Ждать, пока Microsoft выкатит исправленную версию WebView2. 2. Скачать предыдущую корректно работающую версию WebView2 Fixed Version 143.0.3650.139 отсюда, распаковать cab-архив в любую папку на диске и затем создать переменную окружения WEBVIEW2_BROWSER_EXECUTABLE_FOLDER со ссылкой на распакованный архив. Пошагово и с картинками весь процесс уже описан добрыми итальянскими людьми тут https://www.sqlbi.com/blog/marco/2026/01/24/fixing-the-black-icons-issue-in-power-bi-desktop-understanding-the-problem-and-a-temporary-workaround/ P.S. Всё это - хороший повод подумать о том, насколько уязвимы и зависимы нынче программы друг от друга и базовых библиотек. Какой-нибудь стажёр выкатил, не проверив, код в продакшн - половина интернета легло. В интересное время живём! 😁

Подозреваю, что вас и без меня завалило миллионом поздравлений от всех подряд, так что буду краток. Пожелаю вам и себе простых вещей: - безопасности и здоровья вам и вашим близким (у здорового человека много целей, у заболевшего - одна); - финансовой независимости (это не про зарабатывание стотыщ миллионов, а про внутреннее спокойствие и возможность делать то, что считаешь важным, а не просто выживать); - самореализации (вместо утомительных поисков смысла жизни). Традиционные итоги года для разнообразия решил подвести в формате мини-опроса: Фраза года: Хьюстон! Угадайте, что тут у нас?! Покупка года: тяжелое (9 кг!) одеяло от "Асконы" - неожиданно прекрасная вещь, заметно повысившая качество моего сна отслеживаю по показателям, т.к. всегда сплю в часах). Музыка года: "Winter Sun" Сергея Головина, "Ego Death" Polyphia Блюдо года: пшенично-ржаной тартин на закваске собственного приготовления. Не с первой попытки, но получилось испечь. Ролик года: интервью Дороничева. Ощущалось как глоток воздуха. Слово года: однозначно "ИИ" - восхищает и пугает одновременно. Первый год, когда я начал целенаправленно показывать ИИ-инструменты на курсах по VBA и Power BI. Проект года: закончил книгу по Power BI Спорт года: упростил спорзал до одной гири 20 кг - занимаюсь дома только с ней и делаю полный комплекс на всё тело за 30 мин. Из добавок - только витамины, омега и креатин (без фанатизма) Одежда года: Arcteryx (только трудно найти и в России стоит теперь как самолёт) Ивент года: концерт Сергея Головина в джаз-клубе Козлова Вайб года: перестал подсознательно ждать "белой полосы", надеяться на "перемены к лучшему", что когда-нибудь "всё наладится" и т.д. - учусь жить в состоянии окружающего хаоса и нормально при этом себя чувствовать. Сериал года: Одна из многих (Pluribus) Фильм года: Жизнь Чака Инструменты года: - Comet от Perplexity - вместо гугла и яндекса - NotebookLM - для глубоких исследований любой сложной темы - Obsidian - переехал туда со всеми заметками с OneNote Книги года: - по работе - перечитанные в N-ый раз вдоль и поперёк обе книги по DAX Марко Руссо и Альберто Феррари - из художки - открыл для себя Фредерика Бакмана Разочарование года: OZON. Навязанные скидки и повышение комиссии на продажу моих бумажных книг до конских 46%. С января будем возвращаться на прямые продажи с моего сайта с доставкой через СДЭК. Город года: - Красная Поляна - открыли её для себя в этом году всей семьей. Первый раз были - зимой (лыжи), второй - летом (3-дневный поход на Ачишхо). До Сочи, Адлеров и прочих Сириусов так и не доехали - не особо интересно. А вот горы - это вещь, да. Мы с командой провели в этом году 181 тренинг для примерно 2700 сотрудников из 51 компании (64 группы онлайн, остальные - вживую). Половина из них - расширенный Excel и Power Query, другая половина - Power Pivot, прогнозирование, Power BI, VBA и далее по списку. Выезжали с тренингами в Санкт-Петербург, Казань, Алматы, Екатеринбург. Ударный год. Надеюсь, следующий будет не хуже. С Наступающим! 🎄🎉

Никогда человек не бывает так эффективен, собран и производителен, как в последний день перед отпуском 😁 В тему традиционной предновогодней запарки (надеюсь, что это не про вас) ловите новый ролик и статью про то, как посчитать и построить в Excel диаграмму сгорания задач Burndown Chart. Это простая и наглядная визуализация прогресса по любому проекту ограниченному во времени + возможность спрогнозировать примерную дату окончания проекта, если мы сохраним текущий темп работ. Обычно такой тип диаграммы используют программисты при работе по методикам agile и scrum, но легко сообразить, что он вполне успешно подойдет и для многих других областей: ✅ строительства, ✅ продаж товаров или услуг, ✅ учета складских запасов, ✅ освоения бюджета в финансовом планировании и т.д. 👉 Читать статью и смотреть видео (без VPN) у меня на сайте https://www.planetaexcel.ru/techniques/4/64507/ 👉 Смотреть видео на YouTube https://youtu.be/Gx65VLLeHok

Поиск недостающих (пропущенных) значений в числовой последовательности - это хоть и банальная, но, на самом деле, весьма распространённая задача при работе в Microsoft Excel. Вы можете столкнуться с ней при поиске пропущенных документов с последовательной нумерацией; выставленных, но неоплаченных счетов; невыпавших номеров в тираже лотереи и т.д. А ещё эту задачу очень любят давать на собеседованиях программистам и аналитикам и смотреть как они её решают 😉 Разбираю 2 очень разных способа её решения в новом видео и статье: 1. Компактно и красиво - с помощью динамических массивов в новых версиях Excel 2. Страшненькой, но зато универсальной {формулой массива} работающей в любых версиях ✅ Смотреть видео (без VPN), читать статью и качать пример у меня на сайте ✅ Смотреть видео на YouTube

Страшно вспомнить 😁 сколько раз я уже слышал эти вопросы: " - Почему у меня в сводной таблице, построенной из Power Pivot, итоги считаются неправильно?" " - Как сделать так, чтобы Power BI показывал мне правильные итоги в таблице?" Проблема в том, что для неискушённых бизнес-пользователей логика итогов DAX может быть не очень понятной. Многим людям (особенно после Excel) более привычно воспринимать итоги именно как сумму всех вышестоящих значений, а не как отсутствие фильтров (чем они, по сути, и являются). Давайте разберёмся, как, на самом деле, вычисляются в этих программах итоговые значения, почему иногда они могут казаться людям "неправильными" и можно ли на это как-то повлиять и переопределить логику расчёта этих итоговых значений. ✅ Читать статью, качать пример и смотреть видео (без VPN) https://www.planetaexcel.ru/techniques/24/62323/ ✅ Смотреть видео на YouTube https://youtu.be/Sss4EGMuBOk

Как применить Microsoft Excel для решения классической задачи оптимизации длины маршрута - выбора кратчайшего пути из пункта А в пункт Б через несколько промежуточных точек? Ловите очередную статью и видео о том, как можно использовать встроенную в Excel надстройку Поиск решения (Solver) в такой ситуации для поиска оптимального маршрута (частный случай такой задачи ещё называют "задачей коммивояжера"). На самом деле, ничего сложного - главное (как обычно) правильно набросать исходную модель на основе таблицы расстояний, а дальше Excel сделает за нас всю грязную работу 😎 ✅ Читать статью и смотреть видео и скачать файл-пример у меня на сайте без VPN: https://www.planetaexcel.ru/techniques/11/61834/ ✅ Смотреть видео на YouTube https://youtu.be/JYV9-2wfCyk

При работе в Microsoft Excel иногда возникают ситуации, когда нам с вами нужно как-то особо и отдельно обрабатывать только видимые или, наоборот, только скрытые пользователем строки. Например, считать какие-то хитрые отчёты именно по видимым ячейкам, или загружать только видимые ячейки для дальнейшей обработки в Power Query и т.д. Можно, конечно, выделить и скопировать куда-нибудь отдельно только видимые ячейки, но это разовый ручной вариант, который придётся многократно повторять в будущем. Хотелось бы решить эту задачу более изящно, раз - и навсегда. Ловите простой способ реализовать подобное с помощью старой доброй функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS). Причем работать он будет с любыми вариантами скрытия строк: вручную, фильтром, группировкой и т.д. Особенно красиво смотрится связка "срез + запрос Power Query", когда с помощью среза можно оперативно выбирать часть исходных данных, по которым затем работает запрос Power Query. Читать статью, скачать пример и смотреть видео (без VPN): https://www.planetaexcel.ru/techniques/2/60662/ Смотреть видео на YouTube https://youtu.be/WIl2qs-JVlw?si=nrEBQP4Vt_DgMJ7j

Приветствую, друзья! Ловите очередной видеоурок+статью о том, как можно кратно упростить и ускорить создание типовых однообразных мер в отчётах Microsoft Power BI с помощью двух замечательных инструментов: групп вычислений (calculation groups) и параметров полей (field parameters). Если кратко, то суть в том, что можно легко сделать группу шаблонных вычислений (например, общий итог, нарастающий итог, год-к-году, доля внутри года и т.п.), куда потом автоматически может подставляться любая выбранная на срезе пользователем мера - хочешь считай это всё для стоимости, хочешь - для прибыли, хочешь - для количества заказов и т.п. Это позволяет разгрузить модель от однообразных мер, а ваш дашборд - от лишней информации. А это - хорошо 😁 Смотреть видео, читать статью и скачать пример можно тут: https://www.planetaexcel.ru/techniques/24/59265/ Смотреть видео на YouTube тут: https://youtu.be/2ZBkUEucXk8

На днях компания Microsoft анонсировала, что в следующих версиях Excel появится возможность (наконец-то!) автоматического обновления сводных таблиц. За это будет отвечать кнопка Автообновление (Auto Refresh) на вкладке Анализ сводной таблицы (PivotTable Analyze). Для любой отдельно взятой сводной таблицы можно будет либо нажать, либо отжать эту кнопку, переключая, таким образом, сводную в режим автоматического обновления, когда любые изменения в исходном диапазоне данных приведут к обновлению сводной "на лету", без необходимости отдельно нажимать кнопку Обновить (Refresh), как это требуется сейчас. В общем и целом - это, конечно, приятная новость, но реализовать подобное, на самом деле, можно весьма легко на любой версии и прямо сейчас. Причем даже для сводных на основе Power Query и модели данных Power Pivot, обновлять которые майкрософтовский Autorefresh не умеет 😉 Суть способа в создании специального макроса, который будет: 1. Перехватывать событие изменения листа с исходными данными 2. Определять, находится ли изменённая ячейка в таблице, по которой построена сводная (ведь менять могут и произвольные ячейки за её пределами). 3. Обновлять сводную В новом видео подробно разбираемся, как это сделать: Читать статью и смотреть видеоурок https://www.planetaexcel.ru/techniques/8/58518/ Смотреть видео на YouTube https://youtu.be/Ytz-UgTbBFk

Представьте, что вам досталась таблица, где одни и те же значения (например, города) многократно записаны по-разному (Воронеж, г.Воронеж, вАронеж, воронИж и т.д.) И таких городов - несколько десятков и более 🤦‍♂️ Многим, я думаю, и представлять такое особо не надо - это их "обычный вторник". На самом деле, в последних версиях Excel и Power BI в язык М, который работает "под капотом" у Power Query добавили новую функцию - Table.AddFuzzyClusterColumn, которая автоматически умеет собирать такие разношёрстные данные в кучки (кластеры) по максимальной похожести. Нюанс же в том, что в интерфейсе Power Query пока нет кнопок или меню, где можно было бы вызвать эту функцию - применить её можно только введя соответствующий М-код вручную. Ловите очередную статью с видео, где подробно разбираем как это можно реализовать. Читать статью и смотреть видео https://www.planetaexcel.ru/techniques/24/57215/ Смотреть видео на YouTube https://youtu.be/B90uiETo9OI

Недавно вдруг осознал, что среди сотен всех написанных статей и снятых роликов, у меня на сайте до сих пор нет ни одного, посвященного проверке условий в Microsoft Excel. А это ж БАЗА 😁 Так что ловите статью и видео с 7 способами проверять одиночные (что несложно) и множественные (что хитрее) условия в Excel, а именно: ▪️ ЕСЛИ (IF) и вложенные ЕСЛИ - классика ▪️ ЕСЛИМН (IFS) и ПЕРЕКЛЮЧ (SWITCH) - новые функций из последних версий ▪️ ВЫБОР (CHOOSE) - скелет из шкафа, но работает в любой версии ▪️ ВПР (VLOOKUP) с интервальным просмотром вместо кучи вложенных ЕСЛИ ▪️ И на закуску экзотика - прямое умножение на условия и хитрая формула массива с функциями И (AND) или ИЛИ (OR) Читать статью, смотреть видео и скачать файл-пример можно тут https://www.planetaexcel.ru/techniques/25/56761/ Смотреть видео на YouTube https://youtu.be/jjmzhfztONs

Ещё в далёком и прекрасном (хотя тогда так не казалось) 2020 году Microsoft выпустила обновление, добавляющее в Microsoft Excel 365 новую функцию ПРОСМОТРХ (XLOOKUP), пришедшую на замену классическим функциям поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP) и связке функций ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH). В новой функции прекрасно всё (кроме, пожалуй, названия в русской версии). В отличие от предшественников, новая функция имеет простой и понятный синтаксис, умеет искать как по вертикали, так и по горизонтали; может искать снизу вверх, а не сверху вниз и недавно даже начала поддерживать регулярные выражения при поиске. Однако, я до сих пор регулярно встречаю пользователей, которые так и не освоили весь скрытый функционал и возможности этого инструмента. Так что ловите статью и видео с продвинутыми техниками её использования: ▪️Поиск по нескольким сложным условиям ▪️Использование массива вместо одиночного искомого значения ▪️Получение в результатах целого диапазона ▪️Особенности работы ПРОСМОТРX внутри "умных" динамических таблиц ▪️Двумерный поиск по строке и столбцу одновременно ▪️Поиск, совмещенный с выборочным подсчетом (ПРОСМОТРX внутри СУММЕСЛИ...) ▪️Поиск сразу по нескольким листам Читать статью, смотреть видео и скачать пример https://www.planetaexcel.ru/techniques/25/55384/ Смотреть на YouTube https://youtu.be/6bPOty5xNJ4

Представьте, что вам часто приходится фильтровать одну и ту же таблицу по одному или (что гораздо хуже) нескольким столбцам, в попытках найти там частичное совпадение с заданным текстом. Можно, конечно, вручную включать фильтр и по очереди фильтровать каждый столбец, попутно сохраняя куда-то промежуточные результаты фильтрации, но даже при 2-3 столбцах это превращается в какой-то мазохизм 🤮 Есть способ гораздо изящнее - создать на листе поле ввода ActiveX и написать формулу, которая будет фильтровать нашу таблицу, если введённое в поле значение содержится в заданных столбцах. Причем работать вся эта конструкция будет мгновенно, на лету фильтруя при вводе каждый вводимый символ, без нажатий на Enter и щелчков мыши. Смотреть видео на YouTube https://youtu.be/g82yWZ8pw5Y Смотреть видео на Vimeo https://vimeo.com/1069647463 Читать статью и скачать файл-пример https://www.planetaexcel.ru/techniques/2/53714/