엑셀 함수 완전 정복 : 똑소리 나는 직장인의 성공 비밀 노트

예제 파일 다운로드

예제파일 어디잇나요? 1*

회원가입

공지사항

도서목록 | 도서문의

워드프로세서 | 컴퓨터활용능력 | 정보처리기능사

엑셀자동화 서식 | 엑셀자동화 기술 방법 | 엑셀실무활용 팁

엑셀2003 | 워드2003 | 액세스2003 | 파워포인트2003

OA 프로그램 | 서식 | 자격증 | 기타

회원 방문 수	8,351,584 명
접속 인원 수	2,328 명

홈 > 도서정보 > 도서정보

엑셀 함수 완전 정복 : 똑소리 나는 직장인의 성공 비밀 노트

저저자 : 이형범 저
출판사 : 정보문화사
발행일 : 2013년 9월

01 책소개

엑셀!　지금 시작해도 늦지 않았다!

베껴 쓰기는 그만, 이제 직접 문서를 만들어보자!

엑셀을 사용하는 가장 큰 이유 중의 하나는 수식을 사용해서 데이터를 원하는 형태로 요약하고 분석하여 문서 작성에 활용할 수 있다는 것입니다. 특히 엑셀이 제공하는 300여 개의 함수는 복잡한 계산을 간단하고 편리하게 수행할 수 있도록 도와줍니다.

이렇게 유용한 함수와 수식이 어렵게 느껴지는 것은 수식을 어떤 방식으로 작성하느냐에 따라 작업 과정과 결과가 크게 달라지기 때문입니다. 많은 엑셀 사용자들은 데이터를 처리하고 활용하는 단계에서 수많은 어려움에 부딪힙니다. 이 책은 여러분이 직면하고 있는 문제를 여러분 스스로 해결해 나갈 수 있도록 도와줄 것입니다.

엑셀의 시작은 함수부터! 함수를 '제대로' 활용하는 방법을 전수합니다.

★ 엑셀의 A to Z, 함수를 응용하기 전에 기초부터 탄탄하게 다질 수 있도록 도와줍니다.
★ 전문가에게 배우는 노하우로 함수를 더욱 쉽게 이해하고 활용할 수 있습니다.
★ 단계별 난이도에 따라 함수의 다양한 활용 방법을 배웁니다.
★ 활용도가 가장 높은 함수부터 엑셀 2013에서 새로 추가된 함수까지! 이제 함수도 골라 쓸 수 있습니다.
★ 실무 함수 활용 방법을 익혀 수많은 예외 상황에서 발생하는 문제를 쉽게 해결할 수 있습니다.
★ 남들이 써놓은 수식을 베껴 쓰는 것은 이제 그만! 여러분 스스로 수식을 직접 작성할 수 있습니다.

저자 소개

OA 관련 서적 전문 필자로, 다수의 오피스 관련 베스트셀러를 집필한 이후 자타가 공인하는 엑셀 전문가로 평가받고 있다. 오랜 기간 풍부한 집필 경험으로 독자들이 무엇을 요구하는지 정확하게 짚어내며 그만의 특별한 노하우를 담아 많은 사람에게 꾸준히 사랑받고 있다.

현 두드림기획 대표

한국사학진흥재단 주관 엑셀을 활용한 대학경쟁력분석, 교육원가분석 방법 강의

대학행정 및 경쟁력분석을 위한 엑셀 활용법 강의(아주대학교, 아세아연합신학대학교, 서울장신대학교,

부산경상대학교, 진주연암공업대학교, 오산대학교, 안동과학대학교 등)

대학경쟁력분석시스템(U-CAS) 엑셀 기반 프로그램 개발

대표 저서

경쟁력을 높여주는 엑셀 2013 실무+활용 길라잡이

회사 실무에 힘을 주는 한글 2010

회사 실무에 힘을 주는 엑셀 2010

회사 실무에 힘을 주는 파워포인트 2010

회사 실무에 힘을 주는 엑셀 2010 함수&수식

돈 되는 엑셀 실무 활용 기술

엑셀 실무 비밀 기술

엑셀 2007 함수+매크로&VBA 등 100여권

02 목차

Chapter 1. 엑셀 함수 기초 다지기
01 엑셀 2013에서 수식과 함수 입력하기
- 수식 입력과 복사
- 연산자의 종류
- 함수 입력하기
- 함수 마법사로 입력하기

02 수식에서 셀을 참조하여 계산하기
- 상대 참조
- 절대 참조
- 혼합 참조

03 셀에 이름을 정의해서 수식 만들기
- 이름 정의하기
- 이름 만들기
| COLUMN | 이름의 편집 및 삭제

04 수식 오류 해결하기
- 오류 값의 발생 원인과 수정
- 수식의 오류 검사 규칙
- 중첩 수식을 단계별로 계산하기
- 수식과 셀의 관계 표시하기

05 배열 수식에 대한 이해와 적용
- 배열 수식의 입력과 편집
- 배열 수식으로 조건부 개수 구하기
- 배열 수식으로 조건부 합계 구하기

Chapter 2. 사용 빈도수가 높은 함수 정리
01 Sum / 수학 및 삼각함수 합계 구하기
■ 기본 따라잡기 : 금액의 누적 합계 구하기
■ 응용 따라잡기 : 시간의 누적 합계 구하기
■ 함수 활용 노하우
1. 여러 개의 조건을 지정하여 개수 계산하기
2. 여러 개의 조건을 지정해서 합계 계산하기
3. 여러 개의 조건 중 하나 이상을 만족할 때 개수 구하기

02 Average / 통계함수 평균 구하기
■ 기본 따라잡기 : 과목의 평균 구하기
| COLUMN | 기하평균과 조화평균-GEOMEAN 함수와 HARMEAN 함수
■ 응용 따라잡기 : 텍스트까지 포함해서 평균 구하기
■ 함수 활용 노하우
1. 평균을 지정한 자릿수로 반올림하기
2. 0값을 제외하고 평균 계산하기
3. 조건을 만족하는 값들로만 평균 계산하기
| COLUMN | 일정 비율을 제외하고 평균을 구하는 TRIMMEAN 함수

03 Max / 통계함수 최대값 구하기
■ 기본 따라잡기 : 최고 점수 구하기
■ 응용 따라잡기 : 최고 점수를 강조해서 표시하기
■ 함수 활용 노하우
1. 특정 제품의 최고 거래량 구하기
2. 월별 최고 매출액 구하기
3. 가장 큰 매출액의 제품명 구하기

04 Min / 통계함수 최소값 구하기
■ 기본 따라잡기 : 가장 빠른 기록 구하기
■ 응용 따라잡기 : 한계값을 이용한 계산
■ 함수 활용 노하우
1. 특정 거래처의 최초 거래일자 구하기
2. 거래처별로 최초 거래일자와 최종 거래일자 구하기
3. 월별로 최저 매출액과 최고 매출액 구하기

05 Count, CountA, CountBlank / 통계함수 셀 개수 구하기
■ 기본 따라잡기 : 셀 개수 세기
■ 응용 따라잡기 : 병합된 셀의 일련번호 매기기
■ 함수 활용 노하우
- 마지막 데이터만 강조하기
- 입력 행을 쉽게 구분할 수 있도록 강조하기
- 동적 범위로 유효성 검사 설정하기

06 Rank.EQ, Rank.AVG / 통계함수 순위 구하기
■ 기본 따라잡기 : 성적표에서 평균으로 석차 구하기
| COLUMN | PERCENTRANK 함수로 백분율 순위 구하기
■ 응용 따라잡기 : 평균이 같을 때 특정 과목으로 석차 구하기
■ 함수 활용 노하우
- 평균이 같을 때 과목1, 평균과 과목1이 같을 때 과목2로 석차 구하기
- 그룹별로 석차 구하기
- 그룹별 석차에서 평균이 같을 때 과목1로 석차 구하기

07 If / 논리함수 조건 처리하기
■ 기본 따라잡기 : 조건의 참, 거짓에 따라 다른 값 표시하기
■ 응용 따라잡기 : 중첩 IF 함수로 여러 조건 검사하기
■ 함수 활용 노하우
- 평균에 따라 A, B, C, D, F로 학점 구하기
- 모든 과목이 70 이상일 때 합격으로 평가하기
- 평균 또는 회화 점수가 70 미만일 때 탈락으로 평가하기

08 Round, RoundUp, RoundDown / 수학 및 삼각함수 반올림, 올림, 내림 구하기
■ 기본 따라잡기 : 숫자의 반올림, 절상, 절사
■ 응용 따라잡기 : 금액 크기에 따라 절사 또는 절상하기
■ 함수 활용 노하우
- 특정 자릿수만 남기고 반올림하기
- 시간 반올림하기
- 15분, 30분 단위로 반올림하기
| COLUMN | 가장 가까운 배수로 올림/내림-CEILING 함수와 FLOOR 함수

Chapter 3. 날짜와 시간을 처리하는 함수
01 Today, Now / 날짜 및 시간함수 현재 날짜와 시간 구하기
■ 기본 따라잡기 : 현재 날짜와 현재 시간 표시하기
| COLUMN | 날짜와 시간을 표시하는 서식 코드
■ 응용 따라잡기 : 오늘 날짜만 강조해서 표시하기
■ 함수 활용 노하우
- 날짜 계산하기
- 시간 계산하기
- 오늘 날짜까지만 입력하도록 제한하기

02 Date, Year, Month, Day / 날짜 및 시간 함수 날짜 계산하기
■ 기본 따라잡기 : 30일 후 또는 매월 말일로 지급일 계산하기
■ 응용 따라잡기 : 만년 달력 만들기
■ 함수 활용 노하우
- 일자를 분기로 계산하기
- 분기에 따라 거래량과 거래금액 계산하기
- 월별 누계 구하기

03 TIME, HOUR, MINUTE, SECOND / 날짜 및 시간 함수 시간 계산하기
■ 기본 따라잡기 : 출근 시간과 퇴근 시간으로 주/야간 근무 시간 구하기
■ 응용 따라잡기 : 입금시간을 이용하여 시간대별로 수입금 합계 구하기
■ 함수 활용 노하우
- 시간 계산에서 음수 시간 처리하기
- 종료 30분전부터 강조해서 표시하기
- 시간의 평균 계산하기
| COLUMN | DATEVALUE 함수와 TIMEVALUE 함수

04 WEEKDAY / 날짜 및 시간 함수 요일, 주 구하기
■ 기본 따라잡기 : 주간 일정표 만들기
■ 응용 따라잡기 : 요일 알아내기
■ 함수 활용 노하우
- 특정 기간 동안 요일의 개수 세기
- 요일별로 판매량의 합계 구하기
- 월별로 각 주의 생산량 합계 구하기

05 DATEDIF / 날짜 및 시간 함수 날짜 구하기
■ 기본 따라잡기 : 입사일을 이용해서 근속기간 구하기
■ 응용 따라잡기 : 근속기간에 따라 휴가일수 구하기
■ 함수 활용 노하우
- 6개월을 1년으로 계산하는 보험 나이
- 1년마다 1만원씩 증가하는 근속수당 구하기
- 15일부터 최대 20일까지 연차일수 구하기
| COLUMN | DAYS360 함수와 엑셀 2013에 새로 추가된 DAYS 함수

06 EDATE, EOMONTH, WORKDAY, NETWORKDAYS / 날짜 및 시간 함수
전후 개월 구하기
■ 기본 따라잡기 : 지급 기준에 따라 만기일 구하기
■ 응용 따라잡기 : 토요일과 일요일, 공휴일 등을 제외하고 작업일수 구하기
■ 함수 활용 노하우
- 최근 3개월간의 근무일수 구하기
- 작업일수를 정하여 작업 종료일 구하기
- 일요일만 제외하고 근무일수 구하기

Chapter 4. 텍스트를 처리하는 함수
01 Left, Right, Mid / 텍스트 함수 왼쪽, 오른쪽, 특정 위치의 문자열 구하기
■ 기본 따라잡기 : 사원코드로 부서와 직급 알아내기
■ 응용 따라잡기 : 주민등록번호로 생년월일, 성별, 나이 계산하기
■ 함수 활용 노하우
- 성과 이름 분리하기
- 가장 오른쪽 셀에 입력한 문자 찾기
- 휴대폰 번호 중간에 하이픈(-) 삽입하기
| COLUMN | 엑셀 2013의 빠른 채우기로 데이터 분리하기

02 Upper, Lower, Proper / 텍스트 함수 영어 대소문자 변환하기
■ 기본 따라잡기 : 영어 대소문자 변환하기
■ 응용 따라잡기 : 한 셀의 텍스트를 일부는 대문자로, 일부는 소문자로 변환
■ 함수 활용 노하우
- 대소문자 상관없이 제품 검색하기
- 소문자로만 제품코드 입력하기
- 대소문자를 구분해서 데이터 검색하기

03 Text / 텍스트 함수 문자로 변환하기
■ 기본 따라잡기 : 숫자/날짜에 표시 형식을 지정해서 텍스트와 결합하기
■ 응용 따라잡기 : 금액을 한 칸에 숫자 하나씩 표시하기
■ 함수 활용 노하우
- 날짜의 요일을 모두 대문자로 표시하기
- 불규칙하게 입력한 전화번호를 일정한 형식으로 변경하기
- 금액을 한글이나 한자로 표시하기

04 Search, Find / 텍스트 함수 문자 찾기
■ 기본 따라잡기 : “/” 문자로 구분된 숫자를 따로 분리하기
■ 응용 따라잡기 : 제품코드의 알파벳으로 제조일자 알아내기
■ 함수 활용 노하우
- 특정 문자의 포함 여부 확인하기
- 주소에서 동 이름만 추출하기
- 셀에 시트 이름 표시하기
| COLUMN | 셀 정보를 구하는 CELL 함수

05 Substitute, Replace / 텍스트 함수 문자 바꾸기
■ 기본 따라잡기 : 주민등록번호와 날짜 데이터 변경하기
■ 응용 따라잡기 : 한 셀에 특정 문자로 구분해서 입력한 데이터 개수 구하기
■ 함수 활용 노하우
- 한 셀에서 특정 문자의 개수 세기
- 주소를 두 개로 분리하기
- 두 줄로 입력한 데이터를 한 줄로 나타내기

06 Rept / 텍스트 함수 문자 반복하기
■ 기본 따라잡기 : 특정 문자로 그래프 그리기
■ 응용 따라잡기 : 증감률의 크기를 문자의 개수로 표시하기
■ 함수 활용 노하우
- 모자라는 자릿수만큼 ? 채우기
- 설문 조사 결과를 문자 그래프로 나타내기
- 금액 앞에 ￦ 문자를 붙여 한 셀에 한 글자씩 표시하기

Chapter 5. 통계 처리를 위한 함수
01 Small, Large / 통계함수 k번째 작은 값과 큰 값 구하기
■ 기본 따라잡기 : 판매량이 높은 순서와 낮은 순서로 각각 5개씩 판매량 구하기
■ 응용 따라잡기 : 평균이 높은 순서로 5명의 점수와 이름 표시하기
■ 함수 활용 노하우
- 중복 값을 제외하고 작은 값 구하기
- 값의 크기에 따라 가중치를 적용해서 점수 계산하기
- 특정 범위에서 3개의 큰 값 구하기

02 CountIf, SumIf / 통계함수, 수학함수 조건에 맞는 개수와 합계 구하기
■ 기본 따라잡기 : 하나의 조건을 만족하는 개수와 합계 구하기
■ 응용 따라잡기 : 같은 제품끼리 일련번호를 매기고 누계 구하기
■ 함수 활용 노하우
- 중복 데이터의 입력 제한하기
- 초과 시간의 개수와 합계 구하기
- 와일드카드 문자로 조건 지정하기

03 CountIfs, SumIfs / 통계함수, 수학함수 다중 조건에 맞는 개수와 합계 구하기
■ 기본 따라잡기 : 두 개의 조건으로 개수와 합계 구하기
■ 응용 따라잡기 : 행과 열의 값을 조건으로 합계 구하기
■ 함수 활용 노하우
- 평균이 같을 때 첫 번째 과목으로 순위 매기기
- 부서별로 순위 매기기
- 연령대와 성별로 구매액의 합계 구하기

04 AverageIf, AverageIfs / 통계함수 조건에 맞는 평균 구하기
■ 기본 따라잡기 : 성별과 지역을 조건으로 평균 구매액 구하기
■ 응용 따라잡기 : 세 개의 조건을 검사해서 개수, 합계, 평균 구하기
■ 함수 활용 노하우
- 0값을 제외하고 평균 구하기
- 와일드카드 문자를 이용해서 조건에 맞는 평균 구하기
- #DIV/0! 오류 처리하기

05 SumProduct / 수학함수 곱의 합계 구하기
■ 기본 따라잡기 : 과목마다 가중치를 적용해서 점수 계산하기
■ 응용 따라잡기 : [3] 열 단위로 띄어가며 합계 구하기
■ 함수 활용 노하우
- 두 가지 조건으로 개수와 합계 구하기
- 세 가지 조건으로 개수와 합계 구하기
- 기간과 거래처에 따라 거래횟수, 총금액, 최초 및 최근 거래일자 구하기

06 SubTotal / 수학함수 부분합 구하기
■ 기본 따라잡기 : 필터 결과로만 계산하기
| COLUMN | 자동 필터로 데이터 검색하기
■ 응용 따라잡기 : 함수 하나로 11개의 계산 수행하기
■ 함수 활용 노하우
- 필터링한 후에도 순서대로 표시되는 일련 번호 입력하기
- 자동 필터에서 선택한 값을 셀에 표시하기

07 Frequency / 통계함수 빈도수 구하기
■ 기본 따라잡기 : 설문조사 통계표 만들기
■ 응용 따라잡기 : 성적 분포표 만들기
■ 함수 활용 노하우
- 가로 배열로 빈도수 구하기
- 중복을 제외하고 숫자의 개수 세기
- 중복을 제외하고 문자의 개수 세기
| COLUMN | 숫자 또는 문자의 고유 개수 세기

08 D함수 / 데이터베이스 함수 조건에 따른 통계 처리하기
■ 기본 따라잡기 : 성별과 지역에 따라 인원과 구 입금액의 합계 및 평균 구하기
■ 응용 따라잡기 : 연령별로 구입횟수와 구입금액의 최대값 및 최소값 구하기
■ 함수 활용 노하우
- 와일드카드 문자로 검색하기
- 특정 고객 정보 검색하기
- 수식으로 조건 지정하기

Chapter 6. 원하는 값을 찾기 위한 함수
01 Vlookup / 조회 및 참조함수 첫 열에서 값 찾기
■ 기본 따라잡기 : 제품의 단가, 판매량에 대한 할인율 찾기
■ 응용 따라잡기 : 찾는 값을 입력했을 때만 VLOOKUP 함수로 값 찾기
■ 함수 활용 노하우
- VLOOKUP 함수로 값을 찾을 수 없을 때
- 경우에 따라 서로 다른 표에서 값을 찾아야 할 때
- 경우에 따라 서로 다른 열에서 값을 찾아야 할 때

02 Hlookup / 조회 및 참조함수 첫 행에서 값 찾기
■ 기본 따라잡기 : 수우미양가로 평가하기
■ 응용 따라잡기 : 도서 분류에 따라 대여료와 반납예정일, 연체료 계산하기
■ 함수 활용 노하우
- 이중으로 유효성 검사 설정하기
- 팀과 기간으로 실적 찾기
- 테이블에서 특정 셀의 행과 열 강조하기

03 Index, Match / 조회 및 참조함수 위치 번호로 찾기
■ 기본 따라잡기 : 지역과 크기로 택배 요금 찾기
■ 응용 따라잡기 : 임의의 필드 값으로 관련 데이터 찾기
■ 함수 활용 노하우
- 콤보 상자를 이용한 데이터 찾기
- 규격과 두께가 모두 같을 때 단가 찾기
- 영어 대소문자를 구분해서 찾기

04 Offset / 조회 및 참조함수 행, 열만큼 떨어진 위치의 셀 찾기
■ 기본 따라잡기 : 선택한 기간의 총판매량 구하기
■ 응용 따라잡기 : 선택한 제품으로 자동으로 바뀌는 차트 그리기
■ 함수 활용 노하우
- 현재 입력한 데이터까지 자동으로 참조하는 이름 정의하기
- 동적으로 목록이 조정되는 콤보 상자 만들기
- 최근 3개 데이터의 평균 구하기

05 Indirect / 조회 및 참조함수 텍스트가 가리키는 셀 찾기
■ 기본 따라잡기 : 마지막 데이터 찾기
■ 응용 따라잡기 : 이중 유효성 검사 설정하기
■ 함수 활용 노하우
- 세로 배열을 가로 배열로 바꾸기
- 시트를 선택하여 셀 참조하기
- 지정한 기간 동안의 평일, 토요일, 일요일의 개수 구하기

Chapter 7. 실무 예제로 함수 완전 정복
01 재직·경력·퇴직 증명서 자동으로 발급하기
- 사원관리명부에 이름 정의하기
- 증명서 발급 정보 입력하기
- 선택한 사원의 정보를 증명서로 가져오기
- 증명서 발급 가능 여부 확인하기
- 증명서 양식만 한 페이지로 인쇄하기

02 상품정보를 참조해서 자동으로 작성되는 거래명세표
- 공급자, 공급받는자, 거래정보 입력하기
- 상품정보 입력하기
- 거래내역 입력
- 거래명세표 만들기
- 공급자용 거래명세표 완성하기

03 거래명세표를 참조해서 자동으로 작성되는 세금계산서
- 세금계산서 정보 입력하기
- 세금계산서 만들기
- 공급받는자 보관용 세금계산서 만들기

04 연도와 월에 따라 자동으로 그려지는 시급제 사원의 출근현황표
- 사원목록의 이름 정의하기
- 연도와 월을 입력해서 날짜와 요일 표시하기
- 사원목록에서 성명과 주민등록번호 가져오기
- 토요일과 일요일에 대한 조건부 서식 지정하기

05 출근현황표를 이용하여 자동으로 작성하는 급여정산표
- 이름을 기준으로 사원의 기초 정보 가져오기
- 날짜 표시 및 출근표에서 근무시간 가져오기
- 정상근무와 잔업근무에 대한 급여 및 총지급액 계산하기
| COLUMN | 근무시간의 시간과 분을 구분해서 급여 계산하기
- 토/일요일과 마지막 날짜 처리를 위한 조건부 서식 지정하기

06 차량번호를 선택하면 자동으로 그려지는 요금 차트
- 동적으로 차량운행현황을 참조하는 이름 정의하기
- 차량번호의 고유 목록 만들기
- 차량번호와 운행종류에 따라 요금 합계 구하기
- 요금 차트 그리기
- 차량번호와 합계를 차트에 나타내기

07 구간별로 다른 색을 표시하는 매출 차트
- 분류에 따라 모델명을 선택하는 이중 유효성 검사 설정
- 모델명에 따라 매출량 구하기
- 매출 차트 만들기
- 조건부 서식

08 피벗 테이블을 사용해서 거래현황 분석하기
- 수식에서 사용할 이름 정의하기
- 품목의 데이터 유효성 검사 설정하기
- 수식으로 데이터 요약하기
- 피벗 테이블로 데이터 요약하기
- 피벗 테이블 보고서 디자인하기

09 매출내역을 자동으로 검색하기
- 양식 컨트롤 만들기
| COLUMN | Excel 매크로 사용 통합 문서의 저장과 열기
- 컨트롤 서식 설정하기
- 고급 필터의 조건 범위 만들기
| COLUMN | 수식으로 고급 필터의 조건 범위 만들기
- 고급 필터로 데이터를 검색하는 매크로 만들기
- 매크로 실행 단추 만들기

10 매입/매출 통합 관리 문서 만들기
- 매입/매출 데이터 입력하기
- 자동 필터의 검색 결과로 계산하기
- 옵션 단추 만들기
- 자동 필터의 검색 과정을 매크로로 기록하기
| COLUMN | 자동 필터 매크로 코드 분석
- 분기별 검색 및 매입/매출 검색 매크로 만들기

03 도서문의

+ 더 많은 내용보기