Excel 솔버를 자동화하는 방법

Excel 솔버를 최대한 활용하는 방법에서 모델을 향상하고 분석을 강화하는 기술을 다루었습니다. 제가 가장 많이 받은 질문 중 하나는 여러 값을 풀기 위해 솔버를 자동화하는 것입니다.

이전 기사에 간단한 설명이 있었지만 처음부터 끝까지 구현하는 방법에 대한 내용은 없었습니다. 이 기사에서는 개념과 방법을 단계별로 처음부터 끝까지 깊이 있게 다룰 것입니다. 결국에는 자신의 개인 모델을 자동화하고 분석을 확장할 수 있습니다.

어떻게 할 것인가

Excel의 매크로 레코더를 활용하여 실제 코드의 대부분을 처리합니다. 기록 매크로 도구를 사용하면 Excel에서 수동 단계를 VBA 코드로 변환할 수 있습니다.

그런 다음 변경할 핵심 코드를 식별합니다. 그런 다음 이러한 값을 동적으로 선택 및 교체하고 솔버를 다시 실행하는 루프를 생성합니다.

이것은 반기술적이지만 이 스타일의 개발을 통해 코드 작성 방법에 집중하는 대신 프로세스와 개념에 집중할 수 있습니다.

매크로 레코더 설정

매크로 기록 기능은 리본의 개발자 탭에 있습니다. 이것은 기본적으로 사용할 수 없지만 구현하기 쉽습니다. 리본 옵션으로 이동하면 개발자 탭에 대한 확인란이 표시됩니다.

아래 스크린샷은 Mac용이지만 PC 기반 Excel도 비슷합니다.

솔버 추가 기능을 설치하지 않은 경우 추가 기능 메뉴를 통해 설치할 수 있습니다. Microsoft 지원 사이트에는 모든 플랫폼에 대한 지침인 Excel에서 해 찾기 추가 기능 로드가 있습니다.

매크로 기록

이제 설정이 완료되었으므로 녹음할 준비가 되었습니다! 모든 공식과 참조를 사용하여 작업 공간을 적절하게 설정하십시오. 솔버 모델을 구축하기 전에 모든 것을 설정하고 싶을 것입니다.

리본의 개발자 탭으로 돌아갑니다. 빨간 점이 있는 종이가 보일 것입니다. 해당 버튼을 클릭하여 레코더를 엽니다.

매크로에 이름을 지정하고 원하는 경우 설명을 입력합니다.

확인을 누르면 레코더가 시작됩니다.

솔버 설정 단계를 거쳐 해결하세요. 이 작업이 완료되면 개발자 탭으로 돌아가서 기록을 중지합니다. 녹음을 시작할 때 사용한 버튼과 같은 위치에 있을 것입니다.

이제 기본 코드가 있으므로 정리하고 다음 단계를 위해 준비할 수 있습니다.

코드 정리

이제 코드를 편집하고 불필요한 부분을 제거하고 매크로 기록의 필수 부분만 사용하도록 할 수 있습니다.

Excel의 VBA 편집기를 통해 코드를 편집할 수 있습니다. 기록된 코드에 액세스하려면 매크로 버튼을 클릭하십시오.

새 메뉴에서 매크로로 이동하고 편집을 클릭합니다.

이제 코드 편집기를 열었으므로 미세 조정을 시작할 수 있습니다.

불필요한 코드 제거

녹음하는 동안 다른 많은 항목을 클릭하게 된 경우 지금 지우고 아래와 유사한 항목을 남길 수 있습니다. 추가 코드가 많고 깨질까 걱정된다면 언제든지 다시 녹음할 수 있습니다.

솔버 참조 설정

계속 진행하기 전에 마지막 단계로 도구 > 참조로 이동하여 솔버를 선택합니다. 이렇게 하지 않으면 패키지가 매크로에 로드되지 않고 오류가 발생합니다.

이제 매개변수를 변경하고 매크로 메뉴를 열고 매크로를 선택하고 실행을 누르면 결과가 업데이트됩니다.

솔버 모델에 대해 코드를 정렬해 보겠습니다. 어떻게 정렬되는지 볼 수 있습니다. 언어는 다르지만 모든 의미를 결합할 수 있습니다.

다음으로 변경해야 할 부분을 분리하고 자동화를 마무리합니다.

헹구고 반복 - 루프 만들기

이제 작동하는 솔버가 있습니다. 우리는 그것을 여러 번 반복해야 합니다. 함께 예를 들어 계획을 세워봅시다.

우리는 첫 번째 모델에서 $1,000의 예산으로 시작했지만 $100씩 증가하여 $2,500까지 동일한 작업을 수행하려고 합니다.

뛰어들자! 열 B에는 값 목록이 있고 열 C에는 출력이 있습니다. 프로세스를 작성하면 다음과 같습니다.

1. 총 지출을 B10의 값으로 설정합니다.
2. 모델을 풉니다.
3. 출력을 C10에 배치합니다.
4. 다음 B 값을 선택합니다.
5. C 행에 출력을 설정합니다.
6. B의 모든 값에 대해 2-5단계를 반복합니다.

루프 시작

루프는 논리적 끝점에 도달할 때까지 작업을 반복하는 코딩 구성입니다. 루프는 값의 끝에 도달할 때까지 모든 값에 대해 솔버를 실행하여 동일한 프로세스를 반복하는 데 유용합니다.

이 예에서는 행 수로 끝점을 정의합니다. B10:B25에는 16개의 값이 있습니다.

루프를 시작하고 솔버 코드를 둘러싸겠습니다. 루프 번호(i)를 유지하고 각 값을 살펴보고 매번 루프를 해결하도록 변수를 설정합니다.

솔버 재설정

코드 시작 부분에 또 다른 조건을 추가합니다. 매 반복마다 솔버를 다시 실행하고 싶기 때문에 루프마다 재설정합니다. 이렇게 하면 설정이 지워지고 새 모델이 시작됩니다.

SolverReset을 통해 이 작업을 수행할 수 있습니다.

셀 참조 업데이트

이제 16번 풀지만 셀 참조를 업데이트해야 합니다. 원래 B10을 참조했지만 제한을 업데이트하려면 실행할 때마다 한 셀 아래로 이동해야 합니다. 오프셋 기능을 통해 이를 수행할 수 있습니다. "$B$10"을 업데이트하여 셀을 참조한 다음 i 행만큼 오프셋할 수 있습니다(첫 번째 실행에서는 0, 두 번째 실행에서는 1 행 등). 루프가 실행될 때마다 i는 1씩 증가합니다.

출력 수집

출력은 매번 변경되므로 저장하려고 합니다. 위의 동일한 프로세스를 따를 수 있지만 C10에서 오프셋하고 해결된 모델의 값과 동일하게 설정할 수 있습니다. 그런 다음 루프가 다시 시작됩니다.

C10을 호출하여 적절한 행 수만큼 오프셋한 다음 출력 값을 붙여넣습니다.

솔버 알림 피하기

잠시라도 본보기로 배우지 말고 머리를 아껴두도록 합시다. 솔버를 실행했을 때 해당 메뉴가 솔루션을 수락할 것인지 묻는 팝업을 기억하십니까? 비활성화하지 않으면 매번 나타납니다. 100개의 값으로 루프를 설정하는 경우 매번 수락을 클릭해야 한다고 상상해 보십시오!

계속해서 솔버 코드 뒤에 다음 줄을 추가하고 SolverSolve에 (TRUE)를 추가하십시오. 이것은 변경 사항을 수락하는 것과 유사합니다.

이제 완성된 코드가 있습니다!

가동 중

이제 매크로 목록에서 코드를 선택하고 실행하여 코드를 실행할 수 있습니다. 통합 문서를 템플릿으로 사용하려는 경우 매크로를 단추에 할당하여 사용하기 쉽도록 할 수 있습니다. 개발자 탭에서 찾을 수 있습니다.

비교 그래프를 추가할 수도 있습니다. 이 경우 지출 증가율과 전환수 증가율을 비교하려고 합니다. 이 예는 흥미롭지 않지만 실제 데이터는 효율성 중단점을 밝히는 데 도움이 될 수 있습니다.

결론

우리는 꽤 많이 덮었습니다! 자동화된 솔버를 시작하고 실행한 것을 축하합니다. 우리가 다룬 예제는 지나치게 단순했지만 동일한 개념을 취하여 모든 솔버 모델에 적용할 수 있습니다.

이를 통해 여러 번 해결해야 하는 모델에 대한 엄청난 시간을 절약하고 사용자가 다양한 시나리오를 실험하도록 권장할 수 있습니다.

원래 게시 날짜: 2019년 7월 30일