선형 계획법(Linear Programming)은 응용수학에서 가장 흥미로운 분야 중 하나입니다. 다행히 Excel을 활용하면 별도의 전문 프로그램 없이도 선형 계획법 문제를 해결할 수 있습니다. 다만 Excel에는 이를 위한 기본 제공 함수가 없기 때문에, 그래프를 직접 그리는 방법이나 '솔버(Solver)' 추가 기능을 사용하는 방식으로 접근해야 합니다. 이 글에서는 두 가지 방법을 단계별로 자세히 살펴보겠습니다.
선형 계획법이란 무엇인가?
선형 계획법은 수학적 최적화(Mathematical Optimization) 기법 중 하나로, 선형 함수 간의 관계를 바탕으로 최대 이윤 또는 최소 비용을 달성하기 위한 최적의 해를 구하는 모델링 기술입니다.
선형 계획법의 핵심 용어 정리
본격적인 실습에 앞서, 선형 계획법에서 자주 사용되는 기본 용어를 먼저 확인해 보겠습니다.
- 결정 변수(Decision Variables): 문제의 최적해를 결정하는 데 사용되는 변수들로 구성된 표입니다.
- 제약 조건(Constraints): 해를 구할 때 선형 함수에 부과되는 조건을 의미합니다.
- 목적 함수(Objective Function): 우리가 달성하고자 하는 목표를 정량적으로 표현한 함수입니다.
- 선형성(Linearity): 변수들 사이의 관계가 반드시 선형이어야 한다는 조건입니다.
- 유한성(Finiteness): 모든 변수의 해는 유한한 값이어야 합니다.
- 최적해(Optimal Solution): 목적 함수가 최적이 되는 지점으로, 이 지점에서 변수들의 값을 구하게 됩니다.
Excel에서 선형 계획법을 푸는 2가지 방법
Excel에서 선형 계획법을 해결하는 방법은 크게 두 가지입니다. 첫 번째는 그래프를 이용하는 방법, 두 번째는 Solver 추가 기능을 이용하는 방법입니다.
예제로 아래와 같은 목적 함수와 두 개의 제약 조건이 주어졌다고 가정해 보겠습니다.
목적 함수:
A = 8X + 10Y
제약 조건:
2X + 4Y ≤ 72
4X + 2Y ≤ 48
방법 1. 그래프 작성으로 선형 계획법 풀기
그래프를 활용해 선형 계획법 문제를 해결하려면 아래 단계를 따라 진행하세요.
📌 단계:
- 먼저 각 변수의 계수를 표로 정리하여 분리합니다.
- 다음으로, 한 변수의 값을 0으로 놓고 나머지 두 변수의 값을 구합니다. 먼저 1번째 제약 조건에 적용해 보겠습니다.
- 같은 방식을 2번째 제약 조건에도 적용합니다. 여기서는 Y=0일 때 X=12, X=0일 때 Y=24가 됩니다.
- 1번째 제약 조건에서 구한 값들을 선택합니다.
- [삽입] 탭을 클릭합니다.
- 차트 그룹에서 원하는 분산형 차트(Scatter Chart)를 선택합니다.
- 선택한 데이터를 기반으로 그래프가 생성됩니다.
- 차트 위에 커서를 올린 후 마우스 오른쪽 버튼을 누릅니다.
- 컨텍스트 메뉴에서 [데이터 선택(Select Data)]을 선택합니다.
- 데이터 원본 선택 창이 나타납니다.
- 입력 데이터는 현재 Series1이라는 이름으로 되어 있습니다.
- Series1을 선택하고 [편집(Edit)] 버튼을 클릭합니다.
- 계열 이름 상자에 C1을 입력하고 [확인]을 누릅니다.
- 두 번째 제약 조건이 있으므로, 새로운 데이터 계열을 추가해야 합니다. [추가(Add)] 버튼을 클릭합니다.
- 계열 편집 창이 열리면 계열 이름과 X, Y 변수의 값 범위를 입력한 뒤 [확인]을 누릅니다.
- 다음 창에서도 마찬가지로 [확인]을 눌러 완료합니다.
- 완성된 그래프를 확인합니다. 경계점(edge points)에 이름을 붙여 두면 이후 계산이 편리합니다.
- 경계점들을 바탕으로 새로운 데이터셋 테이블을 만듭니다.
이제 수식을 이용해 교차점, 즉 C점의 좌표를 구해 보겠습니다.
- 셀 E15에 아래 수식을 입력합니다.
=MMULT(MINVERSE(C6:D7),F6:F7)
- Enter 키를 누르면 X와 Y 좌표 값이 동시에 계산됩니다.
- 이어서 아래 수식으로 최적값을 구합니다.
=C15*$C$5+C16*$D$5
- Enter 키를 누른 후 채우기 핸들(Fill Handle)을 오른쪽으로 드래그합니다.
변수 값이 달라질 때마다 목적 함수 A의 값도 함께 변하는 것을 확인할 수 있습니다. 그 결과 C점에서 A의 최댓값 192가 나타나며, 이때 X = 4, Y = 16입니다.
방법 2. Excel 솔버(Solver) 추가 기능으로 선형 계획법 풀기
이번에는 Solver라는 추가 기능을 활용해 선형 시스템을 해결해 보겠습니다.
📌 단계:
- 먼저 아래 표처럼 계수들을 정리합니다.
- 셀 E6으로 이동해 다음 수식을 입력합니다.
=($C$5*C6)+($D$5*D6)
이 수식은 목적 함수 A의 결과를 계산합니다.
- 현재 C5와 D5 셀이 비어 있으므로 결과는 0으로 표시됩니다. 이 수식을 E7:E8 범위까지 확장 적용합니다.
- 이제 [파일] → [옵션] → [추가 기능]으로 이동합니다.
- 목록에서 Solver 추가 기능을 선택하고 [이동(Go)]을 클릭합니다.
- Solver Add-in에 체크한 후 [확인]을 누릅니다.
- 셀 E6을 클릭한 상태에서 [데이터] 탭으로 이동합니다.
- [솔버(Solver)] 옵션을 클릭합니다.
- 솔버 매개 변수 창이 나타납니다. 입력 항목은 아래 이미지에 표시되어 있습니다.
- 목표 값 설정(Set Objective)은 솔버를 적용할 셀을 의미합니다.
- 최댓값을 구하고자 하므로 [최대값(Max)] 옵션을 선택합니다. 물론 최소값이나 특정 값 옵션도 사용할 수 있습니다.
- 이후 [추가] 버튼을 클릭합니다.
- X와 Y의 값이 0보다 크거나 같다는 조건을 추가합니다.
- 다른 제약 조건도 추가하기 위해 다시 [추가]를 누릅니다.
- 주어진 제약 조건의 값을 입력합니다. 입력을 마쳤으면 [확인]을 누릅니다.
- 두 개의 제약 조건이 모두 등록된 것을 확인한 뒤, [풀기(Solve)] 버튼을 클릭합니다.
- 변수 값과 목적 함수 A의 최적 결과가 자동으로 계산됩니다.
마무리
이번 글에서는 Excel에서 선형 계획법을 해결하는 두 가지 방법, 즉 그래프를 활용하는 방법과 Solver 추가 기능을 활용하는 방법을 자세히 알아보았습니다. 두 방법 모두 장단점이 있으므로, 문제의 성격과 상황에 맞게 선택하여 활용하시기 바랍니다. 더 많은 Excel 팁과 노하우가 필요하시면 ExcelDemy 웹사이트를 방문해 주시고, 소중한 의견은 댓글로 남겨주세요.