엑셀배우기 - 수식(2)

엑셀의수식(2)

1. F9키와 Ctrl + Z키의 사용

엑셀에서 수식을 분석해야 하는 경우가 많은데, 이 때 F9키를 어떻게 활용할줄 아는 것이 매우 중요합니다.

F9키는 내용 일부의 결과 값을 미리 볼 수 있게 해주는 기능을 가지고 있습니다.

수식 내용 중에 관심 부분을 선택하고 F9키를 누르면 그 선택 범위에 계산된 값을 미리 볼 수 있고,

다시 돌아가려면 Ctrl + Z키를 누르고 그 상태에서 다시 Ctrl + Z키를 누르면 또 계산된 값을 보여줍니다.

F9키 사용시 참고해야할 점들이 있는데요

1. Ctrl + Z키를 누르면 결과 값과 이전 내용을 교대로 보여준다.

2. 선택한 부분을 F9키로 표시하기에 내용이 너무 길면 취소된다.

3. 그림1처럼 D:D부분을 블록설정하고 F9키를 누르게되면 수식이 너무 길다고 오류 표시가 뜬다.

(이는 수식을 D:D처럼 D열전체를 선택할경우 엑셀행의 가장 마지막인 1,048,576까지의 행 전체를 선택하기 때문에 그림1의 오른쪽과 같은 오류가 뜨는걸로 보입니다)

그림1. B:B블록설정 후 F9클릭시 문자수 초과오류 발생

2. 다른시트값 가져오기

현재시트에서 다른시트를 참조하여 수식을 만드는 것도 가능합니다.

현재 이용중인 시트가 너무 복잡하거나, 내가 Sheet2에 입력하는 수치에 따라서 Sheet1의 값이 자동으로 변하게 하는 용도 등으로 사용할 때 유용합니다.

 

그림2.  Sheet2의 수식으로 Sheet 3의 숫자셀개수구하기

위의 그림2처럼 Sheet2에서 수식을이용해 시Sheet3의 내용을 끌어오는게 가능합니다. 수식의 '!'가 의문이신분이 있으실거같아 저도 확인을 해보았는데 다른시트를 참조하면 '!'가 붙는걸로 보입니다.

※ 다른 시트로 이동하는 단축키는 Ctrl + Pange Down이나 Ctrl + Page Up을 누르시면 시트이동이 용이합니다.

다른 시트가 참조되는 수식을 작성하신 후에 참조되는 시트 이름을 수정하거나 그 시트가 이동될 경우 수식 내용은 자동으로 수정되어 결과 값은 유지되지만, 참조 시트 삭제시에 그림3처럼 참조 오류 값인 '#REF!'을 표시하니 참고하시기 바랍니다.

그림3. Sheet3삭제시 Sheet2의 수식값에 #REF오류발생

3. 다른 파일의 값 가져오기

다른 시트의 값을 가져올 수 있는 것 처럼 다른 통합문서의 값도 참조가 가능합니다.

통합문서란 엑셀 파일을 뜻하는데 대부분의 파일은 확장자를 가집니다. 보통 xlsx, xlsm, xlsb, xls등의 확장자를 가집니다.

참고용으로 확장자별 파일 형식을 말씀드리자면

1. xlsx : 일반 통합문서

2. xlsm : 매크로가 포함된 통합문서

3. xlsb : 실행 파일 형식의 바이너리(이진) 통합문서

4. xls : 엑셀2003 이하 버전 통합문서로 엑셀 2007에서 열리면 엑셀 창 제목 표시줄에 '[호환 모드]'가 표시됨

이외의 형식을 알고싶으시다면 엑셀에서 F12(다른이름으로 저장키)를 누르고 파일이름 밑 파일형식을 눌러보시면 더 많은 파일형식을 확인하실 수 있습니다.

아래 그림6처럼 다른통합문서의 파일을 참조할경우에 수식이 완성되면 셀 범위에 달러($)기호가 붙어서 나오는데, 기본적으로 다른 파일 참조 수식 작성시에 달러($)가 생깁니다.

그림4. 통합문서 $기호 표시(A1셀의 15는 다른통합문서의 1~5를 더한값)

또 수식에 참조파일이 닫히게 되면 수식내용에 그 파일의 전체 경로까지 나타납니다.

다른 파일이 참조되는 수식 작성 후, 참조되는 원본 파일의 이름을 수정하거나 파일을 이동, 삭제하면 수식셀에 바로 오류가 뜨지는 않지만 그 셀을 참조하여 다른 셀에 수식을 만들거나 그 파일을 닫고 열면 외부파일 참조 오류가 나타나게 됩니다. 엑셀 2007이나 2010이상의 버젼에서 다른 파일을 참조하는 수식이 들어간 파일을 닫고 열었을 때는 '보안 경고 링크의 자동 업데이트를 사용할 수 없도록 설정했습니다'라는 메시지가 뜨게 되는데 그 메세지 표시줄에서 '이 콘텐츠 사용'을 누르게 되면 수식 결과가 갱신되게 됩니다.

참조되는 파일을 열어야만 수식 결과가 제대로 나오는 함수들이 있는데

1. 수식에서 참조되는 파일을 닫으면 '#VALUE!'를 나타내는 함수들

SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, OFFSET, COUNTBLANK

2. 수식에서 참조되는 파일을 닫으면 '#REF!'를 내는 함수 

INDIRECT

위의 함수들이 있으니 참고해주시면 감사하겠습니다.

※ 오류값을 내는 경우에는 참조파일을 닫는다고 바로 오류 값이 나는게 아니고 그 수식 셀을 새로 고침(더블클릭하고 다른 셀 클릭)하거나 파일을 닫았다가 다시 열면 오류가 발생하게 됩니다.