VLOOKUP 함수는 직장인이 알면 많은 도움이 되는 함수입니다. 그 이유는 VLOOKUP 함수를 통해 테이블의 특정 정보를 빠르게 검색, 동일한 테이블의 다른 열에서 관련 정보를 쉽게 검색할 수 있기 때문입니다.
SUMIF/ COUNTIF 함수 또한 기입 조건에 부합하는 셀들의 합계나 개수를 파악해주는 쉽고 간단하면서도 실무에 유용한 함수들입니다.
이번편에선 VLOOKUP/SUMIF/ COUNTIF 함수 별 사용법 및 주의사항, 그리고 수식에 사용 가능한 비교 연산자 종류까지 알아보겠습니다.우선 각 함수에 대해 설명하기 전 SUMIF/COUNTIF 함수에 사용되는 비교 연산자 종류를 간략히 알려드리겠습니다.
💡아래에서 관심 있는 주제를 클릭하여 해당 본문으로 바로 이동해보세요.
비교 연산자 종류
비교 연산자 종류입니다. SUMIF/COUNTIF 함수 사용 시 참고하면 많은 도움이 되는 연산자 입니다.
| 연산자 | 설명 | 예시 |
| = | ~인 | =10 |
| > | ~보다 큰 | >10 |
| >= | ~보다 크거나 같은 | >=10 |
| < | ~보다 작은 | <10 |
| <= | ~보다 작거나 같은 | <=10 |
| <> | ~가 아닌 | <>10, <>NASDAQ (NASDAQ이 아닌) |
VLOOKUP 함수란?
VLOOKUP 함수는 Vertical Lookup (수직으로 찾아보다)의 줄임말입니다. 하단 사진과 같이 참조 범위를 수직으로 내려가면서 값을 찾는다는 의미입니다.
VLOOKUP 인수
VLOOKUP은 총 4개의 인수를 사용하며 구문은 다음과 같습니다:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup (TRUE or FALSE)])
=VLOOKUP(찾을 값, 참조 범위,열 번호, [일치 여부 (유사 값 or 일치 값)])
| 인수 | 설명 |
| lookup_value (찾을 값) | 참조 범위의 첫번째열 (맨 좌측)에서 검색할 값 |
| table_array (참조 범위) | 값을 참조할 범위 (일반적으로 ‘F4’ 클릭을 통해 절대참조($) 입력) |
| col_index_num (열 번호) | 참조 범위 내 열 번호, 첫번째 열 (=1) |
| range_lookup (일치 여부 (유사 값 OR 일치 값)) | 찾을 값의 일치 옵션. 기본 값은 유사 일치 (TRUE)이지만 실무에선 0 or FALSE 입력하여 ‘정확히 일치’ 옵션으로 사용함. |
VLOOKUP 실전 예제
‘도서 코드’로 ‘도서 제목’ 찾기
아래 예시와 같이 TBWA 03월 구매 도서 정보가 있다 가정하겠습니다.
참조 범위 열: 1. 도서 코드, 2. 도서 제목, 3. 가격, 4. 저자, 5. 요청 수량
여기서 코드에 해당하는 도서 제목을 찾아보겠습니다.
=VLOOKUP(B17,$A$5:$E$11,2,FALSE)
2는 열 번호, 즉 ‘도서 제목’의 열 번호 입니다.
정상적으로 ‘도서 제목’을 불러왔습니다.
‘도서 코드’를 통해 ‘도서 제목’을 찾는 자세한 과정은 다음과 같습니다.
- [A5:E11] 범위의 첫째 열에서 수직으로 내려가면서 ‘도서 코드’ ‘A0304’를 찾는다.
- 같은 행의 두 번째 열에서 ‘도서 제목’인 ‘챗 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? 오류 발생하는 경우
수식에 오타가 있는 경우 발생하는 오류입니다.
| #NAME? 오류 원인 | 틀린 예시 | 옳은 예시 |
| 함수 철자 틀린 경우 | VLOOUP | VLOOKUP |
| lookup_value (찾을 값)이 텍스트이지만 큰따옴표 (” “)를 안 넣은 경우 | A0307 | “A0307” |
| 범위 설정 시 콜론 (:)을 안 넣은 경우 | A5,E11 | A5:E11 |
SUMIF 함수란?
SUMIF 함수는 범위에서 하나의 조건을 만족하는 값의 합계를 구하는 함수인데요. 함수의 조건으로 연산자 및 와일드카드를 사용할 수 있습니다. SUMIF 함수는 데이터에서 특정 조건을 만족하는 범위의 숫자 합계를 구할 때 유용하게 사용됩니다.
SUMIF 인수
SUMIF는 총 3개의 인수를 사용하며 구문은 다음과 같습니다:
=SUMIF(range, criteria, [sum_range])
=SUMIF(조건 범위, 조건, [합계 범위])
| 인수 | 설명 |
| range (조건 범위) | 조건을 적용할 범위 |
| criteria (조건) | 합계를 구할 조건 |
| [sum_range] (합계 범위) | 합계를 구할 범위 (생략 가능합니다) |
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(조건 범위, 조건)
| 인수 | 설명 |
| range (조건 범위) | 조건을 적용할 범위 |
| criteria (조건) | 개수를 셀 조건 |
COUNTIF 예시
= COUNTIF ( {10, 20, 30, 40, 50}, “>=40” )
범위에서 ‘40‘ 이상인 값의 개수를 계산합니다.
결과값으로 2를 도출합니다.
= COUNTIF ( { 채소, 채소, 육류, 육류, 과일 }, “채소“)
조건 범위에서 ‘채소‘인 셀의 개수를 계산합니다.
결과값으로 2를 도출합니다.
COUNTIF 실전 예제
같은 품목인 제품 개수 세기
식료품과 품목, 그리고 가격을 나타내는 테이블 입니다.
품목이 채소인 제품들이 몇개 인지 구해보겠습니다.
=COUNTIF(B3:B10,”채소”)
=COUNTIF(조건 범위,”조건”)
품목이 채소 (애호박, 당근, 양배추, 아스파라거스)의 개수를 4개로 정확히 산출했습니다.
특정 가격 이상 제품 개수 구하기
가격이 1,200원 이상인 제품들이 몇개 인지 구해보겠습니다.
=COUNTIF(C3:C10,”>=1200″)
=COUNTIF(조건 범위,”조건”)
1,200원 이상인 제품들의 개수를 5개로 정확히 불러왔습니다.
SUMIF/COUNTIF 함수 사용시 자주 겪는 오류
1. SUMIF/ COUNTIF 함수에서 ‘숫자값’이나 ‘셀참조’가 아닌 텍스트/연산자 를 입력하는 경우
-> 텍스트/연산자는 항상 ” ” (큰 따옴표)에 포함되어야 합니다.
| 틀린 예시 | 옳은 예시 | 비고 |
| = COUNTIF(C3:C10,>=1200) | = COUNTIF(C3:C10,“>=1200”) | 조건문이 큰 따옴표에 포함되어야 합니다. |
2. 닫힌 통합문서를 참조하는 경우 #VALUE! 오류 발생
SUMIF/ COUNTIF 함수에서 수식에 사용된 다른 통합문서 또한 실행이 되어야합니다.
-> 해당 통합문서를 열고 F9를 눌러 수식을 새로고침 하세요.
3. 조건 문자열이 255자 초과하는 경우 #VALUE! 오류 발생
SUMIF/ COUNTIF 함수에서 255자를 초과하는 문자열을 일치시킬 경우 오류가 발생합니다.
-> 가능하면 문자열을 줄이거나 텍스트 간 이어주는 & (앰퍼센드)를 텍스트 사이에 넣으세요.
아래는 텍스트 사이 & (앰퍼센드)를 기입하는 예시입니다:
| Before | After |
| =COUNTIF(B2:B12,”obfuscatequintilian”) | =COUNTIF(B2:B12,”obfuscate“&”quintilian”) |
FAQ
VLOOKUP 함수란?
VLOOKUP 함수는 Vertical Lookup (수직으로 찾아보다)의 줄임말입니다. 하단 사진과 같이 참조 범위를 수직으로 내려가면서 값을 찾는다는 의미입니다.
SUMIF 함수란?
SUMIF 함수는 범위에서 하나의 조건을 만족하는 값의 합계를 구하는 함수입니다. SUMIF 함수는 데이터에서 특정 조건을 만족하는 범위의 숫자 합계를 구할 때 유용하게 사용됩니다.
COUNTIF 함수란?
지정된 범위에서 ‘한 가지’ 조건을 만족하는 셀 개수를 계산하는 함수입니다.
지금까지 3가지 함수들에 대해 알아보았는데요. 실무에 자주 쓰이지만 가끔씩 오류가 뜨며 헷갈리는 경우도 있으니 필요하실 때 본 포스트를 방문하시면 될 것 같습니다.
