Vous n’avez pas de logiciel, vous avez un tableur et une facture de 280 lignes. Bonne nouvelle : le rapprochement factures présences sur Excel est tout à fait faisable. Moins bonne nouvelle : il demande une préparation soigneuse et résiste mal au temps. Voici une méthode éprouvée, formule par formule, puis un regard honnête sur ce qu’elle laisse passer.
Préparer les données avant toute formule
Neuf fois sur dix, ce qui fait échouer un tableur de contrôle transport n’est pas la formule, c’est la donnée. Avant d’écrire quoi que ce soit, créez trois onglets.
- Facture : les lignes du transporteur, une course par ligne, avec la date, l’usager, les kilomètres et le montant.
- Absences : une ligne par journée d’absence, avec la date et le nom de l’usager.
- Usagers : le nom tel que vous l’écrivez, et la distance théorique entre son domicile et l’établissement.
Une question se pose dès cette étape : d’où vient la liste des absences ? Si elle est tenue sur un cahier à l’accueil, il faudra la ressaisir, ce qui ajoute une heure et un risque d’erreur. Si le secrétariat la tient déjà dans un fichier partagé, demandez-lui de garder toujours les mêmes colonnes, dans le même ordre, avec une ligne par jour d’absence plutôt qu’une plage « du 3 au 7 ». Ce petit accord entre services rend le rapprochement possible chaque mois.
Puis nettoyez. Supprimez les lignes de total et de sous-total glissées au milieu du fichier. Vérifiez que les dates sont de vraies dates et non du texte : une date alignée à gauche dans la cellule est souvent suspecte. Transformez « 17,54 € » en nombre en retirant le symbole. Enfin, harmonisez les noms. C’est l’étape la plus longue : le transporteur écrit « DUPONT A. », votre registre « Dupont Alice ». Une colonne de correspondance dans l’onglet Usagers, renseignée à la main une fois pour toutes, vous fera gagner beaucoup de temps les mois suivants.
RECHERCHEV et factures : les formules du rapprochement
Les formules ci-dessous suivent la syntaxe française, avec le point-virgule comme séparateur. Adaptez les lettres de colonnes à votre fichier.
Construire une clé date-usager
Dans les onglets Facture et Absences, ajoutez une colonne qui réunit la date et le nom harmonisé : =TEXTE(A2;"jj/mm/aaaa")&"|"&C2. Cette clé est le pivot de tout le rapprochement : deux lignes ayant la même clé concernent le même usager le même jour.
Repérer les courses d’usagers absents
Dans l’onglet Facture, cherchez chaque clé dans la liste des absences : =SIERREUR(RECHERCHEV(F2;Absences!D:D;1;FAUX);""). Si la cellule renvoie la clé, l’usager était déclaré absent ce jour-là et la course doit être examinée. Le cas des absences signalées trop tard mérite une lecture au cas par cas ; nous le traitons dans un article dédié.
Compter les lignes par jour
La formule =NB.SI(F:F;F2) indique combien de lignes partagent la même clé. Au-delà de deux, aller et retour compris, il y a probablement un doublon. Notre article sur les doublons de facturation dans le transport explique les faux positifs à écarter.
Comparer les kilomètres
Récupérez la distance théorique de l’usager : =RECHERCHEV(C2;Usagers!A:B;2;FAUX). Calculez l’écart relatif : =(D2-G2)/G2. Au-delà de 15 %, recalculez au tarif du contrat, par exemple =4,10+G2*1,12, soit 17,54 € pour 12 km théoriques. Une mise en forme conditionnelle en rouge sur ces trois colonnes rend le classeur lisible au premier coup d’œil.
Les limites d’Excel pour un audit mensuel
Le classeur fonctionne. Le premier mois, il vous donne même une vraie satisfaction. Les difficultés apparaissent ensuite.
- Les noms dérivent. Un nouvel usager, une faute de frappe, un nom composé abrégé différemment : la recherche échoue sans prévenir, et la ligne passe pour conforme.
- RECHERCHEV s’arrête au premier résultat. Elle dit si une clé existe, pas combien de fois elle a été justifiée.
- Rien ne prouve le passage. Le tableur sait qui était absent, mais il ignore si le chauffeur s’est présenté. Une course facturée un jour de présence est validée même si personne ne l’a effectuée.
- Le fichier est fragile. Une colonne insérée par un collègue, une plage figée sur 300 lignes alors que la facture en compte 320, et les résultats deviennent faux sans message d’erreur.
- Rien n’est tracé. Qui a modifié quoi, quelle version a servi à contester : impossible à reconstituer six mois plus tard.
Ces limites d’Excel en audit ne disqualifient pas l’outil, elles le cantonnent. Il reste excellent pour un contrôle ponctuel ou pour vérifier quelques lignes. Il l’est beaucoup moins pour un contrôle exhaustif, chaque mois, sur plusieurs transporteurs. La comparaison plus générale entre planning transport sur Excel ou logiciel aboutit d’ailleurs à la même conclusion.
Le temps passé au contrôle, et ce qu’un outil automatise
Comptez honnêtement. Construire le classeur prend une journée la première fois. Ensuite, chaque mois : récupérer les fichiers, nettoyer, mettre à jour la table des noms, vérifier les formules, examiner les lignes rouges. Selon l’état du fichier transmis, on parle de deux à quatre heures par transporteur. Multipliez par le nombre de transporteurs, et le temps passé au contrôle devient un poste à part entière.
C’est exactement ce travail que reprend l’audit des factures de transport de NavigoMédic. Le fichier CSV ou Excel est déposé tel quel : colonnes reconnues, formats français lus, lignes de total ignorées, noms abrégés rapprochés des fiches. Les détails techniques figurent sur la page consacrée à l’import des factures transporteur en Excel ou CSV. Surtout, l’outil ajoute ce que le tableur ne peut pas savoir : le pointage du chauffeur, qui prouve ou non que la course a eu lieu.
| Date | Usager | Présence réelle | Km facturés | Facturé | Analyse | Retenu |
|---|---|---|---|---|---|---|
| 02/09 | Usager A | Pris en charge 08:13 | 7,3 km | 12,28 € | Conforme | 12,28 € |
| 03/09 | Usager B | Absence déclarée la veille | 13,5 km | 19,22 € | Litige absence | 0,00 € |
| 03/09 | Usager C | Pris en charge 08:30 | 10,22 km (théo. 7,3) | 15,55 € | Litige kilométrage | 12,28 € |
| 09/09 | Usager D | Absent non prévenu (pointé) | 11,2 km | 16,64 € | Déplacement à vide | 4,10 € |
| 10/09 | Usager E | Aucun second pointage | 4,2 km | 8,80 € | Doublon | 0,00 € |
| 10/09 | Usager F | Pris en charge 08:51 | 4,8 km | 9,48 € | Conforme | 9,48 € |
Le résultat prend la forme d’un tableau qui oppose présences réelles et lignes facturées, en rouge et vert. Notre article sur le tableau comparatif entre présences et factures détaille sa lecture, et le guide pour auditer ses factures de transport replace l’exercice dans un circuit de validation complet.
Questions fréquentes
Peut-on vraiment faire un tableur de contrôle transport fiable ?
Oui, pour un mois et un transporteur, avec des noms harmonisés et des formules vérifiées. La fiabilité baisse quand les volumes augmentent, que plusieurs personnes modifient le fichier ou que les formats des factures changent.
Quelles formules utiliser pour rapprocher factures et absences ?
Une clé date-usager construite avec TEXTE, une recherche avec RECHERCHEV encadrée par SIERREUR, un comptage des doublons avec NB.SI, et un calcul d’écart relatif pour les kilomètres.
Combien de temps prend le contrôle sur Excel ?
Une journée pour construire le classeur, puis de l’ordre de deux à quatre heures par mois et par transporteur, selon la propreté des fichiers reçus. C’est une estimation qui varie fortement d’un établissement à l’autre.
Avant de choisir, vous pouvez comparer votre classeur au résultat d’un outil sur un mois réel : l’audit gratuit d’un mois de factures de transport vous remet un rapport chiffré, sans engagement.
Votre tableur, sans les formules
Voyez NavigoMédic importer une facture Excel telle quelle et la confronter aux absences et aux pointages en quelques minutes.
