오늘부터 엑셀 강의(노하우)를 하루에 하나씩 올려보도록 노력해 보겠습니다. ㅋㅋㅋ
(과연 해낼 수 있을지? ㅠㅠ)
컴퓨터활용능력이나 다양한 엑셀 시험과는 별도로 정말 실무에서 필요한 기술 위주로 진행할 예정이구요.
저는 컴퓨터활용능력 자격증을 보유하고 있으며(매우 어렸을 적에 2급 취득... 1급은... 넘나 빡쎔 ㅠ), 회사에서 7년 간 엑셀만 했다고 봐도 무관한 경력(?)을 가지고 있음을 알려드립니다.
물론 저보다 훨씬 엑셀 잘하시는 분이 많겠지만 제 미천한 기술이 저보다 경험이 부족하신 분들에게 도움이 될거라는 믿음 하에 포스팅을 시작합니다!
첫 시간은 VLOOKUP 함수와 상대참조, 절대참조 개념에 대해서 설명드리겠습니다.
우선 제가 설명드리는 부분은 기본적으로 엑셀에 대해서 기본은 하실 줄 아는 분을 대상으로 합니다.
VLOOKUP함수 정도는 아무것도 아니라는 분들도 많지만, 제가 회사 생활하면서 가장 많이 사용한 함수가 바로 VLOOKUP과 IF입니다.
업무에 따라 다르겠지만 VLOOKUP함수를 사용할 일이 참 많습니다.
왜냐하면 대부분의 DATA들을 여러 곳에서 뽑고, 그걸 취합해서 자료를 만드는 경우가 많기 때문이지요.
VLOOKUP은 한 열을 기준으로 해서 다른 열의 정보를 가져오는 함수입니다.
예를들면서 설명드리겠습니다.
아래와 같이 Sheet1과 2가 있습니다.
Sheet2에는 다양한 정보들이 있고, Sheet1에는 상대적으로 적은 정보들이 있습니다.
이제부터 제가 하고 싶은 행위는 Sheet2의 정보를 이용해 Sheet1의 빈 칸(거래처 및 판매가격)을 채우려고 합니다.
사실 위 사례같은 경우에는 정보의 갯수 자체가 적기 때문에 Sheet1에서 요구되는 대상의 정보를 외워서 Sheet2에서 가져오면되는데요. 만약 Sheet2의 정보가 몇 백, 몇 천, 몇 만 건이라면 그러한 행위를 하는게 어려워집니다. (물론 채워야할 대상이 몇개 안되면 『찾기』 기능을 활용할 수도 있습니다.)
우리는 VLOOKUP이라는 함수를 이용해 빈칸을 채워보도록 합시다.
C3에 커서를 위치하고 메뉴의 『수식』에서 『함수삽입』을 눌러 『VLOOKUP』 함수를 검색 후 선택합니다.
첫번째 항목인 Lookup_value는 Sheet1의 제품명 열입니다. 다시말해 기준이 되는 열입니다.
두번째 항목인 Table_array는 Sheet2의 첫번째 행을 제외한 전체 Table입니다. 다시말해 내가 정보를 가져올 소스가 되는 범위입니다.
세번째 항목인 Col_index_num은 두번째 항목인 Table_array의 선택된 범위에서 내가 필요한 열이 몇 번째 열인지를 입력하는 것입니다.
마지막으로 Range_lookup은 지정한 열에서 값이 정확히 일치하는 것을 불러올 것인지 비슷하게 일치하는 것을 불러올 것인지를 선택하는 것으로 'FALSE' 아니면 'TRUE'만 입력할 수 있습니다. 일반적으로 'FALSE'를 쓸 경우가 훨씬 많습니다.
이렇게 입력을 하면 C3에 다시멸치의 거래처로 '광주상회'가 정확히 불러와지는 것을 알 수 있습니다.
이제 여기서 다시멸치의 판매가격과 쥐포의 거래처, 판매가격을 불러오기 위해
C3의 수식을 아래로, 옆으로 드래그해서 채워넣으면 아래 그림처럼 에러가 납니다.
이렇게 아래로, 옆으로 드래그했을 때 수식이 에러나거나, 이상한 값이 나오는 이유가 바로
상대참조, 절대참조에 대한 이해가 부족하기 때문입니다.
위에 보시는 것처럼 쥐포의 거래처 C4의 수식을 보게되면 Sheet2의 범위가 B3:G10에서 B4:G11로 변경된 것을 알 수 있습니다. 이게 바로 상대참조입니다. 행 또는 열이 밀린만큼 수식이 걸린 대상도 그 만큼 범위가 변경되는 것이지요. 그러면서 Sheet2에서 쥐포가 포함된 범위가 아래로 밀리면서 에러(#N/A)가 발생한 것입니다.
D3도 마찬가지입니다. C3의 수식을 D3에 드래그하면서 VLOOKUP수식의 참조부분들이 다 변경되었습니다. 기준이 되는 첫번째 항목도 B3가 아닌 C3로 변경되었구요. (옆으로 한 칸 옮긴만큼 수식의 범위도 한 칸 옮겨졌음), 기준이 되는 범위도 Sheet2의 B3:G10에서 C3:H10으로 변경되었습니다. 이러한 문제도 있지만 여기선 VLOOKUP함수에서 세번째 입력값인 Col_index_num이 2로 되어있어서 발생한 문제이기도 합니다. 판매가격의 경우 Sheet2에서 2번째가 아닌 5번째에 위치하기 때문에 2가 아닌 5를 입력해야합니다.
상기사례에서 원하는 값을 얻기위해서는 절대참조 기능을 활용해야합니다.
절대참조($)를 하기위해선 수식에서 F4키를 누르면 됩니다. 위 사례의 경우 제품명을 기준으로 Sheet2에서 정보를 가져오는 것이기 때문에 제품명이 입력된 B열만 절대참조로 지정합니다. (절대참조의 경우 열과 행 모두를 절대값으로 지정할 수 있는데, 그 기준은 $의 위치로 구분됩니다. $B3이라고 한다면 B열만 절대위치로 고정된 것이고, $B$3이라고 한다면 B3라는 위치가 절대위치로 고정된 것입니다. B$3라고 한다면 3행이 절대위치로 고정된 것입니다.)
그래서 VLOOKUP의 첫 번째 입력값이 $B3로 지정되었고, Sheet2에서 지정되는 범위는 DATA가 존재하는 범위로 이미 정해져 있기 때문에 행과 열을 한 번에 고정시켜 『Sheet2!$B$3:$G$10』 이렇게 범위를 지정하면 수식을 어디든지 드래그하더라도 DATA를 불러오는 범위는 항상 동일하게 됩니다.
위와 같이 수식을 절대참조 기능을 이용하여 변경하면 위와 같이 드래그해서 올바른 값이 출력되도록 변경할 수 있습니다.
이 부분은 아무래도 직접 연습을 해보면서 익히는게 무엇보다 중요합니다.
실무에서 제 포스팅을 참고해서 지속적으로 연습을 해보시면 확실히 익히실 수 있을 것이라고 생각합니다.
혹시나 파일이 필요하실까 싶어 파일도 첨부합니다.
이해가 안가거나 궁금하신 게 있으시면 언제든지 댓글을 달아주세요.
감사합니다.
※ 도움이 되셨다면 공감 버튼을 눌러주세요! :)
'(엑셀) 노하우' 카테고리의 다른 글
| (엑셀) SUMIF 함수 사용법 (0) | 2020.11.28 |
|---|---|
| (엑셀) COUNTIF 함수 사용법 (0) | 2020.11.28 |
| (엑셀) 그래프 작성 관련 팁(혼합그래프, 누적막대, 꺾은선형) (0) | 2020.10.14 |
| (엑셀) MATCH, INDEX 함수 사용법 - 고급편 (2가지 조건을 만족하는 값 찾기) (0) | 2020.10.14 |
| (엑셀) MATCH, INDEX 함수 사용법 - 기본편 (0) | 2020.10.14 |