안녕하세요?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

그 외, 고려할 사항

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

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

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

안녕하세요?

엑셀은 주로 숫자를 다루는 프로그램이지만 숫자와 더불어 많이 쓰는 내용이 날짜와 관련된 값일 것입니다.

특히, 시계열로 만들어진 데이터를 분석하거나 기간별 분석, 그리고 전년도와의 비교 등을 위해서는 날짜에 대한 함수를 잘 다루는 것이 매우 중요합니다.

그래서 오늘은 날짜와 관련된 함수 중에서 주로 많이 사용되는 함수와 어떤 방법 날짜 계산을 하는지에 대해 알아보겠습니다.

먼저, 날짜 형식부터 알아볼까요?

엑셀에는 굉장히 다양한 형태로 날짜를 표현할 수 있습니다.

아래와 같이 연,월일, 년월, 월일, 요일 표시, 시간 표시 등 다양한 조합을 만들어서 사용할 수 있습니다.

그러나 역시 이 중에서 가장 많이 사용되는 것은 '2019-12-01' 또는 '2019/12/01' 등과 같은 형식일 것입니다.

오늘 예제에서는 '2019-12-01'의 형식으로 사용해 보겠습니다.

1. TODAY()

기본적으로 괄호 안에 들어가는 값 없이 사용합니다.

오늘 날짜를 보내 줍니다.

'=TODAY()' => 2019-12-04로 표시됩니다.

2. NOW()

마찬가지로 괄호안에 값은 넣지 않습니다. 돌려주는 값은 오늘 날짜와 시간입니다.

'=NOW()' => 2019-12-04 09:24

* TODAT(), NOW() 함수를 사용할 때, 참조할 것은 뭔가 sheet에 이벤트가 발생하게 되면 각 함수의 값이 업데이트된다는 것입니다. 오늘 위의 함수로 수식을 만들어서 값을 확인했는데, 이틀 뒤에 다시 열게 되면, 그때의 날짜로 해당 셀의 값이 바뀌어 있으므로, 만일 처음 작성한 날짜가 바뀌면 안 된다면 함수를 쓰지 말고 날짜를 직접 입력해야 합니다.

3. YEAR(날짜), MONTH(날짜), DAY(날짜)

이 세 개의 함수는 날짜로 지정한 값에서 해당하는 년도, 월, 일을 표시해 줍니다.

[A1] 셀의 값이 '2019-12-04'라고 한다면,

'=YEAR(A1)' => 2019

'=MONTH(A1)' => 12

'=DAY(A1)' => 04

로 표시됩니다.

4. DATEDIF(시작하는 날짜, 끝나는 날짜, "계산 기준")

이 함수는 시작하는 날짜와 끝나는 날짜 사이의 차를 보여줍니다.

여기서 "계산 기준"은 Y, M, D, YD, YM, MD 중의 하나를 넣을 수 있고, 꼭 " "안에 넣어야 합니다.

Y는 두 날짜 간의 차이나는 햇수를 표시하고,

M은 두날짜간의 차이나는 개월 수를 표시하고,

D는 두날짜간의 차이나는 날수를 표시합니다.

YM은 두날짜 사이의 개월수를 표시하는데 해가 바뀌면, 바뀐 해는 무시하고 나머지 개월 수만 표시합니다.

YD는 YM과 마찬가지 방법으로 날짜를 구합니다.

마지막으로 MD는 기본 콘셉트는 동일하나, 차이나는 개월 수는 무시하고 남은 날수의 차이만을 표시합니다.

예를 들어 날짜 계산을 해 보면 다음과 같습니다.

[A1] 2015-05-21,

[A2] 2020-11-07 일 때,

'=DATEDIF(A1, A2, "Y) => 5

'=DATEDIF(A1, A2, "M) => 65

'=DATEDIF(A1, A2, "D" => 1,997

'=DATEDIF(A1, A2, "YM) => 5

'=DATEDIF(A1, A2, "YD) => 170

'=DATEDIF(A1, A2, "MD) => 17

과 같습니다.

5. 날짜의 더하기와 빼기

날짜를 더하고 빼는 것은 간단하지 만은 않습니다.

날짜 수만큼 더하거나 빼는 것은 그냥 날짜에 해당 숫자만큼 더하거나 빼면 되기 때문에 어렵지 않습니다

예를 들면, [A1] 2015-05-21, [A2] 20 일 때,

'=A1 + A2' => 2015-06-10이 됩니다. 즉, 2015년 5월 21일부터 20일이 지난 후의 날짜를 표시합니다.

반면, 개월 수를 더하거나, 년 수를 더하는 것은 좀 복잡합니다.

기본적인 원리는 YEAR(), MONTH(), DAY() 함수를 사용하여 날짜를 분리한 후, 더하고 빼는 작업을 한 후 다시 날짜 형태로 만드는 과정을 거쳐야 합니다.

그럼 한번 보겠습니다.

[A1] 2015-05-21, [A2] 7, [A3] 21 일 때,

[A1]으로부터 7년 후의 날짜를 구하려고 한다면,

'=DATE(YEAR(A1)+A2,   MONTH(A1),   DAY(A1))' => 2022-05-21 이 됩니다.

또, [A1]으로부터 21개월 후의 날짜를 구하려고 한다면,

'=DATE(YEAR(A1),   MONTH(A1)+A2,   DAY(A1))' => 2017-02-21 이 됩니다.

원리는 간단한데 수식이 좀 길어져서 보기엔 안 좋긴 합니다.

그래도, 굉장히 중요하고 자주 사용되는 기능이니 꼭 기억해 두시기 바랍니다.

* 참고로, DATE함수는 DATE(year, month, day)와 같은 형식으로 사용됩니다. year, month, day에 해당하는 숫자를 입력하면 자동으로 날짜로 계산되어 표시됩니다.

이상으로 날짜와 관련하여 가장 많이 사용하는 함수를 정리해 봤습니다.

위에서 설명한 것 이외에도 날짜와 관련된 함수는 상당히 많이 있으므로, 관심 있는 분들은 엑셀의 Help 기능을 한번 확인해 보시면 도움이 많이 될 것입니다.

너무 방대하거나 찾기가 어려우면 아래 댓글로 달아 주시면 자세히 설명드리겠습니다.

감사합니다.

오늘은 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로 나오게 됩니다.

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

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