Excel에서 목표 찾기를 사용하는 방법

Goal Seek는 Excel의 What-if Analysis 도구 중 하나로서 원하는 결과를 얻기 위해 수식의 올바른 입력 값을 찾는 데 도움이 됩니다. 수식의 한 값이 다른 값에 미치는 영향을 보여줍니다. 즉, 달성하고자 하는 목표 값이 있는 경우 목표 탐색을 사용하여 이를 얻기 위한 올바른 입력 값을 찾을 수 있습니다.

예를 들어, 당신이 한 과목에서 총 75점을 득점했고 그 과목에서 S등급을 받으려면 적어도 90점이 필요하다고 가정해 봅시다. 고맙게도 평균 점수를 높이는 데 도움이 될 수 있는 마지막 테스트가 하나 있습니다. Goal Seek를 사용하여 S 등급을 받기 위해 최종 시험에서 얼마나 많은 점수가 필요한지 알아낼 수 있습니다.

Goal Seek는 시행착오 방법을 사용하여 올바른 결과에 도달할 때까지 추측을 입력하여 문제를 역추적합니다. Goal Seek는 수동으로 계산하는 데 오랜 시간이 걸렸을 수 있는 수식에 대한 최상의 입력 값을 몇 초 만에 찾을 수 있습니다. 이 기사에서는 몇 가지 예와 함께 Excel에서 목표 찾기 도구를 사용하는 방법을 보여줍니다.

목표 탐색 기능의 구성 요소

목표 찾기 기능은 다음과 같은 세 가지 매개변수로 구성됩니다.

셀 설정 – 수식이 입력되는 셀입니다. 원하는 출력을 원하는 셀을 지정합니다.
가치에 – 목표 탐색 작업의 결과로 원하는 Target / Desired Value 입니다.
셀을 변경하여 – 원하는 출력을 얻기 위해 값을 조정해야 하는 셀을 지정합니다.

Excel에서 목표 찾기를 사용하는 방법: 예 1

간단한 예에서 작동 방식을 보여주기 위해 과일 포장 마차를 운영한다고 상상해보십시오. 아래 스크린샷에서 셀 B11(아래)은 포장 마차를 위해 과일을 구입하는 데 드는 비용을 표시하고 셀 B12는 해당 과일 판매에 대한 총 수익을 보여줍니다. 그리고 B13 셀에는 수익의 백분율(20%)이 표시됩니다.

이익을 '30%'로 늘리고 싶지만 투자를 늘릴 수 없어 수익을 올려야 이익을 얻을 수 있습니다. 하지만 얼마까지? 목표 찾기는 당신이 그것을 찾는 데 도움이 될 것입니다.

B13 셀에서 다음 공식을 사용하여 이익 비율을 계산합니다.

=(B12-B11)/B12

이제 이익률이 30%가 되도록 이익률을 계산하려고 합니다. Excel의 목표 찾기를 사용하여 이 작업을 수행할 수 있습니다.

가장 먼저 할 일은 값을 조정할 셀을 선택하는 것입니다. 목표 찾기를 사용할 때마다 수식이나 함수가 포함된 셀을 선택해야 합니다. 이 경우 B13 셀에 백분율을 계산하는 공식이 포함되어 있으므로 선택하겠습니다.

그런 다음 '데이터' 탭으로 이동하여 예측 그룹에서 '가정 분석' 버튼을 클릭하고 '목표 찾기'를 선택합니다.

3개의 필드가 있는 목표 찾기 대화 상자가 나타납니다.

셀 설정 – 수식(B13)이 포함된 셀 참조를 입력합니다. 이것은 원하는 출력을 가질 셀입니다.

가치에 – 달성하려는 원하는 결과를 입력하십시오(30%).

셀을 변경하여 – 원하는 결과에 도달하기 위해 변경하려는 입력 값(B12)에 대한 셀 참조를 삽입합니다. 셀을 클릭하거나 셀 참조를 수동으로 입력하기만 하면 됩니다. 셀을 선택하면 Excel에서 열 문자와 행 번호 앞에 '$' 기호를 추가하여 절대 셀로 만듭니다.

완료되면 '확인'을 클릭하여 테스트합니다.

그러면 '목표 찾기 상태' 대화 상자가 나타나 아래와 같이 해결 방법을 찾았는지 알려줍니다. 솔루션이 발견되면 '변경 셀(B12)'의 입력 값이 새로운 값으로 조정됩니다. 이것이 우리가 원하는 것입니다. 따라서 이 예에서 분석 결과 30% 수익 목표에 도달하려면 $1635.5(B12)의 수익을 달성해야 한다고 결정했습니다.

'확인'을 클릭하면 Excel이 셀 값을 변경하거나 '취소'를 클릭하여 솔루션을 취소하고 원래 값으로 복원합니다.

B12 셀의 입력 값(1635.5)은 목표(30%)를 달성하기 위해 Goal Seek를 사용하여 찾은 값입니다.

목표 찾기를 사용할 때 기억해야 할 사항

셀 설정에는 항상 '셀을 변경하여' 셀에 종속된 수식이 포함되어야 합니다.
한 번에 단일 셀 입력 값에만 목표 찾기를 사용할 수 있습니다.
'셀 변경'에는 수식이 아닌 값이 포함되어야 합니다.
Goal Seek가 올바른 솔루션을 찾지 못하면 생성할 수 있는 가장 가까운 값을 표시하고 'Goal-Seeking이 솔루션을 찾지 못했을 수 있습니다'라는 메시지를 알려줍니다.

Excel 목표 찾기가 작동하지 않을 때 수행할 작업

그러나 해결책이 있다고 확신하는 경우 이 문제를 해결하기 위해 시도할 수 있는 몇 가지 방법이 있습니다.

Goal Seek 매개변수 및 값 확인

확인해야 할 두 가지 사항이 있습니다. 먼저 '셀 설정' 매개변수가 수식 셀을 참조하는지 확인합니다. 둘째, 수식 셀(셀 설정)이 변경되는 셀에 직간접적으로 의존하는지 확인하십시오.

반복 설정 조정

Excel 설정에서 Excel이 올바른 솔루션과 정확성을 찾기 위해 시도할 수 있는 횟수를 변경할 수 있습니다.

반복 계산 설정을 변경하려면 탭 목록에서 '파일'을 클릭합니다. 그런 다음 하단의 '옵션'을 클릭하십시오.

Excel 옵션 창의 왼쪽 창에서 '수식'을 클릭합니다.

'계산 옵션' 섹션에서 다음 설정을 변경합니다.

최대 반복 - Excel에서 계산할 수 있는 솔루션의 수를 나타냅니다. 숫자가 높을수록 더 많은 반복을 수행할 수 있습니다. 예를 들어 '최대 반복 횟수'를 150으로 설정하면 Excel은 150개의 가능한 솔루션을 테스트합니다.
최대 변경 - 결과의 정확도를 나타냅니다. 숫자가 작을수록 정확도가 높아집니다. 예를 들어 입력 셀 값이 '0'이지만 Goal Seek가 '0.001'에서 계산을 멈춘 경우 이를 '0.0001'로 변경하면 문제가 해결됩니다.

Excel에서 더 많은 가능한 솔루션을 테스트하려면 '최대 반복' 값을 늘리고 더 정확한 결과를 원하면 '최대 변경' 값을 줄이십시오.

아래 스크린샷은 기본값을 보여줍니다.

순환 참조 없음

수식이 자체 셀을 다시 참조하는 경우 이를 순환 참조라고 합니다. Excel 목표 찾기가 제대로 작동하도록 하려면 수식에서 순환 참조를 사용하면 안 됩니다.

Excel 목표 찾기 예 2

누군가로부터 '$25000'의 돈을 빌리고 있다고 가정해 보겠습니다. 그들은 당신에게 20개월 동안 월 7%의 이자로 돈을 빌려주므로 월 '$1327'의 상환액이 됩니다. 그러나 7%의 이자로 20개월 동안 매달 '$1,000'만 지불할 여유가 있습니다. Goal Seek는 '$1000'의 월별 상환액(EMI)을 생성하는 대출 금액을 찾는 데 도움이 될 수 있습니다.

PMT 계산에 필요한 세 가지 입력 변수(이자율 7%, 기간 20개월, 원금 $25,000)를 입력합니다.