엑셀연구소

안녕하세요 엑셀런트입니다.
 
엑셀을 할 때, 알면 매우 유용한 find 함수에 대해 알아보겠습니다.

1. find함수
  (의       미) 지정한 텍스트를 다른 텍스트에서 찾아, 해당 문자의 시작 위치를 나타내는 함수
  (기본수식) = Find(찾는 텍스트, 찾을 텍스트셀)

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

위와 같이 데이터가 구성되어있을때, 몇시 몇분을 구분하기 위해서 어떻게 하면 좋을까요?

left, mid함수를 사용하면 좋겠는데 시간 앞에 2글자인 것도 있고 세 글자인 것도 있어서 어렵습니다.

이 경우, find함수를 사용하면 매우 유용합니다.

(C5셀 입력) =FIND("시",B5)

위와 같이 입력하면 텍스트에서 "시"가 몇번째에 위치하는지 반환됩니다.

이를 활용하여 left함수와 함께 사용하면 아래와 같이 시간을 표현할 수 있습니다.

(TIPS) 실무에 활용하기 유용한 팁
 
1) find함수는 어떤 글자가 포함되어있는지 아닌지를 구분하는데도 매우 유용합니다. 만약 find함수의 결과가 오류인 경우 해당 텍스트가 데이터에 포함되어있지 않다는 것을 의미하므로, 이를 활용하여 특정 텍스트를 포함했는지 아닌지 여부를 가려낼 수 있습니다.

2. left, right, mid함수와 함께 사용할 경우 find함수가 매우 유용해집니다. 함께 활용하는 것을 익혀보세요.

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

안녕하세요 엑셀런트입니다.
 
엑셀을 할 때, 필터 등을 적용한 후 화면에 보이는 셀만의 합계를 구하고 싶을 때가 있습니다.

이럴 때 활용할 수 있는 subtotal함수를 소개합니다.

1. time함수
  (의       미) 데이터베이스의 구분합을 구해주는 함수
  (기본수식) = subtotal(9, 범위)

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

Q. 위와 같이 데이터가 있을 때, 화면에 보이는 셀의 합계를 구하는 함수를 표현하여라.

A. (D4셀 입력) = SUBTOTAL(9,D6:D20)

위와 같이 적용할 경우 아래화면과 같이 필터에 따라 화면에 보이는 셀의 합계만을 구해주게됩니다.

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

1) 9:합계 외 다른기능 활용 가능성

실무에서는 subtotal을 사용할 때 대부분 부분합계로 사용합니다. 때문에 subtotal(9,범위)가 일반적으로 사용됩니다.

다만, 번호에 따라 평균/분산/곱셈/표준편차 등 화면에 보이는 셀들의 연산을 표현할 수 있으므로 필요 시 활용하면 유용합니다.

2) subtotal의 경우 범위에 따라 변경되는 수식이기때문에 업무상 오류를 범하기 쉽습니다. 때문에, 매 작업시에 sum으로 한 합계와 필터를 모두 푼 상태에서 subtotal으로 계산한 합계가 동일한지 확인하는 작업이 필요합니다. 확인 후 필터를 다시 걸어서 subtotal의 기능을 활용하는 것을 권장합니다.

안녕하세요 엑셀런트입니다.
 
엑셀을 할 때, 알면 매우 유용한 time 함수에 대해 알아보겠습니다.
 
1. time함수
  (의       미) 엑셀에서 시간를 나타내는 수 구하기(엑셀이 인식할 수 있는 시간으로 변경해주는 함수)
  (기본수식) = Time(시간, 분, 초)
 
예제를 통해 알아보겠습니다.

Q.  데이터가 위와 같을 때, (A~B열)가 있을 때, 노란색 칸을 채우기위해서는?
     (데이터 : 년도 4자리 + 월도 2자리 + 일자 2자리 + 시간 2자리 + 분 2자리)
 
A. DATE함수 및 TIME함수 사용
(C4셀에 입력) =DATE(LEFT(A4,4),MID(A4,5,2),MID(A4,7,2)) 후 C열 데이터 끝까지 내리기
(D4셀에 입력) =TIME(MID(A4,9,2),RIGHT(A4,2),0) 후 D열 데이터 끝까지 내리기

C열의 날짜 계산을 위해서는 지난 포스팅의 DATE함수를 사용하면 됩니다.
(만약 모르시는 분들은 아래 링크를 먼저 봐주세요)
https://excel-lent.tistory.com/16
 
시작시간을 알기 위해서, 데이터의 9째부터 2자리를 시간으로, 오른쪽에서 2자리를 분으로 가져오면됩니다.
(mid함수, right함수 사용, 모르시는 분들은 아래 포스팅을 참고해주세요)
https://excel-lent.tistory.com/15
 
그 후, time함수 안에 시간, 분을 넣어주시고, 초는 알 수 없으므로 0으로 처리하면 아래의 수식이 완성됩니다.
=TIME(MID(A4,9,2),RIGHT(A4,2),0) 
 
이번에는 반대로, 시간형식의 데이터에서 시,분,초만 분리해보겠습니다.
 
2. HOUR, MINUTE, SECOND함수
 
  1) HOUR
     (의       미) 시간형식의 데이터에서 시간값 반환
     (기본수식) = HOUR(시간데이터)
  2) MONTH
     (의       미) 시간형식의 데이터에서 분값 반환
     (기본수식) = MINUTE(시간데이터)
  3) DAY
     (의       미) 시간 형식의 데이터에서 초값 반환
     (기본수식) = SECOND(시간데이터)
 
다른 수식에 비해 매우 간단합니다.
 
위의 예시에서 시간과, 분과, 초를 나눠보겠습니다.

 
위와 같이 간단하게 분리할 수 있습니다.
 
(TIPS) 실무에 활용하기 유용한 팁
 
1) 실무 업무 중 날짜와 시간을 분리해야하는 업무가 매우 많습니다. 관련 수식을 한 엑셀파일에 정리해두고 복사해서 사용하시면 효율이 더욱 좋습니다. DATE, TIME, HOUR, MINUTE, SECOND, YEAR, MONTH, DAY

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

엑셀을 할 때, 알면 매우 유용한 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,)

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

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