엑셀 목표값 찾기와 방정식 근 구하기(1)

엑셀의 값 찾기 메뉴는 수학 문제 풀 때 답을 잘 모르겠으면 거꾸로 4지 선다형에서 제시된 값을 대입해서 맞는지 알아보는 것처럼, 컴퓨터가 여러가지 수를 임의로 대입해서 가장 근사한 값을 찾아가는 과정입니다.

1. 엑셀 목표값 찾기

가. 메뉴

데이터 메뉴 - 가상분석 아래에 세가지 소메뉴가 있는데, 가운데에 '목표값 찾기'라고 되어 있습니다.

나. 사례

⑴ 목표 원리금 달성을 위한 이자율 또는 원금 계산

복리라는 것이 없지만 복리계산식을 적용해서 원리금이 10만원이 되도록 하는 이자율 또는 10년동안 납입해야 할 원금을 구하기 위해 목표값 찾기 기능을 이용할 수 있습니다.

㈎ 이자율 구하기

① 데이터 - 가상분석 - 목표값 찾기 메뉴를 누릅니다.

② 아래와 같이 수식 셀은 C2셀, 찾는 값은 10만원이므로 100000이라고 입력하고, 값을 바꿀 셀은 이자율이므로 A2셀을 클릭하고, 확인 버튼을 누릅니다.

③ 그러면 아래와 같이 목표값과 현재값으로 10만원이 표시되고, 이자율이 0.05에서 0.23618로 변경되었습니다. 다시 확인버튼을 눌러 확정합니다. 취소버튼을 누르면 이자율이 0.05로 돌아갑니다.

㈏ 원금 구하기

① 위와 방식은 같은데 값을 바꿀 셀이 A2에서B2로만 변경되고, 이자율을 0.05로 바꿔야 합니다.

② 아래와 같이 목표값과 현재값이 일치하고, 원금이 61,391로 변경되었습니다. 당연히 이자율이 낮아지니 원금을 많이 넣어야 하겠지요.

⑵ 복잡한 산식의 목표값 구하기

연봉 6천만원과 5천만원을 맞추기 위해 시급을 얼마로 해야 하는지 구하려고 합니다. 통상시급과 기본급과 연장수당 산식은 아래와 같고, 자격수당도 아래 금액으로 정해져 있다고 가정합니다.

파일은 아래와 같습니다.

사무직연봉.xlsx

0.01MB

 
현재와 변경의 연봉 차액을 0으로 만들어도 되고, 변경된 연봉의 합계를 N6셀의 금액으로 해도 됩니다.

㈎ 차액을 0으로 만드는 시급 구하기
아래와 같이 방정식 풀 듯이 수식으로 합계금액이 나오는 시급을 구할 수도 있습니다. 굉장히 어려워 보이죠? 그러니 목표값 찾기로 해보겠습니다.

① 수식셀으로 o6셀, 값으로 0, 값을 바꿀 셀로 i6셀을 지정하고 확인 버튼을 누르면,

② 아래와 같이 '셀에 값이 있어야 합니다'란 에러 메시지가 나옵니다. 이는 시급이 숫자로 되어 있어야 하는데, 수식으로 되어 있어서 그런 것입니다.

③ 취소 버튼을 누른 후 i6셀을 20,000으로 수정하고, 다시 목표값 찾기를 합니다.

④ 아래와 같이 시급이 20,446으로 변경되고, 차액이 0으로 됩니다. 확인 버튼을 누릅니다.

⑤ 이번에는 O7셀의 차액을 0으로 만들기 위한 i7셀의 값을 구해 보겠습니다. 먼저 i7셀의 시급을 15,000으로 수정합니다.

⑥ 그런데 이번에는 목표값과 현재값이 일치하지 않고 현재값이 20으로 나옵니다. 이것은 통상시급 등 수식에 round, rounddown 등 반올림 관련 함수가 있어 소숫점이하를 조정할 수 없기 때문입니다. 어쨌든 시급이 17,120원으로 수식으로 구한 값과 일치합니다.

⑦ 통상시급, 기본급, 연장수당의 round와 rounddown을 제거하니 차액이 20에서 17로 바뀌었습니다. 반올림 또는 절사때문에 차이가 발생하는 것입니다.

⑧ 다시 목표값 찾기를 해보겠습니다. 목표값과 현재값이 0으로 일치합니다. 그러나, 시급은 17,120으로 변함이 없습니다. 시급의 소숫점이하가 바뀌어서 그렇습니다.

⑨ j7셀에서 '자릿수 늘림' 아이콘을 누르니 소숫점이하가 숫자가 표시되어 0이 아님을 알 수 있습니다.

⑩ i7셀을 확인해보니 17119.7346672466으로 소숫점이하 10자리까지 있습니다.

㈏ 합계액을 일치시키는 시급 구하기

① 데이터 - 가상분석 - 목표값 찾기 메뉴를 누른 후 수식 셀을 이번에는 N7셀을 지정하고, 찾는 값으로는 현재 값 4,316,660을 입력하고, 값을 바꿀 셀로 i7셀을 지정하고, 확인 버튼을 누릅니다.

② 목표값과 현재값이 일치하고, 시급도 변함없이 17,120원으로 표시됩니다. 당연한 결과죠?

2. 매크로로를 이용하여 '목표값 찾기' 반복 실행

위에서 "왜 목표값 찾기를 따로 두번 하지?" 라는 의문을 가지셨을 겁니다.
만약 목표값 찾기시 수식 셀과 바꿀 셀을 두개를 지정하고 확인 버튼을 누르면

아래와 같이 "셀을 하나만 참조해야 합니다"라고 에러 메시지가 나옵니다.

따라서, 매크로를 이용해 반복 수행하도록 해야 합니다.
매크로 작성이 어렵다면 컨닝, 다시 말해 매크로 기록을 하면 됩니다.

가. 매크로 기록

① 개발도구 - 매크로 기록을 누릅니다.

② 매크로 이름이 '매크로1'이라고 나오는데, '목표값찾기'로 수정하고 확인 버튼을 누릅니다. '목표값 찾기'식으로 중간에 공백이 있으면 안됩니다.

③ 목표값 찾기를 실행합니다.

④ 확인 버튼을 누릅니다.

⑤ 이제 매크로 기록을 마쳤으니 개발도구 - 기록 중지를 클릭합니다.

⑥ 코드를 수정하기 위해 개발도구 - Visual Basic 아이콘을 누르거나, Alt + F11 단축키를 누릅니다.

⑦ 사무직연봉.xlsx 아래 모듈이 닫혀 있으므로, 모듈 왼쪽 + 모양을 클릭합니다.

⑧ 그러면 아래와 같이 모듈 아래 Module이 표시됩니다.

⑨ Module1을 더블 클릭하면 오른쪽 코드 창에 '매크로 기록'으로 기록한 매크로 문이 보입니다.

나. 매크로 수정

① Application.CutCopyMode = False 는 불필요하므로 마우스로 범위를 잡은 후 Del키를 눌러 지웁니다.

② 반복을 해야 하므로 for ~ next문을 이용합니다.
아래 구문을 보니 o6와 i6중 6만 7로 변하도록 하면 됩니다.
Range("O6").GoalSeek Goal:=0, ChangingCell:=Range("I6")

range("o6")는 변수 i를 사용하면 range("o"&i)라고 표현합니다. i6도 마찬가지로 range("i"&i) 라고 쓰면 되겠죠?

③ 그리고, option explicit 라고 맨 위에 있으므로, 변수를 명시적으로 선언해야 합니다. 따라서,
dim i as integer 를 추가해야 합니다. i란 변수를 선언하고, 데이터 타입을 정수로 정하는 것입니다.
그러면 아래와 같이 완성됩니다.

dim i as integer for i = 6 to 7 Range("O" & i).GoalSeek Goal:=0, ChangingCell:=Range("I" & i) next

다. 매크로 실행

① 시급이 바뀌었는지 확인해야 하므로, 엑셀로 돌아오기 위해 엑셀 아이콘 또는 Alt + F11키를 누릅니다. VBA 상태로 가는 것도 엑셀로 돌아가는 것도 Alt + F11키입니다.

② 시급을 20,000과 15,000으로 수정합니다.

③ 개발도구 - 매크로 메뉴를 누른 후 매크로 목록에서 '목표값찾기'를 찾아 실행합니다.

④ 아래와 같이 시급 2개가 모두 바뀐 것을 알 수 있습니다.

⑤ 이제 매크로가 들어갔으므로 일반 통합문서로는 저장하지 못하므로, 파일 - '다른이름으로 저장'을 선택한 다음, 파일 타입을 '매크로 사용 통합문서'로 변경한 후 저장버튼을 누릅니다.

사무직연봉(VBA).xlsm

0.02MB

 
방정식 근 찾기는 따로 다루도록 하겠습니다.