이곳은 개발을 위한 베타 사이트 입니다.
기여내역은 언제든 초기화될 수 있으며, 예기치 못한 오류가 발생할 수 있습니다.
기여내역은 언제든 초기화될 수 있으며, 예기치 못한 오류가 발생할 수 있습니다.
Microsoft Excel/함수 목록
덤프버전 :
상위 문서: Microsoft Excel
Microsoft Excel의 함수 목록과 사용법을 간단하게 정리한 문서이다.
모든 함수를 입력할 때는 =함수명(입력값)으로 입력해야 한다. NOW 함수같이 입력값이 없는 함수도 있지만 #NAME? 오류를 뿜어내므로 괄호까지 모두 입력해야 한다.[2]
2010 버전에서 VAR.S나 STDEV.S 등 많은 통계학 함수가 추가되었고, 2013에서도 SKEW.P 등 몇몇 새로운 통계학 함수들이 추가되었다. 2016부터는 매크로 바이러스로 인해 2010 버전 이전에 쓰던 호환 함수들을 지우기 시작했다. 이 함수들은 실행은 가능하지만 저장하려면 2016 버전에서 지원하는 대체함수로 변경해야 저장된다. 2019에서는 더 많은 하위호환 함수들이 빠졌다. 또한 일부 함수는 언어에 따라서 사용이 불가능하기도 하다.[3]
대부분의 함수들은 리브레오피스, Calc, Google Sheets. Numbers 등 에서도 그대로 호환된다.
엑셀 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처럼 적어주면 된다.
엑셀로 통계 분석하는 방법
사용자가 직접 함수를 만들어서 쓸 수도 있는데 이 경우에는 비주얼베이직을 활용해서 직접 계산식을 입력해야 한다. 비주얼베이직으로 들어가서[21][22] 모듈을 새로 생성해서 원하는 대로 만들면 된다. 함수를 적용할 때는 함수 마법사로 들어가 사용자 정의를 클릭하면 된다.
이용등급과 할인가를 인수로 받아서 할인가를 정하는 fn할인가 함수.
이용등급이 "A"이면 10%, "B"이면 5% 할인된 가격을 할인가로 하면 그 외 등급은 할인 없음
이용등급과 할인가를 인수로 받아서 할인가를 정하는 fn할인가 함수.
이용등급이 "A"이면 10%, "B"이면 5% 할인된 가격을 할인가로 하면 그 외 등급은 할인 없음
배열 수식은 위의 함수들을 사용하는 새로운 활용법으로, 만일 컴활 같은 것을 준비한다면 알아두면 좋다.[23] 배열 수식은 수식 입력 후에 Ctrl+Shift+Enter 를 통해 중괄호({,})로 묶어 주어야 하며[24] , 그렇지 않으면 오류값이 반환된다. 나머지는 일반적인 수식을 만드는 것과 동일하다. 여기서는 가장 기초적이고 단순한 형태의 몇몇 배열 수식만 살펴본다.
배열 수식을 통하여 엑셀에서 행렬연산이 가능하다. 먼저 행렬 연산이 결과를 출력할 셀을 다중 선택한 후, 수식을 입력한 뒤에 배열 수식을 입력할 때 처럼 Ctrl+Shift+Enter키를 통해 수식을 묶어주면 된다. 일부만 선택한 경우 해당 셀에 나올 값만 연산이 된다. 아래 함수들은 사실상 배열 수식으로만 사용이 가능하다.
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의 범위를 잡아야 정확한 전치행렬을 연산이 가능하며, 더 적은 범위를 입력했을 경우에는 일부만 반환된다. 더 큰 범위를 설정했을 경우에는 오류를 출력하거나 값이 반복되어 나온다.