제 3 장함수와배열수식 (2/2)
함수 데이터베이스함수 DSUM, DAVERAGE, DMAX, DMIN, DCOUNT, DCOUNTA, DGET 재무함수 FV, PV, NPV, IRR, PMT 텍스트함수 CONCATEATE, SUBSTITUTE, REPLACE, LEFT, RIGHT, MID, FIND 날짜 & 시간함수 TODAY, NOW, DATE, YEAR, MONTH, DAY, DAYS36 배열수식 2
6. 데이터베이스함수 특정셀과참조범위를조회하고값을반환하는함수 DSUM, DAVERAGE, DMIN, DMAX, DGET 과같이함수앞에데이터베이스함수임을나타내는 D 약자가같이표시됨 = DATABASE (Database, Field, Criteria) Database: 워크시트에서원본데이터목록의셀범위를의미 Field: 데이터베이스함수에서사용할열의항목을의미 Criteria: 찾을조건이들어있는셀범위를의미 고급필터와같이검색조건이시트에입력되어있어야함 데이터목록이나참조범위를테이블의개념으로사용 데이터목록에서개별열을 필드 로, 개별행을 레코드 로보고전체데이터목록을필드의집합으로봄 데이터목록에서데이터는항상필드 ( 열 ) 기준으로입력되어야함 3
데이터베이스함수 - DSUM 함수 데이터목록이나데이터베이스에서조건을만족하는값에대한필드 ( 열 ) 의합계를구하는함수 예제 1: 부품번호가 A1 인부품의총제조수량은? 예제 2: 제조라인 L1 또는 L2 에서생산된 A2 부품의총제조수량은? 4
데이터베이스함수 - DAVERAGE 함수 DAVERAGE 함수 예제 : 제조라인 L3 에서발생한평균불량률은? 5
데이터베이스함수 DMAX & DMIN 함수 DMAX & DMIN 함수 예제 : 부품번호 A2 의손실액중가장큰금액과가장작은금액은? 6
데이터베이스함수 DCOUNT/DCOUNTA 함수 DCOUNT/DCOUNTA 함수 예제 : 불량이 2 번이상발생한횟수는? 7
데이터베이스함수 - DGET 함수 DGET 함수 예제 : 제조라인 L3 에서발생한 A3 부품의불량률은? 데이터베이스에서조건을만족하는값이없는경우 #Value! 조건을만족하는값이여러개일경우에는 #Num! 8
7. 재무함수 - FV 함수 - Future Value 현재일정금액을특정기간동안정기적으로불입하고일정한이자율을적용하는투자에대해서만기액을계산하는함수 = FV(Rate, Nper, Pmt, Pv, Type) Rate : 해당기간동안이자율 Nper : 연간총납입기간또는횟수 Pmt : 전체기간동안변경되지않는정기적으로납입하는금액 Pv : 화폐의현재가치또는앞으로지불해야할일련의납입금의현재가치를나타내는총액. 만약 Pv 를생략하면 으로간주 Type : 납입시점을나타내는것으로 또는 1 로표현. 일경우기말, 1 일경우기초를의미하며, 생략할경우 으로간주 이자율과기간의단위를일치시켜야함 (e.g., 매월불입하면월이자율로입력하고기간도개월수로입력 ) 9
FV 함수예제 미래의내집마련을위해주택청약적금을매월말 39, 원씩적립할경우, 매년 4% 의이자율로 5 년간적립한다면 5 년후에받을수있는금액은? 1
재무함수 - PV 함수 Present Value 미래투자액에대해서지금시점에서의가치를계산하는함수 = PV(Rate, Nper, Pmt, Fv, Type) Rate : 해당기간동안이자율, Nper : 연간총납입기간또는횟수 Pmt : 전체기간동안변경되지않는정기적으로납입하는 Fv : 미래가치또는최종상환후의 Type : 납입시점을나타내는것으로 또는 1 로표현. 일경우기말, 1 일경우기초를의미하며, Type 을생략할경우 PV = FV / (1 R) n PV: 현재가치, FV : 미래가치, R: 이자율, N: 기간 11
PV 함수예제 내집마련을위해현금 2,, 원을바로지불하는방법과 5 년동안 11% 의이자율로매월말 45, 원씩을지불하는방법이있다면어떤방법이유리? 12
PV 함수예제 오랫동안연락이없던민준이가보험설계사가되어내사무실로찾아왔다. 민준이는매달 1,원씩 3년간받을수있는좋은연금상품이있다고설명하면서이를위해 3,4,만원만일시불로내면된다고설명한다. 민준이가소개한연금상품을가입하는것이좋을까? 아니면점심이나먹이고보내는것이좋을까? 13
재무함수 - NPV 함수 Net Present Value PV( 현재가치 ) 함수는현금흐름이일정기간동안동일하다는것을가정하지만, NPV(net present value, 순현재가치 ) 함수는매기간의현금흐름이일정하지않아도됨 NPV 의현금흐름은기말에이루어지며현금흐름은정기성을띤다는점을가정함 ( 현금흐름자체가비정기적인경우에는 XNPV 함수를사용 ) = NPV(Rate, Value1, Value2, ) Rate : 일정기간동안의이자율 Value1, Value2 : 지출과수입을표시하는인수로 29 개까지가능 기초투자일경우순현재가치는 NPV(Rate, ) - 기초투자액 으로계산 14
NPV 함수예제 - 투자가기말에이루어지는경우 NPV = -5,,/(1+.5) 1 + 15,,/(1+.5) 2 + 2,,/(1+.5) 3 + 25,,/(1+.5) 4 = -47,619,48 + 13,65,442 + 17,276,752 + 2,567,562 = 3,83,78 원 15
NPV 함수예제 - 투자가기초에이루어지는경우 NPV = 15,,/(1+.5) 1 + 2,,/(1+.5) 2 + 25,,/(1+.5) 3 = 14,285,714 + 18,14,59 + 21,595,94 5,,(215 년투자금 ) = 4,22,244 원 16
재무함수 - IRR 함수 Internal Rate of Return 주기적인현금흐름에대한수익률을계산하는함수 = IRR(Values, [Guess]) Value : 일정기간동안의현금흐름의참조범위 Guess : IRR 결과의근삿값으로생략할경우.1(1%) 로간주 17
재무함수 - PMT 함수 - PayMenT 일정한이자율과정기적인지불을기준으로대출금또는적금액에대한매월납입액을계산하는함수 = PMT(Rate, Nper, Pv, Fv, Type) Rate : 대출또는적금의이자율 Nper : 대출또는적금의총납입횟수 Pv : 투자에대한현재가치 Fv : 투자에대한미래가치, 대출금의경우 Type : 납입시기 ( : 기말납입, 1 : 기초납입 ) 18
PMT 함수예제 19
8. 텍스트함수 - CONCATENATE 함수 여러셀의텍스트를하나의텍스트로결합할때사용 2
텍스트함수 - SUBSTITUTE 함수 셀에입력된문자열을지정한문자 ( 열 ) 로내용을바꿀때사용 = SUBSTITUTE(Text, Old_text, New_text, [Instance_num]) Text : 바꿀문자 ( 열 ) 이포함된셀또는문자 ( 열 ) Old_text : 바꿀문자 ( 열 ) New_text : Old_text 를대체할문자 ( 열 ) Instance_num : Old_text 가여러번있을때몇번째 Old_text 를바꿀지지정하는숫자 21
텍스트함수 - REPLACE 함수 문자열에서지정한수만큼위치한문자를다른내용으로대체 = REPLACE (Old_text, Start_num, Num_chars(Num_byte), New_text) Old_text : 바꾸려는문자 ( 열 ) Start_num : Old_text 에서바꾸려는문자 ( 열 ) 의시작위치 Num_char : Start_num 부터바뀌게될문자 ( 열 ) 의수 Num_byte : Start_num 부터바뀌게될바이트의수 New_text : 새로바뀌게될문자 ( 열 ) 22
텍스트함수 - LEFT & RIGHT 함수 셀에입력된문자 ( 열 ) 의왼쪽 ( 오른쪽 ) 을기준으로지정된숫자만큼문자열을반환할때사용 = LEFT/RIGHT(Text, Num_chars) Text : 추출할문자 ( 열 ) 를포함한참조범위또는문자열 Num_chars : 추출할문자수 23
텍스트함수 - MID 함수 셀에입력된문자열에서사용자가원하는임의의위치에있는문자열을반환 = MID (Text, Start_num, Num_chars) Text : 추출할문자 ( 열 ) 를포함한참조범위또는문자열 Start_num : 추출할문자 ( 열 ) 의시작위치 Num_chars : 추출할문자수 24
텍스트함수 - FIND 함수 입력된문자 ( 열 ) 에서원하는문자 ( 열 ) 의시작위치를찾을때사용 = FIND (Find_text, Within_text, [Start_num]) Find_text : 찾으려는문자 ( 열 ) ( 대소문자구별함 ) Within_text : 찾으려는문자 ( 열 ) 가입력된셀 Start_num : Within_text 에서검색을시작하고자하는위치 ( 생략시 1 로간주 ) 25
텍스트함수 - SEARCH 함수 특정문자또는문자열이처음발견되는위치를찾을때사용 = SEARCH (Find_text, Within_text, [Start_num]) Find_text : 찾으려는문자 ( 열 ) ( 대소문자구별함 ) Within_text : 찾으려는문자 ( 열 ) 가입력된셀 Start_num : Within_text 에서검색을시작하고자하는위치 ( 생략시 1 로간주 ) 26
9. 날짜 / 시간함수 TODAY & NOW / DATE 함수 TODAY & NOW 함수 인수없이사용 DATE 함수 27
날짜 / 시간함수 YEAR & MONTH & DAY 함수 YEAR & MONTH & DAY 함수 28
날짜 / 시간함수 - DAY36 함수 1 개월을 3 일로계산해 36 일로가정하여 [Start_date] 와 [End_date] 사이의일수를계산 = DAYS36(Start_date, End_date, [Method]) Start_date : 날짜계산을위한시작하는날짜 End_date : 날짜계산을위한끝나는날짜 Method : 생략하면 FALSE 로자동으로설정되며미국방식으로계산, TRUE 는유럽방식 ( 시작일이나종료일이어떤달의 31 이면그달의 3 일처리 ) 으로설정 29
1. 배열수식 일차원수평배열 일차원수직배열 3
배열수식 이차원배열 수식을작성한후 Ctrl + Shift + Enter 키를이용하여배열수식을만들면중괄호 ({ }) 로표시됨 31
배열수식예제 (1) 이차원배열 자재주문횟수 수식맨뒤에 1 을곱하는이유는? 데이터 1 데이터 2 결과 1 1 TRUE =A2=B2 1 FALSE =A3=B3 데이터 1 데이터 2 결과 TRUE 1 1 =A6*B6 FALSE 1 =A7*B7 32
배열수식 (1) 이차원배열수식처리절차 (C2=H2) 1 ("M21"="M11") 1 FALSE 1 (C3=H2) 1 ("M11"="M11") 1 TRUE 1 1 (C4=H2) 1 ("M12"="M11") 1 FALSE 1 (C5=H2) 1 ("M21"="M11") 1 FALSE 1 SUM (C6=H2) 1 (C7=H2) 1 SUM ("M13"="M11") 1 ("M23"="M11") 1 SUM FALSE 1 FALSE 1 SUM = 1 (C8=H2) 1 ("M13"="M11") 1 FALSE 1 (C9=H2) 1 ("M21"="M11") 1 FALSE 1 (C1=H2) 1 ("M12"="M11") 1 FALSE 1 (C11=H2) 1 ("M22"="M11") 1 FALSE 1 33
배열수식예제 (2) 이차원배열 자재별총주문량 34
배열수식 (2) 이차원배열수식처리절차 (C2=H2) 25 ("M21"="M11") 25 FALSE 25 (C3=H2) 1 ("M11"="M11") 1 TRUE 1 1 (C4=H2) 15 ("M12"="M11") 15 FALSE 15 (C5=H2) 1 ("M21"="M11") 1 FALSE 1 SUM (C6=H2) 15 (C7=H2) 17 SUM ("M13"="M11") 15 ("M23"="M11") 17 SUM FALSE 15 FALSE 17 SUM = 1 (C8=H2) 22 ("M13"="M11") 22 FALSE 22 (C9=H2) 11 ("M21"="M11") 11 FALSE 11 (C1=H2) 2 ("M12"="M11") 2 FALSE 2 (C11=H2) 121 ("M22"="M11") 121 FALSE 121 35
배열수식예제 (3) 이차원배열 부산대리점의주문량합계 36
배열수식 (3) 이차원배열수식처리절차 IF(B2=" 부산 "), 25 IF( 부산 =" 부산 "), 25 IF(TRUE,25) 25 IF(B3=" 부산 "), 1 IF( 대구 =" 부산 "), 1 IF(FALSE,1) IF(B4=" 부산 "), 15 IF( 서울 =" 부산 "), 15 IF(FALSE,15) IF(B5=" 부산 "), 1 IF( 대전 =" 부산 "), 1 IF(FALSE,1) SUM IF(B6=" 부산 "), 15 IF(B7=" 부산 "), 17 SUM IF( 대구 =" 부산 "), 15 IF( 부산 =" 부산 "), 17 SUM IF(FALSE,15) IF(TRUE,17) SUM 17 = 375 IF(B8=" 부산 "), 22 IF( 서울 =" 부산 "), 22 IF(FALSE,22) IF(B9=" 부산 "), 11 IF( 광주 =" 부산 "), 11 IF(FALSE,11) IF(B1=" 부산 "), 2 IF( 대전 =" 부산 "), 2 IF(FALSE,2) IF(B11=" 부산 "), 121 IF( 춘천 =" 부산 "), 121 IF(FALSE,121) 37
배열수식예제 (4) 이차원배열 서울대리점의주문량평균 38
배열수식 (4) 이차원배열수식절차 IF(B2=" 서울 "), 25 IF( 부산 =" 서울 "), 25 IF(FALSE,25) IF(B3=" 서울 "), 1 IF( 대구 =" 서울 "), 1 IF(FALSE,1) IF(B4=" 서울 "), 15 IF( 서울 =" 서울 "), 15 IF(TRUE,15) 15 IF(B5=" 서울 "), 1 IF( 대전 =" 서울 "), 1 IF(FALSE,1) AVERAGE IF(B6=" 서울 "), 15 IF(B7=" 서울 "), 17 AVERAGE IF( 대구 =" 서울 "), 15 IF( 부산 =" 서울 "), 17 AVERAGE IF(FALSE,15) IF(FALSE,17) AVERAGE = 162.5 IF(B8=" 서울 "), 22 IF( 서울 =" 서울 "), 22 IF(TRUE,22) 22 IF(B9=" 서울 "), 11 IF( 광주 =" 서울 "), 11 IF(FALSE,11) IF(B1=" 서울 "), 2 IF( 대전 =" 서울 "), 2 IF(FALSE,2) IF(B11=" 서울 "), 121 IF( 춘천 =" 서울 "), 121 IF(FALSE,121) 39
배열수식예제 (5) 이차원배열 서울과부산대리점의주문량합계 4
배열수식 (5) 이차원배열수식절차 IF( 부산 =" 서울 "), 25 IF( 부산 =" 부산 "), 25 FALSE 25 25 IF( 대구 =" 서울 "), 1 IF( 대구 =" 부산 "), 1 FALSE FALSE IF( 서울 =" 서울 "), 15 IF( 서울 =" 부산 "), 15 15 FALSE 15 IF( 대전 =" 서울 "), 1 IF( 대전 =" 부산 "), 1 FALSE FALSE SUM IF( 대구 =" 서울 "), 15 IF( 부산 =" 서울 "), 17 OR IF( 대구 =" 부산 "), 15 IF( 부산 =" 부산 "), 17 SUM FALSE FALSE OR FALSE 17 SUM 17 = 7 IF( 서울 =" 서울 "), 22 IF( 서울 =" 부산 "), 22 22 FALSE 22 IF( 광주 =" 서울 "), 11 IF( 광주 =" 부산 "), 11 FALSE FALSE IF( 대전 =" 서울 "), 2 IF( 대전 =" 부산 "), 2 FALSE FALSE IF( 춘천 =" 서울 "), 121 IF( 춘천 =" 부산 "), 121 FALSE FALSE 41