Tim에는 B1 셀에 수식 = SUM (A1 : A7)이 포함 된 워크 시트가 있습니다.

그는이 수식을 아래로 복사하고 범위를 7 행씩 늘려서 B2 셀에 = SUM (A8 : A14) 수식을 포함하고 B3 셀에 = SUM (A15 : A21) 등을 포함하려고합니다. 문제는 그가 그것을 복사 할 때, 범위의 각 “끝”은 그의 필요를 충족시키기 위해 7 씩 증가되어야하는 1 씩만 증가합니다. 그는 Excel에서 적절한 증분을 수행하는 방법을 궁금해합니다.

Excel에서 복사 및 붙여 넣기를 사용하여 적절한 증가를 수행하도록 할 수 없습니다. 그것은 단지 그것을하지 않을 것입니다. 이유는 간단합니다. 공식적인 관점에서 1 씩 증가하는 것이 합리적 일 때가 있습니다. 엑셀은 당신의 마음을 읽을 수 없기 때문에 (적어도 다음 버전까지 :>)), 1 씩만 증가해야한다고 가정합니다.

해결책은 공식을 변경하는 것입니다. 몇 가지 워크 시트 함수를 사용하면 Excel에서 원하는 합계 범위를 구성 할 수 있습니다. 원하는 합계를 제공 할 수식의 다음 예를 고려하십시오.

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

이 수식을 셀 B1에 넣으면 수식이 포함 된 행의 행 번호 (ROW 함수에 의해 반환 됨)를 살펴보기 때문에 작동합니다. 1 행에 있으므로 수식은 Excel에서 다음과 같은 방식으로 평가됩니다.

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

=SUM(INDIRECT("A" & (1-1)7+1 & ":A" & (1-1)7+7))

=SUM(INDIRECT("A" & 07+1 & ":A" & 07+7))

=SUM(INDIRECT("A" & 0+1 & ":A" & 0+7))

=SUM(INDIRECT("A" & 1 & ":A" & 7))

=SUM(INDIRECT("A1:A7"))

=SUM(A1:A7)

B1에서 끝나는 것은 원하는 합계입니다. (INDIRECT 함수는 문자열의 값을 원하는대로 실제 범위 인 것처럼 사용합니다.) 수식을 열 아래로 복사 할 때 행 번호가 증가함에 따라 수식은 양쪽 끝에 7의 적절한 증분을 제공합니다. 범위의.

이 기술에는 사용할 수있는 다른 변형이 있습니다. 유일한 차이점은 변형이 서로 다른 워크 시트 함수를 사용하여 동일한 작업을 수행한다는 것입니다. 예를 들어, 다음 변형은 여전히 ​​ROW 함수를 사용하지만 궁극적으로 OFFSET 함수에 의존하여 원하는 범위를 계산합니다.

=SUM(OFFSET(A1,((ROW()-1)6),0):OFFSET(A7,((ROW()-1)6),0))

OFFSET을 사용하는 더 짧은 접근 방식은 다음과 같습니다.

=SUM(OFFSET($A$1,ROW()*7-7,0,7,1))

접근 방식에 관계없이 실제로 원하는 범위를 구성하기 위해 수식이 나타나는 행을 사용하는 수식을 만드는 것이 아이디어라고 말할 수 있습니다. 지금까지의 각 예제에서는 B1 셀에서 시작한다고 가정합니다. B2 셀에서 시작하려면 시작하는 행을 설명하도록 수식을 수정해야합니다. 이것이 작동하는 방식에 대한 아이디어를 제공하기 위해 B2 셀에서 시작하는 경우 대신이 팁에 제시된 세 가지 수식이 다음과 같은 방식으로 수정됩니다.

=SUM(INDIRECT("A" & (ROW()-2)7+2 & ":A" & (ROW()-2)7+8))

=SUM(OFFSET(A2,((ROW()-2)6),0):OFFSET(A8,((ROW()-2)6),0))

=SUM(OFFSET($A$2,(ROW()-1)*7-7,0,7,1))

다른 위치에서 시작하고 사용하기로 선택한 수식을 추가로 수정해야합니다.

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (8387)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다. 여기에서 Excel의 이전 메뉴 인터페이스에 대한이 팁 버전을 찾을 수 있습니다.

link : / excel-Incrementing_References_by_Multiples_when_Copying_Formulas [수식 복사시 배수로 참조 증가].