[직장인팁]엑셀 목표값 찾기와 해 찾기(목표값 여러개 찾기)

엑셀은 자료를 정리하고 재구성하는데 엄청난 기능을 제공하는 직장인의 필수 프로그램입니다.

그중에서 이번에는 엑셀의 진주같은 기능인 '목표값 찾기'와 '해 찾기'를 알아보도록 하겠습니다.

목표값 찾기와 해 찾기는 특정 수식셀에 원하는 값을 만들기 위해서 그 수식에 인용되는 셀의 값을 변경하는 기능입니다.

목표값 찾기의 경우는 인용되는 셀의 갯수가 1개로 되어있으나, 엑셀에 추가된 '해 찾기'의 기능을 이용하면 여러 개의 셀의 값을 변경하여 원하는 목표값을 찾을 수 있습니다.

그럼 위 두 기능이 어떻게 활용되는지 예시를 통해 알아보겠습니다.

1. 목표값 찾기

보통의 경우, 여러 개의 값의 변경이 필요하고 변화는 셀에 다양한 경우의 조건을 달아줘야 되는 경우가 생기기 때문에 '해 찾기'를 많이 사용합니다. 그러나 '해 찾기'가 어떤 식으로 작동하는지 감을 잡고 활용하기 위해서 먼저 '목표값 찾기'를 해보겠습니다.

먼저 위와 같은 예시의 데이터가 있습니다.
E9셀은 판매가의 합계로 수식으로 E4:E8의 합을 구하게 입력했습니다.
어떤 사유인지는 모르겠으나, 이번 목표 판매가 합계가 100,000천원으로 설정되었고,
저희는 다른 것은 그대로 하고 노랗게 강조된 D8셀의 단가를 조정해서 목표 판매가를 달성할 계획입니다.

그럼 단가를 얼마로 하면 좋을까요? 하나하나 경우의 수를 대입해서 계산하는 방법도 있겠지만, 엑셀의 목표값 찾기 기능을 이용하면 쉽게 구할 수 있습니다.

엑셀 상단의 데이터탭을 보면 '가상분석' 하위에 '목표값 찾기'가 있습니다.
목표값 찾기를 클릭하면 아래와 같은 창이 열립니다.

수식 셀은 찾는 값이 적용될 셀을 의미합니다. 이 경우에는 판매가의 합계인 E9가 됩니다.
찾는 값은 우리의 목표인 목표 판매가인 100000가 되고, 값을 바꿀 셀은 노랗게 강조한 D8의 단가가 되겠습니다.

위에 말씀드린 것과 같이 칸을 채우고 '확인'을 클릭하면 아래와 같이 값을 바꿀 셀의 값이 변경된 것이 보입니다.

판매가의 합계인 E9셀도 수식에 따라 같이 목표 판매가인 100,000천원을 달성했습니다.

'목표값 찾기'는 여러 경우의 수를 자동으로 계산해서 자동으로 원하는 값을 찾아주는 아주 유용한 기능입니다. 다만 한개의 목표값에 제한된 점이 아주 아쉬운 기능이죠.

이런 아쉬움을 날려보내고 엄청 강력한 기능을 탑재하고 나타난게 바로 '해 찾기'입니다.

2. 해 찾기(여러 목표값 찾기)

해 찾기의 사용법과 기능을 위의 예시를 좀 다르게 접근해서 알아보겠습니다.

위에 사용한 예를 그대로 이용해서 이번에는 목표 판매가 합계 100,000천원을 만족하기 위해서 노랗게 강조된 수량을 조정해 보도록 하겠습니다.

먼저 '해 찾기'기능을 사용하기 위해서는 엑셀의 추가기능을 이용해야 됩니다.
메뉴의 빈공간을 왼쪽 클릭해서 아래와 같은 메뉴를 나오게 한 후, '빠른 실행 도구 모음 자용자 지정'을 클릭합니다.

그럼 아래와 같은 창이 나옵니다.

'추가 기능'탭에서 '해 찾기 추가 기능'을 선택한 후 아래 있는 '이동'버튼을 클릭합니다.

그럼 아래와 같이 창이 나오고 '해 찾기 추가 기능'체크박스를 체크하고 '확인'을 클릭해줍니다.

이제 '데이터'탭 메뉴바에 새로운 메뉴가 생긴 것이 눈에 들어옵니다.

위에 메뉴바를 보시면 데이터탭 왼쪽 끝에 분석의 '해 찾기'가 보입니다. '해 찾기'를 클릭합니다.

클릭과 동시에 '해 찾기 매개 변수'란 생소한 창이 나옵니다. 이제부터 '목표값 찾기'의 기억을 되살리며 빈 칸을 채워보겠습니다.

목표 설정은 목표값 찾기의 '수식 셀'과 같은 의미입니다. 인용되는 셀들이 변경되서 최종적으로 원하는 값으로 변경될 셀이죠. 여기서는 판매가 합계인 E9를 입력합니다.

대상은 최대값, 최소값, 지정값 3가지로 지정할 수 있습니다. 저희는 목표가 있으니 지정값으로 선택하고 100000을 입력합니다.

변수 셀 변경은 변경되서 인용될 셀입니다. 예시에서 노랗게 강조된 셀로 C4:C8이 되겠습니다.

다음부터 재미있는 기능이 들어값니다. 바로 '제한 조건에 종속'입니다.

예시에서 우리가 변경할 값은 수량으로 '정수'로 들어가야 됩니다. 그래서 조건을 추가해줘야 되는게 그 기능이 바로 '제한 조건에 종속'에 있습니다. '추가' 버튼을 누릅니다.

먼저 셀 참조에 우리가 변경할 수량(노란 셀)의 범위를 입력합니다. 그리고 조한 조건을 'int'로 설정하면 자동으로 '정수'하고 나옵니다. 아마 프로그래밍하신 분들은 보시면 '정수'구나 하고 아실 것 같습니다.

정수는 'int', 2진수는 'bin', AllDifferent는 'dif'입니다.

추가적으로 A-31품목은 2개 이상 팔아야된다고 하는 조건을 넣습니다.
그리고 전품목을 1개 이상, 재고제한으로 10개 이하로 판매해야 된다는 조건도 넣겠습니다.

실제는 상황에서는 더 다양한 조건이 들어갈 수도 있고, '변수 셀 변경'에도 연속된 셀이 아니라 다양한 범위(다양한 인수)로 입력이 가능합니다.
예를 들어 '변수 셀 변경'을 통해서 수량과 단가를 동시에 조절이 가능합니다.

'해 찾기'에서 하나 고려해야 될 것이 위의 예시와 같이 간단한 자료는 상관이 없으나 복잡하고 큰 자료에서 '해'를 찾을 때에는 '해법 선택'을 고려하여야 원하는 값에 가까운 값을 찾을 수 있습니다.

'해법 선택'에서는 3가지 계산방법을 제시해주고 있습니다.
비선형 GRG(일반 감소된 그라데이션): 완만한 비선형 문제에 사용
LP단면: 선형 문제에 사용
Evolutionary: 비곡선 문제에 사용
각 계산방법의 세부옵션은 '옵션'메뉴에서 설정이 가능합니다. 보통은 간단한 '해'를 찾을 경우는 'GRG 비선형'으로 선택하셔도 무난할 것 같습니다.

아무튼 해 찾기에 필요한 값을 모두 채운 후 '해 찾기(S)' 버튼을 클릭하면 아래와 같이 결과가 표시됩니다.
경우에 따라서 계산에 많은 시간이 소요될 수 있으며 엑셀 하단 상태표시줄에 진행상태가 표시됩니다.

위의 결과를 보면 수량을 변경해서 목표 판매가를 달성할 것을 볼 수 있습니다.

경우에 따라서는 해를 못 찾거나, 최근값을 찾아주는 경우도 있는데, 그럴 경우는 조건을 변경하거나 계산방법을 변경하면서 최적값을 찾아보는 것도 방법이라 생각됩니다.

'해 찾기 결과'에서 보고서를 선택하면 '해 찾기' 실행 결과에 대한 자세한 정보도 같이 표시됩니다.