구글 스프레드시트 함수 vlookup, hlookup, unique

자동으로 값 찾아서 불러오기 함수 vlookup, hlookup

중복값 제외하고 리스트 정리해주는 함수 unique

1. vlookup

어디서 많이 본 함수 일것같습니다. sum 만큼 많이 쓰이는 함수 입니다.

원하는 특정데이터를 다중조건으로 추출할 수 있습니다.

ifs는 다중조건으로 불러온 값을 특정 수식에 의해 계산하는데 많이 쓰이는 함수이고,

vlookup도 다른 함수와 중첩으로 쓰면 그런 형식으로 사용가능하지만, 주로 단일 값을 불러오는데 사용합니다.

 

vlookup(검색 값, 검색범위, 불러올 열의 순서값, 정렬)

 

위 함수를 사용하여 발주서 형식을 만들어 보겠습니다.

왼쪽 단가표를 활용하여 오른쪽 발주서에 품목만 적으면 알아서 가격이 뜨게끔 해보겠습니다.

발주서의 구매항목과 수량은 작성자가 직접 작성을 해야 합니다.

그렇다면 구매항목을 입력했을 경우, 자동으로 단가가 불러와지는 함수를 적용시켜 보겠습니다.

함수를 분석해보면, F3에 있는 품목의 개당 가격을 불러오고 싶으니 A열에서 F3값이 있는 행을 찾아 해당하는 행에서 3번째에 있는 값을 불러와라. 이 뜻입니다. 이 때 검색을 하는 범위는 A3에서 D10이 되는 것이고, 이 범위는 검색할 값이 포함되게 지정해줘야합니다. 

몇 번째 값을 불러올것인지의 숫자는 설정된 범위의 첫번째 열 부터 입니다.

A열 1, B열 2, C열 3 입니다.

 

자, 이제 사과의 단가가 불러와졌습니다. 이제 수량을 입력하면 총 매입가가 계산되게 해보겠습니다.

이건 대단한 함수도 필요 없습니다. 단 이제 앞서배운 여러함수를 미리 적용하여 

품목이 빈칸일 경우엔 셀을 비워놓고, 값이 입력되는 경우 수량*단가가 입력되게 해보겠습니다.

 

=if(f3="","",g3*h3)

 

이제는 바로 아시죠? F3셀이 공백이면 빈칸으로 두고, 값이 있다면 G3과 H3이 곱해진 값을 불러와라.

자 그러면 단가와 매입가 함수는 쭉 드래그해서 아래에도 같이 적용시켜주고, 총 발주금액에 SUM 함수를 써서

합계 금액을 계산해보겠습니다.

 

함수를 드래그해서 적용을 시킬 때, VLOOKUP 함수범위가 A3:D10으로 숫자가 들어가있기 때문에 그냥 그대로 드래그 하게 되면 A4:D11 처럼 한칸씩 밀리게 됩니다. 이것도 앞에서 배웠죠? $를 사용하여 A$3:D$10으로 고정을 시켜놓고 드래그 해주시면 됩니다.

 

총 발주 금액 함수는 이제는 바로 아실겁니다. 

 

=SUM(I3:I9)

 

오류가 나면 IFERROR 함수를 추가해보세요.

 

그리고 구매항목을 일일이 쓰지 않고, 목록에서 선택하고 싶다 하는 경우에도 방법이 있습니다.

 

2. hlookup

vlookup과 기본 개념, 활용 방법등은 동일합니다.

차이라하면 세로와 가로, 행과 열의 차이 입니다.

vlookup은 vertical 즉 세로로 몇 번째에 있는 값을 불러와라, 라는 명령어라면

hlookup은 horizon 즉 가로로 몇 번째에 있는 값을 불러와라, 라는 명령어 입니다.

많이 쓰는 함수는 아닙니다만 혹 사용하고 싶으시면 셀에 =HLOOKUP 만 써도 설명이 보일겁니다.

 

3. unique

유니크 함수는 엑셀에서는 활용하기가 어려웠습니다. 세상 편한 함수 입니다.

거래처 월 매입장을 받았을 때, 한 달간 구매한 품목을 중복없이 보고 싶을 수 있습니다.

UNIQUE 함수로 중복없이 품목리스트를 불러와서, 한 달간 각 품목의 구매 수량과 총 구매 금액을 통계 낼 수 있습니다.

 

=UNIQUE(원하는 범위) 보통은 한 열, 한 행만 기입합니다.

 

A:A를 범위로 지정하면 A열에 있는 값들을 정리하여 중복없이 세로로 나열해줍니다.

A1:G1을 범위로 지정하면 1행에 있는 값들을 정리하여 가로로 나열해줍니다.

 

4. 응용

 

위에 잠시 설명했듯이, unique 함수를 사용하여 품목을 중복없이 리스트업하고 sumif 함수를 사용해

월별 구매 수량, 월별 구매 금액 등을 정리하여 월 통계를 내 볼 수 있습니다.

 

그리고 은행내역을 정리하는데 한 달에도 여러번 불규칙 적이나 반복적으로 거래가 발생하는 각 매입처, 매출처에 분류를 지정해서 분류별 매입, 매출액을 보고 싶다 할 수 있습니다.

 

그런 경우 은행내역 데이터가 있는 범위 옆으로 unique 함수로 매입, 매출처를 중복없이 추출하고 그 옆에 분류를 써줍니다. 그리고 은행내역 데이터표에서 매입, 매출처 옆에 한 열을 추가하여 vlookup을 사용하여 자동으로 분류가 입력되게끔 할 수 있습니다.

 

vlookup 활용

unique 활용