☰

두 워크시트에서 비슷한 데이터를 찾아 세 번째 워크시트에 넣습니다.

tag-icon • tag-icon microsoft-excel combine
두 워크시트에서 비슷한 데이터를 찾아 세 번째 워크시트에 넣습니다.

서로 다른 두 Excel 스프레드시트의 데이터를 세 번째 Excel 스프레드시트로 결합하는 가장 좋고 쉬운 방법은 무엇입니까? 스프레드시트 1에는 사용자 이름만 있습니다. 스프레드시트 2에는 이름, 성, 부서, 사용자 이름 등을 포함한 많은 정보가 있습니다. 세 번째 스프레드시트에는 스프레드시트 2의 이름, 성, 부서와 함께 스프레드시트 1의 사용자 이름을 표시하는 것이 필요합니다.

답변1

사용하려는 기능은 입니다 VLOOKUP. 이를 수행하는 방법은 시트 정렬 방법에 따라 약간 다르지만 모두 동일한 구문을 따릅니다.

=VLOOKUP( lookup value, table array, column index number, range lookup)

  • lookup value검색하려는 데이터입니다.
  • table array검색 대상이 포함된 열을 포함하여 데이터를 가져오려는 셀을 정의합니다.
  • column index numbertable array정보를 가져오려는 열의 인덱스입니다 . (예: A:E 배열의 경우 D열은 입니다 4.)
  • range lookup대략적인 일치가 허용되는지 또는 정확한 일치가 필요한지 여부를 지정하는 TRUE/FALSE 옵션입니다. 간단하게 하기 위해 저는 항상 이것을 FALSE. 자세한 내용이 필요하면 Excel에서 F1을 누르세요.

수식의 형식은 데이터가 모두 동일한 통합 문서에 있는지 여부에 따라 약간씩 달라집니다. 아래에 각각에 대한 예를 들어보겠습니다.

중요 사항:귀하가 사용하고 있는 검색어lookup value ~ 해야 하다table arrayVLOOKUP이 작동하려면 첫 번째 열에서 찾을 수 있습니다 .

첫 번째 예: 모든 데이터는 동일한 Excel 통합 문서에 있지만 다른 시트에 있습니다. 첫 번째 시트에는 "사용자 이름"이라는 라벨이 붙어 있으며 사용자 이름만 포함되어 있습니다. 두 번째 시트는 "사용자 데이터"라고 하며 모든 사용자 세부 정보를 포함합니다. 세 번째 시트를 "조회 결과"라고 부르겠습니다. "사용자 데이터" 시트에는 5개의 열이 있습니다 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)

지금쯤이면 여기서 패턴을 볼 수 있을 것입니다. 각 열에 대해 첫 번째 셀(예: B2)에 VLOOKUP 수식을 작성한 다음 시트의 나머지 부분에 수식을 채울 수 있어야 합니다. 그러나 수식을 잘라내어 붙여넣으면가로질러table array열은 그리 간단하지 않습니다. 및 값을 업데이트해야 합니다 column index number.

두 번째 예: 각 데이터 세트는 자체 Excel 통합 문서에 보관됩니다. 통합 문서 내의 시트 이름은 기본값입니다(예: 첫 번째 시트는 "Sheet1"입니다). 통합 문서 파일 이름은 "Usernames.xlsx", "User Data.xlsx" 및 "Lookup Results.xlsx"입니다. 이것들은 모두 "Me"라는 사용자의 데스크탑에 있는 "My Spreadsheets"라는 폴더에 있습니다.

  1. "User Data.xlsx" 시트의 A열에 모든 사용자 이름이 있는지 확인하세요.
  2. "Usernames.xlsx"의 모든 사용자 이름을 "Lookup Results.xlsx"로 복사합니다.
    • 다시 말하지만 헤더 행을 사용한다고 가정하면 A2에서 시작됩니다.
  3. "Lookup Result.xlsx"의 B2에 대한 수식은 다음과 같아야 합니다.=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  4. "Lookup Result.xlsx"의 B3에 대한 수식은 다음과 같아야 합니다.=VLOOKUP(A3,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  5. "Lookup Result.xlsx"의 C2에 대한 공식은 다음과 같아야 합니다.=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)

다시 말하지만, 지금쯤이면 여기서 패턴을 볼 수 있을 것입니다. 행과 열을 가로질러 필요에 따라 잘라내기/붙여넣기/조정하면 설정이 완료됩니다.

여기서 염두에 두어야 할 또 다른 사항은 이 시트가 "사용자 이름" 데이터의 변경 사항에 대해 자동으로 업데이트되지 않는다는 것입니다. 이 방법을 사용하면 "사용자 데이터" 시트에 대한 변경 사항을 가져올 수 있지만 "사용자 이름"의 변경 사항도 따라가려면 더 고급 기술이 필요합니다.

답변2

Vlookup 및 이와 유사한 기능은 두 시트의 데이터가 동일한 경우에만 작동합니다.
나는 당신에게 필요한 것은 Excel용 퍼지 논리 추가 기능이라고 생각합니다. 이를 통해 여러 가지 다른 매개변수를 기반으로 유사한 결과를 찾을 수 있습니다. 을 체크 해봐다운로드 페이지.

답변3

HLOOKUP/VLOOKUP - 수식을 사용하여 스프레드시트 1에서 사용자 이름을 가져온 다음 사용자 이름을 키로 사용하고 스프레드시트 2를 HLOOKUP/VLOOKUP의 한 인스턴스에 대한 조회 행렬로 사용합니다(제가 어느 것인지는 모르겠습니다. 영어가 아닌 Excel 버전 사용).

답변4

Excel 파일에서 쿼리를 사용할 수 있습니다.

  • 스프레드시트 1에서 데이터세트의 이름을 정의합니다(수식 탭 -> 이름 정의).
  • 스프레드시트 2에서 데이터세트의 이름을 정의합니다.
  • 스프레드시트 1에서 데이터 탭으로 이동하여 "기타 소스에서"를 선택하고 드롭다운에서 "Microsoft Query에서"를 선택합니다.
  • 다른 스프레드시트 파일을 선택하고 열을 수동으로 병합할지 확인하세요.
  • 다음 창 "Excel 파일에서 쿼리"에서 첫 번째 데이터세트의 '사용자 이름' 열을 두 번째 데이터세트의 '사용자 이름' 열로 끌어서 놓으면 이 열 간의 링크가 생성됩니다.
  • 파일 메뉴로 이동하여 "MS Office Excel로 데이터 반환"을 클릭하면 데이터 가져오기 대화 상자가 나타납니다.
  • 일치하는 데이터를 가져올 시트를 선택하세요.
  • 확인을 클릭하세요. 두 스프레드시트의 열과 일치하는 데이터가 표시됩니다.

관련 정보