Если вы часто работаете с таблицами в Excel, у вас обязательно возникнет необходимость свести данные из нескольких таблиц в одну. В этой статье я покажу, как это сделать.
Таблицы и данные
Итак, на входе мы имеем несколько таблиц (или диапазонов) с данными. Для простоты объяснений приведу пример с синтетическими данными.
На одном листе расположена таблица с базовыми данными по сотрудникам компании:
На другом листе есть таблица с их контактными данными, но в неявном виде, без указания имени и фамилии сотрудника. Однако, обе таблицы имеют "Код" сотрудника, это и будет нашим ключом для объединения:
Обращаю внимание, что это именно таблицы, а не диапазоны данных. Если данные собраны в диапазоне и не отформатированы как таблица, способ их пересечения будет немного отличаться.
Для удобства зададим каждой таблице понятное имя. В моем случае это "сотрудники" и "контакты". Имена таблиц можно посмотреть или изменить на вкладке "КОНСТРУКТОР", в разделе "Свойства".
На этом подготовительные работы завершены, переходим к пересечению таблиц.
Соединение данных
В случае, когда обе таблицы имеют общее поле, мы можем объединить их при помощи формулы ВПР. Дополним таблицу, в которую мы хотим поместить данные из другой дополнительными столбцами "Электронная почта" и "Номер телефона":
Щелкаем мышкой в первую свободную ячейку столбца "Электронная почта" и пишем формулу:
Значение каждого аргумента функции указано в схеме ниже. Имя ключевого столбца таблицы подставляется автоматически, после клика на любую его ячейку. В указанной таблице выборка осуществляется по крайнему левому столбцу.
Аналогично поступаем и со столбцом "Номер телефона", в результате получаем таблицу, заполненную всеми известными данными.
Просто, не правда ли?
Нюансы
В примере выше у нас получилось всё легко и непринужденно, однако в реальности все бывает сложнее. К примеру, ключевые значения одной таблицы могут быть представлены как число, а в другой как то же самое число, но в виде текста.
Воспроизведем данную ситуацию в таблице "контакты":
Переключившись на таблицу "сотрудники" сразу увидим, что она отреагировала на изменения не лучшим образом. Все контактные данные сотрудников исчезли:
Происходит это из-за того, что в логике Excel число не может быть равно строке, поэтому ключевое значение в целевой таблице просто не находится. Чтобы избежать таких ошибок мы можем немного модернизировать нашу формулу получения данных, добавив в нее еще одну функцию - ТЕКСТ. Она позволяет преобразовать числа в текст в указанном формате, таким образом все данные в таблице должны снова появиться:
Можно решить эту проблему и другим способом, например добавив в начало таблицы "контакты" еще один столбец, в котором все цифры, записанные как текст перевести в числовой формат при помощи функции ЗНАЧЕН:
Мы преобразовали строку в число, однако также добавили при этом лишний столбец (чтобы он использовался функцией ВПР в качестве столбца с ключевым значением). Таким образом, у нас изменились данные в таблице "сотрудники" - вместо электронной почты теперь текстовая запись кода сотрудника, а вместо номера телефона - электронная почта:
Меняем номера столбцов в формуле ВПР (увеличиваем на единицу, из-за добавленного столбца) и наслаждаемся результатом:
Заключение
Если вы прочитали данную статью, то вам осталось лишь немного попрактиковаться, чтобы успешно освоить навык пересечения таблиц в Excel и решать некоторые проблемы, сопряженные с этим действием.
Подведем итог, функция ВПР - мощный и полезный инструмент для повседневного использования, крайне необходимый для освоения, а функции ЗНАЧЕН и ТЕКСТ - важное дополнение, которое всегда поможет преобразовать данные в таблицах в необходимый формат.