При работе в Microsoft Excel иногда возникают ситуации, когда нам с вами нужно как-то особо и отдельно обрабатывать только видимые или, наоборот, только скрытые пользователем строки. Например, считать какие-то хитрые отчёты именно по видимым ячейкам, или загружать только видимые ячейки для дальнейшей обработки в Power Query и так далее. Во всех этих случаях для каждой строки нам нужно каким-то образом однозначно определять - осталась ли она видимой и отображается сейчас на экране или скрыта от глаз пользователя.
Разбираем простой способ это реализовать с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS):
И несколько сценариев применения этой методики в реальных задачах:
подсчёт видимых ячеек в формулах (обычных и динамических массивах)
загрузку только видимых ячеек в Power Query (с прикольным срезом для выбора)
Как реализовать правильное хранение пользовательских примечаний к результатам запроса Power Query - так, чтобы при обновлении запроса эти примечания оставались напротив тех ячеек, куда их вводили
Используем для этого интересную технику в Power Query - запрос, ссылающийся сам на себя (в отличие от формул, где это приводит к циклическим ссылкам, в Power Query - это вполне ОК).
Поиск совпадений в двух списках в Microsoft Excel - задача типовая и решается плюс-минус стандартными способами - я уже делал на эту тему пару видео и писал подробные статьи с разбором нескольких способов: формулами, через сводные таблицы и даже Power Query. Но что делать, если нужно найти общие элементы не в двух, а большем количестве списков? Трех, четырех, десяти?! На первый взгляд кажется, что задача кратно усложняется, но, на самом деле, всё не так страшно.
Разберём несколько подходов для решения этой задачи:
Обычные формулы (громоздко, но универсально)
Динамические массивы и функция FILTER (в новых версиях Excel)
Мультистраничный веб-запрос в Power Query - это подход, который позволяет загрузить данные не с одной, а сразу с нескольких веб-страниц, объединив их в единую таблицу. Например, загрузить результаты чемпионатов по гольфу из википедии за несколько лет (где каждый год - отдельная страница). Чтобы провернуть такую штуку, нам придётся пройти следующие этапы:
Создать сначала одиночный веб-запрос к любой странице из интересующего списка
Создать параметр для года и внедрить его в запрос
Преобразовать запрос в функцию (параметр станет её аргументом)
Создать список интересующих нас лет (2010 - 2021) и вызвать созданную функцию, подставив ей список в качестве аргумента
Объединить полученные результаты в единую таблицу за все годы
Необходимость учитывать регистр (регистрочувствительность) - одно из первых заметных принципиальных отличий, с которыми сталкиваются те, кто начинают работать в Power Query. В отличие от Excel, который прописные и строчные буквы в подавляющем большинстве случаев не различает, Power Query в этом вопросе строг. При любых операциях с данными (фильтрации, сортировке, удалении дубликатов, в исходном М-коде запросов и т.д.) Query воспринимает большие и маленькие буквы как совершенно разные.
Конечно, рано или поздно, к этому привыкаешь и начинаешь относиться как к данности и учитывать в работе. Так, например, многие пользователи перед фильтрацией, чтобы она была регистроНечувствительной, сначала делают дубликат столбца, в котором затем преобразуют весь текст к одному регистру и только потом фильтруют. Вполне себе способ.
На самом деле, решить эту проблему можно гораздо изящнее, если использовать встроенную в языке М в Power Query функцию с громоздким названием Comparer.OrdinalIgnoreCase.
В аналитических отчетах и дашбордах весьма часто встречается потребность вывести итоговые значения в формате сводной таблицы вида "Топ-N + другие":
Сводные таблицы Excel так, к сожалению, пока не умеют, но если доработать исходные данные с помощью запроса Power Query, то задача решается относительно легко.
Разбираем три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.
Разбор типичных "граблей" - о том, как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.
Одно из самых просматриваемых видео у меня на YouTube-канале - это видео про Мгновенное заполнение (Flash Fill)в Microsoft Excel. Суть этого инструмента в том, что если вам надо как-то преобразовать ваши исходные данные, то достаточно просто начать набирать в соседнем с ними столбце тот результат, который вы хотите получить. После нескольких вручную набранных ячеек (обычно хватает 2-3) Excel "поймёт" логику нужных вам преобразований и автоматически продолжит набранное, завершив всю монотонную работу за вас.
Квинтэссенция эффективности. Волшебная кнопка "сделать всё хорошо", которую мы все так любим, правда?
На самом деле, аналог подобного инструмента есть и в Power Query - там он называется Столбец из примеров (Column from Examples). По сути, это маленький встроенный в Power Query искусственный интеллект, который умеет быстро обучаться на ваших данных и затем их преобразовывать. Давайте детально рассмотрим его возможности на нескольких практических сценариях, чтобы понять, где он может нам с вами пригодиться в реальных задачах.
Подробный и пошаговый разбор весьма типовой задачи, когда в качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:
А на выходе нам нужно собрать данные из всех таблиц в одну плоскую нормированную таблицу, удобную для последующего анализа и построения сводной, т.е. вот в такую:
Само собой, использовать для этого будем наиболее подходящий инструмент - Power Query:
Почти на каждом тренинге по Power Query, когда мы доходим до способов обновления созданных запросов и люди видят как новые данные заменяют при обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно сделать так, чтобы при обновлении старые данные тоже где-нибудь сохранялись и было видно всю историю обновлений?"
Мысль не новая и стандартным ответом на неё будет "нет" - Power Query по умолчанию настроен именно на замену старых данных новыми (что и требуется в абсолютном большинстве случаев). Однако, если очень хочется, то можно обойти это ограничение. Причем способ, как вы дальше увидите, весьма простой.
Многие бизнес-процессы (и даже целые бизнесы) в этой жизни предполагают выполнение заказов ограниченным количеством исполнителей к заданным срокам. Планирование в таких случаях происходит, что называется, "от календаря" и часто возникает потребность переноса запланированных в нём событий (заказов, встреч, поставок) в Microsoft Excel - для дальнейшего анализа формулами, сводными таблицами, построения диаграмм и т.п.
Само-собой, хотелось бы реализовать такой перенос не тупым копированием (что как раз несложно), а с автоматическим обновлением данных, чтобы в будущем всё внесенные в календарь изменения и новые заказы "на лету" отображались бы в Excel. Реализовать такой импорт можно за считанные минуты с помощью надстройки Power Query, встроенной в Microsoft Excel.
