UE,AHRSS

Microsoft Excel/함수목록

last modified: 2015-04-08 10:36:42 Contributors

이 문서는 Microsoft Excel의 함수 목록과 함수의 사용법을 설명한다. 전체 함수의 목록은 마이크로소프트 웹사이트#를 참고할 것.

이 문서는 엑셀 함수목록으로 들어올 수 있다.

Contents

1. 종류별 함수
1.1. 통계 함수
1.2. 수학/삼각함수
1.3. 재무 함수
1.4. 날짜/시간 함수
1.5. 텍스트/정보 함수
1.6. 데이터베이스
1.7. 논리/찾기 함수
2. 사용자 정의 함수
3. 배열 수식

1. 종류별 함수

※ 함수 전부를 쓰지 마시고, 사용빈도가 많은 함수를 서술해주세요.

1.1. 통계 함수

  • AVERAGE(인수1, 인수2, … 인수254) : 인수의 평균값[1]
  • AVERAGEA(인수1, 인수2, … 인수254) : 수치가 아닌 셀을 포함하는 인수의 평균 값
  • AVERAGEIF(조건이 적용될 범위, 조건, 평균을 구할 범위) : 참조된 범위 중에서 조건에 맞는 것만 평균을 구할 범위에서 찾아서 평균을 구하는 함수이다.
  • AVERAGEIFS(평균을 구할 범위, 조건1이 적용될 범위, 조건1, 조건2가 적용될 범위, 조건2, …) : 여러 조건에 맞는 셀들의 평균 값
  • MAX(인수1, 인수2, … 인수254) : 인수들 중에서 최대값을 구한다.[2]
  • MAXA(인수1, 인수2, … 인수254) : 숫자, 빈 셀 논리값(T/F), 숫자로 표시된 텍스트 등을 모두 포함한 이수 중 가장 큰 값을 구한다
  • MIN(인수1, 인수2, … 인수254) : 인수들 중에서 최소값을 구한다.[3]
  • COUNT(인수1, 인수2, … 인수254) : 인수들 중에서 숫자가 있는 인수를 세는 함수이다.
  • COUNTA(인수1, 인수2, … 인수254) : 인수들 중에서 빈 칸이 아닌 모든 인수를 세는 함수이다.
  • COUNTBLANK(범위) : 범위 중 비어있는 셀의 개수를 구함
  • COUNTIF(범위, 조건) : 참조된 범위 중에서 조건에 맞는 셀의 개수를 세는 함수이다.
  • COUNTIFS(조건1이 적용될 범위, 조건1, 조건2, ...) : 여러 조건에 맞는 셀의 개수를 세는 함수이다.
  • FREQUENCY(데이터 배열, bins_array(분포구간)) : 대상 자료의 구간별 분포를 도출한다. 미리 배열 전체를 블록 지정한 후에 배열 수식의 방식처럼 Ctrl+Shift+Enter 로 입력해야 하며, 한 셀에 적용한 후 채우기 핸들로 마저 채우는 방식이 아니다.
  • LARGE(범위, K번째) : 범위에서 K번째로 큰 값을 구한다.
  • SMALL(범위, K번째) : 범위에서 K번째로 작은 값을 구한다.
  • RANK(인수, 범위, 논리값): 범위 내에서 지정한 수의 순위를 구한다. 논리값이 0이거나 생략되면 내림차순으로, 0이외의 값은 오름차순으로 표시
  • VAR(인수1, 인수2, … 인수254) : 인수들의 분산을 구한다
  • STDEVP 또는 STDEV.P[4](인수1, 인수2, … 인수254) : 인수들을 모집단(전체집단)으로 보고 표준편차를 구한다.
  • STDEV 또는 STDEV.S[5](인수1, 인수2, … 인수254) : 인수들을 표본으로 보고 표준편차를 구한다. 표본집단이라는 불확실성을 고려한 식이기 때문에 STDEV.P보다 큰 값을 반환한다.
  • MEDIAN(인수1, 인수2, … 인수254) : 인수들의 중간값을 구한다.
  • MODE(인수1, 인수2, … 인수254) : 인수들 중 가장 많이 발생한 값을 구한다
  • FREQUENCY(배열1, 배열2) : 배열2의 범위에 대한 배열1 요소들의 빈도수 계산
  • GEOMEAN(인수1, 인수2, … 인수254) : 인수의 기하평균을 구한다
  • HARMEAN(인수1, 인수2, … 인수254) : 인수의 조화평균을 구한다
  • PERCENTILE(범위, 인수) : 범위에서 인수 번째 백분위수의 값을 구한다

1.2. 수학/삼각함수

  • ABS(number(인수)) : 인수의 절대값을 구한다.
  • EXP(number(인수)) : 인수를 e의 지수로 올린다. 예를 들어 =EXP(2)면 이↗의 이승 $ e^2 $ 의 값을 표시한다.
  • FACT(number(인수)) : 인수의 팩토리얼을 표시한다. 예를 들어 =FACT(4)면 4!의 값을 표시한다.
  • INT(number(인수)) : 인수의 소숫점 아래를 버리고 가장 가까운 정수로 내림한다.
  • LN(number(인수)) : 해당 값을 자연로그로 변환한다. 예를 들어 =LN(4)면 $ \ln 4 = \log_e 4 $ 의 값을 표시한다.
  • LOG(number(인수), base(밑)) : 해당 값을 지정한 밑의 로그로 변환한다.
  • LOG10(number(인수)) : 해당 값을 상용로그로 변환한다.
  • MOD(number(인수), Divisor(나누는 숫자)) : 인수를 지정한 숫자로 나눠서 남은 나머지를 구한다.
  • PI()[6] : 원주율(파이값)을 소숫점 14자리까지 구한다.
  • POWER(number(인수), power(승수)) : 인수의 지정한 승수만큼의 값을 구한다. 예를 들어 =POWER(2, 5)면, $ 2^5 $ 를 구한다.
  • PRODUCT((number1(인수1), … number254(인수254)) : 인수들의 곱을 구한다.
  • ROUND(number(인수), num_digits(반올림할 자릿수)) : 인수를 자릿수 만큼 반올림한다. 0을 입력하면 소숫점 없이 반올림이 된다. 소숫점을 지정할 때에는 num_digits 값이 양수, 백의 자리나 천의 자리 등을 지정할 때에는 음수여야 한다.[7]
  • ROUNDDOWN(number(인수), num_digits(내림할 자릿수)) : 인수를 자릿수 만큼 내림한다.
  • ROUNDUP(number(인수), num_digits(올림할 자릿수)) : 인수를 자릿수 만큼 올림한다.
  • SUBTOTAL(function_num(함수),[8] ref1(범위), …) : 필터링으로 가려진 데이터들을 제외하고 계산 작업을 할 수 있게 돕는 함수. 이 함수를 걸어놓고 주어진 데이터를 이리저리 필터링하면 그에 따라 함수의 결과값이 계속 바뀌는 것을 볼 수 있다.
  • SUM(number1(인수1), … number254(인수254)) : 인수들의 합을 구한다.
  • SUMIF(Range(참조할 범위), Criteria(조건), sum_range(합을 구할 범위) : 참조된 범위 중에서 조건에 맞는 것만 합을 구할 범위에서 찾아서 합을 구하는 함수이다.
  • SUMPRODUCT(array1(곱할 배열), array2(곱할 배열), …) : 각각의 곱을 수행하고 그것을 합산한다.[9]

1.3. 재무 함수

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

1.4. 날짜/시간 함수

  • DATE(year(연), month(월), day(일)) : 입력된 연, 월, 일의 날짜를 나타낸다.
  • DAY(serial_number(인수)) : 인수에서 날짜를 추출해 낸다.[11]
  • HOUR(serial_number(인수)) : 인수에서 시간을 추출해 낸다.
  • MINUTE((serial_number(인수)) : 인수에서 분을 추출해 낸다.
  • MONTH((serial_number(인수)) : 인수에서 월을 추출해 낸다.
  • NOW()[12] : 컴퓨터에 설정된 현재 년, 월, 일, 시, 분, 초를 표시한다.
  • SECOND((serial_number(인수)) : 인수에서 초를 추출해 낸다.
  • TIME(hour(시), minute(분), second(초)) : 입력된 시, 분, 초를 나타낸다.
  • TODAY()[13] : 현재 날짜를 표시한다. 다른 함수와 중첩해서 쓸 게 아니라 그저 현재날짜만을 표시해야 한다면 번거롭게 이 함수 쓰지 말고 간단히 Ctrl+세미콜론을 이용하면 된다.
  • WEEKDAY(serial_number(인수), return_type[14](유형을 결정하는 숫자)) : 일정 날짜의 요일을 숫자로 출력한다. CHOOSE 함수와 함께 결합해서 사용하기도 한다.
  • YEAR(serial_number(인수)) : 인수에서 연도를 추출해 낸다.

1.5. 텍스트/정보 함수


  • IFERROR(Value(계산), Value_if_error(오류일 시 산출값)) : 주어진 계산이 오류인지 확인하여, 오류 시 산출할 값을 지정한다. 특히 엑셀의 오류 표기들(#DIV/0! 등)을 깔끔하게 정리할 수 있다.
  • ISBLANK(Value(셀)) : 지정한 셀이 비어 있는 셀일 경우 "TRUE" 를, 뭔가가 입력되어 있을 경우 "FALSE" 를 반환한다. 스페이스바 입력도 탐지한다.
  • ISERROR(Value(셀)) : 지정한 셀이 오류가 있는 경우 "TRUE" 를, 오류가 없는 경우 "FALSE" 를 반환한다. 오류 표시 대신 산출할 값을 지정하는 IFERROR 와는 다르므로 주의.[15]
  • LEFT(text(텍스트), num_chars(왼쪽 n개)) : 주어진 텍스트의 왼쪽에서부터 n개까지의 글자를 추출해 표시한다. 결과값은 문자로 취급된다.
  • LEN(text(텍스트)) : 문자열의 길이를 숫자로 추출한다.
  • LOWER(text(텍스트)) : 모든 대문자를 소문자로 치환한다.
  • MID(text(텍스트), start_num(n번째 글자부터), num_chars(x개까지)) : 문자열의 가운데 부분을 추출해 표시한다. n번째 글자부터 x개만큼을 추출한다.
  • PROPER(text(텍스트)) : 문자열의 첫째 글자만을 대문자로 치환한다.
  • RIGHT(text(텍스트), num_chars(오른쪽 n개)) : 주어진 텍스트의 오른쪽에서부터 n개까지의 글자를 추출해 표시한다. 결과값은 문자로 취급된다.
  • SUBSTITUTE(text(텍스트), old_text(대상 텍스트), new_text(바꿀 텍스트), instance_num(n번째 텍스트에 적용)) : 쉽게 말해 광역 치환 함수. 대상이 되는 텍스트를 별도로 지정한 새 텍스트로 바꾼다. 한 셀 내에 대상 텍스트가 여러 개가 있는 상황에서는 instance_num 를 활용할 수 있는데, 모두 바꾸려면 생략하고, 숫자를 입력하면 그만큼 왼쪽에서부터 대상 텍스트를 세어서 선택적으로 적용한다.[16]
  • TEXT(value(셀), format_text(서식)) : 주어진 셀의 내용을 서식에 맞게 적용한다.
  • UPPER(text(텍스트)) : 모든 소문자를 대문자로 치환한다.
  • VALUE(text(텍스트)) : 시간 등의 문자열을 숫자로 치환한 값을 추출한다.

1.6. 데이터베이스


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

1.7. 논리/찾기 함수

  • AND(논리1, 논리2, …) : 인수로 포함된 모든 논리가 전부 참일 경우 "TRUE" 를 출력하고, 하나라도 거짓이 있을 경우 "FALSE" 를 출력한다. IF 함수와 함께 쓰면 TRUE/FALSE 표시 대신 다양한 작업을 할 수 있다.
  • HLOOKUP(찾을 값, 참조할 표, 출력할 행번호, range_lookup[18]) : 수평 방향으로 참조하는 함수. 주어진 표 외에 다른 작은 표가 하나 더 주어진다. 찾을 값을 참조할 표에서 찾아서, 참조할 표로 지정한 범위 내 출력할 자료가 들어있는 행에서 대응하는 것을 골라 출력한다. VLOOKUP 함수와 다른 점은, 참조할 표가 수평 방향으로 작성되어 있을 때 쓴다는 점이다.
  • IF(논리검사, 참일시 출력, 거짓일시 출력) : 논리검사에 들어가는 등식/수식이 참/0이 아닌 경우에는 참일시 출력에 지정한 것을, 거짓/0일경우에는 거짓일시 출력에 지정한 것을 출력. 출력에도 함수가 들어갈 수 있으며, 함수가 아닌 문자가 들어갈 경우엔 프로그래밍상의 이유때문에 큰따옴표로 감싸줘야 한다.
  • INDEX(배열, 행위치, 열위치) : 어떤 배열 내의 몇 행 몇 열에 해당하는 대상을 찾아서 출력한다. MATCH 함수와 함께 엮어서 쓰기도 한다.
  • LOOKUP(찾을 값, 참조할 표, result_vector(출력값이 포함된 배열)) : 주어진 표 외에 다른 작은 표가 하나 더 주어진다. 참조할 표가 수직방향인 VLOOKUP, 수평방향인 HLOOKUP 함수와의 차이점은, 참조할 표의 행과 열의 형태에 제약을 받지 않는다는 점이다.
  • MATCH(찾을 값, 배열, match_type[19]) : 배열 내에서 찾고자 하는 값이 몇 번째에 위치하는지 찾아서 숫자로 출력한다. INDEX 함수와 함께 엮어서 쓰기도 한다.
  • OR(논리1, 논리2, …) : 인수로 포함된 논리 중에 하나라도 참이 있을 경우 "TRUE" 를 출력하고, 전부 거짓일 경우 "FALSE" 를 출력한다. IF 함수와 함께 쓰면 TRUE/FALSE 표시 대신 다양한 작업을 할 수 있다.
  • VLOOKUP(찾을 값, 참조할 표, 출력할 열번호, range_lookup) : 수직 방향으로 참조하는 함수. 주어진 표 외에 다른 작은 표가 하나 더 주어진다. 찾을 값을 참조할 표에서 찾아서, 참조할 표로 지정한 범위 내 출력할 자료가 들어있는 열에서 대응하는 것을 골라 출력한다. HLOOKUP 함수와 다른 점은, 참조할 표가 수직 방향으로 작성되어 있을 때 쓴다는 점이다.

2. 사용자 정의 함수

사용자가 직접 함수를 만들어서 쓸 수도 있는데 이 경우에는 비주얼베이직을 활용해서 직접 계산식을 입력해야 한다. 비주얼베이직으로 들어가서[20][21] 모듈을 새로 생성해서 원하는 대로 만들면 된다. 그러나 함수 마법사에서 친절한 도움말 같은 건 기대할 수 없다.(…) 구체적인 생성방법은 인터넷 검색을 추천.

3. 배열 수식

배열 수식은 위의 함수들을 사용하는 새로운 활용법으로, 만일 컴활 같은 것을 준비한다면 알아두면 좋다. 배열 수식은 수식 입력 후에 Ctrl+Shift+Enter 를 통해 중괄호({,})로 묶어 주어야 하며, 그렇지 않으면 오류값이 반환된다. 나머지는 일반적인 수식을 만드는 것과 동일하다. 여기서는 가장 기초적이고 단순한 형태의 몇몇 배열 수식만 살펴본다.

  • 배열 수식으로 개수 구하는 방법
    • 조건이 1개일 경우
      • =SUM(IF(조건, 1)) : 조건을 만족하면 1로 처리하여 합산한다.
      • =SUM((조건)*1) : 조건 불만족시 0, 만족시 1로 처리.
      • =COUNT(IF(조건, 1)) : 단, COUNT 함수는 조건 불만족(0) 케이스도 합산한다.[22]
    • 조건이 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), 구할 범위)
----
  • [1] 2003버전까지는 인수의 개수가 30까지였지만 2007버전 부터는 254개로 바뀌었다.
  • [2] 단, 논리값과 테스트는 제외한다.
  • [3] 단, 논리값과 테스트는 제외한다.
  • [4] 앞은 2007까지. 이후 버전들은 뒤의 꼴을 사용하는게 기본.
  • [5] STDEVP와 마찬가지
  • [6] 인수가 없다
  • [7] 특히 컴활 등의 시험문제에서 "십의 자리까지" 와 "십의 자리에서" 는 서로 다른 표현. 전자는 -1, 후자는 -2 를 입력해주자.
  • [8] 사용할 함수를 지정한다. 자주 사용하는 11가지의 함수는 1자리 수/100자리 수로 미리 지정되어 있다. 1자리 수는 전체 데이터에 적용하는 함수, 100자리 수는 필터링된 데이터만 적용하는 함수
  • [9] 예를 들어 (a×b)+(c×d)+(e×f) 일 때, array1 은 a, c, e 가 되고 array2 는 b, d, f 가 된다. 더 좋은 설명이 있다면 수정바람.
  • [10] 1: 월초지급, 0 또는 생략: 월말에 지급
  • [11] MS 엑셀은 1을 날짜로 표시하면 1900-01-01로 본다.
  • [12] 인수가 없다.
  • [13] 인수가 없다.
  • [14] 일요일(1)에서 토요일(7)까지의 유형은 1을, 월요일(1)부터 일요일(7)까지의 유형은 2를, 월요일(0)에서 일요일(6)까지의 유형은 3을 입력하면 된다. 주로 쓰는건 2번 유형이다.
  • [15] 보통 이 경우는 IFERROR 함수가 없었던 2003 버전 워크시트에서 사용하기 위해 IF 함수와 중첩하는 경우가 대부분
  • [16] 예를 들어 셀 내용이 wikiwiki, 여기서 "i" 를 "o" 로 바꾸고자 하며, instance_num 값이 3일 경우, 결과값은 wikiwoki 가 된다. 만일 생략할 경우는 wokowoko 가 된다.
  • [17] 별도로 지정해 줘야 한다. 조건부 필터링 기능을 생각해 보면 쉽다.
  • [18] "TRUE"는 유사 일치로, 쉽게 설명하자면 찾을 값이 참조할 표에 없을 때 쓴다. 성적 따라 수우미양가를 구분하듯이, 사전에 정해진 등급을 표에 적용하는 방식. "FALSE"는 정확히 일치로, 찾을 값이 참조할 표에 정확히 전부 다 들어있을 때 쓴다. 각 제품별로 단가표를 적어놓은 경우 등에 쓰인다. 더 나은 설명 수정바람.
  • [19] "0"은 정렬되지 않은 배열에서 정확히 일치하는 값을 찾는다. 여러 개가 검색되었을 경우 첫째 것을 출력한다. "1"은 오름차순으로 정렬된 배열에서 작거나 같은 값 중 가장 큰 값을 찾는다. "-1"은 내림차순으로 정렬된 배열에서 크거나 같은 값 중 가장 작은 값을 찾는다.
  • [20] "개발 도구" 탭에 있다. 만일 이 탭이 없다면, 옵션으로 들어가서 개발 도구 탭을 표시하게 하자.
  • [21] 또는 엑셀 시트에서 Alt + F11키를 동시에 누르면 Visual Basic Editor 실행된다.
  • [22] 무슨 소리냐면, COUNT함수는 숫자의 개수를 세기 때문에 일반적인 IF함수를 쓸 때처럼 IF(조건, 참일 때 값, 거짓일 때 값)이라고 IF(조건, 1, 0)으로 쓰면 원하는 결과가 안나온다는 얘기다. 컴활 준비하는 위키러는 주의하자.