Lecture 07 Advanced Features

Presentation on theme: "Lecture 07 Advanced Features"— Presentation transcript:

1 Lecture 07 Advanced Features

2 Table of Contents Advanced Features 1 Advanced Features 2

3 Advanced Features 1 데이터베이스 함수
=DGET(데이터베이스 범위, 레코드를 구할 열 번호나 필드명, 조건 범위) 용도 : database 범위에서 조건에 맞는 데이터가 하나이면 해당 레코드를 표시함(고급 필터와 일부 유사, 개별 값을 구할 때 사용) DPRODUCT() : Product DAVERAGE() : Average DSUM() : Sum DSTDEV() : Standard Deviation DVAR() : Variance DCOUNT() : 숫자가 입력된 개수 표시(COUNT()) DCOUNTA() : 데이터가 입력된 개수 표시(COUNTA()) DMAX() : Maximum DMIN() : Minimum

4 Advanced Features 1 재무함수 =FV(기간당 이율, 총 납입 기간, 정기 적립 금액, 현재 가치, 납입 시점)
납입한 금액에 이율을 복리로 적용하여 원금과 이자의 합을 표시 =IPMT(기간당 이율, 이자 계산 기간, 총 납입 기간, 현재 가치, 미래 가치) 대출받은 원금에 대하여 매월 상환해야 하는 이자를 표시 =NPER(기간당 이율, 정기 적립 금액, 현재 가치, 미래 가치, 납입 시점) 목표금액을 만들기 위해 소요되는 기간을 표시 =PMT(기간당 이율, 총 납입 기간, 현재 가치, 미래 가치, 납입 시점) 대출받은 원금에 대하여 매월 상환해야 하는 원금과 이자를 표시 =PPMT(기간당 이율, 이자 계산 기간, 총 납입 기간, 현재 가치, 미래 가치) 대출받은 원금에 대해 이자를 제외하고 매월 상환할 원금을 표시 =PV(기간당 이율, 총 납입 기간, 정기 적립 금액, 미래 가치, 납입 시점) 투자액에 대한 현재 가치를 표시 =RATE(총 납입 기간, 정기 적립 금액, 현재 가치, 미래 가치, 납입 시점) 목표금액을 만들기 위해 필요한 이자율을 표시 납입 시점이 1이면 납입 시점 초를, 0이면 납입 시점 말을 의미함

5 Advanced Features 1 레코드 관리

6 Advanced Features 1 레코드 관리 셀 포인터는 반드시 해당 테이블(DB) 내에 존재해야 함

7 Advanced Features 1 레코드 관리 / 조건 : 수강기간이 3이상인 레코드 검색

8 Advanced Features 1 레코드 관리 / 레코드 추가

9 Advanced Features 1 정렬 오름차순과 내림차순의 정렬 순서 오름차순
숫자 → 특수 문자 → 한글 → 논리값 → 오류값 → 빈 셀 내림차순 오류값 → 논리값 → 영문자 → 한글 → 특수 문자 → 숫자 → 빈 셀

10 Advanced Features 1 가상분석(데이터 표) : 데이터 표 기능을 이용하여 인수 셀의 변화에 따른 결과를 구하는 방법 결과값 해석 : (28+25)+예술성+난이도*2(20점 만점이기에 2배) ex. D10 cell : (6*2)=71

11 Advanced Features 1 통합 : 분산된 여러 영역의 데이터에 함수를 적용하여 통합된 결과를 표시하는 기능

12 Advanced Features 1 고급필터 : 같은 행에 위치한 조건은 AND로 인식, 다른 행에 위치한 조건은 OR로 인식함 성별이 남자가 아니면서 독서가 10이상이거나, 지역이 수도권이 아니면서 평균이 5이상

13 Advanced Features 1 시나리오 : 여러 인수의 영향을 받는 수식 셀에 대해 인수의 변화에 따라 수식 셀의 결과 값이 변하는 여러 가지 상황을 예측/요약하는 기능

14 Advanced Features 1 시나리오 : 여러 인수의 영향을 받는 수식 셀에 대해 인수의 변화에 따라 수식 셀의 결과 값이 변하는 여러 가지 상황을 예측/요약하는 기능

15 Advanced Features 1 시나리오 : 여러 인수의 영향을 받는 수식 셀에 대해 인수의 변화에 따라 수식 셀의 결과 값이 변하는 여러 가지 상황을 예측/요약하는 기능

16 Advanced Features 1 시나리오 : 여러 인수의 영향을 받는 수식 셀에 대해 인수의 변화에 따라 수식 셀의 결과 값이 변하는 여러 가지 상황을 예측/요약하는 기능

17 Advanced Features 1 변경내용 추적 시트보호 : 현재 시트 전체를 보호
통합 문서 보호 : 통합 문서 전체를 보호 통합 문서 공유 : 여러 사람이 통합 문서를 공유하고자 할 때 사용 통합 문서 보호와 공유 : 다른 사용자가 변경 내용을 제거하지 못하도록 통합 문서를 보호 범위 편집 허용 : 지정한 범위를 제외한 셀을 보호 변경 내용 추적 : 데이터가 변경되는 상황을 기록한 후 적용할 때 사용

18 Advanced Features 1 변경내용 추적 새로운 통합 문서 만들어서 실습
본 기능은 공유 통합 문서에서만 사용할 수 있음

19 Advanced Features 1 통합 문서 보호, 시트보호
구조 : 시트 이동, 삭제, 숨김, 숨김 해제, 이름 변경, 새 시트 삽입 등의 작업 금지 창 : 통합 문서를 열 때마다 창의 크기와 위치가 같도록 창을 보호 제한된 액세스 : 사용자의 편집에 대한 권한, 사용 기간, 인쇄, 복사 제한

20 Advanced Features 1 유효성 검사 셀에 입력할 수 있는 데이터 형식이나 데이터 값의 범위 등을 제한
사용자들이 유효하지 않은 데이터의 입력을 불가능하게 하거나 유효하지 않은 데이터의 입력을 허용하더라도 작업 완료 후 확인하게 함 셀에 입력할 수 있는 내용에 대한 설명이나 오류 수정에 도움이 되는 메시지를 표시함

21 Advanced Features 1 유효성 검사

22 Advanced Features 1 유효성 검사 중지 : 유효하지 않은 데이터를 입력하면 오류 메시지가 표시되며 입력이 제한됨
경고 : 유효하지 않은 데이터를 입력하면 오류 메시지가 표시되며 입력 데이터를 수정하거나 오류 메시지를 무시하고 입력할 수 있음 정보 : 유효하지 않은 데이터를 입력하면 오류 메시지가 표시되지만 입력은 제한되지 않음

23 Advanced Features 1 Macro
해당 작업이 필요할 때마다 실행할 수 있도록 일련의 명령과 함수를 MS Visual Basic Module로 저장해 놓은 것 자주 수행하는 작업을 자동화 작성한 매크로는 바로 가기 키, 도형, 양식 도구 모음의 단추 등에 연결하여 실행할 수 있음

24 Advanced Features 1 Macro
매크로 바이러스가 널리 퍼지면서 엑셀에서는 매크로가 포함된 파일을 이용할 때 위험성을 알릴 수 있음

25 Advanced Features 1 Macro
매크로 이름 : 매크로 이름에 영문과 숫자를 섞어 지정할 수 있음. 단, 이름의 첫 글자에는 숫자/공백문자를 사용할 수 없음 바로 가기 키 : 키를 누르면 Ctrl과 조합하여 사용할 수 있는 매크로 바로 가기 키가 지정됨. 엑셀의 고유한 바로 가기 키와 중복되지 않게 사용하여야 하며, 만일, 중복 될 경우 지정한 매크로 바로 가기 키가 우선함. 매크로 저장 위치 : 매크로가 파일로 저장되는 위치를 지정. ‘개인용 매크로 통합문서’(다른 통합 문서에서도 사용 가능), ‘새 통합 문서’, ‘현재 통합 문서’ 중에서 선택하여 지정할 수 있음 설명 : 작성하는 매크로에 대한 설명을 입력할 수 있음. 설명은 반드시 입력해야 하는 것은 아님

26 Advanced Features 1 Macro

27 Advanced Features 1 Macro 부서별 정렬 혹은 이름별 정렬도 따로 위와 같이 만듦

28 Advanced Features 1 양식 컨트롤

29 Advanced Features 1 양식 컨트롤 단추 : 매크로를 지정하여 실행함
콤보 상자 : 드롭다운 목록 상자. 목록 상자에서 선택한 항목이 텍스트 상자에 나타남 확인란 : 옵션을 설정하거나 해제함. 시트나 그룹에서 동시에 확인란을 두 개 이상 선택할 수 있음 스핀 단추 : 값을 늘리거나 줄임. 값을 늘리려면 위쪽 화살표를 클릭하고 값을 줄이려면 아래쪽 화살표를 클릭함 목록 상자 : 항목 목록을 표시함 옵션 단추 : 그룹 상자에 포함된 옵션 그룹 중 하나를 선택함. 몇 가지 항목 중 하나만 선택할 수 있게 하려는 경우 옵션 단추를 사용함 그룹 상자 : 옵션 단추나 확인란 등 관련 컨트롤을 그룹으로 묶음 레이블 : 컨트롤이나 시트 또는 양식에 대한 정보를 제공하는 텍스트 스크롤 막대 : 스크롤 화살표를 클릭하거나 스크롤 상자를 끌어서 값 범위를 스크롤 함. 스크롤 화살표 사이를 클릭하면 한 페이지씩 이동할 수 있음

30 Advanced Features 1 양식 컨트롤

31 Table of Contents Advanced Features 1 Advanced Features 2

32 Advanced Features 2 유효성 검사(응용)

33 Advanced Features 2 유효성 검사(응용)

34 Advanced Features 2 유효성 검사(응용) 조건부 서식은 무제한으로 지정할 수 있음
B5:J14범위 지정 후에 조건부 서식 사용 조건부 서식의 수식은 조건을 검사할 범위 목록 중 첫 번째 셀을 기준으로 하여 작성하며 주소 참조 방식의 지정에 주의해야 함

35 Advanced Features 2 유효성 검사(응용) ‘아이콘만 표시’를 선택하면 셀에 값이 표시되지 않고 아이콘만 표시됨

36 Advanced Features 2 유효성 검사(응용)

37 Advanced Features 2 유효성 검사(실습) : 합격/불합격 여부에 따른 강조 표시

38 Advanced Features 2 유효성 검사(실습) 1 2 4 3

39 Advanced Features 2 데이터 변동 시나리오 분석(응용) [ 조건 ]
셀 이름 정의 : B14는 ‘이익률’, B15는 ‘불량률’, G13는 ‘이익합계’로 정의 시나리오 1 : 시나리오 이름은 ‘이익인상’, 이익률 27%, 불량률 2%로 설정 시나리오 2 : 시나리오 이름은 ‘이익인상’, 이익률 20%, 불량률 5%로 설정

40 Advanced Features 2 데이터 변동 시나리오 분석(응용)
‘범위’를 ‘통합문서’로 해야 하는 이유 : 결과값(요약, 피벗테이블) 등이 다른 시트에 표현되기 때문에

41 Advanced Features 2 데이터 변동 시나리오 분석(응용)

42 Advanced Features 2 데이터 변동 시나리오 분석(응용)

43 Advanced Features 2 데이터 변동 시나리오 분석(응용)

44 Advanced Features 2 데이터 변동 시나리오 분석(응용)

45 Advanced Features 2 데이터 변동 시나리오 분석(실습) [ 조건 ]
셀 이름 정의 : C4는 ‘판매수량’, C15는 ‘영업이익’으로 정의 시나리오 1 : 시나리오 이름은 ‘판매수량 증가’, 판매수량은 4000으로 설정 시나리오 2 : 시나리오 이름은 ‘판매수량 감소’, 판매수량은 2000으로 설정

46 Advanced Features 2 데이터 변동 시나리오 분석(실습)

47 Advanced Features 2 데이터 변동 시나리오 분석(실습)

48 Advanced Features 2 데이터 변동 시나리오 분석(실습)
추가해야 할 시나리오가 있을 경우 ‘추가’버튼 사용(연속적 추가)

49 Advanced Features 2 데이터 변동 시나리오 분석(실습)

50 Advanced Features 2 Macro
해당 작업이 필요할 때마다 실행할 수 있도록 일련의 명령과 함수를 MS Visual Basic Module로 저장해 놓은 것 자주 수행하는 작업을 자동화 작성한 매크로는 바로 가기 키, 도형, 양식 도구 모음의 단추 등에 연결하여 실행할 수 있음

51 Advanced Features 2 Macro
매크로 바이러스가 널리 퍼지면서 엑셀에서는 매크로가 포함된 파일을 이용할 때 위험성을 알릴 수 있음

52 Advanced Features 2 Macro
매크로 이름 : 매크로 이름에 영문과 숫자를 섞어 지정할 수 있음. 단, 이름의 첫 글자에는 숫자/공백문자를 사용할 수 없음 바로 가기 키 : 키를 누르면 Ctrl과 조합하여 사용할 수 있는 매크로 바로 가기 키가 지정됨. 엑셀의 고유한 바로 가기 키와 중복되지 않게 사용하여야 하며, 만일, 중복 될 경우 지정한 매크로 바로 가기 키가 우선함. 매크로 저장 위치 : 매크로가 파일로 저장되는 위치를 지정. ‘개인용 매크로 통합문서’(다른 통합 문서에서도 사용 가능), ‘새 통합 문서’, ‘현재 통합 문서’ 중에서 선택하여 지정할 수 있음 설명 : 작성하는 매크로에 대한 설명을 입력할 수 있음. 설명은 반드시 입력해야 하는 것은 아님

53 Advanced Features 2 Macro

54 Advanced Features 2 Macro 부서별 정렬 혹은 이름별 정렬도 따로 위와 같이 만듦

55 Advanced Features 2 양식 컨트롤

56 Advanced Features 2 양식 컨트롤 단추 : 매크로를 지정하여 실행함
콤보 상자 : 드롭다운 목록 상자. 목록 상자에서 선택한 항목이 텍스트 상자에 나타남 확인란 : 옵션을 설정하거나 해제함. 시트나 그룹에서 동시에 확인란을 두 개 이상 선택할 수 있음 스핀 단추 : 값을 늘리거나 줄임. 값을 늘리려면 위쪽 화살표를 클릭하고 값을 줄이려면 아래쪽 화살표를 클릭함 목록 상자 : 항목 목록을 표시함 옵션 단추 : 그룹 상자에 포함된 옵션 그룹 중 하나를 선택함. 몇 가지 항목 중 하나만 선택할 수 있게 하려는 경우 옵션 단추를 사용함 그룹 상자 : 옵션 단추나 확인란 등 관련 컨트롤을 그룹으로 묶음 레이블 : 컨트롤이나 시트 또는 양식에 대한 정보를 제공하는 텍스트 스크롤 막대 : 스크롤 화살표를 클릭하거나 스크롤 상자를 끌어서 값 범위를 스크롤 함. 스크롤 화살표 사이를 클릭하면 한 페이지씩 이동할 수 있음

57 Advanced Features 2 양식 컨트롤

58 Advanced Features 2 Macro와 양식컨트롤 응용 콤보상자 선택 선택 값이 표시될 영역지정(임의)

59 Advanced Features 2 Macro와 양식컨트롤 응용 해당 셀에 맞게 변경함 Copy and Paste

60 Advanced Features 2 Macro와 양식컨트롤 응용

61 Advanced Features 2 Macro와 양식컨트롤 응용
검사 전에 점수와 결과가 표시되는 것을 막기 위해 ‘점수 셀’과 ‘결과 셀’의 글꼴 색을 ‘흰색’으로 지정 함

62 Advanced Features 2 Macro와 양식컨트롤 응용

63 Advanced Features 2 Macro와 양식컨트롤 응용

64 Advanced Features 2 Macro와 양식컨트롤 응용

65 Advanced Features 2 Macro와 양식컨트롤 응용

66 Advanced Features 2 Macro와 양식컨트롤 응용

67 Advanced Features 2 Macro와 양식컨트롤 응용

68 Advanced Features 2 Macro와 양식컨트롤 응용

69 Advanced Features 2 경력/재직 증명서 작성

70 Advanced Features 2 경력/재직 증명서 작성

71 Advanced Features 2 경력/재직 증명서 작성 직접입력

72 Advanced Features 2 경력/재직 증명서 작성 Vector : 찾는 값, 찾을 범위, 결과 범위
Array : 찾는 값, 범위

73 Advanced Features 2 경력/재직 증명서 작성 =LOOKUP(E2,사_원_번_호,부_서)

74 Advanced Features 2 =DATEDIF() 함수 경력/재직 증명서 작성
=LOOKUP(E2,사_원_번_호,퇴_사_일_자) L19 =LOOKUP(E2,사_원_번_호,입_사_일_자) =IF(L19=0,TODAY(),L19) =DATEDIF(D19,H19,"y")&"년 “ &DATEDIF(D19,H19,"ym")&"월 “ &DATEDIF(D19,H19,"md")&"일 근무하였습니다." =DATEDIF() 함수 목적 : 시작일과 종료일 사이의 간격을 지정한 단위로 구함 DATEIF(시작일, 종료일, “형식”) 형식 “Y” : 두 날짜 사이의 연도 수 “M” : 두 날짜 사이의 월(개월) 수 “D” : 두 날짜 사이의 일 수 “YM” : 연도를 무시한 월(개월) 수(Month) “MD” : 연도와 월을 무시한 일 수(Day) “YD” : 연도를 무시한 일 수(Day)

75 Advanced Features 2 경력/재직 증명서 작성