Excel : numérotation automatique d’une colonne selon une autre (A5\:A34 ↔ E5\:E34)

Attribuez automatiquement des numéros séquentiels en colonne A d’après les valeurs de la colonne E, sans VBA. Cet article vous propose une formule robuste compatible « toutes versions » et des variantes modernes (Office 365), avec cas particuliers, pas‑à‑pas, dépannage et bonnes pratiques.

Sommaire

Vue d’ensemble de la question

Objectif : dans la plage A5:A34, attribuer automatiquement des numéros séquentiels à partir de 1 (jusqu’à 30 au maximum car il y a 30 lignes), d’après les valeurs de E5:E34.

Si une valeur de E se répète, la ligne correspondante de A doit reprendre le même numéro que sa première occurrence.
A5:A34 doivent rester vides si la cellule E correspondante est vide ou vaut 0.

Réponse & solution

Formule robuste (compatible Excel 2010 → Microsoft 365)

Entrez la formule ci‑dessous en A5 puis recopiez‑la jusqu’en A34.

Excel en anglais

=IF(OR($E5="", $E5=0), "", IF(COUNTIF($E$4:$E4, $E5)>0, XLOOKUP($E5, $E$4:$E4, $A$4:$A4), MAX($A$4:$A4)+1))

Excel en français (fonctions localisées ; remplacez la virgule par un point‑virgule si nécessaire)

=SI(OU($E5=""; $E5=0); ""; SI(NB.SI($E$4:$E4; $E5)>0; RECHERCHEX($E5; $E$4:$E4; $A$4:$A4); MAX($A$4:$A4)+1))

Ce que fait la formule :

Si E5 est vide ou égal à 0 ⟶ A5 reste vide.
Sinon, si la valeur de E5 est déjà apparue au‑dessus (E$4:E4) ⟶ on réutilise le numéro déjà attribué (via XLOOKUP/RECHERCHEX entre E et A).
Sinon ⟶ on attribue MAX(A$4:A4)+1, soit le prochain numéro (le premier sera 1 car MAX d’une plage vide renvoie 0).

Remarque « maximum 30 » : comme le bloc E5:E34 comporte 30 lignes, le nombre maximal de numéros distincts ne dépassera pas 30.

Variantes utiles / cas particuliers

Si la colonne E peut contenir le texte `"0"` (zéro au format texte)

Étendez la condition :

EN

=IF(OR($E5="", $E5=0, $E5="0"), "", IF(COUNTIF($E$4:$E4, $E5)>0, XLOOKUP($E5, $E$4:$E4, $A$4:$A4), MAX($A$4:$A4)+1))

FR

=SI(OU($E5=""; $E5=0; $E5="0"); ""; SI(NB.SI($E$4:$E4; $E5)>0; RECHERCHEX($E5; $E$4:$E4; $A$4:$A4); MAX($A$4:$A4)+1))

Sans RECHERCHEX (versions plus anciennes d’Excel)

Remplacez XLOOKUP/RECHERCHEX par INDEX/EQUIV :

EN

=IF(OR($E5="", $E5=0), "", IF(COUNTIF($E$4:$E4, $E5)>0, INDEX($A$4:$A4, MATCH($E5, $E$4:$E4, 0)), MAX($A$4:$A4)+1))

FR

=SI(OU($E5=""; $E5=0); ""; SI(NB.SI($E$4:$E4; $E5)>0; INDEX($A$4:$A4; EQUIV($E5; $E$4:$E4; 0)); MAX($A$4:$A4)+1))

Variante moderne 365 : une seule formule « déversée » pour A5:A34

Si vous utilisez Microsoft 365/Excel 2021+, vous pouvez obtenir le même résultat avec une formule matricielle en une seule saisie (aucune recopie nécessaire). Collez la formule suivante en A5 ; elle se « déversera » jusqu’à A34 :

EN

=IF((E5:E34="")+(E5:E34=0), "", XMATCH(E5:E34, UNIQUE(FILTER(E5:E34, (E5:E34<>"")*(E5:E34<>0)))))

FR

=SI((E5:E34="")+(E5:E34=0); ""; EQUIVX(E5:E34; UNIQUE(FILTRE(E5:E34; (E5:E34<>"")*(E5:E34<>0)))))

Pourquoi ça marche ? UNIQUE(FILTRE(...)) construit la liste des valeurs non vides/non nulles dans l’ordre d’apparition. XMATCH/EQUIVX retourne la position de chaque valeur de E5:E34 dans cette liste : ce sont précisément les numéros recherchés. Les lignes vides/égales à 0 restent vides grâce au IF/SI.

Pas à pas : mise en place et vérifications

Préparez votre feuille : vos données sont en E5:E34.
Collez la formule (version « robuste ») en A5.
Recopiez jusqu’en A34 (poignée de recopie ou double‑clic sur l’angle inférieur droit de la cellule).
Contrôlez :
- La première valeur non vide/non nulle reçoit le numéro 1.
- Les valeurs identiques reprennent le même numéro.
- Les cellules de A sont vides lorsque E est vide ou égal à 0.

Exemple rapide

Supposons les entrées suivantes dans E5:E14 :

Cellule	Valeur en E	Résultat attendu en A	Commentaire
E5	Alpha	1	Première valeur non vide
E6	Bravo	2	Nouvelle valeur
E7	Alpha	1	Répétition ⟶ même numéro
E8	(vide)	(vide)	Vide ⟶ pas de numéro
E9	0	(vide)	Zéro ⟶ pas de numéro
E10	Charlie	3	Nouvelle valeur
E11	Bravo	2	Répétition
E12	Delta	4	Nouvelle valeur
E13	0	(vide)	Numérique 0 ⟶ vide
E14	« 0 »	(vide)*	Vide si vous utilisez la variante qui traite « 0 » texte

Conseils pratiques

Recopie : entrez la formule en A5 puis copiez vers le bas jusqu’en A34.
Localisation Excel : selon vos paramètres régionaux, remplacez les virgules par des points‑virgules dans les formules.
Insertion de lignes : si vous insérez des lignes au‑dessus de la ligne 5, vérifiez que les références « ligne précédente » ($E$4:$E4 et $A$4:$A4) s’ajustent correctement.
Stabilité après tri : si vous triez les lignes, les numéros suivent les lignes, ce qui est généralement souhaité (le même identifiant pour la même valeur).
Listes structurées : convertir E5:E34 en Tableau Excel (Ctrl+T) facilite la recopie et les références, mais n’est pas obligatoire.

Aller plus loin : formules « propres », rapides et faciles à maintenir

Nettoyer/normaliser les valeurs avant de numéroter

Si vos données contiennent des espaces parasites, des majuscules/minuscules mélangées ou des caractères invisibles, normalisez la valeur de E5 avant de l’utiliser :

EN (cellule A5)

=LET(
  e, TRIM(CLEAN($E5)),
  IF(OR(e="", e=0), "",
     IF(COUNTIF($E$4:$E4, e)>0, XLOOKUP(e, $E$4:$E4, $A$4:$A4), MAX($A$4:$A4)+1)
))

FR (cellule A5)

=LET(
  e; SUPPRESPACE(EPURAGE($E5));
  SI(OU(e=""; e=0); "";
     SI(NB.SI($E$4:$E4; e)>0; RECHERCHEX(e; $E$4:$E4; $A$4:$A4); MAX($A$4:$A4)+1)
))

Remarque : TRIM/SUPPRESPACE retire les espaces superflus, CLEAN/EPURAGE supprime les caractères non imprimables.

Limiter explicitement à 30 identifiants

Le contexte (30 lignes) suffit normalement. Si vous souhaitez « verrouiller » un plafond explicite de 30 numéros distincts :

EN

=IF(OR($E5="", $E5=0), "",
   IF(COUNTIF($E$4:$E4, $E5)>0, XLOOKUP($E5, $E$4:$E4, $A$4:$A4),
      IF(MAX($A$4:$A4)+1<=30, MAX($A$4:$A4)+1, "")
))

FR

=SI(OU($E5=""; $E5=0); "";
   SI(NB.SI($E$4:$E4; $E5)>0; RECHERCHEX($E5; $E$4:$E4; $A$4:$A4);
      SI(MAX($A$4:$A4)+1<=30; MAX($A$4:$A4)+1; "")
))

Version « ultra concise » avec fonctions dynamiques (365)

Pour des plages plus grandes, la version déversée est souvent plus rapide et plus simple :

EN (A5)

=LET(
  e, E5:E34,
  u, UNIQUE(FILTER(e, (e<>"")*(e<>0))),
  IF((e="")+(e=0), "", XMATCH(e, u))
)

FR (A5)

=LET(
  e; E5:E34;
  u; UNIQUE(FILTRE(e; (e<>"")*(e<>0)));
  SI((e="")+(e=0); ""; EQUIVX(e; u))
)

Dépannage : questions fréquentes et solutions

Mes « 0 » sont parfois au format texte

Utilisez la variante qui teste également "0" (zéro texte). Vous pouvez aussi forcer une conversion :

=IF(OR($E5="", VALUE($E5)=0), "", ...)

En français : SI(OU($E5=""; VALEUR($E5)=0); ""; ...)

La sensibilité à la casse

Par défaut, COUNTIF/NB.SI et XLOOKUP/RECHERCHEX ne sont pas sensibles à la casse (« Alpha » = « ALPHA »). Si vous devez différencier la casse, remplacez le test de répétition par une combinaison INDEX + EQUIV avec EXACT/EXACT :

EN

=IF(OR($E5="", $E5=0), "",
   IF(SUMPRODUCT(--EXACT($E$4:$E4, $E5))>0,
      INDEX($A$4:$A4, MATCH(TRUE, EXACT($E$4:$E4, $E5), 0)),
      MAX($A$4:$A4)+1
))

FR

=SI(OU($E5=""; $E5=0); "";
   SI(SOMMEPROD(--EXACT($E$4:$E4; $E5))>0;
      INDEX($A$4:$A4; EQUIV(VRAI; EXACT($E$4:$E4; $E5); 0));
      MAX($A$4:$A4)+1
))

Performances sur de grandes plages

Préférez la formule « déversée » (365) : un seul calcul matriciel au lieu de 30/3 000 copiés.
Avec la version « ligne par ligne », encapsulez dans LET pour éviter de recalculer plusieurs fois les mêmes sous‑plages ($E$4:$E4, $A$4:$A4).
Si nécessaire, remplacez MAX($A$4:$A4)+1 par un compteur « SCAN » (365) pour réduire les agrégations répétées ; mais dans 30 lignes, l’impact est négligeable.

La formule renvoie 1 alors qu’il n’y a rien au‑dessus : est‑ce normal ?

Oui. Avec MAX($A$4:$A4)+1, si la plage précédente est vide, MAX est évalué à 0 ⟶ premier numéro : 1.

Que se passe‑t‑il si je filtre/masque des lignes ?

Les numéros sont calculés sur les lignes visibles et masquées (la formule ne « voit » pas les filtres). Si vous avez besoin d’un comportement « filtre‑aware », il faut bâtir une logique spécifique à base de AGGREGATE/AGREGAT et de tests de visibilité, ce qui sort du cadre de ce besoin.

Adaptation à d’autres plages et colonnes

Le principe reste identique :

Mettez le point de départ de la formule à la première ligne de votre plage cible.
Adaptez la colonne des données sources (ici E) et la colonne de numérotation (ici A).
Conservez l’astuce « $E$4:$E4 » et « $A$4:$A4 » : elles pointent vers « les lignes au‑dessus » et s’ajustent automatiquement si vous commencez ailleurs (remplacez 4 par « ligne de départ – 1 »).

Checklist de bonnes pratiques

Bloquez la colonne A (protection de feuille) si d’autres personnes éditent le fichier : on évite d’écraser la formule par inadvertance.
Typage des zéros : évitez de mélanger 0 numérique et « 0 » texte. Si ce mélange est fréquent, adoptez la variante avec $E5="0" ou normalisez les entrées (validation de données).
Noms cohérents : si vos valeurs de E proviennent de formulaires ou d’imports, nettoyez‑les (TRIM/SUPPRESPACE & CLEAN/EPURAGE) pour éviter les faux doublons.
Besoin de réinitialiser : si vous supprimez une valeur en E, la cellule A correspondante redevient vide ; rien à faire de plus.

Récapitulatif express

Formule « toutes versions » (A5, à recopier) :
- EN : =IF(OR($E5="", $E5=0), "", IF(COUNTIF($E$4:$E4, $E5)>0, XLOOKUP($E5, $E$4:$E4, $A$4:$A4), MAX($A$4:$A4)+1))
- FR : =SI(OU($E5=""; $E5=0); ""; SI(NB.SI($E$4:$E4; $E5)>0; RECHERCHEX($E5; $E$4:$E4; $A$4:$A4); MAX($A$4:$A4)+1))
Variante 365 « déversée » (A5, pas de recopie) :
- EN : =IF((E5:E34="")+(E5:E34=0), "", XMATCH(E5:E34, UNIQUE(FILTER(E5:E34, (E5:E34<>"")*(E5:E34<>0)))))
- FR : =SI((E5:E34="")+(E5:E34=0); ""; EQUIVX(E5:E34; UNIQUE(FILTRE(E5:E34; (E5:E34<>"")*(E5:E34<>0)))))
Cas particuliers : gérer « 0 » en texte, nettoyage des données, limite explicite à 30.

FAQ courte

Q : La formule doit‑elle absolument commencer en A5 ?
R : Non. Adaptez simplement les deux références « ligne précédente » ($E$4:$E4 et $A$4:$A4) à ligne de départ − 1.

Q : Puis‑je réutiliser cette logique pour attribuer des « ID groupe » ?
R : Oui. C’est une technique standard pour transformer une liste libre (E) en identifiants compacts (A) par ordre d’apparition.

Q : Pourquoi ne pas utiliser un simple COUNTIF cumulatif ?
R : Parce qu’un cumul type COUNTIF($E$5:E5, E5) donnerait 1, 2, 3… à chaque répétition, alors que vous voulez réutiliser le même numéro pour toutes les occurrences d’une même valeur.

Conclusion

Pour numéroter automatiquement A5:A34 d’après E5:E34, la formule « robuste » ci‑dessus couvre tous les usages et versions d’Excel. Si vous disposez d’Excel 365, la solution « déversée » (UNIQUE + XMATCH/EQUIVX) est encore plus concise et évolutive. Dans tous les cas, vous obtenez des numéros stables, séquentiels et réutilisés pour les doublons, tout en laissant vides les lignes dont la valeur source est vide ou nulle.