Excel에는 사용자가 결과를 얻기 위해 하나 이상의 조건을 지정해야 하는 많은 기능이 있습니다.예를 들어 여러 기준에 따라 셀을 계산하려는 경우 Excel에서 COUNTIF 또는 COUNTIFS 함수를 사용할 수 있습니다.
이 자습서에서는 Excel의 COUNTIF 및 COUNTIFS 함수에서 단일 또는 다중 기준을 사용하는 다양한 방법을 다룹니다.
이 자습서에서는 주로 COUNTIF 및 COUNTIFS 함수에 중점을 두지만 이러한 모든 예제는 여러 기준을 입력으로 사용하는 다른 Excel 함수(예: SUMIF, SUMIFS, AVERAGEIF 및 AVERAGEIFS)와 함께 사용할 수도 있습니다.
내용
Excel COUNTIF 및 COUNTIFS 함수 소개
먼저 Excel에서 COUNTIF 및 COUNTIFS 함수를 사용하는 방법을 이해합시다.
Excel COUNTIF 함수(단일 기준 포함)
Excel COUNTIF 함수는 단일 기준에 따라 셀을 계산할 때 가장 잘 작동합니다.여러 기준으로 계산하려면 COUNTIFS 함수를 사용하십시오.
통사론
=COUNTIF(범위, 조건)
입력 매개변수
- 범위 - 계산하려는 셀의 범위입니다.
- 상태 -계산할 셀 범위에 대해 평가해야 하는 기준입니다.
Excel COUNTIFS 함수(여러 기준 포함)
Excel COUNTIFS 함수는 여러 기준에 따라 셀을 계산할 때 가장 잘 작동합니다.
통사론
=COUNTIFS(기준_범위1,기준1,[기준_범위2,기준2]…)
입력 매개변수
- 기준 _ 범위 1 – 기준 1에 대해 평가하려는 셀 범위.
- 기준 1 – 평가하려는 기준은 평가할 셀을 결정하는 criteria_range1입니다.
- [기준_범위2] – 기준 2에 대해 평가하려는 셀 범위.
- [기준2] – 평가하려는 기준은 계산할 셀을 결정하기 위한 criteria_range2입니다.
이제 Excel의 COUNTIF 함수에서 여러 기준을 사용하는 몇 가지 예를 살펴보겠습니다.
Excel COUNTIF 함수에서 NUMBER 조건 사용
#1 기준이 값과 같을 때 셀 개수
조건 매개변수가 지정된 값과 동일한 셀 수를 얻으려면 조건을 직접 입력하거나 조건이 포함된 셀 참조를 사용할 수 있습니다.
아래는 숫자 9를 포함하는 셀을 계산하는 예입니다(즉, 조건 매개변수가 9와 같음).공식은 다음과 같습니다.
=COUNTIF($B$2:$B$11,D3)
위의 예(그림)에서 기준은 셀 D3에 있습니다.수식에 직접 조건을 입력할 수도 있습니다.예를 들어 다음을 사용할 수도 있습니다.
=COUNTIF($B$2:$B$11,9)
#2 조건이 값보다 클 때 셀 개수
지정된 값보다 큰 값을 가진 셀의 수를 얻으려면 보다 큼 연산자(">")를 사용합니다.수식에서 직접 사용하거나 조건과 함께 셀 참조를 사용할 수 있습니다.
Excel의 조건에서 연산자를 사용할 때마다 큰따옴표 안에 넣어야 합니다.예를 들어 조건이 10보다 크면 조건으로 ">10"을 입력해야 합니다(아래 이미지 참조).
공식은 다음과 같습니다.
=COUNTIF($B$2:$B$11,”>10″)
셀에 조건을 포함하고 셀 참조를 조건으로 사용할 수도 있습니다.이 경우 조건을 큰따옴표로 묶을 필요가 없습니다.
=COUNTIF($B$2:$B$11,D3)
조건이 셀에 있기를 원하지만 연산자와 함께 사용하고 싶지 않은 경우에도 마찬가지입니다.예를 들어, D3 셀의 숫자가 >10이 아닌 10이 되도록 할 수 있습니다.
이 경우 연산자와 셀 참조의 조합인 조건부 매개변수를 생성해야 합니다(아래 이미지 참조).
=COUNTIF($B$2:$B$11,”>”&D3)
참고: 연산자와 셀 참조를 결합할 때 연산자는 항상 큰따옴표로 묶입니다.연산자와 셀 참조는 앰퍼샌드(&)로 연결됩니다.
#3 기준이 값보다 작을 때 셀 개수
지정된 값보다 작은 값을 가진 셀의 개수를 얻으려면 보다 작음 연산자("<")를 사용합니다.수식에서 직접 사용하거나 조건과 함께 셀 참조를 사용할 수 있습니다.
Excel의 조건에서 연산자를 사용할 때마다 큰따옴표 안에 넣어야 합니다.예를 들어 기준이 수량이 5보다 작아야 하는 경우 기준으로 "<5"를 입력해야 합니다(아래 이미지 참조).
=COUNTIF($B$2:$B$11,"<5")
셀에 조건을 포함하고 셀 참조를 조건으로 사용할 수도 있습니다.이 경우 표준을 큰따옴표로 묶을 필요가 없습니다(아래 이미지 참조).
=COUNTIF($B$2:$B$11,D3)
또한 조건이 연산자가 아닌 셀에 있기를 원할 수 있습니다.예를 들어, 셀 D3에 <5 대신 5라는 숫자가 표시되도록 할 수 있습니다.
이 경우 연산자와 셀 참조의 조합인 조건부 매개변수를 생성해야 합니다.
=COUNTIF($B$2:$B$11,"<"&D3)
참고: 연산자와 셀 참조를 결합할 때 연산자는 항상 큰따옴표로 묶입니다.연산자와 셀 참조는 앰퍼샌드(&)로 연결됩니다.
#4 여러 기준으로 셀 수 - 두 값 사이
두 값 사이의 값 개수를 얻으려면 COUNTIF 함수에서 여러 조건을 사용해야 합니다.
이를 수행하는 두 가지 방법은 다음과 같습니다.
방법 XNUMX: COUNTIFS 함수 사용
COUNTIFS 함수는 여러 조건을 인수로 사용하고 모든 조건이 TRUE인 경우에만 셀을 계산할 수 있습니다.5와 10과 같이 지정된 두 값 사이의 값이 있는 셀을 계산하려면 다음 COUNTIFS 함수를 사용할 수 있습니다.
=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)
참고: 위의 수식은 5 또는 10을 포함하는 셀을 계산하지 않습니다.이러한 셀을 포함하려면 크거나 같음(>=) 및 작거나 같음(<=) 연산자를 사용합니다.공식은 다음과 같습니다.
=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″)
이러한 조건을 셀에 포함하고 셀 참조를 조건으로 사용할 수도 있습니다.이 경우 표준을 큰따옴표로 묶을 필요가 없습니다(아래 이미지 참조).
셀 참조와 연산자의 조합을 사용할 수도 있습니다(수식에 직접 연산자 입력).연산자와 셀 참조를 결합할 때 연산자는 항상 큰따옴표로 묶습니다.연산자와 셀 참조는 앰퍼샌드(&)로 연결됩니다.
방법 2: 두 개의 COUNTIF 함수 사용
조건이 여러 개인 경우 COUNTIFS를 사용하거나 COUNTIF 함수의 조합을 만들 수 있습니다.다음 수식은 동일한 작업을 수행합니다.
=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)
위의 공식에서 먼저 값이 5보다 큰 셀의 수를 찾은 다음 값이 10보다 큰 셀의 수를 뺍니다.이것은 우리에게 5의 결과를 줄 것입니다(즉, 5보다 크고 10보다 작거나 같은 값).
수식에 5와 10이 모두 포함되도록 하려면 대신 다음 수식을 사용하세요.
=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)
수식에서 "5"와 "10"을 계산에서 제외하려면 다음 수식을 사용합니다.
=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)
이러한 조건을 셀에 포함하고 셀 참조를 사용하거나 연산자와 셀 참조를 조합하여 사용할 수 있습니다.
Excel 함수에서 TEXT 조건 사용
#1 조건이 지정된 텍스트와 같을 때 셀 계산
지정된 텍스트와 정확히 일치하는 셀을 계산하려면 해당 텍스트를 조건으로 사용하면 됩니다.예를 들어 데이터 세트(아래 표시)에서 Joe라는 이름의 모든 셀을 계산하려면 다음 공식을 사용할 수 있습니다.
=COUNTIF($B$2:$B$11,"조")
이것은 텍스트 문자열이므로 텍스트 조건을 큰따옴표로 묶어야 합니다.
셀에 조건을 포함시킨 다음 해당 셀 참조를 사용할 수도 있습니다(아래 참조).
=COUNTIF($B$2:$B$11,E3)
참고: 조건 또는 조건 범위에 선행/후행 공백이 있으면 잘못된 결과를 얻을 수 있습니다.이러한 수식을 사용하기 전에 데이터를 정리해야 합니다.
#2 조건이 지정된 텍스트와 같지 않을 때 셀 개수
위의 예에서 본 것과 유사하게 지정된 텍스트를 포함하지 않는 셀을 셀 수도 있습니다.이렇게 하려면 같지 않음 연산자(<>)를 사용해야 합니다.
JOE라는 이름이 포함되지 않은 모든 셀을 계산한다고 가정하면 다음과 같이 할 수 있는 수식이 있습니다.
=COUNTIF($B$2:$B$11,”<>조”)
셀에 조건을 포함하고 셀 참조를 조건으로 사용할 수도 있습니다.이 경우 표준을 큰따옴표로 묶을 필요가 없습니다(아래 이미지 참조).
=COUNTIF($B$2:$B$11,E3)
조건이 셀에 있기를 원하지만 연산자와 함께 사용하고 싶지 않은 경우에도 마찬가지입니다.예를 들어, 셀 D3에 <>Joe 대신 Joe라는 이름이 지정되도록 할 수 있습니다.
이 경우 연산자와 셀 참조의 조합인 조건부 매개변수를 생성해야 합니다(아래 이미지 참조).
=COUNTIF($B$2:$B$11,"<>"&E3)
연산자와 셀 참조를 결합할 때 연산자는 항상 큰따옴표로 묶습니다.연산자와 셀 참조는 앰퍼샌드(&)로 연결됩니다.
Excel COUNTIF 및 COUNTIFS 함수에서 DATE 조건 사용
Excel은 날짜와 시간을 숫자로 저장합니다.그래서 우리는 그것을 숫자처럼 사용할 수 있습니다.
#1 조건이 지정된 날짜와 같을 때 셀 개수
지정된 날짜가 포함된 셀 수를 얻으려면 날짜와 함께 등호 연산자(=)를 사용합니다.
날짜로 작업하려면 날짜 값이 틀릴 가능성을 없애기 때문에 DATE 함수를 사용하는 것이 좋습니다.예를 들어 2015년 9월 1일 날짜를 사용하려면 다음과 같이 DATE 함수를 사용할 수 있습니다.
= 날짜 (2015,9,1)
지역적 차이에도 불구하고 이 수식은 동일한 날짜를 반환합니다.예를 들어, 01-09-2015는 US 날짜 구문에서는 2015년 9월 1일이고 영국 날짜 구문에서는 2015년 1월 9일입니다.그러나 이 수식은 항상 2105년 9월 1일을 반환합니다.
다음은 날짜 02-09-2015를 포함하는 셀 수를 계산하는 공식입니다.
=COUNTIF($A$2:$A$11,DATE(2015,9,2))
#2 조건이 지정된 날짜 이전 또는 이후인 경우 셀 수를 계산합니다.
지정된 날짜 이전 또는 이후의 날짜가 포함된 셀을 계산하려면 보다 작음/보다 큼 연산자를 사용할 수 있습니다.
예를 들어 2015년 9월 2일 이후의 날짜가 포함된 모든 셀을 계산하려면 다음 공식을 사용할 수 있습니다.
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))
마찬가지로 지정된 날짜 이전의 셀 수를 셀 수도 있습니다.개수에 날짜를 포함하려면 "보다 큼/보다 작음" 연산자와 함께 "같음" 연산자를 사용합니다.
날짜가 포함된 셀 참조를 사용할 수도 있습니다.이 경우 앰퍼샌드(&)를 사용하여 연산자(큰따옴표 내)를 날짜와 결합해야 합니다.
아래 예를 참조하십시오.
=COUNTIF($A$2:$A$11,”>”&F3)
#3 여러 기준으로 셀 수 - 두 날짜 사이
두 값 사이의 값 개수를 얻으려면 COUNTIF 함수에서 여러 조건을 사용해야 합니다.
단일 COUNTIFS 함수 또는 두 개의 COUNTIF 함수의 두 가지 방법으로 이 작업을 수행할 수 있습니다.
방법 XNUMX: COUNTIFS 함수 사용
COUNTIFS 함수는 여러 조건을 인수로 사용하고 모든 조건이 TRUE인 경우에만 셀을 계산할 수 있습니다.9월 2일과 9월 7일과 같이 지정된 두 날짜 사이의 값이 있는 셀을 계산하려면 다음 COUNTIFS 함수를 사용할 수 있습니다.
=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))
위의 수식은 지정된 날짜가 포함된 셀을 계산하지 않습니다.이러한 날짜도 포함하려면 크거나 같음(>=) 및 작거나 같음(<=) 연산자를 사용합니다.공식은 다음과 같습니다.
=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))
셀에 날짜를 포함하고 셀 참조를 기준으로 사용할 수도 있습니다.이 경우 연산자가 셀에 날짜를 포함하도록 할 수 없습니다.수식에 수동으로 연산자를 추가하고(큰따옴표로 묶음) 앰퍼샌드(&)를 사용하여 셀 참조를 추가해야 합니다.아래 참조:
=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)
방법 2: COUNTIF 함수 사용
여러 조건이 있는 경우 COUNTIFS 함수를 사용하거나 두 COUNTIF 함수의 조합을 만들 수 있습니다.다음 공식도 트릭을 수행합니다.
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))
위의 공식에서 먼저 날짜가 9월 2일 이후인 셀의 수를 찾은 다음 날짜가 9월 7일 이후인 셀의 수를 뺍니다.결과는 7입니다(즉, 날짜가 9월 2일 이후이고 9월 7일 또는 그 이전인 셀의 수).
수식에서 9월 2일과 9월 7일을 모두 계산하지 않으려면 대신 다음 수식을 사용하세요.
=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))
계산에서 두 날짜를 제외하려면 다음 공식을 사용하십시오.
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))
또한 셀에 표준 날짜를 설정하고 셀 참조(및 앰퍼샌드가 있는 큰따옴표로 묶인 연산자)를 사용할 수 있습니다.
COUNTIF 및 COUNTIFS 함수의 조건에서 와일드카드 문자 사용
Excel에는 세 가지 와일드카드가 있습니다.
- *(별표) - 임의의 수의 문자를 나타냅니다.예를 들어, ex*는 엑셀, 엑셀, 예, 전문가 등을 의미할 수 있습니다.
- ? (물음표) - 단일 문자를 나타냅니다.예를 들어, Trömp는 Trump 또는 Tramp를 의미할 수 있습니다.
- ~(물결표) - 텍스트에서 와일드카드 문자(~, *, ?)를 식별하는 데 사용됩니다.
COUNTIF 함수를 와일드카드와 함께 사용하면 다른 내장 계산 기능이 실패할 때 셀을 계산할 수 있습니다.예를 들어 다음과 같은 데이터세트가 있다고 가정합니다.
이제 몇 가지 예를 들어보겠습니다.
#1 텍스트가 포함된 셀 계산
텍스트가 있는 셀을 계산하려면 와일드카드 *(별표)를 사용할 수 있습니다.별표는 임의의 수의 문자를 나타내므로 텍스트가 포함된 모든 셀을 계산합니다.공식은 다음과 같습니다.
=COUNTIFS($C$2:$C$11,"*")
참고: 위의 수식은 숫자, 빈 셀 및 논리값이 포함된 셀을 무시하지만 아포스트로피가 포함된(따라서 공백으로 표시됨) 수식의 일부로 포함되었을 수 있는 셀을 계산합니다.
다음은 빈 문자열이나 아포스트로피가 있는 케이스를 처리하는 방법에 대한 자세한 자습서입니다.
#2 비어 있지 않은 셀 계산
COUNTA 기능 사용을 고려하고 있다면 다시 생각해 보십시오.
그것을 시도하십시오, 그것은 당신을 실망시킬 수 있습니다.COUNTA는 빈 문자열을 포함하는 셀도 계산합니다(일반적으로 수식에서 =""로 반환되거나 사람들이 셀에 아포스트로피를 입력하는 경우).빈 문자열이 포함된 셀은 비어 있는 것처럼 보이지만 비어 있지 않으므로 COUNTA 함수로 계산됩니다.
COUNTA는 빈 문자열을 포함하는 셀도 계산합니다(일반적으로 수식에서 =""로 반환되거나 사람들이 셀에 아포스트로피를 입력하는 경우).빈 문자열이 포함된 셀은 비어 있는 것처럼 보이지만 비어 있지 않으므로 COUNTA 함수로 계산됩니다.
따라서 공식을 사용하면 = 쿤타 (A1:A11), 11을 반환해야 할 때 10을 반환합니다.
수정 사항은 다음과 같습니다.
=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))
이 공식을 분해하여 이해합시다.
#3 특정 텍스트가 포함된 셀 수 계산
영업 담당자 이름이 J로 시작하는 모든 셀을 계산한다고 가정합니다.이는 COUNTIF 함수에서 와일드카드를 사용하여 쉽게 달성할 수 있습니다.공식은 다음과 같습니다.
=COUNTIFS($C$2:$C$11,"J*")
J* 조건은 셀의 텍스트가 J로 시작해야 하고 임의의 수의 문자를 포함할 수 있음을 지정합니다.
텍스트의 아무 곳에나 문자가 포함된 셀을 계산하려면 별표로 셀을 묶습니다.예를 들어 문자 "a"가 포함된 셀을 계산하려면 *a*를 조건으로 사용합니다.
제 다른 글들에 비해 이 글은 유난히 깁니다.네가 좋아하길 바래.의견을 남겨서 어떻게 생각하는지 알려주십시오.
