동일한 수식을 복사하면서 테이블 배열 행을 15씩 늘릴 수 있는 솔루션을 찾는 데 어려움을 겪고 있습니다. 첫 번째 시도에서는 수동으로 세 가지 적절한 수식을 만든 다음 전체 수식을 작성하려고 했습니다. 패턴을 잡았지만 운이 없었습니다.
다음과 같은 출력을 찾고 있습니다.
=MAX(VLOOKUP($A2,Sheet2!$A2:$B17,2,0),VLOOKUP($A2,Sheet2!$E2:$F17,2,0),VLOOKUP($A2,Sheet2!$I2:$J17,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A17:$B32,2,0),VLOOKUP($A2,Sheet2!$E17:$F32,2,0),VLOOKUP($A2,Sheet2!$I17:$J32,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A32:$B47,2,0),VLOOKUP($A2,Sheet2!$E32:$F47,2,0),VLOOKUP($A2,Sheet2!$I32:$J47,2,0))
나는 꽤 아마추어이므로 어떤 조언이라도 환영합니다. 미리 감사드립니다.
답변1
OFFSET대부분의 사람들이 이 옵션에 끌리는 기능이 될 것입니다. 그러나 이는 휘발성 기능입니다. 휘발성 기능은 세상의 끝이 아닙니다. 이는 해당 셀이 휘발성 수식과 관련이 없더라도 시트의 셀이 변경될 때마다 다시 계산한다는 의미입니다. 일반 수식은 영향을 미치는 사항이 변경된 경우에만 다시 계산됩니다. 결과적으로 휘발성 함수를 사용하면 많은 과잉 계산을 유도할 수 있습니다. 특히 그것이 복사되어 많은 셀에서 재사용되는 경우에는 더욱 그렇습니다.
정규식(비휘발성)을 사용하여 동일한 결과를 OFFSET얻을 수 있습니다 . 주어진 행(1D 범위)과 열(2D 범위)의 값을 반환한다고 가정하는 경우가 많습니다. 실제로 셀 주소로 반환되는 내용 은 해당 주소의 값 형식을 가져옵니다. 이 주소 반환을 사용하면 한 함수를 사용하여 범위의 시작점을 정의한 다음 다른 함수를 사용하여 범위의 끝을 정의할 수 있습니다. 두 함수를 :로 구분하면 이제 범위가 생깁니다!INDEXINDEXINDEXINDEXINDEXINDEX
이제 행 패턴을 결정하는 수학을 살펴보겠습니다. 수식 1은 행 2에서 시작하고, 수식 2는 행 17에서 시작하고, 수식 3은 행 32에서 시작합니다.... 등등. 따라서 여기의 패턴은 본질적으로 (공식 #-1)*15+2입니다. 이제 수식이 복사될 때마다 1씩 증가하는 카운터를 개발하면 됩니다. 1, 2, 3 등으로 열을 채울 수도 있고 대신 ROW(A1). 어디에서나 처음 사용되면 1을 반환합니다. 복사할 때 2, 3 등을 반환합니다. 완벽한 작은 카운터입니다. (참고: Column(A1)수평으로 계산하는 데 사용될 수 있습니다).
앞에서 설명한 대로 다음과 같은 INDEX형식을 취합니다.
INDEX(SELECT RANGE, ROW in SELECT RANGE, COLUMN in SELECT RANGE)
몇 가지 메모:
행 및 열 번호는 선택한 범위를 기준으로 하며 선택 범위가 2D 범위의 경우 A1에서 시작하거나 1D 범위의 경우 행 1 또는 열 A에서 시작하지 않는 한 워크시트와 동일하지 않습니다.
1D 선택 범위에서는 열 번호가 필요하지 않으며 행 번호만 필요합니다. 선택한 범위가 가로인 경우 행 번호는 실제로 열 번호입니다.
행 또는 열 번호에 0을 입력하면 INDEX는 이를 선택한 범위의 입력 행 또는 열을 반환하는 것으로 간주합니다.
다시 공식을 구축하는 과정으로 돌아가 보겠습니다. 범위의 시작점을 찾는 것부터 시작해 보겠습니다. 따라서 이 경우 우리는 인덱스에게 A2, A17, A32 등을 찾도록 지시하고 싶습니다.
=INDEX($A:$A,(ROW(A1)-1)*15+2)
B17, B32, B47 범위의 끝점을 찾으려면 공식은 다음과 같습니다.
=INDEX($B:$B,ROW(A1)*15+2)
이제 두 가지를 결합하여 범위를 정의하면 공식은 다음과 같습니다.
=INDEX($A:$A,(ROW(A1)-1)*15+2):INDEX($B:$B,ROW(A1)*15+2)
이제 단일 셀에 한 셀의 값 이상을 넣을 수 없기 때문에 수식 자체는 그다지 좋아 보이지 않습니다. 그러나 조회 수식에는 작동합니다. 따라서 범위 방정식을 원래 공식에 대체하면 다음과 같은 결과를 얻을 수 있습니다.
=MAX(VLOOKUP($A2,INDEX(SHEET2!A:A,(ROW(A1)-1)*15+2):INDEX(SHEET2!B:B,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!E:E,(ROW(A1)-1)*15+2):INDEX(SHEET2!F:F,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!I:I,(ROW(A1)-1)*15+2):INDEX(SHEET2!J:J,ROW(A1)*15+2),2,0))