엑셀(Excel)은 다양한 수학적 연산을 수행할 수 있는 강력한 도구입니다. 그중 선형 계획법(Linear Programming)은 통계학과 응용수학의 중요한 분야로, 실무에서 폭넓게 활용됩니다. 선형 계획법 문제를 손으로 직접 푸는 것은 번거롭고 시간이 많이 걸리지만, 엑셀 솔버(Excel Solver)를 사용하면 복잡한 최적화 문제도 빠르고 정확하게 해결할 수 있습니다. 이 글에서는 엑셀 솔버를 활용해 선형 계획법 문제를 푸는 방법을 단계별로 자세히 안내합니다.
선형 계획법이란?
선형 계획법은 주어진 데이터 변수를 바탕으로 예측 분석을 수행하고, 제한된 자원을 최적화하는 데 활용되는 기법입니다. 이를 위해서는 목적 함수(Objective Function)와 몇 가지 제약 조건(Constraints)이 반드시 필요합니다. 엑셀 솔버를 사용하면 이러한 선형 계획법 문제의 최적해를 몇 번의 클릭만으로 구할 수 있습니다.
엑셀 솔버로 선형 계획법 풀기: 단계별 절차
실습을 위해 다음과 같은 비즈니스 문제를 예로 들어 보겠습니다.
어떤 제조업체가 두 가지 제품 'A'와 'B'를 생산한다고 가정해 봅시다. 제품 A 한 단위를 만들려면 원자재 P가 25kg, Q가 35kg, R이 10kg 필요합니다. 마찬가지로 제품 B에는 P 15kg, Q 20kg, R 15kg이 소요됩니다. 제조업체는 원자재 P를 최소 500kg, Q를 최소 850kg, R을 최소 300kg 확보해야 합니다. 제품 A는 단위당 $35, 제품 B는 단위당 $30의 비용이 들 때, 최소 원자재 요구량을 충족하면서 비용을 최소화하려면 각 제품을 몇 단위씩 생산해야 할까요? 그리고 그때의 총비용은 얼마일까요?
1단계: 엑셀에서 솔버 활성화하기
솔버는 MS 엑셀의 추가 기능(Add-in) 프로그램으로, 기본적으로 비활성화되어 있습니다. 따라서 먼저 활성화해야 합니다.
- 파일(File) ➤ 옵션(Options) 메뉴로 이동합니다.
- 추가 기능(Add-ins) 탭을 선택합니다.
- 하단의 관리(Manage) 드롭다운에서 Excel 추가 기능을 선택하고 이동(Go) 버튼을 누릅니다.
- 추가 기능 대화상자가 나타나면 Solver 추가 기능 항목에 체크하고 확인(OK)을 클릭합니다.
- 이제 데이터(Data) 탭의 분석(Analyze) 그룹에서 솔버 프로그램을 확인할 수 있습니다.
2단계: 제약 조건 입력하기
이제 워크시트에 제약 조건과 목적 함수를 입력합니다. 제품 A를 x단위, 제품 B를 y단위 생산한다고 하면, 총비용은 $35x + $30y가 되며, 이것이 우리가 최소화해야 할 목적 함수입니다. 동시에 다음 요구 사항도 충족해야 합니다.
25x + 15y ≥ 500, 35x + 20y ≥ 850, 10x + 15y ≥ 300, x ≥ 0, y ≥ 0
- 먼저 제품 A와 B의 단위별 비용을 입력합니다.
- 그다음 각 제품별 원자재 소요량을 입력합니다.
- 마지막으로 최소 요구량 값을 삽입합니다.
3단계: 엑셀 수식 작성하기
- x 값은 C5 셀에, y 값은 D5 셀에 입력할 것입니다.
- E6 셀을 선택한 후 아래 수식을 입력합니다.
=($C$5*C6)+($D$5*D6)- Enter 키를 누릅니다. 현재 C5와 D5 셀이 비어 있으므로 결과값은 0 또는 공백으로 표시됩니다.
- 이어서 E8 셀을 선택하고 아래 수식을 입력합니다.
=($C$5*C8)+($D$5*D8)- Enter 키를 눌러 값을 반환한 뒤, 채우기 핸들(AutoFill)을 드래그해 나머지 셀에도 수식을 적용합니다.
- C5와 D5가 아직 비어 있으므로 모든 결과는 0으로 표시됩니다.
4단계: 솔버로 선형 계획법 문제 풀기
- 데이터 탭에서 솔버(Solver)를 실행합니다. 그러면 솔버 매개 변수(Solver Parameters) 대화상자가 열립니다.
- 목표 설정(Set Objective) 상자에 E6 셀을 지정합니다.
- 최소화(Min) 옵션을 선택합니다.
- 변수 셀로 C5:D5 범위를 지정합니다.
- 제약 조건을 추가하기 위해 추가(Add) 버튼을 누릅니다.
- 제약 조건 추가(Add Constraint) 대화상자가 나타납니다.
- C5:D5 범위를 선택하고 드롭다운에서 >=(크거나 같음) 기호를 선택한 후 값으로 0을 입력하고 추가를 누릅니다.
- 다음으로 최소 요구량 제약을 위해 E8:E10 범위를 선택하고 >= 기호를 지정한 뒤, 제약 필드에 G8:G10 범위를 입력하고 확인(OK)을 클릭합니다.
- 설정한 제약 조건이 목록에 표시되면 풀기(Solve) 버튼을 누릅니다.
- 계산이 완료되면 결과 관련 대화상자가 나타납니다. 솔버 해 유지(Keep Solver Solution) 옵션을 선택하고 확인을 누릅니다.
- 이제 지정된 셀에 정확한 최적해가 표시됩니다.
최종 결과
- x 값은 77단위, y 값은 6.15단위입니다.
- 최소 비용은 $912입니다.
- 최적화된 원자재 사용량은 P 54kg, Q 850kg, R 300kg입니다.
- 따라서 제조업체는 제품 A를 77단위, 제품 B를 6.15단위 생산하는 것이 최적입니다.
마무리
지금까지 살펴본 절차를 따라 하면 엑셀 솔버를 활용해 선형 계획법 문제를 손쉽게 해결할 수 있습니다. 솔버는 재고 관리, 생산 계획, 물류 최적화 등 다양한 실무 분야에서도 유용하게 활용되니 꾸준히 연습해 보시기 바랍니다. 더 궁금한 점이나 다른 활용 방법이 있다면 댓글로 의견을 남겨주세요. 이와 같은 실용적인 엑셀 활용 팁은 ExcelDemy 웹사이트에서 계속 확인하실 수 있습니다.