Suivre les commandes et les expéditions en ligne dans Excel : modèle, formules et quand automatiser
Vous pouvez suivre les commandes et les expéditions en ligne dans Excel avec une ligne par colis, une liste de statuts cohérente et des formules qui signalent les livraisons en retard et les vérifications obsolètes. Le modèle ci-dessous vous offre un point de départ pratique. Excel peut organiser et hiérarchiser les informations d'expédition, mais les formules seules ne récupèrent pas les événements en temps réel des transporteurs ; ces mises à jour nécessitent des vérifications manuelles ou une source de données connectée.
Ce guide est conçu pour les petites équipes de commerce électronique et toute personne gérant plusieurs commandes entrantes. Il sépare l'enregistrement de l'expédition du travail de suivi, afin que votre feuille de calcul réponde à deux questions différentes : où se trouve le colis et qui doit agir ensuite ?
Créez le suivi autour des colis, et pas seulement des commandes
Un numéro de commande identifie un achat. Un numéro de suivi identifie une expédition dans le système d'un transporteur. Ils ne sont pas interchangeables. Une commande peut être expédiée en plusieurs colis, et chaque colis peut avoir une date de livraison ou un statut différent.
Utilisez une ligne par colis, en répétant l'identifiant de commande si nécessaire. Si une commande n'a pas encore été expédiée, créez une ligne provisoire sans numéro de suivi, puis mettez-la à jour ou divisez-la lorsque les détails de l'expédition sont disponibles. Ne placez pas plusieurs numéros de suivi dans une seule cellule : cela rend le filtrage, la recherche de doublons et la mise à jour des statuts plus difficiles.
Pour un outil de suivi personnel des achats, le responsable peut simplement être vous. Pour un outil professionnel, désignez une personne chargée d'examiner les exceptions, de contacter le transporteur ou le vendeur, ou de communiquer avec le client. Un statut sans personne responsable est facile à négliger.
Modèle Excel de suivi des expéditions à copier-coller
Copiez l'en-tête séparé par des tabulations ci-dessous et collez-le dans la cellule A1 d'une feuille de calcul vierge. Les intitulés doivent occuper les colonnes A à L. Il s'agit d'une structure de départ, et non d'un fichier téléchargeable ou d'une intégration de suivi en temps réel.
Order ID Tracking number Carrier Ship date Promised delivery Status Last carrier event Last checked Tracking URL Days open Review flag OwnerAvant de coller les données de commande, formatez les colonnes A et B en Texte. Les numéros de suivi sont des identifiants, pas des quantités. Les traiter comme des nombres peut supprimer les zéros initiaux ou modifier les longues chaînes numériques. Modifier le format par la suite ne restaurera pas de manière fiable les informations déjà perdues ; réimportez les identifiants d'origine si nécessaire.
| Colonne | À saisir | Règle de fonctionnement |
|---|---|---|
| A: Identifiant de commande | La référence de commande de la boutique ou interne | Répétez-la pour les expéditions fractionnées. |
| B: Numéro de suivi | L'identifiant fourni pour le colis | Conservez-le au format texte et préservez exactement ses caractères. |
| C: Transporteur | Le transporteur associé à ce numéro | Utilisez des noms cohérents plutôt que plusieurs abréviations. |
| D: Date d'expédition | La date utilisée pour commencer le calcul de l'ancienneté de l'expédition | Définissez si elle correspond à l'expédition ou à l'acceptation confirmée par le transporteur. |
| E: Livraison promise | L'engagement de livraison applicable | Laissez vide lorsqu'aucune promesse n'existe ; n'inventez pas de délai. |
| F: Statut | Une catégorie d'expédition normalisée | Choisissez-la dans une liste contrôlée. |
| G: Dernier événement du transporteur | Le dernier texte et l'heure de l'événement | Conservez le détail qui sous-tend le statut normalisé. |
| H: Dernière vérification | Le moment où une personne a vérifié pour la dernière fois les informations de suivi | Saisissez un horodatage fixe, et non une formule qui change continuellement. |
| I: URL de suivi | Un lien vérifié vers la page de suivi | Conservez la destination séparément du numéro de suivi. |
| J: Jours écoulés | L'ancienneté calculée de l'expédition | Utilisez les jours calendaires, sauf si vous établissez volontairement une autre règle. |
| K: Indicateur de vérification | Une invite de suivi calculée | Il s'agit d'une règle interne, pas d'un statut du transporteur. |
| L: Responsable | La personne responsable du suivi | Attribuez la responsabilité avant qu'une exception ne survienne. |
Configurez la feuille avant d'ajouter d'autres lignes
- Convertissez la plage en tableau Excel. Utilisez Insertion > Tableau et confirmez que la plage comporte des en-têtes. Les tableaux facilitent le filtrage et peuvent appliquer les formules de colonnes calculées aux lignes supplémentaires.
- Appliquez les formats de date. Formatez D et E comme des dates. Formatez H avec la date et l'heure. Vérifiez que les valeurs importées sont de vraies dates Excel et non du texte qui ressemble simplement à une date.
- Créez une liste déroulante de statuts. Utilisez la validation des données avec une liste telle que Non expédié, Étiquette créée, En transit, En cours de livraison, Livré, Exception et Retourné.
- Figez la ligne d'en-tête. Gardez les intitulés de colonnes visibles lors de l'examen de listes plus longues.
- Ajoutez un seuil de vérification. Inscrivez « Recheck after days » dans O1 et saisissez 2 dans P1. Il s'agit d'un exemple d'intervalle de vérification interne, et non d'une norme de service du transporteur.
Conservez le libellé de l'événement du transporteur dans la colonne G, même lorsque vous normalisez la colonne F. Par exemple, « Étiquette créée » ne doit pas automatiquement devenir « En transit » : la création d'une étiquette ne prouve pas à elle seule que le transporteur a reçu le colis.
Des formules qui rendent le suivi utile
Les formules ci-dessous utilisent la structure de colonnes précédente, les noms de fonctions en anglais et des séparateurs par virgule. Certains paramètres régionaux d'Excel utilisent plutôt des points-virgules. Saisissez-les à la ligne 2 et recopiez-les vers le bas, ou laissez le tableau Excel les appliquer comme colonnes calculées.
Calculer le nombre de jours calendaires écoulés
Saisissez ceci dans J2 :
=IF(OR(D2="",F2="Delivered",F2="Returned"),"",MAX(0,TODAY()-INT(D2)))Cette formule renvoie le nombre de jours calendaires depuis la date d'expédition pour une expédition en cours. Elle laisse vides les expéditions terminées ou retournées au lieu de continuer à les faire vieillir indéfiniment. La formule ne mesure pas la performance du transport par rapport à l'engagement du transporteur en jours ouvrés.
Si vous avez besoin de la durée historique de livraison, ajoutez une colonne distincte Delivered at et calculez la différence entre cette date enregistrée et la date d'expédition. N'utilisez pas la date du jour comme substitut à la date réelle de livraison.
Signaler les expéditions nécessitant une vérification
Saisissez ceci dans 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"))))))))L'ordre de ces vérifications est important. Les expéditions livrées sont clôturées en premier ; les retours et les exceptions reçoivent des invites spécifiques ; les expéditions ayant dépassé leur date promise sont prioritaires par rapport aux vérifications de routine. Un colis sans numéro de suivi est orienté vers une vérification de l'expédition plutôt que traité comme une mise à jour de suivi ordinaire.
« Date promise dépassée » signifie que la date indiquée dans votre feuille est passée. Cela ne prouve pas qu'un colis est perdu, qu'une indemnisation est due ou qu'un transporteur a enfreint ses conditions. Vérifiez la source de la promesse et les derniers détails de l'expédition avant d'escalader.
La règle de vérification obsolète mesure le temps écoulé depuis H2. Avec P1 défini sur 2, elle signale un enregistrement après deux jours sans vérification enregistrée. Adaptez cet intervalle à votre flux de travail. TODAY() et NOW() se mettent à jour lorsque Excel recalcule ; ce ne sont pas des services de surveillance en arrière-plan.
Compter la file de vérification ouverte
Placez cette formule de synthèse en dehors du tableau de suivi :
=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")Cette formule compte les lignes de colis nécessitant une intervention, et non les commandes ou clients uniques. Étendez la plage si nécessaire, ou remplacez-la par la référence structurée du tableau concerné. Maintenez cette distinction visible dans tout tableau de bord : une commande comportant trois colis peut contribuer à trois enregistrements d'expédition.
Transformer une URL de suivi vérifiée en lien lisible
Dans une colonne supplémentaire facultative, utilisez :
=IF(I2="","",HYPERLINK(I2,"Track package"))Cette formule ouvre l'adresse stockée dans I2. Elle ne récupère pas les événements d'expédition et ne confirme pas que l'adresse est sûre. Utilisez des liens provenant d'un service de suivi fiable ou du transporteur, et évitez de construire des URL propres à un transporteur sans en avoir vérifié le format.
Utilisez le formatage pour faciliter les décisions, pas pour décorer la feuille
Appliquez une mise en forme conditionnelle à l'indicateur de vérification ou à toute la ligne. Utilisez une couleur d'avertissement forte pour les exceptions et les enregistrements dont la date promise est dépassée, une couleur plus claire pour les nouvelles vérifications de routine et un style discret pour les expéditions clôturées. Gardez le libellé textuel visible afin que la feuille reste compréhensible sans couleur.
Pour une règle appliquée à toute la ligne A2:L1000, une formule telle que =$K2="Past promised date" met en évidence l'expédition correspondante. Le signe dollar fixe la colonne du statut tout en permettant au numéro de ligne de changer.
Vérifiez également les doublons avant d'importer un nouveau lot. La répétition d'un identifiant de commande est normale pour les expéditions fractionnées ; la répétition d'une combinaison transporteur-numéro de suivi mérite une vérification. Il peut s'agir d'un doublon accidentel, mais ne le supprimez pas automatiquement sans vérifier la relation avec la commande sous-jacente.
Flux de travail quotidien pour maintenir la fiabilité du suivi
- Ajoutez ou importez les nouvelles expéditions. Conservez les identifiants au format texte et vérifiez que chaque colis possède sa propre ligne.
- Filtrez selon l'indicateur de vérification. Commencez par les exceptions et les promesses dépassées, puis traitez les informations de suivi manquantes et les vérifications obsolètes.
- Vérifiez la source de suivi. Ouvrez la page du transporteur ou utilisez un service multitransporteurs pour suivre le colis. Comparez le numéro de suivi et le transporteur avec la feuille de calcul.
- Mettez à jour les éléments justificatifs. Enregistrez le dernier événement dans G, sélectionnez le statut approprié dans F et saisissez un horodatage fixe de vérification dans H.
- Attribuez l'action suivante. Ajoutez une colonne de notes d'action si le responsable doit consigner un message client, une référence d'enquête ou une date de suivi.
- Rapprochez les enregistrements clôturés. Vérifiez qu'un statut livré s'applique au bon colis, et non automatiquement à tous les colis de la commande.
Ne saisissez pas =NOW() dans « Last checked ». Cette formule change lors du recalcul et peut donner l'impression qu'un enregistrement non consulté a été vérifié récemment. L'horodatage doit représenter une vérification réelle ou une mise à jour automatisée réussie.
Limitez les informations client au strict nécessaire. Un outil de suivi opérationnel partagé n'a généralement pas besoin de contenir une adresse de livraison complète, un numéro de téléphone et tous les détails de la commande sur chaque ligne. Restreignez l'accès et évitez de distribuer des copies non contrôlées contenant des informations personnelles.
Quand automatiser le suivi des expéditions
Automatisez lorsque le maintien à jour des statuts devient le goulot d'étranglement, et pas simplement lorsque la feuille devient longue. Il n'existe pas de seuil universel de nombre d'expéditions. Une feuille de calcul peut rester exploitable pour une liste plus importante évoluant lentement, tandis qu'une liste plus petite comportant des exceptions urgentes peut nécessiter des mises à jour plus fréquentes qu'une personne ne peut effectuer de manière fiable.
| Problème observé | Prochaine étape pratique | Éléments à vérifier |
|---|---|---|
| Copie répétée depuis des exports de commandes | Standardisez les importations ou utilisez Power Query pour les sources prises en charge. | Formatage des identifiants, correspondance des colonnes et gestion des doublons. |
| Vérifications manuelles répétées auprès des transporteurs | Évaluez une connexion aux données de suivi ou un flux de travail s'appuyant sur une API. | Prise en charge de vos transporteurs, services et itinéraires d'expédition réels. |
| Les exceptions sont découvertes trop tard | Ajoutez un traitement planifié ou des alertes déclenchées par des événements. | Définitions des déclencheurs, responsables et gestion des alertes échouées. |
| Plusieurs personnes écrasent les mêmes enregistrements | Utilisez un flux de travail partagé contrôlé ou un système opérationnel. | Autorisations d'accès, historique des modifications et propriété des enregistrements. |
| Les clients dépendent d'une personne pour consulter un classeur | Évaluez un flux de suivi des commandes connecté à la boutique. | Correspondance des commandes, expéditions fractionnées et exactitude du statut présenté au client. |
Power Query peut se connecter à des sources de données prises en charge, notamment à des sources web, mais cela ne transforme pas toute page publique de suivi en flux de données fiable. L'authentification, la structure de la page, les restrictions d'accès et la configuration de l'actualisation peuvent influencer le fonctionnement d'une connexion. Préférez une interface documentée à l'extraction d'une page conçue pour être lue par des personnes.
Pour une intégration personnalisée, l'API de suivi 17TRACK est une option pertinente à évaluer. Consultez sa documentation officielle par rapport au flux de données prévu avant la mise en œuvre. Ne supposez pas qu'une API proposée constitue un connecteur Excel natif ou qu'elle mettra à jour un classeur existant sans travail d'intégration supplémentaire.
Une architecture pratique consiste à recueillir les mises à jour de suivi dans un service ou un entrepôt de données contrôlé, à les associer à vos enregistrements de colis, puis à exposer le tableau obtenu dans Excel. Ne stockez pas les identifiants d'accès dans des classeurs largement partagés. Stockez à la fois l'horodatage de l'événement et celui de l'ingestion afin de distinguer une mise à jour retardée d'un nouvel événement d'expédition.
Testez tout flux automatisé sur des livraisons ordinaires, des expéditions fractionnées, des événements manquants, des exceptions et des colis retournés. Vérifiez la gestion des nouvelles tentatives et des mises à jour répétées. Conservez un indicateur visible de la « dernière actualisation réussie » : un tableau de bord ancien donnant l'impression que les colis sont livrés peut être plus trompeur qu'une feuille de calcul ouvertement incomplète.
FAQ
Excel peut-il mettre automatiquement à jour les numéros de suivi ?
Excel peut afficher des données d'expédition actualisées lorsqu'il dispose d'une connexion ou d'une intégration adaptée. Un numéro de suivi dans une cellule, un lien hypertexte ou une formule TODAY() ne récupère pas les événements du transporteur. Vous avez besoin d'une source prise en charge et d'un processus configuré pour importer ses mises à jour.
Pourquoi Excel a-t-il modifié mon numéro de suivi ?
Excel l'a peut-être interprété comme un nombre plutôt que comme du texte. Formatez la colonne de destination en Texte avant de saisir ou d'importer les identifiants. Si des chiffres ou des zéros initiaux ont déjà été perdus, recopiez l'identifiant depuis sa source d'origine ; le formatage visuel seul ne peut pas reconstruire les informations manquantes.
Dois-je utiliser la date de livraison estimée ou la date promise ?
Conservez-les dans des colonnes distinctes si les deux sont importantes. L'estimation du transporteur peut changer au fur et à mesure de l'avancement de l'expédition, tandis que la promesse faite au client peut provenir du paiement ou de la confirmation du vendeur. La formule de dépassement de délai de ce modèle utilise la date promise. Étiquetez en conséquence toute formule fondée sur une estimation du transporteur.
Comment suivre une commande comportant plusieurs colis ?
Créez une ligne pour chaque colis et répétez l'identifiant de commande. Mettez à jour chaque colis indépendamment. Si vous avez besoin d'un résumé au niveau de la commande, ne marquez la commande comme terminée qu'après que tous les colis nécessaires à son exécution ont atteint le résultat approprié.
Une longue période sans scan signifie-t-elle qu'un colis est perdu ?
Non. L'absence de mises à jour ne prouve pas à elle seule une perte. Vérifiez les dernières informations du transporteur, comparez l'expédition aux attentes de livraison et suivez la procédure d'enquête applicable. Un indicateur de données obsolètes doit déclencher une vérification, et non étiqueter automatiquement un colis comme perdu.
Sources
- Support Microsoft : fonction COUNTIF — syntaxe et comptage fondé sur des critères utilisés dans le résumé des vérifications.
- Support Microsoft : fonction TODAY — calculs de date actuelle et comportement du recalcul.
- Microsoft Learn : connecteur Web de Power Query — connexions aux données web et considérations relatives à l'authentification.
- Suivi 17TRACK — point d'accès au suivi manuel des colis.
- API de suivi 17TRACK et documentation de l'API — références pour évaluer une intégration de suivi personnalisée.
