В 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 года.
При создании отчётов в Microsoft Power BI часто требуется сравнить текущий год (или месяц) с предыдущим, а какой-то выбранный товар - с его соседями из той же категории. С этим проблем нет - в Power BI есть куча встроенных функций на этот счёт.
Но что делать, если хочется сравнивать произвольно выбранные объекты? Т.е. любой выбранный год - с любым другим произвольно выбранным, а не обязательно предыдущим? Или выбранный товар нужно сравнить с каким-то другим, заданным? А может и то, и другое сразу, т.е. хочется выбирать интересные мне любые 2 года и сравнивать любые товары в них?
Мультистраничный веб-запрос в 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 своими руками универсальный сравниватель, который будет наглядно показывать в чем именно не совпадают две любые заданные текстовые строки:
Уж сколько было копий сломано на тему визуализации план-факта, но это вечная история и каждый решает её по-своему. Предлагаю рассмотреть ещё один вариант в вашу копилку:
Делается достаточно легко и почти не требует подготовительных действий с таблицей.
Срезы в сводных таблицах можно использовать не только классическим образом - для фильтрации исходных данных, но и для переключения между различными типами вычислений в области значений:
Реализовать такое очень легко - потребуется лишь пара формул на DAX, вспомогательная таблица и сводная по модели данных Power Pivot.
Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только выделенных (только видимых, удовлетворяющих заданным условиям и т.д.)
В новой версии Excel эта возможность встроена, а в старых придётся чуть-чуть пошаманить с макросами, чтобы её имитировать.
Разбор типичных "граблей" - о том, как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.
С завидной регулярностью (а в последнее время - всё чаще) мне пишут люди с просьбами помочь в различных вычислениях, связанных с лотереями. Кто-то хочет реализовать в Excel свой секретный алгоритм подбора выигрышных чисел, кто-то - найти закономерности в выпавших номерах прошедших тиражей, кто-то - подловить организаторов лотереи на нечестной игре.
В этой статье (и видеоуроке) мне хотелось бы ответить на часть этих вопросов. Благо, в Excel для решения таких задач достаточно инструментов, многие из которых, кстати, могут пригодиться и в более прозаических рабочих ситуациях.
Одно из самых просматриваемых видео у меня на YouTube-канале - это видео про Мгновенное заполнение (Flash Fill)в Microsoft Excel. Суть этого инструмента в том, что если вам надо как-то преобразовать ваши исходные данные, то достаточно просто начать набирать в соседнем с ними столбце тот результат, который вы хотите получить. После нескольких вручную набранных ячеек (обычно хватает 2-3) Excel "поймёт" логику нужных вам преобразований и автоматически продолжит набранное, завершив всю монотонную работу за вас.
Квинтэссенция эффективности. Волшебная кнопка "сделать всё хорошо", которую мы все так любим, правда?
На самом деле, аналог подобного инструмента есть и в Power Query - там он называется Столбец из примеров (Column from Examples). По сути, это маленький встроенный в Power Query искусственный интеллект, который умеет быстро обучаться на ваших данных и затем их преобразовывать. Давайте детально рассмотрим его возможности на нескольких практических сценариях, чтобы понять, где он может нам с вами пригодиться в реальных задачах.
Подробный и пошаговый разбор весьма типовой задачи, когда в качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:
А на выходе нам нужно собрать данные из всех таблиц в одну плоскую нормированную таблицу, удобную для последующего анализа и построения сводной, т.е. вот в такую:
Само собой, использовать для этого будем наиболее подходящий инструмент - Power Query:
Подробный разбор новой оберточной суперфункции LAMBDA, недавно добавленной в Excel. Её синтаксис, варианты применения, нюансы и особенности - от простых формул до динамических массивов и рекурсии:
Почти на каждом тренинге по Power Query, когда мы доходим до способов обновления созданных запросов и люди видят как новые данные заменяют при обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно сделать так, чтобы при обновлении старые данные тоже где-нибудь сохранялись и было видно всю историю обновлений?"
Мысль не новая и стандартным ответом на неё будет "нет" - Power Query по умолчанию настроен именно на замену старых данных новыми (что и требуется в абсолютном большинстве случаев). Однако, если очень хочется, то можно обойти это ограничение. Причем способ, как вы дальше увидите, весьма простой.
Функцию ФИЛЬТР.XML можно использовать не только для импорта XML-данных из интернета - основной задачи, для которой эта функция, собственно, и предназначена. Есть ещё одно - неожиданное и красивое - применение этой функции для быстрого деления слипшегося текста по ячейкам:
Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:
В старых версиях Excel 2007-2013 это можно было сделать с помощью .
Начиная с 2010 года это можно очень удобно делать с помощью .
К этим способам в последних версиях Microsoft Excel теперь можно добавить ещё один - импорт данных из интернета в формате XML с помощью встроенных функций ВЕБСЛУЖБА и ФИЛЬТР.XML.
Дополнительно к уже разобранным способам создания связанных выпадающих списков добавил ещё один - на основе неотсортированных справочников и функции СМЕЩ (OFFSET):
