이 글의 목적은 엑셀에서 ‘Goal Seek(목표값 찾기)’ 기능이 수렴하지 않을 때의 원인과 재현 가능한 해결 절차를 전문가 수준으로 정리하여 현장에서 즉시 적용하도록 돕는 것이다.
1. Goal Seek 작동 원리 이해: 왜 수렴하지 않는가
Goal Seek은 단일 입력 셀을 조정하여 결과 셀의 값을 목표값으로 만드는 루트 찾기 절차를 수행한다. 내부적으로는 단순 반복 또는 수치적 접선·할선(유사 Secant) 기반의 갱신을 통해 오차를 줄여가며 해를 탐색한다. 수렴 실패는 대개 다음과 같은 조건에서 발생한다.
- 목표 함수가 구간에서 단조가 아니거나 극값·불연속점이 존재할 때이다.
- 입력 스케일이 지나치게 크거나 작아 수치 오차가 커질 때이다.
- 순환 참조나 휘발성 함수로 인해 계산이 불안정할 때이다.
- 초기 추정값이 비현실적이거나 목표가 수학적으로 불가능할 때이다.
- 반복 허용 오차·최대 반복 횟수 설정이 현재 문제에 맞지 않을 때이다.
주의 : Goal Seek은 단일 변수만 조정한다. 여러 입력을 동시에 맞추려면 Solver를 고려해야 한다.
2. 즉시 점검 체크리스트
| 항목 | 점검 포인트 | 판단 기준 | 조치 요약 |
|---|---|---|---|
| 수식 유효성 | 참조 범위, #DIV/0!, #VALUE!, #NUM! 여부 | 오류 미발생이어야 한다 | 오류 처리(IFERROR), 분모 하한 설정 |
| 단조성 | 입력 증가 시 결과 증가 또는 감소 일관성 | 구간 내 단조가 바람직하다 | 구간 분할, 변환(log 등)으로 단조 구간 확보 |
| 연속성 | IF, ROUND, INT, MAX/MIN 경계 존재 | 불연속/계단형이면 수렴 저하 | 스무딩, 근사식 사용, Solver 전환 |
| 스케일 | 입력/출력 크기·단위 | 1e-6~1e6 범위 권장 | 단위 변환, 정규화, 백분율 → 소수 변환 |
| 초기값 | 현재 입력 셀의 시작값 | 해에 근접할수록 유리 | 브래킷(상·하한) 시험 후 중앙값 사용 |
| 반복 설정 | 파일 > 옵션 > 수식 > 계산 | 허용 오차·최대 반복 기본값이 과도할 수 있다 | 허용 오차 축소, 반복 횟수 확대 |
| 순환 참조 | 상태 표시줄 경고 또는 수식 검사 | 순환 시 불안정 | 구조 재설계, 중간 계산 시트 분리 |
3. 표준 해결 절차(10분 완성)
- 오류 무효화를 위해 결과 셀에 방어식을 적용한다.
NA()는 차트에서 시각적 구분이 명확하고 수렴 실패 원인 파악에 유리하다.=IFERROR(원래식, NA()) - 단위·스케일 정규화를 수행한다. 예를 들어 % 입력은 0~1 사이 소수로 전환한다.
- 브래킷 설정을 위해 입력의 하한·상한 두 값을 준비하고 결과를 관찰한다. 부호가 바뀌면 해가 존재할 가능성이 높다.
- 초기 추정값을 상·하한의 중앙값으로 지정한 뒤 Goal Seek을 실행한다.
- 반복 계산 옵션을 조정한다. 허용 오차를 1E-6 수준으로 낮추고 최대 반복을 10배 이상 올린다.
- 결과가 진동·발산하면 스무딩 변환 또는 바이섹션 대체 절차를 적용한다.
주의 : INT, ROUND, CEILING/ FLOOR, CHOOSE, LOOKUP 경계처럼 결과가 계단형이면 접선 기반 탐색이 흔들린다. 이 경우 바이섹션 또는 Solver의 전역 탐색 옵션을 고려한다.
4. 수식 안정화 패턴
4.1 분모 안정화
=A1/(MAX(B1, 1E-9)) 작은 분모로 인한 급격한 변화와 #DIV/0!를 방지한다.
4.2 로그 변환
=LN(MAX(x, 1E-9)) 지수적 증가를 완만하게 만들어 탐색 안정성을 높인다.
4.3 부호 전환 체크
=SIGN(목표값 - 결과값) 브래킷 양 끝에서 부호가 다르면 단조 구간 내에 해가 있을 가능성이 높다.
5. Goal Seek 단계별 적용 예시
예시 시나리오
주어진 수요 예측식에서 가격 P를 조정해 이익 함수 Profit(P)을 0으로 만드는 P를 찾는다고 가정한다.
수요(Q) = a - b*P 총수익(TR) = P*Q 총비용(TC) = F + c*Q 이익(Profit) = TR - TC 시트 구성 예시이다.
| 셀 | 내용 | 수식 |
|---|---|---|
| B2 | a | 1000 |
| B3 | b | 2 |
| B4 | F | 5000 |
| B5 | c | 50 |
| B6 | P(입력) | 초기값 100 |
| B7 | Q | =MAX($B$2 - $B$3*$B$6, 0) |
| B8 | TR | =B6*B7 |
| B9 | TC | =B4 + $B$5*B7 |
| B10 | Profit | =B8-B9 |
Goal Seek 설정이다.
목표 셀: B10 목표값: 0 변경할 셀: B6 수렴 실패 시 조치이다.
- Q에 MAX를 사용하여 음수 수요를 0으로 절단했으므로 경계에서 비미분·불연속이 생긴다. 브래킷을 P=0~B2/B3 범위로 제한하고 중앙값을 초기값으로 지정한다.
- F, c, a, b가 커서 스케일이 커지면 P를 1~1000 구간으로 노멀라이즈하여 계산하고 마지막에 역변환한다.
6. 반복 설정 튜닝
파일 > 옵션 > 수식에서 다음을 조정한다.
- 반복 계산 사용 체크한다.
- 최대 반복 횟수: 기본 100을 1000~10000으로 확대한다.
- 최대 변경값(허용 오차): 0.001을 1E-6 수준으로 낮춘다.
주의 : 허용 오차를 무작정 낮추면 계산 시간이 증가한다. 목표 정밀도에 맞춰 합리적으로 선택한다.
7. Goal Seek가 원천적으로 어려운 케이스
- 계단형 요금, 구간별 세율, 조각별 함수 합성으로 결과가 평탄 구간을 갖는 경우이다.
- IF로 의사결정을 분기하는 재고·배치 모델처럼 미분불가 지점이 많을 때이다.
- 복수 변수 상호작용으로 단일 변수 조정만으로는 목표 달성이 불가능할 때이다.
이 경우에는 Solver(GRG 비선형, 단순법, 진화 알고리즘) 또는 커스텀 바이섹션·할선이 더 적합하다.
8. Solver 대체 전략 요약
| 전략 | 적합 문제 | 장점 | 주의점 |
|---|---|---|---|
| GRG 비선형 | 연속·미분 가능 근사 | 빠른 수렴 | 지역해 민감 |
| 진화 알고리즘 | 불연속·복수 극값 | 전역 탐색 | 시간 증가 |
| 단순법(Linear) | 선형 모델 | 해석적 최적성 | 선형성 전제 |
9. 단일 변수 대체 절차: 데이터 표를 이용한 브루트포스
입력 셀의 후보 값을 일정 간격으로 생성하고 데이터 표로 결과를 일괄 계산한 뒤 목표에 가장 근접한 값을 선택한다. 간격을 축소하며 2~3회 반복하면 실용 정밀도를 쉽게 얻는다.
- 열에 P 후보를 0, 10, 20, …로 채운다.
- 1열 위쪽 셀을 결과 셀 참조로 지정한다.
- 데이터 > 가상 분석 > 데이터 표에서 열 입력 셀을 P로 지정한다.
- ABS(결과-목표) 최소 행을 선택한다.
주의 : 간격이 너무 크면 최적값을 건너뛸 수 있다. 1차 탐색 후 간격을 줄여 재탐색한다.
10. VBA로 견고한 바이섹션 구현
불연속·비단조 구간을 피하고 부호가 바뀌는 구간에서만 수렴하도록 하는 바이섹션 예시이다.
Option Explicit
Function ProfitAtP(ByVal P As Double, ByVal a As Double, _
ByVal b As Double, ByVal F As Double, _
ByVal c As Double) As Double
Dim Q As Double
Q = Application.Max(a - b * P, 0)
ProfitAtP = P * Q - (F + c * Q)
End Function
Function BisectionPrice(ByVal a As Double, ByVal bcoef As Double, _
ByVal F As Double, ByVal c As Double, _
ByVal target As Double, _
ByVal lo As Double, ByVal hi As Double, _
Optional ByVal tol As Double = 0.000001, _
Optional ByVal maxIter As Long = 1000) As Variant
Dim fLo As Double, fHi As Double, mid As Double, fMid As Double
Dim i As Long
fLo = ProfitAtP(lo, a, bcoef, F, c) - target
fHi = ProfitAtP(hi, a, bcoef, F, c) - target
If fLo = 0 Then BisectionPrice = lo: Exit Function
If fHi = 0 Then BisectionPrice = hi: Exit Function
If fLo * fHi > 0 Then
BisectionPrice = CVErr(xlErrValue) ' 브래킷 불량
Exit Function
End If
For i = 1 To maxIter
mid = (lo + hi) / 2
fMid = ProfitAtP(mid, a, bcoef, F, c) - target
If Abs(fMid) <= tol Then
BisectionPrice = mid
Exit Function
End If
If fLo * fMid < 0 Then
hi = mid: fHi = fMid
Else
lo = mid: fLo = fMid
End If
Next i
BisectionPrice = mid ' 최대 반복 도달 시 근사치
End Function
시트에서 다음과 같이 사용한다.
=BisectionPrice(B2, B3, B4, B5, 0, 0, 1000) 주의 : 바이섹션은 단조 구간에서만 작동한다. 부호가 바뀌는 브래킷(lo, hi) 검증이 필수이다.
11. 흔한 실패 시그널과 원인 매핑
| 시그널 | 관찰 증상 | 가능 원인 | 대응 |
|---|---|---|---|
| 진동 | 입력 값이 두 점 사이에서 번갈아 변함 | 계단형 수식, 과도한 민감도 | 스무딩, 허용 오차 완화, 바이섹션 |
| 발산 | 입력이 구간 끝으로 치우침 | 초기값 불량, 잘못된 단위 | 초기값 재설정, 단위 점검 |
| 정지 | 변화 없고 반복만 증가 | 평탄 구간, 미세 변화 절단 | 스케일 조정, 허용 오차 축소 |
| 즉시 실패 | 몇 회 내 종료 | 목표 불가능, 오류 값 | 목표 재설계, 오류 처리 |
12. 모델링 습관 체크리스트
- 소수점 반올림은 최종 보고 단계에서만 수행한다.
- 비율은 항상 0~1 소수로 처리한다.
- 분모 하한, 로그·루트 도메인 하한을 부여한다.
- 중간 계산은 별도 범위로 분리하여 가독성을 높인다.
- 중요 파라미터에 데이터 유효성 제한(하한/상한)을 건다.
13. 고급: 할선법(두 점 근사)로 수렴 가속
두 개의 입력-결과 샘플로 기울기를 근사해 다음 추정치를 갱신하는 방식이다. Goal Seek의 내부 동작과 유사하지만, 사용자가 브래킷을 관리하므로 실패 복원력이 높다.
Function SecantRoot(ByVal target As Double, ByVal x0 As Double, ByVal x1 As Double, _ Optional ByVal tol As Double = 1E-6, _ Optional ByVal maxIter As Long = 200) As Variant Dim y0 As Double, y1 As Double, x2 As Double, i As Long y0 = f(x0) - target y1 = f(x1) - target For i = 1 To maxIter If Abs(y1 - y0) < 1E-15 Then Exit For x2 = x1 - y1 * (x1 - x0) / (y1 - y0) x0 = x1: y0 = y1 x1 = x2: y1 = f(x1) - target If Abs(y1) <= tol Then SecantRoot = x1: Exit Function Next i SecantRoot = CVErr(xlErrNA) End Function 여기서 f(x)는 문제별 결과 함수로 정의한다. 계단형에서는 바이섹션보다 불안정할 수 있다.
14. 사례별 처방전
케이스 A: 할인율 찾기(IRR 유사)
현금흐름이 부호 전환을 여러 번 하면 해가 여럿 또는 존재하지 않을 수 있다. 브래킷을 바꿔 여러 해를 탐색하거나 XIRR과 유사한 Solver 접근이 필요하다.
케이스 B: 생산 배치 with 최소 주문 수량(MOQ)
MOQ로 인해 결과가 계단형이다. 목표 재정의(총이익 최대화) 또는 바이섹션+정수 보정이 필요하다.
케이스 C: 재고 안전재고 수준에서 서비스 수준 매칭
표준정규 역함수(NORMSINV) 기반이면 연속이 보장되므로 스케일만 맞추면 Goal Seek이 잘 수렴한다. 단, 수요 분포 가정의 타당성을 먼저 확인해야 한다.
15. 디버깅 루틴(현장 점검용)
- 결과 셀 단독 재계산 후 값 기록한다.
- 입력에 ±1% 변화를 주고 결과 변화 부호를 기록한다.
- 부호가 같으면 단조 구간으로 가정하고 범위를 확장한다. 다르면 범위를 축소한다.
- 오류 마스크(IFERROR) 제거 후 원시 오류를 확인한다.
- 휘발성 함수(OFFSET, INDIRECT, RAND 등) 사용 여부를 점검한다.
16. 실무용 템플릿 수식
' 오차 = 목표값 - 결과값
' 오차 절대값
= ABS(목표값 - 결과값)
' 브래킷 검사
= SIGN(오차@하한) * SIGN(오차@상한)
' 스케일 정규화 예: 입력을 0~1로
= (x - xmin) / MAX(xmax - xmin, 1E-9)
17. 보고서 정밀도 세팅
- Goal Seek 성공 후 표시 형식을 별도로 반올림한다.
- 계산 내부에서는 반올림을 적용하지 않는다.
- 문서화 시 허용 오차, 반복 횟수, 초기값을 표에 기록한다.
18. 체크리스트 요약(프린트용)
- 오류 값 제거(IFERROR, 하한값 도입) 한다.
- 단위와 스케일을 정규화한다.
- 브래킷으로 해 존재 구간을 확보한다.
- 초기값은 중앙값으로 설정한다.
- 허용 오차와 반복 횟수를 문제 난이도에 맞춘다.
- 계단형이면 바이섹션 또는 Solver로 전환한다.
- 결과는 최종 단계에서만 반올림한다.
FAQ
Goal Seek이 “해를 찾지 못했습니다”라고 종료할 때 최우선 조치는 무엇인가?
브래킷을 확보하는 것이다. 입력 하한·상한 두 점에서 오차 부호가 다르게 나오도록 구간을 찾은 뒤 중앙값을 초기값으로 사용하면 재시도 성공 확률이 급상승한다.
INT/ROUND가 필수인 모델에서 수렴성을 높이는 요령은 무엇인가?
계산 내부는 소수로 유지하고 출력 단계에서만 반올림한다. 불가피할 경우 바이섹션으로 대체하고 간격을 충분히 촘촘히 한다.
반복 설정은 어디서 바꾸나?
파일 > 옵션 > 수식에서 반복 계산을 활성화하고 최대 반복·최대 변경값을 조정한다. 모델별로 적정값을 기록해 재현성을 확보한다.
Solver와 Goal Seek 중 무엇을 우선 써야 하나?
단일 변수, 연속·단조 문제는 Goal Seek이 빠르고 간단하다. 불연속, 다변수, 제약조건이 있는 문제는 Solver가 적합하다.
브루트포스 데이터 표 방식의 장단점은?
장점은 직관성과 견고성이다. 단점은 계산량 증가이다. 간격 축소를 단계적으로 적용하면 충분히 실용적이다.