컴활 정리 엑셀 - 계산작업

배열 수식

IF(조건,참,거짓)

SUM(인수1,인수2,...) 합계

AVERAGE(인수1,인수2,..) 평균

MATCH(찾을값, 범위, 옵션)

-1 크거나 같은 값 중 가장 작은 값

0 정확히 일치하는 값

1 작거나 같은 값 중 가장 큰 값

 

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

REPT(텍스트, 개수) 개수만큼 텍스트 반복

FIXED(인수, 자릿수, 논리값) : 인수를 반올림하여 지정된 자리수까지 텍스트로 변환

 

개수 구하는 배열 수식

=SUM((조건1)*(조건2))

=SUM(IF((조건1)*(조건2),1))

=COUNT(IF((조건1)*(조건2),1))

 

합계 구하는 배열 수식

=SUM((조건)*합계구할값)

=SUM(IF(조건,합계구할값)

 

평균 구하는 배열 수식 <- 반드시 IF문 포함

=AVERAGE(IF(조건,평균구할범위))

 

최대값 구하는 배열 수식

=MAX((조건)*최대값구할범위)

=MAX(IF(조건,최대값구할범위))

=LARGE(IF(조건,최대값구할범위),1)

 

최소값 구하는 배열 수식

=MIN(IF(조건,최소값구할범위))

=SMALL(IF(조건,최소값구할범위),1)

 

찾기/참조 함수

VLOOKUP(찾을값, 범위, 열 번호, 옵션) TRUE : 근사값 FALSE : 정확하게 일치

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

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

CONCAT(텍스트1,텍스트2)

CHOOSE(인수,첫번째,두번째,...) 인수가 1이면 1번, 2면 2번쨰 값을 고름

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

EDATE(시작날짜, 개월 수) 시작 날짜 + 개월 수 반환

OFFSET(범위, 행, 열, 높이, 너비) 선택 범위에서 지정 행,열 만큼 떨어진 위치에서 높이와 너비만큼의 데이터 반환

PMT(이자,기간,현재가치,미래가치,납입시점) ex) =PMT(6%/12, 12, -10000000) 

=PMT(월이율/12,12,-가격)

 

논리 함수

FREQUENCY(배열1,배열2) 배열2 영역에 대한 배열1 영역의 값들의 빈도수

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

TRUNC(인수,자릿수) 인수에 대해 자릿수 미만의 수치를 버린 값 반환

ABS(인수) 절대값

NETWORKDAYS(날짜1,날짜2,휴일날짜) 주말(토,일)과 지정한 휴일날짜 를 제외한 날짜1, 날짜2 사이 작업 일수 반환

TEXT(데이터,데이터형식) ex) TEXT(D1-D2,"0점")

 

기타 함수

SUMPRODUCT(배열1,배열2,...) 배열1과 배열2의 개별 요소들끼리 곱한 결과를 모두 더한 값 반환

DAYS(마지막 날짜, 시작 날짜) 마지막 날짜에서 시작 날짜를 뺀 일 수를 계산

SUMIF(조건 범위, 조건, 합계 범위) 조건에 맞는 셀을 찾아 합계 반환

SUMIFS(합계 범위, 조건1범위, 조건1, 조건2범위, 조건2,...) 여러 개의 조건을 지정하여 조건에 맞는 셀들의 합계 반환

MID(텍스트, 시작위치, 개수) 텍스트 시작위치에서부터 지정한 개수만큼 반환

DAVERAGE(범위, 열번호, 조건) 범위에서 조건에 맞는 자료 중 지정된 열번호 열의 평균 반환

 

사용자 정의 함수

If 조건식1 Then

    실행문1

ElseIf 조건식2 Then

    실행문2

Else

    실행문3

End If

 

Select Case 수식 (또는 변수)

    Case is 조건1

        실행문1

    Case is 조건2

        실행문2

    Case Else

        실행문3

End Select

 

For 반복변수=시작값 To 최종값

    실행문

Next 반복변수

 

Format(데이터,데이터형식)