Avec une formule de tableau avancée, vous pouvez trouver des occurrences multiples de lignes dans Google Sheets. Lorsque je parle d’occurrences multiples de lignes, je veux dire les lignes en double.
En plus de la formule de tableau, pour ceux qui préfèrent avoir une formule plus courante, j’ai également cette solution. J’ai une formule COUNTIFS non tableau qui trouve les lignes en double.
Comme vous le savez peut-être déjà, vous devez copier et coller la formule non-tableau dans chaque ligne. Mais il a un avantage. Vous pouvez utiliser cette formule dans la mise en forme conditionnelle.
Qu’est-ce que les occurrences multiples de lignes et ce que nous allons faire ?
Nous allons tester si le contenu des colonnes sélectionnées dans une ligne n’est pas la deuxième, la troisième ou toute autre occurrence dans une plage. Dans chaque ligne, nous allons effectuer ce test.
Dans cet exemple, les occurrences multiples sont basées sur les colonnes A et B. Ici, le numéro de facture 100005 dans la cellule A7 et sa date correspondante dans la cellule B7 sont les deuxièmes occurrences des valeurs de la ligne n° 3, cellule A3 et B3. C’est le cas avec les valeurs des lignes 5 et 9.
Chaque fois que les deuxièmes, troisièmes ou autres occurrences se produisent, la formule dont nous allons discuter renverra la valeur « Non » dans la colonne D, sinon « Oui ». Le résultat « Non » indique que la ligne n’est pas la première apparition et qu’il s’agit d’une ligne en double.
En testant ainsi, vous atteignez les objectifs suivants :
-
Trouver les lignes en double en fonction de correspondances sur une ou plusieurs colonnes. Partout où la formule met « Non », ces lignes sont des doublons. Si la formule met « Oui », on parle de lignes distinctes.
-
Mettre en évidence les lignes distinctes / les lignes d’occurrences multiples non effectuées ou les lignes d’occurrences multiples. Ici, j’ai mis en évidence les lignes d’occurrence non effectuées.
Nous commençons ici.
Le tutoriel étape par étape pour trouver des occurrences multiples de lignes dans Google Sheets
Voici la formule non-tableau pour trouver et marquer la deuxième, la troisième ou toute autre occurrence d’un élément.
=SI(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1, "Oui", "Non")
Vous devez saisir cette formule dans la cellule D2 et faire glisser la poignée de remplissage pour la copier vers le bas jusqu’à D9.
Si vous n’êtes pas familier avec l’utilisation de la formule COUNTIFS dans Google Sheets, voici une brève description. Mais je vous suggère de consulter mon guide des fonctions Google Sheets pour apprendre cette fonction ainsi que toutes les autres fonctions liées au comptage.
Syntaxe : COUNTIFS(plage_de_critères1, critère1, [plage_de_critères2, critère2, …])
En fait, la formule COUNTIFS compte les occurrences d’éléments jusqu’à cette ligne. Par exemple, si vous copiez la formule ci-dessus comme indiqué, la formule dans la cellule E7 serait la suivante. Veuillez utiliser soigneusement les symboles monétaires.
=COUNTIFS($A$2:$A7, $A7, $B$2:$B7, $B7)
Cette formule COUNTIFS compte la plage_de_critères1 A2: A7 et la plage_de_critères2 B2: B7 pour la correspondance avec le critère1 dans A7 et le critère2 dans B7.
En fait, cette ligne 7 est la deuxième occurrence de la ligne 3 en termes de valeurs dans les colonnes A et B. Donc la formule renverra le nombre 2 comme résultat.
Partout où la valeur renvoyée par COUNTIFS est supérieure à 1, cela signifie que ces lignes sont des lignes répétées ou vous pouvez dire des lignes en double. Avec la formule de test logique SI de Google Sheets, vous pouvez renvoyer la valeur « Non » dans ces lignes. Ici, « Non » signifie que ce n’est pas la première apparition de cette ligne.
Maintenant, vous pouvez automatiser cela avec une formule de tableau. Je vais expliquer cela plus tard. Avant cela, vous pouvez voir comment mettre en forme conditionnelle les occurrences multiples de lignes dans Google Sheets. Vous pouvez utiliser les mêmes astuces pour mettre en forme conditionnelle les occurrences multiples de lignes non effectuées.
Comment mettre en évidence les occurrences multiples de lignes dans Google Sheets
Mettre en évidence les lignes en double dans Google Sheets sera assez facile si vous avez correctement appris l’utilisation de la formule ci-dessus.
Sélectionnez la plage et appliquez la même formule ci-dessus après avoir supprimé la clause logique SI. Mais attendez un instant. Suivez les étapes.
Étapes :
- Sélectionnez la plage A2: D12.
- Accédez au menu Format de Google Sheets > Mise en forme conditionnelle.
- Dans le champ Formule personnalisée, appliquez la formule suivante.
=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1
- Définissez la couleur et voilà ! Vous avez défini des règles de mise en évidence pour les lignes distinctes. Modifiez la partie de la formule finale de « =1 » en « >1 » pour modifier la couleur. Cette fois, seules les lignes répétées seront mises en évidence.
Comme mentionné ci-dessus, nous pouvons trouver des occurrences multiples de lignes dans Google Sheets en utilisant une formule de tableau également. Voici cette formule impressionnante.
Formule de tableau pour trouver des occurrences multiples de lignes dans Google Sheets
D’abord, laissez-moi vous fournir la formule. Ici, à des fins de clarification, je vous fournis la formule pour une correspondance de colonne unique. Ensuite, je vous expliquerai comment utiliser cette formule comme ci-dessus avec deux colonnes.
=ArrayFormula(IF(LEN(A2:A),if( SORT(ROW(A2:A)-ROW(A2)+2,SORT(ROW(A2:A)-ROW(A2)+2,A2:A,1),1)-MATCH(A2:A,SORT(A2:A),0)=1,"Oui","Non"),))
Les fonctions SORT et MATCH sont la base de cette formule Google Sheets pour trouver et faire correspondre des occurrences multiples d’éléments dans Google Sheets.
Cette formule vérifie les numéros de facture dans la colonne A et si ils se répètent dans d’autres lignes, les marque avec « Non » pour indiquer que ce n’est pas la première apparition.
Remarque : Vous pouvez ignorer cette partie si vous avez du mal à la comprendre. Pour apprendre l’utilisation de cette formule dans plusieurs colonnes, allez directement à la fin de ce tutoriel où vous pouvez voir une capture d’écran. Cela montre comment utiliser cette formule dans deux colonnes.
La logique de la formule :
D’abord, je parle de la partie de formule SORT(ROW(A2:A)-ROW(A2)+2,A2:A,1).
La fonction ROW dans la formule renvoie les nombres de 2 à 9 comme ci-dessous.
Partout où vous appliquez cette formule, le numérotage ne commence que par deux et c’est obligatoire. Pour cela, j’ai ajusté cette formule de tableau Row en conséquence.
Partie de la formule Sort :
La formule SORT trie la sortie de cette formule de ligne en fonction des valeurs dans la colonne A (numéro de l’étudiant) par ordre croissant.
Syntaxe : SORT(plage, colonne_de_tri, est_ascending)
Dans notre formule de tri ci-dessus (sous la logique de la formule), la plage de tri est la sortie de la fonction de ligne mentionnée ci-dessus. Ce sont des nombres séquentiels de 2 à 9.
Vous pouvez utiliser soit la plage de tri comme une plage, soit comme numéro de colonne dans le tri. Ici, j’ai utilisé les numéros d’étudiant dans la colonne A en tant que colonne de tri. Pour vous montrer, j’ai saisi le résultat dans la colonne E.
Dans la colonne C, j’ai simplement trié les valeurs de la colonne A (numéro d’étudiant) pour votre référence et dans la colonne D les numéros séquentiels.
Maintenant, lorsque vous vérifiez la sortie de la formule de tri ci-dessus dans la colonne E, vous pouvez comprendre une chose. Les nombres de la colonne E sont disposés par rapport à leurs valeurs correspondantes dans la colonne C. Vous pouvez comparer cela avec les valeurs des colonnes A et D.
Maintenant, la deuxième formule de tri prend également les mêmes numéros de ligne comme plage de tri. Mais cette fois, au lieu de la colonne A, le tri se fait en fonction de la valeur retournée par la formule de tri mentionnée précédemment 1 (colonne E). Vous pouvez vous référer à la colonne F sur la capture d’écran ci-dessus pour voir la sortie.
C’est assez déroutant, n’est-ce pas ? Pas besoin d’organiser une séance de remue-méninges. Pour résumer, vous pouvez lire les formules de ligne, de tri 1 et de tri 2 ci-dessus comme suit.
Formule générique : SORT(nombres de lignes, SORT(nombres de lignes, élément dans la colonne A, 1), 1)
nombres de lignes = nombres séquentiels de 2 à 9.
élément dans la colonne A = numéro d’étudiant dans la plage A2: A9
Bref, le but de la formule ci-dessus est le suivant.
Supposez que vous ayez les valeurs ci-dessus dans la plage A2: A9 dans un nouvel onglet. Triez-le par ordre croissant. Maintenant, entrez les chiffres de 2 à 9 dans la plage B2: B9 de cet onglet. Ensuite, faites glisser les lignes pour les organiser selon nos données d’origine. Voyez les nombres séquentiels. Ils seraient placés selon la sortie de la formule ci-dessus.
Partie de formule Match :
Dans des données classées, vous pouvez utiliser la formule générique suivante pour trouver les occurrences des éléments.
nombres séquentiels (doivent commencer à partir de 2) – position relative des éléments = numérotation par groupe.
La position relative peut être obtenue à l’aide de la fonction Match.
=MATCH(A2:A,SORT(A2:A),0)
Syntaxe : MATCH(clé_de_recherche, plage, [type_de_recherche])
Veuillez vous référer au résultat de la formule de Match dans la colonne G sur la capture d’écran ci-dessus. Dans la colonne H, j’ai simplement déduit la valeur de la colonne G de celle de la colonne F.
Comme vous pouvez le voir, cela renvoie 1 pour les éléments non répétés. Si l’élément se répète, vous obtiendrez le compte de cet élément par incrémentation.
Lorsque vous faites défiler vers l’arrière et vérifiez ma formule finale, vous pouvez voir que j’ai utilisé un test logique SI pour mettre « Oui » pour la valeur 1 et si la valeur est supérieure à un, la formule mettra « Non ».
Cette formule renvoie le même résultat que notre formule COUNTIFS. La différence, c’est qu’il s’agit d’une formule de tableau, donc pas besoin de copier et de coller dans toutes les lignes.
Tout comme la formule COUNTIFS non-tableau, vous pouvez également utiliser deux colonnes dans cette formule de tableau. Veuillez vous référer à cette capture d’écran. J’ai marqué où vous voulez apporter les modifications.
Dans les formules de tri et de correspondance, remplacez la plage A2: A par la formule soulignée qui utilise la fonction CONCAT.
Mise à jour : Voir l’image ci-dessus. Il y a en fait trois formules CONCAT. Par erreur, je n’ai souligné que deux d’entre elles.
Ceci combine les colonnes A et B. Si vous voulez vérifier les occurrences multiples dans d’autres colonnes, vous pouvez changer cette partie. Cela signifie que vous n’êtes pas limité à utiliser cette formule dans deux colonnes. C’est tout.
En utilisant l’une ou l’autre des deux formules, vous pouvez trouver des occurrences multiples de lignes dans Google Sheets.
De plus, vous pouvez utiliser cette formule pour identifier les doublons. Si vous appliquez cette formule à votre feuille, vous comprendrez cela.
Vous pouvez maintenant utiliser les fonctions de recherche et de mise en forme conditionnelle dans Google Sheets pour trouver et mettre en évidence les occurrences multiples de lignes. Profitez de la puissance de Google Sheets pour travailler plus intelligemment et plus efficacement.
Pour plus d’informations et de conseils sur Google Sheets, n’hésitez pas à visiter Crawlan.com.
