Каков наилучший / самый простой способ объединить данные из двух разных электронных таблиц Excel в третью электронную таблицу Excel? Таблица 1 будет иметь только имя пользователя. Электронная таблица 2 будет содержать много информации, включая имя, фамилию, отдел, имя пользователя и т.д. Требуется, чтобы в третьей электронной таблице отображались имена пользователей из электронной таблицы 1 с именем, фамилией и отделом из электронной таблицы 2.
4 ответа
5
Функция, которую вы хотите использовать - VLOOKUP . То, как вы это сделаете, будет немного зависеть от того, как устроены ваши листы, но все будут следовать тому же синтаксису:
= 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- это параметр ИСТИНА / ЛОЖЬ, который указывает, является ли приблизительное совпадение приемлемым или требуется точное совпадение. Для простоты я всегда устанавливаюFALSE. Нажмите F1 в Excel, если вам нужно больше деталей.
Формат вашей формулы будет немного отличаться в зависимости от того, находятся ли все ваши данные в одной книге или нет. Я приведу примеры для каждого ниже.
ВАЖНОЕ ПРИМЕЧАНИЕ: поисковый термин, который вы используете в качестве lookup value должен быть найден в первом столбце table array чтобы VLOOKUP работал.
Первый пример: все данные будут в одной книге Excel, но на разных листах. Первый лист называется "Имена пользователей" и содержит только имена пользователей. Второй лист называется "Данные пользователя" и содержит все данные пользователя. Мы назовем третий лист "Результаты поиска". Таблица "Данные пользователя" содержит пять столбцов, A:E
- Убедитесь, что в электронной таблице "Данные пользователя" есть все имена пользователей в столбце А.
- Скопируйте все имена пользователей из "Имена пользователей" в "Результаты поиска".
- Я предполагаю, что вы используете строку заголовка, поэтому первое имя пользователя в "результатах поиска" будет отображаться в A2.
- Формула для B2 в "Результат поиска" должна быть:
=VLOOKUP(A2,'User Data'!A:B,2,FALSE) - Формула для B3 в "Результат поиска" должна быть:
=VLOOKUP(A3,'User Data'!A:B,2,FALSE) - Формула для C2 в "Результат поиска" должна быть:
=VLOOKUP(A2,'User Data'!A:C,3,FALSE)
Вы должны увидеть образец здесь сейчас. Для каждого столбца вы должны просто написать формулу VLOOKUP в первой ячейке (например, B2), а затем заполнить формулу до конца листа. Тем не менее, вырезать и вставлять формулы по столбцам не так просто - вам нужно обновить значения table array и column index number .
Второй пример: каждый набор данных хранится в отдельной книге Excel. Имена листов в рабочей книге являются значениями по умолчанию (т. Е. Первый лист называется "Лист1"). Имена файлов книги: «Usernames.xlsx», «User Data.xlsx» и «Lookup Results.xlsx». Все они находятся в папке "My Spreadsheets", которая находится на рабочем столе пользователя под названием "Me".
- Убедитесь, что на листе «User Data.xlsx» есть все имена пользователей в столбце A.
- Скопируйте все имена пользователей из «Usernames.xlsx» в «Lookup Results.xlsx».
- Опять же, предполагая, что вы используете строку заголовка, это начнется в A2.
- Формула для B2 в "Lookup Result.xlsx" должна иметь вид
=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE) - Формула для B3 в «Lookup Result.xlsx» должна иметь вид
=VLOOKUP(A3,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE) - Формула для C2 в «Lookup Result.xlsx» должна иметь вид
=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)
Опять же, вы должны быть в состоянии увидеть образец здесь сейчас. Вырежьте / вставьте / настройте по мере необходимости по строкам и столбцам, и все готово.
Еще одна вещь, которую следует иметь в виду, заключается в том, что этот лист не будет автоматически обновляться для изменений в данных "Имена пользователей". С помощью этого метода можно вносить изменения в лист "Данные пользователя", но если вы хотите следить за изменениями в "Имена пользователей", потребуются более продвинутые методы.
1
Vlookup и аналогичные будут работать только в том случае, если данные на обоих листах идентичны.
Я считаю, что вам нужно дополнение нечеткой логики для Excel. Это позволит вам найти похожие результаты на основе нескольких различных параметров. Проверьте страницу загрузки.
0
HLOOKUP/VLOOKUP - используйте формулу для извлечения имени пользователя из электронной таблицы 1, затем используйте имя пользователя в качестве ключа и электронную таблицу 2 в качестве матрицы поиска для одного экземпляра HLOOKUP/VLOOKUP (я не знаю, какой из них, как и я используя неанглийскую версию Excel).
0
Вы можете использовать Query from Excel Files:
- Определите имя для набора данных в электронной таблице 1 (вкладка "Формулы" -> Определить имя)
- Определите имя для набора данных в электронной таблице 2
- Находясь в электронной таблице 1, перейдите на вкладку "Данные", выберите "Из других источников" и в раскрывающемся списке выберите "Из запроса Microsoft".
- Выберите другой файл электронной таблицы и подтвердите, что вы хотите объединить столбцы вручную
- В следующем окне "Запрос из файлов Excel" перетащите столбец "имя пользователя" первого набора данных в столбец "имя пользователя" второго набора данных - будет создана связь между этими столбцами
- Зайдите в меню "Файл", нажмите "Вернуть данные в MS Office Excel", появится диалоговое окно "Импорт данных".
- Выберите лист, на который вы хотите импортировать сопоставленные данные
- Нажмите кнопку ОК - вы должны увидеть совпадающие данные со столбцами из обеих таблиц
Или, если вы не возражаете загрузить свои файлы в онлайн-сервис, вы можете использовать, например, http://www.gridoc.com/join-tables и объединять листы с помощью перетаскивания (Отказ от ответственности: я являюсь автором инструмента).
Надеюсь это поможет.