[구글 스프레드 시트 활용] VLOOKUP, MULTIPLY, LEN, LEFT, RIGHT 함수 활용하여 단위가 포함된 달러값 환산하기

이전 포스팅과 이어집니다.

[구글 스프레드 활용] IF, ISNUMBER, SEARCH, SUBSTITUTE 함수 이용하여 달러와 단위값 분리하기

 

 

소수점의 배신

꼼수 써서 만든 식으로는 소수점까지 커버할 수 없었습니다.

역시 인생은 정직하게 살아야 합니다.

 

다시 함수 식을 짜 봅니다.

숫자와 단위를 표현하는 알파벳을 분리하고,

알파벳에 해당되는 값을 곱해줄 예정입니다.

 

이전 포스팅처럼 IF 식을 사용해도 되지만

오늘은 다른 방식으로 접근해봅니다.

 

=MULTIPLY(LEFT(C7,(LEN(C7)-1)),VLOOKUP(RIGHT(UPPER(C7)),B19:C21,2,false))

 

이 짧은 식에 함수가 6개나 들어있습니다.

이전 포스팅에서 언급한 UPPER 함수를 제외하고

하나씩 설명해드리겠습니다.

 

MULTIPLY 함수

말 그대로 곱하기 함수입니다.

=MULTIPLY(곱할 값,곱할 값)

으로 활용합니다.

 

=곱할 값*곱할 값

함수와 동일한 기능입니다.

 

다만 곱할 두 가지 값에 수식이 많이 들어가기 때문에

있어 보이게 함수를 써봅니다.

 

달러+단위 값이 12.3M이라면

=MULTIPLY(12.3,1000000)

이 두 가지 값을 곱해주는 함수 식으로 만드는 게 최종 목표입니다.

 

*MULTIPLY 식으로 두 개 이상의 값은 곱할 수 없으니,

2개 이상의 곱셈을 원할 경우 PRODUCT 함수를 사용해주세요.

 

LEN 함수

=LEN(글자수를 셀 문자)

한 가지 인수만 있으면 되는 함수입니다.

선택한 셀 또는 입력한 문자의 총 글자 수를 세어줍니다.

공백은 물론 . - 같은 문자까지 글자 수에 포함합니다.

 

예를 들어,

=LEN(123456)

함수의 결과값은

6

입니다.

 

글자 수 세기가 왜 필요하냐, 다음 함수에 대한 설명에서 이어집니다.

 

LEFT, RIGHT 함수

=LEFT(문자열, 반환할 문자의 수)

=RIGHT(문자열, 반환할 문자의 수)

1~2개의 인수가 필요한 함수입니다.

입력한 문자열에서 왼쪽/또는 오른쪽에서부터 n개의 문자를 반환하는 함수입니다.

LEFT 함수는 왼쪽부터, RIGHT 함수는 오른쪽부터 셉니다.

반환할 문자의 수를 적지 않으면 1로 간주합니다.

 

안녕하세요.

라는 A1셀이 있다면

=LEFT(A1)	안	=RIGHT(A1)	.
=LEFT(A1,2)	안녕	=RIGHT(A1,2)	요.

위의 표와 같은 값이 반환되는 함수입니다.

 

이 함수와 LEN 함수를 활용하여 숫자와 단위 값을 분리해줍니다.

1K, 12.3M, 3B

식으로 마지막 한자리에 알파벳이 위치하는 형식으로 값을 입력할 것이기 때문에,

LEN 함수로 전체 자릿수를 계산한 값에서 -1을 해주면 숫자의 자릿수가 계산됩니다.

이 값으로 LEFT RIGHT 함수를 적용하여 숫자와 알파벳을 분리합니다.

 

예를 들어,

A1 셀의 값이 12.3M일 경우

=LEN(A1)

의 결과값은 5입니다.

A1의 마지막 한자리가 단위 값이므로 

=RIGHT(A1)

의 결과값은 M이 됩니다.

LEN 함수를 활용하면

=LEFT(A1,LEN(A1)-1)

의 결과값은 12.3입니다.

 

드디어 숫자와 단위를 분리하는 데 성공했습니다.

 

VLOOKUP 함수

드디어 나왔습니다.

엑셀의 꽃이라는 LOOKUP 함수입니다.

LOOKUP에는 세 가지 종류가 있는데,

LOOKUP, VLOOKUP, HLOOKUP이 있습니다.

오늘은 VLOOKUP만 알아보도록 합니다.

 

=VLOOKUP(검색할 값, 검색을 수행할 범위, 반환될 값의 열 위치)

세 가지 인수가 필요한 함수입니다.

 

직접 써보기 전에는 인수들에 대한 감이 잘 오지 않습니다.

아래와 같은 가격표가 있다고 가정합시다.

 

 

이 경우 VLOOKUP을 활용하여 '가' 상품의 가격을 구할 수 있습니다.

 

=VLOOKUP(B6,A2:B4,2)

 

위의 수식을 적용하면 1000이라는 값이 반환됩니다.

풀어서 설명하자면 아래와 같습니다.

 

=VLOOKUP(상품명이 '가'인 값,검색할 행과 결과값이 있는 행(A2:B4),그 범위 중 앞에서 결과값을 뱉어낼 열의 순서)

 

주의해야 할 사항이 세 가지 있습니다.

1. 첫 번째 인수인 검색할 값은 무조건 선택한 범위에서 첫 번째 열에서만 검색됩니다.

2. 두 번째 인수인 검색을 수행할 범위를 선택할 때,

반드시 결과값이 있는 열까지 포함해서 선택해야 합니다.

3. 검색 결과가 여러 개라면 가장 상단에 위치한 행의 결과값만 내놓습니다.

 

만약 검색된 값을 모두 더하고 싶다면 SUMIF 함수를 활용하도록 합니다.

활용법은 언젠가... 꼭...

 

 

그럼 최종 함수 식을 다시 뜯어봅니다.

=MULTIPLY(LEFT(C7,(LEN(C7)-1)),VLOOKUP(RIGHT(UPPER(C7)),B19:C21,2,false))

=(C7의 전차 문자에서 마지막 한 글자 뗀 숫자에다가),((C7을 대문자로 바꿔서)C7의 오른쪽에서 첫 번째 문자가 얼마인지 단위표에서 찾아서)두개를 곱해

 

로 풀어쓸 수 있습니다.

 

그럼 위의 수식을 적용해봅니다.

 

야호

 

 

그런데 다 만들고 보니,

식의 규칙을 모르는 사람이 실수할까 봐 걱정됩니다.

 

그런 상황을 대비해 안내문구를 만들어줍시다.

안내문구 만들기는 다음 포스팅에 계속됩니다.