아래 자료를 기준으로 먼저 날짜 관련 함수를 총정리해보겠습니다.
1. 날짜 연산
① +, - 를 이용하여 날짜를 더하고 빼기
㈀ 일단 B열과 C열 사이에 2개 열을 삽입하기 위해 C라고 있는 부분을 마우스로 클릭 후 D열까지 끈 다음
㈁ 마우스 오른쪽 버튼을 눌러 삽입 메뉴를 누릅니다.
㈂ 그럼 아래와 같이 C열과 D열이 빈 칸으로 되고, C열부터 오른쪽으로 2칸씩 이동합니다.
㈃ C5셀에 더하기, D5셀에 빼기라고 입력해 보겠습니다.
㈄ C4셀에 10, D4셀에 -10이라고 입력합니다.
㈅ 그리고, C6셀에 =b6+c4 라고 입력한 다음 엔터키를 치지 말고
㈆ F4키를 눌러 $C$4라고 더하는 셀 주소가 변경되지 않도록 절대 주소로 바꾼 다음, 엔터키를 칩니다. 엔터키를 치지 않고, D6셀이나, C7셀 등 다른 셀을 마우스로 눌러도 됩니다.
㈇ D6셀에 복사했을 때도 적용될 수 있도록 하려면, C6셀의 수식을
=$b6+c$4
라고, 바뀌지 말아야 할 것 앞에 $표시를 넣어 수정하는 것이 바람직합니다.
$b6라고 $표시를 Shift + 4 키로 입력해도 되지만,
b6 란 글자에(앞,중간,다음 어디든 관계 없음) 커서를 놓고, F4키를 이용해서 한번 누르면 $B$6가 되고, 한번 더 누르면 b$6, 그 다음은 $B6, 그 다음은 B6로 바뀝니다. 따라서, F4키를 세번 누르고,
$C$4를 C$4로 변경하기 위해서는 $C$4 부분에 커서를 놓고, F4키를 한번 누르면 됩니다.
㈈ C6셀의 수식을 D30셀까지 복사하기 위해 채우기 핸들을
오른쪽으로 끌고,
㈉ D6셀의 채우기 핸들을 D30셀까지 끕니다.그러면 아래와 같이 채우기가 완료됩니다. D30셀의 수식은 뭘까요?
=$B30+D$4
입니다.
13-09-12에서 10을 빼니 13-9-2이 맞고, 10을 더하면 13-9-22이 맞습니다.
② workday 함수를 이용해 더하고 빼기
workday 함수는 토,일요일 및 휴일을 제외한 근무일을 기준으로 시작일로부터 몇 일 전·후의 날짜를 구해주는 함수로, WORKDAY(start_date, days, [holidays]) 형식으로 입력하므로, 시작일, 날짜수, 휴일 이라는 세 인수가 있는데, 휴일은 입력하지 않아도 되지만, 신정 연휴, 설 연휴, 추석 연휴 등 우리나라에 특별한 휴일은 별도로 입력해 주는 것이 정확한 결과값을 가져옵니다.
workday함수를 이용한 결과값을 검증하는데 10일은 검증하는데 오래 걸리니 5일 전후의 날짜를 구해 봅시다.
㈀ 먼저 E열과 F열 사이에 빈 열 2개를 삽입하고,
E3셀에 workday라고 입력하고, E3셀과 E4셀을 선택하고, '병합하고 가운데 맞춤 아이콘을 누른 다음,
㈁ E4셀에 5, F4셀에 -5를 입력하고,
E5셀에 더하기, F5셀에 빼기라고 입력합니다.
㈂ 휴일 데이터를 입력해 보겠습니다.
G열을 누른 후 삽입 메뉴를 눌러 열을 추가합니다.
그리고, 휴일 날짜를 입력하는데, 2013-02-11 입력시 20은 입력하지 않고 13으로 입력해도 되며, 13-2-11 처럼 하이픈으로 연결하고, 02가 아닌 2로 입력해도 되고, 13/2/11처럼 /로 연결하거나, 13-2/11처럼 -과 /를 혼합해서 입력할 수도 있습니다. 또한 올해인 경우는 연도는 입력하지 않고 날짜만 입력해도 저절로 연도가 올해로 채워집니다.
그럼 아래와 같이 입력해 봅시다.
㈃ workday 함수를 이용해
E6셀에
=workday($b6,e$5,$g$6:$g$17)
이라고 입력해 봅시다.
=wo 라고만 입력해도 아래와 같이 wo로 시작하는 함수가 제시되므로, workday를 마우스로 더블클릭하거나, 아래 화살표키를 눌러 workday로 이동한 다음 Tab키를 쳐서 쉽게 입력할 수 있습니다.
그리고, b6셀을 마우스로 선택한 다음 F4키를 세번 누르고, 콤마를 입력한 다음, E5셀을 선택하고 F4를 두번 누르고 다시 콤마를 입력한 다음, 마우스로 G6셀에서 G17셀까지 끌어 휴일 범위를 선택한 다음, F4키를 눌러 절대주소로 바꾼 후 오른쪽 괄호를 입력하고 엔터키를 칩니다.
만약 G6셀에서 G17셀을 선택시 G6셀이 수식으로 가려서 선택이 잘 안된다면 G17셀을 먼저 선택하고 위로 끌어도 됩니다.
2013-01-01에서 5일후가 2013-01-08이라고 합니다.
2013년 1월 달력을 보니 근무일이 2,3,4,7,8일이라 2013-01-08이 맞습니다.
㈄ 휴일 데이터가 있지만 2013-01-02이 휴일이라고 가정하고 2013-01-02만 휴일로 입력하려면
=workday($b6,e$4,"2013-01-02")
라고 휴일 데이터를 큰 따옴표로 감싸서 입력합니다.
그러면 2일이 휴일이기 때문에 근무일은 3,4,7,8,9가 5일이기 때문에 답이 13-01-09가 됩니다.
㈅ 되돌리기 아이콘을 눌러 원래 수식으로 돌립니다.
㈆ E6셀의 채우기 핸들을 끌어 F6셀을 채우고, 다시 F6셀의 채우기 핸들을 끌어 F30셀까지 복사합니다.
2013-01-12를 기준으로 5일 후는 위 달력을 보면 2018-01-18이므로 답이 맞고, 5일 전은 2013-01-07이 되므로 맞습니다.
③ edate 함수를 이용해 월 더하고 빼기
edate함수는 시작일로부터 몇 개월 전,후의 날짜를 반환해 주는 함수이며, edate(start_date, months) 식으로 입력하므로 시작일과 월수 2개 인수가 필요합니다.
㈀ H열과 I열을 선택한 후 마우스 오른쪽 버튼을 누른 후 삽입 메뉴를 눌러 빈 열 2개를 삽입합니다.
H3셀에 '월수'라고 입력하고 '병합하고 가운데 맞춤' 아이콘을 눌러 셀을 병합한 다음,
H4,I4셀에 각각 5, -5라고 입력하고,
H5,I5셀에 각각 더하기, 빼기라고 입력합니다.
㈁ =e 라고만 입력해도 아래와 같이 e로 시작하는 함수가 나열되는데, edate함수가 선택되어 있으므로 Tab키를 누르면 edate 다음의 왼쪽 괄호까지 자동으로 입력되어 =EDATE( 라고 표시됩니다.
㈂ =EDATE( 아래에 EDATE(start_date, months) 라고 함수 형식이 제시되어 입력에 도움을 주고 있습니다.
㈃ =edate( 가 입력된 상태에서 B6셀을 선택하고 F4키를 세번 누르고, 콤마 입력후 H4셀을 선택하고 F4키를 두번 누른 다음 오른쪽 괄호를 닫고 엔터키를 쳐서 아래 수식을 완성합니다.
= EDATE($B6,H$4)
㈄ 그럼 결과값이 5개월 후인 2013-06-01이 표시됩니다.
㈅ 채우기 핸들을 끌어 I6셀까지 채우고, 다시 I6셀의 채우기 핸들을 끌어 I30셀까지 채웁니다. 평일인지, 주말인지 관계없이 몇 개월 전,후 날짜를 계산해 주기 때문에 검증하기는 쉽습니다.
④ eomonth 함수를 이용해 몇 개월 전, 후의 말일 구하기
eomonth함수는 시작일로부터 몇 개월 전,후의 말일(End Of Month)을 구하는 함수이며, eomonth(start_date, months) 식으로 입력하므로 시작일과 월수 2개 인수가 필요합니다. 따라서, edate 함수와 인수가 동일합니다.
㈀ 함수명만 다르므로, H열과 I열을 선택한 후 마우스 오른쪽 버튼을 눌러 복사 메뉴를 누르고,
㈁ J열의 J라는 글자부분에 마우스 커서를 놓고 마우스 오른쪽 버튼을 누르거나, J라는 글자 부분을 눌러 J열을 선택한 다음 마우스 오른쪽 버튼을 눌러 아래와 같은 컨텍스트 메뉴가 나오도록 한 다음, '복사한 셀 삽입' 메뉴를 클릭합니다.
㈂ 그러면 아래와 같이 '거래처'이후 기존 열을 오른쪽으로 두 칸씩 밀어내고, H,I열의 내용이 J,K열에 붙여집니다.
㈃ 입력 인수가 같으므로 함수명만 바꾸면 됩니다.
따라서, Ctrl + H키를 누르거나, 홈 - '찾기 및 선택' - 바꾸기 메뉴를 눌러
바꾸기 대화상자가 나타나도록 합니다.
㈄ '찾을 내용'에는 edate 라고 입력하고, 바꿀 내용에는 eomonth 라고 입력하고 '모두 바꾸기' 버튼을 누릅니다.
㈆ 그러면 아래와 같이 '50개 항목이 바뀌었습니다'란 메시지가 나옵니다. 왜 50개 일까요? H열과 I열을 복사해서 J,K열에 붙여 넣었으므로 edate가 H열부터 K열까지 25행 * 4열 = 100개가 바뀌었어야 할 텐데, 50개만 바뀐 것은 J열과 K열만 선택된 상태이기 때문입니다. 다시 말해 바꾸기는 선택된 범위 안에서만 이루어집니다.
㈇ 확인 버튼을 누르고, 닫기 버튼을 누른 후, J6셀의 수식을 살펴보면
=eomonth($B6,J$4)
로 맞게 들어가 있으며,
결과값이 2013-06-30인데, 시작일인 2013-01-01로부터 5개월후 말일이므로 결과값도 맞습니다.
K6셀의 값도 -5이므로 2012-08-31로 맞습니다.
'Excel' 카테고리의 다른 글
| 엑셀 날짜 관련 함수 총정리(4) (today, now, weekday, weeknum, yearfrac 함수) (0) | 2020.07.17 |
|---|---|
| 엑셀 날짜 관련 함수 총정리(3) (year, month, day, hour, minute, second, date, time 함수) (2) | 2020.07.16 |
| 엑셀 날짜 관련 함수 총정리(2) (days, networkdays, datedif 함수) (0) | 2020.07.15 |
| 엑셀 NPV(순 현재가치)와 IRR(내부 수익율) 함수 (0) | 2020.07.08 |
| 엑셀 PV(Present Value, 현재가치), FV(Future Value, 미래가치) 함수 (0) | 2020.07.08 |