☰

Excel에서 두 셀을 참조하여 세 번째 셀을 찾을 수 있나요?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Excel에서 두 셀을 참조하여 세 번째 셀을 찾을 수 있나요?

상당한 양의 데이터가 포함된 스프레드시트가 있습니다. 해당 데이터 중 일부를 특정 셀로 반환해야 합니다. 반환해야 하는 데이터는 항상 "부착된 구성 요소"가 있는 셀 근처에 있습니다. 문제는 "Attached Components" 셀이 여러 개 있다는 것입니다. 예를 들어 "Part 1"과 "Part 2"라는 두 부분이 있고 두 부분 각각에는 상대적으로 서로 가까운 "Attached Components" 섹션이 있습니다. 해당 셀이 위치한 셀도 동일하게 유지되지 않습니다. 그렇지 않으면 해당 셀을 참조하면 됩니다. 다음은 한 부품에 대해 "부착된 구성요소" 근처의 데이터를 반환하기 위해 현재 사용하고 있는 공식입니다.

=IFNA(INDEX(L15:R46,MATCH("Attached Components",M15:M46,0)+2,3),"0")

요약하자면, "부착된 구성 요소"를 참조한 다음 "부품 #_"을 참조하는 셀에서 데이터를 반환하는 수식이 필요합니다.

다음은 "부착된 구성 요소"의 위치가 어떻게 변경될 수 있는지, 그리고 "부품 # 1"과 관련된 위치에 대한 샘플입니다.

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

이것은 매우 구체적인 문제이며 내 설명이 가장 명확하지 않다는 것을 알고 있습니다. 도움을 주셔서 감사드리며 더 자세한 내용을 문의해 주세요.

답변1

나는 다음과 같은 가정하에 작동하도록 노력했습니다.

  • "부착된 구성 요소"는 항상 같은 열에 있습니다.
  • 실제로 각 "재료 번호"에 대한 설명을 조회하려고 합니다.

그리고 저는 이 시트를 사용하여 다음 작업을 수행할 것입니다.

예

이것이 정확히 귀하에게 필요한 것이 아닐 수도 있지만 이에 대한 귀하의 의견을 바탕으로 답변을 개선해 볼 수 있습니다.

공식을 재사용하여 열에서 "부착된 구성 요소"가 어디에 있는지 식별한 다음 2를 추가하면 재료 설명이 시작되는 상대 행이 제공됩니다.

=MATCH("Attached Components",B1:B32,0)+2

결과는 예 "7"에 있습니다.

설명이 있는 마지막 행을 식별해야 합니다. 올바른 범위에서 검색하려면 "부착된 구성 요소"가 있는 행에 따라 수식을 변경해야 합니다. MATCH, ADDRESS, CONCATENATE의 조합은 범위를 다시 생성합니다.

MATCH는 상대 행을 제공하고, ADDRESS는 셀 이름( ADDRESS(1,1)="$A$1" )이 있는 문자열의 행 번호와 열 번호를 변환하고, CONCATENATE는 범위를 생성하기 위해 문자열을 함께 배치합니다.

CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))

그러면 "$C$7:$C$25"와 같은 문자열이 반환됩니다. 따라서 설명 열을 포함하고 값이 18행 더 낮은 행에서 시작됩니다. 더 많거나 적은 행을 포함하려면 수식에서 "+20"을 적절한 값으로 변경하면 됩니다.

마지막 행을 찾는 것은 IF와 MIN을 사용하여 첫 번째 빈 셀을 찾는 것입니다.

{=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
     )
)-1}

이 수식은 배열 수식입니다. 이것이 바로 주위에 괄호가 있는 이유입니다(괄호를 입력하지 마십시오. 수식을 입력한 다음 Ctrl+Shift+Enter를 누르면 나타납니다).

INDIRECT는 셀 참조에 내장한 문자열을 변환합니다. ROW는 결과적으로 행 번호를 제공합니다. MIN은 반환된 범위에서 가장 작은 값을 사용합니다. 끝에 있는 "-1"은 첫 번째 빈 행이 아닌 마지막 설명의 행 번호를 갖는 것입니다.

예제에서 이 수식은 "9"를 반환합니다.

이제 첫 번째 설명과 마지막 설명의 행 번호(7~9)가 있습니다. 필요한 작업을 수행하기 위해 ADDRESS, CONCATENATE 및 INDIRECT를 사용하여 원하는 방식으로 이러한 숫자를 결합할 수 있습니다. 하지만 이번에는 작업할 특정 셀 참조가 있습니다.

예를 들어 Material # 조회는 다음과 같습니다.

VLOOKUP 예

이 마지막 예에서는 셀에 다음이 포함됩니다.

E2:

=MATCH("Attached Components",B1:B32,0)+2

F2(Ctrl+Shift+Enter를 사용하여 입력):

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
    )
)-1

F7:

=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)

이렇게 하면 E7 셀에 재료 #를 입력하면 F7 셀에 설명이 표시됩니다.

편집하다:

의견에 따라 솔루션은 다음과 같이 해결될 수 있습니다.

더 복잡한 예를 사용하면 다음과 같습니다.

복잡한 예

행 일치는 2개의 MATCH 함수를 계단식으로 배열한 것입니다. 첫 번째 MATCH 함수를 사용하여 부품 번호를 찾은 다음 두 번째 함수를 사용하여 관심 있는 섹션을 찾습니다.

2 매칭

F3: 찾고 있는 부분의 문자열

F4: 첫 번째 열에서 "부품 번호"를 찾는 수식입니다.

=MATCH($F$3,A1:A32,0)

F6: 찾고 있는 섹션의 이름

F7 : 앞서 식별한 부품에서 단면을 찾는 수식. 일치는 "부품 번호" 행(F4 셀에 저장됨)에서 시작하는 범위에서 수행됩니다. 범위는 INDIRECT, CONCATENATE, ADDRESS를 사용하는 동일한 종류의 수식을 사용하여 작성됩니다. 그런 다음 MATCH에서 반환된 상대 행은 F4-1로 오프셋되어 절대 행 번호를 갖습니다.

=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1

이제 설명의 첫 번째 행과 마지막 행을 식별하기 위해 이전과 동일한 수식을 재사용할 수 있습니다.

설명의 첫 번째 및 마지막 행

F9: 첫 번째 설명 행을 얻으려면 "부착된 구성 요소" 행의 행 번호에 2를 추가합니다.

=F7+2

F10: 설명 범위에서 첫 번째 빈 행을 찾습니다(F9에 저장된 행에서 시작). 이것은 다음을 사용하여 입력해야 하는 배열 수식입니다.CTRL+SHIFT+ENTER

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
     )
)-1

그런 다음 설명을 표시하기 위해 INDIRECT 및 인덱스 열을 사용할 수 있습니다.

디스플레이 매트릭스

F15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")

G15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")

해당 공식은 재료 번호와 E 열의 색인으로 식별되는 행에 대한 설명을 표시합니다. IF 문은 마지막 행 아래에 있는 행을 표시하지 않도록 하는 것입니다. 이 예에서는 5개의 행만 표시되지만 첫 번째 행을 아래로 끌고 새 인덱스를 추가하여 더 많은 행을 추가하면 이 수식을 복사할 수 있습니다.

관련 정보