2과목 스프레드시트 2강 함수

[유동균강사]

수식의 개념

- 수식은 등호(=)나 ‘+’, ‘-’ 기호로 시작한다.

- 문자열이 수식에 사용될 때에는 쌍 따옴표(“”)로 묶어 주어야 한다.

- 수식은 같은 워크시트의 다른 셀이나, 다른 시트에 있는 셀, 다른 통합 문서의 시트에 있는 셀을 참조할 수 있다.

- 수식이 입력된 셀에는 수식의 결과값이 표시되고, 수식은 수식 입력줄에 표시된다.

- CTRL ~를 누르면 입력된 전체 수식을 볼 수 있다.

연산자 우선 순위

우선순위	연산자	설명
1	:(콜론)	범위 연산자
2	,(쉼표)	결합 연산자
3	공백	교점 연산자
4	-	음수
5	%	백분율
6	^	지수
7	×, /	곱하기, 나누기
8	+, -	더하기, 빼기
9	&	문자열 결합
10	=, <, >, <=, >=, <>	비교 연산자

셀 참조

상대 참조	- 수식을 입력한 셀의 위치가 변동되면 참조가 상대적으로 변경됨 - 표기 예) A1
절대 참조	- 수식을 입력한 셀의 위치와 관계없이 고정된 주소로, 참조가 변경되지 않음 - 표기 예) $A$1
혼합 참조	- 열 고정 혼합 참조 : 열만 절대 참조가 적용됨($A1) - 행 고정 혼합 참조 : 행만 절대 참조가 적용됨(A$1)
다른 워크시트 의 셀 참조	- 시트 이름과 셀 주소 사이를 느낌표(!)로 구분 - 표기 에) =Sheet!A5 - 시트 이름에 공백이 있는 경우 따옴표(‘’)로 묶음
다른 통합 문서 의 셀 참조	- 통합 문서의 이름을 대괄호([ ])로 묶어 표기 - 표기 예) =[매출현황]Sheet4!B6

오류 메시지

#####	셀 너비보다 큰 숫자, 날짜, 또는 시간이 있거나, 계산 결과가 음수인 날짜와 시간이 있을 때
#DIV/0!	나누는 수가 빈 셀이나 0이 있는 셀을 참조한 때(피연산자가 빈 셀이면 0으로 간주됨)
#N/A	함수나 수식에 사용할 수 없는 값을 지정했을 때
#Name?	인식할 수 없는 텍스트를 수식에 사용했을 때
#NULL!	교차하지 않는 두 영역의 교점을 지정하였을 때
#NUM!	표현할 수 있는 숫자의 범위를 벗어났을 때
#REF!	셀 참조가 유효하지 않을 때
#VALUE	잘못된 인수나 피연산자를 사용하거나 수식 자동 고침 기능으로 수식을 고칠 수 없을 때

배열 수식

- 배열 수식은 배열 인수라는 두 개 이상의 값에 의해 이루어진다.

- 배열 수식에 사용되는 배열 인수 각각은 동일한 개수의 행과 열을 가져야 한다.

- 배열 수식은 수식을 입력할 때 CTRL SHIFT ENTER를 누르는 것 외에는 다른 수식을 만들 때와 같다.

- CTRL SHIFT ENTER를 누르면 수식의 앞뒤에 중괄호({})가 자동으로 입력된다.

배열 상수

- 배열 수식에 사용되는 배열 인수를 배열 상수라고 하며, 배열 상수로는 숫자, 텍스트, TRUE나 FALSE 등의 논리 값, #N/A와 같은 오류 값을 사용할 수 있다.

- 배열 상수에 정수, 실수, 5E+3 같은 지수형 숫자를 사용할 수 있다.

- $, 괄호, %, 길이가 다른 행이나 열, 셀 참조는 배열 상수로 사용될 수 없다.

- 배열 상수 값은 수식이 아닌 상수이어야 한다.

- 배열 상수를 입력할 때 열의 구분은 쉼표(,)로 행의 구분은 세미콜론(;)으로 한다.

통계 함수

함수	설명
AVERAGE(인수1, 인수2, …)	인수중 평균 값
MAX(인수1, 인수2, …)	인수 중 가장 큰 값
MIN(인수1, 인수2, …)	인수 중 가장 작은 값
COUNT(인수1, 인수2, …)	인수 중 숫자가 들어있는 셀의 개수
COUNTA(인수1, 인수2, …)	인수 중 비어있지 않은 셀의 개수
COUNTBLANK(인수1, 인수2, …)	인수 중 비어 있는 셀의 개수
COUNTIF(범위, 조건)	지정된 범위에서 조건에 맞는 셀의 개수
LARGE(범위, n번째)	범위 중 n번째로 큰 값
SMALL(범위, n번째)	범위 중 n번째로 작은 값
RANK(인수, 범위, 논리값)	- 지정된 범위 안에서의 인수의 순위 - 논리값이 0이거나 생략되면 내림차순, 0 이외의 값은 오름차순
VAR(인수1, 인수2, …)	인수의 분산
STDEV(인수1, 인수2, …)	인수의 표준 편차
MEDIAN(인수1, 인수2, …)	인수들의 중간 값
MODE(인수1, 인수2, …)	인수 중 가장 많이 발생한 값
FREQUENCY(배열1, 배열2)	배열2의 범위에 대한 배열1 요소들의 빈도수 계산
GEOMEAN(인수1, 인수2, …)	인수의 기하평균
HARMEAN(인수1, 인수2, …)	인수의 조화평균
PERCENTILE(범위, 인수)	범위에서 인수 번째 백분위수 값

수학/삼각 함수

함수	설명
SUM(인수1, 인수2, …)	인수의 합계
SUMIF(조건 범위, 조건, 합계 범위)	조건에 맞는 셀들의 합계
ROUND(인수, 자릿수)	지정한 자릿수로 반올림
ROUNDUP(인수, 자릿수)	지정한 자릿수로 올림
ROUNDDOWN(인수, 자릿수)	지정한 자릿수로 내림
ABS(인수)	인수의 절대값
INT(인수)	인수보다 크지 않은 정수를 구함
RAND( )	0과 1 사이의 난수를 발생
MOD(인수1, 인수2)	인수1을 인수2로 나눈 나머지
FACT(인수)	인수의 계승값
SQRT인수)	인수의 양의 제곱근, 인수가 음수이면 에러
PI( )	수치 상수 파이(π)를 15자리(3.14159265358979)까지 나타냄
EXP(인수)	e를 인수만큼 거듭제곱한 값
MDETERM(배열)	배열의 행렬식을 구함
MINVERSE(배열)	배열의 역행렬을 구함
MMULT(배열1, 배열2)	배열1과 배열2의 행렬 곱을 계산함
SUMPRODUCT(배열1, 배열2, …)	배열에서 대응하는 요소를 곱하고 그 곱의 합을 구함
POWER(인수, 제곱값)	인수의 거듭 제곱값
TRUNC(인수, 자릿수)	소수점 이하를 버리고 인수를 정수로 구함
QUOTIENT(인수1, 인수2, …)	인수1을 인수2로 나눈 몫을 구함
RANDBETWEEN(인수1, 인수2)	지정한 두 수 사이의 난수를 구함
PRODUCT(인수1, 인수2, …)	인수를 모두 곱한 값을 구함

텍스트 함수

함수	설명
LEFT(텍스트, 개수)	텍스트의 왼쪽부터 지정한 개수만큼 표시
MID(텍스트, 시작 위치, 개수)	텍스트의 시작 위치부터 지정한 개수만큼 표시
RIGHT(텍스트, 개수)	텍스트의 오른쪽부터 지정한 개수만큼 표시
LOWER(텍스트)	텍스트를 모두 소문자로 표시
UPPER(텍스트)	텍스트를 모두 대문자로 표시
PROPER(텍스트)	텍스트의 첫 글자만 대문자로 표시
TRIM(텍스트)	텍스트의 양쪽 공백 제거
REPLACE(텍스트1, 시작 위치, 개수, 텍스트2)	텍스트1의 시작 위치에서 개수로 지정된 문자를 텍스트2로 변경
SUBSTITUTE(텍스트2, 인수1, 인수2)	텍스트에서 인수1을 인수2로 변경
LEN(텍스트)	문자의 개수를 구함
TEXT(인수, 형식)	인수를 지정된 형식의 텍스트로 바꿈
FIXED(인수, 자릿수, 논리값)	인수를 반올림하여 지정된 자릿수까지 텍스트로 표시
CONCATENATE(텍스트1, 텍스트2, …)	여러 텍스트 항목을 한 텍스트로 합침
VALUE(텍스트)	텍스트를 숫자로 변환

날짜/시간 함수

함수	설명
YEAR(날짜)	날짜에서 연도만 표시
MONTH(날짜)	날짜에서 월만 표시
DAY(날짜)	날짜에서 일만 표시
HOUR(시간)	시간에서 시간마 표시
MINUTE(시간)	시간에서 분만 표시
SECOND(시간)	시간에서 초만 표시
WEEKDAY(날짜)	- 날짜에 해당하는 요일 번호를 표시 - 요일은 1(일요일)～7(토요일)까지의 정수로 표시
DAYS360(날짜, 날짜)	1년을 12달, 360일로 하여 두 날짜 사이의 일수 계산
DATE(연, 월, 일)	연, 월, 일에 대한 일련번호를 구함(기준 1900년 1월 1일)
TIME(시, 분, 초)	지정한 시간에 대한 일련번호를 구함(0.0～0.999)
TODAY(  )	현재 날짜 표시
NOW(  )	현재 날짜와 시간 표시
DATEVAL‎UE(날짜)	텍스트로 표시된 날짜의 일련번호를 구함

논리 함수

함수	설명
IF(조건, 인수1, 인수2)	조건을 비교하여 참이면 인수1, 거짓이면 인수2를 실행
NOT(인수)	인수에 대한 논리값의 반대값 표시
AND(인수1, 인수2, …)	인수가 모두 참이면 참
OR(인수1, 인수2, …)	인수 중 하나라도 참이면 참
FALSE( )	논리값 FALSE를 표시
TRUE( )	논리값 TRUE를 표시

찾기/참조 함수

- VLOOKUP(기준값, 범위, 열 번호, 옵션)

  범위의 첫 번째 열에서 기준 값과 같은 데이터를 찾은 후, 기준값이 있는 행의 지정된 열 번호 위치에 있는 데이터 표시

- HLOOKUP(기준값, 범위, 행 번호, 옵션)

  범위의 첫 번째 행에서 기준값과같은 데이터를 찾은 후, 기준값이 있는 열의 지정된 행 번호 위치에 있는 데이터 표시

- CHOOSE(인수, 첫 번째, 두 번째, …)

  인수로 정해진 번째의 데이터 표시

- INDEX(범위, 행 번호, 열 번호)

  지정된 범위에서 행 번호와 열 번호에 있는 데이터 표시

- LOOKUP(기준값, 범위)

  범위로 첫째 행 또는 열에서 지정한 값을 찾아, 범위의 마지막 행이나 열의 같은 위치에 있는 값을 입력

- MATCH(기준값, 범위, 옵션)

  옵션으로 지정된 방법으로 지정된 범위에서 기준값과 같은 데이터를 찾아 상대 위치를 표시

- OFFSET(범위, 행, 열, 높이, 너비)

  선택한 범위에서 지정한 행과 열만큼 떨어진 위치에 있는 데이터 영역의 데이터를 표시

- COLUMN(셀)

  주어진 셀의 열 번호를 구함

- COLUMNS(셀 범위)

  주어진 셀 범위의 열 개수를 구함

- ROW(셀)

  주어진 셀의 행 번호를 구함

- ROWS(셀 범위)

  주어진 셀 범위의 행 개수를 구함

- TRANSPOSE(범위)

  범위에 입력된 값을 행/열을 바꾸어 현재 셀 범위에 표시

데이터베이스 함수

- DSUM(범위, 열 번호, 조건)

  해당 범위에서 조건에 맞는 자료를 대상으로 지정된 열에서 합계를 계산한다.

- DAVERAGE(범위, 열 번호, 조건)

  해당 범위에서 조건에 맞는 자료를 대상으로 지정된 열에서 평균을 계산한다.

- DCOUNT(범위, 열 번호, 조건)

  해당 범위에서 조건에 맞는 자료를 대상으로 지정된 열에서 수치가 있는 셀의 개수를 계산한다.

- DCOUNTA(범위, 열 번호, 조건)

  해당 범위에서 조건에 맞는 자료를 대상으로 지정된 열에서 비어 있지 않은 셀의 개수를 계산한다.

- DMAX(범위, 열 번호, 조건)

  해당 범위에서 조건에 맞는 자료를 대상으로 지정된 열에서 가장 큰 값을 찾는다.

- DMIN(범위, 열 번호, 조건)

  해당 범위에서조건에 맞는 자료를 대상으로 지정된 열에서 가장 작은 값을 찾는다.

- DVAR(범위, 열 번호, 조건)

  해당 범위의 열에서 조건과 일치하는 값들의 분산을 구한다.

- DSTDEV(범위, 열 번호, 조건)

  해당 범위의 열에서 조건과 일치하는 값들의 표준편차를 구한다.

- DGET(범위, 열 번호, 조건)

  해당 범위의 열에서 조건과 일치하는 값들의 단일 값을 구한다.

- DPRODUCT(범위, 열 번호, 조건)

  해당 범위의 열에서 조건과 일치하는 값들의 곱을 구한다.

재무 함수

- FV(이자, 기간, 금액, 납입시점)

  미래 가치를 구함(매월 일정한 금액을 불입하였을 경우 만기일에 받을 원금과 이자 계산)

- PV(이자, 기간, 금액, 미래가치, 납입시점)

  현재 가치를 구함(예 : 1년 뒤에 받을 1000원의 현재 가치를 구함)

- NPV(할인율, 금액1, 금액2, …)

  할인율과 앞으로의 지출과 수입을 사용하여 투자의 현재 가치를 계산

- PMT(이자, 기간, 현재가치, 미래가치, 납입시점)

  정기적으로 상환(지급)할 금액을 구함(일정 금액을 대출받았을 경우 이자를 포함하여 매월 상환해야 하는 금액 계산)