Выложил очередное видео и статью о том, как реализовать в сводных таблицах динамический топ-N, когда N самых крупных элементов отображаются сверху, а все остальные автоматически собираются в группу "Другие". Причем это самое N желательно изменять на лету и делать по ситуации Топ-3, Топ-5, Топ-10 и т.п.

По умолчанию, сводные в Excel так не умеют - нужно либо грузить всё в Power Pivot (Power BI) и писать сложные меры на DAX с параметрами, либо (наш метод!) немножко допилить исходники с помощью Power Query 😉

До меня наконец-то добрались обновления Office 365 с 15-ю новыми функциями, которые разработчики Excel анонсировали еще в марте. Так что в ближайших материалах ждите детальный разбор всех этих прелестей на живых примерах и рабочих задачах.

Первым номером идёт пара функций для склейки таблиц - VSTACK и HSTACK, получивших в русском варианте странные названия ВСТОЛБИК и ГСТОЛБИК (Горизонтальный Столбик, Карл!) Особенно красиво эти функции сочетаются с трёхмерными ссылками, позволяя моментально собирать в единое целое таблицы с любого количества листов. Просто песня.

Ловите видео и статью со всеми подробностями и (если у вас Office 365) трясите своих айтишников, чтобы накатили вам все обновления 😊

Допилил и выложил второе в этом году обновление своей надстройки PLEX для Microsoft Excel - версия 2022.2. Добавлено несколько "вкусных" инструментов, которые, я надеюсь, многие оценят:
◾️ Прицел - выделение цветом текущей строки и столбца в пределах рабочей области листа. Очень удобно при работе с большими таблицами на больших мониторах.
◾️ Экспорт диапазона ячеек в виде картинки (PNG, GIF, JPG, BMP), PDF, текстового файла (CSV и TXT), универсальных форматов (XML, JSON), гипертекста (HTML).
◾️ Свёртка - сводная таблица с текстом в области значений (аналог Pivot Column из Power Query).
◾️ Новые функции OutlineLevel и IndentLevel для определения уровней группировки и количества отступов в ячейке - очень полезно при создании иерархий.
◾️ Новые инструменты работы с текстом: удаление N-го слова, удаление всех символов кириллицы или латиницы, удаление всех символов из запрещенного списка.

Смотреть детальный список улучшений и качать новую версию уже можно тут.

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

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

Ловите ссылки на видеоурок на YouTube + статью с примером

Всем школьникам и студентам (и не только) посвящается 🙂 Как решить систему уравнений в Microsoft Excel на примере задачи про биту и мяч. Разбираем два принципиально разных подхода: с помощью обратной матрицы Крамера функциями МОБР и МУМНОЖ для систем линейных уравнений и подбором в надстройке Поиск решения (Solver) - для любых, в т.ч. и нелинейных, случаев.

Ловите, как обычно, ссылочку на видеоурок на YouTube и статью с файлом-примером для скачивания и экспериментов.

Ловите обнову.
Выпустил досрочно-внеплановое обновление моей надстройки PLEX v2022.3 для Microsoft Excel - третье в этом году. В этой версии изменения коснулись, в основном, импорта курсов валют.

Теперь помимо уже имеющихся России, Украины, Беларуси и Казахстана, можно вставить курс любой валюты на любую дату дополнительно для 5 новых стран:

✅ Грузии
✅ Азербайджана
✅ Таджикистана
✅ Туркменистана
✅ Молдовы

Для всех банков вставка производится с делением на соответствующую размерность, т.е. если, например, на сайте указан курс за 100 иен, то после вставки он будет поделен на 100.

Также все макросы импорта курсов валют переписаны на использование XML, RSS и JSON-потоков от банков, что ускоряет процесс загрузки и делает импорт независимым от будущих изменений дизайна сайтов банков.

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

=NBM(Дата;Валюта) - вставка курсов Нац.Банка Молдовы
=NBTJ(Дата;Валюта) - вставка курсов Нац.Банка Таджикистана
=NBTN(Дата;Валюта) - вставка курсов Нац.Банка Туркменистана
=NBAZ(Дата;Валюта) - вставка курсов ЦБ Азербайджана
=NBG(Дата;Валюта) - вставка курсов Нац.Банка Грузии

У всех функций стандартный синтаксис, совпадающий с уже имеющимися функциями вставки курсов валют, состоящий из 2 аргументов: ячейки с датой, для которой нужно получить курс (если не указана, то берётся текущая дата) и трёхбуквенного кода валюты в кавычках ("USD", "EUR" и т.д. если не указано, то по умолчанию берётся доллар США).

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

Как обычно, скачать последнюю версию можно из раздела PLEX у меня на сайте. Только не забудьте после скачивания разблокировать файлы, а то заботливая Windows не даст запустить вам макросы.

Вот, буквально, каждый раз очищая папку спама в Outlook, вспоминаю незлым тихим словом 😁 человека, который додумался до такого "перевода" (в оригинале это были Clean и Empty - по функциональности очень разные команды, если что).

Уж сколько я встречал (хороших и не очень) визуализации на тему план-факта и даже сам делал парочку, но всегда можно придумать что-то ещё. Например, вот такое как выше на картинке. Скромненько и со вкусом, да и делается за пару минут.
Ссылка на видеоурок на YouTube: https://www.youtube.com/watch?v=BzqiMODc440
Ссылка на статью и файл-пример, который можно скачать и использовать готовую диаграмму, подставив в неё ваши данные: https://www.planetaexcel.ru/techniques/4/27098/

Привет, друзья! У нас тут наметилась подпольная распродажа 🛒 остатков тиражей последних изданий двух моих книг: "Microsoft Excel: Готовые решения - бери и пользуйся" и "Скульптор данных в Excel c Power Query". В честь Нового Года стоимость любой бумажной книги 799 р. вместо обычных 1490. Доставка по России любым из удобных вам способов (СберКурьер, DPD, СДЭК, почтой...)

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

Так что если кто давно хотел, но искал подходящий момент и повод, то - это он самый :)

P.S. Электронные книги (цветные PDF с примерами) всех трёх книг, само собой, никуда не денутся и будут доступны у меня на сайте 24/7 как и всегда.

P.P.S. Мы благополучно переехали в Ozon - https://www.ozon.ru/seller/planeta-eksel-805939/products/?miniapp=seller_805939

Всех с Наступившим! 🎄 Пожелание только одно - пусть он будет лучше предыдущего. Это будет несложно 🙂

Ловите традиционный микроподарок в виде интерактивного календаря на 2023 год в Excel с официальными выходными, переносами, праздниками, нормами рабочего времени для бухгалтеров и календариком-пинариком для пущей мотивации. На каждый день можно вводить почасовые задачи - такой себе планировщик "на коленке", но с возможностью доработки и допиливания под ваши задачи и бесконечной гибкостью - это же Excel 😁

Зафиналил, наконец, припоздавшее по причине болезни вездесущим "недогриппом" и последующей потери голоса, большое видео про модный нынче искусственный интеллект Open AI ChatGPT и его использование при работе в Excel.

Штука совершенно космическая, без преувеличения - ничего похожего я в своей жизни ещё не видел. Восхищает и пугает одновременно. Может легко написать за вас достаточно сложную формулу, макрос на VBA и даже М-функцию в Power Query. Начинающего пользователя Excel переплюнет уже сейчас, для середнячка и даже профессионала может стать очень крутым помощником в повседневных задачах. И всё это пока совершенно бесплатно.

Разбираем всё подробно и на примерах из жизни - просвещайтесь https://youtu.be/uor4N7w6xu0

Перенести созданную в Excel табличку в презентацию Power Point кажется детской задачей - чего там думать-то, копируй да вставляй! - но по факту часто выливается в проблему. Дизайн таблиц слетает, толщина рамок "плывёт", формулы пропадают и результат выглядит очень убого.
Знакомо? 🙄

В очередном коротком видео разбираем как сделать этот перенос правильно:

◾ как подготовить таблицу в Excel перед копированием
◾ как не поломать дизайн при переносе
◾ вставить как картинку в разных форматах (и чем они отличаются друг от друга)
◾ вставить с сохранением интерактивности (формул, возможности менять данные, условного форматирования)
◾ вставить со связью (обновление данных в исходном файле будет автоматически менять таблицу на слайде)

Ничего сложного тут нет - посмотрите ролик и закройте для себя эту тему с переносом данных из Excel в Power Point навсегда: https://youtu.be/ibIW9uI8j-8

P.S. И чтоб два раза не вставать: если тема работы с Power Point для вас актуальна, то напоминаю, что на следующей неделе 11 февраля (сб) я провожу в Москве очный полнодневный тренинг "Бизнес-презентация в Power Point". В программе фишки и лайфхаки при работе в Power Point, приемы быстрого создания презентаций, правильное использование мастер-слайдов и макетов, корпоративный дизайн, облегчение жизни докладчика и т.д. и т.п. Кому интересно - приходите, расскажу и покажу намного больше, чем в этом ролике 😁
Подробности тут https://www.planetaexcel.ru/groups/personal.php

Уже достаточно давно в Google Sheets существует функция IMAGE, позволяющая вставлять в ячейки гугл-таблиц картинки по ссылке из интернета. Недавно и Microsoft выкатила в очередном обновлении аналогичную одноименную функцию (в русском варианте ИЗОБРАЖЕНИЕ) для Excel. В новом видео https://youtu.be/dByfRfuIt2E разбираемся с её синтаксисом и применяем на нескольких интересных задачах:

▪️создание штрих-кодов и QR-кодов
▪️вставка логотипов компаний по их названию
▪️создание скриншотов веб-страниц
▪️вставка фрагмента карты по координатам
▪️создание нестандартных диаграмм и вставка их как картинок

А для тех, у кого пока нет последней версии Excel (где новая функция пока только и присутствует) в качестве утешительного приза пишем в конце свой собственный упрощенный аналог этой функции на макросах Visual Basic 😉

Ссылка на текстовый вариант этого материала и файл-пример https://www.planetaexcel.ru/techniques/25/28717/

Скорее всего вы уже где-то слышали про "закон Парето" или "правило 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

Если отбросить новомодный 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-поляне"?

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

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

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

Сравнить текущий год с предыдущим, а предыдущий - с его предшественником и т.д. - одна из типовых задач анализа данных. Финансисты даже придумали для этого специальное обозначение - 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 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/

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

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

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