컴퓨터 활용능력 2급 실기 엑셀의 기초 - 5 [분석작업 2 , 부분합, 목표값찾기,정렬, 피벗테이블, 데이터 통합 , 시나리오 ]

분석작업.

---

• 부분합 
  

- 데이터베이스의 집합체에서 필요한 값을 합쳐 그룹화 한것을 부분합이라고한다.

• 정렬
  

- 데이터베이스의 집합체에서 값을 오름차순( 큰값이 1위 ), 내림차순 (작은값이 1위) 정리해주는것을 정렬이라고한다.

부분합 과 정렬은 문제를 풀이하면서 설명하도록 하겠다.

분석작업문제를 보도록 하자.

'지역별 미수금 현황' 표에서 "결제방식"별로 '할인액', '수금액' , '미수금'의 합계 평균을 계산하는 '부분합'을 작성.

▶ '결제방식'에 대한 정렬기준은 내림차순으로 하시오.

▶ 합계와 평균을 표시하는 순서에 상관없이 처리하시오.

문제를 확인해보니 정렬 과 부분합을 하도록 문제 해결을 요구하고있다.

우선 부분합을 하기전에 정렬을 진행하는것이 좋다. 

정렬을 하는 방법이다.

데이터 -> 정렬 을 누른다.

정렬을 하기전에 정렬을할 데이터들의 집합체를 범위로 잡고 진행해야한다.

( 정렬할때는 데이터베이스 전체를 진행하기 때문에 상단까지 잡는것이 맞다.)

정렬 전.

정렬 후.

만약 정렬이 잘못되었거나 수정해야할경우엔.

다시 정렬버튼을 누른후 정렬 기준을 변경하거나 [ 기준삭제] 를 눌러 기준을 삭제할수 있다.

이제 부분합을 활용할 때이다.

부분합은 아래 와 같이 진행할수 있다.

데이터 -> 부분합 으로 실행할수 있다. ( 엑셀 2016기준)

   

                                   

사용할 함수를 합계와 평균을 구하라고 했으니 2번 해주어야한다.

아래와 같이 출력되었을것이다. 이게 끝이다.

• 목표값 찾기
  

- 지정된 값을 목표값으로 설정되었을때 수식이 자동으로 변경해주는 기능이다.

문제를 보면서 해결해보도록 하자.

문제 : 

'자동차 판매 현황'표에서 크루지의 월납입금(F7)이 500,000이 되려면 상환기간(월)(E7)이 얼마가 되어야 하는지 목표값찾기 기능을 이용하여 계산하시오.

목표값찾기 위치 ( 엑셀 2016 기준)  

데이터 -> 가상분석 -> 목표값 찾기.

.

좌측과 같이 값이 나오게 된다. 인수는 3개를 받는 목표값찾는 함수이다.

수식셀 : 즉 조건이되는 값.

찾는 셀 : 조건이 변경되는 값.

값을 바꿀셀 : 조건이변경되면서 변화가 되는 셀.

이다.

크루지의 월납입금(F7)이 500,000이 상환기간(월)(E7)이 

수식셀 : F7 이되고, 찾는 값 : 500,000  ,   찾아바꿀 셀 E7  로 바꾸면 셀값이 변경된다.

값이 자동으로 변경된다.

목표값 찾기 끝이다.

• 시나리오

- 시나리오는 쉽게 생각하면 어떤 값이 변경되었을때 변경된 보고서를 자동으로 작성해주는 분석작업 부분이다. 문제를 통해 풀이를해보도록하겠다.

문제 :

'상공전자 제품판매 현황' 표에서 할인율[B20:C20]이 다음과 같이 변동하는 경우 판매총액 합계[I16]의 변동 시나리오를 작성하시오.

▶  셀이름 정의 :[B20] 셀은 '회원할인율' , [C20] 셀은 '비회원할인율' , [I16] 셀은 '판매총액합계'로 정의하세요.

▶ 시나리오1 : 시나리오 이름은 '할인율인상' 할인율은 회원 20% , 비회원 15%로 설정하시오.

▶ 시나리오2 : 시나리오 이름은 '할인율인하' 할인율은 회원 10% , 비회원은 5%로 설정하시오.

▶ 위 시나리오에 대한 '시나리오 요약' 보고서는 '분석작업-1' 시트 바로 앞에 위치시키시오.

※ 시나리오 요약 보고서 작성 시 정답과 일치하여야 하며, 오자로 인한 부분 점수는 인정하지 않음.

현재 분석작업-1에서 작업을 요구하는것은 

셀이름 정의, 시나리오 작성하여 위치하라는 뜻이다.

- 셀이름 정의 : 셀의 이름을 정의하는 것이다.

현재 셀이름 정의는 정말 쉽다.

정의할 셀을 선택하고 좌측 상단에 있는 2번 셀을 선택하여 셀의 이름을 정의 하면 끝이다.

위 와같이 정의하면 해당 셀을 클릭하면 좌측에 셀이름이 변경된것을 확인할수 있다.

그러면 셀이름 정의가 끝난다.

시나리오 지정법을 알아보자.

 데이터 -> 가상분석 -> 시나리오 관리자 -> 추가 (엑셀 2016기준)

문제지에서 요구하는 부분을 작성하면 된다.

시나리오값이 회원별 할인율 변동에대해 요구하였다. 

변경되어야하는 셀 을 범위잡으면 된다.

값은 1.00  = 100% 백분율 기준으로.

15 %->  20% 로 변경되어야하니 ( 0.15 -> 0.20)

10% -> 15 % 로 변경되어야함. ( 0.1 -> 0.15)

추가하면 아래와같이 생성된다. 

그리고 닫지 말고 2번째 인하부분도 있으니 추가해준다.

문제와 같이 작성했으니

아래와같이 생성되었을것이다 그리고 세번째 부분을 보니 시나리오 요약보고서를 '분석작업-1'시트 바로위에 위치하라고 했다.

표기 버튼을 누르면 현재 시나리오 대로 변경되버리므로 

문제에서 요구한 다른 위치에 추가되어야한다.

요약버튼을 클릭하면 아래와 같이 나온다.

이름이 정의된대로 값이 자동으로 입력된다.

결과 셀은 이름 정의한 '판매총액합계'가 자동으로 잡히므로 .

문제대로 확인하면.

아래와 같은 결과물을 출력해준다.

위치는 분석작업-1 앞에 위치하지않았다면 드래그 해서 끌어 앞으로 이동시킬수도있다.

이렇게 간단하게 시나리오 가 마무리 되었다.

• 피벗 테이블.

-  Pivot은 선회 축이라는 뜻입니다. 축을 중심으로 데이터나 결과값을 유동적으로 이동하고 변경할 수 있다는 개념에서 Pivot Table이라고 합니다. ( Microsoft - Hyung Shik Ohe)

쉽게 생각하면 데이터를 설정하면 자동적으로 변경되는 표라 생각하면 좋다.

문제를 보면서 보면서 피벗 테이블을 알아보도록 하자.

문제 :

'상공목장 유제품 납품 현황' 표를 이용하여 납품업체는 '보고서 필터', 제품명은 '행 레이블', 납춤일은 '열레이블로 처리하고, '값'에 납품 수량과 납품 총액의 합계를 계산한 후 행/열의 총합계는 표시하지 않는 피벗 테이블을  작성하시오.

▶ 피벗 테이블 보고서는 동일 시트의 [A22]셀에서 시작하시오.

▶ 보고서 레이아웃은 '개요 형식'으로 지정하시오.

▶ 보고서 레이아웃은 '개요 형식'으로 지정하시오.

▶ 납품수량과 납품총액의 합계는 셀 서식을 이용하여 표시 형식을 숫자 범주의 '1000 단위 구분기호'로 지정하시오.

문제를 해결해보자.

우선 범위를 잡고 

삽입 -> 피벗테이블 (엑셀 2016년 기준.)

문제 유형이 기존 워크 시트 A22 셀에서 부터 시작하라고했으니 기존 워크시트, 

새로운 워크시트라면 새 워크 시트를 클릭하면된다.

문제에 나와있는대로 드래그 하면된다.

문제에서  개요형식으로 표기하라했으니.

개요 형식으로 변경해줘야한다.

피벗테이블이 만들어진후 , 피벗테이블 아무 셀이나 클릭하면, 피벗테이블 디자인 메뉴가 생성된다.

디자인-> 보고서레이아웃 -> 개요형식으로 표기 를 누는다.

그리고 총합계 부분을 눌러, 행/열 총합계는 표기하지 않음을 추가해줘야하니.

피벗테이블 아무 셀 클릭후 -> 디자인 -> 총합계 -> 행 및 열의 총합계 해제를 클릭해주면.

마지막 부분에서 납품수량 , 납품 총액의 수 표기형식이 0,000 (천원) 단위로 끊어 표기하라고했으니.

아래 항목으로 들어간다.

규칙을 정할 항목을 마우스 우측클릭하여  

아래와 같이 진행한다.

납품수량과 납품 총액 2개를 적용해주어야한다. 

그럼 마무리된다.

최종된 값이다 아래와 같은결과가 나온다.

• 데이터 표기능 

-  표데이터를 자동으로 행, 열 값에 맞게 채워주는 기능이다.

예) 대출금이 금리, 개월수에 따른 자동으로 값을 채우주는 기능 이다.

문제를 풀어보며 해결해보도록하자.

문제 :

'대출액 상환' 표는 원금(C3), 개월수(C4), 이자율(C5)을 이용하여 월납입액(C6)을 계산한 것이다. '데이터표 ' 기능을 이용하여 개월수와 이자율의 변동에 따른 월납입액의 변화를 [D11:H15]영역에 계산하시오.

월납입액이 이자율, 개월수에 따른 값이 자동으로 입력되는 기능이다.

그럼 하는방법을 보도록 하자.

우선 월납입액에 따른 이자율, 개월수를 계산되는것이니.

C10 셀에 월납입액( C6 ) 셀을 복사하여 입력해준다.

그냥 복사하면 위에 와같은 에러가 발생할것이다.

당연하다.

왜냐하면 월납입액은 계산이 되어있는 수식이다 그렇다보니 복사하면 셀위치 만큼 아래로 내려오게된다.

해결방법은 

1. 위에 월납입액의 수식을 새로 입력

2. 절대참조 를 수식에 적용하여 복사하기 

3. 값을 그냥 입력하는방법 ( 월납입액 수로 그냥 입력)

3번은 조금 위험할수도 있다 2번 이나 1번을 사용하는것을 추천.

셀이 입력된후 범위를 잡는다 

데이터표의 위치는 

데이터 -> 가상분석 -> 데이터표  (엑셀 2016 버전기준)

행 :  (가로 ) 열 : (세로 )

<최종 결과값이다.>

이렇게 데이터 표가 마무리 되었다.

• 데이터 통합.
  

- 여러 데이터를 하나의 표에 통합 ( 합쳐주는) 하는기능이다.

데이터 통합 문제를 풀이하면서 해설해보도록 하겠다.

문제 : 데이터 통합 기능을 이용하여 [표1], [표2] , [표3]에 대한 제품별 '1월생산량', '2월생산량', '3월생산량'의 합계를 '1분기 아이스크림 생산 현황' 표의 [G13:G19] 영역에 계산 하시오.

우선 데이터 통합 메뉴의 위치를 알아보자.

데이터 -> 통합 ( 엑셀 2016 기준)

첫번째로 통합합 범위를 잡고 통합을 눌러야한다.! 

아래와 같은 창이 뜨는데 

참조는 합칠 데이터들의 범위를 추가해주면 완료다.

그리고 겹치는 데이터가 있을것이다. 첫번째 행과( 가로줄) , 왼쪽열 ( 세로줄)

두 체크박스를 체크하고확인을 누르면!

완료된다. 데이터 통합은 이게 끝이다 정말 거저주는 점수라고 생각하면 좋다.

이것으로 

분석 마무리를 하겠습니다.