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

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

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

엑셀 함수 중 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에 대해 알아 봤습니다.

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

오늘은 MATCH함수와 INDEX함수를 사용하여 등급 구간에 따른 결과값을 도출해 내는 방법을 알아보겠습니다.

뭘 하려는 건지 잘 이해가 안되실 텐데요..^^ 다음의 예로 한번 보겠습니다.

예를 들면, 어떤 사람의 점수에 따라 다음의 기준으로 등급을 메기고자 합니다.

즉, 내 점수가 40점이면 4등급, 120점이면 1등급 등으로 자동으로 분류해 주는 것이지요...

이 경우, 아주 간단히는 IF 문을 여러개 중첩해서 처리할 수도 있는데, 그러면 식이 매우 지저분(?)해 지겠죠...

그래서, MATCH 함수와, INDEX함수를 사용하여 간단히 처리할 수 있습니다.

MATCH함수는 셀 범위에서 지정된 항목을 검색한 다음 범위 내에서 해당 항목의 상대 위치를 반환하는 함수입니다.

=MATCH(lookup_value, lookup_array, [match_type])

- lookup_value: (필수) lookup_array에서 찾으려는 값입니다.

                          특정 값을 직접 입력할 수도 있고 cell을 지정할 수도 있습니다.

- lookup_array: (필수) 숫자, 텍스트, 논리값 등이거나, cell을 지정할 수 있습니다.

- match_type: (선택, 기본값 1)

그럼 사용 예를 한번 보겠습니다.

위의 식에서 값인 45는 [A1:E1] 중 작거나 같은 값 중 최대값인 '30' 이 있는 상대위치(즉, 지정된 array에서 상대적 위치)를 나타내는 '2'의 값을 반환합니다. 즉, array에서 두번째 값이라는 것을 표시해 줍니다.

이것은 다시 말해, 값 45는 30보다는 크고 50보다는 작은 구간에 해당하는 값이라는 것을 나타내기도 하는 것이죠...

어렵지 않죠?^^

이것은 뒤에 설명할 INDEX함수에서 위치를 지정하기 위한 값을 구할 때 사용됩니다.

INDEX함수는 표나 범위에서 값을 반환합니다. INDEX함수는 '배열형'과 '참조형'의 두가지 방법으로 사용이 가능한데, 이번 내용에는 '배열형'으로 사용할 것입니다.

간단히 정리하면, 지정됨 범위(array)에서 가로로 몇번째, 세로로 몇번째 있는 cell의 값을 반환해라 하는 것입니다.

= INDEX(array, row_num, [column_num])

- array: (필수) cell의 범위입니다.

- row_num: (필수) 값을 반환할 배열의 행이 위치한 숫자입니다.

- column_num: (선택) 값을 반환할 배열의 열이 위치한 숫자입니다.

그럼 어떻게 사용되는지 한번 보겠습니다.

위의 식에서는 왼쪽 3x3 배열에서 첫번째 행과 세번째 열이 만나는 cell의 값인 '300'이 결과 값으로 반환됩니다.

적용하는 원리는 INDEX함수의 column 위치를 MATCH함수를 사용하여 찾아낸 값을 적용하는 것입니다.^^

아래 예를 한번 보겠습니다.

수식을 보시면, INDEX함수의 column_num을 지정하는 곳에 MATCH 함수를 써서 찾은 '점수'가 해당하는 상대위치를 사용하도록 만들었습니다.

=INDEX(B1:F2, 2, MATCH(A5, B1:F1, 1))

즉, MATCH함수를 이용하여 값 45가 해당하는 구간의 상대위치인 '2'를 찾고, INDEX함수를 사용하여 행과 열이 각각 2인 위치의 값인 '4등급'의 결과를 찾도록 하는 방법입니다.

원리를 알면 어렵지 않은 방법이므로 여러가지 분석에 많이 사용될 수 있을 것입니다.

특히, 위의 예와 같이 점수대별 등급을 정하거나, 학점을 메기는 경우 등에 사용하면 유용할 것입니다.

Excel을 사용하는 방법은 무궁무진한 것 같습니다.

잘 사용하시고 업무에 많은 효율 가져오세요...^^

오늘은 여기까지...^^

Excel에서 일정한 크기를 가지고 있는 문자열 중에서 특정 부분을 추출할 경우가 있다. 이 경우 문자열 함수를 사용하면 된다.

문자열 함수는 크게 LEFT(), RIGHT(), MID()함수가 있는데 각각을 살펴보면 다음과 같다.

1.LEFT 함수

–     LEFT함수는 텍스트 문자열의 시작위치부터 지정된 수의 문자를 반환한다.

–     LEFT(<text>, <num_chars>)

text: 문자열을 추출하고자 하는 값이며, 특정 cell을 지정할 수 있다.

num_chars: 문자열의 시작위치부터 반환하는 문자의 개수이며, 생략하는 경우는 1이다.

[예시]

‘=LEFT(A2,4)’의 경우, B2 cell의 값은 A2의 값인 ‘20010119’ 중 왼쪽에서부터 4번째까지인 ‘2001’을 반환한다.

2.RIGHT 함수

–     RIGHT함수는 지정한 문자 수에 따라 텍스트의 마지막 문자를 반환한다. 즉, 지정한 문자의 오른쪽부터 지정된 수의 문자를 반환한다.

–     RIGHT(<text>, <num_chars>)

text: 문자열을 추출하고자 하는 값이며, 특정 cell을 지정할 수 있다.

num_chars: 문자열의 끝위치부터 반환하는 문자의 개수이며, 생략하는 경우는 1이다.

[예시]

‘=RIGHT(A2,4)’의 경우, C2 cell의 값은 A2의 값인 ‘20010119’ 중 오른쪽에서부터 4번째까지인 ‘0119’를 반환한다.

3.MID 함수

–     MID함수는 시작 위치 및 길이에 따라 텍스트 문자열의 중간에서 문자열을 반환한다.

–     MID(<text>, <start_num>, <num_chars>)

text: 문자열을 추출하고자 하는 값이며, 특정 cell을 지정할 수 있다.

start_num: 추출할 첫 번째 문자의 위치이며, 생략하는 경우는 1이다.

num_chars: 반환하는 문자의 개수이다.

[예시]

‘=MID(A2,5,2)’의 경우, C2 cell의 값은 A2의 값인 ‘20010119’ 중 5번째 문자인 ‘0’부터 2개 문자열인 ‘01’을 반환한다.