다음과 같은 수식으로 채우려는 행이 있습니다.
=SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)
=SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48)
=SUMPRODUCT(D91:D94,D44:D47)/SUM(D44:D47)
하지만 수식을 드래그하면 색인의 알파벳 부분만 변경됩니다. Excel에서 배열 인덱스를 올바르게 변경하는 방법이 있습니까?
답변1
제가 이해한 바에 따르면, 수식이 포함된 셀이 있고 =SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)다음 열의 셀에 수식이 포함되도록 이를 올바르게 채우고 싶습니다 =SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48). 즉, 평소와 같이 열이 1씩 증가하고 행도 1씩 감소하기를 원합니다.
이 기능을 사용하면 이를 달성할 수 있습니다 OFFSET. 이를 통해 알려진 범위에서 지정된 거리에 있는 범위를 구성할 수 있습니다. 이 COLUMN함수를 사용하여 원하는 오프셋의 양을 지정할 수 있습니다.
우리가 원하는 범위는 첫 번째 열 이후의 각 열에 대해 -1 행과 +1 열만큼 오프셋된 B93:B96및 입니다. B46:B49따라서 OFFSET(B93:B96, -1, 1)에 대한 참조를 반환합니다 C92:C95. 그러나 참조는 자연스럽게 열을 업데이트하므로 열 오프셋을 생략할 수 있습니다.
원래 수식에 있는 범위의 경우 B93:B96첫 번째 셀에서 원하는 것, OFFSET(B93:B96, 0, 0)두 번째 셀에서 원하는 것 OFFSET(C93:C96, -1, 0), 세 번째 셀에서 원하는 것 OFFSET(D93:D96, -2, 0)등입니다. 현재 열의 번호인 결과를 얻는 데 사용할 수 있습니다 =COLUMN(A1)(참조가 두 번째 열의 B1로 업데이트되기 때문입니다). 이들을 결합하여
OFFSET(B93:B96, 1 - COLUMN(A1), 0)
참조를 변경하여 단순화할 수 있는 것을 사용할 수 있습니다 .
OFFSET(B94:B97, -COLUMN(A1), 0)
첫 번째 셀에서 this는 OFFSET(B94:B97, -1, 0)which is 로 변환됩니다 B93:B96. 두 번째 셀에서 수식은 다음과 같이 채워 OFFSET(C94:C97, -COLUMN(B1), 0)집니다 C92:C95.
따라서 우리에게 필요한 두 가지 참조는 OFFSET(B94:B97, -COLUMN(A1), 0)및 입니다 OFFSET(B47:B50, -COLUMN(A1), 0). 따라서 공식은 다음과 같습니다.
=SUMPRODUCT(OFFSET(B94:B97, -COLUMN(A1), 0), OFFSET(B47:B50, -COLUMN(A1), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
마지막 합병증이 하나 있습니다. Excel 2007에서 이것을 테스트할 때 SUMPRODUCT수식 내부에서 COLUMN(A1)다음을 반환하는 것을 발견했습니다.정렬값 1이 아니라 값 1을 포함합니다. (수식 평가 대화 상자에서 이를 볼 수 있습니다. COLUMN(A1)결과를 평가할 때 결과는 {1}가 아닌 1이고 표현식 SUMPRODUCT은 두 개의 #VALUE!인수를 가져오고 결과는 0이 됩니다.) 버전에서는 이런 일이 발생하지 않을 수도 있습니다. 그렇다면 다음과 같이 COLUMN(A1)내부를 a 로 감싸십시오 SUM().
=SUMPRODUCT(OFFSET(B94:B97, -SUM(COLUMN(A1)), 0), OFFSET(B47:B50, -SUM(COLUMN(A1)), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
내가 최종본 COLUMN(A1)을 SUM(); 이는 배열 참조가 아닌 평소와 같이 숫자를 반환했기 때문입니다. 함수가 배열 참조를 허용하지 않기 때문에 Excel에서는 이 경우 SUM배열을 원하지 않는다는 것을 깨달았다 고 가정합니다 .COLUMN