엑셀 TIP(팁)- 엑셀의 SUBTOTAL함수에 대해 알아봅니다.

 

유투브 구독신청~ 알림설정~ 좋아요. 감사합니다.

오늘은 이전에 포스팅한 SUMPRODUCT

함수에 이어 엑셀에서 자동필터와 궁합을

이루는 SUBTOTAL함수에 대해

알아 보겠습니다.

일반적인 최대값,최소값,합계,평균값

등을 목록이나 데이터베이스에서

부분합을 구할수 있는 함수 입니다.

함수의 기본식은

=SUBTOTAL(계산방법,범위)입니다.

비교적 간단해 보이죠.

위 그림에서 F15셀을 선택합니다.

그리고 SUBTOTAL함수를 적용합니다.

=SUMTOTAL( 까지 입력하면 위 그림과

같이 여러 계산방법을 적용할수 있는

함수가 팝업메뉴처럼 뜹니다.

여기서 원하시는 함수에 마우스커서를

위치하고 더블클릭하시면 됩니다.

여기서는 일단 SUM함수를 선택

하겠습니다.

그러면 위 그림처럼 숫자9가 적용됨을

알수 있습니다. 그리고

수량,단가,금액 중에 합계를 구하고자

하는 곳의 범위를 드래그해서 선택

하시면 됩니다.

여기서는 수량의 합을 구하기 위해

수량부분(A)을 드래그 해서 범위를

지정했습니다. 즉

=SUBTOTAL(9,D6:D11)이런 함수식이

완성되었습니다. 그리고 엔터를 칩니다.

그러면 F15셀에 값이 78이 구해짐을

알수 있습니다. 이는 D12셀의

=SUM(D6:D11)한 결과와 같음을

알수 있습니다.

​

이번에는 한번더 평균을 구해보겠습니다.

위 그림에서 F16셀을 선택합니다.

그리고 SUBTOTAL함수를 적용합니다.

=SUMTOTAL( 까지 입력하면 위 그림과

같이 여러 계산방법을 적용할수 있는

함수가 팝업메뉴처럼 뜹니다.

여기서 원하시는 함수에 마우스커서를

위치하고 더블클릭하시면 됩니다.

여기서는 일단 AVERAGE함수를 선택

하겠습니다.

그러면 위 그림처럼 숫자1이 적용됨을

알수 있습니다. 그리고

수량,단가,금액 중에 합계를 구하고자

하는 곳의 범위를 드래그해서 선택

하시면 됩니다.

여기서는 수량의 평균을 구하기 위해

수량부분을 드래그 해서 범위를

지정했습니다. 즉

=SUBTOTAL(1,D6:D11)이런 함수식이

완성되었습니다. 그리고 엔터를 칩니다.

그러면 F17셀에 값이 13이 구해짐을

알수 있습니다. 이는 D13셀의

=AVERAGE(D6:D11)한 결과와 같음을

알수 있습니다.

이렇게 일반 기본함수를 이용해서도

가능하나 SUBTOTAL함수를 사용하는

이유는 바로 자동필터를 적용해서

화면에 보이는것만 합계나 평균을

바로 업데이트되어 나타나기

때문입니다.

위 그림에서 보듯이 자동필터

CTRL+SHIFT+L을 눌러 적용하고

과일종류중 사과만을 나타나게 하니

F16셀의값과 F17의셀의값이

변화된것을 알수 있습니다.

즉 사과만의 합계와 평균이 나타남을

알수 있습니다.

아래 동영상을 참고하시면 쉽게

이해되시리라 생각됩니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀의 수학함수인 ABS,MOD,POWER,RAND함수와 통계함수인MEDIAN,MODE함수에 대해 알아봅니다.

 

구독신청~ 알림설정~ 좋아요. 감사합니다.

명절 연휴 잘보내셨나요?

오늘은 엑셀의 수학함수인  ABS함수,

MOD함수,POWER함수,RAND함수와

통계함수인 MEDIAN,MODE함수에 

대해 알아보겠습니다.

먼저 ABS함수에 대해 알아봅겠습니다.

ABS함수는 숫자의 절대값을 나타냅니다.

따라서 =ABS(-25)또는 데이터위치를

셀주소로 하면 =ABS(B4)하면 결과는

25가 나타납니다.

절대값이므로 항상 양수값

입니다. 다음은 MOD함수에 대해

알아보겠습니다.

MOD함수는 수1을 수2로 나눈 나머지를

나타냅니다. 따라서 =MOD(20,7) 또는

셀주소로 =MOD(B5,D5)하면 나머지값이

6이므로 함수 결과는 6이 나타납니다.

다음은 POWER함수에 대해 알아

보겠습니다.

POWER함수는 수1을 수2만큼 거듭제곱

한 값을 나타냅니다. 따라서

=POWER(3,3)또는 =POWER(B6,D6)

하면 3*3*3의 의미 이므로 27 이

나타납니다.

다음은 RAND함수에 대해

알아보겠습니다.

RAND함수는 0과1사이의 난수를 나타

냅니다. 나타나는 값은 고정값이 아니라

계산시마다 새로운 숫자가 나타납니다.

따라서 =RAND()하면 위의 그림에서는

0.421741이지만 값이 고정이 아니므로

수시로 변하는 값을 표현합니다.

다음은 MEDIAN함수에 대해 알아

보겠습니다.

MEDIAN함수는 숫자들의 중간값을

나타냅니다. 따라서

=MEDIAN(10,20,30) 또는

=MEDIAN(B10:D10)하게되면 중간값인

20을 결과값으로 나타내게 됩니다.

다음은 MOD함수에 대해 알아

보겠습니다.

MODE함수는 숫자들의 빈도가 가장

높은값을 나타냅니다. 따라서

=MODE(25,30,25) 또는

=MODE(B11:D11)하게되면 가장

빈도수가 높은 25값을 결과값으로

나타내게 됩니다.

이렇게 오늘은 간략하게 엑셀의 수학함수

인 ABS함수,MOD함수,POWER함수,

RAND함수와 통계함수인 MEDIAN,

MODE 함수에 대해 알아보았습니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀의 텍스트함수인 LEN,TRIM,FIND,FINDB,SEARCH,SEARCHB함수에 대해 알아봅니다.

 

오늘은 엑셀의 텍스트함수인 LEN,TRIM,

FIND,FINDB,SEARCH,SEARCHB 함수

에 대해 알아 보겠습니다.

먼저 LEN함수에 대해 살펴보면

LEN함수는 문자열의 길이를 숫자로

바꾸는 함수입니다. 위 그림처럼 B3(A)의

문자를 LEN함수로 변환하면 C3셀을 클릭 후

=LEN("마산합포구 산호동") 이렇게 입력해도

되고 문자 대신 문자가 위치한 셀 주소를 넣어

=LEN(B3) 이렇게 입력해도 됩니다.

문자 사이 공백도 하나로 인식해 함수

결과식은 9로 나왔네요.

아래의 마산CAD디자인학원은 LEN함수로

변환하니 함수 결과는 영문 한글 모두

한글자로 해서 10이 나왔네요. 이렇게

LEN함수는 문자열의 길이를 숫자로

반환합니다.

​

다음은 TRIM함수에 대해 알아보겠습니다

TRIM함수는 단어사이의 한 칸의 공백을

제외하고 나머지 공백을 모두 삭제하여

반환하는 함수입니다.

위 그림처럼 경남 마산합포구 산호동의

문자가 3칸씩 띄어 입력되어 있는데

TRIM함수를 적용하니 함수결과(C16셀)

를 보니 한칸이 공백만 남긴상태가 되죠

이렇게 TRIM함수는 단어사이의 한칸의

공백을 제외하고 모두 삭제 시키는 함수

입니다.

​

다음은 FIND함수와 FINDB함수에 대해

알아보겠습니다. 함수식은

=FIND(문자열1,문자열2,시작위치)

=FINDB(문자열1,문자열2,시작위치)

입니다.

FIND함수는 영문자의 대,소문자를 구분

하고 와일드카드문자(*,?,~)는 사용할수

없고 각문자를 한글자로 계산합니다.

그런데 FINDB함수는 영문과 숫자는

한글자로, 한글과 특수문자는 두글자로

계산됩니다.

따라서 위의 "마산CAD디자인학원fighting"

문자열을 가지고 FIND와FINDB를 적용한

결과 함수식에 시작점을 정하지 않았을

경우에는 FIND함수는 12와 FINDB함수는

한글과 특수문자는 두글자로 계산하다보니

19가 나왔고 i가 2개있어서 시작점을 기입

하니 FIND함수는 16 FINDB함수는 23이

나왔네요.

​

다음은 SEARCH함수와 SEARCHB함수

에 대해 알아보겠습니다. 함수식은

=SEARCH(문자열1,문자열2,시작위치)

=SEARCHB(문자열1,문자열2,시작위치)

입니다.

SEARCH함수는 영문자의 대,소문자를 구분

하지 않고 와일드카드문자(*,?,~)는 사용가능

하며 각문자를 한글자로 계산합니다.

그런데 SEARCHB함수는 영문과 숫자는

한글자로,한글과 특수문자는 두글자로

계산 됩니다.

따라서 위의 "마산CAD디자인학원fighting"

문자열을 가지고 SEARCH와SEARCHB를

적용한 결과 함수식에 시작점을 정하지

않았을 경우에는 SEARCH함수는 12와

SEARCHB 함수는 한글과 특수문자는

두글자로 계산하다 보니 19가 나왔고

i가 2개있어서 시작점을 기입 하니

SEARCH함수는16,SEARCHB함수는 23이

나왔네요. 일단 결과적으로는 값이 FIND함수

와 FINDB함수랑 같이 나왔는데 여기서

차이점은 함수식기입시 찾을문자열 즉

문자열1기입시  FIND/FINDB함수는 반드시

대소문자 구분해야 오류가 없고

SEARCH/ SEARCHB함수는 대소문자

구분없어도 된다는 점입니다.

FIND함수와 SEARCH함수는 모두 찾을문자

의 위치를 숫자로 반환하는 함수입니다.

이렇게 오늘은 텍스트함수인 LEN함수

TRIM함수,FIND함수,FINDB함수,SEARCH

함수,SEARCHB함수에 대해 살펴보았습니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀의REPLACE,SUBSTITUTE,CONCATENATE함수에 대해 알아봅니다.

 

오늘은 엑셀의 REPLACE,SUBSTITUTE,

CONCATENATE함수에 대해 알아

보겠습니다.

위 그림과 같은 경우 먼저 REPLACE함수

에 대해 알아보겠습니다.REPLACE함수는

텍스트의 일부를 다른 텍스트로 바꾸는

함수 입니다. 기본식은

=REPLACE(바꿀셀위치,몇번째부터,

몇글자를,"대체할글자")입니다.

따라서 D4셀(A)을 클릭 후

=REPLACE(B4,3,8,"그룹") 입력 후

엔터치면 (H)처럼 마산그룹이

입력됩니다.

즉 B4셀의 데이터,마산CAD디자인학원

에서 3번째글자 C부터 8글자

CAD디자인학원글자를 그룹으로

대체한다는 의미 입니다.

​

다음은 SUBSTITUTE 함수에 대해

알아 보겠습니다. SUBSTITUTE함수는

텍스트의 일부를 바꾸거나 삭제할 경우

사용하는 함수입니다. 함수식은

=SUBSTITUTE(원본텍스트,바꿀문자,

변경할문자,치환할위치값) 입니다.

여기서 바꿀문자와 변경할문자는

필수입력 항목이고 치환할 위치 값은

생략 가능합니다.

E4셀(B)를 클릭하고

=SUBSTITUTE(B4,"CAD디자인학원",

"학원") 입력 후 엔터치면 마산학원(I)이

입력됩니다.

즉 B4셀의 데이터,마산CAD디자인학원

에서 CAD디자인학원을 학원으로

바꾼다는 의미 입니다.

어찌보면 PEPLACE함수와  함수식은

다르지만 비슷한 결과를

만들수도 있죠

​

다음은CONCATENATE함수에 대해

알아보겠습니다. CONCATENATE함수는

문자를 연결하다는 함수입니다.

연결 연산자(&)와 동일한 결과를 얻을 수

있는 함수입니다. 이 함수는 반드시

첫 번째 텍스트는 필수로 입력을 해야

하며 두 번째 텍스트부터는 옵션으로

생략이 가능합니다.

함수식은

=CONCATENATE(텍스트,텍스트 2,

텍스트 3,...)입니다.

F4셀(C)를 클릭후

=CONCATENATE(LEFT(B4,2),"컴퓨터",

RIGHT(B4,2)) 입력후 엔터치면

마산컴퓨터학원 이렇게 입력이 됩니다.

즉 마산CAD디자인학원 데이터에서

LEFT함수에의해 마산이 추출되고

CONCATENATE함수의 컴퓨터 그리고

RIGHT함수에서 학원이 추출되어

연결하면 마산컴퓨터학원(J)이렇게

나타나게 됩니다.

이렇게 오늘은 텍스트의 일부를 바꾸거나

연결하는 함수에 대해 알아보았습니다.

​-감사합니다.-

엑셀 TIP(팁)-엑셀의 CHOOSE함수와 IF ERROR함수에 대해 알아봅니다.

 

오늘은 엑셀의 숫자에 대응하는 문자를

입력하는 CHOOSE함수와 함수식의 수식에

오류가 있으면 지정한 값으로 나타내고

오류가 없으면 수식값을 나타내게 하는

IFERROR 함수에 대해 알아보겠습니다.

CHOOSE함수는 기본식이

=CHOOSE(K,"문자1","문자2","문자3")

입니다. 여기서 K는숫자이며 0이 될수 

없습니다. 즉

K숫자1이면--->문자1선택, 

K숫자2이면--->문자2선택 

K숫자3이면---> 문자3선택 

이란의미 입니다.

예를 들어 한번 살펴보겠습니다.

위 그림과 같이 사원코드를 이용해

사원코드의 5번째 자리수에 따라 1이면

부장,2이면 과장,3이면 대리,4이면사원을

표시해야하므로 사원코드에서 5번째자리

수를 추출해야 하므로 문자추출함수인

MID함수를 같이 사용 합니다.

따라서 E6셀을 클릭후 

=CHOOSE(MID(B6,5,1),"부장","과장","대리",

"사원")을 입력후 엔터치면 MID함수에

의해 추출한 값이 1이므로 "부장"이 기입

됩니다. 나머지를 구하기 위해 자동채우기

핸들을 아랫방향으로 드래그하면

위와 같이 구해지는것을 알수 있습니다.

​

다음은 IFERROR함수에 대해 살펴

보겠습니다.

IFERROR함수는 수식에 오류가 있으면

내가 지정한 값으로 나타내고 오류가 없으면

수식값을 나타내게 하는 함수입니다.

기본식은 =IFERROR(수식,"오류표시")

입니다. 따라서 E19셀을 클릭하고

=IFERROR(C19*D19,"알수없음")을 입력후

엔터치면 판매가격20000*판매현황3을 

곱해서 60000이 입력됨을 알수 있습니다.

셀 우측하단의 자동채우기 핸들을 아랫방향

으로 드래그해서 나머지도 구하시면 됩니다.

위 그림에서 보듯이 E22셀과 E24셀값은

"알수없음" 이 입력됨을 알수 있습니다.

즉 IFERROR함수에 의해 계산식에서

판매현황 값이 X즉 ,없음으로 계산식에서

오류가 발생했하여 함수식에서 오류값

으로 "알수없음"이 지정되어 있기

때문입니다.

아래 동영상을 보시면 더욱더 이해가

잘 되실거라 생각됩니다.

-감사합니다.-