블로그 이미지
주로 인재개발원 등의 사이버학습을 정리, 요약하는 상시학습 블로그입니다. 깨비형
« 2017/10 »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

Archive»

체험 블로그 마케팅 서비스 OLPOST

Category»

Notice»

Statistics Graph

 
 



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


아래 예제를 보겠습니다.




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


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


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





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


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


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




이번에도 마찬가지로 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)




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



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



저작자 표시 동일 조건 변경 허락
신고


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



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



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


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




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



저작자 표시 동일 조건 변경 허락
신고