똑똑한 직원이 숨겨 놓고 혼자 보는 엑셀 함수 전략

chapter 1. 수식을 사용하기 위해 알아야 할 필수 지침

1. 엑셀 2007에서 더 편리해진 수식과 함수 입력 방법
수식 입력과 복사
Tip : 원래 서식을 바꾸지 않고 수식만 복사하기
연산자의 종류
함수 입력하기
Tip : 자동 합계로 합계와 평균 구하기
함수 마법사로 입력하기

2. 수식에서 셀을 참조해서 계산하기
상대 참조
Tip : 셀에서 수식 보기
절대 참조
혼합 참조
Tip : 다른 시트, 다른 통합 문서의 셀 참조

3. 셀에 이름을 정의해서 수식 만들기
이름 정의하기
이름 만들기
Tip : 이름의 작성 규칙
Column : 이름의 편집 및 삭제

4. 수식 오류 해결하기
오류 값의 발생 원인과 수정
수식의 오류 검사 규칙
중첩 수식을 단계별로 계산하기
Tip : 오류 검사 옵션 확인하기
수식과 셀의 관계 표시하기
Tip : 연결선 제거하기

5. 배열 수식에 대한 이해
배열 수식의 입력과 편집
Tip : 배열 수식의 편집과 삭제
배열 수식으로 조건부 개수 구하기
배열 수식으로 조건부 합계 구하기


chapter 2. 자주 사용하는 함수부터 정리하자

1. Sum / 수학 및 삼각함수 합계 구하기
? 기본 따라잡기 : 금액의 누적 합계 구하기
? 응용은 이렇게 : 시간의 누적 합계 구하기
? 함수 활용 노하우 :
(1) 여러 개의 조건을 지정하여 개수 계산하기
(2) 여러 개의 조건을 지정해서 합계 계산하기
(3) 여러 개의 조건 중 하나 이상을 만족할 때 개수 구하기

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

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

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

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

6. Rank / 통계함수 순위 구하기
? 기본 따라잡기 : 성적표에서 평균으로 석차 구하기
? 응용은 이렇게 : 평균이 같을 때 특정 과목으로 석차 구하기
Column : PERCENTRANK 함수로 백분율 순위 구하기
? 함수 활용 노하우 :
(1) 평균이 같을 때 과목1, 평균과 과목1이 같을 때 과목2로 석차 구하기
(2) 그룹별로 석차 구하기
(3) 그룹별 석차에서 평균이 같을 때 과목1로 석차 구하기

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

8. Round, RoundUp, RoundDown / 수학 및 삼각함수 반올림, 올림, 내림 구하기
? 기본 따라잡기 : 숫자의 반올림, 절상, 절사
? 응용은 이렇게 : 금액 크기에 따라 절사 또는 절상하기
? 함수 활용 노하우 :
(1) 특정 자릿수만 남기고 반올림하기
(2) 시간 반올림하기
(3) 15분, 30분 단위로 반올림하기
Column : 가장 가까운 배수로 올림/내림할 때 사용하는 CEILING 함수와 FLOOR 함수

Chapter 3. 날짜와 시간을 처리하는 함수

1. Today, Now / 날짜 및 시간함수 현재 날짜와 시간 구하기
? 기본 따라잡기 : 현재 날짜와 현재 시간 표시하기
Column : 날짜와 시간을 표시하는 서식 코드
? 응용은 이렇게 : 오늘 날짜만 강조해서 표시하기
Column : 조건부 서식
? 함수 활용 노하우 :
(1) 날짜 계산하기
(2) 시간 계산하기
(3) 오늘 날짜까지만 입력하도록 제한하기

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

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

4. WEEKDAY, WEEKNUM / 날짜 및 시간함수 요일, 주 구하기
? 기본 따라잡기 : 주간 일정표 만들기
? 응용은 이렇게 : 요일 알아내기
? 함수 활용 노하우 :
(1) 특정 기간 동안 요일의 개수 세기
(2) 요일별로 판매량의 합계 구하기
(3) 월별로 각 주의 생산량 합계 구하기

5. DATEDIF / 날짜 및 시간함수 날짜 구하기
? 기본 따라잡기 : 입사일을 이용해서 근속기간 구하기
? 응용은 이렇게 : 근속기간에 따라 휴가일수 구하기
? 함수 활용 노하우 :
(1) 6개월을 1년으로 계산하는 보험나이
(2) 2년마다 1만원씩 증가하는 근속수당 구하기
(3) 15일부터 최대 20일까지 연차일수 구하기
Tip : DAYS360 함수

6. EDATE, EOMONTH, WORKDAY, NETWORKDAYS / 날짜 및 시간함수 전후 개월 구하기
? 기본 따라잡기 : 지급 기준에 따라 만기일 구하기
? 응용은 이렇게 : 토요일과 일요일, 공휴일 등을 제외한 작업 일수 구하기
? 함수 활용 노하우 :
(1) 최근 3개월간의 근무일수 구하기
(2) 작업 일수를 정하여 작업 종료일 구하기
(3) 일요일만 제외하고 근무일수 구하기
Tip : 나눗셈의 몫과 나머지를 계산하는 QUOTIENT 함수와 MOD 함수



chapter 4. 텍스트를 처리하는 함수

1. Left, Right, Mid / 텍스트함수 왼쪽, 오른쪽, 특정 위치의 문자 구하기
? 기본 따라잡기 : 사원코드로 부서와 직급 알아내기
Tip : CHOOSE 함수로 값 선택하기
? 응용은 이렇게 : 주민등록번호로 생년월일, 성별, 나이 계산하기
? 함수 활용 노하우 :
(1) 성과 이름 분리하기
Tip : 문자열의 길이를 구하는 LEN 함수
(2) 가장 오른쪽 셀에 입력한 문자 찾기
(3) 휴대폰 번호 중간에 하이픈(-) 삽입하기

2. Upper, Lower, Proper / 텍스트함수 영어 대소문자 변환하기
? 기본 따라잡기 : 영어 대소문자 변환하기
? 응용은 이렇게 : 한 셀의 텍스트를 일부는 대문자로, 일부는 소문자로 변환
Tip : 자동 고침 옵션 설정하기
? 함수 활용 노하우 :
(1) 대소문자 상관없이 제품 검색하기
(2) 소문자로만 제품코드 입력하기
(3) 대소문자를 구분해서 데이터 검색하기

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

4. Search, Find / 텍스트함수 문자 찾기
? 기본 따라잡기 : /로 구분된 숫자를 따로 분리하기
? 응용은 이렇게 : 제품코드의 알파벳으로 제조일자 알아내기
Tip : 문자와 코드 번호를 서로 변환하는 CODE와 CHAR 함수
? 함수 활용 노하우 :
(1) 특정 문자의 포함 여부 확인하기
(2) 주소에서 동 이름만 추출하기
(3) 셀에 시트 이름 표시하기
Column : 셀 정보를 구하는 CELL 함수

5. Substitute, Replace / 텍스트함수 문자 바꾸기
? 기본 따라잡기 : 주민등록번호와 날짜 데이터 변경하기
? 응용은 이렇게 : 한 셀에 특정 문자로 구분해서 입력한 데이터 개수 구하기
? 함수 활용 노하우 :
(1) 한 셀에서 특정 문자의 개수 세기
(2) 주소를 두 개로 분리하기
Tip : 인쇄되지 않는 문자와 공백을 제거하는 CLEAN, TRIM 함수
(3) 두 줄로 입력한 데이터를 한 줄로 나타내기

6. Rept / 텍스트함수 문자 반복하기
? 기본 따라잡기 : 특정 문자로 그래프 그리기
? 응용은 이렇게 : 증감률의 크기를 문자의 개수로 표시하기
? 함수 활용 노하우 :
(1) 모자라는 자릿수만큼 ? 채우기
(2) 설문 조사 결과를 문자 그래프로 나타내기
(3) 금액 앞에 ￦ 문자를 붙여 한 셀에 한 글자씩 표시하기
Column : 사용자 지정 서식으로 특정 문자 반복 표시하기



chapter 5. 통계 처리를 위한 함수

1. Small, Large / 통계함수 k번째 작은 값과 큰 값 구하기
? 기본 따라잡기 : 판매량이 높은 순서와 낮은 순서로 각각 5개의 판매량 구하기
? 응용은 이렇게 : 평균이 높은 순서로 5명의 점수와 이름 표시하기
? 함수 활용 노하우 :
(1) 중복 값을 제외하고 작은 값 구하기
(2) 값의 크기에 따라 가중치를 적용해서 점수 계산하기
Tip : 배열 상수 사용하기
(3) 특정 범위에서 3개의 큰 값 구하기

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

3. CountIfS, SumIfS / 통계함수, 수학함수 다중 조건에 맞는 개수와 합계 구하기
? 기본 따라잡기 : 두 개의 조건으로 개수와 합계 구하기
? 응용은 이렇게 : 행과 열의 값을 조건으로 합계 구하기
? 함수 활용 노하우 :
(1) 평균이 같을 때 첫 번째 과목으로 순위 매기기
(2) 부서별로 순위 매기기
(3) 연령대와 성별로 구매액의 합계 구하기

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

5. SumProduct / 수학함수 곱의 합계 구하기
? 기본 따라잡기 : 과목마다 가중치를 적용해서 점수 계산하기
Tip : 인수를 모두 곱하는 PRODUCT 함수
? 응용은 이렇게 : 3열 단위로 띄어가며 합계 구하기
? 함수 활용 노하우 :
(1) 두 가지 조건으로 개수와 합계 구하기
Tip : SUMPRODUCT 함수로 조건에 따라 개수와 합계 구하기
(2) 세 가지 조건으로 개수와 합계 구하기
(3) 기간과 거래처에 따라 거래횟수, 총금액, 최초 및 최근 거래일자 구하기

6. SubTotal / 수학함수 부분합 구하기
? 기본 따라잡기 : 필터 결과로만 계산하기
Column : 자동 필터로 데이터 검색하기
? 응용은 이렇게 : 함수 하나로 11개의 계산 수행하기
? 함수 활용 노하우 :
(1) 필터링한 후에도 순서대로 표시되는 일련 번호 입력하기
(2) 자동 필터에서 선택한 값을 셀에 표시하기
(3) 자동 필터 결과만 복사해서 붙여넣기

7. Frequency / 통계함수 도수 분포 구하기
? 기본 따라잡기 : 설문조사 통계표 만들기
? 응용은 이렇게 : 성적 분포표 만들기
Tip : MODE 함수로 최빈값 구하기
? 함수 활용 노하우 :
(1) 가로 배열로 빈도수 구하기
Tip : TRANSPOSE 함수로 행/열 바꾸기
(2) 중복을 제외하고 숫자의 개수 세기
(3) 중복을 제외하고 문자의 개수 세기
Column : 숫자 또는 문자의 고유 개수 세기

8. D함수 / 데이터베이스함수 조건에 따른 통계 처리하기
? 기본 따라잡기 : 성별과 지역에 따라 인원과 구입금액의 합계 및 평균 구하기
? 응용은 이렇게 : 연령별로 구입횟수와 구입금액의 최대값 및 최소값 구하기
Tip : 붙여넣기 옵션으로 원래 서식 유지하기
? 함수 활용 노하우 :
(1) 와일드카드 문자로 검색하기
(2) 특정 고객 정보 검색하기
(3) 수식으로 조건 지정하기
Column : 데이터베이스 함수의 조건 범위 만들기



chapter 6. 원하는 값을 찾기 위한 함수

1. Vlookup / 찾기 및 참조함수 첫 열에서 값 찾기
? 기본 따라잡기 : 제품의 단가, 판매량에 대한 할인율 찾기
? 응용은 이렇게 : 찾는 값을 입력했을 때만 VLOOKUP으로 값 찾기
? 함수 활용 노하우 :
(1) VLOOKUP 함수로 값을 찾을 수 없을 때
Tip : 엑셀 2003 버전에서 오류 처리하기
(2) 경우에 따라 서로 다른 표에서 값을 찾아야 할 때
(3) 경우에 따라 서로 다른 열에서 값을 찾아야 할 때

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

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

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

5. Indirect / 찾기 및 참조함수 텍스트가 가리키는 셀 찾기
? 기본 따라잡기 : 마지막 데이터 찾기
Tip : 행 번호와 열 번호로 셀 주소를 만드는 ADDRESS 함수
? 응용은 이렇게 : 이중 유효성 검사 설정하기
? 함수 활용 노하우 :
(1) 세로 배열을 가로 배열로 바꾸기
(2) 시트를 선택하여 셀 참조하기
(3) 지정한 기간 동안의 평일, 토요일, 일요일의 개수 구하기



chapter 7. 일류를 위한 함수 활용 전략

1. 재직·경력·퇴직 증명서 자동으로 발급하기
사원관리명부에 이름 정의하기
증명서 발급 정보 입력하기
선택한 사원의 정보를 증명서로 가져오기
증명서 발급 가능 여부 확인하기
증명서 양식만 한 페이지로 인쇄하기

2. 상품정보를 참조해서 자동으로 작성되는 거래명세표
공급자, 공급받는자, 거래정보 입력하기
상품정보 입력하기
거래내역 입력하기
거래명세표 만들기
Column : INDEX 함수로 거래내역의 ‘일자’ 가져오기
공급자용 거래명세표 완성하기

3. 거래명세표를 참조해서 자동으로 작성되는 세금계산서
세금계산서 정보 입력하기
세금계산서 만들기
Tip : 금액합계를 한 글자씩 표시하기
공급받는자 보관용 세금계산서 만들기

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

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

6. 차량번호를 선택하면 자동으로 그려지는 요금 차트
동적으로 차량운행현황을 참조하는 이름 정의하기
고급 필터로 차량번호의 고유 목록 뽑기
Tip : 고급 필터의 조건 범위와 복사 위치
차량번호와 운행종류에 따라 요금 합계 구하기
요금 차트 그리기
Tip : 차트 스타일 갤러리에서 차트 스타일 선택하기
차량번호와 합계를 차트에 나타내기
Tip : 도형 스타일 지정하기
Tip : 차트 요소의 서식 지정하기

7. 구간별로 다른 색으로 표시하는 매출 차트
분류에 따라 모델명을 선택하는 이중 유효성 검사 설정
Tip : 선택한 분류의 모델 개수를 구하는 수식 분석
모델명에 따라 매출량 구하기
매출 차트 만들기
조건부 서식

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

9. 매출내역을 자동으로 검색하기
양식 컨트롤 만들기
Tip : 리본 메뉴에 [개발 도구] 탭 표시하기
컨트롤 서식 설정하기
고급 필터의 조건 범위 만들기
고급 필터로 데이터를 검색하는 매크로 만들기
Column : 수식으로 고급 필터의 조건 범위 만들기
매크로 실행 단추 만들기
Column : Excel 매크로 사용 통합 문서의 저장과 열기

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



appendix. 엑셀 2007에서 엑셀 2003 명령 찾아보기