안녕하세요. 이지이지입니다.
이번 포스팅에서는 엑셀 INDIRECT 함수의 기초와 INDIRECT 함수를 이용한 데이터 유효성 검사에 대해 알아보도록 하겠습니다.
| INDIRECT 함수의 기초 |
[함수 삽입]-[함수 마법사]를 클릭하여 INDIRECT 함수를 선택하면 다음과 같은 함수 인수 대화상자가 나타납니다.
함수 설명 부분에는 "텍스트 문자열로 지정한 셀 주소를 돌려줍니다."라고 설명이 되어 있는데, 딱히 와닿지는 않습니다.
① Ref_text: 참조할 셀의 주소나 이름 관리자에서 정의한 이름을 입력합니다.
② A1: 참조 해석 방식을 입력하는데 공백으로 두거나 TRUE를 입력하면 우리가 평소 사용하는 엑셀의 스타일 참조방식을 사용합니다. FALSE를 입력하면 R1C1 스타일 참조 방식을 사용합니다.
현재 일반적으로 R1C1방식은 거의 사용하지 않으므로 빈칸으로 두는 것을 권장합니다.
요약: INDIRECT 함수는 Ref_text부분에 적힌 셀의 주소로 이동하여 그 이동한 셀에 있는 값을 가져오게 됩니다.
참고: Ref_text부분에 셀의 주소를 그냥 셀의 주소를 적는 것 (예: A1)과 셀의 주소를 쌍따옴표(" ")로 묶는 것(예: "A1")에는 차이가 있습니다. 아래 이미지와 설명을 참고하시기 바랍니다.
D2셀 입력 내용: =INDIRECT(C2)
C2셀 입력 내용: B2
B2셀 입력 내용: 이순신
☞ C2셀에 있는 셀 주소 B2로 가서 B2에 있는 값을 가져온다.
E2셀 입력 내용: =INDIRECT("C2")
C2셀 입력 내용: B2
☞ C2셀에 입력된 값을 가져온다.
위에서 보듯 셀 주소를 따옴표로 묶으면 따옴표로 묶인 셀 주소에 있는 값을 바로 가져오라는 것이고,
셀 주소를 따옴표로 묶지 않으면 따옴표로 묶지 않은 셀 주소로 이동하고 그 셀에 있는 다른 셀 주소로 한 번 더 이동하여 그 값을 가져오라는 것입니다.
| INDIRECT 함수 응용 데이터 유효성 검사 |
다음 엑셀 화면을 예로 들어 INDIRECT 함수를 응용하여 데이터 유효성 검사를 해보도록 하겠습니다.
① 반: 데이터 유효성 검사의 목록을 사용합니다.
② 번호: INDIRECT 함수를 사용하여 반이 바뀌면 반에 맞는 번호만 선택할 수 있도록 데이터 유효성 검사를 합니다.
③ 이름: 데이터 유효성 검사를 사용하지 않고, 여러 함수를 사용해 반과 번호에 맞는 이름이 나타나도록 합니다.
(정말 다양한 방법이 있을 수 있는데요. 여기서는 트릭을 사용하지 않고, 최대한 원형 데이터 그대로 사용하는 예를 보여드리기 위해 좀 복잡한 함수로 구성될 것입니다. 목적은 데이터 유효성 검사이니 패스하셔도 좋습니다.)
1. 먼저 반에 대한 데이터 유효성 검사를 넣어보겠습니다.
① : 데이터 유효성 검사를 할 셀을 선택합니다.
② ~ ③ : [데이터 유효성 검사]-[설정]을 차례로 클릭합니다.
④ : 제한대상은 [목록]을 선택합니다.
⑤ : 원본에는 셀에 들어갈 수 있는 값을 콤마로 구분하여 적습니다.
위 예에서는 1~4반 이므로 1, 2, 3, 4를 입력하였습니다.
2. 다음은 번호에 대한 유효성 검사로 INDIRECT 함수를 사용하도록 하겠습니다.
① : 1반 학생에 해당하는 번호들을 드래그하여 선택합니다.
② : 이름 상자에 class1을 입력합니다. (이름은 원하는 대로 입력하면 됩니다.)
③ : 2반~4반 학생에 해당하는 번호에도 이 과정을 반복합니다.
2반은 class2, 3반은 class3, 4반은 class4로 이름을 입력합니다.
※ 이 포스팅의 목적은 INDIRECT 함수의 사용에 있습니다. 사실 학생 수가 대규모이면 이렇게 하나씩 이름을 정의해주는 것이 쉬운 일은 아닙니다. 이 때는 이름을 동적으로 지정해 주어야 하는데 검색창에 "엑셀 동적범위"로 검색을 하면 많은 자료들이 있으니 참고하시기 바랍니다. 이지이지에서도 조만간 다룰 예정에 있습니다.
① : 데이터 유효성 검사를 할 셀을 선택합니다.
② ~ ③ : [데이터 유효성 검사]-[설정]을 차례로 클릭합니다.
④ : 제한 대상은 목록을 선택합니다.
⑤ : 원본에는 다음과 같이 입력합니다. =INDIRECT("class"&G1)
☞ G1셀에는 반의 값이 들어가게 됩니다. 현재 위에서는 1로 되어 있으므로 곧, INDIRECT(class1)과 같은 의미가 됩니다.
class1은 아까 1반 학생들의 번호를 이름으로 지정해 두었으므로, 원본은 1반 학생의 번호 1, 2를 목록으로 가져오게 됩니다.
다음 화면은 반을 4반으로, 번호 셀을 선택했을 때 데이터 유효성 검사의 적용 예입니다.
방금 보시듯 INDIRECT 함수는 이름과 함께 사용하여 굉장히 유용하게 쓰일 수 있습니다.
실제 INDIRECT 함수의 사용 설명은 여기까지 입니다.
이름을 반과 번호에 맞게 자동으로 입력하는 방법까지 보시고 싶은 분만 다음을 참고하시기 바랍니다.
| 번외편: 이름 자동 입력 수식 |
다음은 이름이 자동으로 입력되는 화면의 예입니다.
위에서 언급한 동적범위를 사용하였고, 각 함수의 결과값에 대해서만 설명드리겠습니다.
반은 4, 번호는 1로 입력하였을 때, 저절로 이름이 치킨으로 나오는 것을 볼 수 있습니다.
사용된 수식은 다음과 같습니다.
=OFFSET(INDEX(이름,MATCH(G2,INDIRECT("class"&G1),0),2),0,1)
위 화면에서 수식에 있는 각 함수의 결과값은 다음과 같습니다.
INDIRECT("class"&G1) → INDIRECT(class4) → 이름 class4를 참조 [B9:B12]
MATCH(G2, B9:B12, 0) → 1
☞ B9:B12 영역에서 G2값 [1]이 나타나는 첫 번째 행은 1
INDEX(이름, 1, 2)
이름 관리자로 정의한 이름 부분:
=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$G$1,Sheet1!$A$2:$A$12,0),0,COUNTIF(Sheet1!$A$2:$A$12,Sheet1!$G$1),2)
→ A9:B12
INDEX(A9:B12, 1, 2) → B9
☞ A9:B12 영역에서 1행 2열은 B9
=OFFSET(B9, 0, 1) → C9 : 치킨
☞ B9셀에서 0행 1열 떨어진 셀은 C9
이상 INDIRECT 함수를 이용한 데이터 유효성 검사에 대한 포스팅을 마치도록 하겠습니다.
마지막 부분에서 이해가 안가시는 분이 있으면 댓글로 남겨주세요~~~
댓글