시나리오를 사용하여 다양한 값 집합 간 전환

Excel에는 시나리오, 데이터 테이블 및 목표 검색과 같은 세 가지 종류의 What-If 분석 도구가 있습니다. 시나리오 및 데이터 테이블은 입력 값 집합을 가져와서 가능한 결과를 결정합니다. 목표 검색은 결과를 가져와서 해당 결과를 생성하는 가능한 입력 값을 결정하는 프로젝트라는 점에서 시나리오 및 데이터 테이블과 다릅니다.

각 시나리오는 최대 32개의 변수 값을 수용할 수 있습니다. 32개 이상의 값을 분석하고 값이 하나 또는 두 개의 변수만 나타내는 경우 데이터 테이블을 사용할 수 있습니다. 하나 또는 두 개의 변수(행 입력 셀에 대한 변수와 열 입력 셀에 대한 변수)로만 제한되지만 Data Table에는 원하는 만큼 다양한 변수 값이 포함될 수 있습니다. 시나리오에는 최대 32개의 다른 값이 있을 수 있지만 원하는 만큼 시나리오를 만들 수 있습니다.

이러한 세 가지 도구 외에도 해 찾기 추가 기능과 같은 What-If 분석을 수행하는 데 도움이 되는 추가 기능을 설치할 수 있습니다. 해 찾기 추가 기능은 목표값 찾기와 유사하지만, 더 많은 변수를 수용할 수 있습니다. Excel에 기본 제공되는 다양한 명령과 채우기 핸들을 사용하여 예측을 만들 수도 있습니다. 고급 모델의 경우 분석 도구 추가 기능을 사용할 수 있습니다.

예산을 만들려고 하지만 수익이 불확실하다고 가정해 보겠습니다. 시나리오를 사용하면 수익에 대해 가능한 다양한 값을 정의한 다음 시나리오 간에 전환하여 가상 분석을 수행할 수 있습니다.

예를 들어 최악의 경우 예산 시나리오가 총 수익 $50,000이고 판매된 상품의 비용은 $13,200이며 총 수익은 36,800달러라고 가정합니다. 이 값 집합을 시나리오로 정의하려면 먼저 다음 그림과 같이 워크시트에 값을 입력합니다.

변경 셀에는 입력한 값이 있고 Result 셀에는 변경 셀을 기반으로 하는 수식이 포함되어 있습니다(이 그림 셀 B4에는 =B2-B3 수식이 있습니다).

그런 다음 , 시나리오 관리자 대화 상자를 사용하여 이러한 값을 시나리오로 저장합니다. 데이터 탭 > What-If Analysis > Scenario Manager > 추가를 참조하세요.

시나리오 이름 대화 상자에서 시나리오 이름을 최악의 경우로 지정하고 B2와 B3 셀이 시나리오 간에 변경되는 값임을 지정합니다. 시나리오를 추가하기 전에 워크시트에서 셀 변경을 선택하면 시나리오 관리자가 자동으로 셀을 삽입합니다. 그렇지 않으면 셀을 직접 입력하거나 셀 변경 대화 상자 오른쪽에 있는 셀 선택 대화 상자를 사용할 수 있습니다.

보호 – 시나리오를 보호할 수도 있으므로 보호 섹션에서 원하는 옵션을 검사 보호하지 않으려면 선택 취소합니다.

• 워크시트가 보호될 때 시나리오를 편집할 수 없도록 변경 방지 를 선택합니다.

• 숨김을 선택하여 워크시트가 보호될 때 시나리오가 표시되지 않도록 합니다.

이제 최상의 사례 예산 시나리오가 총 수익 $150,000이고 판매된 상품 비용은 $26,000이며 총 수익은 $124,000라고 가정합니다. 이 값 집합을 시나리오로 정의하려면 다른 시나리오를 만들고, 이름을 최적 사례로 지정하고, B2 셀(150,000) 및 셀 B3(26,000)에 대해 서로 다른 값을 제공합니다. 총이익(셀 B4)은 수익(B2)과 비용(B3)의 차이인 수식이므로 최상의 시나리오에서는 셀 B4를 변경하지 않습니다.

시나리오를 저장한 후에는 what-if 분석에서 사용할 수 있는 시나리오 목록에서 사용할 수 있게 됩니다. 이전 그림의 값을 고려할 때 최상의 시나리오를 표시하도록 선택한 경우 워크시트의 값은 다음 그림과 유사하게 변경됩니다.

고려하려는 모든 시나리오를 만드는 데 필요한 모든 정보가 하나의 워크시트 또는 통합 문서에 있는 경우가 있을 수 있습니다. 그러나 다른 원본에서 시나리오 정보를 수집할 수 있습니다. 예를 들어 회사 예산을 만들려고 한다고 가정해 보겠습니다. 이러한 각 원본에는 예산 만들기에 사용할 정보가 서로 다르기 때문에 Sales, Payroll, Production, Marketing 및 Legal과 같은 여러 부서에서 시나리오를 수집할 수 있습니다.

병합 명령을 사용하여 이러한 시나리오를 하나의 워크시트로 수집할 수 있습니다. 각 원본은 원하는 만큼 또는 적은 수의 변경 셀 값을 제공할 수 있습니다. 예를 들어 각 부서가 지출 예측을 제공하길 원할 수 있지만 몇 가지 수익 예측만 필요합니다.

병합을 선택하면 시나리오 관리자가 병합 시나리오 마법사를 로드합니다. 이 마법사는 현재 통합 문서에 있는 모든 워크시트를 나열하고 당시 열 수 있는 다른 통합 문서를 나열합니다. 마법사는 선택한 각 원본 워크시트에 있는 시나리오 수를 알려줍니다.

다양한 원본에서 다양한 시나리오를 수집하는 경우 각 통합 문서에서 동일한 셀 구조를 사용해야 합니다. 예를 들어 수익은 항상 B2 셀로 이동하고 지출은 항상 B3 셀로 이동될 수 있습니다. 다양한 원본의 시나리오에 다른 구조를 사용하는 경우 결과를 병합하기가 어려울 수 있습니다.

여러 시나리오를 비교하려면 동일한 페이지에 요약된 보고서를 만들 수 있습니다. 보고서는 시나리오를 나란히 나열하거나 피벗 테이블 보고서 표시할 수 있습니다.

앞의 두 예제 시나리오를 기반으로 하는 시나리오 요약 보고서는 다음과 같습니다.

Excel에 자동으로 그룹화 수준이 추가되어 다른 선택기를 클릭하면 보기가 확장되고 축소됩니다.

현재 값 열은 시나리오 요약 보고서를 만들 때 셀을 변경하는 값을 나타내고 각 시나리오에 대해 변경된 셀이 회색으로 강조 표시되어 있음을 설명하는 요약 보고서의 끝에 메모가 나타납니다.