학습(공부)하는 블로그

컴활1급 실기에 나오는 셀서식은 사용자 지정 서식을 지정하는 문제가 나온다.

그래서 셀서식의 기본형을 먼저 알아야 하고, 이를 변형시킬 수 있어야 한다.

1. 기본형: 양수 형식;음수 형식;0서식;문자 서식 ==> 세미콜론(;)으로 구분합니다.

2. 변형: 조건에 따라 서식을 달리할 경우 조건을 [대괄호] 안에 넣는다.

   - 조건이 하나만 적용할 경우: [조건①]①서식

      ==> 조건①을 만족하는 데이터만 ①서식 적용하고, 나머지는 입력한 값을 그대로 둔다.

   - 조건이 두 개인 경우: [조건①]①서식;[조건②]②서식

      ==> 조건①을 만족하면 ①서식 적용하고, 조건②를 만족하면 ②서식을 적용한다.

   - 조건이 두 개 이상인 경우: [조건①]①서식;[조건②]②서식…

      ==> 조건①을 만족하면 ①서식 적용, 조건②를 만족하면 ②서식을, 추가 조건에는 그에 맞는 서식을 적용

3. 알아야 할 숫자와 문자에 관한 코드

4. 알아야 할 날짜에 관한 코드

COLUMN 함수는 열 값을 반환하는 함수입니다.

앞 시간에 배운 ROW 함수가 행 값을 반환하는 대신 COLUMN 함수는 열 값을 반환하기 때문에 두 함수는 짝을 이룬다고 보면 되겠죠.

그런데 활용하는 분야는 조금 다릅니다.

주로 한 셀에 있는 내용을 여러 칸에 나눠 입력하는 데 사용되는데요,

대표적인 것이 단위가 각각 한 칸씩 있는 세금계산서입니다.

우선 개념부터 이해하고 가겠습니다.

아래 B4셀과 B5셀에 입력된 내용은 각각 =COLUMN()과 =COLUMN(E2)입니다.

함수뒤에 () <--- 이 표시는 해당 셀을 말하는 것이죠.

B4셀에 있는 수식은 B4셀의 열 값을 반환하라는 것이죠. B열 이므로 2가 되겠죠.

반면 B5에 있는 수식은 E2셀의 열 값을 가져오라는 것이죠, E열은 5가 되겠죠.

이제 본격적으로 실습으로 들어가겠습니다.

예제파일은 아래와 같이 세금계산서와 업체데이터 두 시트로 이루어져 있습니다.

여기서 주의할 점은 업체데이터 중 사업자등록번호는 하이픈(-)까지 입력해야 한다는 점입니다.

하이픈(-)을 생략하면 세금계산서에 빈칸이 생깁니다.(직접 해보시면 이해가실 겁니다.)

참고로 이 파일은 제가 직접 작성한 것이 아니고, 인터넷 검색을 통해 가져온 것을 가지고 내용을 수정한 것임을 먼저 알려드립니다.

그럼 지금부터 수식 설명을 하겠습니다.

F4:Q4 이 범위에 있는 사업자등록번호가 한칸에 한 숫자 또는 하이픈(-)으로 들어갑니다.

가져올 데이터는 업체데이터 시트에 있는 C2셀의 데이터가 되겠습니다.

들어간 수식은 아래와 같습니다.

=MID(업체데이터!$C$2,COLUMN()-5,1)

조금 복잡해 보이죠. 원리를 이해하면 아주 쉽습니다.

우선 사업자등록번호는 하이픈(-)을 포함하면 모두 12자리로 이루어져 있습니다.

그래서 MID 함수를 이용해서 사업자등록번호를 자리수에 맞게 가져오려고 합니다.

위 수식은 업체데이터 C2셀에 있는 데이터를 한개씩 가져오라는 것은 알겠는데, COLUMN()-5가 무슨 뜻인지만 알면 되겠죠.

COLUMN()의 값은 현재 셀의 주소가 F4셀이므로 6이 되겠죠. 그런데 맨 처음 글자를 가져와야 하기 때문에 -5를 해줍니다. 그러면 F4셀의 수식은 아래와 같은 모양이 되겠죠.

=MID(업체데이터!$C$2,1,1)

그렇게 입력했으면 채우기 핸들을 이용해서 Q4셀까지 수식을 복사해줍니다.

G4에서 위 수식은 COLUMN()-5의 값이 2가 됩니다. 그래서 아래와 같은 식이 되겠죠.

=MID(업체데이터!$C$2,2,1)

같은 방법으로 H4셀부터 Q4셀까지 3번째부터 끝번째까지 한 글자만 가져오는 수식이 되는 거죠. 이해가 가셨나요?

앞서 만든 수식에서 달라져야 할 부분은 두 곳 밖에 없죠.

가져올 데이터 주소가 업체데이터 시트의 C9셀로 바뀌는 것과COLUMN()에서 얼마를 빼줘야 하는 것만 수정해주면 되죠.

맨 첫 셀이 있는 곳이 V열이죠. 알파벳 순서대로 세면 22번째 열이 됩니다.

COLUMN()-21 하면 되겠죠.

그래서 들어간 수식은 다음과 같습니다. 물론 채우기 핸들로 오른쪽으로 복사해주면 되겠죠.

=MID(업체데이터!$C$2,COLUMN()-21,1)

다음은 F14셀에 들어갈 공란수를 구하는 방법입니다.

공급가액에 들어갈 수 있는 숫자는 백억까지죠. 숫자로 표시하면 11자리까지 표시가 가능합니다.

그런데 공란수란 비어있는 칸이 몇 개인지를 말하는거죠.

공급가액에 들어갈 숫자는 아래 U16:Z19 입력된 가액을 모두 더해야 합니다.

SUM 함수를 사용한 이유입니다.

11자리에서 공급가액을 모두 더한 값의 길이를 빼면 공란수가 나오겠죠.

그래서 F14셀에는 다음과 같은 수식이 들어갑니다.

=11-LEN(SUM(U16:Z19)

U16:Z19의 범위는 U16:Z16으로 표기해도 되고, 그림과 같이 절대셀이 아닌 상대셀로 표기해도 됩니다.

다음은 공급가액을 입력할 차례입니다.

공급가액은 아까도 말했듯이 U16:Z19까지 모두 합한 금액을 각 자리수에 넣어줘야 한다고 했죠.

그래서 일단 들어갈 데이터는 SUM($U$16:$Z419)이 되는 것은 이해가시죠. 또 오른쪽을 끌어서 복사할 예정이므로 절대셀로 지정해 주어야겠죠.

다음은 모두 합한 금액을 11자리로 지정하기 위해 TEXT함수를 사용하겠습니다.

TEXT(값,"???????????") : 물음표(?)의 갯수만큼의 자리수 데이터로 인식하라는 뜻입니다.

TEXT(값, "#,###") : 수치 값을 천 단위마다 구둣점(,)을 찍어라는 뜻입니다.

따라서 위 방법대로 하면 TEXT(SUM($U$16:$Z419),"???????????")의 의미는 공급가액을 모두 합한 숫자를 11자리 문자로 인식하라는 것이죠.

다음 COLUMN()-7은 더 이상 설명하지 않아도 이해할 수 있겠죠.

=MID(TEXT(SUM($U$16:$Z$19),"???????????"),COLUMN()-7,1)

세액에 들어갈 수식도 마찬가지로 작성하면 되겠죠.

수식은 아래와 같습니다.

=MID(TEXT(SUM($AA$16:$AE$19),"??????????"),COLUMN()-18,1)

이제 마지막입니다.

B21셀에 합계금액을 구하는 수식을 입력할 예정인데요.

이건 너무 쉽죠.

U16셀에서 AE19셀까지 모두 더하면 되겠죠.

따라서 수식은 다음과 같습니다.

=SUM(U16:AE19)

여기서 잠깐 팁 한가지.

B14셀부터 E14셀까지 날짜 입력하는 곳 있죠.

오늘을 기준으로 날짜가 자동으로 들어가게 했는데요.

사용한 수식은 각 각  =YEAR(TODAY()), =MONTH(TODAY()), =DAY(TODAY()) 입니다.

예제 파일을 첨부합니다. 

tax-data.xlsx

도움이 되셨다면 공감 꾸~욱 부탁드립니다!!!