스크랩 엑셀의 기본적인 수식과 함수 사용법

[멋쟁이려]

엑셀의 기본적인 수식과 함수 사용법

엑셀을 시작하는 많은 사람들의 공포(?)의 대상이 되는 “수식과 함수”에 대해서 배워보죠 초등학교는 다 나오셨죠? 그럼 걱정하지 마세요. 수학이 싫어 이과를 포기하고 문과로 갔던 저 같은 사람도 이렇게 사용하고 있잖아요? 그리고 우리는 이미 간단한 수식을 이용해서 통합 문서도 만들어봤잖습니까.

어렵고 복잡한 수식은 중급편에서 다루고 여기서는 수식에 친해질 수 있는 정도까지만 다루게 됩니다. 하지만 이 정도만 알아도 당장 엑셀을 활용하는 데는 충분하실걸요? 수(數)라는 말만 들어도 경기를 일으키시는 분들은 다음 내용을 차분히 읽고 실습해보세요.


1. 수식은 =로 시작한다.
앞에서 데이터의 종류를 살펴볼 때 잠깐 언급했던 부분입니다. 엑셀은 =로 시작하는 데이터는 무조건 수식으로 생각하게 됩니다. 다음 그림을 보세요.

왼쪽에 있는 대로 셀에 입력하면 오른쪽과 같은 결과가 나오게 됩니다. 2+2를 입력하면 결과도 2+2이라고만 나오죠? 그리고 왼쪽으로 정렬이 된걸 보면 현재 2+2은 엑셀이 문자자료로 해석했다는 것을 알 수 있습니다.

반면에 =2+2은 결과가 2로 나왔고 오른쪽으로 정렬이 되었습니다. 수식 자료로 해석했다는 얘기겠죠. 이제 감이 잡히시죠? 수식 입력줄을 봐도 알 수 있구요.

2. 엑셀의 수식은 주소를 이용한다.
앞에서와 같이 셀에 일반 수치자료를 입력해서 바로 계산을 해도 되지만, 효율적인 작업을 위해서 엑셀에서는 셀의 주소를 이용한 계산을 이용하게 됩니다. 나중에 셀의 값이 변경되더라도 셀의 주소를 참조하기 때문에 계산된 값도 자동으로 변경되는 것입니다.

① 다음과 같이 데이터를 입력하고 C2 셀을 선택한 후 “=A1+A2+A3″를 입력합니다. 그 후 Enter키를 치면 A1부터 A3셀이 합해진 결과가 나타납니다.

수식은 셀 주소를 이용하도록 습관을 들인다!

② A3셀 값을 5로 변경해 봅니다. 그러면 C2셀의 합계도 자동으로 변하게 됩니다. 이러한 장점 때문에 엑셀에서는 셀의 주소를 참조해서 계산이 이루어집니다.

셀의 값이 변경하면 자동으로 계산값도 변경된다.

3. 마우스를 이용한 수식의 입력
이제 엑셀에서 수식을 이용하는 감이 잡히셨지요? 그런데 일일이 셀 주소를 입력하기도 번거롭죠? 마우스를 이용해서 수식을 간편하게 만들 수 있습니다.

① 계산식을 입력할 셀을 선택한 후 =를 입력합니다. 마우스로 참조할 셀을 클릭합니다. 그러면 자동으로 선택한 셀의 주소가 입력됩니다.

② 연산자(+, -등)를 키보드로 입력하고 계속 참조하는 셀의 주소를 마우스로 클릭합니다.

연산자는 키보드로 입력

④ 수식이 완성되면 Enter키를 치거나 마우스로 다른 셀을 선택하면(다시 말해서 계산 중인 셀을 떠나면) 계산된 결과 값이 나타납니다.

수식도 어렵지 않죠?. 이제 수식을 만드는데 자신감을 가지셔도 됩니다. 그렇다면 엑셀에서는 어떤 연산자를 제공하며, 각각의 기능은 어떻게 되는지를 알아볼 필요가 있겠네요.

엑셀에서 사용되는 연산자(Operator)
연산자는 +와 -같이 연산자 계산의 종류를 지정하는 기호로서 엑셀에서는 다음과 같은 종류의 연산자가 있습니다.

• 산술연산자 : 기본적인 산술 연산을 수행
• 논리연산자 : 두 값을 비교하여 TRUE나 FALSE로 결과를 되돌림
• 텍스트 연산자 : 두 텍스트를 연결하여 한 텍스트 값으로 만듬
• 참조영역 연산자 : 셀이나 셀 범위에 대한 두 참조영역을 한 참조 영역으로 만듬
  

1) 산술 연산자
실제 계산을 할 때는 산술연산자가 많이 사용되고, 나머지는 수식을 응용할 때 주로 사용됩니다. 산술연산자는 독자들이 초등학교 시절 배운 더하기, 빼기 등과 동일합니다. 하나씩 예를 들어 알아보도록 할까요?

엑셀의 다양한 산술 연산자

다른 것은 모두 같은데, ^만 차이가 있습니다. 2^10은 2의 10승을 말하는 것이지요. 2를 10번 곱한다는 뜻입니다.

또한 %는 100을 1이라고 생각하고 이를 기준으로 한 백분율이기 때문에 10%는 0.1로 표시됩니다. 전혀 어렵지 않죠? 하지만 이러한 기본을 잘 알고 있어야만 뒤에서 배울 복잡한 수식도 쉽게 할 수 있는 것입니다. 이점 명심하시고 열심히 연습하시길 바랍니다.

2) 비교 연산자
비교 연산자는 두 값을 비교할 때 사용하는 것으로 일반적인 식에서는 사용되지 않으며 나중에 여러 가지 함수들과 조합해서 사용됩니다. 그 결과는 참(True)과 거짓(False)으로 나타나게 됩니다.

• = 같음
• <> 같지 않음
• <= 작거나 같음
• < 작음
• >= 크거나 같음
• > 큼

다음은 비교 연산자를 사용한 예입니다.

엑셀의 비교 연산자

3) 텍스트 연산자 &(앰퍼센드)
텍스트 연산자는 떨어져 있는 텍스트를 연결할 때 사용하며 수식의 응용에서 많이 쓰이는 기능입니다. 즉 “남” & “한강” 을 “남한강”으로 묶어준다는 뜻입니다. 다음은 텍스트 연산자를 사용해서 텍스트를 연결한 예입니다. 수식에서 “ ”로 “의”를 추가한 것도 눈여겨 봐두시길 바랍니다.

4) 참조 연산자
참조 연산자는 계산에 필요한 셀 범위를 결합할 때 사용하는 것으로 주로 중급편에서 배울 함수와 연결되어집니다.

: 두 참조를 포함하여 두 참조 사이에 있는 셀을 모두 한 참조로 만드는 범위 연산자 B5:B15

, 여러 참조를 한 참조로 만드는 결합 연산자 SUM(B5:B15,D5:D15)

엑셀에서의 연산 순서
엑셀은 수식에 사용된 각 연산자의 특정 순서에 따라 수식을 왼쪽에서 오른쪽으로 계산하며, 괄호를 사용하여 연산 순서를 변경할 수 있습니다. 괄호는 항상 먼저 계산되며, 나머지 순서는 다음 연산순서에 따라 차례대로 진행됩니다.

연산순위 연산자
1 *와 /
2 +와 -
3 &
4 %
5 = < > <= >= <>
6 -
7 ^
8 :
9 (단일 공백)
10 ,(쉼표)

연산 순서는 우리가 아~~주 어릴 때 다 배운 기본적인 상식만 가지고 있으면 됩니다.

엑셀의 기본 함수 사용법
함수란 지정된 순서나 구조에 따라 인수라는 특정 값을 사용하여 계산하는 미리 정의된 수식입니다. 엑셀에는 수백 개의 함수가 있습니다. 보통 엑셀을 시작한 후 함수 때문에 중도에 포기하는 사람들이 많은데, 함수는 결코 어려운 것이 아닙니다.

쉽게 생각해서 함수란 여러 가지 기능을 가진 수식들을 하나로 모아 놓은 것이 라고 생각하면 됩니다. 예를 들어, SUM이라고 하는 함수는 선택된 셀들의 합계를 구하는 함수고, PMT라는 함수는 이율, 대출 기간, 대출 원금 등에 따라 상환금을 계산하는 역할을 합니다.

함수를 사용하려면 특정 구조를 따라야 하는데, 그 구조는 다음에서 보는 바와 같이 함수 이름, 왼쪽 괄호, 쉼표로 구분된 함수의 인수, 그리고 오른쪽 괄호의 순서입니다. 특히 엑셀 2007부터는 함수 첫 글자만 치면 관련 함수 목록이 표시되고, 함수를 선택하면 셀에서 편하게 사용법을 미리 표시해줘서 더 쉽게 함수를 사용할 수 있습니다.

셀에서 바로 함수 목록을 선택할 수 있다.

함수를 선택하면 셀에 간단한 사용법이 표시가 되어서 사용자의 입력을 도와줍니다.

함수를 선택하면 간단한 사용법이 셀에 표시된다.

태어나서 함수라는 것을 처음 다루어보는 분들은 잘 보시기 바랍니다. 아무리 복잡한 함수식이라도 그 원리는 같습니다.

백문이 불여일클릭! 함수를 이용해서 바로 작업을 해보도록 하죠.

1. SUM 함수로 자동 합계 구하기 - 함수마법사

SUM 함수는 지정된 셀 범위의 합계를 구해주는 함수로서 가장 흔하게 사용되는 함수중의 하나입니다.

① 다음과 같은 간단한 성적표 데이터를 만듭니다. 지금부터 하려고 하는 작업은 학생별로 합계를 구하는 것입니다. 갑돌이의 합계를 내기 위해서 =B3+C3+D3 라고 입력해도 되지만 과목의 수가 많다든가 사람이 많을 경우에 일일이 이렇게 수식을 입력하는 것은 비효율적인 일이겠지요.

이런 경우에 함수를 사용하는 겁니다. 먼저 합계를 구하려는 셀인 E3셀을 선택합니다. 데이터를 입력할 셀을 먼저 선택하는 것은 엑셀에서의 기본이므로 꼭 명심하기 바랍니다. 그 다음 수식입력줄 옆의 함수 삽입 단추(Fx)를 클릭합니다.

② 함수마법사가 실행되었습니다. 함수 마법사에는 엑셀에서 사용 가능한 모든 함수들이 종류별로 구분되어져 있습니다. “함수 범주” 항목에서 “수학/삼각”을 선택합니다. 그 후 오른쪽 “함수 이름” 목록에서 SUM을 클릭합니다.

③ 화면 왼쪽에 함수 편집상자가 나타납니다. 함수 편집 상자에는 선택한 함수에 필요한 인수들을 입력하게 됩니다.

선택한 함수의 필요한 인수를 선택하는 함수 인수 대화상자

④ 인수를 입력할 때는 마우스로 셀 범위를 선택하는 것이 효율적입니다. 함수 편집 상자의 “참조 범위 단추”를 클릭하면 범위를 선택할 수 있는 창만 남고 모두 사라지게 되는데 여기에서 범위를 선택하면 됩니다.

참고로 워크시트의 전체적인 데이터를 보면서 작업할 때는 함수 편집 상자를 다른 곳으로 이동시킨 다음 사용하는 것이 효율적인 경우가 많습니다. 함수 편집 상자를 드래그해서 원본 데이터가 보이도록 이동한 후 원본데이터의 범위를 마우스로 선택합니다. 범위를 선택한 부분은 점선으로 깜빡이게 됩니다.

마우스를 드래그해서 범위를 잡아준다.

⑤ 범위 선택이 끝나면 다시 함수 편집 상자가 나타납니다. 인수가 정확하게 입력되었으면 [확인] 버튼을 클릭합니다. 대화상자에서 미리 계산된 결과 값을 볼 수 있는데 인수가 잘못 선택되었거나 수정할 필요가 있으면 현재 상태에서 수정하면 됩니다.

함수 인수 상자에서 미리 결과 값을 볼 수 있다.

⑥ 함수의 결과 값이 셀에 입력되었습니다.

함수도 이렇게 쉽게 사용할 수 있습니다. 각 함수마다 필요한 인수만 차이가 있을 뿐이죠. 엑셀 사용이 능숙해지면 함수 마법사를 사용하지 않고 셀에 직접 수식을 입력해서 빠르게 작업을 완료할 수 있답니다.

2. ∑버튼을 이용한 합계 구하기
SUM 함수는 자주 사용되기 때문에 도구모음이 기본적으로 등록이 되어 있습니다.

① 합계를 구할 셀을 선택합니다. 그 후 도구모음의 ∑(시그마)버튼을 클릭합니다.

② 다음 그림처럼 자동으로 범위가 선택됩니다. 만약 범위가 잘 못 선택되었으면 마우스로 다시 선택하면 됩니다.

③ 범위가 올바로 선택되었으면 키를 칩니다. 셀에 합계가 구해집니다.

3. AVERAGE 함수로 평균 구하기 - 함수식
이번에는 AVERAGE 함수를 사용하는 방법을 알아보겠습니다. SUM 함수를 통해서 함수에 대한 공포심(?)을 없앴기 때문에 한결 쉽게 할 수 있을 것입니다. AVERAGE 함수 역시 선택된 범위의 평균을 구하는 것이므로 필요한 인수는 셀 범위뿐입니다. 이번에는 실제로 함수식을 입력해서 값을 구해보도록 하겠습니다.

AVERAGE 함수의 형식은 다음과 같으며 인수는 최대 30개까지 사용할 수 있습니다.

= AVERAGE(number1,number2, …)

① 평균을 구할 셀을 선택한 다음 “=average(”를 입력합니다. 그 후 마우스로 인수의 범위를 선택합니다.

② ENTER키를 치면 셀에 평균값이 구해집니다.

함수가 점점 쉬워지죠? 엑셀에서 함수를 이용하는 방법은 모두 동일합니다. 다만 각 함수의 특징에 따라서 필요한 인수와 형식이 다른 것이지요. 실제로 실무에서 많이 사용되는 것은 20여 개에 불과합니다.