Y&S 두 여자의 말랑말랑 생활공감

엑셀 함수 중에서 순위를 구할 수 있는 함수 RANK에 대하여 알아보자.

RANK(값, 참조 범위, [옵션])

참조 범위에서 값의 순위를 옵션에 따라 구한다.

옵션

0이나 생략 : 내림차순으로 순위를 구한다. 즉, 큰 수가 1위가 된다.

0이 아닌 임의의 값 : 오름차순으로 순위를 구한다. 즉, 작은 수가 1위가 된다.

RANK 함수 사용시 유의점

1. 수식을 복사할 경우가 많으므로 참조 범위가 고정되도록 반드시 [F4] 키를 눌러 절대 참조로 지정해야 한다.

2. 큰 수가 1위인 경우가 대부분이므로 옵션을 생략하는 경우가 대부분이다.

활용 예

1. 최근분기 순위 - 2분기 중에서 큰 수를 1위로 하여 순위를 구하시오.

2. 우량도 순위 - 반품 중에서 작은 수를 1위로 하여 순위를 구하시오.

엑셀 함수 중에서 가장 기본적인 함수에 대해 알아보자.

가장 먼저 엑셀에서 계산이 필요한 경우 SUM 같은 기본 함수를 사용하면서 엑셀 함수에 대해 알게 된다.

기본 함수에는 SUM, AVERAGE, MAX, MIN, MEDIAN, MODE, LARGE, SMALL 등의 함수가 있다.

엑셀 함수에 대해 초보여서 함수 입력부터 모른다면 다음 글을 참조하자.

함수 사용시 유의점과 팁 by Y

 기본 함수의 사용법과 설명

=> 범위는 숫자 범위를 말하고, 범위에 글자가 포함된 경우는 글자를 무시하고 계산된다.

=> [수식]-[함수 라이브러리]-[자동 합계] 명령을 이용하여 자동 합계 기능을 사용하면 SUM, AVERAGE, COUNT, MAX, MIN 함수를 쉽게 사용할 수 있다. 하지만, 범위에 따라 답이 틀리는 경우가 있기 때문에 나는 권장하지 않는다.

활용 예

함수 왕초보를 위한 합계(SUM) 구하기 실습

여기서부터는 함수를 전혀 사용할 줄 모르는 왕초보를 위한 합계 구하기 과정이다.

엑셀 함수 중에서 엑셀 2007부터 추가된 SUMIFS, AVERAGEIFS 함수에 대해 알아보자.

SUMIF 함수가 하나의 조건에 일치하는 합계를 구하는 함수라면 SUMIF 함수를 향상시킨 함수가 SUMIFS 함수다. SUMIFS 함수는 여러 조건을 만족하는 합계를 구하는 함수다.

마찬가지로 AVERAGEIF 함수를 향상시킨 함수가 AVERAGEIFS 함수다.

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

조건 범위에서 조건과 일치하는 합계 범위의 숫자를 합하는 함수이다.

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

각 조건 범위에서 각 조건과 일치하는 합계 범위의 숫자를 합하는 함수이다.

AVERAGEIF(조건 범위, 조건, 평균 범위)

조건 범위에서 조건과 일치하는 평균 범위의 숫자를 평균내는 함수이다.

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

각 조건 범위에서 각 조건과 일치하는 평균 범위의 숫자를 평균내는 함수이다.

활용 예 

엑셀 함수 중에서 개수를 구할 수 있는 함수를 알아보자.

개수 구하는 함수에는 COUNT, COUNTA, COUNTBLANK, COUNTIF 함수가 있다.

COUNT(범위)

범위에서 숫자 셀의 개수를 구하는 함수이다.

COUNTA(범위)

범위에서 빈셀을 제외한 모든 셀의 개수를 구하는 함수이다.

COUNTBLANK(범위)

범위에서 빈셀의 개수를 구하는 함수이다.

COUNTIF(조건범위, 조건)

조건 범위에서 조건에 일치하는 셀의 개수를 구하는 함수이다.

=COUNTIF(A1:A10, "승진") => A1:A10 범위에서 '승진' 문자가 입력된 셀의 개수를 구한다.

=COUNTIF(A1:A10, ">=80") => A1:A10 범위에서 80이상인 셀의 개수를 구한다.

=COUNTIF(A1:A10, "*주식회사") => A1:A10 범위에서 '주식회사'로 끝나는 문자가 입력된 셀의 개수를 구한다.

활용 예

배열 수식이란 표 형태의 데이터에서 함수로 풀 수 없는 복잡한 계산을 수행하거나 다른 데이터를 따로 입력하지 않고도 하나의 식으로 값을 구하고자 할 때 사용하는 수식이다.

배열 수식 작성시 유의점

1. 수식 입력을 종료하고 답을 보고자할 때 [Enter] 키가 아닌 [Ctrl]+[Shift]+[Enter] 키를 사용한다. [Enter] 키를 누르면 답이 제대로 나오지 않고, #VALUE? 오류가 표시된다.

2. 배열 수식을 사용하여 답을 구하고 수식을 살펴보면 자동으로 수식 앞 뒤에 중괄호({수식})가 붙어 표시된다.

3. 조건이 한개거나 여러개이거나 상관없이 괄호로 묶어주어 조건이라는 걸 쉽게 알아볼 수 있도록 작성하자.

4. 조건을 여러 개를 나열할 때 AND 조건은 *, OR 조건은 +를 사용하여 나열한다.

SUM, MAX, MIN, VAR, STDEV, MEDIAN, MODE 함수 등 같은 방식으로 배열 수식을 작성하므로, 여기에서는 SUM 함수의 예만 살펴본다. 단, COUNT 함수와 AVERAGE 함수는 2번 경우처럼 IF 함수와 같이 사용해야 한다.

1. SUM 함수만 사용하여 조건에 맞는 합 구하기

SUM((조건)*범위)-- 조건이 하나일 경우

SUM((조건1)*(조건2)*범위) -- 조건이 여러 개일 경우

범위에서 조건을 만족하는 값을 더하게 된다.

=SUM((C3:C8="법학과")*D3:D8) 입력 후 [Ctrl]+[Shift]+[Enter] 키    =>    44000

(C3:C8 범위에서 '법학과'인 금액의 합계를 구한다.)

2. SUM 함수와 IF 함수를 모두 사용하여 조건에 맞는 합 구하기

SUM(IF(조건, 범위)) -- 조건이 하나일 경우

SUM(IF((조건1)*(조건2), 범위)) -- 조건이 여러 개일 경우

범위에서 조건을 만족하는 값을 더하게 된다.

=SUM(IF(C3:C8="경영학과",D3:D8)) 입력 후 [Ctrl]+[Shift]+[Enter] 키    =>    27000

(C3:C8 범위에서 '경영학과'인 금액의 합계를 구한다.)

활용 예

엑셀 함수 중에서 데이터베이스 함수에 대해 알아보자.

데이터베이스 함수는 조건을 만족하는 자료에 대하여 더하기, 평균, 표준편차 등의 작업을 하는 함수를 의미한다. 데이터베이스 함수에는 DSUM, DAVERAGE, DMAX, DMIN, DCOUNT, DCOUNTA, DSTDEV, DVAR, DGET, DPRODUCT 함수 등이 있다.

데이터베이스 함수의 인수는 모두 같으므로 DSUM만 설명한다.

DSUM(데이터베이스 범위, 필드, 조건 범위)

데이터베이스 범위에서 조건과 일치하는 필드의 합계를 구하는 함수

필드에는 열번호, 필드 제목 중에 하나를 입력할 수 있다.

가전제품의 판매량을 합하시오.

1. 필드에 열번호를 입력할 경우    =>     =DSUM(B2:F10, 4, B2:B3)

2. 필드에 필드 제목을 입력할 경우      =>    =DSUM(B2:F10, E2, B2:B3)

활용예제

1. 제품분류가 사무용품인 제품의 매출액 합계를 구하시오.

2. 제품분류가 생활용품이고 판매량이 70 이상인 제품의 매출액 평균을 구하시오.

엑셀 함수 중에서 논리 함수에 대해 알아보자.

논리 함수에는 조건을 판단하는 IF 함수와 조건이 여러 개일 때 사용하는 AND, OR 함수가 있다.

IF(조건문, 값1, 값2)

조건문이 참이면 값1을 표시하고, 거짓이면 값2를 표시하는 함수

조건문에는 =(같다), >=(크거나 같다, 이상), <=(작거나 같다, 이하), >(크다, 초과), <(작다, 미만), <>(다르다) 등의 비교 연산자가 포함되어야 한다.

=IF(C3>=70,"우수","")     =>     우수

(중간 점수가 70 이상이면 '우수'를 표시하고, 아니면 공백을 표시한다.)

=IF(E3>=90,"A",IF(E3>=80,"B",IF(E3>=70,"C",IF(E3>=60,"D",IF(E3>=50,"E","F")))))     =>      C

(평균 점수가 90점 이상이면 A, 80점 이상이면 B, 70점 이상이면 C, 60점 이상이면 D, 50점 이상이면 E, 그 외에는 F를 표시한다.)

AND(조건1, 조건2, 조건3.....)

모든 조건이 참이면 TRUE, 하나라도 거짓이면 FALSE를 표시하는 함수

=AND(C3>=80, D3>=80)     =>     FALSE

OR(조건1, 조건2, 조건3.....)

모든 조건이 거짓이면 FALSE, 하나라도 참이면 TRUE를 표시하는 함수

=OR(C3>=80, D3>=80)     =>     TRUE

활용문제

평가1. 평균 점수가 70점 이상이면 합격, 아니면 불합격을 표시하시오.

평가2. 중간, 기말 점수가 모두 80점 이상이면 합격, 아니면 불합격을 표시하시오.

평가3. 중간, 기말 점수 중에서 하나라도 80점 이상이면 합격, 아니면 불합격을 표시하시오.

엑셀 함수 중에서 정보 함수에 대해 알아보자.

정보 함수는 셀에 어떤 값이 들어있는지 체크하기 위한 함수로 앞에 IS가 붙는다.

여기에서는 ISBLANK, ISERROR 함수를 알아보자.

ISBLANK(검사값)

검사값이 빈 셀인지 확인하여 빈 셀이면 TRUE, 빈셀이 아니면 FALSE를 반환하는 함수

=ISBLANK(C5)     =>     FALSE   (C5 셀이 빈셀이 아니므로 FALSE) 

=ISBLANK(C6)    =>    TRUE    (C5 셀이 빈셀이므로 TRUE)

ISERROR(검사값)

검사값에 에러가 있는지 확인하여 에러면 TRUE, 에러가 아니면 FALSE를 반환하는 함수

=ISERROR(4/0)     =>     TRUE   (숫자를 0으로 나누면 '#DIV/0!' 에러가 난다. 에러이므로 TRUE)

활용 예제

1. 이벤트의 참석 여부에 따라 참석했으면 '증정', 아니면 '증정안함'이 표시되도록 하시오.

2. 시험 응시 여부에 따라 응시하지 않았으면 0을 표시하고, 응시했으면 중간 고사 점수에 10점 가산점을 더하시오.

사용자 정의 함수는 엑셀에서 제공하는 함수가 아닌 사용자가 입맛에 맞게 함수를 만드는 것을 말한다.

사용자 정의 함수는 Visual Basic 언어로 만들어지기 때문에 if문이나 swicth문, Select문을 알고 있으면 더 쉽게 만들 수 있다. 여기에서는 더하기 기능만 하는 기본적인 함수를 만들어 본다.

1. 중간과 기말 점수를 합하는 사용자 정의 함수를 만들어본다. [개발 도구] 탭을 클릭한다.

2. [코드] 그룹의 [Visual Basic] 명령을 클릭한다.

3. [Microsoft Visual Basic] 창이 나타나면 [삽입]-[모듈] 메뉴를 클릭한다.

4. 함수 내용을 입력한다. 'Public Function'은 함수를 정의하는 부분이므로 반드시 입력하고, fn_sum은 함수 이름이므로 영어와 한글을 조합하여 마음대로 작성한다. 괄호 안은 함수의 인수다. 다 작성한 후에 닫기 버튼을 클릭하면 자동으로 저장 후 엑셀로 돌아간다.

5. [함수 삽입] 버튼을 클릭한다.

6. [함수 마법사] 창이 나타나면 [범주 선택] 목록 버튼을 클릭한다.

7. [사용자 정의]를 클릭한다.

8. 함수를 선택한 후 [확인] 버튼을 클릭한다.

9. 인수를 입력하는 창이 나타나면 각각의 셀을 클릭하여 입력한 후 [확인] 버튼을 클릭한다.

10. 수식 결과 값이 나타나면 수식을 복사하여 완성한다.

엑셀 함수 중에서 찾기/참조 함수에 대해 알아보자.

찾기/참조 함수에는 값을 찾는 VLOOKUP, HLOOKUP, LOOKUP, INDEX 함수,

값을 선택하는 CHOOSE 함수, 값의 상대 위치를 구하는 MATCH 함수,

참조 영역을 구하는 OFFSET 함수 등이 있다.

VLOOKUP, HLOOKUP, LOOKUP 함수 먼저 알아보자.

LOOKUP 함수는 두 가지 방식이 있다.

1. LOOKUP(검색값, 검사범위, 대응범위)

검사 범위에서 검색값을 찾아 대응범위에서 같은 위치에 있는 값을 표시하는 함수

=LOOKUP(B4,B3:B6,C3:C6)    =>     70

2. LOOKUP(검색값, 배열)

배열에서 첫번째 열/행에서 검색값을 찾아 마지막 열/행의 같은 위치에 있는 값을 표시하는 함수

=LOOKUP(B4,B3:D6)    =>      C

VLOOKUP(검색값, 범위, 열번호, [검색옵션])

범위의 첫번째 열에서 검색값을 찾아, 범위에서 열번호에 해당하는 열에서 같은 행에 있는 값을 표시하는 함수

검색 옵션

TRUE나 생략할 경우 검색값과 정확히 일치하는 값이 없는 경우 근사값을 찾아 표시하고,

FALSE나 0일 경우 검색값과 정확하게 일치하는 값을 표시한다.

=VLOOKUP(B6,B3:D6,3,0)    =>    B

HLOOKUP(검색값, 범위, 행번호, [검색옵션])

범위의 첫번째 행에서 검색값을 찾아, 범위에서 행번호에 해당하는 행에서 같은 열에 있는 값을 표시하는 함수

=HLOOKUP(I3,G3:J5,2,0)     =>     95

INDEX, OFFSET, MATCH 함수에 대해 알아보자.

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

표나 범위에서 해당 행번호, 해당 열번호만큼 떨어진 곳에 위치한 값을 구하는 함수

=INDEX(C5:G9,2,4)     =>     8900

(범위에서 2번째 행, 4번째 열에 위치한 값을 구한다)

OFFSET(기준, 행수, 열수, [높이], [폭])

기준으로부터 행 또는 열 수만큼 떨어진 곳에 있는 특정 높이와 너비의 참조 영역을 표시하는 함수

=OFFSET(D5,2,3)     =>     3800

(D5 셀로부터 2행과 3열 떨어진 곳에 위치한 값을 구한다)

MATCH(검사값, 검사범위, [검사옵션])

검사값을 검사범위에서 검색하여 그 위치를 구하는 함수

검사옵션은 1일 경우 검사값보다 작거나 같은 값 중에서 최대값을 찾고, 0일 경우 검사값과 같은 첫째 값을 찾고, -1일 경우 검사값보다 크거나 같은 값 중에서 최소값을 찾음

=MATCH(D4,C4:G4,0)      =>      2

(D4 값이 범위에서 몇번째에 위치하는 지 구함)

아래는 INDEX 함수와 MATCH 함수의 활용 예이다.

☞ VLOOKUP 함수 활용

2013/06/18 - 엑셀 주소라벨 만들기 (VLOOKUP 함수 실습) by Y

엑셀 함수 중에서 재무 함수에 대해 알아보자.

재무 함수에는 미래가치를 구하는 FV함수, 현재 가치를 구하는 PV 함수, 정기 납입 금액을 구하는 PMT 함수가 있다.

재무 함수에서 주의할 점

1. 지출하는 금액일 경우에는 돈이 나한테서 빠져나가므로 음수로 입력하고, 대출받았거나해서 나한테 들어온 돈일 경우에는 양수로 입력해야 한다.

2. 이율과 기간 모두 월 단위로 입력해야 하므로 연이율 경우에는 12로 나누고, 연단위 기간일 경우 12를 곱해야 한다.

FV(이율, 납입횟수, 정기납입액, [현재가치], [납입시점])

정기적으로 납입횟수만큼 은행에 정기납입액을 정해진 이율로 적립할 경우 미래가치를 구하는 함수

(적금을 부어 결과적으로 내가 탈 금액을 구하는 함수)

현재 가치는 미래 지급액에 상응하는 현재가치를 의미하고 생략하면 0에 해당한다. 생략 가능한 인수이다.

납입시점은 0이나 생략하면 월말에 납입하는 경우이고, 1이면 월초에 납입하는 경우이다. 생략 가능한 인수이다.

=FV(5%/12, 4*12, -200000, 0, 1)    =>   \10,647,156

(매월초 200,000원씩 적립한 금액을 연이율 5%로 계산하여 4년 뒤의 금액)

PMT(이율, 불입총횟수, 현재가치, [미래가치], [납입시점])

일정금액을 대출받았거나 투자했을 때 정기적으로 매월 또는 매년 납입하거나 수령할 금액을 구하는 함수

(대출을 받았을 때 정기적으로 납입할 금액을 구하는 함수)

=PMT(6%/12, 3*12, 0, 20000000, 0)    =>    -\508,439

(연이율 6%일 때 3년 동안 대출금 20,000,000원을 갚기 위해 매월말 입금해야 하는 금액)

PV(이율, 납입횟수, 정기납입액, [미래가치], [납입시점])

매월 일정한 금액을 일정 기간 동안 지불해주는 연금이나 보험의 지급 총액에 대한 현재 가치를 구하는 함수

(내가 일시불로 일정금액을 지불하여 연금을 가입한 후 매달 얼마씩 받는다 했을 때 그 연금에 가입한 것이 유리할지 연금의 현재가치를 알아보는 함수)

=PV(8%/12, 20*12, -300000)   =>   \35,866,288

(이율이 8%일 때 매월말에 300,000원씩 20년 동안 지급해 주는 연금의 현재 가치)

엑셀 함수 중에서 문자열 함수에 대해 알아보도록 하자.

문자열 처리를 위한 함수는 문자열 일부를 추출하는 LEFT, RIGHT, MID 함수,

영문 문자열 처리를 위한 LOWER, UPPER, PROPER 함수,

문자열의 형식을 바꾸는 VALUE, TEXT 함수,

그 외에 TRIM, LEN 함수 등이 있다.

LEFT(문자열, 문자수)

문자열의 왼쪽에서 문자수만큼 추출하는 함수

=LEFT("아름다운 우리나라", 2)    =>   아름

RIGHT(문자열, 문자수)

문자열의 오른쪽에서 문자수만큼 추출하는 함수

=RIGHT("아름다운 우리나라", 2)    =>    나라

MID(문자열, 시작 위치, 문자수)

문자열의 왼쪽에서 시작 위치만큼 떨어진 문자에서부터 문자수만큼 추출하는 함수

=MID("아름다운 우리나라", 3, 5)    =>    다운 우리

LOWER(문자열)

영문 텍스트를 소문자로 변환시키는 함수

=LOWER("ONE APPLE")    =>    one apple

UPPER(문자열)

영문 텍스트를 대문자로 변환시키는 함수

=UPPER("one apple")    =>    ONE APPLE

PROPER(문자열)

영문 텍스트에서 단어의 첫문자만 대문자로 변환하고 나머지는 소문자로 변환하는 함수

=PROPER("ONE APPLE")     =>     One Apple

VALUE(텍스트)

문자형 숫자를 숫자형 숫자로 변환하는 함수

=VALUE("2")     =>     2

TEXT(숫자 값, 표시 형식)

숫자 값에 표시 형식을 지정하여 텍스트로 변환하는 함수

표시 형식은 [셀 서식]-[표시 형식]-[사용자 지정]에서 사용하는 표시 형식과 같은 형식을 사용한다.

=TEXT(41261, "mm월 dd일 (aaaa)")     =>     12월 18일 (화요일)

TRIM(문자열)

문자열에서 여분의 공백을 제거하는 함수

=TRIM("  아름다운   세상 ")     =>     아름다운 세상

LEN(문자열)

문자열의 길이를 구하는 함수

=LEN("아름다운 우리나라!!")     =>     11

엑셀 함수 중에서 날짜 함수에 대해 알아보도록 하자.

날짜 함수는 함수의 인수가 없는 TODAY, NOW 함수,

날짜를 인수로 가지는 YEAR, MONTH, DAY, WEEKDAY 함수,

날짜의 경과 기간을 구하는 함수인 DAYS360 함수,

날짜 데이터를 만드는 DATE 함수가 있다.

TODAY()

시스템(컴퓨터)에 지정된 오늘 날짜를 결과로 보여주는 함수

=TODAY()   =>    2012-12-17

NOW()

시스템(컴퓨터)에 지정된 오늘 날짜와 시간을 보여주는 함수

=NOW()      => 2012-12-17 11:40

YEAR(날짜)

날짜의 년도를 추출하여 보여주는 함수

=YEAR("2012-12-17")     =>  2012

MONTH(날짜)

날짜의 년도를 추출하여 보여주는 함수

=MONTH("2012-12-17")   => 12

DAY(날짜)

날짜의 년도를 추출하여 보여주는 함수

=DAY("2012-12-17")   => 17

WEEKDAY(날짜, [옵션])

날짜의 요일을 숫자로 구해주는 함수

옵션은 생략가능하며, 생략하거나 1을 쓰면 일요일을 1로 시작

2를 쓰면 월요일을 1로 시작, 3을 쓰면 월요일은 0으로 시작

=WEEKDAY("2012-12-17",2)   =>   1(월요일을 의미함)

DAYS360(시작일, 종료일, 옵션)

시작일과 종료일 사이의 일수를 구해주는 함수

옵션은 종료일이 31일일 경우 TRUE이면 30일로 변경하여 계산하고, 생략하거나 FALSE는 31일로 계산한다.

=DAYS360("2012-1-1","2012-1-31",TRUE)   =>  29

DATE(연, 월, 일)

지정한 연, 월, 일로 날짜 데이터를 만드는 함수

=DATE(2012, 1, 2)   =>   2012-1-2