xls - 행 추가시 이름관리자/이름정의 반영하기(동적인 이름정의)

이름정의

이름정의 기능을 사용하고 있다면, 이미 초보자의 영역은 넘었다. 이미 알겠지만, 엑셀은 피벗테이블을 포함한 표형식의 DB형식의 모습과 그냥 범위로 나열되어 있는 형식의 데이터가 있다.

아래와 같은 범위로 된 데이터가 있다. 데이터 표가 아니다. 이럴때, 흔히 표의 특정 영역을 지정하여, 이름정의를 한다.
B2:C20의 영역을 잡아서, 과일이름 영역이라 이름정의를 했다.

정의된 이름(범위)을 사용해서 VLOOKUP 함수를 건다면 다음과 같다. 가장 일반적이지만, 오류의 가능성을 가진 형식이다.

오류가 발생하는 이유는 다음과 같이, 17번 이후에 18번 행(신규 행)이 추가되었을때, 이를 이름정의에서 동적으로 반영하지 못한다는 점이다.

물론, 102번처럼, 이름정의가 사용된 범위의 중간에 행이 들어가게 된다면, 동적으로 반영된다. 아래 그림에서 과일이름영역확장의 중간신규값이 들어갔음에도 제대로 나오는 것을 확인할 수 있다.

이름정의를 할때, 다음과 같이 OFFSET으로 영역을 정해주면, 동적으로 값을 받을 수 있다. 방식은 OFFSET 함수의 범위를 설정할때, COUNTA로 행의 개수를 세어주고, 기준셀에서 HEIGHT값을 잡아주는 방식이다.

여기까지 이해를 했다면, 아래 처럼, WIDTH 값을 잡아주는 것도 이해가 갈 것이다.

해당 영역을 통해 동일하게 VLOOKUP을 잡을 경우, 추가되는 행도 반영이 가능하다.

사용상 치명적인 단점이자, 주의할 점은 아래와 같다.

• 영역 하단에 추가적인 내용을 기재하면 안된다. 영역이 확장된다.
• 이름정의로 설정될 경우, 이름을 좌상단 ADDRESS창에서 직관적으로 확인 할 수 있는데, 이럴 경우 안보인다.

필요한 경우, 사용해보도록 하자.

끝.