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

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

이런 류의 목록이 있을 때, 목록에서 원하는 항목의 값을 얻고자 할 때 쓰는 함수가 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에 대해 알아 봤습니다.

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

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’을 반환한다.