모두들 반갑다. 정보저장소 주인장이다. 이번 주 들어서 겨울 날씨가 조금씩 풀리더니 어제는 진짜 늦가을 정도의 따뜻한 날이 왔다. 날이 따뜻하니 운동하기 딱 좋았을 것 같지만, 실상은 그렇지 않았다. 왜냐? 이놈의 미.세.먼.지 때문! 날씨가 따뜻해졌지만 그의 대가로 미세먼지를 받았다. 차라리 추위를 묻고 더블로 가는 게 나을 뻔했다. 미세먼지가 심한 날은 모두들 마스크를 꼭 착용하고 다니도록 하자!
오늘은 어제 엑셀 함수 모음 1탄에 이어 2탄을 포스팅 해보고자 한다. 어제는 간단하게 실생활에서 누구든지 사용하기 간편한, 그리고 가장 기본이라고 할 수 있는 함수 3가지인 합계(SUM), 평균(AVERAGE), 카운터(COUNT)에 대해 알아보았었다. 오늘은 여기서 약간 심화된 함수인 VLOOKUP, HLOOKUP 함수에 대해 알아보고자 한다.
*(참고) 엑셀 함수 모음 1탄 포스팅 URL :: https://info-storage.tistory.com/111
엑셀 함수 모음 1탄 :: 합계(SUM) / 평균(AVERAGE) / 카운트(COUNT)
모두들 해~위! 저번 주에 폭풍 업로드를 예고했으나 역시나 사람의 마음가짐이란 온전히 챙기기 어렵다. 저번 주에는 심지어 일요일에 회사일로 풀 근무를 해버리는 바람에, 그리고 금-토는 일요일 일을 준비하느..
info-storage.tistory.com
일단 사용하기 앞서 이 함수들이 어떤 의미를 갖는지 알고 써야 하지 않겠는가? 원래 모든 공부들이 개념을 이해하면 응용하고 해결하는 것은 의외로 간단하게 풀리기 마련이다. (이것은 필자가 나름 공부를 좀 했다는 것을 반증한다. 훗) 먼저 엑셀 vlookup 함수는 쉽게 말해 '열'에 있는 데이터와 일치한 데이터를 크롤링해오는 것을 의미한다. 나름 눈치가 있다면 함수 자체에 해석을 해보면 간단하다. 'V'는'verticality'의 약자, '수직'이라는 뜻을 담고 있으며 뒤에 'lookup'은 '찾다'는 의미이다. 즉 수직(열)에 있는 데이터를 찾는다는 의미가 될 수 있다.
그럼 당연히 눈치가 있다면 엑셀 hlookup 함수의 의미는 유추해볼 수 있지 않을까? 'H'는 'horizontality'의 약자, '수평'이라는 의미를 담고 있다. 따라서 이 함수는 수평(행)에 있는 데이터를 크롤링하여 데이터를 입력할 수 있는 함수이다. 개념을 설명하면 이런데 텍스트로서는 충분히 의미를 담을 수도, 여러분들을 이해시킬 수 없을 것이다. 그렇기 때문에 지금부터 실전 예시를 통해 한 번 알아보도록 하자.
#1 엑셀 vlookup 함수 사용법
자! 가장 먼저 vlookup 함수를 활용해보자. 설명하기 앞서 상황을 하나 가정해보자. 상황은 다음과 같다.
상황 예시] 필자는 (주)정보저장소 회사의 대표자로서 10명의 직원 중 유재석, 신동엽, 이수근 직원을 해외로 발령을 보내고자 한다. 명단은 정해졌으나 그에 따른 정보들을 일일이 찾아서 기입하고자 하니 매우 귀차니즘이 심해져 이를 해결할 방법을 찾고자 한다. 이럴 때 어떤 함수를 써야 하겠는가?
위의 상황에서 답이 될 수 있는 것은 당연히 'vlookup' 함수라고 할 수 있다. 먼저 사원 현황을 보면 우리들의 눈에 굉장히 익숙한 10명의 직원 이름이 보인다. 그리고 우측에 발령 현황 명단에는 유재석, 신동엽, 이수근 3명의 이름은 기입되어 있으나 각 개인의 정보인 부서, 사번 란은 비워져 있다. 물론 10명 정도의 소량이면 찾아서 입력하는 게 더 빠를 수 있으나 나중에 몇백 명 되는 단위를 일일이 찾아서 기입하기란 불가능한 수준 일터. 그렇기 때문에 저기 빈칸을 채울 수 있는 vlookup 함수를 사용해보자.
검색을 해보자. 함수를 사용하기 위해선 어떤 기호를 쓰는지 말 안 해도 다들 알 꺼라 믿는다. '='를 먼저 넣고 난 후 vloopup을 검색하자. 그럼 함수란에 버젓이 검색이 될 것이다. 그럼 그것을 탭해주자. 그럼 차례대로 lookup_value / table_array / col_index_num / [range_lookup] 순서대로 나온다. 각 함수값에 대한 내용은 아래를 참고하자.
1_ lookup_value : 찾고자 하는 기준값
2_ table_array : 기준값을 기본으로 기준값과 관련된 데이터를 찾기 위한 범위
3_ col_index_num : 2번 범위에서 찾고자 하는 데이터 순서(열)
4_ range_lookup : 일치하는 값(0, FALSE)을 찾을지, 유사한 값(1, TRUE)을 찾을지 선택
위 내용을 바탕으로 하나씩 차근차근해보자. 일단 발령 현황에 부서란을 먼저 채워보자. 부서란에 vlookup 함수를 넣자. 그러면 위의 4칸을 채워야 한다. 가장 먼저 'lookup_value'를 채워보자. 이 칸은 기준값을 정해 놓는 것이다. 이 표에서는 '유재석(H5)'이 될 것이다. 즉 '유재석'이라는 기준값에 대한 일치하는 정보의 '부서' 데이터 값이 들어가야 하기 때문. 그렇기 때문에 기준값인 H5열을 입력해주도록 하자.
그 다음은 'table_array' 칸이다. 여기는 기준값에 일치하는 데이터를 찾기 위한 범위를 선택할 수 있다. 위의 표에서는 사원 현황 표가 될 수 있다. 그중 C5부터 F14열(빨간색 네모칸)이 해당한다고 보면 된다. 범위를 쭈~욱 드래그하여 설정해주도록 하자.
그 다음으로 'col_index_num' 함수를 설정해야 한다. 이 함수는 쉽게 말해 어떤 열에 있는 데이터 값을 가져오는 것인지를 묻는 것이다. 우리가 부서란을 채워야 하는 상황이기에 두 번째 단계(table_array)에서 선택한 범위 중 2번째 열에 해당하기 때문에 '2'를 기입한다.
마지막으로 range_lookup 함수. 여기는 아까 언급했다시피 '일치'하는 값을 넣을 것인지, '유사'한 값을 넣을 것인지를 묻는 것이다. 우리는 아까 기준값을 H5열 '유재석' 데이터로 정해두었다. 이 데이터와 반드시 일치하는 데이터 값이 들어와야 하기 때문에 '0'을 입력해준다. 그럼 자동으로 'FALSE'라고 나오는데 이것이 바로 일치하는 값을 넣겠다는 의미이다. 참고로 유사한 값을 찾을 경우 '1'을 기입하면 된다. 그럼 'TRUE'라고 뜰 것이니 참고하기 바란다.
이제 모든 데이터를 채웠으니 '엔터'를 클릭하여 마무리하자. 그럼 깔끔하게 부서란에 알맞은 부서 데이터명이 입력되어 있을 것이다.
그리고 나머지 데이터는 드래그를 통해 다 채워주면 된다. 참고로 사번 칸을 채울 때는 기본적인 데이터는 다 같다. 하지만 3번째로 채워야 하는 'col_index_num'은 '4'으로 기입해주어야 한다. 아까 설정한 범위에서 4번째 열에 사번이 있기 때문이다. 이 점 잘 참고해서 사용해보도록 하자. 이제 다음으로 hlookup 함수에 대해 알아보자.
#2 엑셀 hlookup 함수 사용법
두 번째로 알아볼 함수는 바로 'hlookup' 함수이다. vlookup 함수는 '열'에 있는 데이터를 찾는 것이라면, hlookup 함수는 '행'에 있는 데이터를 찾는 것이다. 이것 역시 간단한 상황을 가정하고 함께 살펴보도록 하자.
상황 예시] 필자는 (주)정보저장소 회사의 대표자로서 10명의 직원의 급여 현황을 체크하고자 한다. 각 직급별로 기본급과 인센티브를 설정을 했고, 직원 개개인의 기본급과 인센티브 합계를 계산하고자 하나 이것 역시 일일이 대조하며 기입하기 매우 귀찮은 상황이다. 이럴 때는 어떤 함수를 써야 하는가?
아까도 그렇고 뭔가 상황이 조금 우스꽝스럽다. 실제 회사 대표가 단순히 귀찮다는 이유로 안 하는 경우는 거의 없을 것이다. 다만 데이터가 많았지만 함수는 필수적으로 사용해야 시간을 단축하고 보다 정확한 업무처리가 가능하니 이왕 사용할 거 반드시 숙지해두도록 하자. 아 참고로 위 상황의 답은 역시나 hlookup 함수다.
아까와 마찬가지로 함수를 검색을 해보자. 그럼 똑같이 함수란에 hlookup 함수가 검색될 것이다. 그럼 그것을 탭해주자. 그럼 아까와 마찬가지로 lookup_value / table_array / col_index_num / [range_lookup] 순서대로 나온다. 각 함수값에 대한 내용은 아래를 참고자. 물론 내용은 거의 동일하나 혹시나 하니 남겨둔다.
1_ lookup_value : 찾고자 하는 기준값
2_ table_array : 기준값을 기본으로 기준값과 관련된 데이터를 찾기 위한 범위
3_ row_index_num : 2번 범위에서 찾고자 하는 데이터 순서(행)
4_ range_lookup : 일치하는 값(0, FALSE)을 찾을지, 유사한 값(1, TRUE)을 찾을지 선택
우리가 채워야 할 부분 중 유재석 부장의 기본급 칸을 먼저 채워보자. 급여 기준은 우측 표에 정리가 되어 있다. 부장 치고는 조금 작은 급여인 거 같다. 아무래도 스타트업이라 그런가. 어찌 됐건 여기에 hlookup 함수를 넣어주자. 아까 vlookup 함수를 보고 왔다면 너무나 스근하게 눈치를 챘을 것이다. 각 직급을 기준으로 급여가 다르기 때문에 기준값은 당연히 '직급'이 될 것이다. 따라서 기준값을 넣어야 할 lookup_value에는 C5열에 있는 '부장' 데이터를 기준으로 하자.
그리고 두 번째로 기준값을 바탕으로 찾고자 하는 데이터의 범위인 table_array를 선택해야 한다. 여기는 급여 기준 표의 I4부터 L6까지 범위를 선택해준다.
vlookup 함수와 비교했을 때 앞글자만 다른 row_index_num 함수를 채울 차례다. 앞글자만 다르지 기능은 동일하다. 우리가 찾고자 하는 데이터는 유재석 부장의 기본급이다. 따라서 2번에서 선택한 범위에서 2번째 행에 있는 기본급 데이터들이 필요하다. 따라서 여기에는 숫자 '2'를 기입해주자.
그리고 마지막으로 range_lookup 함수를 넣어주자. 당연히 일치하는 값을 찾아야 하니 '0'을 입력하고 제대로 'FALSE'가 뜨는지 확인 후에 엔터를 누르자. 그럼 유재석 부장의 기본급이 우측 표에서 크롤링되어 자동으로 기입되어 있을 것이다. 그리고 나머지 칸은 드래그를 통해 마저 채워보자.
드래그를 완료하니 '#N/A'라고 뜨면서 수식에 오류가 생겼다. 순간 당황한 필자. "어라..? 이렇게 하는 게 아닌가?"라고 했지만, 이내 평정심을 갖고 정확히 1분 48초 만에 해결했다. 원인은 바로 2번째 함수인 table_array에서 드래그 때문에 범위에 오류가 생긴 것이다. 그럴 때는 '고정'을 시켜두면 된다. 원래 드래그를 통해 값을 채우면 칸이 이동할 때마다 거기에 비례하여 함수값도 모두 이동이 된다. 하지만 고정을 해두면 드래그를 하든 뭘 하든 함수에 고정된 값은 고정된 상태로 쭈~욱 유지된다.
원래 윈도우라면 범위를 선택하고 난 후 'F4'를 눌러주면 자동으로 범위가 고정이 되었다. 맥북에서는 어떻게 하는지 몰라서 그냥 범위에 '$' 기호를 다 붙여두었다. 혹시 모를까 봐 우측 사진에 예시를 만들어 두었으니 참고하기 바란다.
완벽하게 모든 상황을 해결한 필자. 이제 나머지 기본급 칸과 인센티브 칸을 마저 채워보자. 참고로 인센티브 칸은 함수를 사용할 때 3번째 함수인 row_index_num 함수에 '3'이 들어가야 한다. 왜냐하면 인센티브는 2번째 함수에서 선택한 범위 중에 3번째 행에 해당하기 때문. 이런 함정들은 잘 피해서 사용하도록 하자.
오늘은 엑셀 함수 모음 1탄 포스팅에 이어 2탄 엑셀 vlookup / hlookup 함수를 사용하여 엑셀을 좀 더 프로답고 간지 나게 사용하는 방법을 배웠다. 이 함수들은 실제로 필자가 직장에서 이것저것 정리할 때 요긴하게 쓰는 함수들이다. 여러분들도 꼭 숙지해두고 취업준비나 직장에서 요긴하게 써먹었으면 좋겠다. 그럼 오늘은 여기까지! 모두들 행쇼!
'#INFO' 카테고리의 다른 글
| AI 일러스트 아웃라인 글자(폰트)깨기 스근하게 하는 방법 (0) | 2019.12.23 |
|---|---|
| 구글 스프레드시트 / 문서 / 프레젠테이션 사용 방법과 공유 방법 (0) | 2019.12.18 |
| 엑셀 함수 모음 1탄 :: 합계(SUM) / 평균(AVERAGE) / 카운트(COUNT) (0) | 2019.12.10 |
| 깔끔한, 심플한 무료 PPT 피피티 템플릿 사이트 망고보드에서 스근하게 다운로드 하자 (0) | 2019.12.05 |
| 카카오톡 용량 줄이기로 카톡 & 핸드폰 저장공간 늘리기 (0) | 2019.12.03 |
댓글