엑셀 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(팁)- 엑셀의 SUMPRODUCT함수에 대해 알아봅니다.

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

 

오늘은 엑셀의 다중조건수식함수인

SUMPRODUCT함수에 대해 알아

보겠습니다.

일단 함수명에 답이 나와 있네요.

SUM함수는 더하기 함수이고

PRODUCT함수는 곱한다는 함수죠.

그래서 결국 SUMPRODUCT함수는

곱한값을 더한다는 함수 입니다. 물론

이 SUMPRODUCT함수를 사용하지

않고도 구할수는 있습니다.

한번 살펴보겠습니다.

위 그림에서 일단 기본함수인 합계를

구하는 함수는 수량의 합계는 D12셀(A)을

선택 후 =SUM(D6:D11)입력 후 엔터치면

위 그림과 같이 값이 구해집니다.

값이 78임을 알수 있습니다.

F6셀(B)에 금액을 구하는 기본적인

방법은 수량*단가이므로 =D6*E6하면 되죠.

또는 함수로서는

=PRODUCT(D6,E6)하면 값을 구할수

있습니다. 값이 100000임을 알수 있습니다.

그리고 자동채우기를 하면 됩니다.

F12셀의(C) 전체금액은

=SUM(F6:F11)하면 값을 구할수

있습니다. 값이 503500임을 알수 있습니다.

그런데 이번에는 SUMPRODUCT함수를

이용해 더 쉽고 빠르게 구하는 방법을

살펴보겠습니다.

먼저 F12셀을 선택합니다. 그리고

SUMPRODUCT의 기본식은

=SUMPRODUCT(ARRAY1,ARRAY2,...)

입니다. 따라서

=SUMPRODUCT(D6:D11,E6:E11)입력 후

엔터치면 됩니다.

즉 (A)부분 드래그해서 선택하고 콤마로

구분하고 (B)부분 선택 해주면 됩니다.

그러면 우측의 그림과 같이 값이 구해집니다.

즉 503500D임을 알수 있습니다.

기본적으로 구한 F12값과 SUMPRODUCT

함수로 구한 F13값이 동일함을 알수

있습니다.(F12값=F13값)

​

이번에는 조건을 부여해서 계산하는

방법에 대해 알아 보겠습니다.

위 그림에서 보듯이 과일종류 중에서

전체가 아닌 사과만 구해보겠습니다.

F14셀을 선택합니다. 그리고

=SUMPRODUCT((B6:B11)="사과"*1

,D6:D11,E6:E11)입력 후 엔터치면

됩니다.

그러면 우측의 그림과 같이 사과만의

결과값이 150000임을 알수 있습니다.

즉 과일종류중 조건이 TRUE이면

1,FALSE이면 0을 이용하고 수량과

단가의 범위를 지정하면 됩니다.

이렇게 SUMPRODUCT함수를 이용해

곱한값을 합계까지 쉽게 구하는

방법에 대해 알아 보았습니다.

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

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

-감사합니다.-

엑셀 TIP(팁)- 엑셀에서의 균등분할과 자릿수가 많은 숫자를 표시하는 방법에 대해 알아봅니다.

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

 오늘은 엑셀에서 셀에 문자를 균등분할

하는 방법과 숫자의 자릿수가12자리

이상일 경우 지수형식으로 표시가 되고

또는 16자리 이상일 경우 0 으로 표시

되는것을 원래 숫자로 표시하는 방법에

대해 알아 보겠습니다.

먼저 균등분할이란 셀서식을 이용

합니다.

위 그림처럼 (A)의 형식을 (B)의

형식처럼 표현하는 것을 균등분할

이라 합니다.

보통 셀에 문자를 입력후 균등분할

형태를 만들기 위해 스페이스바로

공백을 띄워 입력해도 되지만 많이

번거롭습니다. 따라서

위 그림과 같이 균등분할할 셀을

모두 선택합니다. 그리고 단축키

CTRL+1을 누르셔도 되고 또는

선택 후 마우스오른쪽클릭을 하면

뜨는 창에서 하단부분의 셀서식을

선택해도 됩니다.

그러면 위와 같은 창이 뜨는데 여기서

맞춤탭(A)을 선택하고 텍스트맞춤

부분의 우측의 화살표(B)를 클릭하면

나오는 메뉴에서 균등분할(C)을 선택

하면 됩니다.

그러면 (A)와 같은 형식의 문자가

(B)처럼 균등분할됨을 알수 있습니다.

​

다음은 자릿수가 많은 숫자표시 방법에

대해 알아보겠습니다.

위 그림처럼 선택한 셀에

임의로 숫자를 12자리 이상 입력하고

엔터를 치니 우측처럼 지수형식으로

표시가 됩니다.

이럴 경우 원래 숫자로 표현하는 방법을

알아 보겠습니다.

숫자가 입력된 셀을 선택 후

마우스오른쪽클릭하면 나타나는 대화창

에서 하단부분의 쉼표스타일을 선택

하면 됩니다. 또는

상단 리본메뉴의 표시형식의 쉼표를

선택해도 됩니다..

위 그림에서 보면 보통 숫자를 16자리이상

기입하면 위 방식대로 해도 16자리 이상

부터는 모두 0으로 표시가 됩니다.

이럴경우 원래숫자를 표기하는 방법을

알아 보겟습니다.

첫번째는 셀을 선택하고 숫자를 입력하기

전에 먼저 '을 입력하고 숫자를 입력하면

됩니다.

또는 단축키 CTRL+1을 누르셔도

되고 또는(B) 선택 후 마우스오른쪽클릭을

하면 뜨는 창에서 하단부분의 셀서식을

선택하면 나오는 대화창에서 표시형식을

텍스트로 지정후 숫자를 입력하시면

됩니다.

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

되시리라 생각됩니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀에서 데이터누적합계와 차감액를 구하는 방법에 대해 알아봅니다.

 

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

오늘은 엑셀실무에서 데이터의

누적합계와 차감합계를 구하는

방법에 대해 알아 보겠습니다.

일반적으로 합계는 SUM함수에

의해 쉽게 구할수 있습니다.

그런데 막상 누적합계를 구할려면

어렵게 생각하는 부분이 있죠.

그래서 한번 살펴보겠습니다.

먼저 기본적인 합계(A)와 누적합계를

구하는 두가지방법 (B),(C)을 알아

보겠습니다.

먼저 기본합계는 D17셀을 선택하고

=SUM(D7:D16)입력하고 엔터치면

됩니다. 그러면 합계가 위 그림처럼

구해집니다.

누적합계는 단축키 CTRL+Q와

함수를 이용하는 방법을 실습해

보겠습니다.

위 그림에서 판매량부분(A)을 모두

선택합니다. 그리고 단축키

CTRL+Q를 누르면 (B)와 같이 창이

뜨는데 여기서 상단부분에 합계(C)를

클릭 합니다.

그리고 아래부분에서 제일 우측의

화살표를 눌러보면 누계가 나오는데

우측에 값이표시되는 누계(A)를 클릭

하시면 위 그림과 같이 누계값(B)을

구할수 있습니다.

또는 함수식은

F7셀을 선택한다음 함수식

=SUM($D$7:D7)을 입력하고 엔터

치면 됩니다. 누적이 되어야 하므로

절대참조(F4)를 활용합니다.

구해진 값을 아래방향으로

자동채우기를 하시면 됩니다.

​

다음은 차감액을 구하는 방법에

대해 알아 보겠습니다.

위 그림에서는 차감액이 구해져

있으므로 그옆셀에다 구해보겠습니다.

F24셀을 선택하고 함수식

=IMSUB(E22,D24)를 입력하고 엔터

칩니다. 그러면 1800이라는 값이

구해집니다.참고로 빼기함수는 IMSUB

입니다.

F25셀을 선택한다음 한번더

함수식 =IMSUB(F24,D25)를 입력하고

엔터를 칩니다. 그러면 1800에서150을

뺀값을 구할수 있습니다.

구한값을 자동채우기핸들을 이용해

나머지 값을 구하시면 됩니다.

이렇게 누적합계와 차감합계를

구하는 방법에 대해 알아 보았습니다.

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

되시리라 생각됩니다.

-감사합니다.-