Nous pouvons transformer des doublons en valeurs uniques dans Google Sheets en attribuant des lettres alphabétiques, des chiffres romains ou des nombres dans un ordre séquentiel. Voyons comment faire !
Par exemple, si la chaîne de caractères « pomme » apparaît trois fois, nous pouvons la rendre unique en ajoutant des chiffres romains comme suit :
- pomme
- pomme_I
- pomme_II
Pour cela, nous avons besoin d’une combinaison de trois formules :
- compteur, remplissage automatique séquentiel de chiffres romains/alphabet/nombres
- et Vlookup.
Ces formules peuvent être utilisées individuellement et elles ne sont pas aussi compliquées que vous le pensez.
Je vais d’abord utiliser ces formules dans leurs propres colonnes (colonnes auxiliaires) pour transformer les doublons en valeurs uniques dans Google Sheets. Ensuite, nous pourrons apprendre comment combiner toutes ces formules rapidement pour éviter les colonnes auxiliaires.
Voici un exemple de transformation des doublons en valeurs uniques en attribuant des numéros d’occurrence (lettres alphabétiques/nombres/chiffres romains) dans Google Sheets.
Vous avez la flexibilité de choisir les caractères à utiliser pour rendre les doublons uniques. Cela signifie que vous pouvez basculer entre les chiffres romains, les nombres ou les lettres alphabétiques.
Dans l’exemple ci-dessus, il y a des doublons dans la colonne A. Prenons l’exemple de l’élément « pomme ».
La formule ajoute le numéro d’occurrence I (1 ou a) à la deuxième occurrence de l’élément « pomme », II (2 ou b) à la troisième occurrence du même élément, et ainsi de suite.
Voyons comment rendre les valeurs dupliquées d’une colonne uniques en attribuant des numéros/des lettres d’occurrence, comme expliqué ci-dessus, dans Google Sheets.
Trois Formules pour Ajouter des Occurrences aux Doublons dans Google Sheets
Étape 1 : Formule dans l’onglet ‘Test’
Les données d’exemple utilisées dans la capture d’écran ci-dessus se trouvent dans l’onglet nommé ‘Test’. Dans cet onglet, entrez la formule de comptage en cours d’utilisation suivante dans la cellule D1 :
=ARRAYFORMULA(if(len(A1:A),(COUNTIFS(A1:A,A1:A,ROW(A1:A),"<="&ROW(A1:A))),))
Pour plus d’explications sur cette formule, veuillez consulter cette page – « Running Count in Google Sheets – Formula Examples ».
Étape 2 : Formules dans l’onglet ‘Test_1’ (Remplissage Séquentiel de Chiffres Romains/Alphabet/Nombres)
Veuillez remonter en haut et regarder à nouveau la capture d’écran animée n°1 (GIF).
Ce que nous voulons faire, c’est transformer les doublons en valeurs uniques dans Google Sheets en attribuant des caractères.
Ici, les « caractères » peuvent être des chiffres/nombres alphabétiques ou des chiffres romains. Appelons cela des types d’occurrence. Je vais vous donner des options pour basculer entre les types d’occurrence.
Note : Je ne prends en compte que 50 doublons de chaque valeur. Si vous en voulez plus, vous pouvez modifier les formules ci-dessous en conséquence (comme indiqué ci-dessous).
Quatre formules dans l’onglet « Test_1 » :
Dans la cellule A1, utilisez la formule suivante :
=ArrayFormula(row(A2:A51))
Changez A51 en A101 pour inclure 100 doublons de chaque valeur.
Cette formule renverrait les numéros séquentiels de 2 à 51 dans la plage A1:A50. C’est une formule de tableau, elle se développera automatiquement de A1 à A50.
Explication de la formule – Numérotation automatique dans Google Sheets avec la fonction Row.
Dans la cellule B1, insérez la formule suivante et faites-la glisser jusqu’à B50 (ou B100 pour 100 doublons) pour remplir les lettres alphabétiques (minuscules) :
="_"&lower(regexreplace(address(1,row(B1)),"[^A-Z]",""))
La formule ajoutera également un caractère de soulignement comme valeur préfixe aux lettres alphabétiques (veuillez consulter la capture d’écran n°3 ci-dessous).
La partie « _ »& au début de la formule gère cela. Le reste de la formule est détaillé ici – Comment remplir automatiquement les lettres alphabétiques dans Google Sheets.
Dans la cellule C1, utilisez la formule suivante pour remplir les chiffres romains. Ces chiffres utiliseront également le préfixe du trait de soulignement :
=ArrayFormula("_"&ROMAN(row(B1:B50)))
Ceci est également une formule de tableau. Changez B50 en B100 pour 100 doublons.
Lié : Comment utiliser les chiffres romains dans Google Sheets.
Dans la cellule D1, répétez la formule A1. Mais cette fois, la plage doit être A1:A50 (A100 pour 100 doublons) et nous devons ajouter un préfixe de trait de soulignement. Voici cette formule :
=ArrayFormula("_"&row(A1:A50))
Maintenant, la formule finale dans l’onglet ‘Test’ qui rendra les valeurs dupliquées uniques en leur attribuant leur séquence dans Google Sheets.
Étape 3 : Formule dans l’onglet ‘Test’ – Rendre les Doublons Uniques dans Google Sheets en utilisant Vlookup
Nous pouvons connecter toutes les formules ci-dessus à l’aide de Vlookup. Cela rendra les doublons uniques.
Formule :
=ArrayFormula(A1:A&IFNA(vlookup(D1:D,Test_1!A1:D,E1,0)))
La formule Vlookup ci-dessus se trouve dans la cellule B1 de l’onglet ‘Test’.
Quel est le rôle du Vlookup pour rendre les doublons uniques dans Google Sheets ?
Vlookup recherche le compte en cours, c’est-à-dire les valeurs dans D1:D de l’onglet ‘Test’ (capture d’écran n°4), dans la première colonne de la plage A1:D de l’onglet ‘Test_1’ (capture d’écran n°3).
Ensuite, elle renverrait les valeurs des colonnes 2, 3 ou 4 de l’onglet ‘Test_1’ en fonction de l’entrée dans la cellule E1 de l’onglet ‘Test’ (capture d’écran n°4).
Cela signifie que si vous tapez 2 dans la cellule E1, elle renverra les lettres alphabétiques, 3 renverra les chiffres romains et 4 renverra les nombres (arabes).
Les valeurs de la colonne A sont ensuite combinées avec la sortie du Vlookup en utilisant A1:A& (au début du Vlookup).
Je ne veux tout simplement pas utiliser les colonnes/onglets auxiliaires et les formules multiples. Pouvez-vous combiner les formules ci-dessus et rendre les doublons uniques dans Google Sheets ?
Oui ! Voici les instructions étape par étape.
En remplaçant la référence de cellule/tableau par les formules correspondantes dans le Vlookup ci-dessus, nous pouvons créer une seule formule pour rendre les doublons uniques dans Google Sheets. Voici les étapes.
Étape 1 : Remplacer la Plage Vlookup par une Plage Virtuelle (Séquence)
Dans le Vlookup ci-dessus, la plage est Test1!A1:D. Remplacez cela par {row(A2:A51), »« &ROMAN(row(B1:B50))}.
La formule ultérieure combine les valeurs des colonnes A et C de ‘Test_1’ en tant qu’un tableau à deux colonnes.
Nous pouvons uniquement combiner la formule de la cellule A1 avec la formule de la cellule C1 ou de la cellule D1 pour former un tableau. Nous ne pouvons pas utiliser la formule de la cellule B1 de cet onglet (les lettres alphabétiques), car c’est une formule non-array.
Ainsi, la formule sera :
=ArrayFormula(A1:A&IFNA(vlookup(D1:D,{row(A2:A51),"_"&ROMAN(row(B1:B50))},E1,0)))
ou
=ArrayFormula(A1:A&IFNA(vlookup(D1:D,{row(A2:A51),"_"&row(A1:A50)},E1,0)))
Dans les deux cas, je préfère la première formule (chiffres romains) et je l’utilise donc.
Étape 2: Remplacer la Clé de Recherche Vlookup par une Plage Virtuelle (Occurrence)
La plage de clé de recherche Vlookup est D1:D dans l’onglet ‘Test’.
Nous pouvons remplacer D1:D par la formule correspondante, c’est-à-dire, if(len(A1:A),(COUNTIFS(A1:A,A1:A,ROW(A1:A),"<="&ROW(A1:A))),) de la cellule D1.
=ArrayFormula(A1:A&IFNA(vlookup(if(len(A1:A),(COUNTIFS(A1:A,A1:A,ROW(A1:A),"<="&ROW(A1:A))),),{row(A2:A51),"_"&ROMAN(row(B1:B50))},E1,0)))
Ensuite, remplacez E1 par 2, car il n’y a que deux colonnes dans la plage Vlookup. Voyez le Vlookup final ci-dessous.
Étape 3: Formule de Combinaison pour Rendre les Doublons Uniques en Attribuant les Numéros de Séquence dans Google Sheets
Saisissez le Vlookup complexe ci-dessous dans la cellule B1.
=ArrayFormula(A1:A&IFNA(vlookup(if(len(A1:A),(COUNTIFS(A1:A,A1:A,ROW(A1:A),"<="&ROW(A1:A))),),{row(A2:A51),"_"&ROMAN(row(B1:B50))},2,0)))
N’hésitez pas à supprimer l’onglet ‘Test_1’, la formule de la cellule D1 dans l’onglet ‘Test’ et la valeur dans la cellule E1 dans le même onglet.
C’est tout. Amusez-vous bien !
