컴퓨터활용능력 1 급필기 (2014 년대비 ) [ 엑셀총정리 (2)] 구분 통계 주요 AVERAGE, MAX, MIN, RANK, VAR, STDEV, COUNT, MEDIAN, MODE, AVERAGEA, LARGE, SMALL, COUNTA, COUNTBLANK, COUNTIF, GEOMEAN, HARMEAN, PERCENTILE, FREQUENCY, AVERAGEIF, AVERAGEIFS, COUNTIFS, MAXA 재무 FV, PV, NPV, PMT, AMORDEGRC, AMORLINC, DB, DDB, SLN, SYD SUM, ROUND, ROUNDUP, ROUNDDOWN, ABS, INT, SUMIF, RAND, 수학및삼각 MOD, FACT, SQRT, PI, EXP, POWER, TRUNC, PRODUCT, RANDBETWEEN, MDETERM, MINVERSE, MMULT, SUMPRODUCT, QUOTIENT, SIGN, SUMIFS * 2 급은밑줄표시함 ( 재무는출제안됨 ) 통계 > 평균 Average( 범위 ) AverageA( 범위 ) 평균 ( 숫자가있는셀수로나누기 ) 평균 ( 데이터가있는셀수로나누기 ) - A : data
통계 > 평균 ( 조건만족 ) AverageIF( 조건범위, 조건, 평균범위 ) AverageIFS( 평균범위, 조건 1 범위, 조건 1, 조건 2 범위, 조건 2,, ) 조건을만족하는데이터의평균 여러조건을모두만족하는데이터의평균 통계 > 큰값 / 작은값, 최대 / 최소 Large( 범위,N 번째 ) N 번째큰값 Max( 범위 ) 최대값 ( 숫자가있는셀중에서 ) Small( 범위,N 번째 ) N 번째작은값 MaxA( 범위 ) 최대값 ( 데이터가있는셀중에서 ) Min( 범위 ) 최소값 ( 숫자가있는셀중에서 ) - MINA() : 데이터가있는셀중에서
통계 > 개수 Count( 범위 ) CountA( 범위 ) CountBLANK( 범위 ) 숫자가들어있는셀의개수데이터가들어있는셀의개수데이터가없는셀의개수 통계 > 개수 CountIF( 범위, 조건 ) CountIFS( 범위 1, 조건 1, 범위 2, 조건 2, ) 범위에서조건에맞는셀의개수 여러범위에서조건에맞는셀의개수 * >=3 : 3 은숫자지만부등호 (>,<,>=,<=) 는문자이므로큰따옴표로묶어야함
통계 > 석차, 중간값, 최빈값 Rank ( 석차를구할대상, 범위, 정렬방식 ) Median( 범위 ) Mode( 범위 ) 범위에서대상의석차구하기 - 정렬방식 : 0 or 생략 ( 내림차순 ), 그외 ( 오름차순 ) 중간값 : 크기순으로배열후중앙에위치하는수치 - ex) 1,2,3,4,5 3 - ex) 1,2,3,4 2.5 ( 중간의 2 개값의평균 ) 최빈값 : 가장많이발생한값 - Mod( 피젯수, 젯수 ) : 나머지구하는 * 순위 : E2 =RANK(D2,$D$2:$D$5) 절대참조 ( 다른석차를채우기로계산 ) * 중간값 : C6 =MEDIAN(C2:C5) 78, 70 의평균 * 최빈값 : B7 =MODE(B2:B5) 89이가장많이발생 강의에서 C6=MEDIAN(B2:B5) 로표기되어있으나 M EDIAN(C2:C5) 가맞습니다. #N/A : 사용할수없는값을지정했을경우 통계 > 기하평균, 조화평균, 백분위수 GeoMean( 범위 ) HarMean( 범위 ) Percentile( 범위, 인수 ) 기하평균 (Geometric Mean) : n개의양수가있을때, 이들수의곱의 n제곱근 조화평균 (Harmonic Mean) : n개의양수에대하여그역수들을산술평균한것의역수 범위에서인수번째백분위수 : 범위를 100 의부분으로분할했을때의분할량 [ 계산원리를이해할필요는없음 ] * 기하평균 : B6 =GEOMEAN(B2:B5) * 조화평균 : B7 =HARMEAN(B2:B5) * 70% 위치의값 B8 =PERCENTILE(B2:B5,70%)
통계 > 빈도수, 분산, 표준편차 Frequency( 전체범위, 구간범위 ) Var( 범위 ) StDev( 범위 ) 빈도수 : 구간범위에대한전체범위요소들의빈도수분산 (Variance) 표준편차 (Standard Deviation) * Frequency ( 입력주의 ) : 입력범위를먼저선택하고배열수식으로입력 1) 입력범위 (B10:B13) 선택 2) =FREQUENCY(A2:A9,B2:B4) 입력 3) Ctrl+Shift+Enter 입력 ( 그냥 Enter 입력하면안됨 ) 배열수식으로입력하지않으면 B10 셀에 1 만입력됨 * 분산 : C2 =VAR(A2:A9) * 표준편차 : D2 =STDEV(A2:A9) 재무 (1 급만출제됨 )> 미래가치 ( 적금 ) [ 엑셀총정리 (2) - 재무 ] FV( 이자, 기간, 금액, 현재가치, 납입시점 ) 미래가치 (Future Value): 만기금액 ( 원금 + 복리이자 ) - 이자 ( 이율 ) : 연단위이므로 12로나눔 B4/12 - 기간 : 연단위이므로 12를곱함 B5*12 - 금액 : 결과값이양수로나오도록음수로입력함 -B3 - 현재가치 값이없음 - 납입시점 : 1이면매월초, 0 또는생략하면매월말 값이없음 * 만기금액 B6 =FV(B4/12,B5*12,-B3) 이자기간금액 재무 : 계산원리를이해할필요는없음 형식을알아야함
재무 > 현재가치 ( 연금 ) [ 엑셀총정리 (2) - 재무 ] PV( 이자, 기간, 금액, 미래가치, 납입시점 ) 현재가치 (Present Value): 미래가치를현재의것으로계산한값 - 이자 ( 이율 ) : 연단위이므로 12로나눔 B4/12 - 기간 : 연단위이므로 12를곱함 B5*12 - 금액 : 결과값이양수로나오도록음수로입력함 -B3 - 미래가치, 납입시점 값이없음 * 현재가치 B6 =PV(B4/12,B5*12,-B3) 이자기간금액 연급보험가격이 3,306,766 미만일경우유리한투자 재무 > 대출납입금 [ 엑셀총정리 (2) - 재무 ] PMT( 이자, 기간, 금액, 불입후잔금, 납입시점 ) 월대출납입금 ( 원금 + 이자 ) - PayMenT : 납입 - 이자 ( 이율 ) : 연단위이므로 12로나눔 B4/12 - 기간 : 연단위이므로 12를곱함 B5*12 - 금액 ( 대출원금 ) : 결과값이양수로나오도록음수로입력함 -B3 - 불입후잔금, 납입시점 : 값이없음 * 월납입액 B6 =PMT(B4/12,B5*12,-B3) 이자기간금액
재무 > 순현재가치 ( 투자 ) [ 엑셀총정리 (2) - 재무 ] NPV( 할인율, 금액 ) 순현재가치 (Net Present Value): 투자의현재가치 - 할인율 : 일반적인시장에서얻을수있는수익율 ( 시장이자율 ) 재무 > 감가상각액 ( 정액법, 정율법 ) [ 엑셀총정리 (2) - 재무 ] SLN( 취득액, 잔존가치, 수명년수 ) 정액법 (Straight LiNe) 에의한감가상각액 - 정액법 : 감가총액을수명년수로균등하게나누는방법 - 감가상각액 : 시간이흐르면서감소되는고정자산가치의감소액 - 잔존가치 : 수명이끝나고남아있는가치 DB( 취득액, 잔존가치, 수명년수, 년차, 월수 ) 정률법 (Fixed Percentage of Declining-Balance) 에의한지정한기간동안감가상각액 - 정률법 : 일정한비율로공제 - 년차 : 감가상각을계산할기간, - 월수 : 첫해의개월수 * 정액법 : B5 =SLN(A2,A3,A4) * 정율법 : B6 =DB(A2,A3,A4,2,5) - 년차 : 작년구매 (2 년차 ) - 월수 : 8 월 ~12 월 (5 개월 )
재무 > 감가상각액 ( 이중체감법 ) [ 엑셀총정리 (2) - 재무 ] DDB( 취득액, 잔존가치, 수명년수, 년차, 잔액감소비율 ) 이중체감법 (Double-Declining-Balance) 에의한감가상각액 - 이중체감법 : 처음기간의감각상각비율이가장크고다음기간부터는점점작아짐 - 잔액감소비율 : 생략하면 2로간주됨 - 주의 : 수명년수와년차는동일한단위여야함 ( 수명년수가일단위일경우년차도일단위가됨 ) =DDB(A2,A3,A4*365,1) 일 =DDB(A2,A3,A4*12,1) 월 =DDB(A2,A3,A4,1) 년 재무 > 감가상각액 ( 연수합계법 ) [ 엑셀총정리 (2) - 재무 ] SYD( 취득액, 잔존가치, 수명년수, 년차 ) 연수합계법 (Sum-of-Years Digits) 에의한감가상각액 - 연수합계법 : 정률법과유사 ( 상각비의체감속도완화 ) =SYD(A2,A3,A4,1) =SYD(A2,A3,A4,10)
재무 > 감가상각액 ( 회계기간 ) [ 엑셀총정리 (2) - 재무 ] AmorLinc( 취득액, 취득일, 첫째회계기간의마지막날짜, 잔존가치, 기간, 감가상각률 ) AmorDegrc( 취득액, 취득일, 첫째회계기간의마지막날짜, 잔존가치, 기간, 감가상각률 ) 매회계기간의감가상각액 매회계기간의감가상각액 -AmorLinc 와유사 강의에서 =AMORLINC(A2,A3,A4,A5,A6,A7,A7) 로되어있으나 =AMORLINC(A2,A3,A4,A5,A6,A 7) 가맞습니다. =AMORLINC(A2,A3,A4,A5,A6,A7) =AMORDEGRC (A2,A3,A4,A5,A6,A7) 강의에서 =AMORDEGRC(A2,A3,A4,A5,A6,A 7,A7) 로되어있으나 =AMORDEGRC(A2,A3,A4, A5,A6,A7) 가맞습니다. 수학 / 삼각 > 기본 [ 엑셀총정리 (2) - 수학 / 삼각 ] Sign( 숫자 ) Abs( 숫자 ) 부호값 ( 양수 1,0 0, 음수 -1) 절대값 (Absolute) Fact( 숫자 ) SqRt( 숫자 ) 계승값 (Factorial) 양의제곱근 (Square Root) Int( 숫자 ) 가장가깝고작은정수 (Integer) Pi() 원주율값 (3.14 )
수학 / 삼각 > 합계 [ 엑셀총정리 (2) - 수학 / 삼각 ] Sum( 범위 ) SumIF( 조건범위, 조건, 합계범위 ) SumIFS( 합계범위, 조건1범위, 조건1, 조건2범위, 조건2, ) 조건이추가될수있으므로 합계범위 인수를먼저입력함 합계조건에맞는합계여러조건에맞는합계 수학 / 삼각 > 반올림 [ 엑셀총정리 (2) - 수학 / 삼각 ] Round( 숫자, 자릿수 ) 반올림 RoundDown( 숫자, 자릿수 ) 내림 RoundUp( 숫자, 자릿수 ) 올림 Trunc( 숫자, 자릿수 ) 절삭 TRUNCation : 끝을잘라냄
수학 / 삼각 > 나눗셈, 거듭제곱, 난수 [ 엑셀총정리 (2) - 수학 / 삼각 ] Mod( 피젯수, 젯수 ) Quotient( 피젯수, 젯수 ) Power( 밑, 지수 ) Exp( 지수 ) Rand() RandBetween( 숫자1, 숫자2) 나머지몫 (Quotient) 밑^지수 거듭제곱 (Power) 자연로그 e(2.71 ) 를거듭제곱한값 (e^지수) - 지수 : Exponent 0과 1 사이의난수 (Random) 발생숫자1과숫자2 사이의난수 (Random) 발생 수학 / 삼각 > 곱하기, 배열 [ 엑셀총정리 (2) - 수학 / 삼각 ] Product( 범위 ) SumProduct( 배열 1, 배열 2, ) MDeterm( 배열 ) 곱하기 (Product) 배열에대응하는요소를모두곱하고그곱의합계 - { } 는배열구분기호 - 쉼표 (,) 는열구분기호, 세미콜론 (;) 은행구분기호 배열의행렬식 (Determinant): 계산과정은몰라도됨 - 행렬 : Matrix( 가로줄을행, 세로줄을열이라고함 ) - 행과열수가같아야함
수학 / 삼각 > 행렬 [ 엑셀총정리 (2) - 수학 / 삼각 ] MInverse( 배열 ) MMult( 배열 1, 배열 2) 배열의역행렬 (Inverse) : 계산과정은몰라도됨 - 배열수식으로입력 배열 1 과배열 2 의행렬곱 (Multiplication) 을계산 - 배열수식으로입력 [A5:B6] 범위선택후배열수식입력 [D5:E6] 범위선택후배열수식입력