VLOOKUP 함수는 직장인이 알면 많은 도움이 되는 함수입니다. 그 이유는 VLOOKUP 함수를 통해 테이블의 특정 정보를 빠르게 검색, 동일한 테이블의 다른 열에서 관련 정보를 쉽게 검색할 수 있기 때문입니다.

SUMIF/ COUNTIF 함수 또한 기입 조건에 부합하는 셀들의 합계나 개수를 파악해주는 쉽고 간단하면서도 실무에 유용한 함수들입니다.

이번편에선 총 3가지 VLOOKUP/SUMIF/ COUNTIF 함수 사용법 및 주의사항, 그리고 수식에 사용 가능한 비교 연산자 종류까지 알아보겠습니다.우선 각 함수에 대해 설명하기 전 SUMIF/COUNTIF 함수에 사용되는 비교 연산자 종류를 간략히 알려드리겠습니다.

 

목차 

비교 연산자 종류

VLOOKUP 함수란?

VLOOKUP 인수

VLOOKUP 실전 예제

VLOOKUP 함수 사용 시 자주 겪는 오류

 

SUMIF 함수란?

SUMIF 인수

SUMIF 예시

SUMIF 실전 예제

 

COUNTIF 함수란?

COUNTIF 인수

COUNTIF 예시

COUNTIF 실전 예제

SUMIF/COUNTIF 함수 사용시 자주 겪는 오류

비교 연산자 종류

비교 연산자 종류입니다. SUMIF/COUNTIF 함수 사용 시 참고하면 많은 도움이 되는 연산자 입니다.

연산자 종류 

VLOOKUP 함수란?

VLOOKUP 함수는 Vertical Lookup (수직으로 찾아보다)의 줄임말입니다. 하단 사진과 같이 참조 범위를 수직으로 내려가면서 값을 찾는다는 의미입니다.

VLOOKUP 인수 

VLOOKUP은 총 4개의 인수를 사용하며 구문은 다음과 같습니다:

=VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup (TRUE or FALSE)]) 

=VLOOKUP(찾을 값, 참조 범위, 열 번호, [일치 여부 (유사 값 or 일치 값)]) 

VLOOKUP 인수

VLOOKUP 실전 예제

‘도서 코드’로 ‘도서 제목’ 찾기 

아래 예시와 같이 TBWA 03월 구매 도서 정보가 있다 가정하겠습니다. 

참조 범위 열: 1. 도서 코드, 2. 도서 제목, 3. 가격, 4. 저자, 5. 요청 수량

여기서 코드에 해당하는 도서 제목을 찾아보겠습니다. 

=VLOOKUP(B17,$A$5:$E$11,2,FALSE)

2는 열 번호, 즉 ‘도서 제목’의 열 번호 입니다. 

정상적으로 ‘도서 제목’을 불러왔습니다.  ‘도서 코드’를 통해 ‘도서 제목’을 찾는 자세한 과정은 다음과 같습니다. 

1. [A5:E11] 범위의 첫째 열에서 수직으로 내려가면서 ‘도서 코드’ ‘A0304’를 찾는다. 

2. 같은 행의 두 번째 열에서 ‘도서 제목’인 ‘챗 GPT – 마침내 찾아온 특이점’을 찾는다. 

가격 불러오기 

가격을 가져오기 위해 [C23] 셀에 다음 수식을 입력합니다. 

‘도서 코드로 도서 제목 찾기’와 다른 점은 ‘열 번호’입니다. 가격은 참조 범위의 세 번째 열에 기입되어 있으므로 ‘3’을 기입합니다. 

=VLOOKUP(B23,$A$5:$E$11, 3,FALSE) 

도서 코드 A0304의 가격인 11,700원 을 정상적으로 불러왔습니다.  

다른 시트에서 가격 불러오기

다른 시트에서 참조 범위를 불러와 VLOOKUP 함수를 사용할 수 있습니다. 

참조 범위를 불러오는 데엔 두 가지 방법이 있습니다. 

A. 다른 시트를 직접 입력 후 참조 범위를 불러올 수 있습니다. 

B. 혹은 다른 시트를 클릭 후 참조 범위를 드래그하여 불러올 수 있습니다. 

두 방법으로 불러올 수 있지만 완성되는 형식은 아래와 같이 동일합니다. 

=VLOOKUP(A2,Sheet1!$A$5:$E$11,3,FALSE) 

'시트 명!참조범위' 

시트 명 뒤에 ‘!‘가 붙어야 정상적으로 불러올 수 있는 점 명심하세요. 

VLOOKUP 함수 사용 시 자주 겪는 오류

1. VLOOKUP 함수 사용 시  #N/A 오류 발생 

보통 아래와 같은 두 가지 문제 때문에 #N/A 오류가 발생합니다. 

A) 조회 값이 table_array 인수의 첫 번째 열에 없는 경우

B) 조회 열이 오름차순으로 정렬되지 않은 경우 (Vlookup_TRUE 함수)

->A) 조회 값이 table_array 인수의 첫 번째 열에 없는 경우

조회 값 ‘마케팅 5.0’이 table_array 인수 A2:E11의 두번째 열(도서 제목)에 나타나기 때문에 #N/A 오류가 발생합니다. 

해결법: 

1. ‘도서 제목’이 첫번째 열에 오도록 ‘도서 코드’과 ‘도서 제목’의 위치를 바꿉니다.

2. 조회 테이블 내 위치와 상관 없이 열에서 값을 조회할 수 있는 함수인 INDEX/MATCH 함수를 사용합니다. 

->B) 조회 열이 오름차순으로 정렬되지 않은 경우 (Vlookup_TRUE 함수)

TRUE 함수 기입 시 수식을 잘 작성했는데도 오류가 난다면 조회 열이 오름차순 정렬 되어있는지 확인해야 합니다. 

해결법:

TRUE 함수는 구간이나 범위를 적용하는 함수 (유사 일치)이기에 참조 범위는 항상 오름차순으로 정렬 되어야 합니다. 

하단은 조회열 (가격대 별 비고)을 불러와 각 도서 별 비고를 채우는 예시 입니다. 왼쪽 사진은 정렬이 안된경우, 오른쪽은 정상적으로 오름차순 정렬이 된 경우 입니다. 

  TRUE 함수 사용 시 조회 열을 오름차순 정렬 하면 정상적으로 값을 불러올 수 있습니다. 

2. 첫 번째 값은 정상적으로 나오는데 드래그 후 그다음 값들은 정상적으로 안나오는 경우 

보통 Table_array 오류 때문에 발생합니다.  Table_array가 절대 참조로 범위가 고정되지않은 상태에서 드래그하면 범위가 한 칸씩 밀립니다. 

해결법:

각 범위 앞에 $를 넣거나 범위를 드래그 후 F4키를 눌러서 절대 참조하세요. 

3. #NAME? 오류 발생하는 경우

수식에 오타가 있는 경우 발생하는 오류입니다. 

VLOOKUP 함수 사용시 #NAME? 오류가 발생하는 경우

SUMIF 함수란? 

SUMIF 함수는 범위에서 하나의 조건을 만족하는 값의 합계를 구하는 함수인데요. 함수의 조건으로 연산자 및 와일드카드를 사용할 수 있습니다. SUMIF 함수는 데이터에서 특정 조건을 만족하는 범위의 숫자 합계를 구할 때 유용하게 사용됩니다. 

SUMIF 인수

SUMIF는 총 3개의 인수를 사용하며 구문은 다음과 같습니다

=SUMIF(range, criteria, [sum_range])

=SUMIF(조건 범위, 조건, [합계 범위])

SUMIF 인수

SUMIF 예시 

= SUMIF ( {10, 20, 30, 40, 50}, ">=40" )

범위에서 '40' 이상인 값의 합계를 계산합니다.

결과값으로 40 + 50=90을 도출합니다.

= SUMIF ( { 채소, 채소,육류, 육류, 과일 }, "채소", { 10, 15, 20, 35, 50 } )

조건 범위에서 '채소'인 조건의 합계를 계산합니다.

결과값으로 = 10 + 15 = 25를 도출합니다.

SUMIF 실전 예제 

종목이 같은 회사의 시가총액 합계 구하기

아래 예시는 주식 상장된 회사별 종목과 시가총액을 나타내는 테이블 입니다. 

종목이 NASDAQ인 회사들의 시가총액 합계를 구해보겠습니다. 

=SUMIF(B4:B11,"NASDAQ",C4:C11)

=SUMIF(조건 범위,"조건",[합계 범위])

종목이 NASDAQ인 회사들의 시가총액 합계가 12539.31로 정상적으로 출력되었습니다. 

특정액 이상인 회사 시가총액 합계 구하기

다음은 시가총액이 50 ($1억)이상인 회사들의 시가총액 합계를 구해보겠습니다.

=SUMIF(C4:C11,">50") 

=SUMIF(조건 범위,"조건")

시가총액이 50 ($1억)이상인 회사들의 합계가 12513.45로 정상적으로 출력되었습니다.

보시다시피 합계범위가 지정되지 않아도 조건 범위 (시가총액)에서 조건 (>50)을 만족하는 값의 합계를 정상적으로 계산합니다. 

COUNTIF 함수란?

지정된 범위에서 ‘한 가지’ 조건을 만족하는 셀 개수를 계산하는 함수입니다. 

COUNTIF 인수

COUNTIF는 총 2개의 인수를 사용하며 구문은 다음과 같습니다: 

=SUMIF(range, criteria)

=SUMIF(조건 범위, 조건)

COUNTIF 인수 

COUNTIF 예시

= COUNTIF ( {10, 20, 30, 40, 50}, ">=40" )

범위에서 '40' 이상인 값의 개수를 계산합니다.

결과값으로 2를 도출합니다.

= COUNTIF ( { 채소, 채소,육류, 육류, 과일 }, "채소")

조건 범위에서 '채소'인 셀의 개수를 계산합니다.

결과값으로 2를 도출합니다.

= COUNTIF ( { 채소, 채소, 육류, 육류, 과일 }, “채소“)

조건 범위에서 ‘채소‘인 셀의 개수를 계산합니다.

결과값으로 2를 도출합니다.

 COUNTIF 실전 예제

같은 품목인 제품 개수 세기

식료품과 품목, 그리고 가격을 나타내는 테이블 입니다. 

품목이 채소인 제품들이 몇개 인지 구해보겠습니다. 

=COUNTIF(B3:B10,"채소")

=COUNTIF(조건 범위,"조건")

품목이 채소 (애호박, 당근, 양배추, 아스파라거스)의 개수를 4개로 정확히 산출했습니다. 

특정 가격 이상 제품 개수 구하기

가격이 1,200원 이상인 제품들이 몇개 인지 구해보겠습니다. 

=COUNTIF(C3:C10,”>=1200″)

=COUNTIF(조건 범위,”조건”)

1,200원 이상인 제품들의 개수를 5개로 정확히 불러왔습니다. 

SUMIF/COUNTIF 함수 사용시 자주 겪는 오류

1. SUMIF/ COUNTIF 함수에서 ‘숫자값’이나 ‘셀참조’가 아닌 텍스트/연산자를 입력하는 경우 

-> 텍스트/연산자는 항상 ” ” (큰 따옴표)에 포함되어야 합니다. 

2. 닫힌 통합문서를 참조하는 경우 #VALUE! 오류 발생

SUMIF/ COUNTIF 함수에서 수식에 사용된 다른 통합문서 또한 실행이 되어야합니다. 

-> 해당 통합문서를 열고 F9를 눌러 수식을 새로고침 하세요. 

3. 조건 문자열이 255자 초과하는 경우 #VALUE! 오류 발생 

SUMIF/ COUNTIF 함수에서 255자를 초과하는 문자열을 일치시킬 경우 오류가 발생합니다. 

-> 가능하면 문자열을 줄이거나 텍스트 간 이어주는 & (앰퍼센드)를 텍스트 사이에 넣으세요. 

아래는 텍스트 사이 & (앰퍼센드)를 기입하는 예시입니다: 

지금까지 3가지 함수들에 대해 알아보았는데요. 실무에 자주 쓰이지만 가끔씩 오류가 뜨며 헷갈리는 경우도 있으니 필요하실 때 본 포스트를 방문하시면 될 것 같습니다.