엑셀 보다 크고 보다 작은 - egsel boda keugo boda jag-eun

엑셀 IFS 함수는 IF 함수에 S가 붙은 형태입니다. S는 영어에서 복수형일 때 붙으므로 IF가 여러 개 있는... 조건을 여러 개를 판별하고 값을 구할 수 있는 함수로 이해할 수 있습니다.

여러 조건을 판별하고 값을 구할 때 IF 함수로도 가능하지만 IF 함수가 중첩되므로 수식이 복잡해지고 사용하기가 까다롭습니다. 대신 IFS 함수로 간단하고 알아보기 쉽게 수식을 작성할 수 있습니다.

IF 함수에 대해서는는 다음 링크를 참고하세요.
[엑셀 IF 함수 사용법]

구문(Syntax)

IFS

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)

여러 조건을 판별하고 해당 조건을 만족하는 값을 반환한다.

인수 :

• logical_test1 : 첫 번째 판별조건
• value_if_true1 : 첫 번째 조건이 참(TRUE)이면 반환할 값
• logical_test2 : 두 번째 판별 조건
• value_if_true2 : 두 번째 조건이 참(TRUE)이면 반환할 값
• logical_test3 : 세 번째 판별 조건
• value_if_true3 : 세 번째 조건이 참(TRUE)이면 반환할 값

판별 조건은 총 127개까지 설정할 수 있다.

사용 예

다음과 같이 점수 구간별로 성적 등급을 정한다고 했을 때 IFS 함수로 점수별 성적 등급을 구해보겠습니다.

1) 성적 등급 산출하기

학생별 점수에 해당하는 성적 등급을 산출하려면 [D15] 셀에 다음 수식을 입력합니다.

=IFS(C15>=90,"A",C15>=80,"B",C15>=70,"C",C15>=60,"D",C15>=0,"F")

수식 풀이

[C15] 셀에 입력된 점수가 90점보다 크거나 같은 조건을 만족하면 'A'를 반환하고,
[C15] 셀에 입력된 점수가 80점보다 크거나 같은 조건을 만족하면 'B'를 반환하고,
[C15] 셀에 입력된 점수가 70점보다 크거나 같은 조건을 만족하면 'C'를 반환 하는 방식으로 'F' 등급까지 점수별 등급을 구해줍니다.

마지막의 이무성 학생의 성적이 음수로 입력되어 오류가 발생했고 나머지 학생들의 성적 등급은 정상적으로 산출되었습니다.

2) 성적 등급 산출시 오류 처리하기

앞에서 살펴본 수식에서 이무성 학생의 경우 성적이 음수로 입력되어 오류가 발생했습니다.

=IFS(C15>=90,"A",C15>=80,"B",C15>=70,"C",C15>=60,"D",C15>=0,"F")
=> 범위에 없는 값을 입력하면 #N/A 오류 발생

위의 수식은 조건을 만족할 때만(참일 때) 결과를 반환하도록 작성되었으므로 조건을 하나도 만족하지 못하면 경우가 생기면  #N/A오류가 발생합니다.

이 문제점을 보완하기 위해서 앞의 조건들을 하나도 만족시키지 못했을 경우에는 다음과 같이 '값오류'를 표시할 수 있도록 수식을 수정합니다.

=IFS(C24>=90,"A",C24>=80,"B",C24>=70,"C",C24>=60,"D",C24>=0,"F",TRUE,"값오류")

위의 수식과 동일한 형태이지만 마지막에 TRUE,"값오류"  부분이 추가되었습니다.
TRUE이면 '값오류'를 반환하라"로 해석할 수 있는데 앞의 조건들을 하나도 만족하지 못하면(즉, 모두 FALSE이면) 무조건(TRUE이므로 무조건 만족함) '값오류'를 반환하라는 의미입니다.

관련 글

엑셀에서 COUNTIF 함수를 이용하면

• 한번에 하나의 조건을 만족하는 셀의 개수를 구할 수 있지만

COUNTIFS를 이용하면

• 여러 개의 조건을 만족하는 셀의 개수를 구할 수 있습니다.

COUNTIFS는 COUNTIF 함수의 IF에 S가 붙은 형태입니다.
S는 영어에서 복수형일 때 붙으므로 IF가 여러 개 있는...
즉 조건을 여러 개 정해서 셀의 개수를 구하는 함수라고 생각하면 됩니다.

▶ COUNTIF 함수가 궁금할땐 여기를 클릭!

※ COUNTIFS는 어떻게 발음해야 할까요? 마이크로소트프 홈페이지 동영상 강의를 보면 “카운트이프스”라고 하는데 우리나라 사람들은 “카운트이프에스”라고도 많이 부릅니다.

구문(Syntax)

COUNTIFS

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

여러 조건(criteria)에 맞는 셀의 개수를 구해준다.

인수 :

• criteria_range1 : 첫번째 조건을 적용할 범위
• criteria1 : 개수를 구할 첫번째 조건
• criteria_range2 : 두번째 조건을 적용할 범위
• criteria2 : 개수를 구할 두번째 조건
• …

criteria_range, criteria 는 최대 127개까지 지정할 수 있다.

사용 예

1) 특정 조건을 만족하는 셀의 개수 구하기

아래 그림의 자료에서

• 분류가 '노트류'이면서
• 상품이 '스프링노트'인

것의 개수를 구하려면 다음과 같이 수식을 입력합니다.

=COUNTIFS(C5:C12,"노트류",D5:D12,"스프링노트")

함수의 인수를 살펴보면 다음과 같습니다

수식을 정리해 보면 COUNTIFS함수는 아래 그림과 같이

• 분류가 입력된 첫 번째 조건범위 [C5:C12]에서 값이 '노트류'이고
• 상품이 입력된 두 번째 조건범위 [D5:D12]에서 값이 '스프링노트'인 것을

만족하는 행의 갯수 2를 구해줍니다.

2) 와일드카드 문자로 찾아서 조건을 만족하는 셀의 개수 구하기

이번에는

• 분류가 '노트류'이면서
• 상품 이름에  '스프링노트'가 포함된 것의 개수를 구해보겠습니다.

[B28] 셀에 다음 수식을 입력합니다.

=COUNTIFS(C19:C26,"노트류",D19:D26,"*스프링노트*")

*(별표)는 여러 문자를 대신하므로 '스프링노트'라는 문자열 앞뒤로 몇개의 문자열이 오든 상관없이 '스프링노트'라는 값을 포함한 셀은 개수에 포함됩니다.

분류가 노트류이고 상품이름에 '스프링노트'를 포함한 것은 스프링노트고급형 1개, 스프링노트 2개가 있으므로 COUNTIFS함수는 개수 3을 구해줍니다.

※ 참고 : 와일드카드 문자는 좀 더 정교한 방법으로 값을 찾을 때 사용하는데 다음 2가지가 있습니다.

• ?(물음표) : 한 개의 임의의 문자열을 의미
• *(별표) : 여러 개의 임의의 문자열을 의미

3) 숫자 조건을 만족하는 셀의 개수 구하기

이번에는 다음 표에서

• 분류가 '노트류'이면서
• 판매금액이 10만원 이상(10만원보다 크거나 같은)인 행의 갯수를 구해보겠습니다.

[B42] 셀에 다음 수식을 입력합니다.

=COUNTIFS(C33:C40,"노트류",G33:G40,">=100000")

위에서 분류가 '노트류'이고, 판매금액이 10만원 이상인 것은

• 33행의 210,000원
• 34행의 300,000원 이므로

위 수식은 개수 2을 구해줍니다.

※ 참고 : 위 수식에서 '>=' 연산자가 사용되었는데 특정 값보다 크거나 같은 것을 의미합니다.
연산자를 사용하면 다음과 같이 다양한 방법으로 숫자 조건을 설정할 수 있습니다.

• =100 : 100인
• <>100 : 100이 아닌
• >100 : 100보다 큰
• >=100 : 100보다 크거나 같은(100이상인)
• <100 : 100보다 작은(100미만인)
• <=100 : 100보다 작거나 같은

4) 다른 시트의 자료를 참조하여 참석자 수 집계

이번에는 실무에서 많이 사용하는 '다른 시트의 자료를 집계하는 방법'을 알아보겠습니다.

다음과 같이 교육 참석 현황 자료가 있을 때 이 자료에서 교육과정별, 참석 부서별 참석자 수를 집계해 보겠습니다.

<표 만들기>

교육과정명과 부서가 변경될 때 표에서 교육과정명과 부서명만 바꾸면, 바꾼이름으로 COUNTIFS 함수가 작동하도록 아래와 같이 표를 먼저 만듭니다.

<수식 입력>

• 참석자수를 구하는 수식은 [E47]셀에 다음과 같이 입력
  =COUNTIFS(교육참석현황!$A$2:$A$22,집계!B47,교육참석현황!$C$2:$C$22,집계!C47)
• 불참자수는 정원에서 참석자수를 빼주도록 수식 입력
  =D47-E47
• 마지막으로 [E47:F47]에 입력된 수식을 복사하여 [E48:F55]범위에 붙여 넣으면 집계표가 완성됩니다.

수식을 정리해 보면 COUNTIFS함수는 아래 그림과 같이

• 1-1) 첫번째 조건범위인 교육참석현황 시트의 [$A$2:$A$22] 범위의 값이
• 1-2) 집계 시트의 [B47]셀의 값과 같은 '직장내 성희롱 예방교육'이고
• 2-1) 두번째 조건범위인 교육참석현황 시트의 [C$2:$C$22] 범위의 값이
• 2-2) 집계 시트의 [C47]셀의 값과 같은 '인사팀'인 것을

만족하는 행의 갯수 3을 구해줍니다.

관련 글