Transcript (예) =함수명(인수1, 인수2, …)

Chapter 4.
수식과 함수
학습목표
1.
2.
3.
4.
수식 입력과 셀 참조 이용하기
함수 이용하기
기본함수 이용하기
고급함수 이용하기
2
수식 입력과 셀 참조 이용하기
1. 수식이란?(6-1.xlsx)
 등호로 시작하여 숫자 또는 셀 주소와 연산자로 이루어진 계산식
 (예) = E4 – F4 * 5000
1) 등호(=) : 수식 입력할 때 반드시 먼저 입력해야 함
2) 연산자
① 산술연산자 : +, -, *, /, %, ^
② 비교연산자 : >, <, >=, <=, =, <>
③ 참조연산자 : 참조할 셀이나 영역 지정 :(A1:E1), ,(A1, E1), 공백(A1:E1 C2:P3)
④ & : 데이터 연결 연산자 : 1+2&”그림”, “대한”&”민국”
3) 직접 입력하는 값
① 직접 입력하는 문자나 숫자(5000)
4) 참조(E4, F4) : 수식에서 셀 주소를 이용
① 상대참조 : 연산이 되는 셀의 위치에 따라 셀 주소가 자동으로 변함(성적표)
② 절대참조 : 셀 주소가 항상 고정되어 있음($E$4) – “F4”키 사용함(3월)
③ 혼합참조 : 열이나 행만을 고정하는 방법(E$4 또는 $E4) - 혼합참조.xlsx
3
함수 이용하기
1. 함수란?
 복잡하고 반복적인 계산작업을 쉽고 간단하게 처리할 수 있도록 정의된 수식
 (예) =함수명(인수1, 인수2, …)
1)
2)
3)
4)
5)
등호(=) : 함수 식 앞에 반드시 사용됨
함수명 : 수식을 함축하고 있는 함수 이름
괄호 : 함수명 다음에는 반드시 사용되며 항상 쌍으로 사용됨
인수 : 계산에 사용되는 값들이며, 반드시 사용됨
쉼표 : 인수를 구분하기 위한 기호
2. 함수식 사용방법
1) 자동합계(∑) : [홈] – [편집] – [자동합계]
2) 직접입력 : 수식 입력 줄에 “=함수명(인수,…)” 형식으로 입력함
3) 함수마법사 : [수식] – [함수 라이브러리] –[함수삽입]
4) 마우스클릭(함수마법사) : 수식 입력 줄 왼쪽의
클릭
5) [수식] – [함수라이브러리] 그룹의 기능별 함수 선택
6) 단축키(함수마법사) : shift +F3
4
기본 함수 이용하기
 자동합계 함수
① sum : 인수들의 합계
② average : 인수들의 평균
③ count : 인수에서 숫자가 들어있는 셀 개수
④ max : 인수 중 가장 큰 수
⑤ min : 인수 중 가장 작은 수
 엑셀에서 사용 빈도가 높은 함수
① count 계열 : 셀의 개수
② round 계열 : 반올림
③ 날짜와 시간 관련 함수
④ IF : 조건에 따라 참/거짓이 결정
⑤ countif : 조건에 맞는 데이터의 개수
⑥ sumif : 조건에 맞는 데이터들의 합
5
기본 함수 이용하기(자동합계)
1. (예제) 6-3.xlsx
2. SUM 함수 : 지정된 범위의 모든 셀의 합
1) =SUM(인수1, 인수2,…)
3. AVERAGE 함수 : 인수들의 평균
1) =AVERAGE(인수1, 인수2,…)
4. MAX 함수 : 인수중의 최대값
1) =MAX(인수1, 인수2,….)
5. MIN 함수 : 인수 중의 최소값
1) =MIN(인수1, 인수2,….)
6. RANK 함수 : 선택된 셀의 순위
1) 통계함수
2) =RANK(순위로 선택할 셀, 범위, 차순)
3) (예)RANK(H5, H5:H11, 0)
① 0 : 내림차순, 1 : 오름차순
7. LARGE : 지정된 범위에서 K번째의 큰 값
1) =LARGE(범위, K)
8. SMALL : 지정된 범위에서 K번째의 작은 값
1) =SMALL(범위, K)
9. (연습문제)6-3.xlsx(sheet2)
6
기본 함수 이용하기(COUNT)
1. (예제)6-4.xlsx
2. COUNT 함수
1) 지정된 셀 영역에서 숫자 데이터가 입력된 셀의 개수
2) =count(값1, 값2,….)
3. COUNTA 함수
1) 비어 있지 않은 셀의 개수(문자와 숫자를 포함한 셀의 개수)
2) =counta(값1, 값2,…)
4. COUNTBLANK 함수
1) 비어 있는 셀의 개수
2) =countblank(범위) =>인수가 한 개
7
기본 함수 이용하기(ROUND)
1. ROUND 함수
1) 지정한 자릿수가 되도록 반올림한다.
2) =ROUND(number, 자릿수)
2. ROUNDUP 함수
1) 지정한 자릿수가 되도록 무조건 올림한다.
2) =ROUNDUP(number, 자릿수)
3. ROUNDDOWN 함수
1) 지정한 자릿수가 되도록 무조건 내림한다.
2) =ROUNDDOWN(number, 자릿수)
 자릿수 : 양수=>소수점 아래, 음수=> 정수에서 지정
 (예) round(1234.567, 0) =>1235
round(1234.567, 2) => 1234.57
round(1234.567, -2) =>1200
roundup(1234.567, 2) => 1234.57(-2 : 1300)
rounddown(1234.567, 2) => 1234.56(-2 : 1200)
(연습문제1)6_4.xlsx : 100원 단위에서 적용하기
(연습문제2)6_3.xlsx 의 “sheet1”에서 서류,전공,면접의 평균점수를
소수점 1자리까지 구하기
8
기본 함수 이용하기(날짜와 시간 관리)
1. TODAY( )함수
1) 현재 날짜
2. NOW( )함수
1) 현재 시간(현재 날짜 포함)
3. 지정된 날짜간의 일수 구하기
1) B2 – B3 =>67일
2) TODAY( ) – B2 => 1900-07-18(200일 : 표시형식에서 00”일” 형식으로 변경)
3) A2 - DATE(2013,5,30) =>173일(표시형식 : 00”일”)
4) 날짜간의 일수를 개월/년 수로 표현하는 함수
 DATEDIF(B2, A2, ”M”) =>6개월(“개월” 표시형식으로 변경)
 DATEDIF(B2, A2, ”Y”) => ??
5) (연습문제)6-4.xlsx(“TODAY” 시트)
4. YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(날짜), MINUTE(날짜),
SECOND(날짜), WEEKDAY(날짜)
1) YEAR(A2) => 2013, MONTH(A2) => 11
2) HOUR(A3)=> 10, WEEKDAY(A3) => 3(화요일, 표시형식 : aaaa)
5. (연습문제)6-4.xlsx(“YEAR” 시트)
9
기본 함수 이용하기(IF 함수)
1.
논리 검사를 수행하며 참이나 거짓을 나타낸다.
1) 논리함수
① AND(조건1, 조건2,…) : 조건들이 모두 참일 때만 참, 아니면 거짓
② OR(조건1, 조건2,…) : 조건 중에 하나만 참이면 참
③ NOT(논리식) : 논리식 결과의 반대 값
2) 사용형식
1) IF(조건, 참, 거짓)
2) (예문)
3) 함수 라이브러리 사용 : 6-4.xlsx(“IF” 시트)
4) (연습문제)6-기타예제.xlsx(sheet1)
=>평균이 80이상이면 합격, 아니면 불합격
10
기본 함수 이용하기(COUNTIF 함수)
1.
조건에 맞는 데이터의 개수를 구한다.
1) 통계함수
2) 사용형식
1) COUNTIF(범위, 조건)
2) (예문)6-4.xlsx(“COUNTIF” 시트)
3) (연습문제)6-기타예제.xlsx(sheet2)
3) (예문)
COUNTIF((F5:F16,">"&MEDIAN(F5:F16))
11
기본 함수 이용하기(SUMIF 함수)
1.
조건에 맞는 데이터의 합계를 구한다.
1) 사용형식
1) 수학/삼각 함수
2) SUMIF(범위, 조건, 합산할 범위)
3) (예문)
4) 함수 마법사사용
=> 6-4.xlsx(“COUNTIF” 시트)
5) (연습문제)6-기타예제.xlsx(sheet2)
12
고급 함수 이용하기
 텍스트 함수(교재 p.85)
① left: 왼쪽부터 지정된 개수의 문자
② right: 오른쪽부터 지정된 개수의 문자
③ mid: 주어진 위치부터 지정된 수만큼의 문자
 찾기/참조 함수(교재 p.76)
① choose : 지정된 값의 인수의 값
② match : 값을 찾아서 위치를 알려줌
③ index : 지정된 행과 열의 데이터 값
④ vlookup : 주어진 범위에서 데이터를 찾고 그 행의 열에서 데이터를 가져온다
 데이터베이스 함수(교재 p.79)
=> 데이터베이스의 목록에서 조건에 맞는 합/평균/셀의수/최대/최소 값을 구한다
① DSUM
② DAVERAGE
③ DCOUNT, DCOUNTA
④ DMAX, DMIN
13
고급 함수 이용하기(텍스트 함수)
1. LEFT 함수
1) 형식 : LEFT(text, 숫자)
2) text의 왼쪽에서 숫자만큼 문자를 가져온다.
3) (예문)
2. RIGHT 함수
1) 형식 : RIGHT(text, 숫자)
2) text의 오른쪽에서 숫자만큼 문자를 가져온다.
3) (예문)
3. MID함수
1) 형식 : MID(text, 위치, 숫자)
2) text 왼쪽의 지정된 위치에서 숫자만큼 문자를 가져온다.
3) (예문)
4. (연습문제) 6-5.xlsx
14
고급 함수 이용하기(찾기/참조 함수)
1. CHOOSE 함수
1) 인덱스 번호에 해당하는 값을 표시
2) 형식 : CHOOSE(인덱스 번호, 값1, 값2,…)
3) (예문)
4) (연습문제)6-5.xlsx(주민등록번호)
15
고급 함수 이용하기(찾기/참조 함수)
2. MATCH 함수
1) 형식 : MATCH(검사할 값, 검사범위, 검사유형)
2) 검사범위에서 검사할 값을 찾아 위치 값을 돌려준다.
3) 검사범위는 한 개의 행이나 한 개의 열만 포함한다.
(여러 개의 열이나 행은 안됨)
4) 검사유형 : 0->검사값과 일치, 1->근사값(오름차순), -1 -> 근사값(내림차순)
5) (예문)
16
고급 함수 이용하기(찾기/참조 함수)
3. INDEX 함수
1) 형식 : INDEX(범위, 행번호, 열번호)
2) 범위에서 행번호/열번호로 데이터를 가져온다
3) (예1)
17
고급 함수 이용하기(찾기/참조 함수)
3. INDEX 함수 : INDEX(범위, 행번호, 열번호)
4) (예2)급여가 두 번째로 많은 사람의 행번호 : MATCH(LARGE(J5:J12,2), J5:J12, 0)
18
고급 함수 이용하기(찾기/참조 함수)
4. VLOOKUP 함수
1) 형식 : VLOOKUP(찾을 값, 범위, 열 번호, 검색유형)
2) 범위의 첫 번째 열에서 값을 찾은 후 그 행의 열 번호에서 값을 가져온다.
3) 검색유형 : 0 -> 일치하는 값, 1 -> 비슷한 값
19
고급 함수 이용하기(데이터베이스 함수)
1. 데이터베이스란?
1) 첫 번째 행에 제목이 있는 표
2) 데이터베이스 함수 : 모든 함수는 인수의 형식이 동일하다.
3) 데이터베이스 함수의 조건 :
① 제목이 있는 표가 두 개는 존재해야 함(전체범위의 표, 조건을 위한 표)
② DB의 범위 지정 : 제목이 반드시 포함됨
③ 조건의 범위 : 제목이 반드시 포함됨
④ (예문)
20
고급 함수 이용하기(데이터베이스 함수)
1. DAVERAGE 함수
1) 형식 : DAVERAGE(데이터베이스, 열 이름, 조건)
2) 조건과 일치하는 데이터의 평균을 구함
3) (예문)
21