1/44 엑셀에서 제공하는 함수는 300 개가 넘으며, 이 함수들 중에는 매우 전문적인 분야에서만 사용되거나 자주 사용되지 않는 것들도 많다. 여기서는 일상에서 자주 사용되는 통계 함수, 수학 / 삼각 함수, 논리 함수, 날짜 함수, 찾기 / 참조 함수를 학습해보자. 06_ 다양한 함수 다루기 학습목표

2/44  통계 함수 다루기 1)AVERAGE, AVERAGEA 함수 06_ 다양한 함수 다루기 형식설명 =AVERAGE( 인수 1, 인수 2...) 인수들의 수치 데이터의 평균을 구한다. =AVERAGEA( 인수 1, 인수 2...) 문자열을 포함한 인수들의 평균을 구한다.

3/44 2)MAX, MIN, MEDIAN, MODE 함수 06_ 다양한 함수 다루기 형식설명 =MAX( 인수 1, 인수 2,...) 인수 범위에서 최대값을 구한다. =MIN( 인수 1, 인수 2...) 인수 범위에서 최소값을 구한다. =MEDIAN( 인수 1, 인수 2...) 인수 범위에서 중간값을 구한다. =MODE( 인수 1, 인수 2...) 인수 범위에서 최빈값 ( 가장 많이 나타나는 값 ) 을 구한다.

4/44 3)RANK 함수 ① 내림차순으로 순위를 구할 경우 ② 오름차순으로 순위를 구할 경우 06_ 다양한 함수 다루기 형식설명 =RANK( 수, 범위, 유형 ) 일정 범위 내에서 특정 수치의 순위를 구한다. · 수 : 순위를 구할 값을 지정한다. · 범위 : 순위를 구할 범위를 지정한다. 반드시 절대참조를 해야 한다. · 유형 : 유형을 생략하거나 0 을 입력하면 내림차순으로 정렬된다. 가장 큰 값이 1 등이다. 오름차순으로 정렬할 경우 1 을 입력한다. 가장 작은 값이 1 등이다.

5/44 4)COUNT, COUNTA, COUNTIF, COUNTBLANK 함수 06_ 다양한 함수 다루기 형식설명 =COUNT( 인수 1, 인수 2...) 수치 데이터가 입력되어 있는 셀의 개수 =COUNTA( 인수 1, 인수 2...) 값이 입력되어 있는 셀의 개수 ( 문자 데이터도 포함 ) =COUNTIF( 범위, 조건 ) 조건을 만족하는 셀의 개수 =COUNTBLANK( 범위 ) 비어 있는 셀의 개수

6/44 5)LARGE, SMALL 함수 06_ 다양한 함수 다루기 형식설명 =LARGE( 범위, K) 범위에서 K 번째로 큰 값 =SMALL( 범위, K) 범위에서 K 번째로 작은 값

7/44 06_ 다양한 함수 다루기 통계 함수 활용하기 실습 실습 6-1.xlsx’ 파일을 불러온다.

8/44 06_ 다양한 함수 다루기 2. AVERAGE 함수 ①이 표시되어 있는 [G5] 셀을 클릭하고 다음과 같이 평균을 구하는 함수식을 입력한 후 [Enter] 를 누른다. =AVERAGE(D5:F5) D5:F5 범위, 즉 국어, 영어, 수학 점수의 평균을 구한다.

9/44 06_ 다양한 함수 다루기 3. [G5] 셀에 평균이 구해지면 셀의 채우기 핸들을 더블 클릭하거나 [G12] 셀까지 드래그한다.

10/44 06_ 다양한 함수 다루기 4. RANK 함수 ②라고 표시된 [H5] 셀을 클릭하고 다음 함수식을 입력 한 후 Enter 를 누른다. [H5] 셀에 순위가 구해지면 [H5] 셀의 채우기 핸들을 [H12] 셀까지 드래그한다. =RANK(G5,$G$5:$G$12) G5:G12 범위에서 G5 셀이 차지 하는 순위, 즉 평균 점수의 순위를 구한다.

11/44 06_ 다양한 함수 다루기 5. MAX 함수 ③이 표시된 [D13] 셀을 클릭하고 최대값을 구하는 함수식 (=MAX(D5:D12)) 을 입력한 후 [Enter] 를 누른다.

12/44 06_ 다양한 함수 다루기 6. MIN 함수 ④로 표시된 [D14] 셀을 클릭하고 최소값을 구하는 함수식 (=MIN(D5:D12)) 을 입력한 후 [Enter] 를 누른다.

13/44 06_ 다양한 함수 다루기 7. 자동 채우기 최대값과 최소값이 구해지면 [D13:D14] 셀을 범위 지정한 후 채우기 핸들을 [G13:G14] 셀까지 드래그한다. ➋ ➊

14/44 06_ 다양한 함수 다루기

15/44 06_ 다양한 함수 다루기 8. COUNTIF 함수 ⑤로 표시된 [D17] 셀을 클릭하고 다음과 같이 조건을 만 족하는 셀의 개수를 구하는 다음 함수 식을 입력한 후 [Enter] 를 누른다. =COUNTIF(C5:C12,“=2 학년 ”) C5:C12 범위에서 “2 학년 ” 이 입력 되어 있는 셀의 개수를 구한다.

16/44 06_ 다양한 함수 다루기 9. LARGE 함수 ⑥으로 표시된 [G16] 셀을 클릭하고 다음 함수식을 입력한 후 [Enter] 를 누른다. =LARGE(G5:G12,2) G5:G12 범위에서 2 번째로 큰 값 을 구한다.

17/44 06_ 다양한 함수 다루기 10. SMALL 함수 ⑦로 표시된 [G17] 셀을 클릭하고 다음 함수식을 입력한 후 [Enter] 를 누른다. =SMALL(G5:G12,3) G5:G12 범위에서 3 번째로 작은 값을 구한다.

18/44 06_ 다양한 함수 다루기 11. 함수를 이용하여 경진대회 성적에 대한 통계들을 간편하게 구하였다.

19/44  수학 / 삼각 함수 다루기 1)SUM 함수 2)SUMIF 함수 06_ 다양한 함수 다루기 형식설명 =SUM( 인수 1, 인수 2...) 인수들의 합계를 구한다. 형식설명 =SUMIF( 조건이 적용된 범위, 조건, 합계를 구할 범위 ) 조건을 비교하여 만족하는 인수의 합계를 구한다.

20/44 3)SUMPRODUCT 함수 4)PRODUCT 함수 06_ 다양한 함수 다루기 형식설명 =SUMPRODUCT( 배열 1, 배열 2,...) 배열 또는 범위의 대응되는 값을 곱하여 합을 표시한다. 형식설명 =PRODUCT( 곱할 수 1, 곱할 수 2...) 인수들의 곱을 구하여 표시한다.

21/44 5)ROUND, ROUNDDOWN, ROUNDUP 함수 06_ 다양한 함수 다루기 형식설명 =ROUND( 실수, 자릿수 ) 실수를 지정된 자릿수로 반올림한다. =ROUNDDOWN( 실수, 자릿수 ) 실수를 지정된 자릿수로 내림한다. =ROUNDUP( 실수, 자릿수 ) 실수를 지정된 자릿수로 올림한다.

22/44 06_ 다양한 함수 다루기

23/44 06_ 다양한 함수 다루기 수학 / 삼각 함수 다루기 실습 ‘ 실습 6-2.xlsx’ 파일을 열고, [F5] 셀을 클릭하여 다음 수식을 입력하고 [Enter] 를 누른다. 평균을 구한 후 [F13] 셀까지 드래그하여 모든 아이들의 평균을 구한다. =ROUND(AVERAGE(C5:E5),1) C5:E5 범위의 평균을 구하고 반올림하여 소수 첫째 자리까지 표시한다.

24/44 06_ 다양한 함수 다루기 2. [F15] 셀을 클릭하여 다음 수식을 입력한 후 [Enter] 를 누른다. =SUMIF(F5:F13,“<80”,G5:G13) F5:F13 의 평균이 80 점 미만인 학생들의 응시횟수를 G5:G13 을 참조하여 구해 합한다.

25/44  텍스트 함수 다루기 1)LEN 함수 2)LEFT, RIGHT, MID 함수 06_ 다양한 함수 다루기 형식설명 =LEN( 문자열 ) 문자열의 길이를 표시한다. 형식설명 =LEFT( 문자열, 개수 ) 문자열의 왼쪽부터 지정한 수만큼 문자를 추출한다. =RIGHT( 문자열, 개수 ) 문자열의 오른쪽부터 지정한 수만큼 문자를 추출한다. =MID( 문자열, 시작 위치, 개수 ) 문자열의 시작 위치부터 지정한 개수만큼 추출한다.

26/44 3)UPPER, LOWER, PROPER, SUBSTITUTE 함수 06_ 다양한 함수 다루기 형식설명 =UPPER( 문자열 ) 문자열을 모두 대문자로 표시한다. =LOWER( 문자열 ) 문자열을 모두 소문자로 표시한다. =PROPER( 문자열 ) 문자열의 첫 글자만 대문자로 표시한다. =SUBSTITUTE( 문자열, 인수 1, 인수 2) 문자열의 인수 1 을 인수 2 로 변경한다.

27/44  논리 함수 다루기 1)IF 함수 2)AND, OR, NOT 함수 06_ 다양한 함수 다루기 형식설명 =IF( 조건, 인수 1, 인수 2) 조건을 비교하여 참이면 인수 1, 거짓이면 인수 2 를 실행한다. 형식설명 =AND( 인수 1, 인수 2...) 인수가 모두 참이면 TRUE 다. =OR( 인수 1, 인수 2...) 인수 중 하나라도 참이면 TRUE 다. =NOT( 인수 ) 인수의 반대 논리 값을 표시한다.

28/44 3)IF, AND 중첩 사용 4)IF, OR 중첩 사용 06_ 다양한 함수 다루기

29/44 06_ 다양한 함수 다루기

30/44 06_ 다양한 함수 다루기 텍스트 함수와 논리 함수 다루기 실습 ‘ 실습 6-3.xlsx’ 파일을 열고, 직급을 입력하는 [H4] 셀에 코드의 첫 글자가 S 이면 ‘ 감독 ’, C 이면 ‘ 코치 ’, P 이면 ‘ 선 수 ’ 로 표기하기 위해 다음 함수식을 입 력한 후 [Enter] 를 누른다. 직급이 구 해지면 [H4] 셀의 채우기 핸들을 [H12] 셀까지 드래그한다. =IF(LEFT(C4,1)=“S”,“ 감독 ”,(IF(LEFT(C4,1)=“C”,“ 코치 ”,“ 선수 ”))) 코드의 첫 글자가 S 이면 ‘ 감독 ’, C 이면 ‘ 코치 ’, 그 외의 경우에는 ‘ 선수 ’ 로 표기한다.

31/44 06_ 다양한 함수 다루기 2. [I4] 셀을 선택하고, 중간과 기말 모두 35 이상이면 ‘ 우수 ’, 그렇지 않으면 ‘ 미흡 ’ 으로 표시하기 위해 다음 함수식을 입력한다. =IF(AND(E4>=35,F4>=35),“ 우수 ”,“ 미흡 ”) E4 셀과 F4 셀의 값이 모두 35 이상이면 ‘ 우수 ’, 그렇지 않으면 ‘ 미흡 ’ 으로 표기한다.

32/44 06_ 다양한 함수 다루기 3. [I4] 셀에 ‘ 우수 ’ 가 바르게 표시되면, [I12] 셀까지 드래그하여 함수식을 복사한다.

33/44  날짜 / 시간 함수 다루기 1) 날짜 함수 06_ 다양한 함수 다루기 형식설명 =TODAY() 시스템의 현재 날짜를 표시하며, 인수는 사용하지 않는다. =YEAR( 날짜 ) 날짜에서 연도만 표시한다. =MONTH( 날짜 ) 날짜에서 월만 표시한다. =DAY( 날짜 ) 날짜에서 일만 표시한다. =WEEKDAY( 날짜, 유형 ) 날짜에 해당하는 요일 번호를 표시한다. 요일은 1( 일요일 ) ~ 7( 토요일 ) 까지 정수로 표시한다.

34/44 2) 시간 함수 06_ 다양한 함수 다루기 형식설명 =NOW() 시스템의 현재 날짜와 시각을 표시한다. 인수는 사용하지 않는다. =HOUR( 시간 ) 시간 데이터에서 시만 표시한다. =MINUTE( 시간 ) 시간 데이터에서 분만 표시한다. =SECOND( 시간 ) 시간 데이터에서 초만 표시한다.

35/44 06_ 다양한 함수 다루기 날짜 / 시간 함수 다루기 실습 실습 6-4.xlsx’ 파일을 열고, [C3] 셀에 ‘=TODAY()’ 를 입력하여 시스템 날짜를 표시한다.

36/44 06_ 다양한 함수 다루기 2. 현재 날짜와 생년월일을 이용하여 나이를 구하기 위해 [E6] 셀에 다음 함수식을 입력한 후 [Enter] 를 누른다. 나이가 구해지면 [E6] 셀의 채우기 핸들을 [E14] 셀까지 드래그한다. =YEAR($C$3)-YEAR(C6) C3 셀에 입력된 오늘 날짜의 연도에서 생년월일에 입력된 연도를 뺀다.

37/44 06_ 다양한 함수 다루기 3. [F6] 셀에 현재 날짜와 입사일을 이용하여 근무연수를 구해보자. [F6] 셀에 다음 함수식을 입력하고 [Enter] 를 누른다. =YEAR($C$3)-YEAR(D6) C3 셀에 입력된 오늘 날짜의 연도에서 입사일에 입력된 연도를 뺀다.

38/44 06_ 다양한 함수 다루기 4. 근무연수가 구해지면 [F6] 셀의 채우기 핸들을 [F14] 셀까지 드래그하여 문서를 완성한다.

39/44  찾기 함수 다루기 06_ 다양한 함수 다루기 형식설명 =VLOOKUP( 기준값, 범위, 열 번호, 논리 값 ) 셀 범위나 배열에서 찾을 값에 해당하는 행을 찾은 후 열 번호에 해당하는 셀의 값을 구하는 함수 =HLOOKUP( 기준값, 범위, 행 번호, 논리 값 ) 셀 범위나 배열에서 찾을 값에 해당하는 열을 찾은 후 행 번호에 해당하는 셀의 값을 구하는 함수 · 기준값 : 찾고자 하는 값 · 범위 : 찾을 정보를 갖고 있는 테이블 · 열 번호 / 행 번호 : 찾으려는 값이 있는 테이블의 열 번호 / 행 번호 · 논리값 : TRUE 또는 생략 - 근사값, 정확하게 일치하는 값이 없으면 기준값보다 작은 값 중에서 기준값보다 가장 큰 값 FALSE - 정확하게 일치하는 값, 만일 찾지 못할 경우 #N/A 오류값 반환

40/44 06_ 다양한 함수 다루기 함수 마법사 이용하기 실습 VLOOKUP 함수 ‘ 실습 6-5.xlsx’ 파일을 열고 [D4] 셀에 다음 함수식을 입력한 후 [Enter] 를 누른다. =VLOOKUP(C4,$G$4:$H$7,2,FALSE) [G4:H7] 범위의 첫 번째 열에서 [C4] 셀의값과 정확히 일치하는 값 (FALSE 로 지정했기 때문에 ) 을 찾아 두 번째 열의 값을 표시한다.

41/44 06_ 다양한 함수 다루기 2. [D4] 셀에 직급명이 구해지면 채우기 핸들을 더블 클릭하여 [D12] 셀까지 채운다. 다음과 같이 직급코드별로 직급명을 찾아올 수 있다.

42/44 06_ 다양한 함수 다루기 3. HLOOKUP 함수 [E4] 셀에 다음 함수식을 입력하고 [Enter] 를 누른다. =HLOOKUP(C4,$H$9:$K$10,2,FALSE) [H9:K10] 범위의 첫 번째 행에서 [E4] 셀의값과 정확히 일치하는 값 (FALSE 로 지정했기 때문에 ) 을 찾아 두 번째 행의 값을 표시한다.

43/44 06_ 다양한 함수 다루기 4. 기본급이 구해지면 [E4] 셀의 채우기 핸들을 더블 클릭하여 다음과 같이 직급코드별 기본급을 표시한다.

IT CookBook, 초보자의 첫 번째 엑셀 2010