제 9 장 손 쉬운 데이터 입력부터 간단한 관리까지

제 9 장손 쉬운 데이터 입력부터간단한 관리까지 You Can Do It! I can do it!

차 례 9.1 데이터베이스의 레코드 관리하기 데이터 관리를 위한 기본기능으로 정렬기능에 대하여 배운다. 9.2 필터링 기능으로 데이터 검색하기 특정 조건에 맞는 자료를 쉽게 검색해 볼 수 있는 필터 기능을 배운다. 9.3 부분합으로 직책별 기본급 산출하기 데이터베이스 자료 중 특정 항목들의 합계나 평균을 쉽게 파악하기 위해 제공되는 부분합에 대하여 배운다. 9.4 시나리오 관리자를 이용한 대출금 상환액 알아보기 다양한 상황에 따른 여러가지 결과를 예측할 수 있는 기능을 배운다. 9.5 목표값을 이용하여 상환액에 따른 대출액 계산하기 수식의 결과를 알고 있으나 입력밧을 모르는 경우 사용법을 배운다. 9.6 메모를 이용한 셀 설명 붙이기 임의의 셀에 간단한 설명을 붙일 때 사용하는 메모 기능에 대하여 배운다.

이 장에서 배울 내용 • 정렬: 입력된 자료를 특정키에 의해 순차적으로 정리하는 기능 • 필터: 특정 조건에 맞는 자료를 쉽게 검색해 볼 수 있도록 하는 기능 • 부분합: 데이터베이스 자료 중 특정 항목들의 합계나 평균 등을 쉽게 파악하기 위하여 제공되는 기능 • 시나리오 관리자: 데이터에 대하여 다양한 상황과 변수에 따른 여러 가지 결과를 예측해 보는 기능 • 목표값찾기: 수식의 결과 값을 알고 있으나, 수식에서 그 결과를 계산하기 위해 필요한 입력값을 모르는 경우에 사용하는 기능 • 유효성 검사: 잘못된 데이터를 셀에 입력하는 것을 방지하는 기능 • 피벗테이블: 기존 테이블의 필드명 중에서 데이터 분석을 위해 필요한 필드만을 이용해 새로운 테이블을 구성하여 요약된 정보만을 제공하는 기능 • 메모: 임의의 셀에 간단한 설명을 붙일 때 사용하는 기능

9.1 데이터베이스의 레코드 관리하기 한 가지 기준으로 정렬하기 [대한 전자㈜ 2월 급여표]에서 ‘총급여액’에 대하여 내림차순 정렬하시오 [단계 1] 원본데이터 선택하기 ‘총급여액’ 항목에 해당하는 열[I5:I14]의셀 중 임의의 셀 선택

9.1 데이터베이스의 레코드 관리하기 한 가지 기준으로 정렬하기 [대한 전자㈜ 2월 급여표]에서 ‘총급여액’에 대하여 내림차순 정렬하시오 [단계 2] [홈]-[편집]-[정렬 및 필터]-[숫자 내림차순 정렬] 선택 원본 데이터가 ‘총급여액’에 대한 내림차순 정렬 실행

9.1 데이터베이스의 레코드 관리하기 한 가지 기준으로 정렬하기 [대한 전자㈜ 2월 급여표]에서 ‘총급여액’에 대하여 내림차순 정렬하시오 [단계 3] ‘이름’에 대한 오름 차순 정렬 – [데이터]-[정렬 및 필터] 메뉴 사용

9.1 데이터베이스의 레코드 관리하기 (2) 여러 가지 기준으로 정렬하기 [대한 전자㈜ 2월 급여표]에서 첫 번째 기준은 ‘근무부서’별 내림차순으로, 두 번째 기준은 ‘직책’별 오름차순으로 정렬하시오. [단계 1] 데이터내의 임의의 셀 선택 [홈]-[편집]-[정렬 및 필터]-[사용자 지정 정렬] 또는 [데이터]-[정렬 및 필터]-[정렬] 선택

9.1 데이터베이스의 레코드 관리하기 (2) 여러 가지 기준으로 정렬하기 [대한 전자㈜ 2월 급여표]에서 첫 번째 기준은 ‘근무부서’별 내림차순으로, 두 번째 기준은 ‘직책’별 오름차순으로 정렬하시오. [단계 2] 정렬 기준 선택하기 [정렬]대화상자에서 정렬기준 ‘근무부서’, ‘값’, ‘내림차순’ 서택 두 번째 기준 추가를 위해 [기준 추가] 선택 – ‘직책’, ‘값’, ‘오름차순’

9.1 데이터베이스의 레코드 관리하기 (2) 여러 가지 기준으로 정렬하기 [대한 전자㈜ 2월 급여표]에서 첫 번째 기준은 ‘근무부서’별 내림차순으로, 두 번째 기준은 ‘직책’별 오름차순으로 정렬하시오. [단계 3] 확인 버튼 누르면 원본 데이터가 기준에 따라 정렬

9.2 필터링 기능으로 데이터 검색하기-자동필터 [단계 1] 원본 데이터[B4:I14] 내의임의의 셀을 선택하고, [데이터]-[정렬 및 필터] -[필터] 클릭하면, 각 필드이름에 화살표로 된 목록상자 생성 [단계 2] ‘사원번호’필드의 목록 상자에서 사원번호 ‘TH008’ 선택

9.2 필터링 기능으로 데이터 검색하기-자동필터(계속) [단계 3] 사원번호 ‘TH008’에해당되는 레코드만 표시

9.2 필터링 기능으로 데이터 검색하기-자동필터(계속) [단계 4] 다시 모든 레코드를 표시하기 위해서는 ‘사원번호’ 필드의 목록 상자에서 모두 선택’ 클릭

9.2 필터링 기능으로 데이터 검색하기-자동필터(계속) [단계 5] 처음과 같이 전체 데이터가 보여짐

9.2 필터링 기능으로 데이터 검색하기-자동필터(계속) [단계 6] ‘총급여액’이 350보다 크고 380보다 작은 경우의 데이터 추출 방법 ‘총급여액’ 필드 목록 상자에서 [숫자필터]-[해당범위] 또는 [숫자 필터]-[사용자 지정 필터] 선택

9.2 필터링 기능으로 데이터 검색하기-자동필터(계속) [단계 7] [사용자 지정 자동필터]에 찾을 조건을 목록에서 선택 또는 입력 찾을 조건 : 350<= 총급여액<=380

9.2 필터링 기능으로 데이터 검색하기-자동필터(계속) [단계 8] 필터링 결과가 보여짐

9.2 필터링 기능으로 데이터 검색하기-자동필터(계속) [단계 9] 자동필터 해제하기 [데이터]-[정렬 및 필터]-[필터]를 다시 클릭하면 원본 데이터상에서 화살표 제거

9.2 필터링 기능으로 데이터 검색하기-고급필터 (1) AND 조건실행하기 [단계 1] [B17:C18]에필터링 조건 입력 직책이 ‘사원’이고 총급여액이 ‘330’이상인 사람들을 필터링

9.2 필터링 기능으로 데이터 검색하기-고급필터(계속) (1) AND 조건실행하기 [단계 2] [B4:I14]내의임의의 셀 선택하고 [데이터]-[필터]-[고급] 선택

9.2 필터링 기능으로 데이터 검색하기-고급필터(계속) (1) AND 조건실행하기 [단계 3] [고급 필터] 대화상자에 원본 데이터를 드래그하여 셀 입력

9.2 필터링 기능으로 데이터 검색하기-고급필터(계속) (1) AND 조건실행하기 [단계 4] 확인 버튼 누르면 필터링된 결과가 시트에 보여짐

9.2 필터링 기능으로 데이터 검색하기-고급필터(계속) (2) OR 조건실행하기 [단계 1] [B26:C28]에필터링 조건 입력 직책이 ‘사원’이거나 총급여액이 ‘330’이상인 사람들 필터링

9.2 필터링 기능으로 데이터 검색하기-고급필터(계속) (2) OR 조건실행하기 [단계 2] [B4:I14]내의임의의 셀 선택하고 [데이터]-[필터]-[고급] 실행 [고급필터] 대화상자에 해당하는 범위들 입력

9.2 필터링 기능으로 데이터 검색하기-고급필터(계속) (2) OR 조건실행하기 [단계 3] OR 조건으로필터링된 결과가 보여짐

9.3 부분합으로 직책별 기본급 산출하기 [단계 1] 부분합을 수행을 위해서는 합을 구할 동일 항목들이 연속되어야 함 ‘직책’을 기준으로 내림차순 정렬 수행

9.3 부분합으로 직책별 기본급 산출하기(계속) [단계 2] [데이터]-[윤곽선]-[부분합] 선택

9.3 부분합으로 직책별 기본급 산출하기(계속) [단계 3] [부분합] 대화상자의 옵션 지정 직책별로 기본급과 총급여액의 부분합 구함

9.3 부분합으로 직책별 기본급 산출하기(계속) [단계 4] 부분합 결과 생성

9.3 부분합으로 직책별 기본급 산출하기(계속) [단계 5] 부분합 제거하기 [부분합] 대화상자에서 ‘모두 제거’ 클릭

9.4 시나리오 관리자를 이용한 대출금 상환액 알아보기 [단계 1] 데이터 입력하기 [단계 2] 시나리오 관리자 선택하기 [데이터]-[데이터 도구]-[가상분석]-[시나리오 관리자] [단계 3] 시나리오 추가하기 시나리오 관리자 대화상자에서 추가 버튼을 누르고 시나리오 추가 [단계 4] 시나리오 값 입력하기 시나리오 편집 대화 상자에서 확인 버튼을 누르면 보이는 시나리오 값 대화상자에 금리와 할부 개월에 대한 변경 값 입력 [단계 5] 시나리오 요약 대화 상자 모든 시나리오 입력 후 시나리오 관리자 대화 상자에서 요약 버튼 클릭 시나리오 요약 대화 상자가 보이며, 결과 셀은 상환액을 선택하고 확인 클릭 [단계 6] 시나리오 요약 워크시트 만들기 시나리오 요약 대화 상자에서 확인 버튼 누르면 시나리오 요약 워크시트 생성 금리와 할부 개월이 변경되는 경우의 상환액 변화 확인 가능 다음항목으로

9.4 시나리오 관리자를 이용한 대출금 상환액 알아보기 상환액은 PMT함수 (=PMT(C5/6, C6, C4) 사용 PMT함수 주기적이고 고정적인 지급액과 고정적인 이율에 대한 대출 상환금을 계산하는 함수 앞으로

9.4 시나리오 관리자를 이용한 대출금 상환액 알아보기 앞으로

9.4 시나리오 관리자를 이용한 대출금 상환액 알아보기 시나리오1 : 시나리오 이름 ‘금리 인상’, 금리 7.00%, 할부 개월 4 시나리오2 : 시나리오 이름 ‘금리 인하’, 금리 5.00%, 할부 개월 4 변경할 셀 : 금리와 할부 개월이므로 [C5:C6] 드래그하여 선택 앞으로

9.4 시나리오 관리자를 이용한 대출금 상환액 알아보기 시나리오1 : 시나리오 이름 ‘금리 인상’, 금리 7.00%, 할부 개월 4 시나리오2 : 시나리오 이름 ‘금리 인하’, 금리 5.00%, 할부 개월 4 앞으로

9.4 시나리오 관리자를 이용한 대출금 상환액 알아보기 앞으로

9.5 목표값을 이용하여 상환액에 따른 대출액 계산하기 ‘상환액’을 ‘50,000원’으로 변경할 경우의 대출 금액을 계산하시오. [단계 1] 목표값 찾기 실행하기 목표값을 찾기 위해 상환액 셀[C7]을선택한 후, [데이터]-[데이터 도구]-[가상분석]-[목표값 찾기] 실행

9.5 목표값을 이용하여 상환액에 따른 대출액 계산하기 [단계 2] 목표값 설정을 위한 데이터 입력하기 수식셀: 상환액 [C7] 찾는값 : 매달 갚을 수 있는 금액. 예)500,000 값을 바꿀 셀 : 찾는 값에 따른 대출 금액 [C4]

9.5 목표값을 이용하여 상환액에 따른 대출액 계산하기 [단계 3] 목표값 확인하기 목표값 찾기 대화상자에서 확인 버튼을 누르면 상환액에 따른 대출 금액이 계산되어 워크시트에 표시

9.6 유효성 검사 • 유효성 검사는 잘못된 데이터를 셀에 입력하는 것을 방지하기 위한 기능 • 셀에 입력되는 값의 범위를 지정하거나 한영 전환이 자동으로 이루어지게 하기 위해서 사용

9.6 유효성 검사 [단계 1] ‘근무부서’ 입력 셀을 모두 선택한 후 [데이터]-[데이터 유효성 검사]-[데이터 유효성 검사]를 선택

9.6 유효성 검사 [단계 2] ‘데이터 유효성’ 대화상자의 ‘설정’탭의 ‘제한 대상’에서 원하는 데이터 유효성 검사 형식을 선택. ‘근무부서’에 대한 유효성 검사 형식은 ‘목록’으로 선택. ① 정수 일정한 범위 내의 정수로 데이터 입력 제한 (최대값과 최소값 설정) ② 소수점 일정한 범위 내의 실수로 데이터 입력 제한(예: .2) ③ 날짜 일정 기간 내의 날짜로 데이터 입력 제한 (시작날짜와 끝 날자 설정) ④ 시간 일정 기간 내의 시간으로 데이터 입력 제한 (시작시간과 종료시간 설정) ⑤ 텍스트 길이 : 입력하려고 하는 글자의 수 제한

9.6 유효성 검사 [단계 3] ‘원본’부분에 ‘총무과, 영업과, 자재과, 기획과’를 입력하고, ‘확인’ 버튼을 선택하여 대화상자를 닫음

9.6 유효성 검사 [단계 4] 데이터 테이블의 ‘근무부서’ 입력란에 드롭다운 버튼이 생김 데이터 입력 시 드롭다운 버튼을 선택하여 데이터를 선택하여 입력하거나 직접 입력도 가능하다.

9.6 유효성 검사 [단계 5] [데이터 유효성] 대화상자의 ‘설명 메시지’ 탭에 ‘설명 메시지’를 작성하면 해당 셀 선택 시 간단한 설명 메시지를 보여 줄 수 있다.

9.6 유효성 검사 [단계 6] 제한된 범위 밖의 데이터를 입력할 경우 ‘오류’ 대화상자가 뜸 [데이터 유효성] 대화상자의 ‘오류메시지’ 탭에서 오류 스타일, 제목 그리고 오류 메시지를 사용자의 내용에 맞게 작성 가능

9.6 유효성 검사 [단계 7] [데이터]-[데이터 유효성 검사]-[잘못된 데이터]를 선택하면 데이터 테이블내에서 잘못 입력된 데이터를 찾아 표시해 줌.

9.6 유효성 검사 [단계 8] [데이터]-[데이터 유효성 검사]-[유효성 표시 지우기]를 선택하면 잘못된 데이터에 대한 표시를 지울 수 있음.

9.7 피벗테이블 보고서 • 기존 테이블의 필드명중에서 데이터 분석을 위해 필요한 필드명만을 이용해 새로운 테이블을 구성하여 요약된 정보만을 제공해 주는 기능 • 피벗 테이블 보고서를 만드는 순서 • 피벗 테이블 보고서를 만들 데이터 읽음. • [삽입] 메뉴에서 [피벗테이블]을 선택. • [피벗 테이블 만들기] 대화상자에서 분석할 데이터의 범위와 피벗 테이블 보고서를 넣을 위치 선택. • [피벗 테이블 필드 목록]에서 보고서에 추가할 필드를 선택하거나 만들어질 피벗 테이블 보고서의 각 부분 위치로 각 필드들을 드래그하여 테이블 구성.

9.7 피벗테이블 보고서 [단계 1] 원본 데이터 위의 임의의 셀을 선택한 후 [삽입]-[피벗테이블]-[피벗테이블]을 선택.

제 9 장 손 쉬운 데이터 입력부터 간단한 관리까지

제 9 장 손 쉬운 데이터 입력부터 간단한 관리까지

Presentation Transcript