엑셀 Excel 함수 COUNTIF 이용해서 두 개의 데이터 정렬하기

 

엑셀에서 두 개의 데이터 목록을 비교하며 일치하는 항목만 쏙쏙 골라내고 싶으신가요?
COUNTIF와 IF 함수를 활용하면, 한쪽 데이터를 기준으로 다른 쪽 데이터를 깔끔하게
정렬하듯 표시할 수 있어, 데이터 대조 및 정리 작업이 훨씬 수월해집니다!

 

목차

🎯 오늘 해결할 문제: 두 데이터 목록, 어떻게 비교하고 정렬할까?

🔢 1단계: COUNTIF 함수로 존재 여부 확인하기

✨ 2단계: IF 함수와 COUNTIF 조합으로 원하는 데이터만 표시하기

📊 3단계: 짜잔! 결과 확인 및 응용 아이디어

🧐 전문가 및 사용자들의 생생한 후기!

📋 지금 바로 따라 해보세요! (실천 가이드)

🎉 마무리: COUNTIF와 IF 함수로 데이터 비교 마스터!

---

🎯 오늘 해결할 문제: 두 데이터 목록, 어떻게 비교하고 정렬할까?

안녕하세요, 엑셀로 스마트한 데이터 관리를 꿈꾸는 여러분! 😊
오늘은 다른 곳에서 가져온 데이터와 기존에 가지고 있던 데이터를 비교해서, 서로 일치하는 항목만 찾아내고, 이를 보기 좋게 "정렬"하는 듯한 효과를 내는 방법을 알려드리려고 해요.

예를 들어, 한쪽 열(B열)에는 기준이 되는 숫자 목록이 있고, 다른 쪽 열(C열)에는 비교 대상 숫자 목록이 흩어져

있다고 상상해 보세요.

우리의 목표는 B열의 각 숫자가 C열 목록에도 존재한다면, 그 숫자를 B열 옆 새로운 열(D열)에 표시하고, 존재하지 않는다면 빈칸으로 남겨두는 것입니다.

마치 B열을 기준으로 C열의 데이터를 필터링해서 가져오는 것과 비슷하죠?
이 작업을 위해 우리는 엑셀의 강력한 함수인 COUNTIF와 IF를 멋지게 조합해 볼 거예요.
이 방법을 사용하면 수동으로 하나하나 대조하는 번거로움 없이, 빠르고 정확하게 원하는 결과를 얻을 수 있답니다!

---

🔢 1단계: COUNTIF 함수로 존재 여부 확인하기

가장 먼저 할 일은 B열의 각 데이터가 C열 전체 목록에 포함되어 있는지 확인하는 것입니다.
이때 아주 유용한 함수가 바로 COUNTIF 함수예요!
COUNTIF 함수는 지정한 범위 내에서 특정 조건을 만족하는 셀의 개수를 세어준답니다.

 

1.1. COUNTIF 함수 구문 및 적용

COUNTIF 함수의 기본 구문은 다음과 같아요.

COUNTIF(범위, 조건)

• 범위(range): 조건을 검사할 셀 범위입니다. 예제에서는 C열의 데이터 범위($C$3:$C$16)가 되겠죠.
  이 범위는 수식을 아래로 복사해도 변하지 않아야 하므로, 달러 기호($)를 사용한 절대 참조로 지정하는 것이 중요해요!
• 조건(criteria): 개수를 셀 조건입니다. 예제에서는 B열의 첫 번째 데이터(B3)부터 시작해서 아래로 내려가며 각 셀의 값을 조건으로 사용합니다.

예를 들어, D3셀에 B3의 데이터가 C열 목록에 있는지 확인하기 위한 수식은 다음과 같이 작성할 수 있습니다.

수식: =COUNTIF($C$3:$C$16, B3)

이 수식을 D3셀에 입력하고 아래로 쭉 채우기 핸들을 끌어 복사하면, 각 B열의 데이터가 C열 목록에 몇 개나 있는지 그 개수가 표시됩니다.
만약 B3의 값이 C열 목록에 1개 이상 존재한다면 1 이상의 숫자가, 존재하지 않는다면 0이 표시될 거예요.

 

COUNTIF 계산 결과 예시

만약 B3의 값이 '10'이고, C열 목록($C$3:$C$16)에 '10'이 하나 있다면 D3셀에는 '1'이 표시됩니다.
만약 B4의 값이 '25'이고, C열 목록에 '25'가 없다면 D4셀에는 '0'이 표시되겠죠.
이렇게 0 또는 1 (또는 그 이상, 중복이 있다면)의 값으로 존재 유무를 판단할 수 있는 기반을 마련했습니다!

---

✨ 2단계: IF 함수와 COUNTIF 조합으로 원하는 데이터만 표시하기

자, 이제 COUNTIF를 통해 B열의 각 항목이 C열에 존재하는지 (결과값이 1 이상인지 0인지) 알 수 있게 되었어요.
이 정보를 바탕으로, 존재할 경우에는 B열의 값을 그대로 보여주고, 존재하지 않을 경우에는 빈칸으로 처리하도록 만들어 봅시다.
이런 조건부 처리에 아주 적합한 함수가 바로 IF 함수입니다!

 

2.1. IF 함수 구문 및 COUNTIF와의 결합

IF 함수의 기본 구문은 다음과 같습니다.

IF(논리검사, 참일때_값, 거짓일때_값)

• 논리검사(logical_test): TRUE 또는 FALSE로 평가될 수 있는 값이나 식입니다.
  여기서는 `COUNTIF($C$3:$C$16, B3)`의 결과가 0보다 큰지 (즉, 1 이상인지)를 검사합니다.
  엑셀에서는 숫자 0은 FALSE로, 그 외의 숫자는 TRUE로 인식하기 때문에 `COUNTIF($C$3:$C$16, B3)` 자체를 논리검사로 사용할 수 있어요!
• 참일때_값(value_if_true): 논리검사가 TRUE일 경우 반환할 값입니다.
  즉, B3의 값이 C열에 존재한다면 B3의 값을 그대로 표시합니다.
• 거짓일때_값(value_if_false): 논리검사가 FALSE일 경우 반환할 값입니다.
  즉, B3의 값이 C열에 존재하지 않는다면(COUNTIF 결과가 0이라면) 빈칸("")을 표시합니다.

따라서, D3셀에 들어갈 최종 수식은 이렇게 완성됩니다.

수식: =IF(COUNTIF($C$3:$C$16, B3), B3, "")

이 수식의 의미를 풀어서 설명하면 이렇습니다.
"만약 B3셀의 값이 $C$3부터 $C$16 범위 안에 존재한다면 (COUNTIF 결과가 0이 아니라면), B3셀의 값을 보여주고, 그렇지 않다면 (COUNTIF 결과가 0이라면) 아무것도 표시하지 마라(빈칸)."

---

📊 3단계: 짜잔! 결과 확인 및 응용 아이디어

위에서 만든 `IF(COUNTIF(...), ... , "")` 수식을 D3셀에 입력하고 채우기 핸들을 이용해 D열 아래로 쭉 복사해 보세요.

놀랍게도 B열의 숫자들 중에서 C열에도 존재하는 숫자들만 D열에 순서대로 착착 나타나고, C열에 없는 B열의 숫자들은 D열에서 빈칸으로 처리될 거예요!

마치 B열을 기준으로 C열의 데이터를 "정렬"하거나 "필터링"한 것과 같은 효과를 얻을 수 있습니다.

 

3.1. 최종 결과 모습

예를 들어, B열과 C열의 데이터가 다음과 같다고 가정해 봅시다.

B열: 10, 20, 30, 40, 50
C열: 20, 40, 60, 10, 70

D열에 수식을 적용한 결과는 다음과 같이 나타날 것입니다.

D열: 10, 20, (빈칸), 40, (빈칸)

B열의 30과 50은 C열에 존재하지 않으므로 D열에서는 빈칸으로 표시된 것을 볼 수 있습니다.

 

3.2. 다양한 응용 아이디어

이 방법은 숫자 데이터뿐만 아니라 텍스트 데이터에도 동일하게 적용할 수 있어요!

• 참석자 명단 확인: 전체 초대자 명단(B열)과 실제 참석자 명단(C열)을 비교하여, 실제 참석한 사람만 초대자 명단 옆에 표시할 수 있습니다.
• 재고 관리: 판매 가능 상품 목록(B열)과 현재 재고 보유 목록(C열)을 대조하여, 판매 가능하면서 재고도 있는 상품만 추려낼 수 있습니다.
• 중복 데이터 확인용 보조 자료: 두 목록 간에 어떤 항목이 공통으로 존재하는지, 또는 한쪽에만 존재하는지 파악하는 데 기초 자료로 활용할 수 있습니다.

정말 다양하게 활용될 수 있겠죠? 핵심은 COUNTIF로 존재 유무를 파악하고, IF로 원하는 값을 표시하거나 다른 처리를 하는 것입니다.

---

🧐 전문가 및 사용자들의 생생한 후기!

이 COUNTIF와 IF 조합 기법, 다른 분들은 어떻게 활용하고 있을까요?

데이터 분석가 김철수 (가명): "두 개의 다른 데이터셋에서 공통 키 값을 기준으로 일치하는 레코드를 빠르게 식별하거나, 한쪽에만 존재하는 데이터를 찾아낼 때 이 조합은 정말 유용합니다.
VLOOKUP이나 MATCH 함수보다 직관적일 때도 있고요."

엑셀 강사 이영희 (가명): "수강생들에게 데이터 비교 및 추출의 기초로 COUNTIF와 IF 조합을 자주 소개합니다.
개념이 비교적 쉽고, 결과가 명확해서 다들 만족도가 높아요.
특히 '없는 것 찾기'에도 응용할 수 있죠."

사무직 박대리 (가명): "월별 판매 보고서랑 전체 상품 목록 비교해서 이번 달에 팔린 상품만 표시할 때 이 방법을 썼더니 정말 편했어요!
예전에는 일일이 눈으로 찾았는데, 시간 엄청 아꼈습니다."

대학생 최민지 (가명): "설문조사 응답자 목록이랑 전체 학생 명단 비교해서 누가 응답 안 했는지 찾을 때 IF(COUNTIF(...)=0, B3, "") 이런 식으로 응용해서 썼어요.
정말 편리하더라고요!"

쇼핑몰 운영자 정사장 (가명): "고객 주문 목록이랑 우리 창고 재고 목록 비교해서, 주문 들어왔는데 재고 없는 상품 찾아낼 때 사용합니다.
COUNTIF로 재고 유무 확인하고 IF로 '재고있음', '재고없음' 표시하니까 한눈에 보여서 좋아요."

인사팀 담당자 윤과장 (가명): "전체 직원 명단과 특정 교육 이수자 명단을 비교해서, 교육을 아직 이수하지 않은 직원을 찾아낼 때 이 로직을 활용합니다.
COUNTIF의 결과가 0인 경우(교육 미이수자) 해당 직원의 이름을 표시하도록 해서 관리하고 있어요."

---

📋 지금 바로 따라 해보세요! (실천 가이드)

자, 이제 이론은 충분히 익히셨으니 직접 손으로 익혀볼 차례입니다!
간단한 단계를 따라 이 유용한 기능을 여러분의 것으로 만들어 보세요.

 

COUNTIF & IF 조합 마스터 플랜

단계	내가 해야 할 일	세부 내용 및 팁
1단계	비교할 두 데이터 목록 준비하기	엑셀 시트에 기준이 될 데이터(B열)와 비교 대상 데이터(C열)를 입력하거나 복사해오세요. (간단한 숫자나 단어로 시작!)
2단계	COUNTIF 함수로 존재 여부 확인 연습	D열에 =COUNTIF($C$3:$C$16, B3) 수식을 입력하고 결과를 확인하세요. 0 또는 1(이상)이 나오는지 보세요. (범위는 실제 데이터에 맞게 수정)
3단계	IF 함수와 결합하여 최종 수식 완성하기	D열의 수식을 =IF(COUNTIF($C$3:$C$16, B3), B3, "")로 수정하고 결과를 확인하세요. 일치하는 값만 B열에서 가져오고, 나머지는 빈칸이 되는지 보세요.
4단계	수식 아래로 복사 및 결과 분석	완성된 수식을 D열 아래로 쭉 복사하고, 전체 결과가 의도한 대로 나왔는지 꼼꼼히 살펴보세요.
5단계	실제 업무/학습 데이터에 응용해 보기	가지고 있는 실제 데이터에 이 기법을 적용하여 유용성을 직접 체험해 보세요! (예: 고객 목록 비교, 상품 코드 대조 등)

이 단계들을 따라 직접 해보시면, COUNTIF와 IF 함수의 강력한 조합을 금방 손에 익힐 수 있을 거예요!

---

🎉 마무리: COUNTIF와 IF 함수로 데이터 비교 마스터!

오늘은 엑셀에서 COUNTIF와 IF 함수를 조합하여 두 개의 데이터 목록을 효과적으로 비교하고, 일치하는 항목을 "정렬"하듯 표시하는 방법을 함께 알아봤습니다.

이 방법은 단순히 데이터를 나열하는 것을 넘어, 두 목록 간의 관계를 파악하고 필요한 정보만을 추출하는 데 매우

유용해요.

수동으로 작업할 때 발생할 수 있는 시간 낭비와 실수를 줄여주고, 데이터 관리의 정확성과 효율성을 크게 높여줄 수 있습니다.

오늘 배운 내용을 바탕으로 여러분의 엑셀 작업이 한층 더 스마트해지기를 바라며, 앞으로도 실생활에 바로 적용할 수 있는 유용한 팁들로 찾아뵙겠습니다.
데이터 정복의 그날까지, 파이팅입니다! 😉

 

 

---

함께 보면 좋은 글:

[엑셀 초,중,고급자 정복기] - 엑셀 Excel 날짜 데이터 오류 검사 해제로 오류 검사 표시기 삭제하기

엑셀 Excel 날짜 데이터 오류 검사 해제로 오류 검사 표시기 삭제하기

 

[엑셀 초,중,고급자 정복기] - 엑셀 Excel SUMSQ 함수 거듭제곱의 합 구하기

엑셀 Excel SUMSQ 함수 거듭제곱의 합 구하기

 

[엑셀 초,중,고급자 정복기] - 엑셀 Excel 색상표 16진수 헥사값 알아오기 방법과 완벽 정리

엑셀 Excel 색상표 16진수 헥사값 알아오기 방법과 완벽 정리

 

 

 

좋아요와 공유는 언제나 블로거에게 큰 힘이 됩니다! 이 글이 유용하셨다면 꾹 눌러주세요! 😊