숫자 1234.5를 1,235로 보이게 하는 방법은 두 가지입니다. 셀 서식에서 표시 형식을 바꾸는 쪽과 TEXT 함수로 새 값을 만드는 쪽인데, 화면에 뜨는 글자는 똑같이 1,235라 처음에는 차이가 안 보입니다.
차이는 그 뒤에 드러납니다. 서식을 바꾼 셀은 여전히 1234.5라는 숫자고, TEXT로 만든 값은 「1,235」라는 다섯 글자짜리 글자입니다. 그래서 한쪽은 더하면 합계가 나오고 다른 쪽은 0이 나옵니다.
어느 쪽을 써야 하는지는 그 값을 다음에 무엇에 쓸 것이냐로 갈립니다. 계산에 다시 넣을 값이면 서식, 다른 문장에 끼워 넣을 값이면 TEXT입니다. 이 한 줄이 판단의 전부인데, 실제 데이터에서는 중간이 꽤 많습니다.
목차
하나는 값을 바꾸고 하나는 보이는 것만 바꾼다
같은 화면, 다른 속
셀 서식은 옷을 갈아입히는 일입니다. 셀 안에 들어 있는 숫자는 그대로 두고, 그 숫자를 화면에 어떻게 그릴지만 정합니다. 천 단위 쉼표를 넣든 소수점을 없애든 원화 기호를 붙이든 셀이 품고 있는 값은 1234.5 그대로입니다.
TEXT 함수는 새 값을 하나 만드는 일입니다. 원래 숫자를 읽어서 정해 준 형식대로 글자를 조립해 내놓습니다. 그 결과는 숫자가 아니라 글자라서, 더하기에 들어가면 없는 값 취급을 받습니다.
| 보는 곳 | 셀 서식 | TEXT 함수 |
|---|---|---|
| 셀에 든 것 | 숫자 1234.5 | 글자 1,235 |
| 더하면 | 합계에 들어간다 | 0으로 센다 |
| 정렬하면 | 크기 순으로 선다 | 글자 순으로 선다 |
| 기본 맞춤 | 오른쪽 | 왼쪽 |
| 원래 값으로 | 서식만 되돌리면 된다 | 숫자로 다시 바꿔야 한다 |
이 차이가 눈에 띄는 가장 빠른 자리는 셀을 선택했을 때의 수식 입력줄입니다. 서식을 준 셀은 입력줄에 원래 숫자가 그대로 떠 있고, TEXT로 만든 셀은 입력줄에 수식이, 셀에는 완성된 글자가 보입니다. 화면 아래 상태 표시줄도 같은 것을 말해 줍니다. 합계가 뜨느냐 개수만 뜨느냐로 한 번 더 확인할 수 있습니다.
한 줄로 가르는 기준
그 값을 사람이 읽고 끝낼 것이면 서식, 다른 글자와 이어 붙여 하나의 문장이나 라벨로 만들 것이면 TEXT입니다. 이어 붙이는 순간 어차피 글자가 되기 때문에, TEXT는 그 자리에서만 이득을 냅니다.
셀 서식으로 가는 자리
계산에 다시 들어갈 값이면 여기
표에 들어 있는 금액, 수량, 비율, 날짜는 거의 전부 서식 쪽입니다. 합계를 내고 평균을 내고 피벗으로 묶을 값들이라, 숫자인 채로 두는 것이 나중에 할 일을 줄여 줍니다.
서식은 Ctrl + 1을 눌러 셀 서식 창을 열고 「표시 형식」 탭에서 정합니다. 기본 목록에 없는 모양은 「사용자 지정」에서 직접 씁니다. 여기서 쓰는 형식 코드가 TEXT 함수의 두 번째 인수와 같은 문법이라, 한 번 익혀 두면 양쪽에 다 씁니다.
1천 단위 쉼표와 소수점 자리
#,##0은 소수점 아래를 반올림해 정수로 보이게 하고, #,##0.00은 두 자리까지 보여 줍니다. 값은 어느 쪽에서도 바뀌지 않습니다.
2단위를 글자로 붙일 때
#,##0"원"처럼 큰따옴표로 감싸 붙이면 화면에는 단위가 따라오지만 셀은 여전히 숫자입니다. 셀에 직접 원을 타이핑하는 것과 결과가 완전히 다릅니다.
3값에 따라 모양을 바꿀 때
세미콜론으로 구간을 나누면 양수·음수·0을 다르게 그립니다. 마이너스를 괄호로 보여 주는 회계 표기가 이 구조로 만들어집니다.
형식 코드는 외우는 것이 아니라 필요할 때 찾아 쓰는 쪽이 낫습니다. 기본 목록에서 비슷한 모양을 하나 고른 뒤 사용자 지정으로 넘어가면 그 코드가 입력칸에 들어와 있어, 거기서 한 글자씩 고치는 편이 빠릅니다.
서식은 셀에 붙는 성질이라 값을 지워도 남습니다. 다른 곳에서 값만 복사해 붙여 넣으면 원래 서식이 따라오지 않고, 그래서 숫자가 갑자기 다른 모양으로 보이는 일이 생깁니다.
TEXT 함수로 가는 자리
다른 글자와 이어 붙일 값이면 여기
TEXT가 필요해지는 순간은 정해져 있습니다. 숫자를 다른 글자와 이어 붙이려 할 때입니다. 「A상사 1234.5」처럼 이어 붙이면 서식이 사라진 날것 그대로가 따라 나오기 때문에, 그 자리에서 모양을 입혀야 합니다.
날짜가 특히 그렇습니다. 날짜는 속으로 일련번호라, 그냥 이어 붙이면 45566 같은 숫자가 문장 한가운데 튀어나옵니다. TEXT로 감싸면 2024-10-01처럼 읽을 수 있는 모양이 됩니다.
1보고서 제목과 안내 문장
기준일이나 합계를 문장에 넣는 자리입니다. 값이 바뀌면 문장도 같이 바뀌니 매번 손으로 고칠 일이 없어집니다.
2자릿수를 맞춘 코드 만들기
0000 같은 형식으로 감싸면 앞자리에 0이 붙은 네 자리 코드가 나옵니다. 숫자로는 표현할 수 없는 모양이라 여기는 TEXT밖에 길이 없습니다.
3다른 시스템에 넘길 파일
받는 쪽이 글자 형태를 요구하는 경우입니다. 날짜 여덟 자리나 고정 길이 금액처럼 모양 자체가 규격인 자리라면 TEXT로 고정합니다.
거꾸로 말하면 이 세 자리가 아닌 곳에 TEXT를 쓰면 손해입니다. 표 안의 금액에 TEXT를 씌워 두면 그 열로는 더 이상 계산을 못 하고, 나중에 계산이 필요해질 때 숫자로 되돌리는 일부터 하게 됩니다.
예제 파일에서 1234.5를 #,##0으로 감싼 결과의 글자 수를 세어 보면 다섯입니다. 쉼표까지 글자로 들어가 있다는 뜻이고, 이 값을 나중에 다시 숫자로 쓰려면 쉼표부터 떼어 내야 한다는 뜻이기도 합니다.
합계가 1 어긋나 보이는 이유
보이는 숫자를 더한 값과 실제 합계
서식을 쓰면 반드시 한 번은 만나는 장면이 있습니다. 화면에 뜬 숫자를 눈으로 더한 값과 합계 셀에 나온 값이 1 차이가 나는 일입니다. 예제 파일의 금액 세 줄이 정확히 그 상황입니다.
원래 값은 1234.5와 980과 12345.678입니다. 정수로 보이게 서식을 주면 화면에는 1,235 · 980 · 12,346으로 뜹니다. 이 셋을 눈으로 더하면 14,561인데, 합계 셀은 14,560으로 나옵니다.
합계가 틀린 것이 아닙니다. 엑셀은 보이는 값이 아니라 원래 값을 더하기 때문에 실제 합은 14560.178이고, 그 값에 같은 서식이 얹히면서 14,560으로 보이는 것입니다. 반올림은 각 줄에서 한 번, 합계에서 또 한 번 따로 일어납니다.
주의 — 「표시된 값에 맞추어」 옵션은 파일 전체를 바꿉니다
엑셀 옵션에 이 차이를 없애 주는 설정이 있는데, 켜는 순간 그 통합 문서에 들어 있는 모든 숫자가 보이는 자릿수로 잘립니다. 되돌려도 이미 잘린 값은 돌아오지 않습니다. 합계 한 줄 때문에 켤 만한 스위치가 아닙니다.
보이는 값끼리 딱 맞아야 하는 보고서라면 각 줄을 ROUND로 감싸 값 자체를 정리하는 쪽이 안전합니다. 예제의 세 줄을 각각 ROUND로 정수로 만든 뒤 더하면 14,561이 나오고, 그때는 화면과 합계가 같은 숫자를 말합니다.
바꾸고 나서 달라지는 것들
TEXT로 넘어간 열에서 벌어지는 일
TEXT로 만든 열은 겉보기에 멀쩡한 숫자 열이지만, 그 뒤에 이어지는 작업이 하나씩 어긋나기 시작합니다. 어떤 자리에서 어긋나는지 알고 쓰면 문제가 아니고, 모르고 쓰면 원인을 찾는 데 한참 걸립니다.
| 무엇을 할 때 | 어떻게 되나 | 넘어가는 길 |
|---|---|---|
| SUM으로 더할 때 | 0이 나온다 | 원래 숫자 열을 따로 두고 그쪽을 더한다 |
| 큰 순으로 정렬할 때 | 9가 10보다 뒤로 간다 | 숫자 열을 기준 열로 잡아 정렬한다 |
| VLOOKUP으로 찾을 때 | 숫자 키와 안 맞아 오류가 난다 | 양쪽을 같은 종류로 맞춘다 |
| 피벗으로 묶을 때 | 합계 대신 개수가 잡힌다 | 값 필드 설정을 바꾸기 전에 원본부터 본다 |
| 차트로 그릴 때 | 전부 0인 막대가 나온다 | 데이터 범위를 숫자 열로 잡는다 |
표의 두 번째 줄이 특히 잘 안 보입니다. 글자로 정렬하면 값을 한 글자씩 앞에서부터 견주기 때문에 9는 1보다 크다는 이유로 10 뒤에 섭니다. 줄 수가 적으면 눈으로 잡히지만, 수백 줄짜리 표에서는 순서가 이상하다는 느낌만 남고 원인이 안 보입니다. 정렬 결과부터 의심하지 말고 그 열이 무엇인지부터 봅니다.
숫자인지 글자인지 3초에 보는 법
맞춤을 건드린 적이 없다면 오른쪽에 붙은 것이 숫자, 왼쪽에 붙은 것이 글자입니다.
확실하게 보려면 그 열을 통째로 선택해 화면 아래 상태 표시줄을 봅니다. 합계가 뜨면 숫자고, 개수만 뜨면 글자입니다.
이런 데이터를 만나면
깨끗하지 않은 열에서 갈리는 지점
실무에서 받는 열은 한 종류로 통일돼 있지 않습니다. 시스템에서 내려받은 파일은 숫자처럼 보이는 글자가 섞여 있고, 사람이 손으로 채운 열은 단위와 쉼표가 값 안에 그대로 박혀 있습니다. 이런 열에는 서식도 TEXT도 바로 들어가지 않습니다.
서식을 줬는데 화면이 그대로면 그 셀은 글자입니다. 서식은 숫자에만 걸리기 때문에, 아무 변화가 없다는 것 자체가 이미 답입니다. 이럴 때는 서식을 다시 고르지 말고 값부터 숫자로 되돌립니다.
수식 입력줄과 셀을 나란히 본다
셀에는 1,235인데 수식 입력줄에 1234.5가 떠 있으면 서식이 걸린 숫자입니다.
양쪽 모두 1,235면 그 쉼표는 값의 일부인 글자입니다. 이 한 번의 비교로 대부분 갈립니다.
숫자로 되돌리는 길은 여럿인데, 열 전체라면 텍스트 나누기가 가장 빠릅니다. 데이터 탭에서 열을 선택하고 마법사를 열어 아무것도 바꾸지 않은 채 마침을 누르면, 엑셀이 각 값을 다시 읽으면서 숫자로 인식되는 것은 숫자로 바꿔 놓습니다. 텍스트 나누기로 한 칸에 붙은 값 가르기에서 이 마법사의 각 단계가 무엇을 하는지 다뤘습니다.
값 안에 단위 글자가 박혀 있으면 그것부터 떼어 내야 숫자로 돌아옵니다. 여기서 순서를 뒤집는 사람이 많은데, 서식을 먼저 주고 나중에 값을 고치면 서식은 이미 걸려 있으니 두 번 일하게 됩니다.
고를 때 보는 순서
위에서부터 답하면 한쪽으로 떨어진다
아래 그림은 같은 값 하나를 두고 서식 쪽과 TEXT 쪽이 각각 어떤 화면을 만드는지, 그리고 어디를 보면 지금 어느 쪽인지 알 수 있는지를 한 장에 모은 것입니다.
위에서부터 답해 본다
□ 이 값을 나중에 더하거나 평균 낼 일이 있나 — 있으면 서식
□ 다른 글자와 이어 붙여 한 문장으로 만들 값인가 — 그러면 TEXT
□ 앞자리 0처럼 숫자로는 못 만드는 모양인가 — 그러면 TEXT
□ 피벗이나 차트의 원본이 될 열인가 — 그러면 서식
□ 서식을 줬는데 화면이 안 바뀌나 — 값부터 숫자로 되돌린다
□ 둘 다 필요한가 — 숫자 열을 남기고 TEXT 열을 옆에 하나 더 만든다
세 자리만 보면 됩니다. 셀에 보이는 글자, 수식 입력줄에 든 값, 그리고 맞춤이 어느 쪽에 붙어 있는지입니다.
마지막 줄이 실제로 가장 많이 쓰는 답입니다. 계산용 열과 보여 주기용 열을 따로 두면 어느 쪽도 포기하지 않아도 되고, 나중에 다른 사람이 그 파일을 열어도 어느 열이 원본인지 바로 보입니다. 열 하나 늘어나는 값으로는 싼 편입니다.
숫자를 글자로 만들 일이 잦아지면 그 파일이 보고서에 가까워지고 있다는 신호이기도 합니다. 계산이 끝난 값을 문장에 얹는 단계라면 TEXT가 맞지만, 아직 값이 움직이는 단계라면 서식으로 버티는 편이 고칠 일을 줄여 줍니다.
#엑셀 #TEXT함수 #셀서식 #표시형식 #엑셀함수
