엑셀 이름 참조 대상 동적 범위 설정 방법
엑셀에서 이름을 설정할 때 기본적인 방법으로 범위를 선택하게 되면 나중에 값이 추가됐을 때 이름 설정에서 범위를 다시 수정해야 하는 번거로움이 생긴다.
때문에 이름이 설정된 행이나 열에 값이 추가되면 자동으로 해당 값이 이름에 포함되도록 동적 범위를 설정해 두면 편한데 동적 범위는 함수를 조합한 수식으로 만들 수 있다.
목차 (Contents)
엑셀 동적 범위 설정 하기
1. 이름 관리자 실행.
엑셀 상단의 리본 메뉴에서 [수식 -> 이름 관리자]를 클릭하여 실행하자.
(단축키 [Ctrl + F3]을 눌러도 된다.)
2. 이름 만들기.
이름 관리자 창이 나타나면 [새로 만들기]를 클릭.
(기존에 만들어둔 이름이 있다면 이를 더블 클릭하면 된다.)
새 이름 창이 나타나면 사용할 이름을 입력하고 참조 대상을 입력하자.
일반적으로 참조 대상은 이름으로 설정할 셀의 범위를 선택하지만 범위가 동적 변경되도록 하고 싶다면 OFFSET 함수와 COUNTA 함수를 조합하여 만든 수식을 넣으면 된다.
참조 대상의 수식은 열이 기준인지, 행이 기준인지에 따라 수식에서 한쪽의 값을 삭제 할 수 있으며 행이나 열의 범위를 전체로 지정하지 않고 직접 지정한 경우 -1 부분은 삭제해야 한다.
| - 동적 열 범위 =OFFSET(시작셀,0,0,COUNTA($열:$열)-1) - 동적 행 범위 (수식을 잘 보면 중간에 콤마가 하나 더 있다.) =OFFSET(시작셀,0,0,,COUNTA($행:$행)-1) - COUNTA 함수에 범위를 지정한 경우 -1 삭제 =OFFSET($A$2,0,0,COUNTA($A$2:$A$100)) - 모두 포함 =OFFSET($A$2,0,0,COUNTA($A$2:$A$100),COUNTA($A$2:$Z$2)) |
예를 들어 B열에 값이 추가되면 이름에도 자동으로 추가되도록 하고자 한다면 참조대상에 넣는 수식은 아래와 같은 형태로 사용하면 된다.
| - 사용 예 =OFFSET($B$2,0,0,COUNTA($B:$B)-1) |
3. 수식 확인.
설정한 이름의 참조대상을 변경했다면 정상적으로 적용됐는지 확인해 보자.
기존에 작성한 표에서 행이나 열에 값을 추가하고 이름으로 수식을 계산해 보면 정상적으로 계산이 되는 것을 확인할 수 있을 것이다.
그 외 내용
- 중간에 빈 셀이 있으면 안된다.
참조 대상의 수식에는 COUNTA 함수를 사용하기 때문에 중간에 빈 셀이 있으면 안 된다.
중간에 빈 셀이 섞이게 되면 값이 있는 셀의 개수만 세고 그 수만큼 위에서 순차적으로 값을 가져오는 형태가 되기 때문에 계산이 틀어진다.
이런 경우 빈 셀도 계산에 포함해야 하는지 빈 셀은 무시해야 하는지에 따라 추가로 수식을 수정해야 한다.
- OFFSET 함수의 기능을 잘 알아야 한다.
OFFSET 함수는 시작 셀 외에도 시작 셀을 기준으로 행이나 열의 위치를 이동한 뒤 값을 가져올 수 있다.
이를 활용하면 MATCH 함수와 조합하여 조건에 따라 기준이 되는 시작 셀을 변경하는 것도 가능하며 다른 방법으로 응용할 수도 있기 때문에 OFFSET 함수가 어떻게 작동하는지 기능을 잘 알아두면 좋다.