신입사원교육자료 엑셀 2007 안화미 슬라이드요약 엑셀, 준비운동 엑셀데이터종류 서식설정 함수 차트 데이터베이스 - 2 -
엑셀, 준비운동 화면구성 Office 단추 이전버전의 [ 파일 ] 메뉴기능 빠른실행도구모음 자주사용하는명령들을도구로표시, 리본메뉴위또는아래에표시할수있다. 제목표시줄 지금열려있는파일의이름과엑셀프로그램의이름이표시된다. 처음엑셀 2007 을실행하면 Book1 이표시된다. 리본메뉴탭 2007 에서사용하는명령들이기능별로분류된 탭 을선택하면현재작업에해당하는리본 (Ribbon) 메뉴가나타난다. 리본메뉴에는유사한기능의도구들이그룹 (Group) 으로표시된다. - 4 -
셀이동하기 셀포인터 ( ) 를해당셀로위치후마우스로직접클릭, 혹은방향키이용 Ctrl +,,, Ctrl + Home - 5 - 데이터범위설정하기 하나의셀선택 : 원하는셀로마우스포인터를클릭 여러개의셀선택 : 첫번째셀을선택한후마우스를이용하여범위를설정하고싶은위치까지드래그 전체행과전체열을선택 : 행번호나열번호를클릭 범위를연속적으로선택 : 첫번째셀클릭, 마지막셀 shift + 클릭 범위를비연속적으로선택 : 첫번째셀클릭, ctrl 키를누르며나머지셀클릭 - 6 -
데이터수정및삭제 수정 셀포인트위치시키고다시입력 자료가있는셀을더블클릭혹은 F2 수식입력줄부분을클릭하여수정 삭제 셀내용지우기 : 셀선택후 delete키 서식까지지우기 : 오른클릭 > 삭제 - 7 - 데이터입력과취소 Enter : 데이터입력을마치고셀포인터가한행아래로이동 화살표키 : 데이터입력을마치고셀포인터를방향키 의방향대로한칸씩이동 Esc : 입력중인데이터를취소 Enter shift+enter Tab shift+tab - 8 -
엑셀데이터종류 문자데이터 영문자, 한글, 특수문자등의조합으로만들어진데이터, 왼쪽기준입력 입력하는데이터의길이가셀의너비보다길경우바 로옆셀이비어있을경우이어서표시, 비어있지않을경우잘려표시 우편번호나주민번호같이문자로인식되어야하는숫자데이터는문자임을알리는기호 에이어입력 - 10 -
수치데이터 셀너비는 8 자리이고, 입력숫자길이가 11 자리가넘으면셀너비가자동으로늘어나고초과시지수로표현 셀너비보다초과되는값입력 ##### 이표시되므로열너비조정 숫자는 15 자리까지기억하고그이후는 0 으로표시 ½ 입력하려면 0 ½ 로입력 - 11 - 날짜 / 시간데이터 날짜데이터 연월일구분기호로 - 나 / 를사용 입력된날짜표시형식을변경하려면 [ 서식 ]-[ 셀 ] 메뉴를실행한후 [ 표시형식 ] 탭에서 [ 날짜 ] 항목을클릭하여원하는형식지정 ctrl+; 시간데이터 시간은 : 기호를사용 am 혹은 pm 12 시, 24 시모두가능 ctrl+shift+; - 12 -
논리데이터와메모데이터 논리데이터 True(1) 나 false(0) 의상태를나타냄 메모데이터 shift+f2-13 - 수식데이터 수식데이터임을알리는기호 = 에이어입력 함수도수식데이터 연산자와함께쓸수있음 - 14 -
다양한데이터입력방법 특수문자입력 한글자음 + 한자키 [ 삽입 ]-[ 기호 ] 한자입력 한글입력후한자키 목록중에서선택하여입력 alt+ 여러셀에같은데이터를입력 입력할범위를지정하고데이터를입력한후 ctrl+enter 한셀에여러줄데이터입력 다음줄로넘어가는부분에서 alt+enter - 15 - 데이터자동채우기 문자및숫자데이터에특정연속성이있을경우범위면지정하면데이터를자동으로입력 채우기핸들을이용 숫자데이터, 문자데이터그대로 copy 숫자와문자혼합된데이터는숫자증가, 문자 copy 예외항목은사용자지정목록에지정 - 16 -
편리한데이터입력방법 데이터자동채우기 숫자를드래그하면같은숫자로채우기된다 두개의숫자를범위로하여드래그하면두값의차이만큼씩증감되어채우기된다 날짜데이터를입력하고드래그하면일이자동증가되면서채우기된다 달만증가시키려면마우스오른쪽단추로드래그한후 [ 월단위채우기 ] 를선택한다 사용자정의로연속데이터만들기 [ 도구 ]-[ 옵션 ], [ 사용자지정목록 ] 탭에서새목록을추가 마우스오른쪽단추로드래그 - 17 - 유효성검사 셀에입력하는데이터값의범위를미리지정해두고데이터를입력할때범위내의값이올바르게입력되는지를확인해준다. 한글과영문을번갈아입력하는범위가있을때일정한범위에한글만입력되도록하거나영문만입력가능. 이때키보드의한 / 영키는그대로사용할수있다. 입력되는데이터의길이지정가능 조건을만족하는값만입력되도록지정가능 목록을지정해서목록에서값을선택가능 - 18 -
유효성검사설정 지정한조건에맞지않는데이터가입력되면경고메시지를표시할수있다. 데이터가중복입력되는것을방지한다. - 19 - 서식설정
서식설정 [ 홈 ] 탭 [ 글꼴 ], [ 맞춤 ], [ 표시형식 ],[ 스타일 ] 그룹 병합하고가운데맞춤, 균등분할맞춤, 통화, 주민등록번호, 숫자한글 / 한자 단순히숫자만표현할수도있지만, 백분율표시를하거나통화표시를하거나세자리마다콤마를붙이는등알아보기쉬운각종기본표시형식을지정 - 21 - 사용자지정표시형식지정하기 G/ 표준 : 숫자의일반표시형식 # : 유효한자릿수를표시, 유효하지않은 0 은표시하지않음 0 : 0 값을자릿수만큼표시, : 천단위구분기호를표시 색지정 : 색이름을대괄호 ([]) 로묶어코드첫부분에표시 yy, mm, dd : 날짜데이터의연, 월, 일부분을지정한자릿수대로표시 @ : 문자데이터표시위치를지정 * : * 뒤입력한문자반복표시 - 22 -
사용자지정서식코드구성예 [ 파랑 ]" "0.00;[ 빨강 ]" 0.00";"-";[ 자홍 ]@ 세개의구역으로구성되며세미콜론 (;) 으로구분하여정의 각구역은순서대로양수 ; 음수 ;0 값 ; 문자열을가리킨다 구역을생략하려면세미콜론만입력한다 색상은대괄호 ([]) 안에색상명을써서표시한다 - 23 - 셀에데이터표시하지않기 사용자지정표시형식에서 ;;; 을입력한다. - 24 -
한글 ( 영문 ) 로요일표시하기 사용자지정표시형식에서 aaa ( 한글요일 ] 혹은 ddd ( 영문요일 ) 를입력한다. - 25 - 조건부서식지정하기 특정한조건에맞는셀만자동으로특정셀서식이적용되게하는것으로특정데이터를강조하고자할때많이사용 - 26 -
2007 에추가된조건부서식 엑셀 2007은시각화구성요소제공 데이터막대, 색조, 아이콘집합 [ 홈 ] 탭 > [ 스타일 ] 그룹 > [ 조건부서식 ] -27- 조건부서식에수식입력하기 조건에서 수식이 로선택하고 = 과비교셀로수식을입력한다. - 28 -
서식지정 표서식 기본제공되는표서식을사용하여손쉽게서식지정 [ 홈 ] 탭 [ 스타일 ] 그룹 [ 표서식 ] 표서식변경 [ 표도구 ] 탭 [ 표스타일 ], [ 표스타일옵션 ] 일반셀범위로변환 [ 표도구 ] 탭 [ 도구 ] [ 범위로변환 ] -29- 함수
상대참조와절대참조 상대참조 : 수식을복사할때기본적으로지정되는참조형식으로, 수식이있는셀의상대적위치를기중으로주소가지정, 상대참조의형식의수식을다른위치에복사하여붙여넣으면셀주소가상대위치에따라자동으로변경되어원래수식과는다른셀을참조하게된다 절대참조 : 특정셀을고정시켜참조하고자할경우사용하는방식, 열과행번호앞에 $ 를붙여표시, 절대참조형식의수식은다른위치에복사해도셀위치가변경되지않는다 참조형식변환키 : F4-31 - 수식과함수사용법 수식은반드시 = 로시작 직접수식입력 ( 예 ;=1024+45) 셀참조로계산 ( 예 ;=A2+B2-C2) 함수계산 = 함수이름 ( 인수, 인수, ) 함수사용방법 수식입력줄에서직접입력 함수마법사이용 - 32 -
이름정의하기 셀이나셀범위에셀주소대신사용할이름을미리정의해두면수식에서셀주소를입력할때이름을사용할수있다 이름정의규칙 문자나밑줄로시작, 나머지는문자, 숫자, 밑줄, 마침표등 K5,$k$5 와같은셀참조사용불가 공백불가 최대 255 글자 대소문자구분안함 한통합문서안에같은이름정의불가 기본적으로절대참조 - 33 - 수식 정의된이름활용 이름정의 [ 수식 ] [ 정의된이름 ] [ 이름정의 ], [ 선택영역에서만들기 ] [ 이름상자 ] 에이름입력후 [Enter] 이름을활용한수식작성 =SUM( 정의된이름 ) 이름편집 / 삭제 [ 수식 ] [ 정의된이름 ] [ 이름관리자 ] -34-
동적인범위에이름지정 데이터의양이유동적으로변화되는경우에이름을지정해서효과적으로사용할수있다. Offset 와 Counta 함수이용 Offset : 셀또는셀범위에서지정한행수와열수인범위에대한참조를구한다 =OFFSET(reference,rows,cols,height,width) - 35 - 통계함수 average() : 인수의평균을계산 max() : 인수들중최대값구함 min() : 인수들중최소값구함 count() : 인수들중숫자의개수를구함 counta() : 인수의개수를구함 countif( 범위, 조건 ) : 범위중조건에맞는데이터의개수를구함 large( 인수,x) : 인수중 x번째로큰수를구함 small( 인수,x) : 인수중 x번째로작은수를구함 rank( 값, 참조범위, 방법 ) : 순위를구함 - 36 -
CountIf() 형식 예제 COUNTIF(range,criteria) =COUNTIF(A1:A20, >500 ) A1:A20 범위에서 500 보다큰값이들어있는셀의개수를구합니다. 예시 ) 국어점수 70 점이상이몇명일까? - 37 - COUNTIF vs. COUNTIFS 조건에만족하는셀의개수 여러개의조건에만족하는셀의개수 -38-
Rank() 형식 예제 RANK(number,ref,order) =RANK(K5,K5:K20,1) K5:K20 범위에서 K5 셀값에대한오름차순순위를구합니다. order 인수를생략하거나 0 을지정하면내림차순순위를구합니다. 예시 ) 평균점수로순위를매기려면? - 39 - 수학함수 sum() : 인수의합을계산 sumif( 범위, 조건, 더할영역 ) : 범위중조건에맞는셀의합을구함 round( 인수, 자릿수 ) : 반올림 roundup( 인수, 자릿수 ) : 올림 rounddown( 인수, 자릿수 ) : 내림 - 40 -
sumif() 형식 예제 SUMIF(range,criteria,sum_range) =SUMIF(A1:A3, >=100,B1:B3) A1:A3 범위에서 100 이상인셀을찾아 B1:B3 범위에서같은위치에있는셀의합계를계산합니다. sum_range 인수를생략하면 range 인수에서합계를구합니다 예시 ) 제품별금액합계구하기 - 41 - 날짜 / 시간함수 date( 연, 월, 일 ): 숫자3개를날짜로만듦 now(): 현재시각을구하는함수 year(): 연도추출 month() : 월추출 day() : 날짜에서일추출 today() : 오늘날짜구함 weekday() : 날짜의요일계산 hour(), minute(),second(): 시간, 분, 초추출 - 42 -
날짜 / 시간함수 (2) DATEDIF : 날짜사이의연 / 월 / 일구하는함수 =DateDif( 시작일, 종료일, y(m/d/ym/md/yd) ) =DateDif( 시작일, 종료일, y )& 년 &DateDif( 시작일, 종료일, ym )& 개월 - 43 - 논리함수 if( 조건식, 값 1, 값 2): 조건식이참이면값 1 을, 거짓이면값 2 를수행 and(): 모든조건이참이면참, 하나라도거짓이면거 짓을돌려줌 or(): 조건중하나로도참이면참을, 모두거짓일때만거짓을돌려줌 - 44 -
If() 형식 IF(logical_test,value_if_true,value_if_false) 예제 =IF(A1>=80, 합격, 불합격 ) A1 셀의값이 80 점이상이면합격, 80 점미만이면불합격을표시합니다. 예시 ) 실제판매액이목표액이상이되면 목표달성, 그렇지못한경우 목표미달 - 45 - 문자열함수 left( 문자열, 수치 ) : 문자열왼쪽에서지정한수치만큼문자를추출 right( 문자열, 수치 ) : 문자열오른쪽에서지정한수치 만큼문자를추출 mid( 문자열, 수치 1, 수치 2) : 문자열의수치 1 에서부터수치 2 만큼의문자추출 - 46 -
찾기 / 참조함수 vlookup( 값, 참조범위, 열번호, 찾는방법 ) : 참조범위의첫번째열에서기준값을찾아같은행의지정한열에서값을구함 choose( 찾는번호, 인수값.): 정해진인수목록중지정한번호의인수값을구함 - 47 - vlookup() 형식 예제 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) =VLOOKUP(100,A1:C10,3) A1:C10 범위의첫번째열에서 100 보다작거나같은값중최대값을찾아 3 번째열의값을반환합니다. 예시 ) 실제판매액이목표액이상이되면 목표달성, 그렇지못한경우 목표미달 어수선의 [74 점 ] 이몇등급인지찾고싶어 점수에대한 [ 등급표에서 ] (74 점을 ) 찾아라 등급표 [2 번째열 ] 의값을결과값으로반환하라 [ 근사값 ] 을찾아라 - 48 -
데이터베이스함수 (1) 조건을입력해두고조건에맞는결과를구하는함수 데이터베이스함수구성 DATABASE, FIELD, CRITERIA - 49 - 데이터베이스함수 (2) DAVERAGE 조건에맞는데이터의평균 DSUM 조건에맞는데이터의합계 (SUMIF) DCOUNT/DCOUNTA 조건에맞는데이터의개수 (COUNTIF) - 50 -
데이터베이스함수 (3) DGET 조건에맞는데이터의값 (INDEX,MATCH) - 51 - 배열수식 (1) 조건에맞는데이터를함수를사용하여구할때데이터가입력된일정범위를지정하여 TRUE/FALSE 의값을이용하여결과값을구하는수식 CTRL+SHIFT+ENTER 키를사용하여수식을입력 수식의앞과뒤에 { } 가표시 셀에조건식을입력할필요가없어데이터베이스함수를대신하는경우가많다. - 52 -
배열수식으로빈도수구하기 빈도수를구하는 Frequency 함수는배열함수로 Ctrl+Shift+Enter 를사용한다. - 53 - 차트
차트작성 차트종류만선택하면자동으로차트작성 [ 삽입 ] [ 차트 ] [ 차트도구 ] 를사용하여차트편집 디자인 : 차트전체적인편집 레이아웃 : 차트구성요소별편집 서식 : 차트구성요소별서식지정 차트활용 1 이중축차트 특정데이터계열을보조축으로설정 데이터계열에서마우스오른쪽단추 [ 데이터계열서식 ] [ 보조축 ] 보조축을사용하는데이터계열의차트종류변경 데이터계열에서마우스오른쪽단추 [ 계열차트종류변경 ] -55- -56-
차트활용 2 데이터계열및표식에그림채우기 데이터계열에서마우스오른쪽단추 [ 데이터계열서식 ] [ 채우기 ] [ 그림또는질감채우기 ] [ 파일 ] 클릭하여그림파일선택, [ 쌓기 ] [ 삽입 ] [ 일러스트레이션 ] [ 클립아트 ] -57- 특수문자로차트그리기 단순한형태로데이터를표시하기위해그냥표안에특수문자로데이터의크기를표시하고자할때 F3=IF(E3<0,REPT(" ",ABS(E3)/10),"") G3=IF(E3>0,REPT(" ",E3/10),"") - 58 -
데이터베이스 데이터베이스관련용어 필드 (field): 각각구분된항목으로, 항목의제목을필드명이라고한다 레코드 (record): 관련있는필드들의모임, 하나의행단 위데이터를이루는개념 파일 (file): 여러개의레코드가모여저장단위인파일이된다 - 60 -
정렬 단일정렬 [ 데이터 ] 탭 > [ 정렬및필터 ] 그룹 > [ 오름차순, 내림차순정렬 ] 다중정렬 [ 데이터 ] 탭 > [ 정렬및필터 ] 그룹 > [ 정렬 ] 사용자지정정렬 서식기준정렬 자동필터 [ 데이터 ] 탭 > [ 정렬및필터 ] 그룹 > [ 필터 ] 필드명에표시되는 [ 필터단추 ] 클릭후조건지정 색기준필터 선택필터 조건값이나서식에서마우스오른쪽단추클릭 [ 필터 ] 필터조건선택 -61- -62-
고급필터 자동필터에서지정할수없는다양한조건을지정가능 [ 데이터 ] 탭 >[ 정렬및필터 ] 그룹 >[ 고급...] 고급필터대화상자 현재위치에필터 : 결과데이터를현재원본데이터가위치한곳 다른장소에복사 : 복사위치 로지정한위치에결과데이터표시 데이터범위 : 필터기능이적용될원본데이터가있는위치 조건범위 : 사용자가지정한조건이입력된위치 복사위치 : 결과가추출될위치를지정 고유레코드 : 조건을만족하는행중에서같은내용의행이있을경우한행만표시 AND 조건 : 같은행기술팀이면서대리 근무팀 기술팀 직위 대리 OR 조건 : 다른행기술팀이거나대리 근무팀 기술팀 직위 대리 - 63 - 부분합 그룹별로데이터의합계, 평균, 개수등을자동으로구할수있게한기능 [ 데이터 ] 탭 >[ 윤곽선 ] 그룹 >[ 부분합 ] 기준이되는필드가먼저정렬되어있어야함 부분합계산후 [ 윤곽단추 ] 를사용하여화면표시윤곽설정 정렬 -64-
중첩부분합 1 2 3 해제 - 65 - 데이터표 특정한값의변화에따른결과값의변화를표형태로보여주는기능 [ 데이터 ]>[ 표 ] - 66 -
시나리오 다양한상황과변수에따른여러가지결과값의변화를가상의상황을통해예측하여분석하는기능 [ 도구 ]>[ 시나리오 ] A,B 각제품의반품양이 10 개씩줄거나늘어나면매출액이얼마나증가하고감소할까? 변경셀 결과셀 - 67 - 시나리오작성 1 2 10 개씩감소한값입력 3 4 5 6 작성된시나리오의결과를원본데이터표에표시할때시나리오를선택한후클릭 7-68 -
데이터통합 동일시트또는여러다른시트에각각입력된데이터를일정한기준에의해합쳐서요약, 계산해주는기능 [ 데이터 ]>[ 통합 ] < 두대리점의품목별목표량, 판매량, 판매액의평균 > - 69 - 목표값찾기 수식의결과값을알고있지만, 수식에서그결과를계산하기위해필요한입력값을모를경우사용하는기능 목표값찾기는앞의시나리오의반대개념 시나리오 : 컴퓨터점수를 100 점맞았다면평균이얼마일까 목표값찾기 : 평균이 95 점되려면컴퓨터점수는얼마나되어야할까 [ 도구 ]>[ 목표값찾기 ] - 70 -
목표값찾기작성 대출금액과상환기간, 이자율을이용하여월상환액을계산한것이다. 월상환액을 20000 원으로조정하려면상환기간을얼마로하여야하는지계산하라 - 71 - 피벗테이블 데이터를요약, 분석하는크로스탭형식의보고서 피벗테이블작성 [ 삽입 ] [ 표 ] [ 피벗테이블 ] 피벗테이블구성요소 피벗테이블필드목록창에서해당영역으로필드드래그 보고서필터 - 보고서필터에서선택한항목에따라전체보고서를필터 열레이블 - 보고서위쪽에열로표시할필드설정 행레이블 - 보고서왼쪽에행으로표시할필드설정 값 - 행과열이교차하는곳에데이터값을요약해서표시할필드설정 -72-
피벗테이블의데이터목록수정 데이터목록에서데이터를수정하거나새로운레코드를추가하면 데이터새로고침 을해야지만피벗테이블에반영 데이터목록이피벗테이블에서자동으로반영되도록만들려면 database 셀이름정의를사용 [ 데이터 ]>[ 레코드관리 ] 에서새로운레코드입력후엔터 - 73 - 피벗테이블정렬 세로방향정렬 - 입고량이가장많은화물부터정렬 총합계필드의셀하나를선택한다음 내림차순 정렬도구 가로방향정렬 더블클릭 < 하역사이름으로내림차순정렬 > < 입고량이많은하역사 3 위까지 > - 74 -
그룹만들어피벗작성 1 2 오른클릭 3-75 - 문의사항 안화미 http://key4u.tistory.com hwami@nate.com l3eauty@hotmail.com - 76 -