Заменить содержимое столбца Excel действительными данными из другого столбца / листа?

Question

У меня есть две таблицы Excel, которые были экспортированы из MySQL, с реляционными данными в них, и мне нужно заменить ссылочные идентификаторы фактическими данными из относительного столбца.

Например, в одной таблице:

+----------------------------------------+
| ID  | Name | ForeignKey1 | ForeignKey2 |
+----------------------------------------+
| 1   | Bob  | 1           | 2           |
| 2   | Dave | 2           | 1           |
| 3   | Mary | 3           | 4           |
| 4   | Jane | 4           | 1           |
| etc........

Затем в другой таблице, на которую ForeignKey :

+---------------------+
| ID | Name           |
+---------------------+
| 1  | Banana         |
| 2  | Apple          |
| 3  | Cat            |
| 4  | Dog            |
| etc.......

Есть ли простой способ заменить внешний ключ на относительные данные в Excel, чтобы мои данные выглядели так?

+----------------------------------------+
| ID  | Name | ForeignKey1 | ForeignKey2 |
+----------------------------------------+
| 1   | Bob  | Banana      | Apple       |
| 2   | Dave | Apple       | Banana      |
| 3   | Mary | Cat         | Dog         |
| 4   | Jane | Dog         | Banana      |
| etc........

Answer 1

Это можно сделать с помощью формулы vlookup:

=VLOOKUP(Lookup Value , Table, Column, Exact Match or Approximate Match)

• Значение поиска - это значение, которое нужно искать в таблице, например, A2

• Таблица - это массив выбранных столбцов, которые нужно найти для поиска. Не забудьте добавить сюда дополнительные столбцы, относящиеся к 'Lookup Value', которые будут использоваться в качестве заменяющих значений.

  Например, если вы ищете идентификатор и хотите заменить его на имя, выберите в «справочном листе» столбец с именем «ID» и столбец с именем «Имя». Формула таблицы будет иметь вид:

  =NameofSheet!ColumnNameOfID:ColumnNameOfName
  

• Столбец - это имя столбца из таблицы, которое будет отображаться вместо «Поиск значения»

Вставьте столбец рядом с вашим столбцом внешнего ключа и столбец рядом с вашим столбцом внешнего ключа 2 и используйте vlookup для ссылки на вторую таблицу (на скриншоте ниже вторая таблица начинается со столбца I)

Стандартный синтаксис выглядит примерно так:

Значение поиска - это ваш ключ, внешний ключ1 для вашего первого столбца и foregin key2 для вашего второго. Таблица - это ваш диапазон поиска, вторая таблица, которая на скриншоте ниже - от I до E, ваш столбец - это индекс вашего диапазона поиска, который вы хотите получить.

Последнее значение "Приблизительное соответствие" - это флаг, который указывает, будет ли ваш поисковый ключ точно соответствовать вашим критериям. Для целей, изложенных выше, для этого параметра должно быть установлено значение false.

=VLOOKUP(C2,$I$2:$J$12,2,FALSE)

Перетащите формулу вниз в новый столбец. Скопируйте значения в столбцы внешнего ключа. Убедитесь, что вы скопировали и вставили значения, иначе ваша формула vlookup будет вставлена в.

Символы «$» перед "I" и "2" указывают, что диапазон останется неизменным даже при перетаскивании в другие ячейки, что не идеально, если у вас есть таблица с динамическими данными. В качестве альтернативы вы можете убрать ограничения строки для диапазона и использовать $ I:$ J в качестве диапазона.

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