Home » Мои разработки » Сравнение и соединение таблиц с данными в Excel

Сравнение и соединение таблиц с данными в Excel

Допустим имеется 2 таблицы с данными, при этом 1 столбец в данных таблицах одинаков, таблицы размещены на разных листах. В моем примере одинаков столбец "А".

Необходимо, объединить данные таблицы при этом сравнение будет производится по столбцу "А"(ФИО).Будем использовать стандартную функцию Excel - ВПР.

Итак, функция для сравнения и соединения таблиц, будет следующей:

=ЕСЛИ(ЕНД(ВПР(A2;Лист2!$A$1:$B$200;2;0));"";ВПР(A2;Лист2!$A$1:$B$200;2;0))

Опишу части формулы:

 

ВПР(A2;Лист2!$A$1:$B$200;2;0)

• A2 - значение, которое должно быть найдено в первом столбце табличного массива. В моем примере ищем значение указанное в столбце "A", начиная с ячейки A2.
• Лист2!$A$1:$B$200; -таблица в которой искать, т.е. в данном случае это ссылка на лист2(Лист2), а таблица ($A$1:$B$200) задается, как и все таблицы в Excel - задается левое верхнее значение и правое нижнее значение, т.е. A1-левое верхнее, B200-правое нижнее.
• Значок $ - обозначает абсолютную ссылку в формуле и используется при указании фиксированного адреса ячейки.
• 2; - номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д.  В моем примере, возвращается значение из второго столбца из таблицы $A$1:$B$200.0;
• Функция ЕСЛИ. Синтаксис (лог_выражение;значение_если_истина;значение_если_ложь).
  В данном случае:
  • если значение недоступно, то ничего не указыват
  • если значение доступно и выполнилась функция ВПР, то записываем в ячейку полученное значение.
• Функция ЕНД. Возвращает ИСТИНА, если значение ссылается на значение ошибки #Н/Д (значение недоступно). Необходимо для проверки.

Некоторые пояснения, данная функция будет сравнивать значение по столбцу "А", следующим образом:

1. Берется первое значение в стоблце "А" листа 1
2. Происходит поиск во всем столбце "А" на листе 2
3. Если найдено совпадение, информация из строки на листе 2, дополняется в ячейку, где используется функция.
4. Если значений на листе 2, больше двух, то необходимо указать в функции правую границу поиска, т.е. крайний правый столбец, в моем примере это столбец "B", т.к. первое значение это ФИО, второе внутренний номер телефона(см. таблицу 1 и таблицу 2.)
5. Поиск будет производится до двухсотой строки (значение $B$200), но это значение можно конечно же увеличивать и уменьшать, в зависимости от количества записей в вашей таблицы.

Вставляем функцию в столбец "F" и растягиваем по всей длине списка.Получаем:Данный метод, я использовал для соединения таблиц, с количеством записей(строк) - 300, и столбцов - 10. При этом указанная формула использовалась для нескольких таблиц, т.е. 1ая - список ФИО + дни рождения, 2ая - список ФИО + телефоны, 3ий - список ФИО + должности + отделы + кабинеты.

 

Скачать пример работы с функцией ВПР