CaN (Computer & Network) Tips

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

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

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

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


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


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


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


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


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

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


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


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


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


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


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


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

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

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