Excel & IT Info

아이엑셀러 닷컴, 엑셀러TV

Excel

[Excel 입문] 10. 수식과 함수(1) ㅡ 엑셀 수식 작성 방법

권현욱(엑셀러) 2024. 1. 31. 10:00
반응형

들어가며

누가 뭐라고 해도 Excel을 사용하는 가장 큰 이유 중 하나는 무언가를 "계산"하기 위해서입니다. 이 목적을 달성하게 해 주는 것이 바로 수식과 함수입니다. Excel에는 수많은 종류의 함수가 있어서 우리가 명령을 내려주기만을 고대하고 있습니다. 여러분의 엑셀 실력을 획기적으로 높여 줄 그들과 만날 준비가 되셨나요? 출발합니다.

 

(이미지: 아이엑셀러 닷컴)

수식과 함수, 같은가 다른가

수식(Formula)은 엑셀에서 사용되는 계산식을 말합니다. 수식은 일반적으로 셀(cell)에 입력되며, 수학적 연산이나 셀 참조를 포함할 수 있습니다(예: =A1+B1).

 

함수(Function)라는 말을 들어보셨나요?(수학 시간에 배운 그 함수와는 관련이 없으니 안심(?)하셔도 됩니다). 함수는 엑셀에 내장된 특별한 종류의 수식입니다. 함수는 특정한 작업을 수행하기 위해 사용되며, 함수 이름과 괄호 안에 '인수'를 전달하여 작동시킵니다(예: =SUM(A1:A10)).

 

정리하자면, 함수는 수식의 일부로 사용될 수 있으며, 수식 내에서 특정 작업을 수행하기 위한 도구로 활용됩니다.

 [참고] 수식과 함수가 같은 지 다른 지 따지는 것은 사실 큰 의미는 없습니다. '그런 게 있군' 정도로 가볍게 보고 넘어가세요. 이번 시간이 끝날 무렵이면 저절로 터득하게 됩니다.

 

수식 작성하는 3단계

엑셀에서 수식을 작성하는 방법은 심플합니다.

(1) 수식을 입력할 셀을 선택한다.
(2) 등호(=)를 입력한다.
(3) 셀 주소를 연산자(+, -, *, /)와 함께 입력한다(또는 함수 입력).

 

엑셀에게 "이제부터 수식을 입력할 테니까 준비해"라고 알려주는 표시가 등호(=)입니다. 다음과 같은 표가 있습니다.

 

(이미지: 아이엑셀러 닷컴)

 

'금액'은 '수량'에 '단가'를 곱해서 구합니다. 수식을 작성하는 3단계에 따라서 진행해 볼까요.

 

(1) 수식을 작성할 셀(E3)을 선택하고,

(2) 등호(=)를 입력한 다음,

(3) 수량 셀 주소(C3)을 단가 셀 주소(D3)를 '*'로 연결해 주었습니다.

 

셀 주소는 손으로 직접 입력해도 되지만 마우스로 클릭하면 자동으로 입력됩니다. 여기서 *는 곱하기를 수행하며, 연산자(operator)라고 부릅니다. '=C3*D3'이라고 입력하고 Enter를 누르면 결과가 구해집니다. 금액이 구해진 E3 셀을 선택하면 셀 오른쪽 하단에 조그마한 사각형 점이 나타납니다. 이것을 '채우기 핸들'이라고 합니다. 채우기 핸들을 마우스 왼쪽 버튼을 누른 채 아래로 드래그하면 나머지 품목에 대한 수식이 자동으로 채워집니다(혹은 채우기 핸들을 더블 클릭해도 됩니다).

 

(이미지: 아이엑셀러 닷컴)

 

수량이나 단가를 수정하면 금액이 자동으로 수정됩니다. 엑셀의 셀(cell)은 하나하나가 고성능 계산기입니다.

 

셀 참조 방식 이해 

이번에는 금액 오른쪽에 구성비를 구하겠습니다. 구성비는 각 제품이 "전체 합계 중에서 차지하는 비율"을 말합니다. 따라서 구성비를 구하려면 합계가 필요합니다. 함수를 이용하면 간단히 처리할 수 있지만 아직 함수를 배우지 않았으니 과격한(?) 방식으로 해 보죠.

 

합계를 구할 E12 셀을 선택한 다음, 등호와 + 연산자를 이용해서 각 셀을 하나씩 더하는 수식을 작성합니다.

=E3+E4+E5+E6+E7+E8+E9+E10+E11

 

(이미지: 아이엑셀러 닷컴)

 

 수식을 작성하고 Enter 키를 누르면 결과가 구해집니다. 셀에 입력된 수식은 워크시트 상단의 상자에서 확인할 수 있습니다. 이곳을 '수식 입력줄(Formula Bar)'이라고 하며, 중요한 공간입니다.

 

(이미지: 아이엑셀러 닷컴)

 

이제 구성비를 구할 수 있게 되었습니다. 첫 번째 품목(A0001)의 구성비는 'A0001 품목의 금액/합계'로 구할 수 있습니다. F3 셀을 선택하고 '=E3/E12'라고 입력한 다음 Enter를 누르면 결과가 나타납니다. [홈] 탭 - [표시 형식] 그룹에서 [백분율 스타일]과 [자릿수 늘림] 명령을 한 번씩 눌러주면 소수점 한 자리까지 백분율로 표시됩니다.

 

(이미지: 아이엑셀러 닷컴)

 

 F3 셀을 선택하고 채우기 핸들을 아래로 드래그(또는 더블 클릭)합니다.

 

(이미지: 아이엑셀러 닷컴)

 

 수식이 아래로 복사되는 것까지는 좋은데... 어라? 뭔가 이상합니다. F4 셀부터 아래쪽으로 해괴한(?) 표시가 나타납니다(#DIV/0!). F4 셀을 선택하고 수식 입력줄을 보니 '=E4/E13'이라고 되어 있습니다. E4 셀은 맞게 지정되었지만 E13 셀에는 아무 값도 없습니다. 숫자를 빈 셀(즉 Zero)로 나누라고 했으니까 엑셀의 계산 엔진이 "그게 무슨 소리야. 숫자를 0으로 어떻게 나눠?"라는 항의(?) 표시가 '#DIV/0!'입니다(이것을 'Divide by Zero 오류'라고 합니다).

 

그럼 어떻게 하면 될까요? F열에 있는 각각의 수식에서 E13, E14, E15라고 되어 있는 부분을 모두 E13으로 일일이 바꿔주면 될까요? 그렇게 해도 말릴 사람은 없겠지만 힘든 일이 되겠죠? F3 셀을 선택하고 F2 키를 누르면 수식 편집 상태가 됩니다. 수식 입력줄로 가서 12 앞에 달러 표시($)를 붙인 다음, Enter를 누릅니다.

여기서 $가 뜻하는 것은 "수식을 복사해도 $ 뒤에 있는 부분은 변하지 않도록 해"라는 메시지를 엑셀의 계산 엔진에게 알려주는 겁니다.

 

(이미지: 아이엑셀러 닷컴)

 

F3 셀의 수식을 아래로 복사합니다. 이번에는 제대로 구성비가 계산되었습니다.

 

(이미지: 아이엑셀러 닷컴)

 

 [TIP] 셀 주소 앞에 $를 손으로 직접 입력할 수도 있지만 단축키를 사용하면 편리합니다. 수식 입력줄에서 $를 삽입할 곳(앞에서는 E12)을 클릭한 다음, F4 키를 누르면 $E$12가 됩니다. 이 상태에서 F4를 한 번씩 누를 때마다 E$12 → $E12 → E12로 바뀝니다.

 

 이해가 되셨나요? $가 붙느냐 아니냐에 따라 수식을 복사했을 때 셀 주소가 바뀌기도 하고 그렇지 않기도 합니다. $가 없는 셀 주소(E12)를 상대 주소, 하나만 붙으면($E12, E$12) 혼합 주소, 둘 다 붙으면($E$12) 절대 주소라고 합니다. 셀 참조 방식은 엑셀에서 매우매우 중요한 개념입니다. 반드시 이해하고 넘어가세요.

Excel 기본 함수 5가지

앞에서 각 품목의 합계를 구할 때 '=E3+E4+E5+E6+E7+E8+E9+E10+E11'이라는 과격한 방법을 사용했습니다. 품목 수가 9개 밖에 되지 않으니 그나마 다행이지 품목이 1000개쯤 된다면 도저히 인간이 할 짓(?)이 아닙니다. 이런 경우 함수를 이용하면 간단하게 처리할 수 있습니다. 엑셀을 사용한다면 반드시 알아야 할, 가장 기본이 되는 함수를 5가지를 소개합니다.

 

함수를 사용하는 4단계

'수식을 작성하는 3단계'와 마찬가지로 함수를 사용할 때에도 3단계로 이해할 수 있습니다.

(1) 수식을 입력할 셀을 선택하고 등호(=)를 입력한다.
(2) 함수 이름과 괄호 ()를 입력한다.
(3) 괄호 안에 인수를 입력한다.

 

'인수(arugument)'는 함수가 사용자로부터 입력을 받아서 계산할 값이나 범위 등을 말합니다. 여러 개의 인수가 사용될 수 있으며, 인수와 인수는 콤마(,)나 콜론(:)으로 구분할 수 있습니다. 무슨 말인지는 아래에서 나옵니다.

 

1. SUM 함수

SUM은 합계를 구할 때 사용하는 함수입니다. 아마 엑셀에서 함수를 배울 때 가장 먼저 배우는 함수 중 하나입니다. 다음 세 가지 방식은 같은 결과를 구해줍니다. 

(1) =E3+E4+E5+E6+E7+E8+E9+E10+E11
(2) =SUM(E3,E4,E5,E6,E7,E8,E9,E10,E11)
(3) =SUM(E3:E11)

 

두 번째 수식은 =sum(를 입력하고 Ctrl 키를 누른 채 해당 셀을 클릭하여 작성한 수식입니다. 세 번째 것은 =sum(를 입력하고 마우스로 E3:E11 영역을 드래그 했습니다. 연속된 범위가 아닌 경우에는 (2), 연속된 영역이라면 (3)처럼 사용하면 편리합니다.

 

2. AVERAGE 함수

AVERAGE는 평균을 구하는 함수입니다. 예를 들어 E3:E11 영역의 평균을 구하려면 이렇게 합니다.

=AVERAGE(E3:E11)

 

사용하는 방식은 SUM의 그것과 같습니다.

 

함수에 어느 정도 익숙하다면 직접 작성하는 것이 빠르고 편리할 수 있습니다. 그렇지 않은 경우라면 함수 대화상자를 사용하는 것이 좀 더 쉽습니다. [함수 마법사] 대화상자를 통해 최대값과 최소값을 구해보도록 하죠.

 

3. MIN, MAX

영역(또는 값)에서 가장 작거나 큰 값을 구하고자 할 때 MIN 또는 MAX 함수를 사용합니다. 먼저 최대값을 구해봅니다.

 

(1) 결과를 표시할 셀(H3)을 선택하고 수식 입력줄 왼쪽에 있는 [함수 삽입] 아이콘을 클릭합니다.

 

(2) [함수 마법사] 대화상자에서 [범주] - [통계]를 선택합니다. [함수 선택]에 나타나는 함수 중에서 MAX를 선택하고 [확인] 버튼을 누릅니다.

 

(이미지: 아이엑셀러 닷컴)

 

(3) [함수 인수] 대화상자가 나타납니다. 최대값을 구할 범위가 E3:E11이므로 마우스를 이용하여 범위로 지정합니다.

 

(이미지: 아이엑셀러 닷컴)

 

(4) [확인]을 클릭하면 결과가 구해집니다.

 

(이미지: 아이엑셀러 닷컴)

 

 최소값은 MIN 함수를 이용하여 구할 수 있습니다. 함수를 사용하는 방법은 MAX와 동일합니다.

 

4. COUNT, COUNTA 함수

COUNT와 COUNTA는 범위에서 빈 셀을 제외한 셀의 갯수를 세는 함수입니다. COUNT는 숫자가 들어 있는 셀만 세고, COUNTA는 숫자와 문자가 든 셀을 모두 카운팅한다는 점만 다릅니다. 함수 이름을 알고 있다면 아래와 같은 방식으로 수식을 작성할 수도 있습니다.

 

 (1) 결과를 표시할 셀을 선택하고 =cou라고 입력하면 cou로 시작되는 함수 목록이 나타납니다.

 

(이미지: 아이엑셀러 닷컴)

 

(2) COUNT 함수가 선택된 상태에서 Tab을 누르거나 마우스로 클릭합니다.

 

(3) 수식 아래쪽에 풍선 도움말이 나타나므로 인수를 입력할 때 도움이 됩니다. 개수를 셀 범위를 마우스로 선택하고 Enter를 누르면 결과가 표시됩니다.

 

(이미지: 아이엑셀러 닷컴)

 

COUNTA 함수도 사용법은 동일하므로 직접 확인해 보시기 바랍니다.

 

5. RANK 함수

'Excel 입문 강의' 7번째 시간에 '정렬'에 대해 다루었습니다. 테이블을 정렬하면 데이터의 모습을 파악하는 데 도움이 됩니다만, 원본 데이터의 형태가 바뀌는 문제가 있습니다. RANK 함수를 이용하면 표는 그대로 둔 상태에서 순위를 파악할 수 있습니다. 앞에서와 조금 다른 방식으로 수식을 작성해 봅니다. 다양한 방식 중에서 여러분에게 맞는 방식을 사용하시라는 차원에서 여러 가지를 알려드리는 것이니 참고하세요.

 

 (1) G3 셀을 선택합니다. =rank()라고 입력하고 [함수 삽입] 아이콘을 클릭합니다.

 

(이미지: 아이엑셀러 닷컴)

 

(2) Number 인수란에는 순위를 구할 숫자가 들어 있는 E3, Ref 인수란에는 금액 전체 범위에 해당하는 E3:E11 영역을 범위로 지정합니다. E3:E11 영역은 수식을 아래로 복사했을 때 범위가 변하는 안되므로 절대 주소 형태로 지정해 준 점에 유의하세요. 직접 $를 입력해도 되지만 단축키인 F4를 이용하면 편리합니다. 마지막 Order 인수는 생략합니다. 내림차순으로 순위를 구할 때에는 Order 인수를 생략하거나 0을 입력합니다.

 

(이미지: 아이엑셀러 닷컴)

 

 (3) 순위가 구해집니다. G3 셀의 채우기 핸들을 아래로 드래그하면 데이터 순서는 그대로 있는 상태에서 순위가 내림차순으로 구해집니다. Order 인수를 1로 지정했다면 순위가 반대로, 즉 오름차순으로 구해집니다.

 

나가며

이로써 우리는 함수와 수식의 개념, 셀 참조 형식, 수식을 작성하는 방법 등 엑셀로 작업함에 있어 매우 중요한 개념에 대해 알게 되었습니다. 이번 시간에 소개한 내용만 잘 이해하면 앞으로의 함수 강의는 함수 이름만 추가해 가는 과정일 뿐이라고 생각해도 크게 틀리지 않습니다. 함수에 대해 이해하게 되신 것, 축하합니다.

 

Excel과 VBA의 모든 것 아이엑셀러 닷컴 · 강사들이 숨겨 놓고 보는 엑셀러TV