하이레벨 엑셀 1. 엑셀 함수 정리, Indirect 함수의 활용, #Indirect, #vlookup, #데이터유효성검사
안녕하세요! 기획실 지차장 입니다.
오늘은 하이레벨 엑셀 함수 정리, Indirect함수의 활용에 대해서 알아보겠습니다.
vlookup, sumifs 만큼 많이 사용되는 함수는 아니지만, 잘 활용하면 퇴근시간을 단축시켜주는 고마운 함수 입니다.
스타크래프트로 비유를 하자면, vlookup, sumifs 가 히드라, 드라군, 시즈탱크
오늘의 주인공 Indirect는 퀸, 하이템플러, 사이언스배슬 같은 녀석이죠 :D ( 손이 빠른 분들 활용하면 매우 좋은 )
Indirect 함수는 직접 여러번 활용을 해봐야만, 다시 손이 가고 활용이 가능한 함수 입니다.
안써도 조금 더 엑셀 노가다로 커버할 수 있는 부분을 도와주는 함수라서, 안쓰다보면 평생 안쓰게 됩니다.
쓰는 사람은 계속 쓰게 되는데, 엑셀 초보와 중수를 나눠주는게 이런 함수 녀석 이죠. ( 사용하면 5분이라도 절약 )
Indirect 함수는 문자열을 셀 주소로 변환 합니다.
같은 형식으로 여러개의 시트가 있는 파일에서, 특정 시트의 특정 위치에 있는 값을 가져올때 사용합니다.
여러가지 기능이 있어서, 활용하는 사람들도 각자 자주 사용하는 방법으로만 쓰는 함수라서 일단 해봐야 압니다.
wizard 같은 Indirect 엑셀 함수의 세계로 다같이 들어가 보시죠.
# 엑셀 함수 정리, Indirect 함수의 활용
① 범위를 불러올때 ( 따옴표 x )
일단은 이해하려고 하시기 보다는 외워주세요.
아래 표1을 보시면, d10:d14 범위에 대해서 Indirect가 불러오는 것을 볼 수 있습니다.
=Indirect(F10) → F10셀에 있는 범위값 d10:d14를 불러와줘.
값에 따옴표를 사용하지 않고, 그대로 셀을 입력하면
해당 F10셀에 있는 d10:d14 범위값 100;200;2,000;50,000;1,000,000 을 불러옵니다.
[표1:따옴표 없이 Indirect_ 범위를 불러올때]
② 셀에 있는 값을 불러올때 ( 따옴표 사용! )
Indirect함수에 따옴표를 넣어서 사용하면, 해당 셀에 있는 값을 그대로 불러옵니다.
=Indirect("F10") → F10셀에 있는 값 "d10:d14" 불러와줘.
값에 따옴표를 사용할 경우,
해당 F10셀에 있는 값 "d10:d14" 를 그대로 출력합니다.
[표2:따옴표를 사용하여 Indirect_ 값을 불러올때]
③ 셀에 있는 범위를 불러올때 ( 따옴표 사용! )
Indirect함수에 따옴표를 넣어서 사용하면, 해당 셀에 있는 값 뿐만 아니라, 범위도 그대로 불러옵니다.
=Indirect("d10:d14") → 범위값 d10:d14를 불러와줘.
범위에 따옴표를 사용할 경우,
따옴표 안에 있는 해당 범위 "d10:d14" 에 해당하는 범위값 100;200;2,000;50,000;1,000,000 을 불러옵니다.
[표3:따옴표를 사용하여 Indirect_ 범위를 불러올때]
④ vlookup + Indirect 활용 ( feat 데이터 유효성 검사 )
기본 개념은 위와 같이 알아보았고, 이제 이것을 어떻게 활용하면 좋을까요?
우선 자주쓰는 vlookup 함수가 있습니다.
vlookup 은 정말 정말 중요한 함수이고, vlookup 정도만 잘 이해하셔도 사실 거의 모든 파일은 끝납니다.
실무 엑셀 5. 많이 사용하는 함수 시리즈#1, VLOOKUP
안녕하세요! 기획실 지차장 입니다. 엑셀을 사용하다 보면 실무에서 유독 오른손처럼 많이 쓰는 함수가 있습니다. 제 경우에는 VLOOKUP 이 그랬던 것 같아요. 엑셀을 처음 접하는 분들에게 VLOOKUP
wizardclub.tistory.com
실무 엑셀 6. 많이 사용하는 함수 시리즈#2, IFERROR+VLOOKUP
안녕하세요! 기획실 지차장 입니다. 오늘은 IFERROR 함수를 알아볼께요. 함수명에서 어떤 함수 인지를 유추할 수 있습니다. IFERROR = IF ERROR = 만약, 에러가 발생한다면 ~이렇게 해죠. 만약 에러가 발
wizardclub.tistory.com
그런데 이 vlookup 함수만으로는 조금 애매하거나, 일을 한번 더 해야하는 다음과 같은 경우가 있어요.
예를 들어서 아래 표와 같이 봄, 여름, 가을, 겨울에 해당하는 값을 2018년 시트와, 2019년 시트에서 불러오고자 할때
vlookup 함수만 사용한다면 아래표와 같이 2018년(B열), 2019년)(C열)을 각각 만든 다음
vlookup 함수로 불러와야 합니다.
[표4:vlookup 함수만을 사용하여 data를 불러올 때]
그런데, 만약 B열 하나만 사용해야하는 경우라면 어떨까요?
2018년 시트와, 2019년 시트가 있고, 요약 시트가 있는 경우 Indirect 함수를 사용하면 조금더 쉽게 풀어낼 수 있습니다.
일단 기본모양은 아래와 같구요.
[표5:vlookup 함수로 2018년, 2019년 자산을 가져올때]
1) 데이터유효성 검사
요약 시트 B열만 사용해야하기 때문에, B1셀을 데이터유효성검사 목록으로 2018, 2019로만 만들어 줍니다.
[표6:데이터유효성검사 목록 만들기]
2) Indirect
vlookup 으로 2018, 2019 시트에서 불러올 값이 있는 영역을 Indirect 함수로 불러옵니다.
vlookup 으로 2018년 봄의 data를 불러올때는 다음과 같이 함수를 사용합니다.
= vlookup ( A2 , '2018'!$B$2:$C$5 , 2 , 0 )
이걸 다시 vlookup 으로 바꿔보면
= vlookup ( A2 , vlookup( B1, F2:G3 , 2 , 0 ) , 2 , 0 )
B1셀을 2018, 2019 데이터유효성 검사로 묶어줬기 때문에, B1셀을 변수로 만들고
그에 대응하는 연도별 범위표를 만들어서 당겨온다는 생각이죠.
그런데 말입니다. 이걸 그냥 여기서만 끝내버리면 오류값이 뜹니다.
vlookup 으로 불러올때와 Indirect로 불러올때의 다음 결과 값을 보시면 이해가 쉬우실꺼에요.
vlookup 으로 불러오면 그냥 값으로 끝나기 때문에, 함수안에서 셀주소로 인식이 안됩니다.
indirect 로 불러오면, 해당 범위값을 불러올 수 있죠.
그래서 다음과 같이 Indirect로 묶어주면, vlookup 으로 불러온 '2018'!$B$2:$C$5 값을 셀주소 영역으로 인식해줘서
값을 불러오게 할 수 있습니다.
이상 엑셀 함수정리 Indirect함수, vlookup함수, 데이터유효성검사 에 대해서 알아봤습니다.
vlookup과 콜라보를 하는 경우 외에도 sum 과도 조합이 가능하고
하나더 ! 단독으로 사용하더라도 아래와 같이 시트가 여러개이고 같은 모양 일때 요약시트를 만들때도 활용 가능!
아래 그림을 보시면, D2셀 2018과 D3셀 2019를 각각 Indirect의 시트명으로 활용해서,
각각의 시트에 C2셀에 있는 값을 불러왔습니다.
Indirect는 쓰는 사람에 따라서 활용도가 무척 다양합니다.
오늘은 일부만 가져왔는데, 많은 도움이 되셨으면 좋겠네요.
그럼 또뵈요 :D
