홈블로그리소스Excel에서 온라인 주문 및 배송 추적하기: 템플릿, 수식 및 자동화 시점

Excel에서 온라인 주문 및 배송 추적하기: 템플릿, 수식 및 자동화 시점

17TRACK

온라인 주문과 배송을 Excel에서 추적할 수 있습니다. 패키지당 한 행을 사용하고, 일관된 상태 목록과 기한이 지난 배송 및 오래된 확인 기록을 표시하는 수식을 적용하세요. 아래 템플릿은 실용적인 시작점을 제공합니다. Excel은 배송 정보를 정리하고 우선순위를 지정할 수 있지만, 수식만으로는 운송업체의 실시간 이벤트를 가져올 수 없습니다. 이러한 업데이트에는 수동 확인이나 연결된 데이터 소스가 필요합니다.

Excel 템플릿 수식으로 온라인 주문 및 배송 추적하기

이 가이드는 소규모 이커머스 팀과 여러 건의 입고 주문을 관리하는 모든 사람을 위해 작성되었습니다. 배송 기록과 후속 작업을 분리하여 스프레드시트가 두 가지 질문에 답하도록 합니다. 패키지는 어디에 있으며, 다음에 누가 조치를 취해야 하는가?

주문이 아니라 패키지를 중심으로 추적표 만들기

주문 번호는 구매를 식별합니다. 운송장 번호는 운송업체 시스템 내의 배송을 식별합니다. 둘은 서로 바꿔 사용할 수 없습니다. 하나의 주문이 여러 패키지로 배송될 수 있으며, 각 패키지의 배송일이나 상태가 다를 수 있습니다.

패키지당 한 행을 사용하고 필요한 경우 주문 ID를 반복 입력하세요. 주문이 아직 발송되지 않았다면 운송장 번호 없이 임시 행을 만든 다음, 배송 정보가 उपलब्ध해지면 해당 행을 업데이트하거나 분할하세요. 여러 운송장 번호를 하나의 셀에 입력하지 마세요. 필터링, 중복 확인, 상태 업데이트가 어려워집니다.

개인 구매 추적표라면 담당자는 본인으로 지정하면 됩니다. 비즈니스용 추적표라면 예외 사항을 조사하고, 운송업체나 판매자에게 연락하거나 고객과 소통할 담당자를 지정하세요. 책임자가 없는 상태는 쉽게 간과될 수 있습니다.

복사하여 붙여 넣을 수 있는 Excel 배송 추적 템플릿

아래의 탭으로 구분된 머리글을 복사하여 빈 워크시트의 A1 셀에 붙여 넣으세요. 머리글은 A열부터 L열까지 배치되어야 합니다. 이는 시작용 레이아웃이며, 다운로드 가능한 파일이나 실시간 추적 통합 기능이 아닙니다.

Order ID	Tracking number	Carrier	Ship date	Promised delivery	Status	Last carrier event	Last checked	Tracking URL	Days open	Review flag	Owner

주문 데이터를 붙여 넣기 전에 A열과 B열을 텍스트로 서식 지정하세요. 운송장 번호는 수량이 아니라 식별자입니다. 이를 숫자로 처리하면 앞의 0이 제거되거나 긴 숫자 문자열이 변경될 수 있습니다. 나중에 서식을 변경해도 이미 손실된 정보가 안정적으로 복원되지는 않습니다. 필요한 경우 원본 식별자를 다시 가져오세요.

열입력할 내용작업 규칙
A: Order ID스토어 또는 내부 주문 참조 번호분할 배송에서는 주문 ID를 반복 입력합니다.
B: Tracking number패키지에 부여된 식별자텍스트로 유지하고 문자를 정확히 보존합니다.
C: Carrier해당 번호와 연결된 운송업체여러 약어 대신 일관된 이름을 사용합니다.
D: Ship date배송 기간 계산을 시작하는 날짜발송일인지 운송업체의 수령 확인일인지 정의합니다.
E: Promised delivery적용되는 배송 약속약속이 없으면 비워 두며 기한을 임의로 만들지 않습니다.
F: Status표준화된 배송 범주관리되는 목록에서 선택합니다.
G: Last carrier event가장 최근 이벤트의 내용과 시간표준화된 상태의 근거가 되는 세부 정보를 보존합니다.
H: Last checked누군가 마지막으로 추적 정보를 확인한 시점계속 변경되는 수식이 아니라 고정된 타임스탬프를 입력합니다.
I: Tracking URL확인된 추적 페이지 링크목적지와 운송장 번호를 분리하여 유지합니다.
J: Days open계산된 배송 경과 기간별도의 규칙을 의도적으로 만들지 않는 한 달력상의 날짜를 사용합니다.
K: Review flag계산된 후속 조치 알림이는 운송업체 상태가 아니라 내부 규칙입니다.
L: Owner후속 조치 책임자예외가 발생하기 전에 담당자를 지정합니다.

행을 더 추가하기 전에 시트 설정하기

  1. 범위를 Excel 표로 변환합니다. 삽입 > 표를 선택하고 범위에 머리글이 있는지 확인합니다. 표를 사용하면 필터링이 쉬워지고 계산 열의 수식을 추가 행에 적용할 수 있습니다.
  2. 날짜 서식을 적용합니다. D열과 E열은 날짜로 서식 지정합니다. H열은 날짜와 시간을 모두 표시하도록 지정합니다. 가져온 값이 날짜처럼 보이는 텍스트가 아니라 실제 Excel 날짜인지 확인합니다.
  3. 상태 드롭다운을 만듭니다. 다음과 같은 목록으로 데이터 유효성 검사를 사용합니다: Not shipped, Label created, In transit, Out for delivery, Delivered, Exception, Returned.
  4. 머리글 행을 고정합니다. 긴 목록을 검토할 때 열 레이블이 계속 표시되도록 합니다.
  5. 확인 기준을 추가합니다. O1에 “Recheck after days”를 입력하고 P1에 2를 입력합니다. 이는 내부 검토 간격의 예시이며 운송업체 서비스 기준이 아닙니다.

F열을 표준화하더라도 G열에는 운송업체 이벤트 문구를 그대로 유지하세요. 예를 들어 “Label created”를 자동으로 “In transit”으로 바꾸면 안 됩니다. 라벨이 생성되었다고 해서 운송업체가 소포를 수령했다는 의미는 아니기 때문입니다.

추적표를 유용하게 만드는 수식

아래 수식은 위의 열 배치와 영문 함수 이름, 쉼표 구분 기호를 사용합니다. 일부 지역의 Excel 설정에서는 세미콜론을 사용합니다. 2행에 입력하고 아래로 채우거나 Excel 표에서 계산 열로 적용하세요.

배송 경과일 계산

J2에 다음을 입력합니다.

=IF(OR(D2="",F2="Delivered",F2="Returned"),"",MAX(0,TODAY()-INT(D2)))

이 수식은 배송이 진행 중인 패키지에 대해 발송일 이후의 달력상 날짜 수를 반환합니다. 완료되었거나 반송된 배송은 계속해서 기간이 늘어나지 않도록 빈칸으로 둡니다. 이 수식은 운송업체의 영업일 기준 약속에 대한 운송 성과를 측정하지 않습니다.

과거 배송 소요 시간이 필요하다면 별도의 Delivered at 열을 추가하고, 기록된 배송일과 발송일의 차이를 계산하세요. 실제 배송일 대신 오늘 날짜를 사용하지 마세요.

검토가 필요한 배송 표시

K2에 다음을 입력합니다.

=IF(A2="","",IF(F2="Delivered","Closed",IF(F2="Returned","Review return",IF(F2="Exception","Review exception",IF(AND(E2<>"",TODAY()>INT(E2)),"Past promised date",IF(B2="","Check dispatch",IF(H2="","Check tracking",IF(NOW()-H2>=$P$1,"Recheck status","Monitor"))))))))

확인 순서가 중요합니다. 배송 완료는 먼저 종료 처리하고, 반송과 예외에는 구체적인 알림을 표시하며, 약속 날짜가 지난 배송은 일반적인 확인보다 우선 처리합니다. 운송장 번호가 없는 패키지는 일반적인 추적 업데이트가 아니라 발송 확인 대상으로 분류됩니다.

“Past promised date”는 시트에 입력된 날짜가 지났다는 의미입니다. 이것만으로 소포가 분실되었거나 보상 의무가 발생했거나 운송업체가 약관을 위반했다는 뜻은 아닙니다. 에스컬레이션하기 전에 약속의 출처와 최신 배송 정보를 확인하세요.

오래된 확인 규칙은 H2 이후 경과한 시간을 측정합니다. P1을 2로 설정하면 기록된 확인 없이 이틀이 지난 레코드에 플래그가 표시됩니다. 업무 흐름에 맞게 간격을 조정하세요. TODAY()와 NOW()는 Excel이 다시 계산할 때 업데이트되며 백그라운드 모니터링 서비스가 아닙니다.

검토 대기열의 미처리 항목 수 세기

추적 표 외부에 다음 요약 수식을 배치합니다.

=COUNTIF(K2:K1000,"Review exception")+COUNTIF(K2:K1000,"Review return")+COUNTIF(K2:K1000,"Past promised date")+COUNTIF(K2:K1000,"Check dispatch")+COUNTIF(K2:K1000,"Check tracking")+COUNTIF(K2:K1000,"Recheck status")

이 수식은 고유 주문이나 고객이 아니라 조치가 필요한 패키지 행을 계산합니다. 필요한 경우 범위를 확장하거나 관련 구조화된 표 참조로 바꾸세요. 대시보드에서도 이 차이를 명확히 표시하세요. 하나의 주문에 세 개의 패키지가 있으면 배송 기록은 세 개가 될 수 있습니다.

확인된 추적 URL을 읽기 쉬운 링크로 변환

선택 사항인 추가 열에서 다음을 사용합니다.

=IF(I2="","",HYPERLINK(I2,"Track package"))

이 수식은 I2에 저장된 주소를 엽니다. 배송 이벤트를 가져오거나 주소가 안전한지 확인하지는 않습니다. 신뢰할 수 있는 추적 서비스나 운송업체의 링크를 사용하고, 형식을 확인하지 않은 상태에서 운송업체별 URL을 만들지 마세요.

시트를 꾸미는 것이 아니라 의사 결정을 지원하도록 서식 사용하기

검토 플래그 또는 전체 행에 조건부 서식을 적용하세요. 예외와 약속 날짜 초과 기록에는 강한 경고 색상을 사용하고, 일반적인 재확인에는 더 옅은 경고 색상을 사용하며, 종료된 배송에는 차분한 스타일을 적용합니다. 색상 없이도 워크시트를 이해할 수 있도록 텍스트 레이블을 계속 표시하세요.

A2:L1000에 적용되는 전체 행 규칙에는 =$K2="Past promised date"와 같은 수식을 사용할 수 있습니다. 그러면 해당 배송이 강조 표시됩니다. 달러 기호는 상태 열을 고정하고 행 번호는 변경되도록 합니다.

다른 배치를 가져오기 전에 중복 기록도 확인하세요. 분할 배송에서는 동일한 주문 ID가 반복되는 것이 정상입니다. 하지만 동일한 운송업체와 운송장 번호의 조합이 반복되면 조사해야 합니다. 실수로 중복된 기록일 수 있지만, 기본 주문 관계를 확인하지 않고 자동으로 삭제하지 마세요.

추적표를 신뢰할 수 있게 유지하는 일일 업무 흐름

  1. 새 배송을 추가하거나 가져옵니다. 식별자를 텍스트로 유지하고 각 패키지에 자체 행이 있는지 확인합니다.
  2. 검토 플래그로 필터링합니다. 예외와 기한이 지난 약속부터 시작한 다음, 누락된 추적 정보와 오래된 확인 기록을 처리합니다.
  3. 추적 소스를 확인합니다. 운송업체 페이지를 열거나 다중 운송업체 서비스를 사용하여 패키지를 추적합니다. 스프레드시트의 운송장 번호와 운송업체를 비교합니다.
  4. 근거 정보를 업데이트합니다. G열에 최신 이벤트를 기록하고 F열에서 적절한 상태를 선택한 다음 H열에 고정된 확인 타임스탬프를 입력합니다.
  5. 다음 조치를 지정합니다. 담당자가 고객 메시지, 조사 참조 번호 또는 후속 조치 날짜를 기록해야 한다면 조치 메모 열을 추가합니다.
  6. 종료된 기록을 대조합니다. 배송 완료 상태가 주문의 모든 패키지가 아니라 올바른 패키지에 적용되는지 확인합니다.

“Last checked”에 =NOW()를 입력하지 마세요. 재계산될 때마다 값이 바뀌어 확인하지 않은 기록이 최근에 검토된 것처럼 보일 수 있습니다. 타임스탬프는 실제 확인 또는 성공적인 자동 업데이트를 나타내야 합니다.

고객 정보는 필요한 최소한으로 유지하세요. 공유 운영 추적표에는 일반적으로 모든 행에 전체 배송 주소, 전화번호, 완전한 주문 정보가 필요하지 않습니다. 액세스를 제한하고 개인정보가 포함된 통제되지 않은 사본을 배포하지 마세요.

배송 추적을 자동화해야 하는 경우

워크시트가 길어졌을 때가 아니라 상태를 최신 상태로 유지하는 일이 병목이 될 때 자동화하세요. 보편적인 배송 건수 기준은 없습니다. 느리게 변경되는 대규모 목록은 스프레드시트로 계속 관리할 수 있지만, 긴급한 예외가 있는 소규모 목록은 사람이 안정적으로 수행할 수 있는 것보다 더 자주 업데이트해야 할 수 있습니다.

관찰된 문제실용적인 다음 단계확인할 사항
주문 내보내기 파일을 반복해서 복사함가져오기를 표준화하거나 지원되는 소스에 Power Query를 사용합니다.식별자 서식, 열 매핑, 중복 처리
운송업체를 반복해서 수동 확인함추적 데이터 연결 또는 API 기반 업무 흐름을 검토합니다.실제 사용하는 운송업체, 서비스, 배송 경로 지원 여부
예외를 너무 늦게 발견함예약 처리 또는 이벤트 기반 알림을 추가합니다.트리거 정의, 담당자, 알림 실패 처리
여러 사람이 동일한 기록을 덮어씀통제된 공유 업무 흐름 또는 운영 시스템을 사용합니다.액세스 권한, 변경 기록, 레코드 소유권
고객이 누군가의 통합 문서 확인에 의존함스토어와 연결된 주문 추적 업무 흐름을 검토합니다.주문 매핑, 분할 배송, 고객에게 표시되는 상태의 정확성

Power Query는 웹 소스를 포함한 지원되는 데이터 소스에 연결할 수 있지만, 그렇다고 모든 공개 추적 페이지가 신뢰할 수 있는 데이터 피드가 되는 것은 아닙니다. 인증, 페이지 구조, 액세스 제한, 새로 고침 설정에 따라 연결 작동 여부가 달라질 수 있습니다. 사람이 읽도록 설계된 페이지를 스크래핑하기보다 문서화된 인터페이스를 선호하세요.

맞춤형 통합을 위해 17TRACK Tracking API를 검토할 수 있습니다. 구현하기 전에 의도한 데이터 흐름과 공식 문서를 대조하세요. API 제공 기능이 기본 Excel 커넥터이거나 추가 통합 작업 없이 기존 통합 문서를 업데이트한다고 가정하지 마세요.

실용적인 아키텍처는 통제된 서비스나 데이터 저장소에서 추적 업데이트를 수집하고, 이를 패키지 기록에 매핑한 다음 결과 표를 Excel에 제공하는 것입니다. 널리 공유되는 통합 문서에 인증 정보를 저장하지 마세요. 이벤트 타임스탬프와 수집 타임스탬프를 모두 저장하여 지연된 업데이트와 새로운 배송 이벤트를 구분할 수 있도록 하세요.

일반 배송, 분할 배송, 누락된 이벤트, 예외, 반송 패키지에 대해 자동화된 업무 흐름을 테스트하세요. 재시도와 반복 업데이트를 어떻게 처리하는지 확인하세요. “마지막으로 성공한 새로 고침” 표시를 눈에 잘 띄게 유지하세요. 오래된 배송 완료처럼 보이는 대시보드는 불완전하다고 공개된 스프레드시트보다 더 오해를 불러일으킬 수 있습니다.

FAQ

Excel에서 운송장 번호를 자동으로 업데이트할 수 있나요?

Excel은 적절한 연결이나 통합 기능이 있으면 새로 고쳐진 배송 데이터를 표시할 수 있습니다. 셀에 입력된 운송장 번호, 하이퍼링크 또는 TODAY() 수식만으로는 운송업체 이벤트를 가져올 수 없습니다. 업데이트를 가져올 수 있는 지원되는 소스와 구성된 프로세스가 필요합니다.

Excel이 운송장 번호를 변경한 이유는 무엇인가요?

Excel이 해당 값을 텍스트가 아니라 숫자로 해석했을 수 있습니다. 식별자를 입력하거나 가져오기 전에 대상 열을 텍스트로 서식 지정하세요. 숫자나 앞의 0이 이미 손실되었다면 원본에서 식별자를 다시 복사하세요. 시각적 서식만으로는 누락된 정보를 복원할 수 없습니다.

예상 배송일과 약속 배송일 중 어느 것을 사용해야 하나요?

둘 다 중요하다면 별도의 열에 유지하세요. 운송업체의 예상일은 배송이 진행됨에 따라 변경될 수 있지만, 고객에게 한 약속은 판매자의 결제 과정이나 확인 메시지에서 비롯될 수 있습니다. 이 템플릿의 기한 초과 수식은 약속 배송일을 사용합니다. 운송업체 예상일을 기반으로 하는 수식에는 그 사실을 명확히 표시하세요.

여러 패키지가 있는 주문은 어떻게 추적하나요?

패키지마다 한 행을 만들고 주문 ID를 반복 입력하세요. 각 패키지를 독립적으로 업데이트합니다. 주문 수준의 요약이 필요하다면 주문을 이행하는 데 필요한 모든 패키지가 적절한 결과에 도달한 후에만 주문을 완료된 것으로 표시하세요.

오랫동안 스캔 기록이 없으면 패키지가 분실된 것인가요?

아닙니다. 업데이트가 없다는 사실만으로 분실을 입증할 수는 없습니다. 운송업체의 최신 정보를 확인하고 배송 예상치와 비교한 다음 적용 가능한 조사 절차를 따르세요. 오래된 데이터 플래그는 확인을 유도해야 하며, 패키지를 자동으로 분실 처리해서는 안 됩니다.

출처