최근 수정 시각 : 2022-06-10 13:56:00

Microsoft Excel/함수 목록

파일:상위 문서 아이콘.svg   상위 문서: Microsoft Excel
1. 개요2. 함수
2.1. 수학 함수2.2. 통계학 함수
2.2.1. 추론 통계학 함수2.2.2. 엑셀이나 Calc에서 통계 데이터 분석 도구 사용
2.3. 재무 함수2.4. 날짜/시간 함수2.5. 텍스트/정보 함수2.6. 데이터베이스2.7. 논리/찾기 함수
3. 사용자 정의 함수
3.1. 간단한 제작 방법(If문)3.2. 간단한 제작 방법(select case문)
4. 배열 수식
4.1. 행렬 연산

1. 개요

Microsoft Excel의 함수 목록과 사용법을 간단하게 정리한 문서이다. 전체 함수의 목록은 마이크로소프트 웹사이트 Excel 함수(사전순)을 참조하였다.

2. 함수

모든 함수를 입력할 때는 =함수명(입력값)으로 입력해야 한다. NOW 함수같이 입력값이 없는 함수도 있지만 #NAME? 오류를 뿜어내므로 괄호까지 모두 입력해야 한다.

2010 버전에서 VAR.S나 STDEV.S 등 많은 통계학 함수가 추가되었고, 2013에서도 SKEW.P 등 몇몇 새로운 통계학 함수들이 추가되었다. 2016부터는 매크로 바이러스로 인해 2010 버전 이전에 쓰던 호환 함수들을 지우기 시작했다. 이 함수들은 실행은 가능하지만 저장하려면 2016 버전에서 지원하는 대체함수로 변경해야 저장된다. 2019에서는 더 많은 하위호환 함수들이 빠졌다.

대부분의 함수들은 리브레오피스, Calc, Google Sheets에서도 그대로 호환된다.

2.1. 수학 함수

INT(number(인수))[1] 인수의 소숫점 아래를 버리고 가장 가까운 정수로 내림한다. C언어 등의 floor 함수와 같다. 수학적으로는 최대 정수 함수[2]가 있다.
PRODUCT((number1(인수1), … number254(인수254)) 인수들의 곱을 구한다.
ROUND(number(인수), num_digits(반올림할 자릿수)) 인수를 자릿수 만큼 반올림한다. 0을 입력하면 소숫점 없이 반올림이 된다. 소숫점을 지정할 때에는 num_digits 값이 양수, 백의 자리나 천의 자리 등을 지정할 때에는 음수여야 한다. 특히 컴활 등의 시험문제에서 "십의 자리까지" 와 "십의 자리에서" 는 서로 다른 표현. 전자는 -1, 후자는 -2 를 입력해주자.
이 함수는 일단 인수를 절대값으로 바꿔서 반올림한 뒤 원래 인수가 음수면 그 결과값을 음수로 변환해서 돌려준다. 아래의 ROUNDDOWN과 ROUNDUP도 동일.
ROUNDDOWN(number(인수), num_digits(내림할 자릿수)) 인수를 자릿수 만큼 내림한다.
ROUNDUP(number(인수), num_digits(올림할 자릿수)) 인수를 자릿수 만큼 올림한다.
SUM(number1(인수1), … number254(인수254)) 인수들의 합을 구한다.
SUMIF(Range(참조할 범위), Criteria(조건), sum_range(합을 구할 범위) 참조된 범위 중에서 조건에 맞는 것만 합을 구할 범위에서 찾아서 합을 구하는 함수이다.[3]
SUMPRODUCT(array1(곱할 배열), array2(곱할 배열), …) 배열을 n차원 벡터로 취급하고 내적한다. 예를 들어 (a×b)+(c×d)+(e×f) 일 때, array1 은 a, c, e 가 되고 array2 는 b, d, f 가 된다. 이는 벡터의 내적과 연산법이 같다. 2003 버전까지는 위의 SUMIFS 함수가 포함되지 않았기 때문에, SUMIFS의 기능을 이용하기 위해서는 해당 함수를 이용할 수 있다.

2.2. 통계학 함수

AVERAGE(인수1, 인수2, … 인수254) 인수의 산술 평균값을 구한다. 예전엔 인수의 개수가 30개까지였지만 지금은 254개로 바뀌었다.
MEDIAN(인수1, 인수2, … 인수254) 인수들의 중앙값을 구한다. 셀의 숫자가 24, 12, 57인 경우 중앙값은 12가 아니라 크기 순으로 정렬한 후의 중간 숫자인 24이다.
MAX(인수1, 인수2, … 인수254) 인수들 중에서 최대값을 구한다. 단, 논리값과 텍스트는 제외한다.
MIN(인수1, 인수2, … 인수254) 인수들 중에서 최소값을 구한다. 단, 논리값과 텍스트는 제외한다.
COUNT(인수1, 인수2, … 인수254) 인수들 중에서 숫자가 있는 인수를 세는 함수이다.
COUNTIF(찾으려는 위치, 찾으려는 항목) 통계 함수 중 하나인 COUNTIF를 사용하여 기준을 충족하는 셀의 개수를 계산할 수 있다(예: 고객 목록에 특정 도시가 표시되는 횟수 계산). 예1) =COUNTIF(A2:A5,"런던") 예2) =COUNTIF(A2:A5,A4) 참조
LARGE(범위, K번째) 범위에서 K번째로 큰 값을 구한다.
SMALL(범위, K번째) 범위에서 K번째로 작은 값을 구한다.
RANK(인수, 범위, 논리값) 범위 내에서 지정한 수의 순위를 구한다. 논리값이 0이거나 생략되면 내림차순으로, 0이외의 값은 오름차순으로 표시
[[파일:]

2.2.1. 추론 통계학 함수

VAR.S(인수1, 인수2, … 인수254) 표본 분산(sample variance)은 VAR.S 함수로 구한다. 예전엔 VAR 함수를 썼다.
VAR.P(인수1, 인수2, … 인수254) 모 분산(population variance)을 구한다. 예전엔 VARP 함수를 썼다. VAR 함수와 VARP 함수는 아직은 지원되지만 이후 버전에서는 지원이 종료될 수 있다.
STDEV.S(인수1, 인수2, … 인수254) 표본 표준 편차(sample standard deviation)는 STDEV.S 함수로 구한다. 이전 버전까지는 STDEV 함수를 썼다.
STDEV.P(인수1, 인수2, … 인수254) 모 표준 편차(population standard deviation)는 STDEV.P 함수로 구한다. 예전엔 STDEVP 함수를 썼다. 아직까지는 STDEV 함수와 STDEVP 함수도 지원되지만 이후 버전에서는 지원이 중단될 수 있다.
NORM.DIST(x,mean,standard_dev,cumulative) 지정된 평균과 표준 편차를 갖는 정규 분포값을 반환합니다. 이 함수는 가설 검정 등 통계의 광범위한 영역에서 응용됩니다. x는 분포를 구하려는 값입니다. mean은 분포의 산술 평균입니다. standard_dev는 분포의 표준 편차입니다. Cumulative는 함수의 형태를 결정하는 논리값으로서, cumulative가 TRUE이면 NORM.DIST에서 누적 분포 함수가 반환되고 FALSE이면 확률 질량 함수가 반환됩니다.
CONFIDENCE.NORM(alpha,standard_dev,size) 정규 분포를 사용하여 모집단 평균의 신뢰 구간을 반환합니다. 신뢰 구간은 값의 범위입니다. 표본 평균 x는 이 범위의 중심에 있으며 범위는 x ± CONFIDENCE.NORM입니다. 예를 들어 x가 우편을 통해 주문한 제품 배달 시간의 표본 평균인 경우 모집단의 평균 범위는 x ± CONFIDENCE.NORM입니다. 이 범위에 속하는 임의의 모집단 평균 μ0에 대해 μ0를 기준으로 x보다 먼 표본 평균을 얻을 확률은 alpha보다 큽니다. 이 범위에서 벗어난 임의의 모집단 평균 μ0에 대해 μ0를 기준으로 x보다 먼 표본 평균을 얻을 확률은 alpha보다 작습니다. 즉, 모집단 평균이 μ0이라는 가설의 유의 수준 alpha를 적용하여 x, standard_dev, size를 사용하여 양측 검정을 만든다고 가정합니다. 그러면 μ0이 신뢰 구간에 포함된 경우 해당 가설이 기각되지 않으며, μ0이 신뢰 구간에 포함되지 않은 경우 해당 가설이 기각됩니다. 그러나 신뢰 기간을 바탕으로 다음 번 배달 시간이 신뢰 구간에 포함될 확률이 1 – alpha라고 추론할 수는 없습니다. alpha 유의 수준, standard_dev는 모 표준 편차, size는 표본 크기이다.
STANDARDIZE(Number; Mean; StDev) 확률 변수 정규화된 값으로 변환합니다. Number는 표준화되어야 하는 값입니다. Mean은 분포의 수를 의미합니다. StDev는 분포의 표준 편차입니다.
예) =STANDARDIZE(11;10;1)은 1을 반환합니다. 평균이 10이고 표준 편차가 1인 정규 분포에서 값 11은 10이 의미하는 것보다 높고, 표준 정규 분포에서 값 1이 높은 것과 같습니다.
NORM.S.DIST(z,cumulative) 표준 정규 분포(z-분포)를 반환합니다. 이 분포의 평균은 0이고 표준 편차는 1입니다. 표준 정규 곡선 면적 표 대신 이 함수를 사용합니다. z는 분포를 구하려는 값입니다. cumulative는 cumulative 함수의 형태를 결정하는 논리값으로서, cumulative가 TRUE이면 누적 분포 함수가 반환되고 FALSE이면 확률 질량 함수가 반환됩니다.
Z.TEST(array,x, sigma) z-검정의 단측 검정 p-값을 반환합니다. 가설 모집단 평균 x가 주어진 경우 Z.TEST 함수는 표본 평균이 데이터 집합(배열)의 관측 평균, 즉 관측된 표본 평균보다 클 확률을 반환합니다. array는 필수 요소입니다. x를 검정할 데이터의 배열 또는 범위입니다. x도 필수 요소입니다. 검정할 값입니다. sigma는 선택 요소입니다. 모 표준 편차로서, 이를 생략하면 표본 표준 편차가 사용됩니다.
T.DIST(x,deg_freedom, cumulative) 단측(왼쪽) 스튜던트 t-분포값을 반환합니다. t-분포는 소표본의 데이터를 가설 검정할 때 사용됩니다. t-분포의 임계값 표 대신 이 함수를 사용합니다. x는 분포를 구하려는 숫자 값입니다. deg_freedom은 자유도를 나타내는 정수입니다. Cumulative는 함수의 형태를 결정하는 논리값으로서, cumulative가 TRUE이면 T.DIST에서는 누적 분포 함수가 반환되고 FALSE이면 확률 밀도 함수가 반환됩니다.
T.DIST.2T(x,deg_freedom) 양측 스튜던트 t-분포값을 반환합니다. x는 분포를 구하려는 숫자 값입니다. deg_freedom은 자유도를 나타내는 정수입니다.
T.DIST.RT(x,deg_freedom) 단측(오른쪽) 스튜던트 t-분포값을 반환합니다. x는 분포를 구하려는 숫자 값입니다. deg_freedom은 자유도를 나타내는 정수입니다.
T.TEST(array1,array2,tails,type) 스튜던트 t-검정에 근거한 확률을 반환합니다. T.TEST 함수를 사용하여 두 개의 표본이 같은 평균값을 갖는 두 개의 같은 모집단에서 추출한 것인지를 판단할 수 있습니다. array1은 첫 번째 데이터 집합입니다. array2는 두 번째 데이터 집합입니다. Tails는 분포가 단측인지 또는 양측인지 지정하는 숫자로서 tails = 1이면 T.TEST에서는 단측 분포를, tails = 2이면 양측 분포를 사용합니다. type은 실행할 t-검정의 종류입니다. 타입 1은 쌍을 이루는 것(paired t-test)을 의미합니다. 타입 2는 두 개의 집단이 동일한 분산을 가지고 있는 것을 의미합니다.(등분산 가정 두 집단) 타입 3은 두 개의 집단이 서로 다른 분산을 가진 것을 의미합니다.(이분산 가정 두 집단)
예) =T.TEST(A2:A51,B2:B51,2,3)
CONFIDENCE.T(alpha,standard_dev,size) 스튜던츠 t-분포를 사용하여 모집단 평균의 신뢰 구간을 반환합니다. alpha 유의 수준, standard_dev는 모 표준 편차, size는 표본 크기이다.
CHISQ.DIST(x,deg_freedom,cumulative) 카이-제곱 분포를 반환합니다. 카이 제곱 분포는 하루 중 TV를 보는 시간을 백분율로 나타내는 것처럼 표본에서 특정 부분이 차지하는 백분율의 분포를 조사할 때 일반적으로 사용됩니다. x는 분포를 계산하려는 값입니다. deg_freedom은 자유도를 나타내는 숫자입니다. Cumulative는 함수의 형태를 결정하는 논리값으로서, cumulative가 TRUE이면 CHISQ.DIST에서는 누적 분포 함수가 반환되고 FALSE이면 확률 밀도 함수가 반환됩니다.
CHISQ.DIST.RT(x,deg_freedom) 카이 제곱 분포의 단측(오른쪽) 검정 확률을 반환합니다. χ2 분포는 χ2 검정과 연관됩니다. χ2 검정은 관측값과 기대값을 비교하는 데 사용됩니다. 예를 들어 유전 실험에서 다음 세대의 식물에서 나타날 색에 대한 가설을 세운 다음 관측 결과를 기대값과 비교하여 가설을 검증할 수 있습니다. x는 분포를 계산하려는 값입니다. deg_freedom은 자유도를 나타내는 숫자입니다.
CHISQ.TEST(actual_range,expected_range) 독립 검증 결과를 반환합니다. 즉, CHISQ.TEST에서는 해당 통계 및 적정 자유도에 대한 카이 제곱(χ2) 분포값이 반환됩니다. χ2 검정( 카이-제곱 검정)을 사용하면 실험에 의해 가설이 검증되었는지 확인할 수 있습니다. actual_range는 기대값과 비교하여 검정할 관측값이 포함된 데이터 범위입니다. expected_range는 행 합계와 열 합계를 곱한 값의 총합계에 대한 비율이 들어 있는 데이터 범위입니다.
F.DIST(x,deg_freedom1,deg_freedom2,cumulative) F-분포값을 반환합니다. 이 함수를 사용하면 두 데이터 집합의 분포도가 서로 다른지 확인할 수 있습니다. 예를 들어 고등학교에 입학하는 남녀 학생의 성적을 조사하여 남녀 학생의 분포도가 서로 다른지를 알아볼 수 있습니다. x는 함수를 계산할 값입니다. deg_freedom1은 분자의 자유도입니다. deg_freedom2은 분모의 자유도입니다. Cumulative는 함수의 형태를 결정하는 논리값으로서, cumulative가 TRUE이면 F.DIST에서는 누적 분포 함수가 반환되고 FALSE이면 확률 밀도 함수가 반환됩니다.
F.DIST.RT(x,deg_freedom1,deg_freedom2) 두 데이터 집합에 대한 단측(오른쪽) 검정 F 확률 분포값(분포도)을 반환합니다. x는 함수를 계산할 값입니다. deg_freedom1은 분자의 자유도입니다. deg_freedom2은 분모의 자유도입니다.
F.TEST(array1,array2) array1과 array2의 분산이 크게 다르지 않은 양측 검증 확률인 F-검정의 결과를 반환합니다. 이 함수를 사용하여 두 표본이 다른 분산을 갖는지 확인할 수 있습니다. 예를 들어 공립 학교와 사립 학교의 시험 성적 분포도가 서로 다른지 확인할 수 있습니다. array1은 첫 번째 배열 또는 데이터 영역입니다. array2는 두 번째 배열 또는 데이터 영역입니다.
INTERCEPT(known_y's, known_x's) 기존 x 값과 y 값을 사용하여 한 개의 선이 y 축과 교차하는 지점을 계산합니다. 절편은 known_x's와 known_y's의 값으로 이루어진 가장 적합한 회귀선을 기반으로 합니다. 독립 변수가 0일 때 종속 변수의 값을 확인하려면 INTERCEPT 함수를 사용합니다. 예를 들면 데이터가 상온이나 그 이상의 온도에서 측정된 경우 INTERCEPT 함수를 사용하여 0°C에서의 금속의 전기 저항을 예측할 수 있습니다. known_y's는 관측값이나 데이터의 종속 변수 집합입니다. known_x's는 관측값이나 데이터의 독립 변수 집합입니다.
SLOPE(known_y's, known_x's) known_y's와 known_x's 사이의 데이터 요소에 대한 선형 회귀선 기울기를 반환합니다. 기울기는 선의 두 점 사이의 수직 거리를 수평 거리로 나눈 회귀선의 변화율입니다. known_y's는 종속 데이터 요소의 셀 배열 또는 범위입니다. known_x's는 독립 데이터 요소의 집합입니다.
Example) =SLOPE(A1:A50;B1:B50)
STEYX(known_y's, known_x's) 회귀 분석에서 각각의 x에 대하여 예측한 y 값의 표준 오차를 반환합니다. 표준 오차는 각각의 x 값에 대한 y 예측값의 오차량을 나타냅니다. known_y's는 종속 데이터 요소의 배열이나 범위입니다. known_x's는 독립 데이터 요소의 배열이나 범위입니다.
PERMUT(Count1; Count2) 주어진 숫자에 대한 순열(permutation)을 구한다. 참고로 순열은 뽑는 순서가 있고, 조합(combination)은 뽑는 순서가 없다. Count1은 대상의 전체 숫자이다. Count2는 뽑는 숫자이다.
E1) =PERMUT(6;3)은 120을 반환한다.
E2) =PERMUT(5;2)은 20을 반환한다. 5개의 카드 중 2개의 카드를 뽑는 순서는 20가지의 다른 가능성이 존재한다. 순열 공식은 nPr = n! / (n-r!)이다. 5P2 = 5! / (5-2)! = 5x4x3x2x1 / 3x2x1 = 5x4 = 20이다.
PERMUTATIONA(Count1; Count2) 주어진 숫자에 대한 순열을 구하지만 반복을 허용한다. 즉, 중복 순열을 구한다. Count1은 대상의 전체 숫자이다. Count2는 뽑는 숫자이다.
E1) =PERMUTATIONA(6;3)는 216을 반환한다. 6개의 카드 중 3개의 카드를 뽑는데 다음 카드를 뽑기 전에 이전의 카드를 다시 카드 통에 돌려놓으면 216가지의 다른 경우의 수가 존재한다.
E2) =PERMUTATIONA(11;2)은 121을 반환한다. 11개의 전체 대상 중 얼마나 자주 2개의 대상이 뽑히나?
COMBIN(Count1; Count2) 주어진 숫자에 대한 조합(combination)을 구한다. Count1은 대상의 전체 숫자이다. Count2는 뽑는 숫자이다.
COVARIANCE.P(array1,array2) 두 데이터 집합의 각 데이터 요소 쌍에 대한 편차의 곱의 평균(모집단 공분산)을 반환합니다. 공분산을 사용하면 두 데이터 집합 사이의 관계를 확인할 수 있습니다. 예를 들어 수입이 높을수록 교육 수준이 높은지 여부를 확인할 수 있습니다. array1은 첫 번째 정수 셀 범위입니다. array2는 두 번째 정수 셀 범위입니다.
예) =COVARIANCE.P(A1:A30;B1:B30)
COVARIANCE.S(array1,array2) 두 데이터 집합의 각 데이터 요소 쌍에 대한 편차의 곱의 평균(표본 공분산)을 반환합니다. array1은 첫 번째 정수 셀 범위입니다. array2는 두 번째 정수 셀 범위입니다.
CORREL(Data1; Data2) 두 데이터 세트의 상관 계수(correlation coefficient)를 반환한다. 상관 계수를 사용하면 두 속성 사이의 관계를 확인할 수 있습니다. 예를 들면 어떤 지역의 평균 기온과 에어콘 사용 사이의 상관 관계를 알아볼 수 있습니다. Data1 is the first data set. Data2 is the second data set.
Example) =CORREL(A1:A50;B1:B50) calculates the correlation coefficient as a measure of the linear correlation of the two data sets.
PROB(x_range, prob_range, [lower_limit], [upper_limit]) 영역 내의 값이 두 한계값 사이에 있을 확률을 반환합니다. upper_limit가 정의되지 않으면 x_range의 값이 lower_limit와 같을 확률이 반환됩니다. x_range는 필수 요소입니다. 확률과 관련된 숫자 x 값의 범위입니다. prob_range도 필수 요소입니다. x_range의 값과 관련된 확률의 집합입니다. lower_limit는 선택 요소입니다. 확률을 계산할 범위의 하한값입니다. upper_limit도 선택 요소입니다. 확률을 계산할 범위의 상한값입니다.
KURT(Number1; Number2; ...Number30) 데이터 세트의 첨도(kurtosis)를 반환한다. 최소한 4개의 값이 필요하다. Number1,Number2,...Number30 are numeric arguments or ranges representing a random sample of distribution.
Example) =KURT(A1;A2;A3;A4;A5;A6)
SKEW(Number1; Number2; ...Number30) 표본(sample) 분포의 왜도(skewness, 왜곡도)를 구한다. 왜곡도란 평균에 대한 분포의 비대칭 정도를 나타냅니다. 왜곡도가 양수이면 분포의 비대칭 꼬리가 양의 값 쪽으로 치우치며, 왜곡도가 음수이면 음의 값 쪽으로 치우칩니다.
Example) =SKEW(A1:A50)
SKEW.P(number 1, [number 2],…) 모집단을 기준으로 분포의 왜도를 반환합니다. 왜곡도란 평균에 대한 분포의 비대칭 정도를 나타냅니다. number 1, number 2,…는 모집단 왜곡도를 구하려는 1~254개의 숫자이거나 숫자가 들어 있는 이름, 배열 또는 참조입니다. MS Excel에서는 skew.p 함수를 쓰고, LibreOffice Calc에서는 SKEWP 함수를 쓴다.
E1)SKEWP(2;3;1;6;8;5) returns 0.2828158928
E2) SKEWP(A1:A6) returns 0.2828158928, when the range A1:A6 contains {2;3;1;6;8;5}
E3) SKEWP(Number1;Number2) returns zero always, if Number1 and Number2 results in two numbers.
E4) SKEWP(Number1) returns Err:502 (Invalid argument) if Number1 results in one number, because SKEWP cannot be calculated with one value.

2.2.2. 엑셀이나 Calc에서 통계 데이터 분석 도구 사용

엑셀 2016에선 "파일 → 옵션 → 리본 사용자 지정 → 개발 도구" 하면 화면에 개발 도구가 생긴다. 그리고서 "개발 도구 → Excel 추가 기능 → 분석 도구"를 선택한다.(2010 버전에선 "Excel 추가 기능" 대신 "추가 기능") 그러면 "데이터"에 "데이터 분석"이 생긴다. 그리고 "데이터 분석"에서 z-검정, t-검정, F-검정, 분산 분석, 회귀 분석 등을 한다. 변수들의 입력 범위와 출력할 위치를 지정해주면 된다.

리브레오피스 6의 Calc는 "데이터 → 통계"에서 z-test, Paired t-test, 카이 제곱 테스트, F-test, 분산 분석 (ANOVA), 회귀 등을 고르면 된다.

변수 1 범위는 a2:a51처럼 적어주면 되고, 변수 2 범위는 b2:b51처럼 적어주면 된다. 결과는 d1이나 h1처럼 적어주면 된다.

엑셀로 통계 분석하는 방법

2.3. 재무 함수

FV(rate(이자율), nper(전체연도), pmt(저축액), pv(현재가치), type(지급시기)[4]) 이율에 따르는 만기저축액을 계산한다. 모든 결과값을 양수로 표시하려면 -pmt 처리를 해야 하며, 사용 시 연이율/월이율, 월저축액/연저축액 통일을 하는 것을 잊지 말 것. pv 및 type 는 생략 가능하다.
PV(rate(이자율), nper(전체연도), pmt(저축액), fv(미래가치), type(지급시기)) 이율에 따르는 할부금액을 계산한다. 모든 결과값을 양수로 표시하려면 -pmt 처리를 해야 하며, 사용 시 연이율/월이율, 월저축액/연저축액 통일을 하는 것을 잊지 말 것. pv 및 type 는 생략 가능하다.
PMT(rate(이자율), nper(전체연도), pv(현재가치), fv(미래가치), type(지급시기)) 이율에 따르는 대출원리금을 계산한다. 모든 결과값을 양수로 표시하려면 -pv 처리를 해야 하며, 사용 시 연이율/월이율, 월저축액/연저축액 통일을 하는 것을 잊지 말 것. fv 및 type 는 생략 가능하다.

2.4. 날짜/시간 함수

DATE(year(연), month(월), day(일)) 입력된 연, 월, 일의 날짜를 나타낸다.
DAY(serial_number(인수)) 인수에서 날짜를 추출해 낸다.[5]
HOUR(serial_number(인수)) 인수에서 시간을 추출해 낸다.
MINUTE((serial_number(인수)) 인수에서 분을 추출해 낸다.
MONTH((serial_number(인수)) 인수에서 월을 추출해 낸다.
NOW()[A] 컴퓨터에 설정된 현재 년, 월, 일, 시, 분, 초를 표시한다.
SECOND((serial_number(인수)) 인수에서 초를 추출해 낸다.
TIME(hour(시), minute(분), second(초)) 입력된 시, 분, 초를 나타낸다.
TODAY()[A] 현재 날짜를 표시한다. 다른 함수와 중첩해서 쓸 게 아니라 그저 현재날짜만을 표시해야 한다면 번거롭게 이 함수 쓰지 말고 간단히 Ctrl+ ;을 이용하면 된다.
WEEKDAY(serial_number(인수), return_type[8](유형을 결정하는 숫자)) 일정 날짜의 요일을 숫자로 출력한다. CHOOSE 함수와 함께 결합해서 사용하기도 한다.
YEAR(serial_number(인수)) 인수에서 연도를 추출해 낸다.
Factor 목록[9]
* d, m, y 각각 두 날짜 사이의 일(day), 개월(month), 햇수(year)를 센다.

2.5. 텍스트/정보 함수[10]

CONCATENATE(텍스트1, 텍스트2, ...) 여러 셀에 나뉘어진 텍스트를 한번에 묶어준다. 보통 =A1&B1&C1 ... 의 방식으로 & 를 사용하는 방법도 있지만, 이 함수를 사용하면 나중에 대상 텍스트를 수정할 때 편리하다. =CONCATENATE(A1,B1,C1)
FIND(텍스트, 셀, 숫자) 셀에 특정 텍스트의 위치를 숫자로 표현한다.
IFERROR(Value(계산), Value_if_error(오류일 시 산출값)) 주어진 계산이 오류인지 확인하여, 오류 시 산출할 값을 지정한다. 특히 엑셀의 오류 표기들(#DIV/0! 등)을 깔끔하게 정리할 수 있다. 해당 함수가 없었던 2003 버젼까지는 해당 기능을 이용하기 위해서는 IF와 ISERROR 를 조합하여 사용할 수 밖에 없었다.
2003식으로 정리하면 IF(ISERROR(계산)=TRUE(), Value_if_error(오류일 시 산출값), 계산) 순으로 사용해야 했다.
IFNA 함수는 2013 버전부터 적용되고, N/A오류만 확인/산출 가능하다.
ISBLANK(Value(셀)) 지정한 셀이 비어 있는 셀일 경우 "TRUE" 를, 뭔가가 입력되어 있을 경우 "FALSE" 를 반환한다. 스페이스바 입력도 탐지한다.
ISERROR(Value(셀)) 지정한 셀이 오류가 있는 경우 "TRUE" 를, 오류가 없는 경우 "FALSE" 를 반환한다. 오류 표시 대신 산출할 값을 지정하는 IFERROR 와는 다르므로 주의.[11]
ISERR(Value) N/A 오류를 제외한 모든 오류에 대해 위 함수와 같은 기능 제공
LEFT(text(텍스트), num_chars(왼쪽 n개)) 주어진 텍스트의 왼쪽에서부터 n개까지의 글자를 추출해 표시한다. 결과값은 문자로 취급된다. 이게 싫다면 VALUE 함수와 함께 사용해야 한다.
LEN(text(텍스트)) 문자열의 길이를 숫자로 추출한다.
LOWER(text(텍스트)) 모든 대문자를 소문자로 치환한다.
MID(text(텍스트), start_num(n번째 글자부터), num_chars(x개까지)) 문자열의 가운데 부분을 추출해 표시한다. n번째 글자부터 x개만큼을 추출한다. 결과값은 문자로 취급된다. 이게 싫다면 VALUE 함수와 함께 사용해야 한다.
PROPER(text(텍스트)) 문자열의 각 어절마다 첫째 글자만을 대문자로 변경하고 나머지는 소문자로 변경한다.
RIGHT(text(텍스트), num_chars(오른쪽 n개)) 주어진 텍스트의 오른쪽에서부터 n개까지의 글자를 추출해 표시한다. 결과값은 문자로 취급된다. 이게 싫다면 VALUE 함수와 함께 사용해야 한다.
SUBSTITUTE(text(텍스트), old_text(대상 텍스트), new_text(바꿀 텍스트), instance_num(n번째 텍스트에 적용)) 쉽게 말해 광역 치환 함수. 대상이 되는 텍스트를 별도로 지정한 새 텍스트로 바꾼다. 한 셀 내에 대상 텍스트가 여러 개가 있는 상황에서는 instance_num 를 활용할 수 있는데, 모두 바꾸려면 생략하고, 숫자를 입력하면 그만큼 왼쪽에서부터 대상 텍스트를 세어서 선택적으로 적용한다.[12]
TEXT(value(셀), format_text(서식)) 주어진 셀의 내용을 서식에 맞게 적용한다.
TRIM(셀) 해당 셀의 텍스트 양 끝에 있는 공백을 제외한 텍스트를 추출한다.[13]
UPPER(text(텍스트)) 모든 소문자를 대문자로 치환한다.
VALUE(text(텍스트)) 시간 등의 문자열을 숫자로 치환한 값을 추출한다.

2.6. 데이터베이스

DAVERAGE(database(전체 DB), field(DB 내 찾을 열번호), criteria(기준)[14]) 전체 데이터베이스 내에서 기준에 맞는 자료의 평균을 구한다.
DCOUNT(database(전체 DB), field(DB 내 찾을 열번호), criteria(기준)) 전체 데이터베이스 내에서 기준에 맞는 숫자 포함 셀들의 개수를 구한다.
DCOUNTA(database(전체 DB), field(DB 내 찾을 열번호), criteria(기준)) 전체 데이터베이스 내에서 비어있는 셀은 무시하고 기준에 맞는 셀들의 개수를 구한다.
DGET(database(전체 DB), field(DB 내 찾을 열번호), criteria(기준)) 전체 데이터베이스 내에서 기준에 맞는 자료를 단순 추출한다.
DSUM(database(전체 DB), field(DB 내 찾을 열번호), criteria(기준)) 전체 데이터베이스 내에서 기준에 맞는 자료를 합산한다.

2.7. 논리/찾기 함수

AND(논리1, 논리2, …) 인수로 포함된 모든 논리가 전부 참일 경우 "TRUE" 를 출력하고, 하나라도 거짓이 있을 경우 "FALSE" 를 출력한다. IF 함수와 함께 쓰면 TRUE/FALSE 표시 대신 다양한 작업을 할 수 있다.
CHOOSE(Index_num(1 이상 254이하의 정수형), 값1, 값2, ...) 값[Index_num]를 반환한다. 값의 자료형은 제한이 없지만 Index_num의 값은 1~254로 한정되어 있다는 것에 주의. ITQ 엑셀 함수 문제의 출제 빈도가 높다.
COLUMN(셀) 해당 셀의 가로열의 번호를 표시하는 함수. COLUMN() 의 경우, 현재 수식이 있는 셀의 열의 번호가 표시된다. 계산식에 ROW 와 COLUMN 함수를 적절히 사용하면, 각 셀의 주소에 따라 서로 다른 범위의 계산을 편리하게 할 수 있다.
HLOOKUP(찾을 값, 참조할 표, 출력할 행번호, range_lookup[15]) 수평 방향으로 참조하는 함수. 주어진 표 외에 다른 작은 표가 하나 더 주어진다. 찾을 값을 참조할 표에서 찾아서, 참조할 표로 지정한 범위 내 출력할 자료가 들어있는 행에서 대응하는 것을 골라 출력한다. VLOOKUP 함수와 다른 점은, 참조할 표가 수평 방향으로 작성되어 있을 때 쓴다는 점이다.
IF(논리검사, 참일시 출력, 거짓일시 출력) 논리검사에 들어가는 등식/수식이 참/0이 아닌 경우에는 참일시 출력에 지정한 것을, 거짓/0일경우에는 거짓일시 출력에 지정한 것을 출력. 출력에도 함수가 들어갈 수 있으며, 함수가 아닌 문자가 들어갈 경우엔 프로그래밍상의 이유때문에 큰따옴표로 감싸줘야 한다.
INDEX(배열, 행위치, 열위치) 어떤 배열 내의 몇 행 몇 열에 해당하는 대상을 찾아서 출력한다. MATCH 함수와 함께 엮어서 쓰기도 한다.
INDIRECT(텍스트) 텍스트로 이루어진 수식 혹은 셀의 주소를 실제 값으로 변형시켜 준다. 일반적으로 ADDRESS 함수를 이용하여 텍스트 혹은 수열로 구성된 셀의 주소를 실제 식으로 변환하여 사용할 때 자주 쓰인다.
LOOKUP(찾을 값, 참조할 표, result_vector(출력값이 포함된 배열)) 주어진 표 외에 다른 작은 표가 하나 더 주어진다. 참조할 표가 수직방향인 VLOOKUP, 수평방향인 HLOOKUP 함수와의 차이점은, 참조할 표의 행과 열의 형태에 제약을 받지 않는다는 점이다.
MATCH(찾을 값, 배열, match_type[16]) 배열 내에서 찾고자 하는 값이 몇 번째에 위치하는지 찾아서 숫자로 출력한다. INDEX 함수와 함께 엮어서 쓰기도 한다.
OFFSET(참조, 숫자, 숫자, 숫자, 숫자) 하나의 셀을 기준으로 지정된 숫자의 범위의 셀(혹은 범위) 의 값을 찾는다. 하나의 기준 셀을 지정하여, 각 열과 행의 거리를 지정하여 값 혹은 다른 함수의 범위로 지정할 수 있어, [17] 동일한 패턴의 양식에 반복적으로 다른 범위를 지정하는 셀의 주소를 입력하는 노가다를 줄여줄 수 있다.
OR(논리1, 논리2, …) 인수로 포함된 논리 중에 하나라도 참이 있을 경우 "TRUE" 를 출력하고, 전부 거짓일 경우 "FALSE" 를 출력한다. IF 함수와 함께 쓰면 TRUE/FALSE 표시 대신 다양한 작업을 할 수 있다.
ROW(셀) 해당 셀의 세로행의 번호를 표시하는 함수. ROW() 의 경우, 현재 수식이 있는 셀의 행의 번호가 표시된다. 계산식에 ROW 와 COLUMN 함수를 적절히 사용하면, 각 셀의 주소에 따라 서로 다른 범위의 계산을 편리하게 할 수 있다.
VLOOKUP(찾을 값, 참조할 표, 출력할 열번호, range_lookup) 수직 방향으로 참조하는 함수. 주어진 표 외에 다른 작은 표가 하나 더 주어진다. 찾을 값을 참조할 표에서 찾아서, 참조할 표로 지정한 범위 내 출력할 자료가 들어있는 열에서 대응하는 것을 골라 출력한다. HLOOKUP 함수와 다른 점은, 참조할 표가 수직 방향으로 작성되어 있을 때 쓴다는 점이다.
XLOOKUP(찾을 값, 참조 범위, 출력 범위, 일치 유형[18] , 검색 방식[19]) 참조 범위에서 찾을 값을 찾고 이에 대응하는 출력 범위의 값을 반환한다. 일치 유형과 검색 방식을 이용해 출력값을 조정해줄 수 있다. 기존의 HLOOKUP과 VLOOKUP을 대체할 수 있는 기능을 가졌지만, 엑셀 2019와 오피스 365에서만 지원한다.

3. 사용자 정의 함수

사용자가 직접 함수를 만들어서 쓸 수도 있는데 이 경우에는 비주얼베이직을 활용해서 직접 계산식을 입력해야 한다. 비주얼베이직으로 들어가서[20][21] 모듈을 새로 생성해서 원하는 대로 만들면 된다. 함수를 적용할 때는 함수 마법사로 들어가 사용자 정의를 클릭하면 된다.

3.1. 간단한 제작 방법(If문)

이용등급과 할인가를 인수로 받아서 할인가를 정하는 fn할인가 함수.
이용등급이 "A"이면 10%, "B"이면 5% 할인된 가격을 할인가로 하면 그 외 등급은 할인 없음

#!syntax basic
If 이용등급 = "A" Then
     fn할인가 = 금액 * 0.9

ElseIf 이용등급 = "B" Then
     fn할인가 = 금액 * 0.95

Else
     fn할인가 = 금액

End If

End Function

3.2. 간단한 제작 방법(select case문)

이용등급과 할인가를 인수로 받아서 할인가를 정하는 fn할인가 함수.
이용등급이 "A"이면 10%, "B"이면 5% 할인된 가격을 할인가로 하면 그 외 등급은 할인 없음
#!syntax basic
Select case 이용등급
     Case "A"
          fn할인가 = 금액 * 0.9
     Case "B"
          fn할인가 = 금액 * 0.95
Case else
     fn할인가= 금액

End select

End Function

4. 배열 수식

배열 수식은 위의 함수들을 사용하는 새로운 활용법으로, 만일 컴활 같은 것을 준비한다면 알아두면 좋다.[22] 배열 수식은 수식 입력 후에 Ctrl+Shift+Enter 를 통해 중괄호({,})로 묶어 주어야 하며[23], 그렇지 않으면 오류값이 반환된다. 나머지는 일반적인 수식을 만드는 것과 동일하다. 여기서는 가장 기초적이고 단순한 형태의 몇몇 배열 수식만 살펴본다.
  • 배열 수식으로 개수 구하는 방법
    • 조건이 1개일 경우
      • =SUM(IF(조건, 1)) : 조건을 만족하면 1로 처리하여 합산한다.
      • =SUM((조건)*1) : 조건 불만족시 0, 만족시 1로 처리.
      • =COUNT(IF(조건, 1)) : 단, COUNT 함수는 조건 불만족(0) 케이스도 합산한다.[24]
    • 조건이 2개일 경우
      • =SUM(IF(조건1, IF(조건2, 1)))
      • =SUM((조건1)*(조건2))
      • =COUNT(IF((조건1)*(조건2), 1))
  • 배열 수식으로 합계 구하는 방법
    • 조건이 1개일 경우
      • =SUM((조건)*구할 범위) : 조건을 만족하는 셀만 추려서 합산한다.
      • =SUM(IF(조건, 구할 범위))
    • 조건이 2개일 경우
      • =SUM((조건1)*(조건2)*구할 범위)
      • =SUM(IF((조건1)*(조건2), 구할 범위))
  • 배열 수식과 match 함수로 다중 조건을 만족시키는 셀을 찾는 방법
    • 조건이 1개일 경우(일반 match, 배열 수식 사용 안함)
      • =MATCH(찾을 값, 배열, match_type)
    • 조건이 2개 이상일 경우(배열 수식 사용)
      • =MATCH(찾을 값1&찾을 값2&찾을 값3,배열1&배열2&배열3,match_type) [25]

4.1. 행렬 연산

배열 수식을 통하여 엑셀에서 행렬연산이 가능하다. 먼저 행렬 연산이 결과를 출력할 셀을 다중 선택한 후, 수식을 입력한 뒤에 배열 수식을 입력할 때 처럼 Ctrl+Shift+Enter키를 통해 수식을 묶어주면 된다. 일부만 선택한 경우 해당 셀에 나올 값만 연산이 된다. 아래 함수들은 사실상 배열 수식으로만 사용이 가능하다.
TRANSPOSE(array) 전치행렬을 계산한다.[26]
MMULT(array1, array2) 두 행렬을 곱한다. 계산 불가능한 연산 시 전체 범위에서 오류가 출력된다.
MINVERSE(array) 역행렬을 구한다. 역행렬의 없는 경우에는 오류가 출력된다.

[1] INTeger Part of의 준말 [2] 한국 일본 한정으로 '가우스 기호'라고 부르지만, 정식 명칭은 '최대 정수 함수'가 맞는다. [3] 예를 들어 ' 삼성전자' 스마트폰들의 '판매액' 총합을 구하는 경우, SUMIF(제조사 열 데이터,"삼성전자",판매액 열 데이터)와 같은 식으로 입력하면 된다.(이것은 일종의 의사코드이고 실제 작업에서는 당연히 셀을 알맞게 선택해서 입력해야 한다.) [4] 1: 월초지급, 0 또는 생략: 월말에 지급 [5] MS 엑셀은 1을 날짜로 표시하면 1900-01-01로 본다. 엑셀에서 날짜/시간 형식을 인수로 받는 함수의 경우 이 형식 데이터를 입력한 기존의 셀을 인수로 선택하면 함수 내에서는 날짜/시간에 해당하는 일련번호(Serial Number)로 인식한다. 따라서 DAY(1900-01-01), MONTH(1900-01-01), HOUR(06:00)과 같은 식으로는 입력할 수 없고 인수로 날짜/시간 형식 데이터 셀을 직접 선택해야만 한다. [A] 인수가 없다. [A] 인수가 없다. [8] 일요일(1)에서 토요일(7)까지의 유형은 1을, 월요일(1)부터 일요일(7)까지의 유형은 2를, 월요일(0)에서 일요일(6)까지의 유형은 3을 입력하면 된다. 주로 쓰는건 2번 유형이다. [9] 다른 셀에서 불러올 경우에는 상관없으나 직접 수식에 입력할 경우에는 따옴표가 필요하다 [10] 'IS'로 시작하는 정보 함수는 TRUE(1)/FALSE(0) 논리값을 반환하는 특성상 단독으로 쓰이는 경우는 거의 없고 절대다수가 IF 등 논리 함수와 연계돼서 쓰인다. [11] 보통 이 경우는 IFERROR 함수가 없었던 2003 버전 워크시트에서 사용하기 위해 IF 함수와 중첩하는 경우가 대부분 [12] 예를 들어 셀 내용이 wikiwiki, 여기서 "i" 를 "o" 로 바꾸고자 하며, instance_num 값이 3일 경우, 결과값은 wikiwoki 가 된다. 만일 생략할 경우는 wokowoko 가 된다. [13] 셀에서 "NAMU" 와 "NAMU " 의 차이점을 알기가 상당히 어려운 데다가, 다른 시트에서 데이터를 가져오는 경우나 텍스트 파일을 엑셀로 가져오는 경우 종종 숫자나 텍스트 앞뒤로 공백이 붙어서 숫자임에도 텍스트로 표기되는 경우가 많다. 수천개 데이터에서 이런거 한 두개가 어디에 있는지 찾을려면... [14] 별도로 지정해 줘야 한다. 조건부 필터링 기능을 생각해 보면 쉽다. [15] "TRUE"는 유사 일치로, 쉽게 설명하자면 찾을 값이 참조할 표에 없을 때 쓴다. 성적 따라 수우미양가를 구분하듯이, 사전에 정해진 등급을 표에 적용하는 방식. "FALSE"는 정확히 일치로, 찾을 값이 참조할 표에 정확히 전부 다 들어있을 때 쓴다. 각 제품별로 단가표를 적어놓은 경우 등에 쓰인다. [16] "0"은 정렬되지 않은 배열에서 정확히 일치하는 값을 찾는다. 여러 개가 검색되었을 경우 첫째 것을 출력한다. "1"은 오름차순으로 정렬된 배열에서 작거나 같은 값 중 가장 큰 값을 찾는다. "-1"은 내림차순으로 정렬된 배열에서 크거나 같은 값 중 가장 작은 값을 찾는다. [17] 예: SUM(B3:C7) → SUM(OFFSET(A1,2,1,5,2)) : A1 셀에서 아래로 2칸, 오른쪽으로 1칸을 이동한 셀(B3)을 시작점으로 하여, 가로 2칸 세로 5칸의 범위에 있는 숫자의 합을 구함. [18] 0은 정확히 일치하는 값만 반환하며 일치하는 값이 없을 경우 #N/A 반환, -1은 일치하는 값이 없을 때 그보다 적으면서 가장 가까운 값 반환, 1은 일치하는 값이 없을 때 그보다 많으면서 가장 가까운 값 반환, 2는 와일드카드 옵션이다. [19] 1은 오름차순, -1는 내림차순, 2는 데이터가 정렬되었다고 가정한 오름차순, -2는 데이터가 정렬되었다고 가정한 내림차순이다. 즉 ±2의 경우 데이터가 정렬되어 있지 않으면 잘못된 값을 반환할 수 있다. [20] "개발 도구" 탭에 있다. 만일 이 탭이 없다면, 옵션으로 들어가서 개발 도구 탭을 표시하게 하자. 또는 엑셀 시트에서 단축키 Alt + F11를 동시에 누르면 Visual Basic Editor가 실행된다. 엑셀 뿐만이 아닌 파워포인트, MS 액세스, MS 워드 또한 같은 기능을 제공한다. [21] 사용자 정의 함수를 작성 후 저장할 시 기존 Excel 통합 문서 확장자(.xlsx)로 저장할 수 없다는 것에 주의. 저장 시 파일 형식을 Excel 매크로 사용 통합 문서 형식(.xlsm)으로 변경해주어야 작성한 사용자 정의 함수를 저장할 수 있다. [22] 2급에서는 잘 나오지 않으나 1급은 필수다. [23] 반드시 위의 단축키로. 직접 입력하는 경우 에러가 발생한다. [24] 무슨 소리냐면, COUNT함수는 숫자의 개수를 세기 때문에 일반적인 IF함수를 쓸 때처럼 IF(조건, 참일 때 값, 거짓일 때 값)이라고 IF(조건, 1, 0)으로 쓰면 원하는 결과가 안나온다는 얘기다. 때문에 0은 생략해야만 한다. [25] 예를 들어 A1:A5, B1:B5, C1:C5의 범위에서 각각 D1, D2, D3의 값을 갖는 셀의 순서를 찾을때, =MATCH(D1&D2&D3,A1:A5&B1:B5&C1:C5,0)을 배열 수식으로 넣으면 된다. INDEX 함수와 혼합하여 사용할 수도 있다. [26] m × n 행렬을 전치시키면 n × m의 범위를 잡아야 정확한 전치행렬을 연산이 가능하며, 더 적은 범위를 입력했을 경우에는 일부만 반환된다. 더 큰 범위를 설정했을 경우에는 오류를 출력하거나 값이 반복되어 나온다.

분류