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

Друзья, скоро буду в Астане с открытым тренингом "Создание дашбордов в 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

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

1. Сначала создаём одиночный веб-запрос к любой странице из интересующего списка
2. Затем создаём параметр для перебора страниц и внедряем его в запрос
3. Преобразуем запрос в функцию (параметр станет её аргументом)
4. Создаём список значений для параметра и вызываем созданную функцию, подставив ей список в качестве аргумента
5. Объединяем полученные результаты в единую таблицу

Выглядит эта методика, предполагаю, не совсем очевидно, так что ловите очередной видеоурок и статью с подробным пошаговым разбором всего процесса на примере импорта статистики чемпионатов по гольфу за 12 лет из википедии. Само собой, всё описанное работает не только в Excel, но и в Power BI, если нужно.

Ссылка на видео https://www.youtube.com/watch?v=eKnG_kEJJcI
Ссылка на текстовую статью + файл-пример для скачивания https://www.planetaexcel.ru/techniques/13/31646/

Сравнить текущий год с предыдущим, а предыдущий - с его предшественником и т.д. - одна из типовых задач анализа данных. Финансисты даже придумали для этого специальное обозначение - YoY = Year-Over-Year (год-к-году). В Excel такое можно реализовать очень разными способами. В новом видео подробно разбираем три главных подхода и плюсы-минусы каждого из них:

✅ Самый "колхозный", но самый быстрый вариант - формулами снаружи сводной.
✅ Добавлением дубликата поля и его перенастройкой на вычисление отличия от предшественника - уже поизящнее.
✅ И, наконец, самый красивый способ - мерами на языке DAX при построении сводной по Модели Данных Power Pivot (чутка посложнее, но зато сильно мощнее).

Заодно зацепили нестандартные пользовательские форматы типа 🔺+10% , создание календарной таблицы для Power Pivot и много других побочных фишек. В общем, всё как вы любите.

Видео получилось длинноватое (для YouTube 25 мин - это долго), но мелочиться и делить на куски не стал - смело перематывайте по временным меткам в описании под видео до нужного вам способа, если вдруг станет скучно 😁

Ссылка на видео https://youtu.be/FSv2F6yWuSk
Статья с файлом-примером (если захотите скачать и попрактиковаться) тут https://www.planetaexcel.ru/techniques/8/30763/

Когда ты самоучка, то затык вмертвую может случиться на самых простых, вроде бы, вещах.
Вот, например, сортировка - казалось бы, что тут может быть сложного? Однако в Power BI, с его зоопарком связанных между собой таблиц в Модели Данных, сортировка очень легко может превратиться в проблему. Особенно, если не понимать базовых внутренних принципов её работы.

В новой статье и видео разбираемся:
✔️Как выполнять простую и многоуровневую сортировку таблиц в дашбордах Power BI
✔️Как управлять сортировкой диаграмм
✔️Как сортировать категории и элементы легенды в нашей собственной логической последовательности (а не от А до Я)
✔️Как правильно сортировать месяцы (чтоб не по алфавиту, а по календарю)
✔️Как сортировать таблицу или диаграмму по скрытому полю или мере

Видео, как обычно, на моем канале YouTube: https://youtu.be/d1sTmh24Hwk
Статья с файлами-примерами (если захочется попрактиковаться самостоятельно) тут: https://www.planetaexcel.ru/techniques/24/30217/

Если отбросить новомодный ChatGPT, то на втором месте по по количеству хайпа в моем личном рейтинге будет Power BI с его нечеловеческой красоты интерактивными дашбордами. Причем за последние пару лет количество людей интересующихся этой темой (в том числе среди пользователей Excel, которых я часто вижу) - выросло кратно. С одной стороны, это хорошо, ибо по мощи, гибкости, "всеядности" и красоте визуализаций у Power BI сейчас конкурентов практически нет. С другой стороны, у многих людей я до сих пор встречаю заблуждение, что Power BI - это такой навороченный Power Point (может дело в похожем названии?).

В общем, ловите новый большой видеоурок по основам работы в Microsoft Power BI. Если вы с ним уже работаете, то никаких Америк я вам не открою, скорее всего. Но если раньше вы с ним не сталкивались или до сих пор смутно представляете, что это за штука, то - добро пожаловать. Экспертом по аналитике за полчаса я вас, конечно, не сделаю, но все главные принципы и этапы работы мы с вами разберём:

✅ Что такое Power BI, из чего он состоит, и зачем он нужен
✅ Как загрузить туда и исходные данные и привести их в приличный вид
✅ Как и зачем связывать таблицы между собой
✅ Как создавать вычисляемые столбцы и меры на встроенном в Power BI языке DAX
✅ Как добавить визуализации на ваш дашборд и опубликовать его потом в облако

Видос тут https://youtu.be/jcBEdmD5MCU
Статья с файлами-примерами (если захотите повторить всё самостоятельно) - тут https://www.planetaexcel.ru/techniques/24/27607/

P.S. Если зайдёт, то готов (наравне с Excel) радовать вас в будущем роликами-статьями по фишкам работы и в Power BI. Как считаете, стоит? Или не распыляться и остаться на "Excel-поляне"?

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

В аналитике для выявления этой неравномерности строят обычно диаграмму Парето - комбинированный график со столбцами-результатами и накопительным процентом. В Microsoft Excel для решения этой задачи можно использовать несколько способов:
1. Встроенную диаграмму Парето (если у вас Excel 2016 или новее)
2. Группировку в сводной таблице и сводную диаграмму по ней
3. Надстройку Пакет Анализа и частотный анализ
4. Посчитать всё ручками с помощью формул и т.д.

Ловите свежее видео с подробным разбором всех перечисленных вариантов https://youtu.be/A8yGyUuxCd4
Ссылка на текстовый вариант этого материала и файл-пример https://www.planetaexcel.ru/techniques/4/182