이제부터 엑셀의 함수들을 종류별로 분리해서 배워보겠습니다. 우리는 이미 이전 강좌들을 통해서 수식과 함수를 사용하는 이유와 방법에 대해서 배웠습니다. 어떤 사람들은 “나는 함수를 많이 알고 있다!” 는 식으로 자랑을 하기도 하는데, 이건 자랑거리가 못됩니다. 엑셀에서 제공하는 함수 이름 많이 알아서 살림살이에 도움이 될 수는 없기 때문이죠. ^^ 물론 가장 기본이 되는 2~30개 정도의 함수는 도움말을 참조하지 않아도 수식을 바로 만들 수 있을 정도는 되어야 하지만, 나머지 함수는 필요할 때마다 도움말 등을 참고해서 응용하는 실력만 가지고 있으면 됩니다.
우리는 지금부터 엑셀 함수를 활용하는 여러 방법들에 대해 배우게 됩니다. 무조건 외울려고만 하지말고 어떻게 활용하고, 왜 이렇게 했는 지에 대해 생각하는 시간을 가져보시기 바랍니다. 이런 과정을 통해 여러분들의 엑셀 활용 능력이 극대화되는 것입니다. 명심하세요, 외워서 되는 것이 아니라 응용하는 힘을 키우는 것이 실력이 된다는 것을!
셀의 개수와 합계 함수
일반적으로 셀의 개수를 세는 함수들은 특정 조건에 해당하는 셀의 개수를 결과 값으로 돌려줍니다. 합계 함수는 우리가 많이 사용했던 SUM과 같이 조건을 만족하는 셀들의 합계값을 돌려주죠. 우선 이런 종류의 함수들은 어떤 것들이 있는지 아래 표를 보세요. 제가 생각할 때 많이 사용되고 중요한 함수는 중요 표시를 했으니 더욱 눈여겨 봐두세요. 그리고 함수는 다른 함수와 중첩해서 더 큰 효과를 낼 수 있으니 필요에 따라 여러 함수를 중첩해서 원하는 수식을 만드는 능력도 매우 중요합니다!
- COUNT 전체 범위에서 수치 자료가 포함된 셀의 개수를 셉니다.
- COUNTA 비어있지 않은(데이터가 입력된) 셀의 개수를 셉니다.
- COUNTBLANK 비어있는 셀의 개수를 셉니다.
- COUNTIF 조건에 맞는 셀의 개수를 셉니다.
- COUNTIFS 여러 조건에 맞는 셀의 개수를 셉니다. (2007에서 추가)
- DCOUNT 특정 조건에 맞는 레코드의 수를 셉니다. (데이터베이스 함수)
- DCOUNTA 특정 조건에 맞는 데이터가 입력된 레코드의 수를 셉니다. (데이터베이스 함수)
- DEVSQ 표본 평균에서 편차 제곱의 합을 반환합니다. 주로 통계 분석에서만 활용됩니다.
- DSUM 목록이나 데이터베이스의 레코드 필드(열)에서 지정한 조건에 맞는 숫자의 합계를 계산합니다.
- FREQUENCY 범위 내에서 해당 값의 발생 빈도를 계산하여 세로 배열 형태로 반환합니다.
- SUBTOTAL 범위 내에서 해당 값의 발생 빈도를 계산하여 세로 배열 형태로 반환합니다.
- SUM 인수로 지정한 모든 숫자를 더하는 함수입니다. 긴 설명 필요 없죠?
- SUMIF 특정 조건에 맞는 셀들의 합을 반환합니다.
- SUMIFS 여러 조건에 맞는 셀들의 합을 반환합니다.
- SUMPRODUCT 배열에서 해당 요소를 모두 곱하고 그 곱의 합계를 반환합니다. 배열을 배우면서 잠깐 다뤘었죠?
- SUMSQ 인수의 제곱의 합을 반환합니다.
- SUMX2PY2 두 배열에서 해당 값의 제곱의 합을 모두 더한 값을 반환합니다. 통계분석에서 활용합니다.
- SUMXMY2 두 배열에서 대응값의 차를 제곱한 후 그 결과의 합계를 반환합니다. 통계분석에서 활용합니다.
- SUMX2MY2 두 배열에서 대응값의 제곱의 차를 계산한 다음 차의 합계를 반환합니다. 통계 분석용입니다.
딱 봐서 바로 활용할 곳이 떠오르는 함수가 있는 반면 도대체 어디에 사용하는지 감조차 잡을 수 없는 함수들이 있죠? 그렇다고 전혀 걱정할 필요없습니다. 저도 SUMX2MY2라는 통계 분석용 합계 함수는 한번도 사용해 본적이 없답니다. 그러나 필요한 경우가 생기면 수식을 직접 만들 수도 있겠지만 도움말이나 인터넷에서 검색을 해보고 찾아서 활용할 수도 있는 것입니다.
제가 반복해서 이런 말씀을 드리는 이유는 함수 이름과 그 형식을 무작정 외우려고 하지 말라는 의미입니다. 이제 잔소리 그만해도 되겠죠? ㅎㅎ 그럼 실제로 하나씩 예제를 통해 그 활용도를 이해해 보도록 합시다.
셀 개수 구하기
개수를 세는 함수는 아래 예제를 통해 하나씩 풀어보겠습니다. 먼저 그림을 보세요.
A1:B8의 범위는 “예제”라는 이름을 정의한 상태입니다. 개수 함수를 이용해 E1:E7에 걸쳐 각각 개수를 구해보도록 합시다.
먼저 전체 셀의 개수를 구해보죠. 위 표에서는 선택한 범위의 전체 셀 개수를 바로 구해주는 함수는 없었습니다. 그러나 우리가 배우지는 않았지만 선택한 범위의 행 수와 열 수를 구해주는 ROWS와 COLUMNS 라는 기본 함수가 있습니다. 이런 기본 함수는 알고 계셔야겠죠. (엑셀 전체 함수 목록은 Tips 섹션에서 보실 수 있습니다.)
선택한 범위의 행/열의 수를 모두 구해서 곱하면 우리가 찾는 전체 셀의 개수가 나오겠죠?
계속해서 빈 셀의 개수는 어떻게 구할 수 있을까요? COUNTBLANK라는 함수가 바로 있죠? =COUNTBLANK(예제) 수식으로 간단하게 구할 수 있습니다.
데이터가 있는 셀의 개수는 여러분들이 직접 COUNTA를 이용해서 구해보세요.수치 자료가 있는 셀의 개수는 어떻게 구할까요? 그렇죠. COUNT라는 함수가 바로 제공됩니다.
전혀 어렵지 않죠? 단순히 셀 개수만 구하는 등의 작업은 흔치는 않지만, 여러분들이 여러 함수와 수식, 자동화 등을 통해 엑셀 통합문서를 만들때 요긴하게 사용된답니다.
텍스트가 입력된 셀 개수 구하기
계속해서 E5셀에 텍스트가 입력된 셀의 개수를 구해봅시다. 간단히 생각해서 COUNT를 이용해 수치가 입력된 개수를 구할 수 있으니 전체 셀 개수에서 빼면 될까요? 셀에 입력될 수 있는 값은 텍스트, 수치 외에도 논리값, 오류값, 공백 등이 같이 포함될 수 있으므로 이런 방식으로는 틀린 값을 구하겠죠. 우리가 아직 “문자열” 관련 함수는 배우지 않았는데, 엑셀은 IS라는 함수를 제공해서 셀에 입력된 데이터의 종류를 판단할 수 있게 해줍니다. 이 함수를 통해 셀의 데이터가 텍스트인지, 수식인지, 오류인지 등을 알 수 있습니다.
ISTEXT(셀범위) 함수는 셀범위에 텍스트 자료가 입력된 경우 참값(True)을 반환하게 되는데, 이를 이용해서 SUM, IF, ISTEXT 함수의 조합으로 전체 셀에서 텍스트가 입력된 셀 개수를 구할 수 있는 것이죠. 다음 예제를 보세요.
앞서 배웠던 배열 수식을 만들어서 입력했습니다. 배열 수식의 인자를 하나씩 해석을 해볼까요? ISTEXT(예제)라는 수식을 통해 만약 그 셀이 텍스트를 포함하고 있으면 True를 반환합니다. 바로 앞에 IF함수가 있죠? IF 함수는 조건이 True 일 경우 1을 반환하게 되고, SUM 함수가 반환된 모든 1을 더해서(메모리에 임시로 배열상수로 저장해서) 결과값을 돌려주는 것이죠. 앞에서 배열을 배운 효과가 있죠? ^^
참/거짓 오류가 있는 셀 개수 구하기
IS함수를 이용해서 텍스트가 입력된 셀 개수를 구해봤기 때문에 텍스트가 아닌 셀의 개수를 구하는 방법도 쉽게 유추할 수 있습니다. ISTEXT 함수가 있었으니 ISNONTEXT 함수도 있습니다. IS함수의 사용법에 대해서는 엑셀 도움말을 보시구요. =SUM(IF(ISNONTEXT(예제), 1)) 수식은 텍스트가 아닌 셀의 개수를 구해주겠죠. 똑 같은 원리로 ISLOGICAL은 논리값이 포함된 셀인지를 판단해주고, ISERROR은 에러가 있는지를 판단해줍니다. 더 설명안드려도 되겠죠?
ISERROR는 에러의 종류별로 더욱 자세한 판단도 가능하답니다.
조건에 맞는 셀 개수 구하기 – COUNTIF
우리가 특정 조건을 정하고, 그 조건에 맞는 셀의 개수만 구하고 싶을 때는 COUNTIF를 사용합니다. 함수 이름만 봐도 대강 알 수 있겠죠? =COUNTIF(예제, 40) 이라는 수식은 “예제” 범위에서 40이 입력된 셀의 개수를 구하는 식입니다. =COUNTIF(예제, “1사분기”)는 정확하게 범위에서 “1사분기”라는 텍스트가 입력된 셀의 갯수를 반환하는 것이죠.
참 쉽게 느껴지시죠? 무조건 외워서는 이런 감을 잡기가 힙듭니다. 정확하게 일치하는 조건 뿐만 아니라 >와 < 등 비교연산자를 이용한 조건 식도 가능하답니다.
여러 조건에 맞는 셀 개수 구하기 – COUNTIFS
여러 조건에 맞는 셀 개수를 구하려면 COUNTIF를 여러 번 중첩할 수도 있고, 엑셀 2007에서 처음 소개된 COUNTIFS를 이용해도 됩니다. 방법은 하나만 정해져 있는 것이 아니라 가장 효율적이고 자신에게 맞는 것을 찾는 것이 중요하겠죠?
위 예제에서는 A11:B13까지 데이터를 입력한 후 “예제2″라는 이름을 정의했습니다. E12셀에 COUNTIFS를 이용한 수식을 만들었는데, 예제2의 범위에서 100보다 크고, 300보다 작은 수가 입력된 셀의 갯수를 찾은 것입니다. 이제 더 자세한 설명은 필요없으시죠? ^^
가장 많은 빈도의 셀 수 구하기
엑셀 통계 분석의 기초 중에는 빈도표를 구하는 부분이 있습니다. 물론 뒤에서 간단하게 이 기법들에 대해 설명은 드리겠습니다만, MODE라는 함수가 범위 내에서 가장 많이 출현한(입력된) 수를 반환해 줍니다. (셀의 개수가 아니라 실제 많이 입력된 수) 이 MODE 함수와 COUNTIF 함수를 조합하면 어떤 수가 몇번 입력되었는지를 찾을 수 있을 것입니다.
A14:A22에 위 그림과 같은 수를 입력하고 “예제3″으로 이름을 정의했습니다. D14와 D15에 어떤 수가 가장 많이 입력되었는지, 그리고 그 수가 몇 번 입력되었는지 알아보겠습니다.
D14셀에 MODE 함수를 사용해서 가장 많이 입력된 수가 5라는 것을 알았습니다. 그런데 C15를 보면 MODE 함수를 사용하기 전과는 조금 다른 값이 자동으로 입력되었죠? C15를 선택한 후 주소 입력줄을 보세요.
&라는 문자 합침 연산자를 사용해서 D14의 내용과 합쳐서 미리 문장을 만들어 둔 것입니다. 이런 기법에 대해서는 텍스트 함수를 다루면서 다시 배우게 됩니다.
자 이제 우리는 범위에서 5가 가장 많이 입력된 수라는 것을 알았습니다. 그렇다면 5가 몇번 나왔는지는 COUNTIF 를 이용해서 간단하게 구할 수 있겠죠?
FREQUENCY 함수와 배열을 이용한 빈도표는 고급 강좌에서 자세히 배울겁니다~
조건이 맞는 값을 합하는 SUMIF
셀 개수를 세는 여러 방법들에 대해 배웠습니다. 이제 셀의 값을 합하는 함수들에 대해 알아보고 이번 강좌를 마치도록 하겠습니다. SUM은 워낙 많이 했고, 기초적인 것이기 때문에 다시 다루지는 않을거구요.
SUMIF 함수는 함수의 의미 그대로 SUM과 IF 함수가 결합된 기능을 합니다. 만약 어떤 조건을 만족하면 그 만족하는 값들에 대해서만 합계를 구하는 것이죠. SUMIF 함수의 구문과 인수의 내용은 다음과 같습니다.
SUMIF(range,criteria,sum_range)
▶ Range : 조건을 적용시킬 셀 범위입니다. (조건의 범위)
▶ Criteria : 숫자, 수식 또는 텍스트 형태의 찾을 조건입니다.
예를 들어 Criteria는 32, “32″, “>32″, “사과” 등으로 표시할 수 있습니다.
▶ Sum_range : 합을 구하려는 실제 셀입니다. sum_range의 셀에 대응하는 range의 셀이 찾을 조건과 일치할 때만 더할 수 있습니다. Sum_range를 생략하면 range에 있는 셀들을 더합니다.
아래 예제는 출석일수가 3일 이상인 사람들의 평가점수의 합계를 구한 것입니다.
어렵지 않으시죠? 위 예에서 만약 C2:C6 가 생략되면, 3+4+5 로 12라는 값이 나오게 되겠죠. (조건 범위와 합계 범위가 동일)
이상으로 셈과 합계를 다루는 함수에 대해서 알아봤습니다. 이제 함수라는 것이 더욱 친근하게 다가오시죠? 마지막으로 잔소리 한번 만 더하겠습니다. 무조건 외우려고 하지 마시고, 응용력을 키울 수 있도록 그 용도를 잘 보시고 직접 상황에 맞게 응용해보려는 노력을 하세요. 실력이 팍팍 느실거에요!
