출고 지연이 몇 건인지 매일 세지 말고, 주문표에 ‘예정일·지연일’ 자동으로 붙이기

주문 데이터에서 은근히 시간을 잡아먹는 일이 있습니다. 바로 “오늘 기준으로 지연된 주문이 몇 건인지” 확인하는 작업입니다. 주문일, 채널, 출고일만 보고 눈으로 판단하면 처음에는 별일 아닌데, 채널별 약속 출고일이 다르고 주말·공휴일까지 끼면 금방 헷갈립니다. 특히 월요일 오전에는 금요일 주문과 주말 주문이 섞여 있어서 담당자마다 지연 기준을 다르게 보는 경우도 꽤 많습니다.

이번 글에서는 작은 주문표 하나를 끝까지 개선해 보겠습니다. 원본 주문표에 예정출고일, 지연영업일, 상태, 지연구간을 붙여서 매일 필터만 바꿔도 바로 확인되는 구조로 만드는 방식입니다. 단순히 수식 하나 넣고 끝내는 것이 아니라, 실무에서 자주 틀리는 날짜 형식, 채널명 공백, 휴일 반영 문제까지 같이 잡아보겠습니다.

아래 수식은 Microsoft 365 최신 버전을 기준으로 설명합니다. Excel 2021/2024 등 영구 버전에서는 일부 함수 지원 여부가 다를 수 있으니, 사용 중인 버전에서 함수 지원 여부를 먼저 확인해 주세요.

예제 상황: 채널마다 출고 약속일이 다르다

먼저 원본 주문표는 다음처럼 생겼다고 가정하겠습니다. 중요한 점은 주문일과 출고일이 있고, 채널별로 약속한 출고 기준이 다르다는 것입니다.

이 표를 그냥 범위로 두지 말고 먼저 Ctrl + T로 표로 바꿉니다. 표 이름은 예를 들어 주문이라고 지정해 두겠습니다. 표로 바꾸는 이유는 데이터가 아래로 추가되어도 수식과 조건부 서식이 자연스럽게 따라가기 때문입니다. 실무 파일에서 “이번 달 주문 몇 줄까지 수식을 복사했더라?”를 확인하는 시간을 줄이는 효과가 큽니다.

다음으로 채널별 출고 기준표도 별도로 만듭니다. 이 표 이름은 출고기준이라고 하겠습니다.

휴일 목록도 한 열로 따로 둡니다. 표 이름은 휴일목록, 열 이름은 날짜로 하겠습니다. 공휴일뿐 아니라 회사 창립기념일, 재고조사 휴무일처럼 내부적으로 출고하지 않는 날도 여기에 넣어두면 됩니다.

원본이 지저분하면 예정일 계산부터 흔들린다

CSV나 쇼핑몰 관리자 화면에서 내려받은 주문 데이터는 날짜가 텍스트로 들어오는 경우가 많습니다. 겉으로는 2026-06-01처럼 보여도 실제로는 왼쪽 정렬된 텍스트일 수 있습니다. 이런 상태에서 WORKDAY.INTL 함수를 쓰면 결과가 이상하게 나오거나 오류가 납니다.

가장 간단한 확인법은 주문일 열에 임시로 숫자 표시 형식을 적용해 보는 것입니다. 날짜가 정상 값이면 46000대 같은 일련번호로 바뀌고, 텍스트면 그대로 남습니다. 원본을 매번 내려받는 업무라면 Power Query에서 데이터 형식 변경을 해두는 편이 더 안전합니다. 쿼리 편집기에서 주문일과 출고일을 날짜 형식으로 바꾸고, 채널 열은 형식 → 공백 제거를 적용해 두면 채널명 뒤에 숨어 있는 공백 때문에 조회가 실패하는 문제를 미리 줄일 수 있습니다.

Power Query에서 권장 정리 흐름
- 주문일: 날짜 형식으로 변경
- 출고일: 날짜 형식으로 변경, 빈 값은 그대로 유지
- 채널: 공백 제거 및 정리
- 주문번호: 텍스트 형식 유지

여기서 주문번호를 숫자로 바꾸지 않는 것도 중요합니다. 주문번호 앞의 0이 사라지거나, 하이픈이 있는 번호가 깨지면 나중에 CS 이력이나 송장 데이터와 연결할 때 문제가 됩니다.

예정출고일은 WORKDAY.INTL로 계산한다

이제 주문표에 예정출고일 열을 추가합니다. 채널별 SLA영업일을 찾아와서 주문일 기준으로 영업일을 더해 주면 됩니다. 토요일과 일요일을 제외하고, 휴일목록까지 반영하려면 WORKDAY.INTL 함수가 좋습니다.

=WORKDAY.INTL([@주문일], XLOOKUP([@채널], 출고기준[채널], 출고기준[SLA영업일]), "0000011", 휴일목록[날짜])

"0000011"은 월~금은 근무일, 토·일은 휴무일이라는 뜻입니다. 이 부분을 제대로 이해하지 못하고 그냥 복사해 쓰면, 토요일 근무 회사나 월요일 휴무 업종에서는 결과가 틀어집니다. 예를 들어 일요일과 월요일을 쉬는 구조라면 주말 코드가 달라져야 합니다.

또 하나의 판단 포인트는 “주문일을 1일 차로 볼 것인가?”입니다. 위 수식은 주문일 다음 영업일부터 카운트합니다. 6월 1일 주문이고 SLA가 2영업일이면 6월 3일이 예정출고일이 됩니다. 만약 회사 기준이 주문 당일을 1영업일로 포함한다면 SLA영업일에서 1을 빼는 방식으로 조정해야 합니다.

=WORKDAY.INTL([@주문일], XLOOKUP([@채널], 출고기준[채널], 출고기준[SLA영업일])-1, "0000011", 휴일목록[날짜])

이 부분은 수식보다 업무 기준 확인이 먼저입니다. 같은 파일을 물류팀, CS팀, 정산팀이 함께 볼수록 “예정일 산정 기준”을 표 위쪽에 한 줄로 적어두는 것이 좋습니다.

출고된 건과 미출고 건을 같은 기준으로 나눈다

예정출고일을 만들었으면 다음은 지연영업일입니다. 출고일이 있는 건은 실제 출고일과 예정출고일을 비교하면 됩니다. 다만 단순히 출고일 - 예정출고일로 계산하면 주말과 휴일이 그대로 포함됩니다. 금요일 예정, 월요일 출고인 경우 달력상 3일 차이지만 영업일 기준으로는 1일 지연일 수 있습니다.

=IF([@출고일]="", "", MAX(0, NETWORKDAYS.INTL([@예정출고일], [@출고일], "0000011", 휴일목록[날짜])-1))

NETWORKDAYS.INTL은 시작일과 종료일을 모두 포함해서 영업일 수를 계산합니다. 그래서 같은 날 출고된 경우 1이 나오고, 여기서 1을 빼야 지연일이 0이 됩니다. 이 보정값을 빼지 않으면 정상 출고 건도 지연 1일처럼 표시되는 실수가 생깁니다.

미출고 건은 별도로 판단해야 합니다. 아직 출고일이 없는 주문에 지연영업일을 무리하게 계산하면 빈 값과 오늘 날짜가 섞여 해석이 어려워집니다. 그래서 상태 열을 하나 더 두고, 출고 여부와 오늘 날짜를 함께 보는 방식이 안정적입니다.

=IF([@출고일]="", IF(TODAY()>[@예정출고일], "미출고 지연", "출고 대기"), IF([@지연영업일]>0, "출고 지연", "정상 출고"))

이 수식은 매일 파일을 열 때 자동으로 오늘 기준 상태를 다시 보여줍니다. 단, 보고서 제출용으로 날짜를 고정해야 하는 경우에는 TODAY() 대신 기준일 셀을 따로 만들고 그 셀을 참조하는 편이 낫습니다. 예를 들어 $B$1에 기준일을 입력해 두면, 과거 특정일 기준의 지연 현황도 다시 재현할 수 있습니다.

관리자는 건수보다 ‘구간’을 보고 싶어 한다

지연 건수가 17건이라는 정보만으로는 우선순위를 잡기 어렵습니다. 1일 지연 15건과 6일 이상 지연 2건은 대응 방식이 다릅니다. 그래서 지연구간 열을 추가해 보겠습니다.

=IF([@상태]="정상 출고", "정상", IF([@상태]="출고 대기", "대기", IF([@지연영업일]<=2, "1~2일", IF([@지연영업일]<=5, "3~5일", "6일 이상"))))

여기서 흔한 실수는 미출고 지연 건의 지연일을 빈 값으로 둔 채 구간을 만들려고 하는 것입니다. 그러면 미출고 지연이 모두 1~2일 같은 구간으로 잘못 들어가거나, 아예 분류되지 않습니다. 미출고 건까지 지연일을 계산하고 싶다면 별도의 열을 하나 더 만들어 오늘 기준 지연영업일을 계산하는 방식이 좋습니다.

=IF([@출고일]="", MAX(0, NETWORKDAYS.INTL([@예정출고일], TODAY(), "0000011", 휴일목록[날짜])-1), [@지연영업일])

이 열을 쓰면 아직 출고되지 않은 주문도 오늘 기준으로 며칠 지연인지 확인할 수 있습니다. 이후 지연구간 수식은 오늘기준지연영업일을 기준으로 바꾸면 됩니다.

눈으로 찾지 않게 조건부 서식을 얹는다

수식으로 상태를 만들었더라도, 매일 실무자가 보는 화면에서는 색이 큰 도움이 됩니다. 주문표 전체 범위를 선택한 뒤 조건부 서식에서 “수식을 사용하여 서식을 지정할 셀 결정”을 선택하고 아래 기준을 넣어봅니다.

=$I2="미출고 지연"

여기서 I열이 상태 열이라고 가정했습니다. 표의 첫 데이터 행이 2행이면 $I2처럼 열은 고정하고 행은 상대참조로 둡니다. 이 부분을 $I$2처럼 행까지 고정해 버리면 첫 번째 행의 상태만 보고 모든 행이 같은 색으로 칠해지는 문제가 생깁니다.

색상은 너무 강한 빨강 하나만 쓰기보다, 미출고 지연은 연한 빨강, 출고 지연은 연한 주황, 정상 출고는 색 없음 정도로 두는 편이 읽기 좋습니다. 보고서용 파일이라면 지연구간이 6일 이상인 행만 진한 색으로 강조하는 것도 괜찮습니다.

피벗으로 오늘의 지연판을 만든다

주문표가 어느 정도 정리되면 피벗 테이블을 하나 붙여서 관리자용 요약판을 만들 수 있습니다. 행에는 채널, 열에는 지연구간, 값에는 주문번호 개수를 넣으면 채널별 지연 분포가 바로 보입니다.

이때 값 필드가 “합계”로 들어가면 주문번호가 숫자처럼 해석되어 이상한 결과가 나올 수 있습니다. 주문번호는 반드시 개수로 집계되는지 확인합니다. 또 피벗을 만들기 전 원본을 표로 바꿔두었다면, 주문이 추가되어도 피벗 새로 고침만으로 범위가 확장됩니다.

검산은 이 순서로 하면 빠르다

파일을 만든 뒤에는 수식이 맞는지 몇 건만 찍어보는 것보다, 오류가 자주 나는 지점을 순서대로 확인하는 편이 좋습니다. 먼저 주문일과 출고일이 날짜 값인지 확인합니다. 그다음 채널명이 출고기준표와 정확히 일치하는지 봅니다. 특히 오픈마켓과 오픈 마켓처럼 띄어쓰기 차이가 있으면 XLOOKUP이 못 찾습니다.

그다음 휴일목록이 실제 날짜 값인지 확인합니다. 휴일목록이 텍스트면 수식은 돌아가는 것처럼 보여도 휴일 제외가 되지 않을 수 있습니다. 마지막으로 샘플 3건 정도를 골라 달력으로 직접 세어 봅니다. 월요일 주문, 금요일 주문, 휴일 전날 주문처럼 경계에 걸린 데이터를 고르면 수식 오류를 빨리 발견할 수 있습니다.

실무에서는 “맞는 수식”보다 “기준이 설명되는 수식”이 더 오래갑니다. 출고 기준표, 휴일목록, 기준일 셀을 숨기지 말고 한 시트 안에 정리해 두면 다음 담당자가 파일을 이어받을 때도 수정 포인트가 분명해집니다. 다음 응용으로는 이 주문표에 송장 데이터까지 연결해서, 출고 지연과 배송 지연을 분리해 보는 구조로 확장해 볼 수 있습니다.