|
1. 엑셀 함수란?
엑셀함수란 반복적이고 복잡한 일련의 계산과정을 엑셀에서 미리 정해진 수식(예약어)에 의해 연산되도록 작성한 하나의 엑셀에서 약속이다.
일반 연산에서 "1+2"는 "+"라는 기호는 1과 2의 값을 서로 더하라는 세계적인 공통의 약속이라면 엑셀에서는 함수인 "=SUM(1,2)"을 사용하면 1과2의 합계를 구하라는 엑셀의 일종의 약속이다.
실제 실무에서 사용하는 계산이 다양한 만큼 엑셀에서 함수 또한 매우 다양하다. 단순한 합계를 계산하는 함수에서 어려운 각종 공식등 함수의 종류는 약 300여 종류가 있다.
이러한 엑셀함수는 각종 계산과 분석을 위하여 단독으로 사용될 수도 있고, 엑셀의 매크로 프로그램밍에도 기본적인 명령어(예약어)로도 사용된다.
2. 함수의 기본 문법
1).등 호 : 함수 사용 시 수식과 마찬가지로 입력하는 테이타가 문자열이 아닌 함수 명령임을 알려주는 기호로 등호 입력후 함수가 아닌 일반 문자열을 입력하면 에러가 나오며, 특히 수식중간에 사용되는 함수일 경우는 등호를 붙이지 않는다.
2).함수이름 : 계산하고자 하는 함수 이름으로 한글엑셀의 경우 약 300여 종류가 있다.(엑셀 HELP참조)
3).괄 호 : 괄호는 함수 계산에 필요한 인수의 시작과 끝을 나타낸다. 함수에 따라서 인수를 필요로하지 않는 함수도 있지만 이 경우에도 반드시 괄호를 사용한다(예:『=RAND()』) \주) 각 괄호 앞뒤에 공백이 있으면 함수는 실행되지 않는다.
4).인 수 : 인수는 함수가 미리 정해진 연산 순서에 따라 계산될 때 사용하는 테이타로 참조영역, 숫자, 문자열, 논리값, 함수등의 말하며, 특히 인수로 함수를 사용하면 여러개의 함수를 조합하여 원하는 계산을 할 수 있다(예:『=if(AND(국어>80,산수>80),"합격","불합격)』)어떤 함수에는 입력 위치에 따라 인수의 유형이 정해져 있는 경우도 있으므로 이때에는 함수마법사와 HELP등을 참고하여 기본값으로 정해진 인수의 유형을 입력하면된다.(예:num=수, ref=참조영역,logical=논리값)
5).콤 마 : 함수의 각 인수들은 콤마에 의해 구분된다. (주:숫자 입력시 세자리마다 콤마를 입력하는등 필요없는 콤마를 입력하지 않도록 주의해야한다)
3. 함수의 종류와 간략한 내용 설명
1).수학/삼각 함수 목록
셀 범위의 총 합계 계산, 다른 셀 범위에서 조건을 만족하는 셀 범위의 총 합계 계산, 나머지 반올림 등의 수학적인 계산와 삼각함수을 계산할 때 사용한다.
*ABS: 절대값을 구한다.
*ACOS: 아크 코싸인값을 구한다.
*ACOSH: 역 하이퍼볼릭 코싸인값을 구한다.
*ASIN: 아크 싸인값을 구한다.
*ASINH: 역 하이퍼볼릭 싸인값을 구한다.
*ATAN: 아크 탄젠트값을 구한다.
*ATAN2: 지정된 x, y 죄표에서 아크 탄젠트값을 구한다.
*ATANH: 역 하이퍼볼릭 탄젠트값을 구한다.
*CEILING: 끝 수를 올려 배수 가운데 가장 가까운 수를 구한다.
*COMBIN: 주어진 개체들로 만들 수 있는 조합의 수를 구한다.
*COS: 코싸인값을 구한다.
*COSH: 하이퍼볼릭 코싸인값을 구한다.
*COUNTIF: 주어진 찾을 조건에 맞는 셀 중에서 비어 있지 않은 셀의 개수를 구한다.
*DEGREES: 라디안으로 표시된 각의 크기를 각도로 환산한다.
*EVEN: 수의 끝 수를 올려 가장 가까운 짝수를 만든다.
*EXP: 거듭제곱값을 구한다.
*FACT: 계승값을 구한다.
*FACTDOUBLE: 이중 계승값을 구한다.
*FLLOR: 수의 끝 수를 내려 배수 가운데 가장 가까운 수를 구한다.
*GCD: 최대 공약수를 구한다.
*INT: 소수점 이하를 버리고 가장 가까운 정수로 만든다.
*LCM: 최소 공배수를 구한다.
*LN: 자연 로그값을 구한다.
*LOG: 지정된 밑에 대한 로그값을 구한다.
*LOG10: 밑이 10인 로그값을 구한다.
*MDETERM: 배열의 행렬식을 구한다.
*MINVERSE: 역행렬을 구한다.
*MMULT: 두 행렬의 곱을 구한다.
*MOD: 두 수를 나눈후 나머지를 구한다.
*MROUND: 원하는 배수로 반올림된 값을 구한다.
*MULTINOMIAL: 여러 계수값으로 이루어진 다항식을 구한다.
*ODD: 수의 끝 수를 오려 가장 가까운 홀수를 구한다.
*PI: 파이 값을 구한다.
*POWER: 주어진 수의 거듭제곱값을 구한다.
*PRODUCT: 인수들을 곱한다.
*QUOTIENT: 두 수를 나눈 후 몫을 구한다.
*RADIANS: 각도로 표시된 각의 크기를 라디안으로 환산한다.
*RAND: 0과 1사이의 난수를 구한다.
*ROMAN: 아라비아 숫자를 로마자로 바꾼다.
*ROUND: 특정 값으로 반올립한 값을 구한다.
*ROUNDDOWN: 수의 끝 수를 0쪽으로 내린다.
*ROUNDUP: 수의 끝 수를 0에서 먼 쪽으로 올린다.
*SERIESSUM: 멱급수의 합을 구한다.
*SIGN: 수의 부호를 결정한다.
*SIN: 싸인값을 구한다.
*SINH: 하이퍼볼릭 싸인값을 구한다.
*SQRT: 양의 제곱근을 구한다.
*SQRTPI: (수 * PI)의 제곱근을 구한다.
*SUM: 인수들을 더한다.
*SUMIF: 주어진 찾을 조건에 의해 지정된 셀들을 더한다.
*SUMPRODUCT: 대응되는 값끼리의 곱을 모두 더한다.
*SUMSQ: 인수의 제곱의 합을 구한다.
*SUMX2MY2: 두 배열에서 대응되는 값끼리 각각의 제곱의 차를 모두 더한다.
*SUMX2PY2: 두 배열에서 대응되는 값끼리 각각의 제곱의 합을 모두 더한다.
*SUMXMY2: 두 개의 배열에서 대응되는 값끼리 차의 제곱을 모두 더한다.
*TAN: 탄젠트값을 구한다.
*TANH: 하이퍼볼릭 탄젠트값을 구한다.
*TRUNC: 소수 부분을 버리고 정수로 만든다.
2).논리 함수 목록
조건이 참인지 거짓인지를 알아보거나 여러 조건을 검색할 때 사용한다. 예를 들어 IF 함수를 사용하면 조건이 참이면 값을 표시하고 거짓이면 다른 값을 표시한다.
IF함수를 제외하고는 대부분 복합함수로 사용 된다.
*AND: 모든 인수가 TRUE일 때 TRUE를 돌려준다.
*FALSE: 논리값 FALSE를 돌려준다.
*IF: 실행할 논리 검정을 지정한다.
*NOT: 인수 논리값을 역으로 바꾼다.( NOT함수 사용법은 AND,OR 함수 참조)
*OR: 인수 중 하나라도 TRUE면 TRUE를 돌려준다.
*TRUE: 논리값 TRUE를 돌려준다.
3).문자열 함수 목록
수식에서 문자열을 조작할 때 문자열 함수를 사용한다. 예를 들어 대소문자를 바꾸거나 문자열의 길이를 결정하거나 문자열에 날짜를 연결할 수 있다
*CHAR: 코드 번호에 해당하는 문자열을 알려준다.
*CLEAN: 문자열에서 모든 인쇄 불능 문자를 없앤다.
*CODE: 문자열의 첫 분자의 코드 번호를 알려준다.
*CONCATENATE: 여러 개의 문자열을 하나의 문자열로 결합시킨다.
*DOLLAR: 통화 서식을 사용하여 수를 문자열로 변환한다.
*EXACT: 두 문자열이 동일한지 검사한다.
*FIND: 어떤 문자열을 다른 문자열 안에서 찾는다(대소문자 구분).
*FIXED: 수를 고정된 소수점 아래 자릿수를 가진 문자열로 바꾼다.
*LEFT: 문자열의 가장 왼쪽에 있는 문자를 알려준다.
*LEN: 문자열을 구성하는 문자의 수를 알려준다.
*LOWER: 문자열을 구성하는 문자들을 모두 소문자로 바꾼다.
*MID: 어떤 문자열의 지정된 위치에서 시작하여 지정된 수의 문자들을 추출하여 준다.
*PROPER: 문자열의 모든 단어의 첫 분자를 대문자로 바꾼다.
*REPLACE: 문자열의 어떤 문자들을 다른 문자로 대체한다.
*REPT: 어떤 문자열을 지정한 횟수만큼 반복한다.
*RIGTH: 문자열의 가장 오른쪽에 있는 문자를 알려준다.
*SEARCH: 어떤 문자열을 다른 문자열 안에서 찾는다(대소문자 구분 않음).
*SUBSTITUTE: 문자열 중의 일부 문자들을 새 문자열로 대체한다.
*T: 그 인수를 문자열로 변환한다.
*TEXT: 수의 서식을 정해서 문자열로 변환한다,
*TRIM: 문자열에서 공백 문자를 제거한다.
*UPPER: 문자들을 모두 대문자로 바꾼다.
*VALUE: 문자열 인수를 수로 변환한다.
4) 재무 함수 목록
대부금 상환, 투자 금액의 미래 가치나 순 현재 가치, 채권이나 정기 이자표 가치 등을
결정하는 재무 관련 계산을 할 때 사용합니다.
재무 함수의 공통 인수는 다음과 같습니다.
1.] 미래 가치(fv:future value):
완불한 후의 투자 금액이나 대부금의 가치
2.] 기간 수(nper:number period):
총 지불 횟수 또는 총 투자 기간
3.] 지불 금액(pmt:payment):
투자 금액이나 대부금으로 정기적으로 지불되는 금액
4.] 현재 가치(pv:present value):
투자 시점에서의 투자 금액이나 대부금의 가치로서 예를 들어 대부금의 현재 가치는 최 초로 빌린 금액
5.] 비율(rate):
대부금이나 투자 금액의 이자율이나 할인율
6.] 유형(type):
월초나 월말처럼 지불 기간 동안 지불이 이루어지는 간격
*ACCRINT: 정기적으로 이자가 지급되는 유가증권의 누계 이자를 구한다.
*ACCRINTM: 만기시에 이자가 지급되는 유가증권의 누계 이자를 구한다.
*AMORDEGRC: 매회기중에 감가상각액을 계산한다.
*AMORLINC: 매회기중에 감가상각액을 계산한다.
*COUPDAYBS: 이수기간의 시작일 부터 결산일 까지의 날수를 구한다.
*COUPDAYS: 결산일을 끼고있는 이수기간의 날수를 구한다.
*COUPDAYSNC: 결산일 부터 다음 이수이자 지급일 까지의 날수를 구한다.
*COUPNCD: 결산일직후의 이자지급일을 알려준다.
*COUPNUM: 결산일과만기일사이의 이자지급 횟수를 알려준다.
*COUPPCD: 결산일직전의 이자지급 일을 알려준다.
*CUMIPMT: 주어진 기간사이에 납입하는 대출금이자의 누계액을 구한다.
*CUMPRINC: 주어진 기간사이에 납입하는 대출원금의 누계액을 구한다.
*DB: 지정된 상각 기간중에 자산의 감가상각액을 정율법으로 구한다.
*DDB: 지정된 상각기간중에 자산의 감가상각액을 이중체감법 또는 기타방법으로 구한다.
*DISC: 유가증권의 할인율을 구한다.
*DOLLARDE: 분수로 표시된금액을 십진소수표시금액으로 환산한다.
*DOLLARFR: 십진소수로 표시된금액을 분수표시금액으로 환산한다.
*DURATION: 정기적으로 이자가 지급되는 유가증권의 액면가 100원당 Macauley duration을 구한다.
*EFFECT: 명목연이율과 연간복리계산횟수로 부터 실질연이율을 구한다.
*FV: 투자액의 미래가치를 구한다.
*FVSCHEDULE: 일련의 복리이자율을 적용하여 최초원금의 미래가치를 구한다.
*INTRATE: 완전투자 유가증권의 이율을 구한다.
*IPMT: 투자액에 대해 주어진 기간동안의 이자를 구한다.
*IRR: 일련의 현금흐름에 대한 내부회수율을 구한다.
*MDURATION: 액면가를 100원으로 가상한 유가증권의 수정 Macauley 변형 duration 구한다.
*MIRR: 양과 음의 현금흐름에 서로 다른 이율이 적용될때의 내부회수율을 구한다.
*NOMINAL: 실질 연이율과 연간 복리 계산횟수로 부터 명목연이율을 구한다.
*NPER: 투자기간수를 구한다.
*NPV: 일련의 주기적인 현금흐름과 일정한 할인율을 가지는 투자의 순현재가치를 구한다.
*ODDFPRICE: 첫 이수기간이 경상이수기간과 다른(길거나짧은) 유가증권의 액면가 100원당가격을 구한다.
*ODDFYIELD: 첫 이수기간이 경상이수기간과 다른 유가증권의 연수익율을 구한다.
*ODDLPRICE: 마지막 이수기간이 경상이수기간과 다른(길거나짧은) 유가증권의 액면가 100원당가격을 구한다.
*ODDLYIELD: 마지막 이수기간이 경상이수기간과 다른 유가증권의 연수익율을 구한다.
*PMT: 차입금에 대한 정기적으로 상환하는 원리금을 구한다.
*PPMT: 일정 기간동안의 차입금액에 대한원금의 지불액을 구한다.
*PRICE: 정기적으로 이자가 지급되는 유가증권의 액면가 100원당가격을 구한다.
*PRICEDISC: 할인판매유가증권의 맥면가 100원당가격을 구한다.
*PRICEMAT: 만기시에 이자가지급되는 유가증권의 액면가 100원당가격을 구한다.
*PV: 투자의 현재가치를 구한다.
*RATE: 할부식 대출상환의 이수기간당이율을 구한다.
*RECEIVED: 완전 투자유가증권에 대하여 만기시에 수령하는 금액을 구한다.
*SLN: 일정 기간동안의 자산의 감가상각액을 정액법으로 구한다.
*SYD: 지정된 상각기간중의 자산의 감가상각액을 연수합계법으로 구한다.
*TBILLEQ: 정부 발행어음을 채권으로 간주하였을 때의 연수익율을 구한다.
*TBILLPRICE: 정부 발행어음의 액면가 100원당가격을 구한다.
*TBILLYIELD: 정부발행어음의 연수익율을 구한다.
*VDB: 지정된 감가상각 또는 그일부분중의 자산의 감가상각액을 정율법으로 구한다.
*XIRR: 반드시 주기적이지는 않은 일련의 현금흐름에 대한 내부회수율을 구한다.
*XNPV: 반드시 주기적이지는 않은 일련의 현금흐름에 대한현재가치를 구한다.
*YIELD: 정기적인 이자가지급되는 유가증권의 연수익율을 구한다.
*YIELDDISC: 할인판매 유가증권의 연수익율을 구한다.
*YIELDMAT: 만기시에 이자가 지급되는 유가증권의 연수익율을 구한다.
5) 테이타 베이스 함수 목록
○ 테이타 베이스 함수
- 의미: database로 지정된 영역에서 criteria에 맞는 레코드를 찾아 지정한 field명을 갖는 필드를 대상으로 function 함수를 계산한다.
- 문법: Dfunction(database,field,criteria)
- 구조: 데이터 베이스 함수를 실행할 수 있기 위해서는 다음 그림과 같은 모양의 데이터 베이스 형식을 갖는 표를 대상으로 하여야 한다.
- 데이터 베이스 영역: 데이터 베이스 영역은 첫행을 필드명으로 갖고 각 행은 레코드, 각 열은 필드로 갖도록 작성된 인접 셀 영역의 데이터를 말한다.앞 그림의 경우 A1:I7셀 영역이 데이터 베이스 영역이다.
- 필드명: 필드명은 각 필드에 입력된 데이터를 식별할 수 있도록 하는 이름으로서 데이터 베이스 영역의 첫행에 기록되어야 한다. 필드 이름이 입력된 영역은 일반 데이터 영역과 구분하기 위하여 다른 서식을 지정하여야 한다. 위 그림에서는 첫행의 무늬를 데이터 영역과 구분하여 지정하였다.
데이터 베이스 함수에서 field인수를 직접 필드명으로 입력하기 위해서는 필드명을 큰따옴표(" ")로 묶어야 한다. 앞 그림의 경우 첫행에 기록된 성명, 성별,…급여액 등을 말한다.
- 레코드: 데이터 베이스 영역에서 첫행에 기록된 필드명 외에 각 행에 기록된 데이터를 말한다. 따라서 한 행에는 하나의 레코드를 기록할 수 있다.앞 그림의 경우 2:7행에 기록된 각 사원의 신상 내용이 레코드인 셈이다.
- 필드: 데이터 베이스 영역에서 각 열의 필드명 아래에 기록된 내용을 말한다.동일한필드이 데이터가 입력된 영역에는 동일한 서식을 지정하여야 한다.앞 그림의 경우 A2:I7셀 영역의 각 열에 기록된 내용이 각 필드이다.
- Criteria: 찾을 조건을 포함하는 범위로 검색조건을 지정하는 첫행에는 검색 조건을 지정하고자 하는 필드명이 기록되어야 하고 필드명 아래행은 필드명을 대상으로 검색하고자 하는 조건을 입력한다.
*DAVERAGE 선택한 데이터베이스 엔트리의 평균을 구한다.
*DCOUNT 지정된 데이터베이스와 찾을 조건에서 수를 포함한 셀을 센다.
*DCOUNTA 지정된 데이터베이스와 찾을 조건에서 비어 있지 않은 셀을 센다.
*DGET 찾을 조건에 만족하는 레코드를 데이터베이스에서 추출한다.
*DMAX 선택한 데이터베이스 엔트리에서 최대값을 구한다.
*DMIN 선택한 데이터베이스 엔트리에서 최소값을 구한다.
*DPRODUCT 찾을 조건에 맞는 필드나 레코드의 값들을 곱한다.
*DSTDEV 선택한 데이터베이스 엔트리들의 표본에 대한 표준편차를 구한다.
*DSTDEVP 선택한 데이터베이스 엔트리들의 전체 모집단에 대한 표준편차를 구한다.
*DSUM 찾을 조건에 맞는 레코드의 필드 열에 있는 값들을 더한다.
*DVAR 선택한 데이터베이스 엔트리들의 표본에 대한 분산을 구한다.
*DVARP 선택한 데이터베이스 엔트리들의 전체 모집단에 대한 분산을 구한다.
*SQL.REQUEST 외부 데이터 원본과 연결하고 워크시트에서 쿼리를 실행한다. 그러면 매크로 프로그램이 없어도 결과가 배열로 나타난다.
*SUBTOTAL 목록이나 데이터베이스에서 부분합을 구한다.
첫댓글 울 스승님~ 여러분야에서 진짜 짱입니다요^^