엑셀에서 선형 계획법을 그래프로 푸는 방법 – 단계별 완벽 가이드

여러 변수를 동시에 고려해야 하는 문제를 풀 때 선형 계획법(Linear Programming)이 매우 유용합니다. 선형 계획법을 푸는 방법은 여러 가지가 있지만, 그중 가장 직관적이고 손쉬운 방법은 바로 그래프를 이용한 시각화입니다. 이 글에서는 엑셀에서 선형 계획법을 그래프로 표현하고 최적해를 구하는 전 과정을 단계별로 자세히 안내합니다.

실습용 워크북은 아래에서 무료로 다운로드할 수 있습니다.

선형 계획법이란?

선형 계획법은 여러 수학 함수와 제약 조건을 통해 주어진 상황을 분석하고, 목표값의 최적 지점을 찾아내는 수학적 도구입니다. 이 기법은 사업 투자 최적화, 생산 주기 관리, 필요 제품의 구매 계획 등 다양한 비즈니스 분야에서 폭넓게 활용됩니다.

선형 계획법의 기본 구성 요소

• 결정 변수(Decision Variables): 선형 계획법으로 목적의 최적점을 계산하기 위해 필요한 변수입니다. 의사 결정 상황, 제약 조건, 목적 함수가 모두 이 변수들을 기준으로 설정됩니다.
• 제약 조건(Constraints): 목적 함수를 제한하고 실행 가능 영역을 결정하는 조건입니다. 등식일 수도 있고 부등식일 수도 있습니다.
• 목적 함수(Objective Function): 달성하려는 목표를 나타내는 함수입니다. 적절한 제약 조건 하에서 이 식을 만족시켜 최적해를 찾아야 합니다.
• 실행 가능 영역(Feasible Region): 제약 조건을 적용한 후 목적 함수가 취할 수 있는 영역으로, 최적해는 반드시 이 영역 안에 존재합니다.
• 실행 가능 해(Feasible Solution): 실행 가능 영역의 꼭짓점(코너 포인트)들에 대한 목적 함수의 해를 말합니다.
• 최적해(Optimal Solution): 목적 함수의 최적 지점으로, 계산된 실행 가능 해들 중에서 도출할 수 있습니다.

엑셀에서 선형 계획법을 그래프로 푸는 단계

예를 들어 목적 함수가 F = 6X + 8Y로 주어졌고, 이 함수를 다음 제약 조건 하에서 최대화해야 한다고 가정해 보겠습니다.

2X + 4Y <= 60

4X + 2Y <= 48

이제 아래 단계를 따라 엑셀에서 선형 계획법을 그래프로 그려 최적점을 찾아보겠습니다.

📌 1단계: 목적 함수 및 제약 조건 직선의 점 기록하기

엑셀에서 선형 계획법을 그래프로 그리려면 가장 먼저 목적 함수와 제약 조건의 점들을 기록해야 합니다.

• 먼저 목적 함수와 제약 조건의 계수와 부등호 기호를 정확하게 입력합니다.

• 첫 번째 제약 조건 C1의 경우, 직선을 그리기 위해 두 개의 점을 구합니다. X=0을 대입하면 Y=15가 되고, Y=0을 대입하면 X=30이 됩니다.

• 같은 방식으로 두 번째 제약 조건 C2의 두 점도 구합니다. X=0을 대입하면 Y=24, Y=0을 대입하면 X=12가 됩니다.

그러면 목적 함수, 제약 조건, 그리고 각 제약 조건을 그리기 위한 두 점이 모두 담긴 워크시트가 완성됩니다. 최종적으로 워크시트는 다음과 같은 모습이 됩니다.

📌 2단계: 실행 가능 영역 찾기

1단계를 마쳤다면 이제 실행 가능 영역을 찾아야 합니다.

• 먼저 B6:C8 셀 범위를 선택한 뒤, 삽입 탭 >> 차트 그룹 >> 분산형 또는 거품형 차트 삽입 도구 >> 꺾은선형 분산형(Scatter with Smooth Lines) 옵션을 선택합니다.

• 그러면 B6:C8 셀 값을 기반으로 꺾은선 분산형 차트가 생성됩니다.

• 하지만 아직 원하는 형식이 아니므로 차트를 마우스 오른쪽 버튼으로 클릭한 뒤, 컨텍스트 메뉴에서 데이터 선택… 옵션을 선택합니다.

• 데이터 원본 선택 창이 나타나면 series1 항목을 선택하고 편집 버튼을 클릭합니다.

• 데이터 계열 편집 창이 열리면 계열 이름: 입력란에 C1을 입력합니다. 계열 X 값:에는 B6:B7 범위를, 계열 Y 값:에는 C6:C7 범위를 지정한 후 확인 버튼을 클릭합니다.

• 다시 데이터 원본 선택 창으로 돌아오면 추가 버튼을 클릭합니다.

• 새로운 데이터 계열 편집 창이 나타나면 계열 이름:에 C2를 입력하고, 계열 X 값:에는 B11:B12, 계열 Y 값:에는 C11:C12 범위를 지정한 뒤 확인 버튼을 누릅니다.

• 다시 데이터 원본 선택 창으로 돌아오면 확인 버튼을 클릭합니다.

• 이제 선형 계획법의 모든 제약 조건이 표현된 분산형 그래프가 완성되며, 다음과 같은 모습이 됩니다.

• 두 제약 조건 모두 '이하(<=)' 부등식이므로, 두 제약 직선은 모두 원점 방향을 향합니다. 따라서 실행 가능 영역은 아래 그림과 같이 형성됩니다.

즉, ABCD가 실행 가능 영역이며, A, B, C, D가 해당 영역의 꼭짓점입니다.

📌 3단계: 최적해 구하기

실행 가능 영역을 확인했다면 이제 실행 가능 해들을 구해야 합니다.

• 먼저 각 꼭짓점의 X, Y 좌표를 구합니다. 그래프와 제약 조건 값 표를 통해 A, B, C 점은 각각 (0,15), (0,0), (12,0)임을 쉽게 알 수 있습니다.

• D 점의 좌표를 구하려면 D5:D6 셀을 선택하고 MMULT 함수와 MINVERSE 함수를 사용한 아래 수식을 입력한 후, Ctrl+Shift+Enter를 누릅니다.

🔎 수식 설명:

• MINVERSE('Finding Points of Constraints'!C6:D7)

'Finding Points of Constraints' 워크시트의 C6:D7 셀 값에 대한 역행렬을 반환합니다.

결과: (-0.166666667, 0.333333333) & (0.333333333, -0.166666667)

• =MMULT(MINVERSE('Finding Points of Constraints'!C6:D7), 'Finding Points of Constraints'!F6:F7)

앞서 구한 역행렬 배열과 'Finding Points of Constraints' 워크시트의 F6:F7 배열의 행렬 곱을 반환합니다.

결과: {6, 12}

• 그 결과, 두 제약 직선의 교차점인 D 점의 좌표를 얻을 수 있습니다.

• 이제 모든 꼭짓점을 확보했습니다. 다음으로 이 점들로부터 실행 가능 해를 구해야 합니다. C7 셀에 아래 수식을 입력하고 Enter 키를 누릅니다.

🔎 수식 설명:

• =(C5*'Finding Points of Constraints'!$C$5)

현재 워크시트의 C5 셀 값과 'Finding Points of Constraints' 워크시트의 C5 셀 값을 곱합니다.

결과: 0

• ('Finding Points of Constraints'!$D$5*C6)

'Finding Points of Constraints' 워크시트의 D5 셀 값과 현재 워크시트의 C6 셀 값을 곱합니다.

결과: 120

• =(C5*'Finding Points of Constraints'!$C$5)+('Finding Points of Constraints'!$D$5*C6)

앞의 두 결과를 더합니다.

결과: 120

• 이렇게 하면 꼭짓점 A에 대한 목적 함수의 값을 구할 수 있습니다. 이후 셀의 오른쪽 아래 모서리에 커서를 올리면 채우기 핸들이 나타납니다. 이것을 오른쪽으로 드래그하여 나머지 모든 점에 같은 수식을 복사합니다.

• 그러면 모든 실행 가능 해를 한눈에 확인할 수 있습니다.

• 마지막으로 F를 최대화해야 하므로, F의 최댓값을 찾으면 그래프를 통한 선형 계획법 풀이가 완료됩니다. 보시다시피 F의 최댓값은 D (6,12) 점에서 132입니다. 따라서 최적점은 D (6,12)입니다.

이렇게 그래프를 이용한 선형 계획법 풀이가 끝나고 최종 결과가 도출됩니다.

마무리

지금까지 엑셀에서 선형 계획법을 그래프로 표현하는 모든 단계를 자세히 살펴보았습니다. 글을 꼼꼼히 읽고 실습용 워크북으로 충분히 연습해 보시길 권장합니다. 이 글이 도움이 되었기를 바라며, 추가 질문이나 제안 사항이 있다면 언제든지 댓글로 남겨주세요.
이와 같은 유용한 글을 더 보고 싶다면 ExcelDemy를 방문해 주세요. 감사합니다!