Проследяване на онлайн поръчки и пратки в Excel: шаблон, формули и кога да автоматизирате
Можете да проследявате онлайн поръчки и пратки в Excel с един ред за всеки пакет, последователен списък със статуси и формули, които маркират просрочени доставки и остарели проверки. Шаблонът по-долу ви дава практична отправна точка. Excel може да организира и приоритизира информацията за пратките, но само формулите не извличат актуални събития от превозвачите; тези актуализации изискват ръчни проверки или свързан източник на данни.
Това ръководство е предназначено за малки екипи за електронна търговия и за всеки, който управлява множество входящи поръчки. То отделя записа за пратката от последващата работа, така че електронната ви таблица да отговаря на два различни въпроса: къде е пакетът и кой трябва да предприеме следващо действие?
Изградете инструмента за проследяване около пакетите, а не само около поръчките
Номерът на поръчката идентифицира покупка. Номерът за проследяване идентифицира пратка в системата на превозвача. Те не са взаимозаменяеми. Една поръчка може да бъде изпратена в няколко пакета и всеки пакет може да има различна дата на доставка или статус.
Използвайте един ред за всеки пакет, като при необходимост повтаряте идентификатора на поръчката. Ако поръчката все още не е изпратена, създайте временен ред без номер за проследяване, след което актуализирайте или разделете този ред, когато данните за пратката са налични. Не поставяйте няколко номера за проследяване в една клетка: това затруднява филтрирането, проверката за дубликати и актуализирането на статусите.
За личен инструмент за проследяване собственикът просто може да сте вие. За бизнес инструмент назначете отговорно лице, което ще разследва изключенията, ще се свързва с превозвача или продавача или ще комуникира с клиента. Статус без отговорно лице лесно може да бъде пренебрегнат.
Шаблон за проследяване на пратки в 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 като Text. Номерата за проследяване са идентификатори, а не количества. Третирането им като числа може да премахне водещите нули или да промени дълги цифрови низове. Промяната на формата след това няма надеждно да възстанови вече изгубената информация; при необходимост импортирайте отново оригиналните идентификатори.
| Колона | Какво да въведете | Работно правило |
|---|---|---|
| A: Order 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 | Лицето, отговорно за последващите действия | Определете отговорник преди възникването на изключение. |
Настройте листа, преди да добавяте още редове
- Преобразувайте диапазона в таблица на Excel. Използвайте Insert > Table и потвърдете, че диапазонът има заглавия. Таблиците улесняват филтрирането и могат да пренасят формулите на изчисляемите колони в допълнителни редове.
- Приложете формати за дати. Форматирайте D и E като дати. Форматирайте H с дата и час. Потвърдете, че импортираните стойности са реални дати в Excel, а не текст, който само изглежда като дата.
- Създайте падащо меню за статус. Използвайте Data Validation със списък като Not shipped, Label created, In transit, Out for delivery, Delivered, Exception и Returned.
- Замразете реда със заглавията. Дръжте етикетите на колоните видими при преглед на по-дълги списъци.
- Добавете праг за проверка. Поставете „Recheck after days“ в O1 и въведете 2 в P1. Това е примерен вътрешен интервал за преглед, а не стандарт за услугата на превозвача.
Запазвайте текста на събитията от превозвача в колона G, дори когато нормализирате колона F. Например „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" маркира съответната пратка. Знакът за долар фиксира колоната със статуса, като позволява номерът на реда да се променя.
Проверявайте и за дублирани записи, преди да импортирате друга партида. Повтарящ се идентификатор на поръчка е нормален при разделени пратки; повтарящата се комбинация от превозвач и номер за проследяване заслужава разследване. Възможно е да е случаен дубликат, но не го изтривайте автоматично, без да проверите връзката с основната поръчка.
Ежедневен работен процес за надеждно поддържане на инструмента
- Добавяйте или импортирайте нови пратки. Запазвайте идентификаторите като текст и потвърждавайте, че всеки пакет има собствен ред.
- Филтрирайте по флага за преглед. Започнете с изключенията и просрочените обещания, след което преминете към липсващи данни за проследяване и остарели проверки.
- Проверявайте източника за проследяване. Отворете страницата на превозвача или използвайте услуга за множество превозвачи, за да проследите пакета. Сравнете номера за проследяване и превозвача с електронната таблица.
- Актуализирайте доказателствата. Запишете последното събитие в G, изберете подходящия статус във F и въведете фиксиран времеви печат на проверката в H.
- Определете следващото действие. Добавете колона за бележки, ако отговорникът трябва да запише съобщение до клиента, референция за разследване или дата за последващо действие.
- Съгласувайте затворените записи. Потвърдете, че статусът „доставено“ се отнася за правилния пакет, а не автоматично за всеки пакет в поръчката.
Не въвеждайте =NOW() в „Last checked“. Той се променя при преизчисляване и може да накара непроверен запис да изглежда наскоро прегледан. Времевият печат трябва да представлява реална проверка или успешно автоматизирано актуализиране.
Съхранявайте минимално необходимата информация за клиентите. Споделен инструмент за оперативно проследяване рядко се нуждае от пълен адрес за доставка, телефонен номер и всички подробности за поръчката във всеки ред. Ограничете достъпа и избягвайте разпространяването на неконтролирани копия, съдържащи лична информация.
Кога да автоматизирате проследяването на пратки
Автоматизирайте, когато поддържането на актуални статуси се превърне в тясно място, а не просто когато работният лист стане дълъг. Няма универсален праг за броя пратки. Една електронна таблица може да остане практична при по-голям, бавно променящ се списък, докато по-малък списък със спешни изключения може да изисква по-чести актуализации, отколкото човек може надеждно да извършва.
| Наблюдаван проблем | Практична следваща стъпка | Какво да проверите |
|---|---|---|
| Повтарящо се копиране от експорти на поръчки | Стандартизирайте импортирането или използвайте Power Query за поддържаните източници. | Форматиране на идентификаторите, съпоставяне на колоните и обработка на дубликати. |
| Повтарящи се ръчни проверки при превозвачите | Оценете връзка с данни за проследяване или работен процес, базиран на API. | Поддръжка на действителните ви превозвачи, услуги и маршрути. |
| Изключенията се откриват твърде късно | Добавете планирана обработка или сигнали, задействани от събития. | Дефиниции на тригерите, отговорни лица и обработка на неуспешни сигнали. |
| Няколко души презаписват едни и същи записи | Използвайте контролиран споделен работен процес или оперативна система. | Права за достъп, история на промените и собственост върху записите. |
| Клиентите зависят от това някой да проверява работна книга | Оценете работен процес за проследяване на поръчки, свързан с магазина. | Съпоставяне на поръчките, разделени пратки и точност на статуса, видим за клиента. |
Power Query може да се свързва с поддържани източници на данни, включително уеб източници, но това не превръща всяка публична страница за проследяване в надежден канал за данни. Удостоверяването, структурата на страницата, ограниченията за достъп и настройките за обновяване могат да повлияят на работата на връзката. Предпочитайте документиран интерфейс пред извличане на данни от страница, предназначена за четене от хора.
За персонализирана интеграция 17TRACK Tracking API е подходяща опция за оценка. Проверете неговата официална документация спрямо предвидения поток от данни, преди да го внедрите. Не приемайте, че предлагането на API е вграден конектор за Excel или че ще актуализира съществуваща работна книга без допълнителна интеграционна работа.
Практичната архитектура е да събирате актуализациите за проследяване в контролиранa услуга или хранилище на данни, да ги съпоставяте със записите за пакетите и след това да предоставяте получената таблица на Excel. Дръжте идентификационните данни извън широко споделяните работни книги. Съхранявайте както времевия печат на събитието, така и времевия печат на приемането, за да може забавената актуализация да се различава от ново събитие за пратка.
Тествайте всеки автоматизиран работен процес с обикновени доставки, разделени пратки, липсващи събития, изключения и върнати пакети. Потвърдете как обработва повторните опити и повтарящите се актуализации. Поддържайте видим индикатор „последно успешно обновяване“: табло, което изглежда старо, но показва доставка, може да бъде по-подвеждащо от открито непълна електронна таблица.
Често задавани въпроси
Може ли Excel автоматично да актуализира номерата за проследяване?
Excel може да показва обновени данни за пратките, когато разполага с подходяща връзка или интеграция. Номер за проследяване в клетка, хипервръзка или формула TODAY() не извлича събития от превозвача. Необходим е поддържан източник и конфигуриран процес за импортиране на актуализациите му.
Защо Excel промени номера ми за проследяване?
Excel може да го е интерпретирал като число, а не като текст. Форматирайте целевата колона като Text, преди да въведете или импортирате идентификаторите. Ако цифри или водещи нули вече са изгубени, копирайте идентификатора отново от оригиналния му източник; само визуалното форматиране не може да възстанови липсваща информация.
Трябва ли да използвам прогнозната дата на доставка или обещаната дата?
Дръжте ги в отделни колони, ако и двете са важни. Прогнозата на превозвача може да се променя с напредването на пратката, докато обещанието към клиента може да идва от финализирането на поръчката или потвърждението от продавача. Формулата за просрочие в този шаблон използва обещаната дата. Обозначавайте съответно всяка формула, базирана на прогнозата на превозвача.
Как да проследя поръчка с няколко пакета?
Създайте един ред за всеки пакет и повторете идентификатора на поръчката. Актуализирайте всеки пакет независимо. Ако ви е необходимо обобщение на ниво поръчка, маркирайте поръчката като завършена едва след като всички пакети, нужни за изпълнението ѝ, са достигнали подходящия резултат.
Означава ли дълъг период без сканирания, че пакетът е изгубен?
Не. Само липсващите актуализации не доказват загуба. Проверете последната информация от превозвача, сравнете пратката с очакванията за доставката ѝ и следвайте приложимия процес за разследване. Флагът за остарели данни трябва да подтикне към проверка, а не автоматично да определя пакета като изгубен.
Източници
- Microsoft Support: COUNTIF function — синтаксис и броене по критерии, използвани в обобщението за преглед.
- Microsoft Support: TODAY function — изчисления с текущата дата и поведение при преизчисляване.
- Microsoft Learn: Power Query Web connector — уеб връзки с данни и съображения за удостоверяване.
- 17TRACK tracking — входна точка за ръчно проследяване на пакети.
- 17TRACK Tracking API и API documentation — справочни материали за оценка на персонализирана интеграция за проследяване.
