Notice
Recent Posts
Recent Comments
rand(life)
셀값과 같은 시트이름을 찾아 수식에 사용하기 본문
오늘 어떤 선생님에게서 전화가 왔습니다. 학생별로 시트가 있고, 그 시트에 학생의 성적이 있는데, 별개의 시트에서 학생의 이름을 입력하면, 그 학생의 이름과 같은 시트를 찾아 그 시트의 특정셀값이 출력되게 할 수 없느냐는 질문이었습니다.
정교하게 짜려면 VBA를 사용해야할 것 같습니다만, 간단히 하려면 다음과 같이 가능합니다.
"평균"이라는 이름의 시트에 와서, 다음과 같이 수식을 입력합니다.
=HLOOKUP(B$1,INDIRECT($A2&"!C1:F7"),7,0)
--------답만 알기를 원하신다면 이 이후는 읽지 않으셔도 됩니다.---------
사실, Kevin학생의 국어평균점수는 이미 Kevin시트의 C7셀에서 구해두었기때문에
=Kevin!C7
이라고 간단하게 구할 수 있습니다. 그런데 굳이 복잡한 함수를 쓴 이유는 뭘까요?
엑셀의 함수를 사용할때는 많은 양의 데이타를 다룬다는 것을 항상 염두에 두어야합니다. 그래서 함수를 아래로, 오른쪽으로 끌어채우기했을때도 제대로 작동할 수 있도록 만들어야합니다. 그렇지 않고 모든 셀마다 함수를 달리 주어야한다면 엑셀을 사용하는 의미가 없는 것이지요.
=Kevin!C7 이라는 수식을 아래로 끌면 오류가 날것입니다. Kevin시트가 아니라 Mike시트에서 셀을 찾아야하는데 그걸 표현해 주지 못하니까요.
따라서 올바른 수식을 만들려면 Kevin이나 Mike와 같은 시트이름을 그대로 사용하지 말고, 수식으로 표현해야 할 것입니다.
또, =Kevin!C7 이라는 수식을 오른쪽으로 끌면 차례대로
=Kevin!D7
=Kevin!E7
=Kevin!F7
의 수식이 만들어질 것이고, 각각 영어, 수학, 과학의 평균점수를 구하게 됩니다. 그런데, "평균"이라는 시트에서 모든 과목을 다 보기 원하지 않고 일부 몇개만 보기 원한다면? 그래서 국어 다음에 영어가 나오지 않고 수학이 나오는 식으로 표를 만든다면, 위의 수식대로 해선 안되겠지요. 수학평균이 나와야할 자리에 영어평균이 나오게 되니까, 모든 셀에 수식을 새로 입력해야합니다.
따라서, 올바른 수식을 만들려면 "국어""영어"와 같은 과목이름을 맨 윗행에서 찾아, 그 과목에 해당하는 평균점수를 구하도록 해야할 것입니다.
이제, 정답인 =HLOOKUP(B$1,INDIRECT($A2&"!C1:F7"),7,0) 에 사용된 함수를 하나씩 알아봅시다.
HLOOKUP함수는 이전에 배웠습니다. 맨첫행에서 주어진 값을 찾고, 또 주어진 숫자만큼 아래로 이동한 곳에 있는 셀값을 출력하는 함수입니다.
| =HLOOKUP(B$1, | B1셀의 값을 | 국어 |
| INDIRECT($A2&"!C1:F7"), | INDIRECT($A2&"!C1:F7") <--- 이 부분에서 가리키고 있는 배열의 첫행에서 찾아서 | Kevin시트의 C1:F7의 배열의 첫행 |
| 7, | 7번째 셀에 있는 값을 구하되 | C7셀의 값 |
| 0) | "국어"를 찾되 정확하게 "국어"라고 되어있는 값을 찾아라. "영어"나 "국사"를 찾으면 안된다 |
그럼 HLOOKUP함수는 아시겠지요. INDIRECT함수 부분으로 가겠습니다.
INDIRECT함수는 셀주소를 A1과 같이 직접입력하지 않고 다른 방식으로 셀주소를 표현하는 함수입니다.
예를 들어, "가회동"이라고 주소를 써주지 않고, ""가정"의 첫글자, "회식"의 첫글자, "엄동설한"의 두번째 글자를 모아"라고 주소를 주는 것입니다.
=HLOOKUP(B$1,INDIRECT($A2&"!C1:F7"),7,0)
이 수식에서 HLOOKUP함수의 두번째 인수는 첫행의 값을 찾을 배열입니다. 여기서는 Kevin!C1:F7을 의미해야합니다.
INDIRECT($A2&"!C1:F7")
라는 수식은
A2라는 셀에 있는 값에
!C1:F7 이라는 문자를 더하면 만들어지는 주소를 반환합니다.
A2라는 셀안에는 Kevin이라는 학생의 이름이 들어있습니다. 따라서 결국 위의 수식은
Kevin!C1:F7
이라는 주소를 반환하게 됩니다. 즉,
INDIRECT($A2&"!C1:F7") ===> Kevin!C1:F7 이므로,
HLOOKUP(B$1,INDIRECT($A2&"!C1:F7"),7,0) ===> HLOOKUP(B$1,Kevin!C1:F7 ,7,0) 이 됩니다.
이제 이 수식을 아래로, 오른쪽으로 끌어채우기 해보세요. ^^
2009/03/25 - [컴퓨터/엑셀] - 1일1함수(6) : HLOOKUP 함수 -- 가로방향 데이터 목록에서 값 가져오기
2009/04/21 - [컴퓨터/엑셀] - 1일1함수 (18) INDIRECT 함수 : 셀주소 지정을 간접적으로
'컴퓨터/엑셀' Related Articles
more
