ВПР онлайн: объединить таблицы по ключу

Режимы соединения и приёмы нормализации

Четыре режима закрывают задачи, которые в Excel решают ВПР, ИНДЕКС с ПОИСКПОЗ и СЧЁТЕСЛИ; нормализация ключей заменяет цепочки СЖПРОБЕЛЫ и ПОДСТАВИТЬ.

Как две таблицы находят друг друга

Что умеет ВПР онлайн

Инструмент повторяет функцию ВПР из Excel, но без формул и без требований к формату ключей. Две таблицы вставляются из буфера или загружаются файлами .xlsx и .csv, первая строка каждой — шапка, по ней выбираются ключевые столбцы; если названия совпадают, ключ находится сам. Столбцы второй таблицы, которые нужно подтянуть, отмечаются галочками и добавляются справа к первой.

Ключи перед сравнением приводятся к одному виду: регистр опускается, пробелы по краям и двойные внутри убираются, буква ё считается за е, в режиме «только цифры» остаются цифры, что спасает телефоны с плюсами и скобками и артикулы с дефисами, ведущие нули отбрасываются по желанию. Дубли ключа во второй таблице обрабатываются двумя способами: первое значение, как в Excel, или все значения через точку с запятой, чтобы увидеть, что у одного клиента три заказа.

Четыре режима: «Подтянуть» отдаёт все строки первой таблицы с новыми столбцами, «Совпадения» только пары, «Только в А» и «Только в Б» — строки без пары в другой таблице, то есть сверку. Результат показан с подсветкой найденного и ненайденного, счётчики стоят над ним, файл XLSX или CSV содержит все строки, а кнопка «Копировать» отдаёт таблицу с табуляциями для вставки в Excel.

От перфокарт Холлерита до JOIN и ВПР

Соединять записи по общему признаку люди начали задолго до компьютеров: картотеки библиотек и контор ссылались с одной карточки на другую по номеру, а бухгалтеры сверяли книгу продаж с банковской выпиской по номеру документа. В 1890 году Герман Холлерит механизировал перепись населения США перфокартами: табулятор считал карточки по пробитым признакам, а сортировальная машина раскладывала их по карманам — соединение и сверка стали задачей машины.

Математическую основу дал Эдгар Кодд в 1970 году, описав реляционную модель данных: таблицы связываются по общим столбцам, а операция соединения даёт новую таблицу. Из его статьи выросли SQL и команда JOIN с вариантами INNER, LEFT и анти-соединением, которые в точности соответствуют режимам «Совпадения», «Подтянуть» и «Только в А».

В электронных таблицах ту же задачу решала функция LOOKUP, а в Excel с 1985 года — VLOOKUP, по-русски ВПР: она ищет значение в первом столбце диапазона и возвращает клетку из указанного столбца. Формула капризна к формату ключей и возвращает только первое совпадение, поэтому в 2019 году Microsoft добавила ПРОСМОТРX. Инструмент делает то же самое без формул и нормализует ключи сам.

Как объединить две таблицы

Вставьте основную таблицу в поле А и таблицу-справочник в поле Б, либо загрузите файлы. Убедитесь, что первая строка каждой таблицы — шапка: по ней выбираются ключи и столбцы для подтягивания. Если ключ угадан неверно, смените его в списках под полями; для телефонов и ИНН включите «только цифры».

Выберите режим над результатом. «Подтянуть» отвечает на вопрос «что стоит напротив каждой строки А», «Совпадения» собирает пары, «Только в А» и «Только в Б» показывают расхождения. Счётчики сообщают, сколько ключей совпало, сколько осталось без пары с каждой стороны и сколько дублей ключа во второй таблице.

Проверьте подсветку: зелёные клетки подтянуты, жёлтые остались пустыми, красные строки без пары. Затем скачайте XLSX или CSV со всеми строками либо скопируйте таблицу и вставьте в Excel по клеткам. Кнопка «Пример» показывает всё на четырёх строках заказов и прайса.