[컴활 1급 실기]엑셀 2번_계산 작업 문제 요약

모의고사 1~11 정리

 

---

문제2. 계산 작업(30점)

 

1~4. 수식(함수) ( 각 6점 )

5. 사용자 정의 함수 ( 6점 )

 

---

 

1~4. 수식(함수)

참조함수

반환 결과-번호(상대 위치)

ROW(A), COLUMN(A)

MATCH(A, : , 옵션: 0, 1, -1)

*정확한 값일 때 옵션: 0, 참조영역 기준이 오름차순일 때 옵션: 1, 내림차순일 때 옵션: -1

 

반환 결과-해당 값

INDEX( : , i, j) 

VLOOKUP(A, : , 행번호, 옵션: 0, 1)

HLOOKUP(A, : , 열번호, 옵션: 0, 1)

LOOKUP(A, lookup_vector, result_vector) : 벡터형 참조

LOOKUP(A, lookup_array) : 배열형 참조(참조열과 결과열이 붙어있는 경우에만 쓸 수 있다.)

*정확한 값 옵션:0, 유사한 값: 1 (오름차순 사전 정렬 필요, -1은 없기 때문에 직접 정렬 수정 후 해줘야하기도. 문제에선 그런 거 없긴 했던 듯)

 

 

비교 함수

반환 결과-등수

RANK(A, : ) = RANK.EQ(A, : )

*RANK.EQ, RANK.AVG는 동점자 점수 계산 방식에 따른 차이. 동점자를 같은 등수로 할 지 평균값으로 할지. RNAK.EQ가 많이 쓰임.

 

반환 결과-해당 값

LARGE( : , k), SMALL( : , k)

MAX( : ), MIN( : )

PERCENTILE( : , %) : 백분율에 해당하는 값 찾기(* 꿀팁: 엑셀은 % 인식, 액세스는 인식 못하므로 소수로 작성해야 함)

 

 

계산 함수

통계-총 합, 총 개수, 평균, 중앙값, 분산, 표준편차

SUM( : ) : 셀 안의 숫자 합한다.

COUNT( : ) : 숫자 있는 셀 개수 센다. 

COUNTA( : ) : 문자 있는 셀까지 개수 센다.

AVERAGE( : )

MEDIAN( : ) : 중앙값

STDEV( : ) : 표준편차, Standard Deviation 즉 ST+DEV

VAR( : ) :분산

 

몫, 나머지, 배수

MOD(A, k) : 나머지

QUOTIENT(A, k) : 몫

 

기타

ROUND(A, k) :반올림

ROUNDUP(A, k) : 올림

ROUNDDOWN(A, k) : 내림

TRUC(A, k) : 절삭

 

VALUE(A) : 값을 수치로 바꿔줌

 

 

배열 수식

SUMPRODUCT(배열1, 배열2, ...) : 두 배열을 각각 곱해서 더해줌. 배열수식 아니어도 계산 가능

FREQUENCY(데이터 배열, 구간 배열) : 데이터 중 구간에 해당하는 값들의 빈도를 찾아줌. 도수분포표 개념. 

※ FREQUENCY는 배열수식이어야만 됨.

 

 

배열수식 종류 

※ 아래와 같이 작성 후 Enter 키 말고 꼭 Shift + Enter 키 눌러야 함

1.

= 함수( (조건) * 1 )  : COUNT, SUM 같은 함수

= 함수( (조건) * (계산 범위) ) 

= 함수( (조건1) * (조건2) * (계산 범위) ) 

 

 

2.

=함수(IF(조건, 1)) : COUNT, SUM 같은 함수

=함수( IF(조건, 계산 범위) )

=함수( IF( (조건1) * (조건2) , 계산 범위) )

 

 

 

문자열 함수

이래저래 문자열을 수정해주는 함수들

 

TEXT(A) : 값을 텍스트로 바꿔줌

 

CONCATENATE(A, B, C, ...) : 문자열을 붙여줌

 

TRIM(A) : 문자열의 불필요한 공백을 줄여줌

 

REPT(문자, k) : k 번 문자 반복 출력

 

FORMAT(A, "형식") : 형식(셀 서식)에 맞게 값 출력

 

SUBSTITUTE(A, 문자/열1, 문자/열2) : 문자열에서 문자/열1을 문자/열2로 바꿔줌

 

 

시간, 날짜 함수

DATE(Y, M, D)

TIME(H, M, S)

 

YEAR(A)

MONTH(A)

DAY(A)

 

HOUR(A)

MINUTE(A)

SECOND(A)

 

TODAY() : 현재 시스템 날짜

NOW() :  현재 시스템 날짜 및 시간

 

WEEKDAY(A) = WEEKDAY(A, 1) : 일 1-월7 (첫 주의 시작: 일요일, 달력형)

WEEKDAY(A,2) : 월1 - 일7 (첫 주의 시작: 월요일)

WEEKNUM(A) : 1년 중 몇 번째 주인지

 

WORKDAY(A, k) : 주말, 휴일 제외하고 순수 노동일(k일) 뒤의 날짜

WORKDAY(A, h, l) : 주말, 휴일 제외하고 개인적인(?) 추가 휴일(h일) + 순수 노동일(k일) 뒤의 날짜

 

EDATE(A, k) : End of Date, A 날짜부터 몇 달 전/후의 날짜

EOMONTH(A, k) : End of Month, 몇 달 전/후 마지막 날짜(k 양수: k 달 후, k 음수: k 달 전)

 

DAYS360(A, B) : 두 날짜 차이(B-A) 일수(1년을 360일로 가정한 계산)

DAYS(A, B) : 두 날짜 차이(A-B) 일수(1년을 365일로 가정한 계산)

 

 

데이터베이스 함수(DB 함수)

기본작업의 고급필터 문제와 유사

이유: 조건영역을 따로 설정해둔다는 점

※주의: 액세스의 DB 함수와 인수가 다르다. 액세스의 DB 함수는 SQL 순서로 인수를 작성하면 된다. 

DSUM

DCOUNT

DAVERAGE

...

형식 : D함수(표, 열 이름(열 번호), 조건)

 

 

 

IF함수

반환 결과-조건에 해당하는 값

IF(A, 참, 거) 

IFERROR(A, 거) 

CHOOSE(조건1, A, 조건2, B, 조건3, C, ...)

 

반환 결과-계산 결과

COUNTIF(조건 범위이자 셈 범위, 조건)

 

SUMIF(조건 범위이자 합 범위, 조건, 옵션: 합 범위) 

AVERAGEIF(조건 범위이자 평균 범위, 조건, 옵션: 평균 범위) 

*조건범위와 계산 범위가 같으면 SUMIF(조건범위, 조건) 다르면 SUMIF(조건범위, 조건, 합범위)

 

SUMIFS(합 범위, 조건 범위1, 조건1, 조건 범위2, 조건2, ...)

COUNTIFS(셈 범위, 조건 범위1, 조건1, 조건 범위2, 조건2, ...)

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

 

 

참거짓 함수(Bool)

반환 결과-참, 거짓

ISODD

ISEVEN

ISBLANK

ISNUMBER

 

 

회계 함수

PV(월 이자율, 기간(월), -지급액) : 지급액의 현재 가치

FV(월 이자율, 기간(월), -지급액)  : 지급액의 미래가치

PMT(월 이자율, 기간(월), -지급액) : 대출 상환액

PMT(월 이자율, 기간(월) , , -지급액) : 저금액

* 월 이자율이면 기간(월), 연 이자율이면 기간(년)

* 지급액은 내 기준 지출에 해당하므로 마이너스('-')를 적어서 기입해야 절댓값으로 확인 가능

* PMT대출은 PMT (%, n, -금액) (세 번째 인수: 금액) 이고, PMT저금은 PMT(%, n, , -금액) (네 번째 인수: 금액)이다.

 

 

 

5. 사용자 정의 함수

하는 법: 함수 작성 >꺼내 쓰기

 

1) 함수 작성

개발 도구>Visual Basic>삽입>모듈

 

주어지는 함수구문

Public Function fn함수명(인수1, 인수2, ...) …(1)

End Function …(2)

 

(1)과 (2) 식 사이에 

나만의 함수를 채울 기능을 코드로 작성하면 된다.

* 조건문과 그 안의 실행문으로 작성하면 된다.

※ IF 문이면 END IF, SELECT문이면 END SELECT 등으로 마무리 꼭 해줘야 함. 안 그럼 오류!

 

 

조건문 종류

*작성 편의를 위해서 아래에 대문자로 썼는데 엑셀은 알아서 바꿔주니까 신경 안 써도 된다.

 

IF-ELSE 문

IF 조건문 THEN

    실행문

ELSE 

    실행문

END IF

 

*ELSE 의 경우 실행할 게 없으면 ELSE 문 생략 가능

 

 

IF-ELSE IF-ELSE 문

IF 조건문 THEN

    실행문

ELSE IF 조건문 THEN

    실행문

ELSE 

    실행문

END IF

 

*ELSE 의 경우 실행할 게 없으면 ELSE 문 생략 가능

 

 

SELECT CASE 문

SELECT CASE 변수

    CASE 값1

       실행문1

    CASE 값2

       실행문2

    CASE ELSE

        실행문

END SELECT 

 

*CASE 절 종류

CASE 값

 

CASE 값1, 값2, ...

(예시) CASE A, B, C 

 

CASE 시작값 TO 종료값

(예시) CASE 1 TO 6

 

CASE IS 비교연산자

(예시) CASE IS >= 6

 

실행문

fn함수명 = 값

이런 식으로 출력할 내용을 적으면 된다.

 

 

2)만든 함수 꺼내 쓰기

해당 셀에 '=fn함수명' 이렇게 써도 되고

 

or

 

리본 메뉴 하단 함수 창 'fx' 버튼(함수 삽입) > 함수 마법사 > 범주선택 > 사용자 지정함수 > 내가 만든 함수