안녕하세요?

오늘은 엑셀 표 만드는 방법에 대해 한번 알아보겠습니다.

엑셀 자체가 '스프레드시트'이기 때문에 어떻게 보면 하나의 sheet 자체가 커다란 표가 됩니다. 그런데 새삼스럽게 무슨 '엑셀 표 만들기'냐?라고 할 수 있지만, 스프레드시트에서 얼마나 깔끔하고 보기 좋게 원하는 표를 만드느냐는 몇 가지 기술이 필요합니다.

다시 말하면, 똑같은 제목과 숫자와 내용의 콘텐츠를 엑셀에서 표시하기 위해 표를 만들 때, 그냥 대충 만드는 것이 아니라 데이터의 속성을 이해하고 가장 적절하게 표현할 수 있도록 만드는 것이 매우 중요하다는 것이죠.

정리해 보면, 표를 만들 때 신경써야 할 것은 다음과 같습니다.

1. 표현하고자 하는 데이터를 가장 직관적으로 이해할 수 있게 구현하려면 어떻게 해야 할까?

이것은 어떤 기준을 행으로 하고, 어떤 기준을 열로 할 것인가? 에 대해 생각해야 한다는 것입니다.

쉽게 기준을 잡을 수 있는 것은 그 갯수가 고정되어 있는 것을 '열'로 하고, 계속 추가되거나 삭제되는 데이터는 '행'으로 구현하는 것입니다.

예를 들면, 판매하고 있는 어떤 상품에 대해 매일매일의 판매 현황을 누적해서 보고자 한다면, 가로로 상품명을 나열하고 매일 추가되는 데이터를 행을 늘려가면서 추가합니다.

2. 아래로 데이터가 계속 늘어나는 구조인데 합계 값이 필요하다면 합계는 맨 위에 만든다.

아래로 데이터가 계속 늘어날 가능성이 있는 표는 합계의 위치가 계속 아래로 밀리면서 바뀌게 됩니다. 그리고, 어느 순간 화면 밖으로 벗어나서 합계를 보려면 스크롤을 해야 볼 수 있게 됩니다.

그래서 합계나 평균 등의 값을 윗쪽으로 배치하면 좀 더 수월하게 데이터를 보고 관리할 수 있습니다. 이때, 합계의 범위는 여유 있게 잡아줄 필요가 있습니다. (=sum(B3:B500) 등과 같이)

3. 만드는 사람 입장에서는 이 표를 다음에 볼 때 왜 이 표를 만들었는지 바로 알 수 있도록, 다른 사람 입장에서는 이 표를 만든 사람이 무슨 의도로, 무엇을 강조하기 위해 만들었는지 알 수 있도록 구성한다.

엑셀로 표를 만들 때는 최대한 깔끔하고 한눈에 들어올 수 있도록 만들어야 합니다.

특히, 숫자가 많이 있는 경우는 많은 숫자 중에서 강조하고자 하는 것이 무엇인지를 바로 알 수 있도록 만드는 것이 중요합니다. 이를 위해서는 해당 셀의 값을 어떻게 강조할 것인지?, 표의 선과 셀의 색깔은 어떻게 할 것인지? 폰트의 크기와 색깔은 무엇을 쓸 것인지 등에 대한 고민이 필요합니다.

위의 표에서 보는 것과 같이 제목 행은 진한 바탕색에 밝은 글자로 구성을 하면 어떤 값에 대한 자료를 보겠다라는 것을 쉽게 알 수 있습니다.

다음으로 위의 표에서는 만일 각 상품별로 합계 값이 중요하다면 합계의 데이터를 바로 알 수 있도록 굵고 일반 폰트의 크기보다 한 단계 정도 높은 크기를 사용하는 것이 좋습니다. 그리고, 옅은 색깔로 바탕을 깔아 주면 좋습니다.

그리고, 기초 데이터가 되는 부분, 즉 위의 표를 기준으로 하면 매일매일 각 상품별로 판매된 현황은 각 값의 의미는 있지만 전체 표의 구성으로 보면 숫자 하나하나는 크게 중요하지 않을 수 있습니다. 이럴 경우, 그 값의 색을 회색으로 하거나 폰트 사이즈를 줄여서 강조하고자 하는 다른 값과 구분을 주는 것이 좋습니다.

그 외, 고려할 사항

* 차트를 만들어야 한다면 차드를 만들기 위한 표의 구성을 사전에 고려해서 표를 설계한다.

* 기초 데이터를 기반으로 분석할 내용이 많다면 반복되는 수식을 편하게 넣을 수 있는 것도 고려해서 표를 만든다.

엑셀에서 표 만들기를 할 때 여러 가지를 생각하면서 만들면 좋겠지만, 위에 설명한 내용을 잘 따져 보면서 표를 만들면 만드는 사람도, 보는 사람도 쉽고 정확한 정보를 전달해 주는 의미 있는 표가 될 수 있을 것입니다.

오늘은 SUM 함수와 더불어서 엑셀함수의 가장 기본이 되는 함수를 소개하려고 하는데, 그것은 바로 평균을 구하는 함수(AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, DAVERAGE) 입니다.

엑셀의 함수명 들은 약간 비슷한 점이 있네요. 함수 뒷부분이 IF나 IFS로 끝나는 경우는 SUM함수에 사용하여 SUMIF나 SUMIFS와 비슷한 의미를 지니는 함수를 나타냅니다.

각각의 설명은 다음과 같습니다.

AVERAGE:     각 인수의 평균값을 구함. 인수는 숫자, 이름, 배열, 숫자가 들어있는 참조 등이 될 수 있음

AVERAGEIF:   주어진 조건에 따라 지정되는 셀의 평균(산술 평균)을 구함, 단일 조건 구성

AVERAGEIFS: 주어진 조건에 따라 지정되는 셀의 평균(산술 평균)을 구함, 복합 조건 구성

각 평균함수의 사용법을 보면 다음과 같습니다.

먼저 가장 쉬운 AVERAGE 함수는 말 그대로 주어진 값들의 평균을 구합니다.

사용 방법 아래 그림에서 보는 것과 같이

'=average(평균을 구하고자 하는 값 또는 범위)'

입니다. 범위 대신 각각의 셀을 직접 지정할 수도 있습니다.

다음은 AVERAGEIF 함수입니다.

사용 방법은 

'=averageif(조건을 판단할 범위, 조건, 평균을 구하고자 하는 값이 있는 범위)'

입니다. 아래 예에서 보면, 전체 값(F4:F11) 중에서 1학년(D13)에 해당하는 값으로만 평균을 구하라는 것입니다.

세번째로는 AVERAGEIFS 함수입니다.

기능은 AVERAGEIF와 같은데 다만 조건을 여러가지로 할 경우에 해당합니다.

사용 방법은

'=averageifs(평균을 구하고자 하는 값이 있는 범위, 첫번째 조건, 첫번째 조건을 판단할 범위, 두번째 조건, 두번째 조건을 판단할 범위, ....)'

입니다.

이 함수는 조건이 여러개가 붙을 수가 있으므로, 평균을 구하고자 하는 범위를 먼저 지정해 주고, 그 다음으로 조건과 조건의 범위를 계속 붙여 나가면 됩니다.

이상과 같이 AVERAGE 함수에 대해 알아봤는데요.

SUM함수 만큼은 아니지만 많이 쓰이는 함수이기 때문에 익혀두면 도움이 많이 될 것입니다.

일반적으로 회원목록, 부품목록 등을 엑셀로 만들어 놓는 경우가 많이 있습니다.

회원목록의 경우 구성되는 항목은 회원번호, 이름, 주소, 전화번호 등으로 구성을 할 것이고, 부품목록은 부품 품명을 시작으로 가격, 보관장소, 제고여부 등을 표시할 것입니다.

이런 류의 목록이 있을 때, 목록에서 원하는 항목의 값을 얻고자 할 때 쓰는 함수가 VLOOKUP 함수 입니다.

예를 들면, 회원번호를 입력하면, 해당 회원의 이름, 주소, 전화번호 등을 표시할 수 있도록 만드는 함수인 것이지요.

아래의 예는직원명부를 사번을 기준으로 만들어져 있다고 할 때, 특정 사번을 입력하면, 그 사번에 해당하는 직원의 이름, 입사일, 부서 등을 찾아서 표시해 줄 수 있도록 구성한 것입니다.

이와 같은 데이터에서 다음과 같이 사번(노란색 셀)을 입력하면 이름과 입사일, 그리고 부서를 알 수 있도록 VLOOKUP 함수를 사용하여 만드는 것이 이번 강좌의 목적입니다.

VLOOKUP 함수의 기본 형식은 다음과 같습니다.

= VLOOKUP(lookup_value, table_array, col_index_no, [range_lookup])

쉽게 정리하자면,

=VLOOKUP(조회하려는 값, 값을 조회하려는 범위, 범위 내에서 가장 첫 영역부터의 열 숫자, 정확히 일치 또는 유사 일치(0/FALSE 또는 1/TRUE로 표시됨))

로 정리할 수 있습니다.

여기서 주의할 것은 세번째 인수인 "col_index_no"입니다.

이것은 범위의 첫번째 열을 '1'로 할 때 조회하고자 하는 값이 있는 열의 순서입니다. 즉, 위의 예에서 사번을 기준으로 입사일을 표시하고자 한다면, col_index_no는 '3'이 되는 것입니다.

그럼 이제 '이름'에 해당하는 함수를 표시해 볼까요?

결론부터 정리하자면,

=VLOOKUP(G2, A3:D16, 2, FALSE)

입니다.

조회하려는 값인 노란색 셀의 위치는 G2이고, 조회하고자 하는 범위는 A3:D16입니다. 그리고, 이름은 조회하고자 하는 범위의 두번째 열에 있으므로 '2'가 되는 것입니다. 마지막으로, 정확한 값을 찾는 것이니까 FALSE로 적으면 됩니다.

마찬가지로, 입사일과 부서도 위의 수식과 동일한데, 열의 위치만 달라졌으므로 세번째 인수만 바뀌주면 됩니다.

=VLOOKUP(G2, A3:D16, 3, FALSE)

=VLOOKUP(G2, A3:D16, 4, FALSE)

** 여기서 한가지 TIP을 알아보겠습니다. **

위에서와 같이 열의 갯수를 나타내는 col_index_no는 숫자로 직접 입력하는 방법도 있지만, 셀을 지정할 수도 있습니다. 물론 그 셀의 값은 숫자여야 겠지요.

위의 예시 데이터 표의 아래(또는 위)에 다음과 같이 열에 해당하는 숫자를 입력해 놓습니다.

그리고, 열의 위치를 적는 곳에 해당 셀을 적으면 됩니다. 위의 수식을 변경시켜 보면,

=VLOOKUP(G2, A3:D16, B18, FALSE)

=VLOOKUP(G2, A3:D16, C18, FALSE)

=VLOOKUP(G2, A3:D16, D18, FALSE)

와 같이 할 수 있겠지요.

일반적인 경우는 상관 없겠지만, 수식을 드래그해서 복사하여 사용할 경우 등에는 유용하게 쓸 수 있을 것입니다.

오늘은 데이터를 찾고 조회하는데 많이 사용하는 VLOOKUP 함수에 대해 알아봤습니다.

엑셀에서 Sheet에 있는 표의 갯수를 세는 함수는 일반적으로 COUNT 함수를 사용합니다.

COUNT 함수는 의외로 많이 사용하게 되는데요. 주로 어떤 목록이 있을 때, 조건에 해당하는 갯수가 몇개인지, 전체 목록의 행수가 몇줄인지 등등 활용하는 바에 따라 아주 많은 활용도를 가지고 있다고 볼 수 있습니다.

COUNT함수도 다양하게 있는데요. 

간단히 정리하면 다음과 같습니다.

COUNT: 범위 내에서 숫자가 포함된 셀의 갯수

COUNTA: 범위 내에서 비어있지 않은 셀의 갯수

COUNTBLANK: 범위 내에서 비어있는 셀의 갯수

COUNTIF: 범위 내에서 지정한 조건에 맞는 셀의 갯수

COUNTIFS: 범위 내에서 다양한 조건에 맞는 셀의 갯수

그 외에도 몇가지가 더 있는데 일반적으로 위의 5가지만 잘 알면 활용하는데 전혀 문제가 없다고 볼 수 있습니다.

그럼 오늘은, 다음과 같은 표에서 몇가지 시나리오를 계산하는 방법으로 함수를 알아 보겠습니다.

1. 전체 학급의 학생 수

필요한 함수는 COUNTA 함수이며 형식은 다음과 같습니다. 구하고자 하는 셀의 범위는  같이 범위를 지정할 수도 있고, 직접 셀을 선택해서 입력할 수도 있습니다.

=COUNTA(range) 또는 COUNTA(value1, value2, ...)

예시에서 전체 학생수를 구하기 위해서는 다음과 같이 하면 됩니다.

=COUNTA(A2:A13)

2. 남학생 수

필요한 함수는 COUNTIF 함수이며, 형식은 다음과 같습니다

= COUNTIF(range, criteria)

여기서, range는 갯수를 구하고자 하는 영역이며, criteria는 조건입니다.

예시에서 남학생 수를 구하기 위해서는

=COUNTIF(B2:B13, "남")

3. 방과 후 수업을 듣는 학생 수

필요한 함수는 COUNTA입니다. 물론 COUNTIF 등 다른 함수를 사용할 수도 있습니다.

둘 다 위에서 배운 것이니 적용한 결과만 보겠습니다.

= COUNTA(E2:E13)

= COUNTIF(E2:E13, "O")

두가지는 동일한 결과를 가져 오겠죠?^^

별로 좋은 방법은 아니지만 심심풀이로 이렇게 복잡하게도 할 수 있습니다.ㅎㅎ

= COUNTA(A2:A13) - COUNTBLANK(E2:E13)

COUNTBLANK는 지정한 범위에서 비어있는 셀의 갯수를 구하는 것이기 때문에 COUNTA를 통해 전체 학생수를 구하고 여기서 방과후를 표시한 E열에서 비어있는 셀의 갯수를 빼는 방식으로 구한 것입니다.

이번 예시에서는 굉장히 비효율적인 방식이지만, 복잡한 표에서는 이와 같이 하나의 함수로 만이 아니라 여러가지 함수를 복합적으로 사용할 경우도 생긴다는 것을 염두해 두면 좋을 것 같습니다.

4. 여학생 중 수학점수가 80점 이상인 학생 수

SUMIFS와 유사한 기능인 COUNTIFS 함수를 사용하면 됩니다.

= COUNTIFS(criteria_ragnge1, criteria1, criteria_range2, criteria2, ...)

여기서는 범위와 조건을 순서대로 계속 표시해 주면 다양한 조건에 해당하는 갯수를 구할 수 있습니다. 물론 AND 조건이구요.

위의 예시를 적용해 보면 다음과 같습니다.

=COUNTIFS(B2:B13, "여", C2:C13, ">=80")

[기타] 분석 항목 중 점수가 표시된 열 수

만일, 위의 예시와 같은 표에서 전체 열 중 점수가 있는 열이 몇개인지를 알아보려면, COUNT 함수를 쓰면 됩니다.

COUNT 함수는 단순히 지정된 범위에서 숫자로 되어 있는 갯수만을 구해 주기 때문이죠.

형식은

=COUNT(range) 또는 COUNT(value1, value2, ...)

입니다.

위와 같이, =COUNTA(A2:E2)로 값을 구게 되면, 수학점수와 국어점수가 있는 셀이 2개 이므로, 결과 값은 2로 나오게 됩니다.

이상과 같이 엑셀의 표에서 갯수를 구하는 다양한 함수에 대해 알아 봤습니다.

앞에서도 언급했드시, 간단한 함수지만 많이 사용되고 있고, 다양한 데이터를 분석하기 위해서 필요한 기능이니 많이 사용하면 좋을 것 같습니다.

엑셀의 기본 기능은 뭐니뭐니 해도 계산을 빼 놓을 수 없겠죠?

이 계산 중에서도 가장 기본은 사칙연산(+, -, *, /)입니다.

오늘은 이중 가장 기본이 되는 덧셈에 대해 알아보겠습니다.

엑셀 함수 중 SUM을 계산하는 함수는 대략 11개 정도가 됩니다. 하지만, 이중 많이 사용하는 것은 위의 3개인 SUM, SUMIF, SUMIFS입니다.

각각의 기능 및 형식을 보면 다음과 같습니다.

먼저 SUM은 

=SUM(C2:C8) 과 같이 더하는 범위를 지정하거나,

=SUM(D2, D3, D7) 과 같이 직접 더하는 셀을 지정할 수 있습니다.

물론 이 두가지를 섞어서 사용도 가능합니다. 즉, =SUM(C2:C8, D2, D3, D7)

다음으로 SUMIF를 알아 보겠습니다.

SUMIF 함수는 주어진 조건에 따른 합을 구하는 수식입니다. 즉, 원하는 조건에 해당하는 값만 모아서 합을 구하라는 함수를 말하는 것이지요.

SUMIF 함수의 형식은 다음과 같습니다.

=SUMIF(range, criteria, [sum_range])

여기서, range는 조건을 판별할 대상이 있는 영역을 말합니다.

그리고, criteria는 해당 영역에서 적용할 조건을 적습니다.

마지막으로 sum_range는 해당 조건이 맞았을 경우 더하고자 하는 값이 있는 영역을 말합니다.

좀 더 쉽게 설명하자면 위의 표를 볼 때, 만일 남학생만의 수학점수 합계를 구하고 싶다면, range는 성별이 표시되어 있는 B2:B8이 될 것이고, criteria는 "남"이 될 것입니다. 그리고, 해당 조건에 충족할 때 더해지는 값이 있는 영역은 수학점수가 있는 C2:C8이 될 것입니다.

이것을 함수 수식으로 표시해 보면,

=SUMIF(B2:B8, "남", C2:C8)

과 같이 됩니다. 여기서 조건인 criteria는 위와 같이 "  " 범위에 직접 적을 수도 있고 셀을 지정할 수도 있습니다.

마지막으로 SUMIFS의 기능은 무엇일까요?

SUMIFS는 SUMIF와 비슷하게 조건에 해당하는 값을 더하는 기능인데, SUMIF는 하나의 조건만 사용할 수 있다면, SUMIFS는 여러가지 조건을 동시에 적용할 수 있습니다. 물론, 조건은 "AND" 조건이구요.^^

형식은

= SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

입니다.

이와 같이 합계를 구할 하나의 범위에 여러개의 조건 범위와 조건을 쭉 나열하는 형식으로 되어 있습니다.

예를 들어보면, 성별이 여자이고 수학점수가 90점 이상인 학생의 국어점수의 합계를 구한다면,

sum_range는 D2:D8이 되고, criteria_range1은 B2:B8, criteria1은 "여", criteria_range2는 C2:C8, criteria2는 ">=90"으로 하면 됩니다. 

정리하자면,

=SUMIFS(D2:D8,B2:B8,"여",C2:C8,">=90") 로 할 수 있겠지요.

이상에서와 같이 SUM과 관련된 함수에 대해 알아 봤습니다.

가장 많은 기능을 포함하고 있는 기능이 SUMIFS 함수이니, 이 함수를 꼭 기억해 놓으면 도움이 많이 될 것입니다.

엑셀의 많은 함수와 기능들이 그렇지만, 다양하고 창의적으로 활용한다면 무궁무진한 분석을 할 수 있습니다.

특히, SUMIFS와 같은 함수는 조건을 여러가지로 달 수 있으니, 여러가지 복잡한 내용이 들어 있는 Sheet에서 내가 필요한 합계를 구하는데 유용하게 사용할 수 있을 것입니다.

간단하지만 막강한 기능의 SUM, SUMIF, SUMIFS에 대해 알아 봤습니다.

궁금한 내용은 댓글로 달아 주세요~~