컴활 엑셀 함수 정리

날짜 시간 함수

DAYS 근무 일수 계산하기

DAYS(날짜1,날짜2)

날짜 사이의 일수를 알 수 있다.

EDATE : 3개월 전/후 오늘의 날짜 구학

EDATE(시작날짜, 개월 수)

그런데 만약 1개월을 더하고 1일도 추가하고싶다
EDATE(시작날짜,1)+1 

뒤에 붙는 1은 일수로 계산된다.

날짜 형식을 추가할떄는 TEXT와 함꼐 사용한다

TEXT(EDATE(날짜1,날짜2),"mm/dd") & "~" & TEXT(EDATE(날짜1,날짜2),"mm/dd")

CONCATENATE 가 없이 & 만으로도 텍스트를 붙일 수 있다.

WEEKDAY : 날짜에서 요일알아내기 (1~7 or 0~6)

WEEKDAY(날짜,옵션)
1or생략 : 1(일요일) 7(토요일)
2 : 1(월요일) 7(일요일)
3 : 0(월요일) 6(일요일)

EOMONTH : 3개월 전/후의 마지막 날짜

EOMONTH(시작 날짜,개월수)
예를들어 8월에 1개월을 더하면 9월 30일 을 반환한다. 30일이 마지막 날이기에

NETWORKDAYS : 작업한 날짜만 계산하기

DAYS랑 같은데 휴일을 뺀 일수만 반환한다.

NETWORKDAYS(날짜1, 날짜2, 휴일날짜) 절대주소로 휴일 넣으면 된다

WORKDAY : 주말을 제외한 휴가 마지막 날 계싼

휴일들 중에서 언제 마지막으로 쉬었는지 날짜 사이들에서

WORKDAY(날짜1, 날짜2, 휴일들)

WEEKNUM : 해당날짜가 몇번쨰 주인지

WEEKNUM(날짜, 옵션)
// 1 : 일요일
// 2 : 월요일 부터 주가 시작

---

데이터 베이스 함수 ( 테이블, 열번호, 조건)

<aside> 💡

D로 시작하는 함수는 모두 위의 형식으로 인자들을 받는다.

</aside>

DCOUNTA : 조건에 맞는 자료 중 데이터가 있는 레코드 개수를 계산할떄사용

마치 고급 필터와 비슷한 사용방식이다

DCOUNTA(테이블,열번호,조건)

DAVERAGE : 조건에 맞는 자료들의 평균

마찬가지로 조건을 다른곳에 적어두고 계산한다

DGET : 유일한 값 찾아내기

여러개일 경우 #NUM 오류가 발생한다

DGET(범위,열 번호, 조건)

DSUM : 조건에 맞는 레코드들의 합계

DCOUNT : 숫자만 계산 ( 0도 포함 )

DCOUNTA와의 차이

DCOUNT

• 숫자만 계산
• 필드가 숫자인 셀만 카운트
• 0도 계산에 포함
• 텍스트나 빈 셀은 무시

DCOUNTA

• 모든 데이터 타입 계산
• 비어있지 않은 모든 셀 카운트
• 텍스트, 날짜, 숫자 등 모든 값 포함
• 빈 셀만 제외

예시: 데이터베이스에 다음과 같은 값들이 있다면:

• 100
• "텍스트"
• 0
• (빈 셀)
• 날짜

결과:

• DCOUNT: 2개 카운트 (100, 0)
• DCOUNTA: 4개 카운트 (100, "텍스트", 0, 날짜)

DMAX, DMIN, DPRODUCT, DSTDEV(표준 편차), DVAR(분산)

---

수학/ 삼각 함수

ROUND(인수, 자릿수) : 반올림하기

ROUND(인수 ,반올림 자릿수)

SUMPRODUCT(배열, 배열) : 곱한값들의 합계 구하기

SUMPRODUCT(범위, 범위) // 범위곱과 범위곱을 더함

ROUNDUP : 1에 가까운 방향으로 자리 올림하기

ROUND와의 차이

ROUND

• 일반적인 반올림 함수
• 5 이상은 올림, 5 미만은 내림
• 예시:
  • ROUND(3.2, 0) = 3
  • ROUND(3.5, 0) = 4
  • ROUND(3.7, 0) = 4
  • ROUND(-3.5, 0) = -4

ROUNDUP

• 무조건 올림 함수
• 소수점이 있으면 무조건 올림
• 예시:
  • ROUNDUP(3.2, 0) = 4
  • ROUNDUP(3.5, 0) = 4
  • ROUNDUP(3.1, 0) = 4
  • ROUNDUP(-3.2, 0) = -4

두 함수 모두 두 번째 인수는 반올림할 소수점 자리수를 지정합니다:

• 0: 정수
• 1: 소수점 첫째자리
• 2: 소수점 둘째자리
• 1: 십의 자리
• 2: 백의 자리

SUMIF ( 조건 범위, 조건, 숫자들 ) : 조건에 맞는 품목의 합계만 구하기

데이터베이스처럼 조건이 들어기만 조금 특이하다

SUMIF(조건범위,조건,합계를 구할 숫자 범위)

A B

컴퓨터	1
자동차	2
컴퓨터	4

이런 경우

SUMIF(A1:A3,"컴퓨터",B1:B3)

ABS(인수) : 절대값

EXP(인수) : e의 거듭 제곱 값 계산하기

FACT(인수) : 계승값 (인수 * 인수-1 ……* 1)

INT(인수) : 실수를 정수로 변경하기

무조건 반내림이라고 보면된다.

시험에 대한 평점을 계산하시오

시험점수가 90이상이면 ‘수’ 80이상이면 ‘우’ 70이상이면 ‘미’ ….

CHOOSE와 INT 사용

CHOOSE(INT(점수/10)+1,"가"//10점 이하,"가"......."양","미","우","수","수")

+1을 하는 이유는 아마 CHOOSE에서는 1부터이기떄문 아닐까 싶다.

PI() : 3.141592~~~

인자가 없다. 원주율 구하는 함수다

MOD(인수1, 인수2) : 나머지 구하기

QUOTIENT (인수1, 인수2) 몫 계산하기

QUOTIENT(5000,13)

5000을 13으로 나누었을때의 몫

RANDBETWEEN( 인수1, 인수2) : 인수 사이의 랜덤 번호 구하기

SQRT(인수) : 양의 제곱근 구하기

TRUNC(인수, 자릿수) : 소수 이하 잘라내기

POWER(인수, 제곱값) : 거듭 제곱 구하기

SIGN (인수) : 부호값 구하기

양수면 1 음수면 -1 0이면 0 출력

SUMIFS(합계 구할 범위, 첫번쨰 조건이 적용될 범위, 첫번쨰 조건, 두번쨰 조건이 적용될 범위, 두번쨰 조건 …..)

뭔가 나올 것 같다, 조심하자.

첫번쨰가 숫자들의 영역이라 생각. 나머진 조건 영역 , 조건 순서대로 반복한다.

---

재무함수

NPV ( 할인율, 금액1, 금액2) : 투자가치 구하기

결과가 0보다 크면 투자할 가치가 있는 것이다.

NPV(8%,1차수익, 2차수익) + 투자비용(음수)

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

만기지급액은 5년간 연이율 4%로 매월 초에 예금한 후 매월 복리로 계싼되어 만기에 찾는 예금액을 양수로 계산

FV(4%/12,5*12,예금액, ,1)

매월초라서 납입시점을 1로 함

현재가치는 주어지지않아 비어있게

연이율은 이율/12로

PMT( 이자, 기간, 현재가치, 미래가치, 납입시점) : 장기적인 상환금액

이자 : 기간 동안의 이율

기간 : 납입 회수

현재가치: 앞으로 지급할 납입금의 합계

미래가치 : 최종 지불 후의 현금잔고, 미래가치를 생략하면 0아ㅡ로 간주

납입시점 : 0또는 생략하면 ‘기말’, 1이면 ‘기초;

PV ( 이자, 기간, 금액, 미래가치, 납입시점 ) : 현재가치 구하기

지금까지 보면알겠지만 이자, 기간, 금액, 미래가치 납입시점 인자 순서가 모두 동일하다.

SLN(취득액, 잔존가치, 수명년수) : 시간이 흐르면서 감소되는 고정자산의 가치 ( 정액법 )

DB(취득액, 잔존가치, 수명년수, 기간, 월수) : 시간이 흐르면서 감소되는 고정자산의 가치 ( 정율법 )

취 잔 수 기 월

---

정보함수

ISEVEN : 짝수인지 아닌지

ISODD : 홀수 인지 아닌지

N(인수) : 인수에 대한 숫자값을 반환

TYPE(인수) : 데이터 형식을 숫자로 표시하기

---

찾기 함수

VLOOKUP( 찾을값, 범위, 열번호, 옵션 )

옵션 : TRUE or 생략 = 정확한 값

FALSE = 근사값

INDEX(범위, 행번호, 열번호)

MATCH(찾을값, 범위, 옵션) : 범위상에서 상대적인 위치를 반환한다.

옵션

• -1 : 찾을값보다 크거나 같은 값중 가장 작은값. 범위는 반드시 내림차순
• 0 : 첫번쨰로 정확하게 일치하는 값
• 1 : 찾을값보다 작거나 같은 값중에서 가장 큰값을 찾는다. 범위는 반드시 오름차순으로 정렬

LOOKUP(찾을값, 범위1, 범위2) : 범위1에서 찾을값과 같은 데이터를 찾은 후 같은 행의 범위2에 있는 데이터를 반환함

범위1에서 찾을값을 찾아서 행번호를 찾고 범위2에 해당 상대주소로서의 행번호를 반환함

만약 2가 반환되고

범위2는 C11:C13 이라면 C12의 값이 반환됨

텍스트 함수

MID(텍스트, 시작위치, 개수)

MID("APPLE",2,2) // PP

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

CONCATENATE(텍스트1, 텍스트2) : &과 같은 역할

EXACT : 텍스트 비교

PROPER(텍스트) : 텍스트 첫글자만 대문자로 변환한다.

기출 따라잡기

SUBSTITUTE(PROPER(C3),"Kr","@상공.kr")

REPLACE(텍스트1, 시작위치, 개수, 텍스트2) : 문자열 치환하기

코드형식은 S001이고 강의 코드는 S-년도-001 이렇게 만들어야한다면

REPLACE(B3,2,0,"-"&YEAR(강의년도)&"-"

개수를 0으로 해서 바꾸는거 없이 삽입만 되는것처럼 쓸 수 있다.

SUBSTITUTE(텍스트, 인수1, 인수2) : 인수1을 인수2로 변환

VALUE(텍스트) : 문자를 숫자로 변환하기

FIND(찾을 텍스트, 문자열, 시작위치)

FIND("r","KOREA",1) // 오류, 대소문자도 포함임
FIND("R","KOREA",1) // 1

TRIM(텍스트) : 문자열 공백 지우기

---

통계 함수

COUNTIFS(첫번쨰 범위, 첫번쨰 조건,……)

여러개의 조건에 맞는 자료의 개수를구하는 함수

sumifs와 다른건 첫번쨰 인자로 합계 범위가 없다는 것

COUNT(인수1,인수2,…)

인수로 주어진 값 중 숫자가 있는 셀의 개수를 구한다,.

부서코드별 제품수를 계산하시오

COUNT(IF(부서코드가 같으면,제품수 범위 배열))

COUNTIF는 여러 조건과 범위가 아닌 “딱 한조건 한 범위”

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

계산된 값을 표시할 영역을 블록으로 지정하고 함수를 입력한후 Ctrl+ SHift + Enter

---

안나올거같지만 일단 메모

MDETERM(배열) : 배열의 행렬식 계산하기

행의 수와 열의 수가 같아야한다.

MINVERSE(배열) : 역행렬 계산하기

MMULT(배열1, 배열2) : 행렬의 곱 계산하기

정보함수

ISNONTEXT : 텍스트가 이닌지

ISNUMBER : 숫자인지 아닌지

ISTEXT : 텍스트가 있는지 없는지

ISLOGICAL : TRUE FALSE rk dlTsmswl djqtsmswl