Отслеживание онлайн-заказов и отправлений в 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: Идентификатор заказа | Ссылка на заказ в магазине или внутренний номер заказа | Повторяйте его для разделённых отправлений. |
| B: Номер отслеживания | Идентификатор, предоставленный для посылки | Храните его как текст и сохраняйте все символы без изменений. |
| C: Перевозчик | Перевозчик, связанный с этим номером | Используйте единообразные названия, а не несколько сокращений. |
| D: Дата отправки | Дата, с которой начинается отсчёт срока отправления | Определите, означает ли она отправку или подтверждённое принятие перевозчиком. |
| E: Обещанная доставка | Применимое обязательство по доставке | Оставляйте поле пустым, если обещание отсутствует; не придумывайте крайний срок. |
| F: Статус | Стандартизированная категория отправления | Выбирайте значение из контролируемого списка. |
| G: Последнее событие перевозчика | Последний текст события и время события | Сохраняйте детали, лежащие в основе стандартизированного статуса. |
| H: Последняя проверка | Когда в последний раз кто-либо проверял сведения об отслеживании | Вводите фиксированную отметку времени, а не постоянно меняющуюся формулу. |
| I: URL отслеживания | Проверенная ссылка на страницу отслеживания | Храните адрес отдельно от номера отслеживания. |
| J: Дней открыто | Рассчитанный срок существования отправления | Используйте календарные дни, если намеренно не создаёте другое правило. |
| K: Флаг проверки | Рассчитанное напоминание о последующем действии | Это внутреннее правило, а не статус перевозчика. |
| L: Ответственный | Лицо, ответственное за последующие действия | Назначайте ответственного до возникновения исключения. |
Настройте лист перед добавлением новых строк
- Преобразуйте диапазон в таблицу 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)))Формула возвращает количество календарных дней с даты отправки для незавершённого отправления. Для доставленных или возвращённых отправлений она оставляет поле пустым, вместо того чтобы бесконечно увеличивать срок. Формула не измеряет эффективность перевозки относительно обязательства перевозчика по рабочим дням.
Если вам нужна историческая продолжительность доставки, добавьте отдельный столбец Доставлено в и рассчитайте разницу между записанной датой и датой отправки. Не используйте сегодняшнюю дату вместо фактической даты доставки.
Отметьте отправления, требующие проверки
Введите это в 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() в поле «Последняя проверка». Оно меняется при пересчёте и может создать впечатление, что недавно проверялась запись, к которой никто не обращался. Отметка времени должна отражать реальную проверку или успешное автоматическое обновление.
Храните только необходимый минимум информации о клиентах. Общему рабочему трекеру редко нужны полный адрес доставки, номер телефона и подробные сведения о заказе в каждой строке. Ограничьте доступ и не распространяйте неконтролируемые копии с персональными данными.
Когда следует автоматизировать отслеживание отправлений
Автоматизируйте процесс, когда поддержание актуальности статусов становится узким местом, а не просто когда лист становится длинным. Универсального порога по количеству отправлений не существует. Таблица может оставаться удобной для большого, медленно меняющегося списка, тогда как небольшому списку со срочными исключениями могут требоваться более частые обновления, чем человек способен надёжно выполнять.
| Наблюдаемая проблема | Практический следующий шаг | Что проверить |
|---|---|---|
| Повторное копирование из выгрузок заказов | Стандартизировать импорт или использовать Power Query для поддерживаемых источников. | Форматирование идентификаторов, сопоставление столбцов и обработку дубликатов. |
| Повторные ручные проверки перевозчиков | Оценить подключение к данным отслеживания или рабочий процесс на основе API. | Поддержку фактических перевозчиков, услуг и маршрутов отправлений. |
| Слишком позднее обнаружение исключений | Добавить плановую обработку или оповещения на основе событий. | Определения триггеров, ответственных и обработку неудачных уведомлений. |
| Несколько людей перезаписывают одни и те же записи | Использовать контролируемый общий рабочий процесс или операционную систему. | Разрешения доступа, историю изменений и владельцев записей. |
| Клиенты зависят от того, кто-то проверяет книгу | Оценить процесс отслеживания заказов, подключённый к магазину. | Сопоставление заказов, разделённые отправления и точность статусов для клиентов. |
Power Query может подключаться к поддерживаемым источникам данных, включая веб-источники, но это не делает каждую общедоступную страницу отслеживания надёжным каналом данных. Аутентификация, структура страницы, ограничения доступа и настройки обновления могут влиять на работу подключения. Предпочитайте документированный интерфейс извлечению данных со страницы, предназначенной для чтения людьми.
Для пользовательской интеграции подходящим вариантом для оценки является 17TRACK Tracking API. Перед реализацией сопоставьте его официальную документацию с предполагаемым потоком данных. Не предполагайте, что API является встроенным соединителем Excel или что он обновит существующую книгу без дополнительной интеграции.
Практичная архитектура заключается в сборе обновлений отслеживания в контролируемом сервисе или хранилище данных, сопоставлении их с записями о посылках и последующей передаче полученной таблицы в Excel. Не храните учётные данные в широко распространяемых книгах. Сохраняйте как отметку времени события, так и отметку времени получения данных, чтобы отличать задержанное обновление от нового события отправления.
Тестируйте любой автоматизированный процесс на обычных доставках, разделённых отправлениях, отсутствующих событиях, исключениях и возвращённых посылках. Проверьте обработку повторных попыток и повторных обновлений. Добавьте видимый индикатор «последнее успешное обновление»: старая панель, выглядящая как актуальная, может вводить в заблуждение сильнее, чем открыто неполная таблица.
Часто задаваемые вопросы
Может ли Excel автоматически обновлять номера отслеживания?
Excel может отображать обновлённые данные об отправлениях при наличии подходящего подключения или интеграции. Номер отслеживания в ячейке, гиперссылка или формула TODAY() не получают события перевозчика. Нужны поддерживаемый источник и настроенный процесс импорта обновлений.
Почему Excel изменил мой номер отслеживания?
Excel мог интерпретировать его как число, а не как текст. Отформатируйте целевой столбец как текст до ввода или импорта идентификаторов. Если цифры или начальные нули уже потеряны, снова скопируйте идентификатор из исходного источника; одно лишь визуальное форматирование не восстановит недостающую информацию.
Следует ли использовать расчётную дату доставки или обещанную дату?
Если важны обе даты, храните их в отдельных столбцах. Оценка перевозчика может меняться по мере продвижения отправления, тогда как обещание клиенту может исходить из оформления заказа или подтверждения продавца. Формула просрочки в этом шаблоне использует обещанную дату. Соответственно обозначайте любую формулу, основанную на оценке перевозчика.
Как отслеживать заказ с несколькими посылками?
Создайте отдельную строку для каждой посылки и повторяйте идентификатор заказа. Обновляйте каждую посылку независимо. Если нужен итог на уровне заказа, отмечайте заказ как завершённый только после того, как все необходимые для его выполнения посылки достигли соответствующего результата.
Означает ли длительное отсутствие сканирований, что посылка потеряна?
Нет. Одного отсутствия обновлений недостаточно, чтобы доказать потерю. Проверьте последние сведения перевозчика, сопоставьте отправление с ожидаемыми сроками доставки и следуйте применимой процедуре расследования. Флаг устаревших данных должен инициировать проверку, а не автоматически обозначать посылку как потерянную.
Источники
- Microsoft Support: функция COUNTIF — синтаксис и подсчёт по условиям, используемые в сводке проверок.
- Microsoft Support: функция TODAY — расчёты текущей даты и поведение при пересчёте.
- Microsoft Learn: веб-соединитель Power Query — веб-подключения к данным и особенности аутентификации.
- Отслеживание 17TRACK — страница для ручного отслеживания посылок.
- 17TRACK Tracking API и документация API — источники для оценки пользовательской интеграции отслеживания.
