Basic

엑셀 목표값 시나리오 가상 분석하기

판매 실적을 가지고 다음에는 얼마의 매출을 목표를 달성하려면 얼마나 목표를 잡아야 하나?

고민하게 됩니다. 이때 엑셀의 기능인 분석 도구인 목표값 찾기로 몇%로 목표를 잡으면 각 항목의

목표액을 자동으로 예측하여 줍니다.

아래의 도표를 통하여 매출액의 목표성장율을 정하면 각 항목별 매출액이 얼마인지 구하여 주는

것을 알아보겠습니다. 아래의 목표 성장율을 30%일때 다음달의 매출계획 금액이 나타나게 되는데요

만약 '매출 합계'가 197,133,105가 아닌 220,000,000원을 맞추려면 목표 성장율은 얼마인지 또

목표 성장율에 맞는 '매출 합계'는 각각 얼마인지 알아보겠습니다.

'매출 합계'를 220,000,000원으로 달성시키기 위해 수식이 작성된 D10셀(=sum(D4:D9))을 클릭한후

상단의 [데이터]=>[가상분석]=>[목표값 찾기(G)...]를 클릭하여 줍니다.

'목표값 찾기' 대화상자가 나타나면 /수식 셀/에는 D10셀이 자동으로 기입되며 /찾는 값(V):/ 란에 

목표값 220,000,000를 입력하고 /값을 바꿀 셀(C):/란에는 목표성장율값이 있는 D12셀을 입력하여

줍니다. '확인'을 클릭하여 줍니다. 

확인을 클릭하면 아래처럼  30%의 목표값일 때 처음의 각 항목별 매출 계획금액과 아래 그림 우측의

목표 매출 합계 금액을 220,000,000원으로 설정하였을때 목표 성장율이 45%로 그래고 각 항목의

매출 계획 금액이 목표 성장율과 함계 증가하였음을 확인할수 있습니다.

엑셀 지정한 행과 열의 데이터를 찾는 INDEX함수

엑셀의 INDEX함수는 지정한 행과 열의 위치에 있는 데이터를 찾아서 반환하여 줍니다.

아래에서 예를 들어 알아보겠습니다. 아래처럼 출장비 지급 명세서 작성을 위해 지급기준표에서 직급

과 출장지를 표에서 알맞게 찾아 넣는 방법을 INDEX함수를 통해 알아보겠습니다.

위에서 왼쪽의 출장비 지급 기준표가 있습니다. 출장자의 직급과 출장지역에 따라 금액이 달라지네요

우측의 지급 명세서를 보면 직급과 출장지에 번호가 적혀 있는데요 MATCH함수로 L3셀에서 이름이

'라니' 가 직급은 대리이고 출장지는 대구이므로 지급표의 직급에서 '사장','이사','부장','차장','과장'

다음 '대리' 즉 사장부터 1번째이면 대리는 6번째가 되지요 이것을 MATCH함수로  찾아 주었습니다.

MATCH함수를 간략히 설명하면  =MATCH(찾으려는 값, 찾을범위, 찾는방법)입니다.

INDEX함수로 '라니의 출장경비를 구해보면 =INDEX(찾으려는 값이 있는 셀범위, 행번호, 열번호)

이렇게 되는데요 '찾으려는 값이 있는 셀범위' : C3:G9  가 되겠지요. <$C$3:$G$9>

또 행번호는 6(대리 6번째) 이 되고 열번호는 2(지역 대구 2번째)가 되겠지요

그러면 라니의 출장경비 M3에서 '=INDEX(C3:G9,6,2) 이렇게 하면 출장경비인 87,000원이 구해집

니다. 6번째행 대리, 2번째열 대구 이렇게 따라가 보면 출장비 지급 기준표의 87,000원이 됩니다.

이와 같이 INDEX의 인수중에 행번호와 열번호를 숫자로 넣지 않고 MATCH 함수를 이용해 직접 넣어

보면 '=INDEX(C3:G9,MATCH(J3,B3:B9,0),MATCH(K3,C3:G3,0) ) 이렇게 직접 행번호와 열번호를

INDEX의 인자로 MATCH함수를 직접 넣어 구할수 있습니다.

위의 예제에서 MATCH함수를 INDEX함수의 인자로 넣어 직접 구했으므로 L열과 M열은 지워도 되겠

군요. MATCH함수를 이용하여 행과 열번호를 찾아 INDEX함수를 이용하여 찾은 행과 열번호를 지정

하여 표에서 데이터를 찾아보았습니다.

엑셀 데이터의 위치값을 찾아주는 MATCH함수

엑셀에서 찾는 값이 들어 있는 셀의 상대적인 위치값을 반환하여 주는 함수입니다. 찾을 값을 지정

하고 찾을 셀범위를 지정하여 셀의 위치값을 열과 행의 값을 찾는 방법을 알아보겠습니다.

 아래의 '출장비지급표'의 예를 들어 MATCH함수로 행과 열의 번호를 찾아내어 보겠습니다.

먼저 직급(행번호)를 찾기 위해 L3셀을 클릭한후 '=match(J3, B3:B9, 0)' 이렇게 입력합니다.

J3 은 찾으려는 값(대리), B3:B9는 찾으려는 값이 있는 셀범위, 0은 찾는 방법(일치된값). 그러면 아래

L3셀처럼 범위의 6번째에 있는 '대리' 6번째인 6을 반환하여 표시해 줍니다.

또한 열번호(출장지)의 값을 찾아 열번호를 반환하여 표시하기 위해서는 M3셀에서 '=match(k3, c3:g3

, 0)' 라고 입력한후 엔터를 클릭하여 줍니다. K3은 출장지인 '대구'를 C3:G3 범위에서 찾아 몇번째

값인지 반환하여 표시하라는 의미입니다. 

그런 다음 나머지 셀들의 값도 구하기 위해 아래가지 드레그 하거나 더블 클릭하여 줍니다.

주의할 점은 복사하기 위해 드레그나 더블클릭하기전에 함수 작성값중에 'lookup_array' (범위)에 해당

하는 값에는 '$'를 붙여 고정이 되도록 하여 줍니다. 범위를 선택시 [F4]를 눌러 셀 범위를 고정하여

줍니다.  ' B3:B9 ' => '$B$3:$B$9 ' , 'C3:G9 ' => '$C$3:$G$3' 이렇게 만들어 준후 수식복사

하여 줍니다.

엑셀 2013버전에서 새로이 추가된 함수들 2편

엑셀 에는 무수히 많은 유용한 함수들이 있습니다. 기존 함수에 추가로 13버전에서 추가된 함수를

알아보겠습니다.

먼저 2013버전에서 추가된 정보 함수입니다.

추가된 공학 함수입니다.

찾기 및 참조 함수

추가된 TEXT함수

웹 함수

통계함수

엑셀 2013 추가된 새로운 함수

엑셀 2010버전에서도 함수가 많이 추가되었지요. 새로운 엑셀 2013버전에서도 유용한 함수들이

많이 추가되었습니다. 날짜함수, 논리함수, 재무함수, 삼각함수등이 추가되었습니다.

  먼저 수학 및 삼각함수 추가된 함수입니다

엑셀2013 버전에서 추가된 재무함수 입니다.

추가된 논리함수입니다. 

추가된 날짜함수를 알아봅니다. 

엑셀 머리글 행의 이름을 이용하여 계산하기

엑셀 문서에서 표를 작성할때 매출표등에서 테두리의 색상과 셀색을 바구는 것만이 아니라

표의 머리글 행의 이름을 이용하여 수식을 작성할수 있습니다. 계산할때 숫자가 있는 셀을 마우스로

클릭하지 않고 자동으로 행의 머리글을 이용하여 작성하는 방법을 알아보겠습니다. 

아래의 간단한 엑셀 문서 '판매 현황'이란 예제를 들어 설명해 보겠습니다. 행의 머리글은 '품명',

'판매총금액', '제조원가', '판매이익', '점유율' 이렇게 되어 있습니다.

판매이익과 점유율 사이에 '이익율'을 머리글행의 이름을 이용해 구해 보겠습니다.

먼저 표를 예쁘게 만들어줍니다. 머리글을 포함하여 표를 만들 범위 즉 데이터가 있는 범위를

마우스로 드레그 한후 [홈]=>[표서식]을 클릭한후 적당한 디자인을 선택하여 줍니다.

적당한 표 형식을 선택하니 아래처럼 머리글과 표의 테두리색등의 디자인이 선택되어 표시됩니다.

'판매이익'과 '점유율' 사이에 '이익율'을 작성하기 위해 '점유율' E열을 클릭하고 '삽입'을 클릭합니다

그러면 바로 위쪽에 붓모양의 '삽입옵션'이 있는데요 여기를 누르고 "오른쪽과 같은 서식(R)"을

클릭하여 줍니다.

'열1'이라고 된부분을 '이익율'로 이름을 바꾸어 주고 E4셀에 " =[ "을 입력하면 자동으로 머리글

목록이 나타납니다. 여기서 "판매이익"을  더블클릭합니다.

곧바로 =[판매이익 다음 " ]/[ " 을 입력하며 자동으로 열 머리글 목록이 나타납니다. 여기서

"판매총금액" 을 더블클릭한후 " ] " 를 입력하고 엔터를 눌러줍니다.

그러면 아래표처럼 자동으로 E4셀 의 수식계산뿐만 아니라 E5셀에서부터 E12셀까지 자동으로

수식이 구해지게 됩니다.  이익율이 자동으로 모두 구해집니다.

엑셀에서 표 안의 수식을 계산할대  직접 셀을 클릭하거나 입력하지 않고 행의 머리글 목록을

이용하여 자동으로 수식을 구하는 방법을 알아보았습니다.

엑셀, 지정한 날짜와 기간 구하는 날짜 함수

엑셀 형식에서 날짜 형식으로 날짜를 계산할수 있습니다. TODAY함수는 오늘 현재 날짜를 구할수

있고 DATE함수는 지정한 숫자를 날짜를 구할수 있습니다.

이외에도 자동으로 주말이나 공휴일등을 제외하고 근무한 날을 자동으로 계산도 하여 줍니다.

TODAY함수와 DATE함수를 먼저 알아보겠습니다.

WORKDAY와 NETWORKDAYS함수

아래는 WORKDAY의 예제입니다. 시작일 7월 9일부터 공휴일을 제외하고 소요일수를 더한 날짜가

11월 26일 구해집니다. 단순히 숫자만 더한 날짜보다 더 많은 날짜가 표시됩니다. 근무일이 된것이죠

아래의 예제는 NETWORKDAY함수인데요 시작일과 종료일을 지정한하여 WORKDAY함수와 마찬

가지로 공휴일을 제외하고 실질적인 근무일을 계산하여 줍니다.

DATEDIF함수

엑셀, 꺽은선 그래프 막대그래프가 있는 혼합차트 만들기

엑셀에서 도표를 가지고 차트를 그리다 보면 데이터값이 비슷한 경우에는 상관 없지만 데이터값의

차이가 크거나 다를 경우 차트를 그리면 작은 값이 너무 밑에 있어 데이터를 분석하기가 어렵습니다.

이런경우에는 축을 나눠서 서로 다른 종류의 차트를 혼합하여 사용하면 좋습니다.

꺽은선그래프와 막대그래프를 혼합하여 차트를 만드는 방법을 알아보겠습니다.

먼저 아래 예제 매출분석 도표를 이용해 꺽은선 그래프이 차트를 먼저 만듭니다.

차트를 만들 데이터 범위를 선택합니다. 항목에서부터 6월 합계란까지 마우스로 드레그한 후

상단메뉴의 [삽입]=>[꺽은선형]에서 적당한 꺽은선을 선택하여 기본차트를 만듭니다.

아래그림처럼 꺽은선의 기본차트가 만들집니다. 만들어진 차트를 보면 합계의 그래프가 다른 선에

비해 데이터값이 높아 위쪽에 그려지고 세로축 범례도 범위가 600까지 발생합니다. 합계데이터가

아니라면 세로축이 200까지만 있어도 되겠지요.

합계 그래프를 막대그래프로 바꿔봅니다. 합계 그래프를 마우스로 선택후 마우스 우측 버튼을

클릭한후 대화상자에서 [계열차트 종류 변경]을 클릭합니다.

'차트 종류 변경' 대화상자에서 [세로막대형]에서 적당한 세로막대형을 클릭하여 선택합니다.

'합계' 데이터의 그래프가 꺽은선=>막대 로 변경되었습니다. 그런다음 막대그래프를 선택하여

마우스 우측 버튼을 클릭하여 [데이터 계열서식(F)]를 선택하여 줍니다.

'범례 서식' 대화상자에서 [범례옵션]=>'아래쪽'을 선택하면 범례가 차트 그래프 하단으로 이동하게

됩니다. 그래프 우측에는 보조 축 서식으로 '합계'인 막대그래프의 축이름을 넣을 것입니다.

범례를 하단으로 이동한후 막대그래프를 마우스로 선택한후 우측마우스 버튼을 클릭하여 [데이터

계열 서식(F)]를 클릭합니다. '데이터 계열 서식' 에서 [계열 옵션]에서 '보조축(S)'를 선택합니다.

아래는 우측에 '합계'데이터 막대차트의 데이터 범위 축이름입니다. 왼쪽에는 월별 매출 데이터

범위이고 우측의 숫자는 '합계' 데이터의 범위가 되는 것이지요

또 위의 보조축 데이터 범위 위쪽에 '월별'이란 축이름이 있는데요 이것은 보조 세로축 제목입니다.

입력방법은 [레이아웃]=>[축제목]=>[보조 세로 축 제목]=>[가로제목]을 선택하여 입력합니다.

아래 그림은 상반기 매출 분석 도표를 가지고 월별 항목의 매출 차트와 월별 항목들의 합계를

나타내도록 한 차트 완성입니다. 꺽은선과 막대가 혼합된 형태입니다.

데이터가 다르거나 데이터의 값의 차이가 큰 값들은 이렇게 혼합된 형태의 차트를 만들어 작성하는

것이 데이터의 분석에 유리합니다. 좀더 멋지게 만들어 보세요

엑셀(EXCEL) 머리글 바닥글 작성하는 방법

엑셀은 워크시트가 셀로 구성되어 있어 한글 같은 워드프로그램처럼 용지에 맞게 문서를 작성할 수

없습니다. 셀을 무제한으로 작성할수 있기 때문이죠.

작성한 문서를 프린트 할때 머리글과 바닥글을 입력하여 출력하는 방법을 알아보겠습니다.

아래처럼 데이터가 많은 주소록이 있다고 가정하고 주소록의 머리글과 바닥글을 작성하여 봅니다.

엑셀 상단의 메뉴중에 [보기]=>[페이지레이아웃]을 클릭하여 줍니다.

그러면 아래처럼 워드프로세서처럼 편집 용지가 보이게 됩니다. '머리글'이라고 되어 있는 중간 부분

을 클릭하면 머리글을 작성할 수 있습니다. '주소록'이라고 작성합니다.

머리글은 중간도 있지만 왼쪽과 오른쪽에도 각각 내용을 작성할수 있습니다.

또 상단메뉴의 [디자인]=>[파일이름]을 클릭하면 파일의 제목을 자동으로 입력하여 줍니다.

머리글을 입력하였으면 바닥글을 입력하여 줍니다. [디자인]=>[바닥글로 이동]을 클릭합니다.

[바닥글]에서 '?페이지중 1페이지"를 클릭하면 전체문서 페이지의 몇번째 페이지를

나타내어 줍니다. 물론 머리글과 같이 바닥글을 표시할 부분을 눌러 직접 입력할 수도 있습니다. 

아래는 바닥글의  페이지를 번호를 나타내는 '2페이지 중 1페이지'를 나타나게 입력한 화면입니다.

아래 그림은 위에서 설명한 머리글과 바닥글을 표시한 내용입니다. 첫번째 페이지 하단에는 바닥글

두번째 페이지 상단에는 머리글이 표시되었습니다.

[디자인]=>[옵션] 그룹에서 '첫페이지를 다르게 지정'을 체크 표시하면 전체 페이지중 첫 페이지만

다른 머리글과 바닥글을 구성할수 있습니다. 또 [짝수와 홀수페이지를 다르게 지정]하면 페이지 번호

등의 위치등을 구분할수도 있습니다. 인쇄하기전 '미리보기'를 하여 프린트하여 보세요

엑셀 최대값과 최소값이 클경우 중간 생략 차트 만들기

엑셀에서 도표를 가지고 차트를  만들수 있습니다. 차트로 도표를 나타내면 알아보기도 쉽고

엑셀문서가 고급화(?)되어 집니다.

그러나 도표의 수치가 최대값과 최소값의 차이가 큰경우 차트를 그리면 보기가 않좋습니다.

이번시간에는 최대 최소 간격이 큰 데이터의 차트를 중간 생략형 차트를 만들어 보겠습니다.

아래처럼 차트를 만들 데이터 도표가 있습니다. 차트를 만들 데이터 영역의 최대값과 최소값이

차이가 큽니다. 데이터 영역을 마우스로 드레그하여 선택한후 [삽입]=>차트탭에서[세로막대형]=>

2차원 기본 막대그래프를 선택합니다.

아래그림은 기본 막대차트가 생성된 화면인데요 여기서 차트제목을 알맞게 변경하고 우측의 범례

'순이익'을 지워줍니다.

차트의 세로축 제목을 마우스 우측버튼을 클릭하여 대화상자가 나타나면 맨아래 '축 서식(F)..'를

클릭하여 줍니다.

축서식 대화상자에서 '축옵션' 에서 아래 그림처럼 최소값과 최대값, 주 단위, 보조 단위를 데이터의

범위에 맞게 수정하여 줍니다. 세로축에서 금액을 나타내는 '0'이 너무많으므로 보기좋게 "'표시단위(U)'를 "천"으로 바꾸어 주고 '차트에 단위 레이블 표시(S)'를 체크 해제 합니다.

그러면 아래처럼 차트가 정리됩니다. 여기서 차트를 선택한후 [Ctrl]키를 누른체 차트를 마우스로

끌면 차트가 복사가 됩니다. 복사된 차트를 수정하여 최대값을 나타낼수 있게 만들 것입니다.

복사된 '차트2'에서 세로축 제목을 마우스 우측 버튼을 클릭하여 '축서식(F)'를 클릭하여 수정합니다.

세로 '축서식'에서 [축옵션]에서 최소값과 최대값을 중간을 생략하고 높은값을 나타내는 차트 범위로

알맞게 수정합니다. 여기서는 높은값이 1,650,000원이 되므포 최대값을 1,800,000으로 최소값을

1,500,000으로 수정하였습니다. 차트 데이터에 따라 조정합니다. '닫기'를 클릭합니다.

수정되어진 '차트2'를 마우스 우측버튼을 클릭하여 '차트영역서식(F)'를 클릭하여 '테두리 색'을

'선없음'을 선택한후  닫기를 클릭합니다.

아래그림처럼 테두리가 없어진, 그리고 세로축 단위가 수정된 '차트2'가 완성되었습니다. 차트2의

크기를 마우스로 조정하고 이동하여 차트1과 차트2가 상단으로 겹쳐지게 조정하여 줍니다.

그런다음 차트 1과 차트2의 중간에 물결모양을 넣어 생략된것처럼 보이기 위해 물결모양의 도형을

삽입합니다. [삽입]=>[도형]에서 물결모양을 클릭한후 차트의 중간 부분에 드리워지도록 그려줍니다.

물결모양의 도형을 차트 중간에 그린후 물결도형의 채우기를 흰색으로, 도형윤곽선도 흰색으로 변경

하여 줍니다.[서식]=>[도형채우기] 흰색, [도형윤곽선] 흰색,

아래그림은 차트의 중간부분을 생략한 완성 차트입니다. 설명하느라 좀이상한 부분이 좀 있네요

세로축을 보면 100,200,300,400,500,1500,1700,1800 이렇게 500에서 1500으로 중간이 생략이

되었습니다.

실제로는 2개의 차트를 범위를 다르게 하여 겹쳐 놓은 것이지요. 이렇게 값이 범위가 큰 데이터를

가지고 중간을 생략한 차트를 그리는 방법을 원본차트를 복사하여 중간이 생략된 차트를 그리는

방법을 알아보았습니다.