Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP. Старая и новая функции для объединения таблиц (поиска текста и чисел)

=VLOOKUP(что ищем; таблица, в которой поиск идет в первом столбце; номер столбца, из которого забираем данные; [режим поиска])

=XLOOKUP (что ищем; в каком столбце ищем; из какого столбца забираем; [на что заменяем ошибку]; [ищем текст/число/текст с подстановочными символами] ; [ищем сверху или снизу])

— VLOOKUP ищет только в первом столбце таблицы, а XLOOKUP ссылается на отдельные столбцы (где ищем и откуда возвращаем данные) — ей все равно, какая структура данных. Соответственно, VLOOKUP слетит, если вставить столбец между первым и тем, откуда возвращаем данные (потому что номер возвращаемого столбца зашит как константа). А XLOOKUP, ссылающаяся на столбцы, будет работать при вставке новых столбцов. И может возвращать данные, которые левее, чем столбец для поиска.

— XLOOKUP по умолчанию ищет текст (точное совпадение), а VLOOKUP — ближайшее наименьшее число.

— В режиме поиска числа (пятый аргумент, равный единице или минус единице) XLOOKUP не требует сортировки данных и умеет искать и ближайшее наибольшее тоже;

— У XLOOKUP есть отдельный необязательный (четвертый) аргумент для замены ошибок (когда ничего не найдено) на другое значение. А в случае VLOOKUP для этого дела надо добавлять отдельную функцию IFNA.

— VLOOKUP умеет работать с символами подстановки (* и ?) по умолчанию, а XLOOKUP — нет. Чтобы использовать символы подстановки в XLOOKUP, нужно задать пятый аргумент match_mode равным 2 (по умолчанию 0 - точный поиск).

— VLOOKUP умеет только вертикально (столбцы), для горизонтального поиска используется HLOOKUP / ГПР. XLOOKUP может работать и со строками, и со столбцами.

— VLOOKUP всегда ищет сверху вниз (то есть при 2 и более совпадениях найдет первое), а XLOOKUP умеет и снизу вверх (то есть найдет последнее) - для этого задаем последний аргумент search_mode равным -1.

— В Excel XLOOKUP есть только в 2021 / 365. В старых версиях формулы с этой функцией будут возвращать ошибку #ИМЯ? / #NAME?

Посты по теме:
Видео про функцию ПРОСМОТРX / XLOOKUP
Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT
ПРОСМОТР (LOOKUP), да не X

Определяем первую и последнюю даты квартала по его номеру.

Ловите формулы для определения первой и последней даты квартала по номеру года и номеру квартала. Может пригодиться, если у вас отчеты с функциями СУММЕСЛИМН и другими, куда вы отправляете даты в качестве условий, а пользователю предлагаете выбирать только номер года/квартала в ячейках.
Здесь мы используем функцию ДАТА / DATE, которая позволяет "собирать" дату по трем ее параметрам. Номер месяца мы определяем исходя из номера квартала. Для даты конца квартала используем функцию КОНМЕСЯЦА / EOMONTH (мы не можем использовать фиксированное число для конца квартала, потому что в разные месяцы разное число дней — поэтому находим первый день последнего месяца квартала как

ДАТА(год; квартал * 3 ;1)

, а потом определяем последнюю дату в этом месяце (функция КОНМЕСЯЦА определяет последнюю дату месяца со сдвигом от заданной — мы никуда не сдвигаемся, поэтому последний ее аргумент равен нулю).

Начало квартала:

ДАТА(год; квартал * 3 - 2 ;1)

Конец квартала:

КОНМЕСЯЦА(ДАТА(год; квартал * 3 ;1);0)

Дано: в ячейке есть символы, нам надо получить текстовую строку с ними же, но отсортированными.

Решение: создаем виртуальный массив из всех символов — для этого создаем последовательность (функция ПОСЛЕД / SEQUENCE) чисел от единицы до числа символов в ячейке (ДЛСТР/ LEN), извлекаем символы с помощью ПСТР / MID — сформированная последовательность выступает номерами символов, которые мы этой функцией извлекаем. То есть мы извлекаем 1, 2, ..., N символов из ячейки, где N — число символов в ней, определенное функцией ДЛСТР.

Далее сортируем полученный массив (СОРТ / SORT) и сразу же собираем его обратно в текстовую строку (СЦЕП / CONCAT).

Получится формула:
=СЦЕП(СОРТ(ПСТР(ячейка;ПОСЛЕД(ДЛСТР(ячейка));1)))

Тепловая карта (шкала) без чисел

В условном форматировании со значками можно отключать отображение данных (показывали здесь), как и в случае с гистограммами (здесь).
А в случае с цветовой шкалой нет опции "Показывать только шкалу".
Но это можно исправить, просто применив пользовательский формат, в котором все данные будут скрыты.
В пользовательских форматах можно задавать отдельные форматы для четырех типов данных — положительных чисел, отрицательных, нуля и текста. Задаются они через точку с запятой.
И если ввести такой формат:

;;;

То не будет отображаться ничего. Потому что мы явным образом задаем форматы для всех четырех типов данных, но все они пустые. То есть мы не отображаем ничего. Данные в ячейках никуда не деваются, разумеется — речь только про форматирование.

Подробнее про пользовательские форматы — в этом видео (оно на основе Google Таблиц, но форматы работают одинаково и там, и в Excel за вычетом косметических отличий).

Какая у вас версия Excel?

Microsoft 365 — иконка с лупой (поиск) справа сверху
Excel 2016-2021 — текстовое поле "Что вы хотите сделать?" сверху
Excel 2013 — названия вкладок ленты инструментов ЗАГЛАВНЫМИ буквами
Excel 2010 — прямоугольная кнопка "Файл" (File) со скругленными углами слева сверху
Excel 2007 — круглая кнопка с логотипом Microsoft Office вместо кнопки "Файл"
Excel 2003 — панель инструментов вместо ленты

Или загляните сюда:
Файл — Учетная запись — О программе Excel
File — Account — About Excel

Нумерация с помощью формул

В этом видео рассматриваем несколько вариантов автоматической нумерации строк в диапазоне / таблице:
— с помощью функции СТРОКА / ROW, в том числе с учетом положения таблицы на листе (то есть при вставке новых строк над таблицей нумерация все равно не будет меняться, она останется в пределах таблицы
— с помощью функции ПОСЛЕД / SEQUENCE (живущей в Excel 2021 и Microsoft 365)
— формулой из функций ЕСЛИ / IF, ЕПУСТО / ISBLANK, СТРОКА / ROW и ДВССЫЛ / INDIRECT для нумерации с пропуском пустых строк.

Файл с формулами из видео прикрепляем отдельным сообщением!

Быстро формируем список уникальных значений с помощью расширенного фильтра

Нужны только уникальные значения (без дубликатов)?
Вызываем расширенный фильтр (Данные — Дополнительно / Data — Advanced), выделяем только столбец, из которого нужны уникальные значения, выбираем "Скопировать результат в другое место" (Copy to another location) и выбираем это место, включаем "Только уникальные записи" (Unique records only).

Если формулой с обновлением — то это функция УНИК / UNIQUE, но на только в Microsoft 365😿

P.S. Еще про расширенный фильтр по ссылке.

Решаем задачу подписчицы: при выгрузке данных из 1С в числах есть пробелы, которые превращают числа в текстовые строки. Пробелы надо удалить.

Разбираемся, как с помощью функции КОДСИМВ / CODE понять, что за символ вообще перед нами — в данном случае это не обычный пробел, а неразрывный, поэтому его не удалишь вводом обычного пробела с клавиатуры в окне "Найти и заменить". Его нужно либо копировать, либо вводить с помощью кода — Alt+0160.

И делаем макрос, который позволит удалять ненужные символы одним нажатием кнопки или сочетанием клавиш Alt+цифра.

Код макроса можно сократить до такого:

Selection.Replace What:=" ", Replacement:=""

На месте пробела может быть другой символ, который вам нужно удалять в выделенном диапазоне.
Не забывайте: действие макросов отменить через Ctrl+Z нельзя!

Всю задачу разбираем в видео со звуком.

Начало текущей недели — формулой

Если нам нужна первая дата (понедельник) текущей недели — и чтобы она обновлялась — можно использовать такую формулу:

=СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();2)+1

Что тут происходит?
1 Мы берем текущую дату (функция СЕГОДНЯ / TODAY)

2 Вычитаем из нее сегодняшний же день недели (функция ДЕНЬНЕД / WEEKDAY с аргументом 2 возвращает порядковый номер дня недели — например, 4 для четверга; аргумент 2 определяет привычную для нас систему нумерации, когда неделя начинается с понедельника — иначе, если этот аргумент пропустить, функция будет возвращать 1 для воскресенья, а не понедельника, как принято в некоторых странах, включая США).

3 Прибавляем единицу, чтобы получить не воскресенье предыдущей недели, а понедельник текущей.

А вот тут еще целая пачка формул для извлечения всякого-разного из даты.

Контрольное значение: отслеживаем значения ячеек в любом месте

Если вам нужно всегда видеть, чему равно значение в какой-нибудь ячейке, даже если вы работаете на другом листе — добавьте эту ячейку в окно контрольного значения.

Лента инструментов:
Формулы — Окно контрольного значения
Formulas — Watch Window

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

Автозамена (AutoCorrect). Если вам часто нужно вводить какое-то словосочетание, адрес, имейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах.
Параметры → Правописание → Параметры автозамены
Options → Proofing → AutoCorrect Options

Обратите внимание, что список автозамены один на все приложения Microsoft Office! Так что введенные вами варианты будут заменяться и в Outlook, Word и т.д.

Анпивот (Unpivot) — отмена свертывания, преобразование двумерной таблицы в плоскую, пригодную для построения сводной таблицы.

Эту операцию можно проделать в Power Query:
— Данные — "Из таблицы/диапазона" (выделяем предварительно исходную двумерную таблицу)
— Выделяем в редакторе Power Query те столбцы, которые должны остаться в новой таблице — у нас это "Компания".
— Преобразование — Отменить свертывание других столбцов (Transform — Unpivot Other Columns)

Альтернатива — формулой, если у вас Microsoft 365 с волшебными новыми функциями. Про формулу напишем отдельно!

А вот формула-монстр для отмены свертывания (анпивот) "в режиме реального времени". Но только с новыми функциями 365.

В общем виде формула такая:

=ГСТОЛБИК(ПОСТОЛБЦ(ЕСНД(РАЗВЕРНУТЬ(первый столбец;;СЧЁТЗ(заголовки столбцов, для которых отменяется свертывание));первый столбец));
ПОСТОЛБЦ(ЕСНД(РАЗВЕРНУТЬ(заголовки столбцов;СЧЁТЗ(первый столбец - для подсчета числа значений в нем));заголовки столбцов));
ПОСТОЛБЦ(данные столбцов))

Тут на верхнем уровне три вертикальных массива, которые склеиваются в одну таблицу функцией ГСТОЛБИК / HSTACK.

Последний аргумент с функцией ПОСТОЛБЦ / TOCOL — это просто данные (суммы в нашем случае), которые превращаются из двумерного массива в один столбик за счет этой самой TOCOL.

Первые два аргумента — это столбцы с компаниями и с заголовками. Здесь работает функция EXPAND / РАЗВЕРНУТЬ: она добавляет к массиву с названиями компаний два лишних пустых столбца, заполненных ошибкой Н/Д. Которую мы заменяем на исходные данные с помощью ЕСНД / IFNA. Иначе говоря, мы клонируем каждое название компании три раза (по числу столбцов с данными), потом превращаем в одномерный массив с помощью ПОСТОЛБЦ / TOCOL.

В отдельном сообщении выше файл с формулой с пошаговым разбором отмены свертывания — забирайте, изучайте и пользуйтесь!

Самый компактный вариант для визуализации прогресса в Excel — гистограмма (условное форматирование)

По умолчанию при вставке гистограмм для максимального значения гистограмма занимает всю ячейку, а для остальных — относительно максимального. Но можно задавать вручную максимальное значение. Если таковым сделать план, а самой гистограммой визуализировать факт — то и получится визуализация выполнения.

Сделать такой простенький прогресс-бар можно так:
— Сослаться на фактическое значение в отдельной ячейке, куда мы вставим гистограмму;
— Вставить гистограмму в эту ячейку по вкусу;
— Задать в настройках правила условного форматирования максимальное значение как план/цель — тогда гистограмма будет шириной во всю ячейку, когда факт будет равняться плану. Иначе — будет показываться % выполнения;
— Можно включить опцию "Показывать только столбец", фактическое значение нам здесь не нужно — оно и так есть в исходной ячейке. А тут пусть будет только наш прогресс-бар.

Отличия по строкам
Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)?

1 Выделяем столбцы (можно быстро быстро выделить их сочетанием Ctrl + Shift + стрелка вниз)
2 Ctrl + G —> Выделить (Special)
3 Отличия по строкам (Row differences)
4 Красим выделенные ячейки нужным цветом. Готово!

Смотрим на GIF (без звука)

Видео для новичков: абсолютные и относительные ссылки в Excel

Друзья, вашему вниманию видео по фундаментальной для понимания табличных формул (ссылки работают +- одинаково и в Excel, и в Google Таблицах, и в других офисных пакетах) теме:
— Абсолютные и относительные ссылки
— Смешанные ссылки
— Стиль ссылок R1C1
— Режим "Показать формулы"
— Бонус: почему объединенные ячейки могут портить жизнь при работе с формулами

8 минут со звуком:
https://youtu.be/jCgDpHkmE0s