Обновил старую статью про создание и использование в 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

На днях компания 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

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

Ловите очередной видеоурок+статью о том, как можно кратно упростить и ускорить создание типовых однообразных мер в отчётах Microsoft Power BI с помощью двух замечательных инструментов: групп вычислений (calculation groups) и параметров полей (field parameters).

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

Это позволяет разгрузить модель от однообразных мер, а ваш дашборд - от лишней информации. А это - хорошо 😁

Смотреть видео, читать статью и скачать пример можно тут: https://www.planetaexcel.ru/techniques/24/59265/
Смотреть видео на YouTube тут: https://youtu.be/2ZBkUEucXk8

При работе в 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 Excel для решения классической задачи оптимизации длины маршрута - выбора кратчайшего пути из пункта А в пункт Б через несколько промежуточных точек?

Ловите очередную статью и видео о том, как можно использовать встроенную в Excel надстройку Поиск решения (Solver) в такой ситуации для поиска оптимального маршрута (частный случай такой задачи ещё называют "задачей коммивояжера").

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

✅ Читать статью и смотреть видео и скачать файл-пример у меня на сайте без VPN: https://www.planetaexcel.ru/techniques/11/61834/
✅ Смотреть видео на YouTube https://youtu.be/JYV9-2wfCyk

Страшно вспомнить 😁 сколько раз я уже слышал эти вопросы:

" - Почему у меня в сводной таблице, построенной из Power Pivot, итоги считаются неправильно?"
" - Как сделать так, чтобы Power BI показывал мне правильные итоги в таблице?"

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

Давайте разберёмся, как, на самом деле, вычисляются в этих программах итоговые значения, почему иногда они могут казаться людям "неправильными" и можно ли на это как-то повлиять и переопределить логику расчёта этих итоговых значений.

✅ Читать статью, качать пример и смотреть видео (без VPN) https://www.planetaexcel.ru/techniques/24/62323/
✅ Смотреть видео на YouTube https://youtu.be/Sss4EGMuBOk

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

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

А ещё эту задачу очень любят давать на собеседованиях программистам и аналитикам и смотреть как они её решают 😉

Разбираю 2 очень разных способа её решения в новом видео и статье:
1. Компактно и красиво - с помощью динамических массивов в новых версиях Excel
2. Страшненькой, но зато универсальной {формулой массива} работающей в любых версиях

✅ Смотреть видео (без VPN), читать статью и качать пример у меня на сайте
✅ Смотреть видео на YouTube

Никогда человек не бывает так эффективен, собран и производителен, как в последний день перед отпуском 😁

В тему традиционной предновогодней запарки (надеюсь, что это не про вас) ловите новый ролик и статью про то, как посчитать и построить в Excel диаграмму сгорания задач Burndown Chart. Это простая и наглядная визуализация прогресса по любому проекту ограниченному во времени + возможность спрогнозировать примерную дату окончания проекта, если мы сохраним текущий темп работ.

Обычно такой тип диаграммы используют программисты при работе по методикам agile и scrum, но легко сообразить, что он вполне успешно подойдет и для многих других областей:
✅ строительства,
✅ продаж товаров или услуг,
✅ учета складских запасов,
✅ освоения бюджета в финансовом планировании и т.д.

👉 Читать статью и смотреть видео (без VPN) у меня на сайте https://www.planetaexcel.ru/techniques/4/64507/
👉 Смотреть видео на YouTube https://youtu.be/Gx65VLLeHok