엑셀 TIP(팁)- 엑셀에서 목표값이 변경됨에 따라 과정값을 찾을수 있는 가상분석의 목표값찾기에 대해 알아봅니다.

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

 

오늘은 엑셀에서 수식에서 원하는

결과를 알고 있지만 그 결과를 얻는데

필요한 입력값을 구하는 경우에 사용하는

가상분석의 목표값찾기에 대해 알아

봅니다. 즉 함수식 또는 수식에 의해

결과가 나와 있는 경우 목표값 변경에

의해 중간과정이 바뀌는 것을 찾고자

할때 사용합니다. 한번 살펴 보겠습니다.

위 그림에서 보면

고객별 적금 만기지급액 표 입니다.

여기서 박민수의 만기 지급액을 보면

월불입액이 300000 이고 연이자가3.5%

납입기간이 24개월일 경우 만기시

7447000원을 지급받게 됩니다. 그런데

월불입액과 연이자가 동일조건일때

만기시 지급액이 8800000이 되려면

납입기간이 얼마나 걸릴지 알아보는

과정입니다.

여기서 좌측그림에서 만기시 지급액은

그냥 입력된 값이 아니고 반드시 함수식

또는 수식에 의해 계산되어져야 합니다.

만기 지급액 함수는 FV함수를 사용

합니다. 즉 F12셀을 클릭하고 다음과

같이 입력합니다.

=ROUND(-FV(D12/12,E12,C12),-3)

입력 후 엔터를 치면 7447000금액이

나옵니다.

FV함수 앞에 -(마이너스)를 붙인 이유는

금액이 마이너스 값으로 나오는 것을

방지하기 위해서 입니다.

ROUND함수는 반올림하는 함수입니다.

이런 개별함수에 대해서는 이전에

포스팅되어 있습니다. 참고하시면 됩니다.

일단 좌측 그림에서 현재 만지지급액이

7447000이므로 이 금액이 8800000이

되기 위해 몇개월 소요되는지를 알아보기

위해 F12셀(A)을 클릭하고 상단메뉴의

데이터(B)를 클릭합니다. 그리고

리본메뉴의 가상분석(C)을 클릭하고 나오는

메뉴에서 목표값찾기(D)를 선택합니다.

그러면 뜨는 창에서 수식셀에는 F12

선택한 셀이 지정되고 찾는 금액에

새로운 목표값 8800000을 직접 입력

하시면 됩니다. 그리고 값을 바꿀셀에는

E12셀 즉 박민수의 납입기간셀을

클릭하시면 입력이 됩니다. 그리고

확인을 클릭합니다.

그러면 위 그림과 같이

계산 되어 결과값을 얻을 수 있습니다.

새로운 목표값 8800000이 되기위해

납입기간이 28.1783...이 필요하네요

소수점 자릿수를 제거하면 대략

28개월 소요됨을 알수 있습니다.

이렇게 오늘은 가상분석의

목표값찾기에 대해 살펴 보았습니다.

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

되시리라 생각됩니다. 유투브 동영상에는

이것외에 몇개의 예제를 더 첨부했으니

이해하시는데 도움이 되실것입니다.

-감사합니다.-

엑셀 TIP(팁)- 통계값의 변화에 따른 결과값의 변화 과정을 쉽게 구할 수 있는 데이터값에 대해 알아봅니다.

 

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

오늘은 엑셀에서 통계값의 변화에 따른

결과값의 변화 과정을 쉽게 구할 수 있는

데이터값에 대해 살펴 보겠습니다.

위 그림에서 보듯이 판매량과 판매가에

따른 영업이익의 변화량을 구해 보면

먼저 중요한게 11월 영업이익의 우측

테이블에서 보면 (A),(B)의 값이 48000000

과 18000000을 직접 입력하면 안되고

좌측의 테이블 처럼 매출총액은

판매가*판매량의 수식에의해 또는

함수식이 들어가야 합니다.

영업이익도 마찬가지고 총매출액에서

생산원가,임대료,인건비를 뺀 수식이

들어가야 합니다.

판매량과 판매가에 따른 영업이익의

변화량을 구하기 위해 작성된 표가

있는 D22셀을 클릭하고 =D17을 입력

하고 엔터를 칩니다. 그러면 D17의 값이

입력이 됩니다. 그런데 여기도 마찬가지로

직접 18000000을 입력하면 안됩니다.

(A)부분을 선택하고 상단메뉴의 데이터를

클릭하고 리본메뉴에서 가상분석(C)을

클릭하면 나오는 메뉴에서 데이터값(D)를

선택합니다.

그러면 데이터테이블 창이 하나 뜨는데

여기가 중요합니다.

행 입력셀에는 테이블표에서 보면

행방향이 판매량이고 열방향이 판매가

이므로 행입력셀에는 D12셀을 클릭하고

F4키를 눌러 절대참조로 바꿔줍니다.

열입력셀에는 D11셀을 클릭하고

마찬가지로 F4키를 눌러 절대참조로

바꿔줍니다. 그리고 확인을 누릅니다.

그러면 위 그림과 같이 값이 구해진

것을 알수 있습니다.

이번에는 행과 열값이 아닌 열값만

존재할때 구하는 방법에 대해 알아

보겠습니다.

위 그림은 이자율 변화에 따른 대출금의

상환금액 변동액을 구하는 예시 입니다.

여기서도 마찬가지로 (A)부분의

상환금액의 값도 임의로 입력하면 안되고

반드시 수식또는 함수식에 의해 결정

되어야 합니다.

이럴경우 PMT함수를 사용하는데 함수식은

=-PMT(이자율,납입기간,대출금)입니다.

여기서 이자율은 연이자율이므로 월이자율을

위해 12로 나누어 줍니다.

납입기간 또한 5년이므로 월단위를 위해

12를 곱해줍니다. 따라서

=-PMT($C$9/12,$C$10*12,200000)입력 후

엔터를 칩니다. 그러면 386656값이 입력이

됩니다.여기서 PMT함수 앞에 -를 붙인것은

금액이 -가 나오는 것을 방지하기 위해서

입니다.

구해야할 테이블표에서 H8셀을 선택하고

=C11을 입력하고 엔터를 칩니다.그러면

C11의 셀값이 입력이 됩니다.

여기도 마찬가지로 직접 값을 입력하시면

안됩니다.

(A)부분을 선택하고 데이터/가상분석/

데이터값을 선택합니다. 그러면

위와 같은 창이 뜨는데 여기서는 열값만

존재하므로 행입력셀에는 비워두고

열입력셀을 C9를 클릭해서 입력 합니다.

그리고 확인을 누르면 위 그림과 같이

이자율 변동에 따른 원금상환액을

자동으로 구해짐을 알수 있습니다.

​

이처럼 오늘은 통계값의 변화에 따른

결과값의 변화 과정을 쉽게 구할 수 있는

데이터값에 대해 살펴보았습니다.

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

되시리라 생각됩니다.

-감사합니다.-

엑셀 TIP(팁)- 엑셀의 테이블 필터링에 대해 알아봅니다.

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

 오늘은 엑셀에서 테이블 필터링에 대해

알아보겠습니다. 데이터의 자동필터,

숫자필터,고급필터를 사용하는 방법에

대해 실습을 통해 살펴 보겠습니다.

엑셀을 실행하시고 위 그림과 같은

데이터테이블이 있는경우

(A)부분을 클릭 후 드래그해서 선택하고

상단메뉴의 데이터를 클릭하고 리본메뉴의

필터를 클릭합니다. 또는 단축키

CTRL+SHIFT+L 을 눌러도 동일합니다.

그러면 우측의 그림과 같이 목록부분에

화살표가 생성됨을 알수 있습니다.

다시한번 필터를 클릭하거나

CTRL+SHIFT+L을 누르시면 해제 됩니다.

직원명 우측의 화살표를 클릭하면

나오는 메뉴에서 제일 상단의

텍스트오름차순정렬을 선택해 정렬

할수 있습니다.

부서 부분의 화살표(A)를 클릭해서

나오는 메뉴에서 특정부서만 선택해서

필터링 할수 있습니다. 위 그림에서는

기획부와 영업부만 체크한 상태입니다.

급여 부분의 화살표(A)를 클릭해서

나오는 메뉴에서 숫자필터(B)를 선택

하고 나오는 메뉴에서 크거나 같음(C)

을 선택합니다. 그리고 위 그림과 같이

조건을 부여하고 확인을 클릭하시면

(D)와 같은 결과를 얻으실수 있습니다.

데이터가 필터링이 되면 위 그림과 같이

화살표 모양이 깔대기 모양으로 변경

됩니다.

급여 부분의 깔대기 모양을 클릭해서

나오는 메뉴에서 필터해제를 할수도

있습니다.

고급필터의 경우는 위 그림과 같이

데이터(A)를 선택하고 메뉴의

데이터/ 고급필터(C)를 클릭하면 창이

뜨는데 여기서 목록범위와 조건범위를

지정합니다. 그리고 필터링 결과를

현재 데이터위치에 표시할 것인지

아니면 지정된 다른 위치에 표시

할것인지를 결정하고 확인을 누르시면

됩니다.

위 그림은 고급필터를 동일조건으로

필터링해서 필터링 결과를

다른장소에 복사를 선택(B21셀을클릭)

한 결과 입니다.

이렇게 데이터테이블을 자동필터

숫자필터,고급필터를 간략하게 사용하는

방법에 대해 살펴 보았습니다.

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

되시리라 생각됩니다.

-감사합니다.-