엑셀의 달인으로 인정받는 ‘함수’ 사용법!

열정과 패기로 무장한 신입사원 나유능군. 언제나 초롱초롱한 눈빛을 빛내는 그였지만, 오늘은 왠지 약 먹은 병아리 마냥 흐리멍텅~하기만 합니다. 그 이유는? 지금까지 워드, 파워포인트, 프레지 등 그 어렵다는 고난을 무사히 넘겨왔지만, 무시무시하다던 ‘엑셀’ 앞에서 무릎을 꿇어야만 했기 때문이죠. 어제 오후에 시작한 데이터 작업, 밤을 세워가며 했음에도 불구하고 끊이지 않는 숫자의 행렬.

하지만 이게 왠 일? 평소에도 무시무시한 포스를 뿜어내며 ‘만능인’이라 불리는 한대리님이 손을 한 번 대자마자 그 많은 숫자들이 촥촥 정리되는 것 아니겠습니까!!! 지금까지 나유능군이 했던 작업은 무엇이었단 말입니까?

“반성합니다.”

지금까지 엑셀을 그저 표와 그래프 만드는 데만 이용하셨다면, 반성해야 합니다. 지금까지 엑셀을 그저 줄정렬에만 사용했다면, 반성해야 합니다. 그보다 엑셀의 핵심기능은 ‘함수’라고 할 수 있기 때문이죠. 더 이상 엑셀 앞에서 버벅대는 모습은 그만! 능력자 한대리님 앞에서도 ‘제가 할 수 있습니다’를 당당하게 외칠 수 있는 ‘엑셀 함수들’! 지금부터 소개합니다~

엑셀 함수 기초편! “가장 많이 사용하는 함수는…”

함수는 엑셀에서 강력한 기능 중 하나로, 함수를 이용하면 아무리 복잡한 데이터도, 어려운 수식도 쉽고 간편하게 처리할 수 있습니다. 하지만 처음부터 함수를 능숙능란하게 이용할 수는 없습니다. 배움만이 우리의 살길! 

엑셀은 수식을 사용해 업무상의 효율을 높이는데 목적이 있는 프로그램입니다. 하지만 처음에는 간단한 사칙연산과 소량의 데이터로 시작할지 몰라도, 세상일이 라는 것이 그리 호락호락하지만은 않은 일! 점점 늘어나는 데이터는 ‘함수’로 쉽게 처리할 수 있는 것이죠. 현재 엑셀에서 제공하는 함수는 330여개. 그 중에서도 사용 빈도가 높은 함수는 다음과 같아요. 

 

· 수학 함수 : SUM, SUMIF (수학적인 계산을 할 때)

· 날짜/시간 함수 : DAY, YEAR (날짜와 시간이 필요할 때)

· 통계 함수 : AVERAGE, COUNT (통계를 사용할 때)

· 텍스트 함수 : LEFT, RIGHT, MID (문자열과 관련된 역할)

· 논리 함수 : IF (값을 비교하여 옳고 그름 판정)

· 찾기/참조 함수 : INDEX, VLOOKUP (특정한 값 추출)

· 재무 함수 : FV, PMT (재무 관련 계산할 때)

엑셀 함수 본편! “엑셀 달인으로 인정받는 함수 열전”

앞서 엑셀의 함수에 대해 간단하게, 아주 간단하게 살펴보았다면 지금부터는 실제 업무에 적용할 수도 있으며 부가적으로 옆자리 동료에게 잘난 척도 할 수 있는 엑셀 함수를 소개해드릴까 합니다. 긴장하시고~

1. 해당 그룹에 포함된 인원수가 궁금하다면? “COUNTIF 함수”

나유능군에게 미션이 주어졌습니다. 이번 팀별 프로젝트의 점수를 책정한 엑셀시트에서 각 등급별로 몇 명인지 구분을 하라는 것! 그런데….전체 인원에 대한 등급 분류를 해야 하다보니, 수백, 수천이 넘어가는 데이터네요. 이걸 언제 정리를 하라는 말입니까!!! 라고 울분을 토하기 전에 알아두어야 할 함수가 ‘COUNTIF’입니다. 함구 구성은 다음과 같습니다.

  =COUNTIF(range, criteria)

위의 구성을 말로 풀어보면 '해당 범위(range)에서 정한 조건(criteria)에 맞는 셀의 개수를 구하라' 라고 할 수 있습니다. 간단하게 예를 들어보겠습니다. 

A열에 불특정한 숫자의 배열이 있을 때 ‘1’은 과연 몇 개가 있는지를 확인하는 예제인데요, C1셀에 ‘=COUNTIF(A:A’를 먼저 입력합니다. 여기서 ‘A:A’는 A열에 몇 개의 행까지 있는지 알 수 없기 때문에 A열 전체를 지정하라는 의미입니다. 그리고 그 뒤에 ‘=COUNTIF(A:A,1)’ 이렇게 ‘1’을 입력해주면 C1셀에는 ‘1’에 해당하는 값이 몇 개가 있는지 바로 나타나게 되는 거지요. 참 쉽죠잉~

2. 조건에 맞는 값만 모두 더해라 “SUMIF”

엑셀작업을 하다보면 보이는 모든 데이터 값을 더했으면 좋겠는데, A 경우의 값만 더해라, B 경우의 값만 더하라 등등 주문이 다양해지게 됩니다. 이 경우 일일이 정렬을 이용해 더하다보면 하루가 짧다는 느낌이 매우 강렬하게 들겠죠? 이럴 때 필요한 함수가 바로 ‘SUMIF’ 입니다.

먼저 IF 함수란 일정 조건에 맞을 경우 지정된 계산을 실행 혹은 표기를 하라는 함수입니다. 이중, ‘조건’에 맞을 경우 ‘지정된 영역’을 모두 합산하라는 것이 SUMIF 함수이며, 구성은 다음과 같습니다.

=SUMIF(조건이 있는 열,조건식,합산할 값이 있는 열)

   * 조건이 있는 열 : 어떤 데이터 테이블에서 조건을 지정하려는 열

   * 조건식 : 데이터 테이블에서 찾고자 하는 조건식

   * 합산할 값이 있는 열 : 어떤 데이터 테이블에서 합산할 값이 있는 열

단, 이 SUMIF 함수를 이용할 때 주의할 사항이 있는데요, 조건이 있는 열과 합산할 값이 있는 열은 한 테이블에 있어야 합니다. 

 

위 구성의 의미는 설명하면 A열에 있는 A라는 조건의 값을 B에서 찾아 모두 더하라는 의미가 되겠죠? 결과를 구했더니 모두 합한 값이 '193'으로 나왔습니다. 

 

3. 정렬 함수의 최강자 “VLOOKUP”

‘Look up’은 다들 아시겠지만 무엇인가를 찾는다는 의미고, 앞에 붙은 v는 vertical의 약자로 세로를 의미합니다. 다시 말해 특정 테이블에 세로로 어떤 특정 값을 찾는다는 의미로 볼 수 있겠죠. 조금 어려운가요? 하지만 엑셀에서 가장 많이, 그리고 유용하게 사용하는 함수이므로 꼭 알고 넘어가야 하는 함수입니다. 우선 함수 구성을 보면 다음과 같습니다.

= VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

   * lookup_value : 첫 번째 열에서 찾고자 하는 값

   * table_array : 찾고 싶은 값이 있는 데이터의 범위

   * col_index_num : table_array에서 정한 범위에서 값이 있는 열의 index

   * range_lookup : 논리값. True는 근사값, false는 정확한 값을 의미 

자, 말로 설명해서는 도통 모르겠다 할 경우에는 직접 실천해보는 것이 가장 좋겠죠? 지금부터 VLOOKUP의 강력한 기능을 직접 보여드리겠습니다. 

Sheet1에는 만화주인공들이 이번 인사고과에서 취득한 점수를 나열했습니다. 그런데 이걸 어쩌죠? 부장님이 주신 데이터(Sheet2)를 보니 새로운 사람도 있고, 기존에 있는 사람도 있습니다. 기존에 있는 사람들의 데이터를 나유능씨가 만든 데이터에서 자동으로 가져오고 싶을 때 사용할 수 있는 함수가 vlookup입니다.

우선 값을 입력할 셀에서 함수 서식에 맞게 구성을 합니다. 

 

구성을 설명하자면, '고길동'이라는 사람을 기준으로 shee1에 있는 A열의 값 중에서 고길동에 해당하는 값(2번째 줄)을 찾되, 정확한 값(false)으로 찾으라는 의미가 되겠습니다. VLOOKUP 함수를 완성시키면? 다음과 같이 sheet1에서 고길동에 해당되는 값을 찾아와 해당 셀에 입력시켜주게 됩니다.  

VLOOKUP 함수, 조금 어렵죠? 하지만 많이 이용되는 만큼 반드시 마스터해야 한다는 것도 잊지 마세요~ 엑셀 달인의 길은 멀기만 합니다. 

4. 이렇게 다양한 기능을 가지고 있다니! “SUBTOTAL”

때로는 각각의 상황에 맞는 함수를 이용하기 보다는 하나의 함수가 가진 다양한 기능을 활용하는 방법도 있습니다. 응? 무슨 말인지 모르겠다고요? 지금 설명하려는 SUBTOTAL 함수가 바로 그러한 기능을 갖추고 있는데요. 함수 번호에 따라서 각각 다른 기능을 수행하는 엑셀 함수계의 맥가이버와 같다고나 할 수 있을까요? SUBTOTAL 함수의 구성은 다음과 같습니다.

 =SUBTOTAL(function_num,ref1,ref2…)

   * function_num : 함수번호를 의미 

   * ref : 값을 계산하고자 하는 범위

SUBTOTAL 함수를 사용하기 위해서는 함수 번호를 외우고 있어야 합니다. 함수 번호를 간단하게 정리하면 다음과 같습니다. 

'SUBTOTAL' 함수에서 주로 사용되는 함수번호들.

1. 평균 

2. 수치개수 

3. 데이터 개수 

4. 최대값 

5.최소값

6. 곱셈 

7. 표본 표준 편차 

8. 표준편차 

9. 합계 

                                                     10. 표본분산 

                                                     11. 분산

자, 간단하게 ‘1번’의 기능을 이용해보도록 하겠습니다. 만화주인공들의 인사고과 평균을 SUBTOTAL을 통해 계산해보면 다음과 같습니다. 생각보다 높지 않은데요? ^^;

   

번외편! 간단하지만 반드시 알아야 할 엑셀 기능 한가지!

너무나 많은 기능을 가지고 있기에, 할 이야기도 많고 기능도 많습니다만 함수에 대한 설명은 여기까지 마치도록 하겠습니다. (더 많은 성원이 있으면 다시 돌아옵니다!! ㅋ) 하지만 이대로 넘어가기는 너무 아쉽잖아요? 그래서 누구나 다 알 것 같은데, 그 속을 들여다보면 모르는 사람이 의외로 많은 “&” 기능을 소개해드릴까 해요. 

“&”는 주로 두 셀의 값을 하나로 합칠 때 이용합니다. 어떻게 하는지 실전으로 보여드릴께요.

 

여기서 공통으로 들어가야 하는 문구는 “ ” 안에 넣어주고 & 기능으로 연결해놓으면 됩니다. 자, 결과가 어떻게 되는지 확인해볼까요? 

달인의 길은 멀기만 합니다. 워드를 처음 배울 때는 워드만 알면 다 할 수 있다고 생각했습니다. 그런데 아니더군요. 파워포인트도 배워야 하고, 한글도 다룰 수 있어야 합니다. 이제는 엑셀까지! 하지만 이렇게 배워갈수록 여러분의 실력은 늘어가고, 이를 우러러보는 동료, 후배, 선배들이 많아 진다는 것! 배워서 남 주겠습니까? 지금까지 엑셀 함수는 모르고 사셨던 분들! 지금부터라도 함수의 세계에 푹~ 빠져보세요~

만능직장인의 꿈, 한화데이즈가 이루어드립니다.

직장인 오피스 달인 시리즈, 지금 확인하세요~

  ▶ 부장님께 바칩니다. 깔끔한 파워포인트 위한 7가지 비결! 

  ▶ 개성있는 프리젠테이션의 비결 '프레지'를 아시나요? 

* 이 컨텐츠의 모든 저작권은 한화그룹 공식 블로그 한화데이즈에 있습니다.