[구글 스프레드시트]#32 함수 - COUNTIF 절대참조 (엑셀 공통)

지난 포스팅은 상대참조를 이용한 COUNTIF 활용법 이었습니다. 이번 시간은 절대 참조를 이용한 COUNTIF 활용법입니다.

아래에 실습파일을 첨부하여습니다. 다운로드 후 구글 스프레드시트에 업로드 하여 실습해 보세요.

 

example-2.xlsx

0.02MB

 

업로드 방법

 

 

COUNTIF - 범위 : 절대참조

 

형 식

=COUNTIF( 범위 , 조건 )

범위는 조건을 포함하고 있는 복수의 영역입니다. ( EX ) A3 : A10

수식작성 후 결과를 채우기 핸들로 구해야 할 경우 , 범위는 절대참조, 혼합참조, 상대참조( 또는 동적범위 )를 적절하게 사용해야합니다.

절대참조를 사용할 때 엑셀은 이름정의를 해두면 편합니다.

 

조건은 찾고자 하는 특정 값입니다.조건이 셀에 입력되어있지 않고 , 직접 작성해야하는 경우에는 " " (큰따옴표) 로 묶어 줍니다.

 

예제

문제1 > 3분기신청 현황 중 F5:F23셀을 기준으로 신청 과정 개수를 G5:G23셀에 나타내세요

 

1. G5셀에 셀 포인터를 둡니다. 

1-1. 신청 과정명을 이용하여 개수를 찾지 않고, 신청자명으로 신청과정개수를 알아내야합니다. 어차피 10월~12월 동안 해당 이름이 몇번 등록을 했는지 알아내면 , 신청한 과정의 개수도 알 수 있겠죠? ( 실습 데이터는 한 과정을 중복하여 들은 학생은 없습니다. )

2. = COUNTIF( 범위 , 조건 ) 

    = COUNTIF( 성명의 범위 , 결과 셀에 해당되는 조건값 )

    = COUNTIF( B5:B53 , F5 )  - 잘못된 수식

지난 시간 처럼 하나의 셀에 값을 구하거나 , 범위가 변경되며 값을 구한다면 상대참조를 해도 괜찮겠지만 , 실습 데이터의 경우 상대참조를 이용하여 값을 구하게 되면 엉뚱한 결과값이 만들어지게 됩니다.

 

잘못된 결과값 ( 상대참조와 절대참조 차이점 )

여러셀에 값을 빠르게 구하기 위해 하나의 셀에 수식을 완성 한 후 채우기 핸들을 사용하는게 보편적입니다.

실습 파일의 채우기 핸들 방향은 아래쪽 입니다.

 

 

상대 참조의 상태로 채우기 핸들을 해보았습니다.

G5셀의 첫번째 값은 별 문제 없이 잘 나왔습니다.

 

 

채우기 핸들 후 G6셀 부터 수식을 살펴보니 , 참조한 본문의 영역이 한칸 아래로 내려오는걸 확인 할 수 있습니다.

본문의 참조 영역은 채우기 핸들을 하더라도 위치가 변하지 않고 B5셀부터 참조하여야 올바른 결과값을 구할 수 있게 되겠죠?

참조하는 영역이 위치 변동 없이 첫 영역 그대로 유지해야할 경우 사용하는 참조가 절대 참조입니다. [ 사용키 : F4 ]

 

 

올바른 수식

1. G5셀에 셀 포인터를 둡니다. 

2. = COUNTIF( 범위 , 조건 ) 

    = COUNTIF( 성명 범위 절대참조 , 결과 셀에 해당되는 조건값 )

    = COUNTIF( $B$5:$B$53 , F5 )

범위 작성 요령:

1. =COUNTIF(

2. 성명 범위 드래그 ( 또는 첫번째 셀 클릭 → CTRL + SHIFT + ↓ )

3. F4

조건 작성 요령 :

* 조건을 직접 입력하는 방식이 아닌 , 값을 구할 셀에 해당되는 조건들이 F열에 나열 되어 있으므로 셀 참조를 이용하여 조건을 처리 해야합니다.

* 만약 , 조건을 직접 "천영희" 로 입력하게 되면 채우기 핸들했을때 조건이 복사되어 "천영희"에 해당 되는 결과값이 출력되게 됩니다.

조건을 직접 작성한 경우 채우기핸들 결과 → 염금동이 구해지는 것이 아니라 천영희의 신청 과정 개수가 구해지게 됨

 

 

계산 식 작성화면

채우기 핸들 후 임의에 셀을 더블클릭하여 참조의 범위가 고정 된 것을 확인해 보세요. 
상대참조와 절대참조의 차이점을 아시겠나요? 

채우기 핸들 시 범위가 변한다 : 상대참조

채우기 핸들 시 범위가 고정된다 : 절대참조 ( 우리끼리는 저~~~얼대로 안변한다고 절대참조 )

 

다음 포스팅은 혼합 참조를 사용하는 COUNTIF 입니다.