여러가지 형태의 IF함수 총정리

엑셀/엑셀 기초~중급

여러가지 형태의 IF함수 총정리

내일도화이팅 2023. 7. 8. 22:05

엑셀에서 IF형식의 함수는 많이 존재한다. 하지만, 대부분 프로그래밍 언어에서는 이렇게 많은 형태의 IF문이 존재하지않는데, 이는 충분히 대체가 가능하기 때문이다. 그러니 영어단어 외우듯이 달달 외울 필요는 없다. 대체제는 얼마든지 존재하기 때문에 본질을 이해하고 이를 활용하는 것이 달달 외우고 있는 것보다 효율적이다.

다만, 엑셀에서 제공하는 여러 유형의 IF문을 사용할 경우 조금이나마 짧은 코드를 짤 수 있기때문에 알아서 나쁠껀 없다.

1. IFS

IFS는 중첩 IF문의 변외판이다. 중첩된 IF문을 사용하면 IF문이 길어지는데 이를 편하게 하기위해 만들어진 함수이다. 중첩의 형태에 따라 그냥 IF문을 사용하는게 효율적일 순 있지만, IFS도 자유자재로 사용할 수 있다면, 보다 나은 코드를 짤 수 있을 것이다. IFS는 조건들을 순차적으로 비교하며 TRUE가 나오는 조건을 찾을 경우 그에 맞는 결과를 반환하는 함수로이다. 이해하기 쉽게 예제문제로 설명하겠다.

예제문제
1) 셀 D2가 90점 이상이면 A를 반환하라.
2) 셀 D2가 90점 미만, 80점 이상이면 B를 반환하라.
3) 셀 D2가 80점 미만, 70점 이상이면 C를 반환하라.
4) 셀 D2가 70점 미만, 60점 이상이면 D를 반환하라.
5) 셀 D2가 60점 미만이면 F를 반환하라.
라는 문제가 있다고 가정하자.

위 문제를 IF(조건,참일경우 실행문, 거짓일 경우 실행문)형태인 IF문으로 해결한다고 가정했을 때,
=IF(D2 >= 90, "A", IF(D2 >= 80, "B", IF(D2 >= 70, "C", IF(D2 >= 60, "D", "F"))))
라고 작성을 하여야한다.

하지만 IFS를 사용하면
=IFS(D2 >= 90, "A", D2 >= 80, "B", D2 >= 70, "C", D2 >= 60, "D", TRUE, "F")라고 작성하면된다.

※ 맨 마지막에 TRUE를 넣은 후 "F"를 넣은 이유는 IFS는 IF와 다르게 모든 조건이 FALSE일 경우 어떤 것을 반환한다는 내용을 넣는 인수가 없다. 그래서 맨앞에 조건부터 TRUE를 탐색하여 TRUE를 찾으면 그에 맞는 결과를 반환하는 IFS 특성상 마지막에 절대 참인 TRUE를 넣어야 TRUE를 넣기전 모든 조건이 FALSE일 경우 마지막 값을 반환한다.(이 코드를 넣지않으면 모든 조건이 FALSE일 경우 오류를 반환한다.)

더 복잡한 예시문제를 풀어보자면,
1. 셀D2가 남성이고 셀D3이 40살 이상이면 A을 반환
2. 셀D2가 남성이고 셀D3이 40살 미만, 30살 이상이면 B를 반환
3. 셀D2가 남성이고 셀D3이 30살 미만, 20살 이상이면 C를 반환
4. 셀D2가 여성이고 셀D3이 40살 이상이면 D를 반환
5. 셀D2가 여성이고 셀D3이 40살 미만, 30살 이상이면 E를 반환
6. 셀D2가 여성이고 셀D3이 30살 미만, 20살 이상이면 F를 반환
7. 셀D2가 남성이고 셀D3이 모든 조건에 해당하지 않으면 "BOY"를 반환
8. 셀D2가 여성이고 셀D3이 모든 조건에 해당하지 않으면 "GIRL을 반환

위의 코드를 IFS로 문제를 풀어보면

=IFS(D2="남성", IFS(D3 >= 40, "A", D3 >= 30, "B", D3 >= 20, "C", TRUE, "BOY"), D2="여성", IFS(D3 >= 40, "D", D3 >= 30, "E", D3 >= 20, "F", TRUE, "GIRL"), TRUE,"입력오류")이다.

IF로 풀어보면
=IF(D2="남성", IF(D3 >= 40, "A", IF(D3 >= 30, "B", IF(D3 >= 20, "C", "BOY"))), IF(D2="여성", IF(D3 >= 40, "D", IF(D3 >= 30, "E", IF(D3 >= 20, "F", "GIRL"))), "입력오류")이다.

개인적으로 여러 조건이 중첩되면 IFS를 사용하는 것도 괜찮지만, 프로그래밍을 하는 입장에서 대부분의 언어에 IF밖에 없기에(언어마다 switch문이 있는 언어도 있긴하다.) 엑셀을 하지않는 이상 IFS를 사용하는 일이 적어 그냥 IF를 사용할꺼 같긴하다.

2. IFERROR

IFERROR는 내가 프로그래밍을 배우기 전 엑셀을 사용할 때 매우 유용하게 썼던 함수이고 사용법도 간단하다.

IFERROR는 오류가 발생했을 때 실행문을 실행하는 함수로
=IFERROR(ERROR를 발생하는 코드,"ERROR") 형태로 사용되고
ERROR를 발생하는 코드에서 에러를 발생시킬 경우 ERROR라는 문자열을 반환한다.

이 함수가 유용하게 사용되는 이유는 RANK나 AVERAGE와 같은 함수들의 경우 문자 값이나 빈셀로 되어있는 경우 에러를 반환하는 경우가 있다. 그리고 한 셀에서 에러가 발생하면 그 셀을 인수로 사용하는 셀들은 모두 에러에 빠지게 되어 시스템이 마비가 된다. 그리고 코드를 작성하는 사람이 에러가 발생할 것을 예상한 코드도 존재할 수 있는데, 예를들어 에러가 날줄 알면서 아직 값이 미정이라 안넣은 경우가 있다. 이 때 IFERROR를 사용하면 에러가 아니라 원하는 값을 넣을 수 있다. 아래는 예시문이다.

=IFERROR(ERROR발생 코드, "값을 입력하세요")-> 에러가 발생하면 "값을 입력하세요"이라는 문자 값이 반환된다.
=IFERROR(ERROR발생 코드, "미정") -> 에러가 발생하면 "미정"이라는 문자 값이 반환된다.

※IFERROR는 NA에러도 변환해서 반환하긴 하지만 NA에러만을 변환하여 반환하는 함수인 IFNA도 존재한다. 굳이 분류할 일이 흔하지는 않지만 언제나 그랬듯 필요에 의해 기호에 맞게 사용하면 된다.

3. SUMIF, SUMIFS

SUMIF는 선택한 범위중 조건에 맞는 셀에 입력된 값의 합계를 구하는 함수이고, SUMIFS는 선택한 범위중 조건'들'에 맞는 셀에 입력된 값의 합계를 구하는 함수이다.

맨 뒤에 붙는 S는 조건이 하나이냐 여러개이냐 차이일뿐이다. 그래서 복수형인 S만 맨 뒤에 붙은 것이다.(대신 인수의 순서정도는 다르다.)

3-1 SUMIF
SUMIF의 형태는
=SUMIF(조건을 체크할 셀, 조건, 합계를 구할 셀)이고

예시로는 아래의 값이 입력되어있다는 가정하에

	A	B	C
1	R	1
2	S	2	3
3	R	3
4	S	4
5	R	5
6	S	6
7	R	7
8	S	8
9	R	9
10	S	10

=SUMIF(B1:B10,">5")
=SUMIF(A1:A10,"R",B1:B10)
=SUMIF(B1:B10,">5",B1:B10)
=SUMIF(B1:B10,">"&C2,B1:B10)
의 형태로 코드를 짤 수 있다.

=SUMIF(B1:B10,">5")는 B1에서 B10까지의 범위에서 5보다 큰 값을 더한다는 코드이다. 결괏값은 6 + 7 + 8 + 9 + 10 으로 40이 반환된다.
=SUMIF(A1:A10,"R",B1:B10)는 A1에서 A10까지의 범위에서 "R"과 일치하는 셀에 대응하는 B1에서 B10까지의 셀의 값을 더하는 것이다.

그렇기 때문에 조건을 체크할 셀과 합계를 구할 셀의 크기가 같아야되고 대응 조건은 조건을 체크할 첫번째 셀-합계를 구할 첫번째 셀, 조건을 체크할 두번째 셀-합계를 구할 두번째 셀 이런식이다.

고로,
셀 A1은 셀 B1과 대응되고 셀 A10은 셀 B10과 대응되며,
=SUMIF(A2:A11,"R",B1:B10)일경우
셀 A2는 셀 B1에 대응되고 셀 A11은 셀 B10에 대응된다.

즉,
=SUMIF(A1:A10,"R",B1:B10)은 1 + 3 + 5 + 7 + 9 로 25를 반환한다.

=SUMIF(B1:B10,">5",B1:B10)는 =SUMIF(B1:B10,">5")와 마찬가지의 결과를 반환하는데
만약, =SUMIF(D1:D10,">5",B1:B10)으로 코드가 작성되어있었다면 D1에서부터 D10까지의 셀 중 5 초과의 값을 가진 셀에 대응하는 B1에서 B10까지의 범위의 셀의 합계를 반환한다.

=SUMIF(B1:B10,">"&C2,B1:B10)는 위의 코드들과 마찬가지인데, 위의 코드는 5라는 숫자를 제시한 반면 해당 코드는 C2라는 셀의 위치를 제시했다. C2의 값이 3이므로 =SUMIF(B1:B10,">3",B1:B10)과 같은 코드이다. 그리고 결괏값으로는 4 + 5 + 6 + 7 + 8 + 9 + 10인 49를 반환한다.

※엑셀은 문자값을 합칠때 &기호를 쓴다.

3-2 SUMIFS

SUMIFS는 SUMIF와 형태는 비슷하지만 여러 개의 조건을 넣을 수 있다는 점에서 차이가 있다.
그리고 형태는 SUMIF와 조금 다른데
=SUMIFS(합계를 구할 셀, 조건을 체크할 셀, 조건, ...)이다.

예를들어,
A1에서 A10까지 범위의 셀의 값이 "R"이고 B1에서 B10까지 범위의 셀의 값이 3 초과인 값의 합계를 구하고 싶으면,
=SUMIFS(B1:B10,A1:A10,"R",B1:B10,">3")로 짜면된다.

당연히, 이보다 많은 조건을 넣을 수 있고 =SUMIFS(B1:B10,A1:A10,"R",B1:B10,">3",C1:C10,"남성", D1:D10,"무직")
(A1부터 A10까지의 범위의 셀 값이 "R"이고 B1부터 B10까지의 범위의 셀의 값이 3보다 크고 C1부터 C10까지의 범위의 셀 값이 "남성"이며, D1부터 D10까지 범위의 셀 값이 "무직"인 셀에 대응하는 B1에서 B10까지의 셀 값의 합계를 구하는 식이다.)

이런식으로 크기만 같다면 다른 셀들의 조건도 검토 가능하다.

4. AVERAGEIF, AVERAGEIFS
AVERAGEIF와 AVERAGEIFS는 SUMIF와 SUMIFS와 비슷하다. 여기까지 따라온 똑똑하신 분들은 알겠지만, 그렇다. 합계냐 평균이냐 차이만 있을 뿐 다른건 없다.

4-1. AVERAGEIF

즉,

	A	B	C
1	R	1
2	S	2	3
3	R	3
4	S	4
5	R	5
6	S	6
7	R	7
8	S	8
9	R	9
10	S	10

에서
AVERAGEIF는
=AVERAGEIF(B1:B10,">5") -> B1부터 B10까지의 셀 중 5보다 큰 값을 가진 셀의 평균으로 결괏값은 (6 + 7 + 8 + 9 + 10) / 5인 8이다.

=AVERAGEIF(A1:A10,"R",B1:B10) -> A1에서 A10까지의 범위의 셀 중 값이 "R"인 셀에 대응하는 B1에서 B10까지의 범위의 셀의 평균으로 결괏값은 (1 + 3 + 5 + 7 + 9) / 5 인 5이다.

=AVERAGEIF(B1:B10,">5",B1:B10) -> B1에서 B10까지의 범위의 셀 중 값이 5보다 큰 셀에 대응하는 B1에서 B10까지의 범위의 셀의 평균으로 결괏값은 (6 + 7 + 8 + 9 + 10) / 5 인 8이다.

=AVERAGEIF(B1:B10,">"&C2,B1:B10) -> C2의 값이 3이므로 B1에서 B10까지의 범위의 셀 중 값이 3보다 큰 셀에 대응하는 B1에서 B10까지의 범위의 셀의 평균으로 결괏값은 (4 + 5 + 6 + 7 + 8 + 9 + 10) / 7 인 7이다.
형태 등으로 작성할 수 있다.

4-2. AVERAGEIFS
AVERAGEIFS도 SUMIFS와 마찬가지인 형태로
A1에서 A10까지 범위의 셀의 값이 "R"이고 B1에서 B10까지 범위의 셀의 값이 3 초과인 값의 평균을 구하고 싶으면,
=AVERAGEIFS(B1:B10,A1:A10,"R",B1:B10,">3")로 작성하면 되며,

=AVERAGEIFS(B1:B10,A1:A10,"R",B1:B10,">3",C1:C10,"남성", D1:D10,"무직")
(A1부터 A10까지의 범위의 셀 값이 "R"이고 B1부터 B10까지의 범위의 셀의 값이 3보다 크고 C1부터 C10까지의 범위의 셀 값이 "남성"이며, D1부터 D10까지 범위의 셀 값이 "무직"인 셀에 대응하는 B1에서 B10까지의 셀 값의 평균을 구하는 식이다.)과 같이 여러 셀의 조건을 비교할 수 도 있다.

5. COUNTIF, COUNTIFS

5-1. COUNTIF
COUNTIF는 SUMIF와 AVERAGEIF와 조금 다르다. 조건을 구할 셀과 개수를 구할 셀이 일치하기 때문이다.
형태는
=COUNTIF(조건을 검토하여 개수를 구할 셀, 조건)이다.
즉,

	A	B	C
1	R	1
2	S	2	3
3	R	3
4	S	4
5	R	5
6	S	6
7	R	7
8	S	8
9	R	9
10	S	10

에서
=COUNTIF(B1:B10,">3") -> B1에서 B10까지 범위의 셀 중에서 3보다 큰 셀의 갯수를 반환(7 반환)
=COUNTIF(A1:A10,"R") -> A1에서 A10까지 범위의 셀 중에서 값이 "R"인 셀의 갯수를 반환(5 반환)
등으로 사용된다.

5-2. COUNTIFS
COUNTIFS는 여러 조건에 부합하는 셀의 개수를 구할 수 있다.
형태는
=COUNTIFS(셀의 범위, 조건, 셀의 범위, 조건, ...)
예를들어,
A1에서 A10까지의 범위에 셀의 값이 "R"이고 이에 대응하는 B1에서 B10까지의 셀의 값이 3보다 큰 셀의 개수를 구하고 싶다면
=COUNTIFS(A1:A10, "R", B1:B10, ">3") -> A1에서 A10까지의 범위의 셀 중 값이 "R"인 셀은 1, 3, 5, 7, 9이며, 그 중 3보다 큰 값은 5, 7, 9이므로 결괏값은 3을 반환한다.

로 작성할 수 있다.

6. MAXIFS, MINIFS

MAXIFS와 MINIFS는 서로 비슷한 함수이며 다른 SUMIFS, AVERAGEIFS와 비슷하다. 최댓값을 구할 것이냐 최솟값을 구할 것이냐 차이밖에 없다. 애초에 개발자들은 사용자들이 빠르게 익힐 수 있는 방식으로 함수를 개발하였다.

6-1.MAXIFS
조건'들'(조건이 하나여도 상관없음)에 부합하는 값중 최댓값을 구하는 함수이다.
형태는
=MAXIFS(최댓값을 구할 셀의 범위, 조건을 구할 셀의 범위, 조건, ...) 이다.

	A	B
1	R	1
2	S	2
3	R	3
4	S	4
5	R	5
6	S	6
7	R	7
8	S	8
9	R	9
10	S	10

A1에서 A10까지 범위의 셀의 값이 "R"인 셀에 대응하는 B1부터 B10까지의 셀의 최댓값을 구하고 싶으면
=MAXIFS(B1:B10,A1:A10,"R")->A1에서부터 A10까지의 셀 중 값이 "R"인 셀과 대응하는 B1에서 B10까지의 셀의 값으로는 1, 3, 5, 7, 9가 있으며 이 중 최댓값은 9이기에 9를 반환한다.

로 작성하면 되고

A1에서 A10까지 범위의 셀의 값이 "R"인 셀에 대응하면서 B1부터 B10까지의 셀 중 6이하의 값을 가진 셀에 대응하는 B1부터 B10까지의 셀의 최댓값을 구하고 싶으면
=MAXIFS(B1:B10, A1:A10, "R", B1:B10, "<=6")->A1에서부터 A10까지의 셀 중 값이 "R"인 셀과 대응하는 B1에서 B10까지의 셀의 값으로는 1, 3, 5, 7, 9가 있고 B1에서부터 B10까지의 셀 중 값이 6 이하인 셀과 대응하는 B1에서 B10까지 셀의 값으로는 1, 2, 3, 4, 5, 6이 있다. 그리고 둘의 교집합은 1, 3, 5이고 1, 3, 5의 최댓값은 5이기에 5를 반환한다.

SUMIFS와 AVERAGEIFS와 마찬가지로
=MAXIFS(B1:B10,A1:A10,"R",B1:B10,"<8",C1:C10,"남성", D1:D10,"무직")
(A1부터 A10까지의 범위의 셀 값이 "R"이고 B1부터 B10까지의 범위의 셀의 값이 8보다 작고 C1부터 C10까지의 범위의 셀 값이 "남성"이며, D1부터 D10까지 범위의 셀 값이 "무직"인 셀에 대응하는 B1에서 B10까지의 셀 값의 최댓값을 구하는 식이다.)과 같이 여러 셀의 조건을 비교할 수 도 있다.

6-2.MINIFS
MINIFS는 MAXIFS와 반대로 조건'들'(조건이 하나여도 상관없음)에 부합하는 값중 최솟값을 구하는 함수이다.
형태는
=MINIFS(최솟값을 구할 셀의 범위, 조건을 구할 셀의 범위, 조건, ...) 이다.

	A	B
1	R	1
2	S	2
3	R	3
4	S	4
5	R	5
6	S	6
7	R	7
8	S	8
9	R	9
10	S	10

A1에서 A10까지 범위의 셀의 값이 "R"인 셀에 대응하는 B1부터 B10까지의 셀의 최솟값을 구하고 싶으면
=MINIFS(B1:B10,A1:A10,"R")->A1에서부터 A10까지의 셀 중 값이 "R"인 셀과 대응하는 B1에서 B10까지의 셀의 값으로는 1, 3, 5, 7, 9가 있으며 이 중 최솟값은 1이기에 1을 반환한다.

로 작성하면 되고

A1에서 A10까지 범위의 셀의 값이 "R"인 셀에 대응하면서 B1부터 B10까지의 셀 중 6 이상의 값을 가진 셀에 대응하는 B1부터 B10까지의 셀의 최댓값을 구하고 싶으면
=MINIFS(B1:B10, A1:A10, "R", B1:B10, ">=6")->A1에서부터 A10까지의 셀 중 값이 "R"인 셀과 대응하는 B1에서 B10까지의 셀의 값으로는 1, 3, 5, 7, 9가 있고 B1에서부터 B10까지의 셀 중 값이 6 이상인 셀과 대응하는 B1에서 B10까지 셀의 값으로는 6, 7, 8, 9, 10이 있다. 그리고 둘의 교집합은 7, 9이고 7, 9의 최솟값은 7이기에 7을 반환한다.

SUMIFS와 AVERAGEIFS 그리고 가장 비슷한 MAXIFS와 마찬가지로
=MINIFS(B1:B10,A1:A10,"R",B1:B10,"<8",C1:C10,"남성", D1:D10,"무직")
(A1부터 A10까지의 범위의 셀 값이 "R"이고 B1부터 B10까지의 범위의 셀의 값이 8보다 작고 C1부터 C10까지의 범위의 셀 값이 "남성"이며, D1부터 D10까지 범위의 셀 값이 "무직"인 셀에 대응하는 B1에서 B10까지의 셀 값의 최솟값을 구하는 식이다.)과 같이 여러 셀의 조건을 비교할 수 도 있다.

7. 한계

IF문으로 처리하면 여분의 데이터 크기가 필요하거나 코드가 길어질 수 도 있었을 문제들을 여러 유형의 IF문으로 짧고 간단한 코드로 해결할 수 있는 장점이 있다.

하지만, 한계 또한 존재한다.

앞서 말한 IFS를 제외한 SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS는 교집합 즉, AND연산밖에 할 수 없다.

함수를 하나만 사용해서는 OR연산(합집합) 자체가 불가능하다는 뜻이다.

	A	B
1	R	1
2	S	2
3	R	3
4	S	4
5	R	5
6	S	6
7	R	7
8	S	8
9	R	9
10	S	10

예를들어,
-A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 합을 구하라.
-A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 개수를 구하라.
-A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 평균을 구하라.
-A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 최댓값을 구하라.
-A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 최솟값을 구하라.
라는 문제들을 SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS 하나만 사용해서 풀 수 없다는 뜻이다.

하지만 걱정마라.

계속 강조하는 거지만 메모리와 시간만 받쳐주다면 대체제란 존재한다.

7-1. A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 합을 구하라.
해당 문제는 A1에서 A10까지의 셀의 값이 "R"인 경우와 B1에서 B10까지의 셀의 크기가 7이하인 셀인 경우의 합집합을 구하는 문제로 SUMIFS로 풀 수 없다.

하지만,
=SUMIF(A1:A10,"R",B1:B10) + SUMIF(B1:B10,"<=7") - SUMIFS(B1:B10,A1:A10,"R",B1:B10,"<=7")라는 식을 사용하여 구할 수 있다.

X를 A1:A10의 범위의 셀 중 값이 "R"인 셀에 대응하는 B1:B10의 범위의 셀의 값의 집합이라고 가정하고
Y를 = B1:B10의 범위의 셀의 값 중 7이하의 값의 집합이라고 가정했을 때,
"X와 Y의 합집합 = X + Y - X와 Y의 교집합"이라는 식을 이용하면 답을 구할 수 있다.

쉽게 설명하면,
1) =SUMIF(A1:A10, "R", B1:B10) -> A1에서 A10까지의 범위의 셀 값이 "R"인 셀에 대응하는 B1에서 B10까지의 범위의 셀 값은 1, 3, 5, 7, 9 이고 그 합은 1 + 3 + 5 + 7 + 9이다.

2) =SUMIF(B1:B10,"<=7") -> B1에서 B10까지의 범위의 셀 값 중 7이하인 셀은 1, 2, 3, 4, 5, 6, 7으로 1 + 2 + 3 + 4 + 5 + 6 + 7이다.

3) =SUMIFS(B1:B10, A1:A10, "R", B1:B10,"<=7") -> A1에서 A10까지의 범위의 셀 값이 "R"인 셀에 대응하는 B1에서 B10까지의 범위의 셀 값은 1, 3, 5, 7, 9 이고 B1에서 B10까지의 범위의 셀 값 중 7이하인 셀은 1, 2, 3, 4, 5, 6, 7이고 두 조건 결과의 교집합은 1, 3, 5, 7이고 합은 1 + 3 + 5 + 7이다.

즉, 1번과 2번의 합은 (1 + 3 + 5 + 7 + 9) + (1 + 2 + 3 + 4 + 5 + 6 + 7)로 1, 3, 5, 7이 중복 계산이 되기 때문에 여기서 3번을 빼면 (1 + 3 + 5 + 7 + 9) + (1 + 2 + 3 + 4 + 5 + 6 + 7) - (1 + 3 + 5 + 7) 이 되고 결과는 37로 우리가 원하는 값을 구할 수 있다.

7-2. A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 개수를 구하라.
해당 문제도 7-1번 문제와 마찬가지로
X를 A1:A10의 범위의 셀 중 값이 "R"인 셀에 대응하는 B1:B10의 범위의 셀의 값의 집합이라고 가정하고
Y를 = B1:B10의 범위의 셀의 값 중 7이하의 값의 집합이라고 가정했을 때,
"X와 Y의 합집합 = X + Y - X와 Y의 교집합"이라는 식을 이용하면 답을 구할 수 있다.

결국 원소의 개수든 원소의 합이든 원소가 구해진 뒤 COUNT나 SUM으로 연산되는 것이기 때문이다.

고로,
=SUMIF(A1:A10, "R", B1:B10) + SUMIF(B1:B10, "<=7") - SUMIFS(B1:B10, A1:A10, "R", B1:B10, "<=7")이라는 합계를 구하는 식에서 조금 응용한
=COUNTIF(A1:A10, "R") + COUNTIF(B1:B10, "<=7") - COUNTIFS(A1:A10, "R", B1:B10, "<=7")이다.

그리고 계산해보면,
1) COUNTIF(A1:A10,"R")은 A1에서 A10까지의 범위의 셀에서 "R"인 셀의 개수를 구하면 {A1, A3, A5, A7, A9}로 5를 반환한다.(SUMIF와 다르게 B1과 B10에 대응하지는 않지만, 어짜피 개수라서 대응을 하던 안하던 개수는 똑같다.)

2) COUNTIF(B1:B10, "<=7")은 B1에서 B10까지의 범위의 셀에서 7이하인 셀의 개수를 구하면 {B1, B2, B3 B4, B5, B6, B7}로 7을 반환한다.

3) COUNTIFS(A1:A10, "R", B1:B10, "<=7")은 A1에서 A10까지의 범위의 셀에서 "R"인 셀과 대응하는 B1:B10까지의 범위의 셀 중에서 7이하인 수를 구하면 {1,3,5,7}이므로 4를 반환한다.

즉,
X와 Y의 합집합 = X + Y - X와 Y의 교집합이라는 식을 이용하면,
X와 Y의 합집합 = 5(1번) + 7(2번) - 4(3번)
-> X와 Y의 합집합 = 8
최종적으로 8을 반환한다.

7-3. A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 평균을 구하라.
평균은 7-1과 7-2와 다르게
AVERAGEIF(A1:A10,"R",B1:B10) + AVERAGEIF(B1:B10,"<=7") - AVERAGEIFS(B1:B10,A1:A10,"R",B1:B10,"<=7") 형태로 풀 수 없다.

원소의 합은 더하기 연산이므로 그대로 빼기 연산을 실시하면 원하던 결과값을 얻을 수 있었고
원소의 개수 또한 개수의 더하기 연산이므로 그대로 개수 빼기 연산을 실시하면 워나던 결과값을 얻을 수 있었다.

하지만, 평균은 원소끼리 더한 다음 원소의 개수만큼 나누는 연산이므로 더하기 빼기 연산만으로 문제를 풀어나갈 수 없다.

그렇기 때문에 평균의 성질을 그대로 이용해서 함수를 구성해야한다.

우선, "평균은 원소의 합계 / 원소의 개수"이다.

그렇다면 원소의 합집합의 연산은 합집합의 원소 합계와 합집합의 원소 개수만 구할 수 있다면, 그 둘을 나누는 순간 원소의 평균을 구할 수 있음을 의미한다.

다행히도 우리는 이미 7-1과 7-2에서 합집합의 원소 합계와 합집합의 원소 평균을 구하는 식에 대해 알고있다.

그리고 그 식을 그대로 응용하여 "평균 = 원소의 합계 / 원소의 개수"이라는 식에 대입하면,
=(SUMIF(A1:A10,"R",B1:B10) + SUMIF(B1:B10,"<=7") - SUMIFS(B1:B10,A1:A10,"R",B1:B10,"<=7")) / (COUNTIF(A1:A10,"R") + COUNTIF(B1:B10,"<=7") - COUNTIFS(A1:A10, "R", B1:B10, "<=7")) 이라는 식을 구할 수 있고
7-1과 7-2에서 계산한 결과의 값을 넣으면
= 37 / 8
= 4.625 를 얻을 수 있다.

7-4. A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 최댓값을 구하라.
남은 7-4번과 남은 7-5번은 쉬어가기급으로 간단하다.

X와 Y의 합집합 중 최댓값이란 말은 "X의 최댓값과 Y의 최댓값 중 더 큰 것"을 의미한다.

즉,
A1에서 A10까지의 셀의 값이 "R"인 것과 대응되는 B1에서 B10까지의 범위의 셀 중의 최댓값을 구하는
=MAXIFS(B1:B10, A1:A10, "R") 식과

B1에서 B10까지의 셀의 크기가 7이하인 셀의 최댓값을 구하는
=MAXIFS(B1:B10, B1:B10, "<=7") 식의

결괏값 중 가장 큰 값을 구하면 된다는 것이므로
=MAX(MAXIFS(B1:B10, A1:A10, "R"), MAXIFS(B1:B10, B1:B10, "<=7"))라는 식으로 풀 수 있다.

그리하여 계산해보면,
MAXIFS(B1:B10, A1:A10, "R") -> {1, 3, 5, 7, 9} 중 최댓값은 9
MAXIFS(B1:B10, B1:B10, "<=7")-> {1, 2, 3, 4, 5, 6, 7} 중 최댓값은 7 이므로
=MAX(9,7)
-> 9와 7중에 가장 큰 값은 9이므로 9를 반환한다.

7-5. A1에서 A10까지의 셀의 값이 "R"이거나 B1에서 B10까지의 셀의 크기가 7이하인 셀의 최솟값을 구하라.
최솟값도 최댓값과 마찬가지이다.

X와 Y의 합집합 중 최솟값이란 말은 "X의 최솟값과 Y의 최솟값 중 더 작은 것"을 의미한다.

즉,
A1에서 A10까지의 셀의 값이 "R"인 것과 대응되는 B1에서 B10까지의 범위의 셀 중의 최솟값을 구하는
=MINIFS(B1:B10, A1:A10, "R") 식과

B1에서 B10까지의 셀의 크기가 7이하인 셀의 최솟값을 구하는
=MINIFS(B1:B10, B1:B10, "<=7") 식의

결괏값 중 가장 작은 값을 구하면 된다는 것이므로
=MIN(MINIFS(B1:B10, A1:A10, "R"), MINIFS(B1:B10, B1:B10, "<=7"))라는 식으로 풀 수 있다.

그리하여 계산해보면,
MINIFS(B1:B10, A1:A10, "R") -> {1, 3, 5, 7, 9} 중 최솟값은 1
MINIFS(B1:B10, B1:B10, "<=7")-> {1, 2, 3, 4, 5, 6, 7} 중 최솟값은 1 이므로
=MAX(1,1)
-> 1과 1중에 가장 작은 값은 1이므로 1을 반환한다.

이처럼 원하는 값을 출력하기 위해서는 다양한 방법이 있고 정해진 함수를 적절히 활용하면 원하는 결과를 얻을 수 있다.
위의 함수들을 외울 필요는 전혀없지만, 자신이 원하는 결과를 얻기 위해서 어떤 방식이 있을까에 대해 고민해보는 것은 매우 중요한 과정이다.

다음시간엔 행번호를 출력하는 ROW와 열번호를 출력하는 COLUMN
그리고 VLOOKUP, HLOOKUP, MATCH, 추가적으로 XLOOKUP까지 다루겠다.