Basic

엑셀(EXCEL) 소수점 계산 함수 INT, TRUNC, EVEN, ODD함수

엑셀 함수중에 소수점의 값을 계산하는 함수입니다.

엑셀 차트 그리기 차트 제목 축 이름 범례이동하기

엑셀에서 문서를 더 알아보기 쉽도록 하기 위해 차트를 사용합니다. 도표를 작성하고 도표의

내용을 좀더 수월하고 알아보기 쉽도로고 말이지요.

이번시간에는 도표 데이터를 이용하여 차트를 그리는 방법을 알아보겟습니다.

먼저 아래와 같은 상반기 매출 분석의 도표가 있다고 가정하고 이 도표를 가지고 꺽은선의

차트를 그려봅니다. 먼저 아래도표처럼 차트를 만들 데이터를 마우스를 이용하여 드레그

선택하여 줍니다.

엑셀의 상단 메뉴 [삽입]=>그래프탭에서 [꺽은선형]=>그래프의 유형을 선택합니다.

그러면 아래처럼 기본차트가 생성이 됩니다. 차트에서 가로항목, 세로항목,범례가

기본으로 생성되는데요 여기에 차트제목, 가로세로축 이름을 입력하여 보겠습니다.

 차트의 제목은 차트를 먼저 선택하면 상단에 아래그림처럼 [레이아웃]탭이 생기는데요

[레이아웃]=>[차트제목]=>'차트위' 를 선택하면 차트에 제목입력란이 생깁니다.

 차트 제목을 입력하고 상단메뉴[홈]에서 글자 크기, 글자체등을 보기좋게 수정하여 줍니다.

또 차트의 그림에서 '가로축 제목'과 '세로축 제목'을 입력하려면 먼저 차트를 마우스로 클릭하여

선택한후 상단메뉴의 [레이아웃]=>[축제목]=>[기본가로 축 제목]=>[축 아래 제목]을 클릭하면

차트 그림 하단에 가로축 제목을 입력할수 있는 입력란이 생깁니다. 축 제목을 입력합니다.

세로축 제목도 가로축과 마찬가지입니다. 세로축 제목을 입력합니다.

차트의  오른쪽에 그래프를 설명하는 범례의 위치를 하단으로 이동하여 보면, 먼저 차트를 선택후

상단메뉴의 [레이아웃]=>[범례]=>[아래쪽에 범례 표시]를 클릭하여 줍니다.

범례의 표시가 아래쪽으로 이동하게 됩니다. 아래 그림은 위에서 기본차트에다가 '차트제목',

'차트 가로,세로축 제목', '범례이동'을 적용한 그림입니다.

엑셀의 차트는 한번씩만 따라해 보시면 금방 익힐수 있습니다. 다음시간에는 차트에

'데이터레이블'과 '데이터표' 를  추가하는 방법을 알아보겠습니다.

엑셀의 차트 차트의 요소 살펴보기

엑셀에서 기본 차트는 세로축과 범례등만 간단히 표시하게 되어 있지만 차트 제목이나 축 제목,

범례 위치등을 마음대로 바꾸어 표시할수 있습니다.

아래 그림을 보고 엑셀의 차트 요소에는 어떤 요소가 있는지 알아보겠습니다.

1. 차트 영역 : 차트 제목과 그림영역, 축 , 범례 등 차트 전체

2. 차트 제목 : 차트 제목으로 글꼴과 맞춤 등의 서식을 바꿀수 있습니다.

3. 세로축과 세로축 제목 : 값 Y축의 데이터 범위와 제목을 표시합니다.

4. 그림영역 : 데이터에 따른 데이터 계열과 데이터 레이블 등을 말합니다.

5. 데이터 계열 : 각 항목의 데이터 값을 선과 막대 등으로 표시합니다.

6. 범례 : 데이터 계열에 대한 이름이나 내용을 색이나 모양으로 표시

7. 눈금선 : 데이터 값을 정확히 알 수 있도로고 주 눈금과 보조 눈금선으로 표시

8. 데이터 레이블 : 데이터 값을 정확히 표시하기 위해 직접 표시한값

9. 데이터 표 : 차트 아래에 표 형식으로 데이터 값을 표시한것을 말합니다.

10. 가로축과 가로 축 제목 : 항목 X축의 데이터 범례와 제목을 표시합니다.

엑셀 항목이나 순번을 계속보이게 틀고정 사용하기

엑셀에서 데이터가 많아 한페이지나 한 화면을 넘어가게 많아 화면을 아래로 이동하면 맨위의 첫행

제목이나 항목등이 보이지 않게 되어 항목의 이름을 보기 위해서는 위아래로 이동하여 보게 되는데요

이게 여간 불편하지 않습니다. 이런경우에 [틀고정]을 사용하면 위아래로 이동시 계속하여 필드명

즉 제목이나 항목명을 계속 표시되게 할수 있습니다.

첫행을 고정하거나 첫열 또는 원하는 위치에서 워크시트의 셀을 고정할수 있습니다.

먼저 첫행을 고정하는 방법을 간단히 알아보겟습니다.

엑셀의 상단메뉴 [보기]=>[틀고정]=>[첫행고정]을 누르면 시트의 1행이 고정이 되어 화면을

아래로 내려보아도 예시에서의 '구분,코드,품목,....'등의 항목명이 움직이지 않고 계속 보여집니다.

다음은 첫열 고정하는 방법은 마찬가지로 상단메뉴의 [보기]=>[틀고정]=>[첫열고정]을

클릭하면 도표의 왼쪽 A열의 구분에 해당되는 열이 고정이 되어 좌우로 움직여도 A열은

고정되어 계속 표시가 됩니다. 아래 우측으로 화면을 이동하여도 A열은 고정되어 보여집니다.

이번에는 사용자가 원하는 부분에 틀고정을 할수 있는 방법입니다. 예시에서 구분의 항목명행과

번호열을 행과 열을 틀고정하여 보겠습니다. 고정할부분의 다음셀 즉 아래에서 B2셀을 클릭한후

상단메뉴에서 [보기]=>[틀고정]=>[틀고정]을 클릭하여 줍니다.

그러면 아래처럼 항목명이 있는 1행과 숫자번호가 있는 A열이 고정이 되어 화면을 위아래

또는 좌우로 움직여도 항목명고 숫자가 있는 부분은 계속하여 표시되게 됩니다.

행과 열이 동시에 고정이 되게 되는 것이지요. 틀고정 메뉴를 이용하여 간단하게

도표를 이동하여도 필드명이 계속 표시되게 하는 방법을 알아보았습니다.

엑셀 워크시트의 수정할 부분 남기고 잠금으로 시트 보호하기

다른 사람이 엑셀 문서를 수정하지 못하게 수식이나 내용을 보호하면 수식이나 내용을 지울수

없습니다. 문서 시트의 일부 입력하는 부분만 남기고 시트를 잠금 보호하는 방법을 알아보겟습니다.

이렇게 하면 문서, 창 등도 암호를 지정하여 수정, 복사, 이동의 제한을 할수 있습니다.

엑셀에서 워크시트는 [셀서식] 대화상자의[보호]탭에서 셀을 잠그면 내용을 수정하지 못하도록

설정할수 있습니다. 거래명세서등과 같이 거래 내용만 수정할수 있게 하는 방법입니다.

아래의 거래명세서를 예제로 들어 보겠습니다.

시트가 잠금으로 보호되더라도 내용을 수정할수 있게 품명,규격, 수량,단가 부분을 잠금 해제가

되도록 하려면 잠금해제할 B11셀부터 O16셀까지 드레그하여 선택합니다.

선택한 영역에서 마우스 우측 버튼을 누르고  [셀서식]을 클릭합니다.

[셀서식]대화상자에서 [보호]탭을 클릭하여 [잠금]에 체크를 해제한후 '확인'을 클릭합니다. 

선택한 셀들을 제외한 나머지 셀들이 모두 잠금으로 설정되어 있는 상태입니다.

상단메뉴에서 [검토]=>[시트보호]를 클릭합니다.

시트보호 대화상자가 나타나면 '시트보호 해제 암호(P):' 에 암호를 입력합니다, 예)1111

다시 암호를 확인차 재입력하라는 대화창이 나타납니다. 처음 입력한 암호와 같이 입력합니다.

제대로 시트의 보호가 되는 지 화인하여 보기 위해 시트 보호가 된 부분을 입력 또는 수정하기 위해

더블클릭하여 보면 시트가 보호되어 있어 입력할수 없다고 하는 메세지가 나옵니다.

또 부분적으로 시트보호가 해제된 부분(B11:O16)셀에 입력하려면 입력이 가능하도록 커서가

깜박이는 것을 확인할수 있습니다.

또 시트 보호를 해제하기 위해서는  상단 메뉴의 [검토]=>[시트보호해제]를 클릭한후

시트보호대화상자에서 [암호(P:)]란에 잠금암호를 입력하면 시트보호가 해제됩니다.

엑셀 원형차트 대 원형 차트 원형차트 대 막대차트

엑셀에서 도표를 가지고 원형 차트를 만들다 보면 값이 아주 작은 데이터가 있으면 분포가 고르지

않아 알아보기가 어렵습니다.  이런경우에 비율이 작은 몇개의 데이터 계열을 함께 '기타'로 표시하여

따로 차트를 그려서 비율을 표시할수 있습니다.   

차트는 원형대 원형 차트 또는 원형 대 막대 차트 중 마음에 드는 것으로 골라 말들수 있습니다.

아래와 같은 도표를 가지고 원형차트를 만들어 보면 먼저 도표에서 차트를 만들 데이터를 선택합니다.

선택은 Ctrl 키를 눌러 항목과 합계를 가지고 원형 차트를 만들어 봅니다.

도표에서 데이터를 선택한후 상단메뉴에서 [삽입]=>[원형]챠트=>[원형 대 원형] 차트를 클릭합니다.

그러면 아래처럼 원형 차트 대 원형차트가 나타납니다. 우측의 범례는 지워도 무방합니다.

원형차트에서 마우스 우측버튼을 클릭하여 '데이터 레이블 추가'를 클릭합니다.

그러면 아래처럼 차트에 데이터 레이블 숫자가 표시됩니다. 또 다시 원형차트에서 우측마우스

버튼을 클릭하고 '데이터 레이블 서식'을 클릭합니다.

'데이터 레이블 서식' 대화상자에서 '레이블 옵션'에서 '항목이름(G)' 체크하여 줍니다. 그러면

원형차트에서 항목의 이름과 항목의 숫자가 표시됩니다.

또 원형차트에서 마우스우측 버튼을 클릭하여 '데이터 계열서식'을 클릭하면 원형 대 원형 차트간의

간격 또 원형차트의 쪼개짐, 둘째 원형차트의 크기등을 조정할수 있습니다.

원형차트의 두번째 기타항목의 차트를 원형이 아닌 막대차트로도 나타낼수 있습니다.

상단메뉴의 [삽입]=>[원형]=>[원형 대 가로막대]형을 선택하면 아래 그림처럼 원형차트 대

막대차트로도 표시할수 있습니다. 

엑셀 셀에 참조되어 있는 셀을 연결선으로 수식의 셀 추적하기

엑셀에서 수식이 복잡하게 연결되어 있는 경우 셀의 참조된 셀을 찾기가 어렵습니다.

이런 경우에는 참조된 수식을 눈으로 확인하는 것보다 화살표로 연결된 셀을 표시하여 주면

찾기가 더욱 쉬워집니다.

[참조하는 셀 추적]이나 [참조되는 셀 추적] 메뉴를 이용하면 표시선을 표시할수 있습니다.

아래의 도표에서 우측 사과의 합계금액에 해당하는 G3셀을 클릭하고 상단메뉴 [수식]=>

수식분석 그룹의 [참조되는 셀 추적]을 클릭합니다.

참조되는 셀 추적이 되는 G3셀의 참조 A3:A11셀과 사과에 해당되는 F3셀, 금액을 합계할 D3:D11

셀을 참조하는 것을 알수 있습니다. 화살표로 표시되고 있는 부분을 보시면 됩니다. 

이번에는 품목이 '사과'인 셀이 참조하는 셀을 알아봅니다. 사과를 참조하는 셀을 알아보려면

A3셀을 클릭하고 [수식]탭의 [참조하는 셀 추적]을 눌러봅니다. 그러면 사과를 참조하는 셀을

모두 화살표로 표시하여 줍니다.

참조하는, 참조되는 셀을 표시한 표시선을 제거하려면 [수식]탭의 [연결선 제거]를 클릭하면

아래 그림처럼 도표만 나와있는 상태로 되돌아 오게 됩니다.

[연결선 제거]의 작은 화살표 부분 드롭다운 버튼을 클릭하면 참조되거나, 참조하는 연결선을

선택하여 제거하여 볼 수 있습니다.

엑셀 두개의 워크시트를 한화면에서 나란히 보기

엑셀 한개의 파일에서 워크시트가 여러개 있을경우 이 여러개의 시트를 한화면서 봐야하는 경우가

있습니다. 예를 들면 sheet1, sheet2,....

[새창]메뉴를 이용하면 한개의 파일을 여러개의 창으로 나누어 볼 수 있기 때문에 여러개의 워크시트

를 한화면에서 동시에 볼수 있습니다.

아래의 예를 들어 설명해 볼께요 아주 쉽습니다. 아래 예제에서 '실적통계'라는 엑셀 파일안에

sheet1, sheet2 이렇게 2개의 시트가 있습니다. 두개시트를 나란히 보게하기 위해

상단메뉴 [보기]=>[새창]을 클릭하여 줍니다. 그러면 '실적통계2'가 자동으로 생성이 됩니다.

윈도우의 작업메뉴에서 보면 엑셀파일 창이 자동으로 하나가 더 열려있는걸 볼수 있는데요

여기에서 [보기]=>[모두정렬] 메뉴를 클릭하여 대화상자에서 '세로'를 선택후 확인을 눌러줍니다.

그러면 아래화면처럼 엑셀시트가 두가개 한화면에 세로로 정렬되어 두개다 동시에 보여지게 됩니다.

그런데 한파일의 은 시트를 보여주게 되는데요 왼쪽화면과 오른쪽 화면을 시트명을 클릭하여

다르게 왼쪽 화면은 sheet1, 오른쪽 화면은 sheet2 이렇게 나타내어 볼수 있습니다.

아래 화면에서 보시면 좌측은 sheet1 우수3개월의....., 우측은 sheet2 대리점별 매출...

두개의 시트를 동시에 보다가 시트중 하나의 "X"표 닫기 버튼을 클릭하면 다시 하나의 sheet만

보여지게 됩니다.

상단메뉴 [보기]의 창그룹메뉴에서  [나란히보기]와 [동시스크롤]을 클릭하면 동일한 워크시트

sheet1 =sheet1, sheet2=sheet2 선택시 두개의 워크시트를 동시에 스크롤 할수 있습니다.

엑셀 다른문서의 텍스트 파일을 열너비로 나누어 구분하기

엑셀에서 데이터베이스를 다른 전산프로그램에서 저장한 텍스트문서로 저장한 파일을 많이

사용합니다. 텍스트 파일을 열어 엑셀에 복사하였을때 알아서 나누어지기도 하고 그냥 구분없이

한셀에 내용이 입력되기도 합니다.

이런경우에 텍스트 문서를 불러올때 텍스트 나누기를 이용하여 텍스트파일의 필드를 구분하여

엑셀로 불러올 수 있습니다. 이번시간에는 텍스트 문서를 불러올때 열너비를 기준으로

텍스트를 나누는 방법을 알아보겠습니다.

위와 같이 공백이 없이 텍스트가 모두 붙어있는 파일의 경우 열의 넓이가 일정하게 있다고하면

문서를 불러오면서 텍스트 마법사로 구분할수 있습니다.

엑셀의 메뉴 [데이터]=>외부데이터 가져오기[텍스트] 를 클릭하여 가져올 txt 문서를 찾아

[가져오기]를 클릭하여 줍니다. 그러면 '텍스트 마법사' 1단계 대화상자가 나타납니다.

여기에서 일정한 너비를 사용한 것으로 [너비가 일정함]을 선택후 [다음]을 눌러줍니다.

'텍스트 마법사' 2단계로 일정한 간격의 데이타에서 나눌 부분을 마우스로 클릭하거나

드레그하여 구분지을 선을 나누어 줍니다. 다음을 클릭합니다.

'텍스트 마법사' 3단계에서는 각 열의 데이터 서식을 지정하는데요 년월일의 날짜에 해당된는

곳을 선택한후 '열데이터 서식'에서 '날짜(D): 년월일'을 선택한후 '마침'을 클릭합니다.

'텍스트 마법사'가 3단계까지 마치면 아래처럼 '데이터가져오기' 대화상자가 나타납니다.

데이터를 붙여넣을 셀을 선택한후 '확인'을 클릭합니다.

확인을 클릭한후 .txt 외부문서를 '텍스트 마법사'를 사용하여 엑셀에 불러온 화면입니다.

텍스트마법사의 1단계에서 =>너비가 일정함, 2단계에서=>나눌 위치조정, 3단계에서=>서식지정

을 마치고 txt문서를 불러오면 아래처럼 열마다 구분선에 따라 나눠지게 됩니다.

많은양의 데이터를 너비 기준으로 불러와 나누는 방법을 알아보았습니다. 위에서는 필드명이

맞지는 않지만 필드명을 고치는 것이 방대한 양의 데이터를 다시 작성하는것보다

훨씬 쉽고 편하지 않을까 합니다.

엑셀 함수 COUNTIF함수로 공란수 계산하여 보기

먼저 COUNTIF함수에 대해 간단히 알아보겠습니다. COUNTIF함수는 지정한 범위내에서 조건에

 맞는 셀의 갯수를 구하여 줍니다.

형식 : =COUNTIF(rangr, criteria)   range: 셀범위 , criteria:조건

셀범위(range) 내에서 조건(criteria)에 맞는 값의 개수를 세어줍니다.

위의 예제는 세금계산서중의 일부인데요 공급가액과 부가세를 입력하는 부분입니다. 공급가액

에서 위에 빈자리의 공란을 세어서 위의 노란색 부분에 공란수를 입력하도록 하는데요

공란수를 입력할 D3셀에 " =COUNTIF(E3:O3,"") "이렇게 입력하면 E3:O3 셀이

범위(range)에 해당되고 "" 가 조건(criteria)에 해당되어  E3셀과 O3셀 범위중에

""(따옴표:공백) 과 같은 조건과 같은 셀을 세어 공란수를 표시하여 줍니다.

함수마법사를 이용하는 경우에는 D3셀에 "=COUNTIF"를 입력한 상태에서 키보드의 Ctrl키+A키를

눌러 함수마법사가 열리면 Range 부분에 공란수를 셀 범위를 Criteria부분에 조건 ""(공백)을 입력

한후 [확인]을 눌러 주어도 같은 결과가 나옵니다.