Представьте, что вы загрузили на лист 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