Online rendelések és szállítmányok nyomon követése Excelben: sablon, képletek és mikor érdemes automatizálni
Az online rendeléseket és szállítmányokat Excelben csomagonként egy sorral, egységes állapotlistával, valamint a késedelmes kézbesítéseket és az elavult ellenőrzéseket jelző képletekkel követheti nyomon. Az alábbi sablon praktikus kiindulópontot biztosít. Az Excel képes rendszerezni és rangsorolni a szállítmányokra vonatkozó információkat, de a képletek önmagukban nem kérik le a fuvarozók aktuális eseményeit; ezekhez manuális ellenőrzésre vagy csatlakoztatott adatforrásra van szükség.
Ez az útmutató kis e-kereskedelmi csapatoknak és több beérkező rendelést kezelő személyeknek készült. Elkülöníti a szállítmány nyilvántartását a nyomon követési feladatoktól, így a táblázat két különböző kérdésre ad választ: hol van a csomag, és kinek kell legközelebb intézkednie?
A nyomon követést a csomagokra, ne csak a rendelésekre építse
A rendelési szám egy vásárlást azonosít. A nyomkövetési szám egy szállítmányt azonosít a fuvarozó rendszerében. A kettő nem felcserélhető. Egy rendelést több csomagban is feladhatnak, és minden csomagnak eltérő kézbesítési dátuma vagy állapota lehet.
Használjon csomagonként egy sort, és szükség esetén ismételje meg a rendelési azonosítót. Ha egy rendelést még nem adtak fel, hozzon létre egy ideiglenes sort nyomkövetési szám nélkül, majd frissítse vagy bontsa fel ezt a sort, amikor a szállítmány adatai elérhetővé válnak. Ne helyezzen több nyomkövetési számot egyetlen cellába: ez megnehezíti a szűrést, a duplikátumok ellenőrzését és az állapotok frissítését.
Személyes rendeléskövető esetén a felelős egyszerűen Ön lehet. Üzleti nyomon követőnél jelöljön ki egy felelőst, aki kivizsgálja a kivételeket, felveszi a kapcsolatot a fuvarozóval vagy az eladóval, illetve kommunikál az ügyféllel. A felelős személy nélküli állapotot könnyű figyelmen kívül hagyni.
Másolható és beilleszthető Excel-sablon szállítmányok nyomon követéséhez
Másolja ki az alábbi tabulátorokkal elválasztott fejlécet, és illessze be egy üres munkalap A1 cellájába. A fejléceknek az A–L oszlopokat kell elfoglalniuk. Ez egy kezdő elrendezés, nem letölthető fájl és nem élő nyomkövetési integráció.
Order ID Tracking number Carrier Ship date Promised delivery Status Last carrier event Last checked Tracking URL Days open Review flag OwnerA rendelési adatok beillesztése előtt formázza az A és B oszlopot Szöveg formátumúra. A nyomkövetési számok azonosítók, nem mennyiségek. Számként kezelve elveszhetnek a kezdő nullák, vagy megváltozhatnak a hosszú számsorok. A formátum későbbi módosítása nem állítja helyre megbízhatóan a már elveszett információkat; szükség esetén importálja újra az eredeti azonosítókat.
| Oszlop | Mit kell megadni | Működési szabály |
|---|---|---|
| A: Order ID | Az üzlet vagy a belső rendelési hivatkozás | Részszállítmányok esetén ismételje meg. |
| B: Tracking number | A csomaghoz megadott azonosító | Szövegként kezelje, és őrizze meg pontos karaktereit. |
| C: Carrier | Az adott számhoz tartozó fuvarozó | Használjon egységes neveket többféle rövidítés helyett. |
| D: Ship date | A szállítási időszak kezdetéhez használt dátum | Határozza meg, hogy ez a feladás vagy a fuvarozó általi átvétel megerősítését jelenti-e. |
| E: Promised delivery | Az alkalmazandó kézbesítési ígéret | Hagyja üresen, ha nincs ígéret; ne találjon ki határidőt. |
| F: Status | Szabványosított szállítmánykategória | Ellenőrzött listából válasszon. |
| G: Last carrier event | A legutóbbi esemény szövege és időpontja | Őrizze meg a szabványosított állapot mögötti részleteket. |
| H: Last checked | Amikor valaki legutóbb ellenőrizte a nyomkövetési információkat | Rögzített időbélyeget adjon meg, ne folyamatosan változó képletet. |
| I: Tracking URL | Ellenőrzött nyomkövetési oldal hivatkozása | A célcímet különítse el a nyomkövetési számtól. |
| J: Days open | Számított szállítási idő | Naptári napokat használjon, hacsak szándékosan nem épít ki más szabályt. |
| K: Review flag | Számított nyomonkövetési emlékeztető | Ez belső szabály, nem fuvarozói állapot. |
| L: Owner | A nyomon követésért felelős személy | Kivétel bekövetkezése előtt jelölje ki a felelőst. |
A munkalap beállítása további sorok hozzáadása előtt
- Alakítsa a tartományt Excel-táblázattá. Válassza a Beszúrás > Táblázat lehetőséget, és erősítse meg, hogy a tartomány fejléceket tartalmaz. A táblázatok megkönnyítik a szűrést, és a számított oszlopok képleteit további sorokra is alkalmazhatják.
- Alkalmazzon dátumformátumokat. A D és E oszlopot dátumként formázza. A H oszlopnál a dátumot és az időt is jelenítse meg. Ellenőrizze, hogy az importált értékek valódi Excel-dátumok-e, nem csupán dátumnak látszó szövegek.
- Hozzon létre állapotlegördülőt. Az Adatérvényesítésnél használjon például ilyen listát: Nincs feladva, Címke létrehozva, Szállítás alatt, Kézbesítés alatt, Kézbesítve, Kivétel, Visszaküldve.
- Rögzítse a fejlécsort. Így a hosszabb listák áttekintésekor is láthatók maradnak az oszlopnevek.
- Adjon meg ellenőrzési küszöbértéket. Az O1 cellába írja be: „Újraellenőrzés ennyi nap után”, a P1 cellába pedig 2-t. Ez példa egy belső ellenőrzési időközre, nem a fuvarozó szolgáltatási szabványa.
A G oszlopban őrizze meg a fuvarozói esemény eredeti megfogalmazását akkor is, ha az F oszlopot egységesíti. Például a „Címke létrehozva” állapotot nem szabad automatikusan „Szállítás alatt” állapotra módosítani: a címke létrehozása önmagában nem bizonyítja, hogy a fuvarozó átvette a csomagot.
A nyomon követőt hasznossá tevő képletek
Az alábbi képletek a fenti oszlopelrendezést, az angol függvényneveket és a vesszős elválasztókat használják. Egyes regionális Excel-beállítások pontosvesszőt alkalmaznak. Írja be őket a 2. sorba, majd töltse lejjebb, vagy engedje, hogy az Excel-táblázat számított oszlopként alkalmazza őket.
A nyitott napok naptári számának kiszámítása
Írja be ezt a J2 cellába:
=IF(OR(D2="",F2="Delivered",F2="Returned"),"",MAX(0,TODAY()-INT(D2)))Ez a képlet a nyitott szállítmány feladásának dátuma óta eltelt naptári napokat adja vissza. A lezárt vagy visszaküldött szállítmányokat üresen hagyja, így azok kora nem növekszik korlátlanul. A képlet nem méri a szállítási teljesítményt a fuvarozó munkanapokban meghatározott vállalásához képest.
Ha korábbi kézbesítési időtartamra van szüksége, adjon hozzá külön Delivered at oszlopot, és számítsa ki a rögzített dátum és a feladás dátuma közötti különbséget. Ne használja a mai dátumot a tényleges kézbesítési dátum helyett.
A felülvizsgálatot igénylő szállítmányok jelölése
Írja be ezt a K2 cellába:
=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"))))))))Ezeknek az ellenőrzéseknek a sorrendje fontos. A kézbesített szállítmányok kerülnek először lezárásra; a visszaküldések és a kivételek konkrét emlékeztetőt kapnak; az ígért dátumon túli szállítmányok elsőbbséget élveznek a rutinellenőrzésekkel szemben. A nyomkövetési szám nélküli csomag feladási ellenőrzésre kerül, nem szokásos nyomkövetési frissítésként kezelik.
Az „Ígért dátum elmúlt” azt jelenti, hogy a munkalapon szereplő dátum elmúlt. Ez nem bizonyítja, hogy a csomag elveszett, hogy kártérítés jár, vagy hogy a fuvarozó megszegte a feltételeit. Továbbítás előtt ellenőrizze az ígéret forrását és a legfrissebb szállítmányadatokat.
Az elavult ellenőrzés szabálya a H2 óta eltelt időt méri. Ha a P1 értéke 2, akkor két nap rögzített ellenőrzés nélküli idő után jelöli meg a rekordot. Állítsa be az időközt a munkafolyamatához. A TODAY() és a NOW() az Excel újraszámításakor frissül; ezek nem háttérben működő felügyeleti szolgáltatások.
A nyitott felülvizsgálati sor megszámolása
Helyezze ezt az összesítő képletet a nyomkövetési táblázaton kívülre:
=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")Ez a figyelmet igénylő csomagsorokat számolja, nem az egyedi rendeléseket vagy ügyfeleket. Szükség esetén bővítse a tartományt, vagy cserélje le a vonatkozó strukturált táblahivatkozásra. Irányítópultokon is tartsa láthatóan a különbséget: egy három csomagot tartalmazó rendelés három szállítmányrekorddal járulhat hozzá.
Ellenőrzött nyomkövetési URL olvasható hivatkozássá alakítása
Egy opcionális extra oszlopban használja ezt:
=IF(I2="","",HYPERLINK(I2,"Track package"))Ez megnyitja az I2 cellában tárolt címet. Nem kér le szállítási eseményeket, és nem erősíti meg, hogy a cím biztonságos. Megbízható nyomkövetési szolgáltatótól vagy a fuvarozótól származó hivatkozásokat használjon, és ne hozzon létre fuvarozóspecifikus URL-eket, hacsak nem ellenőrizte azok formátumát.
A formázás a döntéseket támogassa, ne csak díszítse a munkalapot
Alkalmazzon feltételes formázást a felülvizsgálati jelzőre vagy az egész sorra. Használjon erős figyelmeztető színt a kivételekhez és az ígért dátumot túllépő rekordokhoz, világosabb figyelmeztetést a rutin-újraellenőrzésekhez, valamint visszafogott stílust a lezárt szállítmányokhoz. Hagyja láthatóan a szöveges címkét, hogy a munkalap szín nélkül is érthető maradjon.
Az A2:L1000 tartomány teljes sorára alkalmazott szabályhoz használhat például ilyen képletet: =$K2="Past promised date". Ez kiemeli a megfelelő szállítmányt. A dollárjel rögzíti az állapot oszlopát, miközben lehetővé teszi a sorszám változását.
Újabb tétel importálása előtt ellenőrizze a duplikált rekordokat is. A megismételt rendelési azonosító normális részszállítmányoknál; az ismétlődő fuvarozó- és nyomkövetésiszám-kombináció azonban vizsgálatot igényel. Lehet véletlen duplikátum, de az alapul szolgáló rendelési kapcsolat ellenőrzése nélkül ne törölje automatikusan.
Napi munkafolyamat a nyomon követő megbízhatóságának fenntartásához
- Adja hozzá vagy importálja az új szállítmányokat. Azonosítókat szövegként őrizze meg, és ellenőrizze, hogy minden csomagnak saját sora van.
- Szűrjön a felülvizsgálati jelző alapján. Kezdje a kivételekkel és a lejárt ígéretekkel, majd dolgozza fel a hiányzó nyomkövetési adatokat és az elavult ellenőrzéseket.
- Ellenőrizze a nyomkövetési forrást. Nyissa meg a fuvarozó oldalát, vagy használjon többfuvarozós szolgáltatást a csomag nyomon követéséhez. Hasonlítsa össze a nyomkövetési számot és a fuvarozót a táblázattal.
- Frissítse a bizonyítékokat. Rögzítse a legutóbbi eseményt a G oszlopban, válassza ki a megfelelő állapotot az F oszlopban, és adjon meg rögzített ellenőrzési időbélyeget a H oszlopban.
- Jelölje ki a következő műveletet. Adjon hozzá műveleti megjegyzés oszlopot, ha a felelősnek ügyfélüzenetet, vizsgálati hivatkozást vagy nyomonkövetési dátumot kell rögzítenie.
- Egyeztesse a lezárt rekordokat. Erősítse meg, hogy a kézbesített állapot a megfelelő csomagra vonatkozik, és ne alkalmazza automatikusan a rendelés minden csomagjára.
Ne írja be a =NOW() képletet a „Last checked” mezőbe. Újraszámításkor megváltozik, és úgy tűnhet, mintha egy érintetlen rekordot nemrég ellenőriztek volna. Az időbélyegnek valódi ellenőrzést vagy sikeres automatizált frissítést kell képviselnie.
Az ügyféladatokat a szükséges minimumra korlátozza. Egy megosztott működési nyomon követőnek ritkán van szüksége teljes kézbesítési címre, telefonszámra és minden rendelési részletre minden sorban. Korlátozza a hozzáférést, és ne terjesszen ellenőrizetlen másolatokat személyes adatokat tartalmazó fájlokból.
Mikor érdemes automatizálni a szállítmányok nyomon követését?
Akkor automatizáljon, amikor az állapotok naprakészen tartása válik szűk keresztmetszetté, ne pusztán akkor, amikor a munkalap hosszúvá válik. Nincs univerzális szállítmányszám-küszöb. Egy nagyobb, lassan változó lista is kezelhető maradhat táblázatban, míg egy kisebb, sürgős kivételeket tartalmazó lista gyakoribb frissítést igényelhet, mint amennyit egy személy megbízhatóan el tud végezni.
| Megfigyelt probléma | Gyakorlati következő lépés | Mit kell ellenőrizni |
|---|---|---|
| Ismételt másolás a rendelésexportokból | Egységesítse az importálást, vagy használja a Power Queryt támogatott forrásokhoz. | Azonosítóformázás, oszlop-hozzárendelés és duplikátumkezelés. |
| Ismételt manuális fuvarozói ellenőrzések | Értékelje egy nyomkövetési adatkapcsolat vagy API-alapú munkafolyamat használatát. | A tényleges fuvarozók, szolgáltatások és szállítási útvonalak támogatása. |
| A kivételeket túl későn fedezik fel | Adjon hozzá ütemezett feldolgozást vagy eseményvezérelt riasztásokat. | Aktiválási feltételek, felelősök és a sikertelen riasztások kezelése. |
| Több személy írja felül ugyanazokat a rekordokat | Használjon szabályozott megosztott munkafolyamatot vagy működési rendszert. | Hozzáférési jogosultságok, módosítási előzmények és rekordfelelősség. |
| Az ügyfelek attól függnek, hogy valaki ellenőriz-e egy munkafüzetet | Értékeljen az üzlethez kapcsolódó rendeléskövetési munkafolyamatot. | Rendelés-hozzárendelés, részszállítmányok és az ügyfelek számára megjelenő állapot pontossága. |
A Power Query képes csatlakozni támogatott adatforrásokhoz, beleértve a webes forrásokat is, de ettől még nem válik minden nyilvános nyomkövetési oldal megbízható adatcsatornává. A hitelesítés, az oldal szerkezete, a hozzáférési korlátozások és a frissítési beállítások befolyásolhatják, hogy működik-e a kapcsolat. Részesítse előnyben a dokumentált felületet egy olyan oldal lekérdezésével szemben, amelyet emberi olvasásra terveztek.
Egyedi integrációhoz a 17TRACK Tracking API releváns értékelendő lehetőség. A megvalósítás előtt ellenőrizze a hivatalos dokumentációt a tervezett adatfolyam alapján. Ne feltételezze, hogy egy API-ajánlat natív Excel-csatlakozó, vagy hogy további integrációs munka nélkül frissíteni fog egy meglévő munkafüzetet.
Gyakorlati architektúra lehet, hogy a nyomkövetési frissítéseket egy ellenőrzött szolgáltatásban vagy adattárban gyűjti, hozzárendeli őket a csomagrekordokhoz, majd az eredményül kapott táblát elérhetővé teszi az Excelben. A hitelesítő adatokat ne tárolja széles körben megosztott munkafüzetekben. Az esemény időbélyegét és a beérkezés időbélyegét is tárolja, hogy megkülönböztethető legyen a késleltetett frissítés az új szállítási eseménytől.
Minden automatizált munkafolyamatot teszteljen szokásos kézbesítéseken, részszállítmányokon, hiányzó eseményeken, kivételeken és visszaküldött csomagokon. Ellenőrizze az újrapróbálkozások és az ismételt frissítések kezelését. Tartson látható „utolsó sikeres frissítés” jelzőt: egy régi, kézbesítettnek látszó irányítópult félrevezetőbb lehet, mint egy nyíltan hiányos táblázat.
GYIK
Frissítheti az Excel automatikusan a nyomkövetési számokat?
Az Excel meg tudja jeleníteni a frissített szállítási adatokat, ha megfelelő kapcsolattal vagy integrációval rendelkezik. A cellában lévő nyomkövetési szám, egy hiperhivatkozás vagy egy TODAY() képlet nem kér le fuvarozói eseményeket. Támogatott forrásra és az ezek frissítéseit importáló konfigurált folyamatra van szükség.
Miért változtatta meg az Excel a nyomkövetési számomat?
Az Excel számként, nem pedig szövegként értelmezhette. Az azonosítók beírása vagy importálása előtt formázza a céloszlopot Szöveg formátumúra. Ha a számjegyek vagy a kezdő nullák már elvesztek, másolja ki újra az azonosítót az eredeti forrásból; a vizuális formázás önmagában nem tudja helyreállítani a hiányzó információkat.
A becsült vagy az ígért kézbesítési dátumot használjam?
Ha mindkettő fontos, tartsa őket külön oszlopokban. A fuvarozói becslés a szállítmány előrehaladtával változhat, míg az ügyfélnek tett ígéret az eladó pénztárfolyamatából vagy visszaigazolásából származhat. A sablon késedelmi képlete az ígért dátumot használja. A fuvarozói becslésen alapuló képleteket ennek megfelelően címkézze.
Hogyan követhetek nyomon egy több csomagból álló rendelést?
Hozzon létre minden csomaghoz egy sort, és ismételje meg a rendelési azonosítót. Minden csomagot külön frissítsen. Ha rendelési szintű összesítésre van szüksége, csak akkor jelölje a rendelést teljesítettként, amikor a teljesítéséhez szükséges összes csomag elérte a megfelelő eredményt.
A frissítések hosszú hiánya azt jelenti, hogy a csomag elveszett?
Nem. A frissítések hiánya önmagában nem bizonyítja az elvesztést. Ellenőrizze a fuvarozó legfrissebb információit, hasonlítsa össze a szállítmányt a kézbesítési elvárásokkal, és kövesse az alkalmazandó vizsgálati folyamatot. Az elavult adatokra vonatkozó jelző ellenőrzést kezdeményezzen, ne címkézze automatikusan elveszettnek a csomagot.
Források
- Microsoft Support: COUNTIF function — a felülvizsgálati összesítőben használt szintaxis és feltételalapú számlálás.
- Microsoft Support: TODAY function — aktuális dátumszámítások és újraszámítási működés.
- Microsoft Learn: Power Query Web connector — webes adatkapcsolatok és hitelesítési szempontok.
- 17TRACK tracking — manuális csomagkövetési belépési pont.
- 17TRACK Tracking API és API documentation — hivatkozások egy egyedi nyomkövetési integráció értékeléséhez.
