2. 텍스트와 숫자 관련 함수
① value 함수
텍스트를 숫자로 바꿔주는 함수입니다. 텍스트가 아닌 숫자라면 그대로 유지됩니다.
b3셀의 수식은
=VALUE(A3) 입니다.
A3셀이 텍스트로 되어 있지만 숫자만 있으므로 123456으로 잘 표시되고,
숫자인 A2셀의 경우도 value함수를 썼을 때 제대로 표시되지만,
텍스트만 있는 A1셀의 경우는 당연히 value함수를 사용하면 #VALUE! 에러가 발생하는 것이 이해가 가는데,
숫자와 텍스트와 결합된 A4셀의 경우 123이라도 숫자로 바뀌지 않을까 생각되는데, 그렇지 않고 에러가 나는 점 유의하시기 바랍니다.
② text 함수
㉮ 정의
숫자를 일정한 서식의 텍스트로 변환해 주는 함수입니다.
㉯ 서식 #,###과 #,##0의 차이점
C2셀의 수식은
=TEXT(B2,"#,##0")
이며,
컴마를 사용한 서식으로 바꾸는데, #은 숫자가 없으면 표시되지 않고, 0은 0이더라도 0을 반드시 표시해야 한다는 의미입니다. 숫자가 1~9라면 당연히 표시되는데, 숫자가 0일 경우에도 0이라고 표시됩니다.
만약 서식을 #,### 으로 표시하면 0일 경우 빈 칸으로 표시됩니다.
㉰ 소숫점이하 자릿수를 반드시 표시하겠다고 하면
#,##0.00 식으로 표시할 자릿수를 정하면 됩니다.
A3의 소숫점이하 자릿수가 3자리이지만, 텍스트 서식으로 소숫점이하 자릿수를 2로 정했기 때문에 두 자리까지만 표시됐습니다.
만약 A3셀의 숫자가 123456.235라면 일반 숫자의 경우와 마찬가지로 123,456.24로 반올림돼서 표시됩니다.
㉱ 숫자가 아닌 텍스트에 대한 text함수의 적용
숫자가 아닌 텍스트의 경우에도 text함수를 이용해 일정한 서식으로 변환이 가능합니다.
C3셀의 수식이 =text(a3,"#,##0") 인데, 지정된 서식으로 표시되고 있습니다.
㉲ text함수로 변환된 텍스트의 연산이 가능할까요?
답은 Yes 입니다.
D2셀의 수식은 =C2+12, D3셀의 수식은 =C3*38 인데,
모두 계산 결과가 맞습니다.
③ istext, isnontext 함수
㉮ istext 함수는 text인지 여부를 판단하여 true, false값을 반환해주는 함수이며, isnontext 함수는 text가 아닌지 여부를 판단하여 true, false값을 반환해주는 함수입니다. 따라서, 2개는 반대의 결과값을 반환합니다.
E1부터 E4셀까지 istext함수를 사용했고, F1에서 F4셀까지는 isnontext함수를 사용한 것입니다.
결과값 true, false가 반대로 되어 있죠?
㉯ 수식은 문자일까요, 숫자일까요?
D3셀에 =C3*38+C2*27 이라고 입력하고, E3셀에는 =istext(d3), F3셀에 =isnontext(d3) 라고 입력했습니다.
결과값이 어떤가요? 텍스트가 아니라고 False라고 표시됩니다. 엑셀의 장점이 수식과 수식의 결과값이 하나의 셀에서 모두 보인다는 것입니다.
1편에서 '=연산자'의 경우 연산자가 이름인지 텍스트인지에 따라 숫자로 표시되기도 하고, #NAME! 에러가 발생하기도 한다고 했죠? 이와 비슷한 것입니다. =C3*38+C2*27 이라고 길게 텍스트처럼 썼지만, 결과값이 숫자로 나오기 때문에 텍스트가 아닌 숫자입니다.
④ exact 함수
입력된 텍스트 두 개를 비교하여 정확히 같으면 TRUE를 반환하고 그렇지 않으면 FALSE를 반환합니다. EXACT 함수는 대/소문자를 구분하지만 서식 차이는 무시합니다.
위 표는 exact함수를 이용해 텍스트 2개가 일치하는지 비교해 본 것으로 대,소문자를 구분하기 때문에 20행의 경우 FALSE라는 결과값이 나온 것이며, 21행은 같으니까 TRUE인데,
22행이 다른 것은 화면상으로는 보이지 않지만 텍스트2의 뒷 부분에 공백이 있어서 그렇습니다.
23행은 빈 칸이 중간에 있어서 다른 것이고, 24행은 Alt+Enter키를 쳐서 셀 내에서 줄이 바뀌었기 때문에 다른 것입니다. 다만, 25행은 텍스트만 같다면 서식은 비교하는데 영향이 없으므로 TRUE 값이 나온 것입니다.
= 이란 비교 연산자를 이용해서도 글자가 같은지 비교할 수 있는데, 한글은 대소문자 구분이 없으니 문제가 없는데, 영문의 경우 대소문자가 달라도 = 연산자는 TRUE값을 반환하는 점이 exact 함수와 다릅니다.
※ 함수명을 쓸 때 대소문자를 구분하지 않으므로, 편리하게 소문자로 입력하면 됩니다.
⑤ t 함수
텍스트면 텍스트 값을 돌려주고, 텍스트가 아니면 빈칸을 반환합니다.
C2셀은 텍스트, D2셀은 숫자이므로, T함수를 적용하면 G2셀은 값이 보이는데, H2셀은 빈칸이 됩니다.
⑥ n 함수
숫자는 숫자로, 날짜는 해당되는 일련번호로, true는 1, false는 0, 오류는 오류값, 텍스트 등 기타의 경우는 0을 반환합니다.
아래 화면은 Ctrl + `(grave) 키(숫자 1 왼쪽에 있음)를 누르면 나오는데, Ctrl + `는 누를 때마다 수식과 그 결과값간에 변하는 토글 키입니다.
위 수식을 보면 H1셀부터 J2셀까지 모두 n함수를 이용하고 있는데,
숫자인 A2는 숫자로 표시되는데, 숫자처럼 보이지만 텍스트인 A3는 0으로 표시되고, 나머지 A1, A4도 문자이므로 0으로 표시됩니다.
오류값을 표시하고 있는 B1셀을 n함수를 이용해 표시하면 오류값이 그대로 표시되며,이는 i3셀도 같습니다.
날찌인 G1셀의 경우는 1900-01-01부터의 일련번호인 43768(오늘 날짜이므로 날짜가 바뀌면 값도 달라짐)로 표시되었으며, True는 j2셀에 1로, False는 i2셀에 0으로 표시되고 있습니다.
⑦ numbervalue 함수
로컬(나라별 표시형식 차이)에 영향을 받지 않으면서 텍스트를 숫자로 변환합니다. 따라서, 나라에 따라 소숫점과 천단위 등 기호를 입력해야 합니다.
구문은 NUMBERVALUE(text, [decimal_separator], [group_separator]) 이며,
텍스트(셀 주소)를 첫번째 인수로 입력하고, 텍스트에 사용된 소숫점 표시기호를 두번째 인수, 텍스트에 사용된 천단위, 만단위 등 구분을 위한 기호를 세번째 인수로 입력합니다. 두번째 인수와 세번째 인수는 옵션이기 때문에 입력하지 않아도 됩니다.
두번째 인수와 세번째 인수를 모두 입력하거나, 입력하지 않는 것은 되는데, 특이한 것은 세번째 인수를 입력하고, 두번째 인수는 빈칸으로 두면 #VALUE! 에러가 납니다. 기호는 큰 따옴표(" ")안에 넣습니다.
[Ctrl + ` 를 눌러 수식 보기로 전환한 화면]
수식은 PC기준 왼쪽(모바일은 위)에 보이는 것과 같은데,
특이한 것이 몇 가지 있는데,
B29셀의 경우가 위에서 설명한 세번째 인수만 , 로 지정하고, 두번째 인수는 빈칸으로 둔 경우로 #VALUE!에러가 나고,
A30셀의 텍스트내에 한칸공백이 있으므로, B30셀의 경우는 그룹 구분자로 " "(한 칸 공백)을 준 것이 이해가 가는데, B31셀의 경우는 공백이 아닌 ,(컴마)로 줬는데도 숫자로 잘 표시됩니다.
A34셀의 텍스트가 소숫점 표시로 :(콜론)을 사용하고, 그룹 구분자로 _(언더 바) 표시를 사용했기 때문에, B34셀의 수식 입력시 두번째 인수로 :, 세번째 인수로 _ 표시를 사용했고,
B35셀의 경우 구분자가 :과 _인데, .과 ,를 사용했더니 #VALUE! 에러가 났습니다.
이를 종합하면 공백( )과 , 표시는 모두 수용이 되는데, _나 :과 같이 특수한 문자는 반드시 그 문자를 인수에 넣어줘야 하네요.
⑧ isnumber 함수
숫자라면 True, 기타는 False를 반환해주는 함수입니다.
숫자인 A27에서 A29, A32는 True라고 결과값이 표시되었으며,
텍스트인 셀은 False라고 값이 표시되었는데, numbervalue함수를 이용해 숫자로 변환할 수 있더라도 중간에 공백이 있는 경우, '(작은 따옴표)가 있는 경우, 이상한 기호가 들어 있는 경우는 False 입니다.
3. 참고 엑셀 파일
'Excel' 카테고리의 다른 글
| 지번 형식 변환하기 1 (if, not, iserror, or, left, mid, find, substitute 등 함수 이용) (0) | 2020.07.30 |
|---|---|
| rand, randbetween함수, 누계값 구하기 (0) | 2020.07.29 |
| 엑셀, 텍스트와 숫자 다루기(1)(텍스트와 숫자의 구분, 입력 방법, 에러 표시시 조치방법) (0) | 2020.07.23 |
| 엑셀 날짜 관련 함수 총정리(6) (workday.intl, networkdays.intl) (0) | 2020.07.21 |
| 엑셀 날짜 관련 함수 총정리(4) (today, now, weekday, weeknum, yearfrac 함수) (0) | 2020.07.17 |