데이터 테이블을 사용한 What-If 분석

Excel의 데이터 테이블을 사용하면 하나 또는 두 개의 입력을 쉽게 변경하고 What-if 분석을 수행 할 수 있습니다. 데이터 테이블은 일부 셀의 값을 변경하고 문제에 대한 다른 답을 얻을 수있는 셀 범위입니다.

두 가지 유형의 데이터 테이블이 있습니다-

단일 변수 데이터 테이블
2 변수 데이터 테이블

분석 문제에 2 개 이상의 변수가있는 경우 Excel의 Scenario Manager Tool을 사용해야합니다. 자세한 내용 은이 자습서의 시나리오 관리자 를 사용한 What-If 분석 장을 참조하십시오 .

단일 변수 데이터 테이블

하나 이상의 수식에서 한 변수의 다른 값이 해당 수식의 결과를 어떻게 변경하는지 확인하려는 경우 단일 변수 데이터 테이블을 사용할 수 있습니다. 즉, 변수가 하나 인 데이터 테이블을 사용하면 하나의 입력을 변경하면 출력 개수가 변경되는 방식을 결정할 수 있습니다. 예를 들어이를 이해하게 될 것입니다.

Example

30 년 임기 동안 5,000,000의 대출이 있습니다. 다양한 이자율에 대한 월별 지불 (EMI)을 알고 싶습니다. 또한 두 번째 해에 지불되는이자와 원금 금액을 알고 싶을 수도 있습니다.

변수가 하나 인 데이터 테이블을 사용한 분석

변수가 하나 인 데이터 테이블을 사용한 분석은 세 단계로 수행해야합니다.

Step 1 − 필요한 배경을 설정합니다.

Step 2 − 데이터 테이블을 생성합니다.

Step 3 − 분석을 수행합니다.

이 단계를 자세히 이해합시다.

1 단계 : 필요한 배경 설정

이자율이 12 %라고 가정합니다.
모든 필수 값을 나열하십시오.
값이 포함 된 셀의 이름을 지정하면 수식에 셀 참조 대신 이름이 지정됩니다.
Excel 함수 (각각 PMT, CUMIPMT 및 CUMPRINC)를 사용하여 EMI, 누적이자 및 누적 원금에 대한 계산을 설정합니다.

워크 시트는 다음과 같아야합니다.

C 열의 셀 이름이 D 열의 해당 셀에 지정된대로 명명 된 것을 볼 수 있습니다.

2 단계 : 데이터 테이블 생성

다음과 같이 E 열 아래의 입력 셀에 대체하려는 이자율과 같은 값 목록을 입력합니다.

보시다시피 이자율 값 위에 빈 행이 있습니다. 이 행은 사용할 수식에 대한 것입니다.

첫 번째 함수 (PMT) 값 열의 한 행 위와 한 셀 오른쪽에있는 셀에서. 다른 기능 (CUMIPMT and CUMPRINC) 첫 번째 함수의 오른쪽에있는 셀에 있습니다.

이제 이자율 값 위의 두 행은 다음과 같습니다.

데이터 테이블은 다음과 같습니다.

3 단계 : What-If 분석 데이터 테이블 도구를 사용하여 분석 수행

대체 할 수식과 값이 포함 된 셀 범위를 선택합니다. 즉, E2 : H13 범위를 선택합니다.
리본에서 데이터 탭을 클릭합니다.
데이터 도구 그룹에서 What-if 분석을 클릭합니다.
드롭 다운 목록에서 데이터 테이블을 선택합니다.

Data Table 대화 상자가 나타납니다.

열 입력 셀 상자에서 아이콘을 클릭하십시오.
셀을 클릭 Interest_Rate, 즉 C2입니다.

열 입력 셀이 $ C $ 2로 사용되는 것을 볼 수 있습니다. 확인을 클릭하십시오.

데이터 테이블은 아래와 같이 각 입력 값에 대해 계산 된 결과로 채워집니다.

EMI 54,000을 지불 할 수 있다면 12.6 %의 이자율이 자신에게 적합하다는 것을 알 수 있습니다.

2 변수 데이터 테이블

수식에서 두 변수의 서로 다른 값이 해당 수식의 결과를 어떻게 변경하는지 확인하려는 경우 변수가 2 개인 데이터 테이블을 사용할 수 있습니다. 즉, 두 변수 데이터 테이블을 사용하면 두 입력을 변경하면 단일 출력이 어떻게 변경되는지 확인할 수 있습니다. 예를 들어이를 이해하게 될 것입니다.

Example

50,000,000의 대출이 있습니다. 이자율과 대출 기간의 다양한 조합이 월별 지불 (EMI)에 어떤 영향을 미치는지 알고 싶습니다.

두 변수 데이터 테이블을 사용한 분석

2 변수 데이터 테이블을 사용한 분석은 3 단계로 수행해야합니다.

Step 1 − 필요한 배경을 설정합니다.

Step 2 − 데이터 테이블을 생성합니다.

Step 3 − 분석을 수행합니다.

1 단계 : 필요한 배경 설정

이자율이 12 %라고 가정합니다.
모든 필수 값을 나열하십시오.
값이 포함 된 셀의 이름을 지정하면 수식에 셀 참조 대신 이름이 지정됩니다.
Excel 기능을 사용하여 EMI 계산 설정 – PMT.

워크 시트는 다음과 같아야합니다.

C 열의 셀 이름이 D 열의 해당 셀에 지정된대로 명명 된 것을 볼 수 있습니다.

2 단계 : 데이터 테이블 생성

유형 =EMI 셀 F2에서.

입력 값의 첫 번째 목록 (예 : F3 열 아래의 이자율)을 공식 아래의 셀 (예 : F3)로 시작합니다.
입력 값의 두 번째 목록, 즉, 수식 오른쪽에있는 셀 (예 : G2)부터 시작하여 행 2에 걸친 지불 수를 입력합니다.

데이터 테이블은 다음과 같습니다-

What-If 분석 도구 데이터 테이블을 사용하여 분석 수행

수식이 포함 된 셀 범위와 대체 할 두 세트의 값을 선택합니다. 즉, 범위를 선택합니다 – F2 : L13.
리본에서 데이터 탭을 클릭합니다.
데이터 도구 그룹에서 What-if 분석을 클릭합니다.
드롭 다운 목록에서 데이터 테이블을 선택합니다.

데이터 테이블 대화 상자가 나타납니다.

행 입력 셀 상자에서 아이콘을 클릭하십시오.
셀을 클릭 NPER, 즉 C3입니다.
다시 행 입력 셀 상자에서 아이콘을 클릭합니다.
그런 다음 열 입력 셀 상자에서 아이콘을 클릭하십시오.
C2 인 Interest_Rate 셀을 클릭하십시오.
다시 열 입력 셀 상자에서 아이콘을 클릭합니다.

행 입력 셀은 $ C $ 3으로, 열 입력 셀은 $ C $ 2로 간주됩니다. 확인을 클릭하십시오.

데이터 테이블은 두 입력 값의 각 조합에 대해 계산 된 결과로 채워집니다.

EMI 54,000을 지불 할 수 있다면 이자율 12.2 %와 288 EMI가 적합합니다. 즉, 대출 기간은 24 년입니다.

데이터 테이블 계산

데이터 테이블은 변경되지 않았더라도이를 포함하는 워크 시트가 다시 계산 될 때마다 다시 계산됩니다. 데이터 테이블이 포함 된 워크 시트에서 계산 속도를 높이려면 계산 옵션을 다음과 같이 변경해야합니다.Automatically Recalculate 다음 섹션에 설명 된대로 데이터 테이블이 아닌 워크 시트입니다.