엑셀 범위 내에서 원하는 값에 대한 해당 건 찾기, 알아두면 언젠가는 도움되는 Solver 기능

고객으로부터 발송된 샘플에 대한 

금액이 들어왔는데 매출 발생된 인보이스에서

중복된 금액을 차감하고 들어와서 난감한 상황이 되었어요.!

 

물론 중복된 금액은 시스템상 남아있고, 그 금액을 제외한

금액에 대해 처리 해야 하는데, 해당되는 인보이스 찾는데

시간이 너~ 무 걸리더라고요.

 

이번에 어떻게든 방법 찾아서 하자였는데 너무 도움되어

공유합니다.

 

기본 셋팅

File 누르고 Option으로 이동

 

Add-Ins(추가기능) 클릭

관리에서 엑셀 추가기능을 누르고,

Manage 그리고 이동 버튼 클릭합니다.

아래 빨간색 박스 확인

 

아래 Solver Add-in (해찾기 추가) 기능에 체크해주고 확인 버튼 클릭

일단 이렇게 하면 셋팅이 되었습니다.

 

Solver(해찾기) 실행

그럼 이번에는 제가 원하는 값을 찾기 위해 작업 시작

고객으로부터 입금된 금액 중 $2,359.46 에 해당하는 인보이스를

아래 리스트에서 찾으려고 합니다.

여기서부터 더 집중해주세요~

E와 F 셀은 값을 찾기 위해 임의로 추가한 거고.

B1에 있는 입금된 금액은 D셀과 E셀의 곱한거를 전체 더한거로 보시면 됩니다.

E셀에 0,1로 했냐하면 일단 계산이 되냐 안되냐로 보면 될 것 같아요.

(0 = 계산 X, 1= 계산)

하여 해당되는 인보이스일 경우에는 해찾기 기능을 사용하면 자동적으로

이 부분이 변경되어 해당되는 인보이스행은 1로 표기가 되어 쉽게 찾을 수 있어요.

 

해찾기 메뉴는 Data -> Solver로.

 

일단 3가지 조건 시나리오 기입

 

1.      1보다 작거나 같다 범위 선택 그리고 Add

 

2.    0보다 크거나 같다 범위 선택 그리고 Add

 

 

1.      정수로 선택 그리고 OK 버튼 클릭

 

 

이렇게 하면 셋팅된 값이 나오는데, 하기 빨간색 셋팅된 값 참고 부탁드려요

처음 Set objective는 내가 변하고 싶은 셀, 그리고 특정 값은 value of 선택 후

숫자 넣어주시면 됩니다.

이외 아래 네모칸은 위에서 한 3가지 조건이고요.

 

이렇게 하고 Solve를 누르면 되는데 시간이 꽤 걸려요~

 

찾는 시간을 짧게도 해봤는데,

그렇게 하게 되면 정확한 값을 안 찾고 오차가 좀 있더라고요.

 

혹시 오래 걸린다 싶으시면 커피 한잔 하시면 될 듯합니다.

 

혹시 연습하고 싶으신 분들은 아래 파일 참고바랍니다.

원하는 값 찾기 _예제 파일.xlsx

0.02MB