Excel에서 sheet1의 데이터에서 sheet3의 데이터를 찾아야 합니다.

Excel에서 sheet1의 데이터에서 sheet3의 데이터를 찾아야 합니다.

여러 시트가 포함된 Excel 통합 문서가 있습니다. 열에 'SheetA'!W:W는 행의 열 머리글 내에서 찾을 수 있는 텍스트가 나열됩니다 'Sheet3'!1:1. 예를 들어 SheetA'!W42"B16"이 포함되어 있습니다. 셀 Sheet3'!CB1에는 헤더 텍스트 "B16: Sample 40"이 있으므로 대상 열이 됩니다.

예: 샘플'SheetA'!W42

여기에 이미지 설명을 입력하세요

에는 'SheetA'!CD42에 위치해야 하는 샘플 수를 계산하는 공식이 있습니다 'Sheet3'!A:A. 아래 샘플에서는 CD42에서 찾을 수 있는 계산된 값 "30"을 표시합니다 'Sheet3'!A32.

예: 열 머리글(B16)과 관심 샘플 수를 포함하는 행의 머리글과 교차점을 표시하는 Sheet3의 샘플 데이터:

여기에 이미지 설명을 입력하세요

검색된 열과 행이 교차하는 Sheet3의 셀 내용을 가져와 SheetA의 셀에 표시하는 것이 목표입니다. 제공된 샘플에서는 'Sheet3'!CB:CB교차 행 32와 sheetA에 "0.1950581843" 값이 표시됩니다.

이것이 어떻게 이루어질 수 있는지 아는 사람이 있습니까?

답변1

위의 데이터를 바탕으로 몇 가지 가정을하겠습니다. 이 모든 사항이 사실이라면 HLOOKUP문제를 쉽게 해결할 수 있습니다. 그렇지 않으면 더 복잡한 기능 체인이 필요할 수 있습니다.

가정:

  1. 모든 관련 헤더 텍스트는 다음과 'Sheet3'!1:1같습니다.정말 독특하다해당 행 내에서.
  2. 의 모든 관련 헤더 텍스트는 'Sheet3'!1:1"[X]: [Y]" 형식입니다.
    • [X]는 에서 찾을 수 있는 값입니다 'SheetA'!W:W.
    • 'SheetA'!X:X[Y] 는 [X]와 같은 행에 있는 값입니다 .
  3. 의 가능한 모든 값은 'SheetA'!CD:CD에서 검색 가능 'Sheet3'!A:A하므로 검색하려는 데이터와 동일한 행에 있습니다.
    • Sheet3에서 이러한 값이 일부 반복되는 것 같고 'SheetA'!CD:CD다른 열과 일치하도록 의도된 값을 검색할 수 있는 조건이 없는지 확인하고 싶었기 때문에 이것을 넣었습니다. 대신 (따라서 에서 일치하는 경우 부정확한 결과가 생성될 수 있음 'Sheet3'!A:A)
    • 반복이 엄격하게 읽기의 편의를 위한 것이라면 대신 "창 고정" 기능을 사용하는 것이 좋습니다.
  4. 의 모든 관련 값은 'Sheet3!A:A'완벽하게 순차적이며 의 "1"로 시작하고 'Sheet3'!A3해당 열 내에서 반복되지 않습니다.언제나오름차순으로 정렬되었습니다.

위의 가정을 바탕으로 시나리오를 대략적으로 나타내는 샘플 데이터로 시트를 직접 만들었습니다.

다음은 내 "Sheet3"의 부분 스크린샷입니다.

여기에 이미지 설명을 입력하세요

그리고 내 "SheetA"의 부분 샷입니다.

여기에 이미지 설명을 입력하세요

유사품 목록:

  • 시트3
    • 내 A:A = 귀하의 A:A, BY:BY:, CA:CA:, ...
    • 내 B:B, C:C, D:D, ... = 귀하의 B:B, BZ:BZ, CB:CB, ...
  • 시트A
    • 나의 A:A = 당신의 W:W
    • 나의 B:B = 당신의 X:X
    • 내 C:C = 귀하의 CD:CD
    • My D:D = 찾은 데이터를 드롭하려는 열입니다.

두 번째 스크린샷에서 볼 수 있듯이 D2의 공식은 다음과 같습니다.

=HLOOKUP(CONCATENATE(A2,": ",B2),Sheet3!A:Y,C2+2,FALSE)

수식의 단계별 연습:

HLOOKUP셀 범위를 통해 가로로 값을 찾은 다음 상대 행 위치를 기준으로 동일한 열의 셀 값을 반환할 수 있습니다. 4개의 인수가 필요하며 그 중 3개는 필수입니다.조회_값,table_array,row_index_num,[범위_조회]. 이는 Sheet3에서 원하는 데이터를 찾아 SheetA의 셀로 가져오는 작업을 수행하는 공식입니다.

  • 조회_값가장 먼저 찾고 싶은 값입니다 HLOOKUP. 이 값~ 해야 하다첫줄에 나타나다table_array, 이는 검색될 유일한 행이기 때문입니다. HLOOKUP또한 첫 번째 일치 항목만 반환하므로 이러한 값도 고유해야 합니다. 여기서는 CONCATENATE검색 문자열을 작성하는 데 사용하고 있습니다 .
    • 사슬 같이 잇다다양한 문자열과 값을 하나의 문자열로 합칠 수 있습니다. 결과 문자열에 배치되어야 하는 순서에 따라 정렬된 일련의 인수를 허용합니다.
      • A2에 대한 첫 번째 주장은 입니다 CONCATENATE. 문자열의 첫 번째 부분은 A2의 "샘플 이름"입니다.
      • ": "에 대한 두 번째 인수입니다 CONCATENATE. 의 값 형식과 일치하도록 콜론과 공백을 문자열에 넣습니다 'Sheet3'!1:1.
      • 지하 2층에 대한 우리의 마지막 주장입니다 CONCATENATE. .NET의 헤더에 사용되는 구문을 완성하기 위해 B2에서 "샘플 ID"를 가져옵니다 'Sheet3'!1:1.
  • table_array작업 하려는 셀 범위에 대한 참조입니다 HLOOKUP. 첫 번째 행은 다음을 포함해야 한다는 점을 기억하세요.조회_값. 이 범위는 또한 다음에 대한 가능한 모든 값을 포함해야 합니다.row_index_num.
    • 시트3!A:YSheet3의 A열부터 Y열(내 Sheet3에 채워진 유일한 셀)에 있는 모든 셀에 대한 참조입니다. 이렇게 하면 나중에 새 행에 추가된 모든 데이터도 검색 범위에 포함됩니다. 데이터가 행 대신 새 열에 추가되는 경우 참조를 사용하고 싶습니다 Sheet3!1:32(현재 32는 내 Sheet3에 채워지는 마지막 행입니다). 새 열로 데이터가 추가될 수 있는 경우그리고새 행을 사용하려면 전체 시트를 Sheet3!1:1048576또는 으로 참조합니다 Sheet3!A:XFD. (참고: "전체 시트" 참조는 Excel 2013에 유효합니다. 이전 버전에서는 행/열 제한이 더 작을 수 있습니다. 1048576또는 XFD적절하게 조정하세요.)
  • row_index_num은(는) 최상위 행을 기준으로 한 행 위치를 나타내는 양의 정수입니다.table_array. 이는 HLOOKUP일치하는 열에서 어떤 셀을 반환할지 알려줍니다. HLOOKUP검색만 가능 하므로 참고하세요.조회_값맨 윗줄에table_array, 그리고row_index_num음수일 수 없으며 위의 셀에서 정보를 반환하는 데 사용할 수 없습니다 HLOOKUP(적어도 단독으로 사용 가능).조회_값.
    • C2+2- 의 모든 값은 건너뛴 정수 없이 완벽하게 순차적이며 항상 오름차순으로 정렬되므로 해당 값( 에도 표시됨 )을 찾으려는 데이터의 행 번호 표시기로 'Sheet3'!A:A사용할 수 있습니다 . 'SheetA'!C:C이는 +2Sheet3의 3행에서 번호 매기기가 "1"로 시작한다는 사실을 설명하기 위한 것입니다.
  • [범위_조회]에 대한 선택적 인수입니다 HLOOKUP. 옵션은 TRUE 또는 FALSE이며 대략적인 일치가 유효한지 허용할지 또는 정확한 일치만 허용할지 여부를 나타냅니다. 이 인수를 생략하면 Excel의 기본값은 TRUE(대략 일치)로 설정됩니다. 이로 인해 바람직하지 않은 동작이 발생할 수 있습니다. 특히 시트가 특정 방식으로 정렬되지 않은 경우 더욱 그렇습니다. 그래서 우리는 지정합니다거짓HLOOKUP정확히 일치하는 항목만 선택하는지 확인하려면 여기를 클릭하세요 .

위의 내용을 시트 레이아웃에 적용하면 이것이 셀에 필요한 공식이라고 생각합니다 'SheetA'!CE42(데이터를 넣을 위치가 셀이라고 가정).

=HLOOKUP(CONCATENATE(W42,": ",X42),Sheet3!A:CB,CD42+2,FALSE)

Sheet3의 데이터가 CB 열보다 오른쪽으로 더 이동하거나 데이터가 추가 열에 추가될 수 있는 경우 조정해야 합니다.table_array따라서.

관련 정보