Ch11_ 데이터분석과피벗테이블
이장에서다룰내용 01_ 부분합 02_ 피벗테이블과피벗차트 03_ 데이터도구 2
01_ 부분합 부분합작성 부분합대화상자 ➊ ➋ ➌ ➍ ➎ ➏ ➐ ➊ 그룹화할항목 : 값을구하는기준이되는항목을선택한다. ➋ 사용할함수 : 그룹화할필드에적용할함수를선택한다. ➌ 부분합계산항목 : 함수를적용해서계산할필드를선택한다. ➍ 새로운값으로대치 : 기존의부분합결과를없애고, 현재의결과로대치한다. ➎ 그룹사이에서페이지나누기 : 각그룹다음에페이지가나뉜다. ➏ 데이터아래에요약표시 : 부분합결과를표시할위치를선택한다. ➐ 모두제거 : 부분합을해제하고, 원본데이터목록을표시한다. 3
01_ 부분합 윤곽기호» 1 은전체결과 ( 총합계 ) 만표시, 2 는소계만표시, 3 은전체데이터를표시한다.» 확대버튼 (+ 기호 ) 을클릭하면숨겨져있는하위수준 ( 그룹 ) 의데이터를표시하며, 축소버튼 (- 기호 ) 을클릭하면하위수준 ( 그룹 ) 의데이터를숨긴다. 부분합기능을사용하지않고도 [ 데이터 ] 탭의 [ 윤곽선 ] 그룹에있는 [ 그룹 ] 도구를사용해서그룹을설정할수있음 단, 합계나기타수식이입력되어있는셀에셀포인터를두거나그룹으로묶을행이나열을범위로지정한후, 그룹을설정해야함 여러그룹으로부분합작성 두가지이상의그룹으로부분합을작성하려면우선두가지이상의기준으로정렬해야함 부분합을작성할때는우선큰그룹에대한부분합을작성하고, 다시두번째그룹에대한부분합을작성해야함 부분합대화상자에서 새로운값으로대치 옵션을해제하고작성해야함 4
01_ 부분합 실습 11-1 대리점별매출소계구하기 준비매출집계표.xlsx 완성매출집계표 ( 완성 ).xlsx 날짜별로작성된매출집계표에대해대리점별매출합계를구하도록부분합을작성해보자. ➊ 데이터정렬하기 ➋ 부분합작성하기 ➌ 윤곽기호사용하기 5
02_ 피벗테이블과피벗차트 피벗테이블 부분합기능이더발전된형태, 보통한가지필드에만그룹을지어소계를구함 표모양도데이터목록형태를벗어나지않음 여러필드에대한그룹별소계를구하고, 많은양의데이터를한눈에쉽게파악해서요약 / 분석하려면피벗테이블기능을사용할수있음 피벗차트 피벗테이블의요약데이터를시각화할수있음 데이터를간편하게비교할수있으며, 데이터의패턴과추세를볼수있음 피벗테이블작성하기 피벗테이블보고서 대량의데이터를빠르게요약하는데사용하는대화형테이블 피벗테이블을작성하려면 1 원본데이터를정의하고 2 피벗테이블이만들어질통합문서내의위치를지정해야하며 3 필드레이아웃을설정해야함 6
02_ 피벗테이블과피벗차트 원본데이터정의및위치지정 [ 삽입 ]-[ 표 ]-[ 피벗테이블 ] 을클릭하면피벗테이블만들기대화상자가표시됨 대화상자에서원본데이터목록의범위를지정하고피벗테이블보고서를작성할위치를지정함 필드레이아웃설정 엑셀 2007 에서는피벗테이블필드목록창에서레이아웃을설정함 필드선택목록에서보고서에표시할필드를선택하면기본적으로숫자값이있는필드는값영역으로들어가고, 나머지는행레이블영역으로들어감 아래쪽영역에서각필드버튼을드래그하여위치를조정 7
02_ 피벗테이블과피벗차트 피벗테이블레이아웃및디자인선택하기 피벗테이블보고서를작성한후레이아웃과서식을수정하면보고서의내용을더욱이해하기쉽고, 보기좋게만들수있음 피벗테이블을만들고나면리본메뉴에다음과같은피벗테이블도구가생기며, [ 디자인 ] 탭을사용하여레이아웃과서식을수정할수있음 피벗차트작성하기 피벗테이블보고서의데이터를그래픽으로나타냄 피벗차트에도필드의위치를변경하여레이아웃과표시데이터를변경할수있음 피벗차트에서레이아웃을변경하면피벗테이블의레이아웃도변경됨 8
02_ 피벗테이블과피벗차트 실습 11-3 피벗테이블작성하기 준비매출집계표 3.xlsx 완성매출집계표 3( 완성 ).xlsx 피벗테이블을만든후피벗테이블의구성요소를다양하게활용해보자. ➊ 새시트에피벗테이블만들기 ➋ 필드영역이동하기 ➌ 날짜그룹지정하기 ➍ 보고서필터사용하기 ➎ 데이터항목의다중선택 ➏ 데이터이동하기 ➐ 하위레코드확장 /. 축소하기 ➑ 데이터요약기준변경하기 ➒ 필드표시형식변경하기 9
02_ 피벗테이블과피벗차트 10
02_ 피벗테이블과피벗차트 11
02_ 피벗테이블과피벗차트 12
03_ 데이터도구 데이터도구 텍스트를여러셀에나누어넣거나중복항목을일률적으로제거하고, 유효성검사로입력오류를줄이며, 비슷한형식의여러데이터범위를하나의표로통합하여 시나리오, 목표값찾기등의가상분석을사용할수있는데이터도구 텍스트나누기, 중복항목제거, 유효성검사 텍스트마법사 텍스트형식의파일을워크시트로가져올때, 또는워크시트의한열에입력되어있는데이터를구분기호나일정한너비로분리하여각셀에입력하는도구 텍스트마법사대화상자의각단계를따라가며설정사항을선택 13
03_ 데이터도구 중복항목제거 엑셀 2007 에서새로추가된기능으로, 선택한항목에포함된중복레코드를일률적으로제거 데이터유효성검사 데이터가입력될각셀에조건을설정하여특정데이터만입력되게하거나 특정한셀을선택하면지정한형식으로자동변환되게할수있음 양식을작성한사람과사용하는사람이다른경우에설정해두면좋음 14
03_ 데이터도구 데이터통합 데이터의형태가다르더라도같은이름표 ( 필드명 ) 를사용하면필드항목을기준으로통합할수있음 필드명이다른경우에는해당필드들을모두그대로가져오면서통합 통합기능에서사용할수있는함수로는합계, 개수, 평균, 최대값, 최소값, 곱, 숫자개수, 표본표준편차, 표준편차, 표본분산, 분산이있음 가상분석 정확하고신속한의사결정을위해서는상황에따라어떤결과가나올것인지미리예측해야함 가상분석도구에있는목표값찾기, 시나리오기능을사용하면워크시트에입력한데이터를토대로가상의상황을손쉽게분석할수있음 15
03_ 데이터도구 목표값찾기 수식에서원하는결과 ( 목표 ) 값은알고있지만, 그결과값을계산하기위해필요한입력값을모를경우에사용함 목표값찾기는주어진결과값에대해하나의입력값만변경할수있으며, 결과값은입력값을참조하는수식으로작성되어있어야함 ➊ ➋ ➌ ➊ 수식셀 : 결과값이출력될셀주소를지정한다 ( 해당셀에반드시수식이있어야함 ). ➋ 찾는값 : 목표로하는값을입력한다. ➌ 값을바꿀셀 : 목표값을만들기위해변경되는값이들어있는셀주소를지정한다 ( 수식이아닌값이있어야함 ). 시나리오 다양한상황과변수에따른여러가지결과값의변화를, 가상의상황을통해예측하여분석하는도구 셀값의변동에대한서로다른여러시나리오를만들어변화하는결과값을예측하기위해사용 16
03_ 데이터도구 실습 11-6 텍스트나누기, 중복항목제거및유효성검사 준비사원명단.xlsx 완성사원명단 ( 완성 ).xlsx 이름과직급을각각다른셀에나누고, 중복된레코드를제거하고, 입력가능한데이터목록을정해놓자. ➊ 구분기호로텍스트나누기 ➋ 중복항목제거하기 ➌ 데이터유효성검사하기 17
03_ 데이터도구 18
03_ 데이터도구 실습 11-7 데이터통합하기 준비매출현황.xlsx 완성매출현황 ( 완성 ).xlsx 각각다른시트에작성되어있는매출현황파일을시트하나에통합해보자. ➊ 상반기시트통합하기 ➋ 하반기시트통합하기 ➌ 서식지정하기 19
03_ 데이터도구 실습 11-8 가상분석하기 준비매출계획표.xlsx 완성매출계획표 ( 완성 ).xlsx 목표값찾기와시나리오기능을사용하여원하는값을찾아보고, 시나리오요약보고서를작성해보자. ➊ 목표값찾기 ➋ 시나리오작성하기 ➌ 시나리오표시및요약보고서만들기 20