Проблема, с которой сталкивается каждый, кто работает с выгрузками
Классическая ситуация: вы получили выгрузку из 1С, банка или CRM. Данные лежат «как есть» — без оглядки на то, что ВПР умеет искать только слева направо. Ключевой столбец (например, ИНН, номер договора или артикул) стоит справа или в середине таблицы, а тянуть нужно данные из столбцов, которые находятся слева от него.
Что делает большинство? Переставляет столбцы вручную. Или копирует данные на новый лист в нужном порядке.
Есть третий путь — не трогать исходную таблицу вообще. Использовать функцию ВЫБОР и создать так называемую «виртуальную таблицу» прямо внутри формулы.
Как это работает: суть трюка
Функция ВПР ищет значение в первом столбце указанного диапазона и возвращает значение из столбца с указанным номером. Если ключ не первый — ВПР не сработает.
Функция ВЫБОР умеет собирать несколько диапазонов в один виртуальный массив, в том порядке, который нужен вам. То есть мы можем «подать» ВПР таблицу, где ключевой столбец уже стоит первым — при этом исходные данные останутся на своих местах.
Синтаксис:
ВЫБОР({1;2}; ключевой_столбец_1; столбец_с_данными_2)
Где:
{1;2} — номера позиций в виртуальной таблице (первый — ключ, второй — данные).
ключевой_столбец_1 — тот, где лежит искомое значение (даже если он справа).
столбец_с_данными_2 — тот, откуда тянем результат (даже если он слева).
Пошаговый пример + ВПР
Допустим, у вас есть таблица:
A | B | C |
|---|---|---|
Сумма | Дата | Номер договора |
15000 | 01.03.2025 | Д-101 |
23000 | 05.03.2025 | Д-102 |
8000 | 07.03.2025 | Д-103 |
Вам нужно по номеру договора (столбец C) найти сумму (столбец A). Ключ — справа, данные — слева. ВПР в лоб не сработает.
Пишем формулу:
=ВПР("Д-102"; ВЫБОР({1;2}; C2:C4; A2:A4); 2; 0)
Что происходит:
ВЫБОР создаёт виртуальную таблицу из двух столбцов: первый — номера договоров (C), второй — суммы (A).
ВПР ищет «Д-103» в первом столбце этой виртуальной таблицы.
Возвращает значение из второго столбца — 8 000.
Исходная таблица не тронута. Столбцы не переставлены. Всё работает.
Нюансы
1. Количество столбцов в ВЫБОР должно совпадать.
Если вы указываете {1;2}, то и диапазонов должно быть ровно два. Если нужно три — {1;2;3} и три диапазона.
2. Альтернативы.
Если у вас Excel 365 или 2021, проще использовать XLOOKUP (ПРОСМОТРX) — он умеет искать в любом направлении без танцев с ВЫБОР. Но если вы работаете в старой версии или с файлами, которые открываются у коллег — трюк с ВЫБОР остаётся незаменимым.