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

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

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

 

• 단일 변수 데이터 테이블
• 변수가 2개인 데이터 테이블

 

분석 문제에 변수가 두 개 이상 있는 경우 Excel의 시나리오 관리자 도구를 사용해야 합니다. 

 

단일 변수 데이터 테이블

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

 

 

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

 

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

하나의 변수 데이터 테이블과 분석은 세 단계로 수행해야합니다 

• 1 단계 - 필요한 배경을 설정합니다.
• 2 단계 - 데이터 테이블을 만듭니다.
• 3 단계 - 분석을 수행합니다.

 

1단계: 필요한 배경 설정

이자율이 12%라고 가정합니다.

필요한 모든 값을 나열합니다.

수식이 셀 참조 대신 이름을 갖도록 값이 포함된 셀의 이름을 지정합니다.

Excel 함수(각각 PMT, CUMIPMT 및 CUMPRINC)를 사용하여 EMI, 누적 이자 및 누적 원금에 대한 계산을 설정합니다.

워크 시트는 다음과 같이 보일 것입니다 

 

C 열의 셀 이름이 D 열의 해당 셀에 지정된 대로 지정되었음을 알 수 있습니다.

 

2단계: 데이터 테이블 만들기

다음과 같이 입력 셀에서 입력하는 입력 셀에서 대체하려는 이자율, 즉 이자율의 목록을 입력하십시오.

첫 번째 함수(PMT)를 셀의 한 행 위와 값 열의 오른쪽에 한 칸 입력합니다. 다른 함수(CUMIPMT 및 CUMPRINC)를 첫 번째 함수의 오른쪽에 있는 셀에 입력합니다.

지금, 이자율의 값 위의 두 행은 다음과 같이 보인다 -

 

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

• 대체할 수식과 값이 포함된 셀 범위를 선택합니다(예: 범위 선택 – E2:H13).
• 리본에서 데이터 탭을 클릭합니다.
• 데이터 도구 그룹에서 가상 분석을 클릭합니다.
• 드롭다운 목록에서 데이터 테이블을 선택합니다.

 

 

• 데이터 테이블 대화 상자가 나타납니다.
• 열 입력 셀 상자에서 아이콘을 클릭합니다.
• Interest_Rate C2 셀을 클릭합니다.

 

 

• 열 입력 셀이 $C$2로 표시되는 것을 볼 수 있습니다. 확인을 클릭합니다.
• 데이터 테이블은 아래와 같이 입력 값의 각각에 대해 계산 된 결과로 채워진다

 

 

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

 

변수가 2개인 데이터 테이블

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

 

 

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

 

변수가 2개인 데이터 표를 사용한 분석

두 변수 데이터 테이블과 분석은 세 단계로 수행해야합니다 -

• 1 단계 - 필요한 배경을 설정합니다.
• 2 단계 - 데이터 테이블을 만듭니다.
• 3 단계 - 분석을 수행합니다.

 

1단계: 필요한 배경 설정

• 이자율이 12%라고 가정합니다.
• 필요한 모든 값을 나열합니다.
• 수식에 셀 참조 대신 이름이 지정되도록 값이 포함된 셀의 이름을 지정합니다.
• Excel 기능(PMT)을 사용하여 EMI 계산을 설정합니다.
• 워크 시트는 다음과 같이 보일 것입니다

 

 

C 열의 셀 이름이 D 열의 해당 셀에 지정된 대로 지정되었음을 알 수 있습니다.

 

2단계: 데이터 테이블 만들기

• F2 셀에 =EMI를 입력합니다.

 

 

 

• 입력 값의 첫 번째 목록(예: 이자율)을 F 열 아래에 입력하고 수식 아래의 셀(예: F3)부터 시작합니다.
• 입력 값의 두 번째 목록(예: 2행의 지불 횟수)을 수식 오른쪽에 있는 셀(예: G2)부터 시작하여 입력합니다.
• 데이터 테이블은 다음과 같이 보입니다

 

 

 

 

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

• 수식이 포함된 셀 범위와 대체할 두 값 집합, 즉 범위 선택(예: F2:L13)을 선택합니다.
• 리본에서 데이터 탭을 클릭합니다.
• 데이터 도구 그룹에서 가상 분석을 클릭합니다.
• 드롭다운 목록에서 데이터 테이블을 선택합니다.

 

 

• 데이터 테이블 대화 상자가 나타납니다.
• 행 입력 셀 상자에서 아이콘을 클릭합니다.
• NPER 셀(C3)을 클릭합니다.
• 다시 행 입력 셀 상자에서 아이콘을 클릭합니다.
• 그런 다음 열 입력 셀 상자에서 아이콘을 클릭합니다.
• Interest_Rate C2 셀을 클릭합니다.
• 다시 열 입력 셀 상자에서 아이콘을 클릭합니다.

 

 

• 행 입력 셀은 $C$3로, 열 입력 셀은 $C$2로 표시되는 것을 볼 수 있습니다. 확인을 클릭합니다.
• 데이터 테이블은 두 입력 값의 각 조합에 대해 계산 된 결과로 채워집니다 

 

 

54,000의 EMI를 지불할 수 있다면 12.2%의 이자율과 288 EMI가 적합합니다. 이는 대출 기간이 24년임을 의미합니다.

 

데이터 테이블 계산

데이터 테이블은 변경되지 않았더라도 해당 테이블이 포함된 워크시트가 다시 계산될 때마다 다시 계산됩니다. 데이터 테이블이 포함된 워크시트에서 계산 속도를 높이려면 다음 섹션에 나와 있는 것처럼 계산 옵션을 워크시트를 자동으로 다시 계산하지만 데이터 테이블은 다시 계산하지 않도록 변경해야 합니다.

 

워크시트에서 계산 속도 향상

 

두 가지 방법으로 데이터 테이블을 포함하는 워크 시트에서 계산 속도를 높일 수 있습니다 -

 

Excel 옵션

리본에서 파일 탭을 클릭합니다.

왼쪽 창의 목록에서 옵션을 선택합니다.

Excel 옵션 대화 상자가 나타납니다.

왼쪽 창에서 수식을 선택합니다.

계산 옵션 섹션의 통합 문서 계산에서 데이터 테이블을 제외한 자동 옵션을 선택합니다. 확인을 클릭합니다.

 

 

리본

• 리본에서 수식 탭을 클릭합니다.
• 계산 그룹에서 계산 옵션을 클릭합니다.
• 드롭다운 목록에서 데이터 테이블을 제외하고 자동을 선택합니다.