엑셀 솔버(Excel Solver)로 혼합 선형 계획법 문제 해결하는 방법

이 글에서는 엑셀 솔버(Solver)를 활용해 혼합 선형 계획법(Blending Linear Programming) 문제를 해결하는 방법을 소개합니다. 선형 계획법은 비용을 최소화하거나 이익을 극대화해야 하는 비즈니스 분야에서 매우 중요한 기법입니다. 일상생활에서도 식습관이나 지출을 최적화할 때 활용할 수 있을 만큼 실용성이 높습니다. 혼합 선형 계획법은 여러 원자재를 혼합하여 만드는 제품의 이익 또는 비용 최적화를 계산하는 특수한 형태의 선형 계획법입니다. 이번 글에서는 엑셀로 실제 사례 두 가지를 적용하면서 혼합 선형 계획법이 어떻게 작동하는지 살펴보겠습니다.

혼합 선형 계획법이란?

화학 산업이나 식품 공장에서 일한다면 다양한 재료를 섞어 제품을 만들어야 합니다. 예를 들어 의약품을 생산하려면 필요한 성분들을 정해진 비율에 맞춰 혼합해야 하고, 각 성분을 얼마나 구매할지, 완제품의 품질은 어느 수준일지 등도 함께 고려해야 합니다. 이런 상황에서 고객에게 공급할 제품의 혼합량을 최적화하려면 혼합 선형 계획법이 해답을 제공해 줍니다. 이 기법은 원자재를 생산 과정에서 효율적으로 사용하는 방법을 찾도록 도와줍니다.

엑셀 솔버로 혼합 선형 계획법 문제 해결하기: 2가지 예제

이번 글에서는 혼합 선형 계획법의 두 가지 유형을 설명합니다. 하나는 고정 배합비(Fixed Recipe)에 대한 혼합 LP(선형 계획법) 풀이 방법이고, 다른 하나는 가변 배합비(Flexible Recipe)에 대한 풀이 방법입니다.

고정 배합비란 재료의 정확한 비율이나 혼합량을 미리 알고 있는 상황을 말합니다. 반면, 제품 생산 시 원자재를 반드시 정해진 양만큼 쓸 필요가 없다면 가변 배합비 방식의 혼합 LP를 적용해야 합니다.

첫 번째 예제는 고정 배합비 문제를 다룹니다. 여기에는 세 종류의 액체 원료 A, B, C가 있으며, 이를 조합해 A-B(60%-40%)와 A-C(80%-20%)라는 두 가지 신제품을 만듭니다. 그 외에 리터당 매출, 리터당 비용, 공장에 보유한 원자재 수량 같은 파라미터들이 주어지며, 우리의 목표는 이 시나리오에서 이익을 극대화하는 것입니다.

가변 배합비 문제에서는 서로 다른 원자재를 혼합해 일반(regular), 프리미엄(exclusive), 초고급(super quality) 등 다양한 등급의 강철을 생산합니다. 원자재별 가용 수량, 톤당 비용, 품질 등급 데이터가 있고, 필수 생산량과 생산된 강철 등급별 톤당 가격, 최소 요구 등급도 설정되어 있습니다. 여기에는 선형화된 등급(Linearized Rating)이라는 추가 파라미터도 등장합니다.

1. 고정 배합비(Fixed Recipe) 문제를 위한 혼합 선형 계획법

앞서 살펴본 문제 설정을 바탕으로 아래 절차를 따라 진행해 보겠습니다.

단계:

• 먼저 필요한 수식을 설정합니다. 원자재의 사용량(Raw Usage)을 구하기 위해 셀 C12에 아래 수식을 입력합니다.

=SUMPRODUCT(C5:C9,$G$5:$G$9)

여기서는 SUMPRODUCT 함수를 사용해 원료 A의 사용량을 반환받습니다.

• 그다음, 채우기 핸들(필 아이콘)을 오른쪽으로 끌어 E12까지 자동 채우기(AutoFill) 합니다.

• 이후 셀 I11에 아래 수식을 입력해 매출(Revenue)을 계산합니다.

=SUMPRODUCT(G5:G9,H5:H9)*C16

여기서 수식에 셀 C16의 값 3.7854를 곱했는데, 이는 1갤런이 3.7854리터에 해당하기 때문입니다.

• 다음으로 셀 I12에 아래 수식을 입력하고 ENTER 키를 누릅니다.

=SUMPRODUCT(C11:E11,C12:E12)*C16

이 수식은 생산 비용을 반환합니다.

• 이어서 아래 수식으로 이익을 계산합니다.

=I11-I12

• 이제 최소 생산 요구량을 설정합니다.

• 그다음 데이터(Data) >> 솔버(Solver)로 이동합니다. 데이터 탭에 솔버 추가 기능(Solver Add-in)이 없다면 파일(File) >> 옵션(Options) >> 추가 기능(Add-ins) >> Excel 추가 기능 >> 이동(Go) >> Solver Add-in을 선택하고 확인(OK)을 클릭하세요.
• 솔버 추가 기능을 열려면 데이터 >> 솔버를 클릭합니다.

• 이익을 극대화해야 하므로 목표 셋팅(Objective)에 이익 값이 저장된 I13 셀을 지정합니다.
• 변수는 제품의 혼합 비율이므로 G5:G9 범위를 ‘변수 셀(By Changing Variable Cells)’ 항목에 추가합니다.
• 그다음 추가(Add)를 클릭해 제약 조건(constraints)을 입력합니다.

• 원자재 사용량은 가용 재료를 초과할 수 없으므로 첫 번째 제약 조건은 C12:E12 범위가 C14:E14보다 작거나 같다는 것입니다.
• 입력 후 추가(Add)를 클릭합니다.

• 같은 방식으로 생산량이 최소 요구 생산량보다 크거나 같다는 제약 조건도 추가합니다.
• 다음으로 확인(OK)을 클릭합니다.

• 이후 ‘제약 조건이 없는 변수를 음수가 아닌 값으로 만들기(Make Unconstrained Variable Non-Negative)’에 체크합니다.
• 풀이 방법(Solving Method)으로 Simplex LP를 선택합니다.
• 그다음 풀기(Solve)를 클릭합니다.

• 그러면 확인 메시지 상자가 나타납니다. 확인(OK)을 클릭하면 됩니다.

• 마지막으로 최대 이익을 얻기 위해 각 원자재를 얼마나 사용해야 하는지 결과값을 확인할 수 있습니다.
• 또한 매출, 생산 비용, 이익 결과도 함께 얻을 수 있습니다.

이렇게 엑셀 솔버를 사용해 고정 배합비에 대한 혼합 선형 계획법 문제를 해결할 수 있습니다.

2. 엑셀 솔버로 가변 배합비(Flexible Recipe) 혼합 선형 계획법 문제 해결하기

이번 섹션에서는 가변 배합비에 대한 혼합 선형 계획법 문제를 해결하는 방법을 보여 드립니다. 문제 설정에 대한 내용은 앞부분의 설명을 참고하세요. 이제 아래 절차를 따라가 보겠습니다.

단계:

• 먼저 풀이에 필요한 수식을 설정합니다. 셀 F5에 아래 수식을 입력하고 ENTER 키를 누릅니다.

=SUM(C5:E5)

이 수식은 SUM 함수를 사용해 첫 번째 가용(Available) 원자재로부터 생산될 1유형 강철(일반, 프리미엄, 초고급)의 총 생산량을 계산합니다.

• 다음으로 채우기 핸들을 아래로 끌어 F7까지 셀을 채웁니다.

• 이어서 일반 강철(Regular Steel)의 총 생산량을 계산하기 위해 아래 수식을 입력합니다.

=SUM(C5:C7)

• 마찬가지로 셀 C14에 아래 수식을 입력해 선형화된 등급(Linearized Rating)을 계산하고, 인접한 셀을 E14까지 채웁니다.

=SUMPRODUCT($J$5:$J$7,C5:C7)

• 그다음 셀 C16에 아래 수식을 입력하고 E16까지 셀을 채웁니다.

=C12*C8

이 수식은 생산된 강철의 선형화된 등급 값을 구해 줍니다.

• 다음으로 셀 I10에 아래 수식을 입력해 매출(Revenue)을 산출합니다.

=SUMPRODUCT(C11:E11,C8:E8)

• 생산 비용을 계산하려면 아래 수식을 사용합니다.

=SUMPRODUCT(I5:I7,F5:F7)

• 그리고 아래 수식은 이익을 반환합니다.

=I10-I11

• 이후 목표 셋팅(Subject), 변수 셀(Changing Variable) 및 제약 조건(Constraints) 입력은 첫 번째 예제의 솔버 실행 절차를 동일하게 따르면 됩니다. 여기서는 각 부등식의 의미만 간단히 설명하겠습니다.
• 이익을 극대화해야 하므로 이익 값이 있는 셀(I12)을 목표로 지정합니다.
• 다음으로 변경 대상 변수는 강철 제품이므로 생산량이 저장될 C5:E7 범위를 지정합니다.
• 그다음 몇 가지 제약 조건을 추가합니다. 원자재의 선형화된 등급은 선형화된 최소 요구 등급보다 크거나 같아야 하므로, C14:E14 범위는 C16:E16보다 크거나 같습니다.
• 또한 생산량은 요구량보다 커야 하므로 C8:E8 범위는 C10:E10보다 크거나 같습니다.
• 마지막으로 원자재 사용량은 가용 원자재보다 적어야 하므로 F5:F7 범위는 H5:H7보다 작거나 같습니다.

• 풀기(Solve)를 클릭하면 최대 이익을 얻기 위해 각 원자재를 얼마나 사용해야 하는지 결과값을 확인할 수 있습니다.
• 더불어 매출, 생산 비용, 이익 결과도 함께 얻을 수 있습니다.

이렇게 엑셀 솔버를 사용해 가변 배합비에 대한 혼합 선형 계획법 문제도 해결할 수 있습니다.

연습 섹션

여기서는 이 글에서 사용한 데이터 세트를 제공하니, 위에서 소개한 방법들을 직접 연습해 볼 수 있습니다.

결론

지금까지 살펴본 내용을 통해 엑셀 솔버로 혼합 선형 계획법을 적용해 실생활의 최적화 문제를 해결하는 기본 개념을 충분히 익히셨을 것입니다. 이 글에 대해 더 좋은 방법이나 질문, 피드백이 있다면 댓글로 공유해 주세요. 여러분의 의견은 앞으로 더 나은 글을 만드는 데 큰 도움이 됩니다. 더 궁금한 점이 있다면 ExcelDemy 웹사이트를 방문해 확인해 보세요.