Відстеження онлайн-замовлень і відправлень в 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 як Текст. Номери відстеження — це ідентифікатори, а не кількісні значення. Якщо обробляти їх як числа, початкові нулі можуть бути видалені, а довгі числові рядки — змінені. Зміна формату після цього не гарантує відновлення вже втраченої інформації; за потреби повторно імпортуйте початкові ідентифікатори.
| Стовпець | Що вводити | Правило роботи |
|---|---|---|
| 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. Виберіть «Вставлення» > «Таблиця» та підтвердьте, що діапазон містить заголовки. Таблиці спрощують фільтрування й можуть переносити формули розрахункових стовпців у додаткові рядки.
- Застосуйте формати дат. Відформатуйте D і E як дати. Для H використовуйте дату й час. Переконайтеся, що імпортовані значення є справжніми датами Excel, а не текстом, який лише виглядає як дата.
- Створіть спадний список статусів. Використовуйте перевірку даних зі списком на кшталт «Не відправлено», «Етикетку створено», «У дорозі», «Передано кур’єру для доставки», «Доставлено», «Виняток» і «Повернено».
- Закріпіть рядок заголовка. Залишайте назви стовпців видимими під час перегляду довших списків.
- Додайте порогове значення перевірки. Введіть «Повторна перевірка через днів» у O1 і значення 2 у P1. Це приклад внутрішнього інтервалу перевірки, а не стандарт обслуговування перевізника.
Зберігайте формулювання події перевізника у стовпці G, навіть якщо нормалізуєте стовпець F. Наприклад, «Етикетку створено» не слід автоматично перетворювати на «У дорозі»: саме створення етикетки не підтверджує, що перевізник отримав посилку.
Формули, які роблять трекер корисним
Наведені нижче формули використовують описану структуру стовпців, англійські назви функцій і коми як роздільники. У деяких регіональних налаштуваннях 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"))))))))Порядок цих перевірок має значення. Доставлені відправлення спочатку закриваються; повернення та винятки отримують спеціальні підказки; відправлення, для яких минув обіцяний строк, мають пріоритет над звичайними перевірками. Посилка без номера відстеження спрямовується на перевірку відправлення, а не обробляється як звичайне оновлення відстеження.
«Минула обіцяна дата» означає, що дата у вашій таблиці вже минула. Це не доводить, що посилка загублена, що належить компенсація або що перевізник порушив свої умови. Перед ескалацією перевірте джерело обіцянки та останні дані про відправлення.
Правило застарілої перевірки вимірює час, що минув від 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. | Підтримку ваших фактичних перевізників, сервісів і маршрутів. |
| Винятки виявляються надто пізно | Додайте заплановану обробку або сповіщення на основі подій. | Визначення тригерів, відповідальних осіб і обробку невдалих сповіщень. |
| Кілька людей перезаписують одні й ті самі записи | Використовуйте контрольований спільний робочий процес або операційну систему. | Права доступу, історію змін і відповідальність за записи. |
| Клієнти залежать від того, чи перевірить хтось книгу Excel | Оцініть робочий процес відстеження замовлень, підключений до магазину. | Зіставлення замовлень, розділені відправлення та точність статусу для клієнта. |
Power Query може підключатися до підтримуваних джерел даних, зокрема вебджерел, але це не робить кожну загальнодоступну сторінку відстеження надійним каналом даних. Автентифікація, структура сторінки, обмеження доступу та налаштування оновлення можуть впливати на роботу підключення. Віддавайте перевагу документованому інтерфейсу, а не збиранню даних зі сторінки, призначеної для читання людьми.
Для власної інтеграції доречним варіантом для оцінювання є 17TRACK Tracking API. Перед реалізацією звірте його офіційну документацію з передбаченим потоком даних. Не припускайте, що наявність API означає наявність вбудованого конектора Excel або що він оновлюватиме наявну книгу без додаткової інтеграційної роботи.
Практична архітектура полягає в тому, щоб збирати оновлення відстеження в контрольованому сервісі або сховищі даних, зіставляти їх із записами про посилки, а потім передавати отриману таблицю в Excel. Не зберігайте облікові дані у книгах, до яких має доступ багато користувачів. Зберігайте і часову позначку події, і часову позначку отримання даних, щоб відкладене оновлення можна було відрізнити від нової події відправлення.
Тестуйте будь-який автоматизований робочий процес на звичайних доставках, розділених відправленнях, відсутніх подіях, винятках і повернених посилках. Перевірте обробку повторних спроб і повторних оновлень. Додайте видимий індикатор «останнього успішного оновлення»: інформаційна панель зі старим статусом «доставлено» може вводити в оману більше, ніж відкрита неповна електронна таблиця.
FAQ
Чи може Excel автоматично оновлювати номери відстеження?
Excel може відображати оновлені дані про відправлення, якщо має відповідне підключення або інтеграцію. Номер відстеження в клітинці, гіперпосилання чи формула TODAY() не отримують події від перевізника. Потрібні підтримуване джерело та налаштований процес імпорту його оновлень.
Чому Excel змінив мій номер відстеження?
Excel міг інтерпретувати його як число, а не текст. Відформатуйте цільовий стовпець як текст перед введенням або імпортом ідентифікаторів. Якщо цифри чи початкові нулі вже втрачено, скопіюйте ідентифікатор ще раз із початкового джерела; саме візуальне форматування не може відновити відсутню інформацію.
Чи слід використовувати очікувану дату доставки чи обіцяну дату?
Зберігайте їх в окремих стовпцях, якщо важливі обидві. Оцінка перевізника може змінюватися в міру руху відправлення, тоді як обіцянка клієнту може надходити з оформлення замовлення або підтвердження продавця. Формула прострочення в цьому шаблоні використовує обіцяну дату. Відповідно позначайте будь-яку формулу, що ґрунтується на оцінці перевізника.
Як відстежувати замовлення з кількома посилками?
Створіть один рядок для кожної посилки та повторюйте ідентифікатор замовлення. Оновлюйте кожну посилку окремо. Якщо потрібен підсумок на рівні замовлення, позначайте замовлення як виконане лише після того, як усі необхідні для його виконання посилки досягли відповідного результату.
Чи означає тривалий період без сканувань, що посилка загублена?
Ні. Сама відсутність оновлень не доводить втрату. Перевірте останню інформацію перевізника, порівняйте відправлення з очікуваними строками доставки та дотримуйтеся відповідного процесу розслідування. Прапорець застарілих даних має спонукати до перевірки, а не автоматично позначати посилку як загублену.
Джерела
- Microsoft Support: функція COUNTIF — синтаксис і підрахунок за критеріями, використані в підсумку перевірок.
- Microsoft Support: функція TODAY — розрахунки поточної дати та поведінка перерахунку.
- Microsoft Learn: вебконектор Power Query — вебпідключення до даних і міркування щодо автентифікації.
- Відстеження 17TRACK — сторінка для ручного відстеження посилок.
- 17TRACK Tracking API і документація API — джерела для оцінювання власної інтеграції відстеження.
