Google Sheets : comment incrémenter une liste automatiquement / solution 1 avec des formules

J’évoquais dans un article l’importance d’identifier chaque ligne d’une table de données avec un ID unique. Je proposais alors de construire l’ID en amont de la saisie de nouvelles lignes. Ce nouvel article est le premier d’une série qui listera quelques solutions pour créer un ID unique à la volée, à chaque création de nouvelle ligne.

Prenons le cas de saisie de devis pour notre fameuse agence « 10 Zaïne ». Chaque nouvelle pièce doit respecter un chrono sans trous ni doublons. Sinon Marie-Edwige de la compta va vous faire la misère.

Chaque nouveau devis doit obtenir un identifiant constitué de l’année en cours et du rang du dernier devis créé, incrémenté de « 1 ». Dans l’exemple ci-dessus, il est facile de voir que le prochain ID sera 2020-021. Mais si la table est triée par date, ou filtrée sur un client, comment connaître le prochain ID disponible ?

Solution 1 : avec formules seulement

Il s’agit d’utiliser la validation de données dans la colonne en question, en récupérant le dernier numéro créé incrémenté de 1.

À chaque création de ligne, il suffira de choisir la seule proposition. Je créé donc une feuille qui sera dédiée au calcul permanent du dernier numéro attribué. Je rapporte dans cette feuille la colonne des ID déjà attribués, en évitant la première ligne qui contient le titre de la colonne :

=ARRAYFORMULA(DEVIS!A2:A)

Dans notre cas, l’ID est constitué de l’année en cours (un nombre à 4 chiffres) et d’un numéro sur 3 chiffres séparés par un « – » : YYYY-NNN. Le tableur ne peut pas trouver la valeur maximale d’une telle chaîne de caractères.

La formule MAX() renvoie « 0 » 🙁

Il faut donc transformer cette chaîne de caractères en nombre.

La fonction CNUM() convertit une chaine en nombre, mais le « – » pose toujours problème. Il faut donc supprimer ce « -« .

Supprimer un caractère dans une chaîne de caractères :

La fonction SUBSTITUE() permet de remplacer un ou plusieurs caractères dans une chaîne de caractères. Recherchons donc le « – » pour le remplacer par… rien du tout.

=ARRAYFORMULA(SUBSTITUE(DEVIS!A2:A;"-";""))

Attention, si en apparence nous obtenons des nombres, il s’agit en fait de texte, identifiable par l’alignement à gauche par défaut. Mais nous pouvons à présent forcer la conversion en nombre avec CNUM();

=ARRAYFORMULA(CNUM(SUBSTITUE(DEVIS!A2:A;"-";"")))

Nous voyons à présent que les valeurs sont des nombres, la valeur MAX() s’affiche enfin correctement.

Incrémenter et reformater la chaîne de caractères

Il suffit donc à présent d’ajouter une unité à la valeur maximale de la liste.

=MAX(A:A)+1

=GAUCHE(B1;4)&"-"&DROITE(B1;3)

Mettre en place la validation des données

Il suffit à présent de mettre en place une validation des données sur la colonne ID de la feuille de devis. Utilisez le critère « Liste créée à partir d’une plage » et sélectionnez la cellule qui calcule le prochain ID.

À la création d’une nouvelle ligne, la cellule ID affiche le prochain ID disponible.

Cette première solution est une proposition que j’ai pu mettre en place dans plusieurs tables de données. Proposition qui reste perfectible : il faudrait par exemple prendre en compte l’année en cours pour repasser à 0 au 1er janvier, de plus la validation des données affiche une erreur dans les ID déjà renseignés, ce qui peut être perturbant. À suivre donc dans le prochain épisode, avec une solution en script, plus automatique.