엑셀을 편하게] 해찾기(Solver)를 VBA에서 쓰기

엑셀 고급기능 중 해찾기(Solver)가 있습니다. 다수 조건식에 포함된 다수 미지수를 찾을 수 있는 수치해석 방법입니다. 숫자를 다루시는 많은 분들이 쓰고 계실텐데요. 이 해찾기가 VBA에서 된다는 사실을 이외로 모르는 분들이 있습니다. 그래서 이번 포스팅에서 엑셀 해찾기를 VBA에서 쓰는 방법을 소개드리고자 합니다.

 

엑셀 예를 보겠습니다. a, b, c가 미지수인 일차연립방정식입니다. 회색 박스[D2~F4]에 미지수 앞의 상수를 넣었습니다. 주황색 박스[G2~G4]에는 값을 아직 모르는 미지수 a,b,c에 1을 대입했습니다. 파란색 박스[H2~H4]는 답을 차례로 넣었습니다. 그리고 [I열]에는 회색박스와 주황색 박스가 계산되는 조건식을 넣습니다.

 

다음으로 매크로를 기록입니다. [보기]-[매크로 기록]으로 들어가서 매크로 이름은 아무거나 적고 [확인]합니다. 이제 실행하는 작업들이 VBA 코드로 기록이 됩니다.

 

[데이터]-[해찾기]로 들어가면 해 찾기 창이 나옵니다.

 

 

변수 셀 변경(미지수)에 주황색 박스 [G2~G4]를 선택, [추가] 버튼으로 [H2~H3]과 [I2~I4]가 각각 같다는 조건식을 넣습니다. 그 후 [해찾기]를 눌러줍니다.

 

해 찾기 결과로 최적화 조건을 만족했다며 값을 찾았습니다. 1, 2, 3이 미지수 였습니다.

 

이제 매크로를 중단하겠습니다. [보기]-[매크로]-[기록중지]를 합니다.

 

매크로가 어떻게 기록되었는지 VBA 로 들어가 보겠습니다. [Alt+F11]를 누르면 VBA 에디터로 들어오게 됩니다. 중복되는 기록들이 몇개가 보입니다. 빨간색 취소선이 중복되는 코드가 기록된 것입니다. 파란색 박스만 남기면 됩니다.

 

필요 코드만 남기고 맨 위에 "SolverReset" 이라고 추가해 줍니다. 해찾기를 초기화 시키는 명령어입니다.

 

제대로 되는지 확인해 보아야합니다. 실행버튼(세모)을 누르거나 [F5]를 눌러주면 실행이 될텐데... 만약에 VBA에 Solver기능이 추가가 안 되어 있으면 아래와 같은 메세지가 나옵니다. 당황하지 말고, 다음과 같이 해주시면 됩니다.

 

 

[도구]-[참조]에서 Solver에 체크만 해주면 됩니다. 다시 실행시키면 해찾기가 제대로 수행되는 것을 볼 수 있습니다..

 

이제는 해찾기 VBA 코드로 응용해 보겠습니다. 한 쉬트 내에 여러개 해 찾기를 해 보겠습니다. 예를 들기 위해 연립방정식을 하나 추가했습니다. 

 

VBA 에디터 [Alt+F11] 로 들어가서 위의 코드를 아래에 복사를 하고, 조건과 미지수 셀을 변경해 주면 됩니다. 제가 제시한 예에서는 2,3,4 가 6,7,8 로 바뀌면 됩니다.

 

이제 실행 [F5]하면 해찾기 결과가 두 번 나오면서 미지수를 각각 찾아주게 됩니다. <<해 찾기 결과가 나올 때 '확인-enter'을 해주어야 다음 해찾기가 실행됨>>

 

위의 예로 작성한 VBA가 포함된 엑셀 파일입니다. 활용해 보시기 바랍니다.

solver_VBA_up.xlsm

0.02MB

아 그리고..  공감, 댓글은 블로거에게 큰 힘이 되는 것 아시죠?