ВПР Excel: как сопоставить списки учеников по коду участника

Выберите общий ключ двух списков

Допустим, у куратора есть таблица результатов и отдельный справочник учебных групп. В первой записаны код участника и балл, во второй — код и группа. Нужно добавить группу к каждому результату, чтобы затем разобрать затруднения. Это условные внешние таблицы для объяснения функции; статья не предполагает автоматическую выгрузку такой структуры из Umhub.

Не соединяйте списки по номеру строки: сортировка у них может различаться. Имя человека тоже бывает ненадёжным ключом из-за совпадений и вариантов написания. В примере используем устойчивые коды U101, U102 и U103. Сначала убедитесь, что один код справочника соответствует одной группе в выбранном контексте. Если ученик участвует в нескольких потоках, потребуется более точный ключ или предварительный отбор нужного потока.

Подготовьте маленький контрольный набор

На листе Results разместите заголовки «Код», «Балл», «Группа». В строках 2–5 запишите пары: U103 и 8; U101 и 6; U104 и 9; U102 и 7. Столбец группы пока пустой. На листе Groups в A2:B4 внесите справочник: U101 — «Утро», U102 — «Вечер», U103 — «Утро». Все значения вымышлены; U104 специально отсутствует во втором списке.

До формулы сформулируйте ожидаемый результат: «Утро», «Утро», отсутствие соответствия, «Вечер». Число строк в Results должно остаться четыре, сумма баллов — 30. Эти проверки решают разные задачи: сумма помогает заметить потерю чисел, а ручное соответствие кодов — ошибочное назначение группы. Даже правильная сумма не доказывает, что сведения соединены верно.

Задайте точный поиск и закрепите справочник

В C2 листа Results введите формулу в английском синтаксисе: =VLOOKUP(A2,Groups!$A$2:$B$4,2,FALSE). Здесь A2 — искомый код, диапазон Groups — справочник, 2 — номер возвращаемого столбца внутри этого диапазона, FALSE — точное соответствие. В русской локализации функция называется ВПР; разделители аргументов зависят от настроек. При затруднении заполните поля через мастер функций.

По описанию VLOOKUP от Microsoft, поиск идёт в первом столбце указанного диапазона, а результат возвращается из выбранного столбца той же строки. Не пропускайте последний аргумент: по умолчанию используется приблизительный поиск. Знаки доллара удерживают границы справочника при копировании формулы вниз. Протяните её до C5 и сопоставьте каждую строку с заранее записанным ожиданием.

Разберите ненайденный код без подмены результата

Для U104 точного совпадения нет, поэтому ожидается ошибка #N/A, в русской локализации — #Н/Д. Это сигнал о соответствии, а не нулевой балл и не доказательство, что человек не учится. Код мог отсутствовать в справочнике, содержать пробел или относиться к другому потоку. Общие причины разобраны в справке Microsoft об ошибке поиска.

Оставьте такую запись в отдельном списке проверки. Не заменяйте все ошибки на ноль ради аккуратного вида отчёта: так техническая проблема превратится в ложное учебное значение. Если нужно удобное обозначение для читателя, сначала выясните причину и отдельно укажите «Группа не найдена». Исходный балл 9 у U104 должен сохраниться, даже когда признак группы пока неизвестен.

Проверьте неоднозначные ключи и границы диапазона

Теперь мысленно добавьте к справочнику ещё одну строку U102 с другой группой. Такая ситуация требует решения до массового соединения: какой поток рассматривается и почему одному коду назначены разные признаки? Функция поиска не выбирает правильную бизнес-версию данных. Правила разбора дубликатов и конфликтов помогают отличить случайную копию от разных событий одного ученика.

Проверьте также, попали ли новые строки в диапазон формулы. Ссылка $A$2:$B$4 закреплена, но сама по себе не расширяется вслед за добавлением данных ниже. После изменения справочника пересмотрите границы и повторите контрольный набор. Для реальных кодов с ведущими нулями сохраняйте одинаковый тип и написание в обоих списках: отображение числа как 0012 не всегда означает, что хранится именно такой текстовый код.

Передайте таблицу с понятным статусом проверки

В нашем примере должны остаться четыре результата с суммой 30: три группы найдены, одна запись требует уточнения. Проверьте, что баллы 8, 6, 9 и 7 остались у U103, U101, U104 и U102 соответственно. Запишите версию справочника и число ненайденных кодов. Это позволит следующему сотруднику отличить неполное соединение от окончательно проверенного отчёта.

После исправления источника повторите расчёт и только затем используйте сводную таблицу для анализа заданий. ВПР добавляет соответствующий признак, а сводная уже обобщает подготовленные строки. Сохранённый контрольный пример пригодится при очередной загрузке списка: по нему быстро видно, не изменились ли структура справочника, диапазон или правило идентификации участника.