Microsoft Excel/함수 목록
최근 편집일시 : (♥ 0)
![파일:나무위키+상위문서.png](http://obj-sg.the1.wiki/d/82/8a/0bb4602568cf2876f4e245107c482e7872ef091d5e44bbef84992a5b4c06186a.png)
1. 개요[편집]
Microsoft Excel의 함수 목록과 사용법을 간단하게 정리한 문서.
2. 함수[편집]
모든 함수를 입력할 때는 =함수명(입력값)으로 입력해야 한다. NOW 함수같이 입력값이 없는 함수도 있지만 #NAME? 오류를 뿜어내므로 괄호까지 모두 입력해야 한다.[2]
2010 버전에서 VAR.S나 STDEV.S 등 많은 통계학 함수가 추가되었고, 2013에서도 SKEW.P 등 몇몇 새로운 통계학 함수들이 추가되었다. 2016부터는 매크로 바이러스로 인해 2010 버전 이전에 쓰던 호환 함수들을 지우기 시작했다. 이 함수들은 실행은 가능하지만 저장하려면 2016 버전에서 지원하는 대체함수로 변경해야 저장된다. 2019에서는 더 많은 하위호환 함수들이 빠졌다. 또한 일부 함수는 언어에 따라서 사용이 불가능하기도 하다.[3]
대부분의 함수들은 리브레오피스, Calc, Google Sheets. Numbers 등 에서도 그대로 호환된다.
2.1. 수학 함수[편집]
2.2. 통계학 함수[편집]
2.2.1. 추론 통계학 함수[편집]
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. 재무 함수[편집]
2.4. 날짜/시간 함수[편집]
2.5. 텍스트/정보 함수[11][편집]
2.6. 데이터베이스[편집]
2.7. 논리/찾기 함수[편집]
3. 사용자 정의 함수[편집]
사용자가 직접 함수를 만들어서 쓸 수도 있는데 이 경우에는 비주얼베이직을 활용해서 직접 계산식을 입력해야 한다. 비주얼베이직으로 들어가서[21][22] 모듈을 새로 생성해서 원하는 대로 만들면 된다. 함수를 적용할 때는 함수 마법사로 들어가 사용자 정의를 클릭하면 된다.
3.1. 간단한 제작 방법(IF문)[편집]
이용등급과 할인가를 인수로 받아서 할인가를 정하는 fn할인가 함수.
이용등급이 "A"이면 10%, "B"이면 5% 할인된 가격을 할인가로 하면 그 외 등급은 할인 없음
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% 할인된 가격을 할인가로 하면 그 외 등급은 할인 없음
Select case 이용등급
Case "A"
fn할인가 = 금액 * 0.9
Case "B"
fn할인가 = 금액 * 0.95
Case else
fn할인가= 금액
End select
End Function
4. 배열 수식[편집]
배열 수식은 위의 함수들을 사용하는 새로운 활용법으로, 만일 컴활 같은 것을 준비한다면 알아두면 좋다.[23] 배열 수식은 수식 입력 후에 Ctrl+Shift+Enter 를 통해 중괄호({,})로 묶어 주어야 하며[24] , 그렇지 않으면 오류값이 반환된다. 나머지는 일반적인 수식을 만드는 것과 동일하다. 여기서는 가장 기초적이고 단순한 형태의 몇몇 배열 수식만 살펴본다.
- 배열 수식으로 개수 구하는 방법
- 조건이 1개일 경우
- =SUM(IF(조건, 1)) : 조건을 만족하면 1로 처리하여 합산한다.
- =SUM((조건)*1) : 조건 불만족시 0, 만족시 1로 처리.
- =COUNT(IF(조건, 1)) : 단, COUNT 함수는 조건 불만족(0) 케이스도 합산한다.[25]
- 조건이 2개일 경우
- =SUM(IF(조건1, IF(조건2, 1)))
- =SUM((조건1)*(조건2))
- =COUNT(IF((조건1)*(조건2), 1))
- 조건이 1개일 경우
- 배열 수식으로 합계 구하는 방법
- 조건이 1개일 경우
- =SUM((조건)*구할 범위) : 조건을 만족하는 셀만 추려서 합산한다.
- =SUM(IF(조건, 구할 범위))
- 조건이 2개일 경우
- =SUM((조건1)*(조건2)*구할 범위)
- =SUM(IF((조건1)*(조건2), 구할 범위))
- 조건이 1개일 경우
- 배열 수식과 match 함수로 다중 조건을 만족시키는 셀을 찾는 방법
- 조건이 1개일 경우(일반 match, 배열 수식 사용 안함)
- =MATCH(찾을 값, 배열, match_type)
- 조건이 2개 이상일 경우(배열 수식 사용)
- =MATCH(찾을 값1&찾을 값2&찾을 값3,배열1&배열2&배열3,match_type) [26]
- 조건이 1개일 경우(일반 match, 배열 수식 사용 안함)
4.1. 행렬 연산[편집]
배열 수식을 통하여 엑셀에서 행렬연산이 가능하다. 먼저 행렬 연산이 결과를 출력할 셀을 다중 선택한 후, 수식을 입력한 뒤에 배열 수식을 입력할 때 처럼 Ctrl+Shift+Enter키를 통해 수식을 묶어주면 된다. 일부만 선택한 경우 해당 셀에 나올 값만 연산이 된다. 아래 함수들은 사실상 배열 수식으로만 사용이 가능하다.
5. 둘러보기[편집]
[1] 'IS'로 시작하는 정보 함수는 TRUE(1)/FALSE(0) 논리값을 반환하는 특성상 단독으로 쓰이는 경우는 거의 없고 절대다수가 IF 등 논리 함수와 연계돼서 쓰인다.[2] NOW 함수를 예로 들면, =NOW()[3] 대표적인 예로 WON 함수는 한국어 엑셀에서는 작동하지만, 영어나 일본어 등 다른 언어 엑셀에서는 작동하지 않으며, YEN 함수는 일본어 엑셀에서 작동하지만 한국어 엑셀에서는 작동하지 않는다.[4] INTeger part of의 준말[5] 한국과 일본 한정으로 '가우스 기호'라고 부르지만, 정식 명칭은 최대 정수 함수가 맞다.[6] 예를 들어 '삼성전자' 스마트폰들의 '판매액' 총합을 구하는 경우, SUMIF(제조사 열 데이터,"삼성전자",판매액 열 데이터)와 같은 식으로 입력하면 된다.(이것은 일종의 의사코드이고 실제 작업에서는 당연히 셀을 알맞게 선택해서 입력해야 한다.)[7] 1: 월초지급, 0 또는 생략: 월말에 지급[8] MS 엑셀은 1을 날짜로 표시하면 1900-01-01로 본다. 엑셀에서 날짜/시간 형식을 인수로 받는 함수의 경우 이 형식 데이터를 입력한 기존의 셀을 인수로 선택하면 함수 내에서는 날짜/시간에 해당하는 일련번호(Serial Number)로 인식한다. 따라서 DAY(1900-01-01), MONTH(1900-01-01), HOUR(06:00)과 같은 식으로는 입력할 수 없고 인수로 날짜/시간 형식 데이터 셀을 직접 선택해야만 한다.[A] A B 인수가 없다.[9] 일요일(1)에서 토요일(7)까지의 유형은 1을, 월요일(1)부터 일요일(7)까지의 유형은 2를, 월요일(0)에서 일요일(6)까지의 유형은 3을 입력하면 된다. 주로 쓰는건 2번 유형이다.[10] 다른 셀에서 불러올 경우에는 상관없으나 직접 수식에 입력할 경우에는 따옴표가 필요하다[11] 'IS'로 시작하는 정보 함수는 TRUE(1)/FALSE(0) 논리값을 반환하는 특성상 단독으로 쓰이는 경우는 거의 없고 절대다수가 IF 등 논리 함수와 연계돼서 쓰인다.[12] 보통 이 경우는 IFERROR 함수가 없었던 2003 버전 워크시트에서 사용하기 위해 IF 함수와 중첩하는 경우가 대부분[13] 예를 들어 셀 내용이 wikiwiki, 여기서 "i" 를 "o" 로 바꾸고자 하며, instance_num 값이 3일 경우, 결과값은 wikiwoki 가 된다. 만일 생략할 경우는 wokowoko 가 된다.[14] 셀에서 "NAMU" 와 "NAMU " 의 차이점을 알기가 상당히 어려운 데다가, 다른 시트에서 데이터를 가져오는 경우나 텍스트 파일을 엑셀로 가져오는 경우 종종 숫자나 텍스트 앞뒤로 공백이 붙어서 숫자임에도 텍스트로 표기되는 경우가 많다. 수천개 데이터에서 이런거 한 두개가 어디에 있는지 찾을려면...[15] 별도로 지정해 줘야 한다. 조건부 필터링 기능을 생각해 보면 쉽다.[16] "TRUE"는 유사 일치로, 쉽게 설명하자면 찾을 값이 참조할 표에 없을 때 쓴다. 성적 따라 수우미양가를 구분하듯이, 사전에 정해진 등급을 표에 적용하는 방식. "FALSE"는 정확히 일치로, 찾을 값이 참조할 표에 정확히 전부 다 들어있을 때 쓴다. 각 제품별로 단가표를 적어놓은 경우 등에 쓰인다.[17] "0"은 정렬되지 않은 배열에서 정확히 일치하는 값을 찾는다. 여러 개가 검색되었을 경우 첫째 것을 출력한다. "1"은 오름차순으로 정렬된 배열에서 작거나 같은 값 중 가장 큰 값을 찾는다. "-1"은 내림차순으로 정렬된 배열에서 크거나 같은 값 중 가장 작은 값을 찾는다.[18] 예: SUM(B3:C7) → SUM(OFFSET(A1,2,1,5,2)) : A1 셀에서 아래로 2칸, 오른쪽으로 1칸을 이동한 셀(B3)을 시작점으로 하여, 가로 2칸 세로 5칸의 범위에 있는 숫자의 합을 구함.[19] 0은 정확히 일치하는 값만 반환하며 일치하는 값이 없을 경우 #N/A 반환, -1은 일치하는 값이 없을 때 그보다 적으면서 가장 가까운 값 반환, 1은 일치하는 값이 없을 때 그보다 많으면서 가장 가까운 값 반환, 2는 와일드카드 옵션이다.[20] 1은 오름차순, -1는 내림차순, 2는 데이터가 정렬되었다고 가정한 오름차순, -2는 데이터가 정렬되었다고 가정한 내림차순이다. 즉 ±2의 경우 데이터가 정렬되어 있지 않으면 잘못된 값을 반환할 수 있다.[21] "개발 도구" 탭에 있다. 만일 이 탭이 없다면, 옵션으로 들어가서 개발 도구 탭을 표시하게 하자. 또는 엑셀 시트에서 단축키 Alt + F11를 동시에 누르면 Visual Basic Editor가 실행된다. 엑셀 뿐만이 아닌 파워포인트, MS 액세스, MS 워드 또한 같은 기능을 제공한다. [22] 사용자 정의 함수를 작성 후 저장할 시 기존 Excel 통합 문서 확장자(.xlsx)로 저장할 수 없다는 것에 주의. 저장 시 파일 형식을 Excel 매크로 사용 통합 문서 형식(.xlsm)으로 변경해주어야 작성한 사용자 정의 함수를 저장할 수 있다.[23] 2급에서는 잘 나오지 않으나 1급은 필수다.[24] 반드시 위의 단축키로. 직접 입력하는 경우 에러가 발생한다.[25] 무슨 소리냐면, COUNT함수는 숫자의 개수를 세기 때문에 일반적인 IF함수를 쓸 때처럼 IF(조건, 참일 때 값, 거짓일 때 값)이라고 IF(조건, 1, 0)으로 쓰면 원하는 결과가 안나온다는 얘기다. 때문에 0은 생략해야만 한다.[26] 예를 들어 A1:A5, B1:B5, C1:C5의 범위에서 각각 D1, D2, D3의 값을 갖는 셀의 순서를 찾을때, =MATCH(D1&D2&D3,A1:A5&B1:B5&C1:C5,0)을 배열 수식으로 넣으면 된다. INDEX 함수와 혼합하여 사용할 수도 있다.[27] m × n 행렬을 전치시키면 n × m의 범위를 잡아야 정확한 전치행렬을 연산이 가능하며, 더 적은 범위를 입력했을 경우에는 일부만 반환된다. 더 큰 범위를 설정했을 경우에는 오류를 출력하거나 값이 반복되어 나온다.