김형백의 컴퓨터 따라잡기 - 엑셀 기타 중요한 함수들

이 홈페이지의 자료는 100% 무료로 이용하실 수 있습니다. [저작권 안내]
MAIN	HOME \| 공지 \| 집주인 소개 \| 사이트 취지 \| 방명록
EXCEL*	초급 \| 중급 \| 고급 \| 네트워크 \| FAQs \| Q&A \| Tip & Sources 동영상 강좌 다운로드 \| MOUS \| OFFICE XP \| 엑셀 동영상(플래쉬) 강좌
RESOURCES	컴퓨터 기초 / Win98 \| Win 2000 서버 \| Open Office
"Thinking Global, Acting Local!" - 崇人

13. 기타 중요한 함수들

지금부터는 많이 사용되는 중요한 함수를 종류별로 하나씩 정리해 보겠습니다.
이제 어느 정도 함수를 사용 하고 응용하는데 익숙해 졌으리라 생각합니다. 중요한 것은 현재 자신이 사용하는 데이터에 어떻게 엑셀의 함수들을 응용할까 하고 계속 생각을 해봐야 한다는 것입니다. 이 책에서 다루지 않은 함수에 대해서는 도움말을 참고해서 공부하면 됩니다.

1> 날짜와 시간 함수

1-1> YEAR 함수를 이용한 나이계산
YEAR 함수는 날짜에 해당하는 연도를 표시하는 기능을 가지며 연도는 1900에서 9999까지의 정수가 사용될 수 있습니다. 예를들어 =YEAR(99/8/13) 은 1999를 반환하게 되는 것입니다. 이 함수를 이용해서 어떻게 나이를 계산 할 수 있을까요? 다음 그림처럼 기준일에서 생년월일을 뺀 값을 YEAR 함수의 인수로 받고, 다시 이 값에서 1900을 빼면 나이 계산이 되는 것입니다.

1-2> 현재 날짜를 알려주는 TODAY
TODAY 함수는 현재 날짜를 알려주는 함수입니다. 가계부나 기타 경리 서류에서 다른 함수와 중첩해서 간단하지만 여러 가지 작업을 할 수 있는 함수입니다.

1-3> MONTH 함수를 이용해서 매월 생일자를 자동으로 파악하자
MONTH 함수는 일련 번호로 표시된 날짜의 월을 1(1월)에서 12(12월)까지의 정수로 표시하는 역할을 합니다. =MONTH(99/8/23)은 8이라는 결과가 나오게 되는 것이죠. 그럼 앞에서 배운 TODAY 함수와 중첩해서 자동으로 매월 생일자를 표시하도록 해볼까요?
다음과 같은 수식을 이용해 보겠습니다.

=IF(MONTH(C6)=MONTH($B$2),"O","")
→ 만약 C6 셀의 월이 B2셀의 월과 동일하면 O를 표시하고
그렇지 않으면 빈칸으로 표시해라.

조금씩 응용이 되죠? B2 셀에는 =TODAY() 가 들어 있으므로 항상 현재 날짜를 표시합니다. 또한 B2셀은 채우기 핸들을 해도 변경되면 안되므로 절대참조로 했습니다.

1-4> WEEKDAY 함수로 요일 표시하기
WEEKDAY 함수는 날짜에 해당하는 요일을 1(일요일)에서 7(토요일)까지의 정수로 표시하는 함수입니다. =WEEKDAY(99/8/23)은 2가 표시가 됩니다.(월요일) 이 함수를 IF함수의 중첩을 통해 일반 문자열로 표시할 수 있습니다. 물론 TEXT 함수를 사용하는 더 간단한 방법도 있지만 이 함수는 뒤에서 배우기로 하고, 우선은 WEEKDAY와 IF 함수를 중첩해서 요일을 문자로 표시해보도록 하겠습니다. (클릭하면 그림을 크게 볼 수 있습니다.)

물론 수식을 더 줄이려면 수식에서는 "요일" 빼고 셀 사용자 서식에서 "@요일"이라고 정의해 둔다면 더 효율적이겠죠. 이렇게 엑셀의 모든 기능은 연관되어져 있습니다.

1-5> 현재 날짜와 시간을 입력하는 NOW 함수
NOW 함수에서 반환되는 결과값은 엑셀의 기본 날짜 체계인 1904 날짜 체계를 따르도록 되어 있으며, 사용자의 설정에 의해서 다르게 표시할 수도 있습니다. 또한 NOW 함수에 의해 나타난 값은 워크시트가 계산되거나 함수가 들어 있는 매크로가 실행될 때에만 변경되고, 연속하여 새로 고쳐지지는 않습니다.

2> 논리 함수

2-1> AND 함수
AND 함수는 인수가 모두 TRUE이면 TRUE를 표시하고, 하나 또는 그 이상의 인수가 FALSE 이면 FALSE를 나타내는 기능을 합니다. AND 함수의 인수는 모두 30개까지 가능하며 배열이나 참조 영역 인수에 텍스트나 빈 셀이 들어 있으면 그 값은 무시되는 특징이 있습니다. 다음과 같은 상황을 가정해봅시다. 평균 점수가 70점 이상인 학생 중, 영어는 80점 이상, 수학은 85점 이상, 국어는 75점 이상이 되는 학생에게만 표창장을 주기로 결정했습니다. 어떻게 찾아낼 수 있을까요? 이런 경우 AND와 IF 함수를 중복하면 어떨까요? 저는 다음과 같은 식으로 생각을 했습니다.

이제 이 정도 수식은 길게 설명을 하지 않아도 되겠죠? 앞에서 IF함수는 응용의 폭이 넓다고 했는데 이렇게 어떤 함수와도 어울려서 사용할 수 있기 때문에 그렇습니다.

2-2> OR 함수
OR는 여러 가지 인수중 하나라도 참이면 참값을 반환하는 함수입니다. 앞의 예와 응용해보면, 어느 한가지 조건만이라도 만족하면 표창장을 줄 때 사용할 수 있겠죠.

2-3> NOT 함수
NOT은 참과 거짓을 바꿔서 출력하는 함수입니다. NOT 함수 역시 IF와 결합해서 많이 사용됩니다.

3> 수학 / 통계 함수

3-1> COUNTA
인수 목록에서 공백이 아닌 셀과 값의 개수를 계산할 때 사용합니다. COUNTA를 사용하면 범위나 배열에서 데이터를 가진 셀의 개수를 계산할 수 있습니다. 공백을 제외한 오류값도 포함시켜서 계산합니다.

3-2> 절대값을 구하는 ABS
ABS는 절대값을 구해줍니다. 절대값이란 부호가 없는 숫자를 의미하는 것으로, =ABS(-3)은 3을 출력하게 됩니다.

3-3> 인수를 모두 곱하는 PRODUCT
PRODUCT는 인수를 모두 곱해서 출력합니다. SUM은 인수를 모두 더하는 기능이었지요.

3-4> 곱한 후 더해주는 SUMPRODUCT
SUMPRODUCT는 인수를 모두 곱한 후 그 합계를 내주는데 PRODUCT 함수와 SUM 함수가 합쳐진 것입니다.

3-5> 나머지를 구하는 MOD
특정 수를 나눈 나머지를 구합니다. 결과는 나누는 수와 같은 부호를 갖게 되며, 구문은 다음과 같습니다.

        MOD(number,divisor)

▶ Number : 나머지를 구할 수입니다.
▶ Divisor : 나누는 수로서 Divisor가 0이면 #DIV/0! 오류값을 표시합니다.

예를 들어 MOD(3, 2) = 1, MOD(3, -2) = -1이 나오게 됩니다.

3-6> 나눈 몫을 구하는 QUOTIENT
QUOTIENT 함수는 MOD와 반대되는 역할로서, 나눗셈의 몫을 구합니다. 나눗셈을 하고 나머지를 버릴 때 이 함수를 주로 사용하며 구문은 다음과 같습니다.

        QUOTIENT(numerator,denominator)

▶ Numerator : 피제수
▶ Denominator : 제수

즉, QUOTIENT(5, 2) = 2를, QUOTIENT(4.5, 3.1) = 1을 결과값으로 나타내게 됩니다.

3-7> 반올림을 해주는 ROUND
ROUND 함수는 숫자를 지정한 자릿수로 반올림 해줍니다.

        ROUND(number,num_digits)

▶ Number : 반올림할 수입니다.
▶ Num_digits : 반올림할 Number의 자릿수입니다. Num_digits가 0보다 크면 숫자는 지정한 소수 자릿수로 반올림되며, Num_digits가 0이면 가장 가까운 정수로 반올림됩니다. 또 Num_digits가 0보다 작으면 소수점 왼쪽에서 반올림됩니다.

3-8> 수를 올릴 때는 ROUNDUP
ROUNDUP 함수는 0에서 먼 방향으로 수를 올림합니다.

         ROUNDUP(number,num_digits)
▶ Number : 올림할 실수입니다.
▶ Num_digits : 올림할 자릿수입니다.

ROUNDUP은 항상 숫자를 올림하는 것을 제외하고는 ROUND와 유사하게 사용됩니다.

3-9> 수를 올릴 때는 ROUNDDOWN
ROUNDDOWN 함수는 0에 가까운 방향으로 내림하게 됩니다.

         ROUNDDOWN(number,num_digits)
▶ Number : 내림을 할 실수입니다.
▶ Num_digits : 내림을 할 자릿수입니다.

다음은 ROUND와 ROUNDUP, ROUNDDOWN 함수가 사용되는 예입니다.

이 함수들은 데이터 구조를 변경해서 다른 함수와 중첩될 때 많이 사용됩니다.

3-10> 가장 가까운 정수로 내려주는 INT 함수
INT는 소숫점 이하는 버리고 가장 가까운 정수로 내림을 하는 함수입니다. INT(8.9) = 8
을, INT(-8.9) = -9를 반환하는 것이지요. 다음과 같은 재미있는 수식을 한 번 만들어 볼까요? 2000년까지 몇일이나 남았는지 계산을 해보겠습니다. 오늘 날짜는 NOW 함수를 배웠으니까 2000/1/1 - now()를 해보죠. 다음과 같이 결과가 나왔을 것입니다.

뭔가 이상하죠? 엑셀은 날짜를 내부적인 수치로 인식하고 계산한다고 했었습니다. NOW에 의해 나타나는 값을 INT 함수를 이용해서 정수로 만들어보죠.

깨끗하게 정수로 떨어져서 나왔죠? 이것이 INT 함수의 기능입니다.

4> 통계 함수

4-1> 빈도를 계산해주는 FREQUENCY 함수
FREQUENCY 함수는 범위 내에서 해당되는 값의 빈도를 계산하여 수직 배열로 나타냅니다. 예를 들어 성적표의 경우 지정한 점수 범위에 들어가는 시험 성적의 개수 (즉, 85점이하는 몇 명, 90점 이상은 몇 명 이런 방법으로)를 구할 수 있습니다. 또한 FREQUENCY는 배열을 사용하므로 배열식으로 입력해야 합니다. 배열식 입력 방법은 다 아시죠?

FREQUENCY(data_array,bins_array)

▶ Data_array : 빈도 수를 계산하려는 데이터 집합의 배열이나 참조 영역으로서, data_array가 비어 있으면 FREQUECNY는 0으로 이루어진 배열을 나타냅니다.
▶ Bins_array : data_array에 있는 데이터들을 그룹으로 만들기 위한 구간의 배열이나 참조 영역으로서, bins_array가 비어 있으면 FREQUECNY는 data_array의 원소 개수를 나타냅니다.

① FREQUENCY 함수는 배열 함수이므로 수식을 입력한 다음에 반드시 Ctrl+Shift+Enter 키를 쳐야 한다는 것을 명심해 두시구요, 다음 예를 통해서 살펴보도록 하겠습니다. 배열식으로 입력해야 하기 때문에 먼저 구할 셀들의 범위를 선택하는 것도 잊어서는 안되겠지요.

② 수식 입력상자에 인수들의 입력이 끝났으면 그냥 확인 버튼을 클릭하는 것이 아니라 Ctrl +Shift +Enter 키를 같이 누릅니다. 다음 그림처럼 범위를 선택한 셀들에 해당 빈도수에 해당하는 수치가 입력됩니다.

4-2> 기타 통계 함수
위의 FREQUENCY 함수 외에도 표준 편차를 구하는 STDEV, 분산을 구하는 VAR등 수학적으로 많이 사용되는 통계 함수가 많이 있습니다. 이들 함수는 비교적 사용법이 간단하므로 독자들이 직접 도움말을 참고해서 공부해 보시기 바랍니다.

5> 문자열 함수

5-1> LEFT 함수
LEFT 함수는 지정한 문자 수에 따라 문자열의 첫 문자부터 원하는 수 만큼의 문자를 표시해주면 구문은 다음과 같습니다.

LEFT(text,num_chars)

▶ Text : 추출할 문자가 들어있는 문자열입니다.
▶ Num_chars : 추출할 문자 수입니다.

예를 들어 =LEFT("Sale Price",4)를 입력하면 왼쪽부터 4자인 "Sale"을 출력해 주는 것입니다. 이 기능을 이용해서 나이를 계산하는 재미있는 식을 만들어볼까요?

채승헌의 경우 LEFT(B5,2)에 의해서 96이라는 값이 추출되고 99에서 이 값을 빼도록 설정해 두면 나이가 계산 되는 것입니다.

5-2> MID 함수
MID는 지정한 문자 수에 따라 문자열의 지정한 위치로부터 지정한 개수의 문자를 표시하며 그 구문은 다음과 같습니다.

MID(text,start_num,num_chars)

▶ Text : 추출할 문자가 들어 있는 문자열입니다.
▶ Start_num : 추출할 첫 문자의 위치로서 Text에서 첫 문자는 start_num 1입니다.
▶ Num_chars : MID가 문자열에서 표시할 문자 개수로서 Num_chars가 음수면 #VALUE! 오류값을 표시하게 됩니다.

MID 함수를 이용해서 이번에는 남녀 성별을 판단해볼까요? 주민등록번호의 2번째 자리가 1이면 남자, 2이면 여자임을 이용하는 것입니다.

=IF(MID(B3,8,1)="1","남자","여자")라는 식에 의해서 주민등록번호 8번째 자리부터 1자만 출력받아 IF 함수에 의해 남자 또는 여자로 표시되게 만든 것입니다. 갈수록 엑셀이 대단하다는 생각이 들죠?

5-3> TEXT 함수
TEXT 함수는 값을 값을 지정한 표시 형식의 텍스트로 변환할 때 사용하며 구문은 다음과 같습니다.

TEXT(value,format_text)

▶ Value : 수치, 수치 값으로 계산될 수식, 수치 값이 들어있는 셀에 대한 참조영역입니다.
▶ Format_text : 셀 서식 대화 상자의 표시 형식 탭에 있는 범주 상자의 텍스트 표시 형식입니다. Format_text에 별표(*)는 포함될 수 없고 일반 표시 형식은 사용할 수 없습니다.

한가지 유의할 것은 format_text 자리에는 사용자가 임의로 지정하는 형식을 넣는 것이 아니라 앞에서 배웠던 사용자 정의서식에서 배웠던 서식코드를 이용하는 것입니다.

다음 예를 볼까요?

"DDDD"라는 형식에 맞춰서 B2셀의 99-08-23이라는 값이 Monday로 표시되는 것입니다.

5-4> 빈문자를 제거하는 TRIM 함수
TRIM 함수는 단어 사이에 있는 한 칸의 공백을 제외하고 텍스트의 공백을 모두 삭제하는 기능을 가지고 있습니다. 다른 응용 프로그램에서 받은 텍스트에 불필요한 공백이 있을 때 TRIM을 사용해서 간단하게 제거할 수 있습니다. 주로 다른 함수와 중첩해서 사용되며, 사용자의 잘못된 입력으로 인한 오류를 줄일 수 있습니다.
=TRIM(" 1 사분기 매출 ")은 "1 사분기 매출" 이라는 값으로 반환됩니다.

6> 찾기와 참조 함수 - INDEX 함수
찾기와 참조함수는 앞에서 VLOOKUP, HLOOKUP, MATCH 등과 같은 중요한 함수들을 다 예로 들었기 때문에 여기서는 INDEX 함수에 대해서만 알아보도록 하겠습니다.

INDEX 함수는 표나 범위의 값이나 값에 대한 참조 영역을 구해주는데, 구문은 다음과 같습니다.

INDEX(array,row_num,column_num)
▶ Array : 셀 범위나 배열 상수입니다.
▶ Row_num : 값을 구하려는 배열의 행을 선택합니다

E17 셀에 =INDEX(G3:G13,MATCH(A17,B3:B13,0)) 와 같은 수식을 입력했습니다. 지금은 A17셀에 데이터가 없기 때문에 #N/A 라는 에러가 생겼습니다. 하지만 A17 셀에 아무 학생의 이름을 입력하게 되면, 그 학생의 평균 점수가 출력될 것입니다.

이름을 입력하면 평균점수가 자동으로 출력될 것입니다.
앞에서 MATCH 함수를 배울 때 그 기능이 배열에서 몇 번째인지를 알려준다고 했었습니다. 따라서 MATCH(A17,B3:B13,0) 수식에 의해서 A17에 들어온 학생이 표에서 몇 번째인지를 알게 됩니다. 만약 보기에서처럼 김선하가 입력되었다면 11이라는 값이 반환되겠죠. 그럼 결국 위의 식은 =INDEX(G3:G13,11)처럼 되는 것입니다. G3:G13 중에서 11번째 해당하는 것은? 네, 95라는 값이 최종적으로 나타나게 되는 것이지요. 조금 복잡한 듯 하지만 두 함수의 원리를 그대로 보여주는 좋은 예입니다.

물론 에러메시지도 나타나지 않게 할 수 있고, 조금 더 간편하게 작업을 자동화시킬 수 있습니다. 그 내용에 대해서는 뒤에서 다시 배우게 됩니다.

7> 재무함수

7-1> FV 함수
FV(Future Value)는 대표적인 재무함수의 하나로 말 그대로 미래 가치를 계산할 때 사용됩니다. 이와 비슷한 함수는 PV(Present Value)가 있지요. 여기에서 가치란 어떤 투자에 대한 가치를 말합니다. 쉽게 생각해서 일정한 이율을 가진 은행에 현재 얼마씩 몇 년간 예금을 하면 얼마가 될까? 와 같은 계산을 할 때 사용되며 구문은 다음과 같습니다.

FV(rate,nper,pmt,pv,type)

▶ Rate : 기간 당 이율입니다.
▶ Nper : 납입 횟수입니다.
▶ Pmt : 정기적으로 적립하는 금액으로서 전 기간 동안 변경되지 않습니다. 일반적으로 Pmt에는 기타 비용과 세금을 제외한 원금과 이자가 포함됩니다. Pmt를 생략하면 Pv 인수를 반드시 포함해야 합니다.
▶ Pv : 현재 가치 또는 앞으로 지불할 일련의 납입금의 현재 가치를 나타내는 총액으로서 Pv를 생략하면 0으로 간주하며 이 경우 Pmt 인수를 반드시 포함해야 합니다,
Type : 0또는 1로 납입 시점을 나타냅니다. Type을 생략하면 0으로 간주합니다.

FV 함수를 이용할 때는 주의할 점이 있는데, 이율과 납입회수를 지정할 때는 일치하는 단위를 사용해야 한다는 것입니다. 12% 연이율로 4년 만기 대출금에 대한 월 상환액을 계산하려면 이율로 12%/12, 납입회수는 4*12를 사용하면 됩니다. (12개월로 단위를 일치시킨 것을 유심히 봐두시기 바랍니다.) 또한 모든 인수에 대해 저축금과 같이 지불하는 금액은 음수로 표시하며 배당금과 같이 받을 금액은 양수로 표시됩니다.

지금부터 1년 후의 특별한 일로 저축을 한다고 가정을 해봅시다. ￦100,000를 저축 계좌에 예금하고 연이율 6%가 매월 복리로 계산되어 적용됩니다. 월 이율은 6%/12 또는 0.5%입니다. 앞으로 12달 동안 매월 초에 ￦10,000를 예금한다면 12달 후에 계좌에 예금된 돈은 얼마나 될까요? 다음 그림은 이 문제를 해결하는 예입니다.

7-2> PMT 함수
PMT 함수는 정기적으로 불입하고 일정한 이율이 적용되는 대출에 대해 매회 불입액을 계산할 때 사용합니다. 사용되는 인수는 FV 함수에서와 동일합니다.

PMT(rate,nper,pv,fv,type)

연이율 8%의 대출금 ￦1,000,000을 10달 동안 상환할 때 월 상환액은 얼마가 될까요?
=PMT(8%/12, 10, 1000000)라는 식을 입력하면 값이 -103,700이 나오죠? 따라서 매월 ￦103,700씩 납입하면 되는 것입니다.

함수는 여기까지 끝내겠습니다. 아직 데이터베이스 함수는 다루지 않았는데, 데이터베이스 함수는 엑셀의 데이터베이스 기능을 알아야만 이해할 수 있기 때문에 뒤에서 다시 다루기로 하겠습니다.

어려운 함수를 모두 알려고 할 필요는 없으며, 자신에게 도움이 되는 함수들을 집중적으로 반복 연습하는 것이 더 효과적입니다. 함수 모음집을 활용해서 자신에게 꼭 필요한 함수들을 찾아서 공부하시기 바랍니다.

※ Tip - 시트의 에러값 숨기기
앞에서 엑셀에서의 에러메시지에 관해서는 알아봤습니다. 이번에는 에러메시지가 나타나지 않도록 함수를 조합해볼까요? 엑셀의 워크시트 함수에 ISERROR라는 함수가 있습니다. 이 함수는 인수로서 각종 에러메시지를 받게 됩니다. 만약 에러메시지가 인수로 들어오면 참값을, 그렇지 않으면 거짓 값을 되돌리는 것이죠.

에러 메시지를 출력하지 않기 위해서는 IF 함수와 ISERROR 함수를 다음처럼 조합하면 됩니다.

=IF(ISERROR(수식),"",수식)

즉, 에러가 발생하면 ""에 의해서 아무 것도 표시되지 않고, 발생하지 않으면 원래 수식을 그대로 실행하게 되는 것입니다. 괜찮은 방법이죠?

이전(MAYCH 함수로 내신등급 구하기) | 다음(엑셀 함수 목록 총정리)