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

데이터 테이블은 일부 셀의 값을 변경하고 문제의 다른 답변을 내보일 수 있는 셀의 범위입니다. 데이터 테이블의 좋은 예는 주택 담보 대출에 대한 저렴한 금액을 계산하기 위해 다른 대출 금액 및 이자율을 사용하여 PMT 함수를 사용했습니다. 서로 다른 값으로 실험하여 결과의 해당 변형을 관찰하는 것은 데이터 분석의 일반적인 작업입니다.

이 Microsoft Excel 데이터 테이블은 분석 도구로 알려진 명령 모음의 What-If 있습니다. 데이터 테이블을 구성하고 분석할 때 what-if 분석을 수행하고 있습니다.

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

참고: 데이터 테이블 및 VBA(데이터 테이블)를 사용하여 더 Visual Basic for Applications 수 있습니다. 자세한 내용은 데이터 테이블 Excel What-If: VBA를사용하여 더 빠른 계산을 참조하세요.

what-if 분석의 유형    

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

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

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

자세한 내용은 분석에 대한 소개 What-If.

테스트해야 하는 변수 및 수식의 수에 따라 하나의 변수 또는 두 개의 변수 데이터 테이블을 만들 수 있습니다.

하나의 변수 데이터 테이블    

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

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

변수가 하나인 데이터 표

2개의 변수 데이터 테이블    

두 개의 변수 데이터 테이블을 사용하여 한 수식에서 두 변수의 서로 다른 값이 이 수식의 결과를 어떻게 변경하는지 볼 수 있습니다. 예를 들어 2개의 변수 데이터 테이블을 사용하여 이자율 및 대출 조건의 서로 다른 조합이 월간 모기지 상환에 어떤 영향을 주는지 볼 수 있습니다.

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

변수가 두 개인 데이터 표
 

데이터 테이블 계산    

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

한 변수 데이터 테이블에는 단일 열(열 지향) 또는 행(행 지향)의 입력 값이 포함되어 있습니다. 한 변수 데이터 테이블의 모든 수식은 1개 의 수식만 참조해야 입력 셀.

단계

  1. 입력 셀에서 대체할 값 목록을 입력합니다( 열 한 개 아래 또는 한 행에 걸쳐). 값의 양 쪽에 몇 개의 빈 행과 열을 떠날 수 있습니다.

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

    • 데이터 테이블이 열 지향인 경우(변수 값이 열에 있는 경우 위의 한 행의 셀에 수식을 입력하고 한 셀을 값 열 오른쪽에 입력합니다. 이 하나의 변수 데이터 테이블은 열 지향으로, 수식은 D2 셀에 포함되어 있습니다.


      변수가 하나인 데이터 표
      다른 수식에 다양한 값의 효과를 검사하려는 경우 첫 번째 수식의 오른쪽에 셀에 추가 수식을 입력합니다.

    • 데이터 테이블이 행 지향인 경우(변수 값이 행에 있는 경우) 첫 번째 값의 왼쪽에 있는 셀에 수식을 입력하고 값 행 아래에 하나의 셀을 입력합니다.

      다른 수식에 다양한 값의 효과를 검사하려는 경우 첫 번째 수식 아래에 셀에 추가 수식을 입력합니다.

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

  4. 데이터 탭에서What-If Analysis >데이터 테이블(데이터 도구 그룹 또는 예측 그룹)을 Excel 2016.  

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

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

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

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

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

다음 단계 실행

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

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

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

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

  3. 데이터 탭에서 데이터 테이블의 What-If analysis> 데이터 테이블(데이터 도구 그룹 또는 예측 그룹)을 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 )에서 What-If Analysis >데이터 테이블(데이터 도구 그룹 또는 예측 그룹)을 Excel 2016.  

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

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

  8. 확인을 클릭합니다.

2개의 변수 데이터 테이블 예제

2개의 변수 데이터 테이블은 이자율 및 대출 조건의 서로 다른 조합이 월간 모기지 상환에 어떤 영향을 주는지 보여 줄 수 있습니다. 여기서 그림에서 셀 C2에는 두 개의 입력 셀인 B3 및 B4를 사용하는 =PMT(B3/12,B4,-B5)의결제 수식이 포함되어 있습니다.

변수가 두 개인 데이터 표

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

다음 단계를 수행하여 계산 성능을 향상합니다.

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

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

    팁: 선택적으로 수식 탭에서 계산 옵션의 화살표를 클릭한다음 데이터 테이블 제외 자동(계산 그룹)을 클릭합니다.

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

목표값 찾기

수식에서 예상되는 결과를 알고 있지만 수식이 해당 결과를 얻기 위해 어떤 입력 값을 정확히 알지 못하면 Goal-Seek 기능을 사용하세요. 입력 값을 조정하여 원하는 결과를 찾으기 위해 목표 검색 사용 문서를 참조하세요.

Excel 해결사

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

셀에 다른 숫자를 연결하면 질문에 대한 다양한 답변을 신속하게 내보일 수 있습니다. 좋은 예는 주택 또는 자동차에 대해 감당할 수 있는 대출의 정도를 알아내기 위해 다른 금리 및 대출 기간(개월)을 사용하는 PMT 함수를 사용하는 것입니다. 데이터 테이블이라는 셀 범위에 숫자를 입력합니다.

여기서 데이터 테이블은 B2:D8 셀의 범위입니다. B4의 값, 대출 금액 및 D 열의 월별 지급액을 자동으로 업데이트할 수 있습니다. 3.75%의 이자율을 사용하여 D2는 =PMT(C2/12,$B$3 $B,042.01의 월 상환액을 반환합니다.

이 B2:D8 셀 범위가 데이터 표입니다.

테스트하려는 변수 및 수식의 수에 따라 하나 또는 두 개의 변수를 사용할 수 있습니다.

한 변수 테스트를 사용하여 수식의 한 변수의 값이 서로 다른 결과를 어떻게 변경하는지 볼 수 있습니다. 예를 들어 PMT 함수를 사용하여 월간 모기지 상환에 대한 이자율을 변경할 수 있습니다. 한 열 또는 행에 변수 값(이자율)을 입력하고 결과는 가까운 열 또는 행에 표시됩니다.

이 라이브 통합 문서에서 D2 셀에는 지불 수식 =PMT(C2/12,$B$3,$B$4)가 포함되어 있습니다. 셀 B3은 다른 기간 길이(월별 지불 기간 수)에 연결할 수 있는 가변 셀입니다. D2 셀에서 PMT 함수는 이자율 3.75%/12, 360개월 및 $225,000 대출을 연결하고 $ 1,042.01 월 상환액을 계산합니다.

2개의 변수 테스트를 사용하여 수식에서 두 변수의 서로 다른 값이 결과를 어떻게 변경하는지 볼 수 있습니다. 예를 들어 이자율과 월별 상환 기간의 여러 조합을 테스트하여 모기지 상환액을 계산할 수 있습니다.

이 라이브 통합 문서의 셀 C3에는 두 개의 변수 셀인 B2 및 B3를 사용하는 =PMT($B$3/12,$B$2,B4)가 포함되어 있습니다. 셀 C2에서 PMT 함수는 이자율 3.875%/12, 360개월 및 $225,000 대출을 연결하고 $ 1,058.03 월 상환액을 계산합니다.

추가 지원

언제든지 Excel 기술 커뮤니티의 전문가에게 질문하고, Answers 커뮤니티에서 지원을 받을 수 있습니다.

추가 도움이 필요하신가요?

Office 기술 확장
교육 살펴보기
새로운 기능 우선 가져오기
Office Insider 참여

이 정보가 유용한가요?

의견 주셔서 감사합니다!

피드백을 주셔서 감사합니다. Office 지원 에이전트와 연락하는 것이 도움이 될 것 같습니다.

×