[엑셀] COUNTIF 활용 : 다른 표 참조하여 값 자동 입력

 

 

 

 

[엑셀] COUNTIF 활용

다른 표 참조하여 값 자동 입력

 

 

728x90

 

엑셀은 데이터 관리와 분석에 매우 유용한 도구로, 다양한 함수와 기능을 통해 업무의 효율성을 크게 향상시킬 수 있는 현재 오피스 환경에서 없어서는 안 될 소중한 프로그램이죠. 그중에서도 COUNTIF 함수는 특정 조건에 맞는 데이터를 손쉽게 집계할 수 있어 많은 일잘러들이 자주 사용하고 있는데요. 이번 글에서는 COUNTIF 함수를 이용하여 다른 표에 있는 참석자 명단을 참조해 현재 표의 참석 여부를 자동으로 기입하는 방법을 소개해 볼게요.

 

예를 들어, 현재 표에는 순번, 주소, 성명, 참석여부와 같은 정보가 있으며 참석여부 칸은 공란으로 비어 있습니다. 반면, 참석자 명단은 별도의 표에 정리되어 있습니다. 별도의 표에 정리된 참석자 명단을 읽어서 현재 공란으로 비어있는 참석여부 정보를 자동으로 입력되도록 해 보겠습니다.

 

 

 

 

 

현재 엑셀 표의 상황

 

 

▲ 우리에게 주어진 숙제는 위와 같습니다.

좌측 표에는 주소나 성명 등의 참석대상자의 기본 정보가 입력되어 있고 참석여부는 비어있는 상태입니다.

그리고 우측 표에는 실제로 참석한 사람들의 명단이 기입되어 있습니다.

 

이제 우리는 엑셀의 countif 함수를 이용하여 우측의 참석자 명단을 읽어와서 좌측의 표에 성명이 일치하는 사람의 참석여부 칸에 '참석' 또는 '불참'이라고 자동으로 입력되도록 해야 합니다.

 

 

 

 

 

COUNTIF 함수의 기본 구문

 

COUNTIF(범위, 조건)

 

범위 : 조건을 적용할 셀의 범위

조건 : 셀 범위 내에서 세고자 하는 조건입니다. 조건은 숫자, 텍스트, 비교 연산자 등을 포함할 수 있습니다.

 

[예제1]

조건이 숫자인 경우 : 범위 A1:A10에서 값이 3인 셀의 개수를 세고 싶다면?

=COUNTIF(A1:A10, 3)

 

 

[예제2]

조건이 텍스트인 경우 : 

범위 A1:A10에서 "참석"이라는 텍스트가 포함된 셀의 개수를 세고 싶다면?

=COUNTIF( A1:A10 , "참석")

 

 

[예제3]

조건이 셀의 값인 경우 : 

범위 A1:A10 에서 셀번호 F6과 같은 값이 포함된 셀의 개수를 세고 싶다면?

=COUNTIF( A1:A10 , F6)

 

 

[예제4]

조건이 비교연산인 경우 : 

범위 A1:A10 에서 값이 5보다 큰 셀의 개수를 세고 싶다면?

=COUNTIF( A1:A10 , ">5")

 

 

[예제5]

조건이 와일드카드인 경우 : 

범위 A1:A10 에서 "B"로 지작하는 텍스트를 가진 셀의 개수를 세고 싶다면?

=COUNTIF( A1:A10 , "B*")

 

 

여러가지 응용할 수 있는 예제 중에서 우리는 예제3의 방식을 이용하기로 합니다.

= COUNTIF(참석자 명단 범위, 조회할 명단)

 

즉, 참석자 명단 전체 중에 특정인이 있으면 1, 없으면 0을 반환하게 됩니다.

 

 

 

 

 

COUNTIF 함수 적용하기

 

 

적용한 수식 :

=COUNTIF($F$4:$F$7,C4)

 

D4 셀에 위 수식을 입력하였습니다.

$F$4:$F$7는 우측에 있는 참석자 명단의 범위이고  C4는 그 범위에서 찾을 이름 즉, '송강'을 찾을 것입니다.

적용한 결과는 다음과 같습니다.

이렇게 나머지 빈 셀에도 동일한 수식을 복사하여 붙여 넣으면 위처럼 참석여부란에 0 또는 1이 채워집니다.

0은 참석자 명단에 없다(불참)이고 1은 참석자 명단에 있다(참석)를 의미합니다.

COUNTIF 함수는 리턴값을 0 또는 1로 줍니다.

 

그런데 우리는 0, 1이 아니라 '참석', '불참'으로 표기하려고 합니다.

이 방법은 다음 스텝에서 알아보겠습니다.

 

 

 

 

 

IF 함수로 1이면 참석, 0이면 불참

 

제목 그대로 1이면 '참석'으로, 0이면 '불참'으로 표기하도록 해 보겠습니다.

먼저 IF함수의 기본 용법은

IF(조건, 참일 때, 거짓일 때)

입니다.

총 3개의 인자값을 가지며 엑셀에서 조건이 0이면 거짓으로 보고 0 외의 값이면 참으로 간주됩니다.

그래서 조건이 참이면 두 번째 인자를 반환하고 거짓이면 세 번째 인자를 반환합니다.

 

따라서 앞에서 사용한 =COUNTIF($F$4:$F$7,C4) 의 결과가 1이면 "참석", 1이 아니면 "불참"으로 표기하려면 아래와 같이 써 주면 됩니다.

 

=IF(COUNTIF($F$4:$F$7,C4), "참석", "불참")

 

 

최종 적용 결과는 위와 같습니다.

 

결과적으로 우측표에 있는 참석자 4명의 경우 "참석"으로 표기하고 나머지는 "불참"으로 채워졌습니다.

 

명단이 수백 수천명인 경우 이런 방법으로 오류없이 빠르게 데이터를 채울 수 있습니다.