데이터 표를 사용하여 여러 결과 계산

Microsoft Excel에서 데이터 테이블은 가상 분석 도구라고 하는 명령 모음 중 하나입니다. 데이터 테이블을 생성하고 분석하는 것이 곧 가상 분석을 수행하는 것입니다.

가상 분석은 셀 값을 변경하여 이 변경 내용이 워크시트의 수식 결과에 어떤 영향을 미치는지를 확인하는 과정입니다. 예를 들어 데이터 테이블을 사용해 대출의 이자율과 기간을 바꿔가며 월 상환액을 평가할 수 있습니다.

가상 분석 유형    

Excel에는 세 가지 가상 분석 도구가 있습니다. 시나리오, 데이터 테이블, 그리고 목푯값 찾기입니다. 시나리오와 데이터 테이블은 입력값 집합을 사용하여 가능한 결과를 계산합니다. 목푯값 찾기는 확연히 다릅니다. 단일 결과를 기준으로 그 결과를 만들 수 있는 입력값을 계산합니다.

시나리오와 마찬가지로 데이터 테이블은 가능한 결과 집합을 탐색하는 데 도움이 됩니다. 하지만 시나리오와 달리 데이터 테이블은 한 워크시트의 한 테이블에 모든 결과를 보여줍니다. 데이터 테이블을 사용하면 다양한 가능성을 한눈에 쉽게 살펴볼 수 있습니다. 변수가 하나 또는 두 개뿐이라 결과를 표 형태로 쉽게 읽고 공유할 수 있습니다.

데이터 테이블은 세 개 이상의 변수를 수용할 수 없습니다. 두 개 이상의 변수를 분석하려면 대신 시나리오를 사용해야 합니다. 데이터 테이블의 행 입력 셀과 열 입력 셀 각각 하나씩, 최대 두 개 변수로 제한되지만, 원하는 대로 여러 다양한 변숫값을 넣을 수 있습니다. 시나리오는 서로 다른 값을 최대 32개까지 사용할 수 있지만, 원하는 만큼 많은 시나리오를 만들 수 있습니다.

가상 분석 소개 문서에서 자세히 알아보세요.

테스트할 변수와 수식 수에 따라 1변수 또는 2변수 데이터 테이블을 만듭니다.

1변수 데이터 테이블    

하나 이상의 수식에서 한 개 변수에 여러 다른 값을 입력할 때 결과가 어떻게 달라지는지 보려면 1변수 데이터 테이블을 사용합니다. 예를 들어 1변수 데이터 테이블에서 PMT 함수를 사용하여 다양한 이자율에 따라 월 대출 상환액에 어떻게 달라지는지 확인할 수 있습니다. 변숫값을 한 열이나 한 행에 입력하면 결과가 인접한 열이나 행에 표시됩니다.

다음 그림에서 D2 셀에는 =PMT(B3/12,B4,-B5)라는 결제 수식이 있으며, 이 수식은 입력 셀 B3를 참조합니다.

2변수 데이터 테이블    

2변수 데이터 테이블을 사용하여 한 수식에서 두 개 변수의 값에 따라 해당 수식의 결과가 어떻게 달라지는지 확인합니다. 예를 들어 2변수 데이터 테이블에서 이자율과 대출 조건을 서로 다르게 조합할 때 월 대출 상환액에 어떤 영향을 미치는지 확인할 수 있습니다.

다음 그림에서 C2 셀에는 =PMT(B3/12,B4,-B5)라는 결제 수식이 있으며, 이 수식은 입력 셀 B3과 B4를 사용합니다.

 

데이터 테이블 계산    

워크시트가 다시 계산될 때는 데이터가 변경되지 않았더라도 모든 데이터 테이블이 매번 다시 계산됩니다. 데이터 테이블이 포함된 워크시트 계산 속도를 높이려면 계산 옵션을 변경해 워크시트는 자동 재계산하지만 데이터 테이블은 다시 계산하지 않도록 설정할 수 있습니다. 자세한 내용은 데이터 테이블이 포함된 워크시트에서 계산 속도 향상 섹션을 참고하세요.

1변수 데이터 테이블은 입력값을 한 열(열 지향)이나 한 행(행 지향)에 넣습니다. 1변수 데이터 테이블의 모든 수식은 하나의 입력 셀만 참조해야 합니다.

단계

1. 입력 셀에 대체할 값 목록을 입력합니다. 한 열에 세로로 또는 한 행에 가로로 입력할 수 있습니다. 값의 양쪽에 빈 행과 열을 몇 개 남겨 둡니다.

2. 다음 중 하나를 실행합니다.

   • 데이터 테이블이 열 지향인 경우(변숫값이 한 열에 있는 경우) 수식을 값이 있는 열의 바로 위 행, 그리고 바로 오른쪽 셀에 입력합니다. 다음 1변수 데이터 테이블은 열 지향이며 수식은 D2 셀에 있습니다.
     
     
     
     다양한 값이 다른 수식에 미치는 영향을 보고 싶다면 첫 번째 수식의 오른쪽 셀에 추가 수식을 입력합니다.

   • 데이터 테이블이 행 지향인 경우(변숫값이 한 행에 있는 경우)에서는 수식을 값이 있는 행의 바로 아래 행과 첫 값 왼쪽 셀에 입력합니다.
     
     다양한 값이 다른 수식에 미치는 영향을 보고 싶다면 첫 번째 수식의 아래 셀에 추가 수식을 입력합니다.

3. 대체할 수식과 값이 포함된 셀 범위를 선택합니다. 위 그림에서 이 범위는 C2:D5입니다.

4. 데이터 탭에서 가상 분석 >  데이터 테이블 (데이터 도구 그룹 또는 Excel 2016의 예측 그룹)을 선택합니다.

5. 다음 중 하나를 실행합니다.

   • 데이터 테이블이 열 지향이면 열 입력 셀 필드에 셀 참조을(를) 입력합니다. 위 그림에서 입력 셀은 B3입니다.

   • 데이터 테이블이 행 지향이면 행 입력 셀 필드에 셀 참조를 입력합니다.

     참고: 데이터 테이블을 만든 후 결과 셀의 서식을 바꿀 수 있습니다. 그림에서 결과 셀은 통화로 서식이 지정되어 있습니다.

1변수 데이터 테이블에 사용되는 수식은 동일한 입력 셀을 참조해야 합니다.

다음 단계 실행

1. 다음 중 하나를 수행합니다.

   • 데이터 테이블이 열 지향이면, 데이터 테이블 맨 위 행에 있는 기존 수식 오른쪽 빈 셀에 새 수식을 입력합니다.

   • 데이터 테이블이 행 지향이면, 데이터 테이블 첫 번째 열에 있는 기존 수식 아래 빈 셀에 새 수식을 입력합니다.

2. 데이터 테이블과 새 수식이 포함된 셀 범위를 선택합니다.

3. 데이터 탭에서 가상 분석 > 데이터 테이블 (데이터 도구 그룹 또는 Excel 2016의 예측 그룹)을 선택합니다.

4. 다음 중 하나를 수행합니다.

   • 데이터 테이블이 열 지향이면 열 입력 셀 상자에 셀 참조를 입력합니다.

   • 데이터 테이블이 행 지향이면 행 입력 셀 상자에 셀 참조를 입력합니다.

2변수 데이터 테이블은 두 개의 입력값 목록을 포함하는 수식을 사용합니다. 수식은 서로 다른 두 입력 셀을 참조해야 합니다.

단계

1. 워크시트의 셀에 두 입력 셀을 참조하는 수식을 입력합니다.

   다음 예제에서는 수식의 시작 값이 B3, B4, B5 셀에 입력되어 있을 때 C2 셀에 =PMT(B3/12,B4,-B5) 수식을 입력합니다.

2. 수식 아래에 같은 열에 입력값 목록을 하나 입력합니다.

   이 경우 C3, C4, C5 셀에 서로 다른 이자율을 입력합니다.

3. 수식과 같은 행의 오른쪽에 두 번째 목록을 입력합니다.

   D2와 E2 셀에 대출 기간(월 단위)을 입력합니다.

4. 수식이 있는 셀(C2), 값이 있는 행과 열(C3:C5와 D2:E2), 그리고 계산 결과를 표시할 셀(D3:E5)을 포함하는 셀 범위를 선택합니다.

   이 경우 C2:E5 범위를 선택합니다.

5. 데이터 탭의 데이터 도구 그룹 또는 예측 그룹(Excel 2016)에서 가상 분석 > 데이터 테이블 (데이터 도구 그룹 또는 Excel 2016의 예측 그룹)을 선택합니다.

6. 행 입력 셀 필드의 행에 해당 입력값의 입력 셀 참조를 입력합니다.
   행 입력 셀 상자에 셀 B4를 입력합니다.

7. 열 입력 셀 필드의 열에 해당 입력값의 입력 셀 참조를 입력합니다.
   열 입력 셀 상자에 B3 셀을 입력합니다.

8. 확인을 선택합니다.

2변수 데이터 테이블의 예

2변수 데이터 테이블은 이자율과 대출 조건을 서로 다르게 조합할 때 월 대출 상환액에 어떤 영향을 미치는지 보여줄 수 있습니다. 여기 그림에서 C2 셀에는 =PMT(B3/12,B4,-B5)라는 결제 수식이 있으며, 이 수식은 입력 셀 B3과 B4를 사용합니다.

이 계산 옵션을 설정하면 통합 문서 전체를 다시 계산할 때 데이터 테이블 계산은 수행되지 않습니다. 데이터 테이블을 수동으로 다시 계산하려면 해당 수식을 선택한 후 F9 키를 누릅니다.

계산 성능을 높이려면 다음 단계를 따릅니다.

1. 파일 > 옵션 > 수식을 선택합니다.

2. 계산 옵션 섹션에서 자동을 선택합니다.

   팁: 필요하면 수식 탭에서 계산 옵션 옆 화살표를 선택한 다음, 자동을 선택합니다.

특정 목표가 있거나 변수 데이터가 많을 때는 다른 Excel 도구를 사용해 가상 분석을 할 수 있습니다.

목표값 찾기

수식에서 예상되는 결과는 알고 있지만, 그 결과를 얻기 위해 필요한 입력값을 모를 때는 목푯값 찾기 기능을 사용합니다. 목푯값 찾기를 통해 입력값을 조정하여 원하는 결과 찾기를 참조하세요.

Excel 해결사

Excel 해결사 추가 기능을 사용하면 입력 변수 집합에 대한 최적 값을 찾을 수 있습니다. 해결사에는 목표 및 제한 셀의 수식을 계산하는 데 사용되는 결정 변수 또는 단순히 변수 셀이라고 하는 셀 그룹이 사용됩니다. 해 찾기는 제한 조건 셀의 제한을 충족하고 목표 셀에 예상되는 결과를 생성하기 위해 결정 변수 셀의 값을 조정합니다. 자세한 내용은 해결사를 사용해 문제 정의 및 해결하기 문서에서 확인하세요.