강사 : 이연란
셀서식으로문서꾸미기조건부서식으로데이터강조하기차트와스파크라인으로데이터표현하기계산과실무함수다루기데이터베이스관리와데이터분석하기 자료 : blog.daum.net/lyr2609
1. 셀서식으로문서꾸미기 제목과텍스트꾸미기 맞춤과서식복사로보고서꾸미기 보고서테두리지정하기 숫자와날짜데이터에표시형식지정하기 셀스타일을사용한셀꾸미기 사용자지정표시형식이해하기 사용자지정표시형식으로견적서꾸미기
제목과텍스트꾸미기 (1. 셀서식 ) 텍스트선택후 [ 홈 ] 탭 - [ 글꼴 ] 그룹에서글꼴서식지정하기 글꼴및글꼴크기지정 글꼴크게 / 작게 글꼴스타일과글꼴색지정 - 4 -
맞춤과서식복사로보고서꾸미기 (1. 셀서식 ) 리본메뉴에서텍스트맞춤 : [ 홈 ] 탭 - [ 맞춤 ] 그룹 왼쪽 / 가운데 / 오른쪽 / 병합하고가운데맞춤 셀서식대화상자에서셀맞춤 [ 맞춤설정 ] 대화상에서가로 / 세로맞춤지정 가로 : 왼쪽, 가운데, 오른쪽, 채우기, 양쪽맞춤, 선택영역의가운데맞춤, 균등분할등 세로 : 위쪽, 가운데, 아래쪽, 양쪽맞춤, 균등분할등 - 5 -
맞춤과서식복사로보고서꾸미기 (1. 셀서식 ) 서식복사로꾸미기 셀에서식지정하기 : Team 별여러셀을병합하고가운데맞춤 병합된셀선택후 [ 홈 ] 탭 - [ 클립보드 ] 그룹 - 서식복사 ( 두번클릭하여다중복사로지정 ) 커서가일때다른팀에클릭하여계속서식복사 - 6 -
보고서에테두리지정하기 (1. 셀서식 ) [ 홈 ] 탭 - [ 글꼴 ] 그룹 - [ 테두리 ] 에서원하는테두리선택 [ 셀서식 ] 대화상자의 [ 테두리 ] 탭에서사용자지정테두리지정 선스타일, 색, 테두리위치선택 - 7 -
숫자와날짜데이터에표시형식지정하기 (1. 셀서식 ) 숫자표시형식 [ 홈 ] 탭 - [ 표시형식 ] 그룹 회계표시형식, 백분율스타일, 쉼표스타일, 자릿수늘림, 자릿수줄임 날짜표시형식지정 : [ 셀서식 ]-[ 표시형식 ] 탭 날짜 범주를선택하고, 형식 에서 14-Mar-12 를선택 - 8 -
셀스타일을사용한셀꾸미기 (2. 문서서식 ) 제공된셀스타일을사용하여쉽게셀꾸미기 [ 홈 ] 탭 - [ 스타일 ] 그룹 - [ 셀스타일 ] 에서원하는스타일선택 제목이나강조할데이터또는숫자서식 - 9 -
사용자지정표시형식이해하기 사용자지정표시형식 : [ 홈 ] 탭 - [ 표시형식 ] 그룹 - [ 표시형식 ] 의내림단추클릭 - [ 기타표시형식 ] 숫자와문자데이터에사용되는코드 기호 설명 # 숫자표시기호, 유효하지않은 0 은표시안함 0 숫자표시기호, 유효하지않은 0 은 0 으로표시함? 소수점위나아래에있는유효하지않은 0 대신공백을추가해서자릿수맞춤 @ 소수점 (.) 쉼표 (,) 텍스트표시기호, 입력한텍스트를의미 소수점표시 세자리마다자릿수구분, 숫자기호뒤에표시하면 3 의배수로자릿수숨김 겹따옴표 ( ) 안에문자를그대로표시 G/ 표준표시형식을지정하지않은입력상태그대로의숫자 \, $ 통화기호로그대로표시 - 10 -
사용자지정표시형식으로견적서꾸미기 (2. 문서서식 ) 숫자데이터의값을천단위로표시 : #,##0, 날짜표시형식에요일을추가하기 표시형식 : yyyy 년 mm 월 dd 일 뒤에 (aaa) 을추가 견적서의합계금액에문자추가하기 표시형식 : [DBNum4][$-412]G/ 표준 원정 으로표시 견적서번호에문자를포함해번호매기기 : A- 000-11 -
혼자해보세요 ( 실습 1, 실습 2) 실습 1: 상환금 보고서를셀서식과셀스타일로꾸미기 실습 2: 사용자지정표시형식으로데이터표현하기 - 12 -
2. 조건부서식으로데이터강조하기 특정조건에맞는데이터강조하기 상위 15% 매출과평균미만매출표시하기 색조와데이터막대로매출분석하기 빠른분석기능으로매출현황표시하기 수식을사용한조건부서식완성하기
특정조건에맞는데이터강조하기 ( 조건부서식 ) 특정문자를포함하는 상품코드 강조하기 [ 홈 ] 탭 - [ 스타일 ] 그룹 - [ 조건부서식 ]- [ 셀강조규칙 ]- [ 텍스트포함 ] 판매수량 범위가 10 이하인셀강조하기 [ 홈 ] 탭 - [ 스타일 ] 그룹 - [ 조건부서식 ]- [ 새규칙 ] - 14 -
상위 20% 매출과평균초과매출표시하기 (3. 조건부서식 ) 금액 상위 20% 강조하기 [ 홈 ] 탭 - [ 스타일 ] 그룹 - [ 조건부서식 ]- [ 상위 / 하위규칙 ]- [ 상위 20%] 금액 평균미만표시하기, 소비자가 중복값표시하기 [ 홈 ] 탭 - [ 스타일 ] 그룹 - [ 조건부서식 ]- [ 상위 / 하위규칙 ]- [ 평균초과 ],[ 중복값 ] - 15 -
수식을사용한조건부서식완성하기 (3. 조건부서식 ) 수식을지정하여조건부서식을지정하는경우 조건과다른셀에서식을지정해야할때 행전체에서식을지정해야할때 대상에대한참 / 거짓이수식이나함수식으로지정해야해야할때 [ 홈 ] 탭 - [ 스타일 ] 그룹 - [ 조건부서식 ] 에서 [ 새규칙 ] 선택 수식 : =AND($E4>=2015-05-10, $F4="Cash") - 16 -
색조와데이터막대로매출분석하기 (4. 색조막대 ) 색조와데이터막대 : 매출액의크기를시각적으로표시 [ 홈 ] 탭- [ 스타일 ] 그룹- [ 조건부서식 ] 에서 색조 또는 데이터막대 색조 : 색을그라데이션으로표시 데이터막대 : 값의크기에따라막대의크기를표시 규칙편집 : 조건부서식을편집 / 삭제 / 추가 - 17 -
빠른분석기능으로매출현황표시하기 (4. 색조막대 ) 범위를선택한후 [ 빠른분석 ] 아이콘을클릭해조건부서식지정 [ 아이콘집합 ] 으로분석하고규칙편집 - 18 -
3. 차트와스파크라인으로데이터표현하기 차트의구성요소와빠른차트작성하기 차트서식으로꾸미기 이중축차트작성하기 스파크라인으로판매추이알아보기
차트의구성요소와빠른차트작성하기 차트의구성요소 기본차트빠르게그리기 F11: 다른워크시트에기본차트삽입 Alt+F1: 같은워크시트에기본차트삽입 - 20 -
차트서식으로꾸미기 차트요소를선택한후 [ 차트도구 ]- [ 서식 ] 탭- [ 현재선택영역서식 ] 축서식 [ 축서식 ] 창에서 [ 축옵션 ] 클릭 최대값, 주단위, 표시단의변경 주눈금서식 [ 주눈금서식 ] 창에서 [ 채우기및선 ] 클릭 색, 두께, 대시종류등변경 그림영역서식 [ 그림영역서식 ] 창에서 [ 채우기및선 ] 선택 패턴, 전경색, 배경, 테두리등지정 데이터계열서식 [ 데이터계열서식 ] 창에서 [ 계열옵션 ] 선택 간격너비, 계열겹치기등변경 [ 페이지레이아웃 ] 탭 - [ 테마 ] 그룹 - [ 색 ] 클릭하여테마색변경 - 21 -
이중축차트작성하기 (5. 차트 ) 보조축지정 임의의계열선택후마우스오른쪽클릭- [ 계열차트종류변경 ] 을선택 [ 차트종류변경 ] 차트종류 : 표식이있는꺾은선형선택 보조축 : 체크표시 차트서식으로꺾은선형차트꾸미기 축서식으로표시단위변경 데이터계열서식으로표식꾸미기 - 22 -
스파크라인으로판매추이 (6. 스파트라인 ) 스파크라인 : 한셀에삽입하는작은차트 [ 삽입 ] 탭 - [ 스파크라인 ] 그룹에서 3 가지스파크라인중선택 작성된스파크라인편집 스타일, 높은점 / 낮은점등표시체크와색지정 - 23 -
혼자해보세요 ( 차트실습 1, 실습 2) 실습 1: 백화점 B 판매 를콤보차트로분석하고꾸미기 실습 2: 외국인등록인구 를차트와스파크라인으로분석하기 - 24 -
4. 계산과실무함수다루기 요약, 통계함수알아보기 매출순위및그룹별요약하기 그룹별매출통계계산하기 논리정보및텍스트함수알아보기 DM 발송명단의상호와주소완성하기 DM 발송명단의성별구하기
수식작성과셀참조유형알아보기 수식작성의기본 : 등호나부호로시작하고셀을참조하여계산 연산자의종류 산술연산자 사칙연산자를비롯하여기본적인엑셀의수학연산자 +, -, *, /, %, ^ 비교연산자 값을서로비교할때사용하는연산자로, 비교의결과가참 (True) 과거짓 (False) 으로표시 =, >, <, >=, <=, <>, >< 연결연산자 문자와문자, 문자와숫자, 문자와수식결과등을연결하는연산자 & 참조연산자 주로계산에사용되는셀이나범위를지정할때사용 콤마 (,), 콜론 (:), 괄호 (()) 셀참조의유형 상대참조 참조의방법이선택된셀을기준으로상대적인위치를반영 A1, B1 등 절대참조 행과열에 $ 기호를붙여서표시, 수식에서참조할위치가절대변하지않음 $A$1, $B$1 등 혼합참조 상대참조와절대참조를혼합한형태로계산수식을어느방향으로채우느냐에따라다르게적용 $A1, A$1 등 다양한유형의참조위치 현재워크시트 다른워크시트 다른통합문서 = 셀주소 ( 예 :=A1) = 워크시트명! 셀주소 ( 예 : 매출!A1) =[ 전체경로 \ 통합문서명 ] 워크시트명! 셀주소 - 26 -
기본연산자로인센티브계산하기 (7. 수식계산 ) 인센티브계산 [F6] 셀 : =(D6+E6)*0.15 세금계산 [G6] 셀 : =F6*$B$3 B3 셀선택후 F4 키로절대참조지정 인센티브와세금에대한수식채우기 - 27 -
이름정의하여수식계산하기 (7. 수식계산 ) 이름작성하기 [B3] 셀선택후 [ 이름상자 ] 에 세율 을입력한후 Enter 수식적용 [G6] 셀 : =F6* 세율 입력, 수식채우기로나머지셀계산 수식편집하기 [ 수식 ] 탭- [ 정의된이름 ] 그룹- [ 이름관리자 ] 세율 을 부가세 로변경 - 28 -
자동합계기능으로합계와평균 (8. 기본함수 ) 금액계산하기 [G34] 셀 [ 홈 ] 탭 - [ 편집 ] 그룹 - [ 자동합계 ] 클릭 인접한숫자셀범위자동계산 평균계산하기 [G35] 셀 [ 홈 ] 탭 - [ 편집 ] 그룹 - [ 자동합계 ] 의내림단추클릭 - [ 평균 ] 선택 자동인식된범위가아닌경우범위재지정 - 29 -
함수라이브러리에서함수 (8. 기본함수 ) 합계 : [ 수식 ] 탭 - [ 함수라이브러리 ] 그룹 - [ 수학 / 삼각 ] 에서 [SUM] 선택 - 30 -
함수라이브러리에서함수 (8. 기본함수 ) 함수직접입력하기 최고값 [J5] 셀 : =MA 를입력하여 MA 로시작하는함수목록에서선택 함수의인수를추가로입력한후 Enter - 31 -
혼자해보세요 ( 함수실습 1, 실습 2) 실습 1: 연산자와이름을사용한지점별연평균성장률 (CAGR) 계산하기 실습 2: 자동합계기능과함수라이브러리로인원수, 최고점수, 표준편차계산하기 - 32 -
요약, 통계함수알아보기 함수이름형식설명 ROUND계열 SUMIF/AVERAGE IF SUMPRODUCT COUNT, COUNTA, COUNTBLANK ROUND/ROUNDUP/ROUNDD OWN/TRUNC (Number, Num_digits) SUMIF/AVERAGEIF (Range, Criteria, Sum_range) SUMPRODUCT(Array1, Array2,...) COUNT/COUNTA(Value1, Value2,... ),COUNTBLANK(Rang e) Number: 반올림 ( 올림, 내림, 잘라내기 ) 하려는수 Num_digits: 소수이하자릿수를나타내는인수 Range : 조건에맞는지아닌지를검사할셀범위 Criteria : 숫자, 수식, 셀참조주소또는텍스트형태의조건 Sum_range/Average_range : 실제로합계 ( 평균 ) 를구할셀범위 Array1, Array2,..: 같은차원의배열로 2개부터 255개까지지정가능 Value1, Vlaue2,...: 계산할인수들의목록으로최대 255개까지지정가능 Range : 빈셀의개수를셀범위 COUNTIF COUNTIF(Range, Criteria) Range : 조건을적용시켜검사할셀범위 Criteria : 숫자, 수식, 참조주소, 텍스트형식의조건을인수로지정 - 33 -
요약, 통계함수알아보기 함수이름형식설명 FREQUENCY FREQUENCY(Data_array, Bins_array) Data_array : 발생빈도를계산할데이터의범위 Bins_array : 간격이있는범위 LARGE, SMALL LARGE/SMALL(Array, k) Array : k 번째크기를결정할범위또는배열 k : 배열이나셀범위에서 k 번째의순위 MAX, MIN, MEDIAN, MODE.SNGL RANK.EQ/RANK.A VG MAX/MIN/MEDIAN/MODE.SNGL(N umber1, Number2,...) RANK.EQ, RANK.AVG(Number, Ref, Order) Number1, Number2,...: 최대값 ( 최소값, 중간값, 최빈값 ) 을구할숫자값들로 255개까지지정가능 Value1, Value2,...: 최대값 ( 최소값, 중간값, 최빈값 ) 을구할인수들 ( 숫자로된텍스트, TRUE/FALSE, 숫자, 빈셀등 ) 로최대 255개까지지정가능 Ref: 순위를구하기위한비교해야할대상범위 ( 고정된범위일경우참조주소에 $ 를붙여절대참조로지정 ) Order: 0이거나생략하면내림차순, 0이아니면오름차순 - 34 -
매출순위및그룹별요약하기 (9. 통계함수 ) 매출순위구하기 : =RANK.EQ( 담당자별매출액, 실적합계목록 ) 그룹별매출액합계 : =SUMIF( 그룹별목록, 그룹, 실적합계목록 ) - 35 -
매출순위및그룹별요약하기 (9. 통계함수 ) 평균매출구하기 : ROUND, AVERAGEIF 함수등중첩으로사용하기 =ROUND( 그룹별평균매출액, 백단위까지표시 ) 그룹별평균매출액 : =AVERAGEIF( 그룹별목록, 그룹, 실적합계목록 ) 1 [ 수식 ] 탭- [ 함수라이브러리 ] 그룹- [ 수학 / 삼각 ]-[ROUND] 2 중첩함수로 [AVERAGEIF] 함수추가 3 수식입력줄에서 ROUND함수이름클릭하여함수이동하기 4 ROUND함수의 Num_digits에 -2 입력후 Enter - 36 -
그룹별매출통계계산하기 (9. 통계함수 ) 매출 1,2,3 위구하기 : =LARGE( 매출액목록, 순위값 k) 10 억이상실적담당자수 : =COUNTIF( 매출액목록, 조건 ) - 37 -
논리정보및텍스트함수알아보기 함수이름형식설명 AND, OR AND/OR(Logical1, Logical2,...) Logical1, Logical2,... : TRUE 또는 FALSE 값을가지는조건으로최대 255개까지지정가능 TRUE, FALSE TRUE/FALSE() 인수없음 IF IF(Logical_test, value_if_true, value_if_false) Logical_test : 참 (True) 이나거짓 (False) 로판정할수있는값이나식 Value_if_true : 조건의결과가참 (True) 일때반환되는값 Value_if_false : 조건의결과가거짓 (False) 일때반환되는값 ( 생략하면 'False' 로반환되며, 비워두면 0 으로반환됨 ) IFERROR IFERROR(Value, Vlaue_if_error) Value : 오류 (Error) 인지를판정할수있는값이나식 Value_if_error : 오류 (Error) 일때반환되는값이나식 NOT NOT(Logical) Logical : TRUE 나 FALSE 로판정할수있는값이나식 CONCATENATE CONCATENATE(text1,text2,...) text1, text2,.. : 텍스트, 숫자, 셀주소등을인수로지정할수있으며, 최대 255개까지지정가능 LFT, RIGHT LEFT/RIGHT (Text, Num_chars) Text : 추출할문자가포함된텍스트 Num_chars : 추출할문자수로생략되면 1로간주됨 MID MID(Text, Start_num, Num_chars) Text : 추출할문자가포함된텍스트 Start_num : 추출할문자열의시작위치 Num_chars : 추출할문자수 - 38 -
논리정보및텍스트함수알아보기 함수이름형식설명 LEN LEN(Text) Text : 문자수를구할텍스트 REPLACE REPLACE(Old_text, Start_num, Num_chars, New_text) Old_text : 변경전의텍스트 Start_num : 변경전텍스트에서새로운문자로바뀔시작위치 Num_chars : 변경할문자수 New_text : 바꾸어넣을텍스트 TRIM TRIM(Text) Text : 공백을제거할텍스트 IS 로시작하는정 보함수 IS 함수 (Value) Value : TRUE 또는 FALSE 값을검사할셀또는셀참조 N N(Value) Value : 숫자로변환할셀로숫자는그대로, 날짜는해당하는일련번 호로, TRUE 는 1, FALSE 는 0 으로변환 - 39 -
DM 발송명단의상호와주소 (10. 문자함수및논리 ) 상호다시작성 : =REPLACE( 이전상호, 시작글자, 바꿀글자수, 바꿀새텍스트 ) 주소완성 : =CONCATENATE( 서울시송파구, 동이름, 공백, 번지 ) 서울시송파구 를포함하고 동, 번지 를연결하는새로운주소 - 40 -
DM 발송명단의성별구하기 (10. 문자함수및논리 ) 성별문자추출하기 : =MID( 주민등록번호, 시작문자, 글자수 ) 성별구하기 : =IF( 조건, 남자, 여자 ) - 41 -
혼자해보세요 ( 함수실습 1) 실습 1: 등급별카드청구금액에대한보고서계산하기 - 42 -
5. 데이터베이스다루기 데이터정렬하기 다중조건으로데이터정렬하기 부분합으로간단한요약보고서작성하기 부분합으로요약보고서결과복사하기 자동필터에서데이터추출하기 고급필터로다중조건의데이터추출하기 색을기준으로데이터정렬및추출하기
데이터정렬하기 (11. 정렬및부분합 ) 리본메뉴를사용한정렬 Total 기준의정렬 : [ 홈 ] 탭 - [ 편집 ] 그룹 - [ 정렬및필터 ]- [ 숫자내림차순으로정렬 ] 바로가기메뉴를사용한정렬 영업본부별정렬 : 오른쪽마우스를클릭하여바로가기메뉴에서 [ 정렬 ]- [ 텍스트오름차순정렬 ] - 44 -
다중조건으로데이터 (11. 정렬및부분합 ) 다양한조건을만족하는정렬방법 [ 데이터 ] 탭- [ 정렬및필터 ] 그룹- [ 정렬 ] [ 정렬 ] 대화상자에서조건기준에따라정렬방법선택 영업본부, 팀명, 한글이름 은오름차순정렬 직급 은사용자지정목록에서 부장, 차장, 과장, 대리, 사원 순으로정렬 - 45 -
부분합으로간단한요약보고서 (11. 정렬및부분합 ) 정렬된데이터를그룹별로요약하는기능 [ 데이터 ] 탭 -[ 윤곽선 ] 그룹 - [ 부분합 ] 영업본부별합계와팁명별개수를이중부분합으로요약 - 46 -
부분합으로요약보고서결과 (11. 정렬및부분합 ) 부분합결과를그대로복사할경우숨겨진항목이모두복사됨 부분합결과만복사하려면 [ 홈 ] 탭 - [ 편집 ] 그룹 - [ 찾기및선택 ] 에서 [ 이동옵션 ] 클릭 [ 이동옵션 ] 대화상자에서 [ 화면에보이는셀만 ] 선택하여복사 - 47 -
자동필터에서데이터추출하기 (12. 자동필터 ) 선택한데이터로빠르게추출 ( 단순필터링 ) 다산기획 에서오른쪽마우스클릭 -[ 필터 ]- [ 선택한셀값으로필터링 ] 검색어를이용한추출 도서명 항목의필터단추클릭 - 검색어입력란에 학교 를입력하여추출 필터단추를이용한추출 가격 항목의필터단추클릭 - [ 숫자필터 ]- [ 해당범위 ] 선택 [ 사용자지정자동필터 ] 대화상자에서 10000 이상 20000 미만으로조건입력후추출 항목의값을선택하여추출 출간일 항목의필터단추클릭 - 2012 년 7 월, 8 월, 9 월 항목체크후추출 도서명에 학교 라는텍스트가포함되고가격이 10000 원대이면서출간일이 2012 년 3 사분기 (7 월, 8 월, 9 월 ) 인데이터 - 48 -
색기준으로데이터정렬및추출하기 (12. 자동필터 ) [ 데이터 ] 탭 - [ 정렬및필터 ] 그룹 - [ 정렬 ] 도서명의셀색이노랑, 연한빨강순으로정렬 가격의글꼴색이빨강인순으로정렬 도서명 의셀색데이터추출 - 49 -
혼자해보세요 ( 데이터실습 1) 실습 1: ( 주 )GB 연구소 에서성별 / 연구분야별연구비소계 ( 합계, 평균 ) 구하기 - 50 -