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

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

 

 지난 글에서 OFFSET 함수로 1개의 행 데이터를 동적 범위로 참조하는 방법에 대해 설명드렸습니다. 어떤 범위를 참조할 때 데이터가 입력된 범위나 표의 구조가 변경되어도 올바른 범위를 참조하도로 하기 위해서는 OFFSET 함수를 사용하여 참조하는 범위가 동적으로 변하도록 하는 것이 좋습니다.

 

 행 방향(세로 방향)으로 값을 하나씩 추가하는 구조의 데이터 범위가 있을 때 값이 추가되거나 삭제될 때 자동으로 참조 범위가 동적으로 변하게 할 수 있습니다. OFFSET 함수에 대해 잘 모르시는 분들은 OFFSET 함수의 기초에 관해 설명한 아래의 글을 참고해 주시기 바랍니다.

 

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

 

 

 예시를 보면 D2셀에 B열의 주문량 합계를 구하는 수식인 '=SUM(B2:B12)'가 입력되어 있습니다. 이렇게 수식이 입력된 상태에서 13행에 데이터를 추가하겠습니다.

 

 

 당연한 결과지만 D2셀의 수식이 '=SUM(B2:B12)'로 고정되어 있으므로 새롭게 추가된 13행의 값을 반영하지 못합니다. 이렇게 참조하는 범위가 고정되어 있으면 데이터를 추가할 때마다 수식을 수정해야 합니다. 물론 데이터의 양이 적다면 큰 문제가 되지 않겠지만 다뤄야 할 데이터가 방대해질수록 참조 범위가 동적으로 변하도록 설정하는 것이 편리합니다.

 

 이제 OFFSET 함수를 사용하여 참조 범위가 동적으로 변하도록 D2셀의 수식을 바꿔보겠습니다.

 

 

 먼저 D2셀의 수식을 '=SUM(OFFSET(B2,0,0,12,1))'로 수정했습니다. 여기서 'OFFSET(B2,0,0,12,1)'가 의미하는 것을 알아야 합니다.

 

 OFFSET 함수의 첫 번째 인수 'B2'는 참조할 범위의 기준점이 되는 위치입니다. B2셀부터 아래로 추가되는 셀을 모두 더할 것이기 때문에 기준이 되는 위치는 B2셀입니다.

 

 두 번째 인수 '0'은 첫 번째로 입력한 인수인 B2셀에서 행 방향으로 얼마나 이동할 것인지 정하는 인수입니다. 행방향으로 이동하지 않고 B2셀부터 합계를 구할 것이기 때문에 '0'으로 입력합니다.

 

 세 번째 인수 '0'은 첫 번째로 입력한 인수인 B2셀에서 열 방향으로 얼마나 이동할 것인지 정하는 인수입니다. 마찬가지로 열 방향으로도 이동하지 않고 B2셀부터 합계를 구할 것이기 때문에 '0'으로 입력합니다.

 

 네 번째 인수 '12'는 1~3번째에서 정한 기준점이 되는 위치(B2셀에서 행방향으로 0칸, 열 방향으로 0칸이므로 기준점은 B2셀임)로부터 행방향으로 크기가 얼마나 되는 범위를 참조할 것인지 정하는 인수입니다. B2셀부터 B13셀까지 총 12개의 셀을 참조할 것이기 때문에 '12'로 입력합니다.

 

 다섯 번째 인수 '1'은 기준점이 되는 위치로부터 열 방향으로 크기가 얼마나 되는 범위를 참조할 것인지 정하는 인수입니다. 합계를 구하려는 범위는 B열 1개이므로 '1'을 입력합니다.

 

 이렇게 수식을 입력하면 B2셀에서 B13셀까지의 합계를 구할 수 있습니다. 이 상태에서 14행에 데이터를 추가하겠습니다.

 

 

 데이터를 추가했지만 D2셀의 값은 변하지 않습니다. OFFSET 함수의 인수가 상수로 입력되어 참조하는 범위가 고정되었기 때문입니다.

 

 그러면 행 방향으로 데이터가 추가되는 구조에서 참조하는 범위가 동적으로 변하게 하려면 어떻게 해야 할까요? 바로 행방향으로 참조하는 범위의 크기를 정하는 OFFSET 함수의 네 번째 인수가 자동으로 바뀌게 설정하면 됩니다.

 

 지난 글에서 COUNTA 함수를 사용하였듯이 이번에도 OFFSET 함수의 네 번째 인수를 COUNTA 함수를 사용하여 변경하겠습니다.

 

 

 다른 인수들은 그대로 두고 OFFSET 함수의 네 번째 인수만 'COUNTA(B:B)-1'로 변경하였습니다.

 

 COUNTA 함수는 범위에서 비어 있지 않은 셀의 개수를 구하는 함수입니다. 즉 'COUNTA(B:B)-1'의 의미는 B열에서 데이터가 입력된 셀의 개수를 구하고 거기에서 1을 뺀 값입니다. 1을 뺀 이유는 B열에 머리글인 B1셀이 포함되어 있으며, 이 셀은 합계의 대상이 아니기 때문입니다.

 

 이렇게 OFFSET 함수의 네 번째 인수를 'COUNTA(B:B)-1'로 수정하면 B2셀로부터 B열에 데이터가 입력된 셀의 개수(B1셀은 제외)만큼 아래 방향으로 참조 범위가 자동으로 확장되기 때문에 B열에 데이터를 추가하거나 삭제해도 자동으로 합계 대상의 참조 범위가 자동으로 변경됩니다.

 

 수식이 수정된 상태에서 15행에 데이터를 추가해 보겠습니다.

 

 

 데이터만 추가하면 수식을 따로 수정하지 않아도 합계 범위가 자동으로 변경됩니다.