목차

1. 단순 날짜 연산

2. DAYS, DAYS360 함수 - 기간을 계산

3. DATEDIF 함수 - 기간을 일 단위로 계산

4. EDATE, EOMONTH 함수 - 몇달이 지났는지 세는 함수

지난번에 날짜 서식의 기본에 대해서 포스팅했습니다.

서식에 대해서는 설명 드렸기 때문에 오른쪽링크 참고 하시면 됩니다.(날짜 서식과 기본 함수들)

하지만 단순히 연도, 월, 일자를 표기하는 것을 넘어서 기간을 계산하는 강력한 함수들도 있습니다.

이걸 사용하면 달력을 펴고 날짜를 일일히 셀 필요는 없다는 것이죠.

1. 단순 날짜 연산

지난번 포스팅에서도 설명을 했지만 날짜서식이라고 쳐도 기본적으로는 숫자입니다.

그래서 사칙연산이 먹힙니다. 중복이기는 하지만 잠시 집고 넘어가겠습니다.

2. DAYS, DAYS360 함수 - 기간을 계산

DAYS(end_date, start_date) : start_date에서 end_date 사이가 몇일인지 계산합니다.

• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• 윤년과 월에 따른 일자를 포함하여 계산함으로 편리합니다.
  특히 계산하고자 하는 날짜가 수백일이 되면 이 함수만큼 좋은게 없습니다.
• 이 함수는 2013버전에 추가되었습니다. 사용할 수 없을 수 있습니다.

텍스트 "2022-3-1"처럼 일부 날짜 형식은 알아서 변환합니다.

입력된 날짜 서식들은 그냥 빼기를 하는 것과 같은 결과가 나옵니다.

여러 엑셀 파일이나 홈페이지, 외부문서에서 값을 복사해서 붙여넣는 경우에는 날짜처럼 보이지만 텍스트처럼 인식되는 경우가 있는데 이럴때 편리합니다. 하지만 "3월 2일" 같은 형식의 텍스트는 인식하지 못하네요.

DAYS360(start_date,end_date,[method]) : 한달을 무조건 30일로 1년을 360일로 계산하는 방식에서 기간을 구합니다.

• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• [method] : TRUE는 유럽식, FALSE는 미국식입니다.
  TRUE : 종료일자가 31일인 경우에는 무시합니다.(30일로 계산합니다.)
  FALSE : 종료일자가 31일인 경우 포함합니다.(다음달 1일로 계산합니다.) - 기본값입니다.
• 윤년과 월에 따른 일자를 포함하지 않습니다..

아직 문서로 처리하던 시절에 일일히 30, 31일로 반영해서 계산하면 오류가 너무 심해서 전부 30일로 변환해서 처리했죠.

아직도 일부에서는 사용하는 방법인데 요즘은 컴퓨터가 다 계산해 주기때문에 오히려 이게 어렵습니다.

아래 예를 보면 잘 이해되실꺼에요.

DAYS360은 2월이나 3월을 전부 30일로 간주함으로 날짜가 지나도 일수가 안늘어나는 기간이 생기게 됩니다.

그때 처리방법이 True와 False로 나타냅니다.

3. DATEDIF 함수 - 기간을 일 단위로 계산

DATEDIF(start_date,end_date,unit) : 시작날짜(start_date)와 종료일자(end_date) 사이의 연, 월, 일을 반환한다.

• start_date : 계산하고자 하는 기간의 시작 날짜입니다.
• end_date : 계산하고자 하는 기간의 마지막 날짜입니다.
• unit : 반환하려는 정보의 양식입니다.

기간의 차이를 "월"이나 "연도"로만 나타낼 수 있습니다.

예를 들어 unit YM을 이용하면 2002-03-08에서 2012-07-01은 3이 됩니다.(연도는 무시 개월만 표기)

엑셀 도움말에는 아직 최적화가 다 되있지 않다는 설명이 있습니다.

반드시 필요할 때만 사용하는 것이 좋겠네요.

4. EDATE, EOMONTH 함수 - 몇달이 지났는지 세는 함수

일자야 어째든 월이 중요한 경우가 있습니다.

입사하고 몇달이 지났는지 등등 말이죠.

그런 상황에서 사용하는 함수들입니다.

EDATE(start_date, months) : 일자(start_date)에서 달이 지난 만큼을 반환합니다.

• start_date : 시작 날짜입니다. 날짜는 DATE 함수를 사용하거나 날짜 서식을 참조하거나 입력합니다.
• months : start_date 전이나 후의 개월 수입니다. 앞으로의 날짜는 양수로, 지나간 날짜는 음수로 입력하면 됩니다.
• 30일이나 31일짜리 달을 무시하고 몇달 지난 날짜를 반환합니다.

EOMONTH(start_date, months) : 일자(start_date)에서 달이 지난 만큼의 월 마지막 날을 반환합니다.

• start_date : 시작 날짜입니다. 날짜는 DATE 함수를 사용하거나 날짜 서식을 참조하거나 입력합니다.
• months : start_date 전이나 후의 개월 수입니다. 앞으로의 날짜는 양수로, 지나간 날짜는 음수로 입력하면 됩니다.
• 30일이나 31일짜리 달을 무시하고 month에 입력된 숫자만큼 지난 달의 마지막날을 반환합니다.
• 설명이 어려워서 그렇지 유용한 함수로 몇일에 입사를 하든지 월급 나오는 날은 같을 경우 등에서 사용됩니다.

단순 숫자를 서식에 따라서 표현할 수 있게 하는 것은 엑셀의 가장 중요한 기능 중 하나입니다.

그 중 날짜는 우리가 데이터를 구별하고 정리하는데 가장 중요한 요소 중 하나입니다.

셀서식의 날짜나 매뉴에 표시형식이 있지만 자동으로 날짜서식으로 변환되기도합니다.

자동으로 넣는것이 가장 유용한데 "2/3"이라고 입력하면 자동으로 (올해) 2월 3일이라고 변환됩니다.

아래처럼 셀서식을 이용하거나 매뉴의 보기형식을 변경할 수도 있습니다.

1. 날짜 서식

엑셀은 숫자 "1"을 1900년 1월 1일로 해서 "2958465"를 9999년 12월 31일까지 날짜 서식을 통해 지원합니다.

강제로 이전이나 이후를 표기하는 건 가능하지만 기간이 넘어가면 날짜 서식을 지원하지는 않습니다.

예를 들어, 숫자 45000이 날짜가 되면 2023년 03월 15일이 됩니다.

그럼 년도(YEAR) 2023, 월(MONTH) 03, 일(DAY) 15이라는 속성을 가지게 되고 함수를 이용해서 속성들을 따로 표기하거나 연산하는 등의 제어를 할 수 있습니다.

윤년과 연도를 포함하여 달력이 잘 반영되어 있습니다.

그래서 날짜 서식에 사칙연산도 적용됩니다.(곱셈과 나눗셈은 위험하고 더하고 빼기만 사용하겠습니다.)

2. 날짜 서식 함수

TODAY() : 오늘 날짜를 출력합니다. 이 함수는 인수가 없습니다. ()안이 비어있어야 합니다.

시스템 상의 오늘날짜가 출력됨으로 엑셀파일을 내일 다시 열면 갱신되어 있습니다.

매일매일 작성하는 엑셀문서에서 사용할 수 있습니다.

DATE(year,month,day) : 숫자들을 날짜 서식의 날짜로 반환합니다.

• =DATE(2021,10,17) → 2021년 10월 17일이 됩니다.
• 연도(year) : 네 자리 숫자로 이루어진 것이 좋습니다. 8이라고 입력하면 엑셀 버전에 따라 1908이나 2008이 될 수 있습니다. "2000+숫자"로 입력하는 것도 좋습니다.
• 월(month) : 1~12월을 입력합니다. 넘는 수를 넣으면 다음해로 넘어갑니다. 예를 들어 14를 넣으면 12+2로 연도가 넘어갑니다. date(21,14,08) = 22년 2월 8일이 됩니다. 0을 입력하면 작년 12월로 넘어갑니다.(음수는 더 전으로 계산합니다.)
• 일(day) : 1~31을 입력합니다. 해당 월의 마지막날을 넘어가면 다음달로 알아서 계산합니다. 0을 입력하면 지난달의 마지막날이 출력됩니다.(음수는 더 전으로 돌아갑니다.)

그 외에 날짜 서식에서 필요한 숫자만 얻을 수 있는 함수입니다.

YEAR(serial_number) : 날짜 서식에서 연도만 반환합니다.

MONTH(serial_number) : 날짜 서식에서 월만 반환합니다.

DAY(serial_number) : 날짜 서식에서 일자만 반환합니다.

표 형식으로 데이터를 정리해 두기는 하지만 특정하나의 DATA를 찾을 경우도 있습니다.

이때 사용할 수 있는 Vlookup함수과 Hlookup 함수에 대해서 소개하려고 합니다.

기능에 대해서 설명을 해보려면 좀 어렵게 적혀 있습니다.

VLOOKUP : 이 함수는 테이블의 첫 번째 열에 지정된 조회 값의 값을 기반으로 테이블의 지정된 열에서 값을 검색하는 데 사용됩니다.
HLOOKUP : 이 함수는 테이블의 첫 번째 행에 지정된 조회 값의 값을 기반으로 테이블의 지정된 행에서 값을 검색하는 데 사용됩니다.

VLOOKUP과 HLOOKUP은 행과 열만 바꼈을 뿐 같은 함수입니다.

기본적으로 VLOOKUP으로 구조를 먼저 설명하겠습니다.

VLOOKUP(lookup_value, table_arry, col_index_num, [range_lookup])

• lookup_value : 찾는 값입니다. 문자와 숫자도 모두 가능합니다.
• table_arry : 조회값이 있는 범위입니다. 찾는 값(lookup_value)이 맨 왼쪽에 있어야 동작 합니다.
• col_index_num : 출력할 값의 열 번호입니다.
• [range_lookup] : 찾는 값이 정확하게 일치하기를 원하면 False를 유사할 경우는 True를 입력합니다.
                           기본값은 True이나 문자를 기반으로 검색할 경우에는 False를 꼭 입력해야 합니다.

기능에 대해서 알아보면 아래과 같습니다.

표에서 가장 왼쪽에서 값을 찾은 다음에 오른쪽에 몇개 떨어진 값을 찾습니다.

위의 지역명과 숫자를 변경하면 서울의 2012년이나 대구의 2007년도 쉽게 찾을 수 있겠죠.

HLOOKUP(lookup_value, table_arry, col_index_num, [range_lookup])

HLOOKUP은 VLOOKUP에서 열이 행으로 변경되고 나머지는 동일 합니다.

• lookup_value : 찾는 값입니다. 문자와 숫자도 모두 가능합니다.
• table_arry : 조회값이 있는 범위입니다. 찾는 값(lookup_value)이 맨 위쪽에 있어야 동작 합니다.
• col_index_num : 출력할 값의 행 번호입니다.
• [range_lookup] : 찾는 값이 정확하게 일치하기를 원하면 False를 유사할 경우는 True를 입력합니다.
                           기본값은 True이나 문자를 기반으로 검색할 경우에는 False를 꼭 입력해야 합니다.

주의할 점이 있는데 리스트에 행이 병합되어 있으면 병행된 범위의 왼쪽 상단만 값이 있습니다.

그리고 범위 내에 똑같은 값이 여러개 있는 경우 가장 위나 왼쪽에 값만 한번 찾습니다.

그래서 사번이나 코드등 고유번호를 사용해야 하는 점이 있습니다.

조건이 좀 까다로워 보여도 익숙해지면 사용하기 편리합니다.

엑셀의 기본함수 중에서 많이 쓰이는 함수 축에 듭니다.

최근 버전의 엑셀에는 XLOOKUP이라고 VLOOKUP과 HLOOKUP을 합쳐둔 함수도 제공합니다.

< 개요 >

엑셀을 익숙해지면 계산기를 대체할 정도로 편리한 연산능력이 있습니다.
책상에 계산기가 있어도 엑셀을 켜서 쓸 정도로 버릇이 되어 있습니다.
일상적인 사칙연산을 하다보면 소수점이 발생할 수 있는데 보기에 따라서 문서가 보기 힘들어질 수 있습니다.그래서 자주쓰는 양식에는 자리수를 지정해 둬서 보기 좋게 만들 수 있습니다.
이런 소수점을 처리하는 방법에 대해서 알아보겠습니다.

1. Round 함수의 적용

함수를 적용해서 특정 소수점 자리수 이하는 짤라 낼 수 있습니다.
반올림 함수인 ROUND 함수를 소개합니다.

ROUND(number, num_digits) : 숫자를 자리수(num_digits)까지 반올림 합니다.

• number : 대상 숫자입니다.
• num_digits : 표시할 자리수입니다. 양수이면 소수점이 아래로 내려가고 음수이면 정수단위로 올라옵니다.

예시를 들어서 설명하겠습니다. 아래를 자리수가 +일때와 -일때 표기가 편리하게 바뀝니다.

ROUND 함수는 숫자자체에 반올림을 시켜서 값이 변하기 때문에 계산을 하면 변경한 값으로 적용이 됩니다.
ROUNDUP과 ROUNDDOWN도 있는데 사용법이 같은 ROUND는 반올림하지만 UP은 올림을, DOWN은 내림을 함니다.

ROUNDUP(number, num_digits) : 숫자를 자리수(num_digits)까지 올림 합니다.

ROUNDDown(number, num_digits) : 숫자를 자리수(num_digits)까지 내림 합니다.

ROUND 계열 함수들은 연산을해서 숫자 자체를 변경합니다. 

계산을 여러번 하는 경우에는 마지막에 한번만 적용해 주는 것이 정확합니다.

2. 자리수 늘림 줄임으로 표시형식 변경하기

매뉴창에 [홈] - [표시형식] - [자리수 늘림], [자리수 줄임] 누르면 한자리씩 정리합니다.

엑셀은 기본 설정상태일 때는 숫자를 치면 자동으로 숫자 서식이 적용됩니다.
숫자 서식이 적용된 칸에 늘림, 줄임 버튼을 여러 번 사용하면 소수점 표시내용을 길거나 짧게 만들수 있습니다.
여러 범위를 드레그해서 "TAP"이나 "ENTER"로 하얀 범위를 옮길 수 있는데 이때는 하얀칸을 기준으로 합니다.
마우스로 드레그할 경우 중간 소수점 자리를 스스로 계산하니 예측하기 어려울 수 있습니다.
한자리로 맞춰주기 때문에 편리합니다.

이건 서식상의 표시만 달라지지 사실 숫자는 그대로 있습니다.

적용 이후에도 계산을 하면 반영이 됩니다.

숫자 서식인 "숫자", "통화", "회계", "백분율"에도 적용할 수 있습니다.

3. 셀서식에서 표시형식 변경하기

셀을 오른쪽 클릭하면 나타나는 [셀 서식] → [표시형식] → [소수 자리수]에서 변경할 수 있습니다.

마지막으로 설명했지만 셀서식에서 변경하는 것이 근본이고 가장 원하는 방식으로 변경할 수 있습니다.

이 역시 서식상의 표시만 달라지지 사실 숫자는 그대로 있습니다.

적용 이후에도 계산을 하면 반영이 됩니다.

숫자 서식인 "숫자", "통화", "회계", "백분율"에도 자리수를 정할 수 있습니다.

※ 엑셀 2013의 매뉴를 기반으로 하며 모니터 해상도에 따라 출력되는 아이콘의 모양이 다를 수도 있습니다.

다만 소속은 거의 같으니 매뉴를 따라가시면 기능을 금방 찾을 수 있을 것 같습니다.

판매량, 생산량, 매출 같은 데이터의 변화추세를 한눈에 보는 막대챠트를 자주사용합니다.
중요한 데이터를 가지고 그래프를 그릴때 허전해 보일때가 있습니다.
그래프를 강조하는 혼합형 차트를 그려봅시다.
그냥 보기만해도 혼합형이 선이있어서 오른쪽으로 가면 값이 커지는게 강조됩니다.

혼합형을 그리는 방법은 두가지 입니다.

1. 혼합형 바로 그리기

우선 테이터를 선택하고 [삽입] - [챠트] - [혼합형]를 그립니다.
막대형과 꺽은선으로 그리는 방식이 엑셀에서 자동으로 선정하는데 좀 특이합니다. 
세로인 경우 범위에서 오른쪽에 있는 것이 가로인경우 아래 있는 DATA가 꺽은 선형으로 분류됩니다.

DATA가 2개 : (막대) + (꺽은선)
DATA가 3개 : (막대) + (막대) + (꺽은선)
DATA가 4개 : (막대) + (막대) + (꺽은선) + (꺽은선)
DATA가 5개 : (막대) + (막대) + (막대) + (꺽은선) + (꺽은선)

2. 기존 차트에서 수정하기

또 다른 방법은 우선 다중 막대 그래프로 그리고 하나만 선택해서 다른 차트 기능을 활성화 시키면 됩니다.
사실 어렵지도 않고 원하는 데로 그릴 수 있기 때문에 이 방법을 더 많이 사용합니다.
[삽입] - [차트] - [꺽은선 그래프]로 변경합니다.

이제 그래프 하나에 두가지 서식이 적용되었습니다.
설정을 변경 할 때는 한번에 한가지 범주를 선택하고 설정하면 됩니다.

그럼 이렇게 설정할 수 있습니다.

기능적으로는 별로 어렵지 않습니다.

하지만 디자인은 다른 문제라 예쁘게 챠트를 그리기가 어렵더라고요.

그래도 의사표현을 잘하기 위한 팁은 X축과 Y축을 명확하게 명시하고 혼합형그래프를 사용하는 것입니다.

저는 미적감각이 거의 없는 사람이라서 가끔 구박받고는 합니다.
하지만 이렇게 표현하면 최소한 깔끔하면서 말하고자 하는 것을 깨끗하게 할 수 있겠죠
경험상 메시지가 명확해 진 것만으로도 불만은 많이 줄어들더라고요.

이 그래프는 똑같은 그래프를 두개 그려서 하나는 막대 하나는 꺽은선으로 만든 것입니다.

제 작품중에서는 그나마 보기 깔끔하더라고요

각자 상황에서 보기 좋은 차트를 연습해 봅시다.