Подвезли из типографии второе издание "Скульптора данных в Excel c Power Query". По сравнению с первым изменения не принципиальные, ибо с момента его выпуска не так много времени прошло. Однако, всё же добавил пару-тройку глав по новым функциям и инструментам, исправлены опечатки и т.д. по мелочи.

Бумажную (чб) или электронную версию (цветной PDF), как обычно, можно купить-скачать-заказать тут.

Если вы уже покупали ранее электронную, то обновленный вариант вместе с примерами можно скачать из личного кабинета бесплатно 😉

P.S. Собака к книге не прилагается 😁

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

Видео на YouTube https://www.youtube.com/watch?v=nnvCyIW_rnc + статья с файлом-примером на сайте

Как говорится: "Хочешь рассмешить Бога - расскажи ему о своих планах" 😁 Прогнозы - дело неблагодарное, конечно, но - куда без них? Даже если вы далеки от темы и статистика для вас - темный лес, то вполне можно, тем не менее, составить вполне приличный, математически обоснованный прогноз по модели экспоненциального сглаживания, используя инструмент Лист Прогноза, появившийся в Excel начиная с 2016-й версии. Ловите свежий видеоурок и статью о том, как это сделать.
https://youtu.be/4po9_d5T1aQ

Выложил очередное видео и статью о том, как реализовать в сводных таблицах динамический топ-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/