☰

Найдите одинаковые данные на двух листах и ​​поместите их на третий лист.

tag-icon • tag-icon microsoft-excel combine
Найдите одинаковые данные на двух листах и ​​поместите их на третий лист.

Какой самый лучший/простой способ объединить данные из двух разных таблиц Excel в третью таблицу Excel? В таблице 1 будет только имя пользователя. В таблице 2 будет много информации, включая имя, фамилию, отдел, имя пользователя и т. д. Необходимо, чтобы в третьей таблице отображались имена пользователей из таблицы 1 с именем, фамилией и отделом из таблицы 2.

решение1

Функция, которую вы хотите использовать, — это VLOOKUP. То, как вы это сделаете, будет немного зависеть от того, как организованы ваши листы, но все они будут следовать одному и тому же синтаксису:

=ВПР( lookup value, table array, column index number, range lookup)

  • lookup value— это данные, которые вы хотите найти.
  • table arrayопределяет ячейки, из которых вы хотите извлечь данные, включая столбец, содержащий цель поиска
  • column index number— это индекс столбца, table arrayиз которого вы хотите извлечь информацию. (например: для массива A:E столбец D будет 4.)
  • range lookupэто опция TRUE/FALSE, указывающая, приемлемо ли приблизительное совпадение или необходимо точное совпадение. Чтобы не усложнять, я всегда устанавливаю это значение FALSE. Нажмите F1 в Excel, если вам нужны дополнительные сведения.

Формат вашей формулы будет немного отличаться в зависимости от того, находятся ли все ваши данные в одной рабочей книге или нет. Ниже я приведу примеры для каждого из них.

ВАЖНАЯ ЗАМЕТКА:Поисковый термин, который вы используете в качествеlookup value долженбыть доступным для поиска в первом столбце table arrayдля работы функции VLOOKUP.

Первый пример: Все данные будут находиться в одной книге Excel, но на разных листах. Первый лист называется «Имена пользователей» и содержит только имена пользователей. Второй лист называется «Данные пользователей» и содержит все данные о пользователях. Третий лист мы назовем «Результаты поиска». Лист «Данные пользователей» содержит пять столбцов, A:E.

  1. Убедитесь, что в таблице «Данные пользователей» в столбце A указаны все имена пользователей.
  2. Скопируйте все имена пользователей из раздела «Имена пользователей» в раздел «Результаты поиска».
    • Я предполагаю, что вы используете строку заголовка, поэтому первое имя пользователя в «Результатах поиска» попадет в ячейку A2.
  3. Формула для B2 в «Результате поиска» должна быть следующей: =VLOOKUP(A2,'User Data'!A:B,2,FALSE)
  4. Формула для B3 в «Результате поиска» должна быть следующей: =VLOOKUP(A3,'User Data'!A:B,2,FALSE)
  5. Формула для C2 в «Результате поиска» должна быть следующей: =VLOOKUP(A2,'User Data'!A:C,3,FALSE)

Вы должны увидеть здесь шаблон к настоящему моменту. Для каждого столбца вы должны иметь возможность просто написать формулу VLOOKUP в первой ячейке (например, B2), а затем заполнить формулой оставшуюся часть листа. Однако, копирование и вставка формулычерезстолбцы не так просты - нужно обновить значения table arrayи column index number.

Второй пример: Каждый набор данных хранится в своей собственной книге Excel. Имена листов в книге — по умолчанию (например: первый лист — «Лист1»). Имена файлов книги — «Имена пользователей.xlsx», «Данные пользователя.xlsx» и «Результаты поиска.xlsx». Все они находятся в папке «Мои таблицы», которая находится на рабочем столе пользователя с именем «Я».

  1. Убедитесь, что на листе «User Data.xlsx» в столбце A указаны все имена пользователей.
  2. Скопируйте все имена пользователей из «Usernames.xlsx» в «Lookup Results.xlsx».
    • Опять же, если вы используете строку заголовка, это будет начинаться с ячейки A2.
  3. Формула для B2 в "Lookup Result.xlsx" должна быть=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  4. Формула для B3 в "Lookup Result.xlsx" должна быть=VLOOKUP(A3,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  5. Формула для C2 в "Lookup Result.xlsx" должна быть=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)

Опять же, вы должны были уже увидеть здесь шаблон. Вырезайте/вставляйте/подправляйте по мере необходимости по строкам и по столбцам, и все готово.

Еще одна вещь, которую следует иметь в виду, заключается в том, что этот лист не будет автоматически обновляться при изменении данных в "Имена пользователей". Изменения в листе "Данные пользователей" можно получить с помощью этого метода, но если вы хотите отслеживать изменения в "Имена пользователей", вам понадобятся более продвинутые методы.

решение2

Vlookup и подобные будут работать только в том случае, если данные в обоих листах идентичны.
Я считаю, что вам нужна надстройка нечеткой логики для Excel. Она позволит вам находить похожие результаты на основе нескольких различных параметров. Проверьтестраница загрузки.

решение3

ГПР/ВПР — используйте формулу для извлечения имени пользователя из Электронной таблицы 1, затем используйте имя пользователя в качестве ключа, а электронную таблицу 2 — в качестве матрицы поиска для одного экземпляра ГПР/ВПР (я не знаю, какой из них какой, так как использую неанглоязычную версию Excel).

решение4

Вы можете использовать запрос из файлов Excel:

  • Определите имя для набора данных в электронной таблице 1 (вкладка «Формулы» -> «Определить имя»)
  • Определите имя для набора данных в таблице 2.
  • В таблице 1 перейдите на вкладку «Данные», выберите «Из других источников» и в раскрывающемся списке выберите «Из Microsoft Query».
  • Выберите другой файл электронной таблицы и подтвердите, что вы хотите вручную объединить столбцы.
  • В следующем окне «Запрос из файлов Excel» перетащите столбец «имя пользователя» первого набора данных в столбец «имя пользователя» второго набора данных — между этими столбцами будет создана связь.
  • Перейдите в меню «Файл», нажмите «Вернуть данные в MS Office Excel», появится диалоговое окно «Импорт данных».
  • Выберите лист, в который вы хотите импортировать сопоставленные данные.
  • Нажмите «ОК» — вы должны увидеть сопоставленные данные со столбцами из обеих таблиц.

Связанный контент