Category»

Notice»

Statistics Graph
티스토리 모바일 블로그

 
 


이번 시간에는 indirect 함수를 이용한 이중 유효성 검사를 배워보겠습니다.


유효성 검사에 대해서는  지난번에 배웠기 때문에 설명은 생략하겠습니다.


우선 오늘 예제파일은 아래와 같은 직원명부입니다.




이 중에서 소속에 이중 유효성 검사를 적용하고, 옆에 있는 직(급)은 그냥 유효성 검사를 적용할 예정입니다.




아래 그림을 보시면 기획감사실을 제외하고 행정지원국, 복지환경국, 도시건설국 등에 

하부 부서가 있습니다.

그리고 직(급)도 네 가지로 구성해봤습니다.




먼저 유효성 검사에 적용할 데이터에 이름을 지정하겠습니다.


① 먼저 I4셀부터 I9셀까지 마우스로 범위를 지정하고,


② Ctrl키를 누른 상태에서 J4:J10, K4:K9, L4:L8을 추가합니다.





다음은 [수식] 탭에 있는 [정의된 이름] 중에 [선택 영역에서 만들기]를 선택합니다.




이름 만들기 위한 창이 열리면 아래와 같이 [왼쪽 열]에 있는 체크는 지워주시고(안 지워도 되지만 필요없기 때문에 지웁니다. < -- 이것은 가로로 왼쪽에 있는 것을 이름 지정해 줍니다.)

[첫 행]만 체크한 뒤에 [확인]을 클릭합니다.



위에 선택 범위가 국과 직(급)이 길이가 다릅니다. 

그래서 아래와 같은 에러 메시지가 뜹니다.


여기서 [확인]을 클릭하면 행정지원국, 복지환경국 만 이름이 지정됩니다.

그래서 [확인]을 클릭하지 말고 오른쪽 상단에 있는 [닫기] 버튼을 클릭하고 빠져나옵니다.




자 제대로 이름 정의가 되었는 지 확인할 차례입니다.


앞에서 한 것처럼 [수식]탭에 [정의된 이름] - [이름 관리자]를 차례로 클릭합니다.




아래와 같이 이름이 지정된 것을 알 수 있습니다.

여기서 주의할 점은 직(급)의 경우 직_급으로 이름이 정의된 사실을 기억해 놓기 바랍니다.




자 이제 이중 유효성 검사를 설정할 차례입니다.


먼저 C5셀에서 C8셀까지를 범위 지정하고 [데이터]텝에 있는 [데이터 유효성 검사]를 클릭합니다.




유효성 설정하는 창이 나오면 아래와 같이 

제한 대상에 [목록]을 그리고 원본에는 =$H4:$K4 를 입력한 뒤에 [확인] 버튼을 클릭합니다.




이번에는 부서명에 국 하위 부서명을 이중 유효성 검사 설정할 차례입니다.


먼저 부서명이 들어갈 D5셀에서 D8셀까지 범위를 지정하고 [데이터][데이터 유효성 검사]를 선택합니다.




데이터 유효성 조건창이 열리면 아래와 같이 제한 대상을 [목록]으로 하고,

원본에 =indirect(c5) 라고 입력하고 [확인] 버튼을 누릅니다.


여기서 C5에 입력되는 값에 따라 부서명에 유효성 검사가 달라야 하므로 

앞에서 국별로 부서명을 국 이름으로 지정한 이유입니다.


즉, =indirect(c5) <---- c5에 입력되는 국 이름에 따라 부서명은 국 이름으로 유효성 검사를 설정한 이유죠.





제대로 되었다면 아래와 같은 에러 메시지가 뜹니다.

이유는 왼쪽 실/국에 아무 것도 선택되지 않았기 때문이니 무시하고 [예]를 클릭합니다.





다음은 직(급)에 데이터 유효성 검사를 설정할 차례죠.


아래와 같이 범위 설정하고 [데이터] [데이터 유효성 검사]를 클릭합니다.




데이터 유효성 조건 입력창이 열리면, 아래와 같이 제한 대상에 [목록]을 선택하고,

아까 직(급)의 범위가 직_급 이었던 것을 기억하시죠.

그래서 원본에는 =직_급 이라고 입력하고 [확인] 버튼을 클릭합니다.




자 이제 제대로 되었는 지 확인해 보겠습니다.


행정지원국을 선택한 뒤에 부서명을 보면 자치행정과, 회계재산과 등이 제대로 나오는군요.




이번 시간에는 indirect 함수를 이용하여 이중 유효성 검사를 설정하는 방법을 알아보았습니다.


실습했던 파일을 첨부합니다.



기초-3.xlsx




도움이 되셨다면 공감 꾸~욱 부탁드립니다 !!!



저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License



오늘은 지난 시간 약속대로 참조할 영역이 다른 시트일 경우에는 어떻게 하는지를 알아보겠습니다.


아래 예제를 보겠습니다.




위 예제 역시 성적표 입니다.


조금 다른 점이 있다면 위와 같이 성적표 시트가 따로 있고,


아래와 같이 가져올 참조 데이터 영역은 중간고사, 기말고사라는 시트가 따로 있는 경우입니다.





다행인 것은 중간고사 시트와 기말고사 시트의 참조영역 범위가 똑같다는 것이죠.


만약 범위가 다른 경우라면 상당히 난감한 상황이 발생합니다.


(뭐 그래도 가장 데이터가 많은 행을 기준으로 하면 그럭저럭 위기는 모면하겠죠.)




이번에도 마찬가지로 indirect 함수를 사용할 것입니다.


우선 시험종류 아래 B4 셀에 데이터 유효성 검사를 지정하겠습니다.


제한 대상에 [목록]을 선택하시고, 원본: [중간고사,기말고사]라고 입력하시고 [확인]을 클릭합니다.





이젠 성적을 가져올 차례입니다.




여기서 잠깐 생각해봅시다.


B4 셀의 값이 중간고사일 경우와 기말고사일 경우 참조영역은 다음과 같이 표현할 수 있습니다.



      • 중간고사일 경우 참조영역: 중간고사!$B$3:$I$12
      • 기말고사일 경우 참조영역: 기말고사!$B$3:$I$12



참조영역을 가져오기 위해 indirect 함수를 사용한다고 예고했었죠.



그럼 저 영역중에 중간고사와 기말고사는 시트명으로 들어가기 때문에 참조영역이 가능합니다.


그래서 B4셀의 값을 그냥 가져오면 되겠죠.



문제는 $B$3:$I$12의 범위를 어떻게 삽입할 것인가 하는 것입니다.


indirect 함수에서 참조범위가 아닌 것은 모두 데이터로 입력해야 한다는 것을 지난 번에 배웠죠.


그래서 "!$B$3:$I$12" <---- 이 범위가 추가되면 되겠죠.



그런데 한가지 문제가 있습니다.


참조영역과 데이터를 연결하는 방법의 문제인데요,


indirect(B4"!$B$3:$I$12") <--- 이렇게 수식을 넣으면 어떻게 될까요?



입력이 되지 않습니다. 참조영역+데이터의 형식이 되기 때문입니다.


다시 말하자면 참조영역 형식도 아니고 데이터 형식도 아닌 잡탕이 되었기 때문에 인식이 안됩니다.



그래서 엑셀 프로그램이 인식하도록 약간의 기술이 필요합니다.


바로 & 라는 연결연산자가 등장하는거죠.



위 참조영역을 다시 맞게 정리하면 아래와 같습니다.


indirect(B4&"!$B$3:$I$12")






위 그림에서 C6셀에 들어간 수식은 다음과 같습니다.


=VLOOKUP($B$6,INDIRECT($B$4&"!$B$3:$I$12"),2,0)




시트 탭이 몇 개라도 형식만 똑같이 맞춰두면 동적 참조영역 얼마든지 쉽게 만들수 있겠죠!



도움이 되셨다면 공감 꾸~욱 눌러주시고 가세요. ^^



저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License


오늘은 indirect 함수를 배워보겠습니다.



이 함수는 텍스트로 지정한 셀 주소를 반환해주는 함수인데요,


앞서 배운 offset 함수가 동적 영역을 설정하는 반면,


indirect 함수는 동적 참조영역을 설정하는 함수입니다.



무슨 말인지 헷갈리죠?


이렇게 말하는 저도 쉽게 설명 못해 미치겠습니다.



간단히 예를 들면 아~하 라고 쉽게 이해하실 수 있을 것으로 보고


바로 예제로 들어가겠습니다.



아래는 성적표입니다.




같은 시트에 성적표가 두개 있죠.


왼쪽에 있는 것은 중간고사이고 오른쪽에 있는 것은 기말고사입니다.


하단에 보시면 시험종류에 따라 학생의 이름을 입력하면 그 학생의 성적표가 나오도록 하는 것입니다.


물론 if 함수를 사용하면 되겠죠.


B16셀에 


    • 중간고사가 입력되면 참조범위를 $B$3:$I$12로
    • 기말고사가 입력되면 참조범위를 $K$3:$K$12로


지정해주면 되죠.


C18에는 아래과 같은 수식이 입력이 되겠죠.


=VLOOKUP(B18, IF(B16="중간고사", $B$3:$I$12, $K$3:$K$12), 2, 0)





시험이 두 개밖에 없어 중간고사가 아니면 기말고사라면 저렇게 하면 되죠.


문제는 시험이 두 개만 있는 것이 아니고 매월 한번씩 있다면 


if 함수로 일일이 범위를 지정하는 것은 노가다가 되겠죠.



그래서 이럴 경우 사용하는 것이 바로 indirect 함수입니다.


위 그림과 같이 한 시트에 찾기참조할 범위가 두 개 이상 있는 경우에는


지난 번에 배운 이름을 정의하여 각 이름을 범위로 지정해 주는 방법이 간단하겠죠.



이름 지정하는 방법은 배웠죠. 그래서 생략합니다.


대신 오늘은 데이터 유효성검사를 활용하는 법을 알아보겠습니다.


데이터 유효성검사는 셀에 입력하는 자료가 유효한지 아닌지를 검사하는 거죠.


요즘 특정 셀에 정해진 데이터 외에는 선택하지 못하게 하는 방법으로 많이 활용합니다.


B16셀에서 [데이터] - [데이터 유효성 검사] - [데이터 유효성 검사 만들기] 를 선택합니다.




아마 아래와 같은 창이 하나 나타날 것입니다.


각 부분에 대한 설명은 생략하겠습니다. (개별적으로 아시고 싶은 분들은 인터넷 검색하면 됩니다.)




아래와 같이 입력합니다.


제한 대상에 [목록]을 선택하시고, 원본: [중간고사,기말고사]라고 입력하시고 [확인]을 클릭합니다.




제대로 되었다면 아래와 같이 셀 옆에 드롭다운 표시가 나왔죠. 



이제 이 셀에서는 중간고사와 기말고사 외에는 입력이 안됩니다.


(물론 목록 추가하면 추가한 목록도 입력할 수 있겠죠.)



자 이제 돌와와서 참조할 두 곳의 범위에 이름을 중간고사, 기말고사라고 각각 붙이겠습니다.


눈치 빠르신 분은 왜 그렇게 하는지 조금 눈치를 채셨을껍니다.


이름 지정하는 것은 앞에서 배웠으니 그냥 마우스로 범위 지정해서 정하겠습니다.







이젠 성적을 가져올 차례입니다.


여기서 잠깐, indiredt 함수는 가져올 데이터가 특정 행의 데이터일 경우에는 겹따옴표("")로 묶어줍니다.


반면에 가져올 데이터가 참조되는 값일 경우에는 그냥 셀주소만 가져옵니다.


아래 그림을 보시면



K16셀에는 =INDIRECT(K5)라고 입력되었지만 K5에는 그냥 데이터가 입력되어 있기 때문에 애러가 납니다.


K17셀에는 =INDIRECT("K5") 데이터가 입력되었기 때문에 겹따옴표("")로 막았죠. 그러면 셀값이 출력되죠.


K18셀에는 =INDIRECT(N14) N14셀에는 K5라는 셀주소가 입력되어 있죠, 참조가 되는거죠. 그래서 K5의 내용을 가져옵니다.



그러면 다시 본래대로 돌아가겠습니다.


결국 범위이름을 B16셀에 입력하는 데이터와 일치시켰으니, 찾기참조를 B16셀에서 가져오면 되겠죠.



그래서 C18셀에 위 그림과 같은 아래 식이 입력된 것입니다.



=VLOOKUP($B$18, INDIRECT($B$16), 2, 0)



INDIRECT($B$16)의 값은 우리가 B16셀에서 지정해 주는 범위로 변한다는 것 이해가세요.



이번 시간에는 같은 시트에 동적 참조영역을 만드는 방법을 알아보았습니다.



다음 시간에는 시트마다 입력된 형식은 같지만 


서로 다른 시트를 동적참조영역으로 만드는 방법을 알아보겠습니다.




도움이 되셨다면 공감 꾸~욱 눌러주시고 가세요. ^^



저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
 

티스토리 툴바