VBA 산출 등 추가 내용
Solver(해찾기)는 엑셀에서 번들로 제공되며 비즈니스 및 엔지니어링 모델의 최적화 및 시뮬레이션에 사용되는 강력한 도구로서, 다른 입력 매개 변수와 제약조건을 사용하는 여러 모델을 자동으로 해결하기 위해 VBA와 함께 사용하면 더욱 강력해질 수 있습니다.
해찾기는 대상 셀을 찾는 방법과 제약조건에 따라 최적의 해를 찾을 수 있으며, 이를 매크로로 기록하면 다음과 같은 결과가 나옵니다.
| Sub SolverMacro1 () ‘ ‘SolverMacro1 매크로 ‘Jon Peltier가 기록한 매크로 ‘ SolverOk SetCell: = “$B$8”, MaxMinVal: = 1, ValueOf:= “0”, ByChange: = “$B$5:$B$6″, Engine:=1, EngineDesc:=”GRG Nonlinear” SolverAdd CellRef: = “$B$5:$B$6”, Relation: = 1, FormulaText: = “4” SolverOk SetCell: = “$B$8”, MaxMinVal: = 1, ValueOf:= “0”, ByChange: = “$B$5:$B$6″, Engine:=1, EngineDesc:=”GRG Nonlinear” SolverSolve End Sub |
○ SolverOK (첫번째 SovlerOK는 삭제 가능)
∙ SetCell:=”$B$8″ : 구하고자 하는 목표 셀
∙ MaxMinVal:=1 : 최대값은 1, 최소값은 2, 지정값은 3
∙ ValueOf:= “0” : 위의 MaxMinVal이 지정값(3)인 경우에 입력
∙ ByChange: = “$B$5:$B$6” : 변경하고자 셀을 의미
∙ Engine: = 1 : 옵션으로 해찾기하는 방법을 지정하는 걸로 Simplex LP method – 1, GRG Nonlinear method-2, Evolutionary method – 3
EngineDesc=:=”GRG Nonlinear” : 옵션으로 해찾기를 하는 대안으로 Simplex LP, GRG Nonlinear, Evolutionary 로 변경 가능
○ SolveAdd는 제약조건을 의미
∙ CellRef: = “$B$5:$B$6”
∙ Relation: = 1 : 작거나 같다 1, 같다 2, 크거나 같다 3, 정수(Int)형 4, 2진수(bin) 5
∙ FormulaText: = “4” : 제약조건의 값
○ SolverSolve
○ 기타
∙ 새로운 해찾기를 위해서는 SolverReset을 사용하여 실행전에 해찾기를 다시 설정 가능
※ 해찾기를 실행하기 위하여는 추가 기능을 설치해야 함
– 엑셀에서 엑셀의 “옵션”에서 “추가기능”, “이동”을 눌러 “해찾기 추가 기능”을 클릭
-VBA에서도 아래를 눌러 체크해야 함
VB Editor의 Object Viewer를 통해 Solver 라이브러리를 사용 가능
Application.Run을 이용하는 방법
| Sub 해찾기매크로() ‘SolverOk SetCell, MaxMinVal, ValueOf, ByChange ‘ ‘-setcell : 목표셀설정 ‘-maxminval : 최대값1, 최소값2, 지정값3 ‘-valueof : maxminval이 지정값일때 입력 ‘-bychange : 변경할셀….지정하는 부분 ‘ 제약조건 넣는 식 Application.Run “SolverAdd”, “$C$3”, 1, 50 ‘ SolverAdd CellRef:=”$C$3″, Relation:=1, FormulaText:=”50″ ‘ SolverAdd : 1-작거나같다,2-같다,3- 크거나같다, 4-int(정수형), 5-bin(2진수) Application.Run “SolverReset” Application.Run “SolverOk”, “$C$4”, 2, 0, “$C$3”, 1, “GRG Nonlinear” ‘ SetCell:=”$C$4″, MaxMinVal:=2, ValueOf:=0, ByChange:=”$C$3″, Engine:=1, EngineDesc:=”GRG Nonlinear” ‘ EngineDesc를 “Simplex LP” 등으로 변경 가능 Application.Run “SolverSolve”, True End Sub |
만약 분석결과를 알고 싶으면 다음의 코딩을 추가합니다.
이 방법은 리소스 참조 설정 문제를 방지하고 안정적으로 실행할 수 있으나 약간의 성능상의 불이익이 있을 수 있습니다.
| Sub RunSolver () ‘재설정 Application.Run “Solver.xlam!SolverReset” ‘새로운 분석을 시작 Application.Run “Solver.xlam!SolverOk”, “Blah1”, 1, “BlahBlah1” ‘제약 조건 추가 Application.Run “Solver.xlam!SolverAdd”, “Blah2”, 3, 0 Application.Run “Solver.xlam!SolverAdd”, “Blah3”, 2, “BlahBlah3” ‘분석을 실행 Result = Application.Run ( “Solver.xlam!SolverSolve”, True) ‘분석을 끝냄 Application.Run “Solver.xlam!SolverFinish” ‘분석 성공에 관한 보고서 Result <= 3 인 경우 ‘ Result = 0, 솔루션 발견, 최적 및 제약 조건 충족 ‘ Result = 1, 수렴, 제약 조건 만족 ‘ Result = 2, 개선 할 수 없다, 제약 조건 만족 ‘ Result = 3, 최대 반복으로 중지됨 MsgBox “해 찾기 솔루션 찾기”, vb 정보, “솔루션 찾기” 그밖에 ‘ Result = 4, 해 찾기가 수렴하지 않았습니다. ‘ Result = 5, 실현 가능한 해결책 없음 ‘ Result = 6, 해 찾기가 사용자의 요청에 따라 중단 ‘ Result = 7, 선형 모델 가정을 위한 조건 불충족 ‘ Result = 8, 문제가 너무 커서 Solver가 처리할 수 없음 ‘ Result = 9, 해 찾기에서 대상 또는 제약 조건 셀에 오류값 발생 ‘ Result = 10, 최대 시간 제안해 도달해서 중지 ‘ Result = 11, 문제를 해결하는데 사용할 수 있는 메모리 불충분 ‘ Result = 12, 다른 엑셀 인스턴스가 Solver.dll 사용중. 다시 시도 필요 ‘ Result = 13, 모델 오류. 모든 셀과 제약 조건이 유효한지 확인 필요 Beep MsgBox “해 찾기 솔루션을 찾을 수 없습니다.”, vbExclamation, “해결책 찾지 못했습니다” End if End Sub |