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

Microsoft Excel에서 데이터 테이블은 What-If 분석 도구라고 하는 명령 모음의 일부입니다. 데이터 테이블을 생성하고 분석할 때 가상 분석을 수행합니다.

가상 분석은 셀의 값을 변경하여 이러한 변경 내용이 워크시트의 수식 결과에 어떤 영향을 미치는지 확인하는 프로세스입니다. 예를 들어 데이터 테이블을 사용하여 대출의 이자율 및 기간 길이를 변경하여 잠재적인 월별 지불 금액을 평가할 수 있습니다.

가상 분석 유형    

Excel에는 시나리오, 데이터 테이블 및 목표 검색의 세 가지 유형의 가상 분석 도구가 있습니다. 시나리오 및 데이터 테이블은 입력 값 집합을 사용하여 가능한 결과를 계산합니다. 목표 검색은 뚜렷하게 다르며, 단일 결과를 사용하고 해당 결과를 생성할 수 있는 입력 값을 계산합니다.

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

데이터 테이블은 두 개 이상의 변수를 수용할 수 없습니다. 두 개 이상의 변수를 분석하려면 시나리오를 대신 사용해야 합니다. 하나 또는 두 개의 변수(행 입력 셀에 대한 변수와 열 입력 셀에 대한 변수)로만 제한되지만 데이터 테이블에는 원하는 만큼 다양한 변수 값이 포함될 수 있습니다. 시나리오에는 최대 32개의 다른 값이 있을 수 있지만 원하는 만큼 시나리오를 만들 수 있습니다.

자세한 내용은 What-If 분석 소개 문서에서 자세히 알아보세요.

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

하나의 변수 데이터 테이블    

하나 이상의 수식에서 한 변수의 다른 값이 이러한 수식의 결과를 변경하는 방법을 확인하려면 한 변수 데이터 테이블을 사용합니다. 예를 들어 하나의 변수 데이터 테이블을 사용하여 PMT 함수를 사용하여 다른 이자율이 월별 모기지 지불에 미치는 영향을 확인할 수 있습니다. 변수 값을 한 열 또는 행에 입력하면 결과가 인접한 열 또는 행에 표시됩니다.

다음 그림에서 D2 셀에는 입력 셀 B3를 참조하는 결제 수식 =PMT(B3/12,B4,-B5)가 포함되어 있습니다.

2개 변수 데이터 테이블    

두 변수 데이터 테이블을 사용하여 한 수식에서 두 변수의 서로 다른 값이 해당 수식의 결과를 변경하는 방법을 확인합니다. 예를 들어 두 변수 데이터 테이블을 사용하여 이자율과 대출 조건의 다양한 조합이 월별 모기지 지불에 어떤 영향을 미치는지 확인할 수 있습니다.

다음 그림에서 C2 셀에는 두 개의 입력 셀 인 B3 및 B4를 사용하는 결제 수식 =PMT(B3/12,B4,-B5)가 포함되어 있습니다.

 

데이터 테이블 계산    

워크시트가 다시 계산될 때마다 데이터가 변경되지 않더라도 데이터 테이블도 다시 계산됩니다. 데이터 테이블이 포함된 워크시트의 계산 속도를 높이기 위해 계산 옵션을 변경하여 워크시트를 자동으로 다시 계산하지만 데이터 테이블은 다시 계산할 수 없습니다. 자세한 내용은 데이터 테이블이 포함된 워크시트에서 계산 속도 향상 섹션을 참조하세요.

하나의 변수 데이터 테이블에는 단일 열(열 지향) 또는 행 간(행 지향)에 입력 값이 포함됩니다. 한 변수 데이터 테이블의 모든 수식은 입력 셀 하나만 참조해야 합니다.

단계

1. 입력 셀에서 대체하려는 값 목록을 한 열 아래로 또는 한 행에 걸쳐 입력합니다. 값의 양쪽에 몇 개의 빈 행과 열을 그대로 둡니다.

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

   • 데이터 테이블이 열 방향 인 경우(변수 값이 열에 있는 경우) 셀의 한 행 위에 수식을 입력하고 값 열의 오른쪽에 셀 하나를 입력합니다. 이 1 변수 데이터 테이블은 열 지향이며 수식은 D2 셀에 포함되어 있습니다.
     
     
     
     다른 수식에 대한 다양한 값의 영향을 검사하려면 첫 번째 수식의 오른쪽에 있는 셀에 추가 수식을 입력합니다.

   • 데이터 테이블이 행 방향 인 경우(변수 값이 행에 있음) 첫 번째 값의 왼쪽에 있는 셀 열에 수식을 입력하고 값 행 아래에 셀 하나를 입력합니다.
     
     다른 수식에 대한 다양한 값의 영향을 검사하려면 첫 번째 수식 아래 의 셀에 추가 수식을 입력합니다.

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

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

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

   • 데이터 테이블이 열 방향이면 열 입력 셀 필드에 입력 셀에 대한 셀 참조 입력합니다. 위의 그림에서 입력 셀은 B3입니다.

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

     참고: 데이터 테이블을 만든 후 결과 셀의 형식을 변경할 수 있습니다. 그림에서 결과 셀의 형식은 통화로 지정됩니다.

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

다음 단계 실행

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

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

   • 데이터 테이블이 행 방향인 경우 데이터 테이블의 첫 번째 열에 있는 기존 수식 아래의 빈 셀에 새 수식을 입력합니다.

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

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

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

   • 데이터 테이블이 열 지향인 경우 열 입력 셀 상자에 입력 셀에 대한 셀 참조를 입력합니다.

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

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

단계

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. 확인을 클릭합니다.

두 변수 데이터 테이블의 예

두 변수 데이터 테이블은 금리와 대출 조건의 다양한 조합이 월별 모기지 지불에 미치는 영향을 보여 줄 수 있습니다. 이 그림에서 C2 셀에는 두 개의 입력 셀 인 B3 및 B4를 사용하는 결제 수식 =PMT(B3/12,B4,-B5)가 포함되어 있습니다.

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

계산 성능을 향상시키려면 다음 단계를 수행합니다.

1. 파일 > 옵션 > 수식을 클릭합니다.

2. 계산 옵션 섹션의 계산에서 데이터 테이블을 제외한 자동을 클릭합니다.

   팁: 필요에 따라 수식 탭에서 계산 옵션의 화살표를 클릭한 다음 계산 그룹에서자동 데이터 테이블 제외를 클릭합니다.

특정 목표 또는 더 큰 변수 데이터 집합이 있는 경우 몇 가지 다른 Excel 도구를 사용하여 가상 분석을 수행할 수 있습니다.

목표값 찾기

수식에서 예상되는 결과를 알고 있지만 수식이 결과를 가져오는 데 필요한 입력 값을 정확히 모르는 경우 Goal-Seek 기능을 사용합니다. 입력 값을 조정하여 원하는 결과를 찾으려면 목표 검색 사용 문서를 참조하세요.

Excel 해 찾기

Excel Solver 추가 기능을 사용하여 입력 변수 집합에 대한 최적 값을 찾을 수 있습니다. 해 찾기는 목표 및 제약 조건 셀의 수식을 계산하는 데 사용되는 셀 그룹(의사 결정 변수 또는 단순히 변수 셀이라고 함)과 함께 작동합니다. 해 찾기는 제한 조건 셀의 제한을 충족하고 목표 셀에 예상되는 결과를 생성하기 위해 결정 변수 셀의 값을 조정합니다. 이 문서에서 자세히 알아보세요. 해 찾기를 사용하여 문제를 정의하고 해결합니다.