☰

이 문제를 해결하려면 Excel 솔버를 어떻게 사용합니까?

tag-icon • tag-icon microsoft-excel solver
이 문제를 해결하려면 Excel 솔버를 어떻게 사용합니까?

ABC Corporation은 전기 부품 및 부품의 글로벌 유통업체입니다. 회사는 유럽에서 부품과 부품을 구매하기로 계약을 맺고 이를 유럽의 3개 항구 E1, E2, E3의 창고로 배송했습니다. 다양한 구성 요소와 부품은 미국 고객의 요구에 따라 컨테이너에 적재됩니다. 각 항구에는 매달 사용할 수 있는 컨테이너 수가 고정되어 있습니다. 그런 다음 컨테이너는 컨테이너 선박을 통해 P1, P2, P3 및 P4 항구로 해외로 배송됩니다. 이러한 항구에서 컨테이너는 일반적으로 트럭과 연결되어 I1, I2 및 I3의 내륙 항구로 운반됩니다. 매달 각 항구에는 고정된 수의 화물 운송업체가 있습니다. 이러한 내륙 항구는 컨테이너가 수집되어 한 운송 모드에서 다른 운송 모드로(즉, 트럭에서 철도로, 또는 그 반대로) 운송되는 "화물 마을" 또는 복합 운송 교차점이라고도 합니다. 내륙 항구에서 컨테이너는 D1, D2, D3, D4 및 D5에 있는 ABC의 유통 센터로 운송됩니다. 다음은 이 해외 공급망을 따라 각 승선 지점과 목적지 지점과 각 항구에서 사용 가능한 컨테이너 사이의 처리 및 운송 비용($/컨테이너)입니다.

샘플 엑셀 시트

빨간색 셀은 특정 경로가 실행 가능한 옵션이 아니므로 고려할 수 없음을 나타냅니다. 해외 공급망을 따라 각 출발 지점에서 각 목적지까지 최적의 배송을 결정하여 최소 총 배송 비용과 각 단계에서 발생하는 총 배송 비용을 결정합니다.

답변1

다음은 Solver에 대한 문제를 설정하는 예입니다. 더 컴팩트하게 만들 수도 있지만 제가 설정한 방식을 사용하면 문제를 쉽게 진단할 수 있습니다.

  1. '데이터' 탭에서 원본 데이터를 사용하세요.
  2. Solver 문제를 설정하기 위해 "Equation"이라는 새 탭을 만듭니다.
  3. A열부터 D열까지의 경로는 가능한 모든 경로 조합을 설정하는 데 사용됩니다. 예를 들어 행 1은 헤더입니다. 행 2는 E1, P1, I1, D1입니다. 행 2는 E1, P1, I1, D2입니다. 등.
  4. E열부터 G열은 유럽에서 미국 항구까지, 미국 항구에서 내륙 항구까지, 내륙 항구에서 유통 센터까지 관련된 비용입니다. 이는 VLOOKUP 및 MATCH를 사용하여 채워집니다. 예를 들어 E2에서는 =VLOOKUP(A2,Data!$A$3:$E$5,MATCH(B2,Data!$A$2:$E$2,0),FALSE); F2에서는 =VLOOKUP(B2,Data!$A$11:$D$14,MATCH(C2,Data!$A$10:$D$10,0),FALSE); G2에서는 =VLOOKUP(C2,Data!$A$20:$F$22,MATCH(D2,Data!$A$19:$F$19,0),FALSE). 이것들은 모두 채워져 있습니다.
  5. H열은 총 단가입니다. 실행 불가능한 경로를 명확하게 하기 위해 해당 경로에 대한 총 비용을 1,000,000으로 설정했습니다. 예를 들어 H2는 입니다 =IF(OR(E2=0,F2=0,G2=0),1000000,SUM(E2:G2)). 이 채워져 있습니다.
  6. I열은 해당 경로를 이동하는 장치(컨테이너)의 수입니다. 이 열은 솔버에 의해 조작됩니다. 모든 행을 1로 초기화했습니다.
  7. J열은 경로의 총 비용입니다. 예를 들어 J2는 =H2*I2채워져 있습니다.
  8. 목적 함수와 제약 조건을 포함하기 위해 동일한 탭에 또 다른 테이블이 구축됩니다. L1부터 O19까지 있습니다. 여기에 있는 계산의 몇 가지 예는 다음과 같습니다. nUnits는 이전 테이블의 합계이므로 E1의 컨테이너 수는 =SUMIFS($I$2:$I$181,$A$2:$A$181,L2), P1의 컨테이너 수는 입니다 =SUMIFS($I$2:$I$181,$B$2:$B$181,L5). maxUnits는 데이터 탭에서 추출되므로 E1의 최대 컨테이너 수는 =VLOOKUP(L2,Data!$A$3:$F$5,6,FALSE), I1의 최대 컨테이너 수는 =HLOOKUP(L9,Data!$B$10:$D$15,6,FALSE)등입니다. 마찬가지로 수요 단위는 데이터 탭에서 추출됩니다.
  9. 실행 불가능한 경로가 선택되지 않도록 추가 제약 조건을 추가했습니다.
  10. 목표는 J열의 합계인 총 비용입니다.

다음은 Solver를 실행하기 전 방정식 탭의 화면 캡처입니다. 여러 행이 숨겨져 있습니다.

여기에 이미지 설명을 입력하세요

솔버 설정은 다음과 같습니다.

  1. 목표 설정: ~이다$M$19
  2. 받는 사람: 최소
  3. 변수 셀을 변경하여:$I$2:$I$181
  4. 제약 없는 변수를 음수가 아닌 것으로 만들기가 선택 취소되었습니다(제약 조건으로 처리됨).
  5. 해결 방법 선택: 진화적입니다. Evolutionary는 정수 제약 조건을 처리할 때 다른 방법보다 훨씬 빠릅니다.
  6. 제약 조건이 적용됩니다: 많은 ...
    • $I$2:$I$181 = integer- 정수개의 컨테이너가 있어야 합니다.
    • $I$2:$I$181 >= 0 - 마이너스 비용이 발생할 수 없음
    • $M$2 <= $N$2각 행에 대해 중복됨 $M$11 <=$N$11- 최대 컨테이너 수를 위반하지 않음
    • $M$12 = $O$12$M$16 = $O$16수요를 충족하기 위해 각 행에 대해 복제됨
    • $M$17 = $N$17- 실행 불가능한 옵션을 사용하지 마십시오.

아래는 솔버 매개변수 대화 상자의 스크린샷입니다.

여기에 이미지 설명을 입력하세요

이 설정을 사용하면 모든 수요가 충족되고 제약 조건을 위반하지 않은 상태에서 $1,661,119.00의 총 비용이 발생합니다. 아래는 제약 조건 테이블의 스크린샷입니다.

여기에 이미지 설명을 입력하세요

아래는 모든 0개 경로가 필터링된 비용 테이블의 스크린샷입니다.

여기에 이미지 설명을 입력하세요

답변2

일회성 답변을 원하는 경우 가장 저렴한 경로의 E2 > P4 > I2 > D2비용은 $1,452입니다.

내가 사용한 방법은 우아하지 않았습니다. 나는 가능한 모든 옵션에 대한 표를 만들고, 여행의 각 구간에 대한 비용을 조회 INDEX하고 , 총 비용 오름차순으로 정렬했습니다. MATCH내 시트는 다음과 같습니다.

스프레드시트

왼쪽의 데이터는 스프레드시트에서 복사한 것입니다. 오른쪽 테이블은 제가 만든 테이블입니다. E, P, I, D 필드 각각에 대해 그룹별로 수동으로 입력했습니다. D1, D2, D3, D4, D5. 그 5개를 복사해서 많이 붙여넣으세요. I1, D 세트에 대해 복사, I2, D 세트에 대해 복사 등. 테이블이 채워질 때까지. $EP 필드의 경우 E(무엇이든)에서 P(무엇이든)까지 이동하는 비용을 결합하여 조회 INDEX했습니다 .MATCH

=1/(1/INDEX($B$3:$E$5,MATCH([@E],$A$3:$A$5,0),MATCH([@P],$B$2:$E$2,0)))

INDEX배열을 가져와 해당 배열 내의 어딘가에서 셀을 반환합니다. 내가 원하는 행을 찾기 위해 MATCH범위 내에서 E 값이 어디에 있는지 찾아보곤 했습니다 A3:A5. 열을 찾기 위해 B2:B4. 이로 인해 E에서 P로 이동하는 데 드는 비용이 발생했습니다. 1/(1/...)전체 항목에 래퍼를 추가하여 이동 구간이 0이면 오류를 반환하여 사용할 수 없음을 나타냅니다.

그런 다음 해당 수식을 $PI 및 $ID 필드에 복사하여 올바른 범위를 참조하도록 각각을 조정했습니다. 마지막으로 총 비용(Total Cost) 필드는 단순히 해당 세 개의 구간을 합친 것입니다. 오름차순으로 정렬해서 가장 저렴한 경로를 찾았습니다. 모든 오류(사용할 수 없는 경로를 사용하는 오류)는 맨 아래로 전송되었습니다.

현재 이용 가능한 경로를 기준으로 가장 저렴한 경로를 선택할 수 있도록 여행의 각 구간에 대해 현재 이동 중인 운송 수단 수를 추적하려는 지속적인 시스템을 원하는 경우 데이터베이스 솔루션을 살펴보는 것이 좋습니다. 또는 정렬된 목록을 사용하여 맨 위에서 시작하여 현재 각 구간을 이용할 수 있는 경로를 찾을 때까지 아래로 작업할 수 있습니다.

관련 정보