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

1차방정식은 알겠는데, 2차 방정식은 잘 생각이 안나 인터넷을 뒤져 보면서 모처럼 공부했네요. 역시 컴퓨터도 수학에 대한 지식이 있어야 한다는 것을 새삼 깨달았습니다.

​

1. 1차 방정식 근 찾기

​

가. 근의 공식

​

y=ax + b에서 y가 0인 경우의 근은 아래와 같이 구할 수 있습니다.

 

나. 엑셀에 적용

​

⑴ 목표값 찾기를 이용한 값 구하기

​

① 엑셀을 실행한 후 새 통합문서에 아래와 같이 작성합니다.

y의 수식은 ax + b 이므로, =b3*a3+c3 가 됩니다.

​

② x와 a,b 값으로 아무거나 넣습니다.

​

③ 데이터 - 가상분석 - 목표값 찾기를 실행한 후

수식 셀로 d3로 지정하는데 이미 D3셀에 커서가 있으므로 수정할 필요는 없고,

찾는 값으로 0, 값을 바꿀 셀로 a3셀을 클릭하고 확인버튼을 누릅니다.

​

④ 그러면 아래와 같이 y값이 0이 되고, x값은 -2.33333이 됩니다.

확인 버튼을 누릅니다.

​

⑤ 근의 공식은 0이 되는 x값을 구하는 것이지만, 다른 값을 구하는 것도 됩니다.

y의 찾는 값을 10으로 하고 목표값 찾기를 하면, 아래와 같이 x값이 1이 됩니다.

​

​

⑵ 수식을 이용한 값 구하기

​

① E2셀에 '근 구하기'라고 입력하고, E3셀에 =-c3/b3라고 입력합니다.

​

② 그런데, x값이 1인데, 근의 공식으로 구한 값이 -2.33333으로 다르죠?

이건 근 찾기는 y값이 0이 되는 경우의 x값을 구하는 것이라 그렇습니다. 아까 y를 0으로 할 경우 x값이 -2.33333이었죠?

​

그렇다면 0이 아닌 경우에도 적용되는 일반적인 수식으로 바꾸려면 아래와 같이 수정하면 됩니다.

​

③ E3셀의 수식을

=(d3-c3)/b3 로 수정합니다. 이제 목표값 찾기로 구한 값과 근 구하기 수식으로 구한 값이 일치하죠?

​

④ y 값 셀인 D3셀의 값을 바꾸면 근 구하기 셀의 값도 달라집니다.

목표값 찾기를 하려면 D3셀이 수식이어야 하는데, 근 구하기 수식을 적용하려면 y값을 바꿔야 합니다. 좀 충돌이 나죠? 그러면 D3셀의 수식을 나중을 위해 F3셀에 복사해 두도록 하겠습니다.

​

ⓐ 수식 복사는 D3셀의 수식을 아래와 같이 마우스로 긁은 다음, Ctrl + C키를 누르거나 홈 메뉴 아래 복사 아이콘을 클릭하면 되며, 그리고 나서 Esc 키를 누르고,

​

ⓑ F3셀에 '(작은 따옴표)를 입력한 다음

​

ⓒ Ctrl + V키를 누르거나 홈 메뉴의 붙여넣기 아이콘 윗 부분을 눌러서 붙여 넣고, 엔터키를 누르면 됩니다.

​

ⓓ 그러면 아래와 같이 수식이 입력되는데, F2셀에 'y셀 수식'이라고 입력해서 내용을 표기합니다.

​

ⓔ 수식이 셀 너비보다 넓으므로 F열과 G열 사이의 경계선을 더블 클릭해서 열 너비를 자동 조절합니다.

​

⑤ D3셀의 값을 20으로 바꿔서 근 구하기 구한 값이 얼마인지 확인해봅니다.

x값이 4.333333 입니다.

​

⑥ 이번에는 수식으로 구한 값이 맞는지 4.333333을 A3셀에 넣어보면 y값이 얼마가 되는지 알아봐야 하는데, D3셀이 수식이 아니기 때문에 y값이 변하지 않습니다.

이번에는 F3셀의 수식중에서 작은 따옴표만 제외한 부분을 복사해서 D3셀에 붙여 넣고 엔터키를 치겠습니다.

​

⑦ A3셀에 4.333333을 입력하니, y값이 20이 나왔습니다.

​

​

2. 2차 방정식 근 찾기

​

가. 근의 공식

​

y=ax^2 + bx + c 에서 y가 0인 경우의 근을 구하는 공식은 아래와 같습니다.

​

나. 엑셀에 적용

​

⑴ 시트 복사 및 수식 변경

​

① 먼저 sheet1 시트를 복사합니다.

​

복사하는 방법은 두 가지가 있는데,

첫번째 방법은 Ctrl 키를 누른 상태에서 sheet1 탭을 눌러 오른쪽으로 끌면 시트 모양에 + 표시가 나타나고 마우스버튼을 떼면 아래와 같이 시트가 복사됩니다.

​

두번째 방법은 sheet1 탭에서 마우스 오른쪽 버튼을 누르면 나오는 메뉴에서 '이동/복사'를 클릭한 후

​

'복사본 만들기' 왼쪽의 체크박스에 체크하고, 확인 버튼을 누르는 것입니다.

​

그러면 아래와 같이 시트가 복사됩니다.

​

② 첫번째 시트 탭을 더블 클릭한 후 '1차 방정식'이라고 시트명을 입력한 후 엔터키를 쳐서 수정하고,

​

두번째 시트인 sheet1(2)를 더블 클릭한 후 '2차 방정식'이라고 시트명을 수정합니다.

​

③ 복사했으므로 2차 방정식 시트의 내용이 1차 방정식과 동일합니다.

​

④ 2차 방정식은 c가 있으므로 C열과 D열 사이에 넣기 위해 D열에서 마우스 오른쪽 버튼을 누른 후 삽입 메뉴를 클릭해서

​

D열을 삽입하고,

​

E3셀의 수식을

=B3*A3^2+C3*A3+D3

로 수정하고,

G3셀에 수식을 문자 형식으로 붙여넣기 위해 E3셀의 수식을 복사한 후, G3셀에 작은 따옴표를 넣고 붙여넣습니다.

​

​

⑤ 근 구하기 셀인 F3셀의 수식도

=(-C3-SQRT(C3^2-4*B3*D3))/(2*B3)

로 수정합니다.

​

⑥ 2차 방정식의 근은 2개가 있으므로

F3셀 채우기 핸들을 F4셀까지 끈 후

c4다음의 -를 +로 바꾸고 엔터키를 쳐서 아래와 같이 입력합니다.

=(-C4+SQRT(C4^2-4*B4*D4))/(2*B4)

​

⑦ E3셀의 채우기 핸들을 끌어 E3셀의 수식을 E4셀에 복사합니다.

​

⑧ B4셀부터 D4셀까지의 값이 B3셀부터 D3셀의 값과 같아야 하므로

B4셀에 =B3 라고 입력하고, 채우기 핸들을 끌어 D4셀까지 복사합니다.

그리고 d3셀에 -10이라고 입력합니다.

​

⑵ 목표값 찾기

​

목표값 찾기는 하나의 값만 찾아주므로, 목표값 찾기를 두번 해야 합니다.

또한 두번 할 때 x값을 작은 값과 큰 값을 넣는 것이 중요합니다.

​

㈎ 실근 구하기

​

① A3셀에 -10, A4셀에 10을 입력합니다.

​

② E3셀에 커서를 넣고, 데이터 - 가상분석 - 목표값 찾기를 합니다.

​

③ 확인 버튼을 누르면 x값과 근 구하기로 구한 값이 일치하는데, y값은 완전한 0은 아니고, 네번째자리까지만 0인 차이가 발생합니다. 기울기가 있는데 구간에 따른 근사값을 구하기 때문에 어쩔 수 없을 듯 합니다.

​

④ 이번에는 E4셀에서 목표값 찾기를 해 보겠습니다.

​

⑤ 마찬가지로 y가 완전한 0이 되지는 않지만, x값은 근의 공식으로 구한 값과 일치합니다.

​

㈏ 중근 구하기

​

아래 수식을 충족할 경우 근이 하나가 되죠?

​

위 수식을 충족시키기 위해 b3셀에 2, c3셀에 4, d3셀에 2를 입력하고,

​

목표값 찾기를 해보겠습니다.

​

① 먼저 E3셀 값이 0이 되는 x 값을 구해 보겠습니다.

​

② 현재값도 0이 아니고, x값도 근의 공식을 이용한 값과 상이합니다.

​

③ E4셀을 대상으로 목표값을 구해보면, 마찬가지로 0에 근접하지도 않고, x값도 근의 공식을 구한 값과 상이합니다.

​

결국은 2개의 실근은 제대로 근사하게 구하는데, 중근은 제대로 못 구한다는 결론에 도달하고, 역시 수학이 필요하다는 것을 느끼게 됩니다.

​

​

㈐ 허근 구하기

​

허근은 #NUM! 에러가 발생합니다.

b2-4ac가 10^2-4*2*20=-60으로 마이너스 이기 때문이고, x축과 교차하는 그래프가 아니기 때문입니다.

​

① E3셀의 목표값 찾기를 하면 아래와 같이 y값이 엄청 큰 숫자가 나옵니다.

​

② E4셀의 목표값도 0이 아니고, x값도 근의 공식을 구한 값과 차이가 있습니다.

 

근의공식.xlsx

0.01MB

다음에는 y값이 0이 아닌경우의 근의 공식 수정하는 것과 일차 방정식과 이차 방정식의 그래프에 대해 살펴보도록 하겠습니다.