В 2020 году Microsoft выпустила обновление, добавляющее в Microsoft Excel 365 новую функцию ПРОСМОТРХ (XLOOKUP), пришедшую на замену классическим функциям поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP) и связке функций ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH). В отличие от предшественников, новая функция имеет простой и понятный синтаксис, умеет искать как по вертикали, так и по горизонтали, а также может искать снизу вверх, а не сверху вниз. Базовый синтаксис и возможности функции ПРОСМОТРX я уже подробно разбирал в с сопутствующим видеоуроком.
Теперь же давайте посмотрим на более продвинутые техники использования функции ПРОСМОТРX и скрытые её возможности:
Представьте, что вам часто приходится фильтровать одну и ту же таблицу по одному или (что гораздо хуже) нескольким столбцам, в попытках найти там частичное совпадение с заданным текстом.
Само собой, можно включить классический фильтр через Данные - Фильтр (Data - Filter), развернуть выпадающий список в соответствующем столбце и ввести искомый текст в поле поиска, но ведь потом придётся куда-то скопировать результаты, очистить фильтрацию и повторить поиск ещё раз в другом столбце. А если столбцов для поиска будет не 2, а больше? Тоска зелёная, правда?
Но есть другой путь. Суть его в том, чтобы добавить на лист поле ввода ActiveX, куда мы будем вводить искомый текст, а затем написать формулу, которая будет искать и выводить все строки в таблице, где в заданных столбцах есть частичное текстовое совпадение с введёнными данными:
Как реализовать правильное хранение пользовательских примечаний к результатам запроса Power Query - так, чтобы при обновлении запроса эти примечания оставались напротив тех ячеек, куда их вводили
Используем для этого интересную технику в Power Query - запрос, ссылающийся сам на себя (в отличие от формул, где это приводит к циклическим ссылкам, в Power Query - это вполне ОК).
Представьте, что вы подготовили какой-либо расчёт в файле Excel (например, бюджет или отчет по продажам) и отправили его своим коллегам на согласование и правку. Файл прошел по цепочке рецензентов и вернулся к вам уже в изменённом виде. Возникает простой и закономерный вопрос - что именно и где тут изменилось?
Предположим, с ходу видно, что внутри внесено несколько правок, но все их быстро идентифицировать и проконтролировать весьма непросто, тем более, что они могут быть неочевидными - например, изменение какой-то ячейки на этом листе может привести к пересчёту другой на соседнем и т.п. В общем, игра из серии "найди 10 отличий или премию не получишь"
Помочь в подобной ситуации может бесплатная надстройка от Microsoft с названием Inquire, появившаяся в Microsoft Excel, начиная с 2013 года.
Всем успешно восставшим на работу после праздников спешу сообщить, что за эти длинные выходные допилил и выпустил очередное обновление своей надстройки PLEX для Excel - версия 2025.1
Кратко пройдусь по самому интересному:
В инструменте работы с текстом появилась возможность массовой замены текста по справочнику, а также аналогичная по смыслу пользовательская функция TextReplace.
Заодно сделал похожие функции ReplaceChars и RemoveChars, чтобы заменять в тексте ненужные символы на нужные и удалять ненужные сразу целым списком - быстро и удобно.
Добавились инструменты для визуального поиска совпадений в двух выделенных диапазонах - теперь их можно выделять цветом (каждое совпадение - своим) и связывать линией.
Кардинально улучшен запрос к нейросетям - теперь он поддерживает прокси (VseGPT.ru и ProxyAPI.ru) для работы с зарубежными нейросетями, которые ранее были недоступны в России без VPN и оплата которых была возможна только с зарубежных банковских карт. Теперь всё гораздо проще: регистрируетесь на сайте прокси, пополняете баланс любой российской картой, получаете API-ключ и выполняете любые ИИ-запросы прямо в Excel. Кроме ChatGPT и YandexGPT добавлены еще несколько моделей Antropic Claude.
Появилась возможность вставлять в выделенные ячейки флажки-чекбоксы для быстрого создания анкет, чек-листов, списков дел и т.п.
Добавил фильтрацию по содержимому Буфера обмена. Можно заранее скопировать в Буфер одну или сразу несколько ячеек со значениями, которые требуется отфильтровать, затем встать в любую ячейку нужного столбца и использовать эту функцию.
Ну и много всякой мелочевки, полировки и исправления ошибок как обычно.
Скачать последнюю версию надстройки PLEX всегда можно отсюда Подробное описание новых функций есть в файле Справка по PLEX.pdf (будет в архиве вместе с надстройкой) или на странице
Обо всех найденных ошибках и пожеланиях нового функционала можно писать мне на почту
Все вы наверняка много раз видели в маркетплейсах блок "вместе с этим товаром обычно покупают", где к пиву вам услужливо предложат чипсы, к вину - сыр, а к мылу - верёвку. Подобный подход называют ещё кросс-продажами (cross-sell) и, при правильном использовании, он позволяет ощутимо увеличить средний чек и общую удовлетворенность клиента.
Основным инструментом анализа в таких случаях является матрица кросс-продаж (cross-selling matrix) - таблица, которая помогает визуализировать возможности взаимных продаж между различными продуктами или категориями товаров. Она показывает, какие продукты хорошо продаются вместе, а какие - нет, и стоит ли, например, клиенту, купившему товар А, рекомендовать товар Б, или поставить эти товары рядом на сайте или на полке в магазине.
Структура этой таблицы предельно проста - по строчкам и по столбцам откладываются один и тот же набор интересующих нас товаров (или категорий), а на пересечении - числовой показатель, характеризующий кросс-продажи, например, количество клиентов, купивших оба товара, отложенных по осям X и Y:
Само собой, эта квадратная матрица будет симметричной относительно диагонали, ячейки на которой обычно оставляют пустой.
Давайте разберём как можно рассчитать подобную таблицу с помощью языка DAX в Power BI или Power Pivot в Microsoft Excel.
Поиск совпадений в двух списках в Microsoft Excel - задача типовая и решается плюс-минус стандартными способами - я уже делал на эту тему пару видео и писал подробные статьи с разбором нескольких способов: формулами, через сводные таблицы и даже Power Query. Но что делать, если нужно найти общие элементы не в двух, а большем количестве списков? Трех, четырех, десяти?! На первый взгляд кажется, что задача кратно усложняется, но, на самом деле, всё не так страшно.
Разберём несколько подходов для решения этой задачи:
Обычные формулы (громоздко, но универсально)
Динамические массивы и функция FILTER (в новых версиях Excel)
Галочки, они же флажки, они же чекбоксы и в особо запущенных случаях даже крыжики - всё это разные названия для одного простого, но очень полезного объекта, позволяющего удобно отмечать сделанные задачи, выделять элементы различных списков, играть роль бинарного переключателя "вкл-выкл" и т.д. Все вы с ними, конечно же, знакомы.
В этой статье мы рассмотрим несколько способов создания таких флажков-галочек в Microsoft Excel - для новых и старых версий Excel, соответственно. А в конце разберем реальную задачу, где эти чекбоксы можно применить.
Скользящее среднее (Simple Moving Average = SMA) - очень популярный метод анализа данных, используемый во множестве разных областей, включая финансовую аналитику, технический анализ в трейдинге, прогнозирование погоды, обработку сигналов и т.д.
Суть метода очень проста: для каждого значения временного ряда мы берём данные за N предыдущих периодов и их усредняем. Получается эдакое "окно" размером N элементов, скользящее по исходным данным - отсюда и название этого подхода. Само-собой, чем больше размер окна, тем сильнее получается усреднение и сглаживание.
В реальных задачах этот метод используют, например, чтобы:
Уменьшить шум (болтанку), исключив краткосрочные колебания, чтобы лучше видеть глобальное поведение в наших данных.
Поймать момент перелома, когда восходящий тренд сменяется нисходящим.
Выполнить простейший краткосрочный прогноз
Давайте разберёмся, как построить такое скользящее среднее в Excel (что несложно) и в Power BI (что ощутимо похитрее).
Думаю, многие из вас уже не один и не два раза слышали или сталкивались с диаграммой Воронка Продаж - классической визуализацией работы с клиентами в любом бизнесе, связанном с продажами.
Всех поступивших к нам в этом, например, месяце 250 потенциальных клиентов (лидов) мы берём в оборот и сначала с ними связываемся (контакт). Тем, кого мы сумели заинтересовать высылаем нашу презентацию, а затем коммерческое предложение (КП). Ну, и какая-то часть из них в итоге (ура!) соглашается на сделку. Естественно, от этапа к этапу мы теряем часть потенциальных покупателей - кто-то в процессе передумает, кому-то не понравится наши цены, кого-то не "дожмут" наши менеджеры и т.д. И вот этот отсев критически важно отслеживать в любом бизнесе, чтобы понимать свои слабые места и докручивать скрипты продаж, коммуникацию с клиентами и т.д.
В Microsoft Excel есть несколько способов построить подобную визуализацию - от встроенных и простых до навороченных динамических:
Фигуры SmartArt (без масштаба)
Встроенная диаграмма (начиная с Excel 2016)
Имитация линейчатой диаграммой (в любой версии)
Динамическая воронка на сводной таблице и Power Pivot
Уже страшно вспомнить сколько лет пользователи просят Microsoft сделать в строке формул Excel правильный шрифт. В ячейках на листе данные могут отображаться в любом самом безумном дизайне, который только захочет пользователь (хоть Comic Sans'ом пишите), но строка формул - другое дело. Когда пишешь или редактируешь длинную, сложную формулу с кучей вложенных друг в друга функций, то удобный шрифт очень важен.
Разбираемся, каким должен быть правильный шрифт и как его прикрутить к строке формул (но не к ячейкам листа!) в Microsoft Excel.
При создании отчётов в Microsoft Power BI часто требуется сравнить текущий год (или месяц) с предыдущим, а какой-то выбранный товар - с его соседями из той же категории. С этим проблем нет - в Power BI есть куча встроенных функций на этот счёт.
Но что делать, если хочется сравнивать произвольно выбранные объекты? Т.е. любой выбранный год - с любым другим произвольно выбранным, а не обязательно предыдущим? Или выбранный товар нужно сравнить с каким-то другим, заданным? А может и то, и другое сразу, т.е. хочется выбирать интересные мне любые 2 года и сравнивать любые товары в них?
Рад сообщить, что выпустил обновление своей надстройки PLEX для Microsoft Excel - версию 2023.1. Из нового:
Фиксация содержимого диалоговых окон
В большинство диалоговых окон PLEX добавлена кнопка с символом замка, чтобы зафиксировать введённое в это окно содержимое и его параметры (содержимое полей ввода, положение переключателей, включенные флажки и т.д.) в течение текущего сеанса работы с Microsoft Excel. Данная функция может быть весьма полезна тем, кто многократно запускает какие-то инструменты PLEX с одинаковыми настройками и не хочет каждый раз задавать их вручную.
Для отмены фиксации нужно открыть соответствующее окно, отжать кнопку с замком и нажать на кнопку Отмена.
Выбор "умных" таблиц вместо диапазонов
В некоторых диалоговых окнах в полях ввода исходных данных добавлена стрелка слева, которая нужна для переключения между двумя режимами ввода – ручного указания диапазона (как обычно) или выбора одной из имеющихся в книге «умных» динамических таблиц из выпадающего списка:
Если вы используете "умные" таблицы, то это сильно упростит вам жизнь при работе в PLEX.
Новые функции для работы с датами
Добавлены новые пользовательские функции для работы с датами:
Quarter - возвращает квартал для указанной даты в заданном формате (только номер или "1 кв" или "Q-1" и т.д.)
DateInterval - возвращает разницу между двумя исходными датами в формате "Х лет Y мес Z дн"
FirstDate и LastDate - возвращают самую раннюю и позднюю даты из указанного диапазона (при этом игнорируют в нём всё кроме дат)
17 новых функций динамических массивов Если у вас Excel 2021 или новее, то ваша версия Excel поддерживает динамические массивы (dynamic arrays) - революционно-новый и супермощный инструмент для работы с данными. Если вы с ними ещё не знакомы - см. .
Изначально для выполнения операций с ДМ в Excel было всего 5 базовых функций (FILTER, SORT, SORTBY, UNIQUE, SEQUENCE), а в последних обновлениях добавили ещё несколько (но они доступны только подписчикам Office 365 😪).
Следующие 17 функций PLEX предназначены восполнить этот пробел и дать вам возможность управлять динамическими массивами гораздо более гибко:
VPile - составляет исходные диапазоны по вертикали столбиком друг на друга
HPile - объединяет исходные диапазоны, размещая их по горизонтали слева-направо
Append - соединяет исходные диапазоны, учитывая имена столбцов в шапке каждого диапазона, т.е. правильно собирая друг-под-друга столбцы с одинаковыми наименованиями в строке заголовка
Extract - извлекает из диапазона N строк и/или столбцов от начала или конца
Matches - выводит списком все совпадения в двух указанных диапазонах
Mismatches - выводит списком все отличия в двух заданных диапазонах (все элементы первого диапазона, которых нет во втором)
ConvertToRow - преобразует двумерную таблицу в одномерный массив-строку
ConvertToColumn - преобразует двумерную таблицу в одномерный массив-столбец
CloneRows - дублирует исходный диапазон N раз, размещая копии друг-под-другом сверху-вниз
CloneCols - дублирует исходный диапазон N раз, размещая копии рядом слева-направо
SelectRows - извлекает из таблицы строки с заданными номерами
SelectCols - извлекает из таблицы столбцы с заданными номерами
ReverseRows - размещает строки в таблицы в обратном порядке
ReverseCols - размещает столбцы в таблице в обратном порядке
RemoveBlankRows - удаляет из исходного диапазона все полностью пустые строки
Pivoted - сворачивает исходную плоскую таблицу в сводную
Unpivot - выполняет отмену свёртывания (нормализацию) исходной двумерной таблицы в плоскую
Подробную информацию по каждой функции, включая синтаксис и примеры использования - см. в приложенном к PLEX файле справки и на сайте в разделе .
Новые команды в Горячих клавишах
В добавили несколько простых, но полезных и весьма часто используемых команд, любую из которых можно повесить на любое удобное вам сочетание клавиш:
Специальная вставка из буфера только значений
Специальная вставка из буфера только значений с сохранением ширины столбцов
Подгрузить в текущую книгу пользовательские стили PLEX
Выгрузить из текущей книги пользовательские стили PLEX
Полностью очистить диапазон (и содержимое и форматы)
Удалить все строки / столбцы на листе кроме выделенных
Добавить к выделенным ячейкам функцию перехвата ошибок ЕСЛИОШИБКА (IFERROR)
Перевести число в текущей ячейке в сумму прописью
Курсы валют Армении и Венгрии
В дополнение к уже имеющимся 9 странам, в инструмент добавлена возможность получить курс любой валюты на любую дату с сайтов ЦБ Армении и НБ Венгрии:
Также были добавлены пользовательские функции для решения аналогичной задачи напрямую в ячейках листа:
=CBA(Дата;Валюта) - вставка курсов ЦБ Армении
=MNB(Дата;Валюта) - вставка курсов Нац.Банка Венгрии
У всех функций стандартный синтаксис, совпадающий с уже имеющимися функциями вставки курсов валют, состоящий из 2 аргументов:
Ячейка с датой, для которой нужно получить курс (если не указана, то берётся текущая дата).
Трёхбуквенный код валюты в кавычках ("USD", "EUR" и т.д.) или ссылка на ячейку, где он лежит (уже без кавычек). Если этот аргумент опущен, то по умолчанию берётся доллар США.
Прочие улучшения
В инструмент Сравнить добавлена возможность задавать номер ключевого столбца индивидуально для каждого диапазона и возможность вывода результатов на новый лист.
В окне инструмента Обновить все запросы в папке в разделе Power Query добавлена галочка Включая вложенные папки, позволяющая обновлять запросы не только в текущей, но и во всех вложенных в неё папках (любого уровня вложенности).
В Мини-калькулятор добавлена возможность умножать на ноль.
В Календарь добавлены даты до 2030 года.
В окне Текст добавлена возможность выделять цветом ячейки, содержимое которых изменилось после применённых в этом окне преобразований (например, очистки текста от лишних пробелов и т.п.)
Исправления ошибок
Исправлена ошибка, возникающая при попытке назначить или очистить сочетание клавиш в инструменте Горячие клавиши.
Исправлена работа Прицела на пустых листах.
Исправлено некорректное определение последней рабочей ячейки листа в некоторых случаях в инструменте Очистка книги.
Исправлено некорректное склонение по падежам некоторых имен в функции FIO, а также улучшено распознавание имён и обновлена их коллекция.
Исправлены ошибки и неточности перевода некоторых команд в английской версии PLEX.
Исправлена ошибка, возникающая при попытке удалить символы в инструменте Текст.
Исправлена ошибка, возникающая в инструменте Сборка данных с листов при выборе опции Игнорировать скрытые листы.
Мультистраничный веб-запрос в Power Query - это подход, который позволяет загрузить данные не с одной, а сразу с нескольких веб-страниц, объединив их в единую таблицу. Например, загрузить результаты чемпионатов по гольфу из википедии за несколько лет (где каждый год - отдельная страница). Чтобы провернуть такую штуку, нам придётся пройти следующие этапы:
Создать сначала одиночный веб-запрос к любой странице из интересующего списка
Создать параметр для года и внедрить его в запрос
Преобразовать запрос в функцию (параметр станет её аргументом)
Создать список интересующих нас лет (2010 - 2021) и вызвать созданную функцию, подставив ей список в качестве аргумента
Объединить полученные результаты в единую таблицу за все годы
Разбираем нюансы и хитрости сортировки данных в таблицах и диаграммах Microsoft Power BI: простые и многоуровневые сортировки, неявную сортировку, сортировку месяцев и пользовательскую сортировку в нашей собственной логической последовательности.
Если отбросить новомодный ChatGPT, то на втором месте по по количеству хайпа в моем личном рейтинге будет Power BI с его нечеловеческой красоты интерактивными дашбордами. Причем за последние пару лет количество людей интересующихся этой темой (в том числе среди пользователей Excel, которых я часто вижу) - выросло кратно. С одной стороны, это хорошо, ибо по мощи, гибкости, "всеядности" и красоте визуализаций у Power BI сейчас конкурентов практически нет. С другой стороны, у многих людей я до сих пор встречаю заблуждение, что Power BI - это такой навороченный Power Point (может дело в похожем названии?).
В общем, ловите новый большой видеоурок по основам работы в Microsoft Power BI. Если вы с ним уже работаете, то никаких Америк я вам не открою, скорее всего. Но если раньше вы с ним не сталкивались или до сих пор смутно представляете, что это за штука, то - добро пожаловать. Экспертом по аналитике за полчаса я вас, конечно, не сделаю, но все главные принципы и этапы работы мы с вами разберём:
Что такое Power BI, из чего он состоит, и зачем он нужен
Как загрузить туда и исходные данные и привести их в приличный вид
Как и зачем связывать таблицы между собой
Как создавать вычисляемые столбцы и меры на встроенном в Power BI языке DAX
Как добавить визуализации на ваш дашборд и опубликовать его потом в облако
Уже несколько лет в Google Sheets существует , позволяющая вставлять в ячейки листа картинки по ссылке из интернета. Что, впрочем, вполне естественно, поскольку Google-таблицы изначально заточены под работу онлайн - им сам бог велел такую возможность использовать.
У Excel же ничего подобного долго не было. И вот, наконец, осенью 2022 года Microsoft начала среди добровольцев-тестировщиков из программы Office Insider обкатку своего аналога - новой функции ИЗОБРАЖЕНИЕ (IMAGE), также позволяющей вставлять по веб-ссылке картинки из интернета прямо в ячейки листа Microsoft Excel. В начале 2023 года эту функцию уже стали потихонечку разливать с обновлениями подписчикам Office 365 и недавно она, наконец, добралась и до меня. А значит я могу вам о ней рассказать на нескольких практических примерах, а именно:
Думаю, про ChatGPT вам уже, как минимум, слышали, а может даже и попробовали использовать. Вопрос в том, насколько полезен этот ИИ может быть для простого пользователя Excel.
Пробуем переложить на ChatGPT создание формул и макросов на Visual Basic и смотрим, что из этого получится:
Если вы когда-нибудь использовали в своих вычислениях в Excel логические функции (типа ЕСЛИ) или функции подстановки (типа ВПР), то, вполне возможно, уже сталкивались с ситуацией, когда две визуально неотличимых ячейки Microsoft Excel почему-то считает неодинаковыми, что автоматически приводит к неработоспособности функций, ломает фильтрацию, сводные таблицы и т.д. Причин для такого может быть много:
лишние пробелы
использование английских букв похожих на русские и наоборот (русская "эс" и английская "си", например)
использование цифр вместо букв (ноль вместо заглавной "О")
разные версии тире (короткое, длинное, среднее)
неразрывный пробел вместо обычного
невидимые символы переноса строки (Alt+Enter)
... и т.д.
Вопрос в том, где именно эти отличия находятся и как их быстро найти в исходных данных?
Самое простое - это сделать за пару минут прямо в Excel своими руками универсальный сравниватель, который будет наглядно показывать в чем именно не совпадают две любые заданные текстовые строки:
Уж сколько было копий сломано на тему визуализации план-факта, но это вечная история и каждый решает её по-своему. Предлагаю рассмотреть ещё один вариант в вашу копилку:
Делается достаточно легко и почти не требует подготовительных действий с таблицей.
Выпустил досрочно-внеплановое обновление моей надстройки PLEX для Microsoft Excel - третье в этом году. Обновления и улучшения коснулись, в основном, импорта курсов валют.
Новые страны в Курсах валют Теперь помимо уже имеющихся России, Украины, Беларуси и Казахстана, можно вставить курс любой валюты на любую дату для:
Грузии
Азербайджана
Таджикистана
Туркменистана
Молдовы
Для всех банков вставка производится с делением на соответствующую размерность, т.е. если, например, на сайте указан курс за 100 иен, то после вставки он будет поделен на 100.
Также все макросы импорта курсов валют переписаны на использование XML, RSS и JSON-потоков от банков, что ускоряет процесс загрузки и делает импорт независимым от будущих изменений дизайна сайтов банков.
Новые функции вставки курсов
Одновременно с добавлением новых стран (нац.банков) в диалоговом окне Курсы валют, были добавлены пользовательские функции для решения аналогичной задачи напрямую в ячейках листа:
=NBAZ(Дата;Валюта) - вставка курсов ЦБ Азербайджана
=NBG(Дата;Валюта) - вставка курсов Нац.Банка Грузии
У всех функций стандартный синтаксис, совпадающий с уже имеющимися функциями вставки курсов валют, состоящий из 2 аргументов:
Ячейка с датой, для которой нужно получить курс (если не указана, то берётся текущая дата).
Трёхбуквенный код валюты в кавычках ("USD", "EUR" и т.д.) или ссылка на ячейку, где он лежит (уже без кавычек). Если этот аргумент опущен, то по умолчанию берётся доллар США.
Исправления ошибок и улучшения
Как всегда, попутно исправлено несколько ошибок и выполнена незаметная снаружи, но важная внутренняя оптимизация, а именно:
Исправлена ошибка, возникающая при вставке курсов при нестандартных числовых разделителях и нероссийских региональных настройках.
Исправлена ошибка, возникающая при удалении N-го по счету слова в инструменте Текст.
Исправлена ошибка, возникающая в инструменте Свертка при добавлении в область значений числовых полей.
Исправлена ошибка с регистром начальных символов, возникающая при транслитерации в инструменте Текст.
Всем школьникам и студентам (и не только) посвящается Как решить систему уравнений в Microsoft Excel. Разбираем два принципиально разных подхода - с помощью обратной матрицы Крамера функциями МОБР и МУМНОЖ (для систем линейных уравнений) и подбором в надстройке Поиск решения (Solver).(для любых, в т.ч. и нелинейных).
Необходимость учитывать регистр (регистрочувствительность) - одно из первых заметных принципиальных отличий, с которыми сталкиваются те, кто начинают работать в Power Query. В отличие от Excel, который прописные и строчные буквы в подавляющем большинстве случаев не различает, Power Query в этом вопросе строг. При любых операциях с данными (фильтрации, сортировке, удалении дубликатов, в исходном М-коде запросов и т.д.) Query воспринимает большие и маленькие буквы как совершенно разные.
Конечно, рано или поздно, к этому привыкаешь и начинаешь относиться как к данности и учитывать в работе. Так, например, многие пользователи перед фильтрацией, чтобы она была регистроНечувствительной, сначала делают дубликат столбца, в котором затем преобразуют весь текст к одному регистру и только потом фильтруют. Вполне себе способ.
На самом деле, решить эту проблему можно гораздо изящнее, если использовать встроенную в языке М в Power Query функцию с громоздким названием Comparer.OrdinalIgnoreCase.
