☰

Excel — объединение 2 листов в 1 на основе идентификатора человека и создание новых строк, если идентификатор найден более одного раза

tag-icon • tag-icon microsoft-excel worksheet-function
Excel — объединение 2 листов в 1 на основе идентификатора человека и создание новых строк, если идентификатор найден более одного раза

У меня есть данные о человеке и адресные данные на двух разных листах, которые я хочу объединить в один. Проблема в том, что у каждого человека может быть более одного адреса.

Например, если у меня есть следующие 2 набора данных:

**Person Data**
PersonID         Name

1          John Smith
2          Mark Brown

**Address Data**
PersonID      Address

1                  UK
1              France
1                 USA
2                  UK

Мне нужно, чтобы они были объединены следующим образом:

**Combined Data**
PersonID      Name       Address

1       John Smith            UK
1       John Smith        France
1       John Smith           USA
2       Mark Brown            UK

Есть ли способ сделать это через формулы Excel? Спасибо

решение1

Похоже, вы просто хотите добавить имя человека в каждую строку списка адресных данных. Если так, то это просто.

Предположения

  • Данные о людях отсортированы и последовательны (т. е. PersonID 1 находится в строке 2, PersonID 100 находится в строке 101) — если это не так, см. последний раздел.
  • Адресные данные сортируются

Решение

**Combined Data Sheet**
PersonID          Name                            Address
=$Addresses!A2    =OFFSET($Names!$B$1, A2, 0)     =$Addresses!B2

И, конечно же, скопируйте формулы вниз.

Объяснение

Первый и последний столбцы просто копируются из листа Addresses. Функция OFFSETпозволяет вам перемещаться вниз или поперек на определенную величину. В этом случае мы знаем, что правильный Person будет в строке PersonID + 1. Поскольку мы начинаем со строки 1 ( $Names!$B$1), +1 не нужен.

Непоследовательные идентификаторы лиц

Функцию VLOOKUPможно использовать для поиска PersonID, а затем вернуть другой столбец в той же строке. В этом случае, что-то вроде:

=VLOOKUP(A2, Names!$A$1:$B$1000, 2, FALSE)

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