Excel 2019(28부): What-if 분석(If-Then 분석)

Excel에는 What-if 분석을 포함하여 복잡한 수학적 계산을 수행하기 위한 강력한 도구가 포함되어 있습니다. 이 기능은 데이터가 불완전하더라도 데이터를 실험하고 질문에 답하는 데 도움이 될 수 있습니다. 이 기사에서는 Goal Seek라는 분석 도구를 사용하는 방법을 배웁니다.

목표 탐색

Excel에서 수식이나 함수를 만들 때마다 결과를 계산하기 위해 여러 부분을 결합해야 합니다. Goal Seek은 반대 방식으로 작동합니다. 원하는 결과로 시작하면 엔진이 해당 결과를 제공하는 입력 값을 계산합니다. 이 기사에서는 몇 가지 예를 사용하여 목표 탐색을 사용하는 방법을 보여줍니다.

목표 탐색 사용 방법의 예 1

당신이 수업에 등록했다고 가정해 봅시다. 현재 귀하의 점수는 65점이며 이 과목을 통과하려면 최소 70점을 획득해야 합니다. 다행히 GPA를 높이는 데 도움이 될 수 있는 마지막 과제가 하나 있습니다. 목표 탐색을 사용하여 해당 과목을 통과하기 위해 최종 연습에서 필요한 점수를 확인할 수 있습니다.

아래 이미지에서 처음 4개 연습의 점수가 58, 70, 72, 60임을 알 수 있습니다. 다섯 번째 연습의 점수가 어떻게 될지는 알 수 없지만 공식 또는 함수를 작성하여 계산할 수 있습니다. 최종 점수가 필요합니다. 이 경우 각 과제에는 동일한 가중치가 적용되므로 =AVERAGE(B2:B6)를 입력하여 5개 점수의 평균을 계산하기만 하면 됩니다 . 목표 탐색을 사용할 때 셀 B6에는 해당 최종 과제에 필요한 최소 점수가 표시됩니다.

1. 값을 변경하려는 셀을 선택하십시오. 목표 찾기를 사용할 때마다 이미 수식이나 함수가 포함된 셀을 선택해야 합니다. 예를 들어 셀 B7 에는 =AVERAGE(B2:B6) 수식이 포함되어 있으므로 선택합니다 .

2. 데이터 탭에서 What-If 분석 명령을 클릭한 다음 드롭다운 메뉴에서 목표 탐색을 선택합니다.

3. 세 개의 필드가 있는 대화 상자가 나타납니다. 첫 번째 필드인 Set cell: 에는 원하는 결과가 포함됩니다. 이 예에서는 셀 B7이 선택되었습니다.

두 번째 필드인 To value: 는 원하는 결과입니다. 예를 들어, 수업을 통과하려면 최소한 해당 숫자를 획득해야 하기 때문에 70을 입력합니다 .

세 번째 필드인 By Changing cell: 은 Goal Seek이 답변을 배치할 셀입니다. 예를 들어, 최종 연습에서 달성해야 하는 점수를 결정하기 위해 셀 B6을 선택합니다 .

4. 완료되면 확인을 클릭합니다.

5. 대화 상자에는 Goal Seek이 답을 찾을 수 있는지 여부가 표시됩니다. 확인을 클릭하세요 .

6. 결과는 지정된 셀에 나타납니다. 이 예에서 Goal Seek은 최종 과제에서 최소 90점을 획득해야 합격할 수 있다고 계산했습니다.

Goal Seek 사용 예시 2

이벤트를 계획 중이고 $500의 예산을 초과하지 않고 최대한 많은 사람을 초대하고 싶다고 가정해 보겠습니다. 목표 탐색을 사용하여 초대할 사람 수를 확인할 수 있습니다. 아래 예에서 셀 B5에는 총 예약 비용에 1인당 비용을 더한 금액을 계산하는 수식 =B2+B3*B4 가 포함되어 있습니다.

1. 값을 변경하려는 셀을 선택하십시오. 예를 들어 B5 셀을 선택하겠습니다.

2. 데이터 탭에서 What-If 분석 명령을 클릭한 다음 드롭다운 메뉴에서 목표 탐색을 선택합니다.

3. 세 개의 필드가 있는 대화 상자가 나타납니다. 첫 번째 필드인 Set cell: 에는 원하는 결과가 포함됩니다. 이 예에서는 셀 B5가 선택되었습니다.

두 번째 필드인 To value: 는 원하는 결과입니다. 예를 들어, $500만 지출하고 싶기 때문에 500을 입력합니다.

세 번째 필드인 By Changing cell: 은 Goal Seek이 답변을 배치할 셀입니다. 예를 들어, 500달러 이상을 지출하지 않고도 초대할 수 있는 손님 수를 알고 싶기 때문에 B4 셀을 선택합니다.

4. 완료되면 확인을 클릭합니다.

5. 대화 상자에는 Goal Seek이 답을 찾을 수 있는지 여부가 표시됩니다. 확인을 클릭하세요 .

6. 결과는 지정된 셀에 나타납니다. 이 예에서 Goal Seek은 답을 대략 18.62로 계산했습니다 . 이 경우 최종 답은 정수여야 하므로 반올림 또는 반내림이 필요합니다. 반올림하면 예산을 초과하므로 이 예에서는 손님을 18 명 으로 내림합니다 .

위의 예에서 볼 수 있듯이 일부 상황에서는 정수인 답이 필요합니다. Goal Seek이 소수점 숫자를 제공하는 경우 상황에 따라 반올림하거나 내림해야 합니다.

다른 유형의 가정 분석

고급 프로젝트의 경우 다른 유형의 가정 분석(시나리오 및 데이터 테이블)을 고려할 수 있습니다. Goal Seek처럼 원하는 결과에서 시작하여 거꾸로 작업하는 대신 이 옵션을 사용하면 여러 값을 테스트하고 결과가 어떻게 변하는지 확인할 수 있습니다.

스크립트를 사용하면 여러 셀(최대 32개)의 값을 한 번에 바꿀 수 있습니다. 원하는 만큼 시나리오를 생성한 후 수동으로 값을 변경하지 않고도 비교할 수 있습니다. 아래 예에서는 시나리오를 사용하여 예정된 이벤트에 대한 다양한 위치를 비교합니다.

데이터 테이블을 사용하면 수식에서 하나 또는 두 개의 변수를 가져와 원하는 만큼 다른 값으로 바꾼 다음 결과를 테이블에서 볼 수 있습니다. 이 옵션은 시나리오나 목표 탐색과 달리 여러 결과를 한 번에 표시하므로 특히 강력합니다. 아래 예에서는 자동차 대출에 대해 24가지 가능한 결과를 볼 수 있습니다.