[컴활 1급 실기] 자주 나오는 함수
카테고리: Cumhwal
태그: Access Certificate Excel
컴활 1급 실기시험에 자주 출제된 함수들을 정리해봤다. 작성힌 함수를 모두 외울 순 없겠지만 함수에 대한 설명과 형식을 제대로 이해하고 도움말을 통해 사용할 수 있도록 잘 익히자. 특히 실기 시험 때는 하나의 값을 위하여 여러가지 함수를 복합적으로 사용한다. 코딩과 비슷하게 블록형식으로 단계 별로 필요한 인수를 구하고 최종적으로 필요한 값을 넣는 방식으로 함수를 계산하면 될 것 같다.
논리 함수 (IF, AND, OR, IFERROR)
IF 함수
설명 : 참과 거짓에 관한 논리식을 판별하여 참 혹은 거짓일 때 서로 다른 값을 반환하기 위해 사용하는 함수이다.
형식 IF(조건, 참일 때 인수, 거짓일 때 인수)
AND 함수
설명 : 여러 개의 논리식 결과가 모두 참일 때 참을 반환하는 함수이다.
형식 AND(인수1, 인수2, …)
OR 함수
설명 : 여러 개의 논리식 결과 중 하나라도 참이라면 참을 반환하는 함수이다.
형식 OR(인수1, 인수2, …)
IFERROR 함수
설명 : 인수로 지정한 수식이나 셀에서 오류가 발생할 경우 사용자가 지정한 오류값을 반환하고 아니면 수식의 결과를 반환한다.
형식 IFERROR(인수, 오류 시 표시할 값)
수학/삼각 함수 (SUM, ROUND, SUMPRODUCT, ROUNDUP, PRODUCT, SUMIF, INT, MOD, QUOTIENT, TRUNC, SUMIFS)
SUM 함수
설명 : 인수로 주어진 숫자들의 합계를 계산하는 함수이다. 인수는 255개까지 지정할 수 있다. 인수는 숫자 혹은 숫자가 포함된 이름, 배열 또는 셀 주소이어야 한다.
형식 SUM(인수1, 인수2, …)
ROUND 함수
설명 : 숫자를 지정한 자릿수를 반올림하여 표시하는 함수이다.
형식 ROUND(인수, 반올림 자릿수)
SUMPRODUCT 함수
설명 : 인수로 주어진 배열의 각 해당 요소들을 각각 곱한 후, 그 곱의 합계를 반환하는 함수이다. 인수로 사용하는 배열의 행,열의 수는 일치해야한다.
형식 SUMPRODUCT(배열1, 배열2, …)
ROUNDUP 함수
설명 : 숫자를 지정한 자릿수로 올림하여 표시하는 함수입니다.
형식 ROUNDUP(인수, 올림 자릿수)
PRODUCT 함수
설명 : 인수로 주어진 숫자를 모두 곱하는 함수로, 인수는 1~255개까지 지정할 수 있다.
형식 PRODUCT(인수1, 인수2, …)
SUMIF 함수
설명 : 지정한 범위 중 조건에 맞는 데이터를 찾아 합계를 구하는 함수이다. 조건이 적용될 범위와 합계를 구할 범위를 구분하여 지정하자. (COUNTIF 함수는 조건에 맞는 셀의 개수)
형식 SUMIF(조건이 적용될 범위, 조건, 합계를 구할 범위)
INT 함수
설명 : 실수의 소수점 이하를 제거하여 정수로 변활하는 함수이다. 인수로 주어진 실수보다 크지 않은 정수로 변환. (INT(-5.1)은 -6 반환)
형식 INT(인수)
MOD 함수
설명 : 숫자를 나눈 후 나머지를 구하는 함수이다.
형식 MOD(인수1, 인수2) 인수2로 인수1을 나눈 나머지
QUOTIENT 함수
설명 : 숫자를 나눈 후 몫을 구하는 함수이다.
형식 QUITIENT(인수1, 인수2) 인수2로 인수1을 나눈 몫
TRUNC 함수
설명 : 지정한 자릿수 이하의 수치를 버릴 때 사용하는 함수이다. TRUNC(3.1429,2)SMS 3.14를 반환한다.
형식 TRUNC(인수, 자릿수)
SUMIFS 함수
설명 : 여러 개의 조건에 맞는 자료의 합계를 구하는 함수이다.
형식 SUMIFS(합계를 구할 범위, 조건 1 적용 범위, 조건 1, 조건 2 적용범위, 조건 2, …)
재무 함수 (FV, PMT, PV)
FV 함수
설명 : 투자에 대한 미래가치를 계산하는 함수이다. 즉 만기금액을 구한다. 매월 일정액을 불입하거나 일정 금액을 투자한 후 정해진 이율을 복리로 적용하여 ‘원금+’이자’가 얼마인지 계산하는 함수이다. 매월 투자하는 금액과 이율, 기간은 고정되어 있다고 가정한다.
이때 계산은 월 기준으로 잡아야 한다. 연이자인지 월이자인지 확인하고 기간 또한 연단위인지 월단위인지 확인하여 계산하여 함수를 사용한다.
형식 FV(이자, 기간, 금액, 현재가치, 납입시점)
현재가치 \(\rightarrow\) 앞으로 지급할 납입금의 합계 (생략가능)
납입시점 \(\rightarrow\) 0 또는 생략이면 ‘기말’, 1이면 ‘기초’
PMT 함수
설명 : 대출받은 원금에 대해 매월 상환해야하는 원금과 이자를 계산할 때 사용하는 함수이다.
형식 PMT(이자, 기간, 현재가치, 미래가치, 납입시점)
미래가치 \(\rightarrow\) 최종 지불 후의 현금잔고, 생략시 0으로 간주
납입시점 \(\rightarrow\) 0 또는 생략이면 ‘기말’, 1이면 ‘기초’
PV 함수
설명 : 투자액에 대한 현재가치를 계산한다.
현재 100만원은 1년 뒤의 100만원보다 가치가 더 크다. 왜냐면 1년 이율이 5%라 했을 때 1년 뒤 100만원은 이율 5% 줄어든 것과 같기 때문이다. 이율이 5%일 때 현재 100만원은 미래의 106만원과 같다는 것이다. PV는 1년 뒤에 받을 돈 100만원이 현재 가치로 얼마인지 계산하는 함수이다.
형식 PV(이자, 기간, 금액, 미래가치, 납입시점)
미래가치 \(\rightarrow\) 최종 지불 후의 현금잔고, 생략시 0으로 간주
납입시점 \(\rightarrow\) 0 또는 생략이면 ‘기말’, 1이면 ‘기초’
정보 함수 (ISBLANK, ISERROR)
ISBLANK 함수
설명 : 빈 셀을 판별하는 함수이다. 빈 셀을 참조하면 TRUE, 그렇지 않으면 FALSE
형식 ISBLANK(인수)
ISERROR 함수
설명 : 오류 여부를 판별하는 함수이다.
형식 ISERROR(인수)
찾기/참조 함수 (VLOOKUP, INDEX, MATCH, HLOOKUP, LOOKUP, CHOOSE, OFFSET, ROW)
VLOOKUP 함수
설명 : 지정한 범위 맨 왼쪽 열에서 조건에 맞는 자료를 찾고 그 자료가 속한 행 중에서 지정 열의 값을 반환하는 함수이다.
형식 VLOOKUP(찾을값, 범위, 열번호, 옵션)
INDEX 함수
설명 : 셀 범위나 배열에서 행 번호와 열 번호가 교차하는 곳의 값을 반환한다.
형식 INDEX(범위, 행 번호, 열 번호)
MATCH 함수
설명 : 지정된 범위에서 기준값과 같은 데이터를 찾아 범위 내에서의 상대적인 위치를 반환한다. 상대적 위치 = 범위에서 몇 번째. 항목 위치가 아닌 값이 필요한 경우는 LOOKUP 함수 사용.
형식 MATCH(찾을값, 범위, 옵션)
옵션 \(\rightarrow\) -1 찾을값보다 작은 값, 0 정확히 일치하는 값, +1 찾을값보다 큰 값.
HLOOKUP 함수
설명 : 지정 범위 중 첫 번째 행에서 기준값을 찾고, 그 자료가 속한 열 중에서 필요한 값이 있는 행의 위치에 있는 값을 반환한다.
형식 HLOOKUP(찾을값, 범위, 행 번호, 옵션)
LOOKUP 함수
설명 : 찾을 범위에서 특정 기준값으로 자료를 찾고, 값을 구할 범위에서 찾은값과 같은 위치에 있는 값을 반환하는 함수다.
형식 LOOKUP(찾을값, 범위1, 범위2)
CHOOSE 함수
설명 : 인덱스 번호를 이용하여 특정 번째에 있는 값을 반환하는 함수이다. 254개까지의 값(인수) 중에서 한 개의 값을 선택할 수 있다.
형식 CHOOSE(인수, 첫 번째, 두 번째, …)
인수가 3이면 세 번째를 반환한다.
OFFSET 함수
설명 : 선택한 범위에서 지정한 행과 열만큼 떨어진 위치에 있는 데이터의 영역(높이X너비)의 데이터를 반환한다.
형식 OFFSET(선택 범위, 행, 열, 높이, 너비)
다른 함수 속에 넣어서 많이 활용 됨,,
ROW 함수
설명 : 셀 주소에 대한 행 번호를 알아내는 함수이다. 열 위치와 관계없이 1행부터 계산한 해당 행에 대한 번호를 반환한다.
형식 ROW(범위)
범위를 입력하지 않을 시 현재 셀의 행 번호를 반환
텍스트 함수 (LEFT, RIGHT, MID, LEN, UPPER, TEXT, CONCATENATE, REPLACE, PROPER, SUBSTITUE, VALUE, FIND, REPT, TRIM)
LEFT 함수
설명 : 문자 슬라이싱이라 생각하면 된다. 첫 문자부터 원하는 개수만큼 문자를 추출한다.
형식 LEFT(텍스트, 개수)
RIGHT 함수
설명 : 끝 문자부터 원하는 개수만큼 문자를 추출한다.
형식 RIGHT(텍스트, 개수)
MID 함수
설명 : 특정 위치에서 원하는 개수만큼 문자를 추출한다.
형식 MID(텍스트, 시작위치, 개수)
LEN 함수
설명 : 문자열의 문자 수를 구할 때 사용하는 함수이다.
형식 LEN(텍스트)
UPPER 함수
설명 : 문자열에 포함된 소문자를 모두 대문자로 반환한다.
형식 UPPER(텍스트)
TEXT 함수
설명 : 숫자를 표시 형식에 맞는 텍스트로 변환하는 함수이다.
형식 TEXT(인수, 형식)
예) TEXT(5897.77, “$0.000”) -> $5,898
CONCATENATE 함수
설명 : 여러 개의 텍스트를 한 개의 텍스트로 연결하여 표시하는 함수이다. 255개까지 지정 가능하다.
형식 CONCATENATE(텍스트1, 텍스트2, …)
REPLACE 함수
설명 : 문자열의 일부를 다른 문자열로 바꾸는 함수이다.
형식 REPLACE(텍스트1, 시작위치, 개수, 텍스트 2)
텍스트1의 시작 위치에서 개수 만큼 문자를 텍스트 2로 변환한다.
PROPER 함수
설명 : 대,소문자를 적절히 변환하는 함수이다. 단어의 첫 문자, 영문자 아닌 문자의 다음 문자를 대문자로 전환하고 나머지는 소문자로 변환한다.
형식 PROPER(텍스트)
SUBSTITUE 함수
설명 : 문자열의 일부를 찾아 다른 문자열로 바꾸는 함수이다.
형식 SUBSTITUTE(텍스트, 인수1, 인수2)
텍스트에서 인수1을 인수2로 변환한다.
VALUE 함수
설명 : 문자열을 숫자로 변환하는 함수이다.
형식 VALUE(텍스트)
FIND 함수
설명 : 문자열에 포함된 특정 문자를 찾아 위치를 반환하는 함수이다.
형식 FIND(찾을 텍스트, 문자열, 시작 위치)
설명 : 문자열의 시작위치에서부터 찾을 텍스트를 찾아 그 위치를 반환한다.
REPT 함수
설명 : 문자열을 지정한 횟수만큼 반복해서 표시하는 함수이다.
형식 REPT(텍스트, 개수)
TRIM 함수
설명 : 문자열에 공백이 많을 때 문자 사이에 공백 한 칸만 주는 함수이다.
형식 TRIM(텍스트)
예) TRIM(“허 니 테 크”) -> “허 니 테 크”
통계 함수 (COUNTIFS, AVERAGE, MAX, MIN, LARGE, COUNT, COUNTIF, FREQUENCY, SMALL, MEDIAN, COUNTA, STDEV, PERCENTIL, RANK.EQ, RANK.AVG, AVERAGEIF)
COUNTIFS 함수
설명 : 여러 개의 조건에 맞는 자료의 개수를 구하는 함수이다.
**형식 COUNTIFS(조건1 해당 범위, 조건1, 조건2 해당 범위, 조건 2, …) **
판매부, 직급1, 남자는 몇 명인가?라는 질문에 쓰면 됨.
AVERAGE 함수
설명 : 주어진 인수들의 평균을 구하는 함수,
형식 AVERAGE(인수1, 인수2, …)
MAX 함수
설명 : 주어진 인수들, 범위에서 가장 큰 값을 구하는 함수.
형식 MAX(인수1, 인수2, …)
MIN 함수
설명 : 주어진 인수들, 범위 중에서 가장 작은 값을 구하는 함수.
형식 MIN(인수1, 인수2, …)
LARGE 함수
설명 : 자료 범위 중 N번째로 큰 값을 반환하는 함수이다.
형식 LARGE(범위, N번째)
COUNT 함수
설명 : 주어진 인수 혹은 범위 내에 숫자가 있는 셀의 개수를 구하는 함수.
형식 COUNT(인수1, 인수2, …)
COUNTIF 함수
설명 : 지정한 범위에서 조건에 맞는 데이터의 개수를 구하는 함수이다.
형식 COUNTIF(범위, 조건)
FREQUENCY 함수
설명 : 범위 내에서 해당 값의 발생 빈도를 계산하여 세로 배열 형태로 반환하는 함수이다. 점수대별 빈도를 계산할 때 많이 사용한다.
형식 FREQUENCY(배열1, 배열2)
배열2의 범위에 대한 배열 1 각각의 요소들의 빈도수를 계산한다. 풀어서 설명한다면 배열 2에는 점수대가 있고 배열 1에는 실제 점수들이 각각 분포되어 있다.
SMALL 함수
설명 : 자료 범위에서 N번째로 작은 값을 반환하는 함수이다.
형식 SMALL(범위, N번째)
MEDIAN 함수
설명 : 주어진 인수들을 크기 순으로 나열했을 때 중간 위치에 해당하는 값을 반환한다.
형식 MEDIAN(인수1, 인수2, …)
COUNTA 함수
설명 : 인수로 주어진 값 중에서 자료가 입력되어 있는 모든 셀의 개수를 세는 함수이다.
형식 COUNTA(인수1, 인수2, …)
STDEV 함수
설명 : 표준편차를 계산하는 함수이다. 255개까지 지정가능하다.
형식 STDEV(인수1, 인수2)
PERCENTILE 함수
설명 : 자료의 범위에서 N번째의 백분위수를 구하는 함수이다.
형식 PERCENTILE(범위, 인수)
RANK.EQ 함수
설명 : 지정된 범위 안에서 인수의 순위를 구하는 함수이다. 이때 동일한 인수가 있을 시 그 인수의 순위는 모두 가장 높은 순위로 동일하게 계산하고 다음 차례의 인수의 순위는 앞의 개수를 모두 카운트하여 순위를 매긴다.
예시 : (3, 5, 5, 10, 12) / 12 - 1순위, 10 - 2순위, 5 - 3순위, 5 - 3순위, 3 - 5순위
형식 RANK.EQ(인수, 범위, 옵션)
옵션 : 0 또는 생략 \(\rightarrow\) 내림차순 계산 0 이외의 값 \(\rightarrow\) 올림차순 계산
RANK.AVG 함수
설명 : 지정된 범위 안에서 인수의 순위를 구하는 함수이다. 이때 동일한 인수가 있을 시 그 인수의 순위는 모두 인수들의 순위 평균으로 동일하게 계산하고 다음 차례의 인수의 순위는 앞의 개수를 모두 카운트하여 순위를 매긴다.
예시 : (3, 5, 5, 10, 12) / 12 - 1순위, 10 - 2순위, 5 - 3.5순위, 5 - 3.5순위, 3 - 5순위
형식 RANK.AVG(인수, 범위, 옵션)
AVERAGEIF 함수
설명 : 지정한 범위에서 조건에 맞는 데이터만 추출하여 평균을 구하는 작성하는 함수이다.
형식 AVERAGEIF(조건이 적용될 범위, 조건, 평균을 구할 범위)
댓글 남기기