HU

1. 찾기참조함수

vlookup(찾을 데이터, 기준표table_array, 보여줄 값의 열번호, boolean) - 세로. boolean이 true면 유사한 값, false면 정확히 일치..

hlookup

index(제목없는 table_array, 행, 열) = 해당 값

match(찾을 값, 제목없는 lookup_array, num) – num : 1은 오름, 0은 똑같은 값, -1은 내림차순.

*lookup-array는 길게 한 줄

choose(1,"일","월","화","수","목","금","토","일") = 일

2. 데이터베이스 함수

dsum(제목포함 table_array, 더할 열의 index, 제목포함 찾을 값)

  =DSUM(B3:F9,3,C13:C14)

daverage(db, index, value)

dcount - 숫자 셈

dcounta - 데이터 셈

damx

dmin

dproduct

3. 재무함수 - 파라미터는 모두 월롤 변환하여 계산

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

fv(연이율(%)/12,기간(년)*12, -매달저축액(지출이므로 마이너스)) - 해당 기간이 지났을 떄 얼마가 저축되는지. 납입시점은 0이나 생략일 경우 말일, 1일일 경우 1을 입력

PMT 함수(정기적인 상환금액 구하는 함수) =PMT(이자, 기간, 현재가치,미래가치,납입시점)

pmt(이율/12, 대출기간(월), +금액(플러스)) - 결과가 마이너스. 매달 얼마씩 납입해야하는지.

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

pv(이율/12, 기간(월), -매달납부액) -

1. 통계함수

AVERAGEIF(조건적용범위,조건,평균구할범위)

AVERAGEIFS(평균범위, 조건1범위, 조건1, 조건2범위, 조건2,….)

count - 숫자 셈

counta - 빈칸 뺴고 다 셈

countblank - 빈칸 셈

countif(조건범위, 조건)

countifs(조건범위1, 조건1, 조건범위2, 조건2, ...)

max / min / large / small

rank(대상, 범위) - 0이 내림, 1이 올림.

Median(범위) - 중간값

2. 삼각함수

SUM

sumif(조건범위, 조건, 합계범위)

SUMIFS(합계범위, 조건 범위1,”조건1”,조건범위2,”조건2”…)

product

sumproduct

round(숫자, 자릿수) - 자릿수 3은 소수 넷쨰자리에서 반올림, -1은 첫째자리에서 반올림

rounddown - 자릿수 3은 넷째자리에서 버림. -1은 첫째자리에서 버림.

roundup

int - 정수로 표기(소수x)

abs - 절댓값

mod(숫자,나누는 수) - 나머지 계산

3.논리값함수

and

or

if

not

iferror(5/0,"에러발생")

4. 텍스트함수

left

right

mid("string", 3, 1) = r

concatenate(st1, st2, st3, ...)

len("string") = 6

lower - 전부 소문자로

upper - 대문자

proper - 맨 앞글자만 대문자로

trim - 공백제거

rept("d",2) = dd

text("2013-05-05","yyyymmmdd") = 2013May05

text("2013-05-05","mmm.dd.yy") = May.05.13

substitute("KOREe","e","A") = KOREA

find(찾을거, 문자열) - find("3","123") = 3

5. 날짜함수

year("2007-05-27") = 2007

month = 5

day = 27

date(13,05,27) = 1913-05-27

date(2013,05,27) = 2013-05-27  -> 표시형식변경에서 일반으로 하면 일련번호가 나온다.

datedif(입사일, 퇴사일+1,"Y") = 3년 ->"년"은

=DATEDIF(C39,D39,"y")&"년"&DATEDIF(C39,D39,"ym")&"개월"&DATEDIF(C39,D39+1,"md")&"일"

today() - 인수가 없다. 결과는 2016-11-30

now() - 인수가 없다. 결과는 2016-11-30 21:53

days360(입사일, 퇴사일) = 1137 일

workday("2015-04-01",6) - 토,일 제외하고 5일 지난 날짜의 일련번호

eomonth("2015-04-01",1) - 1개월이 지난 5월의 마지막 날짜의 일련번호

edate("2015-04-01", 1) - 1개월이 지난 5월 1일의 일련번호

weekday("2015-04-01") - 날짜에 해당하는 용리을 숫자로 표시한다.

  -1또는 생략 : 일요일이 1

  -2 : 월요일이 1

  -3 : 월요일 0

1. 외부데이터 가져오기

데이터>기타원본>Microsoft Query>MS Access Database

2. 고급 필터

* 사용자정의 필터의 경우 이 제목은 복붙하면 안되고 임의로 지어줘야한다.

(1) =LEFT(A3,1)<>"M"

(2) =E3<AVERAGE(E3:E25)

(3) =OR(LEFT(A3,3)="1-2",AND(AVERAGE(D3:E3)>=100,AVERAGE(D3:E3)<200))

-> (1)나 (3)같은 식에서는 array로 선택하면 안된다.

3. 조건부 서식

드래그 - 제목 포함x -> 홈>조건부서식>새규칙

(1) 수식사용 - $표시 주의

=AND(OR(LEFT($A3,1)="H",LEFT($A3,1)="K"),$D3=2009)