Sledujte online objednávky a zásilky v Excelu: šablona, vzorce a kdy automatizovat
Online objednávky a zásilky můžete sledovat v Excelu tak, že pro každý balík použijete jeden řádek, jednotný seznam stavů a vzorce, které označí opožděné doručení a zastaralé kontroly. Níže uvedená šablona představuje praktický výchozí bod. Excel dokáže informace o zásilkách uspořádat a upřednostnit, samotné vzorce však nezískávají aktuální události od přepravců; tyto aktualizace vyžadují ruční kontroly nebo připojený zdroj dat.
Tato příručka je určena malým e-commerce týmům a všem, kdo spravují více příchozích objednávek. Odděluje záznam o zásilce od následných úkolů, takže tabulka odpovídá na dvě různé otázky: kde se balík nachází a kdo musí jako další něco udělat?
Vytvořte sledování podle balíků, ne pouze podle objednávek
Číslo objednávky identifikuje nákup. Sledovací číslo identifikuje zásilku v systému přepravce. Nejde o zaměnitelné údaje. Jedna objednávka může být odeslána v několika balících a každý balík může mít jiné datum doručení nebo stav.
Použijte jeden řádek pro každý balík a v případě potřeby opakujte ID objednávky. Pokud objednávka ještě nebyla odeslána, vytvořte provizorní řádek bez sledovacího čísla a po zpřístupnění údajů o zásilce jej aktualizujte nebo rozdělte. Nevkládejte několik sledovacích čísel do jedné buňky: ztěžuje to filtrování, kontrolu duplicit a aktualizaci stavů.
U osobního přehledu nákupů můžete být vlastníkem jednoduše vy. U firemního přehledu přiřaďte vlastníka, který bude prověřovat výjimky, kontaktovat přepravce nebo prodejce či komunikovat se zákazníkem. Stav bez odpovědné osoby lze snadno přehlédnout.
Šablona pro sledování zásilek v Excelu ke zkopírování a vložení
Zkopírujte níže uvedený záhlaví oddělené tabulátory a vložte je do buňky A1 v prázdném listu. Nadpisy by měly zabírat sloupce A až L. Jedná se o výchozí rozvržení, nikoli o soubor ke stažení nebo živou integraci sledování.
Order ID Tracking number Carrier Ship date Promised delivery Status Last carrier event Last checked Tracking URL Days open Review flag OwnerPřed vložením údajů o objednávkách naformátujte sloupce A a B jako Text. Sledovací čísla jsou identifikátory, nikoli množství. Pokud s nimi Excel zachází jako s čísly, může odstranit úvodní nuly nebo změnit dlouhé číselné řetězce. Změna formátu později spolehlivě neobnoví informace, které již byly ztraceny; v případě potřeby znovu importujte původní identifikátory.
| Sloupec | Co zadat | Pravidlo |
|---|---|---|
| A: ID objednávky | Referenční číslo objednávky v obchodě nebo interní reference | U rozdělených zásilek jej opakujte. |
| B: Sledovací číslo | Identifikátor dodaný pro daný balík | Ponechte jej jako text a zachovejte přesné znaky. |
| C: Přepravce | Přepravce spojený s tímto číslem | Používejte jednotné názvy namísto několika zkratek. |
| D: Datum odeslání | Datum použité pro zahájení sledování stáří zásilky | Určete, zda znamená odeslání, nebo potvrzené převzetí přepravcem. |
| E: Přislíbené doručení | Platný závazek doručení | Pokud žádný příslib neexistuje, ponechte prázdné; nevymýšlejte termín. |
| F: Stav | Standardizovaná kategorie zásilky | Vybírejte z řízeného seznamu. |
| G: Poslední událost přepravce | Nejnovější text události a čas události | Zachovejte podrobnosti stojící za standardizovaným stavem. |
| H: Poslední kontrola | Kdy někdo naposledy ověřil informace o sledování | Zadejte pevné časové razítko, nikoli průběžně se měnící vzorec. |
| I: URL sledování | Ověřený odkaz na stránku sledování | Cíl udržujte odděleně od sledovacího čísla. |
| J: Počet dnů otevřeno | Vypočítané stáří zásilky | Používejte kalendářní dny, pokud záměrně nevytvoříte jiné pravidlo. |
| K: Příznak kontroly | Vypočítaná výzva k následné kontrole | Jedná se o interní pravidlo, nikoli o stav přepravce. |
| L: Vlastník | Osoba odpovědná za následné kroky | Vlastnictví přiřaďte ještě před vznikem výjimky. |
Nastavte list před přidáním dalších řádků
- Převeďte oblast na excelovou tabulku. Použijte Vložení > Tabulka a potvrďte, že oblast obsahuje záhlaví. Tabulky usnadňují filtrování a mohou přenášet vzorce vypočítaných sloupců do dalších řádků.
- Použijte formáty dat. Sloupce D a E naformátujte jako datum. Sloupec H naformátujte tak, aby obsahoval datum i čas. Ověřte, že importované hodnoty jsou skutečná data Excelu, nikoli text, který pouze vypadá jako datum.
- Vytvořte rozevírací seznam stavů. Použijte Ověření dat se seznamem, například: Neodesláno, Štítek vytvořen, V přepravě, Doručení probíhá, Doručeno, Výjimka a Vráceno.
- Ukotvěte řádek záhlaví. Při kontrole delších seznamů ponechte názvy sloupců viditelné.
- Přidejte práh kontroly. Do O1 vložte „Znovu zkontrolovat po dnech“ a do P1 zadejte 2. Jde o příklad interního intervalu kontroly, nikoli o standard přepravní služby.
Text události přepravce ponechte ve sloupci G i v případě, že normalizujete sloupec F. Například „Štítek vytvořen“ by se neměl automaticky změnit na „V přepravě“: vytvoření štítku samo o sobě nepotvrzuje, že přepravce zásilku převzal.
Vzorce, díky kterým bude přehled užitečný
Níže uvedené vzorce používají výše uvedené rozložení sloupců, anglické názvy funkcí a oddělovače čárkami. Některá regionální nastavení Excelu používají místo nich středníky. Zadejte je do řádku 2 a vyplňte směrem dolů nebo nechte excelovou tabulku, aby je použila jako vypočítané sloupce.
Výpočet počtu dnů otevřené zásilky
Zadejte do J2:
=IF(OR(D2="",F2="Delivered",F2="Returned"),"",MAX(0,TODAY()-INT(D2)))Výsledek představuje počet kalendářních dnů od data odeslání u otevřené zásilky. U dokončených nebo vrácených zásilek ponechá buňku prázdnou, místo aby jejich stáří neomezeně narůstalo. Vzorec neměří výkon přepravy vůči závazku přepravce vyjádřenému v pracovních dnech.
Pokud potřebujete historickou dobu doručení, přidejte samostatný sloupec Doručeno v a vypočítejte rozdíl mezi zaznamenaným datem a datem odeslání. Nepoužívejte dnešní datum jako náhradu skutečného data doručení.
Označení zásilek vyžadujících kontrolu
Zadejte do 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"))))))))Na pořadí těchto kontrol záleží. Doručené zásilky se nejprve uzavřou; vrácení a výjimky obdrží konkrétní výzvy; zásilky po termínu přislíbeného doručení mají přednost před běžnými kontrolami. Balík bez sledovacího čísla je přesměrován ke kontrole odeslání, nikoli považován za běžnou aktualizaci sledování.
„Po termínu přislíbeného doručení“ znamená, že datum v tabulce již uplynulo. Neznamená to, že je zásilka ztracena, že náleží kompenzace nebo že přepravce porušil své podmínky. Před eskalací ověřte zdroj příslibu a nejnovější údaje o zásilce.
Pravidlo zastaralé kontroly měří uplynulý čas od H2. Pokud je v P1 nastaveno 2, označí záznam po dvou dnech bez zaznamenané kontroly. Interval upravte podle svého pracovního postupu. Funkce TODAY() a NOW() se aktualizují při přepočtu Excelu; nejde o služby monitorování na pozadí.
Počet otevřených položek ke kontrole
Umístěte tento souhrnný vzorec mimo tabulku sledování:
=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")Počítá řádky balíků vyžadující pozornost, nikoli jedinečné objednávky nebo zákazníky. V případě potřeby rozsah rozšiřte nebo jej nahraďte příslušným strukturovaným odkazem na tabulku. V každém dashboardu zachovejte toto rozlišení: jedna objednávka se třemi balíky může přispět třemi záznamy zásilek.
Převedení ověřené URL sledování na čitelný odkaz
V nepovinném dodatečném sloupci použijte:
=IF(I2="","",HYPERLINK(I2,"Track package"))Otevře adresu uloženou v I2. Nezískává události zásilky ani nepotvrzuje, že je adresa bezpečná. Používejte odkazy z důvěryhodné služby sledování nebo od přepravce a nevytvářejte adresy specifické pro přepravce, pokud jste neověřili jejich formát.
Formátování používejte k podpoře rozhodování, nikoli k dekoraci listu
Na příznak kontroly nebo celý řádek použijte podmíněné formátování. Pro výjimky a záznamy po termínu použijte výraznou varovnou barvu, pro běžné opakované kontroly světlejší varování a pro uzavřené zásilky tlumený styl. Textový štítek ponechte viditelný, aby byl list srozumitelný i bez barev.
U pravidla pro celý řádek použitého na A2:L1000 zvýrazní odpovídající zásilku například vzorec =$K2="Past promised date". Znak dolaru zafixuje sloupec se stavem a umožní změnu čísla řádku.
Před importem další dávky také zkontrolujte duplicitní záznamy. Opakované ID objednávky je u rozdělených zásilek běžné; opakovaná kombinace přepravce a sledovacího čísla si zaslouží prověření. Může jít o náhodnou duplicitu, ale bez kontroly souvisejícího vztahu objednávky ji automaticky nemažte.
Každodenní pracovní postup pro zachování důvěryhodnosti přehledu
- Přidejte nebo importujte nové zásilky. Identifikátory zachovejte jako text a ověřte, že každý balík má vlastní řádek.
- Filtrujte podle příznaku kontroly. Začněte výjimkami a zásilkami po termínu, poté zpracujte chybějící údaje o sledování a zastaralé kontroly.
- Ověřte zdroj sledování. Otevřete stránku přepravce nebo použijte službu pro více přepravců ke sledování balíku. Porovnejte sledovací číslo a přepravce s tabulkou.
- Aktualizujte podklady. Nejnovější událost zaznamenejte do G, ve F vyberte odpovídající stav a do H zadejte pevné časové razítko kontroly.
- Přiřaďte další úkol. Pokud vlastník potřebuje zaznamenat zprávu zákazníkovi, odkaz na prověření nebo datum následné kontroly, přidejte sloupec s poznámkou k úkolu.
- Sladění uzavřených záznamů. Ověřte, že stav doručení platí pro správný balík, nikoli automaticky pro všechny balíky v objednávce.
Do pole „Poslední kontrola“ nezadávejte =NOW(). Při přepočtu se mění a může způsobit, že nedotčený záznam bude vypadat jako nedávno zkontrolovaný. Časové razítko musí představovat skutečnou kontrolu nebo úspěšnou automatickou aktualizaci.
Informace o zákaznících udržujte v nezbytném minimu. Sdílený provozní přehled obvykle nepotřebuje v každém řádku úplnou doručovací adresu, telefonní číslo a kompletní údaje o objednávce. Omezte přístup a vyhněte se šíření nekontrolovaných kopií obsahujících osobní údaje.
Kdy automatizovat sledování zásilek
Automatizujte tehdy, když se udržování aktuálních stavů stane úzkým hrdlem, nikoli pouze tehdy, když se list prodlouží. Neexistuje univerzální hranice počtu zásilek. Tabulka může zůstat použitelná i u většího, pomalu se měnícího seznamu, zatímco menší seznam s naléhavými výjimkami může vyžadovat častější aktualizace, než člověk dokáže spolehlivě provádět.
| Zjištěný problém | Praktický další krok | Co ověřit |
|---|---|---|
| Opakované kopírování z exportů objednávek | Standardizujte importy nebo pro podporované zdroje použijte Power Query. | Formátování identifikátorů, mapování sloupců a zpracování duplicit. |
| Opakované ruční kontroly u přepravců | Vyhodnoťte připojení k údajům o sledování nebo pracovní postup založený na API. | Podporu vašich skutečných přepravců, služeb a přepravních tras. |
| Výjimky jsou odhalovány příliš pozdě | Přidejte plánované zpracování nebo upozornění řízená událostmi. | Definice spouštěčů, odpovědné vlastníky a zpracování neúspěšných upozornění. |
| Několik lidí přepisuje stejné záznamy | Použijte řízený sdílený pracovní postup nebo provozní systém. | Oprávnění k přístupu, historii změn a vlastnictví záznamů. |
| Zákazníci jsou závislí na tom, že někdo kontroluje sešit | Vyhodnoťte pracovní postup sledování objednávek propojený s obchodem. | Mapování objednávek, rozdělené zásilky a přesnost stavu zobrazovaného zákazníkům. |
Power Query se může připojit k podporovaným zdrojům dat včetně webových zdrojů, to však neznamená, že každá veřejná stránka pro sledování představuje spolehlivý datový kanál. Funkčnost připojení mohou ovlivnit ověřování, struktura stránky, omezení přístupu a nastavení obnovování. Dávejte přednost dokumentovanému rozhraní před načítáním stránky vytvořené pro čtení lidmi.
Pro vlastní integraci je vhodné zvážit 17TRACK Tracking API. Před implementací porovnejte jeho oficiální dokumentaci s plánovaným tokem dat. Nepředpokládejte, že nabídka API představuje nativní konektor Excelu nebo že aktualizuje existující sešit bez další integrační práce.
Praktická architektura spočívá ve shromažďování aktualizací sledování v řízené službě nebo úložišti dat, jejich namapování na záznamy balíků a následném zpřístupnění výsledné tabulky v Excelu. Přihlašovací údaje uchovávejte mimo široce sdílené sešity. Ukládejte časové razítko události i časové razítko načtení, aby bylo možné odlišit opožděnou aktualizaci od nové události zásilky.
Každý automatizovaný pracovní postup otestujte na běžných doručeních, rozdělených zásilkách, chybějících událostech, výjimkách a vrácených balících. Ověřte, jak zpracovává opakované pokusy a opakované aktualizace. Zachovejte viditelný údaj „poslední úspěšné obnovení“: starý dashboard vypadající jako aktuální může být zavádějící více než otevřeně neúplná tabulka.
Časté dotazy
Může Excel aktualizovat sledovací čísla automaticky?
Excel může zobrazovat obnovená data o zásilkách, pokud má vhodné připojení nebo integraci. Sledovací číslo v buňce, hypertextový odkaz ani vzorec TODAY() nezískává události od přepravce. Potřebujete podporovaný zdroj a nakonfigurovaný proces pro import jeho aktualizací.
Proč Excel změnil moje sledovací číslo?
Excel je mohl interpretovat jako číslo namísto textu. Před zadáním nebo importem identifikátorů naformátujte cílový sloupec jako Text. Pokud již došlo ke ztrátě číslic nebo úvodních nul, zkopírujte identifikátor znovu z původního zdroje; samotné vizuální formátování chybějící informace neobnoví.
Mám použít odhadované datum doručení, nebo přislíbené datum?
Pokud jsou důležitá obě, ponechte je v samostatných sloupcích. Odhad přepravce se může v průběhu přepravy měnit, zatímco příslib zákazníkovi může pocházet z dokončení objednávky nebo potvrzení prodejce. Vzorec pro překročení termínu v této šabloně používá přislíbené datum. Každý vzorec založený na odhadu přepravce odpovídajícím způsobem označte.
Jak sledovat objednávku s více balíky?
Vytvořte jeden řádek pro každý balík a opakujte ID objednávky. Každý balík aktualizujte samostatně. Pokud potřebujete souhrn na úrovni objednávky, označte objednávku jako dokončenou až poté, co všechny balíky potřebné k jejímu vyřízení dosáhnou odpovídajícího výsledku.
Znamená dlouhá doba bez skenování, že je balík ztracen?
Ne. Samotná absence aktualizací ztrátu neprokazuje. Ověřte nejnovější informace přepravce, porovnejte zásilku s očekávaným doručením a postupujte podle příslušného procesu šetření. Příznak zastaralých údajů má vyvolat kontrolu, nikoli automaticky označit balík za ztracený.
Zdroje
- Microsoft Support: funkce COUNTIF — syntaxe a počítání podle kritérií použité v souhrnu kontrol.
- Microsoft Support: funkce TODAY — výpočty aktuálního data a chování při přepočtu.
- Microsoft Learn: webový konektor Power Query — webová datová připojení a aspekty ověřování.
- Sledování 17TRACK — vstupní bod pro ruční sledování balíků.
- 17TRACK Tracking API a dokumentace API — reference pro vyhodnocení vlastní integrace sledování.
