논리 함수 통계 함수 수학/삼각 함수 텍스트 함수 날짜 및 시간 함수

Presentation on theme: "논리 함수 통계 함수 수학/삼각 함수 텍스트 함수 날짜 및 시간 함수"— Presentation transcript:

1 논리 함수 통계 함수 수학/삼각 함수 텍스트 함수 날짜 및 시간 함수
7장 작업 시간을 확 줄여주는 실무 함수 논리 함수 통계 함수 수학/삼각 함수 텍스트 함수 날짜 및 시간 함수 찾기/참조 함수 정보 함수 재무 함수 데이터베이스 함수

2 논리 함수 여러 조건에 맞는 값을 구하는 IF 함수의 중첩 여러 조건을 비교하는 조건식이면 AND, OR, NOT 함수
함수범주 논리 함수형식 =AND(Logical1, Logical2,…,Logical30) =OR(Logical1, Logical,…,Logical30) =NOT(Logical) 인 수 - Logical : True나 False값을 가지는 조건

3 견적서 양식 만들기 논리 IF 중첩 함수 견적서는 제품을 공급하기 전에 공급자가 발주자에게 공급할 제품에 대한 내용과 비용을 기술하여 공급자에게 보내는 문서이다. 품목, 수량, 단가 등을 입력하면 자동으로 비용이 계산되는 견적서 양식을 IF 함수를 중첩해서 만들어본다 단가와 수량만 입력하면 자동으로 금액이 표시 되도록 하기 단가와 수량이 입력되기 전에는 금액을 공백으로 표시하기

4 양식 컨트롤을 이용한 판매 금액 계산 논리 IF 중첩 함수와 양식 컨트롤
워크시트 문서에는 버튼, 옵션 단추, 체크 박스, 콤보 상자 등 양식 컨트롤을 삽입하고 셀과 연결할 수 있으며 컨트롤을 실행시켜 수식을 변경하거나, 특정 명령을 수행하는 등의 다양한 작업을 수행하게 할 수 있다. 제품별 판매 금액을 계산하는 표에 옵션단추 컨트롤을 3개를 삽입하여 각 옵션 단추를 클릭하면 할인율이 적용된 금액이 자동으로 계산되도록 중첩 IF 함수와 양식 컨트롤을 이용하여 판매금액을 계산해 보도록 한다 원하는 할인율을 선택하면 자동으로 할인율이 적용된 금액이 표시되도록 한다

5 통계 함수 LARGE, SMALL 함수 함수범주 통계 함수 함수형식 인 수 =LARGE(array, k)
=SMALL(array, k) 인 수 array : 몇 번째로 큰 값/작은 값을 계산할 숫자 데이터 범위 -bins_array : 몇 번째로 큰 값/작은 값을 계산할 지 숫자로 번호 지정

6 통계 함수 배열 수식 배열 수식은 특정 범위의 배열과 배열, 배열과 셀, 배열과 비교 값끼리 비교하고 연산하는 수식
산술 연산자를 사용하면 연산된 결과 표시 비교 연산자를 사용하면 TRUE, FALSE가 값 표시 배열 수식은 수식 입력이 끝난 뒤에는 반드시 Ctrl+Shift+Enter 키를 눌러 앞서 입력한 수식이 배열 수식이라는 표시를 해주어야 한다. 배열 수식이 만들어지면 배열 수식 앞뒤에는 { } 기호가 표시되며 배열 수식 일부는 수정하거나 삭제할 수 없다 = 배열1 * 배열2 입력 후 CTRL + SHIFT + ENTER 키

7 통계 함수 FREQUENCY 함수 사용 예 함수범주 통계 함수 함수형식 인 수
=FREQUENCY(array, bins_array) 인 수 array : 빈도수를 계산할 데이터 범위 -bins_array : 발생 빈도수를 계산할 구간별 간격의 범위 사용 예 FREQUENCY(판매량, 구간수) 입력 후 CTRL + SHIFT + ENTER 키 구간 설명 : 999  : 판매량이 999 이하 1999  : 판매량이 1000부터 1999 사이 2999 : 판매량이 2000부터 2999 사이 공백 : 판매량이 3000 이상

8 신입사원 교육 평가표 완성하기 통계 FREQUENCY, LARGE, SMALL 함수와 배열 수식
배열 수식으로 60점 이상 인원수 구하기

9 수학/삼각 함수 곱하고 더하는 SUMPRODUCT 함수 사용 예 함수범주 수학/삼각 함수 함수형식 인 수
=SUMPRODUCT(array1, array2……) 인 수 - arrayN : 곱하고 더할 데이터 범위 사용 예 SUMPRODUCT(수량배열, 단가배열) + X X +

10 수학/삼각 함수 나머지를 구하는 MOD 함수 무작위 난수를 만드는 RAND 함수 RAND()*(b-a)+a 함수범주
함수형식 =MOD(number, divisor) 인 수 number : 피젯수로 나머지를 구하려는 수 -divisor : 제수로 나누려는 수 함수범주 수학/삼각 함수 함수형식 =MOD(array1, array2……) 인 수 - arrayN : 곱하고 더할 데이터 범위 무작위 난수를 만드는 RAND 함수 함수범주 수학/삼각 함수 함수형식 =RAND() =RANDBETWEEN(bottom, top) 인 수 RAND()는 인수 없이 0보다 크고 1보다 작은 사이의 난수 발생 bottom : 난수 발생할 최소 정수를 입력 - top : 난수 발생할 최대 정수를 입력 RAND()*(b-a)+a b : 최대값, a : 최소값

11 견적서 합계 금액 계산하기 수학/삼각 SUMPRODUCT 함수
COUNTIF 함수를 이용해서 교육 차수별 인원수 구하기

12 교육 차수 지정하기 수학/삼각 MOD 함수 회사에서 전사원을 대상으로 극기 훈련이나 정신 강화 교육 등을 진행한다. 1차수부터 3차수까지 사원들의 교육 차수를 정하려고 할 때 순서대로 차수를 입력하면 한 부서에 너무 많은 인원들이 한 번에 차출될 수 있으므로 MOD 함수를 사용하여 교육 참가 차수 지정하여 본다 COUNTIF 함수를 이용해서 교육 차수별 인원수 구하기 MOD 함수를 이용해서 교육 차수 1-3차수로 나눠 차출

13 샘플용 테스트 제품 선정하기 . . . 수학/삼각 RAND 함수
[F9] 키를 누를 때 마다 난수 발생

14 텍스트 함수 일부 글자를 추출하는 LEFT, RIGHT, MID 함수 사용 예 함수범주 텍스트 함수 함수형식 인 수
=LEFT(text, num_chars) =MID(text, start_num, num_chars) =RIGHT(text, num_chars) 인 수 - text : 추출할 문자가 있는 문자열 또는 셀의 주소 - num_chars : 추출할 문자의 수 - start_num : 추출한 문자열의 시작위치(문자열의 처음 시작 위치는 1) 사용 예 RIGHT(직급코드, 1) MID(직급코드, 4, 2) LEFT(직급코드, 2)

15 텍스트 함수 표시 형식을 지정하는 TEXT 함수 사용 예 함수범주 텍스트 함수 함수형식 인 수
=TEXT(value, format_text) =VALUE(text) 인 수 - value : 사용자 지정 형식을 지정할 문자, 숫자, 날짜/시간, 셀 주소 format_text : 사용자 지정 형식을 지정하여 텍스트로 변환 text : 숫자 타입으로 변경하려는 문자 데이터 또는 셀의 주소 또는 큰따옴표에 묶인 문자 사용 예

16 교통비 청구서 양식 완성하기 텍스트 TEXT, MID 함수
일반 회계 장부처럼 세금 계산서에 금액을 입력할 때 자릿수를 맞춰 한 칸에 하나씩 금액이 표시 되도록 MID ,TEXT 함수를 사용하여 교통비 청구서 양식을 완성한다

17 날짜 및 시간 함수 날짜의 개념 1900년 1월 1일을 기준으로 9999년 12월 31일까지를 1~ 라는 고유번호를 부여 셀 내용을 지우고, 숫자 100을 입력 ▲ 날짜가 입력된 셀 ▲ 숫자 100이 날짜로 환산됨 시간의 개념 24시간을 1로 정의하고, 0과 1사이의 소수점으로 표시 [홈]-[편집]-[지우기]-[서식 지우기]메뉴 선택 ▲ 오후 12시가 입력된 셀 ▲ 서식을 지워, 숫자로 환산됨

18 날짜 및 시간 함수 연, 월, 일의 YEAR, MONTH, DAY 함수 함수범주 날짜/시간 함수 함수형식 인 수 =NOW()
=DATE(year, month, day) =YEAR(serial_number) =MONTH(serial_number) =DAY(serial_number) 인 수 - NOW 함수는 인수가 없으며 오늘 날짜와 시간을 표시합니다. - year : 1900부터 9999까지의 숫자 - month : 1부터 12까지 숫자 - day : 1부터 31까지 숫자 - serial_number : 날짜 데이터 또는 날짜를 일수로 누적 표시한숫자

19 날짜 및 시간 함수 연, 월, 일의 간격을 계산 하는 DATEDIF 함수 함수범주 날짜/시간 함수 함수형식 인 수
=DATEDIF(start_date, end_date, interval) 인 수 - start_date : 두 날짜 사이 기간을 계산할 시작일 - end_date : 두 날짜 사이 기간을 계산할 종료일 - interval : 두 날짜 사이 계산 옵션 interval 설명 y 두 날짜 사이 경과된 연수 m 두 날짜 사이 경과된 개월 수 d 두 날짜 사이 경과된 일수 ym 두 날짜 사이 경과 년도를 제외한 나머지 개월 수 yd 두 날짜 사이 경과 년도를 제외한 나머지 일 수 md 두 날짜 사이 경과 년도와 개월 수를 제외한 나머지 일 수

20 날짜 및 시간 함수 요일을 숫자로 표시하는 WEEKDAY 함수 근무 일수를 반환하는 NETWORKDAYS 함수 함수범주
날짜/시간 함수 함수형식 =WEEKDAY (serial_number, return_type) 인 수 - serial_number : 날짜 데이터/날짜를 일수로 누적 표시한 숫자 - return_type : 숫자로 요일을 반환할 옵션 1 또는 생략: 1(일요일) ~ 7(토요일) 2 : 1(월요일) ~ 7(일요일) 3 : 0(월요일) ~ 6(일요일) 근무 일수를 반환하는 NETWORKDAYS 함수 함수범주 날짜/시간 함수 함수형식 =NETWORKDAYS (start_date, end_date, holiday) 인 수 - start_date : 근무 시작일 지정 - end_date : 근무 종료일 지정 - holiday : 특정 휴무일 지정

21 날짜 및 시간 함수 지정한 날짜의 개월 수를 계산하는 EDATE, EOMONTH 함수 함수범주 날짜/시간 함수 함수형식 인 수
=EOMONTH (start_date,months) =EDATE (start_date,months) 인 수 - start_date : 개월 수를 계산하기 위한 시작일 - months : 전이나 후의 개월 수

22 임금 대장에 날짜 데이터 입력하기 날짜/시간 YEAR, EDATE, NETWORKDAYS, DATEDIF 함수
임금대장에는 1년간의 급여와 상여금의 내역을 정리하고 퇴직자가 발생하면 퇴직금 정산내역을 보여주는 문서이다. 출근일수는  NETWORKDAYS 함수로, 퇴직금 지급년도와 지급일은 YEAR, EDATE 함수로, 근속기간과 근속년수는 DATEDIF 함수를 사용하여 임금대장을 완성해 본다

23 요일별 당직 수당 입력하기 날짜/시간 함수 WEEKDAY 함수
주말과 주중에 따라 차등을 두어 당직수당을 지급하려고 한다. 월~금요일에 당직을 서면 당직수당으로 20000을 지급하고 토~일요일에 당직을 서면 50000을 지급하도록 WEEKDAY 함수와 IF, OR 함수를 사용하여 당직수당을 표시하여 본다

24 찾기/참조 함수 원하는 값을 찾아주는 VLOOKUP, HLOOKUP 함수 함수범주 찾기/참조 함수 함수형식 인 수
=VLOOKUP (lookup_value,table_array,col_index_num, range_lookup) =HLOOKUP (lookup_value,table_array,row_index_num,range_lookup) 인 수 - lookup_value : 찾고 싶은 값 - table_array : 데이터를 검색하고 참조할 데이터 범위 - col_index_num : table_array 내의 열 번호(추출할 열) - row_index_num : table_array 내의 행 번호(추출할 행) - range_lookup : TRUE - 비슷하게 일치하는 경우  FALSE - 정확하게 일치하는 경우

25 찾기/참조 함수 VLOOKUP 사용 예 B2셀의 제품코드를 H3:J9범위에서 찾아 그 줄의 2번째 열에 있는 값을 가져옴
상품 코드를 상품 코드표에서 찾음

26 찾기/참조 함수 HLOOKUP 사용 예 B4셀의 수량을 F3:L4범위에서 찾아 그 줄의 2번째 행에 있는 값을 가져옴
수량을 수량별 할인율표에서 찾음

27 찾기/참조 함수 행/열 번호로 원하는 데이터 값을 찾는 INDEX 함수 INDEX 함수 사용 예 함수범주 찾기/참조 함수
함수형식 =INDEX (array, row_num, column_num) 인 수 array : 배열로 된 셀의 범위 row_num : 셀의 범위에서 참조할 행 번호 column_num : 셀의 범위에서 참조할 열 번호 INDEX 함수 사용 예

28 찾기/참조 함수 행/열 번호를 찾는 MATCH 함수 MATCH 함수 사용 예 함수범주 찾기/참조 함수 함수형식 인 수
=MATCH (lookup_value, lookup_array, match_type) 인 수 lookup_value : 상대적 행 또는 열 번호 찾으려는 값 lookup_array : 배열 행 또는 열 범위 match_type : 0 - 배열에서 찾고 싶은 첫 번째 값을 검색 1 - 오름차순으로 정렬되어 있는 배열에서 작거나 같은 값 중에 최대값 검색 -1 - 내림차순으로 정렬되어 있는 배열에서 크거나 같은 값 중에 최소값 검색 MATCH 함수 사용 예 [직급 표] 를 참조하여 ‘부장’ 값이 있는 행 번호 검색

29 찾기/참조 함수 목록 값을 번호로 검색하는 CHOOSE 함수와 행/열 번호를 알려주는 ROW, COLUMN 함수 함수범주
함수형식 =CHOOSE (index_no, value1, value2,value ) =ROW (reference) =COLUMN (reference) 인 수 index_no : 숫자로 이루어진 색인 값(1에서 254까지의 수) valueN : index_no에 따라 지정할 문자,숫자,주소(1에서 254까지 지정) reference : 특정 위치의 행 번호 또는 열 번호를 알고 싶은 셀 주소 생략 시 현재 위치의 셀 번호를 반환함

30 입/출고 상품 내역 비교하기 찾기/참조 VLOOKUP 함수와 IFERROR 함수
상품이 입고된 내역과 출고된 내역을 비교하여 입출고 상품 내역서를 완성해 본다. [출고 상품 내역]에서 상품명은 [입고 상품 내역] 표를 참조해서 표시하고 [입출고 상품 내역] 표에서 출고 수량은 [출고 상품 내역] 표를 참조해서 표시한다. [출고 상품 내역] 표를 참조해서 출고되지 않은 상품은 “미출고”라고 표시하고 셀 색을 채워 강조한다

31 지역/직급별 출장비표를 참조하여 출장비 내역서 만들기
찾기/참조 Index, Match 함수 [지역/직급별 출장비] 표를 참조하여 출장비 내역서의 일일 출장비가 표시되도록 INDEX 함수와 MATCH 함수를 사용하여 출장비 내역서를 완성해 본다 [지역/직급별 출장비] 표를 참조하여 일일 출장비 계산

32 회원 가입 명단에서 일련번호, 성별 입력하기 찾기/참조 ROW, MID, CHOOSE 함수
주민번호의 7번째 자리에 따라 1900년대: 1-남자 2-여자, 2000년대: 3-남자 4-여자, 1899년 이전 : 9-남자 0-여자

33 정보 함수 셀 값의 오류나 수식의 오류를 검사하는 ISxxx 정보 함수 함수범주 데이터베이스 함수형식 인 수
=ISXXX 함수(Value) 인 수 - Value : 오류를 검사할 셀이나 수식 isXXX함수 설명 ISBLANK 빈 셀을 참조하면 TRUE/아니면 FALSE ISERROR 셀이나 수식에 오류가 있으면 TRUE/아니면 FALSE ISERR #N/A 를 제외한 오류가 있으면 TRUE/아니면 FALSE ISNA #N/A 오류가 있으면 TRUE/아니면 FALSE ISEVEN 짝수 숫자인 경우 TRUE/아니면 FALSE ISODD 홀수 숫자인 경우 TRUE/아니면 FALSE ISLOGICAL 논리 값인 경우 TRUE/아니면 FALSE ISNUMBER 숫자 값인 경우 TRUE/아니면 FALSE ISNOTEXT 숫자와 빈 셀인 경우 TRUE/아니면 FALSE ISTEXT 문자인 경우 TRUE/아니면 FALSE ISREF 참조 값인 경우 TRUE/아니면 FALSE

34 상품 입고 내역서에 나타난 오류 수정 정보 ISERROR, ISNUMER 함수
상품 코드표에 상품 코드가 없는 경우 #N/A 오류 표시가 난다. 그리고 수량을 입력할 때 실수로 숫자 뒤에 쉼표를 입력했더니 금액을 계산하는 수식에 #VALUE! 오류가 나타난다. ISERROR와 ISNUMERIC 함수를 써서  #N/A 오류에는 “코드오류”라고 #VALUE! 오류에는 “수량오류”라고 표시해본다 IF, ISNUMER 함수를 사용하여 #VALUE! 오류에 ‘수량오류’ 표시 IF, ISERROR 함수를 사용하여 #N/A 오류를 ‘코드오류’ 표시

35 재무 함수 재무에 필요한 FV, PMT, IMPT, PPMT, PV, NPER, RATE 함수 함수범주 재무 함수형식 인 수
=FV(rate,nper,pmt,pv,type): 투자액의 미래가치 계산(Future Value) =PMT(rate,nper,pv,fv,type): 정기 상환 원리금 계산(PayMenT) =IPMT(rate,per,nper,pv,fv,type): 투자액에 대한 기간의 이자 =PPMT(rate,per,nper,pv,fv,type):투자액에 대한 기간의 원금 상환액 =PV(rate,nper,pmt,fv,type): 투자액의 현재가치 계산(Present Value) =NPER(rate,pmt,pv,fv,type): 투자기간 계산(Number of PERiods) =RATE(nper,pmt,pv,fv,type): 기간당 이율 계산 인 수 rate: 기간당 이자율 - nper: 납입기간 또는 납입횟수 - pmt: 정기 납입액 pv: 현재 가치 fv: 미래 가치 per : 이자율을 계산할 기간(1과 nper 사이) type: 납입 시점(월초는 1, 월말은 0또는 생략)

36 대출금 상황액과 이자율 계산하기 재무함수 재무 함수를 PMT, FV, PV, NPER, RATE 함수를 사용하여 대출금 상환액, 정기 적금 만기금, 연금의 현재 가치, 납입 기간, 이자율을 계산해본다(기준 시점은 월말)

37 데이터베이스 함수 데이터베이스 함수 함수범주 데이터베이스 함수형식 인 수
=데이터베이스 함수(Database, Field, Criteria) 인 수 - Database : 제목 필드가 포함된 전체 범위 - Field : 결과 값을 계산하기 위한 필드 Criteria : 조건 범위 함수이름 설명 DSUM 조건에 맞는 항목의 합계를 계산 DAVERAGE 조건에 맞는 항목의 평균을 계산 DCOUNT 조건에 맞는 항목의 숫자 갯수 DCOUNTA 조건에 맞는 항목의 개수(공백제외) DMAX 조건에 맞는 항목의 최대값 DMIN 조건에 맞는 항목의 최소값 DGET 조건에 맞는 항목의 레코드 추출 (조건에 맞는 레코드가 여러 개면  #NUM! 오류 값이 반환) DPRODUCT 조건에 맞는 항목의 값을 곱하기 DSTDEV 조건에 맞는 항목의 포본 집단의 추정 표준편차 DSTDEVP 조건에 맞는 항목의 전체 모집단의 실제 표준편차 DVAR 조건에 맞는 항목의 포본 집단의 추정 분산 DVARP 조건에 맞는 항목의 전체 모집단의 실제 분산 

38 도서 조회 화면 완성하기 데이터베이스-DGET 함수