엑셀] 숫자나 글자를 하나씩 분리해서 각 셀에 넣기(세금계산서 양식)

숫자를 한 자리씩 분리해서 각 셀에 넣는 방법에 대해 정리해본다. 

한 셀에 1,234이 입력되어 있다면 한 자릿수씩 분리해서 각 셀에 1, 2, 3, 4 를 입력한다는 뜻이다

물론 금액같은 숫자 뿐 아니라 "마이크로소프트"같은 단어도 "마", "이", "크", "로"... 처럼 한 글자씩 떼어서 분리할 수 있다. 

가장 많이 사용하는 예로 아래 세금계산서 같은 양식에 입력할 때 자리수마다 숫자를 따로 입력해야 한다. 

위의 세금계산서 양식을 조금 자세히 보면 품목별로 수량과 단가를 입력하면 공급가액과 세액을 계산하도록 되어있다. 

(세액은 공급가액의 10%)

따로 소계를 표시하는 항목은 없고 공급가액의 합계를 자릿수마다 하나씩 따로 입력하고

세액도 각 세액의 합계를 한 셀씩 입력한다. 

금액을 자릿수마다 하나씩 분리해서 각 셀에 입력하는 부분만 따로 떼어서 살펴보자. 

1) 숫자를 텍스트로 바꾸기

금액 부분을 숫자 하나씩 떼어내기 위해서 먼저 금액 전체를 text함수를 이용해서 텍스트로 변경한다. 

text 함수의 두번째 인수로 "0" 이나 "?"를 사용할 수 있는데 차이는 자리수를 처리하는 방법이다. 

원래 text함수의 두번째 인수는 셀서식을 지정해주는 것이다. 

0000으로 준다면 숫자 자릿수를 0의 숫자만큼 맞춰 표시하는 것이고(자릿수가 적다면 앞에 0을 채워서 자릿수를 맞춰준다)

숫자 데이터가 25이고 셀서식이 ????이라면 숫자25앞에 두 칸의 빈 칸이 있는 것처럼 인식된다

< 이전 블로그 글 참조 >

[Excel/엑셀 기본, 서식] - 엑셀] 셀 서식-사용자 지정 표시 형식 상세 정리

[Excel/엑셀 팁(TIP)] - 엑셀] 입력된 값이 숫자인지 텍스트인지 구분하기

여기서는 ????를 사용한다. (정확하게는 자릿수에 맞춰서 ?표시 11개)

만약 0000를 사용한다면 

금액이 세금계산서 양식에 표기되는 최대금액이 백억단위인데 그보다 작은 금액을 표기할 때 앞 자리가 0으로 채워지는 문제가 있다.

2) 같은 값은 rept 함수로 반복

수식 인수로 "?"를 11번 다다다다 입력하는 것도 방법이겠지만

rept함수를 사용할 수 있다. 

rept 함수는 첫번째 인수로 받은 문자를 두번째 인수로 받은 숫자만큼 반복해서 입력한다. 

rept("?", 11) 수식은 "?"를 11번 반복해서 입력하라는 뜻이다

3) 텍스트로 된 금액에서 왼쪽부터 한 자리씩 분리하기

여기서는 MID함수를 사용한다. 

mid 함수는 두번째 인수로 지정되는 위치에서 세번째 인수로 지정된 갯수만큼의 문자를 반환하는 함수이다. 

< MID함수에 대한 자세한 내용은 이전 글 참조 >

[Excel/엑셀 기본, 서식] - 엑셀] 텍스트 조작 (&, LEFT, MID, FIND함수)

각각의 자리에 해당하는 숫자를 가져오기 위해 mid함수에서 세번째 인수는 1를 사용한다(1자리씩 가져오기 때문에)

두번째 인수는 각 자릿수마다 다른데 

백억의 자리에는 두번째 인수로 1을

십억의 자리에는 두번째 인수로 2를

    억의 자리에는 두번째 인수로 3을

천만의 자리에는 두번째 인수로 4를..  사용하게 된다. 

즉 숫자가 하나씩 커져서 일의 자리에 해당하는 11까지 인수의 크기가 하나씩 증가하면 된다. 

위 그림처럼 두번째 인수를 1,2,3..으로 하나씩 변경하면서 일일이 입력해도 안되는건 아니지만 키 입력을 하나라도 덜 하는게 엑셀 같은 프로그램의 취지(?)니까 여기서는 그 방법에 대해서도 알아보자

4) 인수 입력을 간단하게

위 그림에서 D4에 입력된 백업에 해당하는 자릿수의 두번째 인수는 1, E4는 2... 이렇게 1씩 커진다. 

이럴 때 column 함수를 사용한다. 

< column함수에 대한 내용은 이전 글 참조 >

[Excel/엑셀 함수] - 엑셀] row 함수와 column 함수

백억 자릿수의 두번째 인수자리에 1대신 column(A1)을 입력한다. 

(column(A1)은 A1의 열 값인 1을 반환한다)

이렇게 수식을 입력하고 자동채우기 버튼을 더블클릭하면 각 자릿수마다 두번째 인수로 column(B1), column(C1)....으로 변경되어 들어가면서 수식을 완성시키게 된다. 

세금계산서 양식 파일을 덧붙여놓는다. 

ttend_세금계산서.xlsx

참고로 공급가액의 합계랑 세액의 합계는 23행과 24행에 계산된다. 

글자색이 흰색으로 되어서 숨겨진 것처럼 보인다. ^^;;