컴퓨터 및 정보통신 - 함수 - 건국대 경영대학 박성식 sungsikpark@nate.com

강의 요약 엑셀에서 지원되는 다양한 함수들의 사용법을 익힌다. 논리 함수 텍스트 함수 날짜 / 시간 함수 재무 함수 2

엑셀에서 지원되는 다양한 함수들 함수의 일반적인 모습과 사용법 Function_name ( a, b, c ) 예를 들어서 합계를 구하는 함수는 SUM이란 이름이고, 평균을 구하는 함수는 AVERAGE란 이름이다. 괄호 안에 있는 a, b, c는 함수에 입력해 주어야 하는 값을 의미한다. 일반적으로는 함수마다 입력해야만 하는 값의 개수가 다르다. 3

함수의 형태 함수 구문 함수도 수식의 일종이므로 정해진 형태로 사용해야 한다. 함수는 등호(=)로 시작하여 함수 이름을 입력하고 괄호 안에 인수를 지정한다. ① 등호(=) : 함수도 수식의 일종이므로 수식임을 표시 ② 함수 이름 : 원하는 계산을 위한 함수이름 ③ 괄호() : 인수가 들어가는 공간 ④ 인수 : 지정된 계산을 위해 함수에 건네주는 데이터 ⑤ 쉼표(,) : 여러 개의 인수를 사용하는 경우, 인수와 인수를 구분 =함수이름(인수1, 인수2, 인수3...) 

함수 마법사 사용하기

1. 자동합계를 계산하는 함수 SUM AVERAGE 형식 : =SUM(셀주소,셀주소..) 예) =SUM(D6:F6) 지정한 데이터 영역의 모든 숫자의 합계 AVERAGE 형식 : =AVERAGE(셀주소,셀주소…) 예) =AVERAGE(D6:F6) 지정한 데이터 영역의 모든 숫자의 평균

엑셀에서 지원되는 다양한 함수들 함수의 일반적인 모습과 사용법 총점, 평균, 개인 평균을 구해보자 합계 구하기 = SUM (C3:C6) 또는 = SUM (C3,C4,C5,C6) 또는 = SUM (C3, C4:C5,C6) 자동합계를 이용한 방법 총점, 평균, 개인 평균을 구해보자 7

엑셀에서 지원되는 다양한 함수들 논리 함수 논리 함수란 참(TRUE)과 거짓(FALSE)을 다루거나 참 또는 거짓의 결과를 내는 함수를 말한다. 8

엑셀에서 지원되는 다양한 함수들 논리 함수 9

엑셀에서 지원되는 다양한 함수들 논리 함수 L2셀의 제목은 “IF문”이라고 표기하고, 셀 포인터를 L3셀로 이동시킨다. 그리고, 다음을 입력한다. =IF(K3>=85,"매우 우수","우수") 입력을 마쳤으면, L3의 내용을 채우기 핸들을 이용해서 L3~L6까지 채워준다. 그러면, 다음과 같은 결과가 나오게 될 것이다. 10

엑셀에서 지원되는 다양한 함수들 논리 함수

엑셀에서 지원되는 다양한 함수들 텍스트 함수 – LEFT() , RIGHT() , MID() 이 함수들은 문자열에서 특정 부분만 추출해서 사용하고 싶을 때, 사용할 수 있는 함수들이다. LEFT()함수는 왼쪽에서부터 지정한 문자수만큼 추출해주고, RIGHT()함수는 오른쪽에서부터 지정한 문자수만큼 추출해준다. MID()함수는 임의의 위치에서부터 지정한 문자수만큼 추출해준다. LEFT()함수와 RIGHT()함수의 사용법은 다음과 같다. = LEFT(text, num_chars) = RIGHT(text, num_chars) text에는 추출할 문자가 들어있는 원래의 텍스트(문자열) 또는 셀 주소를 입력한다. num_chars에는 추출할 문자수를 입력한다. 0보다 크거나 같아야 한다. 만약 text의 길이보다 큰 값이 입력되면 텍스트 전체가 반환된다. 생략하면 1로 간주한다. MID()함수의 사용법은 다음과 같다. = MID(text, start_num, num_chars) 12 12

엑셀에서 지원되는 다양한 함수들 텍스트 함수 – LEFT() , RIGHT() , MID() 13

엑셀에서 지원되는 다양한 함수들 =IF(MID(A4,8,1)="1","남자","여자") 텍스트 함수 – LEFT() , RIGHT() , MID() Quiz : 다음 그림에서 주민등록번호 8번째 위치한 값이 “1” 이면 성별을 “남자”, “2”이면 성별을 “여자”라고 표시되도록 E셀을 채워 보시오.. (IF와 MID 함수 이용) =IF(MID(A4,8,1)="1","남자","여자") 14

엑셀에서 지원되는 다양한 함수들 최대값, 최소값 함수 – MAX() , MIN() 입력한 숫자 들 중에서 또는 지정한 영역 중에서 최대값 또는 최소값을 구하기 위한 함수들이다. 사용법은 다음과 같다. = MAX(number1, number2, …) , = MIN(number1, number2, …) number1, number2는 숫자를 의미하며, 30개까지 따로 입력할 수 있다. number 대신에 숫자가 들어 있는 셀 영역을 입력하면, 그 영역에서 최대값(최소값)을 구해 주게 된다. 단, 숫자가 아닌 값은 0으로 간주 15

엑셀에서 지원되는 다양한 함수들 순위 찾기 함수 – RANK() 주어진 수의 목록 가운데서 특정한 수의 순위를 구하는 함수다. 목록의 수들과 특정한 수의 상대적인 크기를 비교해서 순위를 구하게 된다. 사용법은 다음과 같다. = RANK(number, ref, order) number에는 특정 수를 입력하거나 숫자가 들어 있는 특정 셀을 지정해야 한다. ref는 수의 목록을 의미하며, 셀 영역을 지정해 주거나 참조 영역의 이름을 입력해야 한다. order는 내림차순 순위인지 오름차순 순위인지를 지정하는 값이다. order값이 0이거나 생략이 되면 내림차순 순위를 구하게 되고, order값이 0이 아닌 다른 수이면 오름차순 순위를 구하게 된다. 16

절대참조를 이용한 방법

엑셀에서 지원되는 다양한 함수들 날짜 / 시간 함수 엑셀에서의 날짜 처리 방식 엑셀은 날짜를 순차적인 일련번호 형태로 저장하고 다루는 것이다. 1900년 1월 1일이 정수 1이 되고 이를 기준으로 해서 하루에 1씩 증가하면서 번호가 정해진다. 예를 들어서, 2008년 1월 1일은 39448이 되는 것이다. 왜냐하면, 1900년 1월 1일에서부터 39448일이 지났기 때문이다. 엑셀에서의 시간 처리 방식 시간은 0에서 1사이의 실수로 처리된다. 예를 들어서, 정오 (12:00)는 실제 숫자로는 0.5가 된다는 것이다. 사용자가 셀에 직접 “12:00”라고 입력하더라도, 엑셀은 내부적으로 그 셀의 값을 0.5로 저장한다. 처음에는 “12:00” 또는 “12:00 PM”으로 표시가 되지만, 사용자가 셀의 표시형식을 일반(또는 숫자)으로 변경을 하면 내부적으로 저장하고 있는 숫자 0.5가 표시 된다. 18

엑셀에서 지원되는 다양한 함수들 날짜 / 시간 함수 =NOW() 오늘 현재의 날짜와 시간이 표시 =TODAY() 현재의 날짜에 해당하는 순차 일련 번호를 반환한다. NOW()함수와 유사. 단, TODAY()함수는 시간에 대한 값은 반환치 않음. =DATE (year, month, day) 특정 날짜를 나타내는 순차 일련 번호를 반환한다. 함수가 입력되기 전의 셀의 서식이 무시되고, 날짜 서식으로 변경된다. year : 연도를 나타내는 숫자. month : 월을 나타내는 숫자. 12보다 크면, 12를 빼주고 연도가 1증가. day : 일을 나타내는 숫자. 말일 보다 크면, 말일을 빼주고 월이 1증가. =TIME (hour, minute, second) 특정 시간에 대한 실수를 반환한다. 함수가 입력되기 전의 셀의 서식이 무시되고, 시간 서식으로 변경된다.사용법 hour : 시간을 나타내는 숫자. minute : 분을 나타내는 숫자. second : 초를 나타내는 숫자. 19

날짜 / 시간 함수

범주 날짜/시간 형식 =DATEDIF(Start_date, End_date, Interval) 인수  * Start_date : 기간을 구할 시작 날짜  * End_date : 기간을 구할 마지막 날짜  * Interval : 기간을 구할 단위. 연 수나 개월 수, 일 수 등을 지정합니다.     Y : 연 수로 구합니다.     M : 개월 수로 구합니다.     D : 일 수로 구합니다.     YM : 시작일과 종료일을 같은 해로 간주하고 개월 수로 구합니다.     YD : 시작일과 종료일을 같은 해로 간주하고 일 수로 구합니다.

오늘 날짜 자동으로 구하기 1 2 3

주민등록번호로 생년월일과 나이 구하기 1 3 2

=DATEDIF(DATE(LEFT(E3,2), MID(E3, 3, 2), MID(E3, 5, 2)), TODAY(), "Y") 4 5

DATEDIF =datedif(시작일, 현재일, y,m,d 중 하나를 넣으면 되요) year의 Y, month의 M, day의 D 하지만 주의할 건, "   " 큰따옴표 필수(문자 취급), #NAME?

2. 반올림 함수 ROUND 형식 : = ROUND(셀주소, 반올림할 자릿수) 예) =ROUND(H6,2) 지정한 데이터 영역에 입력된 숫자를 반올림 처리 반올림할 자릿수가 정수일 때는 음수(-1,-2..)로 표현 100단위는 -2, 1000단위는 -3 입력 소수점 이하 반올림은 양수(1,2,3 ..)로 표현

반올림 관련 함수 ROUND : 지정한 자릿수 다음 숫자가 5이상이면 값을 올리고 5미만이면 값을 내린다. ROUNDUP : 지정한 자릿수 다음 숫자가 1이상이면 값을 올린다. ROUNDDOWN : 지정한 자릿수 다음 숫자는 무조건 내린다. 범주 수학/삼각 형식 =ROUND(Number, Num_digits)  =ROUNDUP(Number, Num_digits)  =ROUNDDOWN(Number, Num_digits) 인수  * Number : 반올림하거나 올림, 또는 내림할 숫자  * Num_digits : 반올림하거나 올림, 또는 내림할 자릿수

급여 항목별로 반올림, 올림, 내림 계산하기 1 2 3 4 5

3. 순위 함수 RANK 형식 : =RANK(셀주소, 참조영역, 순위결정방법) 예) =RANK(C4,$C$4:$C$19,0) 순위결정 방법 0 -> 내림차순 순위결정 방법 1 -> 오름차순 RANK.AVG(10.5)와 RANK.EQ (각각10)는 공동순위 표현 방식에서 차이(2010버전)

4. 개수 계산 함수 COUNT COUNTA COUNTIF 형식 : =COUNT(셀주소, 셀주소…) 지정한 데이터 영역에서 숫자 데이터가 입력되어 있는 셀의 개수 COUNTA 형식 : =COUNTA(셀주소, 셀주소..) 지정한 데이터 영역에서 숫자와 문자를 포함한 모든 데이터가 입력되어 있는 셀의 개수 COUNTIF 형식 : =COUNTIF(셀서식, 조건) 지정한 데이터 영역에서 조건에 맞는 데이터가 입력된 셀의 개수 출력

개수를 구하는 함수 COUNT : 숫자 데이터가 입력되어 있는 셀의 개수를 구한다. COUNTA : 숫자나 문자를 가리지 않고 데이터가 입력되어 있는 모든 셀의 개수를 구한다. COUNTBLANK : 아무것도 입력되어 있지 않는 빈 셀의 개수를 구한다. COUNTIF : 지정한 조건에 부합하는 셀의 개수를 구한다. 범주 통계 형식 =COUNT(Value1, Value2,...)  =COUNTA(Value1, Value2,...)  =COUNTBLANK(Range)  =COUNTIF(Range, Criteria) 인수 * Value1, Value2,... : 개수를 구하고자 하는 셀 값이 입력되어 있는 셀 주소나 셀 범위 * Range : 검사하고자하는 셀 범위 * Criteria : 개수를 구하고자 하는 조건

몇 명이 회식비를 내지 않았나? 개수를 계산하는 함수를 사용하여 회식비 납부자수와 미납자 수, 전체 직원 수의 성별 인원수 등을 계산. 1 2 3 4 5

6 7 8 10 9

5. 중첩 함수 IF IF, AND IF, OR 형식 : =IF(조건, 참값, 거짓값) 조건에 충족하면 참값, 조건에 불 충족하면 거짓 값을 반환 IF, AND 형식 : =IF(AND(조건,조건..),참값,거짓값) 예) =IF(AND(A4>=90,B4>=90,C4>=90),”T”,”F”) 두 개 이상의 조건을 모두 충족시키면 참값, 불 충족하면 거짓값 반환 IF, OR 형식 : =IF(OR(조건,조건..),참값,거짓값) =IF(OR(A4>=90,B4>=90,C4>=90),”T”,”F”) 두 개 이상의 조건 중 하나라도 만족시키면 참값, 모든 조건을 불 충족하면 거짓값 반환

조건에 따라 다양한 값 찾는 함수 범주 찾기/참조 영역 형식 = LOOKUP(Lookup_value, Array)  =VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)  =HLOOKUP(Lookup_value, Table_array, Row_index_num, Range_lookup) 인수  * Lookup_value : 검색할 데이터.    * Array : LOOKUP 함수에서 조건과 조건에 따른 결과값  * Table_array : Lookup_value에서 지정한 데이터를 만족하는 값이 들어있는 참조 영역  * Col_index_num : Table_array에서 지정한 참조 영역 중, 조건에 만족하는 행에서 표시하려는 데이터가 있는 열 번호 * Row_index_num : Table_array에서 지정한 참조 영역 중, 조건에 만족하는 열에서 표시하려는 데이터가 있는 행 번호  * Range_lookup : 조건과 근사값을 찾으려면 ‘1’ 이나 ‘TRUE'를, 조건과 정확하게 일치하는 값을 찾으려면 ’0‘이나 ’FASLE'를 지정.

엑셀에서 지원되는 다양한 함수들 참조 / 찾기 영역 함수 – VLOOKUP() , HLOOKUP() A2:B6 선택 후 [이름상자]에서 “제품”이라고 입력후 [Enter]키를 친다. 이름상자

엑셀에서 지원되는 다양한 함수들 참조 / 찾기 영역 함수 – VLOOKUP() , HLOOKUP() 메뉴의 [수식]-[이름 관리자]를 클릭하고 "제품"의 [참조 대상]의 범위가 제대로 설정되었는지 확인해 본다. 제대로 설정되었으면 [닫기] 버튼을 클릭한다.

엑셀에서 지원되는 다양한 함수들 참조 / 찾기 영역 함수 – VLOOKUP() , HLOOKUP() D2셀에 가서 다음과 같이 입력해본다. = VLOOKUP(“바지”, 제품, 2) 아래와 같은 결과가 나올 것이다. “제품”이라고 이름 붙여진 범위에 가서, 첫째열에서 “바지”를 찾고, 그 줄에 있는 2번째 열의 값을 추출해 온 것이다. 물론 다음과 같이 입력해도 동일한 결과가 나온다. = VLOOKUP(“바지”, A2:B6, 2)

엑셀에서 지원되는 다양한 함수들 참조 / 찾기 영역 함수 – 실습 결과 화면

지역에 따라 다른 운임 자동으로 구하기 1 2 VLOOKUP 함수의 인수들 * Lookup_value : 찾고자 하는 데이터이므로 출장 지역에 대한 셀을 지정. * Table_array : 참조 영역으로 지역명과 금액이 입력되어 있는 셀 영역을 지정. * Col_index_num : 참조 영역으로 지정한 셀 영역의 두 번째 행에 금액이 입력되어 있으므로 ‘2’를 지정. * Range_lookup : 정확한 값을 찾기 위해 ’0‘이나 ’FALSE'를 입력.

3

주요 재무 관련 함수 FV 함수는 일정 이율로 일정 기간 동안 일정 금액을 투자할 경우, 얻을 수 있는 미래의 가치(Future Value)를 산정. 범주 재무 형식 =FV(Rate, Nper, Pmt, Pv, Type) 인수 * Rate : 기간 중 이자율  * Nper : 납입기간  * Pmt : 월 납입액. 지출되는 금액이므로 음수(-)로 입력합니다.  * Pv : 현재 가치. 현재 입금액이 없으면 0을 입력하거나 비워둡니다.  * Type : 납입시기. 월초에 납입하면 1을, 월말에 납입하면 0을 입력하거나 비워둡니다.

정기 적금 수령액 계산하기 - FV 1 2 3

PMT 함수는 일정 조건으로 일정 금액을 만들기 위해 필요한 지불액(Payment)을 계산. 연 5 범주 재무 형식  =PMT(Rate, Nper, Pv, Fv, Type) 인수 * Rate : 기간 중 이자율  * Nper : 납입기간  * Pv : 현재 가치. 즉, 대출액과 같이 이미 받은 금액을 입력합니다.  * Fv : 미래 가치. 모두 상환하는 경우, 생략하거나 0을 입력합니다.  * Type : 납입시기. 월초에 납입하면 1을, 월말에 납입하면 0을 입력하거나 비워둡니다.

대출금에 대한 월 불입액 계산하기 - PMT 1 2 3

FV 함수는 미래의 가치를 구하는데 비해, PV 함수는 현재의 가치(Present Value)를 계산 범주 재무 형식 =PV(Rate, Nper, Pmt, Fv, Type) 인수 * Rate : 기간 중 이자율  * Nper : 납입기간  * Pmt : 월 지급액. 음수(-)로 입력합니다.  * Fv : 미래 가치. 지정된 기간 후 취득할 금액이나 상환 금액  * Type : 납입시기. 월초에 납입하면 1을, 월말에 납입하면 0을 입력하거나 비워둡니다.

분할로 지급받을 수 있는 금액의 현재 가치 구하기 - PV 1 2 3

NPER 함수는 일정한 미래의 가치를 얻는데 걸리는 시간이나 횟수(Number of PERiods)를 계산 범주 재무 형식 =NPER(Rate, Pmt, Pv, Fv, Type) 인수 * Rate : 기간 중 이자율  * Pmt : 월 납입액. 음수(-)로 입력합니다.  * Fv : 미래 가치. 지정된 기간 후 취득할 금액이나 상환 금액  * Pv : 현재 가치. 현재 입금액이 없으면 0을 입력하거나 비워둡니다.  * Type : 납입시기. 월초에 납입하면 1을, 월말에 납입하면 0을 입력하거나 비워둡니다.

대출 받은 금액을 상환하는데 걸리는 기간 구하기 - NPER 1 2 3

RATE 함수는 이율을 계산. 물품을 할부로 구입할 때, 지정된 상환 기간과 할부금을 통해 이율이 얼마나 적용되었는지를 파악할 때 사용할 수 있다. 범주 재무 형식 =RATE(Nper, Pmt, Pv, Fv, Type) 인수 * Nper : 납입 기간  * Pmt : 월 납입액. 음수(-)로 입력합니다.    * Pv : 현재 가치. 대출금이나 상품 구입액을 입력합니다.  * Fv : 미래 가치. 지정된 기간 후 취득할 금액이나 상환 금액  * Type : 납입시기. 월초에 납입하면 1을, 월말에 납입하면 0을 입력하거나 비워둡니다.

매월 납입하는 금액 이율 구하기 - RATE 1 2 3

이메일 제출 시 제출형태 : 제목은 반드시 “학번_성명” 첨부 파일은 “학번_성명.xlsx” 이메일 : sungsikpark@nate.com 블로그 주소 : http://blog.daum.net/computer_park