엑셀연구소

안녕하세요 엑셀런트입니다.

엑셀을 할 때, 알면 매우 유용한 date 함수에 대해 알아보겠습니다.

1. date함수

  (의       미) 엑셀에서 날짜를 나타내는 수 구하기(엑셀이 인식할 수 있는 날짜로 변경해주는 함수)

  (기본수식) = DATE(년도, 월도, 일자)

예제를 통해 알아보겠습니다.

Q. 위와 같이 환율데이터(B~C열)가 있을 때, 노란색 칸을 채우기위해서는?

A. A열에 B열 값을 날짜함수로 추가한 후, vlookup 사용.

(A3셀에 입력) =DATE(LEFT(B3,4),MID(B3,6,2),RIGHT(B3,2)) 후 B열 데이터 끝까지 내리기

(F3셀에 입력) =VLOOKUP(E3,A:C,3,)

익숙한 형태의 그림이기때문에 바로 vlookup을 사용하고 싶지만, B열과 E열이 형식이 달라, 사용할 수 없습니다.

(만약 vlookup을 모르시는 분들은 아래 링크를 먼저 봐주세요)

https://excel-lent.tistory.com/11

이럴 때, 필요한 함수가 DATE 함수입니다.

B열에 있는 텍스트형식의 데이터 "YYYY.MM.DD"를 날짜형식으로 변경해줘야합니다.

LEFT, MID, RIGHT 함수로 날짜형식에서 연/월/일을 발췌한 후, DATE안에 순서에 맞게 넣어주면 됩니다.

(만약, LEFT, MID, RIGHT함수를 모르시는 경우 아래 링크를 참고해주세요.)

https://excel-lent.tistory.com/15

이번에는 반대로, 날짜형식의 데이터에서 년,월,일만 분리해보겠습니다.

2. YEAR, MONTH, DAY함수

  1) YEAR

     (의       미) 날짜형식의 데이터에서 년도값 반환

     (기본수식) = YEAR(날짜)

  2) MONTH

     (의       미) 날짜형식의 데이터에서 월도값 반환

     (기본수식) = MONTH(날짜)

  3) DAY

     (의       미) 날짜형식의 데이터에서 일자값 반환

     (기본수식) = DAY(날짜)

다른 수식에 비해 매우 간단합니다.

위의 예시에서 다시 연도와, 월도와, 일자를 나눠보겠습니다.

위와 같이 간단하게 분리할 수 있습니다.

(TIPS) 실무에 활용하기 유용한 팁

1) 시계열의 데이터에서, 특정년/월/일자의 데이터를 분리하고자 할때 year, month, day함수를 사용하면 유용합니다.

안녕하세요 엑셀런트입니다.

엑셀에서 자주 쓰는 함수인 left, right, mid에 대해 알아보겠습니다.

1. left

  (의       미) 왼쪽에서부터 지정한 숫자만큼의 데이터 읽어오기

  (기본수식) = LEFT(데이터셀, 왼쪽부터 읽을 데이터 수)

2. right

  (의       미) 오른쪽에서부터 지정한 숫자만큼의 데이터 읽어오기

  (기본수식) = RIGHT(데이터셀, 오른쪽부터 읽을 데이터 수)

3. mid

  (의       미) 지정한 셀에서 A부터 B까지 데이터 읽어오기

  (기본수식) = MID(데이터셀, 읽을 데이터 처음위치, 읽을데이터 수)

예제를 통해 알아보겠습니다.

위와 같이 left, right, mid함수를 활용하면 편리하게 데이터의 특정부분을 추출할 수 있습니다.

(TIPS) 실무에 활용하기 유용한 팁

1) left, right, mid함수로 데이터를 가져오는 경우 숫자라고 하더라도 문자로 형식처리가 됩니다.

숫자의 형식을 유지하는 여러 방법이 있지만, *1을 하는 방법으로 간단하게 숫자형식으로 표현할 수 있습니다.

(변경 전) =LEFT(B3,4)

(변경 후) =LEFT(B3,4)*1

2) find함수 등을 쓰면 left, right, mid함수를 보다 파워풀하게 활용할 수 있습니다.

다음번에 find함수를 활용한 응용법을 소개해드리겠습니다.

안녕하세요 엑셀런트입니다.

엑셀에서 자주 쓰는 함수인 countif/countifs에 대해 알아보겠습니다.

1. countif

  (의       미) 주어진 조건에 맞는 셀의 갯수

  (기본수식) = countif(조건의 범위, 조건)

예제를 통해 알아보겠습니다.

Q. 원천데이터 (B~D열)의 정보를 활용해서, G열을 채워라.

A. countif을 활용하여 이 문제를 해결하겠습니다.

    (G6셀에 입력) =COUNTIF($C$4:$C$18,F6) 및 수식 아래로 내리기

위와 같이 입력하면 휴지의 전체 구매횟수가 계산되고, 수식을 내리면 나머지 항목의 합계도 구할 수 있습니다.

* 중간에 표시되어있는 $표시는 행/열을 고정시키는 기호입니다. 범위를 지정한 후 F4키를 누르면 고정시킬 수 있습니다.

* 고정시켜야 수식을 아래로 내려도 정상작동하기 때문에 반드시 해주어야합니다.

이번에는 countifs에 대해 알아보겠습니다.

countifs는 countif와 동일한 기능을하는데, 조건이 여러가지인 경우 사용합니다.

2. countifs

  (의       미) 주어진 조건들에 맞는 셀의 갯수

  (기본수식) = countif(조건의 범위1, 조건1, 조건의 범위2, 조건2, ...  )

예제를 통해 알아보겠습니다.

Q. 원천데이터 (B~D열)의 정보를 활용해서, H열을 채워라.

A. countifs을 활용하여 이 문제를 해결하겠습니다.

    (G6셀에 입력) =COUNTIFS($C$4:$C$18,F6,$D$4:$D$18,G6) 및 수식 아래로 내리기

위와 같이 입력하면 휴지 중 크리넥스의 갯수가 계산되고 수식을 내리면 나머지도 아래와 같이 채워집니다.

(TIPS) 실무에 활용하기 유용한 팁

1) 되도록 countifs를 사용

countif와 countifs 간에는 기능적인 차이는 없습니다. 조건이 한 개만 가능한지, 한 개 이상도 가능한지만 다릅니다.

때문에 조건이 한개이더라도 습관적으로 countifs를 쓰면 같은 값이 나오기 때문에 되도록 countifs를 쓰는 것이 좋습니다.

2) 범위지정

범위를 지정할 때, 마우스로 해당 범위를 지정하는 것이 번거로울 경우가 있습니다.

이 경우, Ctrl + Space Bar를 활용하여 해당 열 전체를 선택한 후, Shift + 오른쪽화살표를 통해 범위를 지정할 수 있습니다.

수식을 줄이고, 가독성을 높이기 위해 해당 방법을 사용해보세요.

(정리 전) =COUNTIFS($C$4:$C$18,F6,$D$4:$D$18,G6)

(정리 후) =COUNTIFS(C:C,F6,D:D,G6)

정리 전에 비해 수식이 짧아집니다. 또한, 작업속도도 빨라집니다.

위와 같은 방식으로 편리하게 작업해보세요.

이상 countif/countifs의 활용에 대한 설명이었습니다.

실무에서 많이 쓰는 함수인 만큼, 자유자재로 사용하시면 업무효율이 많이 높아질 것입니다.

<안내>

사용, 배포 등의 제한이 없습니다. 자유롭게 사용해주세요.

관련하여 보완이 필요한 부분이 있으면 댓글을 통해 말씀해주세요.

조언, 문의, 추가자료 언제든 환영합니다.

감사합니다.

안녕하세요 엑셀런트입니다.

엑셀에서 자주 쓰는 함수인 sumif/sumifs에 대해 알아보겠습니다.

1. sumif

  (의       미) 주어진 조건에 의해 지정된 셀들의 합계

  (기본수식) = sumif(조건의 범위, 조건, 합계를 구할 데이터의 범위)

예제를 통해 알아보겠습니다.

Q. 원천데이터 (B~D열)의 정보를 활용해서, G열의 정보를 채워라.

A. sumif을 활용하여 이 문제를 해결해 보겠습니다.

    (G6셀에 입력) =SUMIF($B$4:$B$18,F6,$D$4:$D$18) 및 수식 아래로 내리기

위와 같이 입력하면 휴지의 전체 사용기간이 합계되어 나타나고, 수식을 내리면 나머지 항목의 합계도 구할 수 있습니다.

* 중간에 표시되어있는 $표시는 행/열을 고정시키는 기호입니다. 범위를 지정한 후 F4키를 누르면 고정시킬 수 있습니다.

* 고정시켜야 수식을 아래로 내려도 정상작동하기 때문에 반드시 해주어야합니다.

이번에는 sumifs에 대해 알아보겠습니다.

sumifs는 sumif와 동일한 기능을하는데, 조건이 여러가지인 경우 사용합니다.

2. sumifs

  (의       미) 주어진 조건들에 의해 지정된 셀들의 합계

  (기본수식) = sumif(합계를 구할 데이터의 범위, 조건의 범위1, 조건1, 조건의 범위2, 조건2, ...  )

예제를 통해 알아보겠습니다.

Q. 원천데이터 (B~D열)의 정보를 활용해서, H열의 정보를 채워라.

A. sumifs을 활용하여 이 문제를 해결해 보겠습니다.

    (H6셀에 입력) =SUMIFS($D$4:$D$18,$C$4:$C$18,G6,$B$4:$B$18,F6)

위와 같이 입력하면 휴지 중 크리넥스 브랜드의 전체 사용기간이 합계되어 나타나고, 수식을 내리면 나머지 항목의 합계도 구할 수 있습니다.

조건이 더 늘어나면 뒤에 더 추가할 수 있습니다.

* 중간에 표시되어있는 $표시는 행/열을 고정시키는 기호입니다. 범위를 지정한 후 F4키를 누르면 고정시킬 수 있습니다.

* 고정시켜야 수식을 아래로 내려도 정상작동하기 때문에 반드시 해주어야합니다.

(TIPS) 실무에 활용하기 유용한 팁

1) 되도록 sumifs를 쓰는 것을 생활화

위에서 본 것처럼 sumif와 sumifs 간에는 기능적인 차이는 없습니다. 조건이 한 개인 경우 sumif, 조건이 한개 이상인 경우 sumifs를 쓰는 것입니다. 

조건이 한개이더라도 sumifs를 쓰면 같은 값이 나오기 때문에 되도록 sumifs를 쓰는 것을 생활화 하면 보다 편리하고 익숙하게 작업할 수 있습니다.

2) 범위지정

범위를 지정할 때, 마우스로 해당 범위를 지정하는 것이 번거로울 경우가 있습니다.(데이터가 아래로 매우 길거나 한 경우)

이 경우, Ctrl + Space Bar를 활용하여 해당 열 전체를 선택한 후, Shift + 오른쪽화살표를 통해 범위를 지정할 수 있습니다.

이렇게 하면, 범위를 지정하는 번거로움이 없어지고, 고정($, F4로 설정)시킬 필요도 없어집니다.

(정리 전) =SUMIFS($D$4:$D$18,$C$4:$C$18,G6,$B$4:$B$18,F6)

(정리 후) =SUMIFS(D:D,B:B,F6,C:C,G6)

정리 전에 비해 확연히 수식이 짧아집니다. 또한, 작업속도도 빨라집니다.

위와 같은 방식으로 편리하게 작업해보세요.

이상 sumif/sumifs의 활용에 대한 설명이었습니다.

실무에서 많이 쓰는 함수인 만큼, 자유자재로 사용하시면 업무효율이 많이 높아질 것입니다.

<안내>

사용, 배포 등의 제한이 없습니다. 자유롭게 사용해주세요.

관련하여 보완이 필요한 부분이 있으면 댓글을 통해 말씀해주세요.

조언, 문의, 추가자료 언제든 환영합니다.

감사합니다.

안녕하세요 엑셀런트입니다.

vlookup의 반대 함수인 hlookup함수를 소개합니다.

vlookup이 배열의 첫 열에서 값을 검색하여 같은 행의 데이터를 찾아주는 반면

hlookup은 배열의 첫 행에서 값을 검색하여 같은 열의 데이터를 찾아주는 함수입니다.

1. hlookup

  (의       미) 배열의 첫 행에서 값을 검색하여, 지정한 행의 같은 열에서 데이터를 돌려줍니다.

  (기본수식) = hlookup(찾으려는 값, 값이 있는 테이블 범위, 테이블 내의 행번호, [옵션키])

예제를 통해 알아보겠습니다.

Q. 원천데이터 (3~6행)의 정보를 활용해서, H열의 정보를 채워라.

A. hlookup을 활용하여 이 문제를 해결해 보겠습니다.

    (C10셀에 입력) = HLOOKUP(C$9,$C$3:$L$6,2,FALSE) 및 수식 오른쪽으로 복사

                                *2 : 데이터의 두번째 줄에 성별이 있기 때문

    (C11셀에 입력) = HLOOKUP(C$9,$C$3:$L$6,3,FALSE) 및 수식 오른쪽으로 복사

                                *3 : 데이터의 세번째 줄에 특별활동이 있기 때문

* 중간에 표시되어있는 $표시는 행/열을 고정시키는 기호입니다. 범위를 지정한 후 F4키를 누르면 고정시킬 수 있습니다.

* 고정시켜야 수식을 아래로 내려도 정상작동하기 때문에 반드시 해주어야합니다.

위와 같이 입력하면 네 사람의 성별과 특별활동을 찾아 입력할 수 있습니다.

다만, 2와 3의 차이로 일괄복사를 할 수 없고 중간에 다른 값을 입력해야합니다.

(이 번거로움은 match함수를 쓰면 해결이 가능한데, 다음번에 match함수에 대한 컨텐츠에서 자세히 설명하겠습니다.)

(TIPS) 실무에 활용하기 유용한 팁

1) 범위지정

범위를 지정할 때, 마우스로 해당 범위를 지정하는 것이 번거로울 경우가 있습니다.(데이터가 오른쪽으로 매우 긴 경우)

이 경우, 테이블 범위 지정 시 Shift + Space Bar를 활용하여 해당 열 전체를 선택한 후, Shift + 아래화살표를 통해 범위를 지정할 수 있습니다.

2) True, False선택관련

엑셀에서는 True/False기능을 통해 유사일치/정확한일치 기능일 지원합니다.

다만 대부분의 실무사례에서는 정확한일치 기능을 활용합니다.

정확한 일치를 활용하려고 하는 경우, Ture/False 중 아무것도 입력하지 않아도 정확한 일치가 됩니다.

종합하면, 아래와 같은 수식으로 정리가 됩니다.

(정리 전) =HLOOKUP(C$9,$C$3:$L$6,2,FALSE)

(정리 후) =HLOOKUP(C9,3:6,2,)

정리 전에 비해 확연히 수식이 짧아진 것을 알 수 있습니다.

또, 마우스를 한번도 사용하지 않아도 되기 때문에 일을 잘해 보이는데 매우 유용합니다.ㅎㅎ

안녕하세요 엑셀런트입니다.

실무에서 가장 많이 쓰이는 함수인 vlookup에 대해 알아보겠습니다.

1. vlookup

  (의       미) 배열의 첫 열에서 값을 검색하여, 지정한 열의 같은 행에서 데이터를 돌려줍니다.

  (기본수식) = vlookup(찾으려는 값, 값이 있는 테이블 범위, 테이블 내의 열번호, [옵션키])

예제를 통해 알아보겠습니다.

Q. 원천데이터 (B~E열)의 정보를 활용해서, H열의 정보를 채워라.

A. vlookup을 활용하여 이 문제를 해결해 보겠습니다.

    (H3셀에 입력) =VLOOKUP(G3,$B$3:$E$12,4,FALSE) 및 수식 아래로 내리기

위와 같이 입력하면 박서준의 장래희망인 구조기술사가 H3셀에 입력되고 수식을 밑으로 내리면 아래와 같이 완성됩니다.

* 중간에 표시되어있는 $표시는 행/열을 고정시키는 기호입니다. 범위를 지정한 후 F4키를 누르면 고정시킬 수 있습니다.

* 고정시켜야 수식을 아래로 내려도 정상작동하기 때문에 반드시 해주어야합니다.

(TIPS) 실무에 활용하기 유용한 팁

1) 범위지정

범위를 지정할 때, 마우스로 해당 범위를 지정하는 것이 번거로울 경우가 있습니다.(데이터가 아래로 매우 길거나 한 경우)

이 경우, Ctrl + Space Bar를 활용하여 해당 열 전체를 선택한 후, Shift + 오른쪽화살표를 통해 범위를 지정할 수 있습니다.

이렇게 하면, 범위를 지정하는 번거로움이 없어지고, 고정($, F4로 설정)시킬 필요도 없어집니다.

2) True, False선택관련

엑셀에서는 True/False기능을 통해 유사일치/정확한일치 기능일 지원합니다.

다만 대부분의 실무사례에서는 정확한일치 기능을 활용합니다.

정확한 일치를 활용하려고 하는 경우, Ture/False 중 아무것도 입력하지 않아도 정확한 일치가 됩니다.

종합하면, 아래와 같은 수식으로 정리가 됩니다.

(정리 전) =VLOOKUP(G3,$B$2:$E$12,4,FALSE)

(정리 후) =VLOOKUP(G3,B:E,4,)

정리 전에 비해 확연히 수식이 짧아진 것을 알 수 있습니다.

또, 마우스를 한번도 사용하지 않아도 되기 때문에, 효율이 높아집니다.(조금 멋있게 작업할 수 있습니다.)

이상 vlookup의 활용에 대한 설명이었습니다.

실무에서 가장 많이 쓰는 함수인 만큼, 자유자재로 사용하시면 업무효율이 많이 높아질 것입니다.

<안내>

사용, 배포 등의 제한이 없습니다. 자유롭게 사용해주세요.

관련하여 보완이 필요한 부분이 있으면 댓글을 통해 말씀해주세요.

조언, 문의, 추가자료 언제든 환영합니다.

감사합니다.

안녕하세요 엑셀런트입니다.

중소기업에서 사용할 수 있는 부서별 예산관리 양식을 공유합니다.

부서별 예산 관리 자동화로 편리하게 관리해보세요.

<How To Use>

1. 기준일자를 설정해주세요.

2. 부서별 월별 예산을 C열에 입력해주세요.

3. 수입은 월별로 자동으로 입력됩니다.(기준일자 이전까지) 지출을 입력해주세요.

4. 잔액은 같은 년도의 합계만 표현합니다.(if 기준일자가 2024년이면, 2024년의 합계)

<주요엑셀로직>

잔액(D6)

=SUMIFS(G8:XFD8,G$5:XFD$5,">="&$F$2,G$5:XFD$5,"<="&$C$2)

기준일자보다 작고, 기준일자가 포함된 연도의 1월1일 이상인 데이터의 합계를 구함.

<안내>

사용, 배포 등의 제한이 없습니다. 자유롭게 사용해주세요.

관련하여 보완이 필요한 부분이 있으면 댓글을 통해 말씀해주세요.

조언, 문의, 추가자료 언제든 환영합니다.

감사합니다.

안녕하세요 엑셀런트입니다.

재무관리 중, 금리 민감도 측정에 있어 매우 중요한 이론인 듀레이션을 엑셀로 구현한 파일을 공유드립니다.

듀레이션의 이해

1. Macaulay Duration(맥컬리 듀레이션)

   전통적 방식의 듀레이션으로, 가중평균 잔존만기의 개념

   sum(t x PVt) / sum(PVt) 로 계산 

   현금흐름에 변동이 없을 경우에만 사용 가능

2. Modified Duration(수정 듀레이션)

   금리민감도를 추정하기 적합하도록 맥컬리듀레이션을 (1+금리)로 나눈 개념

   현가함수의 미분을 통해 기울기를 표현

3. Effective Duration(유효 듀레이션)

   전통적 방식의 한계를 극복하기 위해, (PV-delta  ㅡ  PV+delta) / (PVo*2*delta) 방식으로 산출하는 듀레이션

   현금흐름이 확정된 경우, 수정듀레이션과 비슷한 수치

   현금흐름이 변동되는 경우에도 각 시점의 현가만 산출할 수 있다면 유효듀레이션은 사용 가능

자세한 내용은 엑셀파일을 통해 확인해주세요.

<안내>

사용, 배포 등의 제한이 없습니다. 자유롭게 사용해주세요.

관련하여 보완이 필요한 부분이 있으면 댓글을 통해 말씀해주세요.

조언, 문의, 추가자료 언제든 환영합니다.

감사합니다.