CaN (Computer & Network) Tips

엑셀(Excel)에 데이터를 입력해 일종의 데이터셋이나 데이터베이스로 사용하는 경우 개별 항목이 추가되거나 삭제가 되면, 이를 참조해 사용하던 수식이 영향을 받게 된다. 이런 점을 고려해서 2003 버전 이전에서는 데이터 범위를 참조할 때 Offset 함수 등을 이용 해 동적 범위를 인식하는 간단한 수식을 만들어 사용하기도 했다.

엑셀 2007 버전 이후로는 데이터 표 기능을 이용해 간단하게 해결할 수 있다.

우선 아래 그림과 같은 표가 있다고 가정한다. A열은 명단이고, B열은 매출액 데이터라고 한다.

전체 데이터 영역을 선택([Ctrl] + [Shift] + [8])하고, 삽입 탭의 표 메뉴를 선택하거나, 바로 가기 키인 [Ctrl] + [L]을 누른다.


[표 만들기] 대화 상자에서 범위나 머리글 포함 여부를 확인하고 [확인] 단추를 누른다.


이렇게 하면 아래 그림과 같이 범위 전체에 서식이 적용된 윤곽이 나타나고 상단에 이 범위 이름이 "표1"이라는 이름으로 지정된 것을 볼 수 있다.


그 다음 단계는 범위에 이름을 지정하는 것이다. 2007 이후에서는 추천하는 방법은 아니다. 예전부터 비슷하게 써온 적이 있다면 이와 같이 진행할 수 있다.

우선 A열의 열 제목을 제외한 명단 부분을 선택하고, 이름 지정 상자에서 적당한 이름을 지정한다. 여기에서는 "명단"이라고 입력하고 [Enter]를 누른다.


B열의 매출 부분도 같은 방법으로 "매출"이라는 이름을 지정해 준다.


이제 이렇게 지정한 범위를 참조하는 수식을 작성해 보도록 한다. 아래 그림과 같이 명단에서 특정 이름을 선택하면, 그 이름에 해당하는 매출액을 보여주는 조회 프로그램을 만든다고 가정한다.


A2셀에 명단에 적힌 이름이 펼쳐지는 선택 상자를 만들기 위해 아래 그림과 같이 [데이터] 탭에서 [데이터 유효성 검사...] 메뉴를 찾아 실행한다.



"제한 대상"에서 '목록'을 선택하고, "원본" 상자에 직접 '=명단'(등호 포함)이라고 입력한다.


이렇게 하고 [확인]을 누르면 A2셀 오른쪽 외곽에 아래 방향 화살표가 나오고 이를 펼치면 명단에 들어있는 이름들을 확인하고 선택할 수 있다.



이번에는 B2셀에 해당 이름의 매출액을 조회하는 수식을 작성해 입력한다. 이때 조회하는 원본 데이터의 범위는 "표1"이라는 이름으로 지정한다.


추가로 매출액 전부를 합계한 총매출 항목도 만들어 본다. 이때에도 미리 지정해 둔 "매출"이라는 이름을 수식에 이용한다.


이렇게 데이터 범위를 이름으로 지정해 놓고, 원본 데이터에 새로운 항목이 추가되었을 때도 그대로 반영이 되는지 확인하기 위해 아래 그림과 같이 원본 데이터에 항목을 하나 추가한다.


그 다음 바로 만들어 놓았던 수식 페이지를 보면 이름도 추가되어 있고, 총매출도 자동으로 재계산이 이루어진 것을 확인할 수 있다.


따라서, 엑셀에 자료용으로 데이터를 입력해 둘 때에는 항상 데이터 표로 만들어 두는 것이 요긴하다.

또한, 앞서 말한 바와 같이 필요한 부분별로 이름 지정할 필요 없이 표의 구조를 참조로 이용하면 더 간단하게 동적 범위를 활용할 수 있다.

예를 들어, 위에서 총매출액을 구할 때 사용했던 수식인 "=SUM(매출)"은 아래와 같이 바꿔 사용할 수 있다.


데이터 표의 구조와 각 요소들을 어떻게 참조하는 지는 위 그림과 같이 사용 중에 나오는 도움말이나 자동 입력 기능으로 비교적 쉽게 알 수는 있지만, 필요하다면 따로 확인해 두면 좋다.