학습(공부)하는 블로그 :: '데이터 유효성 검사' 태그의 글 목록
 

 

Notice»

Recent Post»

Recent Comment»

Recent Trackback»

03-19 18:04

 

'데이터 유효성 검사'에 해당되는 글 1

  1. 2014.12.18 indirect 함수를 이용한 동적참조영역 활용법 -1
 
반응형


오늘은 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셀에서 지정해 주는 범위로 변한다는 것 이해가세요.



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



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


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




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



반응형
: