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

 

앞의 두 포스팅과 이어집니다

[구글 스프레드 시트 활용] 한화를 달러로 변환 후 달러에 빌리언Billion, 밀리언Million, 킬로(thousand, Kilo) 적용하기

 

[구글 스프레드 시트 활용] 달러 한화로 계산하기 (빌리언Billion, 밀리언Million, 킬로(thousand, Kilo) 포함)

 

 

어제 글 마무리에 말씀드린대로

1M이라고 쓰면 1,000,000값을 뱉어내는 식을 만들어보겠습니다.

오늘은 드디어 엑셀답게 함수를 사용합니다.

 

 

우선, 어제 완성한 시트를 복사해줍니다.

필요한 자료는 비슷하니까요.

 

복사를 원하는 시트의 이름 옆에 삼각형을 오른쪽 클릭하면 설정창이 뜹니다.

여기서 '복사'를 눌러줍니다.

 

다음으로 복사를 누르면 기존 스프레드에 복사, 새 스프레드에 복사가 뜹니다.

기존 스트레드에 복사는 내 드라이브 내에 존재하는 다른 구글 스프레드 문서에 붙여넣을 수 있는 기능이고,

새 스프레드에 복사는 해당 시트를 복사하여 새 스프레드 문서를 만들어주는 기능입니다.

 

 

12K, 123M, 1B 등을 적으면 달러로 만들어주는 방법을 생각해봅니다.

숫자 뒤에 붙은 K, M, B를 각각 숫자0 3개, 6개, 9개짜리로 바꿔주면 됩니다.

우와 너무너무 쉽다

 

우선 단위+달러값을 쓸 셀 영역을 만들어줍니다.

복사붙여넣기를 하면 수식이 깨지므로 드래그해서 옮겨줍니다.

 

드르륵

그리고 달러+단위값 셀을 만들어줍니다.

 

이왕 하는 김에 서식복사 복습도

 

이제 달러값을 구해보겠습니다.

오늘은 갑자기 난이도가 높아지므로, 최대한 차근차근 설명해드리도록 하겠습니다.

 

달러 결과값 칸에 삽입할 함수식은 아래와 같습니다.

 

=IF(ISNUMBER(SEARCH(B16,UPPER(C8))),SUBSTITUTE((UPPER(C8)),"K","000"),IF(ISNUMBER(SEARCH(B17,UPPER(C8))),SUBSTITUTE((UPPER(C8)),"M","000000"),IF(ISNUMBER(SEARCH(B18,UPPER(C8))),SUBSTITUTE((UPPER(C8)),"B","000000000"))))

으악 이게 뭐야

 

사용된 함수를 하나씩 뜯어봅니다.

 

IF 함수

=IF(논리식, True값, False값)

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

논리식에 따라 True에 해당하는 값, False에 해당하는 값을 뱉어냅니다.

 

1) 논리식은 말 그대로 논리식입니다.

A=0, A>8, A>=-1 같은 수식입니다.

수식 뿐 아니라 A=A 같은 텍스트끼리 비교하는 논리식도 가능합니다.

 

2)True값

논리식 값이 참일 경우에 셀에 표시될 결과값입니다.

A=0이라는 식일 경우, A가 0일경우 True, 0이 아닌 모든 수는 False 입니다.

 

3) False값

논리식 값이 거짓일 경우에 셀에 표시될 결과값입니다.

 

예를 들어,

=IF(A1>0, 참, 거짓)

이라는 식이 있을 경우,

A1셀의 값이 1일 경우 결과값은 참,

-1일 경우 결과값은 거짓입니다.

 

그런데 이 3번째 인수인 False 값에 IF 함수를 한번 더 사용해서 다중 IF 함수로 사용이 가능합니다.

A1이 0보다 작아? 그럼 다음 논리 나와!

같은 개념입니다.

 

우리가 사용할 식에서는 얘가 K인지, M인지, B인지 구분이 필요하기 때문에 IF 식이 3개 필요합니다.

 

 

ISNUMBER 함수

=ISNUMBER(값)

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

값이 숫자면 True, 숫자가 아니면 False를 뱉는 함수입니다.

 

🙋‍♀️잠깐! 우리는 알파벳을 찾는데 왜 숫자를 판단하는 식이 나오죠?

 

이유는 바로 다음 함수에 대한 설명에서 이어집니다.

 

 

SEARCH 함수

=SEARCH(검색하려는 문자, 검색할 텍스트, 검색을 시작할 텍스트 자릿수)

2~3가지 인수가 필요한 함수입니다.

검색할 텍스트에서 검색하려는 문자가 위치한 자릿수를 숫자로 반환합니다.

*인수 값을 입력할때, 셀 선택이 아니라 직접 문자를 입력할 경우 큰따옴표 안에 넣어줍니다.

 

예를들어,

=SEARCH("그때", "그때그때")

라는 식이 있으면

결과값은 1입니다.

 

그런데 만약 이 식을

=SEARCH("그때", "그때그때", 2)

로 변경한다면,

결과값은 3입니다.

 

만약 검색할 텍스트 내에 검색하려는 문자가 없다면 #VALUE! 라고 신경질을 부립니다.

 

이 SEARCH 함수의 TRUE 결과값이 숫자이기 때문에, ISNUMBER 함수가 필요한 것입니다.

이 셀 안에 K, M, B가 있다면 숫자값을 반환해줄테니까요.

 

UPPER 함수

=UPPER(값)

으로 사용하는 함수로,

값 안에 들어있는 알파벳을 모두 대문자로 바꿔줍니다.

 

비교할 단위값을 소문자 m(ex. 123m)으로 적더라도 자동으로 대문자로 바뀌기 때문에

SEARCH 함수 적용이 가능해집니다.

(구글 스프레드/엑셀식은 대소문자를 구분합니다.)

 

SUBSTITUTE 함수

=SUBSTITUE(검색할 텍스트, 대체될 텍스트, 대체할 텍스트)

로 구성된 함수입니다.

 

예를 들어,

=SUBSTITUE("하이퀄리티", "하이", "로우")

라는 식이 있으면

결과값은 로우퀄리티 입니다.

 

이 함수를 이용해서 K를 000으로 변경할 수 있습니다.

 

그럼 첫번째 IF 함수절을 다시 뜯어봅시다

 

=IF(ISNUMBER(SEARCH(B18,UPPER(C8)),SUBSTITUTE(UPPER(C8)),"K","000"), IF(~

=IF(C8셀 안에 K 있냐?(잠깐, C8 셀 안에 든거 대문자로 바꿔봐), 있으면 K를 000으로 바꿔, 없으면 다음놈 나와)

 

이렇게 구성되어 있는 IF 함수입니다.

이걸 3번 반복해줍니다.

 

그렇게 완성된 식이

=IF(ISNUMBER(SEARCH(B16,UPPER(C8))),SUBSTITUTE((UPPER(C8)),"K","000"),IF(ISNUMBER(SEARCH(B17,UPPER(C8))),SUBSTITUTE((UPPER(C8)),"M","000000"),IF(ISNUMBER(SEARCH(B18,UPPER(C8))),SUBSTITUTE((UPPER(C8)),"B","000000000"))))

이 어마무시한 식입니다.

모아놓고 보면 무서워보이는데

생각보다 별거 아닌 놈입니다.

 

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

 

짜릿해!!

기특해!

 

 

다만! 이 수식에는 커다란 단점이 있습니다.

소숫점 단위가 포함된 숫자의 경우 올바른 계산식이 적용되지 않습니다.

(ex. 123.45K의 경우 123.45000의 결과값이 반환되므로, 123달러로 취급됩니다.)

 

이러한 문제를 방지하려면 숫자와 단위값을 분리하고, 분리된 값을 각각 곱해야합니다.

관련된 함수식은 다음번에 포스팅 해보도록 하겠습니다.

거듭 말씀드리지만 아주 약간... 정말 조금의... 삶의 질 상승이 목표니까요.

 

이전 포스팅에 말씀드렸던,

구글 스프레드 시트에는 NUMBERSTRING이 없어서 직접 함수식 짜야 하는

한화의 결과를 한글 단위로 바꾸는 방법은 다음 포스팅까지 연구해오겠습니다.

▼아래의 포스팅에서 확인 가능합니다.

 

[구글 스프레드 시트] 숫자를 한글로 변환하여 표현하기(스크립트 편집기를 사용하여 스크립트 적용하기)