[엑셀] OFFSET 함수로 1개의 행 데이터를 동적 범위로 참조하는 방법

 엑셀을 하면서 어떤 범위를 참조하는 수식을 만든 다음에 셀이 추가되거나 삭제되면서 수식이 원하는 값을 가져오지 못하는 경우가 자주 있습니다.

 

 이렇게 기존에 참고하고 있는 범위 내에서 편집이 이루어지더라도 참조 범위가 자동으로 알맞게 조정되게 하려면 OFFSET 함수를 사용하면 됩니다. 참고로 '동적범위'라고 하면은 참조하는 범위가 고정되어 있지 않고 자동으로 참조 범위가 조정되는 것을 말합니다.

 

 OFFSET 함수의 기초 사용 방법은 아래 글을 참고해 주시면 되겠습니다.

[엑셀] OFFSET 함수의 기초 사용 방법

 

 OFFSET 함수를 사용한 동적범위 설정의 필요성에 대해서 먼저 설명드리겠습니다.

 

 

 B열부터 G열까지 월별 실적이 있고 B12셀부터 B17셀까지 B열부터 G열까지의 합계를 구하는 수식이 입력되어 있는 간단한 구조입니다. 여기서 7월 데이터를 입력하기 위해 H열에 데이터를 추가하겠습니다.

 

 

 H열이 추가되었지만 B12셀의 수식을 보면 여전히 '=SUM(B4:G4)'이므로 추가된 열의 셀을 포함하지 않습니다. 이렇게 중간에 데이터의 구조가 바뀌었을 때 수식이 원하는 대로 범위를 참조하게 하려면, 즉 위와 같은 상황에서 열을 추가해도 자동으로 B열부터 H열까지의 합계를 구하도록 수식이 자동으로 바뀌게 하려면 OFFSET 함수를 사용하여야 합니다.

 

 물론 G열을 복사해서 붙여넣은 다음 기존에 있던 데이터를 삭제하거나, 아니면 열을 삽입한 다음 수식을 바꿔도 되지만 데이터의 양이 많다면 처음부터 동적범위로 설정하는 것이 편리합니다.

 

 OFFSET 함수를 사용하여 1개의 행을 동적범위를 참조하기 위하여 삽입한 H열을 다시 삭제한 다음 진행하겠습니다.

 

 

 B12셀에 '=SUM(OFFSET(B4,0,0,1,6))'을 입력합니다. 동적범위를 참조하려면 먼저 이 수식을 이해할 수 있어야 합니다.

 

 합계의 대상이 되는 'OFFSET(B4,0,0,1,6)' 부분을 보겠습니다.

 

 첫 번째 인수 'B4'는 기준점이 되는 위치입니다. 4행에서 합계를 구하려는 첫 번째 데이터 위치입니다.

 

 두 번째 인수 '0'은 기준점에서 행방향으로 얼마나 이동할지 정하는 인수입니다. B4의 기준점에서 이동하지 않아야 4행의 데이터 합계를 구할 수 있기 때문에 '0'을 입력합니다.

 

 세 번째 인수 '0'은 기준점에서 열 방향으로 얼마나 이동할지 정하는 인수입니다. 1월 데이터인 B4셀부터 합계를 구해야 하기 때문에 열 방향으로도 이동하지 않습니다.

 

 네 번째 인수는 '1'입니다. OFFSET 함수의 네 번째 인수를 1~3번째 인수에서 정한 기준점에서 행의 크기가 얼마인 배열을 만들지 정하는 인수입니다. B4셀을 기준으로 1개의 행을 만들기 때문에 '1'을 입력합니다.

 

 다섯 번째 인수는 '6'입니다. OFFSET 함수의 다섯 번째 인수를 1~3번째 인수에서 정한 기준점에서 열의 크기가 얼마인 배열을 만들지 정하는 인수입니다. B4셀을 기준으로 열의 크기가 6인, 즉 G4셀까지 포함하기 위해 6을 입력합니다.

 

 즉 'OFFSET(B4,0,0,1,6)'은 B4:G4를 나타내므로 '=SUM(OFFSET(B4,0,0,1,6))'의 값은 B4셀부터 G4셀까지의 합계인 '390'이 나타납니다.

 

 그런데 수식을 이렇게 입력하면 참조하는 범위가 동적으로 변하지 않습니다. OFFSET 함수의 각각의 인수가 상수로 입력되어 있기 때문입니다. H열을 추가하여 값을 입력해 보겠습니다.

 

 

 H열을 추가하여 값을 입력해도 B12셀의 값은 변하지 않습니다. 여전히 'OFFSET(B4,0,0,1,6)'은 B4:G4의 범위를 참조하기 때문입니다.

 

 그러면 여기서 생각해 볼 것이 있습니다. 위와 같이 1개의 행 데이터를 참조하는 상황에서 참조하는 범위가 동적으로 변하기 위해서는 수식의 어떤 부분이 중요할까요? 바로 OFFSET 함수의 다섯 번째 인수입니다.

 

 참조하는 범위의 시작점이 B4셀인 것과 1개의 행인 범위를 참조하는 것이므로 1~4번째 인수는 변함이 없습니다. 다만 열이 추가되었을 때 어떠한 방법을 사용하여 OFFSET 함수의 다섯 번째 인수가 자동으로 바뀌도록 한다면 참조하는 범위가 동적으로 바뀔 수 있습니다.

 

 다섯 번째 인수를 어떻게 바꿀 것인가에 대해서는 여러 가지 방법이 있겠지만 가장 간단한 것은 'COUNTA' 함수를 사용하는 것입니다. 'COUNTA' 함수는 범위 내에서 비어 있지 않은 셀의 개수를 구하는 것입니다. 

 

 지금은 4행의 데이터 합계를 구하는 것이므로 OFFSET 함수의 다섯 번째 인수를 'COUNTA(4:4)'로 입력하면 참조 범위가 동적으로 바뀔 수 있습니다.

 

 그런데 위와 같은 상황에서는 주의할 것이 있습니다. 4행에서 합계를 구할 범위는 B4셀부터 H4셀까지로 총 7개의 셀입니다. 하지만 'COUNTA(4:4)'의 값은 4행에서 비어 있지 않은 셀의 개수이므로 총 '8'개입니다. 왜냐하면 지점명이 입력된 A열이 포함되었기 때문입니다. 

 

 그러므로 OFFSET 함수의 다섯 번째 인수를 'COUNTA(4:4) - 1'로 입력한다면 열이 추가되더라도 항상 원하는 범위의 값만 참조할 수 있습니다.

 

 

 B12셀의 수식을 '=SUM(OFFSET(B4,0,0,1,COUNTA(4:4)-1))'로 수정하자 B4셀부터 H4셀의 합계값이 나타납니다. 나머지 H열에 값을 입력하고 B12셀의 수식을 드래그하여 B17셀까지 채워보겠습니다.

 

 

 각 지점의 행에 있는 데이터의 합계가 나타납니다. 이렇게 수식이 완성된 상태에서 H열 다음에 열을 추가하여 데이터를 입력해 보겠습니다.

 

 

 열이 추가되면 수식을 수정하지 않아도 자동으로 범위가 확장되어 합계를 구할 수 있습니다.