엑셀 IF 함수에 AND, OR, IFS 함께 쓰는 법

IF는 예 아니면 아니요로 답하는 질문 하나를 처리해요. 하지만 실제 업무 규칙에는 대개 그 이상이 필요해요. 여러 등급으로 나누거나, 여러 조건을 한꺼번에 따지거나, 계산할 수 없을 때도 알맞은 결과를 보여 줘야 하죠. 이 글에서는 IFS, AND, OR, IFERROR로 이런 규칙을 쓰고, 읽기 쉽게 유지하는 방법을 배워요.

예시로 영업팀의 목표 관리 시트를 써 볼게요. E~H열에는 앞으로 만들 규칙이 들어가고, 아직 몇 칸이 비어 있어요.

fx

	A	B	C	D	E	F	G	H
1	담당자	매출	목표	수수료율	상태	등급	검토 필요	달성률
2	이아리	18,400	17,500	5%	목표 달성	달성	아니요	105.1%
3	박미나	14,600	16,000	5%	목표 미달			91.3%
4	배준호	22,100	20,000	6%	목표 달성	달성	아니요	110.5%
5	오지은	9,900	12,000	4%		검토	예	82.5%
6	신동현	31,500	30,000	7%	목표 달성	달성	아니요	105%
7	한서윤	0	0	5%	목표 달성	#DIV/0!	아니요

조건 하나, 결과 둘: IF

IF는 조건을 확인해서 참이면 한 값을, 거짓이면 다른 값을 돌려줘요.

=IF(logical_test, value_if_true, value_if_false)복사

• logical_test는 B5>=C5처럼 결과가 TRUE 또는 FALSE인 비교식이에요.
• value_if_true는 조건이 TRUE일 때 셀에 표시할 값이에요.
• value_if_false는 조건이 FALSE일 때 표시할 값이에요.

오지은의 상태를 채우려면 E5를 클릭하고 다음과 같이 입력하세요.

=IF(B5>=C5,"목표 달성","목표 미달")복사

매출(9,900)이 목표(12,000)보다 작아서 조건이 FALSE가 되고, E5에 목표 미달이 표시돼요. 결과로 쓸 텍스트는 큰따옴표로 감싸고, 숫자와 셀 참조는 따옴표 없이 써요.

조건에는 다음 비교 연산자를 쓸 수 있어요.

IFS로 값을 등급별로 나누기

등급 열에는 결과가 세 가지 필요해요. 목표의 100% 이상이면 달성, 90% 이상이면 근접, 나머지는 검토예요. IFS는 조건과 결과의 쌍을 순서대로 확인해서, 처음으로 TRUE가 되는 조건의 결과를 돌려줘요.

=IFS(test1, value1, [test2, value2], ...)복사

1. 박미나의 등급 셀을 클릭하세요

   F3을 클릭하세요. 박미나는 목표 16,000에 매출 14,600으로 목표의 91%를 달성했어요(B3/C3는 0.9125예요).

2. 높은 등급부터 차례로 쓰세요

   다음과 같이 입력하세요.

   =IFS(B3/C3>=1,"달성",B3/C3>=0.9,"근접",TRUE,"검토")복사

   엑셀은 먼저 B3/C3>=1을 확인해요. FALSE이므로 B3/C3>=0.9로 넘어가고, 이건 TRUE라서 근접을 돌려줘요. 그 뒤의 조건은 아예 보지 않아요.

3. 마지막은 TRUE로 끝내세요

   마지막 쌍인 TRUE,"검토"는 나머지를 모두 받아 주는 역할이에요. TRUE는 언제나 참이기 때문에 앞의 조건을 모두 통과하지 못한 값은 여기로 와요. 이 쌍이 없으면 어떤 조건에도 맞지 않는 값은 #N/A를 돌려줘요. 목표의 82.5%를 달성한 오지은이 검토로 표시되는 것도 이 쌍 덕분이에요.

순서가 중요해요. IFS는 처음 만나는 TRUE에서 멈추기 때문에 가장 엄격한 조건을 먼저 확인해야 해요. B2/C2>=0.9를 맨 앞에 두면 105%를 달성한 이아리도 근접이 돼요. 1.05도 0.9 이상이니까요.

IFS가 나오기 전에는 같은 규칙을 IF 안에 IF를 넣어서 썼어요.

=IF(B3/C3>=1,"달성",IF(B3/C3>=0.9,"근접","검토"))복사

결과는 같지만 등급이 하나 늘 때마다 IF(와 닫는 괄호가 하나씩 늘어나요. 결과가 세 가지 이상이면 IFS가 읽기도, 확인하기도 쉬워요.

AND와 OR로 조건 결합하기

AND와 OR는 여러 조건을 받아서 TRUE나 FALSE 하나를 돌려줘요. 그래서 IF의 조건 자리에 넣기에 딱 맞아요.

• AND(test1, test2, ...)는 모든 조건이 TRUE일 때만 TRUE예요.
• OR(test1, test2, ...)는 조건이 하나라도 TRUE면 TRUE예요.

AND: 두 규칙을 모두 만족해야 해요. 목표를 달성했고 동시에 매출이 20,000을 넘은 담당자에게 보너스를 준다고 해 볼게요. 4행이라면 이렇게 써요.

=IF(AND(B4>=C4,B4>20000),"예","아니요")복사

배준호는 목표 20,000에 매출 22,100이라서 두 조건이 모두 TRUE이고 결과는 예예요. 목록 전체로 복사하면 배준호와 신동현만 예가 나와요. 이아리도 목표는 달성했지만 18,400은 20,000을 넘지 않아요.

OR: 규칙 하나만 맞아도 돼요. 검토 필요 열은 목표에 못 미쳤거나 또는 수수료율이 5% 미만인 담당자를 표시해요. G3을 클릭하고 다음과 같이 입력하세요.

=IF(OR(B3<C3,D3<0.05),"예","아니요")복사

박미나는 목표에 못 미쳤기 때문에 첫 번째 조건만으로 OR가 TRUE가 되고, G3에 예가 표시돼요. 수수료율은 정확히 5%라서 두 번째 조건은 FALSE지만, OR에서는 상관없어요.

수수료율을 D3<5%처럼 백분율로 써도 돼요. 엑셀은 이를 0.05로 읽어요. NOT은 결과를 뒤집어요. NOT(B5>=C5)는 오지은이 목표에 못 미쳤을 때 TRUE예요.

IFERROR로 오류 대신 대체 값 보여 주기

한서윤은 막 입사해서 목표가 아직 0이에요. 달성률 B7/C7은 0으로 나누게 되어 #DIV/0!이 표시돼요. IFERROR는 오류를 잡아서 대신 원하는 값을 보여 줘요.

=IFERROR(value, value_if_error)복사

H7을 클릭하고 다음과 같이 입력하세요.

=IFERROR(B7/C7,"해당 없음")복사

H7에 해당 없음이 표시돼요. 나머지 행에서는 나눗셈이 제대로 되니까 IFERROR가 달성률을 그대로 돌려줘요. H3에는 박미나의 91.3%(0.9125를 백분율로 표시한 값)가 그대로 나와요.

수식 전체에도 똑같이 쓸 수 있어요. 한서윤의 등급인 F7도 IFS가 목표로 나누기 때문에 #DIV/0!이 나와요. 이렇게 감싸세요.

=IFERROR(IFS(B7/C7>=1,"달성",B7/C7>=0.9,"근접",TRUE,"검토"),"목표 없음")복사

H7fx=IFERROR(B7/C7,"해당 없음")

	A	B	C	D	E	F	G	H
1	담당자	매출	목표	수수료율	상태	등급	검토 필요	달성률
2	이아리	18,400	17,500	5%	목표 달성	달성	아니요	105.1%
3	박미나	14,600	16,000	5%	목표 미달	근접	예	91.3%
4	배준호	22,100	20,000	6%	목표 달성	달성	아니요	110.5%
5	오지은	9,900	12,000	4%	목표 미달	검토	예	82.5%
6	신동현	31,500	30,000	7%	목표 달성	달성	아니요	105%
7	한서윤	0	0	5%	목표 달성	목표 없음	아니요	해당 없음

긴 규칙을 읽기 쉽게 유지하기

• 기준값은 셀에 두세요. 수식마다 0.9를 직접 쓰는 대신 K2 같은 셀에 넣고 $K$2로 참조하세요. 규칙을 바꿀 때 셀 하나만 고치면 돼요.
• 중첩 대신 IFS를 쓰세요. 결과가 둘이면 IF, 셋 이상이면 IFS예요.
• 긴 수식은 여러 줄로 나누세요. 수식 입력줄을 클릭하고 Alt+Enter를 누르면 수식 안에서 줄을 바꿀 수 있어요. 엑셀은 계산할 때 줄 바꿈을 무시해요.
• 경계값을 시험해 보세요. 달성률이 정확히 1이나 0.9인 값을 넣어 보고 >=와 >가 의도대로 동작하는지 확인하세요.

자주 하는 실수

• 숫자를 따옴표로 감싸기. B2>="20000"은 숫자와 텍스트를 비교해요. 엑셀은 텍스트를 항상 숫자보다 크다고 보기 때문에 모든 담당자에게 FALSE가 나와요. 숫자는 따옴표 없이 B2>=20000처럼 쓰세요.
• 텍스트를 따옴표 없이 쓰기. =IF(B5>=C5,달성,미달)은 엑셀이 달성, 미달이라는 이름을 찾기 때문에 #NAME?이 나와요. 결과로 쓸 텍스트는 큰따옴표로 감싸세요.
• 등급 순서를 거꾸로 쓰기. IFS와 중첩 IF는 처음 TRUE가 되는 조건이 이겨요. 가장 엄격한 조건부터 확인하세요.
• IFS에 마지막 쌍을 빠뜨리기. 어떤 조건에도 맞지 않는 값은 #N/A가 돼요. TRUE,와 나머지에 해당하는 결과로 끝내세요.
• 빈 데이터가 조건을 통과하기. 한서윤의 상태가 목표 달성으로 나오는 건 0이 0보다 크거나 같기 때문이에요. 빈칸이나 0이 "모름"을 뜻한다면 그것부터 확인하세요. =IF(C7=0,"목표 없음",IF(B7>=C7,"목표 달성","목표 미달"))처럼요.
• 범위를 비교식 두 개로 이어 쓰기. =IF(0.9<=B3/C3<1,...)은 엑셀에서 생각대로 동작하지 않아요. AND(B3/C3>=0.9,B3/C3<1)을 쓰세요.

연습

연습

내 목표에 맞는 등급 규칙 만들기

1. 목표 대비 진행 상황을 관리하는 시트를 여세요. 매출, 성적, 저축, 모금액 무엇이든 좋아요. 마땅한 게 없다면 이 글의 담당자 여섯 명을 입력하세요.
2. 각 행이 목표를 달성했는지 알려 주는 상태 열을 IF로 추가하세요.
3. 가장 엄격한 조건부터 시작해서 TRUE로 끝나는 세 단계 IFS 규칙으로 등급 열을 추가하세요.
4. 목표 미달이나 값 누락처럼 확인이 필요한 행을 AND나 OR로 표시하는 검토 열을 추가하세요.
5. 달성률 열을 추가해서 IFERROR로 감싸고, 목표 하나를 0으로 바꿔서 시험해 보세요.

그다음 아래 시트의 빈 셀 네 개를 채워 보세요.

직접 해 보기4개 중 0개 완료

1. 1.E5에 오지은의 매출(B5)이 목표(C5) 이상이면 목표 달성, 아니면 목표 미달을 표시해 보세요.힌트 보기
2. 2.F3에 IFS로 박미나가 목표의 100% 이상이면 달성, 90% 이상이면 근접, 나머지는 검토를 표시해 보세요.힌트 보기
3. 3.G3에 박미나가 목표에 못 미치거나 수수료율(D3)이 5% 미만이면 예, 아니면 아니요를 표시해 보세요.힌트 보기
4. 4.H7에 한서윤의 매출을 목표로 나누되, 목표가 아직 0이므로 오류 대신 해당 없음을 표시해 보세요.힌트 보기

E5fx수식 입력 셀 E5Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E	F	G	H
1	담당자	매출	목표	수수료율	상태	등급	검토 필요	달성률
2	이아리	18,400	17,500	5%	목표 달성	달성	아니요	105.1%
3	박미나	14,600	16,000	5%	목표 미달			91.3%
4	배준호	22,100	20,000	6%	목표 달성	달성	아니요	110.5%
5	오지은	9,900	12,000	4%		검토	예	82.5%
6	신동현	31,500	30,000	7%	목표 달성	달성	아니요	105%
7	한서윤	0	0	5%	목표 달성	#DIV/0!	아니요

실력 점검

• 두 가지 결과 중 하나를 돌려주는 IF 수식을 쓸 수 있나요?
• 마지막에 나머지를 받아 주는 쌍을 넣어서, 값을 세 개 이상의 등급으로 나누는 IFS 수식을 쓸 수 있나요?
• IF 안에서 AND나 OR로 두 조건을 결합할 수 있나요?
• 수식을 IFERROR로 감싸서 입력이 잘못됐을 때 오류 대신 메시지를 보여 주고, 언제 쓰면 안 되는지 설명할 수 있나요?