컴활1급실기 - 엑셀 함수

[StudyBag]

* 출제빈도수가 높은 함수 정리 *

① FIND(찾을텍스트, 문자열, 시작위치) : 문자에서 특정문자 찾기

② FINDB(Byte단위)/SERCH/SERCHB((찾을텍스트, 문자열, 시작위치)

③ NETWORKDAY(날짜1,날짜2,휴일날짜) [예] : NETWORKDAY(입사일,“2008-02-24”)

④ WEEKDAY(날짜,옵션) : 옵션1(일~토) / 옵션2(월~일) / 옵션3(화~일)

⑤ WORKDAY(시작일, 일수, 휴일날짜)

⑥ TEXT(인수, 형식) : 형식에 맞는 텍스트로 바구기

[예제] [표]에서 주말을 제외한 5일후의 날짜를 반풍가능일로 계산하시오.

► 표시 예 : 01월 08일

► WORKDAY, TEXT 함수 사용

답안 : TEXT(WORKDAY(판매일,3,휴일날짜(E3,$E$3:$E$8),“YY-MM-DD”)

⑦ DAYS360(시작일,종료일) / DAYS360(시작일,종료일)/360

⑧ LARGE(범위,1) / LARGE(IF(조건=E)*계산범위,1)

⑨ RANK(인수,범위) [예] : RANK(E3,$E$3:$E$8)

⑩ EDATE(시작날짜, 개월수) / EMONTH(시작날짜, 개월수) / Date(년,월,일)

⑪ OFFSET(범위, 행, 열, 높이, 너비) : 3칸 아래 2칸 오른쪽에 있는 자료 찾기

⑫ IFERROR(인수,오류표시값) : “” / “판매량없음”

⑬ ISERROR(인수) : 오류가 있는 셀 판별하기

⑭ ➊ INDEX ( 범위, 행 번호, 열 번호) ➋ MATCH ( 찾을 값 , 범위, 옵션) : 옵션(0, 1, -1)

⑮ ROUND(반올림) / ROUNDUP(올림) / ROUNDDOWON(내림)

[표시예] 3 8 6 4 . 5 5 8 8

천의자리 : -3 소수이하 첫째리에서 반올림 : 0

백의자리 : -2 소수첫째자리 표시 : 1

십의자리 : -1 소수둘째자리 표시 : 2

⑯ MID(텍스트, 시작위치, 개수) : 4~5번째 MID(텍스트, 4(시작위치), 개수(2))

⑰ QUOTIENT(인수1, 인수2) : 인수1과 인수2사이의 정수 부부 몫만 추출

⑱ NPV(할인율, 금액1, 금액2....) : 투자의 현재가치

⑲ FV(이율,기간,금액,현재가치,납입시점) : 투자의 미래가치(만기금액)

⑳ PMT(이율,기간,금액,미래가치) : 정기적인 상환금

㉒ PV(이율,기간,금액,미래가치) : 현재가치

㉓ SLN(취득액,잔존가치,수명년수) : 정액법으로 감가상각구하기

㉔ REPT(텍스트,개수) : 문자열 반복표현

㉕ COUNTIF(범위,조건)

㉖ COUNTIFS(첫번째조건이적용되는범위,첫번째조건,두번째조건이적용되는범위,두번째조건)

㉗ SUMIF(조건이적용되는범위,조건,합계를구할범위)

㉘ SUMIFS(합계를구할범위,첫번째조건이적용되는범위,첫번째조건,두번째조건이적용되는범위,두번째조 건)

㉙ AVERAGEIF(조건이적용되는범위,조건,평균을구할범위)

㉚ AVERAGEIFS(평균을구할범위,첫번째조건이적용되는범위,첫번째조건,두번째조건이적용되는범위,두 번째조건)

※ 여기서 조건은 “>=80”처럼 ‘큰따옴표’를 사용하여 표시한다.

㉛ VLOOKP(찾을값 ,범위, 열 번호, 옵션) - ‘참조표’ 가 ‘세로’로 되어 있음

㉜ HLOOKP(찾을 값 ,범위, 행 번호, 옵션) - ‘참조표’ 가 ‘가로’로 되어 있음

㉝ LOOKUP(찾을 값, 범위1, 범위2)

㉞ FREQUENCY(배열1, 배열2) : 배열2의 범위에 대한 배열1요소들의 빈도 수를 계산

㉟ MOD(인수1, 인수2) : 인수1을 인수2로 나눈 나머지를 구합니다.

㊱ REPLACE(텍스트1, 시작위치, 개수, 텍스트2) : 텍스트1의 시작 위치에서 개수만큼 텍스트2로 변 환합니다

㊲ PROPER(텍스트) : 텍스트의 첫 글자만 대문자로 변환합니다.

㊳ SUBSTITUTE(텍스트, 인수1, 인수2) : ‘텍스트’에서 ‘인수1’을 ‘인수2’로 변환합니다.

㊴ UPPER(텍스트) : 소문자를 대문자로 변환

.

㊵ 데이터베이스 함수(범위, 열 번호, 조건)

* 먼저 지시문에 조건설정이 있으면 조건을 셀에 입력한다.

* 데이터 전체 범위를 드래그하여 지정

* 열 번호(데이터표에서 찾아)지정

* 조건셀 드래그 지정 - Enter클릭하여 결과확인

 DCOUNTA() : 건수/개수 계산하기

 DVERAGE() : 평균 값 계산하기

 DGET() : 유일 값 찾아내기

 DSUM() : 수량 합계 / DCOUNT() : 건수, 개수 계산 / DMAX() : 최대 값 / DMIN() : 최소 값

㊶ 배열수식 함수 활용

A. 배열수식이란

* 배열 수식은 배열 인수라는 2개 이상의 값에 의해 이루어진다.

* 배열 수식은 하나 이사의 값 집합에 대해 여러 가지 계산을 수행하고 또는 여러 개의 결과를 반환

* 배열 수식에 사용 되는 배열 인수 각각은 동일한 개수의 행과 열을 가져야 한다.

B 배열 수식의 활용

[1]. 조건이 한 개일 때 이용하여 ‘개수’ 구하는 방법

* 방법1 : {=SUM((조건)*1)}

* 방법2 : {=SUM(IF(조건,1)}

* 방법3 : {=COUNT(IF(조건,1)}

※ 조건의 개수에 따라 조건을 지정하는 부분만 아래와 같이 늘어납니다.

* 방법1 : {=SUM((조건1)*(조건2)*1)}

* 방법2 : {=SUM(IF((조건1)*(조건2),1))}

* 방법3 : {=COUNT(IF((조건1)*(조건2),1))}

[2]. 조건이 한 개일 때 배열 수식을 이용하여 ‘합계 ’구하는 방법

* 방법1 : {=SUM(조건)*(합계를 구할 범위))}

* 방법2 : {=SUM(IF(조건,합계를 구할 범위))}

[3]. 조건이 한 개일 때 이용하여 ‘평균 ’구하는 방법

방법 : {=AVERAGE(IF(조건, 합계를 구할 범위))}

[giant]

index, vlookup, hlookup, match, sum, average, left, right, mid의 함수는
많이 출제되는 함수인듯 하네요