Transcript 제 1장 엑셀의 기초
제 8장 데이터 관리 분석 차례 step 1 데이터베이스와 데이터 관리 step 2 데이터 정렬 step 3 필터링-자동필터/고급필터 step 4 레코드 관리 step 5 부분합 구하기 step 6 피벗 테이블 만들기 Step 7 목표값 찾기 Step 8 시나리오 만들기 Step 9 떨어져 있는 셀의 차트 그리기 엑셀2000 제9장 데이터 관리 분석 1 1. 데이타베이스와 데이터 관리 : 데이터베이스는 어떤 대상의 데이터를 체계적으로 모아놓고, 데이터를 보다 쉽 게 검색하거나 필요한 데이터만 따로 추출할 수 있다. 데이테베이스의 특징 워크시트에서 서로 관련된 데이터가 들어 있는 일련의 행을 목록이라고 한다. 데이터베이스의 열은 필드(Field)로 행은 레코드(Record)로 이용한다. 목록의 최상위 행에는 반드시 필드명(열 이름표)을 기재해야한다. 엑셀2000 제9장 데이터 관리 분석 2 1. 데이타베이스와 데이터 관리 데이터 관리 기능 데이터를 크기순으로 정렬하거나 추출할 때에는 열 기준에 의해 분류ㆍ검색이 가능 하다. 메뉴에서 [레코드 관리]를 이용해서 데이터의 추가, 삭제, 변경이 가능하다. 피벗 테이블의 표 기능을 이용해서 특정 기준에 의한 집계표 작성이 가능하다. 데이터베이스 관련 용어 엑셀2000 제9장 데이터 관리 분석 3 2. 데이터 정렬 : 정렬은 작은 값에서 큰 값으로 정렬되는 오름차순과 큰 값에서 작은 값으로 정 렬되는 내림차순이 있다. 기본 정렬 순서(오름차순 정렬일 때) 숫자는 가장 작은 음수에서 가장 큰 양수로 정렬된다. 논리값은 TRUE보다 FALSE가 앞에 정렬된다. 오류값의 정렬순서는 모두 같고, 빈 셀은 항상 마지막으로 정렬된다. 실습(P260) : 메뉴를 이용한 데이터 정렬 엑셀2000 제9장 데이터 관리 분석 4 2. 데이터 정렬 정렬 정렬 이전의 상태로 되돌리려면 정렬 명령을 수행하고 난 후 [편집] [정렬 취소] 메 뉴를 선택하거나 표준 도구모음의 실행취소 아이콘을 누른다. 데이터를 정렬할 때 분류 기준 항목은 3개까지 지정할 수 있다 정렬 기준이 되는 열의 셀을 선택하고 정렬 도구를 누르면 현재 열을 첫 번째 기준으 로 자동 정렬된다. 정렬 기준이 되는 열의 셀을 선택하고 정렬 도구를 누르면 현재 열을 첫 번째 기준으 로 자동 정렬된다. 사용자 정의 정렬 가나다순이나 역순으로 정렬하지 않고 월별, 요일별 등으로 정렬하거나 사용자가 직 접 정렬 순서를 지정하여 수행하고자 할 때 이용한다. 사용자 정의 정렬 순서는 사용자 정의 목록에 등록되어 있는 것만 가능하므로 사용 자 정의 정렬을 수행하기 전에 원하는 목록이 있는지 확인하고, 만약에 목록에 등록 되어 있지 않으면 기준으로 사용할 목록을 추가한 다음 수행한다. 사용자 정의 정렬 순서는 첫 번째 기준에만 적용이 가능하다. 엑셀2000 제9장 데이터 관리 분석 5 2. 데이터 정렬 실습(P262) : 사용자 정의 정렬 순서로 정렬하기 엑셀2000 제9장 데이터 관리 분석 6 2. 데이터 정렬 실습(P262) : 사용자 정의 정렬 순서로 정렬하기 엑셀2000 제9장 데이터 관리 분석 7 3. 필터링 : 데이터베이스에서 가장 빈번하게 이루어지는 작업은 전체 레코드 중 특정 조건 을 만족하는 레코드만을 추출하는 것으로 워크시트에서 데이터를 검색하려면 필터 기능을 사용한다. 필터의 종류에는 자동 필터와 고급 필터가 있다. 실습(P265) : 자동필터로 데이터 추출하기 엑셀2000 제9장 데이터 관리 분석 8 3. 필터링 : 차트의 제목이나 축 제목, 축 레이블, 범례 등을 새롭게 추가하거나 변경 및 삭 제할 수 있다. 자동 필터 복원 필터 단추를 누르고 ‘모두’ 항목을 선택하면 모든 행이 다시 워크시트에 표시된다. [데이터] [필터] [자동 필터] 메뉴를 다시 선택하면 자동 필터가 해제된다. 필터 단추 여러 개를 선택하여 필요한 항목을 선택하면 모든 항목을 포함하는 행만 표시 된다. 자동 필터 옵션 : 자동 필터를 설정하고 필터 단추를 누른 후 [사용자 정의] 항목이나 [Top 10] 항 목을 선택해서 조건을 지정, 행을 검색하거나 일정한 범위 내에 들어 있는 행만 나타낼 수 있다. 실습(P268) : 자동 필터 옵션 이용하기 엑셀2000 제9장 데이터 관리 분석 9 3. 필터링 엑셀2000 제9장 데이터 관리 분석 10 3. 필터링 고급 필터 추출하는 데이터들을 다른 위치로 표시하거나 검색 레코드의 특정 필드에 해당하는 데이터만 추출 가능하다. 고급 필터 기능을 사용할 경우 전체 데이터 범위와 조건 범위 그리고 필요에 따라 결 과값이 나타날 범위를 같은 필드명과 함께 지정한다. 조건 범위 작성 규칙 조건 범위의 첫머리에는 목록과 동일한 필드명을 반드시 기재한다. 특정값을 입력하면 일치하는 항목만 검색 할 수 있다. 비교 연산자를 사용하여 검색 범위를 지정할 수 있다. 대표 문자를 사용하여 조건을 지정할 수 있다. 같은 행에 조건을 입력하면 AND 조건이 되고, 다른 행에 조건을 입력하면 OR 조건 이 된다. 실습(P271) : 고급 필터를 이용한 검색 (총점이 ‘240’ 보다 큰 데이터만 고급 필터로 작성.) 엑셀2000 제9장 데이터 관리 분석 11 3. 필터링 엑셀2000 제9장 데이터 관리 분석 12 3. 필터링 고급 필터 옵션 현재 위치 : 고급 필터 결과를 현재 위치에 일시적으로 표시한다. 다른 장소에 복사 : 고급 필터 결과를 지정한 복사 위치에 표시한다. 데이터 범위 : 고급 필터의 대상이 되는 워크시트의 데이터 범위를 지정한다. 찾을 조건 범위 : 데이터 범위의 필드명과 동일한 필드명을 갖는 조건 범위를 지정. 복사 위치 : 다른 장소에 복사 옵션을 선택했을 때만 결과가 복사될 위치를 지정한다. 동일한 레코드는 하나만 : 고급 필터의 결과가 동일한 경우 하나만 나타낸다. 실습(P273) : 필터 결과를 다른 장소에 복사하기 엑셀2000 제9장 데이터 관리 분석 13 4. 레코드 관리-레코드 검색 고급 필터 옵션 [레코드 관리] 대화상자를 사용해서 데이터의 추가, 삭제, 변경, 추출이 가능하다. 레코드 검색이란 워크시트의 데이터 중에서 지정한 조건을 만족하는 레코드만 대화 상자를 통해 보는 것을 말한다. 수식이 입력된 필드는 레코드 대화상자에서 입력 불가능한 상태로 나타난다. 엑셀2000 제9장 데이터 관리 분석 14 4. 레코드 관리-레코드 검색 명령 단추의 기능 실습(P275) : 레코드 찾기와 수정 검색 조건 지정 방법 입력란에 여러 개의 조건을 입력하면 모든 조건을 만족하는 레코드만 검색한 다.(AND 조건을 의미) 엑셀2000 제9장 데이터 관리 분석 15 4. 레코드 관리-레코드 검색 대표 문자를 사용하여 비교 조건을 입력할 수 있다. 물음표(?)와 별표(*) : (예) : ?2?4 : ?가 있는 위치는 한문자에 대해 어떤 값이던 모두 허용 (예) : 장* : 장 다음에는 1문자이상 어떠한 문자가 되던 모두 허용 실습(P275) : 레코드 추가 엑셀2000 제9장 데이터 관리 분석 16 4. 레코드 관리-레코드 검색 레코드 추가 규칙 목록에 필드명(열 이름표)이 반드시 포함되어 있어야 한다. 한 번에 32개의 필드(열)까지 표기할 수 있다. 수식이 들어 있는 필드는 입력에서 제외된다. 데이터를 입력하고 enter 를 누르면 수식이 들어 있는 필드는 자동으로 계산되어 결 과가 워크시트에 표시된다. 특정 레코드 관리 : 레코드를 삭제하려면 먼저 삭제할 레코드를 레코드 관리 대화상자에 나타내어야 한 다. 실습(P278) : 레코드 삭제하기 엑셀2000 제9장 데이터 관리 분석 17 6. 부분합 구하기 메뉴에서 [데이터] [부분합]을 선택해서 목록의 부분합과 총합계를 자동으로 구할 수 있는데 부분합을 구하기 위해서는 먼저 부분합의 기준이 항목에 의해 크기순으 로 정렬한다. 여러 수준으로 부분합을 작성하려면 부분합을 구할 모든 열로 우선 순위를 정하여 정렬해야 하며 부분합이 삽입된 상태에서 데이터를 변경하면 부분합과 총합계가 자 동으로 재 계산된다. 실행과정 엑셀2000 제9장 데이터 관리 분석 18 6. 부분합 구하기 실습(P280) : 학과별 부분합 엑셀2000 제9장 데이터 관리 분석 19 5. 부분합 구하기 하위 수준 표시 실습(P282) : 하위 데이표시 선택 엑셀2000 제9장 데이터 관리 분석 20 6. 피벗 테이블 만들기 : 엑셀 목록, 데이터베이스, 외부 데이터, 다른 피벗 테이블 등을 원본으로 하여 많은 양의 데이터를 여러 방법(합계, 평균, 개수 등)으로 요약해서 특정 조건에 해당하는 집계표를 작성하는 것을 피벗 테이블이라고 한다. 원본 데이터의 행 또는 열의 위치를 변경하여 데이터를 여러 모양으로 표시할 수 있 다. 특정 항목으로 필터링 하거나 상세히 보기 등을 지정할 수 있다. 각 필드에 여러 조건을 지정 가능하며 분야별로 데이터를 집계할 수 있다. 피벗 테이블은 작업중인 워크시트나 새로운 워크시트에 작성 가능하다. 피벗 테이블을 작성한 다음에라도 기본 데이터 값을 변경하면 피벗 테이블의 데이터 값 역시 자동으로 바뀐다. 실습(P285) : 피벗 테이블 작성 엑셀2000 제9장 데이터 관리 분석 21 6. 피벗 테이블 만들기 피벗 테이블의 구조 쪽 필드 : 요약된 데이터를 필터링 하는 데 사용할 필드 행 필드 : 행 방향으로 지정된 것으로 두 개 이상 지정 가능 열 필드 : 열 방향으로 지정된 것으로 두 개 이상 지정 가능 데이터 필드 : 일반적으로 수치 데이터를 여러 종류의 함수로 요약 엑셀2000 제9장 데이터 관리 분석 22 6. 피벗 테이블 만들기 실습(P288) : 피벗 테이블 보기 피벗 테이블의 도구모음 엑셀2000 제9장 데이터 관리 분석 23 7. 목표값 찾기 : 특정 셀의 값을 변화시켜 수식에서 원하는 결과값을 찾을 때 사용한다. 값을 변화시키려는 셀은 결과를 계산하는 수식에서 반드시 참조하고 있어야 한다. 실습(P290) : 판매가격 = (단가*수량)-(단가*수량*할인율) 해 찾기 : 목표값은 하나의 셀 값을 변화시켜 원하는 결과 값을 찾는데 비하여, 해답 찾기는 여러 셀의 값을 변경하여 결과 값을 찾는다. 또한 변화하는 셀에 여러 조건을 사용할 수 있다. 엑셀2000 제9장 데이터 관리 분석 24 8. 시나리오 만들기 : 워크시트를 다양한 방법으로 예측할 때 사용하는 값의 집합을 시나리오라고 한다. 워크시트를 구성하는 셀의 가상 값을 저장하여 결과를 예측하여 나타낼 수 있다. 실습(P290) : 어느 가게의 할인율1 (수량 200개 판매 할 때는 10% 할인) 할인율2 ( 수량 300개 판매 할 때는 20% 할인) 엑셀2000 제9장 데이터 관리 분석 25 8. 시나리오 만들기 시나리오 관리자 명령 단추 표시 닫기 추가 삭제 편집 병합 요약 : 선택한 시나리오를 워크시트에 반영하여 결과를 본다. : [시나리오 관리자] 대화상자를 닫고 워크시트로 복귀한다. : 새로운 시나리오를 정의한다. : 선택한 시나리오를 목록에서 삭제한다. : 선택한 시나리오의 이름, 변경, 셀, 설명, 가상 값 등을 변경한다. : 다른 통합문서의 시나리오를 현재 시나리오 목록에 결합한다. : 시나리오 요약 보고서, 피벗 테이블 보고서를 만든다. 엑셀2000 제9장 데이터 관리 분석 26 8. 시나리오 보고서 : 사용자가 정의한 시나리오를 보고서 형태로 만드는 것으로 시나리오 요약 보고 서와 시나리오 피벗 테이블 보고서 두 종류가 있다. 시나리오 요약 보고서는 변 경 셀의 값의 변화에 따라 결과 셀의 값이 어떻게 변경되는지 한 눈에 알아볼 수 있도록 작성되고, 시나리오 보고서는 새로운 워크시트에 만들어진다. 실습(P296) : 시나리오 요약 보고서 엑셀2000 제9장 데이터 관리 분석 27