Sledovanie online objednávok a zásielok v Exceli: šablóna, vzorce a kedy automatizovať
Online objednávky a zásielky môžete sledovať v Exceli tak, že každý balík bude mať vlastný riadok, použijete jednotný zoznam stavov a vzorce, ktoré označia oneskorené doručenia a neaktuálne kontroly. Šablóna nižšie poskytuje praktický základ. Excel dokáže organizovať a prioritizovať informácie o zásielkach, ale samotné vzorce nezískavajú aktuálne udalosti od prepravcov; tieto aktualizácie si vyžadujú manuálne kontroly alebo pripojený zdroj údajov.
Táto príručka je určená malým tímom elektronického obchodu a každému, kto spravuje viacero prichádzajúcich objednávok. Oddeľuje záznam o zásielke od následných úloh, takže tabuľka odpovedá na dve rôzne otázky: kde sa balík nachádza a kto musí urobiť ďalší krok?
Vytvorte sledovanie okolo balíkov, nie iba objednávok
Číslo objednávky identifikuje nákup. Sledovacie číslo identifikuje zásielku v systéme prepravcu. Nie sú zameniteľné. Jedna objednávka môže byť odoslaná v niekoľkých balíkoch a každý balík môže mať iný dátum doručenia alebo stav.
Použite jeden riadok na každý balík a v prípade potreby zopakujte ID objednávky. Ak objednávka ešte nebola odoslaná, vytvorte predbežný riadok bez sledovacieho čísla a potom ho aktualizujte alebo rozdeľte, keď budú k dispozícii údaje o zásielke. Nevkladajte viacero sledovacích čísel do jednej bunky: sťažuje to filtrovanie, kontrolu duplicít a aktualizáciu stavov.
Pri osobnom sledovaní nákupov môžete byť vlastníkom jednoducho vy. Pri firemnom sledovaní priraďte vlastníka, ktorý preverí výnimky, skontaktuje prepravcu alebo predajcu alebo bude komunikovať so zákazníkom. Stav bez zodpovednej osoby sa ľahko prehliadne.
Šablóna sledovania zásielok v Exceli na kopírovanie a vloženie
Skopírujte záhlavie oddelené tabulátormi nižšie a vložte ho do bunky A1 v prázdnom hárku. Nadpisy by mali obsadiť stĺpce A až L. Ide o základné rozloženie, nie o súbor na stiahnutie ani živú integráciu sledovania.
Order ID Tracking number Carrier Ship date Promised delivery Status Last carrier event Last checked Tracking URL Days open Review flag OwnerPred vložením údajov o objednávkach naformátujte stĺpce A a B ako Text. Sledovacie čísla sú identifikátory, nie množstvá. Ak ich Excel spracuje ako čísla, môže odstrániť úvodné nuly alebo zmeniť dlhé číselné reťazce. Neskoršia zmena formátu spoľahlivo neobnoví už stratené informácie; v prípade potreby znova importujte pôvodné identifikátory.
| Stĺpec | Čo zadať | Pravidlo |
|---|---|---|
| A: ID objednávky | Referenčné číslo objednávky v obchode alebo interné číslo | Pri rozdelených zásielkach ho zopakujte. |
| B: Sledovacie číslo | Identifikátor pridelený balíku | Ponechajte ho ako text a zachovajte presné znaky. |
| C: Prepravca | Prepravca priradený k tomuto číslu | Používajte jednotné názvy namiesto viacerých skratiek. |
| D: Dátum odoslania | Dátum, od ktorého sa počíta vek zásielky | Určte, či znamená odoslanie alebo potvrdené prijatie prepravcom. |
| E: Sľúbené doručenie | Príslušný záväzok doručenia | Ak neexistuje žiadny prísľub, nechajte pole prázdne; nevymýšľajte termín. |
| F: Stav | Štandardizovaná kategória zásielky | Vyberajte z riadeného zoznamu. |
| G: Posledná udalosť prepravcu | Najnovší text udalosti a čas udalosti | Zachovajte podrobnosti za štandardizovaným stavom. |
| H: Posledná kontrola | Kedy niekto naposledy overil informácie o sledovaní | Zadajte pevný časový údaj, nie neustále sa meniaci vzorec. |
| I: URL sledovania | Overený odkaz na stránku sledovania | Cieľ odkazu uchovávajte oddelene od sledovacieho čísla. |
| J: Počet dní otvorenia | Vypočítaný vek zásielky | Používajte kalendárne dni, ak zámerne nevytvárate iné pravidlo. |
| K: Príznak kontroly | Vypočítaná výzva na následnú kontrolu | Ide o interné pravidlo, nie stav prepravcu. |
| L: Vlastník | Osoba zodpovedná za následnú kontrolu | Vlastníctvo priraďte ešte pred vznikom výnimky. |
Nastavte hárok pred pridaním ďalších riadkov
- Preveďte rozsah na excelovskú tabuľku. Použite Vložiť > Tabuľka a potvrďte, že rozsah obsahuje záhlavia. Tabuľky uľahčujú filtrovanie a môžu preniesť vzorce vypočítaných stĺpcov do ďalších riadkov.
- Použite formáty dátumu. Stĺpce D a E naformátujte ako dátumy. Stĺpec H naformátujte tak, aby obsahoval dátum aj čas. Overte, že importované hodnoty sú skutočné excelovské dátumy, nie text, ktorý iba vyzerá ako dátum.
- Vytvorte rozbaľovací zoznam stavov. Použite Overenie údajov so zoznamom, napríklad Neodoslané, Štítok vytvorený, V preprave, Na doručenie, Doručené, Výnimka a Vrátené.
- Ukotvite riadok záhlavia. Počas kontroly dlhších zoznamov ponechajte názvy stĺpcov viditeľné.
- Pridajte prah kontroly. Do bunky O1 vložte „Znova skontrolovať po dňoch“ a do P1 zadajte 2. Ide o príklad interného intervalu kontroly, nie štandard služby prepravcu.
Text udalosti prepravcu ponechajte v stĺpci G aj vtedy, keď štandardizujete stĺpec F. Napríklad „Štítok vytvorený“ by sa nemal automaticky zmeniť na „V preprave“: samotné vytvorenie štítku nepotvrdzuje, že prepravca balík prevzal.
Vzorce, vďaka ktorým bude sledovanie užitočné
Nasledujúce vzorce používajú vyššie uvedené rozloženie stĺpcov, anglické názvy funkcií a oddeľovače čiarkami. Niektoré regionálne nastavenia Excelu používajú namiesto nich bodkočiarky. Zadajte ich do riadku 2 a vyplňte smerom nadol alebo ich nechajte automaticky použiť tabuľkou Excelu ako vypočítané stĺpce.
Výpočet počtu dní otvorenia
Zadajte do J2:
=IF(OR(D2="",F2="Delivered",F2="Returned"),"",MAX(0,TODAY()-INT(D2)))Vzorec vráti počet kalendárnych dní od dátumu odoslania pri otvorenej zásielke. Doručené alebo vrátené zásielky ponechá prázdne, aby ich vek ďalej neobmedzene nenarastal. Vzorec nemeria výkon prepravy voči záväzku prepravcu vyjadrenému v pracovných dňoch.
Ak potrebujete historické trvanie doručenia, pridajte samostatný stĺpec Doručené dňa a vypočítajte rozdiel medzi zaznamenaným dátumom a dátumom odoslania. Nepoužívajte dnešný dátum ako náhradu za skutočný dátum doručenia.
Označenie zásielok, ktoré si vyžadujú kontrolu
Zadajte 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"))))))))Poradie týchto kontrol je dôležité. Doručené zásielky sa najprv uzavrú; vrátenia a výnimky dostanú konkrétne výzvy; zásielky po sľúbenom dátume majú prednosť pred bežnými kontrolami. Balík bez sledovacieho čísla sa presmeruje na kontrolu odoslania, nie na bežnú aktualizáciu sledovania.
„Po sľúbenom dátume“ znamená, že dátum v tabuľke už uplynul. Neznamená to, že sa balík stratil, že vznikol nárok na náhradu alebo že prepravca porušil svoje podmienky. Pred eskaláciou overte zdroj prísľubu a najnovšie údaje o zásielke.
Pravidlo neaktuálnej kontroly meria uplynulý čas od H2. Ak je v P1 nastavená hodnota 2, záznam označí po dvoch dňoch bez zaznamenanej kontroly. Interval prispôsobte svojmu pracovnému postupu. Funkcie TODAY() a NOW() sa aktualizujú pri prepočte Excelu; nejde o služby monitorovania na pozadí.
Počítanie otvoreného zoznamu kontrol
Tento súhrnný vzorec umiestnite mimo tabuľky sledovania:
=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číta riadky balíkov, ktoré si vyžadujú pozornosť, nie jedinečné objednávky alebo zákazníkov. V prípade potreby rozsah rozšírte alebo ho nahraďte príslušným štruktúrovaným odkazom na tabuľku. V každom prehľade zachovajte toto rozlíšenie: jedna objednávka s tromi balíkmi môže predstavovať tri záznamy zásielok.
Prevod overenej URL sledovania na čitateľný odkaz
V nepovinnom ďalšom stĺpci použite:
=IF(I2="","",HYPERLINK(I2,"Track package"))Otvorí adresu uloženú v I2. Nezíska udalosti zásielky ani nepotvrdí, že adresa je bezpečná. Používajte odkazy z dôveryhodnej služby sledovania alebo od prepravcu a nevytvárajte URL špecifické pre prepravcu, pokiaľ ste neoverili ich formát.
Formátovanie používajte na podporu rozhodnutí, nie na ozdobenie hárka
Na príznak kontroly alebo celý riadok použite podmienené formátovanie. Pre výnimky a záznamy po sľúbenom dátume použite výraznú výstražnú farbu, pre bežné opakované kontroly svetlejšiu výstrahu a pre uzavreté zásielky tlmený štýl. Textový štítok ponechajte viditeľný, aby bol hárok zrozumiteľný aj bez farieb.
Pri pravidle pre celý riadok aplikovanom na A2:L1000 zvýrazní príslušnú zásielku napríklad vzorec =$K2="Past promised date". Znak dolára zafixuje stĺpec stavu a zároveň umožní meniť číslo riadku.
Pred importom ďalšej dávky skontrolujte aj duplicitné záznamy. Opakované ID objednávky je pri rozdelených zásielkach normálne; kombinácia opakovaného prepravcu a sledovacieho čísla si zaslúži preverenie. Môže ísť o náhodnú duplicitu, ale bez kontroly súvislosti s pôvodnou objednávkou ju automaticky nemažte.
Denný pracovný postup na zachovanie spoľahlivosti sledovania
- Pridajte alebo importujte nové zásielky. Identifikátory uchovávajte ako text a overte, že každý balík má vlastný riadok.
- Filtrujte podľa príznaku kontroly. Začnite výnimkami a prekročenými prísľubmi, potom prejdite na chýbajúce údaje sledovania a neaktuálne kontroly.
- Overte zdroj sledovania. Otvorte stránku prepravcu alebo použite službu pre viacerých prepravcov na sledovanie balíka. Porovnajte sledovacie číslo a prepravcu s tabuľkou.
- Aktualizujte dôkazy. Najnovšiu udalosť zaznamenajte do G, v F vyberte príslušný stav a do H zadajte pevný čas kontroly.
- Priraďte ďalší krok. Ak vlastník potrebuje zaznamenať správu zákazníkovi, referenciu vyšetrovania alebo dátum následnej kontroly, pridajte stĺpec s poznámkou k úlohe.
- Skontrolujte uzavreté záznamy. Overte, že stav doručenia sa týka správneho balíka, nie automaticky každého balíka v objednávke.
Do poľa „Posledná kontrola“ nezadávajte =NOW(). Pri prepočte sa mení a môže spôsobiť, že nedotknutý záznam bude vyzerať ako nedávno skontrolovaný. Časová pečiatka musí predstavovať skutočnú kontrolu alebo úspešnú automatickú aktualizáciu.
Informácie o zákazníkoch uchovávajte v nevyhnutnom minime. Zdieľaná prevádzková tabuľka zvyčajne nepotrebuje úplnú doručovaciu adresu, telefónne číslo a kompletné údaje objednávky v každom riadku. Obmedzte prístup a vyhnite sa distribúcii nekontrolovaných kópií obsahujúcich osobné údaje.
Kedy automatizovať sledovanie zásielok
Automatizujte vtedy, keď sa udržiavanie aktuálnych stavov stane prekážkou, nie jednoducho vtedy, keď sa hárok predĺži. Neexistuje univerzálny prah počtu zásielok. Tabuľka môže zostať použiteľná aj pri väčšom, pomaly sa meniacom zozname, zatiaľ čo menší zoznam s naliehavými výnimkami môže vyžadovať častejšie aktualizácie, než dokáže človek spoľahlivo vykonávať.
| Zistený problém | Praktický ďalší krok | Čo overiť |
|---|---|---|
| Opakované kopírovanie z exportov objednávok | Štandardizujte importy alebo použite Power Query pre podporované zdroje. | Formát identifikátorov, mapovanie stĺpcov a spracovanie duplicít. |
| Opakované manuálne kontroly prepravcov | Vyhodnoťte dátové pripojenie na sledovanie alebo pracovný postup podporovaný API. | Podporu skutočne používaných prepravcov, služieb a trás zásielok. |
| Výnimky sa odhaľujú príliš neskoro | Pridajte plánované spracovanie alebo upozornenia riadené udalosťami. | Definície spúšťačov, zodpovedných vlastníkov a riešenie zlyhaných upozornení. |
| Viacerí ľudia prepisujú tie isté záznamy | Použite riadený zdieľaný pracovný postup alebo prevádzkový systém. | Povolenia prístupu, históriu zmien a vlastníctvo záznamov. |
| Zákazníci sú odkázaní na niekoho, kto kontroluje zošit | Vyhodnoťte pracovný postup sledovania objednávok prepojený s obchodom. | Mapovanie objednávok, rozdelené zásielky a presnosť stavu zobrazovaného zákazníkom. |
Power Query sa môže pripojiť k podporovaným zdrojom údajov vrátane webových zdrojov, ale to neznamená, že každá verejná stránka sledovania je spoľahlivým dátovým kanálom. Funkčnosť pripojenia môže ovplyvniť autentifikácia, štruktúra stránky, obmedzenia prístupu a konfigurácia obnovovania. Uprednostnite zdokumentované rozhranie pred získavaním údajov zo stránky určenej na čítanie ľuďmi.
Pre vlastnú integráciu je vhodnou možnosťou na vyhodnotenie 17TRACK Tracking API. Pred implementáciou porovnajte jeho oficiálnu dokumentáciu so zamýšľaným tokom údajov. Nepredpokladajte, že ponuka API je natívnym konektorom Excelu alebo že aktualizuje existujúci zošit bez ďalšej integračnej práce.
Praktická architektúra spočíva v zhromažďovaní aktualizácií sledovania v riadenej službe alebo úložisku údajov, ich priradení k záznamom balíkov a následnom sprístupnení výslednej tabuľky Excelu. Prihlasovacie údaje uchovávajte mimo zošitov široko zdieľaných. Ukladajte časovú pečiatku udalosti aj časovú pečiatku prijatia, aby bolo možné odlíšiť oneskorenú aktualizáciu od novej udalosti zásielky.
Automatizovaný pracovný postup testujte na bežných doručeniach, rozdelených zásielkach, chýbajúcich udalostiach, výnimkách a vrátených balíkoch. Overte, ako spracúva opakovania a opakované aktualizácie. Zachovajte viditeľný ukazovateľ „posledné úspešné obnovenie“: starý prehľad vyzerajúci ako aktuálny môže byť zavádzajúcejší než otvorene neúplná tabuľka.
Často kladené otázky
Môže Excel automaticky aktualizovať sledovacie čísla?
Excel môže zobrazovať obnovené údaje o zásielkach, keď má vhodné pripojenie alebo integráciu. Sledovacie číslo v bunke, hypertextový odkaz ani vzorec TODAY() nezískavajú udalosti od prepravcu. Potrebujete podporovaný zdroj a nakonfigurovaný proces na import jeho aktualizácií.
Prečo Excel zmenil moje sledovacie číslo?
Excel ho mohol interpretovať ako číslo namiesto textu. Pred zadaním alebo importom identifikátorov naformátujte cieľový stĺpec ako Text. Ak už došlo k strate číslic alebo úvodných núl, skopírujte identifikátor znova z pôvodného zdroja; samotné vizuálne formátovanie nedokáže obnoviť chýbajúce informácie.
Mám použiť odhadovaný dátum doručenia alebo sľúbený dátum?
Ak sú dôležité oba, uchovávajte ich v samostatných stĺpcoch. Odhad prepravcu sa môže počas prepravy meniť, zatiaľ čo prísľub zákazníkovi môže pochádzať z pokladne predajcu alebo potvrdenia. Vzorec oneskorenia v tejto šablóne používa sľúbený dátum. Každý vzorec založený na odhade prepravcu príslušne označte.
Ako sledovať objednávku s viacerými balíkmi?
Vytvorte jeden riadok pre každý balík a zopakujte ID objednávky. Každý balík aktualizujte samostatne. Ak potrebujete súhrn na úrovni objednávky, označte objednávku ako dokončenú až po dosiahnutí príslušného výsledku pri všetkých balíkoch potrebných na jej splnenie.
Znamená dlhé obdobie bez skenovania, že sa balík stratil?
Nie. Samotná absencia aktualizácií stratu nedokazuje. Overte najnovšie informácie prepravcu, porovnajte zásielku s očakávaným doručením a postupujte podľa príslušného procesu vyšetrovania. Príznak neaktuálnych údajov má vyvolať kontrolu, nie automaticky označiť balík za stratený.
Zdroje
- Microsoft Support: funkcia COUNTIF — syntax a počítanie podľa kritérií použité v súhrne kontrol.
- Microsoft Support: funkcia TODAY — výpočty aktuálneho dátumu a správanie pri prepočte.
- Microsoft Learn: webový konektor Power Query — webové dátové pripojenia a požiadavky na autentifikáciu.
- sledovanie 17TRACK — vstupný bod na manuálne sledovanie balíkov.
- 17TRACK Tracking API a dokumentácia API — referencie na vyhodnotenie vlastnej integrácie sledovania.
