중요: Office 2016 및 Office 2019에 대한 지원은 2025년 10월 14일에 종료됩니다. 어디서나 아무 디바이스에서든 작업하고 계속 지원을 받으려면 Microsoft 365로 업그레이드하세요. Microsoft 365 받기
이 문서에서는 가상 분석에 사용할 수 있는 Microsoft Excel 추가 기능 프로그램인 Solver를 사용하여 최적의 제품 조합을 결정하는 방법을 설명합니다.
수익성을 최대화하는 월별 제품 조합을 확인하려면 어떻게 해야 하나요?
회사에서는 매월 생산할 각 제품의 수량을 결정해야 하는 경우가 많습니다. 가장 간단한 형태로 , 제품 혼합 문제는 이익을 극대화하기 위해 한 달 동안 생산되어야하는 각 제품의 양을 결정하는 방법을 포함한다. 제품 조합은 일반적으로 다음 제약 조건을 준수해야 합니다.
-
제품 조합은 사용 가능한 것보다 더 많은 리소스를 사용할 수 없습니다.
-
각 제품에 대한 수요가 제한됩니다. 과잉 생산이 낭비 (예를 들어, 멸망 약물)때문에 우리는 수요가 지시하는 것보다 한 달 동안 제품의 더 많은 것을 생산 할 수 없습니다.
이제 제품 혼합 문제의 다음 예제를 해결해 보겠습니다. 그림 27-1에 표시된 파일 Prodmix.xlsx 이 문제에 대한 해결 방법을 찾을 수 있습니다.
공장에서 6개의 다른 제품을 생산하는 제약 회사에서 일한다고 가정해 봅시다. 각 제품의 생산에는 인건비와 원료가 필요합니다. 그림 27-1의 행 4는 각 제품의 파운드를 생산하는 데 필요한 노동 시간을 보여주고, 행 5는 각 제품의 파운드를 생산하는 데 필요한 원료의 파운드를 보여줍니다. 예를 들어 제품 1 파운드를 생산하려면 6시간의 노동과 3.2파운드의 원자재가 필요합니다. 각 약물에 대해 파운드당 가격은 6행으로 지정되고 파운드당 단가는 7행으로 지정되며 파운드당 이익 기여도는 9행으로 제공됩니다. 예를 들어, 제품 2에 대 한 판매 $11.00 파운드 당, 단위 비용 발생 $5.70 파운드 당, 그리고 기여 $5.30 파운드 당 이익. 각 약물에 대한 월의 수요는 행에 주어진다 8. 예를 들어 제품 3에 대한 수요는 1041파운드입니다. 이번 달에는 4,500시간의 노동과 1600파운드의 원자재를 사용할 수 있습니다. 이 회사는 어떻게 월별 수익을 최대화할 수 있습니까?
Excel 해 찾기에 대해 전혀 알지 못하면 워크시트를 생성하여 제품 조합과 관련된 수익 및 리소스 사용량을 추적하여 이 문제를 공격할 수 있습니다. 그런 다음 우리는 시행 착오를 사용하여 사용 가능한 것보다 더 많은 노동이나 원료를 사용하지 않고 수요를 초과하는 약물을 생산하지 않고 이익을 최적화하기 위해 제품 믹스를 변경합니다. 이 프로세스에서는 시행착오 단계에서만 해 찾기를 사용합니다. 기본적으로 해 찾기는 시행착오 검색을 완벽하게 수행하는 최적화 엔진입니다.
제품 혼합 문제를 해결하는 핵심은 지정된 제품 조합과 관련된 리소스 사용량 및 수익을 효율적으로 계산하는 것입니다. 이 계산을 만드는 데 사용할 수 있는 중요한 도구는 SUMPRODUCT 함수입니다. SUMPRODUCT 함수는 셀 범위에서 해당 값을 곱하고 해당 값의 합계를 반환합니다. SUMPRODUCT 계산에 사용되는 각 셀 범위는 동일한 차원이어야 합니다. 즉, 두 개의 행 또는 두 개의 열이 있는 SUMPRODUCT 사용할 수 있지만 한 열과 한 개의 행은 사용할 수 없습니다.
제품 조합 예제에서 SUMPRODUCT 함수를 사용하는 방법의 예로 리소스 사용량을 계산해 보겠습니다. 노동 사용량은 다음을 통해 계산됩니다.
(약 1파운드당 노동 사용)*(약 1파운드 생산)+ (약 2 파운드당 노동 사용)*(약물 2 파운드 생산) + ... (약 6파운드당 노동 사용)*(약 6파운드 생산)
D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4로 더 지루한 방식으로 노동 사용량을 계산할 수 있습니다. 마찬가지로, 원료 사용량은 D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5로 계산할 수 있습니다. 그러나 6개의 제품에 대한 워크시트에 이러한 수식을 입력하는 데는 시간이 많이 걸립니다. 예를 들어 공장에서 50개의 제품을 생산한 회사와 함께 일하는 경우 얼마나 오래 걸릴지 상상해 보십시오. 노동 및 원료 사용량을 계산하는 훨씬 쉬운 방법은 수식 SUMPRODUCT D14에서 D15로 복사하는 것입니다($D$2:$I$2,D4:I4). 이 수식은 D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (노동 사용량)를 계산하지만 입력이 훨씬 쉽습니다! 수식을 복사할 때 2행에서 제품 믹스를 캡처하도록 범위 D2:I2와 함께 $ 기호를 사용합니다. D15 셀의 수식은 원료 사용량을 계산합니다.
비슷한 방식으로, 우리의 이익은 에 의해 결정됩니다
(약 1 파운드 당 이익)*(약 1 파운드 생산) + (약 2 파운드 당 이익)*(약물 2 파운드 생산) + ... (약 6 파운드 당 이익)*(약 6 파운드 생산)
수익은 수식 SUMPRODUCT(D9:I9,$D$2:$I$2)을 사용하여 D12 셀에서 쉽게 계산됩니다.
이제 제품 혼합 해 찾기 모델의 세 가지 구성 요소를 식별할 수 있습니다.
-
대상 셀입니다. 우리의 목표는 이익을 극대화하는 것입니다 (셀 D12에서 계산).
-
셀 변경. 각 제품의 생산된 파운드 수(셀 범위 D2:I2에 나열됨)
-
제약 조건. 다음과 같은 제약 조건이 있습니다.
-
사용 가능한 것보다 더 많은 노동 또는 원료를 사용하지 마십시오. 즉, D14:D15 셀(사용된 리소스)의 값은 F14:F15 셀(사용 가능한 리소스)의 값보다 작거나 같아야 합니다.
-
수요가 있는 것보다 더 많은 약물을 생산하지 마십시오. 즉, D2:I2(각 약물에서 생성된 파운드)의 값은 각 약물에 대한 수요보다 작거나 같아야 합니다(D8:I8 세포에 나열됨).
-
우리는 어떤 약물의 음수 금액을 생산할 수 없습니다.
-
대상 셀을 입력하고 셀을 변경하며 제약 조건을 해 찾기로 입력하는 방법을 보여 줍니다. 그런 다음 해결 단추를 클릭하여 수익을 극대화하는 제품 믹스를 찾기만 하면 됩니다.
시작하려면 데이터 탭을 클릭하고 분석 그룹에서 해 찾기를 클릭합니다.
참고: 26장 "Excel 해 찾기를 사용한 최적화 소개"에서 설명한 대로 해 찾기는 Microsoft Office 단추, Excel 옵션, 추가 기능을 차례로 클릭하여 설치됩니다. 관리 목록에서 Excel 추가 기능을 클릭하고 해 찾기 추가 기능 상자를 검사 확인을 클릭합니다.
그림 27-2와 같이 해 찾기 매개 변수 대화 상자가 나타납니다.
대상 셀 설정 상자를 클릭한 다음 수익 셀(셀 D12)을 선택합니다. 셀 변경 상자를 클릭한 다음 각 약물에서 생성된 파운드가 포함된 D2:I2 범위를 가리킵니다. 이제 대화 상자가 그림 27-3으로 표시됩니다.
이제 모델에 제약 조건을 추가할 준비가 되었습니다. 추가 버튼을 클릭합니다. 그림 27-4에 표시된 제약 조건 추가 대화 상자가 표시됩니다.
리소스 사용 제약 조건을 추가하려면 셀 참조 상자를 클릭한 다음 D14:D15 범위를 선택합니다. 가운데 목록에서 <= 를 선택합니다. 제약 조건 상자를 클릭한 다음 F14:F15 셀 범위를 선택합니다. 이제 제약 조건 추가 대화 상자가 그림 27-5와 같이 표시됩니다.
이제 해 찾기가 변경되는 셀에 대해 다른 값을 시도할 때 D14<=F14 (사용된 노동력은 사용 가능한 노동보다 작거나 같음) 및 D15<=F15 (사용 가능한 원료보다 작거나 같음)를 모두 충족하는 조합만 고려됩니다. 추가를 클릭하여 수요 제약 조건을 입력합니다. 그림 27-6과 같이 제약 조건 추가 대화 상자를 입력합니다.
이러한 제약 조건을 추가하면 해 찾기가 변경되는 셀 값에 대해 서로 다른 조합을 시도할 때 다음 매개 변수를 충족하는 조합만 고려됩니다.
-
D2<=D8 (약 1의 생산량은 약 1에 대한 수요보다 작거나 같음)
-
E2<=E8 (약 2의 생산량은 약 2에 대한 수요보다 작거나 같음)
-
F2<=F8 (약 3의 생산량은 약 3에 대한 수요보다 작거나 같음)
-
G2<=G8 (약 4제의 생산량은 약 4에 대한 수요보다 작거나 같음)
-
H2<=H8 (약 5제의 생산량은 약 5에 대한 수요보다 작거나 같음)
-
I2<=I8 (약 6제의 생산량은 약 6에 대한 수요보다 작거나 같음)
제약 조건 추가 대화 상자에서 확인을 클릭합니다. 해 찾기 창은 그림 27-7과 같습니다.
해 찾기 옵션 대화 상자에서 셀 변경이 음수가 아니어야 한다는 제약 조건을 입력합니다. 해 찾기 매개 변수 대화 상자에서 옵션 단추를 클릭합니다. 다음 페이지의 그림 27-8에 표시된 것처럼 선형 모델 가정 상자와 음수가 아닌 것으로 가정 확인란을 선택합니다. 확인을 클릭합니다.
음수가 아닌 것으로 가정 확인란을 선택하면 해 찾기가 각 변경 셀이 음수가 아닌 값을 가정하는 변경 셀의 조합만 고려합니다. 제품 혼합 문제는 선형 모델이라는 특수한 유형의 해 찾기 문제이므로 선형 모델 가정 상자를 선택했습니다. 기본적으로 해 찾기 모델은 다음 조건에서 선형입니다.
-
대상 셀은 폼의 용어 (셀 변경)*(상수)를 함께 추가하여 계산됩니다.
-
각 제약 조건은 "선형 모델 요구 사항"을 충족합니다. 즉, 각 제약 조건은 폼의 용어 (셀 변경)*(상수) 를 함께 추가하고 합계를 상수와 비교하여 평가됩니다.
이 해 찾기 문제가 선형인 이유는 무엇인가요? 대상 셀(이익)은 로 계산됩니다.
(약 1 파운드 당 이익)*(약 1 파운드 생산) + (약 2 파운드 당 이익)*(약물 2 파운드 생산) + ... (약 6 파운드 당 이익)*(약 6 파운드 생산)
이 계산은 폼의 용어 (셀 변경)*(상수)를 함께 추가하여 대상 셀의 값이 파생되는 패턴을 따릅니다.
우리의 노동 제약 조건은 에서 파생 된 값을 비교하여 평가된다 (약의 파운드 당 사용 노동 1)*(약물 1 파운드 생산) + (약 2 파운드 당 사용 노동 2)*(약 2 파운드 생산)+ ... (노동 우리약의 파운드 당 ed 6)*(약 6 파운드 생산) 사용 가능한 노동에.
따라서 형식의 용어 (셀 변경)*(상수) 를 함께 추가하고 합계를 상수와 비교하여 노동 제약 조건을 평가합니다. 노동 제약 조건과 원료 제약 조건은 모두 선형 모델 요구 사항을 충족합니다.
요구 제약 조건은 형식을 사용합니다.
(약물 1 생산)<=(약 1 수요) (약 2 생산)<=(약 2 수요) §(Drug 6 produced)<=(Drug 6 Demand)
각 수요 제약 조건은 또한 선형 모델 요구 사항을 충족합니다. 각 제약 조건은 폼의 용어 (셀 변경)*(상수)를 함께 추가하고 합계를 상수와 비교하여 평가되기 때문입니다.
제품 혼합 모델이 선형 모델임을 보여 주면 왜 신경을 품어야 할까요?
-
해 찾기 모델이 선형이고 선형 모델 가정 을 선택하면 해 찾기 모델에 대한 최적의 솔루션을 찾을 수 있습니다. 해 찾기 모델이 선형이 아닌 경우 해 찾기는 최적의 솔루션을 찾을 수 있거나 찾을 수 없습니다.
-
해 찾기 모델이 선형이고 선형 모델 가정 을 선택하는 경우 해 찾기는 매우 효율적인 알고리즘(simplex 메서드)을 사용하여 모델의 최적 솔루션을 찾습니다. 해 찾기 모델이 선형이고 선형 모델 가정 을 선택하지 않는 경우 해 찾기는 매우 비효율적인 알고리즘(GRG2 메서드)을 사용하며 모델의 최적 솔루션을 찾는 데 어려움을 겪을 수 있습니다.
해 찾기 옵션 대화 상자에서 확인을 클릭한 후 그림 27-7의 앞부분에 표시된 기본 해 찾기 대화 상자로 돌아갑니다. 해결을 클릭하면 해 찾기는 제품 혼합 모델에 대한 최적의 솔루션(있는 경우)을 계산합니다. 챕터 26에서 언급했듯이, 제품 혼합 모델에 대한 최적의 솔루션은 가능한 모든 솔루션 집합에 대한 수익을 극대화하는 셀 값(각 약물에서 생산된 파운드)의 집합이 될 것입니다. 다시 말하지만, 가능한 솔루션은 모든 제약 조건을 충족하는 셀 값의 변경 집합입니다. 그림 27-9에 표시된 변경 셀 값은 모든 프로덕션 수준이 음수가 아니고, 프로덕션 수준이 수요를 초과하지 않으며, 리소스 사용량이 사용 가능한 리소스를 초과하지 않기 때문에 가능한 솔루션입니다.
다음 페이지의 그림 27-10에 표시된 변경되는 셀 값은 다음과 같은 이유로 사용할 수 없는 솔루션을 나타냅니다.
-
우리는 그것에 대 한 수요 보다 약물 5의 더 많은 생산.
-
우리는 사용 가능한 것보다 더 많은 노동을 사용합니다.
-
우리는 사용 가능한 것보다 더 많은 원료를 사용합니다.
해결을 클릭한 후 해 찾기는 그림 27-11에 표시된 최적의 솔루션을 빠르게 찾습니다. 워크시트에서 최적의 솔루션 값을 유지하려면 해 찾기 솔루션 유지를 선택해야 합니다.
우리의 제약 회사는 의 수준에서 월별 이익을 극대화 할 수 있습니다 $6,625.20 생산하여 596.67 약물의 파운드 4, 1084 약물의 파운드 5, 그리고 다른 약물의 아무도! 우리는 다른 방법으로 $ 6,625.20의 최대 이익을 달성 할 수 있는지 여부를 결정할 수 없습니다. 우리가 확신 할 수있는 것은 우리의 제한된 자원과 수요로, 이번 달에 $ 6,627.20 이상을 만들 수있는 방법이 없다는 것입니다.
각 제품에 대한 수요가 충족 되어야 한다고 가정합니다 . (파일 Prodmix.xlsx 실행 가능한 솔루션 없음 워크시트를 참조하세요.) 그런 다음 D2:I2<=D8:I8에서 D2:I2>=D8:I8로 수요 제약 조건을 변경해야 합니다. 이렇게 하려면 해 찾기를 열고 D2:I2<=D8:I8 제약 조건을 선택한 다음 변경을 클릭합니다. 그림 27-12에 표시된 제약 조건 변경 대화 상자가 나타납니다.
=>선택한 다음 확인을 클릭합니다. 이제 해 찾기가 모든 요구를 충족하는 셀 값만 변경하는 것을 고려하도록 했습니다. 해결을 클릭하면 "해 찾기가 가능한 솔루션을 찾을 수 없습니다."라는 메시지가 표시됩니다. 이 메시지는 모델에서 실수를 한 것이 아니라 제한된 리소스로 모든 제품에 대한 수요를 충족할 수 없다는 것을 의미합니다. 해 찾기는 단순히 우리가 각 제품에 대한 수요를 충족하려는 경우, 우리는 더 많은 노동, 더 많은 원자재 또는 둘 다의 더 많은 것을 추가해야한다는 것을 우리에게 말하고있다.
우리가 각 제품에 대한 무제한 수요를 허용하고 우리는 각 약물의 부정적인 수량을 생산하는 것을 허용하는 경우 어떻게되는지 보자. (이 해 찾기 문제는 파일 Prodmix.xlsx 값 수렴 안 함 워크시트에서 확인할 수 있습니다.) 이 상황에 가장 적합한 솔루션을 찾으려면 해 찾기를 열고 옵션 단추를 클릭한 다음 음수가 아닌 것으로 가정 상자의 선택을 취소합니다. 해 찾기 매개 변수 대화 상자에서 요청 제약 조건 D2:I2<=D8:I8을 선택한 다음 삭제를 클릭하여 제약 조건을 제거합니다. 해결을 클릭하면 해 찾기는 "셀 값 설정이 수렴되지 않음"이라는 메시지를 반환합니다. 이 메시지는 대상 셀을 최대화할 경우(예제와 같이) 임의로 큰 대상 셀 값이 있는 가능한 솔루션이 있음을 의미합니다. (대상 셀을 최소화하려면 "셀 값이 수렴되지 않음 설정"이라는 메시지는 임의로 작은 대상 셀 값이 있는 가능한 솔루션이 있음을 의미합니다.) 우리의 상황에서, 약물의 부정적인 생산을 허용 하 여, 우리는 실제로 "만들기" 다른 약물의 임의로 많은 금액을 생산 하는 데 사용할 수 있는 리소스. 우리의 무제한 수요를 감안할 때, 이것은 우리가 무제한 이익을 만들 수 있습니다. 실제 상황에서는 무한의 돈을 벌 수 없습니다. 간단히 말해서 "값 설정이 수렴되지 않음"이 표시되면 모델에 오류가 발생합니다.
-
우리 제약 회사가 현재 인건비보다 시간당 $1 이상으로 최대 500시간의 노동을 구매할 수 있다고 가정해 보겠습니다. 어떻게 하면 수익을 극대화할 수 있을까요?
-
칩 제조 공장에서 4명의 기술자(A, B, C 및 D)는 세 가지 제품(제품 1, 2 및 3)을 생산합니다. 이번 달, 칩 제조 업체는 제품 1의 80 단위, 제품 2의 50 단위, 그리고 제품 3의 최대 50 단위를 판매 할 수 있습니다. 기술자 A는 제품 1과 3만 만들 수 있습니다. 기술자 B는 제품 1과 2만 만들 수 있습니다. 기술자 C는 제품 3만 만들 수 있습니다. 기술자 D는 제품 2만 만들 수 있습니다. 생산된 각 단위에 대해 제품은 제품 1, $6; 제품 2, $7; 및 제품 3, $10. 각 기술자가 제품을 제조하는 데 필요한 시간(시간)은 다음과 같습니다.
제품
기술자 A
기술자 B
기술자 C
기술자 D
1
2
2.5
수행할 수 없음
수행할 수 없음
2
수행할 수 없음
3
수행할 수 없음
3.5
3
3
수행할 수 없음
4
수행할 수 없음
-
각 기술자는 매월 최대 120시간까지 작업할 수 있습니다. 칩 제조업체는 어떻게 월별 수익을 극대화할 수 있을까요? 소수 단위 수를 생성할 수 있다고 가정합니다.
-
컴퓨터 제조 공장은 마우스, 키보드 및 비디오 게임 조이스틱을 생산합니다. 단위당 이익, 단위당 노동 사용량, 월별 수요 및 단위별 기계 시간 사용량은 다음 표에 제공됩니다.
마우스
키보드
조이스틱
이익/단위
$8
$11
$9
노동 사용량/단위
.2시간
.3시간
.24시간
컴퓨터 시간/단위
.04시간
.055시간
.04시간
월별 수요
15,000
27,000
11,000
-
매달 총 13,000시간의 노동 시간과 3,000시간의 기계 시간을 사용할 수 있습니다. 제조업체는 어떻게 공장에서 월별 이익 기여도를 최대화할 수 있습니까?
-
각 약물에 대한 최소 200 단위의 수요를 충족시켜야한다고 가정하는 약물 예제를 해결합니다.
-
제이슨은 다이아몬드 브레이슬릿, 목걸이, 귀걸이를 만듭니다. 그는 매월 최대 160시간을 일하고 싶어합니다. 그는 800 온스의 다이아몬드를 가지고 있습니다. 각 제품을 생산하는 데 필요한 다이아몬드의 이익, 노동 시간 및 온스는 아래에 제공됩니다. 각 제품에 대한 수요가 무제한인 경우 Jason는 어떻게 수익을 극대화할 수 있을까요?
제품
단위 이익
단위당 노동 시간
단위당 다이아몬드 온스
팔찌
₩300,000
.35
1.2
목걸이
\200,000
.15
0.75
귀걸이
$100
.05
.5
