문제의 공식화
당신이 일하는 회사에 XNUMX개의 창고가 있다고 가정해 보겠습니다. 여기서 상품이 모스크바 전역에 흩어져 있는 XNUMX개의 상점으로 이동합니다.
각 상점은 우리에게 알려진 특정 수량의 상품을 판매할 수 있습니다. 각 창고의 용량은 제한되어 있습니다. 작업은 총 운송 비용을 최소화하기 위해 상품을 배송할 창고를 합리적으로 선택하는 것입니다.
최적화를 시작하기 전에 상황을 설명하는 수학적 모델인 Excel 시트에 간단한 표를 컴파일해야 합니다.
다음과 같이 이해됩니다.
- 연한 노란색 표(C4:G6)는 각 창고에서 각 상점까지 하나의 품목을 운송하는 비용을 설명합니다.
- 보라색 셀(C15:G14)은 각 상점에서 판매하는 데 필요한 상품의 수량을 나타냅니다.
- 빨간색 셀(J10:J13)은 각 창고의 용량(창고가 보유할 수 있는 최대 상품 양)을 표시합니다.
- 노란색(C13:G13) 및 파란색(H10:H13) 셀은 각각 녹색 셀의 행 및 열 합계입니다.
- 총 운송 비용(J18)은 상품 수와 해당 운송 비용의 제품 합계로 계산됩니다. 계산을 위해 여기에서 함수를 사용합니다. SUMPRODUCT (합산).
따라서 우리의 작업은 최적의 녹색 세포 값을 선택하는 것으로 축소됩니다. 그리고 라인(파란색 셀)의 총 금액이 창고(빨간색 셀)의 용량을 초과하지 않도록 하고 동시에 각 상점이 판매해야 하는 수량(각 상점의 수량)을 받습니다. 노란색 셀은 요구 사항(보라색 셀)에 최대한 가까워야 합니다.
해법
수학에서 자원의 최적 분포를 선택하는 이러한 문제는 오랫동안 공식화되고 설명되었습니다. 그리고 물론, 그것들을 푸는 방법은 무딘 열거(매우 길다)가 아니라 아주 적은 수의 반복으로 오랫동안 개발되었습니다. Excel은 추가 기능을 사용하여 사용자에게 이러한 기능을 제공합니다. 솔루션 검색 (해결사) 탭에서 Data (데이트):
탭에 있는 경우 Data Excel에는 이러한 명령이 없습니다. 괜찮습니다. 이는 추가 기능이 아직 연결되지 않았음을 의미합니다. 활성화하려면 엽니다. 입양 부모로서의 귀하의 적합성을 결정하기 위해 미국 이민국에다음 선택 파라미터 - 기능 추가 - 소개 (옵션 - 추가 기능 - 이동). 열리는 창에서 필요한 줄 옆의 확인란을 선택하십시오. 솔루션 검색 (해결사).
추가 기능을 실행해 보겠습니다.
이 창에서 다음 매개변수를 설정해야 합니다.
- 타겟 기능 최적화 (세트 t돈 셀) – 여기에 최적화의 최종 주요 목표, 즉 총 운송 비용(J18)이 있는 분홍색 상자를 표시해야 합니다. 대상 셀은 최소화(예: 비용인 경우), 최대화(예: 이익인 경우) 또는 주어진 값(예: 할당된 예산에 정확히 맞추기)으로 가져오려고 할 수 있습니다.
- 변수 셀 변경하기 (By 변화 세포) – 여기에서 우리는 최소 배송 비용인 결과를 달성하고자 하는 값을 변경하여 녹색 셀(C10: G12)을 나타냅니다.
- 제한 사항과 일치 (제목 에 전에, 제약) – 최적화할 때 고려해야 하는 제한 사항 목록입니다. 목록에 제한 사항을 추가하려면 버튼을 클릭하십시오. 추가 (추가하다) 표시되는 창에 조건을 입력합니다. 우리의 경우 이것은 수요 제약이 될 것입니다.
창고의 최대 부피에 대한 제한:
물리적 요인(창고 및 운송 수단의 용량, 예산 및 시간 제약 등)과 관련된 명백한 제한 외에도 "Excel에 대한 특별" 제한을 추가해야 하는 경우가 있습니다. 예를 들어, Excel은 상점에서 창고로 물품을 다시 운송하도록 제안함으로써 배송 비용을 "최적화"하도록 쉽게 조정할 수 있습니다. 🙂
이러한 일이 발생하지 않도록 하려면 확인란을 활성화된 상태로 두는 것이 가장 좋습니다. 무제한 변수를 음이 아닌 것으로 만들기 또는 때때로 이러한 순간을 제한 목록에 명시적으로 등록합니다.
필요한 모든 매개변수를 설정한 후 창은 다음과 같아야 합니다.
해결 방법 선택 드롭다운 목록에서 다음 세 가지 옵션 중 하나를 선택하여 해결하기 위한 적절한 수학적 방법을 추가로 선택해야 합니다.
- 심플렉스 방법 선형 문제, 즉 출력이 입력에 선형적으로 의존하는 문제를 해결하기 위한 간단하고 빠른 방법입니다.
- 일반 다운그레이드 구배법(OGG) – 입력 데이터와 출력 데이터 사이에 복잡한 비선형 종속성이 있는 비선형 문제의 경우(예: 광고 비용에 대한 판매 종속성).
- 솔루션을 위한 진화적 탐색 – 생물학적 진화의 원리에 기반한 비교적 새로운 최적화 방법(hello Darwin). 이 방법은 처음 두 방법보다 몇 배 더 오래 작동하지만 거의 모든 문제(비선형, 이산)를 해결할 수 있습니다.
우리의 작업은 분명히 선형입니다. 1개 배달 – 40루블 소비, 2개 배달 – 80루블 소비. 등이므로 심플렉스 방법이 최선의 선택입니다.
이제 계산을 위한 데이터를 입력했으므로 버튼을 누릅니다. 해결책을 찾기 (해결하다)최적화를 시작합니다. 셀과 제약 조건이 많이 변경되는 심각한 경우 솔루션을 찾는 데 오랜 시간이 걸릴 수 있지만(특히 진화 방법의 경우) Excel 작업은 문제가 되지 않습니다. 몇 분 안에 다음 결과를 얻을 수 있습니다. :
우리 창고의 용량을 초과하지 않고 각 상점에 필요한 상품 수에 대한 모든 요청을 충족하면서 공급 볼륨이 상점간에 얼마나 흥미롭게 분배되었는지 주목하십시오.
찾은 솔루션이 적합하면 저장하거나 원래 값으로 롤백하고 다른 매개변수로 다시 시도할 수 있습니다. 선택한 매개변수 조합을 다음과 같이 저장할 수도 있습니다. 시나리오. 사용자의 요청에 따라 Excel은 세 가지 유형을 작성할 수 있습니다. 보고서 해결되는 문제에 대한 별도의 시트: 결과 보고서, 솔루션의 수학적 안정성에 대한 보고서 및 솔루션의 한계(제한 사항)에 대한 보고서 그러나 대부분의 경우 전문가에게만 관심이 있습니다. .
그러나 Excel에서 적절한 솔루션을 찾을 수 없는 상황이 있습니다. 이 예에서 창고의 총 용량보다 많은 양의 상점 요구 사항을 표시하면 이러한 경우를 시뮬레이션할 수 있습니다. 그런 다음 최적화를 수행할 때 Excel은 가능한 한 솔루션에 접근하려고 시도한 다음 솔루션을 찾을 수 없다는 메시지를 표시합니다. 그럼에도 불구하고 이 경우에도 유용한 정보가 많이 있습니다. 특히 비즈니스 프로세스의 "약한 링크"를 볼 수 있고 개선해야 할 부분을 이해할 수 있습니다.
물론 고려된 예는 비교적 간단하지만 훨씬 더 복잡한 문제를 해결하기 위해 쉽게 확장됩니다. 예를 들어:
- 재원 분배 최적화 프로젝트의 사업 계획 또는 예산의 지출 항목별로. 이 경우 제약사항은 자금조달 금액과 프로젝트 시기이며, 최적화의 목표는 이익을 극대화하고 프로젝트 비용을 최소화하는 것입니다.
- 직원 일정 최적화 기업의 임금 기금을 최소화하기 위해. 이 경우 제한은 고용 일정 및 직원 테이블의 요구 사항에 따라 각 직원의 희망 사항입니다.
- 투자 투자 최적화 – 이익을 극대화하거나 (더 중요한 경우) 위험을 최소화하기 위해 여러 은행, 증권 또는 기업 주식 간에 자금을 올바르게 분배해야 할 필요성.
어쨌든 추가 기능 솔루션 검색 (솔버) 매우 강력하고 아름다운 Excel 도구이며 현대 비즈니스에서 직면해야 하는 많은 어려운 상황에서 도움이 될 수 있으므로 주의를 기울일 가치가 있습니다.