실무에서 자주 사용하는 엑셀(Excel) 함수식 112가지(엑셀 2010기준)

소개글

Microsoft Office Excel(이하 엑셀)은 전 세계적으로 많은 사람들이 사용하는 사무용 프로그램입니다. 엑셀이 가지고 있는 다양한 기능과 편리한 인터페이스 때문에 엑셀은 데이터 분석이나 계산 등의 업무에 널리 활용되고 있습니다. 특히 엑셀이 제공하는 함수와 수식을 이용하면 복잡한 계산이나 표 작성 등을 효율적으로 수행할 수 있습니다.
그런데 엑셀에서의 함수나 수식 활용은 일반 사용자들에게 생각처럼 쉽지 않습니다. 이것은 엑셀 함수의 종류가 매우 다양하고 사용방법도 함수마다 조금씩 다르며, 특정 문제 해결을 위해 함수를 선택하거나 조합하는 작업이 조금 까다롭기 때문입니다.
따라서 본 자료에서는 사용자들이 일반적인 표 계산 작업에서 빈번히 발생하는 상황을 유형별로 구분하여 각 상황에 적용할 수 있는 함수식을 실제 예를 통하여 제시합니다. 그리고 제시된 함수식의 구조와 의미를 상세히 설명함으로써 사용자들이 함수식을 변형하여 다른 상황에 응용할 수 있도록 구성하였습니다.
본 자료에서는 함수 자체에 대한 사항은 상세히 설명하지 않습니다. 이것은 엑셀에 있는 HELP기능을 이용하면 함수의 의미, 기능, 사용법에 대하여 자세히 알 수 있기 때문입니다. 따라서 사용자께서는 키보드의 “F1”키를 눌러 나타난 “Excel 도움말 창”에서 함수 자체에 대한 설명을 참고해 주시기 바랍니다.
독자들께서 본 자료에 소개된 각종 함수식을 숙지하고 실제 작업에 활용하신다면 엑셀 작업 능력이 몇 단계 업그레이드된 자신을 발견하게 될 것입니다.
※. 본 자료에서의 설명은 MS Office 엑셀 2010을 기준으로 하였습니다.
※. 본 자료에서 제시한 대부분의 함수식은 엑셀 2010 및 하위버전(엑셀 2007, 엑셀 2003 등)에서 사용할 수 있습니다.(단, 엑셀 2010에 새롭게 추가된 몇 가지 함수(VAR.P함수, MODE.SMGL 함수 등)는 하위버전(엑셀 2003, 엑셀 2007 등)에서 작동되지 않습니다.)
※. 실습용 엑셀파일은 별도로 제공하지 않습니다. 독자들께서 가지고 계신(또는 만든) 임의의 엑셀파일로 실습하셔도 무리가 없습니다.

목차

제1장 수식과 함수 활용의 기본
1.1 엑셀에서 수식과 함수란?
1.2 함수식의 구성 형식
1.3 셀에 함수식을 입력하는 방법
1.4 상대주소, 절대주소, 이름 활용하기
1.5 중첩 함수식 사용하기
1.6 배열수식 활용하기
1.7 배열상수 활용하기
1.8 함수식을 적용할 때 발생하는 오류의 종류와 대처법
1.9 수식 분석 기능으로 함수식을 분석하고 오류 추적하기
1.10 도움말 기능에서 함수 사용법 찾기

제2장 문자열(텍스트) 데이터 처리하기
2.1 영어 소문자로만 구성된 문자열에서 일부분을 대문자로 바꾸기
2.2 영어 대문자로만 구성된 문자열에서 일부분을 소문자로 바꾸기
2.3 문자열에서 특정 문자 다음에 위치한 부분만 추출하기
2.4 특정 형식으로 표현된 문자열(숫자 포함)에서 숫자만 추출하기
2.5 문자열 안에 현재 날짜와 시간 나타내기
2.6 특정 문자를 다른 문자로 일괄 변경하기
2.7 문자열의 특정 위치에 있는 문자를 다른 문자로 바꾸기
2.8 문자열에 있는 공백이나 눈에 보이지 않는 이상한 문자 없애기
2.9 문자열에서 있는 특정 문자나 기호 없애기
2.10 문자열을 구성하는 단어의 위치를 서로 바꾸기
2.11 문자열의 지정된 부분에 특정 문자 삽입하기
2.12 문자열에 특정 단어가 있는지 알아보기
2.13 표에 특정 단어가 있는지 여부와 그 단어가 있는 셀 주소 표시하기(배열수식)
2.14 가장 짧은 문자열 찾기(배열수식)
2.15 각 셀에 분리 입력되어 있는 텍스트를 연결하여 하나의 문자열로 만들기
2.16 숫자가 입력된 셀의 빈 부분을 특정 문자로 채우기
2.17 학번을 이용하여 입학 년도와 학과 이름 표시하기
2.18 두 문자열을 비교하여 동일 여부 표시하기
2.19 문자열에서 문자(숫자)를 하나씩 분리하여 서로 다른 셀에 나타내기

제3장 날짜 데이터 처리하기
3.1 오늘 날짜 나타내기
3.2 특정 날짜의 요일 나타내기
3.3 특정 날짜로부터 토, 일요일을 제외한 8일 후의 날짜 나타내기
3.4 완료일 기준으로 오늘 현재 남아있는 작업일수(주말 제외) 구하기
3.5 윤년 여부 판단하기
3.6 특정 날짜로부터 6개월 후의 날짜 나타내기
3.7 특정 날짜로부터 50일 후의 날짜 나타내기
3.8 두 날짜 사이의 일 수 계산하기
3.9 근속 기간을 년과 월로 표시하기
3.10 텍스트 형식으로 입력된 생일에서 만 나이 계산하기
3.11 특정 날짜가 1년 중 몇 번째 주에 속하는지 나타내기
3.12 매달 두번째 일요일에 해당되는 날짜 나타내기
3.13 분기별 합계 구하기
3.14 날짜별 합계 구하기

제4장 시간 데이터 처리하기
4.1 현재 시간 나타내기
4.2 24시간 넘는 시간 합계를 구한 후, 일 수와 시간으로 나타내기
4.3 시간 데이터에 일정 크기의 시간 더하기
4.4 분을 반올림하여 시로 나타내기
4.5 아르바이트 근무시간에 대한 급여 계산하기

제5장 합계 구하기
5.1 표에서 단순 합계 구하기
5.2 상위 3개(또는 하위 3개)값의 합계 구하기(배열수식)
5.3 특정 조건에 부합되는 값들의 합계 구하기
5.4 2차원 표 형식의 합계표 만들기(배열수식)
5.5 곱한 후, 그 결과 값들의 합계 구하기
5.6 곱한 후, 그 결과 값들의 합계 구하기(2)-(배열수식)
5.7 홀수번째 행에 있는 값만 합하기
5.8 부분합 계산하기
5.9 숨긴 행은 제외하고 합계 구하기

제6장 횟수, 개수 구하기
6.1 여러 조건에 부합되는 항목의 개수 구하기
6.2 여러 조건에 부합되는 항목의 개수 구하기(2)-(배열수식)
6.3 특정 텍스트가 포함되어 있는 셀의 개수 구하기
6.4 표에 있는 빈 셀의 개수 구하기
6.5 숫자가 입력되어 있는 셀의 개수 구하기(배열수식)
6.6 두 문자열이 동일한지 비교한 후, 서로 다른 항목의 개수 구하기(배열수식)
6.7 빈 행은 제외하고 일련번호 표시하기
6.8 중복값은 1개로 간주하여 고유한 데이터의 개수 구하기(배열수식)
6.9 중복되지 않은 데이터의 개수 구하기(배열수식)
6.10 특정 셀 범위(배열)의 행이나 열 개수 구하기
6.11 빈도수가 가장 높은 항목과 그 빈도수 표시하기(배열수식)
6.12 한 과목이라도 50점 이하이면 “탈락” 표시하기
6.13 도수분포표 만들기(배열수식)

제7장 조건에 맞는 데이터를 찾아 가공하기
7.1 평균 점수에 따른 등급 부여하기
7.2 OR조건으로 검사해서 “합격”, “불합격” 표시하기
7.3 AND조건으로 검사해서 “우수”, “보통” 표시하기
7.4 시험 성적에 대한 석차 구하기(동점자 처리 방법 포함-배열수식)
7.5 목록에서 N번째 큰 값(또는 작은 값) 구하기
7.6 특정 조건에 부합되는 데이터 목록 추출하기(배열수식)
7.7 조건에 부합되는 값에서 최대값(최소값) 구하기
7.8 조건에 부합되는 값의 합계 구하기(데이터베이스 함수 활용)
7.9 목록에서 고유한 값만 골라 나타내기(배열수식)
7.10 두 목록을 비교해서 동일하지 않은 항목의 개수와 그 목록 나타내기(배열수식)

제8장 찾기(참조)함수를 활용하여 작업하기
8.1 학번으로 이름과 반 정보 찾기
8.2 구매액에 따른 할인율을 찾아 할인 후 금액 계산하기
8.3 직급/연차별 급여 기준표에서 기준금액을 찾아 급여 계산하기
8.4 INDEX와 MATCH함수를 이용하여 2차원표에서 데이터 찾기
8.5 OFFSET함수로 특정 범위에 있는 값들의 합계 구하기
8.6 CHOOSE함수를 이용하여 특정 지점의 계약실적 합계 구하기

제9장 조건부 서식에 함수식 적용하기
9.1 특정 요일 날짜 셀에만 서식 적용하기
9.2 빈 셀에만 서식 적용하기
9.3 특정 값 사이의 숫자가 있는 셀에만 서식 적용하기
9.4 특정 문자가 있는 셀에만 서식 적용하기
9.5 중복된 값이 있는 셀에만 서식 적용하기
9.6 최대값이 있는 셀에 서식 적용하기

제10장 데이터 유효성 검사에 함수식 적용하기
10.1 데이터가 중복 입력되지 않게 하기
10.2 특정 문자로 시작되는 데이터만 입력되게 하기
10.3 금요일과 토요일은 입력되지 않게 하기
10.4 숫자만 입력되게 하기
10.5 홀수만 입력되게 하기

제11장 통계치 계산하기
11.1 특정 조건을 만족하는 값들에 대한 평균 구하기
11.2 표준편차(Standard Deviation) 계산하기
11.3 특정 조건을 만족하는 값들에 대한 표준편차 구하기
11.4 분산(Variance) 계산하기
11.5 특정 값 사이에 있는 숫자로 난수(Random Value) 만들기
11.6 최빈값(출현 빈도가 가장 높은 숫자(항목)) 찾기

제12장 수학 및 공학 관련 계산하기
12.1 십진수를 이진수로 변환하기
12.2 이진수를 십진수로 변환하기
12.3 이진수를 16진수로 변환하기
12.4 계승값 구하기
12.5 최대 공약수 구하기
12.6 최소 공배수 구하기
12.7 나누기에서 몫만 구하기
12.8 나누기에서 나머지만 구하기
12.9 거듭제곱 계산하기
12.10 제곱근 계산하기
12.11 지정된 자리로 반올림하기
12.12 실수(소수점이 있는 숫자)를 정수로 변환하기
12.13 역행렬(Inverse Matrix) 구하기(배열수식)

제13장 기타 함수 활용
13.1 정액법에 의한 감가상각비 구하기
13.2 정률법에 의한 감가상각비 구하기
13.3 대출금에 대한 월 상환액(원금+이자) 계산하기
13.4 정기적금의 만기 후 금액 계산하기
13.5 판매가액을 물품가액과 부가가치세로 분리하기

제14장 사용자 정의 함수 활용
14.1 사용자 정의 함수 만들기
14.2 사용자 정의 함수를 추가기능으로 저장하여 사용하기

본문내용

7.6 특정 조건에 부합되는 데이터 목록 추출하기(배열수식)
표에서 특정 조건에 부합되는 데이터 목록을 추출하는 방법은 다음과 같습니다.

가) 아래 화면과 같이 평균점수가 50점 이하인 학생 이름을 추출하기 위하여 I3셀에 [ =INDEX($B$3:$B$13,LARGE(IF($G$3:$G$13 <= 50, ROW($G$3:$G$13)-ROW($I$3)+1,-1),ROW()-ROW($I$3)+1)) ]을 입력합니다.

나) 그리고 “Ctrl+Shift+Enter”키를 눌러 배열수식으로 변경합니다.(수식이 “ {=INDEX($B$3:$B$13,LARGE(IF($G$3:$G$13 <= 50, ROW($G$3:$G$13)-ROW($I$3)+1,-1),ROW()-ROW($I$3)+1))} “의 형태로 변경됨)

다) 그러면 I3셀에 평균점수가 50점 이하인 학생 중에서 가장 뒤쪽 행에 있는 학생 이름(“김아중”)이 나타납니다. 계속하여 I3셀을 복사(Ctrl+C)한 후, I열에 “#VALUE!”표시가 나타날 때까지 붙여넣기(Ctrl+V)하면 I열에 평균이 50점이하인 학생의 이름이 표시됩니다

참고 자료

없음