엑셀 2007 활용 담당강사: 서은주 E-mail: sej0264@hanmail.net 게시판: blog.daum.net/lagers

목차 화면 구성 함수 빠른 데이터 입력 유효성 검사 효과적인 문서 편집 서식 지정 차트 데이터베이스 관리 수식 RANK, COUNT, COUBTBLANK, COUNTA, COUNTIF, SUMIF, IF, AND, OR, VLOOKUP 유효성 검사 효과적인 문서 편집 서식 지정 차트 기본 서식 정리 차트 작성 표 서식 차트 활용 사용자 지정 표시 형식 데이터베이스 관리 조건부 서식 정렬 수식 필터 셀 참조 유형 부분합 정의된 이름 활용 피벗 테이블

화면구성 Office 단추 빠른 실행 도구모음 제목 표시줄 리본 메뉴 탭 이전 버전의 [파일]메뉴 기능 자주 사용하는 명령들을 도구로 표시, 리본메뉴 위 또는 아래에 표시 할 수 있다. 제목 표시줄 지금 열려 있는 파일의 이름과 엑셀 프로그램의 이름이 표시된다. 처음 엑셀 2007을 실행하면 ‘Book1’이 표시된다. 리본 메뉴 탭 2007에서 사용하는 명령들이 기능별로 분류된 “탭”을 선택하면 현재 작업에 해당하는 리본(Ribbon)메뉴가 나타난다. 리본 메뉴에는 유사한 기능의 도구들이 그룹(Group)으로 표시된 다.

빠른 데이터 입력 1 한 셀에 여러 줄 입력 특정 범위에 데이터를 빠르게 입력 자주 쓰는 특수 문자를 간단히 입력 분수 입력 [Alt+Enter] 특정 범위에 데이터를 빠르게 입력 셀 범위 선택 후 [Enter]나 [Tab] 키를 누르면 데이터 입력 자주 쓰는 특수 문자를 간단히 입력 [Office 단추]  [Excel 옵션] [언어교정][자동 고침 옵션] 분수 입력 0 1/2, 2 3/4 001, 002, 003… 과 같은 숫자 입력 ‘(작은 따옴표) 숫자 입력 더블 클릭 자동 채우기

빠른 데이터 입력 2 날짜 입력 선택한 셀 범위에 한꺼번에 데이터 입력 반복되는 내용을 목록에서 선택하여 입력 오늘 날짜 : Ctrl+; 업데이트되는 날짜 : =TODAY( ) 선택한 셀 범위에 한꺼번에 데이터 입력 Ctrl+Enter 반복되는 내용을 목록에서 선택하여 입력 Alt+↓ 마우스 오른쪽 단추 – [드롭다운 목록에서 선택]

유효성 검사 목록 [데이터] – [데이터 도구] – [유효성 검사] – [설정] 탭 – [제한 대상] : [목록], [원본] 설정 [원본] 설정 3가지 방법 원본 데이터 직접 입력 (ex. 부장, 차장, 과장, 대리, 사원) 같은 워크시트의 데이터 참조 (셀 범위 선택) 다른 워크시트의 데이터 활용 : 이름 정의 후 ‘=정의된이름’

유효성 검사 유효성 검사 규칙 설정 IME 모드 설정 [설정], [설명 메시지], [오류 메시지] 탭 입력하는 글자 수 제한 지정한 숫자 범위로 입력 제한 IME 모드 설정 데이터 입력 시 한글, 영문 모드 자동 설정

효과적인 문서 편집 1 서식 복사 원하는 옵션만 선택하여 붙여넣기 [홈] – [클립보드] – [서식 복사] 더블 클릭 : 반복사용 원하는 옵션만 선택하여 붙여넣기 원본 복사 후 원하는 위치에서 마우스 오른쪽 단추 – [선택하여 붙여넣기] 원본 복사 후 원하는 위치에서 [붙여넣기] – [붙여넣기 옵션 단추]

효과적인 문서 편집 2 값 붙여넣기 열 너비 연산하여 붙여넣기 행/열 바꿈 연결하여 붙여넣기 임의의 셀에 값 입력  복사 데이터 선택  마우스 오른쪽 단추  [선택하여 붙여넣기] [연산] 범주에서 연산자 선택 행/열 바꿈 연결하여 붙여넣기 데이터 연결하기 원본 데이터 복사  연결할 셀에서 마우스 오른쪽 단추  [선택하여 붙여넣기]  [연결하여 붙여넣기] 연결 끊기 : [Office 단추] – [준비] – [파일연결편집] – [연결 끊기]

기본 서식 정리 1 [홈] 탭 – [글꼴], [맞춤], [표시형식] 그룹 병합하고 가운데 맞춤, 균등 분할 맞춤, 통화, 주민등록번호, 숫자 한글/한자

서식 지정 – 표 서식 기본 제공되는 표 서식을 사용하여 손쉽게 서식 지정 [홈] 탭 – [스타일] 그룹 – [표 서식] 표 서식 변경 [표 도구] – [표 스타일], [표 스타일 옵션] 일반 셀 범위로 변환 [표 도구] – [도구] – [범위로 변환]

서식 지정 – 사용자 지정 표시 형식 1 셀 서식 대화상자 열기 : [Ctrl+1] 사용자 지정 표시 형식 작성 방법 표시 형식 기호 “문자열” “ “ 안에 입력된 문자열을 화면에 그대로 표시 색상 : [색]

서식 지정 – 사용자 지정 표시 형식 2 숫자 표시 형식 표시 형식 기호 : #, 0, , # : 유효한 자리수만 표시 0 : 데이터가 입력되지 않으면 무조건 0 표시 Ex) #,##0 “주”, 000, #,##0, (천 단위 제외하고 표시)

서식 지정 – 사용자 지정 표시 형식 3 4형식 구조 양수 ; 음수 ; 0 ; 텍스트

서식 지정 – 사용자 지정 표시 형식 4 날짜 표시 형식 문자 표시 형식 표시 형식 기호 : Y, M, D (연, 월, 일) AAA, AAAA(한글요일), DDD, DDDD(영문요일) 문자 표시 형식 표시 형식 기호 : @ 예 : @ “팀장”

서식 지정 – 조건부서식 1 ‘조건부 서식’ 이란? 특정 조건에 맞는 셀만 서식 지정 엑셀 2007은 시각화 구성 요소 제공 데이터 막대, 색조, 아이콘 집합 [홈] – [스타일] – [조건부 서식]

서식 지정 – 조건부서식 2 조건부 서식 설정 기본 제공되는 조건 연산자 사용 새 규칙 규칙 지우기 규칙 관리

수식 – 셀 참조 유형 셀 참조 유형 상대 참조, 절대 참조, 혼합 참조 F4

수식 – 정의된 이름 활용 이름 정의 이름을 활용한 수식 작성 이름 편집/삭제 [수식] – [정의된 이름] – [이름 정의], [선택 영역에서 만들기] [이름 상자]에 이름 입력 후 [Enter] 이름을 활용한 수식 작성 =SUM(정의된 이름) 이름 편집/삭제 [수식] – [정의된 이름] – [이름 관리자]

함수 RANK 인수 순위 계산 Number Ref Order 순위를 구할 데이터 순위를 구할 전체 데이터 범위 순위를 구할 전체 데이터 범위 Order 순위를 결정할 방법 내림차순 : 높은 숫자가 1 오름차순 : 낮은 숫자가 1

함수 COUNT/COUNTBLANK/COUNTA 숫자가 입력된 셀 개수/빈 셀 개수/데이터가 입력된 모든 셀 개수

함수 COUNTIF/COUNTIFS 인수 조건에 만족하는 셀의 개수/여러 개의 조건에 만족하는 셀의 개수 Range 조건을 판단할 셀 범위 Criteria 조건 조건 값 조건이 입력된 셀

함수 SUMIF/SUMIFS 인수 조건에 만족하는 데이터의 합계/ 여러 개의 조건에 만족하는 데이터의 합계 조건에 만족하는 데이터의 합계/ 여러 개의 조건에 만족하는 데이터의 합계 인수 Range : 조건을 판단할 셀 범위 Criteria : 조건, 값, 셀 Sum_Range : 합계를 계산할 셀 범위

함수 IF 인수 조건의 판단 결과에 따라 다른 결과 표시 Logical_Test : 판단할 조건 Value_if_True : Logical_Test를 판단한 결과가 True일 때 표시할 값 Value_if_False : Logical_Test를 판단한 결과가 False일 때 표시할 값

함수 중첩 IF 판단할 조건이 여러 개이고 조건에 따른 결과 값이 다른 경우 함수 중첩 이름 상자의 ▼ 클릭 함수 선택

함수 IF/AND 함수 중첩 중 다른 함수 인수 창을 표시하려면 판단할 조건이 여러 개 지정한 조건이 모두 만족할 때만 Value_if_True 값 표시 함수 중첩 중 다른 함수 인수 창을 표시하려면 수식 입력 줄에서 해당 함수 이름 부분에 클릭

함수 VLOOKUP 인수 특정 값과 관련된 데이터를 자동으로 표시 Lookup_Value : Table_Array의 첫 열에서 찾을 값 Table_Array : 참조할 데이터 범위, 첫 열에 Lookup_Value가 있어야 함 Col_Index_Num : 결과로 표시할 데이터가 입력된 열 번호 Range_Lookup : 정확하게 일치하는 값 (False, 0) 비슷하게 일치하는 값 (True, 1)

차트 작성 차트 종류만 선택하면 자동으로 차트 작성 [차트 도구]를 사용하여 차트 편집 [삽입] – [차트] 디자인 : 차트 전체적인 편집 레이아웃 : 차트 구성 요소 별 편집 서식 : 차트 구성 요소별 서식 지정

차트 활용 1 이중 축 차트 특정 데이터 계열을 보조 축으로 설정 보조 축을 사용하는 데이터 계열의 차트 종류 변경 데이터 계열에서 마우스 오른쪽 단추 – [데이터 계열 서식] – [보조 축] 보조 축을 사용하는 데이터 계열의 차트 종류 변경 데이터 계열에서 마우스 오른쪽 단추 – [계열 차트 종류 변경]

차트 활용 2 차트 제목 셀과 연결 차트 원본 데이터 숨기기 차트 제목 선택 – 수식 입력줄 클릭 – ‘=연결할 셀 클릭’ – [Enter] 차트 원본 데이터 숨기기 [차트 도구] – [디자인] – [데이터] – [데이터 선택] – [숨겨진 셀/빈 셀] – [숨겨진 행 및 열에 데이터 표시]

차트 활용 3 차트 원본 데이터 숨기기 꺾은선 차트 작성 시 빈 값 처리 [차트 도구] – [디자인] – [데이터] – [데이터 선택] – [숨겨진 셀/빈 셀] – [숨겨진 행 및 열에 데이터 표시] 꺾은선 차트 작성 시 빈 값 처리 [차트 도구] – [디자인] – [데이터] – [데이터 선택] – [숨겨진 셀/빈 셀] – [선으로 데이터 요소 연결]

차트 활용 4 데이터 계열 및 표식에 그림 채우기 데이터 계열에서 마우스 오른쪽 단추 – [데이터 계열 서식] – [채우기] – [그림 또는 질감 채우기] – [파일] 클릭하여 그림 파일 선택, [쌓기] [삽입] – [일러스트레이션] – [클립 아트]

정렬 단일 정렬 다중 정렬 사용자 지정 정렬 서식 기준 정렬 [데이터] – [정렬 및 필터] – [오름차순, 내림차순 정렬] [데이터] – [정렬 및 필터] – [정렬] 사용자 지정 정렬 서식 기준 정렬

자동 필터 [데이터] – [정렬 및 필터] – [필터] 필드명에 표시되는 [필터 단추] 클릭 후 조건 지정 색 기준 필터 선택 필터 조건 값이나 서식에서 마우스 오른쪽 단추 클릭 – [필터] – 필터 조건 선택

부분합 특정 필드를 기준으로 데이터 합계, 평균, 최대, 최소값 등을 자동으로 계산 [데이터] – [윤곽선] – [부분합] 특정 필드를 기준으로 데이터 합계, 평균, 최대, 최소값 등을 자동으로 계산 [데이터] – [윤곽선] – [부분합] 기준이 되는 필드가 먼저 정렬되어 있어야 함 부분합 계산 후 [윤곽 단추]를 사용하여 화면 표시 윤곽 설정

피벗 테이블 1 데이터를 요약, 분석하는 크로스탭 형식의 보고서 피벗 테이블 작성 피벗 테이블 구성 요소 데이터를 요약, 분석하는 크로스탭 형식의 보고서 피벗 테이블 작성 [삽입] – [표] – [피벗 테이블] 피벗 테이블 구성 요소 피벗 테이블 필드 목록 창에서 해당 영역으로 필드 드래그 보고서 필터 보고서 필터에서 선택한 항목에 따라 전체 보고서를 필터 열 레이블 보고서 위쪽에 열로 표시할 필드 설정 행 레이블 보고서 왼쪽에 행으로 표시할 필드 설정 값 행과 열이 교차하는 곳에 데이터 값을 요약해서 표시할 필드 설정

피벗 테이블 2 다양한 레이아웃 값 필드 함수 변경 및 표시 형식 설정 하위 시트 작성 피벗 테이블 필터 보고서 필터 항목 기준 자동 시트 작성 계산 필드 추가 그룹 필드 설정 피벗 테이블 디자인 피벗 차트 작성