조건별 데이터 관리

(1)

0

6장

조건별 데이터 관리

1

학습목표

• 자동 필터, 고급 필터 기능을 사용해서 데이터를

• 레코드 기능을 사용해서 데이터를 관리

• 유효성 검사 기능을 사용해서 데이터 입력 조건을 지정

• 데이터베이스 함수를 사용한 조건별 계산 방법에 대해 알아본다

(2)

2

데이터 필터링 : 사용자가 설정한 조건에 맞는 데이터만 화면에 표시

z 데이터베이스란?

• 방대한 양의 데이터를 특정 용도에 맞게 체계적으로 정리

• 데이터베이스 구성

필드명: 각 필드를 구분할 수 있는 필드의 이름으로, ‘열 이름표’라고도 함.

레코드: 하나 이상의 필드로 구성되며, 데이터베이스의

‘행’을 의미.

필드: 같은 종류의 데이터 모 임으로, 데이터베이스의

‘열’을 의미.

3

데이터베이스 작성 시 지켜야 할 규칙

▲ 잘못된 데이터베이스 목록의 예

• 필드명은 병합하지 않고, 한 줄로 작성해야 한다.

• 필드 1개당 정보는 1개만 담겨야 한다.

• 데이터 목록 중간에 빈 행이나 빈 열이 없어야 한다.

필드명이 병합된 셀에 작성되면 데이 터베이스 목록으로 인식되지 않음

하나의 셀에 부서와 직급이 함께 입력 되어 있으면, 부서별, 직급별로 관리 할 수 없음

빈 행이 있으면, 데이터 관련 메뉴 선 택 시에 일부분만 데이터베이스 범위 로 지정됨.

(3)

4

데이터 필터링

z 자동필터 사용 방법

• [데이터]-[필터]-[자동필터] 메뉴를 선택해서 실행

• 목록 단추를 이용해서 쉽고 빠르게 필터 조건을 설정

• 자동 필터를 사용하려면 데이터 목록에 반드시 필드명(열 이름표)이 있어야

• 자동 필터를 사용해서 추출한 결과 데이터는 원본 목록(데이터 목록)에 표시된다.

• 두 개 이상의 필드(열)에 조건이 설정된 경우 AND 조건으로 결합되어 표시된다.

5

데이터 필터링

z 고급필터 사용 방법

• 고급 필터를 사용하기 위해서는 먼저 워크시트에 조건 을 입력

• 원본 데이터와 다른 위치에 추출된 결과를 표시할 수 있으며, 조건에 맞는 특정한 필드(열)만을 추출할 수 도 있다.

• 한 필드에 3개 이상의 조건을 지정할 수 있고, 두 개 이상의 필드를 AND나 OR로 결합해서 추출할 수 있다.

z 고급필터의 조건 지정 방법

• 조건을 입력할 때 같은 행에 입력하면 AND 조건으로 추출한다.

• 조건을 입력할 때 다른 행에 입력하면 OR 조건으로 추출한다.

• 같은 필드에 두 개의 조건을 AND로 설정하고 싶다면 같은 필드명을 두 번 쓰고, 각각의 조건을 입력하면 된다.

(4)

6

실습1. 실습 1. 자동필터 기능을 사용한 데이터추출

◀부서가 ‘교육팀’이고, 성별이

‘남자’인 레코드만 추출([데 이터]-[필터]-[자동 필터],

‘부서’ 필드의 목록 단추를 누르고 ‘교육팀’ 선택)

◀기본급이 많은 직원 5명 추출 (‘기본급’ 필드의 목록 단추를 누르고, (Top10…)을 선택)

◀’성별’ 필드의 목록 단추를 누르고 ‘남자’ 선택

◀선택적 자동 필터 대화상자 에서 ‘큰 순서’, ‘5’, ‘항목’을 선택하고, <확인> 버튼

실습1. 실습 1. 자동필터 기능을 사용한 데이터추출(계속)

◀나이가 40세 이상이고, 80~85년 사이에 입사한 직원만 추출

◀[데이터]-[필터]-[모두 표시] 메뉴를 선택해서 앞에서 지정한 조건을 모두 취소

◀’나이’ 필드의 목록 단추를 클릭해서 ‘(사용자 지정…)’을 선택

◀크거나 같다를 의미하는 비교연산자 ‘>=’를 선택하고, 오른쪽 첫 번째 항목에 ‘40’을 입력하고 <확 인> 버튼

◀’입사일’ 필드의 목록단추를 클릭해서 ‘(사용자 지정…)’ 를 선택

◀크거나 같다를 의미하는 비교연산자 ‘>=’를 선택하고 오른쪽 첫 번째 항목에 ’80-01-01’ 을 입력,

‘그리고’ 옵션을 선택하고, 왼쪽 두 번째 항목에 작거나 같다를 의미하는 비교연산자 ‘>=’를 선택하 고, 왼쪽 두 번째 항목에 ’85-12-31’을 입력, <확인> 버튼

(5)

8

실습 실습 2. 고급필터기능을 사용한 데이터 추출 2.

조건1

성별이 여자이거나, 부서가 홍보팀인 직원 명단을 [A35]셀에 추출

고급 필터는 오른쪽 그림과 같이 조건을 미리 별도 셀에 입력해 두어야 함

OR 조건으로 검색하려면 오른쪽과 같이 조건을 서로 다른 행에 입력해야 함

다음으로 데이터 목록 안에 셀 포인터를 두고, [데이터]-[필터]-[고급 필터] 선택

고급 필터 대화상자에서 결과는 ‘다른 장소에 복사’ 옵션을 선택, 목록 범위는 셀 포인터가 데이터 목록 안에 위치해 있으므로 이미 지정되어 있다.

‘조건 범위’ 항목을 클릭, [L3~M5]셀을 범위로 지정, 복사 위치 항목을 클릭, [A35] 셀을 범위로 지정한 후 <확인> 버튼 클릭

실습2. 실습 2. 고급필터기능을 사용한 데이터 추출(계속)

조건2 : 조건1과 같은 조건으로 성명, 부서, 직급, 성별, 나이 필드만 [A35]

셀에 추출

성명, 부서, 직급, 성별, 나이 필드의 제목을 선택, 마우스의 오른쪽 버튼을 누르고 [복사] 해서, [A35] 셀에 붙여넣기

[데이터]-[필터]-[고급 필터] 메뉴를 선택하여, 조건1 같이 설정하고, 마지 막 복사 위치 항목은 미리 복사해 놓은 [A35]~[E35]를 지정하고, <확인>

조건3 : 근무년수 5, 10, 15, 20년 된 직원들의 명단을 다른 시트에 추출

역시 OR 조건이므로 오른쪽 그림과 같이 각각 다른 행에 조건 입력

데이터 레코드를 추출할 시트 [sheet3]의 [A1]셀에 셀 포인터를 두고, [데이터]-[필터]-[고급 필터]

결과 옵션으로 ‘다른 장소에 복사’ 선택, ‘목록 범위’는 [인사 기록표] 시트의 테이블 전체 선택.

‘조건 범위’ 는 , 이전 시트의 근무년수에 대한 조건을 입력해 놓은 셀을 마우스로 드래그해서 범위 지정

‘복사 위치’ 항목은 또 다른 시트인 [sheet3]의 [A1] 셀을 클릭해서 <확인>

(6)

10

레코드 관리

z 레코드 관리 기능의 기본 사항

• 레코드 단위로 데이터를 표시하고, 추가, 삭제, 수정, 검색할 수 있다.

• 데이터는 항상 데이터 목록의 가장 마지막에 추가된다.

• 레코드 관리에서는 새로운 필드를 추가하거나 삭제할 수 없다.

• 필드 중 수식에 의해 입력된 필드는 수정이나 삭제할 수 없다.

• 레코드 관리 대화상자에 표시할 수 있는 필드는 32개로 제한되어 있다.

• 각 필드 간의 이동은 Tab키나 마우스를 이용한다.

• 각 레코드 간 이동은 방향키나 수직이동줄, [이전찾기], <다음찾기> 버튼을 이용한다.

• 데이터 목록이 아닌 곳에 셀포인터가 있거나, 데이터목록 주변에 병합된 셀이 있으면 다음과 같은 오류메시지가 나오면서 기능이 수행되지 않는다.

11

z 레코드 관리 대화상자

워크시트 이름

수식으로 되어 있는 필드

현재 레코드 번호/전체 레코드 수

새로운 레코드 추가

현재 레코드 삭제

입력 전 데이터로 데이터 복원

이전 레코드로 이동

다음 레코드로 이동

특정 검색 조건을 입력

레코드 관리

(7)

12

실습 실습 3. 레코드 관리 기능으로 추가/삭제/검색 3.

성명 수험번호 워드 엑셀 파워포인트

이준우 A14 80 75 100

김경태 B25 86 81 90

우제원 B32 77 83 88

김진영 C25 90 78 82

구나현 C16 87 82 79

1. 다음 레코드를 추가한다.

데이터 목록에 셀 포인터를 두고 [데이터]-[레코드 관리] 메뉴 선택

<새로 만들기> 버튼 클릭, 추가할 데이터 입력, 엔터를 치면 마지막 행에 레코드가 추가된 것을 확인

작업을 반복하여 제시한 레코드를 모두 추가

실습3. 실습 3. 레코드 관리 기능으로 추가/삭제/검색(계속)

2. 수험번호 A21인 레코드를 찾아 워드 점수를 75점으로 수정한다.

3. 수험번호 C33인 레코드를 찾아 삭제한다.

<조건> 버튼을 누르면 수식이 입력된 필드에도 조건을 입력할 수 있도록 항목값이 활성화됨

데이터]-[레코드 관리]에서 ‘A12’ 를 입력하고, <이전 찾기> 버튼을 누른다. 수험번호가 A12 인 레코드 가 검색되면 워드 점수를 ‘75’로 수정한 후 엔터를 누른다.

다시 <조건> 버튼을 누르고, 수험번호란에 ‘C33’을 입력하고, <이전 찾기> 버튼 클릭.

수험번호가 C33인 레코드가 검색되면 <삭제>

레코드는 한번 삭제하면 [실행 취소] 메뉴로 되돌릴 수 없다.

(8)

14

유효성 검사

[데이터]-[유효성 검사] 메뉴

• 셀에 조건을 설정하여 특정한 데이터만 입력 되게 함.

• 특정할 셀을 선택하면 한영 전환이 자동으로 변환되게 함.

• 주로 자신이 만든 양식을 엑셀을 잘 모르는 다 른 사람이 사용하거나 해당 양식에 대해 잘 이 해하지 못하는 사람이 사용하는 경우에 설정

• 설정: 셀에 어떤 제한 기준을 설정할 것인지 선택

• 설명메시지: 특정한 셀을 선택하면 자동으로 표시될 메시지 설정

• 오류 메시지: 제한 기준을 설정한 후 이에 맞지 않는 값을 입력했을 때 표시할 메시지를 설정

• IME 모드: 특정한 셀을 선택하면 자동으로 한글/영문 전환이 이루어질 수 있도록 설정

15

실습4. 실습 4. 데이터 목록 지정하고, 입력 제한하기

유효성 검사 기능을 사용하여 판매원 필드에는 입력할 수 있는 목록을 제한 하여 선택할 수 있게 하고, 판매일자 필드는 5, 6월 날짜만 입력하도록 제 한한다.

• 판매원 이름이 입력되는 [A2:A14] 셀 범위를 지정한 후, [데이터]-[유효성 검사] 메뉴 선택

• [설정] 탭 선택, 제한 대상 목록에서 ‘목록’을 선택, 원본란에 ‘김성준, 박진만, 이제원, 홍길 동, 한상호’ 이름을 입력

• [오류 메시지] 탭 선택, 스타일은 정보를 선택, 제목과 오류메시지를 입력한 후, <확인>버튼

• 위와 같은 방법으로 날짜가 입력되어 있는 [D2]셀에서 [D14]셀까지에 대해 2006년5월1일 에서 2006년6월30일까지의 날짜만 입력되도록 목록제한 설정 및 오류메시지(“이 표는 5월 과 6월 판매분만 입력합니다.”) 설정

(9)

16

데이터베이스 함수

z 데이터베이스 함수의 일반 형식

=함수명(Database, Field, Criteria)

데이터베이스 범위 열번호 조건

함수 내 용 설 명

DAVERAGE 선택한 데이터베이스 엔트리의 평균을 구한다.

DCOUNT 지정된 데이터베이스와 찾을 조건에서 수를 포함한 셀을 센다.

DCOUNTA 지정된 데이터베이스와 찾을 조건에서 비어 있지 않은 셀을 센다.

DGET 찾을 조건에 만족하는 레코드를 데이터베이스에서 추출한다.

DMAX 선택한 데이터베이스 엔트리에서 최대값을 구한다.

DMIN 선택한 데이터베이스 엔트리에서 최소값을 구한다.

DPRODUCT 찾을 조건에 맞는 필드나 레코드의 값들을 곱한다.

DSTDEV 선택한 데이터베이스 엔트리들의 표본에 대한 표준편차를 구한다.

DSTDEVP 선택한 데이터베이스 엔트리들의 전체 모집단에 대한 표준편차를 구한다.

DSUM 찾을 조건에 맞는 레코드의 필드 열에 있는 값들을 더한다.

DVAR 선택한 데이터베이스 엔트리들의 표본에 대한 분산을 구한다.

DVARP 선택한 데이터베이스 엔트리들의 전체 모집단에 대한 분산을 구한다.

z 데이터베이스 함수의 종류

17

실습5. 실습 5. 지역별 계산 결과 구하기

지역별 합계, 평균, 최대값, 수입건수 등을 계산 : 데이터베이스 함수 사용

데이터베이스 함수의 조건이 됨

• 1월 평균 구하기(DAVERAGE 함수): [B18] 셀 선택, 함수 마법사 DAVERAGE 선택

• 첫번째 인수 [A3:C15], 두번째 인수 ‘2’, 세번째 인수 [A17:A18]

• 2월 최대값 구하기(DMAX 함수): [C18] 셀 선택

• 1월 최소값 구하기(DMIN 함수): [D18] 셀 선택

(10)

실습 실습 5. 지역별 계산 결과 구하기(계속) 5.

• 2월 수입 건수 구하기(DCOUNT 함수) :[E18] 셀 선택

• 1월 합계 구하기(DSUM 함수) : [F18] 셀 선택

• 이제 [A18] 셀에 지역명을 입력하면 그에 따른 값들이 자동으로 바뀐다.