[Microsoft Office 365/Excel]이름 정의에서 동적 범위 설정
특정 셀 범위를 지정하고 [수식] - [정의된 이름]-[이름 정의]를 클릭하면 "새 이름" 대화상자가 나타난다. 여기서 이름을 임의로 설정하고 확인하고 나서부터는 특정 셀 범위를 일일이 선택하지 않고 설정한 이름만 입력하면 자동으로 참조할 수가 있다.
하지만 앞에서 언급한 방식의 이름 정의는 초기에 설정한 셀 범위만 적용 대상이라서 이후에 데이터를 추가로 입력하거나 삭제할 때는 이러한 점이 반영이 되질 않아서 [이름 관리자]에 들어가서 편집을 해야한다. 변경된 셀이 얼마 되지 않으면 변경될 때마다 편집을 해줘도 크게 지장은 없으나 변경되는 셀이 많아질수록 실질적으로 일일이 다시 범위를 지정해주는 작업을 하기란 불가능에 가깝다.
그래서 이러한 점을 극복하기 위해 이름 정의 시 OFFSET()함수를 이용해서 동적 범위를 설정하는 방법이 있다. 동적 범위를 설정하면 새로 데이터를 추가 또는 삭제하는 셀을 자동으로 인식하여 확장 및 축소를 하게 해준다.
예제
여기서 OFFSET() 함수는 셀 또는 셀 범위의 시작점에서 지정한 행 및 열의 거리만큼 이동한 위치의 셀 또는 범위를 반환하는 함수다.
구문
OFFSET(reference, rows, cols, [height], [width])
OFFSET (셀 또는 셀 범위, 행(입력한 만큼 이동), 열(행과 동일), [높이], [너비])
※ 여기서 선택 요소인 높이 및 너비는 각각 반환되는 참조의 높이(행) 및 너비(열)를 설정하는 것이다.
OFFSET 함수
이 문서에서는 Microsoft Excel의 OFFSET 함수에 사용되는 수식 구문과 이 함수를 사용하는 방법을 설명합니다. 설명 셀 또는 셀 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환합
support.microsoft.com
그래서 3번 사진의 수식[=OFFSET($B$6,0,0,COUNTA($B:$B)-1,5)]을 분석하자면 일단 시작점(기준점)은 B6이고 행과 열의 이동값(0)은 없다.
그리고 높이 인수에서 COUNTA() 함수는 비어 있지 않은 셀의 갯수를 구하는 함수인데 COUNTA($B:$B)-1를 입력하면 모든 B열에서 비어있지 않은 셀의 갯수를 출력하게 된다. 그리고 '-1'을 추가한 이유는 첫 행에 있는 머리글을 제외하기 위해 입력한 것이다.
마지막으로 너비에는 B ~F열까지 범위로 설정하기 위해 인수로 '5'를 입력한다.
