Ближе к делу
Функции — сердце каждой таблицы в Microsoft Excel: с их помощью вы получаете нужные значения или целые массивы данных. Но стоит объединить несколько функций в одну формулу, и перед вами — настоящий сверхинструмент для любых, даже самых сложных задач.
В этой статье я разберу как классические, так и свежие фишки Excel (они работают в Excel 365 и веб-версии). Если у вас совсем старая версия программы, некоторые формулы могут не поддерживаться.
Microsoft 365 Персональный
Microsoft 365 открывает доступ к Word, Excel, PowerPoint на пяти устройствах, 1 ТБ облака OneDrive и множеству других полезных сервисов.
1. INDEX + XMATCH: Мощный и гибкий поиск данных
Комбо INDEX и MATCH уже классика Excel. Но если заменить устаревший MATCH на XMATCH, у вас появится инструмент моментального поиска по любой части таблицы.
INDEX достаёт из массива нужное значение: XMATCH находит номер строки или столбца, а INDEX возвращает результат по этому адресу. Работает и по строкам, и по столбцам, может вернуть как одно значение, так и целую строку или столбец.
Базовый синтаксис INDEX:
где…
Допустим, чтобы узнать финальный балл игрока Натаниэль, я пишу:
Тут Натаниэль в пятой строке таблицы T_Scores, а оценка хранится в шестом столбце.
Но если прописывать номер строки вручную, смысл в автоматизации теряется — вы же и так видите, где нужная строка. Намного удобнее вложить внутрь XMATCH — чтобы строка выбиралась автоматически!
Синтаксис XMATCH:
где…
Теперь формула выглядит так:
Внутренний XMATCH ищет в столбце Player таблицы T_Scores то, что указано в ячейке H2, определяет номер строки — и отдаёт его INDEX.
И да — если не писать два последних аргумента в XMATCH, будет выполняться простой поиск сверху вниз по точному совпадению. Это стандартные настройки:
Теперь, если я выберу в H2 другого игрока, XMATCH найдёт нужную строку, а INDEX тут же подтянет данные по нему.
Удобно! Но что, если мне нужна не "итоговая", а, скажем, оценка за конкретный раунд?
Пока столбец прописан жёстко — придётся каждый раз менять формулу вручную. Но и тут выручит вложенный XMATCH! Его можно внедрить для поиска не только строки, но и столбца:
В итоге один XMATCH определяет строку по имени игрока, второй — столбец по нужному раунду. Вот вам идеальный двунаправленный поиск!
Проверяю в деле: выбираю другого игрока — формула моментально выдаёт результат для любого раунда.
2. IF + AND/OR: Молниеносная проверка сразу нескольких условий
Обычный IF проверяет только одно условие: если оно выполняется — возвращает один результат, если нет — другой. А вот если критериев несколько, приходят на помощь AND и OR.
Синтаксис IF:
где…
Например, чтобы выяснить, есть ли у сотрудника стаж больше пяти лет, пишу:
в ячейку C2. Если стаж больше 5 лет — Excel выдаст "Y" (да), иначе "N" (нет). И да, формула сама распространится вниз по списку.
А если нужно отнести к "Старшим" тех, у кого и стаж больше 5 лет, и есть золотой сертификат? Все остальные — "Младшие".
Тут IF и AND работают вместе:
где…
Финальная формула:
Имейте в виду: любой текст в логических формулах Excel должен быть в кавычках (кроме TRUE и FALSE).
Вбиваю формулу в D2, растягиваю вниз — и статус появляется у каждого.
AND выдаёт "да" только если совпали все условия. А если стать менеджером можно либо при 10 годах стажа, или если есть золотой сертификат, то нужна комбинация IF + OR:
Как вложить OR в IF:
где…
Вставляю формулу в D2:
Жму Enter — и вижу:
А если результат — TRUE или FALSE, можно превратить их прямо в чекбоксы! Выделяю столбец, нажимаю «Чекбокс» на вкладке «Вставка» — и готово.
3. UNIQUE + FILTER: Очищаем выдачу от дублей!
UNIQUE показывает только уникальные значения, FILTER — выводит данные по заданному фильтру.
Начнем с FILTER. Вот базовый формат:
где…
Например, если написать:
в F2 — Excel отдаст имена всех менеджеров магазинов типа "DIY" из таблицы T_Shops. Если таких нет, напишет "Нет результата".
Результаты FILTER и UNIQUE "расходятся" вниз и вправо по пустым ячейкам рядом — оставляйте свободное место, иначе появится ошибка "#ПЕРЕЛИВ!". И ещё: такие формулы не работают внутри структурированных таблиц, только на обычных ячейках.
Если менеджер ведет несколько магазинов, его имя появится несколько раз:
Вот почему следующим шагом применяем UNIQUE. Пример простого вызова:
где…
В H2 такая формула покажет всех уникальных менеджеров без повторов из столбца "Менеджеры" таблицы T_Shops:
А если соединить обе формулы — вложить FILTER в UNIQUE — сразу избавляемся от дублей и оставляем только нужных по типу магазинов:
Хотите ещё и отсортированный список? Просто вложите результат ещё и в SORT!
4. EOMONTH + SEQUENCE: Список последних дней месяца за одну секунду!
Для отчётов, бюджета или годового планирования — комбо EOMONTH и SEQUENCE поможет вам сгенерировать автоматический список конечных дат месяца, начиная с любой точки.
Сначала проверьте: ячейки с датами должны быть в формате "Дата" или хотя бы "Числовой формат" (выделите, нажмите Ctrl+1 и выберите нужное). Иначе Excel покажет вместо дат странные числа.
Работать с EOMONTH очень просто — в неё нужно всего два значения:
где…
Например:
Указали дату из C1 (например, 15 августа), добавили 3 месяца (B2) — получили 30.11, последний день ноября.
Но как показать сразу целый список дат на конец каждого месяца? Тут нам пригодится SEQUENCE — ему можно указать сразу четыре параметра:
где…
Например:
в A1 появится цепочка из 10 нечётных чисел — для теста.
SEQUENCE формирует массив и автоматически расширяет его по нужному диапазону. Главное — оставьте рядом свободное место, чтобы не столкнуться с ошибкой #ПЕРЕЛИВ!
Теперь связаем обе функции: SEQUENCE станет аргументом месяц для EOMONTH.
Если начальная дата в A1, то для вывода всех последних дней месяцев 2025 года пишу в A2:
где A1 — стартовая дата, 11 — следующие месяцы (февраль–декабрь).
Нужно вывести последний день каждого второго месяца? В A2 пишу:
Здесь первый параметр SEQUENCE — 6 (шесть месяцев), последний — 2 (шаг — два месяца).
А если нужно получить датасет не столбиком, а строчкой — формула такая:
Второй параметр SEQUENCE — это столбцы, последний — шаг (через месяц).
EOMONTH с SEQUENCE — формула "живая": сменили стартовую дату — и весь список дат перерендерился. А чтобы закрепить результат, выделите нужные даты, нажмите Ctrl+C, затем Ctrl+Shift+V и выберите "Вставить значения".
В этом обзоре я поделился формулами Excel, которые реально экономят время и делают работу с таблицами намного приятнее. И главное — не бойтесь комбинировать даже одни и те же функции: например, два XLOOKUP подряд для супер-гибкого поиска!
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru