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

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

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

새로 추가된 기능 1 [파일]탭 백스테이지 보기 엑셀 2007 오피스 원형 단추를 [파일]탭이 복원되면서 백스테이지 보기 기능 추가 증가와 감소를 분서해 주는 조건부 서식 이 기능을 사용하면 2개 항목에 대한 비교 분석을 강조할 수 있으며 각각 음수와 양수를 나타내는 색을 지정하거나 음수와 양수의 방향을 자유롭게 변경할 수 있습니다

새로 추가된 기능 2 리본 메뉴 사용자 지정 추가 2007에서 리본 메뉴 변경 할 수 없었으나 2010버전에 사용자가 원 하는 대로 구성할 수 있으며 [가져오기/내보내기]기능 추가 증가와 감소를 분서해 주는 조건부 서식 이 기능을 사용하면 2개 항목에 대한 비교 분석을 강조할 수 있으며 각각 음수와 양수를 나타내는 색을 지정하거나 음수와 양수의 방향을 자유롭게 변경할 수 있습니다

새로 추가된 기능 3 증가와 감소를 분서해 주는 조건부 서식 증가(양수)와 감소(음수)를 구분하여 표현하는 기능 추가 증가와 감소를 분서해 주는 조건부 서식 이 기능을 사용하면 2개 항목에 대한 비교 분석을 강조할 수 있으며 각각 음수와 양수를 나타내는 색을 지정하거나 음수와 양수의 방향을 자유롭게 변경할 수 있습니다

새로 추가된 기능 4 수식 도구 화학식이나 복잡한 수학공식을 엑셀에서 직접 작성할 수 있는 기능 텍스트 상자에 직접 수식을 작성할 수 있고 [수식도구] –[디자인]탭에서 편집할 수 있다 증가와 감소를 분서해 주는 조건부 서식 이 기능을 사용하면 2개 항목에 대한 비교 분석을 강조할 수 있으며 각각 음수와 양수를 나타내는 색을 지정하거나 음수와 양수의 방향을 자유롭게 변경할 수 있습니다

새로 추가된 기능 5 스파크 라인(셀 안에 차트 그리기) 단일 셀에 작은 차트를 만들어 데이터의 패턴을 빠르게 확인 계절별 증가 또는 감소 등의 중요한 데이터 추세를 빠르고 쉽게 강 조하여 시간을 절약할 수 있다.

새로 추가된 기능 6 다양한 형식의 붙여넣기 옵션 전체, 값, 수식, 테두리, 행열 바꾸기 값 붙여넣기: 값만, 수식 서식 포함, 전체 서식 기타 붙여넣기: 서식, 연결하여 붙여넣기, 그림으로 붙여넣기

새로 추가된 기능 7 슬라이서 피벗 테이블에 추가된 기능으로 작성된 피벗테이블과 피벗 차트를 필터링 하여 동적인 데이터 분석에 주로 활용할 수 있다.

새로 추가된 함수 1 Rank.EQ : Rank함수와 동일한 결과를 반환 Rank.AVG : 동점자가 있는 경우 평균 순위를 반환 =Rank.AVG(값, 범위, 정렬옵션) 예로 1위가 2명인 경우 1위는 없고 1.5위가 순위로 반환됩니다

새로 추가된 함수 2 NETWORKSDAYS.INTL =NETWORKDAYS.INTL(시작일, 종료일, 11, 휴일) 예로 1위가 2명인 경우 1위는 없고 1.5위가 순위로 반환됩니다

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

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

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

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

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

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

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

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

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

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

수식과 셀 참조 =A1*(B1+C1)-150 수식의 형식 셀 참조 참조의 종류 수식의 시작은 등호(=) 입력 숫자와 셀 주소 혼합 사용 가능 수식 기호 : 산수에서 사용하는 사칙 연산과 지수, 백분율, 괄호 등 이 포함 셀 참조 다른 셀의 값을 수식에 이용할 때 셀 참조 사용 참조의 종류 상대 참조 절대 참조 혼합 참조 =A1*(B1+C1)-150

함수식의 기본 형식과 입력 방법 =sum(A1:C1, D1) 함수식의 형식 함수 입력 방법 자동합계 직접입력 함수 마법사 등호(=), 함수 이름, 인수를 입력하는 괄호, 인수로 이루어짐 함수 입력 방법 자동합계 [수식]-[함수 라이브러리] 그룹의 [자동 합계] 합계, 평균, 숫자 개수, 최대값, 최소값 직접입력 함수 마법사 [수식-[함수 라이브러리] 그룹의 [함수 삽입] 도구 수식 입력줄 왼쪽의 [함수 삽입] 도구 =sum(A1:C1, D1)

수식 – 정의된 이름 활용 이름 정의 이름을 활용한 수식 작성 이름 편집/삭제 [수식] – [정의된 이름] – [이름 정의], [선택 영역에서 만들기] [이름 상자]에 이름 입력 후 [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 ‘조건부 서식’ 이란? 특정 조건에 맞는 셀만 서식 지정 엑셀 2007은 시각화 구성 요소 제공 데이터 막대, 색조, 아이콘 집합 [홈] – [스타일] – [조건부 서식]

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

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

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

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

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

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