CaN (Computer & Network) Tips

Excel(엑셀) 시트에 입력된 데이터 표에서 행과 열을 동시에 만족하는 데이터를 찾아오는 방법으로 vlookup과 match 등의 함수를 이용하는 방법에 대해 글을 올린 적이 있다.

참고: [Excel] 행과 열을 동시에 만족하는 데이터 찾아오기, 2009. 1. 18.

index 함수를 활용하는 등 약간 다른 방법을 적용할 수도 있지만, 여기에서는 "이름"을 활용하는 방법을 소개한다.

우선 아래와 같은 데이터 표가 있다고 한다.


데이터 표 전체를 선택하고, [수식] 메뉴에서 이름 관리와 관계된 항목 중 [선택 영역에서 만들기]를 선택한다.


이미 입력되어 있는 행과 열의 제목으로 이름을 만든다.


아래 그림과 같이 [이름 관리자] 메뉴를 보면 이름이 지정되어 있는 것을 확인할 수 있다.


그 다음 아래 그림과 같이 조회를 위한 표를 작성한다. 찾을 이름과 과목에는 이미 지정되어 있는 목록이 나타나도록 "데이터 유효성" 기능을 사용한다.


입력할 내용의 목록을 지정하는 것은 반드시 필요한 과정은 아니지만, 사용자의 입력을 편리하게 하고, 오류를 없애기 위한 절차이므로 실무에서는 해주는 것이 좋다.

일단, 이름과 과목에 찾을 내용을 입력하지 않았다 하더라도 결과 셀(점수)에 사용할 수식을 점검하면 아래와 같다.


위 그림에 있는 것처럼 "=나다라 영어"와 같이 행과 열에서 찾을 데이터를 빈 칸 하나를 사이에 두고 입력한다. 이렇게 하면 아래 그림과 같이 결과가 나타난다.


찾을 항목을 이름과 과목 셀에서 동적으로 지정해야 하므로 아래 그림과 같이 셀 주소를 포함하는 수식으로 변경한다.


그런데 이렇게 하면 아래 그림과 같이 오류가 나타나면 제대로 된 결과가 나타나지 않는 것이 정상이다.


이를 해결하기 위해 수식에 입력한 두 개의 셀 주소에 indrect 함수를 추가하여 아래와 같이 전체 수식을 수정한다.


이렇게 하면 제대로 된 결과가 나타나는 것을 확인할 수 있다.


indirect 함수를 사용하면 해당 셀에서 참조하는 값 자체를 가져올 수 있다.

수식 안에서 공란을 사용하는 이유는 다음 글을 참고한다.

참고: [Excel] 수식에서 셀 주소로 셀 범위 지정할 때 사용하는 기호 3가지, 2009. 1. 21.

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

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

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

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


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


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


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

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


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


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


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



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


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



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


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


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


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


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

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

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


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

사용 기능
- 데이터 유효성 검사

어떤 셀은 한글로만, 어떤 셀은 영문으로만 입력해야 하는 경우가 있는데 이때마다 [한/영] 키를 누르는 것이 번거롭다면 이 기능을 사용할 수 있다.

사용 기능
- 데이터 유효성 검사

Excel 2003 이하: [데이터] > [유효성 검사]