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

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

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

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

Осенью 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, кстати. Если есть, что предложить-подсказать-поругать - пишите смело. Всех выслушаю, всё учту 😉

Уже очень давно пользователи просят 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. Посмотрим.

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

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

Зафиналил и выложил очередную версию моей надстройки 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

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

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

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

Скользящее среднее (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/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Все вы наверняка много раз видели в маркетплейсах блок "вместе с этим товаром обычно покупают", где к пиву вам услужливо предложат чипсы, к вину - сыр, а к мылу - верёвку. Подобный подход называют ещё кросс-продажами (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

Всем успешно восставшим на работу после праздников спешу сообщить, что за эти длинные выходные допилил и выпустил очередное обновление своей надстройки 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. Обо всех найденных ошибках можно писать мне на почту info@planetaexcel.ru 🙏

А вот расскажите, было у вас когда-нибудь такое, что вы сделали красивый и сложный файлик в Excel, а потом отправили его коллегам. И файл прошёл по цепочке товарищей и вернулся к вам с кучей "улучшений". А вы смотрите на него и думаете; "Что тут изменилось-то?! И где именно?"

Если это про вас, то смотрите-читайте новую статью и видео про то, как быстро сравнить две версии файла и найти все правки, которые туда внесли с помощью бесплатной надстройки Inquire в Microsoft Excel.
Штука не новая (появилась ещё в 2013), но многие до сих пор не в курсе, а зря 😁

Смотреть видео на YouTube https://youtu.be/mW-3WcKHIvA

Смотреть на Vimeo https://vimeo.com/channels/planetaexcel/videos/sort:date/format:thumbnail

Читать статью и скачать примеры https://www.planetaexcel.ru/techniques/12/51798/

Представьте, что вы загрузили на лист Excel таблицу из внешнего мира с помощью Power Query, а потом захотели добавить к ней несколько примечаний от руки.

Как сделать так, чтобы после обновления запроса, когда порядок строк в таблице изменится, введённые примечания по-прежнему остались напротив исходных строк, а не съехали куда попало?

Для этой очень распространённой проблемы есть красивое и оригинальное решение - самоссылающийся запрос. Помните, как барон Мюнгхаузен вытащил себя за волосы из болота? Мы сделаем что-то похожее, только в Power Query 😉

Смотреть видео на YouTube https://youtu.be/q6Sx7hfnyzI
Смотреть видео на Vimeo, если YouTube тормозит https://vimeo.com/1062891843?share=copy
Читать статью и скачать пример https://www.planetaexcel.ru/techniques/24/52911/

Представьте, что вам часто приходится фильтровать одну и ту же таблицу по одному или (что гораздо хуже) нескольким столбцам, в попытках найти там частичное совпадение с заданным текстом. Можно, конечно, вручную включать фильтр и по очереди фильтровать каждый столбец, попутно сохраняя куда-то промежуточные результаты фильтрации, но даже при 2-3 столбцах это превращается в какой-то мазохизм 🤮

Есть способ гораздо изящнее - создать на листе поле ввода ActiveX и написать формулу, которая будет фильтровать нашу таблицу, если введённое в поле значение содержится в заданных столбцах. Причем работать вся эта конструкция будет мгновенно, на лету фильтруя при вводе каждый вводимый символ, без нажатий на Enter и щелчков мыши.

Смотреть видео на YouTube https://youtu.be/g82yWZ8pw5Y
Смотреть видео на Vimeo https://vimeo.com/1069647463
Читать статью и скачать файл-пример https://www.planetaexcel.ru/techniques/2/53714/

Ещё в далёком и прекрасном (хотя тогда так не казалось) 2020 году Microsoft выпустила обновление, добавляющее в Microsoft Excel 365 новую функцию ПРОСМОТРХ (XLOOKUP), пришедшую на замену классическим функциям поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP) и связке функций ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH).

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

Однако, я до сих пор регулярно встречаю пользователей, которые так и не освоили весь скрытый функционал и возможности этой функции и банально используют её только как "более удобный аналог ВПР".
На самом деле, она может гораздо больше 😜

Так что ловите статью и видео с продвинутыми техниками её использования:
▪️Поиск по нескольким сложным условиям
▪️Использование массива вместо одиночного искомого значения
▪️Получение в результатах целого диапазона
▪️Особенности работы ПРОСМОТРX внутри "умных" динамических таблиц
▪️Двумерный поиск по строке и столбцу одновременно
▪️Поиск, совмещенный с выборочным подсчетом (ПРОСМОТРX внутри СУММЕСЛИ...)
▪️Поиск сразу по нескольким листам

Читать статью, смотреть видео и скачать пример https://www.planetaexcel.ru/techniques/25/55384/
Смотреть на YouTube https://youtu.be/6bPOty5xNJ4

Недавно вдруг осознал, что среди сотен всех написанных статей и снятых роликов, у меня на сайте до сих пор нет ни одного, посвященного проверке условий в Microsoft Excel. А это ж БАЗА 😁

Так что ловите статью и видео с 7 способами проверять одиночные (что несложно) и множественные (что хитрее) условия в Excel, а именно:

▪️ ЕСЛИ (IF) и вложенные ЕСЛИ - классика
▪️ ЕСЛИМН (IFS) и ПЕРЕКЛЮЧ (SWITCH) - новые функций из последних версий
▪️ ВЫБОР (CHOOSE) - скелет из шкафа, но работает в любой версии
▪️ ВПР (VLOOKUP) с интервальным просмотром вместо кучи вложенных ЕСЛИ
▪️ И на закуску экзотика - прямое умножение на условия и хитрая формула массива с функциями И (AND) или ИЛИ (OR)

Читать статью, смотреть видео и скачать файл-пример можно тут https://www.planetaexcel.ru/techniques/25/56761/
Смотреть видео на YouTube https://youtu.be/jjmzhfztONs