엑셀(EXCEL) 함수 TIP 45 - (두개의 표에서 vlookup으로 중복값찾기, 은행별 정기예금 및 적금금리 표예)
vlookup중복값찾기
아래는 은행별 정기적금과 정기예금금리 표입니다. 아래와 같은 엑셀(EXCEL)표가 있는 경우 좌측의 표를 참고하여 우측을 채우고자 합니다. 즉, 좌측의 K열을 참고하여 우측의 O열에 채워넣고자 합니다. 아래는 몇개가 되지 않아서 하나하나 좌측의 표를 참고하여 우측에 기록하면 됩니다. 하지만 열의 행이 많은 경우에는 불가능하거나 시간이 많이 걸립니다.
열이 많을 경우 마우스로 드래그하여 하단방향으로 이동하면서 찾야야 할 것입니다. 이러한 경우 vlookup함수를 이용하면 간단하게 채울수가 있습니다. 저희 회사에서도 통계작업을 할 때 엑셀을 많이 사용하며, 그 중에 각종 함수사용을 많이 합니다.
은행별 정기적금과 적기예금 금리(세후이자율) 비교 표
vlookup함수대화상자
해당값을 구하고자 하는 셀을 클릭(H3)한 후 함수마법사 클릭(fx), 함수마법사 대화상자에서 함수검색(vlookup) 한 후 vlookup를 선택합니다.
함수인수대화상자
위와 같이 순서대로 하면 함수인수대화상자가 나타납니다. vlookup과 관련하여 4가지 인수가 나타납니다. 영어로 되어 있지만 전혀 겁먹을 필요가 없습니다. 해당 단어의 의미만 알면 쉽게 이해가 됩니다.
첫번째 : lookup_value(두 표에서 공통된 값(단어)를 찾는 열의 첫값)
즉, 좌측표와 우측표에서 은행별 같은 공통된 단어가 있습니다. 예를 들어 우측의 광주은행의 값에 좌측의 광주은행값의 적금금리를 끌고와야 합니다.
두번째 : Table_array(참조하고자 하는 값의 범위)
즉, 우측의 표에 적금금리을 찾아오기 위해서는 참조하고자 하는 범위가 좌측표(은행별정기적금금리)입니다. 좌측표를 마우스로 드래그하면 Table_array에 C2:D15라는 값이 나타납니다. 이를 절대값으로 변환을 해주는데 해당 부분을 클릭하고 절대값변환키인 F4를 한번클릭하면 됩니다. 아래와 같이 $C$2:$D$15로 변하게 됩니다.
이렇게 하는 이유는 나중에 아랫부분에서 마우스로 채우기핸들을 이용하기 때문에 절대값으로 입력이 되어야 하기 때문입니다.
세번째 : Col_index_num(찾아오고자 하는 값들이 몇번째 열에 있는가)
좌측의 참조범위의 표는 2열로 이루어져 있습니다. col이라는 단어는 컬럼이라는 단어의 약자로 기둥이라는 의미입니다. 찾고자 하는 값이 2번째 열에 있기 때문에 2를 기록하면 됩니다. 만약 여기에 1을 기록하게 되면 금리를 찾아오는 것이 아니라 은행을 찾아오게 됩니다.
네번째 : Range_lookup(똑 같은 값을 기록하라)
0이라고 적거나 false라고 기록하면 됩니다. 의미는 똑같은 값을 기록하라는 뜻입니다.
결과표 : 첫열(H3)클릭 후 하단방향으로 드래그하여 채움
H3열에 #N/A라고 기록이 됩니다. 이 부분을 클릭해서 하단방향으로 드래그를 하면 아래와 같이 숫자로 채워집니다. 해당 값이 있는 경우에는 그 값을 끌고와서 기록이 되고 없는 경우에는 #N/A라고 기록이 됩니다. 찾고자 하는 값이 없다는 뜻입니다. 케이뱅크은행, 한국카카오은행, 농협은행의 경우는 좌측표에 없기 때문에 #N/A라고 기록이 됩니다.
좌측은 %로 나타났지만 우측의 적금금리는 소숫점으로 나타났습니다. 이를 셀서식에서 바꿔주면 됩니다.
셀서식 변환하기 : 해당열선택 > 백분율, 자릿수늘림(두번클릭)
vlookup으로 찾은 결과값
