Basic

엑셀 사용자 지정 서식 형식 여러가지 코드

엑셀 문서에서 각 셀마다 셀서식의 표시형식 탭에서 사용자가 원하는 형식으로 직접 서식을 바꾸어

사용할 수 있습니다. 셀서식에서 형식탭에서 날짜, 숫자, 통화, 텍스트, 백분율등 기본으로 제공된

서식을 사용하여도 가능하지만 이것 외의 코드는 사용자 지정서식 형식에 입력하여 사용할수 있지요.

[사용자지정] 서석에서 숫자형식 코드를 알아봅니다. 아래의 해당코드를 여러가지로 조합하여 사용

가능한데요 연산자를 여러개 사용할 때는 " ; "(세미콜론) 으로 구분하여 사용합니다.

엑셀 수식 오류 발생 원인 알아보기

엑셀에서 어떤 수치에 대해 셀에 수식을 작성하였는데 오류가 발생하는 경우가 있습니다

수식이 잘못되었을 경우 자세히 보면 오류가 나는 유형이 각기 다릅니다. 원인에 따라

오류내용의 표시가 다르게 나타나지요. ####, #NAME?,#DIV/0 ........등 이처럼 오류의 내용이 다르기

때문에 엑셀에서는 오류 유형에 따라 표시를 다르게 하여 줍니다.

오류내용=> #########

이건 오류라기 보다는 데이터의 길이가 셀넓이보다 길어 나타내는 내용으로 셀의 넓이를

넓게 해주면 값이 모두 보여지게 됩니다.

오류내용=> #NAME?

이 오류는 이름이 잘못되어 이싸는 오류내역 표시인데요 정의된 이름이 없거나 함수를

잘못 사용하였는지 확인하여 줍니다 아래 오류는 SUM함수 인수를 " =SUM(C1,B1) "

입력한후 엔터를 치면 아래처럼 오류내역이 사라지고 제대로 표시되게 됩니다.

오류내용 => #DIV0?

셀에 입력된 수식의 나눗셈에서 어떤값을 0으로 나눌때 나타나는 오류입니다. 나눈값이

0또는 셀을 지정한 경우 셀이 비어 있을때 오류가 발생합니다.

0이 아닌 값으로 나누어 주던지 아니면 빈공간이 아닌 값이 있는 셀을 지정합니다.

오류내용 => #REF!

참조된 셀이 없을 때 나타나는 오류입니다. 수식에 참조된 셀이 사라졌는지 확인합니다.

오류내용 => #VALUE!

값이 잘못되었다는 오류내역입니다. 연산이나 함수의 인자로 사용한 값이 잘못되었는지

확인하여 줍니다.

 오류내용 => #NUM!

숫자를 잘못 사용하였을때 나타나는 오류이므로 함수의 인수에 사용된 숫자가

올바른 값인지 확인하여 줍니다.

오류내용 => #N/A

함수의 수식에서 사용할수 없는 값을 참조하였을때 나타내는 오류입니다. 수식에서 참조된

값이나 셀이 있는지 확인하여 줍니다. 아래는 비교할 '마산' 영업소가 없네요

오류내용 => #NULL!

존재하지 않는 값을 사용하였을때 나타내는 오류로 교차되지 않는 셀 범위를

사용하였는지 확인하여 줍니다. 아래는 SUM 함수 인수에서 E1은 인수 지정시 없는 값이지요.

엑셀에서 오류 표시가 나타나는 형태에 따라 오류의 원인을 알아보았습니다. 한번쯤 알아두시면

오류가 나타나 확인하는데 무엇때문에 오류가 났는지 확인하기가 용이할것입니다.

엑셀 숫자앞에 빨간색 세모 파랑색 세모 증감 표시하기 사용자 지정

엑셀 시트의 숫자앞에 증감용 세모를 사용자 지정서식으로 표시하여 봅니다. 주식 시황표처럼

매월 매출 증감표에서 전월대비 증감 이런식으로 표를 만들어 단순히 +, - 로 표시하기보다는

"▲ ,  ▼ " 이렇게 색이있는 화살표를 색과 함께 표시하여 주면 더 보기가 좋겠지요.

위에 표를 예를 들어볼께요. 위의 표에서 증감 항목의 숫자가 5월-4월 증감매출을 표시한

부분이 단순히 증가이면 양수, 감소이면 음수로 표시하였습니다. 눈에 확 들어오지 않죠

E3셀에서 마우스 우측 버튼을 눌러 [셀서식]=>표시형식 탭에서[사용자지정]을 클릭합니다.

[사용자서식] 에서 형식(T): 란에 " [빨강]▲#,##0;[파랑]▼#,##0;"변동없음"  "을 입력합니다.

세모모양은 한글로 "ㅁ"을 누르고 키보드의 한자키를 누르면 아래처럼 선택할수 있습니다.

형식란에 입력을 마치고 [확인]을 클릭하면 아래처럼 E3셀의 증가분이 빨강색 숫자와 위화살표

모양이 숫자 앞에 붙게 됩니다. E3셀을

나머지 셀에도 복사하기 위해 E3셀의 하단모서리 부분을 마우스로 더블클릭하면 E9셀까지 셀 형식이

자동으로 복사가 됩니다.

5  4월이 매출이 크면 빨강색의 상승화살표와 금액이 표시되고 매출이 적어진 경우에는 파랑색의

하강화살표와 감소된 금액이 표시됩니다. 맨처음 단순히 "-"마이너스 표기된 경우보다 훨씬보기

좋네요 눈에 잘들옵니다.

4월과 5월 매출금액이 같도록 해보니 "변동없음" 이라고 표기되네요

 " [빨강]▲#,##0 ; [파랑]▼#,##0 ; "변동없음"  "이라고 사용자지정 형식란에 입력하였는데요

여기서 빨강색 표시 형식 부분과 파랑색 표시 형식 부분 변동없음 표시형식 부분의 사이를 " ; "

(세미콜론)으로 구분합니다. 2개이상의 조건으로 서식을 구분할 때는 세미콜론으로 구분합니다.

엑셀 (EXCEL) 외부의 문서 데이터를 활용하여 표만들기

엑셀, 외부의 다른 형태의 문서로 엑셀에서 표만들기

엑셀이외의 다른 형태의 문서에서 데이터를 불러와 표를 만든는 방법을 간단히 알아보겠습니다.

외부문서는 먼저 준비되어 있어야 합니다. 여기서 예를 들기 위해 성적표참조.txt라는 문서의

데이터를 불러와 표를 만들어 볼께요

엑셀의 맨왼쪽 상단에 있는 오피스버튼을 클릭하여 '열기'를 클릭합니다. 열기 상자에서 미리준비한

외부 문서를 열어줍니다.(여기서는' 성적표참조.txt'를 활용했습니다)

외부 데이터를 불러오는 작업은 3단계로 진행됩니다. 먼저 1단계로 데이터의 파일 유형입니다.

'구분 기호로 분리됨(D)'를 클릭합니다. txt문서는 일정하게 간격을 구분하기 어렵기 때문에 쉼표나

탭키로 데이터를 구분하여 작성합니다.  그아래에 보시면 '구분시작행(R)'은 원본문서에서 표를 만들

자료의 시작 행을 적어줍니다. 1은 첫행, 2는 2번째행....    다음'을 클릭하여 2단계로 갑니다.

2단계는 데이터의 구분 기호를 설정하는데요 예제의 txt문서는 탭으로 데이터를 구분하였기에 탭을

선택하였습니다. 원본데이터가 쉼표(,)로 구분되었다면 쉼표를 체크합니다.

3단계에서 데이터의 서식을 선택합니다. 예제는 텍스트와 숫자로 구분되었기에 '일반'에 체크하

였습니다. 하단의 '데이터 미리보기(P)'에서 열을 선택하여 텍스트, 숫자, 날짜 의 서식을 각각

열별로 지정할수 있습니다.

 '고급'탭에서는 숫자의 소수점이나, 천단위 구분기호도 지정할수 있습니다.

3단계에서 지정을 마치고 '마침'을 클릭하면 아래처럼 엑셀 시트에 txt문서의 데이트가 순서대로

셀에 표시됩니다.

 이제부터 엑셀의 데이터가 되었으니 테두리나 배경색을 지정하여 표를 꾸며주면 됩니다.

엑셀에서 외부문서의 데이트를 불러와 표를 만드는 방법을 간단하게 알아보았습니다.

엑셀 시간을 계산하는 방법을 알아봅니다.

엑셀에서 숫자의 계산은 아주 간단합니다. 직접 숫자를 작성하여 계산할수도 있고 숫자가 있는셀을

지정하여 계산할수 있습니다. 근데 시간을 계산하면 일반적으로 셀에 형식을 지정하지 않으면 소수

점이나 아주 큰 숫자가 나타나곤 합니다.

이번시간에는 숫자를 계산하는 방법을 간단한 예제로 알아보도록 하겠습니다.

아래 예제를 보면 맨첫번째 '하나'의 근무시간을 시작시간과 종료시간을 계산하여 알아보면

[ 근무시간=종료시간-시작시간 ] 즉 D2셀에서 =C2-B2 이렇게 작성하여 주면 되는데요 여기서

단순 계산만 하면 숫자가 이상하게 나오기 때문에 D2셀의 셀서식을 사용자 서식에서 '[h]:mm'형태로

바꿔줍니다.

그렇게 해주면 근무시간이 간단하게 계산이 되는데요 위에서 '하나'와 '로기'는 날짜가 있어 계산이

금방 되지만 날짜가 없는 시간만 있는 셀은 위처럼 계산하면 ####### 이렇게 표시되는데요 종료시간

보다 시작시간의 숫자가 크기 때문에 '-'가 되기 때문입니다.

이걸 해결하기 위해 IF문을 활용하여 종료시간의 숫자가 적은경우 IF(C4<B4,1,0) 1을 반환 아니면0을

반환하여 종료시간에 1을 더해 주어 계산하면 올바른 시간이 계산이 됩니다. 아래 그림을 자세히

보시면 금방 이해되세요

종료시간이 크면 IF문에서 0이 되어 그대로 계산, 종료시간이 작으면 IF문에서 1이 되어 1을  더하여

계산합니다.

아래는 시간을 표시한 숫자에 몇분을 더하는 방법입니다 간단히 알아볼께요

예로 8시 20분에 30분을 경과한 시간을 알아보면  아래처럼 B2셀에 +30을 하여 주면 아래처럼 8:20

을 표시해 줍니다. 8:50분이 되어야 하는데 원하는 답이 아니게 됩니다.

TIME 함수를 사용하면 되는데요 '=B2+TIME(0,30,0)'이렇게 하여 주면 됩니다. 시간함수에서 분에

해당되는 인자에 30을 넣어주면 30분으로 인식하여 8시 20분에 30분을 더하여 8시 50분이라 표현

하여 줍니다.

함수형식은 TIME(hour, minute, second)  수치로 주어진 시간,분,초를 시간형식의 엑셀 일련 번호로

변환하여 주는 함수입니다.

엑셀(EXCEL)함수 글자수를 세어주는 LEN함수와 글자를 변환해주는 SUBSTITUTE함수

엑셀에서 LEN함수는 문서의 텍스트 글자열에서 문자의 개수를 세어줍니다.

하나의 셀에 있는 문자열의 개수를 세어 줍니다. 아래 예제를 보면 A1셀에는 숫자 1~5까지 5개의

숫자가 쓰여 있는데요 이것을 LEN함수로 표현하면 C1셀에서 '=LEN(A1)' LEN함수와 인자로 A1셀을

지정하여 주면 A1셀의 문자열의 문자 개수를 세어 표현하여 줍니다.

또 A2셀에는 영문과 ABC다음에 ','(쉼표) 가 나오는데요 이 쉼표도 문자로 취급하여 세어주게 됩니다.

이번에는 문자열에서 해당하는 문자를 찾아 치환하는 SUBSTITUTE 함수에 대해 간략히

알아볼께요

 간단한 예를 들어 설명하여 볼께요. 아래 그림에서 A1셀에 있는 문자열중에 ','(쉼표) 를 찾아 '-'

(마이너스) 표시로 바꾸어 보면 변환되어 표현될 C1셀에 '=SUBSTITUTE(A1,",","-")' 와 같이 입력

하면 변환되어지는데요 인자로 들어 있는 A1에 들어 있는 문자열 '가,나,다,라,마,바,사' 중에

"," 를(old_text) 찾아 "-" 문자로(new_text) 변환하여 표현하라는 의미입니다.

SUBSTITUTE 의 인자중에 old_text와 new_text 의 인자는 문자이므로 ""를 써 주어야 합니다.

한가지 예를 들어 문자열중에  ","(쉼표) 가 몇개인지를 세어 숫자로 나타내는 방법을 알아볼께요

LEN함수는 문자의 개수를 세어주고, SUBSTITUTE 함수는 원하는 문자를 원하는 문자로 치환해주는

것을 이용하여 ","를 찾아 볼께요

A1의 문자의 개수를 세어보면 =LEN(A1) 이렇게 하면 A1셀의 문자수는 쉼표를 포함하여 13입니다.

여기서 A1셀의 문자열에서 SUBSTITUTE 함수로 ","(쉼표)를 ""(공백)으로 대처하여 LEN함수로 세어

보면 7이 나오게 됩니다. 그러면 앞서 설명한 A1셀의 문자를 그대로 세어 준 숫자 13과

A1셀의 문자열중 ","를 ""공백으로 치환한후 세어준 숫자 7을 계산하면 (13-7) 6이란 숫자가

나옵니다.

즉 SUBSTITUTE함수로 변환한 ","의 갯수를 세어준 셈이 되지요.

위에 엑셀에서 계산한 방법을 한번 자세히 보시면 이해가 되실거에요

엑셀함수중 많이 사용되는 루트 함수(SQRT)와 거듭제곱(POWER) 함수에 대해 간단히 사용법을

알아보겠습니다.

SQRT함수는 양의 제곱근을 구하는 함수입니다.

함수형식 : =SQRT(number)  number:제곱근을 구할 숫자

POWER함수는 밑수로 지정한 만큼 거듭제곱한 결과를 구하여 주는 함수입니다.

엑셀에서 두개이상의 문자를 결합하여 주는 문자 결합함수 PHONETIC함수에 대해 알아보겠습니다.

문자결합함수는 '&"연산자로도 직접 결합할수도 있구요 CONCATENATE함수로도 결합할수 있습니다.

먼저 '&'연산자로 간단히 보면 B2셀과C2셀에 각각 '문','자'를 D2셀에서 '=B2&C2' 라고 입력한 후

엔터를 치면 아래처럼 D2셀에 '문자'로 결합이 됩니다.

 아래그림은 문자결합 함수인 PHONETIC함수와 CONCATENATE함수를 비교한 예제인데요 먼저 F1셀

과 F2셀을 보면 결과값이 같은데요. 사용법은 '=PHONETIC(A1:C1)', '=CONCATENATE(A1,B1,C1)'로

약간 다른데요 PHONETIC함수는 범위를 입력하고 CONCATENATE는 인수를 각각 지정을 하였습니다.

 또 다른 예제로 아래를 보시면 위에서 처럼 같은 방법으로 문자를 A2,B2,C2을 결합하였지만 결과가

다름을 알 수 있는데요. 여기서 자세히 보시면 PHONETIC함수는 숫자를 참조하시 않습니다.

오직 문자만 참조를 하여결합하여 줍니다.

 위에서 CONCATENATE로 결합한 중간에 날짜가 이상하게 숫자로 표시되었는데요 이건 컴퓨터가

인식하는 날로부터 일자를 세어서 표현하여 주는 것이기 때문에 TEXT함수를 이용하여 형식을 지정

하여 주면 해결됩다. CONCATENATE 함수 인수중 C2에 해당하는 인수자리에

 '=TEXT(C2,"yyyy-mm-dd")'라고 입력하면 날짜의 형태로 표시하여 줍니다.

 문자결합함수 PHONETIC 을 이용해 결합할 문자 중간에 쉼표(,)를 넣어보려면 아래처럼 입력하여

A14셀에 표시하도록 해 줍니다. A14셀에 'PHONETIC(A1:B12)'를 입력하여 주면 범위에 있는 문자를

결합하여 주는데요 결과는 1월,2월,3월,.....이렇게 나오게 됩니다.

두개의 문자결합 함수는 장단점이 있는데요

먼저 PHONETIC함수는 윗주문자를 가져옵니다.숫자인경우 반영하지 않고 오직 문자만 인수에

반영합니다. 숫자를 입력하고 싶으면 숫자앞에 '(작은따옴표)를 입력하여 주면 숫자도 결합할수

있으나 계산을 할수 없더군요 또 직접 입력딘 텍스트에 대해서만 적용이 되며, 수식을 통하 인수

지정은 이함수로 합쳐지지 않습니다.

CONCATENATE함수는 함수전체를 합쳐주지만 인수를 하나씩 열거해 주어야 하는 단점이 있습니다.

PHONETIC함수는 많이 사용하진 않지만 알아두면 유용하게 사용될 때가 있을거 같습니다.

엑셀에서 데이터를 입력하다보면 같은 단어를 반복해서 입력해야 하는 경우가 있습니다. 이럴때

간단하게 숫자를 입력하면 어떤단어가 자동으로 표시되게 하거나 특정한 값에 따라 셀 색이 자동으로

채워지거나 변하게 하면 입력이 훨씬 수월해 지겠지요.

이번시간에는 간단한 예를 들어 엑셀문서에서 특정값에 따라 자동으로 문자가 입력되거나 셀의 색을

바꾸는 방법을 알아보겠습니다.

사용자 지정 서식을 이용하는 방법과 조건부 서식으로 지정하는 방법을 알아봅니다.

아래 예제에서 성별을 입력해야 하는데요 남자, 여자 이렇게 반복이 되는 것을 '1'과 '2'를 입력하여

'1'을 입력하면'남자', '2'를 입력하면 '여자' 를 입력되게 하여 봅니다.

성별을 입력할 셀을 드레그하여 선택한후 마우스 우측 버튼을 눌러 [셀서식]=>[표시형식]탭에서

[사용자 지정] 서식을 클릭한후 형식(T): 입력란에 [=1]"남자";[=2]"여자";; 를 입력한후 확인을 클릭

합니다.

그런다음 성별입력란 B3셀에 남자이면 '1'을 입력하면 '남자' 라고 자동으로 입력이 됩니다.

B4셀에 '2'를 입력하면 자동으로 '여자'가 입력되는 것을 확인할수 있습니다.

다음으로 특정한 값에 따른 셀의 색이 변화하여 채워지는 방법을 알아보겠습니다.

아래 예제에서 동물이름 '고양이'를 입력하면 입력한 글자가 빨간색으로 변하도록 하려면

동물이름을 입력할 셀을 드레그하여 선택한후 [홈]=>[조건부서식]=>[셀강조규칙]=>[같음]을 클릭

합니다.

조건부 서식 대화상자 [같음] 에서 '다음 값과 같은 셀의 서식 지정:'란에 '고양이'를 입력하고

적용할 서식을 '빨강 텍스트'로 선택하여 줍니다.

입력한 값에 따라 셀의 글자색이 변하는지 확인합니다. 동물이름란에 고양이를 입력한 셀의 글자가

빨간색으로 변하였음을 확인할수 있습니다.

간단한 2가지의 예제로 특정한 값에 따른 글자색이 변하거나, 값에 따른 특정한 값이 자동으로

입력되도록 하는 방법을 알아보았습니다.

엑셀에서 데이터가 들어 있는 데이터 베이스나 표등의 많은 데이터내에서 중복된 항목을 찾아 없애

주는 방법으로 데이터를 하나하나 찾지 않고 자동으로 중복된 항목을 제거하는 방법으로 많이 사용

되는 엑셀의 기능중 하나입니다.

많은 양의 데이터에서 항목을 찾아 지워야 하는 기능으로 아래표에서 간단하게 예를 들어 봅니다.

아래 그림에서 항목중에 사과가 4번이나 들어 있는데요 이것을 찾아 중복값을 지워줍니다. 

중복값을 비교하기 위해 옆셀에 복사하여 놓고 중복값을 찾을 셀을 드레그하여 선택한후 풀다운

메뉴에서 [데이터]=>[중복된 항목 제거]를 클릭하여 줍니다.

'중복된 항목 제거 경고' 대화상자가 나타나면 영역을 확장할것이니 묻는데요 여기서 B열까지 함께

항목을 제거할 것이면'선택영역확장'을 아니면 C열만 제거할 것이면 '현재 선택 영역으로 정렬'을

선택하고' 중복된 항목 제거'를 눌러 줍니다.

아래처럼 중복값을 제거 화면에서 '열C'를 선택하고 '확인'을 클릭하여 줍니다.

그러면 중복값인 사과가 4개에서 맨앞에 나타난 1개를 제외한 3개가 지워지게 됩니다. 또

중복값이 몇개가 지워졌는지도 나타내어 줍니다.

이와 같이 데이터베이스나 표에서 기초적인 중복되 값을 제거하여 주는 방법을 알아보았습니다.