엑셀 조건부 조회·계산 함수 4가지 완벽 정리

엑셀에서 조건에 맞는 값을 찾거나 시간 차이를 계산할 때는 INDEX·IF, SUMPRODUCT·COUNTIF, 조건부 서식 등 4가지 함수 조합을 상황에 맞춰 사용하면 돼요.

조건에 맞는 행을 순차 출력하는 함수

특정 조건을 만족하는 행의 데이터를 차례대로 뽑아내야 할 때가 있어요.

가장 많이 쓰이는 방법은 INDEX + SMALL + ROW + IF 조합이에요. 이 함수들을 배열 수식으로 감싸면 조건을 만족하는 행 번호를 차례로 찾아서 INDEX로 해당 값을 출력해줘요.

대신 INDEX + LARGE + IF로도 역순 출력이 가능해요. 가장 최근 데이터부터 보고 싶을 때 쓰면 유용한데, SMALL 대신 LARGE를 사용하면 돼요.

함수 선택 가이드

• INDEX + SMALL/ROW + IF: 조건 만족 행을 위에서 아래로 순차 출력
• INDEX + LARGE + IF: 조건 만족 행을 아래에서 위로 역순 출력
• 조건을 만족하는 행 번호를 추출한 후 INDEX로 값을 불러오는 구조라고 생각하면 이해하기 쉬워요

조건에 맞는 값 존재 여부를 Y/N으로 표시하기

어떤 조건을 만족하는 값이 존재하는지만 판단하면 될 때는 단순하게 Y 또는 N으로 표시해야 해요.

이럴 때는 IF + SUMPRODUCT 또는 IF + COUNTIF를 조합하면 돼요.

SUMPRODUCT는 조건을 만족하는 셀의 개수를 세고, COUNTIF도 마찬가지로 조건 일치 셀의 개수를 반환해요. 이를 IF로 감싸서 개수가 0이면 N, 1개 이상이면 Y로 출력하는 식이에요.

실제 사용 예시

• SUMPRODUCT 사용: 조건이 여러 개일 때 각 조건을 AND로 연결하면서 개수를 세기
• COUNTIF 사용: 단일 조건만 있을 때 간단하게 Y/N 판단하기
• 결과 해석: 0이면 조건 불만족 → N 반환, 1 이상이면 조건 만족 → Y 반환

날짜·시간 차이 계산 및 서식 지정

날짜나 시간의 차이를 구할 때는 셀 간에 단순히 빼기만 해도 기본 계산이 돼요.

예를 들어 [종료일] – [시작일]을 하면 일 수 차이가 나오고, [종료시간] – [시작시간]을 하면 시간 차이가 나와요.

하지만 시간 단위로 정확히 표시하려면 표시 서식을 맞춰야 해요.

주요 서식 옵션

• hh:mm:ss: 시:분:초 형식으로 표시 (예: 12:45:30)
• hh”시”mm”분”ss”초”: 한글 표기 (예: 12시45분30초)
• 일반 숫자: 소수점 이하 차이를 그대로 보기

계산 자체는 간단하지만 서식이 제대로 지정되지 않으면 날짜 번호(예: 45123) 같은 이상한 값이 표시되니까 항상 서식을 확인해야 해요.

조건부 서식으로 지각·초과 시간 표시하기

엑셀 조건부 서식 기능을 쓰면 특정 셀 값이 기준을 넘으면 자동으로 배경색이나 글자색을 바꿀 수 있어요.

예를 들어 출퇴근 시간이 09:00을 넘으면 지각으로 자동 표시하고 싶다면, 조건부 서식의 조건으로 AND 함수와 시간 비교 논리를 조합하면 돼요.

구체적으로는 조건부 서식 > 수식으로 조건 설정 > AND(조건1, 조건2) 같은 식으로 여러 조건을 동시에 검사할 수 있어요.

실제 활용 예시

이런 식으로 조건부 서식을 설정하면 데이터를 한눈에 파악하기 훨씬 쉬워요.