Posted
Filed under MS Excel
|| English || 中文 || view 12692 ||

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

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

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

[Excel] 데이터 표로 동적 범위 활용하기

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

[Excel] 데이터 표로 동적 범위 활용하기

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

표 만들기

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

[Excel] 데이터 표로 동적 범위 활용하기

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

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

[Excel] 데이터 표로 동적 범위 활용하기

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

[Excel] 데이터 표로 동적 범위 활용하기

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

[Excel] 데이터 표로 동적 범위 활용하기

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

데이터 유효성 검사

데이터 유효성

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

데이터 유효성

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

[Excel] 데이터 표로 동적 범위 활용하기

[Excel] 데이터 표로 동적 범위 활용하기

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

VLOOKUP
[Excel] 데이터 표로 동적 범위 활용하기

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

[Excel] 데이터 표로 동적 범위 활용하기
[Excel] 데이터 표로 동적 범위 활용하기

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

[Excel] 데이터 표로 동적 범위 활용하기

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

[Excel] 데이터 표로 동적 범위 활용하기

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

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

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

[Excel] 데이터 표로 동적 범위 활용하기
[Excel] 데이터 표로 동적 범위 활용하기
[Excel] 데이터 표로 동적 범위 활용하기

데이터 표의 구조와 각 요소들을 어떻게 참조하는 지는 위 그림과 같이 사용 중에 나오는 도움말이나 자동 입력 기능으로 비교적 쉽게 알 수는 있지만, 필요하다면 따로 확인해 두면 좋다.
"MS Excel " 분류의 다른 글
[MS Excel] 셀에 입력하는 수식 자체에 주석 달기  (0)2010/11/24  
[Excel 2016] 새로 추가된 차트 유형  (0)2015/09/23  
[Excel] 차트 작성 시 숫자로 된 X축이 데이터로 처리되는 것을 방지하기  (0)2007/07/29  
[MS Excel] 셀 안에 특정 문자를 반복해서 가득 채워넣기  (4)2008/11/21  
[Excel] 이름 목록에서 랜덤하게 이름 뽑아내기  (0)2013/01/06  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
김형욱

군대에서꼭해보고싶었던기능이었는데..
몰라서못했었어요ㅠ

감사합니다~