제 3 장함수와배열수식 (1/2)
함수 기술통계함수 AVERAGE, MEDIAN, MODE, STDEV, VAR, KURT, SKEW, MAX, MIN, SUM, COUNT, STDEVP, VARP, RANK.EQ, PERCENTILE.INC, COUNTIF, LARGE, SMALL 수학 / 삼각함수 SUM, PRODUCT, SUMPRODUCT, SUBTOTAL, SUMIF 찾기 / 참조함수 VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, CHOOSE, OFFSET 논리함수 IF, AND, OR, NOT 2
1. 엑셀함수의개요 함수 (Function): 특정값 (Value) 을계산하기위해엑셀에서지원하는수식 함수구문은반드시 등호 (=) 를사용한다. 구문 : 함수에서사용하는문자열 계산 : 수식에인수를넣어계산 인수 : 함수의연산값 결과값 : 함수가산출한값 동일한함수는 7 개까지, 다른함수는 64 개까지중첩해서사용가능 =IF(AVERAGE(E3:F3)>85, MAX(E3:F3), MIN(E3:F3)) 입력가능한인수의최대수는 255 개 3
함수의구문 인수와인수사이는반드시 콤마 (,) 로분리 = SUM (3,15,B2:F5) 인수는반드시괄호로묶음 함수이름 (Function) 식을나타내는등호 (=) 4
산술및비교연산자 산술연산자 의미 계산식 산술연산자표현 ( ) 괄호 = (256 4) 128 = (256 4)*128 ^ 지수 = 4 2 = 4^2 * 곱셈 = 5 2 = 5*2 / 나눗셈 = 6 3 = 6/3 + 덧셈 = 10 + 2 = 10+2 - 뺄셈 = 10-2 = 10-2 비교연산자 의미 비교식 비교연산자표현 = 같다 A1과 A2가같다 A1=A2 > 보다크다 A1이 A2 보다크다 A1>A2 < 보다작다 A1이 A2 보다작다 A1<A2 >= 크거나같다 A1이 A2보다크거나같다 A1>=A2 <= 작거나같다 A1이 A2보다작거나같다 A1<=A2 <> 같지않다. A1과 A2는같지않다 A1<>A2 5
함수마법사 수식자동완성기능 6
함수마법사 함수마법사호출 [ 수식 ] 탭 [ 함수라이브러리 ] 그룹 [ 함수삽입 ] 함수마법사호출 Shift + F3 12 개범주의 400 여개의함수를포함 ( 표 3-2 함수의종류참조 ) 7
2. 기술통계 (Descriptive Statistics) 함수 산술평균 = AVERAGE (number1, number2, ) 최대값 = MAX (number1, number2, ) 최소값 = MIN (number1, number2, ) 표본표준편차 = STDEV (number1, number2, ) 표본분산 = VAR (number1, number2, ) 모집단의표준편차 = STDEVP (number1, number2, ) 모집단의분산 = VARP (number1, number2, ) 개수 = COUNT (number1, number2, ) 최빈수 = MODE (number1, number2, ) 왜도 = SKEW (number1, number2, ) 첨도 = KURT (number1, number2, ) 8
기술통계함수 백분위수 = PERCENTILE (array, k) K 번째큰값 = LARGE (array, k) K 번째작은값 = SMALL (array, k) 조건부합개수 = COUNTIF (range, criteria) 9
기술통계함수 10
기술통계함수 - RANK 함수 RANK.EQ 함수 : 데이터의순위를매길때사용하며, 동일한데이터가여러개일때집합내최상위순위를보여줌 cf., RANK.AVG, RANK = RANK.EQ (Number, Ref, [Order]) Number : 지정한셀의순위가산출되는수 Ref : 순위를매기고싶은전체데이터의목록을참조하는인수 Order : 순위를결정하는방법을정의하는수 (0 : 내림차순, 1 : 오름차순 )( 생략될경우 0 으로인식 ) 11
RANK 함수예제 수식을다른셀에복사하기위해서절대참조로지정 12
기술통계함수 PERCENTILE 함수 참조범위에서백분위수에해당하는데이터를찾는함수 PERCENTILE.INC 는참조범위에서백분위수 0 과 1 에해당하는값을포함하여계산 (cf., PERCENTILE.EXC 함수 ) = PERCENTILE.INC(Array, K) Array : 순위를정하고자하는데이터배열또는참조범위 K : 0 에서 1 사이범위의백분위수 13
PERCENTILE 함수예제 14
기술통계함수 - PERCENTRANK 함수 참조범위에서특정값의백분위수를찾는함수 PERCENTRANK.INC 함수는참조범위에서백분위수 0 과 1 에해당하는값을포함하여결과를계산 = PERCENTRANK.INC (Array, X, [Significance]) Array : 상대순위를정하고자하는데이터배열또는참조범위 X : 백분위수를확인할값 Significance : 백분위수의유효자릿수를나타내는값 ( 생략하면 PERCENTRANK 는세자릿수 (0.xxx) 를사용 ) [ 조건 ] 수강생의키 180cm는상위몇 % 에해당하는가? [ 결과 ] 14% =1-PERCENTRANK.INC(F2:F16,180) 19% =1-PERCENTRANK.EXC(F2:F16,180) 14% =1-PERCENTRANK(F2:F16,180) 15
기술통계함수 - COUNTIF 함수 조건을만족하는셀의개수를계산하는함수 = COUNTIF(Range, Criteria) Range : 지정한조건에부합하는셀의개수를세려는셀범위 Criteria : 숫자, 식, 셀참조또는텍스트의형식으로된조건 조건은반드시큰따옴표안에입력함 [ 조건 ] 나이가 25 세이하인학생은몇명인가? [ 결과 ] 11 =COUNTIF(E2:E16,"<=25") [ 조건 ] 수강생중경영학과학생의수는? [ 결과 ] 5 =COUNTIF(D2:D16," 경영 ") 16
기술통계함수 LARGE & SMALL 함수 17
3. 수학 / 삼각함수 18
수학 / 삼각함수 SUM 함수 19
수학 / 삼각함수 PRODUCT 함수 : 인수로입력된셀또는참조범위의모든값을곱함 SUMPRODUCT 함수 : 주어진배열 ( 대응되는행또는열 ) 의모든데이터를서로곱한뒤그곱의합계를계산 20
수학 / 삼각함수 SUBTOTAL 함수 =SUBTOTAL(Function_num, Ref1, [Ref2], ) 함수 함수번호 ( 숨겨진값포함 ) 함수번호 ( 숨겨진값무시 ) AVERAGE 1 101 COUNT 2 102 COUNTA 3 103 MAX 4 104 MIN 5 105 PRODUCT 6 106 STDEV 7 107 STDEVP 8 108 SUM 9 109 VAR 10 110 VARP 11 111 21
수학 / 삼각함수 SUBTOTAL 함수예제 22
수학 / 삼각함수 SUMIF 함수 김씨성을가진학생의토익점수의합 : = SUMIF(D2:D16, 김 *, F2:F16) 23
수학 / 삼각함수 SUMIFS 함수 : 참조범위내의데이터중에여러가지조건을만족하는데이터를합산 = SUMIFS(Sum_range, Criteria_range1, Criteria1, [Criteria_range2, Criteriaum2], ) 조건들은논리곱 (AND) 으로결합 24
4. 찾기 / 참조함수 VLOOKUP/HLOOKUP 함수 주어진값이참조목록에포함되는지검색하고조건을만족하는값을반환하는함수 = VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup]) Lookup_value : 참조목록인 Table_array 의첫번째열에서조회하고자하는값 Table_array : Lookup_value 로선택된값을조회하는참조목록 Col_index_num : 수직참조목록 (Table_array) 의첫번째열에서 Lookup_value 의근삿값또는정확한값을찾았을경우같은행에서구하고자하는값이있는열의번호 [Range_lookup] : 근삿값을찾는경우참 (true : 기본값 ), 텍스트와같이정확히일치하는값을찾을경우거짓 (false) 참조목록의첫번째열 ( 또는행 ) 은반드시오름차순으로정렬 25
VLOOKUP 함수예제 합계점수에대한성적을참조목록을이용하여입력 참조목록 26
찾기 / 참조함수 HLOOKUP 함수예제 수강생의성적에따라재수강여부를비고에입력 27
찾기 / 참조함수 - LOOKUP 함수 한열 ( 행 ) 이나여러열 ( 행 ) 에서조건을만족하는값을구하는함수 벡터형 vs. 배열형 벡터형 LOOKUP 함수 한열 ( 행 ) 의참조범위에서값을조회하고이에대응되는열 ( 행 ) 의값을반환 값을조회하는영역과반환될값을가진셀영역을각각따로지정하는방식 = LOOKUP(Lookup_value, Lookup_vector, [Result_vector]) Lookup_value : Lookup_vector 의첫째행또는열범위에서검색하는값 Lookup_vector : Lookup_value 를조회하는한행또는한열만포함하는범위 [Result_vector] : Lookup_vector 와크기가같은한행또는한열에대응되는범위 [Lookup_vector] 인수는반드시오름차순으로정렬 28
벡터형 LOOKUP 함수예제 29
찾기 / 참조함수 배열형 LOOKUP 함수 참조범위가되는조회하는열 ( 행 ) 과대응하는결과열 ( 행 ) 을하나의연속된범위로설정 참조범위의시작열 ( 행 ) 은값을조회하는영역이되고참조범위가끝나는열 ( 행 ) 은반환될값을포함한영역이됨 = LOOKUP(Lookup_value, Array) Lookup_value : LOOKUP 으로지정된인수가첫번째배열에서검색하는값 Array : Lookup_value 와비교할텍스트, 숫자또는논리값을포함하는셀의참조범위 30
배열형 LOOKUP 함수예제 31
찾기 / 참조함수 - MATCH 함수 한열 ( 행 ) 의셀영역에서조건을조회하고, 해당영역의시작 ( 첫번째 ) 셀부터조건의상대적위치를반환 = MATCH(Lookup_value, Lookup_array, [Match_type]) Lookup_value : Lookup_array 에서찾으려는값 ( 대소문자의구분없이사번, 이름, 코드등의숫자, 텍스트, 논리값또는셀참조 ) Lookup_array : Lookup_Value 가포함된셀들의범위 [Match_type] : lookup_array 에서 lookup_value 를찾는방법으로 -1, 0, 1 세가지가있다. 생략하면 1 로간주한다. 1 [Match_type] 이 1 일경우 lookup_value 보다작거나같은값중가장큰값을찾는다 (lookup_array 는반드시오름차순정렬 ) 2 [Match_type] 이 0 일경우 Lookup_value 와정확하게일치하는값을찾는다. 3 [Match_type] 이 -1 일경우 Lookup_value 보다크거나같은값중가장작은값을찾는다 (lookup_array 는반드시내림차순정렬 ) 32
MATCH 함수예제 33
찾기 / 참조함수 - INDEX 함수 참조범위에서주어진위치에해당하는값을반환하는함수 배열형과참조형이있음 배열형 INDEX 함수 : 셀영역에서좌측최상단을기준으로 N 번째셀값또는 N 행 M 열번째셀값을반환 = INDEX(Array, Row_num, [Column_num]) Array : 가져올값이있는셀범위나배열상수를의미한다. 배열에행이나열이하나만있을때는 Row_num 이나 Column_num 인수의생략이가능하다. Row_num : 가져올행수 Column_num : 가져올열수
배열형 INDEX 함수예제 35
찾기 / 참조함수 참조형 INDEX 함수 : 따로떨어져있는영역을지정하여주어진조건의위치값을반환 = INDEX(Reference, Row_num, [Column_num], [Area_num]) Reference : 셀의참조범위 ( 영역별참조범위를콤마 (,) 로구분하여설정할수있음 ) Row_num : 참조범위에서행의수 Column_num : 참조범위에서열의수 Area_num : 행과열이만나는셀의값을반환하기위한참조의범위를의미함. Reference 인수가다수의참조범위를가지고있을경우나열된참조범위의순서에따라번호를매기게됨 36
참조형 INDEX 함수예제 37
찾기 / 참조함수 - CHOOSE 함수 여러값가운데하나를선택하는함수 = CHOOSE (Index_num, Value1, Value2, ) Index_num : Value1, Value2,... 중선택할값을지정하는인수 Value1, Value2... : 수식, 문자열, 참조등을의미하며 29 개까지입력 Index_num 이 1 보다작거나목록의마지막값의수보다크면 #VALUE! 오류가발생 38
CHOOSE 함수예제 판매량을 A 형추세, B 형추세, C 형추세의세유형으로설정하고판매가를고정, 변동, 증가의세가지유형으로설정하여각유형에따른예상수익계산 39
찾기 / 참조함수 - OFFSET 함수 참조한셀이나셀의범위에서지정한수의행과열만큼떨어져있는셀의값을반환 = OFFSET (Reference, Rows, Cols, Height, Width) Reference : 참조할셀또는참조범위 Rows : 이동할행의수 ( 양수는아래로이동, 음수는위로이동 ) Cols : 이동할열의수 ( 양수는오른쪽으로이동, 음수는왼쪽으로이동 ) Height : 구하는데이터의행의개수를의미하며, 반드시양수여야한다. Width : 구하는데이터의열의개수를의미하며, 반드시양수여야한다. 40
OFFSET 함수예제 : 월별판매수익을조회하여표시 =B23& 월예상판매수익 41
5. 논리함수 - IF 함수 지정한조건에대해참 (true) 과거짓 (false) 을판별 = IF (Logical_test, Value_if_true, Value_if_false) Logical_test : 참과거짓을판정하는논리식 Value_if_true : 논리식의판정결과참일경우나타내는값 Value_if_false : 논리식의판정결과거짓일경우나타내는값 다른함수와중첩하여사용가능 ( 표 3-5 참조 ) 42
논리함수 - AND 함수 논리곱으로모든조건식을충족할경우에참 (true), 그렇지않을경우에거짓 (false) 값을반환 = AND (Logical1, [Logical2], ) Logical : true 또는 false 를결과값으로갖는값, 수식또는참조영역 IF 와 AND 함수사용예 43
논리함수 - OR 함수 논리합으로조건식중하나라도만족할경우참 (true), 하나라도만족하지않은경우에거짓 (false) 값을반환 = OR (Logical1, [Logical2], ) IF 와 OR 함수사용예 44
논리함수 - NOT 함수 주어진조건에참과거짓을바꿔서출력하는함수 45