엑셀 함수 Q&A(실무에서 꼭 필요한)

이 책의 소개



본서는 엑셀 함수를 사용하다 막혔을 때 시원하게 뚫어주는 Q&A 형태로 만들어진 엑셀 함수 지침서입니다. 실무에서 엑셀을 사용하면서 함수에 대해 궁금한 내용을 여러가지 상황에 따른 해결책을 소개한 도서 로 전체 270개의 함수 문제를 제시하고 이를 해결하는 과정을 소개했습니다.



이 책의 특징



엑셀의 기초를 익히고 실무에서 자신이 필요한 시트를 만들려면 제일 먼저 부딪히는 어려움이 함수식입니다. 엑셀을 오랫동안 전문적으로 사용해온 사람들에게도 함수식은 그리 만만한 문제가 아닙니다. 하지만 함수식을 잘 활용하면 아주 다양한 자동화 문서를 만들 수 있습니다.



본서에서는 함수들의 기능을 하나씩 설명하는 것이 아니라 실제 업무에 적용된 함수식들을 설명하고 있 습니다. 함수식을 잘 구성하는 제일 빠른 방법은 많은 실제 함수식 사례를 살펴보는 것이라는 사실을 오랜 경험을 통해 알게 되었습니다.



자신이 처리하는 작업에 따라서는 본서가 제공하는 완성된 함수식을 그대로 사용할 수도 있을 것이며 또 는 유사한 작업의 경우를 찾아 본서의 함수식을 조금만 수정하면 자신의 함수식으로 쉽게 만들 수 있을 것입니다.

PART 01 함수식 사용의 기초 지식

SECTION 001 함수식에 사용되는 연산자와 연산의 우선순위

SECTION 002 절대주소와 혼합주소를 편하게 입력하는 방법

SECTION 003 입력하다가 함수의 형식을 알아보는 방법

SECTION 004 함수식을 복사하는 2가지 방법

SECTION 005 함수식을 수정하는 2가지 방법

SECTION 006 복잡한 함수식을 분석하는 방법

SECTION 007 수식 에러를 추적하는 편리한 도구들

SECTION 008 수식에서 발행하는 7가지 에러와 해결책

SECTION 009 에러 없이도 계산이 잘못되는 6가지 경우

SECTION 010 이름을 만드는 3가지 방법과 이름의 규칙

SECTION 011 이름 정의의 3가지 비밀

SECTION 012 이름을 사용해서 많은 데이터를 한번에 입력하는 방법

SECTION 013 여러 시트의 동일 범위에 하나의 이름 부여하기

SECTION 014 시트를 구분하지 않는 이름 만들기

SECTION 015 다른 시트나 다른 워크북의 셀을 참조하는 방법

SECTION 016 날짜와 시간의 계산 원리

SECTION 017 배열 상수와 배열식의 의미



PART 02 날짜와 시간 처리하기

SECTION 018 불규칙한 날짜를 빠르게 입력하려면

SECTION 019 2개의 날짜 간격을 년수, 월수, 일수로 계산하려면

SECTION 020 총 작업시간이 주어진 프로젝트 마감일을 계산해요

SECTION 021 오늘의 당직자를 자동으로 체크하려면

SECTION 022 휴일을 제외한 근무일수를 구해 임금을 지불하려면

SECTION 023 빠른 마감일을 찾아 작업 착수일을 결정하려면

SECTION 024 주민등록번호로 출생년도와 나이, 성별을 구하려면

SECTION 025 주민등록번호로 "1966년 10월 5일생"처럼 기록하려면

SECTION 026 수 많은 날짜에 한번에 분기를 표시하려면

SECTION 027 3월 결산, 9월 결산 법인의 경우에 분기 계산은?

SECTION 028 토요일, 일요일을 피해서 월말 결제일을 구해주세요

SECTION 029 날짜에서 해당 월의 일수를 구해 일일 평균을 구하려면

SECTION 030 다양한 기간의 어음 결제일을 구하려면

SECTION 031 기준일로부터 며칠, 몇 개월 후의 날짜를 구해요

SECTION 032 *월 *번째 *요일에 열리는 만년 행사표를 만들려면

SECTION 033 텍스트로 입력된 날짜를 엑셀의 날짜로 변신시켜요

SECTION 034 스핀 단추를 눌러 날짜를 설정하려면

SECTION 035 스크롤 막대로 날짜 구간을 지정하려면

SECTION 036 이 날은 올 초부터 따져서 몇 번째 주일까?

SECTION 037 다양한 과년도 날짜를 올해 날짜로 바꾸려면?

SECTION 038 숫자만으로 시간을 빨리 입력하려면

SECTION 039 2개 열의 숫자로 "시간:분" 만들고 총계를 구하려면

SECTION 040 시간을 구하는데 음수 때문에 에러가 나와요!

SECTION 041 시간을 1분, 1시간, 10분, 30분 단위로 정리하려면

SECTION 042 변치 않는 현재 날짜와 시간을 자동으로 기록하려면

SECTION 043 6개 열의 숫자로 년, 월, 일, 시, 분, 초 형식을 만들려면

SECTION 044 1개 셀에 기록된 날짜와 시간을 2개 열로 분리하려면

SECTION 045 날짜와 시간별로 일정표를 만들려면

SECTION 046 밤 12시를 넘는 임금을 계산하려면

SECTION 047 평일, 주말, 오버타임을 구분해서 임금을 계산하려면

SECTION 048 310분은 몇 시간 몇 분인가?

SECTION 049 52시간 20분은 며칠, 몇 시간, 몇 분?



PART 03 합계를 구하거나 집계표 만들기

SECTION 050 데이터가 입력된 행까지만 누계를 표시하려면

SECTION 051 - (음수)까지 모두 더해서 변동 회원수를 구하려면

SECTION 052 중간 합계를 제외하고 합계를 구하는 방법은?

SECTION 053 정수로 표시된 소수의 합계를 제대로 계산하려면

SECTION 054 특정 표시가 된 데이터만 합계를 구하려면

SECTION 055 기준 값 이상의 데이터와 기준 값 미만의 데이터 개수를 세려면

SECTION 056 여기저기 분산 기록된 직원의 판매량 합계를 구하려면

SECTION 057 판매 기록에서 사원별 집계표와 제품별 집계표를 만들려면

SECTION 058 판매 데이터를 제품별/사원별 2차 테이블로 정리하려면

SECTION 059 콤보상자로 선택해서 사원/제품별 판매액과 건수 구하기

SECTION 060 원본표에 데이터가 추가되면 자동 수정되는 집계표는?

SECTION 061 데이터를 요일별, 월별로 집계하려면

SECTION 062 여러 장의 월별 집계 시트를 빠르게 만들려면

SECTION 063 콤보 상자에서 선택해서 월별 집계표를 만들려면

SECTION 064 스크롤 막대로 날짜 구간별 집계표를 만들려면

SECTION 065 10월, 11월, 12월 시트를 더해서 4분기 시트를 만들려면

SECTION 066 500KB 처럼 단위가 있는 데이터의 단위별 합계는?

SECTION 067 데이터 순서가 다른 3개 표의 총괄표를 만들려면

SECTION 068 특정 데이터만 제외하고 합계를 구하려면

SECTION 069 특정 구간별로 합산하려면

SECTION 070 여러 개 표의 서로 다른 항목의 값을 더하려면

SECTION 071 처음부터 지정한 항목까지만 합계를 구하려면

SECTION 072 최상위 3개나 최하위 3개의 합계와 %를 구하려면

SECTION 073 특정 글자가 있는 데이터만 더하려면

SECTION 074 입력되는 금액과 수량의 누계가 계속 표시되게



PART 04 개수나 횟수 세기

SECTION 075 데이터 종류별로 건수를 구하려면

SECTION 076 데이터를 중복되지 않게 세려면

SECTION 077 0은 제외하고 세어서 평균을 구하려면

SECTION 078 평균 미만, 또는 평균 이상인 데이터의 개수는?

SECTION 079 딱 1번만 등장하는 데이터의 개수를 알려면?

SECTION 080 사원별로 당직 일수를 세어서 수당을 구하려면

SECTION 081 제품 코드에 섞여있는 숫자로 규격별 개수를 세려면

SECTION 082 특정 일 이후의 데이터 개수만 구하려면

SECTION 083 2차원으로 데이터 개수를 구하려면

SECTION 084 요일별, 월별로 데이터 개수를 구하려면?

SECTION 085 업체별 월별로 매출 건수를 구하려면

SECTION 086 7월 중 금요일에 5만원 이상 주문한 데이터 개수는?

SECTION 087 8월1일에서 8월10일 사이에 5만원 이상 판매한 건수는?

SECTION 088 시간대 별로 데이터 개수를 구하려면

SECTION 089 소수점 있는 데이터를 정수 구간 별로 카운트하려면

SECTION 090 특정 구간별로 개수를 구하려면

SECTION 091 값이 없는 데이터를 제외한 구간별 데이터 개수?

SECTION 092 특정 날짜와 시간에 속하는 데이터의 개수는?

SECTION 093 30분 단위로 나누어서 데이터 개수를 구하려면

SECTION 094 특정 월에 작성된 데이터의 개수를 세려면

SECTION 095 자료를 입력할 때 개수와 총액이 계속 표시되게

SECTION 096 도시별 데이터를 도별로 세려면?

SECTION 097 필터로 선택한 데이터만 개수와 합계를 구하려면

SECTION 098 주문횟수가 제일 많은 제품 이름과 주문 횟수는?

SECTION 099 기준 값이 바뀌면 다시 1부터 번호를 부여하려면

SECTION 100 빈 행은 제외하고 일련번호를 부여하려면



PART 05 텍스트 처리하기

SECTION 101 텍스트와 함께 기술한 숫자에 서식을 지정하려면?

SECTION 102 표의 제목과 오늘의 날짜를 같이 표시하려면

SECTION 103 텍스트 중간에 함수식을 사용하면 서식은 어떻게?

SECTION 104 4개 행에 기록된 값들을 묶어서 1개 행에 기록하려면

SECTION 105 코드를 참고해서 크기, 색상 등의 정보가 표시되게 하려면

SECTION 106 수량이 비어 있으면 금액도 공백이 되게 하려면

SECTION 107 괄호를 전부 없앤 후 "일반" 서식을 지정하려면

SECTION 108 1개 셀의 완전한 주소를 시, 구, 동 별로 소트하려면?

SECTION 109 주소에서 시, 구, 동까지만 추출하려면?

SECTION 110 지역과 상호를 분리해서 기록하려면

SECTION 111 "-" 앞부분 숫자만 10을 곱해 수정하려면

SECTION 112 "-" 문자 뒤의 부품코드로 단가를 입력하려면

SECTION 113 두 번째 "-" 문자 뒤의 값만 추출하려면

SECTION 114 완전한 주소에서 특정지역은 배송료에 "없음"을 기록하려면?

SECTION 115 전화 통화 목록에서 시외전화를 구분하려면

SECTION 116 "부장 이병재"를 "이병재 부장"으로 바꾸려면

SECTION 117 콤마로 구분된 데이터의 개수를 구하려면

SECTION 118 주민등록번호에서 뒷자리를 "*"로 표시하려면

SECTION 119 표의 제목과 차트의 도형 제목이 함께 바뀌려면

SECTION 120 양방향 문자 차트를 만들려면

SECTION 121 영문자의 첫 번째 문자만 대문자로 조절하려면

SECTION 122 사업자 번호를 "-"로 분리해 12개의 칸에 기록하려면

SECTION 123 세금계산서처럼 뒷자리부터 한 칸씩 채우려면?

SECTION 124 불필요한 공백을 정리하려면

SECTION 125 문자 사이에 "-" 문자를 삽입하려면



PART 06 값을 찾거나 가져오기

SECTION 126 데이터를 추가, 삭제하면 같이 변하는 콤보상자?

SECTION 127 주문번호만 선택하면 자동으로 완성되는 주문장

SECTION 128 제품을 선택하면 단가가 자동으로 입력되게

SECTION 129 운행 거리에 따라 임금을 지불하려면?

SECTION 130 우대고객과 일반고객을 구분해 할인율을 적용하려면?

SECTION 131 수평 테이블에서 직급별 기본급과 수당을 가져오려면

SECTION 132 기준표 없이 점수별 평점을 구하려면?

SECTION 133 사원 이름으로 인사 정보를 조회하려면

SECTION 134 직급별, 호봉별로 기본급을 가져오려면

SECTION 135 회사별 제품별 가격 조견표를 만들려면

SECTION 136 분기별로 최고 실적자와 최저 실적자를 찾으려면

SECTION 137 6개의 영업소 표에서 품목별 판매량을 가져오려면

SECTION 138 1반의 홀수, 2반의 짝수 학생은 1고사장으로

SECTION 139 3번째 등장하는 특정 항목의 데이터를 가져오려면

SECTION 140 3가지 항목으로 구분해 정보를 가져오려면

SECTION 141 3번째로 많이 팔린 제품의 정보를 가져오려면

SECTION 142 특정 시트의 값이 변하면 총괄 시트의 값도 변하게

SECTION 143 다른 시트에 있는 영업소별 매출표를 가져오려면

SECTION 144 제품별로 최고액 구매자 정보를 조회하려면

SECTION 145 2종류의 가격표에서 특정 제품의 가격을 찾으려면

SECTION 146 대리점 이름을 입력해서 그 대리점의 지역을 찾으려면

SECTION 147 제일 유사한 납품 가격과 업체 이름을 찾으려면

SECTION 148 이름을 선택해서 사진과 신상 정보를 가져오려면

SECTION 149 OA를 선택하면 OA 서적만 표시하는 이중 콤보상자

SECTION 150 한번 선택한 항목은 표시하지 않는 콤보상자?



PART 07 서식과 콤보상자로 데이터 관리하기

SECTION 151 1개 셀씩 건너서 색을 칠하려면

SECTION 152 1개 행씩 건너서 색을 칠하려면

SECTION 153 1개 열씩 건너서 색을 칠하려면

SECTION 154 2개, 3개 행씩 묶어서 색을 칠하려면

SECTION 155 2개, 3개 열씩 묶어서 색을 칠하려면

SECTION 156 3번째 행, 4번째 행마다 한번씩 색을 칠하려면

SECTION 157 2번째 열, 3번째 열마다 색을 칠하려면

SECTION 158 토요일과 일요일에만 노란색을 칠하려면

SECTION 159 특정 년 특정 월의 데이터만 굵게 표시하려면

SECTION 160 새로운 월이 시작될 때마다 색을 칠하려면

SECTION 161 특정 값 이상인 데이터만 색으로 구분하려면

SECTION 162 평균 값 이상인 특정 데이터를 구분하려면

SECTION 163 최대 값은 파란색, 최저 값은 빨간색으로 기록하려면

SECTION 164 값을 크기별로 3개 그룹으로 나누어 구분하려면

SECTION 165 제일 큰 값 3개와 제일 작은 값 3개를 구분하려면

SECTION 166 2번 또는 3번 이상 중복된 데이터를 구분하려면

SECTION 167 1번만 등장한 특정 데이터를 구분하려면

SECTION 168 콤보상자에서 선택한 데이터만 동적으로 색이 칠해지게

SECTION 169 데이터를 입력하는 행에 자동으로 테두리가 쳐지게

SECTION 170 중복 입력을 방지하려면

SECTION 171 토요일, 일요일의 입력을 거부하려면

SECTION 172 "A"로 시작해서 2자, "B"로 시작해서 3자만 입력되게

SECTION 173 숫자만 또는 문자만 입력되게 하려면



PART 08 원하는 데이터만 추출하거나 값 구하기

SECTION 174 필터링할 때마다 다시 일련번호를 부여하려면

SECTION 175 추출한 데이터만 합계 금액과 거래 건수 등을 구하려면

SECTION 176 데이터를 추출해서 여러 항목의 통계 값을 구하려면

SECTION 177 데이터를 추출할 때마다 새로운 누계를 기록하려면

SECTION 178 인위적으로 숨기는 행을 합계에서 제외하려면

SECTION 179 특정 값 이상 특정 값 미만인 데이터만 골라내려면

SECTION 180 2개의 조건으로 데이터를 추출하려면

SECTION 181 2월의 자전거나 부천대리점의 롱스키만 추출하려면

SECTION 182 2월과 3월의 자전거 데이터만 추출하려면

SECTION 183 자전거 200대 이상, 롱스키 300대 미만만 추출하려면

SECTION 184 특정 년월부터 특정 년월까지의 데이터만 추출하려면

SECTION 185 평균 이상 데이터만 추출하려면

SECTION 186 2번 이상 중복되는 데이터만 추출하려면

SECTION 187 고급 필터에서 버튼만 누르면 데이터가 추출되게

SECTION 188 거래처와 상품명을 선택해 총 판매량과 총 금액을 구하려면

SECTION 189 거래처를 선택해서 총 금액과 최대, 최저 금액을 구하려면

SECTION 190 단가와 판매량을 기준으로 거래건수와 평균금액을 구하려면

SECTION 191 특정 월일부터 특정 월일까지의 데이터 개수와 값의 합계를 구하려면

SECTION 192 항목을 선택해서 해당 데이터를 조회하려면

SECTION 193 내 맘대로 조건을 구성하는 자동 검색기를 만들려면?

SECTION 194 특정 날짜로부터 특정 일 이후의 값만 계산하려면?



PART 09 자동화된 실무 차트 만들기

SECTION 195 콤보 상자로 선택한 월의 차트를 그리려면

SECTION 196 옵션 단추로 선택해서 해당 데이터의 차트를 그리려면

SECTION 197 목록 상자로 선택해서 해당 데이터의 차트 그리기

SECTION 198 스크롤 막대로 선택한 값의 구간만 차트를 그리려면

SECTION 199 행을 선택하면 그 행의 데이터로 차트가 그려지게

SECTION 200 표에 추가되는 데이터도 자동으로 차트가 그려지게

SECTION 201 몇 개씩 데이터를 건너뛰면서 차트를 그리려면

SECTION 202 항상 마지막 7개 데이터만 차트를 그리려면

SECTION 203 온도계 차트를 만들려면

SECTION 204 게이지 차트를 만들려면

SECTION 205 좌우 대칭형 차트를 만들려면

SECTION 206 위, 아래 대칭형 차트를 만들려면

SECTION 207 간트 차트를 그리려면

SECTION 208 추세선을 표시하는 식을 표시하려면



PART 10 데이터 목록 처리하기

SECTION 209 함수식으로 오름차순(내림차순) 소트를 하려면

SECTION 210 역순으로 기록하려면

SECTION 211 중복되지 않게 데이터를 뽑으려면

SECTION 212 2개의 목록에서 2번째 목록에 없는 값만 추출하려면

SECTION 213 1차, 2차 표에 모두 있는 값만 추출하려면

SECTION 214 평균보다 큰 값만 추출하려면

SECTION 215 중복된 입력된 데이터를 골라내려면

SECTION 216 데이터 목록에 몇 번 등장하는지 알려면

SECTION 217 0은 제외하고 평균을 구하려면?

SECTION 218 평균에 가장 근접한 값은?

SECTION 219 특정 값 이상만 평균을 구하려면

SECTION 220 제품별로 수량 합계와 평균을 구하려면

SECTION 221 요일별로 판매액 합계와 평균을 구하려면

SECTION 222 동적으로 상위 N개의 합계와 평균을 구하려면

SECTION 223 동적으로 N개 이내에서 최대, 최소 금액을 구하려면

SECTION 224 팔릴 때마다 자동으로 재고가 조절되려면

SECTION 225 석차를 올바로 또는 거꾸로 구하려면

SECTION 226 데이터를 입력, 삭제할 때마다 동적으로 석차를 구하려면

SECTION 227 반별 석차와 학년별 전체 석차를 구하려면

SECTION 228 동점일 때 특정 점수로 석차를 조절하려면

SECTION 229 금액을 100원, 10원에서 반올림하려면

SECTION 230 금액을 100원 단위로 정리하려면

SECTION 231 소수 이하 자리에서 반올림, 올림, 내림을 하려면



PART 11 재무 계산 해결하기

SECTION 232 2천만원을 년 4%의 이율로 3년간 예치하면 얼마?

SECTION 233 500만원을 년리 10%로 예치하면 언제 1억이 될까?

SECTION 234 매월 50만원씩 월이율 1%로 적립하면 3년 후에는 얼마?

SECTION 235 천만원을 년리 7%로 3년간 빌리면 얼마를 갚아야 하나?

SECTION 236 당좌대월 상태에서 50만원씩 적립해서 1억이 되는 기간은?

SECTION 237 2년 후에 받을 3천만원의 권리는 얼마에 양도할 수 있을까?

SECTION 238 년간 4800만원의 임대수익이 있는 상가를 얼마에 사야?

SECTION 239 4개월 만기의 천만원짜리 어음을 950만원 받으면 할인율은?

SECTION 240 매월 250만원의 임대료 받는 상가는 얼마주고 사야 하나?

SECTION 241 2억에 산 임대 상가를 5년 후 3억에 팔면 수익률은?

SECTION 242 1억 6천만원짜리 임대 상가의 5년 후 매각 금액은?

SECTION 243 2천만원을 월 0.5% 이율로 3년 동안 상환하면 월 납입액은?

SECTION 244 월 상환 금액에 맞춰 대출 가능 금액을 알아보려면?

SECTION 245 주택 담보 대출의 상환 기간을 계산하려면?

SECTION 246 천만원을 빌리고 월 100만원씩 12개월 갚으면 이율은?

SECTION 247 2년 만기 7% 이율로 대출시 매회 이자는?

SECTION 248 2년 만기 7% 이율로 대출시 매회 상환 원금은?

SECTION 249 회차별 대출 원금, 이자, 누적 납입금, 잔액은?

SECTION 250 4회에서 6회까지 대출 이자 총계를 구하려면?

SECTION 251 4회에서 6회까지 대출 상환 원금 총계를 구하려면?

SECTION 252 정액법으로 차량의 감가상각비를 구하려면?

SECTION 253 정률법으로 차량의 감가상각비를 구하려면?

SECTION 254 년수 합계법으로 차량의 감가상각비를 구하려면?

SECTION 255 이중 체감법으로 차량의 감가상각비를 구하려면?

SECTION 256 명목 금리와 실질 금리를 변환하려면



PART 12 논리, 에러, 정보 처리하기

SECTION 257 점수대별로 평점을 부여하려면

SECTION 258 60점 미만의 과락 점수에 대해 "불합격" 처리하려면

SECTION 259 행사 참석자와 불참자를 구분하려면

SECTION 260 "서울", "경기"의 "디카" 구매자에게만 경품을 주려면

SECTION 261 한 과목이라도 공백이면 "재시험"을 기록하려면

SECTION 262 시간 계산에서 음수가 나오지 않게 하려면

SECTION 263 VLOOKUP 함수 사용시 #NA 에러를 없애려면

SECTION 264 작업 중인 시트의 완전한 경로 이름을 알려면

SECTION 265 현재 작업 중인 시트의 이름만 표시하려면

SECTION 266 작업 중인 엑셀 파일의 이름을 표시하려면



PART 13 업무에 바로 써먹는 실무 문서 만들기

SECTION 267 매입매출장 만들기

SECTION 268 금전출납부 만들기

SECTION 269 견적서와 거래명세서 만들기

SECTION 270 세금계산서 만들기

이용학