Microsoft Excel - 상남경영원 특별과정 강의안 - 2002. 7

강의 개요 강의 일자 강의 장소 강의 내용 강사 연락처 2002년 7월 10일 수요일 1교시-2교시 2002년 7월 24일 수요일 3교시 강의 장소 상남경영원 3층 전자도서실 강의 내용 Windows 사용법 Review Excel의 기본적인 내용 Excel 사용 팁 Excel의 응용 – 함수, 피벗 테이블, 차트작성 강사 연락처 조재성: 한국기술평가㈜ 컨설턴트 jjason@ktenc.co.kr 02-3775-3066

목차 Windows 사용 Review Excel의 개요 및 기본 기능 간단한 함수 활용 데이터의 정렬과 피벗 테이블 차트마법사를 이용한 그래프 작성

1. Windows 사용 Review Windows / MS Office의 시작 단축키 마우스의 조작 키보드의 유용한 키 시작버튼  새 오피스 파일 단축키 Alt+F4: 프로그램의 종료, Alt+Tab: 프로그램의 전환 Shift+방향키: 텍스트의 블록 설정 Ctrl+C: 복사, Ctrl+X: 잘라내기, Ctrl+V: 붙여넣기, Ctrl+A: 전체선택 Ctrl+Z: 실행취소, Ctrl+Y: 재실행 마우스의 조작 마우스 오른쪽 버튼을 ‘마법의 버튼’이라고 함  매우 유용한 버튼!! 클릭 / 더블클릭 / 트리플 클릭 / 드래그 (문서 상에서: 더블클릭  단어선택, 트리플클릭  문장선택) Ctrl+마우스 휠 조정: 화면에 나타나는 문서 크기의 조정이 가능 키보드의 유용한 키 윈도우시작 키, Ctrl+ESC, 메뉴키 (마우스의 오른쪽 버튼과 동일)

2. Excel의 개요 및 기본 기능 기본사항 실무에서의 엑셀 활용 엑셀은 Microsoft 사의 스프레드시트 프로그램이다. 스프레드시트의 기본적인 기능은 “계산”에 있으며, 엑셀은 데이터베이스 역할을 수행할 수 있다. 셀 단위의 계산, 참조기능, 상대주소의 적용, 데이터 정렬 등의 기능을 보유함으로써, 엑셀은 단순한 계산이 아닌 데이터베이스로서의 역할을 해낼 수 있다. 하지만 엑셀은 단순한 계산기나, 데이터베이스 기능의 프로그램을 넘어서서, 업무 능률을 크게 향상 시킬 수 있는 하나의 도구이다. 실무에서의 엑셀 활용 워드프로세서의 대용, 각종 서식의 작성, 보고서 작성 매출액분석, 재무제표의 작성 원가관리, 손익분기점 분석, 민감도 분석, 통계처리 인사관리, 급여관리, 고객관리, 연말정산, 재고관리

① 엑셀의 화면 구성 단추추가/삭제 기본메뉴 도구모음: 표준 도구모음: 서식 이름상자 수식 입력줄 전체선택 열번호 핸들 길잡이 선택셀 눈금선 행: 216=65536 열: 28=255(A~IV) 행번호 스크롤바 시트 탭 Sheet 스크롤

② 기본 기능 1 실습.xls / 기본기능 1 Sheet의 삽입, 이동, 복사 행, 열 창 메뉴 시트 탭에서 오른쪽 버튼을 클릭 삽입, 삭제, 이름 바꾸기, 이동/복사 시트의 이동 및 복사는 마우스 드래그로 편하게 할 수 있음 행, 열 행, 열 삽입, 삭제: 선택 후 오른쪽 클릭  삽입 (행의 위, 열의 왼쪽에 삽입) 행, 열 숨기기: 숨기려는 행, 열 선택 후 오른쪽 클릭  숨기기 / 숨기기취소 행 높이, 열 너비: 행과 열의 구분되는 부분에서 마우스 드래그 복수의 행 또는 열을 선택한 후에 높이 또는 너비를 동일하게 조정가능 창 메뉴 새 창  정렬: 새 창 명령으로 새로 생긴 창(들)은 동일한 작업이 진행됨. 다른 Sheet를 동시에 보면서 작업하는 경우에 유용한 기능임. 틀고정: 왼쪽 또는 위쪽을 고정시키고 화면을 스크롤 할 수 있음. 데이터가 많을 경우에 매우 유용한 기능임.

③ 기본 기능 2 실습.xls / 기본기능 2 셀 입력 엑셀은 셀 단위의 작업이 이루어지는 특징이 있음. 화면에 나타나는 것과 실제 셀의 내용은 다를 수 있음에 주의 수식을 입력하기 위해서는 등호(=)를 입력하여야 함. 셀 서식 (마우스 오른쪽 클릭 또는 Ctrl+1) 표시형식: 셀의 ‘성질’을 규정함. 맞춤: 셀의 내용에 대한 위치 지정, 셀병합 글꼴: 셀 내용의 font 지정 등 테두리: 선택된 영역을 중심으로 선 적용 무늬: 셀의 색을 설정 보호: 셀의 입력을 제한하는 기능

셀 서식표시형식 셀 서식을 적용하기 위해서는 셀을 선택하거나 영역지정을 하여야 함. 행, 열 전체 지정: 행, 열 번호를 선택 넓은 영역 지정: 마우스를 드래그 또는 Shift 를 누른 상태에서 마우스를 클릭 불규칙한 영역의 지정: Ctrl 을 누른 상태에서 마우스를 클릭 숫자와 유사하며 통화 기호를 선택할 수 있음. 소수점 아래 자릿수 지정 입력되는 숫자는 자동적으로 %로 표시됨. (소수점 아래 자릿수 설정 가능) 1000단위 쉼표 적용 음수(-) 표시형식 선택 입력되는 내용이 어떤 것이든지 상관없이 입력한 그대로를 나타냄. (숫자를 입력하여도 텍스트로 인식함.)

셀 서식보호 셀 잠금 지정한 셀에 입력을 제한하는 기능 [도구]  [보호]  [시트보호] 를 선택하여야 함. [도구]  [보호]  [시트보호] 를 선택하여야 함. 기본적으로 셀 서식의 보호 기능은 잠금으로 되어 있으므로 특정 셀 또는 영역만을 보호하기 위해서는 다음의 절차를 따라야 함. 시트 전체 선택 셀 서식  보호  잠금 해제 보호하려는 셀 또는 영역 선택 셀 서식  보호  잠금 선택 도구  보호  시트보호 선택

④ 기본 기능 3 실습.xls / 기본기능 3 셀의 주소 및 영역이름 설정 자동채우기 기능 각 셀은 주소가 부여되어 있음: 열번호+행번호 셀의 주소는 상대주소와 절대주소가 있음 상대주소는 엑셀이 자동으로 주소를 변경하여줌 절대주소는 어떠한 경우에도 셀의 주소가 변경되지 않음. 절대주소는 행번호 또는 열번호 앞에 ‘$’ 표시를 추가함 (예: $A$1, $A1, A1$) 셀 만이 아니라 ‘영역’도 이름을 지정할 수 있음 이름을 지정하고자 하는 영역을 선택한 후, 이름 상자에서 이름을 지정함. 지정되어 있는 영역의 이름들은 이름 상자의 드롭다운 박스에 리스트 됨. 지정된 이름을 삭제하고자 하는 경우 [삽입]  [이름]  [정의]  이름선택 후 삭제 자동채우기 기능 핸들: 셀을 선택하는 경우 우하단의 점을 핸들이라 함. 셀에 수식이 입력되어 있는 경우에는 ‘상대주소’가 적용되어 복사가 됨. 텍스트인 경우에는 그대로 복사됨. [도구]  [옵션]  [사용자정의목록]에서 추가로 등록할 수 있음.

영역이름 지정하기 ② 이름상자에서 이름을 지정 ① 이름을 지정하고자 하는 영역 선택 정의되어 있는 이름을 삭제하려는 경우

자동 채우기 수식이 입력된 셀을 선택한 후 핸들을 드래그 함. 주소가 상대주소이므로 자동으로 수식이 복사됨. 새 목록을 선택하고 추가 버튼을 누르는 경우 목록 항목에 직접 입력 가능 [도구]  [옵션]  [사용자 정의 목록] 이 곳에 등록된 목록의 경우에는 핸들을 이용한 자동채우기가 가능 함. 목록이 있는 영역을 선택한 후 가져오기 버튼을 누르면 추가 가능.

⑤ 기본 기능 4 문서 관리 일반적인 프로그램과 동일한 저장 방법. (저장 단축키는 Ctrl+S) 문서의 이름을 바꾸어서 저장하고 싶을때에는 [파일]  [다른이름으로 저장] 문서를 보호하기 위해서 암호를 지정할 수 있음. 저장 또는 다른이름으로 저장  도구  일반 옵션 암호을 입력하여 저장하는 경우 파일을 열 때, 암호를 입력해야 함.

3. 간단한 함수 활용 엑셀은 9개 범주의 함수를 제공 재무, 날짜/시간, 수학/삼각, 통계, 찾기/참조 영역, 데이터베이스, 텍스트, 논리값, 정보 ① 함수마법사 아이콘 ② 함수 범주 선택 ③ 함수 선택 ④ 함수인수 입력 ⑤ 함수가 입력된 셀 함수는 수식을 입력하는 것과 같이 등호를 입력하고 적용한다. 각각의 함수에는 규정되어 있는 인수들이 있음.

① SUM, SUMIF, AVERAGE, RANK 연속된 셀의 영역을 입력할 때에는 ‘셀주소:셀주소’ 형식으로 입력함. 마우스를 이용하여 영역을 드래그하여 인수를 입력할 수 있음. 예) SUM(10,A1,20) / SUM(A3:A15) SUMIF: 조건에 맞는 셀들의 합을 계산 SUMIF(조건검사 영역, 조건, 계산되는 영역) 조건은 숫자, 식, 텍스트가 될 수 있으며 따옴표로 묶어야 함. 조건검사 영역의 같은 행에 있는 계산되는 영역의 숫자가 계산됨. AVERAGE: 평균을 계산 AVERAGE(인수, 인수, …) RANK: 지정된 영역내의 순위를 알려줌. RANK(숫자, 영역, 옵션) 옵션은 생략 또는 0일 경우에는 내림차순(높은 숫자가 1위), 기타일 경우 오름차순

② COUNT, COUNTA, COUNTIF, COUNTBLANK 조건은 숫자, 식, 텍스트 등이 될 수 있음. 조건은 따옴표로 묶어주어야 함. COUNTBLANK: 목록내에 비어있는 셀의 개수를 구함. COUNTBLANK(인수목록 또는 영역) COUNTA와 반대임.

이 값을, 이 영역의 첫번째 열에서 찾아서, 같은 행의 이 열에 있는 값을 추출함. ③ IF, VLOOKUP IF: 조건을 검사하여 조건이 TRUE일 경우와 FALSE일 경우 정해진 값을 돌려줌. IF(조건, TRUE일 경우, FALSE일 경우) IF 함수는 7개 까지 중첩을 해서 사용할 수 있다. =IF(B14>=90, "A", IF(B14>=80, "B", IF(B14>=70, "C", IF(B14>=60, "D", "F")))) VLOOKUP: 찾고자 하는 값을 영역 내에서 검색하여 관련된 값을 추출함. VLOOKUP(찾고자 하는 값, 값을 찾으려는 영역, 열번호, 옵션) “찾고자 하는 값”을 “값을 찾으려는 영역”의 첫번째 열에서 찾아서 같은 행에 있는 “열번호” 번째 열의 값을 추출함. 옵션은 FALSE일 경우에 정확하게 일치하는 값을 비교함. =VLOOKUP(D3, $B$16:$C$18,2,FALSE) 이 값을, 이 영역의 첫번째 열에서 찾아서, 같은 행의 이 열에 있는 값을 추출함. 1 2

4. 데이터 정렬과 피벗 테이블 데이터 관리 피벗 테이블 데이터 정렬  지정하는 열을 기준으로 데이터를 오름차순 또는 내림차순으로 정렬 자동 필터  원하는 값을 가지고 있는 데이터만 표시하는 기능 피벗 테이블 데이터를 표로 만들어 주는 기능 엑셀의 강력한 데이터 관리 능력의 대표적인 기능 임.

① 데이터 정렬 데이터 정렬 (sorting) [데이터]  [정렬] 영역의 첫번째 행을 머리글 행으로 할것인가를 결정 기준을 3개까지 정할 수 있음. ① 정렬하고자 하는 영역 설정 ② 정렬 기준의 선택 ③ 오름, 내림차순 결정 ④ 머리글 행 결정

② 자동 필터 자동 필터 [데이터]  [필터]  [자동 필터] 자동필터를 적용하려고 하는 영역을 선택한 후 자동 필터 선택 항목을 선택하여 필터 적용된 열의 드롭다운 박스는 파란색으로 변경됨. 필터가 적용되어 드롭다운 박스가 생김 필터를 적용하고자 하는 항목을 선택 고객2팀 데이터만 표시됨

③ 피벗 테이블 피벗 테이블 작성 1 3 2 4 [데이터]  [피벗 테이블/피벗 차트 보고서] 원본데이터 영역의 첫 행은 열의 제목이어야 함. 레이아웃을 어떻게 설정하느냐가 중요함. 1 3 2 4 원본데이터 영역 입력

각 항목(원본 데이터 영역의 첫째 행)을 행, 열, 데이터 영역으로 드래그 하여 피벗테이블 레이아웃 각 항목(원본 데이터 영역의 첫째 행)을 행, 열, 데이터 영역으로 드래그 하여 가져감

5. 차트 마법사를 이용한 그래프 작성 1 2 3 4 차트종류와 하위종류를 선택 차트의 원본데이터 영역을 입력 데이터계열(X축)의 기준(행 또는 열)을 선택 ‘제목’~’데이터 테이블’ 까지 각 항목 지정 3 차트위치 지정 4

차트상에서 서식 변경 차트의 각 부분에서 마우스 오른쪽 클릭을 하는 경우 서식 또는 옵션을 변경할 수 있음. 차트제목 서식 차트영역 서식 레이블 서식 그림영역 서식 데이터계열 서식 축 제목 서식 눈금선 서식 축 서식 축 제목 서식 범례서식