Pourquoi « moyenne » et « champ calculé » ne vont pas de soi
Le réflexe paraît simple : glisser la date de dépôt et la date de restitution, créer un champ Délai, puis basculer la synthèse en Moyenne. Excel ne le propose pas. La documentation Microsoft indique que la fonction de synthèse ne peut pas être modifiée pour les champs calculés.
La raison tient à la définition : « les formules associées aux champs calculés fonctionnent sur la somme des données sous-jacentes pour tous les champs inclus dans la formule ». Le champ (Restitution − Dépôt) renvoie donc la somme des dates de restitution moins la somme des dates de dépôt, soit le cumul des délais du groupe affiché. La même page écarte les références de cellules, les noms définis et les fonctions de matrice ; restent les opérateurs, les constantes et les autres champs du rapport. Tout l'exercice consiste à faire apparaître le dénominateur sous la forme d'un champ.
Préparer la source, écrire la formule, regrouper
La table source doit comporter une ligne par fiche, avec des colonnes homogènes : Référence, Type de vélo, Date de dépôt, Date de restitution. Les deux dates doivent être de vraies dates, alignées à droite, sinon la soustraction n'a pas de sens. Ajoutez une colonne Compteur et saisissez 1 sur chaque ligne. Ne saisissez aucune information confidentielle ni donnée personnelle : l'exercice se fait sur le jeu fictif ci-dessous.
Le compteur fonctionne parce que la somme des différences est égale à la différence des sommes : le numérateur donne le cumul des délais du groupe, le dénominateur son nombre de fiches. Pour lire une variation, regroupez ensuite les dates : clic droit sur une étiquette de date, Grouper, cochez Début à et Fin à, puis choisissez une unité sous Par. Le regroupement ne touche pas la formule, il change seulement les lignes dont les sommes l'alimentent. Décidez quelle date le porte, car le dépôt et la restitution ne répondent pas à la même question.
Le même menu réunit manuellement des éléments sélectionnés, par exemple plusieurs types sous un groupe Vélos spéciaux.
- Étendre la plage ou passer par un tableau structuré, puis actualiser pour voir apparaître le champ Compteur.
- Onglet d'analyse du tableau croisé dynamique, groupe Calculs, Champs, éléments et jeux, Champ calculé.
- Nommer le champ Délai moyen (jours) et saisir = ('Date de restitution' - 'Date de dépôt') / Compteur, en double-cliquant les noms de champs.
- Dans Paramètres des champs de valeurs, Format de nombre, choisir Nombre avec une décimale, puis afficher le champ Compteur en Nombre pour contrôler l'effectif.
Mise en situation entièrement fictive : cinq fiches d'atelier
Les cinq fiches ci-dessous sont inventées pour l'exercice. Elles décrivent un atelier de remise en état de vélos imaginaire, sans lien avec un dossier réel. Reconstituez la table, ajoutez la colonne Compteur, puis créez le champ calculé.
En ligne Type de vélo, le cumul affiche 18 jours pour les vélos de ville contre 17 pour les cargos : lu seul, il désigne la ville comme poste le plus lent. Le champ calculé neutralise le nombre de fiches et inverse la lecture : 18 / 3 = 6 jours pour la ville, 17 / 2 = 8,5 jours pour les cargos, 35 / 5 = 7 jours au total. Recalculez ces 7 jours à la main avant de poursuivre.
| Fiche (fictive) | Type de vélo | Dépôt puis restitution | Délai (jours) |
|---|---|---|---|
| F-001 | Ville | 02/03 puis 09/03 | 7 |
| F-002 | Ville | 03/03 puis 09/03 | 6 |
| F-003 | Ville | 04/03 puis 09/03 | 5 |
| F-004 | Cargo | 05/03 puis 13/03 | 8 |
| F-005 | Cargo | 06/03 puis 15/03 | 9 |
Trois chemins pour un même indicateur
Le champ calculé n'est pas toujours la meilleure route. Si vous pouvez écrire dans la table source, une colonne Délai ligne par ligne reste plus lisible et autorise la synthèse Moyenne, que Microsoft range parmi les fonctions disponibles pour un champ de valeurs ordinaire. Le champ calculé garde l'avantage quand la source doit rester intacte ou qu'elle est alimentée par un import.
Notez votre choix dans le titre de la feuille : un lecteur qui voit « Somme de Délai » peut croire à une moyenne sans l'intitulé sous les yeux.
| Approche | Quand la préférer | Limite à connaître |
|---|---|---|
| Champ calculé (Restitution − Dépôt) | Lire le cumul des jours immobilisés | Agrégé en Somme, donc jamais une moyenne |
| Champ calculé divisé par un Compteur | Obtenir la moyenne sans calcul ligne par ligne | Le compteur doit valoir 1 partout |
| Colonne Délai dans la source | Comparer Moyenne, Maximum et Nombre | Suppose un accès en écriture à la source |
Les erreurs qui faussent la moyenne sans message d'alerte
Aucune des situations ci-dessous ne déclenche de message : le tableau affiche un nombre, simplement faux. Affichez le champ Compteur en Nombre à côté de la moyenne ; si l'effectif ne correspond pas à votre comptage de fiches, le dénominateur est en cause.
Formulez enfin le résultat en une phrase vérifiable : « délai moyen de remise en état : 7 jours sur 5 fiches restituées en mars ». « Somme de Délai : 35 » ne dit pas ce qu'elle mesure. Champ calculé, regroupement et format des valeurs font partie des points travaillés dans la formation Excel.
Tous les exercices utilisent exclusivement un jeu de données fictif, jamais un vrai dossier client.
| Situation | Ce qui se passe | Action |
|---|---|---|
| La date de restitution est vide, le vélo est encore en atelier | La cellule vide est lue comme zéro et la fiche compte au dénominateur | Ajouter un champ Statut en filtre, ou ne garder que les fiches restituées |
| Les dates viennent d'un export et s'alignent à gauche | Traitées comme du texte, elles ne sont pas additionnées | Les reconvertir en dates, vérifier l'alignement, puis actualiser |
| La colonne Compteur a été ajoutée après le tableau | Le champ n'apparaît pas dans la liste des champs | Étendre la plage source ou passer par un tableau structuré, puis actualiser |
Questions fréquentes
Pourquoi mon champ apparaît-il sous l'étiquette « Somme de Délai moyen » ?
C'est le comportement attendu : la fonction de synthèse d'un champ calculé ne peut pas être changée. Votre formule contenant déjà la division, la valeur affichée est bien une moyenne : renommez l'étiquette pour éviter la confusion.
Puis-je écrire une fonction de moyenne directement dans le champ calculé ?
Non, car un nom de champ placé dans une fonction est compris comme la somme de ce champ. Il faut donc un dénominateur explicite, comme la colonne Compteur.
Comment traiter les vélos encore en atelier au moment de l'analyse ?
Excluez-les du calcul, car une date de restitution absente est interprétée comme zéro. Ajoutez une colonne Statut dans la source, puis placez ce champ en filtre du tableau.
Mon résultat s'affiche comme une date de janvier 1900, que faire ?
La soustraction hérite parfois du format de ses opérandes alors que le résultat est un nombre de jours. Ouvrez Paramètres des champs de valeurs, Format de nombre, puis choisissez Nombre.
Repères et limites
Les intitulés d'onglets et de boîtes de dialogue varient selon la version d'Excel ; vérifiez le libellé exact sur votre poste. La méthode décrite vaut pour un tableau croisé dynamique construit sur une plage ou un tableau de feuille de calcul : un rapport appuyé sur un modèle de données ne propose pas de champ calculé et demande d'écrire une mesure. Toutes les valeurs de l'exercice sont inventées à titre d'illustration.
Sources et ressources
Donnez une suite concrète à votre projet.
Consultez le parcours « Excel » et ses modalités pour préparer votre échange avec notre équipe.
Demander le programme par e-mailVotre messagerie s’ouvre avec la formation et l’article préremplis. Envoyez ensuite votre demande à contact@ceemfi.org.
Consulter le programme →